MS SQL2005笔记

1.执行SQL文件快捷键 F5

2.创建数据库语句
  cteate datebase book
  on primary
  (name=book_date,
   filename='d:\book.mdf',
   size=55,
   maxsize=120,
   filegrowth=10%)
  log on
  (name=book_log,
   filename-'d:\book.ldf',
   size-12,
   maxsize=30,
   filegrowth=3)

3.修改数据库(数据库扩容)
  alter database book
  modify file
  (name='d:\book.mdf',
   size=60
  )

4.缩减数据库
  dbcc shrinkdatabase(batabase_name[,new_size['masteroverride']])
  dbcc shrinkdatabase('book')---将book数据库的空间缩减至最小容量

5.将book数据库设置为只读状态
  exec sp_dboption 'book','read only',true

6.数据库更名
  exec sp_renamedb 'book','shu'

7.删除数据库
  drop database book

8.自定义数据类型
  exec sp_addtype meetingday,samlldatetime,'not null'----创建
  exec sp_droptype 'meetingday'----删除

9.删除约束
  alter table table_name
  drop constraint constraint_name

10.增加列
  alter table book
  add author varchar null

11.删除列
  alter table book
  drop column author

12.修改列的数据类型
  alter table book
  alter column price smallmoney

13.查看表的结构信息
  exec sp_help table_name

14.删除数据
  delete from book where price=10000----删除一列
  delete from book----删除全部数据
  drop table table_name----删除表

15.check约束
  alter table book
  add
  constraint ck_book check(性别='男' or 性别='女')----添加约束

  alter table book
  drop constraint ck_book

16.default约束的创建、查看和删除
  alter table book
  add constraint 性别 default '男' for 性别----添加约束

  alter table book
  drop constraint de_性别

17.primary key的创建、查看和删除
  alter table book
  add comstraint pk_作者编号 primary key clustered(作者编号)----添加pk

  alter table book
  drop constraint pk_作者编号

18.foreign key的创建，查看和删除
  alter table book
  add comstraint pk_作者编号 primary key clustered(作者编号)----添加pk
 
  alter table bookin
  add constraint fk_作者编号 foreign key(作者编号)
  references book(作者编号)

19.使用默认default
  use book
  go
  create default mr_定价
  as '100'
  go----创建默认值

  exec sp_bindefault mr_定价,'book.定价'----绑定

20.规则的使用
  use book
  go
  create rule gz_定价
  as @定价>=0 and @定价<=10000
  go----创建规则

  exec sp_bindrule gz_定价,'book.定价'

21.数据语言DDL、DML、DCL
  DDL:create,alter,drop
  DML:select,insert,update,delete
  DCL:grant,revoke

22.使用distinct消除重复值
  select distinct id from book----消除重复ID

23.使用top n返回n行
  select top 5* from book----返回结果集中的前五行

24.别名使用
  select '图书编号'=编号, '图书书号'=ISBN号, '图书定价'=定价, '图书出版社'=出版社 from book
  select 编号'图书编号', ISBN号'图书书号', 定价'图书定价', 出版社'图书出版社' from book
  select 编号 as '图书编号', ISBN号 as '图书书号', 定价 as '图书定价', 出版社 as '图书出版社' from book

  select min(定价) as 最小定价,max(定价) as 最大定价, avg(定价) as 平均定价 from book

25.排序
  select 书名,定价 from book order by 定价 desc
  select 书名,编号,出版社 from book order by 书名desc, 编号asc

  select 书名 from book where 编号 in('XH4560','YBZ1245','YGH78')

26.like关键字、通配符
  %：匹配包含0个或者多个字符的字符串
  _：匹配任何单个的字符
  []:匹配任何在范围或者集合之内的单个字符
  [^]:匹配任何不在范围或者集合之内的单个字符

27.范围查询
  =、<、>、<>、!<、!>、!=、>=、<=、between、not between、not、and、or

28.使用compute子句
  compute子句用来计算总数或者进行分组小计，总计值或小计值将作为附加的新行出现在查询结果中；
  该子句用在where子句之后。
  select 编号,书名,ISBN号,定价,出版社 from book where 出版社='中国长安' compute avg(定价);

  compute by子句对by后面给出的列进行分组显示，并计算该列的分组小计。使用compute by子句时
  必须按照order by和compute by 中指定的列进行排序。
  select 编号,书名,定价,出版社 from book order by 出版社 compute avg(定价) by 出版社;

29.使用group by 子句
  将查询结果按照group by后面指定的列进行分组，该子句写在where子句的后面。
  当在select子句中包含聚合函数时，最适合使用group by子句
  select 出版社，count(出版社) as'本数' from book group by 出版社;

30.having子句
  having子句用于限制组或者聚合函数的查询条件。该子句常常用在group by子句之后，在结果集分
  组之后再进行判断。如果查询条件需要在分组之前被应用，则使用where子句，其限制查询条件比
  使用having子句更有效。

  select 出版社，avg(定价) as '平均价格' from book group by 出版社 having 出版社='中国长安'；

  可以在select子句和having子句中使用聚合函数，但是不能在where子句中使用他们。
  select 出版社,avg(定价) as'平均价格' from book group by 出版社 having avg(定价)>60;

31.使用嵌套查询
  select 编号,书名,定价 from book where 定价>(select avg(定价) from book)；

32.使用union运算符
  select 书名 from book1 union select 编号 from book2；

33.exists关键字
  exists子句用于测试跟随的子查询中的行是否存在，如果存在返回true。

  select 书名,定价 from book where exists(select 编号 from teacher);

34.索引
  create unique/clustered/nonclustered index index_name on table/view
  drop index table_name.index_name

  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'

35.是否显示查询计划
  set showplan_all on|off
  set showplan_text on|off

36.是否显示磁盘IO统计
  set statistics io on|off

  use book
  go
  set statistics io on
  go
  select * from book where 编号='HZF45798'
  go
  set statistics io off
  go

37.使用update statistics更新索引的统计信息
  use book
  go
  update statistics book ix_book
  go

38.使用dbcc showcontig语句扫描表
  use book
  go
  dbcc showcontig(book,ix_book)
  go

39.使用dbcc indexdefrag语句进行碎片整理
  use book
  go
  dbcc indexdefrag(库,表,索引)
  go

40.视图
  create view view_name
  [with encryption]
  as
  select_statement
  ----with encryption子句对视图进行加密
  
  use book
  go
  create view v_book
  as
  select * from book where 出版社='中国长安'----创建视图

  use book
  select * from v_book
  ----检查视图是否建立及视图的返回结果

  use book
  go
  alter view v_book
  with encryption
  as
  select 出版社,count(*)出版总数,sum(定价)出版总价 from book group by 出版社
  ----修改视图

  use book
  go
  drop view v_book
  ----删除视图

  use book
  go
  exec sp_helptext 'v_book'
  ----查看视图的定义信息

  use book
  go 
  exec sp_helptext 'v_bookbycbs'
  ----查看已加密的视图

  use book
  go 
  exec sp_depends 'v_bookbycbs' 
  ----获得视图对象的参照对象和字段

  use book
  go 
  select 书名,作者姓名 from v_book where 定价=85.8
  ----通过视图查询数据

  use book
  go 
  insert into v_book values('硬件测试',50)
  ----通过视图插入数据
 
41.存储过程
  use book
  go
  create procedure p_book
  as
  select * from book where 出版社='中国长安'
  ----创建存储过程

  use book
  go
  exec p_book
  ----执行存储过程

  create procedure p_bookNum
  @出版社 varchar(20),
  @bookNum smallint output
  as
  set @bookNum=
  (
   select count(*) from book where 出版社=@出版社
  )
  print @bookNum
  ----创建存储过程

  declare @出版社 varchar(20),
  declare @bookNum smallint
  set @出版社='中国长安'
  exec p_bookNum @出版社,@bookNum
  ----执行带参数的存储过程

  alter procedure procedure_name
  ----修改存储过程

  drop procedure p_bookNum
  ----删除存储过程

42.触发器的创建和管理
  create trigger trigger_name
  on(table|view)
  {for|after|instead of}{[insert]、[update]、[delete]}
  [with encryption]
  as
  if update(column_name)
  [{and|or}update(cloumn name)...]
  sql_statesments

  use book
  go
  create trigger book_trigger
  on book
  for insert
  as
  print'插入数据成功'
  go
  ----插入数据触发器

  use book
  go
  create trigger book_trigger
  on book
  instead of delete
  as
  print'数据删除不成功'
  go
  ----删除数据触发器

  use book
  go
  create trigger book_trigger
  on book
  for update
  as
  if update(定价)
  begin
    rollback transaction
  end
  ----更新数据触发器

  use book
  go
  create trigger book_trigger
  on book
  for delete
  as
  begin
    raiserror('Unauthorized',10,1)
    rollback transaction
  end
  ----更新数据触发器

43.查看触发器信息
  sp_help：查看触发器的一般信息
  sp_helptext：查看触发器的定义信息
  sp_depends：查看指定触发器所引用的表或者指定的表涉及的所有触发器

  exec sp_helptrigger table_name

  use book
  go
  select name from sysobjects where type='tr'
  go
  ----使用系统表sysobjects查看数据库表存在的所有触发器相关信息

44.修改触发器
  sp_rename oldname,newname
  ----修改触发器的名字

  use book
  go
  alter trigger book_trigger
  on book
  instead of delete,insert,update
  as
  print'你执行的删除、增加、修改无效'
  ----修改触发器

  drop trigger trigger_name
  ----删除触发器
  
45.禁止和启动触发器
  alter table book disable trigger all
  alter table book enable  trigger all

46.创建SQLServer登陆账户
  sp_addlogin[@loginname=]'login'[,[@password=]'password'][,[@defdb=]'database']
  [,[@deflanguage=]'language'][,[@sid=]sid][,[@encryptopt=]'encryption_option']

  use book
  exec sp_addlogin 'book-login','book'
  exec sp_adduser 'book_login'

  sp_adduser[@loginname=]'login'[,[@name_in_db=]'user'][,[@grpname=]'group']

  sp_helplogins
  ----查看登陆用户名

  exec sp_password 'old_password','new_password','login'
  ----修改密码

  exec sp_addsrvrolemember 'login','role'
  ----将登陆账号加入角色服务器

  exec sp_helpuser
  ----查看数据库用户账户

  exec sp_droplogin 'b_login'
  ----删除登陆账号

  exec sp_revokedbaccess 'b_user'
  ----删除用户
  
  exec sp_addrole 'myrole','dbo'
  ----添加角色

  exec droprole 'role'
  ----删除指定库的角色

47.权限
  use master
  go
  grant create database,create table
  to book_user1,book_user2
  ----给用户book_user1,book_user2授予建库、建表的权限

  use book
  go
  grant select on book1 to public
  go
  grant insert,update,delete on book1 to book_user1,book_user2
  ----给用户book_user1、book_user2授予对book1表的所有权限，先给public角色授予select权限，然
  后将特定的权限授予book_user1、book_user2

  use master
  go
  deny create database,create table
  to book_user1,book_user2
  ----拒绝给book_user1,book_user2授予多个语句权限

  revoke create table,create default
  from book_user1,book_user2
  ----撤销授予多个用户账户的多个权限

48.控制流语句
  if...else：当逻辑表达式为真的时候，执行语句1；else可以省略；必须放在begin...end语句块中；
  if 逻辑表达式
    语句1
  else
    语句2

  begin...end：用来定义语句块，常用于while循环、case条件

  declare：定义局部变量
 
  return：无条件退出批命令、存储过程或者触发器；返回0表明成功，返回负数代表出错；

  waitfor：用来暂时停止程序执行

  goto：改变程序执行的流程

49.事务
  begin/commit/rollback transaction

  事务的使用：alter database
       backup log
       create database
       disk init
       drop database
       dump transaction
       load database
        load transaction
       reconfigure
       restore database
       restore log
       update statistics

50.游标
  use book
  go
  declare Crsbook cursor
  for
  select * from book
  ----声明游标

  open Crsbook
  ----打开游标

  fetch next from Crsbook
  ----从游标中检索行

  update book set 出版社='中国商业行业' where current of Crsbook
  ----更新出版社

  delete from book where current of Crsbook
  ----删除记录

  close Crsbook
  ----关闭游标

  deallocate Crsbook
  ----释放游标

by： tony