sql学习记录
实验一
参照实验指导书附录，建立数据库SCHOOL，分别建立students、teachers、courses、choices四张表。
用SQL创建数据库、建表。建表时为各表建立关键字、设置外码
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
47create dabase db_school
on primary
(
name='schoolDB_data',
filename='E:\schoolDB_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on(
name='schoolDB_log',
filename='E:\schollDB_log.ldf',
size=2mb,
filegrowth=1mb
)
create table students(
sid char(10) primary key,
sname char(30) not null,
email char(30),
gread int
)
create table teachers(
tid char(10) primary key,
tname char(30) not null,
email char(30),
salary int
)
create table courses(
cid char(10) primary key,
cname char(30) not null,
hour int
)
create table choices(
no int primary key,
sid char(10),
tid char(10),
cid char(10),
score int,
foreign key(sid) references students(sid),
foreign key(tid) references teachers(tid),
foreign key(cid) references courses(cid)
)为students表、courses建立按主键增序排列的索引
1
2create index stuid on students(sid)
create index couid on courses(cid)删除course上的索引
1
drop index courses.couid
在students表中增加一个“出生日期”字段，类型为日期时间型
1
2alter table students
add "出生日期" datetime删除students表中的出生日期字段
1
2alter table students
drop column 出生日期删除students表
1
drop table students
实验二 sql语句
查询年级为2001的所有学生的名称,按编号顺序排列
1
2
3select sid,sname from students
where grade = '2001'
order by sid ASC查询所有课程中含有data的课程编号
1
2select cid from courses
where cname like 'data%'统计所有老师的工资
1
select avg(salary) from teachers
查询至少选了3门课的学生编号
1
2
3select sid from choices
group by sid
having count(*)>3查询学号为80009026的学生的姓名、所选课名及成绩
1
2
3
4
5select students.name, courses.cname, score
from students, courses, choices
where
students.sid = choices.sid and choices.cid = courses.cid
and choices.sid = '80009026'查询没有学生选的课程编号
1
2
3
4
5
6select cid from courses
where not exists
(
select courses.cid from courses, choices
where choices.cid = courses.cid
)查询既选了C++又选了Java课程的学生编号
1
2
3
4
5
6
7
8
9select sid from choices,courses
where choices.cid=courses.cid
and cname='C++'
and sid in
(
select sid from choices,courses
where choices.cid=courses.cid
and cname='Java'
)查询选了C++但没选Java课程的学生编号
1
2
3
4
5
6
7
8
9select sid from choices
where
cid = (select cid from courses where cname='C++')
and
sid not in
(
select sid from choices where
cid = (select cid from courses where cname='Java')
)向students表中插入”LiMing”的个人信息(编号：700045678，名字：LiMing，Email：
LX@cdemg.com，年级：1992)1
2insert into students(sid, sname, email, grade)
values('700045678','LiMing','LX@cdemg.com','1992')将”LiMing”的年级改为2002
1
2update students set grade='2002'
where sname='LiMing'删除所有选了Java课程的学生选课记录
1
2
3
4
5
6delete from choices
where cid in
(
select cid from courses
where cname='Java'
)求出每门课的课程号、选课人数，结果存入数据库表T1中
1
2
3
4
5
6
7
8
9
10create table T1(
cid char(10),
m int
)
insert into T1(cid, m)
select courses.cid, count(sid)
from courses, choices
where courses.cod = choices.cid
group by courses.cid查询所有选课记录的成绩并换算为五分制
1
2select sid, cid, score/20 as score
from choices查询成绩小于80的选课记录，统计总数、平均分、最大值和最小值
1
2
3select count(*) m, avg(score) avg_score, max(score) max_score,
min(score) min_score from choices
where score<80按成绩顺序排序显示choices表中所有记录
1
2select * from choices
order by score创建视图V1,显示学生姓名、所选课名称、任课教师名
1
2
3
4
5
6
7create view V1
as
select sname, cname, tname
from students, courses, teachers, choices
where students.sid = choices.sid
and courses.cid = choices.cid
and teachers.tid = choices.tid取消V1视图
1
drop view V1
实验三 数据库完整性与安全性
创建一张新表class，包括Class_id(varchar(4)), Name(varchar(10)),Department(varchar(20))三个属性列， 其中Class_id为主码
1
2
3create TABLE CLASS( Class_id varchar(4) primary key,
Class_name varchar(10),
Department varchar(20))使用SQL命令创建一张学生互助表，要求：包括学生编号、学生姓名、学生帮助对象的编号，每个学生有且仅有一个帮助对象，帮助的对象必须是已存在的学生
1
2
3
4
5
6
7
8
9
10
11create table help
(
sid char(10) primary key,
sname char(10),
help_sid char(10)
);
alter table help
add constraint fk_help
foreign key (help_sid)
references help(sid);使用STC数据库，修改设置，完成以下2种不同的要求，请写出对应的语句或简述操作过程：
- 当更新、删除STU表中记录时，若SC表中有此学生的记录，则拒绝
1
2
3
4
5
6alter table sc
add constraint fuck
foreign key (sno)
references stu(sno)
on update no action
on delete no action - 当更新、删除STU表中记录时，若SC表中有此学生的记录，则自动更新或删除
1
2
3
4
5
6alter table sc
add constraint fuck2
foreign key (sno)
references stu(sno)
on update cascade
on delete cascade
- 当更新、删除STU表中记录时，若SC表中有此学生的记录，则拒绝
使用sql命令完成以下任务
创建Worker表,定义约束U1、U2，其中U1规定Name字段取值唯一，U2规定sage字段上限是28
1
2
3
4
5
6
7
8
9
10
11
12create table worker(
number char(5),
name char(8),
sex char(1),
sage int,
deapartment char(20)
);
alter table worker
add constraint U1 unique(name);
alter table worker
add constraint U2 check(sage <= 28);去除U1约束,修改约束U2，令sage的值大于等于0
1
2
3
4
5
6
7alter table worker
drop constraint U1;
alter table worker
drop constraint U2;
alter table worker
add constraint U2 check(sage >= 0)创建规则rule_sex,规定更新或插入的值只能是M或F，并绑定到Worker的sex字段
1
2
3create rule rule_sex
as @value in('M','F');
sp_bindrule rule_sex, 'worker.[sex]';
创建触发器并测试
为worker表创建触发器T1，当插入或更新表中数据时，保证所操作记录的sage大于0
1
2
3
4
5
6create trigger T1
on worker
for update,insert
as
if (select sage from inserted) <= '0'
rollback transaction为worker表创建触发器T2，禁止删除编号为00001的记录
1
2
3
4
5
6create trigger T2
on worker
for delete
as
if (select number from deleted) = '00001'
rollback transaction为worker表创建触发器T3，要求更新一个巨鹿时，表中记录的sage要比老记录的sage值大
1
2
3
4
5
6create trigger T3
on worker
for update
as
if (select sage from inserted) < (select sage from deleted)
rollback transaction
