五、DQL：数据查询语言，用来查询记录（数据）

冷不防 2023-10-04 19:23 26阅读 0赞

### 单表查询 ###

>     /*
>       select  列的列表/* |函数  from  表名
>       [where 条件]  --条件
>       [group by 列名]  --分组
>       [having 条件]   --分组条件
>       [order by 列名...] --排序
>       [limit m,n] ---分页
>     */
>     # 顺序: where - group by -having-order by-limit

### 1.简单查询 ###

# 查询stu表所有的列所有行的数据
    # * 所有列, 实际开发中, 尽量不要写* 
    # 所有行, where 条件恒成立, 不写where ,默认:条件恒成立
    # 查询返回的是一个结果集(表(虚拟表))
    SELECT * FROM stu;
    #查询部分列
    # 查询所有学生的 学号,姓名
    SELECT sid, sname FROM stu;
    #给列取别名, 给表取别名
    # 列 as  别名  as可以省略
    SELECT sid AS "学号", sname 姓名 FROM stu;
    
    # 如果别名之间使用到特殊符号, 比如 空格
    # 一定要使用 双引号 引起
    # 在数据库中, 值只能使用单引号引起
    SELECT sid AS "学 号", sname "姓 名" FROM stu;
    
    #表使用别名  后期表关联查询使用  
    # 列名:  表名.列名
    # 如果列名很长 简化  表名.列名   --> 表别名.列名
    SELECT stu.sid , s.sname FROM stu s;

### 2.条件查询 ###

>     # where 条件
>     # 比较运算符  =、!=、<>(不等于)、<、<=、>、>=
>     # 逻辑运算符  and(与)   or(或)  not(非)
>     # BETWEEN…AND   大于等于 值  并且小于等于值
>     # in(值1, 值2,...) 
>     # not in (值1, 值2,...) 
>     # is null
>     # is not null
>     /*
>       写sql步骤:
>          1. 找表(需求,数据来源那些表)
>          2. 找查询的列(数据库的列,函数列,计算列)
>          3. 找条件(where中的条件, 表连接的条件, 分组的having条件)  
>          4. 对select语法进行拼接 
>     */

SELECT * FROM stu;
    
    #1.查询性别为女，并且年龄50的记录
    SELECT * FROM stu WHERE gender ='female' AND age = '50';
    
    #2.查询学号为S_1001，或者姓名为liSi的记录
    SELECT * FROM stu WHERE sid = 'S_1001' OR sname='liSi';
    
    #3.查询学号为S_1001，S_1002，S_1003的记录
    SELECT * FROM stu WHERE sid = 'S_1001' OR sid='S_1002' OR sid='S_1003';
    
    # in(列表)  --> 值1,值2,...
    # 只要符合列表的任意一个值,
    # sql执行的顺序:1. from 表  全表
    #               2. where 子句: 从第一步中结果筛选
    #               3. select *
    SELECT * FROM stu WHERE sid IN('S_1001','S_1002','S_1003');
    
    #4.查询学号不是S_1001，S_1002，S_1003的记录
    SELECT * FROM stu WHERE sid != 'S_1001' AND sid!='S_1002' AND sid!='S_1003';
    SELECT * FROM stu WHERE NOT(sid = 'S_1001' OR sid='S_1002' OR sid='S_1003');
    SELECT * FROM stu WHERE sid NOT IN('S_1001','S_1002','S_1003');
    
    #null: 这一列没有录入值
    # null 与任何值进行计算,结果永远为null
    SELECT sid,sname,age,age+10 newAge FROM stu;
    # null与任何比较,结果都false 
    # 比较null,  = null 错误   != null 错误
    #            is null 正确  is not null 正确
    
    #5.查询年龄为null的记录
    SELECT * FROM stu WHERE age = NULL; #错误
    SELECT * FROM stu WHERE age IS NULL; #正确
    
    #查询年龄不为null的记录
    SELECT * FROM stu WHERE age IS NOT NULL; #正确
    SELECT * FROM stu WHERE age != NULL; #错误
    
    #6. 查询年龄在20到40之间的学生记录
    SELECT * FROM stu WHERE age >=20 AND age <=40;
    #BETWEEN 小值 AND 大值: 大于等于小值 并且 小于等于大值
    SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
    #7.查询性别非男的学生记录
    SELECT * FROM stu WHERE gender != 'male';
    SELECT * FROM stu WHERE gender <> 'male';
    SELECT * FROM stu WHERE NOT(gender = 'male');

### 3.模糊查询 ###

#like 相似的匹配   通配符:  %: 任意多个任意字符    _: 一个任意字符
    #=   相等,精确匹配
    
    # 查询姓名以l开头的
    #  l%  张%   li%
    SELECT * FROM stu WHERE sname  LIKE 'l%';
    
    # 查询姓名以i结尾
    # %i 
    SELECT * FROM stu WHERE sname  LIKE '%i';
    
    # 查询姓名第二个字符是i
    # _i%
    SELECT * FROM stu WHERE sname  LIKE '_i%';
    
    
    # 查询姓名中包含i的
    # %i%
    SELECT * FROM stu WHERE sname  LIKE '%i%';
    
    # 查询姓名由5个字符组成
    # _____
    SELECT * FROM stu WHERE sname  LIKE '_____';

### 4.字段控制 ###

# 1. 去重   distinct   去掉重复的记录
    #查询学生性别
    # select  distinct  列集合 
    # distinct 只能写一个, 对select 查询的所有列组合去重
    SELECT DISTINCT gender ,  sid FROM stu;

### 5.常数列 ###

>     ## 常数列: 人为给查询添加n个指定值的列
>     # s.* s对应表的所有列
>     SELECT s.*, '学生' AS role  FROM stu s;

### 6.计算列 ###

>     ## 计算列: 有表中的列计算(+-*/ 求余)的结果
>     SELECT * FROM emp;
>     #查询员工月收入= 薪水+ 奖金
>     SELECT e.*, e.sal + e.comm AS 月收入 FROM emp e;
>     # 注意: null与任何值计算结果都是null
>     # 对null 进行转换: 0
>     #IFNULL(值1,值2) 如果值1是null, 返回值2,如果不为null,返回值1
>     SELECT e.*, e.sal + IFNULL(e.comm,0) AS 月收入 FROM emp e;

### 7.排序 ###

>     /*
>      order 列名|函数列|计算列 排序方式,列名1|函数列1|计算列1 排序方式1,....
>      排序方式: 默认升序: asc
>                降序: desc
>      Order by 后面可以接多个列, 代表的含义:
>         首先按照order by的第一个列进行主排序, 
>           如果order by的第一个列中值重复, 
>           order by的第一列的重复值按照order by的第二列排序
>     */

#1.查询所有学生记录，按年龄升序排序
    SELECT * FROM stu ORDER BY age ;
    SELECT * FROM stu ORDER BY age ASC ;
    
    #2.查询所有学生记录，按年龄降序排序
    SELECT * FROM stu ORDER BY age DESC ;
    
    #3.查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
    SELECT * FROM emp ORDER BY sal DESC, empno DESC;
    
    SELECT * FROM emp ORDER BY empno DESC, sal ASC;

### 8.聚合函数 ###

>     /*
>      count(列名|*|常数列)  求记录数, 统计有多少行记录
>      sum(列名|计算列)  求总和
>      avg(列名|计算列)  求平均
>      max(列名|计算列)  求最大值
>      min(列名|计算列)  求最小值
>     */

#聚合函数对null忽略
    #5个聚合函数都是对某列的统计, 得到结果是总结果
    SELECT COUNT(age) FROM stu;
    #4.查询emp表中记录数
    #count(列名) 选择这一列有null, 忽略, 结果不对
    # 要求选择的列一定不能包含null
    SELECT * FROM emp;
    SELECT COUNT(empno) FROM emp;
    SELECT COUNT(comm) FROM emp;
    
    #count(*) 对所有列来统计, 不会出现一行的所有列都为null
    #一行的所有列都为null 才忽略   统计的结果正确
    # 效率太低: 
    SELECT COUNT(*) FROM emp;
    SELECT e.*,1 FROM emp e;
    # count(1) 正确, 效率高  推荐的
    SELECT COUNT(1) FROM emp; 
    
    
    
    #5.查询emp表中有奖金的人数：
    SELECT COUNT(1) FROM emp WHERE comm IS NOT NULL;
    
    SELECT COUNT(comm) FROM emp;
    
    #6.查询emp表中月薪大于2500的人数
    SELECT COUNT(1) FROM emp WHERE sal > 2500;
    
    
    #7.统计月薪与佣金之和大于2500元的人数
    SELECT COUNT(1) FROM emp WHERE sal+IFNULL(comm,0) > 2500;
    
    SELECT sal+IFNULL(comm,0) FROM emp 
    #8.查询有佣金的人数，以及有领导的人数：
    SELECT COUNT(1) FROM emp WHERE comm IS NOT NULL AND mgr IS NOT NULL;
    
    
    #9.查询所有雇员月薪和  sum
    SELECT SUM(sal) FROM emp;
    
    
    
    #10.查询所有雇员月薪和，以及所有雇员奖金和
    SELECT SUM(sal) 月薪和, SUM(comm) 奖金和 FROM emp;
    
    
    #11.查询所有雇员月薪+奖金和
    SELECT SUM(sal + IFNULL(comm,0)) "月薪+奖金和"  FROM emp;
    
    ## sum()  avg() 列是数值列
    
    #12.统计所有员工平均工资
    SELECT AVG(sal) FROM emp;
    
    SELECT  SUM(sal) / COUNT(1) FROM emp;
    
    
    #13.查询最高工资和最低工资：
    # max() min() 
    SELECT MAX(sal) 最高工资, MIN(sal) 最低工资 FROM emp;

### 9.分组查询 ###

>     ## group by 列名1,列名2,...
>     ## 使用group by , select 能出现的 分组中出现列,聚合函数
>     ## 聚合函数使用分组,表示对分组之后的数据的统计
>     ## 聚合函数 没有使用分组,对整张表的统计

SELECT deptno,COUNT(1) FROM emp GROUP BY deptno;
    #14.查询每个部门的部门编号和每个部门的工资和
    SELECT deptno,SUM(sal) 工资和 FROM emp GROUP BY deptno;
    
    #15.查询每个部门的部门编号以及每个部门的人数：
    SELECT deptno,COUNT(1) 人数 FROM emp GROUP BY deptno;

### 10.where 、 having ###

>     ## where : 筛选, 对整张表的筛选
>     ## having: 筛选, 对分组之后的筛选
>     /*
>      where 与having的区别:
>         1. where对整张表的筛选,having对分组之后的筛选
>         2. where 位于group by前面, havig位于group by 后面
>         3. where 独立存在, having 必须跟着group by
>         4. where 条件使用到表中所有列, 不能使用聚合函数
>            having条件使用到分组出现列, 聚合函数, 表中其他列不能使用
>     
>     */

#16.查询每个部门的部门编号以及每个部门工资大于1500的人数
    SELECT 
      deptno,
      COUNT(1) 人数 
    FROM
      emp 
    GROUP BY deptno 
    HAVING sal > 1500;  #错误
    
    SELECT 
      deptno,
      COUNT(1) 人数 
    FROM
      emp 
    WHERE sal > 1500
    GROUP BY deptno ;
    
    
    SELECT * FROM emp WHERE sal > 1500
    
    #17.查询工资总和大于9000的部门编号以及工资和
    SELECT 
      deptno,
      SUM(sal) 
    FROM
      emp 
    WHERE SUM(sal) > 9000
    GROUP BY deptno ;  #错误
    
    SELECT 
      deptno,
      SUM(sal) 
    FROM
      emp 
    GROUP BY deptno 
    HAVING SUM(sal) > 9000; #正确

### 11.分页查询 ###

>     ## 前端页面传递数据:           
>     ## 查询第几页:         页码
>     ## 一页显示多少条记录: 页容量
>     
>     ##分页的SQL: limit (mysql)  oracle: rownum
>     ## limit 语法: limit [x,] y
>     ## x: 开始序号: 从0开始, 如果x=0, 可以省略不写
>     ## y: 往后查询几条数据
>     SELECT COUNT(1) FROM emp;
>     ## 总记录数: 14
>     ## 每一页显示5条数据:   页容量
>     ## 总页数(整数) = 总记录数 % 页容量  ==0?总记录数/页容量:(总记录数/页容量[整除])+1
>

SELECT * FROM emp;
    ## 页容量:5  页码: 1
    #查询第一页: limit x , y
    SELECT * FROM emp LIMIT 0,5;
    SELECT * FROM emp LIMIT 5;
    #查询第二页: limit x , y
    SELECT * FROM emp LIMIT 5,5;
    
    #查询第三页: limit x , y
    SELECT * FROM emp LIMIT 10,5;
    ## x = (页码-1)*页容量
    ## y = 页容量