怎么新建MySQL数据库

发布时间：2022-06-23 作者：admin
阅读：278
很多朋友都对“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中怎么实现左外连接，用法是怎样”的介绍了，感谢各位的阅读，如果大家想要了解更多相关的内容，欢迎关注群英网络，小编每天都会为大家更新不同的知识。
    

            

            

                
推荐阅读：

                
    
                                            
  1. 
                            
                                decode函数在sql中如何使用？作用你了解吗？
                            
                        
    2. 
                                            
  2. 
                            
                                sql语句group by怎样使用？一文带你搞懂
                            
                        
    3. 
                                            
  3. 
                            
                                SQL Server如何判断表是否存在？教你两个方法
                            
                        
    4. 
                                    

            

        


        

        
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：mmqy2019@163.com进行举报，并提供相关证据，查实之后，将立刻删除涉嫌侵权内容。


        

                            mariadb左外连接
            
        

    

    

                    
上一篇：MariaDB中正规表达式的用法是怎样，如何应用

                            
下一篇：MariaDB数据类型有几种，基本语法是什么 

            


    

        
相关阅读

        

            
        

    


                

                    

                        
                    

                


                

                    

                        二维码-群英
                        
长按识别二维码并关注微信

                        
更方便到期提醒、手机管理

                    

                    

                        

                            
7*24 全天候服务

                            
售前  400-678-4567
                            

                            
售后 
                                    0668-2555666

                            
                            
售后 
                                 400 678 4567
                            

                            
信息安全 
                                    0668-2555 118

                            
域名空间 
                                 3004329145
                            

                            
                        

                    

                


                

                    新用户注册，获新人礼包
                    管理控制台