數據庫常用的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. DROP TABLE IF EXISTS `abc`;
2. CREATE TABLE `abc` (
3. `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT comment'商品名稱',
4. `name` char(80) NOT NULL DEFAULT '' comment'商品名稱',
5. `title` char(20) NOT NULL DEFAULT '' comment'商品名稱',
6. `type` tinyint(1) NOT NULL DEFAULT '1' comment'商品名稱',
7. `condition` char(100) NOT NULL DEFAULT '' comment'商品名稱',
8. `show` bit DEFAULT 1 comment '是否可見',
9. `price` decimal(5,2) not null comment '價格',
10. `status` enum('0', '1', '2') NOT NULL DEFAULT '0' comment '狀態',
11. PRIMARY KEY (`id`),
12. UNIQUE KEY `name` (`name`)
13. ) 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. SELECT prod_nameFROM Products;
2. #檢索單列
3.  
4. SELECT prod_id, prod_name, prod_priceFROMProducts;
5. #檢索多列
6.  
7. SELECT * FROM Products;
8. #檢索所有列
9.  
10. SELECT DISTINCTvend_id FROMProducts;
11. #檢索不同的值
12.  
13. SELECTprod_name FROM Products LIMIT 5;
14. #返回不超過5行數據
15.  
16. SELECTprod_name FROM Products LIMIT 5 OFFSET 5;
17. #返回從第5行起的5行數據。LIMIT指定返回的行數，LIMIT帶的OFFSET指定從哪兒開始。
18.  
19. /* SELECT prod_name, vend_id
20. FROMProducts; */
21. SELECTprod_name
22. FROMProducts;
23. #多行注釋

2.排序檢索數據

1. SELECTprod_name
2. FROMProducts
3. ORDER BYprod_name;
4. #排序數據
5.  
6. SELECT prod_id, prod_price, prod_name
7. FROMProducts
8. ORDER BY prod_price, prod_name;
9. #按多個列排序
10.  
11. SELECT prod_id, prod_price, prod_name
12. FROMProducts
13. ORDER BY 2, 3;
14. #按列位置排序，第三行表示先按prod_price, 再按prod_name進行排序
15.  
16. SELECT prod_id, prod_price, prod_name
17. FROMProducts
18. ORDER BY prod_priceDESC, prod_name;
19. #prod_price列以降序排序，而prod_name列（在每個價格內）仍然按標準的升序排序

3.過濾數據

1. SELECT prod_name, prod_price
2. FROMProducts
3. WHERE prod_price< 10;
4. #檢查單個值
5.  
6. SELECT prod_name, prod_price
7. FROMProducts
8. WHERE vend_id <> ‘DLL01';
9. #不匹配檢查
10.  
11. SELECT prod_name, prod_price
12. FROMProducts
13. WHERE prod_priceBETWEEN 5 AND 10;
14. #范圍值檢查
15.  
16. SELECT cust_name
17. FROMCUSTOMERS
18. WHERE cust_emailIS NULL;
19. #空值檢查

4.高級數據過濾

1. SELECTprod_id, prod_price, prod_name
2. FROMProducts
3. WHERE vend_id = ‘DLL01'ANDprod_price <= 4;
4. #AND操作符
5.  
6. SELECTprod_name, prod_price
7. FROMProducts
8. WHEREvend_id='DLL01' OR vend_id='BRS01';
9. #OR操作符
10.  
11. SELECTprod_name, prod_price
12. FROMProducts
13. WHERE (vend_id = 'DLL01'ORvend_id='BRS01')
14. ANDprod_price >= 10；
15. #求值順序 AND的優先級高于OR
16.  
17. SELECTprod_name, prod_price
18. FROMProducts
19. WHERE vend_idIN (‘DLL01','BRS01')
20. ORDER BY prod_name;
21. #IN操作符
22.  
23. SELECT prod_name
24. FROMProducts
25. WHERE NOTvend_id = ‘DLL01'
26. ORDER BY prod_name;
27. #NOT 操作符
28.  
29. SELECT prod_name
30. FROMProducts
31. WHEREvend_id <> ‘DLL01'
32. ORDER BY prod_name;
33. #NOT 操作符

5.通配符進行過濾

1. SELECT prod_id, prod_name
2. FROMProducts
3. WHERE prod_nameLIKE ‘Fish%';
4. #%表示任何字符出現任意次數，找出所有以詞Fish起頭的產品
5.  
6. SELECT prod_id, prod_name
7. FROMProducts
8. WHERE prod_nameLIKE ‘%bean bag%';
9. #‘%bean bag%'表示匹配任何位置上包含文本bean bag的值，不論它在之前或之后出現什么字符
10.  
11. SELECT prod_name
12. FROMProducts
13. WHERE prod_nameLIKE ‘F%y';
14. #找出以F起頭，以y結尾的所有產品

根據郵件地址的一部分來查找電子郵件，例如WHERE email LIKE ‘b%@forta.com'
 
WHERE prod_nameLIKE ‘%'; #不會匹配產品名稱為NULL的行，其它均可
 
%代表搜索模式中給定位置的0個、1個或多個字符

下劃線的用途與%一樣，但它只匹配單個字符，而不是多個字符

1. SELECT prod_id, prod_name
2. FROMProducts
3. WHERE prod_nameLIKE ‘__inchteddy bear';
4. #搜索模式要求匹配兩個通配符而不是一個

方括號（[]）通配符用來指定一個字符集，它必須匹配指定位置（通配符的位置）的一個字符

1. SELECT cust_contact
2. FROMCustomers
3. WHERE cust_contactLIKE ‘[JM]%'
4. ORDER BY cust_contact;

#[JM]匹配方括號中任意一個字符，它也只能匹配單個字符，任何多于一個字符的名字都不匹配。[JM]之后的%通配符匹配第一個字符之后的任意數目的字符，返回所需結果。

1. SELECT cust_contact
2. FROMCustomers
3. WHERE cust_contactLIKE ‘[^JM]%'
4. ORDER BY cust_contact;
5. #以J和M之外的任意字符起頭的任意聯系人名

6.創建計算字段

1. SELECT Concat(vend_name, ‘ (‘, vend_country, ‘)')
2. FROMVendors
3. ORDER BY vend_name;
4.  
5. 輸出
6. Bear Emporium(USA)
7. Bears R Us (USA)
8. Doll House Inc.(USA)
9. Fun and Games(England)
10.  
11. SELECT Concat(vend_name, ‘ (‘, vend_country, ‘)')
12. ASvend_title
13. FROMVendors
14. ORDER BY vend_name; #給拼接而成新字段起了一個名稱
15.  
16. SELECT prod_id,
17. quantity,
18. item_price,
19. quantity*item_price AS expanded_price
20. FROMOrderItems
21. WHERE order_num = 20008;
22. #匯總物品的價格

7.使用函數處理數據

1. SELECT vend_name, UPPER(vend_name)AS vend_name_upcase
2. FROMVendors
3. ORDER BY vend_name;
4. #文本處理函數
5.  
6. SELECT cust_name, cust_contact
7. FROMCustomers
8. WHERE SOUNDEX(cust_contact) =SOUNDEX(‘MichaelGreen');
9. # SOUNDEX()函數搜索，匹配所有發音類似于Michael Green 的聯系名
10.  
11. SELECT order_num
12. FROMOrders
13. WHERE YEAR(order_date) = 2012;
14. #從日期中提取年份

8.數據匯總

1. SELECT AVG(prod_price)ASavg_price
2. FROMProducts;
3. WHERE vend_id = ‘DLL01';
4.  
5. SELECT COUNT(*)ASnum_cust
6. FROMCustomers;
7. #COUNT(*)對表中行的數目進行計數，不管表列中包含的是空值（NULL）還是非空值
8.  
9. SELECT COUNT(cust_email)ASnum_cust
10. FROMCustomers;
11. #只對具有電子郵件地址的客戶計數
12.  
13. SELECT MAX(prod_price)ASmax_price
14. FROMProducts;
15. #返回Products表中最貴物品的價格
16.  
17. SELECT MIN(prod_price)ASmin_price
18. FROMProducts;
19. #返回Products表中最便宜物品的價格
20.  
21. SELECT SUM(quantity)ASitems_ordered
22. FROMOrderItems
23. WHERE order_num = 20005;
24. #SUM(quantity)返回訂單中所有物品數量之和，WHERE 子句保證只統計某個物品訂單中的物品
25.  
26. SELECT SUM(item_price*quantity)AS total_price
27. FROMOrderItems
28. WHERE order_num = 20005;
29. #SUM(item_price*quantity)返回訂單中所有物品價錢之和，WHERE子句保證只統計某個物品訂單中的物品
30.  
31. SELECT AVG(DISTINCTprod_price)AS avg_price
32. FROMProducts
33. WHERE vend_id = ‘DLL01';
34. #使用DISTINCT參數，平均值只考慮各個不同的價格
35.  
36. SELECT COUNT(*) AS num_items,
37. MIN(prod_price)AS price_min,
38. MAX(prod_price)AS price_max,
39. AVG(prod_price)AS price_avg
40. FROMProducts;
41. #組合聚集函數

9.分組數據

1. SELECT vend_id,COUNT(*) AS num_prods
2. FROMProducts
3. GROUP BY vend_id;
4. #創建分組
5.  
6. SELECT vend_id,COUNT(*) AS num_prods
7. FROMProducts
8. WHERE prod_price >= 4
9. GROUP BY vend_id
10. HAVING COUNT(*) >= 2;
11. #WHERE 子句過濾所有prod_price至少為4的行，然后按vend_id分組數據，HAVING子句過濾計數為2或2以上的分組。
12.  
13. SELECT order_num,COUNT(*) AS items
14. FROMOrderItems
15. GROUP BY order_num
16. HAVING COUNT(*) >= 3
17. ORDER BY items, order_num;
18. #按訂購物品的數目排序輸出

10.使用子查詢

1. SELECT cust_id
2. FROMOrders
3. WHERE order_numIN (SELECT order_num
4. FROM OrderItems
5. WHERE prod_id = ‘RGAN01');
6.  
7. SELECT cust_name, cust_contact
8. FROMCustomers
9. WHERE cust_idIN (‘10000000004', ‘10000000005');

11.聯結表

1. SELECT vend_name, prod_name, prod_price
2. FROMVendors, Products
3. WHERE Vendors vend_id = Products.vend_id;
4. #創建聯結
5.  
6. SELECT vend_name, prod_name, prod_price
7. FROMVendorsINNER JOIN Products
8. ONVendors.vend_id = Products.vend_id;
9. #內聯結
10.  
11. SELECT prod_name, vend_name, prod_price, quantity
12. FROMOrderItems, Products, Vendors
13. WHERE Products.vend_id = Vendors.vend_id
14. ANDOrderItems.prod_id = Products.prod_id
15. ANDorder_num = 20007;
16. #聯結多個表

12.創建高級聯結

1. SELECT c1.cust_id, c1.cust_name, c1.cust_contact
2. FROMCustomersAS c1, Customers AS c2
3. WHERE c1.cust_name = c2.cust_name
4. ANDc2.cust_contact = ‘Jim Jones';
5. #自聯結，此查詢中需要的兩個表實際上是相同的表
6.  
7. SELECT C. *, O.order_num, O.order_date,
8. OI.prod_id, OI.quantity, OI.item_price
9. FROMCustomersAS C, Orders AS O, OrderItems AS OI
10. WHERE C.cust_id = O.cust_id
11. ANDOI.order_num = O.order_num
12. ANDprod_id = ‘RGAN01';
13. #自然聯結排除多次出現，使每一列只返回一次
14.  
15. SELECT Customers.cust_id, Orders.order_num
16. FROMCustomersLEFT OUTER JOIN Orders
17. ONCustomers.cust_id = Orders.cust_id;
18. #從FROM子句左邊的表Customers表中選擇所有行
19.  
20. SELECT Customers.cust_id, Orders.order_num
21. FROMCustomersRIGHT OUTER JOIN Orders
22. ONOrders.cust_id =Customers.cust_id;
23. #從右邊的表中選擇所有行。
24.  
25. SELECT Customers.cust_id, Orders.order_num
26. FROMOrdersFULL OUTER JOIN Customers
27. ONOrders.cust_id = Customers.cust_id;
28. #檢索兩個表中的所有行并關聯那些可以關聯的行

13.組合查詢

1. SELECT cust_name, cust_contact, cust_email
2. FROMCustomers
3. WHERE cust_state IN (‘IL', ‘IN', ‘MI')
4. UNION
5. SELECT cust_name, cust_contact, cust_email
6. FROMCustomers
7. WHERE cust_name = ‘Fun4ALL'
8. ORDER BY cust_name, cust_contact;
9. #SQL允許執行多個查詢，并將結果作為一個查詢結果集返回

14.插入數據

1. INSERT INTO Customers(cust_id,
2. Cust_name,
3. Cust_address,
4. Cust_city,
5. Cust_state,
6. Cust_zip,
7. Cust_country,
8. Cust_contact,
9. Cust_email)
10. VALUES(‘100000000006',
11. ‘Toy Land',
12. ‘123 Any Street',
13. ‘New York',
14. ‘NY',
15. ‘111111',
16. ‘USA',
17. NULL,
18. NULL);
19. #插入完整的行
20.  
21. INSERT INTO Customers(cust_id,
22. Cust_contact,
23. Cust_email,
24. Cust_name,
25. Cust_address,
26. Cust_city,
27. Cust_state,
28. Cust_zip,
29. Cust_country)
30. SELECT cust_id,
31. Cust_contact,
32. Cust_email,
33. Cust_name,
34. Cust_address,
35. Cust_city,
36. Cust_state,
37. Cust_zip,
38. Cust_country
39. FROMCustNew；
40. #將另一個表中的顧客列合并到Customers表中。
41.  
42. SELECT *
43. INTOCustCopy
44. FROMCustomers;
45. #從一個表復制到另一個表中

15.更新和刪除數據

1. UPDATE Customers
2. SETcust_contact = ‘Sam Roberts',
3. Cust_email = ‘sam@toyland.com'
4. WHERE cust_id = ‘100000000000006';
5. #更新多個列
6.  
7. UPDATE Customers
8. SETcust_email = NULL
9. WHERE cust_id = ‘1000000005';
10. #刪除某個列
11.  
12. DELETE FROM Customers
13. WHERE cust_id = ‘1000000006';
14. #刪除數據

16. 創建和操縱表

1. CREATE TABLE OrderItems
2. (
3. Order_num INTEGER NOT NULL,
4. Order_item INTEGER NOT NULL,
5. Prod_id CHAR(10) NOT NULL,
6. Quantity INTEGER NOT NULL DEFAULT 1,
7. Item_price DECIMAL(8, 2) NOT NULL
8. );
9.  
10. ALTER TABLE Vendors
11. ADDvend_phone CHAR(20);
12. #給表增加一個名為vend_phone的列，其數據類型為CHAR
13.  
14. ALTER TABLE Vendors
15. DROP COLUMN vend_phone;
16. #該表中的某列
17.  
18. DROP TABLE CustCopy;
19. #刪除表

17.高級SQL特性

主鍵：表中一列（或多個列）的值唯一標識表中的每一行。主鍵是一種特殊的約束，用來保證一列或一組列的值唯一標識表中的每一行。這方便直接或交互地處理表中的行。沒有主鍵，要安全地UPDATE 或DELETE特定行而不影響其他行會非常困難。
①任意兩行的主鍵值都不相同；
      ②每行都具有一個主鍵值（即列中不允許NULL值）
      ③包含主鍵值的列從不修改或更新。
      ④主鍵值不能重用

1. CREATE TABLE Vendors
2. (
3. Vend_id CHAR(10) NOT NULL PRIMARYKEY,
4. Vend_name CHAR(50) NOT NULL,
5. Vend_address CHAR(50) NULL,
6. Vend_city CHAR(5) NULL,
7. Vend_state CHAR(10) NULL,
8. Vend_zip CHAR(10) NULL,
9. Vend_country CHAR(50) NULL
10. );
11.  
12. ALTER TABLE Vendors
13. ADD CONSTRAINT PRIMARY KEY (vend_id);

#給表vend_id 列定義添加關鍵字PRIMARYKEY, 使其成為主鍵