day 35小结

目录

• null 和 notnull
  • 使用null的时候:
  • 使用notnull的时候:
• 单表操作
• 多表操作
  • 一对多:
  • 多对多:
  • 一对一:
• 多表查询:

null 和 notnull

使用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)

使用notnull的时候:

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)

单表操作

​ 分组: 指的是将所有记录按照某个相同的字段进行归类,比如针对员工信息表的职位分组,或者性别分组等

​ group by
​ 用法: select 组合函数,选取的字段 from employee group by 分组的字段;
​ group by :是分组的关键词
​ group by 必须和聚合函数(count)出现

​ where条件语句和groupby分组语句的先后顺序
​ where > group by > having (***********************)

​ 例子:
​ 1.以性别为例,进行分组,统计一下男生和女生的人数是多少:

select count(id), gender from  employee group by gender;			+-----------+--------+			| count(id) | gender |			+-----------+--------+			|10 | male|			| 8 | female |			+-----------+--------+			2 rows in set (0.00 sec)					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 se		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: 表示对grounp 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 字段名 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;				+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+				| id | name| gender | age | hire_date  | post| post_comment | salary | office | depart_id |				+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+				|  1 | egon| male|  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL |7300.33 |401 | 1 |				|  2 | alex| male|  78 | 2015-03-02 | teacher| NULL | 1000000.31 |401 | 1 |				|  3 | wupeiqi| male|  81 | 2013-03-05 | teacher| NULL |8300.00 |401 | 1 |				|  4 | yuanhao| male|  73 | 2014-07-01 | teacher| NULL |3500.00 |401 | 1 |				|  5 | liwenzhou  | male|  28 | 2012-11-01 | teacher| NULL |2100.00 |401 | 1 |				|  6 | jingliyang | female |  18 | 2011-02-11 | teacher| NULL |9000.00 |401 | 1 |				|  7 | jinxin | male|  18 | 1900-03-01 | teacher| NULL |30000.00 |401 | 1 |				|  8 | 成龙| male|  48 | 2010-11-11 | teacher| NULL |10000.00 |401 | 1 |				|  9 | 歪歪| female |  48 | 2015-03-11 | sale| NULL |3000.13 |402 | 2 |				| 10 | 丫丫| female |  38 | 2010-11-01 | sale| NULL |2000.35 |402 | 2 |				+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+				10 rows in set (0.00 sec)

mysql> select * from employee limit 10,10;				+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+				| id | name| gender | age | hire_date  | post  | post_comment | salary| office | depart_id |				+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+				| 11 | 丁丁| female |  18 | 2011-03-12 | sale  | NULL |  1000.37 |402 | 2 |				| 12 | 星星| female |  18 | 2016-05-13 | sale  | NULL |  3000.29 |402 | 2 |				| 13 | 格格| female |  28 | 2017-01-27 | sale  | NULL |  4000.33 |402 | 2 |				| 14 | 张野| male|  28 | 2016-03-11 | operation | NULL | 10000.13 |403 | 3 |				| 15 | 程咬金 | male|  18 | 1997-03-12 | operation | NULL | 20000.00 |403 | 3 |				| 16 | 程咬银 | female |  18 | 2013-03-11 | operation | NULL | 19000.00 |403 | 3 |				| 17 | 程咬铜 | male|  18 | 2015-04-11 | operation | NULL | 18000.00 |403 | 3 |				| 18 | 程咬铁 | female |  18 | 2014-05-12 | operation | NULL | 17000.00 |403 | 3 |				+----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+				8 rows in set (0.00 sec)

总结:

​ 使用顺序:

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 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 :					idname  age  					1zekai  18					2zhangsan 23  					3xxxx19								由于salary是比较敏感的字段，因此我们需要将此字段单独拆出来， 变成一张独立的表								private：										id  salaryuid  (外键 + unique)					15000 1					26000 2					33000 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)