day 35 多表查询
昨日补充
用 name=''比name is null快
单表操作
分组
group by
分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
用法：
select 聚合函数，选取的字段 from 表名 group by 分组的字段；
group by:是分组的关键词
group by 必须和聚合函数一起出现
聚合函数：
max : 求最大的
min:求最小的
count: 计数，数量
sum：求和
avg：平均数
where 条件语句和group by分组语句的先后顺序：
where>group by>having(************)
例子：
#以性别为例，进行分组，统计男生和女生的人数
select count(id),sex from employee group by sex;
#对部门进行分组，求出每个部门年龄最大的那个人；
select max(age),depart_id from employee group by depart_id;
# count和sum的区别：
select depart_id,count(age) from employee group by depart_id;
select depart_id,sum(age) from employee group by depart_id;
having
表示对group by 之后的数据，进行二次筛选
# 对部门进行分组，求出部门的年龄的平均数
select depart_id,avg(age) from employee group by depart_id;
# 找到部门平均年龄大于35的部门
select depart_id,avg(age) from employee group by depart_id having avg(age)>35;
使用的先后顺序：
where>group by>having(************)
升序 降序
order by
语法：
order by 字段名 asc #升序
order by 字段名 desc #降序
如果对多个字段进行排序，
比如：
order by age desc,id asc;
表示：先对age进行降序，如果age有相同的行，则对id进行升序
select * from employee order by age desc,id desc;
使用的先后顺序：
where > group by > having > order by
limit
语法：
分页
limit offset,size
offset:行数据索引
size:取多少条数据
#从第一条开始取十条
select * from employee limit 0,10;
# 从第十一条开始取十条
select * from employee limit 10,10;
使用的先后顺序：
where > group by > having > order by > limit
多表操作
外键
使用原因：
a.减少占用的空间
b.只需要修改表一次，其余的表中数据就会进行相应的修改
一对多
使用方法：
constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)；
例子：
create table department(
id int auto_increment primary key,
name varchar(32) not null default ''
)charset=utf8;
insert into department (name) values ('研发部');
insert into department (name) values ('运维部');
insert into department (name) values ('前台部');
insert into department (name) values ('小卖部');
create table userinfo(
id int auto_increment primary key,
name varchar(32) not null default '',
depart_id int not null default 1,
constraint fk_user_depart foreign key (depart_id) references department(id)
)charset=utf8;
insert into userinfo (name,depart_id) values ('zekai',1);
insert into userinfo (name,depart_id) values ('zekai',2);
insert into userinfo (name,depart_id) values ('zekai',3);
insert into userinfo (name,depart_id) values ('zekai',4);
insert into userinfo (name,depart_id) values ('zekai',1);
insert into userinfo (name,depart_id) values ('zekai',2);
insert into userinfo (name,depart_id) values ('zekai',1);#会报错
多对多
create table boy(
id int auto_increment primary key,
bname varchar(32) not null default ''
)charset=utf8;
insert into boy (bname) values ('zhangsan'),('lisi'),('wangeu');
create table girl(
id int auto_increment primary key,
gname varchar(32) not null default ''
)charset=utf8;
insert into girl (gname) values ('cuihua'),('gangdan'),('jianguo');
create table boy2girl(
id int auto_increment primary key,
bid int not null default 1,
gid int not null default 1,
constraint fk_boy2girl_boy foreign key (bid) references boy(id),
constraint fk_boy2girl_girl foreign key (gid) references girl(id)
)charset=utf8;
insert into boy2girl (bid,gid) values (1,1),(1,2),(2,3),(3,3),(2,2);
select * from boy left join boy2girl on boy.id=boy2girl.bid left join girl on girl.id=boy2girl.gid;
一对一
例子：
user:
id name age salary
1 zekai 18 5000
2 zekai1 23 6000
3 zekai2 19 3000
由于salary是比较敏感的字段，由此我们需要将此字段单独拆出来，变成一张独立的表
private:
id salary uid(外键+unique)
1 5000 1
2 6000 2
3 3000 3
create table user(
id int auto_increment primary key,
name varchar(32) not null default ''
)charset=utf8;
insert into user (name) values ('zhangsan'),('lisi'),('wangeu');
create table priv(
id int auto_increment primary key,
salary varchar(32) not null default '',
uid int not null default 1,
constraint fk_priv_user foreign key (uid) references user(id),
unique(uid)
)charset=utf8;
insert into priv (salary,uid) values (2000,1),(2800,2),(3000,3);
多表联查
left join .....on
select * from userinfo left join department on userinfo.depart_id=department.id;
right join ....on
select * from userinfo right join department on userinfo.depart_id=department.id;
