最新要闻

广告

手机

光庭信息跌4.57% 2021上市超募11亿2022扣非降74% 时快讯

光庭信息跌4.57% 2021上市超募11亿2022扣非降74% 时快讯

 搜狐汽车全球快讯 | 大众汽车最新专利曝光：仪表支持拆卸 可用手机、平板替代-环球关注

搜狐汽车全球快讯 | 大众汽车最新专利曝光：仪表支持拆卸 可用手机、平板替代-环球关注

家电

  1. 首页
  2. 移动通信
  3. 内容

全球快资讯：【技术积累】Mysql中的SQL语言【技术篇】【二】

来源：博客园

什么是多表查询？如何在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_numFROM orders oINNER JOIN order_goods og ON o.id = og.order_idINNER 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_numFROM orders oINNER JOIN order_goods og ON o.id = og.order_idINNER JOIN goods g ON og.goods_id = g.idWHERE 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_numFROM orders oINNER JOIN order_goods og ON o.id = og.order_idINNER JOIN goods g ON og.goods_id = g.idWHERE o.user_id = 1;

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

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

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

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

# 1.基本子查询语法SELECT column1, column2, …FROM table1WHERE column1 = (SELECT column1 FROM table2 WHERE condition);# 2.带IN操作符的子查询SELECT column1, column2, …FROM table1WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);# 3.带EXISTS操作符的子查询SELECT column1, column2, …FROM table1WHERE 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_gradeFROM studentWHERE 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 table1UNIONSELECT 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操作符的类型，多表查询可以分为内连接、左连接、右连接和全连接。

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

关键词：