Mysql 语句复习-左右连接理解

mysql 内置语句

---

mysql> create database testdatabase;
Query OK, 1 row affected (0.02 sec)

mysql> use testdatabase
Database changed

mysql> show tables;
Empty set (0.00 sec)
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| my |
| mysql |
| performance_schema |
| sys |
| testdatabase |
±-------------------+

mysql> exit
Bye

准备表、数据

---

建学生表

CREATE TABLE Student(Sno VARCHAR(10),Sname NVARCHAR(10),Sage DATETIME,Ssex NVARCHAR(10));

INSERT INTO Student VALUES(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
INSERT INTO Student VALUES(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
INSERT INTO Student VALUES(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
INSERT INTO Student VALUES(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
INSERT INTO Student VALUES(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
INSERT INTO Student VALUES(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
INSERT INTO Student VALUES(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
INSERT INTO Student VALUES(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’) ;

####建课程表

CREATE TABLE Course(Cno VARCHAR(10),Cname NVARCHAR(10),Tno VARCHAR(10));
INSERT INTO Course VALUES(‘01’ , ‘语文’ , ‘02’);
INSERT INTO Course VALUES(‘02’ , ‘数学’ , ‘01’);
INSERT INTO Course VALUES(‘03’ , ‘英语’ , ‘03’);

建课程表

CREATE TABLE Teacher(Tno VARCHAR(10),Tname NVARCHAR(10));
INSERT INTO Teacher VALUES(‘01’ , ‘张三’);
INSERT INTO Teacher VALUES(‘02’ , ‘李四’);
INSERT INTO Teacher VALUES(‘03’ , ‘王五’);

建成绩表

CREATE TABLE SC(Sno VARCHAR(10),Cno VARCHAR(10),score DECIMAL(18,1));
INSERT INTO SC VALUES(‘01’ , ‘01’ , 80);
INSERT INTO SC VALUES(‘01’ , ‘02’ , 90);
INSERT INTO SC VALUES(‘01’ , ‘03’ , 99);
INSERT INTO SC VALUES(‘02’ , ‘01’ , 70);
INSERT INTO SC VALUES(‘02’ , ‘02’ , 60);
INSERT INTO SC VALUES(‘02’ , ‘03’ , 80);
INSERT INTO SC VALUES(‘03’ , ‘01’ , 80);
INSERT INTO SC VALUES(‘03’ , ‘02’ , 80);
INSERT INTO SC VALUES(‘03’ , ‘03’ , 80);
INSERT INTO SC VALUES(‘04’ , ‘01’ , 50);
INSERT INTO SC VALUES(‘04’ , ‘02’ , 30);
INSERT INTO SC VALUES(‘04’ , ‘03’ , 20);
INSERT INTO SC VALUES(‘05’ , ‘01’ , 76);
INSERT INTO SC VALUES(‘05’ , ‘02’ , 87);
INSERT INTO SC VALUES(‘06’ , ‘01’ , 31);
INSERT INTO SC VALUES(‘06’ , ‘03’ , 34);
INSERT INTO SC VALUES(‘07’ , ‘02’ , 89);
INSERT INTO SC VALUES(‘07’ , ‘03’ , 98);

查询语句

---

SELECT * FROM sc ;

SELECT * FROM sc WHERE cno =‘01’

SELECT * FROM sc WHERE cno = ‘02’

• SELECT * FROM sc WHERE cno =‘01’)a 与SELECT * FROM sc WHERE cno = ‘02’)b 等值连接

SELECT * FROM( SELECT * FROM sc WHERE cno =‘01’)a JOIN (SELECT * FROM sc WHERE cno = ‘02’)b ON a.sno= b.sno;

• SELECT * FROM sc WHERE cno =‘01’)a 与SELECT * FROM sc WHERE cno = ‘02’)b 右连接连接，结果会多显示语句中右表b（因为是右连接）中没有匹配的行

SELECT * FROM( SELECT * FROM sc WHERE cno =‘01’)a RIGHT JOIN (SELECT * FROM sc WHERE cno = ‘02’)b ON a.sno= b.sno;

• SELECT * FROM sc WHERE cno =‘01’)a 与SELECT * FROM sc WHERE cno = ‘02’)b 左连接，结果会多显示语句中左表a（因为是左连接）中没有匹配的行

SELECT * FROM( SELECT * FROM sc WHERE cno =‘01’)a LEFT JOIN (SELECT * FROM sc WHERE cno = ‘02’)b ON a.sno= b.sno;

---

以下验证结果是否如上我说，这是a左连接b，连接条件由a.sno= b.sno 换成b.sno= a.sno ，结果还是多显示a，即语句左边的没有匹配的行

SELECT * FROM( SELECT * FROM sc WHERE cno =‘02’)a LEFT JOIN (SELECT * FROM sc WHERE cno = ‘01’)b ON b.sno= a.sno;

换其他表验证，也是符合的。

SELECT * FROM( SELECT * FROM sc WHERE cno =‘01’)a RIGHT JOIN sc ON a.sno= sc.sno;

//以a表为条件，向右边的sc连接（对接），对接更好理解，能以sno对接正确的
对接，若sc中有而a表有没有，则显示a这行为空，sc显示该行实际记录。右连接一直是保留右边没有对接多余的数据。