50个常用的SQL语句练习

基本信息Student(`S#`,Sname,Sage,Ssex) 学生表

Course(`C#`,Cname,`T#`) 课程表

SC(`S#`,`C#`,score) 成绩表

Teacher(`T#`,Tname) 教师表

问题：

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

select a.`S#` from (select `S#`,score from SC where `C#`='001') a,(select `S#`,score

from SC where `C#`='002') b where a.score>b.score and a.`S#`=b.`S#`;

↑一张表中存在多对多情况的

2、查询平均成绩大于60分的同学的学号和平均成绩；

答案一：select `S#`,avg(score) from sc group by `S#` having avg(score) >60;

↑一对多，对组进行筛选

答案二：SELECT s ,scr

FROM (SELECT sc.`S#` s,AVG(sc.`score`) scr FROM sc GROUP BY sc.`S#`) rs

WHERE rs.scr>60 ORDER BY rs.scr DESC

↑嵌套查询可能影响效率

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

答案一：select Student.`S#`,Student.Sname,count(`C#`),sum(score) from Student left Outer join SC on Student.`S#`=SC.`S#` group by Student.`S#`,Sname

↑如果学生没有选课，仍然能查出，显示总分null（边界情况）

答案二：SELECT student.`S#`,student.`Sname`,COUNT(sc.`score`) 选课数,SUM(sc.`score`) 总分FROM Student,sc

WHERE student.`S#`=sc.`S#` GROUP BY sc.`S#`

↑如果学生没有选课，sc表中没有他的学号，就查不出该学生，有缺陷！

4、查询姓“李”的老师的个数；

select count(distinct(Tname)) from Teacher where Tname like '李%';

5、查询没学过“叶平”老师课的同学的学号、姓名；

select Student.`S#`,Student.Sname from Student where `S#` not in (select distinct(SC.`S#`) from SC,Course,Teacher where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname='叶平');

↑反面思考Step1：先找学过叶平老师课的学生学号，三表联合查询

Step2：在用not in 选出没学过的

Step3：distinct以防叶平老师教多节课；否则若某同学的几节课都由叶平教，学号就会出现重复

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#` and SC.`C#`='001'and exists( Select * from SC as SC_2 where SC_2.`S#`=SC.`S#` and SC_2.`C#`='002' );

↑注意目标字段`S#`关联

exists subquery 可以用in subquery代替，如下

select Student.`S#`,Student.Sname from Student,Sc where Student.`S#`=SC.`S#` and SC.`C#`='001'and sc.`s#` in ( select sc_2.`s#` from sc as sc_2 where sc_2.`c#`='002' );

↑不同之处，in subquery此处就不需要关联了

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select `S#`,Sname from Student

where `S#` in

(select `S#` from SC ,Course ,Teacher where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname='叶平' group by `S#`

having count(SC.`C#`)=(select count(`C#`) from Course,Teacher where Teacher.`T#`=Course.`T#` and Tname='叶平')

);

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

（太混乱）Select `S#`,Sname from (select Student.`S#`,Student.Sname,score ,(select score from SC SC_2 where SC_2.`S#`=Student.`S#` and SC_2.`C#`='002') score2 from Student,SC where Student.`S#`=SC.`S#` and `C#`='001') S_2 where score2

自己写的另一种方法：

SELECT student.`S#`,student.Sname FROM student

WHERE `S#` IN

( SELECT a.`S#` FROM

(SELECT * FROM sc WHERE `C#`='001') a ,

(SELECT * FROM sc WHERE `C#`='002') b

WHERE a.score>b.score AND a.`S#`=b.`S#`

) ;

↑子查询的应用方式与第1题类似，

在一对多关系表中，如果多组之间需要比较，可以将不同组抽出为几个子查询，再比较。

这里的“一”指课程编号。

9、查询所有课程成绩小于60分的同学的学号、姓名；

↓初始答案（效率最低）：

select `S#`,Sname from Student where `S#` not in (select Student.`S#` from Student,SC where Student.`S#`=SC.`S#` and score>60); （第二个select根本不需要联合查询）

↓改进简化版（效率更高）：

select `S#`,Sname from Student

where `S#` not in

(select distinct `S#` from SC where score>60); （从反面思考更简化）

↓自己写的另一种方法（效率其次，但有缺陷。边界情况：没有学任何课程的人，查不出来）：

SELECT Student.`S#`,Student.Sname FROM Student

WHERE `S#` IN

(SELECT `S#` FROM sc GROUP BY `S#` HAVING MAX(score)<60);

In 和not in 去构造，有时候查出来的结果并不一样，需要考虑目标字段`S#`是否在几个表中都有

10、查询没有学全所有课的同学的学号、姓名；

select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#` group by Student.`S#`,Student.Sname having count(`C#`) <(select count(`C#`) from Course);

↑有缺陷，没有选任何课的人查不出来。因为使用了关联查询，若存在关联不上的（一张表有，另一张表没有），就会遗漏。

select student.`s#`,student.sname from student

where student.`s#` not in

(select `s#` from sc group by `s#` having count(`c#`) = (select count(`c#`) from course));

↑可以查出没有选任何课的人，单表查询操作，不涉及关联。

11、查询至少有一门课与学号为“P1001”的同学所学相同的同学的学号和姓名；

select DISTINCT Student.`S#`,Sname from Student,SC where Student.`S#`=SC.`S#` and `C#` in (select `C#` from SC where `S#`='P1001') ; (存在性用in即可）

↑没有排除自身，↓把结果中的P1001自己去掉

12、查询至少学过学号为“P1001”同学所有一门课的其他同学学号和姓名；

select distinct SC.`S#`,Sname from Student,SC where Student.`S#`=SC.`S#` and `C#` in (select `C#` from SC where `S#`='001') AND Student.`s#` != 'P1001';←(绿色为补充，排除P1001本身)

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；

（有错误，很混乱）update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.`C#`=SC.`C#` ) from Course,Teacher where Course.`C#`=SC.`C#` and Course.`T#`=Teacher.`T#` and Teacher.Tname='叶平'); mysql报错，可能其他数据库能这么写

题目特点，把一张表的值查出来再插到这张表中，但实际不允许，会报错。

自己写：

UPDATE SC SET score=

(SELECT AVG(R.score) FROM

(SELECT score,`C#` FROM SC) R

WHERE R.`C#`=sc.`C#` GROUP BY R.`C#`)

WHERE sc.`C#` IN

(SELECT `C#` FROM Course,Teacher WHERE Course.`T#`=Teacher.`T#` AND Teacher.`Tname`='叶平'

)

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；

（错误,条件不够）select `S#` from SC where `C#` in (select `C#` from SC where `S#`='1002') group by `S#` having count(*)=(select count(*) from SC where `S#`='1002');

自己写：

15、删除学习“叶平”老师课的SC表记录；

(delete后能加表名吗？？)Delete SC from course ,Teacher where Course.`C#`=SC.`C#` and Course.`T#`= Teacher.`T#` and Tname='叶平';

自己写：delete from sc where `C#` in (select `C#` from Course,Teacher where Course.`T#`=Teacher.`T#` and Tname=’叶平’)；

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2号课的平均成绩；

(省略不看了)Insert SC select `S#`,'002',(Select avg(score) from SC where `C#`='002') from Student where `S#` not in (Select `S#` from SC where `C#`='002');

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

SELECT `S#` AS学生ID

,(SELECT score FROM SC WHERE SC.`S#`=t.`S#` AND `C#`='004') AS数据库

,(SELECT score FROM SC WHERE SC.`S#`=t.`S#` AND `C#`='001') AS企业管理

,(SELECT score FROM SC WHERE SC.`S#`=t.`S#` AND `C#`='006') AS英语

,COUNT(*) AS有效课程数, AVG(t.score) AS平均成绩

FROM SC AS t GROUP BY `S#` ORDER BY avg(t.score)

自己写：SELECT `S#` AS 学号ID,

(SELECT score FROM sc WHERE sc.`S#`=t.`S#` AND `C#`='001') AS 语文,

(SELECT score FROM sc WHERE sc.`S#`=t.`S#` AND `C#`='002') AS 数学,

(SELECT score FROM sc WHERE sc.`S#`=t.`S#` AND `C#`='003') AS 英语,

(SELECT score FROM sc WHERE sc.`S#`=t.`S#` AND `C#`='004') AS 政治,

COUNT(`C#`) AS 有效课程数,

A VG(score) AS 课程平均分

FROM SC AS t GROUP BY `S#` ORDER BY AVG(t.score) DESC;

必须要有自关联，否则返回不止1行

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT DISTINCT L.`C#` AS 课程ID,

Cname AS 课程名,

L.score AS 最高分,

R.score AS 最低分

FROM SC AS L ,SC AS R ,course

WHERE L.`C#`= R.`C#` AND course.`C#`=L.`C#`

AND L.score = (SELECT MAX(IL.score) FROM SC AS IL WHERE L.`C#` = IL.`C#` GROUP BY IL.`C#`)

AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.`C#` = IR.`C#` GROUP BY IR.`C#`);

自己写：

SELECT `C#` AS课程号,

(SELECT MAX(score) FROM sc WHERE sc.`C#`=t.`C#` GROUP BY `C#`) AS 最高分,

(SELECT MIN(score) FROM sc WHERE sc.`C#`=t.`C#` GROUP BY `C#`) AS 最低分

FROM sc t GROUP BY t.`C#` ;

19、按各科平均成绩从低到高和及格率的百分数从高到低

（问题不清，且运行不出）SELECT t.`C#` AS 课程号,max(https://www.wendangku.net/doc/6414207367.html,ame)AS 课程名,isnull(A VG(score),0) AS 平均成绩,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数FROM SC T,Course

where t.`C#`=course.`C#` GROUP BY t.`C#`

ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）

SELECT SUM(CASE WHEN `C#` ='001' THEN score ELSE 0 END)/SUM(CASE `C#` WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分,100 * SUM(CASE WHEN `C#` = '001' AND score >= 60 THEN 1 ELSE 0

END)/SUM(CASE WHEN `C#` = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数,SUM(CASE WHEN `C#` =

'002' THEN score ELSE 0 END)/SUM(CASE `C#` WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分,100 *

SUM(CASE WHEN `C#` = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN `C#` = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数,SUM(CASE WHEN `C#` = '003' THEN score ELSE 0 END)/SUM(CASE `C#` WHEN '003' THEN 1 ELSE 0 END) AS UML平均分,100 * SUM(CASE WHEN `C#` = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN `C#` = '003' THEN 1 ELSE 0 END) AS UML及格百分数,SUM(CASE WHEN `C#` = '004' THEN score ELSE 0 END)/SUM(CASE `C#` WHEN '004' THEN 1 ELSE 0 END) AS 数据库平

均分,100 * SUM(CASE WHEN `C#` = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN `C#` =

'004' THEN 1 ELSE 0 END) AS 数据库及格百分数FROM SC

修改后：

SELECT SUM(CASE WHEN `C#` ='001' THEN score ELSE 0 END)/SUM(CASE `C#` WHEN '001' THEN 1 ELSE 0 END) AS 语文平均分,

100 * SUM(CASE WHEN `C#` = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN `C#` = '001' THEN 1 ELSE 0 END) AS 语文及格率,

SUM(CASE WHEN `C#` ='002' THEN score ELSE 0 END)/SUM(CASE `C#` WHEN '002' THEN 1 ELSE 0 END) AS 数学平均分,

100 * SUM(CASE WHEN `C#` = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN `C#` = '002' THEN 1 ELSE 0 END) AS 数学及格率,

SUM(CASE WHEN `C#` ='003' THEN score ELSE 0 END)/SUM(CASE `C#` WHEN '003' THEN 1 ELSE 0 END) AS 英语平均分,

100 * SUM(CASE WHEN `C#` = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN `C#` = '003' THEN 1 ELSE 0 END) AS 英语及格率,

SUM(CASE WHEN `C#` ='004' THEN score ELSE 0 END)/SUM(CASE `C#` WHEN '004' THEN 1 ELSE 0 END) AS 政治平均分,

100 * SUM(CASE WHEN `C#` = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN `C#` = '004' THEN 1 ELSE 0 END) AS 政治及格率

FROM SC ;

21、查询不同老师所教不同课程平均分，按课程分数从高到低显示。包括教师ID、教师姓名、课程ID、课程名、平均成绩

SELECT max(Z.`T#`) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.`C#` AS 课程ＩＤ,MAX(https://www.wendangku.net/doc/6414207367.html,ame) AS 课程名称,A VG(Score) AS 平均成绩FROM SC AS T,Course AS C ,Teacher AS Z where T.`C#`=C.`C#` and C.`T#`=Z.`T#` GROUP BY C.`C#` ORDER BY A VG(Score) DESC

Max没必要加！

修改后：

SELECT Z.`T#` AS 教师ID,

Z.Tname AS 教师姓名,

C.`C#` AS 课程ID,

https://www.wendangku.net/doc/6414207367.html,ame AS 课程名,

A VG(Score) AS 平均成绩

FROM SC AS T,Course AS C ,Teacher AS Z

WHERE T.`C#`=C.`C#` AND C.`T#`=Z.`T#` GROUP BY C.`C#` ORDER BY A VG(Score) DESC;

22、查询如下课程成绩第3 名到第6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

Mysql 不支持top语句

SELECT DISTINCT top 3 SC.`S#` AS 学生学号,

Student.Sname AS 学生姓名,

T1.score AS 企业管理,

T2.score AS 马克思,

T3.score AS UML,

T4.score AS 数据库,

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) AS 总分

FROM Student,SC LEFT JOIN SC AS T1 ON SC.`S#` = T1.`S#`

AND T1.`C#` = '001' LEFT JOIN SC AS T2 ON SC.`S#` = T2.`S#`

AND T2.`C#` = '002' LEFT JOIN SC AS T3 ON SC.`S#` = T3.`S#`

AND T3.`C#` = '003' LEFT JOIN SC AS T4 ON SC.`S#` = T4.`S#`

AND T4.`C#` = '004'WHERE student.`S#`=SC.`S#`

AND ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) NOT IN

(

SELECT DISTINCT TOP 15 WITH TIES ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +

ISNULL(T4.score,0)

FROM sc LEFT JOIN sc AS T1 ON sc.`S#` = T1.`S#`

AND T1.`C#` = 'k1' LEFT JOIN sc AS T2 ON sc.`S#` = T2.`S#`

AND T2.`C#` = 'k2' LEFT JOIN sc AS T3 ON sc.`S#` = T3.`S#`

AND T3.`C#` = 'k3' LEFT JOIN sc AS T4 ON sc.`S#` = T4.`S#`

AND T4.`C#` = 'k4' ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) +

ISNULL(T4.score,0) DESC

);

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT SC.`C#` as 课程ID, Cname as 课程名称

,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS ‘[100 - 85]’

,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]

,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]

,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

FROM SC,Course where SC.`C#`=Course.`C#` GROUP BY SC.`C#`,Cname;

修改后：[90,100]指定要加单引号变成一个字符串！

SELECT c.`C#` AS 课程号,

c.`Cname` AS 课程名称,

SUM(CASE WHEN score BETWEEN 90 AND 100 THEN 1 ELSE 0 END) AS '[90,100]',

SUM(CASE WHEN score BETWEEN 75 AND 89 THEN 1 ELSE 0 END) AS '[89,75]',

SUM(CASE WHEN score BETWEEN 60 AND 74 THEN 1 ELSE 0 END) AS '[74,60]',

SUM(CASE WHEN score <60 THEN 1 ELSE 0 END) AS '[59,-]'

FROM sc s,course c

WHERE s.`C#`=c.`C#` GROUP BY s.`C#`;

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

SELECT 1+(SELECT COUNT( distinct 平均成绩) FROM (SELECT `S#`,AVG(score) AS 平均成绩FROM SC GROUP BY `S#` ) AS T1 WHERE 平均成绩> T2.平均成绩) as 名次, `S#` as 学生学号,平均成绩

FROM (SELECT `S#`,AVG(score) 平均成绩FROM SC GROUP BY `S#` ) AS T2 ORDER BY 平均成绩desc; 修改后：

SELECT 1+(

SELECT COUNT(DISTINCT 平均成绩) FROM

(SELECT `S#`,A VG(score) AS 平均成绩FROM SC GROUP BY `S#`) AS T1

WHERE 平均成绩> T2.平均成绩

) AS 名次,

学生学号,

平均成绩

FROM ( SELECT `S#` AS 学生学号,A VG(score) 平均成绩FROM SC GROUP BY `S#` ) AS T2

ORDER BY 平均成绩DESC;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT t1.`S#` as 学生ID,t1.`C#` as 课程ID,Score as 分数

FROM SC t1

WHERE score IN (SELECT TOP 3 score FROM SC WHERE t1.`C#`= `C#` ORDER BY score DESC ) ORDER BY t1.`C#`;

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

select `C#`,count(`S#`) from sc group by `C#`;

加课程名：

SELECT s.`C#`,Cname,COUNT(DISTINCT`S#`) 选课人数

FROM course c,sc s WHERE s.`C#`=c.`C#`

GROUP BY s.`C#`;

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

select SC.`S#`,Student.Sname,count(`C#`) AS 选课数from SC ,Student

where SC.`S#`=Student.`S#` group by SC.`S#` ,Student.Sname having count(`C#`)=1;

自己写：

SELECT student.`S#`,Sname,选课数

FROM student,

(SELECT sc.`S#`,COUNT(sc.`C#`) 选课数FROM sc GROUP BY sc.`S#`) AS 选课统计

WHERE student.`S#`=选课统计.`S#` AND 选课数=1

;

28、查询男生、女生人数

Select Ssex, count(Ssex) as 人数

from Student

group by Ssex

29、查询姓“张”的学生名单

SELECT Sname FROM Student WHERE Sname like '张%';

30、查询同名同性别学生名单，并统计同名人数

select Sname,count(*) from Student group by Sname having count(*)>1;

加性别：

SELECT sname,ssex,COUNT(*) 相同人数FROM student GROUP BY Sname,Ssex HA VING COUNT(*)>1;

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

select Sname, CONVERT(char (11),DA TEPART(year,Sage)) as age

from student where CONVERT(char(11),DATEPART(year,Sage))='1981';

Mysql中没有DATEPART，使用DATE_FORMAT

SELECT `S#`,Sname,DATE_FORMAT(Sage,'%Y') 出生日期,2016-DATE_FORMAT(Sage,'%Y') 年龄FROM student;

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

Select `C#`,Avg(score) from SC group by `C#` order by Avg(score),`C#` DESC ;

优先排序二级排序

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

select Sname,SC.`S#` ,avg(score) from Student,SC

where Student.`S#`=SC.`S#` group by SC.`S#` having avg(score)>80;

34、查询课程名称为“数学”，且分数低于60的学生姓名和分数，若没选这节课，用0代替。Select Sname,score

from Student,SC,Course

where SC.`S#`=Student.`S#` and SC.`C#`=Course.`C#` and https://www.wendangku.net/doc/6414207367.html,ame='数据库'and score ;

原答案并不能显示成绩为零的记录，根本就不存在

怎么写？

用student left join 。。。

35、查询所有学生的选课情况；

SELECT SC.`S#`, SC.`C#`, Sname, Cname

FROM SC,Student,Course

where SC.`S#`=Student.`S#` and SC.`C#`=Course.`C#` ;

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

SELECT distinct student.`S#`,student.Sname,SC.`C#`,SC.score

FROM student,Sc

WHERE SC.score>=70 AND SC.`S#`=student.`S#`;

补充后：

SELECT Sname,Cname,score

FROM Student,Course,score

WHERE SC.`S#`=Student.`S#` AND SC.`C#`=Course.`C#` AND score>70;

37、查询P1004同学不及格的课程，并按课程号从大到小排列

select `C#` 陆华不及格课程from sc where S#=’P1004’ score <60 order by `C#` ;

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

SELECT student.`S#`,student.`Sname`

FROM student,course,sc

WHERE SC.`S#`=Student.`S#` AND SC.`C#`=Course.`C#` AND sc.`C#`='003' AND score>80;

39、求选了’001’课程的学生人数

select count(*) from sc where `C#`=’001’;

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

正确答案：四表联合

SELECT Student.Sname,score 最高分

FROM Student,SC,Course C,Teacher

WHERE Student.`S#`=SC.`S#` AND SC.`C#`=C.`C#` AND C.`T#`=Teacher.`T#`

AND Teacher.Tname='叶平'

AND SC.score=(SELECT MAX(score) FROM SC WHERE `C#`=C.`C#` );

说明：sc的别名只能在外侧使用，内测使用而外侧不用，只能查出来一门课程，效果并不是等价的。

自己写的有错误：人名只能为各组group的第一行，原因：？

SELECT Sname,Cname 叶平老师课程,最高分

FROM student,course,

(SELECT sc3.`S#`,sc3.`C#`,MAX(score) 最高分FROM sc sc3 GROUP BY sc3.`C#`) rs

WHERE rs.`S#`=Student.`S#` AND course.`C#`=rs.`C#`

AND rs.`C#` IN

(SELECT sc2.`C#` FROM course,sc sc2,teacher

WHERE course.`C#`=sc2.`C#`AND teacher.`T#`=course.`T#`

AND Tname='叶平' GROUP BY sc2.`C#`

)

41、查询各个课程及相应的选修人数

SELECT `C#` ,COUNT(*) FROM sc GROUP BY `C#`;

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

（没有意义）select distinct A.`S#`,B.score from SC A ,SC B where A.Score=B.Score and A.`C#` <>B.`C#` ;

43、查询每门功课成绩最好的前两名

（不支持top语句）SELECT t1.`S#` as 学生ID,t1.`C#` as 课程ID,Score as 分数FROM SC t1

WHERE score IN (SELECT TOP 2 score FROM SC WHERE t1.`C#`= `C#` ORDER BY score DESC ) ORDER BY t1.`C#`;

44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select `C#` as 课程号,count(*) as 人数from sc group by `C#` order by count(*) desc,`C#`;

45、检索至少选修两门课程的学生学号

SELECT `S#`,COUNT(*) 选课数FROM sc GROUP BY `S#` HA VING COUNT(*)>2;

46、查询每个学生都选修的课程的课程号和课程名

(错误，查询出来结果是所有课程，因为他们都被选修过，不管多少人)

select `C#`,Cname from Course where `C#` in (select `C#` from sc group by `C#`)

修改后：

SELECT rs.`C#` FROM

(SELECT `C#`,COUNT(*) 选课数量FROM sc GROUP BY `C#`) rs

WHERE rs.选课数量=(SELECT COUNT(*) FROM student);

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

三表联合即可，不必太复杂

select Sname from Student where `S#` not in

(select `S#` from Course,Teacher,SC

where Course.`T#`=Teacher.`T#` and SC.`C#`=course.`C#` and Tname='叶平');

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

SELECT sc.`S#`,AVG(score) 平均成绩

FROM sc

WHERE `S#` IN (SELECT `S#`FROM sc WHERE score<60 GROUP BY `S#` HA VING COUNT(*)>2 )

GROUP BY sc.`S#`;

49、检索“004”课程分数小于60的学生，按分数降序排列的同学学号

select `S#` from SC

where `C#`='004' and score <60

order by score desc;

50、删除“P1002”同学的“001”课程的成绩delete from Sc where `S#`='P1001' and `C#`='001';

常用SQL语句大全

常用SQL语句大全 一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 DROP database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2…from tab_old definition only 5、说明：删除新表 DROP table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键：Alter table tabname DROP primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：DROP index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：DROP view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1 11、说明：几个高级查询运算词

精典的SQL语句

精典的SQL语句ffice ffice" /> 1. 行列转换--普通 假设有张学生成绩表(CJ)如下 Name Subject Result 张三语文80 张三数学90 张三物理85 李四语文85 李四数学92 李四物理82 想变成 姓名语文数学物理 张三80 90 85 李四85 92 82 declare @sql var char(4000) set @sql = 'select Name' select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Res ult end) ['+Subject+']' from (select distinct Subject from CJ) as a select @sql = @sql+' from test group by name' exec(@sql) 2. 行列转换--合并 有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1 如何化成表B BR>id pid 1 1,2,3 2 1,2 3 1 创建一个合并的函数 create function fmerg(@id int) returns var char(8000) as begin declare @str var char(8000) set @str=''

select @str=@str+','+cast(pid as var char) from 表A where id=@id se t @str=right(@str,len(@str)-1) return(@str) End go --调用自定义函数得到结果 select distinct id,dbo.fmerg(id) from 表A 3. 如何取得一个数据表的所有列名 方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOL UMN表中取得该数据表的所有列名。 SQL语句如下： declare @objid int,@objname char(40) set @objname = 'tablename' select @objid = id from sysobjects where id = object_id(@objname) select 'Column_name' = name from syscolumns where id = @objid order b y colid 是不是太简单了？呵呵不过经常用阿. 4. 通过SQL语句来更改用户的密码 修改别人的,需要sysadmin role EXEC sp_password NULL, 'newpassword', 'User' 如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa 5. 怎么判断出一个表的哪些字段不允许为空？ select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE= 'NO' and TABLE_NAME=tablename 6. 如何在数据库里找到含有相同字段的表？ a. 查已知列名的情况 SELECT https://www.wendangku.net/doc/6414207367.html, as TableName,https://www.wendangku.net/doc/6414207367.html, as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type='U' AND https://www.wendangku.net/doc/6414207367.html,='你的字段名字' b. 未知列名查所有在不同表出现过的列名 Select https://www.wendangku.net/doc/6414207367.html, As tablename,https://www.wendangku.net/doc/6414207367.html, As columnname From syscolumns s1, sysobjects o Where s1.id = o.id And o.type = 'U' And Exists ( Select 1 From syscolumns s2 Where https://www.wendangku.net/doc/6414207367.html, = https://www.wendangku.net/doc/6414207367.html, And s1.id <> s2.id ) 7. 查询第xxx行数据 假设id是主键： select *

ORACLE常用SQL语句大全

ORACLE常用SQL语句大全 一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not nul l],..) 根据已有的表创建新表： A：select * into table_new from table_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only<仅适用于Oracle> 5、说明：删除表 drop table tablename

6、说明：增加一个列，删除一个列 A：alter table tabname add column col type B：alter table tabname drop column colname 注：DB2DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、添加主键： Alter table tabname add primary key(col) 删除主键： Alter table tabname drop primary key(col) 8、创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、创建视图：create view viewname as select statement 删除视图：drop view viewname 10、几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最小：select min(field1) as minvalue from table1 11、几个高级查询运算词 A：UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B：EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C：INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。 注：使用运算词的几个查询结果行必须是一致的。 12、使用外连接

SQL查询语句大全集锦(超经典)

SQL查询语句大全集锦 MYSQL查询语句大全集锦 一、简单查询 简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的 表或视图、以及搜索条件等。 例如，下面的语句查询testtable表中姓名为“张三”的nickname字段和email字段。 复制内容到剪贴板 代码:SELECT `nickname`,`email`FROM `testtable`WHERE `name`='张三' (一) 选择列表 选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。 1、选择所有列 例如，下面语句显示testtable表中所有列的数据： 复制内容到剪贴板 代码:SELECT * FROM testtable 2、选择部分列并指定它们的显示次序 查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。 例如： 复制内容到剪贴板 代码:SELECT nickname,email FROM testtable 3、更改列标题 在选择列表中，可重新指定列标题。定义格式为： 列标题=列名 列名列标题 如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列 标题：

复制内容到剪贴板 代码:SELECT 昵称=nickname,电子邮件=email FROM testtable 4、删除重复行 SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认 为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。 5、限制返回的行数 使用TOP n [PERCENT]选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT时，说明n是 表示一百分数，指定返回的行数等于总行数的百分之几。 例如： 复制内容到剪贴板 代码:SELECT TOP 2 * FROM `testtable` 复制内容到剪贴板 代码:SELECT TOP 20 PERCENT * FROM `testtable` (二) FROM子句 FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图， 它们之间用逗号分隔。 在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列 所属的表或视图。例如在usertable和citytable表中同时存在cityid列，在查询两个表中的cityid时应使用下面语句格式加以限定： 复制内容到剪贴板 代码:SELECT `username`,citytable.cityid FROM `usertable`,`citytable` WHERE usertable.cityid=citytable.cityid在FROM子句中可用以下两种格式为表或视图指定别名： 复制内容到剪贴板 代码:表名 as 别名 表名别名例如上面语句可用表的别名格式表示为： 复制内容到剪贴板

SQL常用语句+举例

SQL 常用语句+举例 相关表： 1. distinct: 剔除重复记录 例：select distinct stroe_name from Store_information 结果： 2. And / or: 并且/或 例：在表中选出所有sales 高于$1000或是sales 在$275及$500之间的记录 Select store_name ,sales from Store_information Where sales>1000 Or (sales>275 and sales <500) 3. 例：在表中查找store_name 包含 Los Angeles 或San Diego 的记录 Select * from Store_information where store_name in (‘Los Angeles ’,’San Diego ’) 结果： 4. Between : 可以运用一个范围抓出表中的值

与in 的区别：in 依照一个或数个不连续的值的限制抓出表中的值 例：查找表中介于Jan-06-1999 及Jan-10-1999 中的记录 Select * from Store_information where date between ‘Jan-06-1999’ and ‘Jan-10-1999’ 结果： 5. Like : 让我们依据一个套式来找出我们要的记录 套式通常包含： ’A_Z ’: 所有以A 开头，中间包含一个字符，以Z 结尾的字串 ’ABC%’: 所有以ABC 起头的字串 ’%XYZ ’: 所有以XYZ 结尾的字串 ’%AN%’: 所有包含AN 的字串 例：Select * from Store_information where store_name like ‘%An%’ 结果： 6. Order by: 排序，通常与ASC （从小到大，升序）、DESC （从大到小，降序）结合使用 当排序字段不止一个时，先依据字段1排序，当字段1有几个值相同时，再依据字段2排序 例：表中sales 由大到小列出Store_information 的所有记录 Select Store_name, sales,date from Store_information order by sales desc 结果： 7. 函数：AVG （平均值）、COUNT （计数）、MAX （最大值）、MIN （最小值）、SUM(求和) 语句：select 函数名(字段名) from 表名 例：求出sales 的总和 Select sum(sales) from Store_information 结果 8. COUNT （计数） 例：找出Store_information 表中 有几个store_name 值不是空的记录

经典SQL语句大全

一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1. dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1

数据库基本SQL语句大全

数据库基本SQL语句大全 数据库基本----SQL语句大全 一、基础 1、说明：创建数据库 Create DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2…from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键：Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围

50个常用sql语句实例(学生表 课程表 成绩表 教师表)

Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2)) 前面加一列序号： if exists(select table_name from information_schema.tables where table_name='Temp_Table') drop table Temp_Table go select 排名=identity(int,1,1),* INTO Temp_Table from Student go select * from Temp_Table go drop database [ ] --删除空的没有名字的数据库 问题： 1、查询“”课程比“”课程成绩高的所有学生的学号； select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#; 2、查询平均成绩大于分的同学的学号和平均成绩； select S#,avg(score) from sc group by S# having avg(score) >60; 3、查询所有同学的学号、姓名、选课数、总成绩； select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S#,Student.Sname from Student

数据库经典SQL语句大全

数据库经典SQL语句大全 篇一：经典SQL语句大全 下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先，简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk'，'testBack'， 'c:mssql7backupMyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key]，col2 type2 [not null]，..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1，col2? from tab_old definition only 5、说明： 删除新表： tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col?.) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。

大数据库基本SQL语句大全

数据库基本_SQL语句大全 学会数据库是很实用D~~记录一些常用的sql语句...有入门有提高有见都没见过的...好全...收藏下... 其实一般用的就是查询,插入,删除等语句而已....但学学存储过程是好事...以后数据方面的东西就不用在程序里搞喽..而且程序与数据库只要一个来回通讯就可以搞定所有数据的操作.... 一、基础 1、说明：创建数据库 Create DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice ‘disk‘, ‘testBack‘, ‘c:\mssql7backup\MyNwind_1.dat‘ --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2…from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col)说明：删除主键：Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围

50个经典sql语句总结

一个项目涉及到的50个Sql语句(整理版) --1.学生表 Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表 Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号 --3.教师表 Teacher(T,Tname) --T 教师编号,Tname 教师姓名 --4.成绩表 SC(S,C,score) --S 学生编号,C 课程编号,score 分数 */ --创建测试数据 create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男') insert into Student values('02' , N'钱电' , '1990-12-21' , N'男') insert into Student values('03' , N'孙风' , '1990-05-20' , N'男') insert into Student values('04' , N'李云' , '1990-08-06' , N'男') insert into Student values('05' , N'周梅' , '1991-12-01' , N'女') insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女') insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女') insert into Student values('08' , N'王菊' , '1990-01-20' , N'女') create table Course(C varchar(10),Cname nvarchar(10),T varchar(10)) insert into Course values('01' , N'语文' , '02') insert into Course values('02' , N'数学' , '01') insert into Course values('03' , N'英语' , '03') create table Teacher(T varchar(10),Tname nvarchar(10)) insert into Teacher values('01' , N'张三') insert into Teacher values('02' , N'李四') insert into Teacher values('03' , N'王五') create table SC(S varchar(10),C 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)

50个常用的SQL语句练习

基本信息Student(`S#`,Sname,Sage,Ssex) 学生表 Course(`C#`,Cname,`T#`) 课程表 SC(`S#`,`C#`,score) 成绩表 Teacher(`T#`,Tname) 教师表 问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.`S#` from (select `S#`,score from SC where `C#`='001') a,(select `S#`,score from SC where `C#`='002') b where a.score>b.score and a.`S#`=b.`S#`; ↑一张表中存在多对多情况的 2、查询平均成绩大于60分的同学的学号和平均成绩； 答案一：select `S#`,avg(score) from sc group by `S#` having avg(score) >60; ↑一对多，对组进行筛选 答案二：SELECT s ,scr FROM (SELECT sc.`S#` s,AVG(sc.`score`) scr FROM sc GROUP BY sc.`S#`) rs WHERE rs.scr>60 ORDER BY rs.scr DESC ↑嵌套查询可能影响效率 3、查询所有同学的学号、姓名、选课数、总成绩； 答案一：select Student.`S#`,Student.Sname,count(`C#`),sum(score) from Student left Outer join SC on Student.`S#`=SC.`S#` group by Student.`S#`,Sname ↑如果学生没有选课，仍然能查出，显示总分null（边界情况） 答案二：SELECT student.`S#`,student.`Sname`,COUNT(sc.`score`) 选课数,SUM(sc.`score`) 总分FROM Student,sc WHERE student.`S#`=sc.`S#` GROUP BY sc.`S#` ↑如果学生没有选课，sc表中没有他的学号，就查不出该学生，有缺陷！ 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.`S#`,Student.Sname from Student where `S#` not in (select distinct(SC.`S#`) from SC,Course,Teacher where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname='叶平'); ↑反面思考Step1：先找学过叶平老师课的学生学号，三表联合查询 Step2：在用not in 选出没学过的 Step3：distinct以防叶平老师教多节课；否则若某同学的几节课都由叶平教，学号就会出现重复 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#` and SC.`C#`='001'and exists( Select * from SC as SC_2 where SC_2.`S#`=SC.`S#` and SC_2.`C#`='002' ); ↑注意目标字段`S#`关联 exists subquery 可以用in subquery代替，如下 select Student.`S#`,Student.Sname from Student,Sc where Student.`S#`=SC.`S#` and SC.`C#`='001'and sc.`s#` in ( select sc_2.`s#` from sc as sc_2 where sc_2.`c#`='002' ); ↑不同之处，in subquery此处就不需要关联了

强化-SQL语句强化训练(史上最全最经典,呕血推荐) sql语句练习

2010/7 1. 有4个关系模式如下：出版社（出版社编号，出版社名称）；图书（图书编号，书名，出版社编号，定价）；作者（作者编号，姓名）；著书（图书编号，作者编号，作者排序） 注：作者排序=1表示第一作者，依此类推。用SQL语句，完成第36~39题。 (1).检索所有定价超过20元的书名。 答案：SELECT书名（1分） FROM图书（1分） WHERE定价>20（2分） (2).统计每个出版社图书的平均定价。 答案：SELECT出版社编号，A VG（定价）（2分） FROM图书（1分） GROUP BY出版社编号（1分） (3).将科学出版社的所有图书定价下调5%。 答案：UPDATE图书SET定价=定价*0.95（1分） WHERE出版社编号IN（1分） （SELECT出版社编号FROM出版社（1分） WHERE出版社名称="科学"）（1分） 【说明】WHERE出版社名称LIKE"科学"也正确。 (4).列出所有图书的书名、第一作者姓名和出版社名称。 答案：SELECT书名，姓名，出版社名称（1分） FROM出版社A，图书B，作者C，著书D（1分） WHEREA.出版社编号=B.出版社编号ANDB.图书编号=D.图书编号（1分） ANDC.作者编号=D.作者编号AND作者排序=1。(1分) S(SNO,SNAME,AGE,SEX，SDEPT) SC(SNO,CNO,GRADE) C(CNO,CNAME,CDEPT，TNAME) 1.试用SQL的查询语句表达下列查询： ①检索LIU老师所授课程的课程号和课程名。 ②检索年龄大于23岁的男学生的学号和姓名。 ③检索至少选修LIU老师所授课程中一门课程的女学生姓名。 ④检索W ANG同学不学的课程的课程号。 ⑤检索至少选修两门课程的学生学号。 ⑥检索全部学生都选修的课程的课程号与课程名。 ⑦检索选修课程包含LIU老师所授课程的学生学号。 2．试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询： ①统计有学生选修的课程门数。 ②求选修C4课程的学生的平均年龄。 ③求LIU老师所授课程的每门课程的学生平均成绩。 ④统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。 ⑤检索学号比WANG同学大，而年龄比他小的学生姓名。 ⑥检索姓名以WANG打头的所有学生的姓名和年龄。 ⑦在SC中检索成绩为空值的学生学号和课程号。 ⑧求年龄大于女同学平均年龄的男学生姓名和年龄。 ⑨求年龄大于所有女同学年龄的男学生姓名和年龄。 3．试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作： ①往基本表S中插入一个学生元组（'S9'，'WU'，18）。