学习笔记--亲测MySQL练习题（WIN10）

在上一章学习笔记--图解mySQL安装过程后，相信大家都已经学会了怎么在WIN10安装MySQL了，今天小文给大家带来一些MySQL练习题，据说学会这些练习题就可以完美撒花了！

MySQL版本：MySQL8.0

测试表格：

• Student (SId,Sname,Sage,Ssex)
• Course (CId,Cname,TId)
• Teacher (TId,Tname)
• SC (SId,CId,score)

建数据库，建表格：

Student：

CREATE DATABASE practise;
USE pratice;
CREATE TABLE Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(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('09' , '张三' , '1989-12-20' , '女');
INSERT INTO Student VALUES('10' , '李四' , '1989-12-25' , '女');
INSERT INTO Student VALUES('11' , '李四' , '1991-12-30' , '女');
INSERT INTO Student VALUES('12' , '赵六' , '1992-01-01' , '女');
INSERT INTO Student VALUES('13' , '孙七' , '1991-01-01' , '女');

Course：

CREATE TABLE Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');

Teacher：

CREATE TABLE Teacher(TId varchar(10),Tname varchar(10));
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

SC：

CREATE TABLE SC(SId varchar(10),CId 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);

练习题：

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

select * 
from (select * from SC where CId = '01') A, (select * from SC where CId = '02') B 
where A.SId = B.SId and A.Score > B.Score;

2.查询同时存在" 01 "课程和" 02 "课程的情况

select * 
from (select * from SC where CId = '01') A, (select * from SC where CId = '02') B 
where A.SId = B.SId;

3. 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

select *
from (select SId ,score from SC where CId = '01') A 
left join (select SId ,score from SC where CId = '02') B
on A.SId = B.SId

4. 查询不存在" 01 "课程但存在" 02 "课程的情况