数据库实例练习

1.根据表的结构创建以下四个表

表一. student（学生表）

属性名	数据类型	可否为空	注 释
Sno	vchar(20)	否	学号（主键）
Sname	varchar (20)	否	学生姓名
Ssex	varchar (20)	否	学生性别
Sbirthday	date	可	学生出生年月
Class	varchar (20)	可	学生所在班级

表二. teacher（教师表）

属性名	数据类型	可否为空	注 释
Tno	varchar (20)	否	教工编号（主键）
Tname	varchar (20)	否	教工姓名
Tsex	varchar (20)	否	教工性别
Tbirthday	date	可	教工出生年月
Prof	varchar (20)	可	职称
Depart	varchar (20)	否	教工所在部门

表三. course（课程表）

属性名	数据类型	可否为空	含 义
Cno	varchar (20)	否	课程号（主键）
Cname	varchar (20)	否	课程名称
Tno	varchar (20)	否	教工编号（外键）

表四. score（成绩表）

属性名	数据类型	可否为空	含 义
Sno	varchar (20)	否	学号（外键）
Cno	varchar (20)	否	课程号（外键）
Degree	Decimal(4,1)	可	成绩

2.分别向以上四个表中插入如下数据

Student表：

Sno	Sname	Ssex	Sbirthday	class
108	曾华	男	1977-09-01	95033
105	匡明	男	1975-10-02	95031
107	王丽	女	1976-01-23	95033
101	李军	男	1976-02-20	95033
109	王芳	女	1975-02-10	95031
103	陆君	男	1974-06-03	95031

Teacher表：

Tno	Tname	Tsex	Tbirthday	Prof	Depart
804	李诚	男	1958-12-02	副教授	计算机系
856	张旭	男	1969-03-12	讲师	电子工程系
825	王萍	女	1972-05-05	助教	计算机系
831	刘冰	女	1977-08-14	助教	电子工程系

Course表：

Cno	Cname	Tno
3-105	计算机导论	825
3-245	操作系统	804
6-166	数字电路	856
9-888	高等数学	831

Score表：

Sno	Cno	Degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

3.查询Student表中的所有记录的Sname、Ssex和Class列。

select Sname,Ssex,Class from student;

4.查询Score表中成绩在60到80之间的所有记录。

select * from score where Degree>60 and Degree<80;

5.查询Score表中成绩为85，86或88的记录。

select * from score where Degree in (85,86,88);

6.查询Student表中“95031”班或性别为“女”的同学记录。

select * from student where Class='95031' or Ssex='女';

7.以Class降序查询Student表的所有记录。

select * from student order by Class desc;

8.以Cno升序、Degree降序查询Score表的所有记录。

select * from score order by Cno,Degree desc;

9.查询所有学生的Sname、Cno和Degree列

select Sname,Cno,Degree 
from student 
inner join score
on student.Sno=score.Sno;

10.查询所有学生的Sno、Cname和Degree列

select s.Sno,c.Cname,sc.Degree 
from student as s 
left join score as sc 
on s.Sno=sc.Sno 
left join course as c 
on sc.Cno=c.Cno;

11.查询所有学生的Sname、Cname和Degree列。

select s.Sname,c.Cname,sc.Degree 
from student as s 
left join score as sc
on s.Sno=sc.Sno 
left join course as c 
on sc.Cno=c.Cno;

12.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select student.Sno,Sname,Ssex,Sbirthday,Class 
from student inner join score 
on student.Sno=score.Sno 
and 
	score.Cno='3-105' 
and 
	score.Degree>(select Degree from score where Sno='109' and Cno='3-105');

13.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

mysql> select * from score
    -> where Degree >
    -> (select Degree from score
    -> where Sno='109' and Cno='3-105');

14.查询“张旭“教师任课的学生成绩。

mysql> select sc.Sno,sc.Cno,sc.Degree from score as sc
    -> inner join course as c
    -> on sc.Cno=c.Cno
    -> inner join teacher as t
    -> on c.Tno=t.Tno
    -> where t.Tname='张旭';

15.查询95033班和95031班全体学生的记录

mysql> select * from student where Class in ('95033','95031');

16.查询出“计算机系“教师所教课程的成绩表。

mysql> select sc.Sno,sc.Cno,sc.Degree from score as sc
    -> inner join course as c
    -> on sc.Cno=c.Cno
    -> inner join teacher as t
    -> on c.Tno=t.Tno
    -> where t.Depart='计算机系';

17.查询选修编号为“3-105”课程且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

mysql> select Cno,Sno,Degree
    -> from score a
    -> where
    -> Cno='3-105'
    -> and
    -> (select Degree from score b where Cno='3-105' and a.Sno=b.Sno)
    -> >
    -> (select Degree from score c where Cno='3-245' and a.Sno=c.Sno);

18.查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”课程的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

mysql> select Cno,Sno,Degree
    -> from score as a
    -> where
    -> Cno='3-105'
    -> and
    -> (select Degree from score as b where Cno='3-105' and a.Sno=b.Sno)
    -> >=
    -> (select Degree from score as c where Cno='3-245' and a.Sno=c.Sno)
    -> order by Degree desc;

19. 查询Student表中不姓“王”的同学记录。

mysql> select * from student where Sname not like '王%';

20.以班号和年龄从大到小的顺序查询Student表中的全部记录。

mysql> select * from student order by Class desc,Sbirthday;

21.查询“男”教师及其所上的课程。

mysql> select Tname,Cname from teacher as t,course as c where t.Tno=c.Tno and t.
Tsex='男';（inner join简化版）

22.查询和“李军”同性别的所有同学的Sname.

mysql> select Sname from student where Ssex=(select Ssex from student where Sname='李军') and Sname not in ('李军');

23.查询和“李军”同性别并同班的同学Sname.

mysql> select Sname from student where
    -> Ssex=(select Ssex from student where Sname='李军')
    -> and
    -> Class=(select Class from student where Sname='李军')
    -> and
    -> Sname not in ('李军');

24.查询所有选修“计算机导论”课程的“男”同学的成绩表。

mysql> select s.Sname,c.Cname,sc.Degree
    -> from student as s
    -> inner join course as c
    -> inner join score as sc
    -> where s.Sno=sc.Sno
    -> and sc.Cno=c.Cno
    -> and c.Cname='计算机导论'
    -> and s.Ssex='男'
    -> ;

25.给学生表中的姓名字段添加一个唯一索引

mysql> alter table student add unique(Sname);

26.查看学生表的表结构

27.将外键全部删除
查看course表的外键名：

show create from course\G;

删除course表的外键：

查看score表的外键名：

删除score表的两个外键：

28.将学生李军选修的计算机导论课程成绩改为99分。

mysql> update score set Degree=99
    -> where
    -> Sno=
    -> (select Sno from student where Sname='李军')
    -> and
    -> Cno=
    -> (select Cno from course where Cname='计算机导论');

29.删除教师表的主键

alter table teacher drop primary key;

30.删除学生表中的学生性别字段

alter table student drop column Ssex;