devbox
我家小花儿
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MariaDB连接的相关操作_zstack mariadb 命令行链接

作者：我家小花儿 | 2024-04-22 19:02:12

赞

踩

zstack mariadb 命令行链接
  1. MariaDB数据库操作
  2. MariaDB表操作
  3. MariaDB表结构修改
  4. MariaDB增删改查操作
  5. MariaDB子句
  6. MariaDB聚合函数
  7. MariaDB连接
  8. MariaDB操作符
  9. MariaDB正则与条件
  10. MariaDB约束
  11. MariaDB导出与导入
  12. 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;

  • 1
  • 2
  • 3
  • 4

图形表示如下：

在这里插入图片描述

注： 上图中，两个图形的中间交叉蓝色部分就是连接的结果集。

为了方便演示，我们需要创建两个表，并插入一些数据 ：

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 )
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

插入数据 -

--- 学生信息数据
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());

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57

当前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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

当前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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
示例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;

  • 1
  • 2
  • 3
  • 4
  • 5

上面查询语句查询所有科目的考试分数，得到以下结果 ：

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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
示例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;

  • 1
  • 2
  • 3
  • 4
  • 5

执行上面查询语句，得到以下结果 ：

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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
示例3

查询指定学生，并且成绩大于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;

  • 1
  • 2
  • 3
  • 4
  • 5

执行上面查询语句，得到以下结果 -

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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

###　左外连接

LEFT OUTER JOIN用于返回ON条件中指定的左侧表中的所有行，并仅返回满足连接条件的其他表中的行。

LEFT OUTER JOIN也被称为LEFT JOIN。

语法：

SELECT columns  
FROM table1  
LEFT [OUTER] JOIN table2  
ON table1.column = table2.column;

  • 1
  • 2
  • 3
  • 4

图形表示如下：

在这里插入图片描述

注： 上图中，两个图形的左侧表(table1)和右侧表(table2)中间交叉蓝色部分，以及左侧表(table1)就是连接返回的结果集。

为了方便演示，我们需要创建两个表，并插入一些数据 -

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 )
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

插入数据 -

--- 学生信息数据
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());

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57

当前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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

当前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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
示例1

使用以下语法根据给定的参数条件连接两个表 - students和scores，即查询学生信息和对应的成绩信息，如果没有成绩则使用NULL值表示。

SELECT students.student_id, students.student_name, scores.subject_id, scores.score  
FROM students   
LEFT JOIN scores  
ON students.student_id = scores.student_id
ORDER BY students.student_id;

  • 1
  • 2
  • 3
  • 4
  • 5

上面查询语句查询所有科目的考试分数，得到以下结果 -

MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
    -> FROM students
    -> LEFT 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 |
|          5 | Blaba        |       NULL |  NULL |
+------------+--------------+------------+-------+
9 rows in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

上面示例的查询结果中，由于最后一行(student_id=5)的学生还没有任何信息，所以在使用LEFT JOIN连接后，右侧表(scores)相关列的值使用NULL来填充。

示例2

查询指定学生，并且成绩大于85分的信息 -

SELECT students.student_id, students.student_name, scores.subject_id, scores.score  
FROM students   
LEFT JOIN scores  
ON students.student_id = scores.student_id
WHERE students.student_name='Maxsu' AND scores.score > 85;

  • 1
  • 2
  • 3
  • 4
  • 5

执行上面查询语句，得到以下结果 -

MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
    -> FROM students
    -> LEFT 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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
示例3

查询没有考试成绩的学生信息(尚未录入) -

SELECT students.student_id, students.student_name, scores.subject_id, scores.score  
FROM students   
LEFT JOIN scores  
ON students.student_id = scores.student_id
WHERE scores.score IS NULL;

  • 1
  • 2
  • 3
  • 4
  • 5

执行上面查询语句，得到以下结果 -

MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
    -> FROM students
    -> LEFT JOIN scores
    -> ON students.student_id = scores.student_id
    -> WHERE scores.score IS NULL;
+------------+--------------+------------+-------+
| student_id | student_name | subject_id | score |
+------------+--------------+------------+-------+
|          5 | Blaba        |       NULL |  NULL |
+------------+--------------+------------+-------+
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

右外连接

RIGHT OUTER JOIN用于返回ON条件中指定的右表中的所有行，并且仅返回来自其他表中连接字段满足条件的行。

MariaDB RIGHT OUTER JOIN也被称为RIGHT JOIN。

语法：

SELECT columns  
FROM table1  
RIGHT [OUTER] JOIN table2  
ON table1.column = table2.column;

  • 1
  • 2
  • 3
  • 4

图形表示如下：

img

注： 上图中，两个图形的左侧表(table1)和右侧表(table2)中间交叉蓝色部分，以及右侧表(table2)就是连接返回的结果集。

为了方便演示，我们需要创建两个表，并插入一些数据 -

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 )
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

插入数据 -

--- 学生信息数据
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());

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57

当前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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

当前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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
示例1

使用以下语法根据给定的条件连接两个表 - students和scores，即查询学生信息和对应的成绩信息，如果没有成绩则使用NULL值表示。

SELECT scores.subject_id, scores.score,students.student_id, students.student_name
FROM scores
RIGHT JOIN students
ON students.student_id = scores.student_id
ORDER BY students.student_id;

  • 1
  • 2
  • 3
  • 4
  • 5

上面查询语句查询所有科目的考试分数以及学生，得到以下结果 -

MariaDB [testdb]> SELECT scores.subject_id, scores.score,students.student_id, students.student_name
    -> FROM scores
    -> RIGHT JOIN students
    -> ON students.student_id = scores.student_id
    -> ORDER BY students.student_id;
+------------+-------+------------+--------------+
| subject_id | score | student_id | student_name |
+------------+-------+------------+--------------+
|          1 |  81.0 |          1 | Maxsu        |
|          2 |  89.0 |          1 | Maxsu        |
|          3 |  92.0 |          1 | Maxsu        |
|          2 |  95.0 |          2 | JMaster      |
|          3 |  72.0 |          2 | JMaster      |
|          1 |  59.0 |          3 | Mahesh       |
|          3 |  77.0 |          3 | Mahesh       |
|          2 |  81.0 |          4 | Kobe         |
|       NULL |  NULL |          5 | Blaba        |
+------------+-------+------------+--------------+
9 rows in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

上面示例的查询结果中，由于最后一行(student_id=5)的学生还没有任何分数信息，所以在使用RIGHT JOIN连接后，左侧表(scores)相关列的值使用NULL来填充。可以看到右侧表(students)的每一行都有列出来了。

示例2

查询指定学生，并且成绩大于85分的信息 -

SELECT students.student_id, students.student_name, scores.subject_id, scores.score  
FROM scores
RIGHT JOIN students
ON students.student_id = scores.student_id
WHERE students.student_name='Maxsu' AND scores.score > 85;

  • 1
  • 2
  • 3
  • 4
  • 5

执行上面查询语句，得到以下结果 -

MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
    -> FROM scores
    -> RIGHT JOIN students
    -> 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)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
示例3

查询没有考试成绩的学生信息(尚未录入) -

SELECT students.student_id, students.student_name, scores.subject_id, scores.score  
FROM scores
RIGHT JOIN students
ON students.student_id = scores.student_id
WHERE scores.score IS NULL;

  • 1
  • 2
  • 3
  • 4
  • 5

执行上面查询语句，得到以下结果 -

MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
    -> FROM scores
    -> RIGHT JOIN students
    -> ON students.student_id = scores.student_id
    -> WHERE scores.score IS NULL;
+------------+--------------+------------+-------+
| student_id | student_name | subject_id | score |
+------------+--------------+------------+-------+
|          5 | Blaba        |       NULL |  NULL |
+------------+--------------+------------+-------+
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号