数据库原理实验报告

-- 查询总的选课数
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

实验要求：

实验一 熟悉数据库管理系统环境
实验二 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）STUDENT表中增加一个字段入学时间scome：

alter table student 
add scome date

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

alter table student
drop column sdept

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

alter table sc
DROP fk_course

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

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

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

drop table sc

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

create index index_sname
on student(sname desc)

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

drop index index_sname on student

实验总结：

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