sql常用语句

drop database 学生成绩管理数据库
create database 学生成绩管理数据库
on primary
(
 name='学生成绩管理_data',
 filename='c:\学生成绩管理_data.mdf',
 size=10,
 maxsize=unlimited,
 filegrowth=10%
) 
log on
(
 name='学生成绩管理_log',
 filename='c:\学生成绩管理_log.ldf',
 size=1,
 maxsize=5
 

)   

create table 学生表
(
    学号 char(12) primary key not null,
    姓名 char(8) not null ,
    性别 char(2) not null default('男'),
    出生年月 datetime ,
    所在系 varchar(30) check(所在系='工程技术系' or 所在系='经济管理系' or 所在系='文法系' or 所在系='英语系')
)     

create table 课程表
(
    课程号 bigint identity(00010001,1) primary key not null,
    课程名 varchar(30) not null,
    学分 smallint  not null check(学分<=6 and 学分>=1),
    任课教师 char(8)
) 
create table 成绩表
(
    primary key(学号,课程号),
    学号 char(12)  not null references 学生表(学号),
     课程号 bigint   references 课程表(课程号), 
    成绩 numeric (5,1) check(成绩<=100 and 成绩>=0)
    
    
)    
insert into 学生表(学号,姓名,性别,出生年月,所在系) values('200600010001','李洁','女','1986-8-3','工程技术系')
insert into 学生表(学号,姓名,性别,出生年月,所在系) values('200600010002','张明','男','1986-8-3','工程技术系')
insert into 学生表(学号,姓名,性别,出生年月,所在系) values('200600010003','陈明洁','男','1986-8-3','工程技术系')
insert into 学生表(学号,姓名,性别,出生年月,所在系) values('200600020001','钟清','女','1986-8-3','文法系')
insert into 学生表(学号,姓名,性别,出生年月,所在系) values('200600020002','贺珊珊','女','1986-8-3','文法系')


insert into 课程表(课程名,学分,任课教师) values('计算机应用基础','4','李明')
insert into 课程表(课程名,学分,任课教师) values('数据库应用','3','王一凡')
insert into 课程表(课程名,学分,任课教师) values('计算机网络','4','丁治学')
insert into 课程表(课程名,学分,任课教师) values('面向对象程序设计','5','张也好')
insert into 课程表(课程名,学分,任课教师) values('微机组装与维护','2','赵翠花')

insert into 成绩表(学号,课程号,成绩) values('200600010001','00010002','89')
insert into 成绩表(学号,课程号,成绩) values('200600010001','00010003','68')
insert into 成绩表(学号,课程号,成绩) values('200600010001','00010005','73')
insert into 成绩表(学号,课程号,成绩) values('200600010002','00010002','56')
insert into 成绩表(学号,课程号,成绩) values('200600010002','00010004','62')

select * from 学生表
select * from 课程表
select * from 成绩表

update 课程表 set 任课教师='王-凡' where 任课教师='李明';
update 课程表 set 任课教师='丁治学' where  任课教师='张也好';

select 学生表.学号,学生表.姓名,课程表.课程号,课程表.课程名,成绩表.成绩
from 成绩表,课程表,学生表 
where 学生表.学号=成绩表.学号 and 课程表.课程号=成绩表.课程号 


select 课程表.课程号,课程表.课程名,成绩表.成绩
from 
成绩表 inner join 课程表 on 成绩表.课程号= 课程表.课程号



drop table 学生表
drop table 课程表
drop table 成绩表






select * from sysobjects   

alter table 学生表           ---------------------------------->修表
(
    add columnname varchar(50),
    drop column columnname,
    modify column 姓名 char(10),
    add constraint aa check(出生年月>'1991-1-1'),
    drop constraint aa

    add default column 性别   
    drop default column 性别   

    
    
 )
create unique  index U_aa on 学生表(学号 desc)                                         ---->建立索引
alter unique index U_aa on 学生表(学号 asc)  
drop index  学生表.U_aa

create view vie as select 学生表.学号,学生表.姓名,课程表.课程号 from 学生表,课程表                         ------->建立视图
alter view vie as select 学生表.姓名,课程表.课程号 from 学生表,课程表  
drop view vie

select 学生表.学号,学生表.姓名,课程表.课程名 from 学生表,课程表------------------------------------>内联
select 学生表.学号,学生表.姓名,成绩表.成绩 from
学生表 inner  join 成绩表 on 学生表.学号=成绩表.学号
成绩表 inner join 课程表 on 成绩表.课程号=课程表.课程号-------------------------------------内联
           
                
-----------------------------------游标--------------------------------------------------
alter table 成绩表
add flag varchar(20)

declare @score int 
declare cur_sor cursor for select 成绩 from 成绩表 for update of flag
open cur_sor
fetch next from cur_sor into @score
while @@fetch_status=0 
begin 
    if @score>=90
        update 成绩表 set flag='优秀'
    if  @score >=80 and @score <90 
        update 成绩表 set flag='良好'
    if @score>=60 and @score<80 
        update 成绩表 set flag='及格'
    if @score<60
        delete  from 成绩表 where current of cur_sor
    fetch next from cur_sor into @score
    
end 

close cur_sor
deallocate cur_sor
 delete form 学生表 

---------------------------------------存储过程--------------------------------------------------
create procedure proc_xs_select @id char(20) as
    select 姓名,性别 from 学生表 where 学号=@id 

create procedure proc_xs_insert 
@id char(20),
@name char(20),
@sex char(20),
@place char(20),
@time datetime as
    insert 学生表(学号,姓名,性别,出生年月,所在系) values(@id,@name,@sex,@time,@place)

create procedure proc_xs_update @name char(20),@sex char(20),@id char(20) as
    update 学生表 set 性别=@sex,姓名=@name where 学号=@id

create procedure proc_xs_delete @id char(20) as
    delete 学生表 where 学号=@id

drop procedure proc_xs_select
drop procedure proc_xs_update
drop procedure proc_xs_delete 
    
execute proc_xs_select 200600010001
execute proc_xs_insert '200600020000','55','女','工程技术系','1986-8-3'
execute proc_xs_delete '200600020009'


create procedure InputAndOutput
 @id char(20),
 @aaaa char(20) out 
as
select * from 成绩表  

declare @aa
execute InputAndOutput 200600020000 ,@aa
---------------------触发器--------------------------
create trigger tri_xs
on 学生表
for insert
as
begin
    update 成绩表 set 成绩=成绩+20

end
drop trigger tri_xs
select * from  成绩表 


create table 住宿人
(
    names varchar(12),
    sex varchar(12),
    房间号 varchar(12) default(4)
)
create table 房间
(
    空床位数 int default(4),
    房间号 int 
)
drop table 住宿人

--插入触发器:注意inserted表
go
create trigger trig_住宿人
  on 住宿人
  for insert
  as
  declare @fjh int
  select @fjh=房间号 from inserted                             --
  update 房间 set 空床位数=空床位数-1 where 房间号=@fjh
  select * from 房间
go

--删除触发器:注意deleted表
create trigger trig_退宿人    
  on 住宿人
  for delete 
  as
  declare @fjh int
  select @fjh=房间号 from deleted   --副本
  update 房间 set 空床位数=空床位数+1 where 房间号=@fjh
  select * from 房间
go 
insert 住宿人(names,sex,房间号) values('long','男',4)
select * from 住宿人





--p145 11
create trigger tri_xs 
on 学生表
for insert
as
    select * from  学生表
sp_helptext tri_xs


create trigger tri_update 
on 学生表
for update
as
 if  exists(select * from sysobjects where name='backuptable')
    drop table backuptable     --多次按下update语句时，使得backtable中只有两条记录

 select * into backuptable from deleted           --产生表格，并且拷贝记录
 insert into backuptable select * from inserted   --已经产生了表格，现在往里面插入数据
 select * from backuptable 
go 

update 学生表 set 出生年月=出生年月-1 where 学号='200600010001'

------------------------------------------------

declare @sum int ,@i int
select @sum=0,@i=1

while(@sum<1000)
begin
    select @sum=@sum+@i
    select @i=@i+1

end
print '1+2+3+......+n>1000时，n是？，sum?'
print 'sum:'
print @sum
print 'n:'
print @i