MYSQL练习题

工作一年，发现自己sql写不好，所以从其他地方抄来一些题目，自己练习写了一下,写的都很基础

准备工作如下
–1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
–3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
–4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
*/
–创建测试数据
create table Student(s_id varchar(10),s_name nvarchar(10),s_birth datetime,s_sex 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(c_id varchar(10),c_name varchar(10),t_id varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);
create table Teacher(t_id varchar(10),t_name varchar(10));
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);
create table Score(s_id varchar(10),c_id varchar(10),s_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"课程成绩高的学生的信息及课程分数
1.1 遇事不会不要慌，先查一组数据：把01的分数查找出来　（单表查询）

SELECT
s.s_id,s.s_score as 课程01分数
from Score s
where s.c_id = 01 

执行结果：

1.2　在1.1的基础上把02的分数查出来加进去：（关联一张表出来）

SELECT
s.s_id,s.s_score as 课程01分数,s1.s_score as 课程02分数
from Score s,Score s1
where s.c_id = 01  
and s1.c_id = 02
and s.s_id = s1.s_id

执行结果：

1.3　在1.2的基础上继续添加，增加条件：01的分数比02的分数高：

SELECT
s.s_id,s.s_score as 课程01分数,s1.s_score as 课程02分数
from Score s,Score s1
where s.c_id = 01  
and s1.c_id = 02
and s.s_id = s1.s_id
and s.s_score > s1.s_score

执行结果：

1.4　接下来把学生信息添加进去：（再关联一张表）

SELECT
c.*,s.s_id,s.s_score as 课程01分数,s1.s_score as 课程02分数
from Score s,Score s1,Student c
where s.c_id = 01  
and s1.c_id = 02
and s.s_id = s1.s_id
and s.s_score > s1.s_score
and s.s_id = c.s_id

执行结果：

哈哈哈哈写到这里往回看一下，有没有发现问题，有数据缺失了哦，当某个学生有课程0１的分数但是没有课程0２的分数，这种数据会被漏掉，重新来：
1.1　 一定要先确认主表，这里主表是Stuent表：
先将所有学生信息查询出来：

Select * from Student

执行结果：

1.2　向查询结果中添加01课程的成绩（关联用于查询01成绩Score表）

SELECT
s.*,a.s_score as 01score
from Student s 
LEFT JOIN Score a on s.s_id = a.s_id and a.c_id = 01

执行结果：可以看到成绩为空的也查询出来了

1.3　向其中添加02课程的成绩（继续关联用于查询02成绩Score表）

SELECT
s.*,a.s_score as 01score,b