MariaDB中的右外连接语法是哪些 如何应用
|
关于“MariaDB中的右外连接语法是什么，如何应用”的知识有一些人不是很理解，对此小编给大家总结了相关内容，具有一定的参考借鉴价值，而且易于学习与理解，希望能对大家有所帮助，有这个方面学习需要的朋友就继续往下看吧。 MariaDB RIGHT OUTER JOIN用于返回ON条件中指定的右表中的所有行，并且仅返回来自其他表中连接字段满足条件的行。 MariaDB RIGHT OUTER JOIN也被称为RIGHT JOIN。 语法： SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column; 注： 上图中，两个图形的左侧表(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 ) ); 插入数据 - --- 学生信息数据 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 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; 上面查询语句查询所有科目的考试分数以及学生，得到以下结果 - 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) 上面示例的查询结果中，由于最后一行(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; 执行上面查询语句，得到以下结果 - 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) 示例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; 执行上面查询语句，得到以下结果 - 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)算符 到此这篇关于“MariaDB中的右外连接语法是什么，如何应用”的文章就介绍到这了,更多相关内容请搜索群英网络以前的文章或继续浏览下面的相关文章，希望大家以后多多支持！ （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
