数据库实例练习

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 courseG;

在这里插入图片描述
删除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;

在这里插入图片描述

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/ilovepython/p/11068842.html