5

【技术积累】Mysql中的SQL高级技巧【一】 - 程序员天佑

 1 year ago
source link: https://www.cnblogs.com/yyyyfly1/p/17471764.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
1.【技术积累】Mysql中的SQL高级技巧【一】06-10

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

多表查询就是在一个查询中涉及到多个表，通过特定的关联方式连接多个表，并根据条件从中查询出所需要的数据。

多表查询是关系型数据库中最为基础的应用之一。

一个比较典型的例子就是，我们在查询一个订单的详细信息时，需要从订单表中获取订单编号，然后根据订单编号关联到商品表，获取商品信息，再根据商品信息关联到库存表中，查询商品数量。一个完整的查询操作需要同时查询多个表才能完整获取所需信息。

在MySQL中，进行多表查询的方式有多种，比较常用的有三种：内连接、左连接、右连接。其中：

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

建表语句

# 1.订单表


CREATE TABLE orders (
id int(11) NOT NULL AUTO_INCREMENT,
order_num bigint(20) NOT NULL,
order_time datetime NOT NULL,
user_id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO orders (id, order_num, order_time, user_id)
VALUES
(1, 1111111, '2022-11-11 12:00:00', 1),
(2, 2222222, '2022-10-10 10:00:00', 2),
(3, 3333333, '2022-09-09 09:00:00', 3);


# 2.商品表


CREATE TABLE goods (
id int(11) NOT NULL AUTO_INCREMENT,
goods_name varchar(20) NOT NULL,
price decimal(10,2) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO goods (id, goods_name, price)
VALUES
(1, '商品1', 99.00),
(2, '商品2', 399.00),
(3, '商品3', 199.00);


# 3.订单商品关联表


CREATE TABLE order_goods (
id int(11) NOT NULL AUTO_INCREMENT,
order_id int(11) NOT NULL,
goods_id int(11) NOT NULL,
goods_num int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO order_goods (id, order_id, goods_id, goods_num)
VALUES
(1, 1, 2, 2),
(2, 1, 3, 1),
(3, 2, 1, 3),
(4, 2, 2, 1),
(5, 3, 3, 2);

多表查询的SQL语句

# 1.查询订单表中所有订单的详细信息，包括订单编号、订单时间、用户ID、商品名称、商品价格、商品数量等。


SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
FROM orders o
INNER JOIN order_goods og ON o.id = og.order_id
INNER JOIN goods g ON og.goods_id = g.id;


# 2.查询订单表中所有订单的详细信息，包括订单编号、订单时间、用户ID、商品名称、商品价格、商品数量等。但只显示2019-12-10之后的订单。


SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
FROM orders o
INNER JOIN order_goods og ON o.id = og.order_id
INNER JOIN goods g ON og.goods_id = g.id
WHERE o.order_time >= '2019-12-10';


# 3.查询用户ID为1的用户所下的所有订单的详细信息，包括订单编号、订单时间、用户ID、商品名称、商品价格、商品数量等。


SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
FROM orders o
INNER JOIN order_goods og ON o.id = og.order_id
INNER JOIN goods g ON og.goods_id = g.id
WHERE o.user_id = 1;

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

子查询是指在一个查询语句中嵌套另一个查询语句的过程。子查询可以作为一个完整的查询来使用，也可以作为其他查询的一个子部分。子查询可以返回一个值或一组值，这些值可以用来过滤主查询中的结果，或者作为主查询的一部分。

在MySQL中，可以使用子查询来执行复杂的数据查询和分析。子查询可以嵌套在SELECT、INSERT、UPDATE 和 DELETE等语句中。

下面是一些常用的MySQL子查询语句：

# 1.基本子查询语法


SELECT column1, column2, …
FROM table1
WHERE column1 = (SELECT column1 FROM table2 WHERE condition);


# 2.带IN操作符的子查询


SELECT column1, column2, …
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);


# 3.带EXISTS操作符的子查询


SELECT column1, column2, …
FROM table1
WHERE EXISTS (SELECT column1 FROM table2 WHERE condition);

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

CREATE TABLE student (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
gender ENUM('male', 'female') NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE score (
id INT NOT NULL AUTO_INCREMENT,
student_id INT NOT NULL,
course VARCHAR(50) NOT NULL,
grade INT NOT NULL,
PRIMARY KEY (id)
);


INSERT INTO student (name, gender) VALUES
('Alice', 'female'),
('Bob', 'male'),
('Charlie', 'male');


INSERT INTO score (student_id, course, grade) VALUES
(1, 'math', 80),
(1, 'physics', 90),
(2, 'math', 85),
(2, 'physics', 95),
(3, 'math', 90),
(3, 'physics', 80);

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

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

在这个例子中，我们使用了两个子查询来计算Bob的平均成绩和最高成绩。第一个子查询使用了AVG聚合函数来计算平均成绩，第二个子查询使用了MAX聚合函数来计算最高成绩。在子查询中，我们使用了WHERE子句来限制查询结果的范围，以便仅查询与当前学生相关的成绩。最后，我们把子查询的结果作为主查询的一部分输出。

总的来说，子查询是一个非常强大的工具，可以用来执行复杂的数据分析和查询。在MySQL中，子查询可以嵌套在其他查询中，以实现更灵活、更高效的数据查询和操作。

子查询和多表查询有什么区别？

子查询是在一个SQL语句中嵌套另一个完整的SELECT语句，用于提供一个参考对象或结果集，这个结果集将被嵌入到更大的查询中。它用于在一个查询中检索条件的子集，它可以被用于任意的SELECT, INSERT, UPDATE和DELETE语句中。

多表查询是指从多张表中检索信息的查询，它可以结合使用JOIN, UNION等等，以检索或计算来自多个表的特定数据集。多表查询经常用于大型数据库中，其中将数据分布在许多表中而不是一个表中。

主要区别在于：

  1. 子查询一般是单独的SQL语句，而多表查询通常涉及到多个表格。

  2. 子查询一般只返回一行，而多表查询可以返回多行。

  3. 子查询用于在一个查询中检索条件的子集，而多表查询则用于在多个表中联结，计算或筛选出需要的结果集。

  4. 子查询可以嵌套在其他语句中，而多表查询中嵌套其他SELECT语句时候通常使用JOIN等关键字。

在实际使用中，两种类型的查询可以结合使用，以实现更复杂和更灵活的数据库操作。

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

联合查询（Union）是将多个SELECT语句的结果合并成一个结果集输出的查询方式。它的关键字是UNION，它可以连接两个或多个SELECT语句的结果集，并去除重复数据。

在MySQL中进行联合查询，需要满足以下几个条件：

  1. 联合查询的每个SELECT语句的列数必须相同。

  2. 联合查询的每个SELECT语句的列名可以不同，但是需要用AS关键字定义别名。

  3. 联合查询的每个SELECT语句的列的数据类型必须相同或可自动转换。

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `table1` (`id`, `name`, `age`) VALUES
(1, '张三', 18),
(2, '李四', 19),
(3, '王五', 20);

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `table2` (`id`, `name`, `age`) VALUES
(1, '张三', 18),
(2, '李四', 19),
(3, '赵六', 22);

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

SELECT name, age FROM table1
UNION
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”。

下面是建立两个表的建表语句，我们用交叉查询找到这两个表的笛卡尔积：

CREATE TABLE table_a (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE table_b (
id int(11) NOT NULL AUTO_INCREMENT,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO table_a (id, name) VALUES (1, '张三'), (2, '李四');


INSERT INTO table_b (id, age) VALUES (1, 20), (2, 30);

交叉查询语句：

SELECT * FROM table_a CROSS JOIN table_b;


SELECT * FROM table_a JOIN table_b;

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

交叉查询和多表查询都用于从多个表中检索数据。但是，它们之间存在明显的区别。

交叉查询： 交叉查询也称为笛卡尔积查询。它是指从表中取出所有可能的组合并生成结果集。也就是说，交叉查询会返回一个表和另一个表中所有行的组合。如果有两个表，每个表都有10行，则结果集将包含100个行。交叉查询的结果集非常大，往往会消耗大量的时间和系统资源。通常不建议使用交叉查询。

多表查询： 多表查询指的是从多个表中检索有关联的数据。多表查询通常使用JOIN操作符将多个表连接起来，然后根据各个表之间的关系提取数据。JOIN操作符将返回一个表中的行与另一个表中的行相关联。根据JOIN操作符的类型，多表查询可以分为内连接、左连接、右连接和全连接。

总体来说，交叉查询的结果集包含了两个表中所有行的组合，而多表查询能够检索有相关联的数据。虽然交叉查询能够检索到所有数据，但它的结果往往特别冗长且不可操作。相反，多表查询更为常见，它能更精准地提取需要的数据，也能够更高效地操作数据。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK