目录
检索数据
不同值 DISTINCT
- DISTINCT关键字作用于所有列,不仅仅是跟在其后那列。
限制语句
- LIMIT/TOP
- 注意第一个被检索行是第0行
排序检索数据
ORDER BY
- 应是最后一条子句
- 可用非检索列排序
- 可用相对列排序
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY 2,3;
- 注意:可能代码长了(代码更改时)易导致歧义/错误
降序
ORDER BY prod_price DESC,prod_name;
- 仅指定前面这一列
过滤数据
空值检查
- 过滤数据时一定要验证含NULL行确实出现在返回数据中
- 当选择不包含指定值所有行时,并不返回NULL值的行(NULL具有特殊含义,无法判断是否匹配
IN
- 圆括号内,用逗号分隔的合法值
WHERE vend_id IN('DLL01','BRS01')
- 效果类似OR,执行更快,可包含其他SELECT语句
通配符
- 使用通配符,必须使用LIKE操作符
- 通配符仅能用于文本字段(字符串)
- 尽量不要再搜索模式的开始使用通配符,可能搜索起来过慢
%
- 字符出现任意次数
- 可匹配0个字符
- 许多DBMS用空格填补字段内容,如WHERE prod_name LIKE 'F%y',某列50个字符,存储文本为Fish bean bag toy(17个字符),其后附加33个空格,则无法检索该行。
- 无法匹配NULL
_
- 单个字符
计算字段
拼接 concat
SELECT Concat(vend_name,'(',vend_country,')')
去掉空格
- RTRIM()
- LTRIM()
- TRIM() (去掉字符串左右两边的空格)
测试计算
- 省略FROM字句,简单地访问和处理表达式
SELECT 3*2;
将返回6
-
SELECT Now();
将返回当前日期和时间(不一定是mysql)
函数
- 提取字符串的组成部分:SUBSTRING()
- 数据类型转换:CONVERT()
- 取当前日期:CURDATE()
- 大写:UPPER()
- 返回字符串左边的字符:LEFT()
- 字符串长度:LENGTH()
- 小写:LOWER()
- 将文本串转换为描述其语音表示的字母数字模式:SOUNDEX()
数值处理函数
- 绝对值:ABS()
- 指数:EXP()
- 圆周率:PI()
- 平方根:SQRT()
聚集函数
- 平均值:AVG() #忽略NULL值
行数:COUNT():
- COUNT(*):无论NULL值都计数
- COUNT(column):忽略NULL值
MAX() MIN() SUM()
- 用于文本数据时,MAX()、MIN()返回该列排序后最后最前行
#聚集函数尽量使用别名
例子
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
返回特定供应商提供的产品的平均价格,平均值仅考虑各个不同的价格
DISTINCT
- 不能用于COUNT(*),必须指定列名,不能用于计算或表达式
分组数据
GROUP BY
输入
SELECT vend_id,COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
输出
vend_id num_prods
----- --------
BRS01 3
DLL01 4
FNG01 2
分析
- GROUP BY子句指示DBMS按vend_id排序并分组数据。即会对每个vend_id而不是整个表计算num_prods一次。
规定
- GROUP BY子句可以包含任意数目的列,因此可对分组嵌套
- 若嵌套,数据将在最后指定的分组上进行汇总(所以建立分组时,指定的所有列都一起计算,所以不能从个别列取回数据)
- GROUP BY子句中的每列都必须是检索列或有效表达式(但不能是聚集函数),若在SELECT中使用表达式,则必须在GROUP BY子句指定相同表达式。不能使用别名。
- 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)
- 除聚集计算语句,SELECT语句中的每一列都必须在GROUP BY子句中给出
- 如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。 如果列中有多行NULL值,它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
HAVING
- 类似WHERE,区别:WHERE过滤行,HAVING过滤分组。
- 里面不能嵌套SELECT子句
具有两个以上产品且其价格大于等于4的供应商
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
vend_id num_prods
------- -----------
BRS01 3
FNG01 2
顺序
子查询
- 作为子查询的SELECT语句只能查询单个列(单个行?)
- 常用于in操作符
联结表
内联结(INNER JOIN ... ON)与WHERE子句对应关系
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;
别名
- 表别名只在查询执行中使用,列别名返回到客户端
外联结
- A LEFT JOIN B:左表中选中所有行(无论是否存在匹配行)
- 全外联结:FULL OUTER JOIN
组合查询
UNION
使用场景:
- 一个查询中从不同的表返回结构数据
- 对一个表执行多个查询,按一个查询返回数据
注意:
- UNION中每个查询必须包含相同的列、表达式或聚集函数(顺序随意)
- 列数据类型必须兼容(不必完全相同)
- UNION自动去除重复行,若返回所有匹配行,则UNION ALL
- ORDER BY用于最后,排序所有SELECT返回的结果
更新和删除数据
UPDATE
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
DELETE
DELETE FROM Customers
WHERE cust_id = '1000000006';
- DELETE不需要列名或通配符。DELETE删除整行而不是删除列。要删除指定的列,使用UPDATE语句。
- DELETE防止删除存在外键的行
视图
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors;
存储过程
执行存储过程
EXECUTE AddNewProduct( 'JTS01',
'Stuffed Eiffel Tower',
6.49,
'Plush stuffed toy with the text La
➥Tour Eiffel in red white and blue' );
创建:CREATE PROCEDURE
事务处理
事务:transaction
回退:rollback
提交:commit
保留点:savepoint
游标
待续。