分组查询，多表联查
null 和not null
使用null的时候：
where 查询条件为 ：where 列表名 is null;
例：
create table t8( id int auto_increment primary key, name varchar(32), email varchar(32) )charset=utf8; insert into t8 (email) values ('xxxx'); mysql> insert into t8 (email) values ('xxxx'); Query OK, 1 row affected (0.05 sec) mysql> select * from t8; +----+------+-------+ | id | name | email | +----+------+-------+ | 1 | NULL | xxxx | +----+------+-------+ 1 row in set (0.00 sec) mysql> select * from t8 where name=''; Empty set (0.00 sec) mysql> select * from t8 where name is null; +----+------+-------+ | id | name | email | +----+------+-------+ | 1 | NULL | xxxx | +----+------+-------+ 1 row in set (0.01 sec)
使用not null的时候：
where 查询条件为 ：where 列表名 = ‘’;
例：
create table t9( id int auto_increment primary key, name varchar(32) not null default '', email varchar(32) not null default '' )charset=utf8; insert into t9 (email) values ('xxxx'); mysql> insert into t9 (email) values ('xxxx'); Query OK, 1 row affected (0.03 sec) mysql> select * from t9; +----+------+-------+ | id | name | email | +----+------+-------+ | 1 | | xxxx | +----+------+-------+ 1 row in set (0.00 sec) mysql> select * from t9 where name=''; +----+------+-------+ | id | name | email | +----+------+-------+ | 1 | | xxxx | +----+------+-------+ 1 row in set (0.00 sec)
创建表： create table employee( id int not null unique auto_increment, name varchar(20) not null, gender enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int )charset utf8; insert into employee(name,gender,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ;
单表查询
分组：
group by
将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
用法：
select 聚合函数, 选取的字段 from employee group by 分组的字段;
group by : 是分组的关键词
group by 必须和 聚合函数(count) 出现
where 条件语句和groupby分组语句的先后顺序：
where > group by > having(*********)
例子：
1.以性别为例， 进行分组， 统计一下男生和女生的人数是多少个：
mysql> select gender,count(id) as total from employee group by gender;
+--------+-------+
| gender | total |
+--------+-------+
| male | 10 |
| female | 8 |
+--------+-------+
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)
3.min : 求最小的
4. sum ： 求和
5. count ： 计数 数量
count 和 sum的区别：
mysql> select depart_id,count(age) from employee group by depart_id;
+-----------+------------+
| depart_id | count(age) |
+-----------+------------+
| 1 | 8 |
| 2 | 5 |
| 3 | 5 |
+-----------+------------+
3 rows in set (0.00 sec)
mysql> select depart_id,sum(age) from employee group by depart_id;
+-----------+----------+
| depart_id | sum(age) |
+-----------+----------+
| 1 | 362 |
| 2 | 150 |
| 3 | 100 |
+-----------+----------+
3 rows in set (0.03 sec)
6. avg ： 平均数
having
表示对group by 之后的数据， 进行再一次的二次筛选
mysql> select depart_id,avg(age) from employee group by depart_id ;
+-----------+----------+
| depart_id | avg(age) |
+-----------+----------+
| 1 | 45.2500 |
| 2 | 30.0000 |
| 3 | 20.0000 |
+-----------+----------+
3 rows in set (0.00 sec)
mysql> select depart_id,avg(age) from employee group by depart_id having avg(age) > 35;
+-----------+----------+
| depart_id | avg(age) |
+-----------+----------+
| 1 | 45.2500 |
+-----------+----------+
1 row in set (0.00 sec)
mysql> select depart_id,avg(age) as pj from employee group by depart_id having pj > 35;
+-----------+---------+
| depart_id | pj |
+-----------+---------+
| 1 | 45.2500 |
+-----------+---------+
1 row in set (0.00 sec)
where 条件语句和groupby分组语句的先后顺序：
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 desc;
limit
分页
limit offset， size
offset： 行数据索引
size: 取多少条数据
mysql> select * from employee limit 0,10;#第一行为0，从第一行开始，取10条信息
总结
使用顺序为：
select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件;
where > group by > having > order by > limit
多表操作
外键
a. 减少占用的空间
b. 只需要修改department表中一次， 其余的表中的数据就会相应的修改
一对多：
使用方法：
constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
create table department(
id int auto_increment primary key,
name varchar(32) not null default ''
)charset utf8;
insert into department (name) values ('研发部');
insert into department (name) values ('运维部');
insert into department (name) values ('前台部');
insert into department (name) values ('小卖部');
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),
#constraint fk_user_depart foreign key (depart_id) references department(id),
)charset utf8;
insert into userinfo (name, depart_id) values ('zekai', 1);
insert into userinfo (name, depart_id) values ('xxx', 2);
insert into userinfo (name, depart_id) values ('zekai1', 3);
insert into userinfo (name, depart_id) values ('zekai2', 4);
insert into userinfo (name, depart_id) values ('zekai3', 1);
insert into userinfo (name, depart_id) values ('zekai4', 2);
insert into userinfo (name, depart_id) values ('zekai4', 5);
多对多：
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;
mysql> select * from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;
+----+----------+------+------+------+------+---------+
| id | bname | id | bid | gid | id | gname |
+----+----------+------+------+------+------+---------+
| 1 | zhangsan | 1 | 1 | 1 | 1 | cuihua |
| 1 | zhangsan | 2 | 1 | 2 | 2 | gangdan |
| 2 | lisi | 5 | 2 | 2 | 2 | gangdan |
| 2 | lisi | 3 | 2 | 3 | 3 | jianguo |
| 3 | zhaoliu | 4 | 3 | 3 | 3 | jianguo |
+----+----------+------+------+------+------+---------+
5 rows in set (0.00 sec)
mysql> select bname, gname from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;
+----------+---------+
| bname | gname |
+----------+---------+
| zhangsan | cuihua |
| zhangsan | gangdan |
| lisi | gangdan |
| lisi | jianguo |
| zhaoliu | jianguo |
+----------+---------+
5 rows in set (0.00 sec)
mysql> select bname, gname from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid where bname='zhangsan';
+----------+---------+
| bname | gname |
+----------+---------+
| zhangsan | cuihua |
| zhangsan | gangdan |
+----------+---------+
2 rows in set (0.02 sec)
一对一：
user :
id name age
1 zekai 18
2 zhangsan 23
3 xxxx 19
由于salary是比较敏感的字段，因此我们需要将此字段单独拆出来， 变成一张独立的表
private：
id salary uid (外键 + unique)
1 5000 1
2 6000 2
3 3000 3
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);
insert into priv (salary, uid) values (6000, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'uid'
多表联查：
mysql> select * from department;
+----+--------+
| id | name |
+----+--------+
| 1 | 研发部 |
| 2 | 运维部 |
| 3 | 前台部 |
| 4 | 小卖部 |
+----+--------+
4 rows in set (0.07 sec)
mysql> select * from userinfo;
+----+--------+-----------+
| id | name | depart_id |
+----+--------+-----------+
| 1 | zekai | 1 |
| 2 | xxx | 2 |
| 3 | zekai1 | 3 |
| 4 | zekai2 | 4 |
| 5 | zekai3 | 1 |
| 6 | zekai4 | 2 |
+----+--------+-----------+
6 rows in set (0.00 sec)
left join 。。。 on
select * from userinfo left join department on depart_id = department.id
mysql> select name from userinfo left join department on depart_id = department.id;
ERROR 1052 (23000): Column 'name' in field list is ambiguous
mysql> select userinfo.name as uname, department.name as dname from userinfo left join department on depart_id = department.id;
+--------+--------+
| uname | dname |
+--------+--------+
| zekai | 研发部 |
| zekai3 | 研发部 |
| xxx | 运维部 |
| zekai4 | 运维部 |
| zekai1 | 前台部 |
| zekai2 | 小卖部 |
+--------+--------+
6 rows in set (0.00 sec)
right join ... on
mysql> insert into department (name) values ('财务部');
Query OK, 1 row affected (0.04 sec)
mysql>
mysql> select * from department; );
+----+--------+
| id | name |
+----+--------+
| 1 | 研发部 |
| 2 | 运维部 |
| 3 | 前台部 |
| 4 | 小卖部 |
| 5 | 财务部 |
+----+--------+
5 rows in set (0.00 sec)
mysql> select * from userinfo;
+----+--------+-----------+
| id | name | depart_id |
+----+--------+-----------+
| 1 | zekai | 1 |
| 2 | xxx | 2 |
| 3 | zekai1 | 3 |
| 4 | zekai2 | 4 |
| 5 | zekai3 | 1 |
| 6 | zekai4 | 2 |
+----+--------+-----------+
6 rows in set (0.00 sec)
mysql> select userinfo.name as uname, department.name as dname from userinfo left join department on depart_id = department.id;
+--------+--------+
| uname | dname |
+--------+--------+
| zekai | 研发部 |
| zekai3 | 研发部 |
| xxx | 运维部 |
| zekai4 | 运维部 |
| zekai1 | 前台部 |
| zekai2 | 小卖部 |
+--------+--------+
6 rows in set (0.00 sec)
mysql> select userinfo.name as uname, department.name as dname from userinfo right join department on depart_id = department.id;
+--------+--------+
| uname | dname |
+--------+--------+
| zekai | 研发部 |
| zekai3 | 研发部 |
| xxx | 运维部 |
| zekai4 | 运维部 |
| zekai1 | 前台部 |
| zekai2 | 小卖部 |
| NULL | 财务部 |
+--------+--------+
7 rows in set (0.00 sec)
inner join
mysql> select * from department inner join userinfo on department.id=userinfo.depart_id;
+----+--------+----+--------+-----------+
| id | name | id | name | depart_id |
+----+--------+----+--------+-----------+
| 1 | 研发部 | 1 | zekai | 1 |
| 1 | 研发部 | 5 | zekai3 | 1 |
| 2 | 运维部 | 2 | xxx | 2 |
| 2 | 运维部 | 6 | zekai4 | 2 |
| 3 | 前台部 | 3 | zekai1 | 3 |
| 4 | 小卖部 | 4 | zekai2 | 4 |
+----+--------+----+--------+-----------+
6 rows in set (0.00 sec)
