SQL是目前使用最為廣泛的數據庫語言之一。這里，我總結了在數據庫上，用SQL語言對數據排序、過濾和分組，以及表、視圖、聯結、子查詢、游標、存儲過程和觸發器等內容。
數據庫相關
查所有數據庫 show databases;
創建數據庫 create database 數據庫名;
查看數據庫 show create database 數據庫名; //顯示當初創建這個庫的時候使用什么樣的sql語句
創建數據庫指定字符集 create database 數據庫名 character set utf8/gbk
刪除數據庫 drop database 數據庫名;
使用數據庫 use 數據庫名;
表相關
創建表 create table 表名(id int,name varchar(10)); //表名區分大小寫
查看所有表 show tables;
查看單個表屬性 show create table 表名; //使用的什么創建語句，可以在后面加\G使描述更清晰
查看表字段 desc 表名;
創建表指定引擎和字符集 create table 表名(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;
刪除表 drop table [if exists] 表名;刪除表（可選擇添加是否存在則刪除）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DROP TABLE IF EXISTS `abc`;CREATE TABLE `abc` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT comment'商品名稱', `name` char(80) NOT NULL DEFAULT '' comment'商品名稱', `title` char(20) NOT NULL DEFAULT '' comment'商品名稱', `type` tinyint(1) NOT NULL DEFAULT '1' comment'商品名稱', `condition` char(100) NOT NULL DEFAULT '' comment'商品名稱', `show` bit DEFAULT 1 comment '是否可見', `price` decimal(5,2) not null comment '價格', `status` enum('0', '1', '2') NOT NULL DEFAULT '0' comment '狀態', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)) ENGINE=INNODB DEFAULT CHARSET=utf8; |
建立數據庫：
CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
約束
not null 非空
default 默認約束語句，用于約束對應列中的值的默認值，除非默認值為空值，否則不可插入空值
unique 唯一約束語句，用于約束對應列中的值不能重復，可以有空值，但只能出現一個空值
primary 主鍵 = 唯一 + 非空
auto_increment 自動增長，用于系統自動生成字段的主鍵值
foreign key(從表id) reference 主表名（id）； 表與表之間建立聯系
修改表
修改表名 rename table 舊表名 to 新表名;
修改表名 alter table 舊表名 rename 新表名
修改字段數據類型 alter table 表名 modify 字段名 數據類型
修改表屬性 alter table 表名 engine=myisam/innodb charset=utf8/gbk;
添加表字段 alter table 表名 add 新字段名 新數據類型 [約束] [first/after 已存在字段名];
刪除表字段 alter table 表名 drop 字段名;
修改表字段名和類型 alter table 表名 change 舊字段名 新字段名 類型;
修改表的類型和位置 alter table 表名 modify 字段名 類型 first/after 已存在字段名;
刪除表 drop table 表名;
更改表的存儲引擎 alter table 表名 engine = 新的存儲引擎;
刪除表的外鍵約束 alter table 表名 drop foreign key 外鍵名; //刪除所有的外鍵之后，才能刪除對應的主鍵所在的表
數據相關
插入數據：
insert into 表名 values(5,‘xiaoming',null);
insert into 表名 (字段名1,字段名2…) values (2,‘aa'…);
insert into 表名 values(5,‘xiaoming',null),(5,‘xiaoming',null),(5,‘xiaoming',null);
insert into 表名 (字段名1,字段名2) values (2,‘aa'),(2,‘aa'),(2,‘aa');
查詢
select * from 表名;
select name from 表名;
select * from 表名 where id=10;
修改
update 表名 set 要修改的字段名=100 where 根據字段名=10;
刪除
delete from 表名 where 字段名=10;
下面是補充
1.檢索數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SELECT prod_nameFROM Products;#檢索單列 SELECT prod_id, prod_name, prod_priceFROMProducts;#檢索多列 SELECT * FROM Products;#檢索所有列 SELECT DISTINCTvend_id FROMProducts;#檢索不同的值 SELECTprod_name FROM Products LIMIT 5;#返回不超過5行數據 SELECTprod_name FROM Products LIMIT 5 OFFSET 5;#返回從第5行起的5行數據。LIMIT指定返回的行數，LIMIT帶的OFFSET指定從哪兒開始。 /* SELECT prod_name, vend_idFROMProducts; */SELECTprod_nameFROMProducts;#多行注釋 |
2.排序檢索數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SELECTprod_nameFROMProductsORDER BYprod_name;#排序數據 SELECT prod_id, prod_price, prod_nameFROMProductsORDER BY prod_price, prod_name;#按多個列排序 SELECT prod_id, prod_price, prod_nameFROMProductsORDER BY 2, 3;#按列位置排序，第三行表示先按prod_price, 再按prod_name進行排序 SELECT prod_id, prod_price, prod_nameFROMProductsORDER BY prod_priceDESC, prod_name;#prod_price列以降序排序，而prod_name列（在每個價格內）仍然按標準的升序排序 |
3.過濾數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SELECT prod_name, prod_priceFROMProductsWHERE prod_price< 10;#檢查單個值 SELECT prod_name, prod_priceFROMProductsWHERE vend_id <> ‘DLL01';#不匹配檢查 SELECT prod_name, prod_priceFROMProductsWHERE prod_priceBETWEEN 5 AND 10;#范圍值檢查 SELECT cust_nameFROMCUSTOMERSWHERE cust_emailIS NULL;#空值檢查 |
4.高級數據過濾
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SELECTprod_id, prod_price, prod_nameFROMProductsWHERE vend_id = ‘DLL01'ANDprod_price <= 4;#AND操作符 SELECTprod_name, prod_priceFROMProductsWHEREvend_id='DLL01' OR vend_id='BRS01';#OR操作符 SELECTprod_name, prod_priceFROMProductsWHERE (vend_id = 'DLL01'ORvend_id='BRS01') ANDprod_price >= 10；#求值順序 AND的優先級高于OR SELECTprod_name, prod_priceFROMProductsWHERE vend_idIN (‘DLL01','BRS01')ORDER BY prod_name;#IN操作符 SELECT prod_nameFROMProductsWHERE NOTvend_id = ‘DLL01'ORDER BY prod_name;#NOT 操作符 SELECT prod_nameFROMProductsWHEREvend_id <> ‘DLL01'ORDER BY prod_name;#NOT 操作符 |
5.通配符進行過濾
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE ‘Fish%';#%表示任何字符出現任意次數，找出所有以詞Fish起頭的產品 SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE ‘%bean bag%';#‘%bean bag%'表示匹配任何位置上包含文本bean bag的值，不論它在之前或之后出現什么字符 SELECT prod_nameFROMProductsWHERE prod_nameLIKE ‘F%y';#找出以F起頭，以y結尾的所有產品 |
根據郵件地址的一部分來查找電子郵件，例如WHERE email LIKE ‘b%@forta.com'
WHERE prod_nameLIKE ‘%'; #不會匹配產品名稱為NULL的行，其它均可
%代表搜索模式中給定位置的0個、1個或多個字符
下劃線的用途與%一樣，但它只匹配單個字符，而不是多個字符
|
1
2
3
4
|
SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE ‘__inchteddy bear';#搜索模式要求匹配兩個通配符而不是一個 |
方括號（[]）通配符用來指定一個字符集，它必須匹配指定位置（通配符的位置）的一個字符
|
1
2
3
4
|
SELECT cust_contactFROMCustomersWHERE cust_contactLIKE ‘[JM]%'ORDER BY cust_contact; |
#[JM]匹配方括號中任意一個字符，它也只能匹配單個字符，任何多于一個字符的名字都不匹配。[JM]之后的%通配符匹配第一個字符之后的任意數目的字符，返回所需結果。
|
1
2
3
4
5
|
SELECT cust_contactFROMCustomersWHERE cust_contactLIKE ‘[^JM]%'ORDER BY cust_contact;#以J和M之外的任意字符起頭的任意聯系人名 |
6.創建計算字段
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SELECT Concat(vend_name, ‘ (‘, vend_country, ‘)')FROMVendorsORDER BY vend_name; 輸出Bear Emporium(USA)Bears R Us (USA)Doll House Inc.(USA)Fun and Games(England) SELECT Concat(vend_name, ‘ (‘, vend_country, ‘)') ASvend_titleFROMVendorsORDER BY vend_name; #給拼接而成新字段起了一個名稱 SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_priceFROMOrderItemsWHERE order_num = 20008;#匯總物品的價格 |
7.使用函數處理數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT vend_name, UPPER(vend_name)AS vend_name_upcaseFROMVendorsORDER BY vend_name;#文本處理函數 SELECT cust_name, cust_contactFROMCustomersWHERE SOUNDEX(cust_contact) =SOUNDEX(‘MichaelGreen');# SOUNDEX()函數搜索，匹配所有發音類似于Michael Green 的聯系名 SELECT order_numFROMOrdersWHERE YEAR(order_date) = 2012;#從日期中提取年份 |
8.數據匯總
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
SELECT AVG(prod_price)ASavg_priceFROMProducts;WHERE vend_id = ‘DLL01'; SELECT COUNT(*)ASnum_custFROMCustomers;#COUNT(*)對表中行的數目進行計數，不管表列中包含的是空值（NULL）還是非空值 SELECT COUNT(cust_email)ASnum_custFROMCustomers;#只對具有電子郵件地址的客戶計數 SELECT MAX(prod_price)ASmax_priceFROMProducts;#返回Products表中最貴物品的價格 SELECT MIN(prod_price)ASmin_priceFROMProducts;#返回Products表中最便宜物品的價格 SELECT SUM(quantity)ASitems_orderedFROMOrderItemsWHERE order_num = 20005;#SUM(quantity)返回訂單中所有物品數量之和，WHERE 子句保證只統計某個物品訂單中的物品 SELECT SUM(item_price*quantity)AS total_priceFROMOrderItemsWHERE order_num = 20005;#SUM(item_price*quantity)返回訂單中所有物品價錢之和，WHERE子句保證只統計某個物品訂單中的物品 SELECT AVG(DISTINCTprod_price)AS avg_priceFROMProductsWHERE vend_id = ‘DLL01';#使用DISTINCT參數，平均值只考慮各個不同的價格 SELECT COUNT(*) AS num_items, MIN(prod_price)AS price_min, MAX(prod_price)AS price_max, AVG(prod_price)AS price_avgFROMProducts;#組合聚集函數 |
9.分組數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELECT vend_id,COUNT(*) AS num_prodsFROMProductsGROUP BY vend_id;#創建分組 SELECT vend_id,COUNT(*) AS num_prodsFROMProductsWHERE prod_price >= 4GROUP BY vend_idHAVING COUNT(*) >= 2;#WHERE 子句過濾所有prod_price至少為4的行，然后按vend_id分組數據，HAVING子句過濾計數為2或2以上的分組。 SELECT order_num,COUNT(*) AS itemsFROMOrderItemsGROUP BY order_numHAVING COUNT(*) >= 3ORDER BY items, order_num;#按訂購物品的數目排序輸出 |
10.使用子查詢
|
1
2
3
4
5
6
7
8
9
|
SELECT cust_idFROMOrdersWHERE order_numIN (SELECT order_num FROM OrderItems WHERE prod_id = ‘RGAN01'); SELECT cust_name, cust_contactFROMCustomersWHERE cust_idIN (‘10000000004', ‘10000000005'); |
11.聯結表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SELECT vend_name, prod_name, prod_priceFROMVendors, ProductsWHERE Vendors vend_id = Products.vend_id;#創建聯結 SELECT vend_name, prod_name, prod_priceFROMVendorsINNER JOIN ProductsONVendors.vend_id = Products.vend_id;#內聯結 SELECT prod_name, vend_name, prod_price, quantityFROMOrderItems, Products, VendorsWHERE Products.vend_id = Vendors.vend_idANDOrderItems.prod_id = Products.prod_idANDorder_num = 20007;#聯結多個表 |
12.創建高級聯結
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SELECT c1.cust_id, c1.cust_name, c1.cust_contactFROMCustomersAS c1, Customers AS c2WHERE c1.cust_name = c2.cust_nameANDc2.cust_contact = ‘Jim Jones';#自聯結，此查詢中需要的兩個表實際上是相同的表 SELECT C. *, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_priceFROMCustomersAS C, Orders AS O, OrderItems AS OIWHERE C.cust_id = O.cust_idANDOI.order_num = O.order_numANDprod_id = ‘RGAN01';#自然聯結排除多次出現，使每一列只返回一次 SELECT Customers.cust_id, Orders.order_numFROMCustomersLEFT OUTER JOIN OrdersONCustomers.cust_id = Orders.cust_id;#從FROM子句左邊的表Customers表中選擇所有行 SELECT Customers.cust_id, Orders.order_numFROMCustomersRIGHT OUTER JOIN OrdersONOrders.cust_id =Customers.cust_id;#從右邊的表中選擇所有行。 SELECT Customers.cust_id, Orders.order_numFROMOrdersFULL OUTER JOIN CustomersONOrders.cust_id = Customers.cust_id;#檢索兩個表中的所有行并關聯那些可以關聯的行 |
13.組合查詢
|
1
2
3
4
5
6
7
8
9
|
SELECT cust_name, cust_contact, cust_emailFROMCustomersWHERE cust_state IN (‘IL', ‘IN', ‘MI')UNIONSELECT cust_name, cust_contact, cust_emailFROMCustomersWHERE cust_name = ‘Fun4ALL'ORDER BY cust_name, cust_contact;#SQL允許執行多個查詢，并將結果作為一個查詢結果集返回 |
14.插入數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
INSERT INTO Customers(cust_id, Cust_name, Cust_address, Cust_city, Cust_state, Cust_zip, Cust_country, Cust_contact, Cust_email)VALUES(‘100000000006', ‘Toy Land', ‘123 Any Street', ‘New York', ‘NY', ‘111111', ‘USA', NULL, NULL);#插入完整的行 INSERT INTO Customers(cust_id, Cust_contact, Cust_email, Cust_name, Cust_address, Cust_city, Cust_state, Cust_zip, Cust_country)SELECT cust_id, Cust_contact, Cust_email, Cust_name, Cust_address, Cust_city, Cust_state, Cust_zip, Cust_countryFROMCustNew；#將另一個表中的顧客列合并到Customers表中。 SELECT *INTOCustCopyFROMCustomers;#從一個表復制到另一個表中 |
15.更新和刪除數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
UPDATE CustomersSETcust_contact = ‘Sam Roberts',Cust_email = ‘[email protected]'WHERE cust_id = ‘100000000000006';#更新多個列 UPDATE CustomersSETcust_email = NULLWHERE cust_id = ‘1000000005';#刪除某個列 DELETE FROM CustomersWHERE cust_id = ‘1000000006';#刪除數據 |
16. 創建和操縱表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
CREATE TABLE OrderItems(Order_num INTEGER NOT NULL,Order_item INTEGER NOT NULL,Prod_id CHAR(10) NOT NULL,Quantity INTEGER NOT NULL DEFAULT 1,Item_price DECIMAL(8, 2) NOT NULL); ALTER TABLE VendorsADDvend_phone CHAR(20);#給表增加一個名為vend_phone的列，其數據類型為CHAR ALTER TABLE VendorsDROP COLUMN vend_phone;#該表中的某列 DROP TABLE CustCopy;#刪除表 |
17.高級SQL特性
主鍵：表中一列（或多個列）的值唯一標識表中的每一行。主鍵是一種特殊的約束，用來保證一列或一組列的值唯一標識表中的每一行。這方便直接或交互地處理表中的行。沒有主鍵，要安全地UPDATE 或DELETE特定行而不影響其他行會非常困難。
①任意兩行的主鍵值都不相同；
②每行都具有一個主鍵值（即列中不允許NULL值）
③包含主鍵值的列從不修改或更新。
④主鍵值不能重用
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE TABLE Vendors(Vend_id CHAR(10) NOT NULL PRIMARYKEY,Vend_name CHAR(50) NOT NULL,Vend_address CHAR(50) NULL,Vend_city CHAR(5) NULL,Vend_state CHAR(10) NULL,Vend_zip CHAR(10) NULL,Vend_country CHAR(50) NULL); ALTER TABLE VendorsADD CONSTRAINT PRIMARY KEY (vend_id); |
#給表vend_id 列定義添加關鍵字PRIMARYKEY, 使其成為主鍵
