sql.sql

这个是本人在培训学校上SQL课的总结,很全面的.初学者可以看看.

在过程中需要判断时，使用输出参数，如果没有判断可以只使用输入参数 */
5)使用通配符参数查询数据
create proc p_课程表
@课程名 varchar(12)='%'
as
select *from 课程表1
where 课程名 like @课程名
6)在过程中使用默认值
7)修改过程
alter proc 过程名
8)删除过程
drop proc 过程名
----------------
9)@@nestlevel:返回当前存储过程执行的嵌套层次（初始值为 0）
create proc p_level1
as
 print '层数'+cast(@@nestlevel as char)
go
----------------
create proc p_level2
as
 print '层数 '+cast(@@nestlevel as char)
 exec p_level1
go
--------
p_level3
--------
create proc p_level3
as
 print '层数 '+cast(@@nestlevel as char)
 exec p_level2
go
------
select @@nestlevel
--------------------------------
10)return:使用 return 语句返回值
----------------
create proc myout
@a char(10),@b char(10)
as
 update aa
  set a1=@a
 where a2=@b
if @@rowcount =0 --@@rowcount:返回上一条sql语句所影响行数
 return -1
else
 return 0 
------------
myout 'bbb','212'
-----
declare @rn int
exec @rn=myout 'bbb','222'
if @rn = -1
     print 'No change'
 else
   print'ok'
--------------
9.触发器
1）定义：触发器是一种实施复杂完整性约束的特殊存储过程，当对它所保护的数据进行修改时自动激活，
        防止对数据进行不正确的修改，不像存储过程，不能直接调有。
2）分类：
after/for:(事后触发器)指定触发器只有在触发sql语句中指定的所有操作都以完成后才触发，这种
         触发器也被习惯称为 for 触发器。先执行相应的 insert 或 delete 或 update 操作，
         把数据插入到表格中，执行相对触发器去检查，内容的正确性，
         如果错误执行rollback tran 语句，使系统回滚整个操作。
instead of:(替代触发器)指定执行触发器中的内容而不是执行引发触发器的sql语句，从而替代相映的
         sql 语句。
-------------------
不管那种触发器都执行 insert,delete,update 操作。
--------------------
3)创建：
create trigger tr_会计
on 会计表
for/instead of  update,insert,delete
as
 if -- (select count(*)from inserted)>0 and (select count(*)from deleted)>0
    exists(select 1 from inserted)and exists(select 1 from deleted)
   begin 
    insert  会计_log(who,[action])
     values(current_user,'UPDATE')
   end
 if --(select count(*)from inserted)>0 and (select count(*)from deleted)=0
   exists(select 1 from inserted)and not exists(select 1 from deleted)
    begin 
    insert  会计_log(who,[action])
     values(current_user,'INSERT')
   end 
 if --(select count(*)from inserted)=0 and (select count(*)from deleted)>0
   not exists(select 1 from inserted)and exists(select 1 from deleted)
   begin 
    insert  会计_log(who,[action])
     values(current_user,'DELETE')
   end
---------------
4)修改:
alter trigger 触发器名
-----------------------
5)删除:
drop trigger 触发器名
-----------------------
6)禁用和启用触发器
alter table 表名
disable trigger 触发器名
---------------------
alter table 表名
enable trigger 触发器名
------------------------
7)查看表中是否有触发器
sp_helptrigger ss--表名
-------------------------------------
8)为表中的列建立触发器
create trigger tr_update
on ss
for update
as
/*if exists(select 1 from inserted)and
   exists(select 1 from deleted)*/
if update(name)
 begin 
   rollback tran
  print'有人在修改数据库表中的名字'
 end
----------------
10.游标：在数据库中，select语句查询的结果是表结果集，如果要对行集结果进行操作时，
         就要使用游标。
1)分类：
1.静态游标：静态游标的完整结果集在游标打开时放在tempdb中，当用静态游标打开数据时，
           其他操作对数据的影响在游标中不显示。
2.动态游标
3.只进游标
4.键集驱动游标
------------------------
2)定义及使用：
declare cursor_ff cursor
for 
select *from ff         //声明游标
----------------
open cursor_ff          //打开游标
----------------
fetch next from cursor_ff //提取游标数据
----------------
close cursor_ff         //关闭游标
---------------- 
deallocate cursor_ff    //释放游标
----------------
--------------------------------------
DECLARE cursor_name CURSOR 
[ LOCAL | GLOBAL ]   --游标的作用范围
[ FORWARD_ONLY | SCROLL ]                    --//游标
 --只进
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] --//类型
--静态     键集       动态     快速只进
/*[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
--只读         只进锁         允许更新
[ TYPE_WARNING ] */
FOR select_statement 
--游标所要使用的sql 语句
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
--游标的动作（更新，只读）
-------------------------------------------
--打开游标：
open cursor_name
-------------------
--提取游标：
fetch
从 Transact-SQL 服务器游标中检索特定的一行。
语法:
FETCH
        [ [ NEXT | PRIOR | FIRST | LAST
                | ABSOLUTE { n | @nvar }
                | RELATIVE { n | @nvar }
            ] 
            FROM
        ] 

next:下一个
prior:返回紧临当前行前面的结果行，并且当前行递
      减为结果行。如果 FETCH PRIOR 为对游标的
      第一次提取操作，则没有行返回并且游标置于
      第一行之前。
first:返回表中的第一条数据
last:返回表中的最后一条数据
absolute:
relative:
-----------------------------------
--关闭游标：
close cursor_name
-----------
--释放游标
deallocate cursor_name
---------------------------------
静态:只支持 next
declare  cur_aa  cursor static
for select *from ff
    order by 编号 desc
open cur_aa
fetch next from cur_aa
close cur_aa
deallocate cur_aa
-------------------
动态:不支持 absolute
------------------
declare  cur_aa  cursor dynamic
for select *from ff
    order by 编号 desc
open cur_aa
fetch next from cur_aa
fetch relative -1 from cur_aa
fetch first from cur_aa
fetch prior from cur_aa
fetch last from cur_aa
fetch absolute -1 from cur_aa
close cur_aa
deallocate cur_aa
------------------------
只进：只能应用 next
declare  cur_aa  cursor forward_only
for select *from ff
    order by 编号 desc
open cur_aa
fetch next from cur_aa
--//fetch relative -1 from cur_aa
--//fetch absolute 1 from cur_aa
--//fetch first from cur_aa
--//fetch last from cur_aa
--//fetch prior from cur_aa
close cur_aa
deallocate cur_aa
------------------
动态游标不支持 absolute
declare  cur_ssss  cursor dynamic
for select *from ff
    --order by 编号 desc
open cur_ssss

fetch absolute -1 from cur_ssss
 update ff
  set 姓名='sff'
 where 编号=110
close cur_ssss
deallocate cur_ssss
-----------------------------------
使用游标更改删除数据
-----------------------------------
create table 设备表
(颜色 char(10),
 类别 char(5),
数量 int)
------------------------
insert into 设备表
values('红色','桌子',176)
------------------------
select*from 设备表
------------------------
select 颜色,类别,sum(数量)as 总数
from 设备表
group by 颜色,类别
with cube
-------------
cube:统计函数
-------------
select 颜色,类别,sum(数量)as 总数
from 设备表
group by 颜色,类别
with rollup
-----------------------
使用游标更改删除数据
----------------------
fetch next|first|prior|last|relative|absolute 
 from cursor_name
update table_name|view_name
 set column_name='value'
 where column_name='expression'
--------------------------------
delete from table_name|view_name
where column_name='expression'
-----------------------------------
使用游标更改删除数据
游标必须声明为 for update
----------------------
fetch next|first|prior|last|relative|absolute 
 from cursor_name
update table_name|view_name
 set column_name='value'
where current of  cursor_name
 --where column_name='expression'
--------------------------------
delete from table_name|view_name
where current of  cursor_name
--where column_name='expression'
--------------------------------
declare cur_ff cursor  (static or dynamic or forward_only)
for select*from ff
for update 
open cur_ff
fetch next from cur_ff
update ff           ---修改
set 编号=500
where current of cur_ff
close cur_ff
deallocate cur_ff
--------------------------------
declare cur_ff cursor 
for select*from ff
for update 
open cur_ff
fetch next from cur_ff
delete from  ff           ---删除
where current of cur_ff
close cur_ff
deallocate cur_ff
---------------------------
sp_addumpdevice:添加备份设备的命令
sp_addumpdevice 
将备份设备添加到 Microsoft? SQL Server?。

语法
sp_addumpdevice [ @devtype = ] 'device_type' ,
    [ @logicalname = ] 'logical_name' ,
    [ @physicalname = ] 'physical_name'
------------------------------
sp_addumpdevice 'disk','bakup','f:\bakup.bak'
------------------------------
backup database:备份数据库
---------------------
备份整个数据库：

BACKUP DATABASE { database_name | @database_name_var } 
TO < backup_device > [ ,...n ] 
----------------------------------
backup database pubs to bakup
----------------------------------
restore database:还原数据库
---------------------------
restore database pubs from bakup
--------------------------------
sp_addlogin:添加登陆
------------------
语法
sp_addlogin [ @loginame = ] 'login'---登陆名
    [ , [ @passwd = ] 'password' ] ---密码
    [ , [ @defdb = ] 'database' ]  ---数据库
    [ , [ @deflanguage = ] 'language' ] ---语言
    [ , [ @sid = ] sid ]           ---系统标识符
    [ , [ @encryptopt = ] 'encryption_option' ] ---加密
---------------------------------
添加用户：
sp_addlogin 'user1','abcdef','pubs'
----------------
查看登陆
select *from syslogins
----------------------------
修改和删除账户
------------------
---改变默认数据库：
sp_defaultdb user1,pubs
-----------------
---改变默认语言
sp_defaultlanguage user1,english
-----------------
--删除登陆
sp_droplogin aa
-----------------------------
---数据库权限
sp_addlogin aa,aa,human
----------------------
--指定访问数据库
use human
go
sp_grantdbaccess aa
--撤销对数据库的访问
sp_revokedbaccess aa
---------------------------
--查看固定服务器角色
sp_helpsrvrole
---------------------
--授予aa服务器角色
sp_addsrvrolemember aa,'sysadmin'
--删出
sp_dropsrvrolemember aa,'sysadmin'
---------------------
权限： insert,update,delete,select,create
-------------------------------------------
授权的分类：
1.对象权限: insert,update,delete,select
2.语句(命令)权限: create
----------------------
授权语法：1.对象权限
grant [permission] on [table|view] to [user]
----------------------
grant insert on aa to aa
----------------------
grant delete on aa to aa
----------------------
grant update on aa to aa
-----------------------
授权语法：2.语句(命令)权限
grant [permission] to [user]
---------------------------
grant create table to aa
---------------------------
grant create database to aa