sql server

• • • • 数据库的操作
      • sql server高级
        • 内建函数之string部分
        • 内建函数之math部分
        • 内建函数之date部分
        • 内建函数之聚合函数
        • 内建函数之系统统计函数
        • 事务
      • 储存过程
      • 触发器
      • 游标
      • 创建用户定义树数据类型与函数

数据库的操作

SELECT name FROM master.dbo.sysdatabases--查看拥有的数据库，dbid>6时属于非系统的数据库

use databasename
select * from sysobjects where xtype='U' --查询数据库的所有的表格
C = CHECK 约束 
D = 默认值或 DEFAULT 约束 
F = FOREIGN KEY 约束 
L = 日志 
FN = 标量函数 
IF = 内嵌表函数 
P = 存储过程 
PK = PRIMARY KEY 约束（类型是 K） 
RF = 复制筛选存储过程 
S = 系统表 
TF = 表函数 
TR = 触发器 
U = 用户表 
UQ = UNIQUE 约束（类型是 K） 
V = 视图 
X = 扩展存储过程

登录名

create login  dd with password='zxw123', default_database=master--增加用户
--添加windows用户的主体
create login [zhbzxw\zz]    --机器名称\用户名
from windows
with default_database = master,      --默认数据库
     default_language = 简体中文  --登录名的默认语言
--查看windows的登录名
select s.name,
       s.principal_id,
       s.sid,
       s.type_desc,
       s.is_disabled
from sys.server_principals s
where s.type_desc in ('WINDOWS_LOGIN',
                      'WINDOWS_GROUP')
--改变默认的数据库和语言
alter login [zhbzxw\zz]
with default_database = master,
     default_language = english
--禁用和启用登录名
  alter login [zhbzxw\zz] disable     
   alter login [zhbzxw\zz] enable
--删除用户
drop login [zhbzxw\zz]
--sql server主体

--1.1创建sql登录名
create login zz with password = 'zhbzxw',default_database = master
 --1.2创建登录名，同时设置密码策略
create login zz1
with password = 'zhbzxw' must_change,  --在第一次登录时会提示修改密码

     check_expiration = on,   --对sql登录名强制实施密码过期策略
     check_policy = on        --把Windows密码策略应用到sql登录名上
--查看登录名
select name,
       principal_id,
       sid,
       type_desc,
       is_disabled
from sys.server_principals 
where type_desc in ('SQL_LOGIN')
--修改用户
alter login ggg
with name = www,
     password = '12345678'
     default_database = master
 --删除用户
drop login www
--服务器角色
-4.1查看固定服务器角色
select name,
       principal_id,
       sid,
       type_desc,
       is_disabled
from sys.server_principals 
where type_desc = 'SERVER_ROLE'


--显示固定服务器角色
exec sp_helpsrvrole


--4.2把登录名添加到服务器角色
exec sp_addsrvrolemember 
    @loginame = 'ggg',
    @rolename = 'sysadmin'


--4.3删除服务器角色成员,这是就算ggg已经登录到sql server,也会导致ggg无法继续操作
exec sp_dropsrvrolemember 
    @loginame = 'ggg',
    @rolename = 'sysadmin'


--4.4显示固定服务器角色的成员
select p.name,          --固定服务器角色
       p.principal_id,
       p.sid,

       pp.name,         --成员
       pp.principal_id,
       pp.sid
from sys.server_principals p
inner join sys.server_role_members r    
        on p.principal_id = r.role_principal_id
inner join sys.server_principals pp
        on pp.principal_id = r.member_principal_id        
where p.name = 'sysadmin'


--显示服务器角色的成员
exec sp_helpsrvrolemember
    @srvrolename = 'sysadmin'

用户

--创建数据库用户
create user ggg
for login ggg
with default_schema = ggg  --用户的默认架构，如果不存在，会自动创建
--查看角色详细信息
exec sp_helpuser 
    @name_in_db = 'ggg'
--查看拥有的角色
select*
from sys.database_principals
where type_desc = 'SQL_USER'
--修改数据库用户and 删除
alter user zz1
with default_schema = dbo, name = wwwc   
drop user zz1
ALTER USER GGG WITH LOGIN = GGG
--3.用户定义的数据库角色
--3.1创建
create role wc_role authorization db_owner 


--3.2修改角色名称
alter role wc_role
with name = w_role


--3.3给角色添加用户
exec sp_addrolemember
    @rolename = 'w_role',
    @membername = 'ggg'


--3.4从角色中删除用户
exec sp_droprolemember
    @rolename = 'w_role',
    @membername = 'ggg'


--3.5删除
drop role wc_role
--4.应用程序角色
--4.1创建
create application role www_c
with password = '123',
     default_schema = dbo


--4.2修改
alter application role www_c
with name = wwc,
     password = '12345'


--4.3授予权限
grant select on dbo.wcObjects to wwc


--4.4**应用程序角色
exec sp_setapprole 
    @rolename = 'wwc',
    @password ='12345'


--4.5查询数据
select * from dbo.wcObjects


--4.6拒绝了对对象 'book' (数据库 'wc'，架构 'dbo')的 SELECT 权限。
select * from dbo.book


--4.7删除应用程序角色
drop application role wwc
--查询当前连接的主机的域用户名
select *
from sys.server_principals
where type_desc in ('WINDOWS_LOGIN','WINDOWS_GROUP')
and CHARINDEX('NT',name)=0

其他

--top
1.查询前Ｎ条记录      SELECT TOP N * FROM 表名

2.按照百分比查询记录（关键字PERCENT） SELECT TOP N PERCENT FROM 表名  
--局部变量
DECLARE @变量名 数据类型--声明
SET @变量名 = 值--赋值1
SELECT @变量名 = 值--赋值2
--全局变量
--全局变量由系统定义和维护，用户只能读取，不能赋值，也不允许用户定义全局变量。全局变量是有两个at符号（@）作为前缀的 
全局变量
含义
@@ERROR 
最后一个T-SQL错误的错误号。
@@IDENTITY
最后一次插入的标识列 。
@@CURSOR_ROWS
返回连接上打开的上一个游标中的当前限定行的数目。
@@FETCH_STATUS
返回针对连接当前打开的任何游标发出的上一条游标 FETCH 语句的状态。
@@ROWCOUNT 
受上一个SQL语句影响的行数。
@@SERVERNAME
本地服务器的名称。
@@SERVICENAME
该计算机上的SQL服务的名称。
@@TIMETICKS
当前计算机上每刻度的微秒数。
@@TRANSCOUNT 
当前连接打开的事务数。
@@VERSION
SQL Server的版本信息。

--tsql的输出语句
SELECT 和PRINT

--条件判断语句
IF (条件) 
BEGIN   
  语句块1
END
ELSE IF(条件)
BEGIN
  语句块2
END

--case语句
CASE
WHEN  条件1 THEN 结果1 
WHEN  条件2 THEN 结果2
…
[ELSE 其他结果] 
END

SELECT '学号'= Scores.StuID, '姓名'=StuName,'笔试成绩'=   

          CASE

              WHEN WrittenScore < 60 THEN 'E'   

              WHEN WrittenScore BETWEEN 60 AND 69 THEN 'D'   

              WHEN WrittenScore BETWEEN 70 AND 79 THEN 'C'

              WHEN WrittenScore BETWEEN 80 AND 89 THEN 'B'

              ELSE 'A'     

          END

       FROM infos INNER JOIN Scores

       ON infos.StuID = Scores.StuID

--循环语句
WHILE (条件) 
BEGIN    
  语句块
  [BREAK]  
  [CONTINUE]    
END

例子本次考试笔试成绩较差，现在要为笔试不及格学员加分。加分规则是先将笔试不及格学员加2分，然后看是否都通过，如果还有未通过学员再加2分，再看是否都通过，如此循环加分直到笔试都及格为止，最后显示总共加了多少分。

DECLARE @count INT

DECLARE @number INT

SET NOCOUNT ON --不显示受影响的行数

SELECT @count = COUNT(*) FROM scores WHERE WrittenScore<60   

SET @number = 0    

WHILE( @count> 0 )   

BEGIN

  UPDATE scores SET WrittenScore = WrittenScore + 2 WHERE WrittenScore < 60  

  SET @number = @number + 2   

  SELECT @count = COUNT(*) FROM scores WHERE WrittenScore<60   

END

PRINT '累计加分' + CONVERT(VARCHAR(2),@number) 

--高级查询
all
any
in not in
exists not exists
distinct
union

sql server高级

内建函数之string部分

--charindex('want to find str','strings','start location')
select charindex('林','108好汉中有林冲',1)  '8'
--return left char and len=2
select left('my name is zylg',2) my
--return right char and len=4
select right('my name is zylg',4) zylg
--return len 
select len('my name is zylg') '15'
--change become lower
select lower('MY NAME IS ZYLG') 'my name is zylg'
--change become upper
select UPPER('my name is zylg') 'MY NAME IS ZYLG'
select LTRIM('            hello') 'hello'
select rtrim('hello          ') 'hello'
select REPLACE('my name is zylg','zylg','zhbzxw') 'my name is zhbzxw'
select REVERSE('my name is zylg') 'glyz si eman ym'
--str(转化的数字，要转化的长度，小数的位数)
select STR(1245.7856,6,2) '1245.8'
select SUBSTRING('hello',1,2) he

内建函数之math部分

select PI()
select FLOOR(2.55)--向下取整
select CEILING(2.55)--向上取整
select POWER(5,3) --5^3
select SQRT(25)   --5
SELECT RAND()     --(0,1)random
select ROUND(2.51,0)    --四舍五入,参数二为小数的位数
--of course have many function,but want not to write        

内建函数之date部分

print getdate()
print datepart(yy,getdate())
print dateadd(yy,5,getdate())--年份加上5
print datediff(dd,getdate(),dateadd(mm,5,getdate()))--相隔时间数
select day(getdate()),month(getdate()),year(getdate())

内建函数之聚合函数

avg
max
min
sum
count

内建函数之系统统计函数

用不太到，不写了

事务

BEGIN TRAN --开始事务       
DECLARE @error int       
SET @error = 0     
UPDATE Bank SET CurrentMoney = CurrentMoney - 1000
WHERE CustomerID='10012213' 
SET @error = @error [email protected]@ERROR
UPDATE Bank SET CurrentMoney = CurrentMoney + 1000
WHERE CustomerID='13012215'                             
SET @error = @error [email protected]@ERROR
IF @error>0
ROLLBACK TRAN  --回滚事务 
ELSE
COMMIT TRAN     --提交事务

事务的分类

显示事务，有明确的开始，回滚和提交
隐式事务，一般没有明确的事务的开始
自动提交事务，每一个sql语句就是一个自动提交的事务

事务的特性

1.原子性：事务是不可分的，如果事务中任何操作失败，则事务失败
2.一致性：当事务结束时，事务必须一致的状态
3.隔离性：对数据操作的若干事务相互隔离，不会产生相互依赖的情况
4.持久性：一旦发生提交，则数据将会发生改变，一般情况下不能撤销

储存过程

几个系统常用的储存过程

名称	功能
sp_databases	列出SQL Server实例中的数据库
sp_tables	当前环境中查询的对象列表
sp_columns	当前环境中查询的指定表或视图的列信息
sp_grantlogin	创建SQL Server登录名
sp_grantdbaccess	将数据库用户添加到当前数据库
xp_cmdshell	操作系统命令行解释器的方式执行给定的命令字符串
xp_logininfo	返回Windows用户和Windows组信息
xp_grantlogin	授予Windows组或用户对SQL Server的访问权限

--对cmdshell进行授权
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

--小例子
DECLARE @cmd varchar, @var varchar
SET @var = 'hello zylg'
SET @cmd = 'echo ' + @var + ' > D:/xiaozhu.txt'
EXEC master..xp_cmdshell @cmd

调用过程

EXEC[UTE] 存储过程名字 参数列表

创建储存过程

CREATE PROCE[DURE] 存储过程名
参数名 数据类型 [= 默认值],   
... ...,
参数名 数据类型 [= 默认值]
AS
BEGIN
   ...
   SQL语句
   ...
END
GO

无参数

create proc showdatabase as
begin 
select * from   dbo.MSreplication_options
end
go
exec showdatabase

–创建存储过程 求最大值

drop proc [dbo].[P_Max]
go
CREATE PROCEDURE [dbo].[P_Max]
@a int, -- 输入
@b int, -- 输入
@Returnc int output --输出
AS 

if (@a>@b) 
   set @Returnc [email protected]
else
   set @Returnc [email protected]
return (10)
-- 调用
declare @Returnc int,@zylg int
exec @zylg=P_Max  2,3,@Returnc output 
select @Returnc
select @zylg
--output让变量可以从函数里面赋值，然后在外面使用，return自己体会。
CREATE PROCEDURE [dbo].[P_Max]
with encryption
as
加密储存过程的定义

修改和删除储存过程

alter proc name...在我看来像是冲定义一样
drop proc name

触发器

--创建触发器
CREATE TRIGGER 触发器名
ON 表名 FOR 触发器操作类型 
AS
BEGIN
...
触发器SQL语句
...
END
GO
--修改触发器
AlTER TRIGGER 触发器名
…
--删除触发器
DROP TRIGGER 触发器名

USE BankDB
GO
CREATE TRIGGER trInsert
ON UserInfo FOR INSERT --插入的触发器
AS
BEGIN
   --定义变量
   DECLARE @CardID VARCHAR(5)
   DECLARE @Accounts VARCHAR(20)
   DECLARE @Balance MONEY
   --从临时表中获取插入数据
   SELECT @CardID = CardID,@Accounts = Accounts,@Balance = Balance
          FROM INSERTED  --在插入的临时表inserted寻求插入
   --判断插入数据是否为空
   IF @CardID IS NOT NULL
          INSERT INTO UserLog Values (@CardID,@Accounts,'开户',@Balance,GetDate())  进行插入 
END
GO

USE BankDB
GO
CREATE TRIGGER trDelete
ON UserInfo FOR DELETE
AS
BEGIN
   DECLARE @CardID VARCHAR(5)
   DECLARE @Accounts VARCHAR(20)
   DECLARE @Balance MONEY
   SELECT @CardID = CardID,@Accounts = Accounts,@Balance = Balance
          FROM DELETED   ①
   IF @CardID IS NOT NULL   
          INSERT INTO UserLog Values (@CardID,@Accounts,'销户',@Balance,GetDate())
END
GO

USE BankDB
GO
CREATE TRIGGER trUpdate
ON UserInfo FOR Update
AS
BEGIN
   --定义变量
   DECLARE @CardID VARCHAR(5)
   DECLARE @Accounts VARCHAR(20)
   DECLARE @OldBalance MONEY
   DECLARE @NewBalance MONEY
   --获取更新前的数据
   SELECT @CardID = CardID,@Accounts = Accounts,@OldBalance = Balance
          FROM DELETED
   --获取更新后的数据
   SELECT @NewBalance = Balance
          FROM INSERTED WHERE CardID = @CardID
   --通过余额变换判断操作类型
   IF @NewBalance > @OldBalance
   BEGIN
          INSERT INTO UserLog Values (@CardID,@Accounts,'存钱',(@NewBalance - @OldBalance),GetDate())
   END
   ELSE IF @NewBalance < @OldBalance
   BEGIN
          INSERT INTO UserLog Values (@CardID,@Accounts,'取钱',(@OldBalance - @NewBalance),GetDate())
   END
END
GO

ALTER TRIGGER trUpdate
ON UserInfo FOR Update
AS
BEGIN
   --定义变量
   DECLARE @CardID VARCHAR(5)
   DECLARE @Accounts VARCHAR(20)
   DECLARE @OldBalance MONEY
   DECLARE @NewBalance MONEY
   --获取更新前的数据
   SELECT @CardID = CardID,@Accounts = Accounts,@OldBalance = Balance
          FROM DELETED
   --获取更新后的数据
   SELECT @NewBalance = Balance
          FROM INSERTED WHERE CardID = @CardID
   --回滚操作
   IF @NewBalance < 0
   BEGIN
          ROLLBACK
   END
   --通过余额变换判断操作类型
   IF @NewBalance > @OldBalance
   BEGIN
          INSERT INTO UserLog Values (@CardID,@Accounts,'存钱',(@NewBalance - @OldBalance),GetDate())
   END
   ELSE IF @NewBalance < @OldBalance
   BEGIN
          INSERT INTO UserLog Values (@CardID,@Accounts,'取钱',(@OldBalance - @NewBalance),GetDate())
   END
END
GO

use mydb
go
create trigger iner
on students for insert,update,delete
as 
begin
print '数据发生了变动'
end

查看触发器

--查看数据库中所有的触发器
use 数据库名
go
select * from sysobjects where xtype='TR'

查看触发器的定义

use 数据库名
go
exec sp_helptext '触发器名称'

查看触发器属性

use mydb
go
exec sp_helptrigger students

禁用和启用触发器

　禁用:alter table 表名 disable trigger 触发器名称
　　启用:alter table 表名 enable trigger 触发器名称

游标

1.创建游标
例子：
定义一个游标，返回公司信息中的所有数据，大开游标遍历公司的信息表，直到找到名称为“zylg”位置，打印公司的代码和名称

declear company_cursor cursor for select companyid ,companyname from company 
declear @id varchar(10),@name varchar(30)
open company_cursor
fetch next from company_cursor into @id,@name while @@fetch_status=0
begin
    if @name="zylg"
    begin
        print("找到目标地址")
        print（@id+@name）
        break
    end
    fetch next from company_cursor into @id,@name
end

滚动的游标

declear company_cursor scroll cursor for select companyid ,companyname from company 
open company_cursor
--转到最后一行
fetch last from company_cursor
--转到当前的上一行
fetch prior from company_cursor
--转到第二行
fetch absolute 2 from companr_cursor
--转到前两行
fetch relative -2 from company_cursor
--转到当前行的下两行
fetch relative 2 from company_cursor

关闭游标
close name
deallocate name

使用：

declare m_cursor cursor scroll for
select  Address,PeopleId from  PeopleDetail

open m_cursor
declare   @Address varchar(50), @PeopleId int

fetch next from m_cursor into @Address,@PeopleId
while @@FETCH_STATUS=0
begin
    print @Address + convert(varchar(3), @PeopleId)
    fetch next from m_cursor into @Address,@PeopleId
end

close m_cursor
deallocate m_cursor

创建用户定义树数据类型与函数

create type nstext from varchar(20) null--定义了一个20字节的可以为空的数据类型
drop type nstext--删除

use mydb
go
create function myfunction5 (@char1 varchar(10),@char2 varchar(10) ,@char3 varchar(10) )
returns varchar ---呃，returns,行吧
as
begin
declare @newchar varchar(10)
set  @newchar = replace(@char1,@char2,@char3)
return(@newchar) 
end

drop function myfunction