数据库---查询

查询关键字之having过滤

---

having与where的功能是一模一样的，都是对数据进行筛选的作用。where用在分组之前的筛选，having用在分组之后的筛选。为了更好的区分，所以将where说成筛选，havng说成过滤。

案例：

# 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门
1.先获取每个部门年龄在30岁以上的员工的平均薪资
	select post,avg(salary) from emp where age>30 group by post;
2.在过滤出平均薪资大于10000的数据
	select post,avg(salary) from emp 
	where age>30 
	group by post
	having avg(salary) > 10000;
'''针对聚合函数,如果还需要在其他地方作为条件使用 可以先起别名.'''
	select post,avg(salary) as avg_salary from emp 
	where age>30 
	group by post
	having avg_salary > 10000;

查询关键字之distinct去重

---

去重的前提，数据必须是一模一样的才可以(如果数据有主键肯定无法去重)。

比如说只查询年龄，不显示其他。年龄有重复，则可以使用distinct去重。

select distinct age from emp;

查询关键字之order by排序

---

按照薪资高低排序

# 升序
	select * from emp order by salary;  # 默认是升序(从小到大)
	select * from emp order by salary asc;  # 关键字asc 可以省略 升序
# 降序
	select * from emp order by salary desc;  # 关键字desc 降序(从大到小)

设置第二排序字段：当第一个字段排序是相等的时候，我们可以按照第二个字段的排序进行排序。

# 先按照年龄升序排序 如果年龄相同 则再按照薪资降序排序
select * from emp order by age asc,salary desc;

练习题：统计各部门年龄在10岁以上的员工平均工资 并且保留平均工资大于1000的部门并按照从大到小的顺序排序

select post,avg(salary) as avg_salary from emp where age > 10 group by post
      having avg_salary > 1000
      order by avg_salary desc;

查询关键字之limit分页

---

分页即限制展示条数。

1.限制只展示五条数据

select * from emp limit 5;

2.分页效果

select * from emp limit 5,5;

3.查询工资最高的人的详细信息

select * from emp order by salary desc limit 1;

当数据特别多的时候，经常使用limit来限制展示条数，节省资源，防止系统崩溃。

查询关键字之regexp正则

---

select * from emp where name regexp '^j.*(n|y)$';  # regexp 正则表达式写在''里

补充说明:我们目前所讲的是MySQL查询关键字中使用频率较高的一些，正则表达式用的比较少。

多表查询思路

多表查询的思路总共就两种.

数据准备：

create table dep(
		id int primary key auto_increment,
    name varchar(32)
  );
  create table emp(
  	id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others') default 'male',
    age int,
    dep_id int
  );
  insert into dep values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营'),(205,'安保');
  insert into emp(name,age,dep_id) values('jason',18,200),('tony',28,201),('oscar',38,201),('jerry',29,202),('kevin',39,203),('jack',48,204);

1.子查询

方法：就相当于是我们日常生活中解决问题的方式(一步步解决)。
将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件
案例：使用子查询获取jason所在的部门名称
# 1.先获取jason的部门编号
	select dep_id from emp where name='jason';
# 2.将结果加括号作为查询条件
	select name from dep where id=(select dep_id from emp where name='jason');

2.连表操作

方法：先将多张表拼接到一起 形成一张大表 然后基于单表查询获取数据
连表操作有四个关键字inner join，left join，right join和union。
# inner join		内连接
  	select * from emp inner join dep on emp.dep_id=dep.id;
    '''只连接两张表中有对应关系的数据'''
# left join			左连接
  	select * from emp left join dep on emp.dep_id=dep.id;
    '''以左表为基准 展示所有的数据 没有对应项则用NULL填充'''
# right join		右连接
  	select * from emp right join dep on emp.dep_id=dep.id;
    '''以右表为基准 展示所有的数据 没有对应项则用NULL填充'''
# union				全连接
  	select * from emp left join dep on emp.dep_id=dep.id
    union
    select * from emp right join dep on emp.dep_id=dep.id;
    '''左右两表数据全部展示 没有对应项则用NULL填充'''
    

我们学会了连表操作之后，其实就可以将N多张表拼接到一起。

思路:我们可以将两张表拼接之后的结果起别名当做一张表使用，然后再去跟另外一张表拼接。

select * from emp inner join 
(select emp.id as epd,emp.name,dep.id from emp inner join dep on emp.dep_id=dep.id) as t1
on emp.id=t1.epd;

可视化软件之Navicat

---

Navicat可以充当很多数据库软件的客户端，提供了图形化界面能够让我们更加快速的操作数据库。

下载

navicat有很多版本，并且默认都是收费使用。正版可以免费体验14天 ， 针对这种图形化软件版本越新越好。直接百度官网即可下载。

使用

内部封装了SQL语句，用户只需要鼠标点点点就可以快速操作（用鼠标点击代替了查找数据库的代码）。

连接mysql：

创建数据库：

创建表：

导入外部sql文件：

查询(自己写SQL语句)：选择数据库后，点击查询，然后新建查询，就可以自己写sql语句了。

使用navicat编写SQL，如果自动补全语句，那么关键字都会变大写。SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)。注释的方法一个是#还有一个是--。

多表查询练习题

---

1、查询所有的课程的名称以及对应的任课老师姓名

思路： 1.先明确需要几张表 course表 teacher表

​ 2.大致查找一些表中的数据情况

​ 3.既然是多表查询 那么查询思路 子查询 连表操作(复杂的SQL需要两者配合使用)

​ 4.编写完成后 使用美化功能 将SQL语句规范化

SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;

2、查询平均成绩大于八十分的同学的姓名和平均成绩

思路： 1.先明确需要用到几张表 student score
2.大致查看一下两张表里面的数据
3.先获取平均成绩大于80分的学生信息(按照student_id分组)

select score.student_id,avg(num) as avg_num from score group by score.student_id having avg_num>80;

​ 4.结果需要从两个表里面的获取 student SQL语句执行之后的虚拟表

SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY score.student_id HAVING avg_num > 80 ) AS t1 ON student.sid = t1.student_id;

3、查询没有报李平老师课的学生姓名

思路： 1.先明确需要用到几张表 四张表
2.先查询李平老师的编号

select tid from teacher where tname='李平老师'

		3.再查李平老师教授的课程编号

select cid from course where teacher_id=(select tid from teacher where tname='李平老师')

​ 4.根据课程编号 去score表中筛选出所有选了课程的学生编号

select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));

​ 5.根据学生编号去学生表中反向筛选出没有报李平老师课程的学生姓名

SELECT
	sname 
FROM
	student 
WHERE
	sid NOT IN ( SELECT DISTINCT student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) )

4、查询没有同时选修物理课程和体育课程的学生姓名(两门都选了和一门都没选的 都不要 只要选了一门)

思路： 1.先明确需要用到几张表 三张
2.先获取物理课程和体育课程的编号

select cid from course where cname in ('物理','体育');

​ 3.再去分数表中筛选出选了物理和体育的数据(包含了选了一门和两门 没有选的就已经被排除了)

select * from score where course_id in (select cid from course where cname in ('物理','体育'))

​ 4.如何剔除选了两门的数据(按照学生id分组 然后对课程计数即可)

select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by student_id HAVING count(course_id) = 1;

​ 5.根据上述学生id号筛选出学生姓名

SELECT
	sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		student_id 
	FROM
		score 
	WHERE
		course_id IN ( SELECT cid FROM course WHERE cname IN ( '物理', '体育' ) ) 
	GROUP BY
		student_id 
	HAVING
	count( course_id ) = 1 
	)

5、查询挂科超过两门(包括两门)的学生姓名和班级

思路： 1.先明确需要几张表 三张表
2.先去score表中筛选出所有不及格的数据

select * from score where num < 60;

​ 3.如何筛选每个学生挂科的门数(按照学生id分组 对学科计数即可)

select student_id from score where num < 60 group by student_id HAVING count(course_id) >= 2;

​ 4.由于最终的结果需要取自两张表 所以应该拼接

select student.sname,class.caption from class inner join student on class.cid=student.class_id;

​ 5.使用步骤3获取到的学生编号 对步骤4的表结果筛选数据

SELECT
	student.sname,
	class.caption 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );