07.mysql查询

-- 01. 查询所有学生的所有信息
-- 获取整张表所有的数据 									select * from 表名;
-- 获取所有学生的所有信息
use school;
select * from `students`;
-- 获取所有老师的所有信息
select * from `teachers`;
--------------------------------------------------------------------------------------------------------------------------
-- 02. 查询所有学生的学号、姓名和籍贯(投影和别名) - alias
-- 投影：获取指定字段对应的数据 							select 字段1,字段2, from 表名;
select stu_id,stu_name,stu_addr from students;
-- 获取所有老师的名字和职称
select tea_name,tea_title from teachers;
-- 别名 as (可以给字段或表取别名)							select 字段名/表名 as 新字段名/新表名	（as可以省）				
select stu_id as 学号,stu_name as 姓名,stu_addr as 籍贯 from students;
select stu_id 学号,stu_name 姓名,stu_addr 籍贯 from students;				
-- 03. 查询所有课程的名称及学分(投影和别名)
select `cou_name` as 课程名称, `cou_credit` as 学分 from courses;
--------------------------------------------------------------------------------------------------------------------------
-- 04. 查询所有女学生的姓名和出生日期(数据筛选)
-- 条件筛选													select 内容 from 表名 where 条件	
-- 比较运算符：	除了基础的比较运算符，                      between....and.... 在什么和什么之间	
-- 逻辑运算符： and 、or 、not
select stu_name,stu_birth from students where stu_sex=0;

-- 05. 查询籍贯为“四川成都”的女学生的姓名和出生日期(数据筛选)
select stu_name,stu_birth from students where stu_addr='四川成都' and stu_sex=0;

-- 06. 查询籍贯为“四川成都”或者性别是女的学生(数据筛选)
select * from students where stu_addr='四川成都' or stu_sex=0;

-- 07. 查询所有80后学生的姓名、性别和出生日期(数据筛选)
select stu_name,stu_birth,stu_sex from students where '1980-1-1'<= stu_birth and stu_birth<='1989-12-31';
select stu_name,stu_birth,stu_sex from students where stu_birth between '1980-1-1' and '1989-12-31';
--------------------------------------------------------------------------------------------------------------------------
-- 08. 查询学分大于2分的课程名称和学分(数据筛选)
select cou_name,cou_credit from courses where cou_credit>2;

-- 09. 查询学分是奇数的课程的名称和学分(数据筛选)
-- sql中的数学运算 + - * / % 
select cou_name,cou_credit from courses where cou_credit % 2 = 1;
-- select cou_name,cou_credit,cou_credit+1,cou_credit *10 from courses;   字段内是数字的值支持直接数学运算

-- 10. 查询选择选了1111的课程考试成绩在90分以上的学生学号(数据筛选)
select stu_id from records where cou_id = 1111 and score >= 90;

-- 11. 查询名字叫“杨过”的学生的姓名和性别
select stu_name 姓名,stu_sex 性别 from students where stu_name='杨过'; 

-- 12. 查询姓“杨”的学生姓名和性别(模糊查询)
-- 1.模糊查询  使用通配符查询   												like配合通配符查询（like替代=）
-- %：匹配任意多个任意字符
-- _: 匹配任意一个任意字符
-- 2.sql中正则表达式 regexp                                                     regexp 正则表达式
-- mysql的正则不支持\w \d \s等反斜杠开头的符号 其他的基本都支持；
-- 字段 regexp 正则表达式 -- 判断字段对应数据中是否有满足正则规则的字串

select stu_name 姓名,stu_sex 性别 from students where stu_name like '杨%';
select stu_name,stu_sex from students where stu_name regexp '^杨.+$';   -- ^要检测是否是字符串开头 否则X杨X也会被匹配

-- 13. 查询姓“杨”名字两个字的学生姓名和性别(模糊查询)
select stu_name 姓名,stu_sex 性别 from students where stu_name like '杨_';
select stu_name,stu_sex from students where stu_name regexp '^杨.$';

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

-- 15. 查询名字中有“不”字或“嫣”字的学生的姓名(模糊查询)
select stu_name 姓名 from students where stu_name like '%不%' or stu_name like '%嫣%' ;
select stu_name from students where stu_name regexp '[不嫣]';

-- 16. 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)
select stu_name 姓名 from students where stu_name like '杨__' or stu_name like '林__' ;
select stu_name from students where stu_name regexp '^[杨林]..$';
--------------------------------------------------------------------------------------------------------------------------
-- 17. 查询没有录入籍贯的学生姓名(空值处理)
-- 空值处理 判断是否是空值（null） 											is null / is not null
-- 判断是否是空串   字段=""
-- 判断是否是空格字符串  ：字段 regexp '^[ \t\n]*$'
select stu_name from students where stu_addr is null or stu_addr regexp '^[ \t\n]*$';

-- 18. 查询录入了籍贯的学生姓名(空值处理)									trim(字段) 去掉空格字符
select stu_name from students where stu_addr is not null and trim(stu_addr) != '';
--------------------------------------------------------------------------------------------------------------------------
-- 19.查询学生选课的所有日期(去重)
-- 去重 在select 后面加 distinct
select distinct sel_date from records;
select distinct stu_id,sel_date from records;      -- 如果distinct 后面有两个字段 必须两个字段同时重复才会去重

-- 20. 查询学生的籍贯(空值处理和去重)
select distinct stu_addr from students where stu_addr is not null and trim(stu_addr) != '' ;
--------------------------------------------------------------------------------------------------------------------------
-- 21. 查询男学生的姓名和生日按年龄从大到小排列(排序)
-- 排序 （默认从小到大排序）如果要从大到小 在结尾加上 desc				select 内容 from 表名 where 条件 order by 字段;    

select stu_name,stu_birth from students where stu_sex=1 order by stu_birth; -- 默认 asc
select stu_name,stu_birth from students where stu_sex=1 order by stu_birth desc;

-- 查询学生的姓名和生日，查询结果的所有男女信息分开放，性别相同的按照年龄从小到大排序
-- 多个字段信息排序仅需逗号隔开
select stu_name,stu_birth,stu_sex from students order by stu_sex ,stu_birth desc;
--------------------------------------------------------------------------------------------------------------------------
-- 22. 将上面查询中的生日换算成年龄(日期函数、数值函数)			
-- 日期函数 和 数值函数									计算时间差			timestampdiff(单位, 起始日期, 终止日期)
select stu_name,timestampdiff(year,stu_birth,now()) as 年龄 from students;
--------------------------------------------------------------------------------------------------------------------------
-- 聚合函数 将多个数据以指定方式变成一个值 
-- max min sum count avg variance stddev 
-- 直接使用聚合函数的时候，查询只能查询一个值
-- count(字段名) 查询指定字段对应的查询结果中非null数据的个数
-- count(*) 统计查询结果中数据的行数（包含null等）
-- count(任意常量) 作用等同于*
--  count(distinct 字段名) -统计指定字段内非空去重后的个数
-- 控制小数位数的数学函数
-- round(数字,N) --控制指定数字的N位小数（四舍五入）
-- floor(小数)  向小取整
-- ceil(小数)  向大取整
-- avg计算平均值的时候，如果对应的数据中有null null不参与求和也不参数统计个数
-- ifnull(字段,数据) - 判断指定字段对应的是否为null ,如果是null，返回指定值。非null是多少返回多少。
-- ifnull是mysql方言 其他数据库可能不支持
-- if(条件,值1,值2) - 判断条件是否成立，如果成立返回值1,不成立返回值2
-- case when - 标准sql判断语法
-- case when 条件 then 值1 else 值2 end
-- 分组 
-- select 内容 from 字段 group by 字段1，字段2...;
-- 注意：内容只能是分组字段和聚合内容
-- 分组筛选  也就是说 筛选条件在分组前用 where 筛选条件在分组以后用 having
-- select 内容 from 字段 where 筛选条件 group by 字段1，字段2...;
-- select 内容 from 字段 group by 字段1，字段2... having 分组后的筛选条件;

-- 23. 查询年龄最大的学生的出生日期(聚合函数)
select min(stu_birth) from students;
-- 24. 查询年龄最小的学生的出生日期(聚合函数)
select max(stu_birth) from students;
-- 25. 查询编号为1111的课程考试成绩的最高分(聚合函数)
select max(score) from records where cou_id = 1111;
-- 26. 查询学号为1001的学生考试成绩的最低分(聚合函数)
select min(score) from records where cou_id = 1001;
-- 查询女生的人数
select count(stu_sex) from students where stu_sex=0;
select count(*) from students where stu_sex=0;
-- 27. 查询学号为1001的学生考试成绩的平均分和标准差(聚合函数)
select round(avg(score),2),round(stddev(score),2) from records where stu_id = 1001;
-- 28. 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分(聚合函数)
select score from records where stu_id=1001;
-- 方法一：
select sum(score)/count(*) from records where stu_id=1001;
-- 方法二：
select avg(ifnull(score,0)) as 平均分 from records where stu_id=1001;
-- 方法三：
select avg(if(score is null,0,score)) as 平均分 from records where stu_id=1001;
-- 查询所有学生的名字和性别，性别返回男或女
select stu_name,if(stu_sex=0,'女','男') as 性别 from students;
-- 方法四：
select stu_name, (case when stu_sex=0 then '女' else '男' end) as 性别 from students;
-- ifnull函数 - MySQL方言
-- 29. 查询男女学生的人数(分组和聚合函数)
select if(stu_sex=0,'女','男'), count(*) from students group by stu_sex;
-- 30. 查询每个学院学生人数(分组和聚合函数)
select col_id,count(*) from students group by col_id;
-- 31. 查询每个学院女学生人数(分组和聚合函数)
select col_id,count(*) from students where stu_sex=0 group by col_id;
-- 32. 查询选课学生的学号和平均成绩(分组和聚合函数)
select stu_id,avg(score) from records group by stu_id;
-- 查询每个学院男女学生人数(分组和聚合函数)
select col_id,stu_sex,count(*) from students group by col_id,stu_sex; 
select col_id,sum(stu_sex) as '男',sum(if(stu_sex=0,1,0)) as '女' from students group by col_id;
select col_id,sum(stu_sex) as '男',count(*) - sum(stu_sex)  as '女' from students group by col_id;

-- 33. 查询平均成绩大于等于90分的学生的学号和平均成绩(分组和聚合函数)
select stu_id,avg(score) from records group by stu_id having avg(score)>=90;
-- 34. 查询所有课程成绩大于80分的同学的学号和平均成绩(分组和聚合函数)
select stu_id,avg(score) from records group by stu_id having min(score) >=80;
--------------------------------------------------------------------------------------------------------------------------
-- 嵌套查询(子查询) 将一个查询的结果放到另一个查询使用
-- 查询年纪最大的学生的姓名
select min(stu_birth) from students;
select stu_name from students where stu_birth=(select min(stu_birth) from students);
-- 35. 查询年龄最大的学生的姓名(嵌套查询)
select stu_name from students where stu_birth=(select min(stu_birth) from students);
-- 36. 查询选了两门以上的课程的学生姓名(嵌套查询/分组/数据筛选)
select stu_id from records group by stu_id having count(*)>2;
select stu_name from students where stu_id in (select stu_id from records group by stu_id having count(*)>2);
select stu_name from students where stu_id = any(select stu_id from records group by stu_id having count(*)>2);
--------------------------------------------------------------------------------------------------------------------------
-- 将两张或者多张表链接成一张表
-- 方法一：表1,表2    笛卡尔积现象(让第一张表的每一条记录和第二章表的每一条记录都链接一次)
-- 方法二：表1 inner join 表2 on 连表条件     -根据连表条件将两张表链接成一张表   (连表后相同的字段名有两个)
-- 方法三：表1 natural join 表2   (两张表内有相同的字段名作为连表条件来链接两张表)(连表后相同的字段名只有一个)
-- 方法四：表1 left join 表2  on 连表条件 （保证左边表的完整性右边不足的用null填充）


-- 37. 查询学生的姓名、生日和所在学院名称(连接查询)
select stu_name,stu_birth,col_name from students inner join colleges on students.col_id =colleges.col_id;
select stu_name,stu_birth,col_name from students natural join colleges;
-- 38. 查询学生姓名、课程名称以及成绩(连接查询)
select stu_name,cou_name,score from students natural join records natural join courses;

-- 查询学生姓名和学院编号和学院名称
select stu_name,colleges.col_id,col_name from students inner join colleges on students.col_id = colleges.col_id;
select stu_name,col_id,col_name from students natural join colleges;
--------------------------------------------------------------------------------------------------------------------------
-- select 内容 from 表名 limit N;      N就是取N行结果
-- select 内容 from 表名 limit M,N;    M是从第几行开始（M从0开始计算第一行是0以此类推），取N行结果
-- 查询学生的学号和对应的分数，按照分数从高到低前5名
select stu_id,score from records order by score desc limit 5;
-- 查询学生的学号和对应的分数，按照分数从高到低取第6-10名
select stu_id,score from records order by score desc limit 5,5;
-- 39. 上面的查询结果按课程和成绩排序取前5条数据(分页查询)
-- 40. 上面的查询结果按课程和成绩排序取第6-10条数据(分页查询)
-- 41. 上面的查询结果按课程和成绩排序取第11-15条数据(分页查询)
--------------------------------------------------------------------------------------------------------------------------
-- 42. 查询选课学生的姓名和平均成绩(嵌套查询和连接查询)
select stu_id,avg(score) from records group by stu_id;
select stu_name, avg_score from (select stu_id,avg(score) as avg_score from records group by stu_id) as t1 natural join students;
-- 方法二
select stu_name,avg(score) from students natural join records group by stu_id;
-- 43. 查询学生的姓名和选课的数量(嵌套查询和左外连接查询)
select stu_name,count(rec_id) from students left join records on students.stu_id=records.stu_id group by students.stu_id;

-- 44. 查询每个学生的姓名和选课数量
select stu_name,count(*) from students natural join records group by stu_id;

-- 45. 查询没有选课的学生的姓名(左外连接和数据筛选)
select stu_name from students left join records on students.stu_id=records.stu_id where rec_id is null;