1030 mysql_1030 MySQL单表操作和多表操作

单表操作

分组

分组指的是,将所有的记录按照某个相同字段进行归类

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;