数据库 --- 基础知识 3

目录

• 单表操作
  • 分组 (group by)
  • 升序 降序（order by）
  • 分页 (limit)
  • 总结 （重点）
• 多表操作 （重点）
  • 外键
  • 一对多
  • 多对多
  • 一对一
  • 多表联查

单表操作

分组 (group by)

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

# 用法：
select 聚合函数，选取的字段 from 表名 group by 分组的字段；

# 注意： gruop by :是分组的关键词，必须和聚合函数出现

# 例：
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) 
                        
# 常用的聚合函数
1.min    求最小值
2.sum    求和（所有值相加的总和）
3.count  计数（对某一相同事物进行数量统计）
4.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（升序）  # 使用时没有表明就默认是升序
orser by 字段名 desc（降序）

如果对多个字段进行排序，
				比如：
					age desc, id asc;
					表示： 先对age进行降序， 如果age有相同的行， 则对id进行升序
				
				 select * from employee order by age desc, id desc;
				+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
				| id | name       | gender | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
				+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
				|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                    | NULL         |    8300.00 |    401 |         1 |
				|  2 | alex       | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
				|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                    | NULL         |    3500.00 |    401 |         1 |
				|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
				|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                    | NULL         |   10000.00 |    401 |         1 |
				| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                       | NULL         |    2000.35 |    402 |         2 |
				| 14 | 张野       | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
				| 13 | 格格       | female |  28 | 2017-01-27 | sale                       | NULL         |    4000.33 |    402 |         2 |
				|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                    | NULL         |    2100.00 |    401 |         1 |
				| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                  | NULL         |   17000.00 |    403 |         3 |
				| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                  | NULL         |   18000.00 |    403 |         3 |
				| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                  | NULL         |   19000.00 |    403 |         3 |
				| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                  | NULL         |   20000.00 |    403 |         3 |
				| 12 | 星星       | female |  18 | 2016-05-13 | sale                       | NULL         |    3000.29 |    402 |         2 |
				| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                       | NULL         |    1000.37 |    402 |         2 |
				|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                    | NULL         |   30000.00 |    401 |         1 |
				|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                    | NULL         |    9000.00 |    401 |         1 |
				|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
				+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

分页 (limit)

limit offset， size
offset： 行数据索引
size: 取多少条数据
    
# 使用方法：select * from 表名 limit 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 

多表操作 （重点）

外键

# 使用的原因;
 a. 减少占用的空间
 b. 只需要修改 约束表中一次，其余的表中的数据就会相应的修改

一对多

# 一对多: 案例 员工职位表（多） 公司职位表（一）

# 使用方法：constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
在需要进行约束的表中加入，对表中的某一行进行约束

# 建表规则：先建立主表，再建立从表，在从表中设置主表的唯一字段(通常为主键)作为外键

# 例：
# 创建表1：
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 ('小卖部');

# 创建表2：				
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),
)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);
# 注：当添加的depart_id与department(id)不一致的时候会报错
insert into userinfo (name, depart_id) values ('zekai4', 5);

多对多

# 使用方法：类似于招聘，一个面试者可以面试多家公司，一个公司可以面试多个面试者

# 建表规则：新建第三张表，通过两个外键形成多对多关系

# 创建数据表1
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');

# 创建数据表2				
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');

# 创建数据表1与数据表2共同链接的表	
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)

一对一

# 使用方法：onstraint 外键名 foreign key (被约束的字段) references user(约束的字段),
          unique(被约束的字段)  # 将之前一对多进行约束，只能出现一个被约束的字段

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
# lift 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

# lift join ... on 的升级版使用方法
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> 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)

# left join ... on 查询
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> 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)
				
# left join ... on 与 right join ... on 的区别
left join ... on 是以左边为基准，如果右边没有会使用NULL补充
right join ... on 是以右边为基准，如果左边没有会使用NULL补充
			

# inner join ... on 查询	
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)