-- ORDER BY 语句应该始终放在最后 SELECT field1, field2 FROM table_name WHERE field1 =3;
WHERE操作符
操作符
说明
=
等于
!=或<>
不等于
<(>)
小于(大于)
<= (》=)
小于等于(大于等于)
!<
不小于
!>
不大于
BETWEEN
在指定两个值之间
IS NULL
为NULL值
1 2 3 4 5 6 7 8 9 10
-- 范围检查,范围需要用AND连接,包括边界 SELECT*FROM stu WHERE grade BETWEEN70AND100; +----+----------+-----+-------+---------------------+ | id | name | sex | grade | birthday | +----+----------+-----+-------+---------------------+ |1| zhangsan | boy |99|2019-09-1219:21:31| |3| wangwu | boy |77|2019-09-0319:22:52| |4| zhaoliu | boy |77|2019-09-1019:28:22| +----+----------+-----+-------+---------------------+ 3rowsinset (0.33 sec)
高级数据过滤
1 2 3 4 5 6 7 8 9 10 11
-- AND 操作 SELECT*FROM stu WHERE grade>90AND sex = "boy";
-- OR 操作 SELECT*FROM stu WHERE grade >90OR grade <60;
-- IN 操作 SELECT*FROM stu WHERE name IN ('zhangsan', "lisi");
-- NOT操作 SELECT*FROM stu WHERENOT name IN ('zhangsan', "lisi");
AND 操作符优先级高于OR,可以加括号改变优先级
IN比OR执行更快
IN可以包含其他SELECT语句
NOT否定跟在它后面的语句
使用通配符进行数据过滤 LIKE
通配符
作用
%
匹配任何字符出现任意次
_
匹配单个字符
[]
匹配指定位置的一个字符(只有SQL Server支持)
1 2 3 4 5
-- 匹配name中带有n的字段 select*from stu where name like "%n%";
-- 匹配name以z或w开头的字段(SQL Server) select*from stu where name like "[zw]%";
通配符搜索耗时比其他搜索长,不要依赖于通配符搜索
创建计算字段
计算字段并不存在于数据库,只是在执行SELECT语句时生成的
拼接字段
DBMS
符号
Access, SQL Server
+
DB2,Oracle,PostgreSQL,SQLite,Open Office Base
`
Mysql,MariaDB
使用Concat()函数
1 2 3 4 5 6 7 8 9 10 11 12 13
-- SQL Server SELECT name + "(" + grade + ")" FROM stu ORDERBY grade;
-- MySQL SELECT CONCAT(name,"(",grade,")") FROM stu ORDERBY grade; +----------------------------+ | CONCAT(name,"(",grade,")") | +----------------------------+ | lisi(66) | | wangwu(77) | | zhaoliu(77) | | zhangsan(99) | +----------------------------+
使用别名
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
-- SQL Server SELECT name + "(" + grade + ")" AS new_name FROM stu ORDERBY grade;
-- MySQL SELECT CONCAT(name,"(",grade,")") AS new_name FROM stu ORDERBY grade; +--------------+ | new_name | +--------------+ | lisi(66) | | wangwu(77) | | zhaoliu(77) | | zhangsan(99) | +--------------+
执行算数运算
支持 +,-,*,/
1 2 3 4 5 6 7 8 9 10 11 12
SELECT* , grade*0.1AS new_grade FROM stu ORDERBY grade; +----+----------+-----+-------+---------------------+-----------+ | id | name | sex | grade | birthday | new_grade | +----+----------+-----+-------+---------------------+-----------+ |2| lisi | boy |66|2019-09-0419:22:05|6.6| |3| wangwu | boy |77|2019-09-0319:22:52|7.7| |4| zhaoliu | boy |77|2019-09-1019:28:22|7.7| |1| zhangsan | boy |99|2019-09-1219:21:31|9.9| +----+----------+-----+-------+---------------------+-----------+ 4rowsinset (0.00 sec)
mysql>SELECT*FROM stu WHERE SOUNDEX(name) = SOUNDEX("enn"); +----+------+-----+-------+---------------------+ | id | name | sex | grade | birthday | +----+------+-----+-------+---------------------+ |5| en | boy |33|2019-09-2920:41:41| +----+------+-----+-------+---------------------+
时间日期处理函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
-- SQL Server SELECT*FROM stu WHERE DATEPART(yy, birthday) =2012;
-- Access SELECT*FROM stu WHERE DATEPART("yyyy", birthday) =2012;
-- PostgreSQL SELECT*FROM stu WHERE DATE_PART('year', birthday) =2012;
-- Oracle SELECT*FROM stu WHERE to_number(to_char(birthday,'yyyy')) =2012;
-- MySQL MairiaDB SELECT*FROM stu WHEREYEAR(birthday) =2012;
-- SQLite SELECT*FROM stu WHERE strftime('%Y', birthday) =2102;
数值处理函数
函数
说明
ABS()
绝对值
COS(),SIN(),TAN()
余弦,正弦,正切
PI()
圆周率
EXP()
指数
SQRT()
平方根
聚集函数
SQL聚集函数
函数
说明
AVG()
返回某列平均值
COUNT()
返回某列行数
MAX() MIN()
返回某列最大最小值
SUM()
返回某列之和
COUNT(*)对表中所有行计数,不管是否包含空值
COUNT(列)对特定列中具有特定值的行计数,忽略空值
可以使用DISTINCT聚集不同的值(Access不支持)
1 2 3 4 5 6 7 8 9 10 11 12 13
SELECT COUNT(*) AS count_num, AVG(DISTINCT grade) AS avg_grade, MAX(grade) AS max_grade, MIN(grade) AS min_grade, SUM(grade) AS sun_grade FROM stu; +-----------+-----------+-----------+-----------+-----------+ | count_num | avg_grade | max_grade | min_grade | sun_grade | +-----------+-----------+-----------+-----------+-----------+ |6|74.2000|99|33|448| +-----------+-----------+-----------+-----------+-----------+ 1rowinset (0.00 sec)
分组数据 GROUP BY
1 2 3 4 5 6 7 8 9 10 11
select grade, count(*) AS nums from stu groupby grade; +-------+------+ | grade | nums | +-------+------+ |99|1| |66|1| |77|2| |33|1| |96|1| +-------+------+ 5rowsinset (0.00 sec)
GROUP BY 子句可以包含任意数目的列,因此可以对分组进行嵌套,进行更细致的分组
如果嵌套了分组,数据将在最后指定的分组上进行汇总。(所有列一起计算)
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式(不能是聚集函数),如果在SELECT中使用表达式,则必须在GROUP子句中指定相同的表达式,不能使用别名.
大多数DBMS不允许GROUP BY 带有长度可变的数据类型
除聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY中给出
如果分组列中包含一个NULL值,则NULL作为一个分组返回,多个NULL作为一个分组。
GROUP BY应该出现在WHERE之前,ORDER BY 之前。
过滤分组 HAVING
1 2 3 4 5 6 7 8 9 10
select grade, count(*) AS nums from stu groupby grade having grade >60; +-------+------+ | grade | nums | +-------+------+ |99|1| |66|1| |77|2| |96|1| +-------+------+ 4rowsinset (0.10 sec)
HAVING 与 WHERE 的区别: WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤,WHERE排除的行不包括在分组中,这可能会改变计算值,从而影响HAVING中基于这些值过滤掉的分组。 如果不指定GROUP BY 大多数DBMS会同等对待他们,不过,使用HAVING时应该结合GROUP BY 而WHERE应该用于标准的行级过滤。
分组和排序
GROUP BY 与 ORDER BY 经常完成相同的工作,但他们非常不同,ORDER BY 是对产生的输出进行排序,而GROUP BY 是对行进行排序,但输出的可能不是分组的顺序,所以在使用GROUP BY时,也应该给出ORDER BY 子句。
1 2 3 4 5 6 7 8 9 10 11 12
--- 除ACCESS外,大部分DBMS支持用别名排序 select grade, count(*) AS nums from stu groupby grade having grade >60 orderbycount(*); +-------+------+ | grade | nums | +-------+------+ |66|1| |96|1| |99|1| |77|2| +-------+------+
子查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14
select*from songs where singer in -> (select id from singer where name = "张靓颖"); +-----------+---------------------+--------------------------------------------------------+--------+ | id | name | link | singer | +-----------+---------------------+--------------------------------------------------------+--------+ |169794| 天下无双 | http://music.163.com/song/media/outer/url?id=169794|10561| |327089| 画心 | http://music.163.com/song/media/outer/url?id=327089|10561| |327163| 我们说好的 | http://music.163.com/song/media/outer/url?id=327163|10561| |327225| 如果爱下去 | http://music.163.com/song/media/outer/url?id=327225|10561| |5233037| 另一个天堂 | http://music.163.com/song/media/outer/url?id=5233037|10561| |31877130| 饿狼传说 (Live) | http://music.163.com/song/media/outer/url?id=31877130|10561| |431853688| 我的梦 (Live) | http://music.163.com/song/media/outer/url?id=431853688|10561| +-----------+---------------------+--------------------------------------------------------+--------+ 7rowsinset (0.25 sec)
子查询可以嵌套,但出于效能考虑,不应该嵌套过多。
作为计算字段使用子查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
mysql>SELECT name, -> (SELECTCOUNT(*) FROM songs WHERE songs.singer = singer.id) AS nums ->FROM singer WHERE type IN -> (SELECT id FROM singer_type WHERE type = "华语男歌手") ->ORDERBY name -> LIMIT 5; +-----------------+------+ | name | nums | +-----------------+------+ | “阿兰姐” |50| |023 GC |0| | A3 |32| | abduwali tohti |8| | ABSDJHONG |22| +-----------------+------+ 5rowsinset (0.69 sec)
mysql>SELECT id, name from singer where name="阿沁" ->UNION ->SELECT name, id FROM singer WHERE name="林俊呈"; +-----------+----------+ | id | name | +-----------+----------+ |1872| 阿沁 | | 林俊呈 |30107224| +-----------+----------+ 2rowsinset (0.07 sec)
CREATEVIEW song_singer AS SELECT songs.name AS song_name, songs.link, singer.name AS singer_name, singer_type.type FROM songs,singer,singer_type WHERE songs.singer = singer.id AND singer.type = singer_type.id;
创建视图时可以使用格式化语句或计算字段
使用视图
SELECT语句中的所有约束条件在视图中都适用。
1
mysql>SELECT*FROM song_singer WHERE singer_name = "薛之谦";