SQL基本用法一 Li.015

1. 建立表 4个表关系

1.学生表

Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

2.课程表

Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

3.教师表

Teacher(t_id,t_name) –教师编号,教师姓名

4.成绩表

Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

-- 学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
);

-- 课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

-- 教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);

-- 成绩表
CREATE TABLE `Score`(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);

1.1 插入对应的数据

-- 插入学生表测试数据
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' , '女');
insert into Student values('09' , '如花' , '1991-02-15' , '女');

-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
insert into Course values('04' , '体育' , '01');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
insert into Score values('09', '01', 50);
insert into Score values('09', '02', 40);
insert into Score values('09', '03', 90);
insert into Score values('09', '04', 99);

2. SQL练习

查询姓"张"老师的个数

select count(t_id) from Teacher where t_name like "张%";

查询1990年出生的学生名单

select s_id, s_name from Student where year(s_birth)=1990;

查询课程编号为"02"的总成绩

select c_id,sum(s_score) as '总成绩' from Score where c_id=02;

查询选了课程的学生人数思路学生唯一, 然后再计算总数

select count(distinct s_id) as "选课人数" from Score;

查询各科成绩最高和最低的分: 以如下的形式显示: 课程ID, 最高分, 最低分

select c_id as 课程ID, max(s_score) as 最高分, min(s_score) as 最低分 from Score group by c_id;

查询每门课程被选修的学生数

select c_id, count(s_id) from Score group by c_id;

查询男生,女生人数

select s_sex as 性别, count(s_id) as 人数 from Student group by s_sex;

查询平均成绩大于60分的学生的学号和平均成绩

学号与平均成绩都在Score表中可以获取到

select s_id as 学号, avg(s_score) as 平均成绩 from Score group by s_id having avg(s_score)>60;

查询至少选修两门课程的学生学号学生学号与学生选修课程数都在Score表中

select s_id as 学号,count(c_id) as 选修课程数 from Score group by s_id having count(c_id)>=2;

查询两门以上不及格课程的同学的学号及其平均成绩

2.1 这种做法是错误的, 只计算不及格课程的平均成绩, 如果还有其他课程及格就未计算在内了

select s_id as 学号, avg(s_score)as 平均成绩
from Score
where s_score <60
group by s_id
having count(c_id)>=2;

2.2 学生学号, 学生不及格课程总数, 平均成绩都在Score表中, 但是一条语句无法查询出来,不及格条件后的平均成绩是不正确(即是不及格课程的平均成绩),不是总成绩(包含及格与不及格课程成绩).

-- 查询到不及格课程总断>2的学生
select s_id from Score where s_score<60 group by s_id having count(c_id)>2;

-- 查询学生的总成绩计算的平均成绩
select s_id,avg(s_score) as avg_score from Score group by s_id;

-- 将2表内连接起来
select s1.s_id, s2.avg_score
from
(select s_id from Score where s_score<60 group by s_id having count(c_id)>2) as s1
join
(select s_id,avg(s_score) as avg_score from Score group by s_id) as s2
on s1.s_id=s2.s_id;

2.3 这次查询不及格课程数>1的学生,学号,平均成绩.(“09号有3门课程,三门成绩(40+50+90)/3=60”, 错误做法只计算不及格成绩(40+50)/2=45)


-- 错误做法
select s_id as 学号, avg(s_score)as 平均成绩
from Score
where s_score <60
group by s_id
having count(c_id)>1;

-- 正确做法
select s1.s_id, s2.avg_score
from
(select s_id from Score where s_score<60 group by s_id having count(c_id)>1) as s1
join
(select s_id,avg(s_score) as avg_score from Score group by s_id) as s2
on s1.s_id=s2.s_id;

查询名字完全一致的学生, 统计同名人数

-- 名字, 同名人数都在表Student中
select s_name, count(s_id) as 同名人数 from Student group by s_name having count(s_id)>1;

查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select c_id, avg(s_score) as '平均成绩' from Score group by c_id order by avg(s_score) ASC, c_id DESC;

查询不及格课程号和学生号, 课程号从大到小排列

-- 这些不及格课程号与学生号数据都在Score表中

select s_id, c_id, s_score from Score where s_score <60;

检索课程编号为“03”且分数小于60的学生学号，结果按按分数降序排列

select s_id, s_score from Score where c_id=03 and s_score<60;

统计每门课程的学生选修人数(超过5人的课程才统计), 要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select c_id, count(s_id) from Score group by c_id having count(s_id)>5 order by count(s_id) DESC, c_id ASC;

查询所有课程成绩小于60分的学生的学号、姓名. 学生成绩在表Score中, 学生姓名在表Student, 内连接2个表

-- 小于60分的学生号
select s_id, s_score where s_score<60;

-- 学生姓名

select s_id, s_name from Student;

-- 通过s_id内连接2个表, 2个成绩<60分会有2条数据, 可用group by去重

select sco.s_id,s.s_name from Score as sco join Student as s on sco.s_id=s.s_id where s_score<60 group by s_id;

-- 还可以这么写
select s_id,s_name from Student where s_id in (select s_id from Score where s_score<60);

查询没有学全所有课的学生的学号、姓名

2.4 全部课程数在Course中, 学生所选课程在Score中, 学生姓名在Student中.(隐藏条件学生所学课程一定属于全部课程中的一种)

理解是学没有学全所有课程的所有学号与学生姓名(注意是所有学生)

-- 查询全部课程数
select count(c_id) from Course;

-- 查询学生学号与课程数
select s_id, count(c_id) as count_cid from Score group by s_id;

-- 查询学生学号与学生姓名
select s_id, s_name from Student;

-- 左连Student与Score表, 为什么左连接, 因为是没有学无所有课程的学生, 学生一定会有, 学生不一定有选修的课程成绩.
select s.s_id, s.s_name from
Student as s
left join
Score as sco
on sco.s_id=s.s_id
group by s.s_id
having count(c_id)<(select count(c_id) from Course);

查询出只选修了两门课程的全部学生的学号和姓名

2.5 学生选修课程数在Score表中, 学生姓名在表Student中


-- 查询学生选择课程数为2的学生
select s_id,count(c_id) group by s_id having count(c_id)=2;

-- 查询学生姓名

select s_id, s_name from Student;

-- 左连接2个表

select s.s_id, s.s_name from
Student as s
left join
Score as sco
on s.s_id=sco.s_id
group by s.s_id having count(c_id)=2;

查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

2.6 学生选修课程与成绩在Score表中, 学生姓名在表Student中

-- 查询课程03,>80的学生成绩
select s_id, s_score, c_id from Score where c_id="03" and s_score>80;

-- 查询学生姓名

select s_id, s_name from Student;

-- 内连接2个表, 注意不是左右连接,有对应成绩的才检索,否则不检索
select s.s_id, s.s_name from
Student as s
join
Score as sco
on s.s_id=sco.s_id
and sco.s_score>80 and sco.c_id="03";

-- 其他写法

select s_id, s_name from Student where s_id in (select s_id from Score where s_score>80 and c_id=03);

查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号内连接 s1同学.s_id=s2同学.s_id, s1.c_id=“01”, s2.c_id=“02”, s1.s_score>s2.s_score, 最后去重

select s1.s_id from Score s1,Score s2 where s1.s_id=s2.s_id and s1.c_id="01" and s2.c_id="02" and s1.s_score>s2.s_score group by s1.s_id;

按平均成绩从高到低，按如下形式显示: 学生ID, 有效课程数, 有效平均分

select s_id '学生ID', count(c_id) '有效课程数', avg(s_score) '有效平均分' from Score group by s_id;

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数: 课程ID和课程名称

select c.c_id, c.c_name, sum(case when sco.s_score>=85 and sco.s_score<=100 then 1 else 0 end) as '[100-85]',
sum(case when sco.s_score>=70 and sco.s_score<85 then 1 else 0 end) as '[85-70]',
sum(case when sco.s_score>=60 and sco.s_score<70 then 1 else 0 end) as '[70-60]',
sum(case when sco.s_score<60 then 1 else 0 end) as '[<60]'
from Course c  join  Score sco on sco.c_id=c.c_id group by c.c_id;

查询出每门课程的及格人数和不及格人数

select c_id,
sum(case when s_score>=60 then 1 else 0 end) as '及格人数',
sum(case when s_score<60 then 1 else 0 end) as '不及格人数'
from Score group by c_id;

查询出每门课程的及格百分数

select c_id, sco.及格人数/(sco.及格人数+sco.不及格人数)*100 as '及格百分比' from (
select c_id,
sum(case when s_score>=60 then 1 else 0 end) as '及格人数',
sum(case when s_score<60 then 1 else 0 end) as '不及格人数'
from Score group by c_id
) as sco;

查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

2.7 注意是其他学生, 应该去除自己s_id=“01"的情况

select s_id,s_name from Student where s_id in (select s_id from Score where c_id in (select c_id from Score where s_id="02") group by s_id);

-- 内连接

select s.s_id, s.s_name from
Student s join Score sco on s.s_id=sco.s_id and sco.c_id in (select c_id from Score where s_id='01') and sco.s_id<>01 group by s.s_id;;

查询和“01”号同学所学课程完全相同的其他同学的学号

2.8 注意是其他学生, 应该去除自己s_id=“01"的情况

属于的"01"学生的课程数, 应该与"01"学生的总课程数一致才算完全一致.

-- 计算"01"号学生的课程数
select count(c_id) from Score where s_id="01";

-- 计算其他学生属于"01"课程的课程数

select s_id from Score where c_id in (select c_id from Score where s_id="01") and s_id<>01 group by s_id having count(c_id)=(select count(c_id) from Score where s_id="01");

把“SCORE”表中“张三”老师教的课的成绩都更改为此课程的平均成绩

-- 查询"张三老师名字和id"
select t_id, t_name from Teacher where t_name="张三";

-- 查询张三老师的课程

select t_id, c_id from Course where t_id in (select t_id from Teacher where t_name="张三");

-- 课程分组的平均成绩

select avg(s_score) as avg_score,c_id from Score group by c_id;

-- 张三老师课程与课程分组平均成绩内连接

select t.t_id, t.c_id, sco.avg_score from
(select t_id, c_id from Course where t_id in (select t_id from Teacher where t_name="张三")) t
join
(select avg(s_score) as avg_score,c_id from Score group by c_id) sco
on t.c_id=sco.c_id;

-- 内连接得到老师和学生课程成绩和每科的平均成绩
select * from Score as ss1 join (select sco.c_id as sco_cid,sco.avg_score as sco_avg_socre from
(select t_id, c_id from Course where t_id in (select t_id from Teacher where t_name="张三")) t
join
(select avg(s_score) as avg_score,c_id from Score group by c_id) sco
on t.c_id=sco.c_id) as ss2
on ss1.c_id=ss2.sco_cid;

-- 更新上面的内连接表
update Score as ss1 join (select sco.c_id as sco_cid,sco.avg_score as sco_avg_socre from
(select t_id, c_id from Course where t_id in (select t_id from Teacher where t_name="张三")) t
join
(select avg(s_score) as avg_score,c_id from Score group by c_id) sco
on t.c_id=sco.c_id) as ss2
on ss1.c_id=ss2.sco_cid
set ss1.s_score=ss2.sco_avg_socre;

按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示: 课程号，课程名平均成绩，及格百分数

-- 查询课程号与课程平均成绩
select c_id, avg(s_score) as avg_score from Score group by c_id;

-- 及格百分数

select s1.c_id, jige/all_chenji, s2.avg_score
from Score as s1
join (select sum(case when s_score >=60 then 1 else 0 end) as jige,
count(s_id)all_chenji,
avg(s_score) as avg_score,
c_id
from Score group by c_id
) as s2
on s1.c_id=s2.c_id group by s1.c_id
order by s2.avg_score ASC, jige/all_chenji DESC;

查询不同老师所教不同课程平均分从高到低显示

select t.t_name, avg(s_score) from Teacher as t
join
Course as c
on t.t_id=c.t_id
join
Score as sco
on c.c_id=sco.c_id group by sco.c_id
order by avg(s_score) DESC;

查询学生平均成绩及其名次

select s_id,avg(s_score),@curRank:=@curRank + 1
from Score s1,(select @curRank:=0) q group by s_id;

查询各科成绩前三名的记录（不考虑成绩并列情况）

待解决

查询所有学生的学号、姓名、选课数、总成绩

2.9 所有学生, 用左连接, 查到所有学生

-- 学号,姓名在Student表中, 学号,选课数,总成绩在Score表中, 左连接起来
select s.s_id, s.s_name,count(c_id),sum(s_score) from Student as s
left join
Score as sco
on s.s_id=sco.s_id
group by s_id;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

-- 学号,姓名在Student表中, 平均成绩,选课数在Score表中, 左连接起来
select s.s_id, s.s_name,avg(s_score) from Student as s
left join
Score as sco
on s.s_id=sco.s_id
group by s_id
having avg(s_score)>85;

查询有2门不同课程成绩相同的学生的学号、课程号、学生成绩

select s1.s_id,s1.c_id,s1.s_score from Score s1,Score s2
where s1.s_id=s2.s_id
and s1.s_score=s2.s_score
and s1.c_id<>s2.c_id
group by s1.s_id,s1.c_id;

查询选修了全部课程的学生信息

select * from Student where s_id in (select s_id from Score group by s_id having count(c_id)=(select count(c_id) from Course));

1. 建立表 4个表关系#

1.1 插入对应的数据#

2. SQL练习#

2.1 这种做法是错误的, 只计算不及格课程的平均成绩, 如果还有其他课程及格就未计算在内了#

2.2 学生学号, 学生不及格课程总数, 平均成绩都在Score表中, 但是一条语句无法查询出来,不及格条件后的平均成绩是不正确(即是不及格课程的平均成绩),不是总成绩(包含及格与不及格课程成绩).#

2.3 这次查询不及格课程数>1的学生,学号,平均成绩.(“09号有3门课程,三门成绩(40+50+90)/3=60”, 错误做法只计算不及格成绩(40+50)/2=45)#

2.4 全部课程数在Course中, 学生所选课程在Score中, 学生姓名在Student中.(隐藏条件学生所学课程一定属于全部课程中的一种)#

理解是学没有学全所有课程的所有学号与学生姓名(注意是所有学生)#

2.5 学生选修课程数在Score表中, 学生姓名在表Student中#

2.6 学生选修课程与成绩在Score表中, 学生姓名在表Student中#

2.7 注意是其他学生, 应该去除自己s_id=“01"的情况#

2.8 注意是其他学生, 应该去除自己s_id=“01"的情况#

属于的"01"学生的课程数, 应该与"01"学生的总课程数一致才算完全一致.#

待解决#

2.9 所有学生, 用左连接, 查到所有学生#