1 | 实验八:T-SQL(6) |
#0.建表代码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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72create database Students
on
primary(name=Students,
filename='D:\test\test.mdf',
size=4mb,
maxsize=10mb,
filegrowth=2mb
)
log on
(name=Studentslog,
filename='D:\test\testlog.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)
use Students;
create table Student
(
sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)
create table Course
(
Cno char(6) not null,
Cname char(10),
Cpno char(6),
Ccredit int null,
primary key(cno),
)
go
create table SC
(
Sno char(8) not null foreign key references Student(sno),
Cno char(6) not null foreign key references course(cno),
primary key(Sno,Cno),
Grade int null
)
insert into Student values('19130201','丁鹏','男',20,'Software')
insert into Student values('19130202','王韵婷','女',20,'Software')
insert into Student values('19130203','尹嘉琪','男',18,'Software')
insert into Student values('19130204','卢冬冬','男',20,'Software')
insert into Student values('19130205','史逸凡','男',19,'Software')
insert into Course values('1','数据库',5,4)
insert into Course values('2','数学',null,2)
insert into Course values('3','信息系统',1,4)
insert into Course values('4','操作系统',6,3)
insert into Course values('5','数据结构',7,5)
insert into Course values('6','数据处理',null,2)
insert into Course values('7','PASCAL语言',6,4)
insert into SC values('19130201',1,99)
insert into SC values('19130202',5,95)
insert into SC values('19130203',3,100)
insert into SC values('19130205',1,93)
insert into SC values('19130204',5,92)
alter table course add foreign key(cpno) references course(cno)
select * from Course;
select * from SC;
select * from Student;
建表示例图
![DQQPAUM`X~V~S4D@T1O_1P.png-14.4kB
##1. 创建登录名log1,口令为123456,缺省数据库为 student1
exec sp_addlogin 'log1','123456','Students'
##2. 创建用户wangyong,登录名为log11
exec sp_grantdbaccess 'log1','wangyong'
##3. 创建角色student_role1
EXEC sp_addrole 'student_role'
##4. 为角色studen_role增加成员wangyong1
EXEC sp_addrolemember 'student_role', 'wangyong'
##5. 授予角色studen_role在表student上的查询权限、在sc表上的插入权限1
2GRANT select on Student TO student_role
GRANT insert on SC TO student_role
##6. 撤销角色studen_role在表student上的查询权限1
REVOKE SELECT ON Student FROM student_role
##7. 删除角色、用户、登录名1
2
3exec sp_droprolemember 'student_role','wangyong'
exec sp_droprole 'student_role'
exec sp_droplogin 'log1'
##8. 创建视图v_1查询计算机系的所有学生学号、姓名、性别、年龄1
2
3
4
5create view CS_Student
AS
select sno as '学号',Sname as '姓名',Ssex as '性别',Sage as '年龄'
FROM Student
WHERE Sdept='Software'