什麼是多表查詢？如何在MySQL中進行多表查詢？

多表查詢就是在一個查詢中涉及到多個表，通過特定的關聯方式連線多個表，並根據條件從中查詢出所需要的資料。

多表查詢是關係型資料庫中最為基礎的應用之一。

一個比較典型的例子就是，我們在查詢一個訂單的詳細資訊時，需要從訂單表中獲取訂單編號，然後根據訂單編號關聯到商品表，獲取商品資訊，再根據商品資訊關聯到庫存表中，查詢商品數量。一個完整的查詢操作需要同時查詢多個表才能完整獲取所需資訊。

在MySQL中，進行多表查詢的方式有多種，比較常用的有三種：內連線、左連線、右連線。其中：

內連線是一種常用的表連線方式，只有在兩個表中都具有相同的資料時，才會將兩個表進行連線。內連線包含四種：等值連線，非等值連線，自連線和多表連線。
左連線也是一種常用的表連線方式，左連線會保留左表中的所有資料，並將右表中符合條件的資料進行匹配，如果沒有匹配的資料則返回NULL。左連線只會返回左表中的資料。
右連線和左連線很相似，只是保留的是右表的資料。

建表語句

# 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等等，以檢索或計算來自多個表的特定資料集。多表查詢經常用於大型資料庫中，其中將資料分佈在許多表中而不是一個表中。

主要區別在於：

子查詢一般是單獨的SQL語句，而多表查詢通常涉及到多個表格。
子查詢一般只返回一行，而多表查詢可以返回多行。
子查詢用於在一個查詢中檢索條件的子集，而多表查詢則用於在多個表中聯結，計算或篩選出需要的結果集。
子查詢可以巢狀在其他語句中，而多表查詢中巢狀其他SELECT語句時候通常使用JOIN等關鍵字。

在實際使用中，兩種型別的查詢可以結合使用，以實現更復雜和更靈活的資料庫操作。

什麼是聯合查詢？如何在MySQL中進行聯合查詢？

聯合查詢（Union）是將多個SELECT語句的結果合併成一個結果集輸出的查詢方式。它的關鍵字是UNION，它可以連線兩個或多個SELECT語句的結果集，並去除重複資料。

在MySQL中進行聯合查詢，需要滿足以下幾個條件：

聯合查詢的每個SELECT語句的列數必須相同。
聯合查詢的每個SELECT語句的列名可以不同，但是需要用AS關鍵字定義別名。
聯合查詢的每個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操作符的型別，多表查詢可以分為內連線、左連線、右連線和全連線。

總體來說，交叉查詢的結果集包含了兩個表中所有行的組合，而多表查詢能夠檢索有相關聯的資料。雖然交叉查詢能夠檢索到所有資料，但它的結果往往特別冗長且不可操作。相反，多表查詢更為常見，它能更精準地提取需要的資料，也能夠更高效地運算元據。