数据库原理实验报告

-- 查询总的选课数
select COUNT(*) as 选课总人数
from student

-- 查询没门被选课程的人数、平均分
select cno,COUNT(*) as 人数,AVG(grade) as 平均分
from sc group by cno
--from sc

--查询每个选课同学所选的课的课程门数和选课平均分
select COUNT(*) as 课程门数,AVG(grade) as 选课平均分
from sc
group by sno
--查询每个选课平均分在80以上的同学所选的课程门数和平均分
select sno,COUNT(*) as 所选课程数 ,AVG(grade) as 成绩
from sc
group by sno
having AVG(grade)>=80
--查询女生人数小于200的各学院的女生人数
select sdept as 学院 ,COUNT(*) as 女生人数
from student
where ssex='女'
group by sdept
having COUNT(*) <=200

--查询选课门数在三门以上学生的学号
select sno as 学号,COUNT(*) as 选课门数
from student
--where COUNT(*)>=3
group by sno
having COUNT(*)>=1
--查询选课门数在三门以上且每门成绩都在80分以上学生的学号
select sno as 学号,COUNT(*) as 选课门数
from sc
where grade >=80
group by sno
having COUNT(*)>=3

--DAY YEAR
select DAY('2019-10-23')
select YEAR(getdate())

--LEFT
select sname,LEFT(sno,2)--查询sno前两位
from student

--
selcet *,YEAR(getdate())-sage
from student
where YEAR(getdate())-sage>2000

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

实验要求：

实验一 熟悉数据库管理系统环境
实验二 SQL定义语言
实验三 使用SQL语言进行简单查询
实验四 使用SQL语言进行复杂查询
实验五 SQL常用数据更新操作
实验六 综合应用

实验一：熟悉数据库管理系统环境&&实验二：SQL定义语言

实验过程及分析：

1.创建一个数据库和需要的表：

create database XSGL
go
use XSGL
go
create table student			--创建学生表
(sno char(8) primary key,       --(主键)学生姓名
 sname char(8) not null unique, --学生姓名
 ssex char(2) default '男' check(ssex='男' or ssex='女'),  --性别给定默认值为'男'，取值只能取‘男’或‘女’
 sage tinyint check(sage>13 and sage<50),
 sdept char(20))


create table course				 --创建课程表 
(cno char(2) PRimary key,        --课程编号
 cname varchar(50),  --课程名称
 cpno char(2),       --先修课号
 ccredit tinyint)	 --课程名

create table sc         --创建成绩表
(sno char(8),           --学生学号
 cno char(2),           --课程编号
 grade tinyint,         --成绩
 constraint pk_grade primary key(sno,cno),
 constraint fk_stuid foreign key(sno) references student(sno),
 constraint fk_course foreign key(cno) references course(cno),
 constraint ck_grade check(grade>=0 and grade<=100) )
go
insert into student(sno,sname, ssex,sage,sdept) values('95001', '李勇', '男', 20, 'CS')
insert into student(sno,sname, ssex,sage,sdept) values('95002', '刘晨', '女', 19, 'IS')
insert into student(sno,sname, ssex,sage,sdept) values('95003', '王敏', '女', 18, 'MA')
insert into student(sno,sname, ssex,sage,sdept) values('95004', '张立', '男', 19, 'IS')
insert into student(sno,sname, ssex,sage,sdept) values('95005', '刘云', '女', 18, 'CS ')
insert into course(cno, cname,ccredit,cpno) values('1', '数据库', 4, '5')
insert into course(cno, cname,ccredit,cpno) values('2', '数学', 6, null)
insert into course(cno, cname,ccredit,cpno) values('3', '信息系统', 3, '1')
insert into course(cno, cname,ccredit,cpno) values('4', '操作系统', 4, '6')
insert into course(cno, cname,ccredit,cpno) values('5', '数据结构', 4, '7')
insert into course(cno, cname,ccredit,cpno) values('6', '数据处理', 3, null)
insert into course(cno, cname,ccredit,cpno) values('7', 'PASCAL语言', 4, '6')
insert into sc(sno,cno,grade) values('95001', '1' ,92)
insert into sc(sno,cno,grade) values('95001', '2' ,85)
insert into sc(sno,cno,grade) values('95001', '3' ,88)
insert into sc(sno,cno,grade) values('95002', '2' ,90)
insert into sc(sno,cno,grade) values('95002', '3' ,80)
insert into sc(sno,cno,grade) values('95003', '2' ,85)
insert into sc(sno,cno,grade) values('95004', '1' ,58)
insert into sc(sno,cno,grade) values('95004', '2' ,85)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

（1）STUDENT表中增加一个字段入学时间scome：

alter table student 
add scome date
1
2

（2）删除STUDENT表中sdept字段:

alter table student
drop column sdept
1
2

（3）删除创建的SC表中cno字段和COURSE表cno字段之间的外键约束:

alter table sc
DROP fk_course
1
2

（4）重建（3）中删除的约束：

alter table sc
add constraint fk_course foreign key(cno) references course(cno)
1
2

（5）.重新定义一个简单表，然后用SQL语言DROP语句删除该表结构：

drop table sc
1

（6）.用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引：

create index index_sname
on student(sname desc)
1
2

（7）.用SQL语言DROP语句删除索引：

drop index index_sname on student
1

实验总结：

1. 创建表的时候可以添加约束
2. 可以添加主键唯一标识 用primary key
   
3. 使用alter添加，修改列，还可以删除表中约束如索引 index
4. 使用DROP 可以直接删除表 删除的时候先要删除外键表后才可以删除主键表
5. 删除外键只能用alter 指定表 而不能用on来选择表， 删除索引不能用alter 直接用DROP INDEX 索引 ON 表（语法限定）

实验目的

掌握简单数据查询操作。

实验内容

使用各种查询条件完成指定的查询操作

实验步骤

1、创建学生表student、课程表course和选课表SC,并输入数据（注意数据的完整性）；

2、对各表中的数据进行不同条件的查询；

实验过程（还是使用实验一的表）

1、查询全体学生的学号和姓名

select sno,sname 
from student;
1
2

2、查询全体学生的详细记录

select * 
from student;
1
2

3、查询软件学院的学生姓名、年龄、系别

select sname,sage,sdept
from student
where sdept='MA';
1
2
3

4、查询所有选修过课程的学生学号（不重复）

select distinct sno 
from sc;
1
2

5、查询考试不及格的学生学号（不重复）

select distinct sno
from sc
where grade<60;
1
2
3

6、查询不是软件学院、计算机系的学生性别、年龄、系别

select ssex,sage,sdept
from student 
where sdept not in('CS','MA');
1
2
3

7、查询年龄18－20岁的学生学号、姓名、系别、年龄

select sno,sname,sdept,sage 
from student
where sage>=18 and sage<=20;
1
2
3

8、查询姓李的学生情况
select *
from student
where sname like ‘李%’;

9、查询姓刘或姓李的学生情况
select *
from student
where sname like ‘刘%’ or sname like ‘李%’;

10、查询1983年以后出生的学生姓名

select sname 
from student 
where sage < 2019-1983
1
2
3

11、创建表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)计算学生各科总成绩并赋予别名

create table studentgrade(
    Sno char(8) ,
    mathgrade int,
    englishigrade int,
    chinesegrade int
)
1
2
3
4
5
6

select sum(mathgrade+chinesegrade+englishigrade) '学生总成绩' 
from studentgrade;
1
2

12、查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列

select *
from student 
order by sdept,sage desc;
1
2
3

实验总结

模糊查询用% 如like ‘李%’
或者确定仅两个字的用_ 如like ‘李_’

数据库原理实验四：使用SQL语言进行复杂查询

实验目的

掌握复杂数据查询操作。

实验内容

掌握各种连接查询、嵌套查询的使用。

实验过程（还是使用实验一的表）

1. 查询每个学生及其选课情况

select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where student.sno=sc.sno
1
2
3

2. 查询每门课的间接先修课

select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno 
1
2
3

3. 将STUDENT,SC进行右连接

select student.sno,sname,ssex,sage,sdept,cno,grade
from student right outer join sc on student.sno=sc.sno
1
2

4. 查询既选修了2号课程又选修了3号课程的学生姓名、学号

select student.sno,sname
from student inner join sc on student.sno=sc.sno
where cno='3' and sc.sno in
(select sno
from sc
where cno='2')
1
2
3
4
5
6

5. 查询和刘晨同一年龄的学生

select student.sno,sname
from student
where sname!='刘晨' and sage=
(select sage 
from student
where sname='刘晨')
1
2
3
4
5
6
7

6. 选修了课程名为“数据库”的学生姓名和年龄

select sname,sage
from student
where sno in
(select sno
from sc
where cno in
(select cno
from course 
where cname='数据库'))
1
2
3
4
5
6
7
8
9

7. 查询其他系比IS系任一学生年龄小的学生名单

select student.sno,sname
from student
where sdept<>'IS' and
sage<any
(select sage 
from student
where sdept='IS')
1
2
3
4
5
6
7

8. 查询其他系中比IS系所有学生年龄都小的学生名单

select student.sno,sname
from student
where sdept<>'IS' and 
sage<all
(select sage 
from student 
where sdept='IS')
1
2
3
4
5
6
7

9. 查询选修了全部课程的学生姓名

select sname
from student
where Sno in
(select Sno from SC
group by Sno
having count(*) = (select count(*) from course ))
1
2
3
4
5
6

10. 查询计算机系学生及其性别是男的学生

select student.sno,sname
from student
where sdept='IS' and ssex='男'
1
2
3

11. 查询选修课程1的学生集合和选修2号课程学生集合的差集

select sno
from sc 
where cno='1' except 
select sno
from sc
where cno='2'
1
2
3
4
5
6

12. 查询李丽同学不学的课程的课程号

select cno
from course
where cno not in
(select cno
from sc
where sno in
(select sno
from student
where sname='李丽'))
1
2
3
4
5
6
7
8
9

13. 查询选修了3号课程的学生平均年龄

select avg(sage) as avgsage
from student inner join sc on student.sno=sc.sno
where cno='3'
1
2
3

14. 求每门课程学生的平均成绩

select cno,avg(grade) as avggrade
from sc
group by cno
1
2
3

15. 统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列

select course.cno '课程号', count(sc.sno) '人数'
from course,sc 
where course.cno=sc.cno 
group by course.cno 
having count(sc.sno)>3 
order by count(sc.sno) desc,course.cno asc
1
2
3
4
5
6

16. 查询学号比刘晨大，而年龄比他小的学生姓名

select sname
from student
where sno>(select sno from student where sname='刘晨') 
and
sage<(select sage from student where sname='刘晨')
1
2
3
4
5

17. 求年龄大于所有女同学年龄的男同学姓名和年龄

select sname,sage
from student
where ssex='男' and 
sage>(select max(sage) from student where ssex='女')
1
2
3
4

实验总结

1. 分组group by 要用having来限制条件
2. desc是降序，asc是升序
3. any（）是任意，all（）是所有

数据库原理实验五：SQL的常用数据更新操作

实验目的

掌握SQL的常用数据更新操作，熟练应用INSERT,UPDATE,DELETE语句。

实验内容

1. 插入如下学生记录（学号：95030，姓名：李莉，年龄：18）

insert into student(sno,sname,sage)
values ('95030','李莉',18)
1
2

2. 插入如下选课记录（95030，1）

insert into sc(sno,cno)
values('95030',1)
1
2

3. 计算机系学生年龄改成20

update student
set sage=20
where sdept='CS'
1
2
3

4. 把数学系所有学生成绩改成0

update sc
set grade=0
where 'MA'=
(select sdept
from student
where student.sno=sc.sno)
1
2
3
4
5
6

5. 把低于总平均成绩的女同学成绩提高5分

update sc 
set grade+=5
where grade<
(select avg(grade) 
from sc inner join student
on student.sno=sc.sno
where ssex='女')
1
2
3
4
5
6
7

6. 删除95030学生信息

delete
from student
where sno='95030'
1
2
3

7. 删除SC表中无成绩的记录

delete 
from sc
where grade is null;
1
2
3

8. 删除张娜的选课记录

delete
from sc
where sno=
(select sno from student where sname='张娜')
1
2
3
4

9. 删除不及格的学生选课记录

delete
from sc
where grade<60
1
2
3

10.删除数学系所有学生选课记录

delete
from sc
where sno in 
(select sno from student where sdept='MA')
1
2
3
4

11. 删除所有未被选修的课程

delete
from course
where cno not in (select cno from sc)
1
2
3

12. 查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

create table STU 
(sno char(8), 
sname char(8), 
ssex char(2) 
)
1
2
3
4
5

insert into STU(sno,sname,ssex);
1

select distinct student.sno,sname,ssex
from student,sc 
where student.sno not in
(select sno from sc where grade<80) and student.sno=sc.sno
1
2
3
4

13. 建立一个sdeptgrade 表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade

create table sdeptgrade 
(sdept char(8) primary key, 
avggrade int
) 
1
2
3
4

insert into sdeptgrade;
1

select student.sdept, avg(sc.grade) 
from student inner join sc
on (student.sno = sc.sno) 
group by student.sdept;
1
2
3
4

实验总结

删除主键表数据如果有外键约束就会报错

数据库原理实验六：综合应用

实验目的

根据数据库设计步骤完成简单应用的设计和创建，了解数据在前后台的交互。

实验内容

1、建立一个数据库和五张表的表结构。

2、根据表结构使用SQL语句添加相应约束。

（1）员工人事表employee

create table employee
(
emp_no char(5) primary key,
emp_name char(10) not null,
Sex char(1) not null,
Dept char(4) not null,
Title char(6) not null,
data_hired datetime not null,
birthday datetime null,
salary int not null,
Addr char(50) null,
Mod_date datetime default(getdate())
)
1
2
3
4
5
6
7
8
9
10
11
12
13

（2）客户表customer

create table customer
(
cust_id char(5) primary key,
cust_name char(20) not null,
Addr char(40) not null,
tel_no char(10) not null,
Zip char(6) null
)
1
2
3
4
5
6
7
8

（3）销售主表sales

create table sales
(
order_no int primary key,
cust_id char(5) not null,
sale_id char(5) not null,
tot_amt numeric(9,2) not null,
order_date datetime not null,
ship_date datetime not null,
incoice_no char(10) not null
)
1
2
3
4
5
6
7
8
9
10

（4）销货明细表sale_item

create table sale_item
(
order_no int not null,
prod_id char(5) not null,
Qty int not null,
unit_price numeric(9,2) not null,
order_date datetime null
constraint primary_sale primary key(order_no,prod_id)
)
1
2
3
4
5
6
7
8
9

（5）产品名称表product

create table product
(
prod_id char(5) not null primary key,
prod_naem char(20) not null
)
1
2
3
4
5

3、录入数据并实现实现如下查询

（1）查找定单金额高于20000的客户编号；

select cust_id 
from sales 
where tot_amt>20000
1
2
3

（2）选取销售数量最多的前5条订单订单号、数量；

select top 5 order_no,Qty 
from sale_item 
order by Qty desc
1
2
3

（3）显示sale_item表中每种个别产品的订购金额总和，并且依据销售金额由大到小排

     来显示出每一种产品的排行榜；
1

select prod_id, sum(Qty*unit_price) '金额' 
from sale_item 
group by prod_id 
order by '金额' desc
1
2
3
4

（5）计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序；

select "s2".月份,sum("s2".tot_amt) '销售金额总和',"s1".prod_id '产品编号'
from sale_item "s1"
join (select month(order_date) '月份',order_no,tot_amt from sales) "s2"
on "s1".order_no="s2".order_no
group by "s2".月份,"s1".prod_id
order by "s2".月份,"s1".prod_id
1
2
3
4
5
6

（6）检索单价高于2400元的的产品编号、产品名称、数量、单价及所在订单号；

select s.prod_id, product.prod_name, s.Qty, s.unit_price, s.order_no 
from product,sale_item s
where s.unit_price> 2400 and product.prod_id=s.prod_id
1
2
3

（7）计算每一产品销售数量总和与平均销售单价；

select sum(Qty)'销售数量', avg(unit_price)'平均销售单价'
from sale_item
1
2

（8）创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。

create view view_name AS
select cust_id,cust_name,Addr from customer where Addr='上海'
1
2

实验总结

1. 设置主键，自动为 not null

2. unique和主键区别：
   unique：唯一并且 一张表可设置多个unique 可空 但是只能有一行数据空
   主键： 唯一并且 一张表只能有一个主键

3. 主键可通过 constraint 主键名 primary key（列，列）来设置组合键

4. 给表取别名的时候 不能用单引号，要用双引号或者不用引号
   而给列取别名的时候可以选择单引号 或者 as 连接词 或者不用引号

5. 视图是为了保存一张表 下次查找该表 可直接 使用 如本实验中：

select * from view_name 
1

就可以查看视图。