[大数据]SQL（二）查询

SQL（二）查询

1.预习——查询

• 单列查询

select prod_name
from prodycts;

• 多列查询

select prod_id,prod_name,prod_price
from products;

• 所有列查询

  先查询列名，再查询数据

  效率低下，尽量不用，一般自己把列都一一写出来。

select *
from products;

• 去重查询

select distinct vend_id
from products;

• 限制结果

select vend_id
from products
limit 5;#mysql返回不多于五行

select vend_id
from products
limit 5,5;# mysql返回从行5开始的5行，第一行是行0

• 完全限定表名

#可以使用完全限定的名字来引用列

#列名完全限定
select products.prod_name
from products;

#表名完全限定
select products.prod_name
from crashcourse.products;#crashcourse是数据库

2.预习——排序检索数据

• 按单列排序

select prod_name
from products
order by prod_name;# 对prod_name列以字母顺序排序数据

• 按多列排序

select prod_id,prod_proce,prod_name
from products
order by prod_price,prod_name;#根据这两列进行排序，有主次（先按照价格，后按照名称）。

• 指定排序方向

DESC 关键字只应用到直接位于其前面的列。如果想对多个列全都进行降序排序，必须在每个列后指定DESC关键字。

ASC 是升序排序，但没多大用，因为默认是升序。

#降序排序——单列
select prod_id,prod_price,prod_name
from products
order by prod_price desc;

#降序排序——多列中，一列降序
select prod_id,prod_price,prod_name
from products
order by prod_price desc,prod_name;#先按照prod_price降序排序，再对prod_name进行升序排序

#降序排序——多列中，多列降序
select prod_id,prod_price,prod_name
from products
order by prod_price desc,prod_name desc;#先按照prod_price降序排序，再对prod_name进行升序排序

• 运用order by 与 limit组合，找出某列中的最大值或最小值

#倒序排序，并返回一行值，即最大值
select prod_price
from products
order by prod_price desc
limit 1;

3.数据表介绍

• tb_student学生表

• tb_teacher老师表

• tb_college学院表

• tb_course课程表

• tb_record选课表

4.基础题

-- 查询所有学生的所有信息
-- * 的原理：先查询列名，再查询数据
-- 效率低下，尽量不用，一般自己把列都一一写出来。
SELECT 
    *
FROM
    tb_student;
    
-- 查询学生的学号、姓名和籍贯(投影)
SELECT 
    stu_id,
    stu_name, 
    stu_addr
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_addr = '四川成都' 
    AND stu_sex = 0;
    
-- 查询籍贯为“四川成都”或者性别是 女的学生
SELECT 
    stu_name, 
    stu_birth
FROM
    tb_student
WHERE
    stu_addr = '四川成都' OR stu_sex = 0;
    
-- 查询所有80后学生的姓名、性别和出生日期(筛选)
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 mod 2<> 0;

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

-- 查询名字叫“杨过”的学生的姓名和性别
select stu_name as 姓名,
		case stu_sex
        when 1 then '男' else '女' end as 性别
from tb_student
where stu_name='杨过';

-- if 是mysql里的函数
select stu_name as 姓名,
		if(stu_sex,'男','女') as 性别
from tb_student
where stu_name='杨过';

4.模糊查询

-- 查询姓“杨”的学生姓名和性别(模糊)
select stu_name as 姓名,
		if(stu_sex,'男','女') as 性别
from tb_student
where stu_name like'杨%';

-- 查询姓“杨”名字两个字的学生姓名和性别(模糊)
select stu_name as 姓名,
		if(stu_sex,'男','女') as 性别
from tb_student
where stu_name like'杨_';

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

-- 查询学号最后一位是3的学生的学号和姓名（模糊）
select stu_id,
		stu_name
from tb_student
where stu_id like '%3';

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

-- 有多个条件的时候使用union，因为用or后，优化的时候会失效
-- union会去重，union all 不会去重
select stu_name
from tb_student
where stu_name like'%不%' 
union 
select stu_name
from tb_student
where stu_name like'%嫣%';

-- 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)
select stu_name
from tb_student
where stu_name like'杨__' or stu_name like'林__'; 

-- 正则表达式
select  stu_id,
		stu_name
from tb_student
where stu_name regexp '[林杨][\\u4e00-\\u9fa5]{2}';

5.空值处理与去重

-- 查询没有录入籍贯的学生姓名(空值处理)
-- 三值逻辑：TRUE/FALSE/UNKOWN
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;

-- 查询学生的籍贯(去重)
-- trim去除空格
select distinct stu_addr
from tb_student
where trim(stu_addr) <>'' and stu_addr is not null;

-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
select stu_name,
		stu_birth
from tb_student
where stu_sex=1
order by stu_birth asc;

-- 补充：将上面的生日换算成年龄(日期函数、数值函数)
-- curdate()今天的日期，datediff（）日期差值，floor（）向下取整，ceil()向上取整
select stu_name as 姓名,
		floor(datediff(curdate(), stu_birth) / 365) as 年龄
from tb_student
where stu_sex=1
order by 年龄 desc;

6.聚合函数

-- sum/avg/count/max/min/stddev标准差/variance方差

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

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

-- 查询编号为1111的课程考试成绩的最高分(聚合函数)
select max(score)
from tb_record
where cou_id=1111;

-- 查询学号为1001的学生考试成绩的最低分(聚合函数)
select min(score) as 最低分
from tb_record
where stu_id=1001;

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

-- 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分(聚合函数)
-- 成绩有空值，不能除以成绩的个数，count(*)数行
select sum(score)/count(stu_id) as 平均分
from tb_record
where stu_id=1001;

select sum(score)/count(*) as 平均分
from tb_record
where stu_id=1001;

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

7.分组操作group by…having

#分组前筛选用where，分组后的筛选用having

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

-- 查询每个学院学生人数(分组和聚合函数)
select 
	col_id as 学院编号,
	count(*) as 人数
from tb_student
group by col_id
with rollup;#总和

-- 查询每个学院男女学生人数(分组和聚合函数)
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),1) as 平均成绩
	from tb_record
    group by stu_id;

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

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

8.子查询、嵌套查询

#查询年龄最大的学生的姓名(子查询/嵌套查询)
#即在条件里嵌套了另外一个查询语句
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);

• 常用的查询结构

  

9.连表/连接/联结查询

9.1两张表

#查询学生的姓名、生日和所在学院名称
#方一
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 as t1,tb_college as t2
where t1.col_id=t2.col_id;

#方二，内连接
select 
		stu_name,
        stu_birth,
        col_name
from tb_tudent 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;

9.2多张表

#查询学生姓名、课程名称以及成绩(连接查询/联结查询)
#方一
select 
	stu_name,
    cou_name,
    score
from tb_student,tb_course,tb_record
where tb_student.stu_id=tb_record.stu_id 
		and tb_record.cou_id=tb_course.cou_id
        and score is not null; 
        

#方二
select 
	stu_name,
    cou_name,
    score
from tb_student 
				inner join tb_record on tb_student.stu_id=tb_record.stu_id
                inner join tb_course on tb_record.cou_id=tb_course.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; 

9.3子查询与连接查询

-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
-- 每一个衍生表都要有自己的别名
select stu_name,
		avg_score
from tb_student natural join 
							(select stu_id,
							round(avg(score),1) as avg_score
                            from tb_record
                            group by stu_id) as tmp;#衍生表


select stu_name,
		avg(score)
from tb_student natural join tb_record
where score is not null
group by stu_id;

10.分页查询

limit是mysql的方言，其他的数据库不一样

#上面的查询结果取前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 cou_id asc,score desc#如果是内连接时，需要使用完全限定名
limit 5;

#上面的查询结果取第6-10条数据(分页查询)
#方一
select 
	stu_name,
    cou_name,
    score
from tb_student
				natural join tb_record
                natural join tb_course
where score is not null
order by cou_id asc,score desc
limit 5,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 cou_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 cou_id asc,score desc
limit 5
offset 10;

11.外连接

外连接：左外连接 / 右外连接 / 全外连接（mysql不支持全外连接）

全外 = 左外 union 右外：可以得到所有表的数据

左外连接：可以得到所有左表的数据

右外连接：可以得到所有右表的数据

-- 查询学生的姓名和选课的数量(其中有三个学生没有成绩，查不了，所以这不是全部学生的数据)
select stu_name,
		total
from tb_student natural join (select stu_id,
									 count(*) as total
								from tb_record
                                group by stu_id) as tmp;
                                
#查询每个学生的姓名和选课数量(左外连接和子查询)
#左外连接
select stu_name as 姓名,
		coalesce(total,0) as 选课数量  # coalesce（）返回参数里第一个不是空值的值
from tb_student as t1
	left join (select stu_id,count(*) as total
				from tb_record
                group by stu_id) as t2
	on t1.stu_id=t2.stu_id;

#右外连接
select stu_name,
		total
from (select stu_id,
			 count(*) as total
	  from tb_record
	  group by stu_id) as t1
right join tb_student as t2 on t1.stu_id=t2.stu_id;

#全外 = 左外 union 右外