Python-Core-50-Courses/第43课.SQL详解之DQL.md at master · lldhsds/Python-Core-50-Courses

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 有几个地方需要加以说明：

MySQL目前的版本不支持全外连接，上面我们通过union操作，将左外连接和右外连接的结果求并集实现全外连接的效果。大家可以通过下面的图来加深对连表操作的认识。
MySQL 中支持多种类型的运算符，包括：算术运算符（+、-、*、/、%）、比较运算符（=、<>、<=>、<、<=、>、>=、BETWEEN...AND...、IN、IS NULL、IS NOT NULL、LIKE、RLIKE、REGEXP）、逻辑运算符（NOT、AND、OR、XOR）和位运算符（&、|、^、~、>>、<<），我们可以在 DML 中使用这些运算符处理数据。

在查询数据时，可以在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`	返回全局唯一标识符

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQL详解之DQL

FilesExpand file tree

第43课.SQL详解之DQL.md

Latest commit

History

第43课.SQL详解之DQL.md

File metadata and controls

SQL详解之DQL