单表多表操作 联表查询

目录

1|0一、单表操作

1|11. 分组——> group by

  • 分组指的是：将所有表记录按照某个相同字段进行归类

  • 用法：

    • select 聚合函数,选取的字段 from 表名 group by 选择分组的字段 having 条件
      • 当group by后的字段为一个表的主键时，那么在select后面就可以查询该主键所在表的所有字段。否则select后查的字段只能是group by后的那个字段。
      • group by后可以跟多个字段，表示相同这些字段分到一组。如[1,2] [2,3] [,2,3] [3,4] ， [1,2] 分为一组，[2,3] 分为一组 ，[3,4] 分为一组，共三组。

  • 聚合函数：count/sum/max/min/avg

    • having：表示对分组之后的聚合函数处理的结果，进行再一次的筛选。

  • 注意：group by一般要和聚合函数一起使用。

    • 注意：聚合函数也可单独使用，放在select 之后。但此时select后面不能再跟其他字段。

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

  • 实例：

    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)

1|22. 排序——> order by

  • order by对查询的结果进行排序

  • 用法：

    • order by 字段名 asc/desc ，其中asc默认的表示升序排序，desc表示降序排序
    • 如果对多个字段进行排序，如 order by age desc , id asc，则先对age进行降序排序，如果排完序记录中有相同的age时，再把有相同的这些行按id升序排序。

1|33. 分页——> limit
  • 用法：
    • limit 参数1 , 参数2 。参数1表示行索引，从该行开始，表记录第第一行数据的索引是0，往下递增，参数2表示取多少行。

1|44. 总结（很重要）

  • 以上高级用法的使用顺序是：

  • select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件;

  • where > group by > having > order by > limit

  • 关键字执行的优先级

    1. from：找到表
    2. where：拿着where指定的约束条件，去文件/表中取出一条条记录
    3. group by：将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
    4. select：执行select
    5. distinct：去重
    6. having：将分组的结果进行having过滤
    7. order by：将结果按条件排序：order by
    8. limit：限制结果的显示条数

2|0二、多表操作

2|11. 外键

  • 使用外键的原因：

    • 减少占用的空间
    • 只需要修改一次原表中的数据 ，其余有对应外键的表中的数据就会相应的修改。

  • 使用方法：

    constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

2|22. 一对多（很常见类型）

  • 实例：

    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),
	
				)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);

2|33. 多对多（常见类型）

  • 实例：

    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)

2|44. 一对一（不常见类型）

  • 实例：

    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'

3|0三、多表联查

3|11. 左连接——>left join 表名 on..

  • 表示已left左边的表为主，会把左边的表中的信息全部显示，被join的表按照左边的表的数据一一对应显示。

  • 实例

    # 原表
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)

# 联表查询：            
			
# 错误写法				
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)

3|22. 右连接——>right join 表名 on..

  • 表示已right右边的表为主，会把右边的表中的信息全部显示，被join的表按照右边的表的数据一一对应显示。

  • 实例：

    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)

3|33. 内连接——>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)

3|44. 交叉连接
  • 语法：from 表1,表2

__EOF__

本文作者：BigSun丶
本文链接：https://www.cnblogs.com/Mcoming/p/11766291.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！
posted @   BigSun丶  阅读(587)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
点击右上角即可分享
微信分享提示