MSSQL笔记

1.使用Transact-SQL命令查看数据库信息
sp_helpdb'数据库名称'

2.查看数据库空间信息
sp_spaceused

3.增加数据库的分配空间
alter database 数据库名称 modify file(name='d:\book.mdf',size=60)

4.查看数据库选项
exec sp_dboption '数据库名称'

将数据库设置为只读状态
exec sp_dboption 'WebGame_User','read only',tre

5.数据库改名
1>将数据库设置为单用户exec sp_dboption '数据库名','single user','true'
2>执行exec sp_renamedb '原名','新名'
3>取消单用户exec sp_dboption '数据库名','single user','false' 

6.删除数据库
drop database 数据库名

7.创建用户自定义数据类型
exec sp_addtype meetingday,smalldatetime,'not null'

删除用户自定义数据类型
exec droptype 'meetingday'

8.查看表的结构
exec sp_help 表名

9.约束类型
check:用于限制输入一列或多列的值的范围
default:默认值
PK:主键
FK:外键
unique:非主键唯一

10.添加check约束
alter table teacher add constraint ck_teacher check(性别='男' or 性别='女')

删除约束
alter table teacher drop constraint ck_teacher

11.添加default约束
alter table teacher add constraint 性别 default '男' for 性别

删除约束
alter table teacher drop constraint de_性别

12.添加PK约束
alter table teacher add constraint PK_作者编号 primary key clustered(作者编号)

删除约束
alter table teacher drop constraint PK_作者编号

13.添加fK约束
alter table book add constraint PK_编号 primary key clustered(编号)
alter table bookin add constraint FK_编号 foreign key(编号) references book(编号)

14.top n查询
从结果集中返回n条数据
select top 20 * from User_RegistInfo where UR_AccountID > 10009 and UR_AccountID < 10060 order by UR_AccountID desc;

15.修改查询结果中的标，及列名
select '图书编号'=编号,'图书书号'=ISBN编号,'图书定价'=定价,'图书出版社'=出版社 from book
select 编号'图书编号',ISBN编号'图书书号',定价'图书定价',出版社'图书出版社' from book
select 编号 as '图书编号',ISBN编号 as '图书书号',定价 as '图书定价',出版社 as '图书出版社' from book

16.in 查询
括号里面为或的意思
select 书名 from book where 编号 in('HK1002','BX2005','HYB2009')

17.like查询
四种通配符
%:匹配包含0个或者多个字符的字符串
_:匹配任何单个的字符
[]:匹配任何在排列内的单个字符;[m-p]
[^]:匹配任何不在范围或者集合之内的单个字符;[^m-p]
select * from book where 书名 like '中%'

18.is null查询 查询没有赋值的行
select 书名,出版社 FROM book where 出版社 is null

19.compute子句用来计算总计或进行分组小计
select * from book where 出版社='中国长安' compute avg(定价)

20.group by
select * from book group by 出版社

21.having    select子句和having子句中使用聚合函数，where子句中不可使用
select * from book group by 出版社 having 出版社='中国长安'

22.嵌套查询
select * from book where 定价>(select avg(定价) from book)

23.union
将两个或者多个查询结果合并成一个结果

24.exists
用于测试跟随的子查询中的行是否存在，存在返回真

25.index
创建索引：create unique clustered index ix_book on book(编号)
删除索引：drop index book.ix_book
显示索引相关信息：exec sp_helpindex book
重命名索引：exec sp_rename 'book.ix_book','ix_booknew'
更新索引：update statistics book ix_book
获取索引碎片信息：DBCC showcontig(book,ix_book)
索引碎片整理：DBCC indexdfrag(数据库,表,索引名)

26.view
创建视图：create view v_book as select * from book where 出版社='中国长安'
查看视图：select * from v_book
修改视图：alter view v_booknew with encryption as select 出版社,count(*)出版总数,sum(定价)出版总价 from book group by 出版社
删除视图：drop view v_book
查看视图定义信息：exec sp_helptext'v_book_t'
查看已经加密的视图：exec sp_helptext'v_bookbycbs'
查看视图对象的参照对象和字段：exec sp_depends'v_bookbycbs'
通过视图查询数据：select 书名,作者姓名 from v_book_t where 定价=59.8
通过视图更新数据：insert into v_book values('硬件测试',50)

27.临时存储过程
本地临时存储过程：以#开头，存放在tempdb中的，断开连接后就会自动删除；
全局临时存储过程：以##开头，存放在tempdb中的，创建后任意用户都能执行，不需要特定的权限；当所有用户执行完成，自动的删除；
远程存储过程：位于远程服务器上的存储过程，使用分布式查询和EXECUTE命令执行一个远程存储过程；
扩展存储过程：使用外部语言编写的存储过程；

28.存储过程三种重新编译的方法
1>在创建存储过程时使用with recompile子句：
create procedure p_book @出版社 varchar(20) with recompile as select * from book where 出版社=@出版社
2>execute p_booklp'中国长安' with recompile
3>exec sp_recompile book

29.触发器
insert触发器：create trigger trigger1_book on book for insert as print'摄入数据成功'
delete触发器：create trigger trigger2_book on book instead of delete as print'数据删除成功'
update触发器：create trigger trigger3_book on book for update as if update(定价) begin rollback transaction end
delete触发器：create trigger trigger4_book on book for delete as begin raiserror('Unauthorized!',10,1) rollback transaction end

查看所有的触发器：EXEC sp_helptrigger 表名
使用系统表查看触发器：select name from sysobjects where type='TR'

30.创建数据库用户
exec sp_addlogin 'b_login','book'
exec sp_adduser 'b_login','b_user',db_owner

31.修改登录密码
exec sp_password 'old_password','new_password','login'

将登陆账号添加到登陆服务器
exec sp_addsrvrolemember'login','role'

改变登录账号和用户名之间的关系
sp_change_users_login'action','user','login'

32.查看数据库用户账户
exec sp_helpuser

删除登录账号
exec sp_droplogin 'login'

删除数据库用户账号
exec sp_revokedbaccess 'user'

33.创建新的角色
exec sp_addrole 'role','dbo'

34.在当前库创建角色
exec sp_addapprole 'approle','123456'

删除指定库的角色
exec sp_droprole 'myrole'

35.授予权限
grant create database,create table to book_user1,book_user2
grant insert update,delete on book to book_user1,book_user2

36.撤销权限
revoke create table from book_user1
revoke create table,create default from book_user1,book_user2

37.Sql编程
1>局部变量：前面加@
2>赋值：select
3>全局变量：前面加@@
4>运算符：**指数、%取模

38.内部函数
1>系统函数：app_name()、convert()、current_timestamp、newID()、nullif()...
2>日期函数：getdate()、dateadd()、year()...
3>字符串函数：str()、upper()、lower()...
4>数学函数：abs()、power()、sign()、tan()
5>集合函数：sum()、max()、avg()

39.流控语句
1>if...else
use book
go
declare @定价 money,@message varchar(250)
set @定价=8000
if exists(select * from book1 where 定价>@定价)
    begin
 select distinct book1.编号，书名，定价，作者姓名 from book1.编号=teacher.编号 and 定价>@定价
    end
else
set @message='不存在高于8000元的书' 
print @message

2>begin...end 相当于括号

3>while、break、continue

4>declare 定义局部变量

5>case 表达式
use book
go
select 书名，出版社=
case 出版社
when'中国长安' then'中国最具实力的出版社之一'
when'海南' then'海外影响力最强的出版社之一'
end
from book1

use book
go
select 书名，出版社，定价情况=
case 定价
when '定价is null' then'未录入定价'
when '定价>0 and 定价<30' then'价格合适'
when '定价>=30 and 定价<100' then'价格偏高'
when '定价>=100 and 定价<300' then'未价格高'
when '定价>=300 then'价格特别高'
end
from book1

6>return 
实现无条件退出命令、存储过程或者触发器249；返回0表示成功，负数为错误

7>waitfor
用来暂时停止程序执行，直到设定的时间结束

8>goto
用来改变程序执行的流程，使程序跳到标识符指定的程序行再继续往下执行
declare @sum smallint,@i smallint
set @i=1
set @sum=0
beg:
  if(@i<=100)
 begin
       set @sum=@sum+@i
    set @i=@i+1
    goto beg
 end
print @sum

9>print
在屏幕上显示用户的信息以及char、varchar数据类型变量的内容；其他数据类型需先进行类型转换

40.事务
1>事务中不可执行的语句
alter database
backup log
create database
disk init
drop database
dump transaction
load database
load transaction
reconfigure
restore database
restore log
update statistics

use book
go
begin transaction
insert...
declare @countnum int
set @countnum = (select count(*) from book where 编号='200701')
if @countnum > 4
begin
rollback transaction
print'此编号的书已超过9本，不能再次插入'
end
else
begin
commit transaction
print'此编号的书还未超过9本，你的操作插入成功'
end

41.锁
1>显示当前持有的所有锁信息
exec sp_lock

42.游标cursor
use book
go
declare Crsbook1 cursor
for
select * from book1
open Crsbook1
fetch next from CrsBook1
    update book1 set 出版社='中国商业行业' where current of CrsBook1
    delete from book1 where current of Crsbook1
close CrsBook1
deallocate CrsBook1

43.数据库的日常维护
1>导入数据：xls、txt...
2>导出数据：access

44.数据库的备份与还原
1>备份内容：master、msdb、model、用户库、事务日志
创建备份介质
exec sp_addumpdevice 'disk','book_bak','d:\backup_db'
执行备份操作
backup database book to book_bak

2>日志的备份
创建日志备份的设备
exec sp_addumpdevice'disk','book_log_bak','d:\back_db'
备份日志
back log book to book_log_bak

3>还原数据库
restore database book from disk='d:\back_db'

45.附加数据库

46.编程接口
1>通过ODBC访问

 

by：tony