MySQL练习3

来源网络，整理后分享

student为学生表，course为课程表，score为成绩表，其中sno字段关联student的sno字段，cno字段关联course的cno字段

执行的sql语句：

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
use test;

CREATE TABLE STUDENT
(SNO CHAR(7) NOT NULL primary key,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
sage int(3) NOT NULL,
SDEPT VARCHAR(5) NOT NULL) default charset=utf8;

CREATE TABLE COURSE
(CNO CHAR(3) NOT NULL primary key,
CNAME varchar(15) NOT NULL,
hours int(3)
) default charset=utf8;


CREATE TABLE SCORE
(SNO CHAR(7) NOT NULL,
CNO CHAR(3) NOT NULL,
Grade int(3),
primary key(SNO, CNO),
foreign key(SNO) references STUDENT(SNO),
foreign key(CNO) references COURSE(CNO)) default charset=utf8;


INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9512101' ,'李勇'
,'男' ,19,'计算机系');
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9512102' ,'刘晨'
,'男' ,20,'计算机系');
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9512103' ,'王敏'
,'女' ,19,'计算机系');
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9521101' ,'张立'
,'男' ,22,'信息系');
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9521102' ,'吴宾'
,'女' ,21,'信息系');
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9521103' ,'张海'
,'男' ,20,'信息系');
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9531101' ,'钱小力'
,'女' ,18,'数学系');
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9531102' ,'王大力'
,'男' ,19,'数学系');


INSERT INTO COURSE(CNO,CNAME,hours)VALUES ('C01' ,'计算机文化学',70);
INSERT INTO COURSE(CNO,CNAME,hours)VALUES ('C02' ,'VB',90);
INSERT INTO COURSE(CNO,CNAME,hours)VALUES ('C03' ,'计算机网络',80);
INSERT INTO COURSE(CNO,CNAME,hours)VALUES ('C04' ,'数据库基础',108);
INSERT INTO COURSE(CNO,CNAME,hours)VALUES ('C05' ,'高等数学',180);
INSERT INTO COURSE(CNO,CNAME,hours)VALUES ('C06' ,'数据结构',72);

INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9512101','C01',90);
INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9512101','C02',86);
INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9512101','C06', null);

INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9512102','C02',78);
INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9512102','C04',66);

INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9521102','C01',82);
INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9521102','C02',75);
INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9521102','C04',92);
INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9521102','C05',50);

INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9521103','C02',68);
INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9521103','C06',null);

INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9531101','C01',80);
INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9531101','C05',95);

INSERT INTO SCORE(SNO,CNO,GRADE)VALUES ('9531102','C05',85);

练习题目：

1.分别查询学生表和学生修课表中的全部数据。
    学生表: select * from student;
    学生修课表: select * from course;
2.查询成绩在70到80分之间的学生的学号、课程号和成绩。
    select sno, cno, grade from score where grade between 70 and 90;
3．查询C01号课程成绩最高的分数, 考出该分数的学生编号，课程编号
    select sno, cno, max(grade) from score where cno = 'C01';
4.查询学生都选修了哪些课程,要求列出学生姓名，课程名称，课程号。
    select st.sname, co.cname, sc.cno from student st, score sc, course co where
st.sno = sc.sno and co.cno = sc.cno;
5.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。
    select avg(grade), max(grade), min(grade) from score where cno = 'C02';
6.统计每个系的学生人数。
    select sdept, count(sno) from student group by sdept;
7.统计每门课程的修课人数和考试最高分。
    select cno, count(sno), max(grade) from score group by cno;
8.统计每个学生的选课门数,列出学生编号和选课门数,并按选课门数的递增顺序显示结果。
    select sno, count(cno) from score group by sno order by count(cno);
9.统计全部选修课的学生总数和考试的平均成绩。
    select count(distinct sno), avg(grade) from score;
10.查询选课门数超过2门的学生的学生编号, 平均成绩和选课门数。
    select sno, avg(grade), count(cno) from score group by sno having count(cno) > 2;
11.列出总成绩超过200分的学生,要求列出学号、总成绩。
    select st.sno, sum(sc.grade) from student st, score sc where st.sno = sc.sno
group by sc.sno having sum(sc.grade) > 200;
12.查询选修了c02号课程的学生的姓名和所在系。
    select st.sname, st.sdept from student st, score sc where st.sno = sc.sno
and sc.cno = 'C02';
13.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
    select st.sname, sc.cno, sc.grade from student st, score sc where st.sno = sc.sno and sc.grade > 80 order by sc.grade desc;
14.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。
    select st.sname, st.ssex, sc.grade, co.cname from student st, score sc, course co where st.sno = sc.sno and sc.cno = co.cno and co.cname='数据库基础' and st.ssex='男' and st.sdept = '计算机系';
15.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
    select distinct st1.sname, st1.sage from student st1, student
st2 where st1.sno != st2.sno and st1.sage = st2.sage;
16.查询哪些课程没有人选,要求列出课程号和课程名。
    select cno, cname from course where cno not in (select distinct cno from score);
    -- where 只能用于内连接
    select * from score sc right join course co on sc.cno = co.cno where sc.sno
is null；
17.查询有考试成绩的所有学生的姓名、修课名称及考试成绩
--要求将查询结果放在一张新的永久表(假设新表名为newsc)中。
    create table new_sc
    select st.sname, co.cname, sc.grade from student st, score sc, course co where st.sno = sc.sno and sc.cno = co.cno and sc.grade is not null;

18.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,

--并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
    select st.sdept, st.ssex, co.cname, sc.grade from student st, score sc,
course co where st.sno = sc.sno and sc.cno = co.cno and st.sdept='信息系' or
st.sdept='计算机系';
    
19.实现如下查询:
--(1)    查询选修了C01号课程的学生的姓名和所在系。
    select st.sname, st.sdept from student st, score sc where st.sno = sc.sno
and sc.cno = 'C01';
--(2)    查询数学系成绩80分以上的学生的学号、姓名。
     select distinct st.sno, st.sname from student st, score sc where st.sno =
sc.sno and sc.grade > 80 and st.sdept='数学系';
--(3)    查询计算机系学生所选的课程名.
    select distinct co.cname from student st, score sc, course co where st.sno = sc.sno and sc.cno = co.cno and st.sdept='计算机系';
20.将计算机系成绩高于80分的学生的修课情况(学生姓名, 成绩,课程编号)插入到另一张表中，在插入数据过程中建表。
    create table new_table
    select st.sname, sc.grade, sc.cno from student st, score sc where st.sno =
sc.sno and st.sdept = '计算机系' and sc.grade > 80;
    select distinct co.cno, co.cname from student st, score sc, course co where
st.sno = sc.sno and sc.cno = co.cno and st.sdept='计算机系';
21.删除修课成绩小于50分或者没有分数的学生的修课记录
    delete  from score  where grade < 50 or grade is null;
22.将所有选修了"c01"课程的学生的成绩加10分。
    update score set grade = grade + 10 where cno = 'c01';