MySql教程

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

本文主要是介绍【技术积累】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_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操作符的类型,多表查询可以分为内连接、左连接、右连接和全连接。

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

这篇关于【技术积累】Mysql中的SQL高级技巧【一】的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!