SQL 数据库原理及应用 （第4版） 实验

数据库原理实验

实验2 设计数据库、创建数据库和数据表

//多行SQL命令，最后一行不加逗号

1.创建数据库
create database 教学;

//删除数据库
drop database 教学;

//修改数据库
alter database Student modify name = 教学


2.创建数据表
use 教学

//创建学生表
create table 学生(
学号 varchar(4)  primary key,
姓名 varchar(20) NOT NULL,
性别 nchar(1) check( 性别 in ('男','女') ),--性别 nchar(1) check( 性别 = ‘男’ or 性别 = ‘女’ )
专业班级 varchar(10),
出生日期 date,
联系电话 varchar(15),
--是否入学 varchar(2) default '是'
)

创建学生作业表
use 教学
create table 学生作业(
课程号 varchar(4),
学号 varchar(4),
作业1成绩 int,
作业2成绩 int,
作业3成绩 int 
)

创建课程表
use 教学
create table 课程(
课程号 varchar(4)  primary key,
课程名 varchar(20) NOT NULL,
学分数 decimal(2,1) not null,
学时数 int not null,
任课教师 varchar(20) not null, 
)

//修改表的列
//表已经有数据时，不能再更改列的数据类型，数据大小只能由小改到大，
use Teach
alter table Studnet
alter column id varchar(6)



3.往数据表中插入信息
insert into 学生 (学号,姓名,性别,专业班级,出生日期,联系电话)
values	('0433','张艳','女','生物04','1986-9-13',null)

insert into 学生 (学号,姓名,性别,专业班级,出生日期,联系电话)
values  ('0496','李越','男','电子04','1984-2-23',13142980985)
		('0529','赵欣','男','会计05','1984-1-27',13898003455)
		('0531','张志国','男','生物05','1986-9-10',13587465647),
		('0538','于兰兰','女','生物05','1984-2-20',18736479574),
		('0591','王丽丽','女','电子05','1984-3-20',19887456748),
		('0592','王海强','男','电子05','1986-11-1',null)

insert into 课程
values	('K001','计算机图形学',2.5,40,'胡晶晶'),
		('K002','计算机应用基础',3,48,'任泉'),
		('K006','数据结构',4,64,'马跃先')
		('M001','政治经济学',4,64,'孔繁新'),
		('S001','高等数学',3,48,'赵晓尘')
		
insert into 学生作业 
values ('K001','0433',60,75,75),
	   ('K001','0529',70,70,60),
	   ('K001','0531',70,80,80),
	   ('K001','0591',80,90,90),
	   ('K002','0496',80,80,90),
	   ('K002','0529',70,70,85),
	   ('K002','0531',80,80,80),
	   ('K002','0538',65,75,85),
	   ('K002','0592',75,85,85),
	   ('K006','0531',80,80,90),
	   ('K006','0591',80,80,80),
	   ('M001','0496',70,70,80),
	   ('M001','0591',65,75,75),
	   ('S001','0531',80,80,80),
	   ('S001','0538',60,NULL,80)



select* from 学生
where 学号='0531'

delete  from 学生作业

实验3 数据库的单表查询和连接查询

1.查询各位学生的学号、班级、姓名
select 学号,专业班级,姓名
from 学生

2.查询课程的全部信息
select *
from 课程

3.查看数据库中有哪些专业班级
select distinct 专业班级
from 学生

4.查询学时数大于60的课程信息
select *
from 课程
where 学时数>60

5.查询在1986年出生的学生的学号、姓名、和出生日期
select 学号,姓名,出生日期
from 学生
where 出生日期 like '1986%'

6.查询三次作业的成绩都在80分以上的学号、课程号
select 学号,课程号
from 学生作业
where 作业1成绩>80 and 作业2成绩>80 and 作业3成绩>80

7.查询姓张的学生的学号、姓名和专业班级
select 学号,姓名,专业班级
from 学生
where 姓名 like '张%'

8.查询05级的男生信息
select *
from 学生
where 性别='男' and 专业班级 like '%05'

9.查询没有成绩的学号和课程号
select 学号,课程号
from 学生作业
where 作业1成绩 is null or 作业2成绩 is null or 作业3成绩 is null

10.查询学号为0538的学生的作业1总分
select SUM(作业1成绩) as 作业1总分
from 学生作业
where 学号='0538'

11.查询选修了K001课程的学生人数
select COUNT(*) 
from 课程
where 课程号='K001'

12.查询数据库中共有多少个班级
select distinct count(专业班级)
from 学生

13.查询选修三门以上(含三门)课程的学生的学号和作业1平均分、作业2平均分和作业3平均分
select 学号,AVG(作业1成绩) as 作业1平均分,AVG(作业2成绩) as 作业2平均分,AVG(作业3成绩) as 作业3平均分
from 学生作业
group by 学号
having(COUNT(*)>=3)

14.查询于兰兰的选课信息,列出学号、姓名、课程名（使用两种连接查询的方式）

内连接查询
select 学生.学号,姓名,课程名
from 学生,课程,学生作业
where 学生.学号 = 学生作业.学号 and 课程.课程号 = 学生作业.课程号

外连接查询
select 学生.学号,姓名,课程名
from 学生
left join 学生作业
on 学生.学号 = 学生作业.学号
left join 课程
on 课程.课程号 = 学生作业.课程号

实验4 数据查询和数据操纵

use 教学

1.使用查询语句完成以下任务(每一个查询都要给出SQL语句，并且列出查询结果)。
(1)查询与“张志国” 同一班级的学生信息(使用连接查询和子查询方式)。

连接查询
select s1.学号, s1.姓名, s1.性别, s1.专业班级, s1.出生日期, s1.联系电话 
from 学生 as s1, 学生 as s2
where s1.专业班级 = s2.专业班级 and s2.姓名 = '张志国'

子查询
select *
from 学生
where 专业班级 = ( select 专业班级
					from 学生
					where 姓名 = '张志国' )

(2)查询比“计算机应用基础”学时多的课程信息(使用连接查询和子查询方式)。

连接查询
select c1.课程号, c1.课程名, c1.学分数,  c1.学时数, c1.任课教师
from 课程 as c1, 课程 as c2
where c1.学时数 > c2.学时数 and c2.课程名 = '计算机应用基础'

子查询
select *
from 课程
where 学时数 > ( select 学时数
				  from 课程
				  where 课程名 = '计算机应用基础' )

(3)查询选修课程号为K002的学生的学号、姓名(使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询)。

连接查询：
SELECT 学生.学号, 学生.姓名
FROM 学生
INNER JOIN 学生作业
ON 学生.学号 = 学生作业.学号 and 学生作业.课程号 = 'K002'

普通子查询：
select distinct 学生.学号, 学生.姓名
from 学生
where 学生.学号 in (
select 学号
from 学生作业
where 课程号 = 'K002' )

相关子查询
select distinct 学生.学号, 学生.姓名
from 学生
where  'K002' in ( select 课程号
					from 学生作业
					where  学生.学号 = 学号 )

使用exists关键字的相关子查询
select distinct 学生.学号, 学生.姓名
from 学生
where exists ( select *
				from 学生作业
				where  学生.学号 = 学号 and 课程号 = 'K002' )

(4)查询没有选修K001和M001课程的同学的学号、课程号和三次成绩(使用子查询)。
select 学号, 课程号, 作业1成绩, 作业2成绩, 作业3成绩
from 学生作业
where 课程号 not in ( 'K001', 'M001' )

2. 使用数据操纵完成以下任务(每一个任务都要给出SQL语句，并且列出查询结果)。

(1)在学生表中添加一条学生记录，其中，学号为0593， 姓名为张乐，性别为男，专业班级为电子05。
insert into 学生( 学号, 姓名, 性别, 专业班级 )
values ( '0593', '张乐', '男', '电子05' )

select * 
from 学生
where 姓名='张乐'

(2)将所有课程的学分数变为原来的两倍。
select 学分数
from 课程


update 课程
set 学分数 = 2 * 学分数

select 学分数
from 课程


(2)删除张乐的信息
select 姓名
from 学生

delete
from 学生
where 姓名 = '张乐'

select 姓名
from 学生

实验5 视图

1.创建一个电子05的学生视图 (包括学号、姓名、性别、专业班级、出生日期)。
create view 电子05
as select 学号, 姓名, 性别, 专业班级, 出生日期
from 学生
where 专业班级 = '电子05'

2. 创建一个生物05的学生作业情况视图(包括学号、姓名、课程名、作业1成绩、 作业2成绩、作业3成绩)。
create view 生物05作业
as select 学生.学号, 学生.姓名, 课程.课程名, 学生作业.作业1成绩,  学生作业.作业2成绩, 学生作业.作业3成绩
from 学生, 课程, 学生作业
where 学生.学号 = 学生作业.学号 and 课程.课程号 = 学生作业.课程号

3.创建一个学生作业平均成绩视图(包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩)。
create view 学生作业平均成绩
as select 学号, sum(作业1成绩) as 作业1平均成绩, sum(作业2成绩) as 作业2平均成绩, sum(作业3成绩) as 作业3平均成绩
from 学生作业
group by 学号

4. 修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。
alter view 生物05作业
as select 学生.学号, 学生.姓名, 课程.课程名, 学生作业.作业1成绩
from 学生, 课程, 学生作业
where 学生.学号 = 学生作业.学号 and 课程.课程号 = 学生作业.课程号

5.向电子05的学生视图中添加一条记录，其中学号为0596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8 (除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化? )
select *
from 学生

select * 
from 电子05

insert into 电子05
values	('0596','赵亦','男','电子05','1986-6-8')
学生表中也新增了此信息


6. 将电子05的学生视图中赵亦的性别改为“女“ (除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化? )。
update 电子05
set 性别 = '女'
where 姓名 = '赵亦'
学生表中也更新了此信息

7.删除电子05的学生视图中赵亦的记录。
delete
from 电子05
where 姓名 = '赵亦'

8.删除电子05的学生视图(给出sQL语句即可)。
drop view 电子05