【技术积累】Mysql中的SQL高级技巧【一】

松风鸣

什么是多表查询？如何在MySQL中进行多表查询？

多表查询就是在一个查询中涉及到多个表，通过特定的关联方式连接多个表，并根据条件从中查询出所需要的数据。
多表查询是关系型数据库中最为基础的应用之一。
一个比较典型的例子就是，我们在查询一个订单的详细信息时，需要从订单表中获取订单编号，然后根据订单编号关联到商品表，获取商品信息，再根据商品信息关联到库存表中，查询商品数量。一个完整的查询操作需要同时查询多个表才能完整获取所需信息。
 
在MySQL中，进行多表查询的方式有多种，比较常用的有三种：内连接、左连接、右连接。其中：

• 内连接是一种常用的表连接方式，只有在两个表中都具有相同的数据时，才会将两个表进行连接。内连接包含四种：等值连接，非等值连接，自连接和多表连接。
  
• 左连接也是一种常用的表连接方式，左连接会保留左表中的所有数据，并将右表中符合条件的数据进行匹配，如果没有匹配的数据则返回NULL。左连接只会返回左表中的数据。
  
• 右连接和左连接很相似，只是保留的是右表的数据。
  

 
建表语句

1. # 1.订单表
4. CREATE TABLE orders (
5. id int(11) NOT NULL AUTO_INCREMENT,
6. order_num bigint(20) NOT NULL,
7. order_time datetime NOT NULL,
8. user_id int(11) NOT NULL,
9. PRIMARY KEY (id)
10. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
13. INSERT INTO orders (id, order_num, order_time, user_id)
14. VALUES
15. (1, 1111111, '2022-11-11 12:00:00', 1),
16. (2, 2222222, '2022-10-10 10:00:00', 2),
17. (3, 3333333, '2022-09-09 09:00:00', 3);
20. # 2.商品表
23. CREATE TABLE goods (
24. id int(11) NOT NULL AUTO_INCREMENT,
25. goods_name varchar(20) NOT NULL,
26. price decimal(10,2) NOT NULL,
27. PRIMARY KEY (id)
28. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
31. INSERT INTO goods (id, goods_name, price)
32. VALUES
33. (1, '商品1', 99.00),
34. (2, '商品2', 399.00),
35. (3, '商品3', 199.00);
38. # 3.订单商品关联表
41. CREATE TABLE order_goods (
42. id int(11) NOT NULL AUTO_INCREMENT,
43. order_id int(11) NOT NULL,
44. goods_id int(11) NOT NULL,
45. goods_num int(11) NOT NULL,
46. PRIMARY KEY (id)
47. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
50. INSERT INTO order_goods (id, order_id, goods_id, goods_num)
51. VALUES
52. (1, 1, 2, 2),
53. (2, 1, 3, 1),
54. (3, 2, 1, 3),
55. (4, 2, 2, 1),
56. (5, 3, 3, 2);

复制代码

 
多表查询的SQL语句

1. # 1.查询订单表中所有订单的详细信息，包括订单编号、订单时间、用户ID、商品名称、商品价格、商品数量等。
4. SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
5. FROM orders o
6. INNER JOIN order_goods og ON o.id = og.order_id
7. INNER JOIN goods g ON og.goods_id = g.id;
10. # 2.查询订单表中所有订单的详细信息，包括订单编号、订单时间、用户ID、商品名称、商品价格、商品数量等。但只显示2019-12-10之后的订单。
13. SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
14. FROM orders o
15. INNER JOIN order_goods og ON o.id = og.order_id
16. INNER JOIN goods g ON og.goods_id = g.id
17. WHERE o.order_time >= '2019-12-10';
20. # 3.查询用户ID为1的用户所下的所有订单的详细信息，包括订单编号、订单时间、用户ID、商品名称、商品价格、商品数量等。
23. SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
24. FROM orders o
25. INNER JOIN order_goods og ON o.id = og.order_id
26. INNER JOIN goods g ON og.goods_id = g.id
27. WHERE o.user_id = 1;

复制代码

什么是子查询？如何在MySQL中进行子查询？

子查询是指在一个查询语句中嵌套另一个查询语句的过程。子查询可以作为一个完整的查询来使用，也可以作为其他查询的一个子部分。子查询可以返回一个值或一组值，这些值可以用来过滤主查询中的结果，或者作为主查询的一部分。
在MySQL中，可以使用子查询来执行复杂的数据查询和分析。子查询可以嵌套在SELECT、INSERT、UPDATE 和 DELETE等语句中。
下面是一些常用的MySQL子查询语句：

1. # 1.基本子查询语法
4. SELECT column1, column2, …
5. FROM table1
6. WHERE column1 = (SELECT column1 FROM table2 WHERE condition);
9. # 2.带IN操作符的子查询
12. SELECT column1, column2, …
13. FROM table1
14. WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);
17. # 3.带EXISTS操作符的子查询
20. SELECT column1, column2, …
21. FROM table1
22. WHERE EXISTS (SELECT column1 FROM table2 WHERE condition);

复制代码

现在，我们来看一个具体的例子：假设我们有两张表，一张是学生表，另一张是成绩表。学生表包含学生的信息，包括姓名、学号、性别等。成绩表包含学生的成绩信息，包括学号、科目、成绩等。我们通过子查询来查询某个学生的平均成绩和最高成绩。

1. CREATE TABLE student (
2. id INT NOT NULL AUTO_INCREMENT,
3. name VARCHAR(50) NOT NULL,
4. gender ENUM('male', 'female') NOT NULL,
5. PRIMARY KEY (id)
6. );
9. CREATE TABLE score (
10. id INT NOT NULL AUTO_INCREMENT,
11. student_id INT NOT NULL,
12. course VARCHAR(50) NOT NULL,
13. grade INT NOT NULL,
14. PRIMARY KEY (id)
15. );
18. INSERT INTO student (name, gender) VALUES
19. ('Alice', 'female'),
20. ('Bob', 'male'),
21. ('Charlie', 'male');
24. INSERT INTO score (student_id, course, grade) VALUES
25. (1, 'math', 80),
26. (1, 'physics', 90),
27. (2, 'math', 85),
28. (2, 'physics', 95),
29. (3, 'math', 90),
30. (3, 'physics', 80);

复制代码

现在，我们来写一个子查询，查询Bob的平均成绩和最高成绩：

1. SELECT name,
2. (SELECT AVG(grade) FROM score s WHERE s.student_id = student.id) AS avg_grade,
3. (SELECT MAX(grade) FROM score s WHERE s.student_id = student.id) AS max_grade
4. FROM student
5. WHERE name = 'Bob';

复制代码

在这个例子中，我们使用了两个子查询来计算Bob的平均成绩和最高成绩。第一个子查询使用了AVG聚合函数来计算平均成绩，第二个子查询使用了MAX聚合函数来计算最高成绩。在子查询中，我们使用了WHERE子句来限制查询结果的范围，以便仅查询与当前学生相关的成绩。最后，我们把子查询的结果作为主查询的一部分输出。
总的来说，子查询是一个非常强大的工具，可以用来执行复杂的数据分析和查询。在MySQL中，子查询可以嵌套在其他查询中，以实现更灵活、更高效的数据查询和操作。
子查询和多表查询有什么区别？

子查询是在一个SQL语句中嵌套另一个完整的SELECT语句，用于提供一个参考对象或结果集，这个结果集将被嵌入到更大的查询中。它用于在一个查询中检索条件的子集，它可以被用于任意的SELECT, INSERT, UPDATE和DELETE语句中。
多表查询是指从多张表中检索信息的查询，它可以结合使用JOIN, UNION等等，以检索或计算来自多个表的特定数据集。多表查询经常用于大型数据库中，其中将数据分布在许多表中而不是一个表中。
主要区别在于：

• 子查询一般是单独的SQL语句，而多表查询通常涉及到多个表格。
  
• 子查询一般只返回一行，而多表查询可以返回多行。
  
• 子查询用于在一个查询中检索条件的子集，而多表查询则用于在多个表中联结，计算或筛选出需要的结果集。
  
• 子查询可以嵌套在其他语句中，而多表查询中嵌套其他SELECT语句时候通常使用JOIN等关键字。
  

在实际使用中，两种类型的查询可以结合使用，以实现更复杂和更灵活的数据库操作。
什么是联合查询？如何在MySQL中进行联合查询？

联合查询（Union）是将多个SELECT语句的结果合并成一个结果集输出的查询方式。它的关键字是UNION，它可以连接两个或多个SELECT语句的结果集，并去除重复数据。
在MySQL中进行联合查询，需要满足以下几个条件：

• 联合查询的每个SELECT语句的列数必须相同。
  
• 联合查询的每个SELECT语句的列名可以不同，但是需要用AS关键字定义别名。
  
• 联合查询的每个SELECT语句的列的数据类型必须相同或可自动转换。
  

1. CREATE TABLE `table1` (
2.   `id` int(11) NOT NULL AUTO_INCREMENT,
3.   `name` varchar(50) DEFAULT NULL,
4.   `age` int(11) DEFAULT NULL,
5.   PRIMARY KEY (`id`)
6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
8. INSERT INTO `table1` (`id`, `name`, `age`) VALUES
9. (1, '张三', 18),
10. (2, '李四', 19),
11. (3, '王五', 20);
13. CREATE TABLE `table2` (
14.   `id` int(11) NOT NULL AUTO_INCREMENT,
15.   `name` varchar(50) DEFAULT NULL,
16.   `age` int(11) DEFAULT NULL,
17.   PRIMARY KEY (`id`)
18. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
20. INSERT INTO `table2` (`id`, `name`, `age`) VALUES
21. (1, '张三', 18),
22. (2, '李四', 19),
23. (3, '赵六', 22);

复制代码

下面是一个SQL语句的示例，用于查询两个表的name和age列的联合结果，并去除重复数据： 

1. SELECT name, age FROM table1
2. UNION
3. SELECT name, age FROM table2;

复制代码

联合查询和多表查询有什么区别？

联合查询和多表查询都是SQL语言中用于从多个表中获取数据的查询方法，但它们的方式和作用略有不同。
联合查询（UNION）是指将两个或多个表中相同列的数据连接在一起，形成一个结果集。比如在两个表中查询出相同的列，可以使用UNION将它们合并成一个结果集。使用UNION操作时，查询语句必须返回相同的列名和数据类型，而且返回的结果集将自动去除重复行。
而多表查询（JOIN）是指用户可以使用SQL语言将多个表连接起来，形成一个虚拟的表，允许在其中进行查询或数据操作。其作用是将多张表中有关联的数据重新组合，从而可以更方便的查询和操作数据。在多表查询中有不同的连接类型，例如INNER JOIN（内连接）、LEFT JOIN（左连接）、RIGHT JOIN（右连接）等，它们表示不同的数据连接方式和结果集输出方式。
因此，联合查询和多表查询是两种不同的查询方式。联合查询适用于查询相同结构的数据集，而多表查询则适用于查询在多个相关表之间的数据。
什么是交叉查询？如何在MySQL中进行交叉查询？

交叉查询（Cross Join）是一种在数据库中组合两个或多个表的查询方法，即返回每个表的所有行的组合。交叉查询用于从多个表中检索数据，它返回两个（或更多）表的所有可能的组合。交叉查询不使用任何条件过滤数据，它仅仅是将数据组合在一起。
在MySQL中进行交叉查询，需要使用关键字“CROSS JOIN”或者省略掉关键字“CROSS”，直接使用“JOIN”。
下面是建立两个表的建表语句，我们用交叉查询找到这两个表的笛卡尔积：

1. CREATE TABLE table_a (
2. id int(11) NOT NULL AUTO_INCREMENT,
3. name varchar(10) DEFAULT NULL,
4. PRIMARY KEY (id)
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8. CREATE TABLE table_b (
9. id int(11) NOT NULL AUTO_INCREMENT,
10. age int(11) DEFAULT NULL,
11. PRIMARY KEY (id)
12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
16. INSERT INTO table_a (id, name) VALUES (1, '张三'), (2, '李四');
19. INSERT INTO table_b (id, age) VALUES (1, 20), (2, 30);

复制代码

交叉查询语句：

1. SELECT * FROM table_a CROSS JOIN table_b;
4. SELECT * FROM table_a JOIN table_b;

复制代码

交叉查询和多表查询有什么区别？

交叉查询和多表查询都用于从多个表中检索数据。但是，它们之间存在明显的区别。
交叉查询： 交叉查询也称为笛卡尔积查询。它是指从表中取出所有可能的组合并生成结果集。也就是说，交叉查询会返回一个表和另一个表中所有行的组合。如果有两个表，每个表都有10行，则结果集将包含100个行。交叉查询的结果集非常大，往往会消耗大量的时间和系统资源。通常不建议使用交叉查询。
多表查询： 多表查询指的是从多个表中检索有关联的数据。多表查询通常使用JOIN操作符将多个表连接起来，然后根据各个表之间的关系提取数据。JOIN操作符将返回一个表中的行与另一个表中的行相关联。根据JOIN操作符的类型，多表查询可以分为内连接、左连接、右连接和全连接。
总体来说，交叉查询的结果集包含了两个表中所有行的组合，而多表查询能够检索有相关联的数据。虽然交叉查询能够检索到所有数据，但它的结果往往特别冗长且不可操作。相反，多表查询更为常见，它能更精准地提取需要的数据，也能够更高效地操作数据。

来源:https://www.cnblogs.com/yyyyfly1/p/17471764.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！