python day35 数据库多表查询

单表查询

#单表操作语法
SELECT DISTINCT 字段1,字段2... FROM 表名
                              WHERE 条件
                              GROUP BY field
                              HAVING 筛选
                              ORDER BY field
                              LIMIT 限制条数

分组 : group by

#group by：将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
#语法  
select 聚合函数,筛选的字段名 from 表名 group by 分组的字段名

#1.以性别为例， 进行分组， 统计一下男生和女生的人数是多少个
select count(id),gender from employee group by gender;

#2. 对部门进行分组， 求出每个部门年龄最大的那个人
select depart_id,max(age) from employee group by depart_id;

max : 求最大值

min : 求最小值

sum : 求和

count : 计数

avg : 求平均值

过滤 : having

where和having

执行优先级从高到低 : where > group by > having
    1. where 发生在分组group by之前,因而where中可以有任意字段,但不能使用聚合函数
    2.having发生在分组group by之后,因而having中可以使用分组字段,无法直接取到其他字段,可以使用聚合函数

语法

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

查询排序 order by

#语法
select * from 表名 order by 字段名;

select * from 表名 order by 字段名 asc;   #升序

select * from 表名 order by 字段名 desc;	 #降序

#先按照age排序，如果年纪相同，则按照薪资排序
select * from employee order by age,salary desc;

限制查询的记录数 limit

'''
limit 分页查询
limit offset， size	
    offset： 行数据索引
    size: 取多少条数据
'''
#语法
select * from 表名 order by 字段名 desc limit 3; #默认初始位置为0

select * from 表名 order by 字段名 desc limit 1,3#从第2条开始往后查3条

select * from employee limit 10,10;

单表小结

#使用顺序
where > group by > having > order by > limit

多表操作

外键 foreign key

外键优点:

1. 减少占用空间
2. 只需修改关联表中的数据,其余表中的数据也会相应的修改

#语法
constraint 外键名 foreign key 
	(被约束字段名) references 约束的表(约束的字段名);
    
constraint fk_user foreign key 
	(depart_id) references department(id);

多对多

create table boy(
	id int auto_increment primary key,
    bname varchar(32) not null default''
)charset utf8;

insert into (bname) values ('simple'),('simple2');

create table girl(
	id int auto_increment primary key,
    gname varchar(32) not null default''
)charset utf8;

insert into girl (gname) values ('momo'),('momo2');

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(gid)
)varchar utf8;

insert into boy2girl (bid,gid) values (1,1),(1,2),(2,2);

select * from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id = boy2girl.gid;

一对一

#(外键 + unique)
create table priv(
	id int auto_increment primary key,
    salary int not null default 0,
    uid int not null default 1,
    
    contraint fk_priv_user foreign key (uid) references user(id),unique(uid)
)varchar utf8;

insert into priv (salary, uid) values (2000, 1);
insert into priv (salary, uid) values (2800, 2);
insert into priv (salary, uid) values (3000, 3);

#如果uid重复添加,会报错

多表联查

#语法
select 字段列表 from 
	表1 inner/left/right join 表2 on 表1.字段 = 表.2字段;

内连接

# 找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
# department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
select * from employee inner join department on employee.dep_id=department.id;

外链接

#优先显示左表全部记录
# 以左表为准，即找出所有员工信息，当然包括没有部门的员工
# 本质就是：在内连接的基础上增加左边有右边没有的结果
select * from employee left join department on employee.dep_id=department.id;

外链接

#优先显示右表全部记录
# 以右表为准，即找出所有部门信息，包括没有员工的部门
# 本质就是：在内连接的基础上增加右边有左边没有的结果

select * from employee right join department on employee.dep_id=department.id;