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;