SQLSqlserver珍藏多年的小笔记

--用户创建自定义数据类型,MR_NXT 是库
use MR_NXT 
exec sp_addtype postcode ,'char(8)','not null'

--SQL Server2008 提供多种强制数据完整性的机制
--(1)空值和非空值(NULL 或者 NOT NULL)
    --允许空值(NULL) 默认情况下,列循序为空值,即允许用户在添加数据时省略该列的值
    --不允许空值(NOT NULL)不允许在没有指定列默认值的情况下忽略该列的值
--(2)默认值(default)
    --如果在插入行是没有指定列的值,那么默认值将指定列中所使用的值,默认值可以是去任何取值为常量的对象
    --在Create Table中使用Default关键字创建默认定义,将常量表达式值派为列的默认值,这是标准方法
    --在Create Defalut语句创建默认对象,然后使用sp_bindefalut系统存储过程将它绑定到列上,这是一种向前兼容的功能
--(3)特定标识属性(Identity)标识列自增长
    --数据库中如果某列被指派定标识属性(Identity),系统将自动为表插入的新行生成连续递增的编号,
    --因为标识值通常唯一,所以标识列常定义为主键(PK,primary key)
    --一个列不能同时具有NULL属性和IDentity属性,二者只能选其一
--(4)约束(constraints)
    --SQL是用来定义SQLServer2008自动强制数据库完整性的方式,使用约束优先于使用触发器、规则和默认值,下面有5中约束
    --非空(NOT NULL)使用户必须在表的指定列中输入一个值,每个表中可以有多个非空约束
    --检查(CHECK)用来指定一个布尔操作,限制输入到表中的值
    --唯一性(Unique)使用户的应用程序必须向列中输入一个唯一的值,值不能重复,但可以为空
    --主键(PK,Primary key)建立一列或多列的组合以唯一标识表中的每一行,主键可以保证实体完整性,一个表只能有一个主键,同时主键中的列不能接受空值
    --外键(FK,foreign key)用于建立和加强两个表数据之间的链接的一列或多列,当一个表中作为主键的一列被添加到另一个表中时,链接就建立了,主要目的是控制存储在外键表中的数据


--备份类型
    --完整备份:完整备份包括特定数据库(或者一组特定的文件组或文件)中的所有数据,以及可以恢复这些数据的足够的日志
    --差异备份:差异备份基于数据的最新备份,这称为差异的基准或者差异基准,差异基准是读/写数据的完整备份,差异备份仅包括自建立差异基准后发生更改的数据,
    --通常,建立基准备份之后很短时间内执行的差异备份的基准更小,创建速度也更快,因此,使用差异备份可以加快进行频繁备份的速度,从而降低数据丢失的风险
    --文件备份:可以分别备份和还原数据库中的文件,使用文件备份能够只还原损坏的文件.而不用还原数据库的其余部分,从而加快了恢复恢复

--恢复模式
    --简单恢复:允许将数据库恢复到最新的备份,简单恢复更容易管理,但如果数据文件损坏,出现数据丢失的风险系数会很高
    --完整恢复:允许将数据库恢复到故障点状态,完整恢复提供了最大的灵活性,使数据库可以恢复到早期时间点,在最大范围内防止出现故障时丢失数据,
    --于简单恢复类型相比,完整恢复模式和大容量日志恢复模式向数据提供更多的保护
    --大容量日志恢复:允许大容量日志记录操作,大容量日志恢复模式是对完全恢复模式的补充,对某些大规模操作(如创建索引或大容量复制),它比完全恢复模式性能
    --更高,占用的日志空间会更少,不过,大容量日志恢复模式会降低时点恢复的灵活性。

--T-SQL语言的组成
    --数据定义语言(Data Definition Language,DDL):用于在数据库系统中对数据库、表、视图、索引等数据库对象进行创建和管理 
    --数据控制语言(Data Control Language,DCL):用于在数据库系统中数据的完整性、安全性等的控制 
    --数据操纵语言(Data Mainpulation Language,DML):用于插入、修改、删除和查询数据库中的数据

--T-SQL语言结构语法如下:
    --SELECT 子句 [into 子句] from [子句] [where 子句] [group by 子句] [having 子句] [order by 子句]
    --查询表中女的信息
    --select * from student where Sex='女' order by StuAge
    --数据常量的各个位之间不要加逗号,例如,123123这个数字不能表示为: 123,123,浮点常量使用符号e来指定,例如:1.5e3,-3.14e1,  e被读作"乘10的几次幂"
    --日期和时间常量,SQL规定日期、时间和时间间隔的常量值被指定为日期和事件的常量,例如: '2018-11-14', yyyy-MM-dd(时间格式),
    --Current_Date、SYSDate和getDate()三个获取当前时间的区别: Current_date 返回的是当前会话时间,而sysDate返回的是服务器时间,Date返回的是当前客户端的时间

--局部变量,局部变量名必须以@开头

    --（1）声明局部变量需要使用Declare
        --Declare{ @varable_name datatyp [...]} 
        --参数说明 @varable_name的变量必须以@开头,另外变量名的形式必须符合SQLserver标识符的命名方法
        --dataType:局部变量使用的数据类型,可以是除了Text、ntext或者image类型以外的系统数据类型
        --声明局部变量 @songname的SQL语句以下:
        --declate @songaname char(10)
    --（2）为局部变量赋值,一种是select语句,另一种是set语句,使用select语句为变量赋值的语法如下
        --SEELCT @varable_name=expression  from table_name where clause
--注释符 可以使用ANSI标准的注释符(--),用于单行注释,多行注释(/*开头*/结尾)    

--运算符是一种符号,用来进行常量、变量或者列之间的数字列算和比较操作,T-SQL语言运算符有:算法运算符、赋值运算符、比较运算符、逻辑运算符、位运算符、连接运算符
    --算法运算符在两个表达式上执行数字运算,这两个表达式可以是数字数据类型分类:(+、-、*、/)
    declare    @x int ,@y int ,@z int 
    select @x=2,@y=5
    set @z=@x%@y
    print @z

--赋值运算符即赋值运算符,即等号(=),使用赋值运算符将参数设置成一个由表达式返回的值
    --第一种解决方式
    declare @songname char(20)
    set @songname='loving'
    print @songname
    --第二种解决方式
    declare @song_name char(20)
    select @song_name='loveIng'
    print @song_name 

--比较运算符测试两个表达式是否相同,除了text/ntextt或image数据类型的表达式外,比较运算符可以用于所有的表达式,表达式()
    use OA4T111
    select * from userInfo where sex='女' and  PassWOrd=1

--位运算符的操作数可以是整数数据类型或二进制串数据类型(image数据类型除外)
    --    %    按位and
    --    |    按位or
    --    ^    按位互斥or
    --    ~    按位not

--连接运算符“+”用于连接两个或两个以上的字符或二进制串、列名或串和列的混合体,将一个串加入到另一个串的末尾
    declare @name char(20)
    set @name=' 最爱'
    print '我喜欢的电影是'+@name

--通配符是指匹配指定范围内或者属于方括号所指定的集合中的任意单个字符,可以在涉及模式匹配的字符串比较( Like,Patindex)
    -- like        %a%        是指包含a的模糊查询
    select patindex('%abb%','abcaabbeeabb')
    --patindex    select patindex(%abb%,'abcaavveeabb') 结果是5,也就是abb第一次出现的位置

--流程控制语句
    --begin    end        语句用于将多个Transact-SQL语句组合为一个逻辑块,当流程控制语句必须执行一个包含两条或两条以上的T-SQL语句
        begin    
        select * from Role
        end

        --在begin ...end语句中完成把两个变量的值交换 
        declare @tempx int , @tempy int ,@t int
        set @tempx=1
        set @tempy=2
        begin
            set @t=@tempx
            set @tempx=@tempy
            set @tempy=@t
        end
        print @tempx
        print @tempy

    -- if else   IF<表达式> {命令行|程序块} else {命令行1|程序块1}
        --判断数字3是否是正数
        declare @ift int 
        set @ift=3
        if @ift>0
        print '@ift是正数'
        print 'end'
        
        --判断数字8的奇偶行
        declare @ifnum int 
        set @ifnum=8
        if @ifnum%2=0
        print '@ifnum是奇数'
        print 'end'

        --判断两个数8和3的大小
        declare @ifnum8 int,@ifnum3 int  
        set @ifnum8=8
        set @ifnum3=3
        if  @ifnum8>@ifnum3
        print '@ifnum8大于@ifnum3'
        else
        print  '@ifnum3小于@ifnum8'

        --输入一个坐标值,然后判断它在哪一个象限
        declare @ifnuma int,@ifnumb int  
        set @ifnuma=8
        set @ifnumb=-3
        if  @ifnuma>0
            if @ifnumb>0
                print '位于第一象限'
            else
                print '位于第四象限'
        else
            if @ifnumb>0
                print '位于第二象限'
            else
                print '位于第三象限'
    
    --case好处可以避免编写多重if than嵌套循环
        use  mr_nxt
        go
        select *, 备注=case 
        when grade>=90 then '成绩优秀'
        when grade>=80 then '成绩良好'
        when grade>=70 then '成绩及格'
        else '不合格'
        end
        from  [dbo].[tb_Grade]


    --while 在条件为真的情况下,while子句可以循环地执行其后的一条T-SQL语句
        --求1-10之间的整数的和
        declare @n int ,@sum int 
        set @n=1
        set @sum=0
        while @n<=10
        begin
            set @sum+=@n    --也可以使用 set @sum= @sum+@n 
            set @n+=1
        end
        print @sum
    --continue  命令可以让程序跳过continue命令之后的语句
    --break
        --求1-10之间的偶数的和,并用continue控制语句输出
            declare @x1 int, @sumoushu int
            set @x1=1
            set @sumoushu=0
            while @x1<10
            begin
                set @x1+=1
                if @x1%2=0
                    set @sumoushu+=@x1
                else
                    continue
            end
            print @sumoushu

    --return 语句用于查询或过程无条件退出,return语句可在任何时候用于从过程、批处理或语句块退出,位于return之后的语句不会被执行,
            --语法    return [整数值]
            declare @xreturn int 
            set @xreturn=3
            if @xreturn>0
            print '遇到return之前'
            return 
            print '遇到return之后'
    
    --goto    命令用来改变程序执行的流程,使流程跳到标识符指定的程序再继续往下执行
            --语法 goro 标识符需要在其后面加上一个冒号 ":"
            declare @xgoto int 
            select @xgoto=1
            loving:
                print @xgoto
                select @xgoto+=1
            while @xgoto<=3 goto loving

    --waitfor  触发器

     waitfor delay '00:00:03'
     print '生日快乐小可爱111'

     waitfor time '23:22:10'
     print '生日快乐小可爱222'
    
    
    --T-SQL语句操作数据库之
    --数据库的创建
    create Database Mrkj on primary
    (
        name=Mrkj_data,
        FileName='E:SQLDataDataBaseMrkj.mdf',
        Size=5mb,
        maxsize=unlimited,
        filegrowth=10%
    )
    log on
    (
        Name=Mrkj_log,
        FileName='E:SQLDataDataBaseMrkj.ldf',
        Size=3mb,
        maxsize=50mb,
        filegrowth=2mb
    )

    create Database Ming on primary
    (
        name=Ming_data,
        FileName='E:SQLDataDataBaseMing.mdf',
        Size=5mb,
        maxsize=unlimited,
        filegrowth=10%
    )
    log on
    (
        Name=Ming_log,
        FileName='E:SQLDataDataBaseMing.ldf',
        Size=3mb,
        maxsize=50mb,
        filegrowth=2mb
    )
    --使用Alert database修改文件或者文件组
    --将一个大小为10MB的数据刻文件mrkj添加到MRKJ数据库中,该数据文件的大小为为10MB,最大的文件大小为100MB，增长速度为2MB,更改mdf路径
    --修改文件大小时,修改后的大小要大于修改前的大小, database 数据库
    use Mrkj
    alter database  MrKJ
    add file
    (
        name=mrkj,
        FileName='E:SQLDataDataBasecsMrkj.mdf',
        Size=10mb,
        maxsize=100MB,
        filegrowth=10%
    )

    --使用存储过程修改数据库名称  将数据库名称更改
    use Ming
    exec sp_renamedb 'Ming', 'MR'
     
    --删除数据库
    use master
    drop database MR

    --使用CreateTable语句创建表
    use MR_NXT
    create table Student(
        Id int  not null,
        Name varchar(50),
        Age int 
    )
    
    --创建、修改和删除约束
    --1.非空约束决定表中的性是否可为该列包含空值,空值(或Null)不同于零(0)、空白或者长度为0的字符串(如""),Null的意思是没有输入,出现Null通常表示未知或未定义
        --(1)创建非空约束
            alter table Student
            add  constraint checkIsNull  check ( Name is not null)
        --(2)修改非空约束
            alter table student
            alter column Id int null
    --主键约束
        --(1)创建主键约束
            use MR_NXT
            create table Product
            (
                Id int constraint  pk_Id  primary key,
                ProductName varchar(50),
                Size varchar(50),
                State int  constraint checkState check (State is not null)
            )
        --(2)在现有表中创建主键的约束
            use MR_NXT
            alter table student
            add constraint _Id primary key(Id)
        --(3)删除主键约束
            use MR_NXT
            alter table student
            drop constraint Id
    --唯一约束unique用于强制实施集中值的唯一性,根据unIque约束,表中的任何两行都不能出现相同的列值
        --(1)创建Emoloyee,并将字段Id设置唯一约束
            use MR_NXT
            create table Employee
            (
                Id int constraint UQ_ID unique,
                Name char(50),
                Sex char(2),
                Age int 
            )

        --(2)在现有表中创建唯一约束
            use MR_NXT
            alter table Employee
            add constraint unique_Id unique(Id)

        --删除唯一约束
            use MR_NXT
            alter table Employee
            drop constraint unique_Id 

    --检查约束Check可以强制域的完整性,Check约束类似foreign key,可以控制进入列中的值
        --(1)创建检查约束
            use MR_NXT
            --drop table Employee
            create table Employee
            (
                Id int constraint UQ_ID unique,
                Name char(50),
                Sex char(2) constraint Ck_Sex check(sex in ('男','女')),
                Age int 
            )
        --(2)在现有表中创建检查格式如下
            use MR_NXT 
            alter table employee
            add constraint Check_Sex Check(sex='女')
        --(3)删除检查约束
            use MR_NXT
            alter table employee
            drop  constraint Check_Sex  
    --默认约束
        --(1)创建表时创建默认约束,将Sex设置默认约束为女
            use MR_NXT
            --drop table Employee
            create table Employee
            (
                Id int constraint UQ_ID unique,
                Name char(50),
                Sex char(2) constraint def_sex default '女',
                Age int 
            )
        --(2)在现有表中创建默认约束
            use MR_NXT 
            alter table Employee
            add constraint def_sex default '男' for Sex
        --(3)删除默认约束
            use MR_NXT
            alter table employee
            drop  constraint def_sex

  --外键约束
        --(1)创建表时创建外键约束
            use MR_NXT
            create table Loborage
            (
                 Id int,
                 Wage Money constraint fkey_Id foreign key(Id) references Employee(Id)
            )
        --(2)在现有表中创建外键约束
            use MR_NXT 
            alter table Loborage
            add constraint Fkey_Id foreign key(Id) references Employee(Id)
        --(3)删除外键约束
            use MR_NXT
            alter table Loborage
            drop  constraint Fkey_Id

    --使用alter table 修改表结构
        --(1)向数据库Student表中添加Name字段
            use MR_NXT
            alter table student
            add Name char(2)
        --(2)将Student表中Name的字段的数据类型修改为char(50)
            use MR_NXT
            alter table student
            alter column Name char(50)
        --(3)删除Student表中Name字段
            use MR_NXT
            alter table student
            drop column Name
        --使用Drop table 语句删除表 drop database 表名

    --数据操作
        --使用SELECT 语句浏览数据表
            select * from Employee
        --使用Insert语句添加数据
            insert into Employee values('001','小明','男','19')
        --使用updaate语句修改指定数据
            update Employee set Age+=1aa
            update Employee set Age='18' where Name='张三'  --指定Name条件
        --使用delete语句删除指定数据
            delete Employee where Id='001'

    --视图操作
        --创建Emoloyee表中的所有记录的视图View_Emp
            create view View_Emp
            as select * from Employee
        --创建绑定到架构的视图view1
            create view View1
            with schemabinding
            as
            select name,sex from Employee
            with check option


     --with子句 临时命名的结果集,计算没一年龄的数量
     use MR_NXT
     with ageReps(age,agecount)as
     (
        select age,COUNT(*)
        from Employee as AgeReports
        where Age is not Null
        group by Age
     )
     select age,agecount from ageReps

     --查询最大的Age
      use MR_NXT
      select distinct 员工编号=Id, Name as 姓名, Sex 性别,Age 年龄 from Employee

      select *from Employee

      --into 创建新表并将来自查询结果行插入新表中
      select Name,Age into tb_Employee from Employee

      select *from tb_Employee

      --where 子句  
      select *from Employee where Name='雨欣' And Age=25

      --逻辑运算符(Not And OR)
      select *from Employee where Sex='男' And not Age=22 or Sex='女' and Age=25
      
      --Like % 关键字模糊查询
      select *from Employee where Name like '%欣%' And Age=25

      --Like 带特殊字符需要转义的
      select *from Employee where Name  like '%\%%'   escape '';
      
      -- Like _(下划线)匹配任意单个字符
      select *from Employee where Name like '_欣' And Age=25

      --lIKE []通配符,表示查询一定范围内的单个字符[2~4]表示2到4的数,包括2,3,4, 2[2~4]就表示22,23,24,条件加上单引号''
      select *from Employee where    Age like '2[2-5]'

      --使用Between 关键字 字段 between 条件 And 条件 ||  字段 Not between 条件 And 条件指定范围条件
      select *from Employee where Age between 22 and 27
      --不在范围内的Not Between and 
      select *from Employee where Age not  between 22 and 27
      
      --使用In关键字
       select *from Employee where Id in(13,14)
      --使用 Not In关键字
       select *from Employee where Id not  in(14)

      --All、SOME、 ANY关键字
      --All 这是查询大于张三 Age的数据
         select *from Employee where Age >ALL
          (
                 select age from Employee where name in('张三')
          )
      --ANY 大于最大值
        select *from Employee where Age >ANY
          (
                 select age from Employee where name in('张三')
          )

      --Exists关键字用于指定一个子查询,测试行是否存在
       select * from Employee where exists (select null)

       --Group By子句 按一个或多个列或表达式的值将一组选定行组合成一个集,针对每一组返回一行
        --按性别分组,使用聚合函数列需要包含在子句中select Sex
        select  Sex from Employee  group by Sex
        --将年龄和性别分组
        select  Sex,age from Employee  group by Sex,age

        --Having 子句 查询每个年龄段的人数大于等于两人的年龄
        select Age 年龄, count(*) 人数 from Employee  group by Age having COUNT(Age)>=2

        --Group by 子句 返回的列中所使用的排序顺序,除非同时指定了TOP，否则orderby子句在子查询、视图、内联函数中无效
        --ASC 指定按升序,从最低值到最高值对指定列中的值进行排序(从小到大)
        --desc 指定按降序,从最高值到最低值对指定列中的值进行排序(从大到小)
        select * from Employee where sex='女' order by Age desc

        select * from Employee where Age>=18 order by Name asc

        --compute子句
        select * from Employee order by Sex compute avg(Age) 
        select * from Employee order by Sex compute avg(Age) by sex

        --Distinct 去重复的记录Age
        select distinct Age from Employee  

        --TOP关键字 限制显示的行数
        select top 5  Name,Age from Employee  

        --union合并多个查询结果,默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
        SELECT Name FROM Student
        UNION
        SELECT Name FROM Student

            SELECT * FROM Student
        --union all 合并表
        --新增点数据
        insert into Student(Age,Sex,Name)values(18,'男','张三')
        insert into Student(Age,Sex,Name)values(19,'女','王五')
        insert into Student(Age,Sex,Name)values(20,'男','赵六')
        insert into Student(Age,Sex,Name)values(28,'男','孙⑦')
        insert into Student(Age,Sex,Name)values(25,'女','李⑦')

        --union all 合并表,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。数据将会重复
        SELECT Name,Age FROM Student
        UNION ALL
        SELECT Name,Age FROM Student

        --使用union合并有不同列数的两个表,如果列数不够则使用Null来添加
        select   Id,Sex ,age from Student
        union all
        select cno,CNAME,NULL from   course

        --使用union进行多表的数据合并
        select   Id,Sex from Student
        union 
        select cno,CNAME from   course
        union
        select Id,Name from   Employee

        --子查询是一个可以嵌套在select、insert、update、delete语句或其他子查询中的查询,任何允许使用表达式的地方都可以使用子查询
        --查询tb_Grade 中Name是王五的
        select * from Student  where Name=(select Name FROM tb_Grade WHERE Name='王五')

        --带in的嵌套查询 Name是王五的
        select * from Student  where Name in (select Name FROM tb_Grade )

        --带 not in的嵌套查询 Name是王五的
        select * from Student  where Name not  in (select Name FROM tb_Grade )

        --没有参加过考试的学生
         select * from Student  where Name not  in (select Name FROM tb_Grade where Name is not null )

        --带exitst的嵌套查询
         select * from Student  where  Exists (select Name FROM tb_Grade where Student.Id=tb_Grade.ID )

         --联接查询
         --内部连接 Inner join
         select * from Student  stu
         inner join tb_Grade tb
         on stu.Id=tb.ID

         --左外部连接 Left join,如果左边的有一行没有匹配到右边的一行，那么右边变成空值Null
         select * from Student  stu
         left join tb_Grade tb
         on stu.Id=tb.ID
         --右外部连接 Right join,如果右边的有一行没有匹配到左边的一行，那么左边变成空值Null
         select * from Student  stu
         right join tb_Grade tb
         on stu.Id=tb.ID

        --完整外连接 full join ,,如果有一行没有匹配到,那么其他没有匹配到的变成空值Null
         select * from Student  stu
         full join tb_Grade tb
         on stu.Id=tb.ID

         --交叉联接,如果student 有6行数据,Course有5行,那么表中最后的行数是(5*6)30行
         select * from Student  stu
         cross join tb_Grade

         --case语句更新数据,如果是男的年龄-1,如果是女的年龄+1
         update Student set Age=case when Sex='男' then Age-1 when Sex='女' then Age+1 end


         --Create Proc 语句创建存储过程
         Create Procedure Proc_Student
         @Proc_Sno int
         as 
         select * from Student where Id=@Proc_Sno

         --使用Execute 执行存储过程
         Create Procedure proStudentValues
         as 
         select top 3 * from Student 
         --Exec执行存储过程
         exec  proStudentValues

         --查看存储过程
         --使用sp_helptext查看存储过程的定义
         select * from sys.sql_modules
         exec sp_helptext 'proStudentValues'

         --修改存储过程
         alter procedure  proStudentValues
         @sno varchar(10)
         as
         select * from student
         --触发器的概念 SQLServer提供两种主要机制来强制使用业务规则和数据完整性,即约束和触发器
         --触发器是一种特殊类型的存储过程,当指定表中的数据发送变化时触发器自动生效

         --触发器的优点,
         --(1)触发器自动执行,对表中的数据进行修改后,触发器立即被激活
         --(2)为了实现复杂的数据库更新操作,触发器可以调用一个或多个存储过程,甚至可以通过调用外部过程
         --(3)触发器能够实现比CHECK约束更为复杂的数据完整性约束,在数据库中,为了实现数据完整性,可以使用Check约束和触发器
         --Check约束不允许引用其他表中的列来完成检查检查工作,而触发器可以引用其他表中的列,触发器更适合在大型数据库管理系统中用来约束数据的完整性
         --(4)触发器可以检测数据库内的操作,从而取消了数据库未经许可的操作,使用数据库修改、更新操作更加安全,数据库的运行也很稳定
         --(5)一个表可以同时存在3个不同操作的触发器(Insert、update、delete)
         --触发器的常规种类DML触发器、DDL触发器和登录触发器

         --创建DML触发器,当数据库发生数据操作语言时将会调用DML触发器
         Create Trigger Trigger_Product
         on product
         after insert 
         as 
         raiserror('正在想表中插入数据',16,10)

         insert into Product values('触发器1111','50g',0)

         --创建DDL触发器,这些事件知主要对应Create、Alter、drop,类似DDL操作的某些系统存储过程
         Create trigger Trigger_ProductDDL
         on database
         for drop_Table, alter_Table
         as 
            print '只有,"Trigger_ProductDDL"触发器无效时,才可以删除或修改表'
            rollback
            --rollback 如果数据被删除后修改操作进行回滚,显示提示
        --只有对修改、删除product表时触发器才显示信息
        drop table Product

        --创建登录触发器
        use master
        go
        create login yy with password='TMsoft' must_change,
            check_ExPiration=on;
        go
        grant view server state to yy;
        go
        Create trigger connection_limit_trigger
        ON ALL Server with Execute as 'yy'
        for login
        as 
        begin
         if ORIGINAL_LOGIN()='yy' And
         (select COUNT(*) from sys.dm_exec_sessions where is_user_process=1 and original_login_name='yy')>1
         rollback
        end; 

        --查看触发器
        --(1)通过sp-helptext查看触发器
        exec sp_helptext 'Trigger_Product'

        --(2)通过sys.Objects显示,查询类型为TR的触发器
        select * from sys.objects where type='TR'

        --修改触发器 可以通过alter trigger 语句实现
        --插入、修改、删除数据时先提示信息
        alter trigger Trigger_Product
        on product
        after insert,update,delete
        as 
        raiserror ('正在向表中插入、修改或者删除数据',16,10)
         --修改DML触发器,防止用户修改数据
         alter trigger Trigger_ProductDDL
         on database
         for drop_Table, alter_Table
         as 
            print '只有,"Trigger_ProductDDL"触发器无效时,才可以删除或修改表'
            rollback
        --重命名触发器  exec sp_rename '原触发器名称','新触发器名称'
        exec sp_rename 'trigger_Product','trigger_Product_DML'

        --禁用和启用触发器
        --使用disable trigger 语句禁用DML触发器
        disable trigger trigger_Product on product

        --使用disable trigger 语句启用DML触发器
        enable trigger trigger_Product on product

        --删除触发器
        drop trigger trigger_Product 

        --删除DDL触发器
        drop trigger trigger_Product on database

        --删除登录触发器
        drop trigger trigger_Product on all server

        --索引的优点
        /*
        (1)创建唯一索引,保证数据库表中每一行数据的唯一性
        (2)大大加速数据的检索速度,这也是创建索引的最主要的原因
        (3)加速表和表至今的连接,特别是在实现数据的参考完整性特别有意义
        (4)使用分组和排序子句进行数据检索时,同样可以减少查询中分组和排序的时间
        (5)通过使用索引,可以在查询的过程中使用优化隐藏器,提供系统的性能
        */

        --索引的缺点
        /*
        (1)创建索引和维护索引需要耗费时间,这种时间随着数据量增加而增加
        (2)索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚集引用,那么需要的空间就会更大
        (3)当对表中的数据进行新增修改删除时,索引也要动态地维护,降低了数据的维护速度
        */

        --索引的分类有聚集索引、非聚集索引、唯一索引、包含性索引、索引视图、全文索引、空间索引、筛选索引和XML索引
        --聚集索引除了可以提高查询性能外,还可以按需重新生成或重新组织来控制表碎片
        --非聚集索引具有独立于数据行的结构,非聚集索引包含非聚集索引键值,并且每个键值项都有指向包含该键值的数据行的指针

        --创建非聚集索引
        use MR_NXT
        create index ix_student_no
        on Student(id)

        --创建唯一聚集索引,无法对表创建多个聚集索引
        use MR_NXT
        create unique clustered index ix_Product_no
        on Product(id)

        --SQL游标,允许定位在结果集的特定行,从结果集的当前位置检索一行或一部分行,支持对结果集中的数据库数据所做的更改提供不同级别的可见性支持

        --游标的实现
        /*
        (1)声明游标
        (2)打开游标
        (3)从一个游标中查找信息
        (4)关闭游标
        (5)释放游标
        */

        --游标的类型(静态游标、动态游标、只进游标、键集驱动游标)
        /*
        (1)静态游标的完整结果集在游标打开时建立在tempdb中,静态游标总是按照游标打开时的原样显示结果集

        (2)动态游标与静态游标相对,当滚动游标时,动态游标反映结果集中所作的所有更改,结果集中的行数据值、顺序和成员在每次提取时都会改变

        (3)只进游标不支持滚动,它只支持游标从头到尾顺序提取,只在从数据库中提取查出来才能进行检索
        (4)打开游标时,键集驱动游标中的成员和行顺序是固定,键集驱动游标由一套被称为键集的唯一标识符控制
        */

        --创建游标
        use MR_NXT
        declare Cur_Emp cursor for
        select * from Employee
        go

        --创建一个名为Cu_Emp_01的只读游标
        use MR_NXT
        declare Cur_Emp_01 cursor for
        select * from Employee
        for read only  --只读游标,不允许游标内数据被更新
        go

        --创建Cur_Emp_02的更新游标
        use MR_NXT
        declare Cur_Emp_02 cursor for
        select Name,Sex,Age from Employee 
        for update  --只读游标,不允许游标内数据被更新
        go

        --打开游标可以使用Open命令
        declare Emp_01 cursor for --声明游标
        select * from Employee
        where Id=13  
        open Emp_01  --打开游标
        go

        --使用@@fetch_status控制一个where循环的游标
        declare ReadCursor cursor for
        select * from student
        open ReadCursor

        fetch next from readCursor
        while @@FETCH_STATUS=0
        begin
            fetch next from ReadCursor
        end

        --关闭游标 Global 是全局游标 
        declare CloseCursor cursor for
        select * from Student
        for read only
        open CloseCursor
        close CloseCursor

        --释放游标,当游标关闭之后,并没有在内存中释放所占用的系统资源,所有可以使用Deallocate命令删除游标引用
        --释放需要释放的游标CloseCursor
        declare FreeCurosor cursor for
        select * from Student
        open freecursor
        close freecursor
        deallocate freecursor

        --使用系统过程查看游标 sp_cursor_list 和sp_descride_cursor查看游标的属性
        --声明一个游标,并使用sp_cursor_list
        declare Cur_Employee cursor for
        select Name from Employee
        where Name like '张%'
        open Cur_Employee
        declare @Report cursor
        exec sp_cursor_list @cursor_return=@Report output,
            @cursor_scope=2
        fetch next from @Report
        while (@@FETCH_STATUS<>-1)
        begin
            fetch next from @Report
        end
        close @Report
        deallocate @Report
        go
        close Cur_Employee
        deallocate Cur_Employee
        go

        --声明一个游标,并使用exec sp_describe_cursor
        declare Cur_Employee cursor for
        select Name from Employee
        open Cur_Employee
        declare @Report cursor
        exec  sp_describe_cursor  @cursor_return=@Report output,
            @cursor_source=N'global',@cursor_identity=N'Cur_Employee'
        fetch next from @Report
        while (@@FETCH_STATUS<>-1)
        begin
            fetch next from @Report
        end
        close @Report
        deallocate @Report
        go
        close Cur_Employee
        deallocate Cur_Employee
        go

        --SQL函数的使用
        /*聚合函数概述
         SUM  返回表达式中所有值的和
         AVG  计算平均值
         Min  返回表达式的最小值 
         Max  返回表达式的最大值 
         Ccount 返回组项目的数量 
         distinct 返回一个集合,并从指定集合中删除重复的元组
        */
        select * from tb_grade

        --修改表类型
        alter table tb_grade
        alter column Grade  int
        --SUM（求和）函数
        select  SUM(Age) as 总年龄 from Student  

       --AVG（平均）函数
        select  Avg(Age) as 平均年龄 from Student  
       --MIN（最小）函数
        select  MIN(Age) as 最小年龄 from Student
       --MAX（最大）函数
        select  MAX(Age) as 最大年龄 from Student  
       --count(返回int数量）函数
        select  COUNT(*) as 总数据 from Student 
       --distinct(去重）函数
        select  distinct Name as 去重姓名 from Student
        --查询不重复记录函数
        select  distinct (Name) from Student order by Name
       --查询重复记录函数
        select  Name,COUNT(Name) from Student group by Name having count(Name)>1 order by Name
        
        --ABS(绝对值)函数
        select ABS(1.0) as '1.0的绝对值',
               ABS(0.0) as '0.0的绝对值',
               ABS(-1.0) as '-1.0的绝对值'
        --圆(圆周率)函数
        select PI() AS 圆周率

        --Power(乘方)函数
        select Power(2,2) as '2的幂',
               Power(3,3) as '3的幂',
               Power(4,4) as '4的幂'
        --Rand(随机浮点数)函数
        select RAND(100),RAND(),RAND()
        --字符串函数
        --CharIndex返回字符串的起始位置,下标从1开始
         select CHARINDEX('六',Name) from Student where Id=3

          select * from Student
        --Left(取左边指定的字符)
        select LEFT('无敌小可爱',2)
        select LEFT(Name,1) ,count(LEFT(Name,1))from Student group by LEFT(Name,1)
        
        --Right(取左边指定的字符)  假设Id 00001  取后面3位Right(Id,3)
        select Right('无敌小可爱',2)
        select Right(Id,1) ,count(Right(Id,1))from Student group by Right(Id,1)
        
        --Len返回字符个数函数
        select len('ABCDE') AS 个数

        --replace替换字符串
        select REPLACE('MingMing','ing','i') AS 替换结果

        --reverse反转函数
        select reverse('MingMing') AS 反转结果

        --STR函数返回由数字数据转换来的字符数据
        select STR(123.45) as 'STT'
        select STR(123.45,5,1) as 'STT'
        select STR(123.45,8,1) as 'STT'

        --Substring
         select SUBSTRING('abcde',1,2)

         --日期和时间函数
         /*常用时间函数及说明
          DateAdd   在向指定日期加上一段时间的基础上,返回新的datetime值
          DataFill  返回跨2个指定日期的日期和时间边界数
          getdate   返回当前系统日期和时间
          day        返回日期中的天的整数
          month        返回日期中的月份的整数
          year        返回日期中的年份的整数
         */
         --GetDate(返回当前系统日期和时间)
         select GETDATE() as 当前时间

          --Day(返回当前指定日期的天)
         select DAY('2018-12-2') as 'Day'

           --Month(返回当前指定日期的天)
         select Month('2018-12-2') as 'Month'

         --Year(返回当前指定日期的天),数据库将0解释为1900年1月1日
         select Year('2018-12-2') as 'Year'

         --DateDiff(返回日期和时间的边界数),计算开始时间和结束时间距离了多久
         select DATEDIFF(DAY,'2018-4-12','2018-12-2') as 时间差距

         --DateAdd(添加时间)在当前时间添加了一个月时间,可以用于电商下单,控制下单时间派送
         select DATEADD("Month",1,getdate()) as  加一个月的时间
         select DATEADD(YEAR,1,getdate()) as  加一个月的时间

         --转换函数
         --Cast函数
         select CAST('Mingrikeji' as nvarchar(6)) as 结果
        
        --转nvarchar类型
         select 
            GETDATE() as unConvertDateTim,
            cast(Getdate() as nvarchar(30)) as UsingCast,
            Convert(nvarchar(30),Getdate(),126) as UsingConvertTo_Is
            GO
        --转时间类型
            select 
            GETDATE() as unConvertDateTim,
            cast(Getdate() as datetime) as UsingCast,
            Convert(datetime,Getdate(),126) as UsingConvertTo_Is
            GO
        --返回数据库名称
        select DB_NAME() as [Current DataBase];
        go 

        --SQL事务
        /*事务的概念,事务包含4中重要的属性,被称为ACID(原子性、一致性、隔离性和持久性),一个事务必须通过ACID
            (1)原子性:事务是一个整体的工作单元,事务对数据库所作的操作要么全部执行,要么全部不执行
            (2)一致性:事务在完成时,必须使所有的数据都保持一致状态,在相关数据库中,所有规则二都必须应用事务的修改,以保持
            所有数据的完整性,如果事务成功,则所有的数据将变成一个新的状态,如果事务失败,则所有输几局将处于开始之前的状态
            (3)隔离性:由某一事务所作的修改必须与其他啊事务所作的修改隔离,事务查看数据时数据所处的状态,要么是另一并发事务
            修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态的数据
            (4)持久性:当事务提交后,对数据库所作的修改就会永久保存下来
        */

        --开始事务
        select * from Employee where Id=13
        begin transaction Update_data
            update Employee set Name='章子婷'
            where Id=13
        commit transaction Update_data
        select * from Employee where Id=13

        --结束事务,当一个事务执行完成之后,要将其结束,以便释放所占用的内存资源,结束事务使用Commit语句
        select * from Employee 
        begin transaction Insert_data
            insert into Employee
            values('16','门闻双','女','22')
        commit transaction Insert_data
        go
        if @@ERROR=0
            print '插入新纪录成功'
        go
        select * from Employee  

        --事务的工作机制
        select * from Employee 
        begin transaction Update_Dat
            update Employee set Name='闻双' where Id=16
            Delete Employee where Id=16
        commit transaction Update_Dat

        --事务的隔离性
        --Read Uncommitted(未提交读)
        begin transaction 
        update Employee set Name='章子婷'
        set transaction isolation level read uncommitted  --设置未提交隔离级别
        commit  transaction
        select * from Employee

        --Read Committed(提交读)
        set transaction isolation level read uncommitted
        begin transaction
        select * from Employee
        rollback transaction
        set transaction isolation level read uncommitted  --设置提交隔离级别
        update Employee set Name='章子婷'     
        
        --Repeatable Read(提交读)
        set transaction isolation level Repeatable Read
        begin transaction
        select * from Employee
        rollback transaction
        set transaction isolation level  Repeatable Read  --设置提交隔离级别
        insert into Employee values('16','星星','男','25')

        --Serializable
        set transaction isolation level Serializable
        begin transaction
        select * from Employee
        rollback transaction
        set transaction isolation level  Serializable --设置可串行读
        delete from Employee wHere Id=13

        --SQL查看锁
        select * from sys.dm_tran_locks
        
        --创建分布式事务
            set Xact_Abort on
        begin  distributed transaction  
            update Employee set Name='星星' where Id=16
            update Employee set Name='婷子' where Id=16
        commit transaction Update_Dat