mysql 1030 table_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;