MySql 03 笔记

MySql 03

---

今天内容：

(1)select查询语句

---

1.select查询语句(重中之重)

1.1 字段的排序

 select [distinct] 字段名1 [as 别名1], 字段名2 [as 别名2], ... from 表名
   [where 查询条件]
   [order by 字段名1 asc或desc, 字段名2 asc或desc, ...]; 

  如：
   -- 查询员工表中所有薪水超过1000元的名字和薪水
   select first_name, salary from s_emp
   where salary > 1000;
   -- 查询员工表中所有薪水超过1000元的名字和薪水，并按照薪水进行升序排序
   select first_name, salary from s_emp
   where salary > 1000
   order by salary asc;
   -- asc关键字表示升序排序，可以省略，默认就是升序排列
   select first_name, salary from s_emp
   where salary > 1000
   order by salary;
   -- desc关键字可以实现降序排列
   select first_name, salary from s_emp
   where salary > 1000
   order by salary desc;

  -- 查询员工表中所有部门的所有员工的薪水，要求按照部门进行升序，按照薪水升序
   select first_name, dept_id, salary from s_emp
   order by dept_id asc, salary asc; 
   -- 查询员工表中所有部门的所有员工的薪水，要求按照部门进行升序，按照薪水降序
   select first_name, dept_id, salary from s_emp
   order by dept_id asc, salary desc; 
   -- 查询员工表中所有部门的所有员工的薪水，要求按照部门进行降序，按照薪水降序
   select first_name, dept_id, salary from s_emp
   order by dept_id desc, salary desc; 
   -- 查询员工表中所有部门的所有员工的薪水，要求按照部门进行降序，按照薪水升序
   select first_name, dept_id, salary from s_emp
   order by dept_id desc, salary asc; 

   -- 实现三个字段的排序，按照部门编号降序，薪水降序，名字降序
   select dept_id, salary, first_name from s_emp
   order by dept_id desc, salary desc, first_name desc;

1.2 常用的字符串函数

如：
   -- 使用查询实现获取字符串'hello世界'的长度，获取字符串的字节个数，汉字占3个
   select length('hello世界') 获取字符串长度;
   -- 使用查询实现字符串'hello世界'的字符长度，获取字符的个数，汉字是一个字符
   select char_length('hello世界') 获取字符长度;
   -- 使用查询实现将字符串'hello'转换为大写
   select upper('hello') 转为大写;
   -- 使用查询实现将字符串'HELLO'转换为小写
   select lower('HELLO') 转为小写;
   -- 使用查询实现将字符串'helloworld'中的world获取出来
   -- 默认下标从1开始，下面的sql是表示从下标6开始获取5个字符
   select substr('helloworld', 6, 5) 获取子串;
   -- 使用查询实现字符串的拼接
   select concat('hello', 'world') 拼接字符串;

   -- 使用查询实现字符串内容的替换
   -- 将'e'替换为'E'
   select replace('hello', 'e', 'E') 替换字符串;
   -- 当出现重复字符时，则全部进行替换
   select replace('hehe', 'e', 'E') 替换字符串;
   -- 使用查询实现指定字符串出现的首位置
   -- 表示查找字符串'hello'中'e'第一次出现的索引位置，下标默认从1开始
   select instr('hello', 'e') 查找第一次出现位置;
   -- 使用查询实现去除字符串两端的空白字符
   select trim('     hello     ') 去除两端空白字符;
   select length(trim('     hello     ')) 去除两端空白字符后的长度;

1.3 常用的数值函数

如：
   -- 使用查询将数值19.3584进行四舍五入保留3位小数
   select round(19.3584, 3) 保留3位小数;            -- 19.358
   -- 使用查询将数值19.3584进行四舍五入保留2位小数
   select round(19.3584, 2) 保留2位小数;            -- 19.36
   -- 使用查询将数值19.3584进行四舍五入保留1位小数
   select round(19.3584, 1) 保留1位小数;            -- 19.4
   -- 使用查询将数值19.3584进行四舍五入保留0位小数
   select round(19.3584, 0) 保留0位小数;            -- 19
   -- 使用查询将数值19.3584进行四舍五入保留-1位小数，别名有-号要加""
   select round(19.3584, -1) "保留-1位小数";        -- 20
   -- 使用查询将数值19.3584进行四舍五入保留-2位小数
   select round(19.3584, -2) "保留-2位小数";        -- 0

   -- 使用查询将数值19.3584进行截取保留3位小数
   select truncate(19.3584, 3) 保留3位小数;            -- 19.358
   -- 使用查询将数值19.3584进行截取保留2位小数
   select truncate(19.3584, 2) 保留2位小数;            -- 19.35
   -- 使用查询将数值19.3584进行截取保留1位小数
   select truncate(19.3584, 1) 保留1位小数;            -- 19.3
   -- 使用查询将数值19.3584进行截取保留0位小数
   select truncate(19.3584, 0) 保留0位小数;            -- 19
   -- 使用查询将数值19.3584进行截取保留-1位小数，别名有-号要加""
   select truncate(19.3584, -1) "保留-1位小数";        -- 10
   -- 使用查询将数值19.3584进行截取保留-2位小数
   select truncate(19.3584, -2) "保留-2位小数";        -- 0

1.4 常用的日期函数

如：
   -- 使用查询来获取当前系统日期
   select curdate() 当前系统日期;   
   -- 使用查询来获取当前系统时间
   select curtime() 当前系统时间;
   -- 使用查询来获取日期中的月份
   select month(curdate()) 获取当前日期中的月份;

   -- 使用查询来获取当前系统日期的昨天
   select curdate()-1 昨天;
   -- 使用查询来获取当前系统日期的明天
   select curdate()+1 明天;

练习：
   -- 向员工表中插入员工信息27，'云峰', curdate(), 200
   insert into s_emp (id, first_name, start_date, salary) values(27, '云峰', curdate(), 200);
   insert into s_emp (id, first_name, start_date, salary) values(28, '晓奎', curdate()+2, 300);
   insert into s_emp (id, first_name, start_date, salary) values(29, '宇鉴', '20191111', 300);
   insert into s_emp (id, first_name, start_date, salary) values(30, '宇鉴', 
'2019-12-12', 3);
   -- 查询员工表中所有员工的名字和入职日期
   select first_name, start_date from s_emp;

1.5 常用的聚合(多行、分组)函数

单行函数 - 主要指将一条数据交给该函数处理之后的结果还是一条数据。
   多行函数 - 主要指将多条数据交给该函数处理之后的结果是一条数据。

   sum() - 主要用于实现总和的计算，如：计算总分。
   avg() - 主要用于实现平均值的计算，如：平均分。
   max() - 主要用于查找最大值，如：最高分。
   min() - 主要用于查找最小值，如：最低分。
   count() - 主要用于统计个数，通常使用方式为：count(*)，如：学生人数 

如：
   -- 查询员工表中所有员工的总薪水
   select sum(salary) 总薪水 from s_emp;
   -- 查询员工表中所有员工的平均薪水
   select avg(salary) 平均薪水 from s_emp;
   select round(avg(salary), 2) 平均薪水 from s_emp;
   -- 查询员工表中的最高薪
   select max(salary) 最高薪水 from s_emp;
   -- 查询员工表中的最低薪，不考虑空值
   select min(salary) 最低薪水 from s_emp;
   -- 查询员工表中的总人数
   select count(*) 总人数 from s_emp;

练习：
   -- 查询成绩表中所有学生的总成绩
   select sum(studentresult) 总成绩 from result;
   -- 查询成绩表中所有学生的平均成绩
   select avg(studentresult) 平均成绩 from result;
   select round(avg(studentresult),2) 平均成绩 from result;
   -- 查询成绩表中所有学生的最高分 
   select max(studentresult) 最高分 from result;
   -- 查询成绩表中所有学生的最低分
   select min(studentresult) 最低分 from result;
   -- 查询成绩表中所有记录的个数
   select count(*) 个数 from result;
   -- 查询学生表中岁数最大的学生姓名和出生日期
   -- date类型也可以使用min函数计算最小值
   select min(borndate) 岁数最大 from student;
   -- 查询学生表中岁数最小的学生姓名和出生日期
   select max(borndate) 岁数最小 from student;

1.6 分组查询的实现

select [distinct] 字段名1 [as 别名1], 字段名2 [as 别名2], ... from 表名
   [where 查询条件]
   [group by 字段名]
   [order by 字段名1 asc或desc]; 

如：
   -- 查询学生表中每个年级的学生人数，每...就表示按照...分组
   select gradeid, count(*) from student
   group by gradeid;
   -- 查询学生表中每种性别的学生人数
   select sex, count(*) from student
   group by sex;
   -- 查询学生表中每种性别的学生人数并按照人数进行降序排列
   select sex, count(*) from student
   group by sex
   order by count(*) desc;
   -- 注意：下面的代码虽然没有报错，但与实际情况不符，多个学生应该有多个学号
   select studentno, sex, count(*) from student
   group by sex
   order by count(*) desc;

案例讲解：
   -- 查询成绩表中每门课程的平均分
   select subjectid, avg(studentresult) from result
   group by subjectid;
   -- 查询成绩表中每门课程的平均分,并按照平均分进行降序排列
   select subjectid 课程编号, avg(studentresult) 平均分 from result
   group by subjectid
   order by 平均分 desc;
   -- 查询每个年级中每种性别的学生人数
   select gradeid 年级编号, sex 性别, count(*) 学生人数 from student
   group by gradeid, sex;
   -- 先按照年级升序，在按照人数降序
   select gradeid 年级编号, sex 性别, count(*) 学生人数 from student
   group by gradeid, sex
   order by gradeid asc, count(*) desc;

1.7 分组后的过滤

 select [distinct] 字段名1 [as 别名1], 字段名2 [as 别名2], ... from 表名
   [where 查询条件]
   [group by 字段名]
   [having 条件]
   [order by 字段名1 asc或desc]; 

如：
   -- 查询总人数超过18人的所有年级
   -- 也就是先计算每个年级的总人数，再按照18进行过滤
   -- 下面的写法直接报错：where子句不许使用分组函数，因为此时还没有分组
   select gradeid, count(*) from student
   where count(*) > 18
   group by gradeid;
   -- 正确的写法
   select gradeid, count(*) from student
   group by gradeid
   having count(*) > 18;

执行流程：
   from子句 => where子句 => group by子句 => having子句 => select子句 
=> order by子句

练习：
   -- 查询每个年级的总课时，并升序排列 （subject表）
   select gradeid, sum(classhour) from subject
   group by gradeid
   order by sum(classhour) asc;
 
   -- 查询每个学员的平均分（result表）
   select studentno, avg(studentresult) from result
   group by studentno;

   -- 查询每门课程的平均分，并降序排列（result表）
   select subjectid, avg(studentresult) from result
   group by subjectid
   order by avg(studentresult) desc;

   -- 查询每个学生的总分，并降序排列（result表）
   select studentno, sum(studentresult) from result
   group by studentno
   order by sum(studentresult) desc;

1.8 子查询

子查询就是指将一个查询语句的结果作为条件进行下一次查询的机制，也叫作多次/多重查询，也就是在查询语句中嵌套查询语句。

如：
   -- 查询学生表中比'崔今生'小的学生姓名和出生日期
   -- 第一步：先查询'崔今生'的生日信息   '1990-01-05'
   select borndate from student
   where studentname = '崔今生';
   -- 第二步：根据'崔今生'的生日信息来查询比该生日信息大的学生信息  23个
   select studentname, borndate from student
   where borndate > '1990-01-05'; 

   -- 实现真正的子查询
   select studentname, borndate from student
   where borndate > ( select borndate from student
   	where studentname = '崔今生'); 

练习：
   -- 查询学生表中与'崔今生'在同一个年级的学生姓名和年级编号
   -- 第一步：查询学生表中'崔今生'所在的年级编号  1
   select gradeid from student
   where studentname = '崔今生';
   -- 第二步：根据查询到的年级编号再查同一个年级的学生姓名和年级编号
   select studentname, gradeid from student
   where gradeid = 1;
   -- 合并起来
   select studentname, gradeid from student
   where gradeid = ( select gradeid from student
       where studentname = '崔今生');

案例讲解：
   -- 查询'JavaSE'课程中考了100分的学生名字 
   -- 第一步：根据课程名称'JavaSE'来查询对应的课程编号   1
   select subjectid from subject
   where subjectname = 'JavaSE';

   -- 第二步：根据课程编号和100分的成绩来查询对应的学生学号  S1101019
   select studentno from result
   where subjectid = 1 and studentresult = 100;

   -- 第三步：根据学生的学号来查询对应的学生名字
   select studentname from student
   where studentno = 'S1101019';

   -- 合并上述结果
   select studentname from student
   where studentno = ( select studentno from result
   	where subjectid = ( select subjectid from subject
   		where subjectname = 'JavaSE') and studentresult = 100);