数据裤重点 -----表操作

查询语句

---

mysql 多表关系 查询语句 索引

添加数据补充：将一个查询结果插入到另一张表中

create table student(name  char(10),gender int);
insert into student values('jsck',1);
insert into student values('rose',0);

create table student_man (name char(10),gender int);
insert into student_man select *from student where gender =1

所有 select 关键字

select distinct * from table_name
	where
	group by
	having
	order by
	limit a,b
必须存在的有：
select 
	* 可以换成任意的一个或多个字段名称
	from
	table_name
	
#注意：关键字的顺序是固定的不能随意变化

where 条件

where 后面可以是
1.比较运算符
	<,> >=  <=  =  !=
2.成员运算
	in  not in    后面是一个set
3.逻辑运算符
	and  or not
	not 要放在表达式的前面，and 和or  放在表达式的中间
4.模糊查询
	like
	%表示  任意个数的任意字符
	_表示一个任意的字符

#练习
请查询  姓 李   数学小于80 分   并且英语 大于20分  的人的数学成绩
select math,name from stu where math <80 and english >20 and name like "小%";

distinct 去重：

select distinct *from stu;
#注意仅当查询结果中所有字段全部相同时 才算重复的记录

指定阶段：

1.星号表示所有的字段
2.手动指定需要查询的字段
3.还可以是四则的运算
4.聚合函数

#请查询  英语及格的人的平均值
select name,(math+english)/2 as 平均分 from stu where english>=60;

取别名:

select name,math+english as 总分 from stu where name ='xxx'；

#as  可以省略

统计函数：

也称为聚合函数

求和  sum
平均数 avg
最大值 max
最小值 min
个数  count     # 字段名称可以用* 代替，如果字段为空会被忽略

示例；
查询所有人的平均工资
select avg(salary) from table_name;


错误示例：  查询工资最高的人的姓名
select name,max(salary) from table_name;
	#默认显示的是第一个name    首先name行有很多，而最高工资的 那个就对应一个
	#两列对应的  行数  不匹配
select name from emp where salary=max (salary);
	#报错
	#分析  where读取满足条件的一行， 但是max（）需要先拿到所有数据 才能求取最大值
	#由于读取没有完成    所以无法求出结果
**结论  where 后面不能使用聚合函数**

接下来使用 having 解决

group by：

即分组的意思，即将一个整体按照某个特征或依据来分为不同的部分

目的是分组统计方便，示例 男的几位，女的几位

基本语法：
select xxx  from  table_name group by 字段名称；

1：统计每个性别有几人
select  sex, count(*) from table_name group by sex;

2:查询每个性别有几个，并带名字
select name,sex,count(*) from table_name group bsex;

# mysql 5.6下  查询的结果是name仅显示该分组下的第一个  
# 5.7以上则直接报错 ,5.6也可以手动开启这个功能  

# 我们可以用group_concat 将分组之外的字段 做一个拼接 ,但是这是没有意义
# 如果要查询某个性别下的所有信息 直接使用where 即可  

#结论: 只有出现在了group by 后面得字段才能出现在select的后面

having:

用于过滤 ，但是与where不同的是，having使用在分组之后

案例：

1.求出平均工资大于500的部门信息
select dept,avg(salary)  from emp group by dept having avg(salary)>500
2.查询部门人数少于3的部门名称  人员名称  人员个数
select dept,group_concat(name),count(*) from emp group by dept having count(*)<3;

order:

根据某个字段排序

基本语法：
select * from table_name order by 字段名称;
#默认是升序

#改为降序
select * from table_name order by 字段名称 desc；
#升序
select *from table_name order by 字段名称 asc；

#多个字段排序   第一个相同的情况下 按照第二个
select *from table_name order by 字段名称1 desc ,字段名称2 asc;

案例：
select * from emp order by salary desc ,id desc

limit

用于限制要显示的记录数量

语法1：
select * from table_name limit 个数
语法2：
select *from table_name limit a,b  (a,起始位置【索引从0开始】，b限制个数)


示例
1.查询前三条
select *from table_name limit 3;
2从第三条开始查，查询三条记录
select *from table_name limit 2,3;

经典使用场景：
1.每一页显示的条数  a=3
2.明确当前页数   b=2
3.计算起始位置  c =（b-1)*a

#django 提供了现成的分页组件，但是它是先查询的所有数据，丢到列表中，再取出数据

子查询

将一个查询语句的结果作为另个查询语句的条件 或是数据来源

一次性查不到想要的数据时 就需要使用子查询

create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double);

insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张飞","男",24,1,"员工",3000),
(3,"关羽","男",30,1,"员工",4000),
(4,"孙权","男",25,2,"总监",6000),
(5,"周瑜","男",22,2,"员工",5000),
(6,"小乔","女",31,2,"员工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司马懿","男",24,3,"员工",6000);

create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"行政"),(3,"财务");

#需求:财务部有哪些:
#数据在两张表中 可以使用链接查询
select emp.name from emp inner join dept on dept.id = emp.dept_id where dept .name = "财务"; 

#子查询方式:
#数据在两张表中,先查询那张?
#emp? 不行  不知道部门名 查dept
#第一步 需要知道财务部的id 
select id from dept where  name = "财务";
#第二步 用查询的到的id作为判断条件查询emp
select name from emp where dept_id = 3;
# id3不能写死 是上一个查询的结果 所以直接写在后面  加上括号就变成了子查询
select name from emp where dept_id = (select id from dept where  name = "财务");

in 关键字子查询

当内层查询（括号内）结果会有多个结果时，不能使用= 必须使用in

另外子查询必须只能包含一列数据

实例；

"查询平均年龄大于25的部门名称
子查询方式:
平均年龄大于25的部门id有哪些?
先要求出每个部门的平年龄! 筛选出平均年龄大于25的部门id
拿着部门id 去查询部门表查询"

select name from dept where id in  (select dept_id from emp group by dept_id having avg(age) > 25);


"多表查询方式:
先把数据拼接到一起 在加以筛选"

select dept.name from emp inner join dept
on emp.dept_id = dept.id 
group by dept.name
having avg(age) >25;

子查询思路：
1.要分析 查到最终的数据  到底有哪些步骤
2.根据步骤写出对应的sql语句
3.把上一个步骤的sql语句丢到下一个sql语句中作为条件

exists 关键字子查询

内层查询有结果时，外层才会执行

#由于内层查询产生了结果，所以执行了外层查询dept的所有数据
select *from emp where exists (select *from emp where salary > 1000);
#查看exists 的返回结果: 只有 0 和 1

select (exists (select *from emp where salary > 10000));

#一个查询结果也是一个表 既然是表就能链接起来
#综合练习:
"查询每个部门工资最高的员工信息
先查询每个部门的最高工资
将查询结果与员工表联合起来
在加条件判断部门id相同并且 最高工资相同 则显示"

select *from emp  inner join  
(select dept_id,max(salary) m from emp group by dept_id)  t2
on emp.dept_id = t2.dept_id 
where
emp.salary = t2.m; 

多表查询

也称笛卡尔查询

select * from table,table1,table2.....

#笛卡尔查询会出现大量的错误数据，数据关联关系错误
添加过滤条件  从表外键值  等于主表的主键值

#并且会产生重复的字段信息   

案例：
select dept.name 部门，dept.id 部门编号,enp.name 姓名，emp.id 员工编号，sex from emp,dept where dept.id =dept_id;

内连接查询：（本质上就是笛卡尔查询）

语法：
select *from table1 inner join table2;
案例：
select *from emp inner join dept where dept_id =dept.id;

inner 可以省略
select *from emp join dept where dept_id =dept.id;

外连接查询-----左右外连接

左连接
需求：查询所有员工以及其所属的部门信息
select *from emp left join dept on dept_id =dept.id
#注意：外连接查询中不能使用where 关键字  必须使用on专门做表的对应关系

右连接
需求：查询所有部门以及其对应的员工信息
select *from emp right join dept on dept_id =dept.id;

全外连接查询

无论是否匹配成功 两边表的数据都要全部显示

需求： 查询所有员工与所有部门的对应关系
select *from emp full join dept on dept_id =dept.id;
#mysql不支持外连接


操作：
将左外连接和右外连接查询的结果 作为一个合并
select *from emp left join dept on dept_id =dept.id
union
select *from emp right join dept on dept_id =dept.id;

union  的用法；
select *from table_name
union
select *from table_name1;

#union将自动去除重复的记录
#union  all 不去重复
#还有 union  连接的两个查询结果  列数必须相同，一般用在多个结果结构完全一致

三表联查

create table stu(id int primary key auto_increment,name char(10));

create table tea(id int primary key auto_increment,name char(10));

create table tsr(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(t_id) references tea(id));

insert into stu values(null,"张三"),(null,"李四");
insert into tea values(null,"egon"),(null,"wer");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);



#egon老师教过哪些人?
select *from stu join tea join tsr
on stu.id = tsr.s_id and tea.id = tsr.t_id
where tea.name = "egon";


'tea表中查得egon的 id 为1
关系表中查的 123和egon有关系
学生表中查得 123的名字为abc'

总结

外连接查询 查到的是没有对应关系的记录，但是这样的数据原本就是有问题的，所以最常见的就是内连接查询

内连接表示 只显示匹配成功的记录

外连接 没有匹配成功的也要实现

多表查询的案例：

create table stu(id int primary key auto_increment,name char(10));

create table tea(id int primary key auto_increment,name char(10));

create table tsr(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(t_id) references tea(id));

insert into stu values(null,"张三"),(null,"李四");
insert into tea values(null,"egon"),(null,"wer");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);


#egon老师教过哪些学生
select tea.name,stu.name from tea join tsr join stu
on
tea.id =t_id and stu.id =s_id
where tea.name ='egon'


#子查询实现
select * from stu where id in (select s_id from tsr where t_id =(select id from tea where name ='egon'))