Linux:MYSQL（四）多表操作

最新推荐文章于 2022-09-24 13:13:32 发布
最新推荐文章于 2022-09-24 13:13:32 发布
阅读量326 收藏
点赞数
分类专栏： Linux服务 MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_40001704/article/details/87887984
版权

多表查询 union 纵向合并

两张表合并，需要对应关系,union有去重功能

select stuid,name 
from students
union
select tid,name 
from teachers;

多表查询 横向合并

一、cross join 交叉连接

笛卡尔乘积 横向乘积，横向合并
缺点：无效项太多

select s.name as student_name,s.age,t.name as teacher_name
from students as s
cross join 
teachers as t;
避免标题一样，可以起别名，但可能太多，同时再给表起个别名

二、inner join …on 内连接

等值连接：让表之间的字段以“等值”建立连接关系；
不等值连接  平等关系，谁在前都可以


select s.name as stu_name,t.name as tea_name
from students as s
inner join
teachers as t
on s.teacherid = t.tid;
取出学生对应的老师，并把学生标题和老师标题改别名


老版本写法：
select 
	s.name as stu_name,t.name as tea_name
from 
	students as s，teachers as t
where
	s.teacherid = t.tid;

三、left outer join … on左外连接 --可不加outer

select * 
from students as s
left outer join 
teachers as t 
on 
s.teacherid = t.tid;
左学生表全要，右老师表有的则匹配，没有则忽略

四、right outer join … on右外连接 --可不加outer

select stuid as id,s.name as stu_name,t.name as tea_name 
from students as s 
right join 
teachers as t 
on 
s.teacherid = t.tid; 
左学生表姓名对应右老师姓名，但只显示右边老师表有的

五、自连接:去掉重复列的等值连接

  • 实验：一张表内如何体现出两张表的感觉
搭建实验环境：
MariaDB [db1]>
create table 
	emp (id int,name char(50),leaderid int);

MariaDB [db1]>
insert 
	emp values(1,'liuying',null),
	(2,'dushan',1),
	(3,'dudou',1),
	(4,'duwen',2)，
	(5,'dusiying',1);

MariaDB [db1]> select * from emp;
+------+----------+----------+
| id   | name     | leaderid |
+------+----------+----------+
|    1 | liuying  |     NULL |
|    2 | dushan   |        1 |
|    3 | dudou    |        1 |
|    4 | duwen    |        2 |
|    5 | dusiying |        1 |
+------+----------+----------+


实际举例：解题思路，把emp表想象成emp和leader两张表，筛选时把emp设置两次别名即可
MariaDB [db1]>
select 
	e.id,e.name as slave,l.name as master 
from 
	emp as e
left join 
	emp as l
on
	e.leaderid=l.id 
order by 
	id;
+------+----------+---------+
| id   | slave    | master  |
+------+----------+---------+
|    1 | liuying  | NULL    |
|    2 | dushan   | liuying |
|    3 | dudou    | liuying |
|    4 | duwen    | dushan  |
|    5 | dusiying | liuying |
+------+----------+---------+

六、变种右外连接取反举例

select 
	*
from 
	students as s 
left join 
	teachers as t 
on 
	s.teacherid = t.tid
where
	t.tid is null;

去除右边表交集部分

七、full outer join …on** 完全外连接

最尴尬的是mysql不支持full outer join..on语句，
mysql解题思路：先用左外连接和右外连接合并，最后用union去重即可
其他数据库直接用full outer join 语句

select 
	* 
from 
	students s 
left join 
	teachers t 
on 
	s.teacherid=t.tid
union
select 
	*
from 
	students s
right join 
	teachers t 
on
	s.teacherid=t.tid;

实战举例：

MariaDB [hellodb]>
select 
	s.stuid,s.name as student_name,t.name as teacher_name,s.teacherid 
from 
	students s 
left join 
	teachers t 
on 
	s.teacherid=t.tid 
union 
select 
	s.stuid,s.name as student_name,t.name as teacher_name,s.teacherid 
from 
	students s 
right join 
	teachers t 
on 
	s.teacherid=t.tid;
+-------+---------------+---------------+-----------+
| stuid | student_name  | teacher_name  | teacherid |
+-------+---------------+---------------+-----------+
|     1 | Shi Zhongyu   | Miejue Shitai |         3 |
|     2 | Shi Potian    | NULL          |         7 |
|     3 | Xie Yanke     | NULL          |        16 |
|     4 | Ding Dian     | Lin Chaoying  |         4 |
|     5 | Yu Yutong     | Song Jiang    |         1 |
|     6 | Shi Qing      | NULL          |      NULL |
|     7 | Xi Ren        | NULL          |      NULL |
|     8 | Lin Daiyu     | NULL          |      NULL |
|     9 | Ren Yingying  | NULL          |      NULL |
|    10 | Yue Lingshan  | NULL          |      NULL |
|    11 | Yuan Chengzhi | NULL          |      NULL |
|    12 | Wen Qingqing  | NULL          |      NULL |
|    13 | Tian Boguang  | NULL          |      NULL |
|    14 | Lu Wushuang   | NULL          |      NULL |
|    15 | Duan Yu       | NULL          |      NULL |
|    16 | Xu Zhu        | NULL          |      NULL |
|    17 | Lin Chong     | NULL          |      NULL |
|    18 | Hua Rong      | NULL          |      NULL |
|    19 | Xue Baochai   | NULL          |      NULL |
|    20 | Diao Chan     | NULL          |      NULL |
|    21 | Huang Yueying | NULL          |      NULL |
|    22 | Xiao Qiao     | NULL          |      NULL |
|    23 | Ma Chao       | NULL          |      NULL |
|    24 | Xu Xian       | NULL          |      NULL |
|    25 | Sun Dasheng   | NULL          |      NULL |
|  NULL | NULL          | Zhang Sanfeng |      NULL |
+-------+---------------+---------------+-----------+

八、去交集

select 
	* 
from
	(select 
		s.stuid,s.name as student_name,t.name as teacher_name,s.teacherid 
	from 
		students s 
	left join 
		teachers t 
	on 
		s.teacherid=t.tid 
	union 
		select s.stuid,s.name as student_name,t.name as teacher_name,s.teacherid 
	from 
		students s 
	right join 
		teachers t 
	on 
		s.teacherid=t.tid;)
	as a
where
	a.teacherid is null 
or
	a.stuid is null;

子查询

  • 子查询：在查询语句嵌套着查询语句，性能较差
    基于某语句的查询结果再次进行的查询

  • 用在WHERE子句中的子查询
    用于比较表达式中的子查询；子查询仅能返回单个值

SELECT 
	Name,Age 
FROM 
	students 
WHERE 
	Age>(SELECT 
			avg(Age) 
		FROM 
			students);
从学生列表中显示大于平均年龄的人员，然后用这个条件在学生表中显示出改学生的name和age
  • 用于WHERE…IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
    • 用于EXISTS，两个表中得有符合条件才行
SELECT 
	Name,Age 
FROM 
	students 
WHERE 
	Age IN (SELECT 
				Age 
			FROM 
				teachers);
从老师列表中显示年龄，然后用这个条件在在学生表显示出符合该年龄的学生name和age
学生年龄和老师年龄一样才能才能搜选出结果
  • 用于FROM子句中的子查询
    使用格式：SELECT tb_alias.col1,… FROM (SELECT clause) AS tb_alias WHERE Clause;
SELECT 
	s.aage,s.ClassID 
FROM 
	(SELECT 
		avg(Age) AS aage,ClassID 
	FROM 
		students 
	WHERE 
		ClassID IS NOT NULL 
	GROUP BY 
		ClassID) 
AS s
WHERE 
	s.aage>30;
	
列出每个班平均年龄大于30岁的学生ID，先查平均年龄，在查大于30岁平均年龄

三张表：列出student_name,course_name,score 三项

一、学生表：

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

二、分数表：

MariaDB [hellodb]> select * from scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
|  1 |     1 |        2 |    77 |
|  2 |     1 |        6 |    93 |
|  3 |     2 |        2 |    47 |
|  4 |     2 |        5 |    97 |
|  5 |     3 |        2 |    88 |
|  6 |     3 |        6 |    75 |
|  7 |     4 |        5 |    71 |
|  8 |     4 |        2 |    89 |
|  9 |     5 |        1 |    39 |
| 10 |     5 |        7 |    63 |
| 11 |     6 |        1 |    96 |
| 12 |     7 |        1 |    86 |
| 13 |     7 |        7 |    83 |
| 14 |     8 |        4 |    57 |
| 15 |     8 |        3 |    93 |
+----+-------+----------+-------+
15 rows in set (0.00 sec)

二、科目表

MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.00 sec)

解题思路：看对应关系，两个表两个表做连接

先列出student_name和score

MariaDB [hellodb]> 
select 
	* 
from 
	students 
inner join 
	scores 
on 
	students.stuid=scores.stuid;
	
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |  1 |     1 |        2 |    77 |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |  2 |     1 |        6 |    93 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |  3 |     2 |        2 |    47 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |  4 |     2 |        5 |    97 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |  5 |     3 |        2 |    88 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |  6 |     3 |        6 |    75 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |  7 |     4 |        5 |    71 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |  8 |     4 |        2 |    89 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |  9 |     5 |        1 |    39 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 | 10 |     5 |        7 |    63 |
|     6 | Shi Qing    |  46 | M      |       5 |      NULL | 11 |     6 |        1 |    96 |
|     7 | Xi Ren      |  19 | F      |       3 |      NULL | 12 |     7 |        1 |    86 |
|     7 | Xi Ren      |  19 | F      |       3 |      NULL | 13 |     7 |        7 |    83 |
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | 14 |     8 |        4 |    57 |
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | 15 |     8 |        3 |    93 |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
15 rows in set (0.01 sec)

select *后可知，后四个字段是scores表的，classid,teacherid,gender三个字段是students表的，只提取我们需要的student_name和score即可(顺便起别名）

MariaDB [hellodb]> 
select  
	st.name as student_name , sc.score 
from 
	students as st 
inner join	 
	scores as sc 
on 
	st.stuid=sc.stuid;

+--------------+-------+
| student_name | score |
+--------------+-------+
| Shi Zhongyu  |    77 |
| Shi Zhongyu  |    93 |
| Shi Potian   |    47 |
| Shi Potian   |    97 |
| Xie Yanke    |    88 |
| Xie Yanke    |    75 |
| Ding Dian    |    71 |
| Ding Dian    |    89 |
| Yu Yutong    |    39 |
| Yu Yutong    |    63 |
| Shi Qing     |    96 |
| Xi Ren       |    86 |
| Xi Ren       |    83 |
| Lin Daiyu    |    57 |
| Lin Daiyu    |    93 |
+--------------+-------+

由题可知分数表是学生表和科目表的纽带，再分析成绩表和科目表的关系，因为科目表和成绩表都有课程编号，这时候我们再挑出课程标号courseid

MariaDB [hellodb]> 
select  
	st.name as student_name , sc.score,sc.courseid 
from  
	students as st 
inner join  
	scores as sc 
on 
	st.stuid=sc.stuid;

+--------------+-------+----------+
| student_name | score | courseid |
+--------------+-------+----------+
| Shi Zhongyu  |    77 |        2 |
| Shi Zhongyu  |    93 |        6 |
| Shi Potian   |    47 |        2 |
| Shi Potian   |    97 |        5 |
| Xie Yanke    |    88 |        2 |
| Xie Yanke    |    75 |        6 |
| Ding Dian    |    71 |        5 |
| Ding Dian    |    89 |        2 |
| Yu Yutong    |    39 |        1 |
| Yu Yutong    |    63 |        7 |
| Shi Qing     |    96 |        1 |
| Xi Ren       |    86 |        1 |
| Xi Ren       |    83 |        7 |
| Lin Daiyu    |    57 |        4 |
| Lin Daiyu    |    93 |        3 |
+--------------+-------+----------+
15 rows in set (0.00 sec)

最后取出的表再和courses表内连接即可取出题目索要的答案；

MariaDB [hellodb]> 
select  
	st.name as student_name , sc.score, c.course
from 
	students as st 
inner join 
	scores as sc 
on  
	st.stuid=sc.stuid
inner join 
	courses as c
on 
	sc.courseid=c.courseid;

SELECT语句执行顺序表：

杜达达
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysqldump 备份多张相关联的表，并且执行有条件的筛选
04-05
总结关于 mysqldump 多张表筛选备份的使用方法
Linux下MySQL多表操作
wwl012345的博客
05-29 450
建立如下两个数据库进行多表操作 1.普通双表连接查询 select 表1.列名，表2.列名 from 表1，表2 where 连接条件； select 表1别名.列名，表2别名.列名 from 表1 as 表1别名，表2 as 表2别名 where 连接条件； 2.内连接查询 内连接发生在从两个表中选取记录且第一个表中...
Linux shell实现MySQL数据库简单查询&&MySQL多表联合查询
sunny05296的博客
06-01 1999
Linux shell实现MySQL数据库简单查询&&MySQL多表联合查询 两张表： appdb.t_position: id, appId, appName, eventId, isdel appdb.t_record: id, uid, name 根据t_record中的name查找uid，uid对应t_position中的appId，根据uid关联找到相等的appId，关键
linux下的单表查询、多表查询、MySQL函数、SQL优化
joker_ice_cream的博客
02-28 239
单表查询 查询所有字段的所有行： 用 ’ \G ’ 代替 ‘ ; ’，表示格式化，数据量大的时候可以这样用，更清晰。 查询部分字段的所有行 带条件查询，查询所有字段的部分行 条件查询 SELECT col_name FROM tb_name WHERE 条件 查询条件可以是大于等于不等于（>,=,<>）等等(模糊查询)，也可以是更加复杂的判断都是可以的。 精确查询 AND 需要满足所有条件才会被查询出来； OR 只需要满足其中一个条件即可被查询出来 模糊查询 % 是通配符
【MySQL】06-多表查询
qq_44922493的博客
09-24 1165
【MySQL】06.简单易懂的 多表查询
linux下mysql数据库的操作的方法
09-09
在MySQL命令行界面，你可以执行以下基本操作： - `mysql -u root -p`：登录MySQL。 - `create database mysqltest;`：创建一个名为`mysqltest`的数据库。 - `show databases;`：显示所有数据库，包括系统数据库`...
基于linux下：mysql-5.7安装包
04-18
在Linux环境下安装MySQL 5.7是一个常见的任务，尤其对于那些需要强大数据库支持的服务器管理员和开发者来说。MySQL 5.7是该数据库系统的一个重要版本，提供了许多增强的特性和性能优化。以下是对这个安装过程的详细...
Linux下启动多个mysql服务器例子
09-10
在Linux环境中，有时我们需要运行多个MySQL服务器实例，以满足不同应用或数据库的需求。这个过程涉及到配置不同的数据库目录、设置权限、复制配置文件以及创建自定义启动脚本等步骤。以下是一个详细的指南，介绍如何...
linux 安装 mysql操作手册
10-31
在Linux系统中安装MySQL是一项常见的任务，特别是在搭建服务器环境或开发过程中。本篇文章将详细介绍如何在Linux上快速有效地安装MySQL，包括对已有MySQL环境的处理和新安装的步骤。 首先，如果你的系统中没有MySQL...
Linux操作系统上面安装mysql指南
最新发布
04-10
text版本的学习指南进行基础的上传
mysqldump多表联合条件设置方法
davyxie的专栏
12-27 3525
mysqldump多表联合条件设置方法
Linux中MySQL数据库的使用⑤-----多表查询、视图表
Chenih的博客
09-15 472
一、多表查询 UNION联合查询 UNION操作符用于合并两个或多个SELECT语句的结果集 要求： 1.两边select语句的字段数必须一样 2.两边可以具有不同数据类型的字段 3.字段名默认按照左边的表来设置 4.用法： select column_name(s) from table1 union select column_name(s) from table2; INNER JOIN：内连接 INNER JOIN关键字在表中存在至少一个匹配时返回行。 select student.id, stu
mysql表加锁、全表加锁、查看加锁、解锁
weixin_34111790的博客
11-08 435
单个表锁定： 格式：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},……] 例子：lock tables db_a.tbl_aaa read;　　// 锁定了db_a库中的tbl_aaa表 解锁：unlock tables; 全局表锁定： 命令：FLUSH TABLES WITH READ L...
Mysql&Hive练习语句(一)
barry8023的博客
10-24 612
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10)); insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12...
LEFT JOIN ON 后面跟多个条件和Where的区别
热门推荐
Star.Guo的专栏
05-26 2万+
LEFT JOIN ON 后面跟多个条件和Where的区别,如果认为LEFT JOIN的 On后面跟的条件和写在Where后面一样，那你就错了,测试LEFTJOIN 发现，ON后面跟了好几个条件
两个表之间的关联linux,请教多对多关联中间表关系的删除
weixin_35356909的博客
05-13 246
需求如下,多对多中,A表USERuser_idint(10)user_name varchar(20),B表(中间表)USER_GROUP,user_idint(10)group_id int(10)C表GROUP,group_id int(10)group_name varchar(20)在开发当中,平常的多对多操作,大多是对它进行级联操作,如删除A表就级联删除B表及C表的关联关系及记录...
Linux mysql-多表查询
zhangt123321的博客
11-28 763
多表查询: 建表 mysql> create table emp(emp_id int,emp_name char(10),emp_age tinyint, dpt_id int); mysql> create table dpt(dpt_id int,dpt_name char(10)); insert into emp values(1,'robin',30,200); insert into emp values(2,'zorro',25,200); insert i
Linux运维进阶：MySQL DBA实战指南
"这篇运维进阶教程专注于Linux系统下的MySQL DBA实践，共计38页，涵盖了MySQL在生产环境中的关键应用，包括安装优化、复制技术、集群解决方案、性能监控和调优，以及备份与恢复策略。教程针对的读者是运维工程师和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值