mysql,day05笔记

今日内容

1,多表操作的两大方法

在上班的时候我们需要的数据肯定会来自多张表(#这个数据准备也可以当作自己的练习题多打代码)
数据准备
#建表
create table dep(
id int primary key auto_increment,
name varchar(20) 
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务')
;
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

2,连接表的概念

select * from dep,emp;
# 结果是一个笛卡尔积(了解)
好奇之下去查了什么叫笛卡尔积
就是指包含两个集合中任意取出两个元素构成的组合的集合.
举例子，假设R中有元组M个,S中有元组N个,则R和S的笛卡尔积中包含的元组数量就是M*N.这个规则可以向多个关系扩展.
我们不是把数据一一对应，而是按照相同的条件做对应关系，就是说，按照一定的条件进行分类
# 在设计多张表的操作下，非常容易出现两张表字段相同的情况，这个时候我们都会习惯的在字段前面加上该字段所载的表名，来确定到底是哪张表的哪个字段，就是说给每张表做标记，不会因为多张一样的表操作的时候混乱了
select emp,name，dep，name from emp，dep where dep.id = emp.dep_id;

3,多表查询方式1:连接表

内连接;使用比较运算符（包括=、>、<、<>、>=、<=、!> 和!<）进行表间的比较操作，查询与连接条件相匹配的数据
# 关键字 inner join
select * from emp inner join dep on dep.id = emp.dep_id;
# inner join只会连接两张表都有的数据，两张表中没有对应关系的数据都会被直接删除，就是说我只要你有我也有的，但如果我俩都没有的，那么这个就不会要
左连接
left join
select * from emp left join dep on dep.id = emp.dep_id;
# 左连接就是从左边开始的所有数据，与其他数据一一对应，但如果没有相对的数据，那么就是用NULL填充
右连接
right join
select * from emp right join dep on dep.id = emp.dep_id;
# 右链接就是从右边开始的所有数据，与其他数据一一对应 ，但如果没有相对的数据，那么就是用NULL填充
全连接(了解)
 union (就是把左连接和右链接拼在一起)
    select * from emp left join dep on dep.id = emp.dep_id
    union
    selcet * from emp right join dep on dep.id = emp.dep_id;
# 总结：学习领悟了上述的方法之后，我们就可以掌握连接多张表的方法，其实就是万变不离其宗，一环套一环，先可以连接两张表，然后把其结果当成一张表去连接其他表，你也可以两张两张链接，之后再链接两张表的结果表

4,多表查询方式2:子查询

子查询其实就是我们日常生活中解决问题的最基本方法;分布操作，就是说是我我们平常解决问题的方法，一步一步解决，拆开来解决问题
在MySQL中子查询的定义;就是将一张表的查询结果当作另一条SQL语句的查询条件,# 意思就是，像超市分类，超市有关人员已经将食品分类，消费者按照有关人员的分类当作查询哪个物品的条件
比如;我们想查员工jason所在的部门名称
	1.先去员工表中查询出jason对应的部门id
	2.拿着部门id去部门表中查询部门名称 #(思路，加解题方法)
select emp.dep_id from emp where emp.name= 'jason';
select dep.name from dep where dep.id = 200;
一旦一条sql语句需要被当做条件那么只需要在该sql语句前后加上括号即可，就像皇上的圣旨，黄马褂，
select dep.name from dep	
				where dep.id = (select emp.dep_id from emp where emp.name='jason');
# 小知识点 连接查询是 通过主外键 让多个表数据对应 成一个表数据，而子查询是查到的数据 利用这个数据再查别的 如查张三的年级ID 再用这个年级ID去查对应的年级名
当子查询的条件是多个的情况虾上述的等号我们可以用in来代替，方便也节省更好看
每个部门最新入职的员工(依据昨天的员工表18条数据的) 了解
# 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
select t1.id.name，t1.hire_date,t1.post,t2.* from emp as t1 inner join
(select post,max(hire_date) as max_date from emp group by post) as t2 on t1.post = t2.post
where t1.hire_date = t2.max_date;

总结

设计到多表查询，往往可能都要连接表操作和子查询配合使用，就是说如果我们要设计多张表查询的时候，基本上都可能会使用到连接表操作和子查询的一起使用，就像吃饭肯定要有碗筷的道理一样

5,补充

group_concat
	select group_cncat(name,";",age) from emp  group by id;
concat
	select concat(name,';',sex,';',age,';',dep_id) from emp;
concat_ws #就是说当有多个字段信息连接符一致的时候，我们可以使用concat_ws节省书写量，看起来也好看，
	select concat_ws(';',name,sex,age,dep_id) from emp;
# exists(了解)
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。
# 就是说我用这个exists关键字查询，我看不到查询的记录，但我只能看到True或者False 如果是True，就是在外层查询的语句在进行，如果是False，那么就是不进行查询
以下是例子；
select * from emp
	where exists
    (select id from dep where id > 3);
select * from emp
	(select id from dep where id > 250);

6,Navicat使用

# Navicat的存在就是简便了操作数据库的方法，(鼠标点就完事了)
# 而且它也可以充当很多数据库软件的客户端
下载与安装
	https://www.cr173.com/soft/126934.html
推荐参考:
	https://www.cnblogs.com/Kathrine/p/12844846.html
    
直接点击next安装即可，无需额外的设置
使用特点；
	将很多sql语句的操作给你封装成了点就完事的操作
  但是你需要知道的是虽然你用的是点点点，但是软件内部其实是自动帮你书写了对应的sql语句发送给了服务端执行，帮你省去了写代码的sql语句的过程
# 这个软件的好处还可以帮你美化sql语句，就是你可以根据它美化慢慢学习让自己的sql语句变得更好看，自己可以修改sql语句
########################################
MySQL中的注释						
第一种                                  
	# 注释 ctrl+? shift+3
第二种
	-- 注释
#######################################

7,多表查询练习题

sql语句书写思路
# 我们在书写sql语句查询的时候不要想着一次性成功
# 而应该写一点查一点看一点再继续往下写，而且我们可以先用*号占位，如果占时想不起来先占位，后面再返回来写
# 在书写复杂的sql语句查询的时候你可以先用中文将思路写下来之后再去用sql实现
1,查询所有的课程的名称以及对应的任课老师姓名
select * from course;
select * from teacher;
 	SELECT
	teacher.tname,
	course.cname 
	FROM
	teacher
	INNER JOIN course ON teacher.tid = course.teacher_id;
2,查询平均成绩大于八十分的同学的姓名和平均成绩
1.1先明确成绩信息在哪张表
select * from score;
1.2求平均需要使用聚合函数 也就意味着需要用到分组
select score.student_id,avg(score.num) from score group by score.student_id HAVING avg(score.num) > 80;
3.根据学生id获取学生姓名
 SELECT
	student.sname,
	t1.avg_num 
	 FROM
	student
INNER JOIN ( SELECT score.student_id, avg( score.num ) AS avg_num FROM score GROUP BY score.student_id HAVING avg( score.num ) > 80 ) AS t1 ON student.sid = t1.student_id;
4,查询没有报李平老师课的学生姓名
我们可以采用逆向思想(我们不可以死脑筋可以逆向思维)
1.1先查李平教授的课程
1.2再查所有报了李平老师的学生id
1.3利用取反操作获取没有报李平老师课程的学生信息
	SELECT
	student.sname 
	FROM
	student 
	WHERE
	student.sid NOT IN (
	SELECT DISTINCT
 		score.student_id 
	FROM
 		score 
 	WHERE
		score.course_id IN ( 
 		SELECT course.cid 
 		FROM course 
 		WHERE course.teacher_id = ( 
 			SELECT teacher.tid 
 			FROM teacher 
 			WHERE teacher.tname = '李平老师' ) ) 
 );
4,查询没有同时选修物理课程和体育课程的学生姓名
(只要报了物理或者体育一门课的学生,不要两门都报和都没报)
1.先获取物理课和体育课的id号
2.查询出所有只要报了这两门课的(无论是一门 还是两门的)
3.针对学生进行分组 对报的课程进行计数  只要课程数是1的
 SELECT
	student.sname 
 FROM
 	student 
 WHERE
	student.sid IN (
	SELECT
 		score.student_id 
 	FROM
 		score 
 	WHERE
		score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) ) 
	GROUP BY
		score.student_id 
 	HAVING
 	count( score.course_id ) = 1 
);

作业

1,查询挂科超过两门(包括两门)的学生姓名和班级
select caption,sname 
from student inner join class on student.class_id = class.cid
(select * from student where sid = student_id)
(select score.student_id from scorewhere score.num < 60 as t1 group by student_id having count(student_id) >= 2);


2,查询平均年龄在25岁以上的部门名(基于昨天的表)
select pos,avg(age) from emp group by post having avg(age) > 25;