今日内容概要

  • 1 > 查询关键字
  • 1.1 > 关键字之having过滤
  • 1.2 > 关键字之distinct去重
  • 1.3 > 关键字之order by排序
  • 1.4 > 关键字之limit分页
  • 1.5 > 关键字之regexp正则
  • 2 > 多表查询思路
  • 3 > 可视化软件之Navicat
  • 3.1 > 下载
  • 3.2 > 使用
  • 4 > 多表查询练习题


1 > 查询关键字

1.1 > 关键字之having过滤

   having与where的功能是一模一样的 都是对数据进行筛选。
   where用在分组之前的筛选。
   having用再分组之后的筛选。
   为了更好的区分 我们将where称之为筛选 having说成是过滤。
   演示 : 统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门

# 1.先获取每个部门年龄在30岁以上的员工平均薪资。
		select * from emp where age>30

mysql 多关键词搜索 mysql多表查询关键字_database

# 2.对于年龄大于30岁的员工根据部门分组并且展示每个部分分组的平均薪资
		'''
			针对分组之后的数据再次筛选 需要使用having而不是where
		'''
	select post,avg(salary) from emp
	 where age > 30 group by post;

mysql 多关键词搜索 mysql多表查询关键字_mysql 多关键词搜索_02

# 再过滤出平均工资大于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;

mysql 多关键词搜索 mysql多表查询关键字_数据_03


mysql 多关键词搜索 mysql多表查询关键字_数据_04

1.2 > 关键字之distinct去重

   去重的前提 数据必须是一模一样的才可以(如果数据有主键肯定无法去重)。
   演示: 将表中年龄字段去重。

select distinct age from emp;

mysql 多关键词搜索 mysql多表查询关键字_database_05

1.3 > 关键字之order by排序

   演示: 将表中数据按照薪资高低排序。（升、降序）

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

mysql 多关键词搜索 mysql多表查询关键字_mysql_06


mysql 多关键词搜索 mysql多表查询关键字_数据_07

   演示2: 先按照年龄升序排序 如果年龄相同 则再按照薪资降序排序

select * from emp order by age,salary desc;

mysql 多关键词搜索 mysql多表查询关键字_数据库_08


   演示3: 统计各部门年龄在10岁以上的员工平均工资 并且保留平均工资大于1000的部门并按照从大到小的顺序排序。

# 1.先对部门进行分组筛选各部门大于10岁的员工的平均薪资的数据
	select post,avg(salary) from emp
	 where age > 10 
	 group by post;
	# 2.再对数据进行筛选保留平均工资大于10000的部门做降序排列
	select post,avg(salary) from emp
	 where age>10
	 group by post
	 having avg(salary)>10000
	 order by avg(salary) desc;

mysql 多关键词搜索 mysql多表查询关键字_数据_09


mysql 多关键词搜索 mysql多表查询关键字_mysql_10

1.4 > 关键字之limit分页

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

   演示1: 限制只展示五条数据

select * from emp limit 5;

mysql 多关键词搜索 mysql多表查询关键字_数据库_11


   演示2: 分页效果

select * from emp limit 5,5; 
	'''
	第一个参数是从第条数据后开始展示，
	第二个参数是展示几条数据
	'''

mysql 多关键词搜索 mysql多表查询关键字_数据库_12


mysql 多关键词搜索 mysql多表查询关键字_数据库_13


   演示3: 查询工资最高的人的详细信息

select * from emp order by salary desc limit 1;
	'''
		将数据中薪水按照降序排序只展示一条数据
	'''

mysql 多关键词搜索 mysql多表查询关键字_mysql 多关键词搜索_14

1.5 > 关键字之regexp正则

   演示1: 查找名字开头是j，结尾是n或者y的姓名字段。

select * from emp where name  regexp '^j.*(n|y)$';

mysql 多关键词搜索 mysql多表查询关键字_database_15

2 > 多表查询思路

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

1.子查询
		就相当于是我们日常生活中解决问题的方式（一步步解决）
		将一条SQL语句的查询结果加括号当做另一条SQL语句的查询条件
		eg:以昨天的员工表和部门表为例 查询jason所在的部门名称
      	子查询的步骤
        	1.先查jason所在的部门编号
          	2.根据部门编号去部门表中查找部门名称

	2.连表操作
		先将多张表拼接到一起 形成一张大表 然后基于单表查询获取数据
		eg:以昨天的员工表和部门表为例 查询jason所在的部门名称
      	连表操作
        	1.先将员工表和部门表按照某个字段拼接到一起
         	2.基于单表查询

   实际演练

# 1.准备数据
		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),('kevin',39,203),('jack',48,204);

mysql 多关键词搜索 mysql多表查询关键字_database_16


mysql 多关键词搜索 mysql多表查询关键字_mysql 多关键词搜索_17


   演示1:使用子查询 获取jason所在的部门名称

# 1.先获取jason的部门编号(结果是张表)
	select dep_id from emo where name = 'jason';

mysql 多关键词搜索 mysql多表查询关键字_mysql 多关键词搜索_18

# 2.将结果加括号作为在部门表里查询部门名称条件
	select name from dep where id=(select dep_id from emp where name='jason');

mysql 多关键词搜索 mysql多表查询关键字_mysql_19

   笛卡尔积(了解知识)。

select * from emp,dep;	# 会将所有的数据全部对应一遍
	
	select * from emp,dep where emp.dep_id = dep.id;
	'''
		连表操做 效率低下
		1.一条SQL语句的查询结果 我们也可以看成是一张虚拟表
		2.如果一条SQL语句中设计到多张表的字段名称编写 建议使用表名前缀做区分
	'''

   连表操做可以使用笛卡尔积但是效率低下,
   我们可以使用下面几个关键字来完成连表操做

   内连接 – inner

select * from emp inner join dep on emp.dep_id = dep.id;

	''' 只用于连接两张表中有对应关系的数据 '''

mysql 多关键词搜索 mysql多表查询关键字_mysql 多关键词搜索_20


   左连接 – left join

select  * from emp left join dep on emp.dep_id=dep.id;

	''' 以左表为基准 展示所有的数据 右表当中没有对应项则用NULL填充 '''

mysql 多关键词搜索 mysql多表查询关键字_mysql_21

   右连接 – right join

select * from emp rigth join dep on emp.dep_id = dep.id 
	
	''' 以右表为基准 展示所有的数据 左侧没有对应项用NULL填充 '''

mysql 多关键词搜索 mysql多表查询关键字_database_22


   全连接 – 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填充'''

mysql 多关键词搜索 mysql多表查询关键字_database_23


   演示2: 使用连表操作获取jason所在的部门名称

select dep.name from emp 
	inner join dep on emp.dep_id=dep.id
	where emp.name = 'jason';

mysql 多关键词搜索 mysql多表查询关键字_mysql 多关键词搜索_24


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

   具体思路如下：

'''
	我们可以将两张表拼接之后的结果起别名当做一张表使用
	然后再去跟另外一张表拼接
'''
	select * from emp inner join 
	(select emp.id,emp.name,dep.id from emp inner join dep on emp.dep_id=dep.id) as t1
	on emp.id=t1.epd;

3 > 可视化软件之Navicat

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

3.1 > 下载

   navicat有很多版本 并且默认都是收费使用
   正版可以免费体验14天
   针对这种图形化软件 版本越新越好(不同版本图标颜色不一样 但是主题功能是一样的)

3.2 > 使用

   内部封装了SQL语句 用户只需要鼠标点点点就可以快速操作
   连接数据库 创建库和表 录入数据 操作数据
   外键 SQL文件 逆向数据库到模型 查询(自己写SQL语句)

# 使用navicat编写SQL 如果自动补全语句 那么关键字都会变大写

 SQL语句注释语法(快捷键与pycharm中的一致 ctrl+?)
  	# 
    --

4 > 多表查询练习题

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

select 
		course.cname,
		teacher.tname 
	from 
		course 
		inner join teacher on course.teacher_id = teacher.tid;

mysql 多关键词搜索 mysql多表查询关键字_mysql 多关键词搜索_25

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

# 1.先明确要使用几张表 student score
	# 2.大致查看以下表中内容
	# 3.先获取学生表中大于80分的学生信息按照学生编号分组
	# 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;

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

# 此题有两种思路 第一种是正向查询 第二种是反向查询(先查所有报了李平老师课程的学生id 之后取反即可)
	# 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 = '李平老师' ) ) )

   8、查询没有同时选修物理课程和体育课程的学生姓名

# 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 
	)

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

# 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 );