MariaDB內連接
在MariaDB數據庫中，連接用於從多個表中檢索數據。當有兩個或兩個以上的表時，則需要使用連接實現。
MariaDB中有三種類型的連接：
- INNER JOIN (也稱爲
SIMPLE JOIN) - LEFT OUTER JOIN (也稱爲
LEFT JOIN) - RIGHT OUTER JOIN (也稱爲
RIGHT JOIN)
MariaDB INNER JOIN
MariaDB INNER JOIN是最常見的連接類型，它返回連接條件滿足的多個表中的所有行。
語法：
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;圖形表示如下：
注： 上圖中，兩個圖形的中間交叉藍色部分就是連接的結果集。
爲了方便演示，我們需要創建兩個表，並插入一些數據 -
USE testdb;
DROP table if exists students;
DROP table if exists subjects;
DROP table if exists scores;
-- 學生信息
CREATE TABLE students(
student_id INT NOT NULL AUTO_INCREMENT,
student_name VARCHAR(100) NOT NULL,
student_address VARCHAR(40) NOT NULL,
admission_date DATE,
PRIMARY KEY ( student_id )
);
-- 科目信息
CREATE TABLE subjects(
subject_id INT NOT NULL AUTO_INCREMENT,
subject_name VARCHAR(100) NOT NULL,
PRIMARY KEY ( subject_id )
);
-- 成績信息
CREATE TABLE scores(
id INT NOT NULL AUTO_INCREMENT,
student_id int(10) NOT NULL,
subject_id int(10) NOT NULL,
score float(4,1) DEFAULT NULL,
created_time datetime DEFAULT NULL,
PRIMARY KEY ( id )
);插入數據 -
--- 學生信息數據
INSERT INTO students
(student_id, student_name, student_address, admission_date)
VALUES(1,'Maxsu','Haikou','2017-01-07 00:00:00');
INSERT INTO students
(student_id, student_name, student_address, admission_date)
VALUES
(2,'JMaster','Beijing','2016-05-07 00:00:00'),
(3,'Mahesh','Guangzhou','2016-06-07 00:00:00'),
(4,'Kobe','Shanghai','2016-02-07 00:00:00'),
(5,'Blaba','Shenzhen','2016-08-07 00:00:00');
-- 科目信息數據
INSERT INTO subjects
(subject_id, subject_name)
VALUES(1,'計算機網絡基礎');
INSERT INTO subjects
(subject_id, subject_name)
VALUES(2,'高等數學');
INSERT INTO subjects
(subject_id, subject_name)
VALUES(3,'離散數學');
-- 分數
INSERT INTO scores
(student_id, subject_id, score, created_time)
VALUES(1,1,81,'2017-11-18 19:30:02');
INSERT INTO scores
(student_id, subject_id, score, created_time)
VALUES(1,2,89,NOW());
INSERT INTO scores
(student_id, subject_id, score, created_time)
VALUES(1,3,92,NOW());
INSERT INTO scores
(student_id, subject_id, score, created_time)
VALUES(2,2,95,NOW());
INSERT INTO scores
(student_id, subject_id, score, created_time)
VALUES(2,3,72,NOW());
INSERT INTO scores
(student_id, subject_id, score, created_time)
VALUES(3,1,59,NOW());
INSERT INTO scores
(student_id, subject_id, score, created_time)
VALUES(3,3,77,NOW());
INSERT INTO scores
(student_id, subject_id, score, created_time)
VALUES(4,2,81,NOW());當前studens表中的行記錄如下 -
MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 1 | Maxsu | Haikou | 2017-01-07 |
| 2 | JMaster | Beijing | 2016-05-07 |
| 3 | Mahesh | Guangzhou | 2016-06-07 |
| 4 | Kobe | Shanghai | 2016-02-07 |
| 5 | Blaba | Shenzhen | 2016-08-07 |
+------------+--------------+-----------------+----------------+
5 rows in set (0.00 sec)當前score表中的行記錄如下 -
MariaDB [testdb]> select * from scores;
+----+------------+------------+-------+---------------------+
| id | student_id | subject_id | score | created_time |
+----+------------+------------+-------+---------------------+
| 1 | 1 | 1 | 81.0 | 2017-11-18 19:30:02 |
| 2 | 1 | 2 | 89.0 | 2017-11-28 22:31:57 |
| 3 | 1 | 3 | 92.0 | 2017-11-28 22:31:58 |
| 4 | 2 | 2 | 95.0 | 2017-11-28 22:31:58 |
| 5 | 2 | 3 | 72.0 | 2017-11-28 22:31:58 |
| 6 | 3 | 1 | 59.0 | 2017-11-28 22:31:58 |
| 7 | 3 | 3 | 77.0 | 2017-11-28 22:31:58 |
| 8 | 4 | 2 | 81.0 | 2017-11-28 22:31:58 |
+----+------------+------------+-------+---------------------+
8 rows in set (0.00 sec)示例1
使用以下語法根據給定的參數條件連接兩個表 - subjects和scores：
SELECT subjects.subject_id, subjects.subject_name, scores.score
FROM subjects
INNER JOIN scores
ON subjects.subject_id = scores.subject_id
ORDER BY subjects.subject_id;上面查詢語句查詢所有科目的考試分數，得到以下結果 -
MariaDB [testdb]> SELECT subjects.subject_id, subjects.subject_name, scores.score
-> FROM subjects
-> INNER JOIN scores
-> ON subjects.subject_id = scores.subject_id
-> ORDER BY subjects.subject_id;
+------------+----------------+-------+
| subject_id | subject_name | score |
+------------+----------------+-------+
| 1 | 計算機網絡基礎 | 81.0 |
| 1 | 計算機網絡基礎 | 59.0 |
| 2 | 高等數學 | 89.0 |
| 2 | 高等數學 | 81.0 |
| 2 | 高等數學 | 95.0 |
| 3 | 離散數學 | 77.0 |
| 3 | 離散數學 | 92.0 |
| 3 | 離散數學 | 72.0 |
+------------+----------------+-------+
8 rows in set (0.00 sec)示例2
查詢每個學生的成績 -
SELECT students.student_id, students.student_name, scores.subject_id, scores.score
FROM students
INNER JOIN scores
ON students.student_id = scores.student_id
ORDER BY students.student_id;執行上面查詢語句，得到以下結果 -
MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
-> FROM students
-> INNER JOIN scores
-> ON students.student_id = scores.student_id
-> ORDER BY students.student_id;
+------------+--------------+------------+-------+
| student_id | student_name | subject_id | score |
+------------+--------------+------------+-------+
| 1 | Maxsu | 1 | 81.0 |
| 1 | Maxsu | 2 | 89.0 |
| 1 | Maxsu | 3 | 92.0 |
| 2 | JMaster | 2 | 95.0 |
| 2 | JMaster | 3 | 72.0 |
| 3 | Mahesh | 1 | 59.0 |
| 3 | Mahesh | 3 | 77.0 |
| 4 | Kobe | 2 | 81.0 |
+------------+--------------+------------+-------+
8 rows in set (0.00 sec)示例2
查詢指定學生，並且成績大於85分的信息 -
SELECT students.student_id, students.student_name, scores.subject_id, scores.score
FROM students
INNER JOIN scores
ON students.student_id = scores.student_id
WHERE students.student_name='Maxsu' AND scores.score > 85;執行上面查詢語句，得到以下結果 -
MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
-> FROM students
-> INNER JOIN scores
-> ON students.student_id = scores.student_id
-> WHERE students.student_name='Maxsu' AND scores.score > 85;
+------------+--------------+------------+-------+
| student_id | student_name | subject_id | score |
+------------+--------------+------------+-------+
| 1 | Maxsu | 2 | 89.0 |
| 1 | Maxsu | 3 | 92.0 |
+------------+--------------+------------+-------+
2 rows in set (0.00 sec)