MariaDB中怎么实现左外连接,用法是怎样
Admin 2022-06-23 群英技术资讯 272 次浏览
MariaDB LEFT OUTER JOIN
用于返回ON
条件中指定的左侧表中的所有行,并仅返回满足连接条件的其他表中的行。
LEFT OUTER JOIN
也被称为LEFT JOIN
。
语法:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
图形表示如下:
注: 上图中,两个图形的左侧表(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 )
);
插入数据 -
--- 学生信息数据
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)
使用以下语法根据给定的参数条件连接两个表 - 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;
上面查询语句查询所有科目的考试分数,得到以下结果 -
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)
上面示例的查询结果中,由于最后一行(student_id=5
)的学生还没有任何信息,所以在使用LEFT JOIN
连接后,右侧表(scores
)相关列的值使用NULL
来填充。
查询指定学生,并且成绩大于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;
执行上面查询语句,得到以下结果 -
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)
查询没有考试成绩的学生信息(尚未录入) -
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;
执行上面查询语句,得到以下结果 -
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)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。
猜你喜欢
MariaDB中数据库创建有什么条件,方法是什么? 在MariaDB中,具备创建或删除数据库权限的,一般是超级用户或管理员才能够实现。而要创建数据库,我们有两种方式可以选择,下面我们详细的了解看看。
MariaDB如何安装?一些朋友可能会遇到这方面的问题,对此在下文小编向大家来讲解一下,内容详细,易于理解,希望大家阅读完这篇能有收获哦,有需要的朋友就往下看吧!
WHERE子句提供了一种在操作使用完全匹配时检索数据的方法。 在需要具有共享特征的多个结果的情况下,LIKE子句适应宽模式匹配。LIKE子句测试模式匹配,返回true或false。 用于比较的模式接受以下通配符:"%",其匹配字符数(0或更多); 和"_",它匹配单个字符
使用NULL值时,请记住它们是未知值。 它们不是空字符串或零,它们是有效值。 在表创建中,列规范允许将它们设置为接受空值,或拒绝它们。 只需使用NULL或NOT NULL子句。 这在缺少记录信息(如ID号)的情况下具有应用。
这篇文章给大家分享的是MariaDB中创建和删除临时表的方法,临时表在我们需要保存一些临时数据时是非常有用的,因此分享给大家做个参考,下文有详细的介绍,有需要的朋友可以参考,接下来就跟随小编来一起学习一下吧!
成为群英会员,开启智能安全云计算之旅
立即注册Copyright © QY Network Company Ltd. All Rights Reserved. 2003-2020 群英 版权所有
增值电信经营许可证 : B1.B2-20140078 粤ICP备09006778号 域名注册商资质 粤 D3.1-20240008