创建计算字段
拼接
SELECT Concat(vend_name, ' (', vend_country, ')')
AS ven_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
可以用来检验语句是否正确或者测试,例如:
SELECT 2 * 3; -- (will return 6)
SELECT TRIM(' abc '); -- (will return 'abc')
SELECT Now(); -- (will return current time)
数据处理函数
函数
文本处理函数
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
SOUNDEX()
函数,是一个将任何文本串转换为描述其音节表示的字母数字模式的算法,可以用于模糊音查找
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');
日期时间处理函数
SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = 2012
数值处理函数
一般数学函数ABS() COS() EXP() PI() SIN() SQRT() TAN()
分别是绝对值,余弦,指数,圆周率,正弦,开方,正切
汇总数据
聚集函数
AVG
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
COUNT
SELECT COUNT(*) AS num_cust
FROM Customers;
MAX
SELECT MAX(prod_price) AS max_price
FROM Products;
MIN
SELECT MIN(prod_price) AS min_price
FROM Products;
SUM
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;
聚集不同值
DISTINCT
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
组合聚集函数
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;
分组数据
GROUP BY
创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
GROUP BY
在WHERE
语句之后,ORDER BY
语句之前
过滤分组
WHERE
过滤行,HAVING
过滤分组
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
SELECT子句顺序
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING ->ORDER BY
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
ORDER BY vend_id
使用子查询
子查询
子查询过滤
从内向外处理
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 vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON 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;
高级联结
表别名
好处是可以缩短语句,允许在一个SQL语句使用多次同一张表
SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND O.order_num = OI.order_num
AND OI.prod_id = 'RGAN01';
自联结
SELECT C2.cust_id, C2.cust_name, C2.cust_contact
FROM Customers AS C1, Customers AS C2
WHERE C2.cust_name = C1.cust_name
AND C1.cust_contact = 'Jim Jones';
DBMS处理联结远远比子查询快得多
自然联结
排除多次出现,每一列只返回一次
外联结
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_order
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
组合查询
UNION
使用UNION
给出每条SELECT语句,之间加上UNION
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';
规则
每个查询必须包含相同列,表达式或聚集函数,数据类型需兼容
包含重复行
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';