MariaDB中的内连接语句是什么 具体怎样应用
|
这篇文章给大家分享的是MariaDB中的内连接语句是什么，具体如何应用。小编觉得挺实用的，因此分享给大家做个参考，文中的介绍得很详细，而要易于理解和学习，有需要的朋友可以参考，接下来就跟随小编一起了解看看吧。 在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; 注： 上图中，两个图形的中间交叉蓝色部分就是连接的结果集。 为了方便演示，我们需要创建两个表，并插入一些数据 - 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 使用以下语法根据给定的参数条件连接两个表 - 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; 上面查询语句查询所有科目的考试分数，得到以下结果 - 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) 示例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; 执行上面查询语句，得到以下结果 - 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) 示例2 查询指定学生，并且成绩大于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; 执行上面查询语句，得到以下结果 - 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) 感谢各位的阅读，以上就是“MariaDB中的内连接语句是什么，具体如何应用”的内容了，经过本文的学习后，相信大家对MariaDB中的内连接语句是什么，具体如何应用都有更深刻的体会了吧。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
