5月25号晚上数据库练习

drop database DBS

create database DBS

use DBS

create table 院系表
(YXBH CHAR(8) PRIMARY KEY CLUSTERED,--院系编号
YXMC CHAR(20) NOT NULL,--院系名称
YXZR CHAR(8) --院系主任
)

create table 教师表
(
jsh char(5) constraint pk_jsh primary key,
jsm char(20)
)

create table 课程表
(
kch char(3) constraint pk_kch primary key,
kcm char(20)
)
go

create table 教师表
(
jsh char(5) constraint pk_jsh primary key,
jsm char(20)
)

create table 成绩表
(
xh char(7) references 学生表,--学号
kch char(3) references 课程表,--课程号
cj int default 0 check (cj >= 0and cj <= 100),--成绩
jsh char(5) references 教师表--教师号
constraint pk_cj primary key(xh,kch)--主码
)



CREATE TABLE 学生表
(
XH CHAR(7)
CONSTRAINT PK_XH PRIMARY KEY NONCLUSTERED,--学号
XM CHAR(20) NOT NULL,--姓名
sfz char(18) unique nonclustered,--身份证
yxbh char(8) references 院系表 --院系编号，外码
)

drop table 学生表
insert into 学生表 values('0301001','李永年','350500198305214026','001')
insert into 学生表 values('0301002','张丽珍','350500198512017017','001')
insert into 学生表 values('0302001','陈俊雄','320300198503213042','001')
insert into 学生表 values('0302002','李军','210200198409112402','001')
insert into 学生表 values('0302003','王任芳','502400198401223341','001')
insert into 学生表 values('0303001','赵雄伟','401200198312111123','001')
select * from 学生表

select top 4 * from 学生表
select top 50 percent * from 学生表 order by xm

select distinct(kch) as kch from 学生表

insert into 教师表 values('01002','王崇阳')
insert into 教师表 values('01001','李穆')
insert into 教师表 values('02001','吴赛')
insert into 教师表 values('02002','冯远客')
insert into 教师表 values('03001','李莉')
insert into 教师表 values('03002','简方')
insert into 教师表 values('01003','刘高')

insert into 课程表 values('001','高等数学')
insert into 课程表 values('002','计算机基础')
insert into 课程表 values('003','网络基础')
insert into 课程表 values('005','大学英语')

insert into 院系表 values('001','计算机','冯远客')
insert into 院系表 values('002','经管','简方')
insert into 院系表 values('003','数学','黄梅')

insert into 成绩表 values('0301001','001','89','01001')
insert into 成绩表 values('0301002','001','78','01002')
insert into 成绩表 values('0302001','002','85','02001')
insert into 成绩表 values('0301001','005','69','02002')
insert into 成绩表 values('0302001','001','56','01002')
insert into 成绩表 values('0302002','001','93','02001')
insert into 成绩表 values('0302003','001','67','01003')

select distinct(kch) as kch from 成绩表

select avg(cj) as 平均成绩 from 成绩表

select max(cj) as 最高分,min(cj) as 最低分 from 成绩表

select 学生表.xh as 学号,学生表.xm as 姓名, 课程表.kch as 课程号,成绩表.cj
as 成绩
from 学生表 inner join 成绩表 on (学生表.xh=成绩表.xh)
inner join 课程表 on (课程表.kch=成绩表.kch)

select 学生表.xh as 学号,学生表.xm as 姓名,课程表.kcm as 课程名
from 学生表 left join 成绩表 on 成绩表.xh=学生表.xh
left join 课程表 on 成绩表.kch=课程表.kch

select 学生表.xh as 学号,学生表.xm as 姓名,课程表.kcm as 课程名
from 学生表
right join 成绩表 on 成绩表.xh=学生表.xh
right join 课程表 on 成绩表.kch=课程表.kch

select 学生表.xh as 学号,
学生表.xm as 姓名,
课程表.kcm as 课程名
from 学生表 full join 成绩表 on 成绩表.xh=学生表.xh
full join 课程表 on 成绩表.kch=课程表.kch


select * from 成绩表 where cj<70 or cj>80

select * from 成绩表 where cj>=60

select * from 成绩表 where cj between 70 and 80

select * from 成绩表 where kch in('001')

select * from 学生表 where(xm like '李%')

select * from 成绩表 order by kch,cj desc

select top 3 * from 成绩表 order by cj desc

select avg(cj) as 平均成绩,kch as 课程编号 from 成绩表 group by kch

select sum(cj) as 成绩总和,jsh as 教师号, count(*) as 人数
from 成绩表 where cj>60 group by jsh

select * from 学生表 where xh>0302002
union
select * from 学生表 where xh<0302002

select xm as 姓名,kcm as 课程名,cj as 成绩
from 学生表,成绩表,课程表
where 学生表.xh=成绩表.xh and 课程表.kch=成绩表.kch
and kcm='高等数学'
union
select xm as 姓名,kcm as 课程名,cj as 成绩
from 学生表,成绩表,课程表
where 学生表.xh=成绩表.xh and 课程表.kch=成绩表.kch
and kcm='大学英语'

select * from 学生表 where xh in
(select xh from 成绩表 where kch in
(select kch from 课程表 where(kch='计算机基础')))

select * form 学生表 as A, 成绩表 as B
where A.xh=B.xh and B.cj>any(select C.cj from 成绩表 as C,学生表 as D where C.xh=D,xh and D.xm like '陈%')

select * from 学生表 as A,成绩表 as B
where A.xh=B.xh and B.cj>all
(select C.cj from 成绩表 as C,学生表 as D
where C.xh=D.xh and D.xm like '陈%')

use DBS
create login teacher with password='123',default_database=DBS
create user teacher1 for login teacher
exec sp_change_users_login 'update_one','teacher1','teacher';

exec sp_addlogin 'student','0000','教学管理'--用存储过程创建账号
,sp_addlogin loginName,
password,database"创建用户"
grant all on 管理系统 to student with grant option

revoke insert on 学生表 from student

revoke all on 学生表 from public