练手的mysql_mysql练手

1、根据图创建下列表格

没有外键的表先创建，创建顺序为teacher，class，course，student

CREATE TABLE class (

cid INT NOT NULL auto_increment PRIMARY KEY,

caption CHAR (32) NOT NULL

) ENGINE = INNODB DEFAULT charset = utf8;

INSERT INTO score (caption)

VALUES

(‘三年二班‘),

(‘一年三班‘),

(‘三年一班‘) ；

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号

CREATE VIEW vw1 AS SELECT

score.student_id AS "学号",

student.sname AS "姓名",

course.cname AS "科目",

number AS "生物分数"

FROM

score

LEFT JOIN course ON course.cid = score.course_id

LEFT JOIN student ON score.student_id = student.sid

WHERE

course.cname = ‘生物‘;

CREATE VIEW vw2 AS SELECT

score.student_id AS "学号",

student.sname AS "姓名",

course.cname AS "科目",

number AS "物理分数"

FROM

score

LEFT JOIN course ON course.cid = score.course_id

LEFT JOIN student ON score.student_id = student.sid

WHERE

course.cname = ‘物理‘;

SELECT

*

FROM

vw1

INNER JOIN vw2 ON vw1.学号 = vw2.学号

WHERE

vw1.生物分数 > vw2.物理分数;

我们发现按照当前的表结构，没有符合上述条件的学生，不具可比性，但是如果数据量扩大时，具备可比性了，那应该怎么写呢？

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

SELECT

score.student_id AS "学号",

student.sname AS "姓名",

sum(score.number) AS "总分数",

avg(score.number) AS gva

FROM

score

LEFT JOIN course ON course.cid = score.course_id

LEFT JOIN student ON score.student_id = student.sid

GROUP BY

student_id

HAVING

gva > 60;

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

SELECT

score.student_id AS "学号",

student.sname AS "姓名",

sum(score.number) AS "总成绩",

count(score.course_id) AS ‘课程数‘

FROM

score

LEFT JOIN course ON course.cid = score.course_id

LEFT JOIN student ON score.student_id = student.sid

GROUP BY

student_id

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

SELECT

count(tname) AS "个数"

FROM

teacher

WHERE

tname LIKE "波%" ;

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

思路：没学过某个老师，我可以找到学过这个老师的学生，并在学生表判断，排除这些学过的就是没学过了

(学生学的课程id in (先找叶平老师教的课程id))

最后只要排除 not in这群学生就可了

SELECT

*

FROM

student

WHERE

sid NOT IN (

SELECT DISTINCT

student_id

FROM

score

WHERE

course_id IN (

SELECT

cid

FROM

course

LEFT JOIN teacher ON teacher_id = tid

WHERE

tname = "波多"

)

)

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

思路：先查出所有学了 001 或 002 的学生 course_id  in (001,002)

然后group by  学生id，having进行科目数统计，等于2的就是符合条件的

SELECT

student_id,

sname

FROM

student

LEFT JOIN score ON student_id = student.sid

WHERE

course_id IN (1, 2)

GROUP BY

student_id

HAVING

count(student_id) = 2;

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

SELECT

*

FROM

student

WHERE

student.sid IN (

SELECT DISTINCT

student_id

FROM

score

WHERE

course_id IN (

SELECT

cid

FROM

course

LEFT JOIN teacher ON teacher.tid = course.teacher_id

WHERE

teacher.tname = ‘饭岛‘

)

);

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

SELECT

id1,

NAME

FROM

(

SELECT

student_id AS id1,

number AS number1,

student.sname AS NAME

FROM

score

LEFT JOIN student ON student.sid = score.student_id

WHERE

score.course_id = 1

) AS A

LEFT JOIN (

SELECT

student_id AS id2,

number AS number2

FROM

score

LEFT JOIN student ON student.sid = score.student_id

WHERE

score.course_id = 2

) AS B ON A.id1 = B.id2

WHERE

number1 > number2;

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

SELECT DISTINCT

student.sid,

sname

FROM

student

LEFT JOIN score ON student.sid = score.student_id

WHERE

student.sid IN (

SELECT

student_id

FROM

score

WHERE

number < 60

);

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

SELECT

sid,

sname

FROM

student

WHERE

sid IN (

SELECT

student_id

FROM

score

GROUP BY

student_id

HAVING

count(student_id) = 3

);

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

思路：先找到001同学的course---一个元组

course_id in 元组  ---student id元组

sid in student id元组

SELECT DISTINCT

sid,

sname

FROM

student

WHERE

sid IN (

SELECT

student_id

FROM

score

WHERE

course_id IN (

SELECT

course_id

FROM

score

WHERE

student_id = 1

)

)

AND sid != 1;

13、查询学过学号为“001”同学所有一门课的其他同学学号和姓名

SELECT

sid,

sname

FROM

student

WHERE

sid IN (

SELECT

student_id

FROM

score

WHERE

course_id IN (

SELECT

course_id

FROM

score

WHERE

student_id = 1

)

GROUP BY

student_id

HAVING

count(student_id) = 1

)

AND sid != 1;

原文：http://www.cnblogs.com/xinsiwei18/p/5860527.html