SQL必知必会

检索数据

SELECT prod_name FROM Products;
#检索多列数据
SELECT prod_id,prod_name,prod_price FROM Products;
#检索所有列
SELECT * FROM Products;
#DISTINCT关键字，返回不同的值，去除相同的值
SELECT DISTINCT vend_id FROM Products;
#LIMIT关键字限制返回的数据行数
SELECT prod_name FROM Products LIMIT 5;
#指定从哪儿开始检索已经检索的函数 LIMIT 4 OFFSET 3 表示从第3行开始检索4行数据
SELECT prod_name FROM Products LIMIT 4 OFFSET 3;

排序检索数据

SELECT prod_name FROM Products ORDER BY prod_name;
#返回多列进行排序，首先按照价格排序，然后按照名称
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name;
#按照检索数据的第二列prod_price和第三列prid_name进行排序
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3;
#DESC关键字以降序来排序检索数据，ASC为升序，默认为升序。
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC;

过滤数据

SELECT prod_name,prod_price FROM Products WHERE prod_price = 3.49;
#检索prod_price小于10的数据
SELECT prod_name,prod_price FROM Products WHERE prod_price <10;
#检索vend_id不等于DLL01的数据
SELECT vend_id,prod_name FROM Products WHERE vend_id != 'DLL01';
#BETWEEN关键字范围值检索
SELECT prod_name,prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
#检索空值
SELECT cust_name FROM Customers WHERE cust_email IS NULL;

高级数据过滤

SELECT prod_name,prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
#由于AND关键字在求值过程中优先级更高，操作符被错误组合
SELECT prod_name,prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price ####= 10;
#圆括号的优先级高于AND和OR，所以先过滤括号内的OR条件
SELECT prod_name,prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price ####= 10;
SELECT prod_name,prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

用通配符进行过滤

SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE "Fish%";
#检索prod_name包含bean bag的数据
SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE '%bean bag%';
#下划线_与%通配符作用一样，但它只匹配单个字符
SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';
#方括号[]通配符用来指定一个字符集，它必须匹配指定位置的字符
#匹配所以姓名以J或M开头的联系人
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;

创建计算字段

SELECT CONCAT(vend_name,'(',vend_country,')') FROM Vendors ORDER BY vend_name;
#AS后面为别名
SELECT CONCAT(vend_name,"<",vend_country,">") AS vend_title FROM Vendors ORDER BY vend_name;
#计算字段
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

使用函数处理数据

SELECT vend_name,UPPER(vend_name) AS vend_name_upper FROM Vendors ORDER BY vend_name;
SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;
#求最大值
SELECT MAX(cust_id) AS maxid FROM Customers ORDER BY cust_name;
#求平均值
SELECT AVG(cust_id) AS avgid FROM Customers ORDER BY cust_name;
SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id='DLL01';
#求和
SELECT SUM(cust_id) AS sumid FROM Customers ORDER BY cust_name;

汇总数据

SELECT COUNT(*) AS num_cust FROM Customers;
SELECT COUNT(cust_email) AS num_cust FROM Customers;
SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM Products;

分组数据

SELECT vend_id,COUNT(*) AS num_prods FROM Products GROUP BY vend_id;
#HAVING非常类似于WHERE,WHERE过滤行，而 HAVING 过滤分组
SELECT cust_id,COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*)>=2;
SELECT vend_id,COUNT(*) AS num_prods FROM Products WHERE prod_price >=4 GROUP BY vend_id HAVING COUNT(*)>=2;
SELECT order_num,COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*)>=3 ORDER BY items,order_num;

---

SELECT子句及其顺序

子 句	说 明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	否
HAVING	组级过滤	仅在按组计算聚集时使用
ORDER BY	输出排序顺序	否

---

使用子查询

SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id='RGAN01');
SELECT cust_name,cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id='RGAN01'));
#作为计算字段使用子查询
SELECT cust_name,cust_state,(SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS Orders FROM Customers ORDER BY cust_name;

多表查询 联结表 等值联结，成为内联结

SELECT vend_name,prod_name,prod_price FROM Vendors,Products WHERE Vendors.vend_id = Products.vend_id;
#多表查询，过滤了三次
SELECT prod_name,vend_name,prod_price,quantity FROM OrderItems,Products,Vendors WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;

高级联结表

SELECT cust_name,cust_contact FROM Customers AS C,Orders AS O,OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = "RGAN01";
SELECT cust_id,cust_name,cust_contact FROM Customers WHERE cust_name = (SELECT cust_name FROM Customers WHERE cust_contact='Jim Jones')
#自联结
SELECT c1.cust_id,c1.cust_name,c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';
#自然联结
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';
#左外联结LEFT OUTER JOIN 从 FROM 子句左边的表（Customers 表）中选择所有行左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。
SELECT Customers.cust_id,Orders.order_num FROM Customers  LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ord FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;

组合查询

#关键字UNION,会从查询结果集中自动去除了重复的行，如果不想重复可使用UNION ALL
SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_name = 'Fun4All'
#UNION ALL不会去除重复行
SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION ALL SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_name = 'Fun4All'
SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION SELECT cust_name,cust_contact,cust_email FROM Customers WHERE cust_name = 'Fun4All' ORDER BY cust_name,cust_contact;

插入数据

INSERT INTO Customers VALUES('1000000006','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_address,cust_city,cust_state,cust_zip,cust_country FROM CustNew;
#将一个表的所以数据复制到另一张表里
CREATE TABLE CustCopy AS SELECT * FROM Customers;

更新和删除数据

UPDATE Customers SET cust_email = "9men@live.cn" WHERE cust_id = '1000000005';
UPDATE Customers SET cust_contact = 'Sam Roberts',cust_email='sam@google.com' WHERE cust_id = '1000000006';
DELETE FROM Customers WHERE cust_id = '1000000006';

创建和操纵表

CREATE TABLE Products (
	prod_id CHAR(10) NOT NULL,
	vend_id CHAR(10) NOT NULL,
	prod_name CHAR(254) NOT NULL,
	prod_price DECIMAL(8,2) NOT NULL,
	prod_desc VARCHAR(1000) NULL
)
#指定默认值
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 Vendors DROP COLUMN vend_phone;
#删除表
DROP TABLE CustCopy;