数据库教程-mariadb教程-MariaDB左外连接

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

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

LEFT OUTER JOIN也被称为LEFT JOIN

语法:

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

图形表示如下:

MariaDB左外连接

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

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

使用以下语法根据给定的参数条件连接两个表 – studentsscores,即查询学生信息和对应的成绩信息,如果没有成绩则使用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来填充。

示例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; 

执行上面查询语句,得到以下结果 –

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) 

示例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; 

执行上面查询语句,得到以下结果 –

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) 

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 mariadb教程 只为你绽放。

本文固定链接: http://kua0.com/2019/01/31/数据库教程-mariadb教程-mariadb左外连接/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注