sql笔记

检索数据

select

检索不同的值

distinct

限制结果

limit 第0行开始  limit 0,1 返回第一个

注释

--  #  /* */

排序数据

order by

select * from orderitems order by order_item, quantity;

先按照order_item排序，再按照quantity排序；

默认升序，降序用 desc

过滤数据

 where

范围值检查

BETWEEN 5 AND 10 

空值检查

IS NULL

组合WHERE子句

AND OR NOT

IN操作符

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%';

%表示任何字符出现任意次数
_下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

计算字段

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;

聚集函数

数据分组

GROUP BY 

过滤分组

HAVING

HAVING支持所有WHERE操作符

SELECT子句顺序

子查询

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Order
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));