sql語言

20世紀70年代，IBM開發出SQL，用于DB2

1981年，IBM推出SQL/DS數據庫

業內標準微軟和Sybase的T-SQL，Oracle的PL/SQL

SQL作為關系型數據庫所使用的標準語言，最初是基于IBM的實現在1986年被批準的。1987年，“國際標準化

組織(ISO)”把ANSI(美國國家標準化組織) SQL作為國際標準。

SQL：ANSI SQL

SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

 

SQL語言規范

在數據庫系統中，SQL語句不區分大小寫(建議用大寫)

但字符串常量區分大小寫

SQL語句可單行或多行書寫，以“;”結尾

關鍵詞不能跨多行或簡寫

用空格和縮進來提高語句的可讀性

子句通常位于獨立行，便于編輯，提高可讀性

注釋：

SQL標準：

/*注釋內容*/ 多行注釋

— 注釋內容 單行注釋，注意有空格

MySQL注釋：

#

數據庫的組件(對象)：

數據庫（database）、表（table）、索引(index)、、用戶(user)、存儲過程(procedure)、

函數(function)、觸發器(trigger)、事件調度器(event scheduler)等

命名規則：

必須以字母開頭

可包括數字和三個特殊字符（# _ $）

不要使用MySQL的保留字

同一database(Schema)下的對象不能同名

SQL語句分類

DDL: Data Defination Language（數據的定義語言）對數據結構，數據對象

CREATE（增）, DROP（刪）, ALTER（改）

DML: Data Manipulation Language（數據的操作語言）對數據的，加記錄數據

INSERT（增）, DELETE（刪）, UPDATE（改）

DCL：Data Control Language（數據的控制語言）權限為題

GRANT（授權）, REVOKE（撤銷）

DQL：Data Query Language（數據的查詢語言）

SELECT（選擇）

 

SQL語句構成：

Keyword組成clause（關鍵字組成的句子）

多條clause組成語句

示例：

SELECT *? ? ? ? ? ? ? ? ? ? ? ? ? ?SELECT子句

FROM? ? products? ? ? ? ? ? ?FROM子句

WHERE? ?price>400? ? ? ?WHERE子句

說明：此為一組SQL語句，由三個子句構成，SELECT,FROM和WHERE是關鍵字

 

創建數據庫：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] ‘DB_NAME’;

CHARACTER SET ‘character set name’

COLLATE ‘collate name’

刪除數據庫

DROP DATABASE|SCHEMA [IF EXISTS] ‘DB_NAME’;

查看支持所有字符集：SHOW CHARACTER SET;

查看支持所有排序規則：SHOW COLLATION;

獲取命令使用幫助：

mysql> HELP KEYWORD;

查看數據庫列表：

mysql> SHOW DATABASES；

 

表：二維關系

設計表：遵循規范

定義：字段，索引

字段：字段名，字段數據類型，修改符

約束，索引：應該創建在經常用作查詢條件的字段上

 

創建表：CREATE TABLE

(1) 直接創建

(2) 通過查詢現存表創建；新表會被直接插入查詢而來的數據

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)] [table_options]

[partition_options] select_statement]

(3) 通過復制現存的表的表結構創建，但不復制數據

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE

old_tbl_name) }

注意：

Storage Engine是指表類型，也即在表創建時指明其使用的存儲引擎，同一庫中不同表可以使用不同的存儲

引擎

同一個庫中表建議要使用同一種存儲引擎類型

CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修飾符, col2 type2 修飾符, …)

字段信息

?col type1

?PRIMARY KEY(col1,…)

?INDEX(col1, …)

?UNIQUE KEY(col1, …)

表選項：

?ENGINE [=] engine_name

SHOW ENGINES;查看支持的engine類型

?ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

獲取幫助：mysql> HELP CREATE TABLE;

表操作

查看所有的引擎：SHOW ENGINES

查看表：SHOW TABLES [FROM db_name]

查看表結構：DESC [db_name.]tb_name

刪除表：DROP TABLE [IF EXISTS] tb_name

查看表創建命令：SHOW CREATE TABLE tbl_name

查看表狀態：SHOW TABLE STATUS LIKE ‘tbl_name’

查看庫中所有表狀態：SHOW TABLE STATUS FROM db_name

 

數據類型：

數據長什么樣？

數據需要多少空間來存放？

系統內置數據類型和用戶定義數據類型

MySql支持多種列類型：

數值類型

日期/時間類型字符串(字符)類型

選擇正確的數據類型對于獲得高性能至關重要，三大原則：

更小的通常更好，盡量使用可正確存儲數據的最小數據類型

簡單就好，簡單數據類型的操作通常需要更少的CPU周期

盡量避免NULL，包含為NULL的列，對MySQL更難優化

數據類型

1、整型

tinyint(m) 1個字節 范圍(-128~127)

smallint(m) 2個字節 范圍(-32768~32767)

mediumint(m) 3個字節 范圍(-8388608~8388607)

int(m) 4個字節 范圍(-2147483648~2147483647)

bigint(m) 8個字節 范圍(+-9.22*10的18次方)

取值范圍如果加了unsigned，則最大值翻倍，如tinyint unsigned的取值范圍為(0~255)

int(m)里的m是表示SELECT查詢結果集中的顯示寬度，并不影響實際的取值范圍，規定了MySQL的一些交

互工具（例如MySQL命令行客戶端）用來顯示字符的個數。

對于存儲和計算來說，Int(1)和Int(20)是相同的

BOOL，BOOLEAN：布爾型，是TINYINT(1)的同義詞。zero值被視為假。非zero值視為真

2、浮點型(float和double)，近似值

float(m,d) 單精度浮點型 8位精度(4字節) m總個數，d小數位

double(m,d) 雙精度浮點型16位精度(8字節) m總個數，d小數位

設一個字段定義為float(6,3)，如果插入一個數123.45678,實際數據庫里存的是123.457，但總個數還以實際

為準，即6位

3、定點數

在數據庫中存放的是精確值,存為十進制

decimal(m,d) 參數m<65 是總個數，d<30且 d<m 是小數位

MySQL5.0和更高版本將數字打包保存到一個二進制字符串中（每4個字節存9個數字）。

例decimal(18,9)小數點兩邊將各存儲9個數字，一共使用9個字節：小數點前的數字用4個字節，小數點后的

數字用4個字節，小數點本身占1個字節

浮點類型在存儲同樣范圍的值時，通常比decimal使用更少的空間。float使用4個字節存儲

double占用8個字節

因為需要額外的空間和計算開銷，所以應該盡量只在對小數進行精確計算時才使用decimal——例如存儲財

務數據。但在數據量比較大的時候，可以考慮使用bigint代替decimal

4、字符串(char,varchar,_text)

char(n) 固定長度，最多255個字符

varchar(n)可變長度，最多65535個字符

tinytext 可變長度，最多255個字符

text 可變長度，最多65535個字符

mediumtext 可變長度，最多2的24次方-1個字符

longtext 可變長度，最多2的32次方-1個字符

BINARY(M) 固定長度，可存二進制或字符，長度為0-M字節

VARBINARY(M) 可變長度，可存二進制或字符，允許長度為0-M字節

內建類型：ENUM枚舉, SET集合

char和varchar：

1.char(n) 若存入字符數小于n，則以空格補于其后，查詢之時再將空格去掉。所以char類型存儲的字符串末

尾不能有空格，varchar不限于此

2.char(n) 固定長度，char(4)不管是存入幾個字符，都將占用4個字節，varchar是存入的實際字符數+1個字

節（n< n>255)，所以varchar(4),存入3個字符將占用4個字節

3.char類型的字符串檢索速度要比varchar類型的快

varchar和text：

1.varchar可指定n，text不能指定，內部存儲varchar是存入的實際字符數+1個字節（n< n>255)，text是實

際字符數+2個字節。

2.text類型不能有默認值

3.varchar可直接創建索引，text創建索引要指定前多少個字符。varchar查詢速度快于text

5.二進制數據：BLOB
BLOB和text存儲方式不同，TEXT以文本方式存儲，英文存儲區分大小寫，而Blob是以二進制方式存儲，

不分大小寫

BLOB存儲的數據只能整體讀出

TEXT可以指定字符集，BLOB不用指定字符集

6.日期時間類型

date 日期 ‘2008-12-2′

time 時間 ’12:25:36’

datetime 日期時間 ‘2008-12-2 22:06:44’

timestamp 自動存儲記錄修改時間

YEAR(2), YEAR(4)：年份

timestamp字段里的時間數據會隨其他字段修改的時候自動刷新，這個數據類型的字段可以存放這條記錄最

后被修改的時間

 

修飾符

所有類型：

NULL 數據列可包含NULL值

NOT NULL 數據列不允許包含NULL值

DEFAULT 默認值

PRIMARY KEY 主鍵

UNIQUE KEY 唯一鍵

CHARACTER SET name 指定一個字符集

數值型

AUTO_INCREMENT 自動遞增，適用于整數類型

UNSIGNED 無符號

創建復合主鍵

對照創建表family2和family的表定義

DROP TABLE [IF EXISTS] ‘tbl_name’;

ALTER TABLE ‘tbl_name’

字段：

添加字段：add

ADD col1 data_type [FIRST|AFTER col_name]

刪除字段：drop

修改字段：

alter（默認值）, change（字段名）, modify（字段屬性）

索引:

添加索引：add index

刪除索引: drop index

表選項

修改:

查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;

查看幫助：Help ALTER TABLE

修改表的語言類型

DML語句

DML:

INSERT, DELETE, UPDATE, SELECT

INSERT：一次插入一行或多行數據

語法

INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

{VALUES | VALUE} ({expr | DEFAULT},…),(…),…

[ ON DUPLICATE KEY UPDATE 如果重復更新之

col_name=expr[, col_name=expr] … ]

簡化寫法：

INSERT tbl_name [(col1,…)] VALUES (val1,…), (val21,…)

表名后面不加定義名 后面 values 后面跟表的5個字段都要賦值

表名后面加字段定義，values后面要一一對應，沒有寫的字段就是null

也可以一次加2行

 

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

SELECT …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

沒有說明的列 用默認值 null 表示? 用于把舊表的信息導入到新表中

類型匹配互相包容

UPDATE：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

注意：一定要有限制條件，否則將修改所有行的指定字段

限制條件：

WHERE

LIMIT

Mysql 選項：–safe-updates| –i-am-a-dummy|-U

DELETE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

可先排序再指定刪除的行數

注意：一定要有限制條件，否則將清空表中的所有數據

限制條件：

WHERE

LIMIT

TRUNCATE TABLE tbl_name; 清空表