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; 注： 上图中，两个图形的左侧表(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) 示例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; 上面查询语句查询所有科目的考试分数，得到以下结果 - 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) 以上就是关于“MariaDB中怎么实现左外连接，用法是怎样”的介绍了，感谢各位的阅读。 （编辑：汽车网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
