Skip to content

Latest commit

 

History

History
445 lines (376 loc) · 15.9 KB

第43课.SQL详解之DQL.md

File metadata and controls

445 lines (376 loc) · 15.9 KB
Raw

SQL详解之DQL

接下来，我们利用之前创建的学校选课系统数据库，为大家讲解 DQL 的应用。无论对于开发人员还是数据分析师，DQL 都是非常重要的，它关系着我们能否从关系数据库中获取我们需要的数据。建议大家把上上一节课中建库建表的 DDL 以及 上一节课中插入数据的 DML 重新执行一次，确保表和数据跟没有问题再执行下面的操作。

use `school`;

-- 查询所有学生的所有信息
-- 说明：实际工作中不建议使用 select * 的方式进行查询
select  *
  from  tb_student;

-- 查询学生的学号、姓名和籍贯
select  stu_id,
        stu_name,
        stu_addr
  from  tb_student;
  
select  stu_id as 学号,
        stu_name as 姓名,
        stu_addr as 籍贯
  from  tb_student;

-- 查询所有课程的名称及学分
select  cou_name as 课程名称,
        cou_credit as 学分
  from  tb_course;

-- 查询所有女学生的姓名和出生日期
select  stu_name,
        stu_birth
  from  tb_student
 where  stu_sex=0;

-- 查询籍贯为“四川成都”的女学生的姓名和出生日期
select  stu_name,
        stu_birth
  from  tb_student
 where  stu_sex=0 
   and  stu_addr='四川成都';

-- 查询籍贯为“四川成都”或者性别是女的学生
select  stu_name,
        stu_birth
  from  tb_student
 where  stu_sex=0 
    or  stu_addr='四川成都';

-- 查询所有80后学生的姓名、性别和出生日期
-- 方法一：
select  stu_name,
        stu_sex,
        stu_birth
  from  tb_student
 where  stu_birth >= '1980-1-1' 
   and  stu_birth <= '1989-12-31';

-- 方法二：
select  stu_name,
        stu_sex,
        stu_birth
  from  tb_student
 where  stu_birth between '1980-1-1' and '1989-12-31';

-- 查询学分大于2的课程的名称和学分
select  cou_name,
        cou_credit
  from  tb_course
 where  cou_credit > 2;

-- 查询学分是奇数的课程的名称和学分
select  cou_name,
        cou_credit
  from  tb_course
 where  cou_credit % 2 <> 0;

-- 查询选择选了1111的课程考试成绩在90分以上的学生学号
select  stu_id
  from  tb_record
 where  cou_id = 1111
   and  score > 90;
 
-- 查询姓“杨”的学生姓名和性别(模糊)
-- % 可以匹配零个或任意多个字符
select  stu_name,
        stu_sex
  from  tb_student
 where  stu_name like '杨%';

-- 查询姓“杨”名字两个字的学生姓名和性别(模糊)
-- _ 可以匹配一个字符
select  stu_name,
    stu_sex
  from  tb_student
 where  stu_name like '杨_';

-- 查询姓“杨”名字三个字的学生姓名和性别(模糊)
select  stu_name,
        stu_sex
  from  tb_student
 where  stu_name like '杨__';

-- 查询名字中有“不”字或“嫣”字的学生的姓名(模糊)
-- 方法一：
select  stu_name,
        stu_sex
  from  tb_student
 where  stu_name like '%不%'
    or  stu_name like '%嫣%';

-- 方法二：
select  stu_name,
        stu_sex
  from  tb_student
 where  stu_name like '%不%'
 union
select  stu_name,
        stu_sex
  from  tb_student
 where  stu_name like '%嫣%';

-- 查询姓“杨”或姓“林”名字三个字的学生的姓名
select  stu_name,
        stu_sex
  from  tb_student
 where  stu_name regexp '^[杨林][\\u4e00-\\u9fa5]{2}$';

-- 查询没有录入籍贯的学生姓名
select  stu_name
  from  tb_student
 where  stu_addr = ''
    or  stu_addr is null;

-- 查询录入了籍贯的学生姓名
select  stu_name
  from  tb_student
 where  stu_addr <> ''
   and  stu_addr is not null;

-- 查询学生选课的所有日期
select  distinct sel_date
  from  tb_record;

-- 查询学生的籍贯
select  distinct stu_addr
  from  tb_student
 where  stu_addr <> ''
   and  stu_addr is not null;

-- 查询学院编号为1的学生姓名、性别和生日按年龄从大到小排列
-- asc - 升序（从小到大，默认），desc - 降序（从大到小）
  select  stu_name,
          stu_sex,
          stu_birth
    from  tb_student
   where  col_id = 1
order by  stu_sex asc,
          stu_birth asc;

-- 补充：将上面的性别处理成“男”或“女”，将生日换算成年龄
  select  stu_name as 姓名,
          if(stu_sex, '男', '女') as 性别,
          floor(datediff(curdate(), stu_birth) / 365) as 年龄
    from  tb_student
   where  col_id = 1
order by  stu_sex asc,
          年龄 desc;

-- 查询年龄最大的学生的出生日期
select  min(stu_birth)
  from  tb_student;

-- 查询年龄最小的学生的出生日期
select  max(stu_birth)
  from  tb_student;

-- 查询学号为1001的学生一共选了几门课
select  count(*)
  from  tb_record
 where  stu_id = 1001;

-- 查询学号为1001的学生考试成绩的平均分
select  round(avg(score), 1) as 平均分
  from  tb_record
 where  stu_id = 1001;

-- 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分
-- 方法一：
select  sum(score) / count(*)
  from  tb_record
 where  stu_id = 1001;
 
-- 方法二：
select  avg(coalesce(score, 0))
  from  tb_record
 where  stu_id = 1001;

-- 查询学号为1001的学生考试成绩的标准差(聚合函数)
select  stddev_samp(score)
  from  tb_record
 where  stu_id = 1001;

-- 查询男女学生的人数
  select  case stu_sex when 1 then '男' else '女' end as 性别,
          count(*) as 人数
    from  tb_student
group by  stu_sex;

-- 查询每个学院学生人数
  select  col_id as 学院编号,
          count(*) as 人数
    from  tb_student
group by  col_id;

-- 查询每个学院男女学生人数
  select  col_id as 学院编号,
          case stu_sex when 1 then '男' else '女' end as 性别,
          count(*) as 人数
    from  tb_student
group by  col_id, stu_sex;

-- 查询每个学生的学号和平均成绩
  select  stu_id as 学号,
          round(avg(score), 2) as 平均成绩
    from  tb_record
group by  stu_id;

-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- 方法一：
  select  stu_id as 学号,
          round(avg(score), 2) as 平均成绩
    from  tb_record
group by  stu_id
  having  平均成绩 >= 90;

-- 方法二：
select  *
  from  (  select  stu_id as 学号,
                   round(avg(score), 2) as 平均成绩
             from  tb_record
         group by  stu_id) as t
 where  平均成绩 >= 90;

-- 查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
  select  stu_id as 学号,
          round(avg(score), 2) as 平均成绩
    from  tb_record
   where  cou_id in (1111, 2222, 3333)
group by  stu_id
  having  avg(score) >= 90;

-- 嵌套查询：把一个查询的结果作为另外一个查询的一部分来使用
select  stu_name
  from  tb_student
 where  stu_birth = (select  min(stu_birth) 
                       from  tb_student);

-- 查询选了两门以上的课程的学生姓名
select  stu_name
  from  tb_student
 where  stu_id in (  select  stu_id
                       from  tb_record
                   group by  stu_id
                     having  count(*) > 2);

-- 查询学生的姓名、生日和所在学院名称
-- 方法一：
select  stu_name,
        stu_birth,
        col_name
  from  tb_student, tb_college
 where  tb_student.col_id = tb_college.col_id;

-- 方法二：
select  stu_name,
        stu_birth,
        col_name
  from  tb_student inner join tb_college
    on  tb_student.col_id = tb_college.col_id;

-- 方法三：
select  stu_name,
    stu_birth,
        col_name
  from  tb_student natural join tb_college;

-- 查询学生姓名、课程名称以及成绩
-- 方法一：
select  stu_name,
        cou_name,
        score
  from  tb_student, tb_course, tb_record
 where  tb_student.stu_id = tb_record.stu_id
   and  tb_course.cou_id = tb_record.cou_id
   and  score is not null;

-- 方法二：
select  stu_name,
        cou_name,
        score
  from  tb_student inner join tb_record inner join tb_course
    on  tb_student.stu_id = tb_record.stu_id
   and  tb_course.cou_id = tb_record.cou_id
 where  score is not null;
 
-- 方法三：
select  stu_name,
        cou_name,
        score
  from  tb_student natural join tb_record natural join tb_course
 where  score is not null;

-- 补充：上面的查询结果取前5条数据
  select  stu_name,
          cou_name,
          score
    from  tb_student natural join tb_record natural join tb_course
   where  score is not null
order by  stu_id asc, score desc
   limit  5;

-- 补充：上面的查询结果取第6-10条数据
  select  stu_name,
          cou_name,
          score
    from  tb_student inner join tb_record inner join tb_course
      on  tb_student.stu_id = tb_record.stu_id
     and  tb_course.cou_id = tb_record.cou_id
order by  stu_id asc, score desc
   limit  5
  offset  5;

-- 补充：上面的查询结果取第11-15条数据
  select  stu_name,
          cou_name,
          score
    from  tb_student natural join tb_record natural join tb_course
   where  score is not null
order by  stu_id asc, score desc
   limit  5
  offset  10;

-- 查询选课学生的姓名和平均成绩
select  stu_name,
        avg_score
  from  tb_student t1 inner join (  select  stu_id,
                                            round(avg(score), 2) as avg_score
                                      from  tb_record
                                  group by  stu_id) t2
    on  t1.stu_id = t2.stu_id;

-- 查询学生的姓名和选课的数量
select  stu_name,
        total
  from  tb_student t1 natural join (  select  stu_id,
                                              count(*) as total
                                        from  tb_record
                                    group by  stu_id) t2;

-- 查询每个学生的姓名和选课数量(左外连接和子查询)
select  stu_name as 姓名,
        coalesce (total, 0) as 选课数量
  from  tb_student t1 left join (  select  stu_id,
                                           count(*) as total
                                     from  tb_record
                                 group by  stu_id) t2
  on  t1.stu_id = t2.stu_id;

上面的 DQL 有几个地方需要加以说明：

  1. MySQL目前的版本不支持全外连接，上面我们通过union操作，将左外连接和右外连接的结果求并集实现全外连接的效果。大家可以通过下面的图来加深对连表操作的认识。

  2. MySQL 中支持多种类型的运算符，包括：算术运算符（+、-、*、/、%）、比较运算符（=、<>、<=>、<、<=、>、>=、BETWEEN...AND...、IN、IS NULL、IS NOT NULL、LIKE、RLIKE、REGEXP）、逻辑运算符（NOT、AND、OR、XOR）和位运算符（&、|、^、~、>>、<<），我们可以在 DML 中使用这些运算符处理数据。

  3. 在查询数据时，可以在SELECT语句及其子句（如WHERE子句、ORDER BY子句、HAVING子句等）中使用函数，这些函数包括字符串函数、数值函数、时间日期函数、流程函数等，如下面的表格所示。

    常用字符串函数。

    函数 功能
    CONCAT 将多个字符串连接成一个字符串
    FORMAT 将数值格式化成字符串并指定保留几位小数
    FROM_BASE64 / TO_BASE64 BASE64解码/编码
    BIN / OCT / HEX 将数值转换成二进制/八进制/十六进制字符串
    LOCATE 在字符串中查找一个子串的位置
    LEFT / RIGHT 返回一个字符串左边/右边指定长度的字符
    LENGTH / CHAR_LENGTH 返回字符串的长度以字节/字符为单位
    LOWER / UPPER 返回字符串的小写/大写形式
    LPAD / RPAD 如果字符串的长度不足，在字符串左边/右边填充指定的字符
    LTRIM / RTRIM 去掉字符串前面/后面的空格
    ORD / CHAR 返回字符对应的编码/返回编码对应的字符
    STRCMP 比较字符串，返回-1、0、1分别表示小于、等于、大于
    SUBSTRING 返回字符串指定范围的子串

    常用数值函数。

    函数 功能
    ABS 返回一个数的绝度值
    CEILING / FLOOR 返回一个数上取整/下取整的结果
    CONV 将一个数从一种进制转换成另一种进制
    CRC32 计算循环冗余校验码
    EXP / LOG / LOG2 / LOG10 计算指数/对数
    POW 求幂
    RAND 返回[0,1)范围的随机数
    ROUND 返回一个数四舍五入后的结果
    SQRT 返回一个数的平方根
    TRUNCATE 截断一个数到指定的精度
    SIN / COS / TAN / COT / ASIN / ACOS / ATAN 三角函数

    常用时间日期函数。

    函数 功能
    CURDATE / CURTIME / NOW 获取当前日期/时间/日期和时间
    ADDDATE / SUBDATE 将两个日期表达式相加/相减并返回结果
    DATE / TIME 从字符串中获取日期/时间
    YEAR / MONTH / DAY 从日期中获取年/月/日
    HOUR / MINUTE / SECOND 从时间中获取时/分/秒
    DATEDIFF / TIMEDIFF 返回两个时间日期表达式相差多少天/小时
    MAKEDATE / MAKETIME 制造一个日期/时间

    常用流程函数。

    函数 功能
    IF 根据条件是否成立返回不同的值
    IFNULL 如果为NULL则返回指定的值否则就返回本身
    NULLIF 两个表达式相等就返回NULL否则返回第一个表达式的值

    其他常用函数。

    函数 功能
    MD5 / SHA1 / SHA2 返回字符串对应的哈希摘要
    CHARSET / COLLATION 返回字符集/校对规则
    USER / CURRENT_USER 返回当前用户
    DATABASE 返回当前数据库名
    VERSION 返回当前数据库版本
    FOUND_ROWS / ROW_COUNT 返回查询到的行数/受影响的行数
    LAST_INSERT_ID 返回最后一个自增主键的值
    UUID / UUID_SHORT 返回全局唯一标识符