SQL必知必会总结
1.DISTINCT 只返回不同的数据
SELECT DISTINCT vend_id FROM Products;
2.LIMIT 限制查询行数
SELECT prod_name FROM Products LIMIT 5;
3.LIMIT n OFFSET m 限制从m行开始的n行
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
4.AND 优先级高于 OR,OR加括号
SELECT prod_name FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
5.IN操作符一般比一组OR执行的更快(数据量少不明显)
6.% 通配符不会匹配NULL
SELECT prod_name LIKE '%';
7._ 单字符通配符
SELECT prod_name WHERE prod_name LIKE '_dy';
8.CONCAT() 连接字段或字符
SELECT CONCAT(vend_name, vend_country) FROM Vendors ORDER BY vend_name;
9.RTRIM() 去除右侧空格、LTRIM() 去除左侧空格、TRIM() 去除两侧空格
SELECT RTRIM('name ')
SELECT LTRIM(' name')
SELECT TRIM(' name ')
10.YEAR() 提取年份
SELECT order_num,order_date FROM Orders WHERE YEAR(order_date) = 2012;
11.COUNT() *则统计总行数不管值是否为空,具体列名则统计该列不为NULL的行数
12.HAVING 过滤组
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
13.UNION 默认去重,UNION ALL 取消去重
14.将查询的结果直接插入到另一个表中
INSERT INTO table1(id,age,name)
SELECT id,age,name
FROM table2;
15.复制表
CREATE TABLE CusCopy AS SELECT * FROM Customers;
16.创建视图
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num=Orders.order_num;