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 :
					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)