建库
CREATE
DATABASE
TABLENAME
(
-- 主数据文件的具体描述
name = ' employee1 ' ,
filename = ' d:、databas、eemployee1_data.mdf ' ,
size = 10 ,
filegrowth = 10 %
)
(
-- 次数据文件的具体描述
name = ' employee2 ' ,
filename = ' d:、databas、eemployee2_data.ndf ' ,
size = 20
maxsize = 100 ,
filegrowth = 1
)
log on
(
-- 日志文件1的具体描述
name = ' employeelog1 ' ,
filename = ' d:、databas、eemployeelog1_data.ldf ' ,
size = 10 ,
filegrowth = 1
)
(
-- 日志文件2的具体描述
name = ' employeelog2 ' ,
filename = ' d:、databas、eemployeelog2_log.ldf ' ,
size = 10 ,
filegrowth = 1
)
(
-- 主数据文件的具体描述
name = ' employee1 ' ,
filename = ' d:、databas、eemployee1_data.mdf ' ,
size = 10 ,
filegrowth = 10 %
)
(
-- 次数据文件的具体描述
name = ' employee2 ' ,
filename = ' d:、databas、eemployee2_data.ndf ' ,
size = 20
maxsize = 100 ,
filegrowth = 1
)
log on
(
-- 日志文件1的具体描述
name = ' employeelog1 ' ,
filename = ' d:、databas、eemployeelog1_data.ldf ' ,
size = 10 ,
filegrowth = 1
)
(
-- 日志文件2的具体描述
name = ' employeelog2 ' ,
filename = ' d:、databas、eemployeelog2_log.ldf ' ,
size = 10 ,
filegrowth = 1
)
建表
create
table
stuMarks(
ExamNo varchar ( 10 ) not null primary key , -- 主键
stuNo varchar ( 6 ) references stuInfo(stuNo) not null , -- stuInfo表内stuNo的外键
writtenExam int ,
labExam int
)
ExamNo varchar ( 10 ) not null primary key , -- 主键
stuNo varchar ( 6 ) references stuInfo(stuNo) not null , -- stuInfo表内stuNo的外键
writtenExam int ,
labExam int
)
创建用户和分配权限
-- 创建WINDOWS身份验证的帐户
exec sp_grantlogin ' windows 域名域帐户 '
-- 创建SQL身份验证的帐户
exec sp_addlogin ' 帐户名 ' ' 密码 '
-- 创建数据库用户
exec sp_grantdbaccess ' 登录帐户 ' ' 数据库帐户 '
-- 向数据库用户授权
grant 权限 [ 表名 ] to 数据库帐户
use studb
go
-- 为zhangshan分配对表stuinfo的select,insert,update的权限
grant select , insert , update , on stuInfo to zhangshan
-- 为zhangshan分配建表的权限
grant create table to zhangshan
变量和IF ELSE的应用
declare
@myavg
float
--
声明一个float类型的变量
select @myavg = avg (writtenExam) from stuMarks -- 把查询结果赋给这个变量
print ' 本班平均分: ' + convert ( varchar ( 5 ), @myavg ) -- 将变量的值转换为varchar类型并打印出变量的值
if ( @myavg > 70 )
begin
print ' 本班笔试成绩优秀,前三名的成绩为 '
select top 3 * from stuMarks order by writtenExam desc
end
else
begin
print ' 本班笔试成绩较差,后三名的成绩为 '
select top 3 * from stuMarks order by writtenExam asc
end
select @myavg = avg (writtenExam) from stuMarks -- 把查询结果赋给这个变量
print ' 本班平均分: ' + convert ( varchar ( 5 ), @myavg ) -- 将变量的值转换为varchar类型并打印出变量的值
if ( @myavg > 70 )
begin
print ' 本班笔试成绩优秀,前三名的成绩为 '
select top 3 * from stuMarks order by writtenExam desc
end
else
begin
print ' 本班笔试成绩较差,后三名的成绩为 '
select top 3 * from stuMarks order by writtenExam asc
end
CASE的应用
SELECT
姓名
=
stuName,学号
=
stuNo,
笔试成绩 = CASE
when WrittenExam is null ' 缺考 '
....
else convert ( varchar ( 5 ),WrittenExam)
end
from stuMark
笔试成绩 = CASE
when WrittenExam is null ' 缺考 '
....
else convert ( varchar ( 5 ),WrittenExam)
end
from stuMark
子查询
--
包括子查询的结果集
select stuName from stuInfo where stuno in ( select stuNo from stumarks)
-- 不包括子查询的结果集
select stuName from stuInfo where stuno not in ( select stuNo from stumarks)
-- 将找到的结果赋给sutno
select stuName from stuInfo where stuno = ( select stuno from wtumarks where id = 4 )
-- 将最后个结果赋给stuno
select stuName from stuInfo where stuno = ( select stuno from wtumarks)
select stuName from stuInfo where stuno in ( select stuNo from stumarks)
-- 不包括子查询的结果集
select stuName from stuInfo where stuno not in ( select stuNo from stumarks)
-- 将找到的结果赋给sutno
select stuName from stuInfo where stuno = ( select stuno from wtumarks where id = 4 )
-- 将最后个结果赋给stuno
select stuName from stuInfo where stuno = ( select stuno from wtumarks)
事务的例子
开始事务: begin transaction
提交事务: commit transaction
回滚事务: rollback transaction
用隐式事务前需要设置: set implicit_transactions on 将隐式事务模式打开
begin transaction
-- 定义变量,用于累计事务执行过程中的错误次数
declare @errorsun int
update bank set currentMoney = currentMoner - 1000 where customername = ' 张三 '
-- 累计是否有错误
set @errorsun = @errorsun + @@error
update bank set currentMoney = currentMoner + 1000 where customername = ' 李四 '
set @errorsun = @errorsun + @@error
if @errorsun <> 0
begin
print ' 交易失败,回滚事务 '
rollback transaction
end
else
begin
print ' 交易成功,提交事务 '
commit transaction
end
go