单表操作 外键 多表联查
目录
单表操作
分组：group by
分组指的是：讲所有记录按照某一个相同字段进行归类，比如对员工信息表的职位分组
用法：
select 聚合函数，选取的字段 from 表名 group by 分组的字段；
- group by 必须和聚合函数出现
- group by 是分组的关键
列子：
1. 以性别为例， 进行分组， 统计一下男生和女生的人数是多少个：
select count(id), gender from employee group by gender;
+-----------+--------+
| count(id) | gender |
+-----------+--------+
| 10 | male |
| 8 | female |
+-----------+--------+
2 rows in set (0.00 sec)
2. 对部门进行分组， 求出每个部门年龄最大的那个人？
mysql> select depart_id,max(age) from employee group by depart_id;
+-----------+----------+
| depart_id | max(age) |
+-----------+----------+
| 1 | 81 |
| 2 | 48 |
| 3 | 28 |
+-----------+----------+
3 rows in set (0.01 sec)
haviing
表示对group by 之后的数据，进行再一次的二次筛选
mysql> select depart_id,avg(age) from emloyee group by depart_id having avg(age)>35
+-----------+----------+
| depart_id | avg(age) |
+-----------+----------+
| 1 | 45.2500 |
| 2 | 30.0000 |
| 3 | 20.0000 |
+-----------+----------+
3 rows in set (0.00 sec)
order by 升序 降序
用法
order by 字段名 asc(升序) desc(降序)
如果对多个字段进行排序，
比如：
age desc, id asc;
表示： 先对age进行降序， 如果age有相同的行， 则对id进行升序
select * from employee order by age desc, id desc;
limit 分页
limit offset,size
offset：行数据索引
size：取多少条数据
用法
select * from employee limit 0,10; # limit 10 默认索引从0开始 取10条
总结
使用顺序
select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件
where > group by > having > order by > limit
外键
使用原因：
- 减少占用的空间
- 只需要修改一个表中数据，其余的表中数据就会相应的修改
一对多
使用方法：
constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
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)
# 可多个外键
# constraint fk_user_depart foreign key (depart_id) references department(id)
)charset utf8
多对多
create table boy (
id int auto_increment primary key,
bname varchar(32) not null default ''
)charset utf8;
insert into boy (bname) values ('zhangsan'),('lisi'),('zhaoliu');
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;
一对一
create table user (
id int auto_increment primary key,
name varchar(32) not null default ''
)charset=utf8;
insert into user (name) values ('zhangsan'),('zekai'),('kkk');
create table priv(
id int auto_increment primary key,
salary int not null default 0,
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);
# insert into priv (salary, uid) values (2800, 2);
# insert into priv (salary, uid) values (3000, 3);
#
多表联查
left join ...on
用法
以左边的为主
select * from 表1 left join 表2 on 表1被约束的字段名 = 表2约束的字段名
right join ...on
用法
以右边的为主
select * from 表1 left join 表2 on 表1被约束的字段名 = 表2约束的字段名
inner join
用法
select * from 表1 left join 表2 on 表1被约束的字段名 = 表2约束的字段名
