单表操作
分组
分组指的是,将所有的记录按照某个相同字段进行归类
group by
用法:
select 聚合函数,选取的字段 from employee group by 选取的字段;
加别名:
select 聚合函数 as 别名,选取的字段 from employee group by 选取的字段;
以性别分组为例:
select count(id),gender from employee group by gender;
group by:是分组的关键词
group by 必须和 聚合函数(count) 出现
count(计数),sum(求和),min(最小),max(最大)
having
表示对group by 之后的数据,进行第二次的筛选
select depart_id,avg(age) from employee group by depart_id; #第一次筛选
select depart_id,avg(age) from employee group by depart_id having avg(age) > 35; #having第二次筛选
where条件语句和group by分组语句的优先级
where > group by > having
升序和降序
order by
order by 字段名 asc(升序) desc(降序)
多字段进行排序
例:
age desc,id asc
表示:先对age进行降序,如果有相同age的,用id进行升序
select * from employee order by age desc,id asc;
分页
limit
limit offset, size
offset: 行数据索引
size: 取多少条数据
select * from employee limit 0,10;
select * from employee limit 10,10;
总结
使用顺序
select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件;
where > group by > having > order by > limit
多表操作*******
外键
使用原因
减少占用的空间
只需要修改一次department表中的数据,其余的表中的数据会相应的跟着修改
一对多
使用方法
constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
create table department(
id int auto_increment primary key,
name varcher(32) not null default ''
)charset=utf8;
insert into department (name) valuse ('研发部');
insert into department (name) valuse ('销售部');
create table userinfo(
id int auto_incremrnt primary key,
name varcher(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 (被绑定列) references department (绑定列)
)charset=utf8;
insert into userinfo (name,depart_id) valuse ('name1',1);
insert into userinfo (name,depart_id) valuse ('name2',2);
多对多
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 boy_girl(
id int auto_increment primary key,
bid int not null default 1,
gid int not null default 1,
constraint fk_boy_girl_boy foreign key (bid) references boy(id),
constraint fk_boy_girl_boy foreign key (gid) references girl(id)
)charset=utf8;
insert into boy_girl (bid,gid) values (1,1),(1,2),(2,3),(3,3),(2,2);
select * from boy left join boy_girl on boy.id = boy_girl.bid left join girl on gir.id = boy_girl.gid; #获取名字和id
select bname,gname from boy left join boy_girl on boy.id = boy_girl.bid left join girl on girl.id = boy_girl.gid; #获取名字
select bname,gname from boy left join boy_girl on boy.id = boy_girl.bid left join girl on gril.id = boy_gril.gid where bname = 'zhangsan'; #获取男名字是'zhangsan'的表
一对一
user :
id
name
age
1
x1
11
2
x2
12
3
x3
13
由于salary是比较敏感的字段,所以要把他独立出来
private:
id
salary
uid (外键+unique)
1
2000
1
2
3000
2
3
4000
3
create table user(
id int auto_increment primary key,
name varchar(32) not null default ''
)charset=utf8;
insert into user (name) values ('x1'),('x2'),('xx');
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 (3000,2);
insert into priv (salary,uid) values (4000,3);
多表连查
left join …… on ……
select * from userinfo left join department on depart_id = department.id;
select userinfo.name as uname,department.dname from userinfo left join department on depart_id = department.id; #无的不显示
right join …… on ……
select userinfo.name as uname, department.name as dname from userinfo right join department on depart_id = departmrnt.id; #无的显示NULL
inner join(了解)
select * from department inner join userinfo on department.id = userinfo.depart_id;