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( 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显示该行实际记录。右连接一直是保留右边没有对接多余的数据。