前言
SQL,Structured Query Language,结构化查询语言
SQL语言包括:DDL、DML、DQL、DCL、TCL
DQL,Data Query Language,是一种用于查询数据库中的数据语言。与其他数据库语言(如:DML、DDL等不同),DQL主要关注的是查询过程中所设计到的数据内容,而不涉及数据的增加、删除或修改
语法
执行顺序 SELECT 子句 6 FROM 子句 1 JOIN...ON... 子句 2 WHERE 子句 3 GROUP BY 子句 4 HAVING 子句 5 ORDER BY 子句 7 LIMIT 子句 8
基础查询_SELECT…FROM…
语法:
SELECT 字段1,字段2,... FROM 表名; -- SELECT子句 用于指定查询表中的字段 -- FROM子句 用于指定数据来自哪张表
示例:
查询表person中所有字段的所有记录SELECT * FROM person;
查询表person中的某些指定字段
SELECT id,name,gender,age FROM person
注意:
- 在实际开发中,java代码若执行DQL语句,不要用
*
因为当使用SELECT *
时数据库首先要查询数据字典了解带查询表的表结构,了解全字段后才能进行查询,由此给数据库带来额外的开销,这并不划算。因为java代码仅需要编写一次,因此我们应当在SELECT后将所有字段列出
过滤_WHERE
在DQL语句中,WHERE同样用于指定过滤条件,此时仅将满足该过滤条件的记录查询出来
示例:
查看6岁人员的信息,列出:id,名字,性别,年龄SELECT id,name,gender,age FROM person WHERE age=6;
查看除6岁人员外所有人员的信息,列出:id,名字,性别,年龄
SELECT id,name,gender,age FROM person WHERE age<>6;
连接多个条件_AND、OR
AND,与,都为真时才为真
OR,或,都为假时才为假
示例:
查看表person中6岁的男性都有谁,列出:id,名字,性别,年龄SELECT id,name,gender,age FROM person WHERE age=6 AND gender='男';
查看表person中6岁的女性都有谁,列出:id,名字,性别,年龄
SELECT id,name,gender,age FROM person WHERE age=6 AND gender='女';
注意:
AND
的优先级大于OR
,若出现两者紧挨着的情况,会先进行AND
判断再进行OR
判断,为了提高OR的优先级,需要使用()
列表_IN、NOT IN
示例:
查看表person中所有6岁、12岁、24岁人员的信息:id,名字,性别,年龄SELECT id,name,gender,age FROM person WHERE age IN (6,12,24);
查看表person中所有除了6岁、12岁、24岁人员的信息:id,名字,性别,年龄
SELECT id,name,gender,age FROM person WHERE age NOT IN (6,12,24);
闭区间_(NOT) BETWEEN…AND…
BETWEEN...AND...
等效于闭区间[]
示例:
查看表person中6~24岁人员的信息:id,名字,性别,年龄SELECT id,name,gender,age FROM person WHERE age BETWEEN 6 AND 24;
查看表person中除了6~24岁人员外所有人员的信息:id,名字,性别,年龄
SELECT id,name,gender,age FROM person WHERE age NOT BETWEEN 6 AND 24;
去除重复行_DISTINCT
DISTINCT
可以将结果集按照指定字段去除重复行,且其必须紧跟在SELECT关键字
之后
示例:
查看表person中的人员年龄组成SELECT DISTINCT age FROM person;
模糊查询_LIKE
LIKE
有两个可用的通配符:
- _:表示任意一个字符。(确切数量,表示1个字符)
- %:表示任意个字符。(不确切的数量,表示0~任意个字符)
示例:
查看表person中名字中含有玲
的人员信息:id,名字,性别,年龄SELECT id,name,gender,age FROM person WHERE name LIKE '%玲%';
查看表person中姓
王
的人员信息:id,名字,性别,年龄SELECT id,name,gender,age FROM person WHERE name LIKE '王%';
查看表person中名字以
玲
结尾的人员信息:id,名字,性别,年龄SELECT id,name,gender,age FROM person WHERE name LIKE '%玲';
查看表person中名字中第二个字为
莹
的人员信息:id,名字,性别,年龄SELECT id,name,gender,age FROM person WHERE name LIKE '_莹%';
空值判断_IS NULL、IS NOT NULL
用于判断一个字段是否为NULL
- IS NULL,判断字段的值是否为NULL
- IS NOT NULL,判断字段的值是否不为NULL
注意!不可以用
=
和<>
判断NULL
值
示例:
查看表person中年龄为空人员的名字SELECT name FROM person WHERE age IS NULL;
查看表person中年龄不为空人员的名字
SELECT name FROM person WHERE age IS NOT NULL;
排序_ORDER BY
ORDER BY
子句用于对查询结果集进行排序,可以按照ORDER BY
后指定的字段值进行升序
或降序
排序
- 升序
ORDER BY 字段 [ASC]
,ASC可省,此为将当前结果集按照指定的字段值从小到大排序- 降序
ORDER BY 字段 DESC
,此为将当前结果集按照指定的字段值从大到小排序
ORDER BY可以按照多个字段排序,此时排序存在优先级:
- 首先按照ORDER BY后指定的第一个字段的值将结果集按照该字段排序方式(升序或降序)排序
- 排序后再按照第二个字段指定的值排序,仅会对第一个字段值相同的记录按照第二个字段排序
- 若有第三个字段以此类推
示例:
查看表person中人员年龄与名字,按年龄从大到小排列SELECT name,age FROM person ORDER BY age DESC;
查看表person中人员生日与名字,按生日从大到小排列
SELECT name,birth FROM person ORDER BY birth; -- 因为生日的锚定物为1970年元旦,据这个时间点越远则越大,但是相对来说年龄越小,生日越小,所以birth按照从小到大进行排列得到的是年龄从大到小的排列
分页查询_LIMIT m,n
分页查询的作用是将一个DQL语句的查询结果分段查询出来
当一条DQL语句可以查询出非常大量的数据时,我们可以分批将数据查询,避免因为数据庞大导致系统过度的资源开销
注意!分页查询是方言,并没有在SQL92标准中有语法定义,这意味着不同的数据库分页方式的SQL可能完全不同
- ORACLE中使用伪列ROWNUM完成
- MySQL和MariaDB则使用LIMIT子句完成
语法:
SELECT ... FROM ... ... ORDER BY ... LIMIT m,n; -- m:表示跳过结果集的m条记录 -- n:表示从跳过的m条记录后连续检索n条记录
在分页查询中有两个常见的参数:
- 页数:当前显示第几页的内容
- 条目数:每页显示的条目数
由此得到分页公式:
- m:(页数-1)*条目数
- n:条目数
示例:
查看表person中年龄最小的前5名的名字、年龄、性别SELECT name,age,gender FROM person ORDER BY age LIMIT 0,5;
在DQL中使用函数或表达式
在SELECT子句中使用表达式
示例:
SELECT name,salary,salary*12 FROM person;
在SELECT子句中使用函数
IFNULL函数
语法:
IFNULL(arg1, arg2)
作用:
- 当arg1的值不为NULL时,函数返回arg1的值
- 当arg2的值为NULL时,函数返回arg2的值
示例:
SELECT IFNULL(salary,0) FROM person;
在WHERE中使用表达式
示例:
查看表person中年薪高于60000的成员信息:id,名字,月薪SELECT id,name,salary FROM person WHERE IFNULL(salary,0)*12 > 60000;
在WHERE中使用函数
示例:
查看表person中哪些成员的奖金少于3000,展示其:id,名字,奖金SELECT id,name,comm FROM person WHERE IFNULL(comm,0)<3000;
别名
- 别名通常使用在
SELECT
子句和FROM
子句中- 在
SELECT
子句中我们可以为字段取别名
- 当字段为函数或表达式时,我们通常给字段添加别名,为了增加可读性
- 为
SELECT
中的子查询取别名- 在
FROM
子句中可以为表添加别名
语法:
字段名<空格>别名
示例:
SELECT name,salary sal,salary*12 'annusal' -- 无论是直接空格跟还是 单引号/双引号 括都可以,只不过 单引号/双引号 中允许名称中额外含有空格 -- 两种语法中的<空格>部分也可以替换为:<空格>AS<空格>,不过不推荐这么写 FROM person;
聚合函数
概念
聚合函数又称为多行函数、分组函数。对记录按照指定字段统计的,可以将多行记录统计出一行记录
聚合函数分类
- MIN:统计指定字段的最小值
- MAX:统计指定字段的最大值
- SUM:对指定字段求和
- AVG:对指定字段求平均值
- COUNT:统计记录数的
注意事项
MIN、MAX、SUM、AVG是对值的统计,而COUNT是对记录数的统计
聚合函数忽略NULL值,这个在AVG、COUNT上尤为明显
示例
查看表person中年龄为12~24岁成员的总人数
SELECT COUNT(*) FROM person WHERE age BETWEEN 12 AND 24;
查看表person中成员的最大年龄
SELECT MAX(age) FROM person
查看表person中成员年龄的平均值
SELECT AVG(age) FROM person;
分组_GROUP BY
GROUP BY子句可以将结果集按照指定字段值相同的记录进行分组,配合聚合函数可以实现组内统计
- 在SELECT子句中出现聚合函数时,那么不在聚合函数中的字段都要出现在GROUP BY子句中
- GROUP BY子句是配合聚合函数使用的,如果SELECT子句中没有聚合函数,通常不写GROUP BY
按单字段分组
示例:
统计表person中每种职业的人数SELECT COUNT(*),job FROM person GROUP BY job;
按多字段分组
分组规则为:各字段的值都一样的记录视作一组
示例:
统计表person中同性别同职业的人数SELECT COUNT(*),job,gender FROM person GROUP BY job,gender;
按照聚合函数的结果排序
示例:
统计表person中各职业的平均工资SELECT AVG(IFNULL(salary,0)),job FROM person GROUP BY job ORDER BY AVG(IFNULL(salary,0));
过滤(通过聚合函数)_HAVING
HAVING子句用于分组中的过滤条件
注意:
- 聚合函数不能再WHERE子句中使用
- WHERE子句中使用聚合函数错误的原因:过滤时机不对
- WHERE的过滤时机是在第一次检索表中每一条记录时进行过滤的,用于确定结果集的记录
- 聚合函数的过滤是在WHERE之后进行的:
聚合函数进行过滤的前提是先从表中获得初始结果集,然后通过限制条件对初始结果集进行过滤,得到聚合函数过滤后的结果集- HAVING子句是紧跟在GROUP BY子句之后,用于对分组进行过滤的子句
HAVING和WHERE的区别
- WHERE是在第一次检索表数据时用于添加过滤条件,确定结果集
- HAVING是在GROUP BY之后(将结果集分组之后)添加过滤条件的,用于确定分组
- 过滤时机不同,作用不同
示例:
查看表person中平均工资高于6000的职业SELECT job,salary,( SELECT AVG(IFNULL(salary,0) FROM person) ) 'avg_sal' FROM person GROUP BY job HAVING AVG(salary)>6000;
子查询_SubQuery
概念
嵌套在一个SQL语句中的DQL语句,该DQL被称为子查询
应用场景
- DQL中使用子查询
- 在SELECT子句中,将当前子查询结果作为一个字段展示
- 在WHERE子句中,将当前子查询结果作为过滤条件使用(最常用的场景)
- DML中使用子查询
- 将一个查询结果集用于增删改操作
- DDL中使用子查询
- 在创建表时可以将一个子查询结果集当作表创建
- 在创建视图时使用一个子查询结果集
子查询分类
- 单行单列子查询,该子查询的结果集只有一个值
- 多行单列子查询,该子查询结果集是多个值
- 多列子查询(单行多列子查询 | 多行多列子查询),将该子查询当作一张表使用
在DQL中使用子查询
单行单列子查询
示例:
查看表person中平均工资大于所有成员平均工资的工作SELECT AVG(salary) FROM person WHERE salary>( SELECT AVG(salary) FROM person ) GROUP BY job;
多行单列子查询
多行单列子查询可以检索出若干个值,因此作为过滤条件使用时需要:
- 判断等值,要配合:
IN
、NOT IN
使用,等于是不能同时等于好几个值的,只能等于其中之一- 判断>、<、>=、<=时,要搭配
ANY
和ALL
使用
- >ANY(列表):大于列表其中之一,判断标准:大于列表最小值即可
- >ALL(列表):大于列表所有,判断标准:大于列表最大值
- <ANY(列表):小于最大的即可
- <ALL(列表):小于最小的即可
- 示例:
- 查看表person中与赵莹、周玲同职业的成员都有谁
SELECT name,age FROM person WHERE job=( SELECT job FROM person WHERE name IN ('赵莹','周玲') );
在DML语句中使用子查询
示例:
给表person中老师
职业的成员上涨500元工资UPDATE person SET salary=salary+500 WHERE job_id=( SELECT job_id FROM person WHERE job='老师' );
在DDL语句中使用子查询
可以将一个查询结果集当作一张表创建出来
示例:
创建一张新表teacher,该表中记录了每个老师的工资情况,要求:按工资从高到低排列,展示其名字和工资CREATE TABLE teacher AS SELECT name,salary FROM person WHERE job='老师' ORDER BY salary DESC;