|
create database testdb
go
use testdb
go
create table student
(
?stuId int primary key identity(1,1),
?stuName varchar(20) unique not null,
?stuAge int,
?stuDefen int
)
go
insert into student
select 'zhande',20,90 union
select 'dili',32,80 union
select 'sand',23,72 union
select 'deid',25,86 union
select 'deodl',34,50
--创建索引
--索引类型 unique clustered nonclustered
if exists(select * from sysindexes where name='my_index')
drop index student.my_index
go
create nonclustered index my_index
on student(stuDefen)
with
fillfactor=40
go
create unique index my_indexName
on student(stuName)
with
fillfactor =40
go
select * from student with (index=my_indexName)
where stuName like '%zh%'
go
--调用索引
select * from student with(index=my_index)
where stuDefen between 80 and 90
go
if exists(select 1 from sysobjects where name='view_myjiji')
drop view view_myjiji
go
create view view_myjiji
as
select * from student
go
select * from view_myjiji
go
--创建登陆账户
create login aqianjianren with password='xiaolin520'
go
--给这个用户授权数据库的访问权限
create user haha for login aqianjianren
go
--授权
grant select,update on student to haha
go
--调用无参数的存储过程
?if exists(select 1 from sysobjects where name='pro_my')
drop procedure pro_my
go
create procedure pro_my
as
insert into student values('小林',21,99)
go
--调用
execute pro_my
go
select * from student
go
--带参数的存储过程
if exists(select 1 from sysobjects where name='pro_myyoucan')
drop procedure pro_myyoucan
go
create procedure pro_myyoucan
?@stuName varchar(20),
?@stuAge int,
?@stuDefen int
as
insert into student values(@stuName,@stuAge,@stuDefen)
go
--调用
execute pro_myyoucan '石建军',88
go
select * from student
go
--带默认值的存储过程
?create procedure pro_mydefault
?@stuName varchar(20)='大嘴',
?@stuAge int=18,@stuDefen)
go
--调用
execute pro_mydefault '李向阳',default,88
go
select * from student
go
--带输出参数的存储过程
if exists(select 1 from sysobjects where name='pro_mydefaultout')
drop procedure pro_mydefaultout
go
create procedure pro_mydefaultout
?@stuName varchar(20),
?@stuDefen int,
?@newsId int output
as
insert into student values(@stuName,@stuDefen)
?set @newsId=@@identity
print '该列的学号是'+convert(varchar(20),@newsId)
go
--调用
declare @news int
execute pro_mydefaultout '小英雄雨来',88,@news output
print @news
go
select * from student
go
--使用return返回结果
if exists(select 1 from sysobjects where name='pro_mydefaultout1')
drop procedure pro_mydefaultout1
go
create procedure pro_mydefaultout1
?@stuName varchar(20),@stuDefen)
?return @@identity
?print '该列的学号是'+convert(varchar(20),@@identity)
go
--调用
declare @news int
execute pro_mydefaultout1 '陈浩南',45
go
select * from student
go
--事务
declare @err int
?set @err=0
--执行sql语句前 开始事务
?begin transaction
? update student set stuDefen=stuDefen+20 where stuId=4
set @err=@err+@@error
? update student set stuDefen=stuDefen-20 where stuId=7
set @err=@err+@@error
--判断事务是否成功 <>0 表示不等于
if(@err=0)
begin
? print '转账成功，提交事务'
? commit transaction
end
else
begin
? print '转账失败，回滚事务'
?? rollback transaction
end
go
select * from student
go
?
--触发器分类：(1)针对DML类型触发器
--(2)针对DDL类型的触发器,alter,create,drop
--定义触发器的语法：
create trigger stu_trigger
on student
after insert,update
as
print '触发器的应用 插入成功！'
go
update student set stuName='张克俊' where stuId=21
insert into student values('哈哈d',86)
go
select * from student
go
--定义DDL触发器的语法：
create trigger 触发器名
on all server/database
for/after event_type/event_group
as
?sql语句
--DDL触发器只有after触发器，没有instead of触发器，此触发器可以建立在数据库上，也可以建立在服务器上
--不允许删除StuMarks表中任何一条记录
if exists(select * from sys.objects where name='tg_delete')
?drop trigger tg_delete
go
create trigger tg_delete
on stuMarks
after delete
as
?begin
--??select * from inserted
--??select * from deleted
??--定义变量
??declare @no varchar(10)
??--得到要删除的学号
??select @no=stuNo from deleted
??--提示用户
??print '不能删除学号为'+@no+'的学员'
??--回滚数据
??rollback
?end
go
--(1)修改外围应用配置器的设置
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'xp_cmdshell',1
reconfigure
go
--(2)创建文件夹
exec xp_cmdshell 'md e:/student',no_output
go
--(3)创建数据库
if exists(select * from sys.sysdatabases where name='Student')
?drop database Student
go
create database Student
on
(
?name='Student_data',
?filename='e:/student/Student_data.mdf',
?size=3MB,
?maxsize=5MB,
?filegrowth=10%
)
log on
(
?name='Student_log',
?filename='e:/student/Student_log.mdf',
?size=1MB,
?filegrowth=10%
)
go
--(4)使用数据库
use Student
go
--(5)创建表
if exists(select * from sys.sysobjects where name='StuInfo')
?drop table StuInfo
go
create table StuInfo
(
?stuName varchar(10) not null,
?stuNo varchar(10) not null,
?stuSex char(2) not null,
?stuAge int not null,
?stuSeat int identity(1,1) not null,
?stuAddress varchar(50)
)
go
alter table StuInfo
?add constraint pk_StuInfo_stuNo primary key(stuNo),
??constraint ck_StuInfo_stuNo check(stuNo like 'S253__'),
??constraint ck_StuInfo_stuSex check(stuSex='男' or stuSex='女'),
??constraint ck_StuInfo_stuAge check(stuAge>=15 and stuAge<=50),
??constraint ck_StuInfo_stuSeat check(stuSeat>=1 and stuSeat<=30),
??constraint df_StuInfo_stuAddress default('地址不详') for stuAddress
if exists(select * from sys.sysobjects where name='StuMarks')
?drop table StuMarks
go
create table StuMarks
(
?examNo varchar(18) not null,
?writtenExam float,
?labExam float
)
go
alter table StuMarks
?add constraint pk_StuMarks_examNo primary key(examNo),
??constraint ck_StuMarks_examNo check(examNo like 'E200507____'),
??constraint fk_StuMarks_StuInfo_stuNo foreign key(stuNo) references StuInfo(stuNo),
??constraint ck_StuMarks_writtenExam check(writtenExam>=0 and writtenExam<=100),
??constraint df_StuMarks_writtenExam default 0 for writtenExam,
??constraint ck_StuMarks_labExam check(labExam>=0 and labExam<=100),
??constraint df_StuMarks_labExam default 0 for labExam
--给学生信息表插入以下数据 insert into StuInfo values('张三','S25301','男','沙坪坝') insert into StuInfo values('李四','S25302','女',18,default) insert into StuInfo values('王五','S25303','大学城') select * from StuInfo --给成绩信息表插入以下数据 insert into StuMarks values('E2005070001',10,20) insert into StuMarks values('E2005070003',75,98) insert into StuMarks values('E2005070004',100,63) select * from StuMarks
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
