题目:
CREATE DATABASE zxl
---------------建表------------
create table Student
(
S# char(12) primary key,
Sname char(6) not null,
Sage INT not null,
Ssex char(2) check(Ssex between ' 男' and '女')
)
create table Course
(
C# char(2) primary key,
Cname char(6) not null,
T# char(12)references Teacher(T#) ,
)
create table SC
(
S# char(12) references Student(S#),
C# char(2) references Course(C#),
Score int ,
primary key(S#,C#)
)
create table Teacher
(
T# char(12) primary key,
Tname char(6) not null
)
---------------插入数据--------------------
Insert into Student values('201190609112','zzz',21,'男')
Insert into Course values('01','数据库','111')
Insert into SC values('201190609112','01',100)
insert into Teacher values('111','刘培华')
---------------select查询----------------------
select Tname,Cname
From SC,Teacher,Course
Where SC.C# = Course.C# and Course.T#=Teacher.T# and SC.S#='201190609112'
---------------建立视图查找------------------
create view zxl
as
select Cname,Tname,Score
From SC,Teacher,Course
WHERE SC.C# = Course.C# and Course.T#=Teacher.T# and SC.S#='201190609112'
-------执行视图
SELECT *FROM ZXL
--------------建立过程---------------
CREATE procedure zxll
@num varchar(12)
as
select avg(Score)
from SC,Teacher,Course
where SC.C# = Course.C# and Course.T#=Teacher.T# and SC.S#=@num
----------执行过程
exec zxll
@num='201190609112'
创建角色:
sp_addrole 'admin'
创建登陆和用户:
sp_addlogin 'zzzz','zzzz','zxl'
sp_adduser 'zzzz','zzzz','admin'
应用实例:
--添加
--添加用户:
exec sp_addlogin '用户名 ', '密码 ', '默认数据库名 '
--添加到数据库
exec sp_grantdbaccess '用户名 ', '数据库名 '
--分本权限
grant insert,select,update,delete on table1 to public
--删除
--删除权限
revoke insert,delete on table1 from public
--删除数据库中的用户
exec sp_revokedbaccess '用户名 '
--删除用户
exec sp_droplogin '用户名 '