mysql重点，表查询操作和多表查询

表单查询

1. 完整的查询语句语法

select distinct(* or 字段名 or 四则运算 )from 表名
where 条件
group by 条件
having 条件
order by 排序
limit 控制从哪里开始显示以及显示几条

distinct表示的是去除查询结果的重复数据，查询结果必须是完全相同的。

以上是书写顺序，必须按照这个顺序来书写sql语句

但是并不代表执行顺序

2.简单查询

准备数据
create table stu(id int primary key auto_increment,name char(10),math float,english float);
insert into stu values(null,"赵云",90,30);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"大乔",10,70);
insert into stu values(null,"李清照",100,100);
insert into stu values(null,"铁拐李",20,55);
insert into stu values(null,"小李子",20,55);


查看所有数据
select *from stu;
查看 英语成绩
select name,english from stu;
查看所有人的数学成绩 并去除项姓名相同的数据
select distinct name,math from stu;
统计每个人的总分
select name,english+math 总分 from stu;
为每个为的英语加10分显示
select name,english+10 总分 from stu;


调整显示的格式:
需要 在字段的数据前加上字段名: 
name:赵云   english:90  math:30

如果是python怎么实现?   字符串拼接
mysql中的字符串拼接函数  concat()

select concat("姓名:",name),concat("英语:",english),concat("数学:",math) from stu;

取别名时  可以省略 as
select concat("姓名:",name) name,concat("英语:",english) english,concat("数学:",math) math from stu;

concat_ws()  类似python的join



需求 如果 总分小于150  在名字后面加上shit 大于等于 加上nice
如果 那就意味有逻辑判断 (了解)
select 
(case 
when english + math > 120 then
concat("name"," nice")
when english + math <= 130 then
concat("name"," shit")
end),
english,math from stu;

select if(english+math > 120,concat(name,"nice"),concat(name,"shit")) from stu;

where关键字

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 "小%"; 

指定字段

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


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

取别名

select name,math+english as 总分 from stu where name='赵云'；
as 可以省略

统计函数

也称之为聚合函数

将一堆数据经过计算得出一个结果

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

#可以用在字段的位置，或是分组的后面
例如：查询所有人的平均工资
select avg(salary) from emp

错误案例：查询工资最高的人的姓名
select name,max(salary) from emp;
	#默认显示第一个name，因为name有很多行，而max(salary)只有一行，两列的函数不匹配
select name from emp where salary=max(salary);
	#报错，原因：伪代码
	#结论：where后面不能使用聚合函数，因为此时读取数据没有完成，么有数据不能使用聚合函数

group by

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

分组是为了统计

语法:
select xxx from table_name group by 字段名称;

需求:统计每个性别有几个人 
select sex,count(*) from emp group by sex;

需求: 查询每个性别有几个 并且显示名字
select name,sex,count(*) from emp group by sex;

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

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

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

having

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

案例：

#求出平均工资大于500的部门信息
select dept,avg(salary) from emp group by dept having avg(salary)>5000;

#查询 部门人数小于3的部门名称 人员名称 人员个数
select dept，group_concat(name),count(*) from emp group by dept having count(*)<3;
#group_contant表示相同的name在同一行显示

order

根据某个字段排序

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

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

# 多个字段  第一个相同在按照第二个    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 起始位置,个数;


# 查询前三条 
select * from  emp limit 3;

# 从第三条开始 查询3条   3-5
select * from  emp limit 2,3;


#  注意:起始位置 从0开始

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


select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;


# django 提供了现成的分页组件  但是它是先查询所有数据 丢到列表中 再取出数据   这样如果数据量太大可能会有问题 

子查询

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

​ 当我们一次性查不到想要数据时就需要使用子查询

in 关键字子查询

​ 当内层查询 (括号内的) 结果会有多个结果时, 不能使用 = 必须是in ,另外子查询必须只能包含一列数据

​ 需求: 指定一个部门名称,获取改部门下的所有员工信息

1.查询出 平均年龄 大于25的部门编号

select  dept_id from emp  group by  dept_id  having avg(age) > 25;



2.再根据编号查询部门的名称  

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

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

exists 关键字子查询

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

 案例:
 select* from dept where exists (select * from dept where id = 1);
 # 由于内层查询产生了结果 所以 执行了外层查询dept的所有数据 

多表查询

笛卡尔积查询

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

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

# 并且会产生重复的字段信息  例如员工里的 部门编号  和 部门表的id字段 
在select 后指定需要查询的字段名称 

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

内连接查询：

本质上就是笛卡尔积查询,inner可以省略

语法:
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 emp
union 
select * from emp;

# union将自动去除重复的记录  
# union all 不去重复 


select sex,name from emp 
union 
select * from dept;
# 注意  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 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"));

小结：

select [distinct] *|字段名|四则运算|函数  from table_name
	where    比较运算符   逻辑运算符    成员运算符   区间  between and  模糊匹配 like   exists   																	regexp 正则匹配
	group by    
	having   通常根聚合函数  count sum max  min avg 
	order by 
	limit a,b

子查询

多表查询

笛卡尔积 内连接 外连接

内连接最常用

通常一个需求 可以用连表 也可以 用子查询