当前位置：首页 > news >正文

MySQL 系统学习系列 - SQL 语句 DQL 语句的使用（3）《MySQL系列篇-05》

news 来源：原创 2024/9/20 22:36:26

SQL 语句 DQL

多表连接查询

连接与多表查询：连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据

基本简介与表之间的搭建（用于使用多表查询语句-即准备工作）

单词普及(名称) 单词
连接 join
内 inner
外 left/right
基本语法[内连接示例] inner join on ...

单词普及(名称)	单词
连接	join
内	inner
外	left/right
基本语法[内连接示例]	`inner join on ...`

# 表与数据的准备CREATE TABLE IF NOT EXISTS t1( -- 左表id INT NOT NULL,name VARCHAR(15));CREATE TABLE IF NOT EXISTS t2( -- 右表id INT NOT NULL,name VARCHAR(15));# 插入数据INSERT INTO t1 VALUES(1,'t1a');INSERT INTO t1 VALUES(2,'t1b');INSERT INTO t1 VALUES(3,'t1c');INSERT INTO t1 VALUES(4,'t1d');INSERT INTO t1 VALUES(5,'t1e');INSERT INTO t1 VALUES(6,'t1f');INSERT INTO t2 VALUES(1,'t2b');INSERT INTO t2 VALUES(2,'t2c');INSERT INTO t2 VALUES(3,'t2d');INSERT INTO t2 VALUES(4,'t2e');INSERT INTO t2 VALUES(5,'t2f');

笛卡尔积与内连接：可以理解没有LEFT|RIGHT的JOIN就为内连接，具体语法如下面的外连接处

# 笛卡尔积 [每一个连接都会当做一行]-- 笛卡尔积:当有一张表中有3行数据，一张有5行数据，则连接后的数据则有 3*5 数据SELECT * FROM t1,t2; -- 多表查询连接，会有30条数据SELECT * FROM t1 join t2; -- 直接使用连接join：该多表连接，等价与上面 [同样属于笛卡尔积]# 内连接 [相当于求两表的交集]SELECT * FROM t1 JOIN t2 ON t1.id=t2.id; -- 此时该两张表，就可以根据id相等的进行查询连接，只有5条数据 SELECT t1.id,t1.name,t2.name FROM t1 JOIN t2 ON t1.id=t2.id; -- 因为id的值是相同的，所以也可以将id过滤掉一个

外连接：SELECT * FROM 表1 LEFT/RIGHT JOIN 表2 ON 连接条件[基准]

# 左外连接 [相当于取两圈的左圈]SELECT * FROM t1 LEFT JOIN t2 on t1.id=t2.id; -- 左表全部保留，右表关联不上的用null表示 [而且只会查询到左表列的最大值]SELECT * FROM t1 LEFT OUTER JOIN t2 on t1.id=t2.id; -- 加个OUTER同理，下面同理# 右外连接 [相当于取两圈的右圈]SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id; -- 右表全部保留，左表关联不上的用null表示# 取左表/右表的独有数据 [直接判断右表或左表为null即可   SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; -- 取左表独有的数据 [ 即判断处右表是空的 ]SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE t1.id IS NULL; -- 取右表独有的数据 [ 即判断处左表是空的 ]

全连接&并集去交集

# 全连接（mysql不支持全连接，但可以通过union变相实现全连接；oracle支持全连接）-- union 可以将两张表的数据进行连接，并且会自动去重-- 所以我们就可以通过获取左/右连接的数据，再进行union进行两表的连接SELECT * FROM t1 LEFT JOIN t2 on t1.id=t2.id -- 左表的数据union -- 可以进行多表的数据连接，并会自动进行去重操作SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id; -- 右表的数据/**除了union外，还有一个union all语句与union一样可以进行多表的数据的谅解不同的是union会对连接的数据进行去重，而union all不会自动进行去重union all 的效率要比 union 高很多*/

# 并集去交集 [扩]-- 可以直接使用union语句，来对左表与右表独有的数据进行连接即可SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL -- 左表独有的数据union -- 连接两表的数据SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE t1.id IS NULL; -- 右表独有的数据

**union连接两个及两个以上的SELECT的结果字符集**<!-- ## DQL（3） -->## 子查询- 子查询是SQL嵌套查询的程序模块。当一个查询是另一个查询的条件时，称之为子查询。
- 语法：比较运算符{ALL|SOME|ANY} （子查询运算符）
- ```sqlSELECT * FROM 表名 WHERE 比较列名 = ( -- 括号中的为子查询语句SELECT 列名s FROM 表名 .....);# 如SELECT * FROM stu WHERE age = (SELECT DISTINCT age FROM stu ORDER BY age DESC LIMIT 2,1);-- .....................................................

子查询与多表连接练习

表结构与数据的准备

# 表结构定义CREATE TABLE IF NOT EXISTS grade ( # 定义一个班级表g_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,g_name VARCHAR(25) NOT NULL);# 因为一个班级可有多名学生，而一名学生只能有一个班级（所以班级表与学生表是一个一对多的关系，即需要再学生表中加上班级表的主键作为外键）CREATE TABLE IF NOT EXISTS students ( # 定义一个学生表id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(11) NOT NULL,grade_id int, # 定义外键列               CONSTRAINT fk_students_grade FOREIGN KEY(grade_id) REFERENCES grade(g_id) # 定义外键);# 数据插入INSERT INTO grade VALUES(0,'JAVA班'),(0,'PYTHON班'),(0,'JS班'),(0,'HTML班'); # 插入班级信息INSERT INTO students VALUES(0,"li",4),(0,"hello",4),(0,"wang",4),(0,"kong",1),(0,"deng",3),(0,"2kong",2); # 插入学生信息

练习

# 1.查询Java班的所有学生信息SELECT g_id FROM grade WHERE g_name='JAVA班'; -- 查询JAVA班的id，再通过该id去学生表中查询所有的学生信息 【该语句就为下面语句的子查询】SELECT * FROM students WHERE grade_id in (SELECT g_id FROM grade WHERE g_name='JAVA班'); -- 根据查询到的班级id，来查询对应的学生信息 # 2.查询所有学生信息中名字长度最长的学生SELECT * FROM students WHERE id in (SELECT MAX(LENGTH(name)) FROM students); -- 获取所有名字的最大长度值SELECT * FROM students HAVING LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM students); -- 根据最大长度值，获取对应的学生信息# 3.查询所有学生信息中名字长度小于5的学生SELECT * FROM students HAVING LENGTH(name)<5;# 4.查询名字长度大于5的学生，来自哪一个班级SELECT s.id,s.name,g.g_name FROM students s LEFT OUTER JOIN grade g ON s.grade_id=g.g_id HAVING LENGTH(s.name)>5;# 5.查询JAVA与PYTHON班的所有学生姓名SELECT id FROM grade WHERE g_name='JAVA班' or g_name='PYTHON班'; -- 查询JAVA与PYTHON班的班级idSELECT * FROM students WHERE grade_id in (SELECT g_id FROM grade WHERE g_name='JAVA班' or g_name='PYTHON班');# 6.查询名字长度小于5的学生，包括班级信息SELECT * FROM students s LEFT OUTER JOIN grade g ON g.g_id=s.grade_id HAVING LENGTH(s.name)<5;