综述
只记录几个简单的用法,只用于方便回忆
增
insert into [表名] values
(),
(),
();
删
delete from [表名]
where 条件
改
update [表名] set 字段 = ???
where 条件
查
select [字段] from [表名]
where 条件
索引
create unique nonclustered index [索引名]
on [表名]
重命名索引
exec sp_rename ‘旧名’,‘新名’
视图
create view [视图名] as
select 语句
alter view [视图名] as
select 语句
drop view [视图名]
存储过程
create procedure [存储过程名]
[参数名 类型], [参数名 类型]
as
select 语句
执行存储过程
exec [存储过程名] [参数];
例子
use SC_4959
go
create procedure apdclass
(@dno char(6), @spno char(8), @class_no char(4), @tcid integer)
as
insert into student_course_4959(tcid, s_no)
select @tcid, student_4959.s_no
from student_4959
where student_4959.class_no = @class_no
and student_4959.dno = @dno
and student_4959.spno = @spno
and student_4959.s_no in (select student_course_4959.s_no from student_course_4959 where student_course_4959.tcid = @tcid)
use xmgl
go
create procedure P3_存储过程(@所在部门号 char(4) output, @部门平均工资 float output)
as
select @所在部门号 = 所在部门号, @部门平均工资 = avg(工资)
from 员工表
group by 所在部门号
②调用存储过程,显示指定部门的平均工资。
use xmgl
declare @部门平均工资 float
exec P3_存储过程 '1001', @部门平均工资 output
select '平均工资' = @部门平均工资
触发器
触发器触发的时间
- for 和 after(之后触发)
- instead of(之前触发、直接取代)
自动创建的表
- inserted(增)
- deleted(删)
- 改的话两个表都会自动创建
对应情况(可以多选)
- insert
- update
- delete
回滚操作
- rollback transaction
例子
use 教学管理
if exists (select name from sysobjects where name = 'T_教师变化' and type = 'TR')
drop trigger T_教师变化
go
create trigger T_教师变化 on 教师表
instead of delete
as
begin
declare @教师工号 char(6)
select @教师工号 = 工号 from deleted
if exists(select 1 from 开课表 where 开课表.工号 = @教师工号)
begin
rollback
end
end