以 Sql 必威:Server 数据库为例,基础概念

T-SQL中的流程控制语句2

  1. 创建数据库
    /创建数据库libraryDB/
    CREATE DATABASE libraryDB
    ON
    (
    /数据库文件的详细描述/
    NAME = 'libraryDB_mdf', --主数据库文件的逻辑名
    FILENAME = 'E:librarylibraryDB_mdf.mdf', --主数据文件的物理名
    SIZE = 3MB, --初始大小
    FILEGROWTH = 20% --增长率
    )
    LOG ON
    (
    /日志文件的详细描述/
    NAME = 'libraryDB_ldf', --日志文件的逻辑名
    FILENAME = 'E:librarylibraryDB_ldf.ldf', --日志文件的物理名
    SIZE = 1MB, --初始大小
    MAXSIZE = 15MB, --最大值
    FILEGROWTH = 10% --增长率
    )
    Go

  2. 数据表(创建|修改|删除)
    --判断BookType表是否存在,存在则删除
    if exists (select 1 from sysobjects where [name]='BookType')
    begin
    drop table BookType
    end
    --创建图书类型表:BookType
    create table BookType
    (
    TypeId int not null identity(1,1) primary key, --图书类型编号(主键、标识列、从1开始、每次增加1)
    TypeName varchar(50) not null, --图书类型名称
    Remark varchar(100) --备注信息
    )
    --修改BookType表,增加备注字段
    alter table BookType
    alter column Remark varchar(100)
    --删除表BookType
    drop table BookType

  3. 创建主键
    --为表添加主键
    alter table productinfo
    add constraint PK_ProductInfo_ProductId primary key(ProductId)
    --删除主键只需要将add替换为drop

  4. 创建外键
    --为表添加外键
    alter table productpromotion
    add constraint FK_Promotion_Product foreign key (ProductId) references ProductInfo(ProductId)
    --删除外键只需要将add替换为drop

  5. 检查约束
    --创建检查约束(商品编号的长度大于2)
    alter table productinfo
    add constraint CK_Product_Number check(len(ProductNumber)>2)--删除外键只需要将add替换为drop
    --创建检查约束(性别为男或者女)
    --check(Gender in ('男', '女'))

  6. 惟一约束
    --创建唯一约束(商品编号唯一)
    alter table productinfo
    add constraint UQ_Product_Number unique (ProductNumber)

  7. T-SQL编程(定义变量、为变量赋值)
    --T-SQL中定义变量
    declare @sum int;
    declare @i int;
    --set赋值(一次只能为一个变量赋值)
    set @sum = 0;
    --select赋值(一次可以为多个变量赋值)
    select @sum=0,@i=1;

  8. T-SQL编程(if-else循环)
    --if实例
    declare @i int;
    set @i=7;
    if(@i%2 = 0)
    print '偶数'
    else
    print '奇数'
    go

  9. T-SQL编程(while)
    --请输出1-10之间的数字
    declare @i int;
    set @i = 1;
    while (@i<=10)
    begin
    print @i;
    set @i = @i + 1;
    end

  10. T-SQL编程(case)
    --case的第一种语法格式(使用case将0显示为:空闲 1显示为:使用中)
    select intComputerId,
    'State'=case
    when intInUse=0 then '空闲'
    when intInUse=1 then '使用中'
    end,
    chvComputerName,chvDescription
    from tblcomputer;
    --case的第二种语法格式
    select intComputerId,
    'State'=case intInUse
    when 0 then '空闲'
    when 1 then '使用中'
    end,
    chvComputerName,chvDescription
    from tblcomputer

  11. 视图
    --创建视图的语法示例
    create view view_RecordDetail
    as
    select cp.chvComputerName, ci.chvUserName, ri.dtmStart, ri.dtmEnd, ri.mnyFee
    from TblRecordInfo as ri--as为表取别名
    inner join TblCardInfo as ci on ri.intCardId=ci.intCardId--内连接用inner join,同时应该为两张表指定连接字段
    inner join TblComputer as cp on ri.intComputerId=cp.intComputerId
    --使用视图(和表很相似)
    Select * from view_RecodDetail

  12. 存储过程(无参数)
    --创建不带参数的存储过程
    create procedure pro_ComputerUseState
    as
    begin
    select * from tblcomputer;
    end
    --调用不带参数的存储过程
    exec pro_ComputerUseState;

  13. 存储过程(含输入参数)
    --如何创建有输入参数的存储过程
    create procedure pro_getComputerState
    @state int=0--参数默认为输入参数
    as
    begin
    select intComputerId,
    'intInUse'=case intInuse
    when 0 then '未使用'
    when 1 then '以使用'
    end,
    chvComputerName,
    chvDescription
    from tblcomputer
    where intInuse=@state
    end
    go
    --调用带有输入参数的存储过程
    declare @state int;
    set @state = 1;
    exec pro_getComputerState @state;

  14. 存储过程(含输入参数、输出参数)
    --创建带有输入参数和输出参数的存储过程,多个参数之间用逗号,隔开,最后一个参数后无需逗号
    create proc pro_getComputerStateById
    @intComputerId int,
    @state int output
    as
    begin
    select @state = intInUse
    from tblcomputer
    where intcomputerid=@intComputerId
    end
    --调用带输出参数的存储过程,调用时一定要在输出参数后加关键字output
    declare @state int, @computerId int;
    set @computerId = 7;
    exec pro_getComputerStateById @computerId,@state output
    select @state;

  15. 触发器
    --判断触发器是否存在,存在则删除触发器
    if exists (select * from sys.sysobjects where name = 'tr_insertRecord')
    drop trigger tr_insertRecord
    go
    --如何定义|创建一个触发器
    create trigger tr_insertRecord
    on TblRecordInfo
    for insert--for等价于after,表示当新增完记录之后才会执行触发器
    as
    begin
    declare @cardid int, @startTime datetime;
    select @cardid = intcardid, @startTime = dtmStart from inserted;
    select '卡号:'+convert(nvarchar(5), @cardid);
    select '上机开始时间:'+convert(nvarchar(20), @startTime);
    end

  16. 事务处理
    --定义变量@sumError用于记录事务过程中发生错误的次数
    declare @sumError int;
    set @sumError = 0;
    begin transaction
    update tblaccount set mnycurrentmoney = mnycurrentmoney + 200000 where chvAccountName='宝钢集团'
    --通过系统变量@@error可以获取上次被执行的sql是否执行成功,如果执行成功@@error的值为0,否则为1
    set @sumError = @sumError + @@error
    update tblaccount set mnycurrentmoney = mnycurrentmoney - 200000 where chvAccountName='安钢集团'
    set @sumError = @sumError + @@error
    --判断是否在执行过程中出现错误
    if(@sumError<>0)
    begin
    print '事务执行失败,即将回滚'
    rollback transaction
    end
    else
    begin
    print '事务执行成功,即将提交'
    commit transaction
    end

 f. 主键 PK ~ 外键 FK 

 主键:保证全局唯一性;
 外键:建立和加强两个表数据之间链接的一列或多列,强制引用完整性,可以有效防止误删;
  主键约束 ~ 外键约束 ~ 唯一约束
  - 主键约束
  主键约束用于实现实体完整性,每个表的主键有且只能有一个,主键列不能包含null值。声明联合主键采用第2、3种方法。创建PK约束,具体参见大话数据库或 三种方法创建主键约束;
  系统默认生成的主键约束名为:PK_表名_序列串号
  - 外键约束
  外键约束用于实现参照完整性,一个表A:foreign key指向另一个表B:primary key,表B是主表,表A是从表。外键约束创建三种方法,参见大话数据库或者 三种方法创建外键约束;
  系统默认生成的外键约束名为:FK_表名_字段名_序列串号
 示例主/外键的三种创建方法:
  1. 创建table时,直接在字段后面声明为 primary key 或者 foreign key

1  create table orders(
2         orderID varchar(10) not null primary key,
3         orderProduct varchar(30) not null,
4         personID varchar(20) foreign key references persons(personID)
5   );

  2. 创建table时,全部字段声明之后,添加主键和外键的约束语句

1  create table orders(
2         orderID varchar(10) not null,
3         orderProduct varchar(30) not null,
4         personID varchar(20) not null,
5         constraint PK_orders primary key(orderID),
6         constraint FK_orders_personID foreign key(personID) references persons(personID)
7  );

  3. 在table已创建后,为表添加主外键约束

1  alter table orders
2         add constraint PK_orders primary key(orderID),
3              constraint FK_orders_personID foreign key(personID) references persons(personID) 

  - not null 约束
  强制列不接受null值,具体使用参考上述代码。
  - default 约束
  用于向列中插入默认值,default只能用于insert语句且不能与identity同时用,具体使用参考如下示例代码:
  1. 创建table时,直接在字段后面声明为 default

1  create table Certifications(
2      certID int not null primary key identity(1001,1),
3      certName varchar(20) not null,
4      certPassword varchar(20) default('12345678'),
5      certTime varchar(30) default(getdate())
6  );

  2. 注意,default约束不存在此种方法;
  3. 在table已创建后,为表添加默认约束

1  alter table Certifications
2      add constraint DF_Certifications_certPassword default '123456' for certPassword,
3           constraint DF_Certifications_certTime default getdate() for certTime

  - check 约束
  用于限制列中的数据的范围,为多个列定义check约束采用第2、3种方法,具体方法如下:
  1. 创建table时,直接在字段后面添加:check(条件表达式)
  2. 创建table时,全部字段声明之后添加:constraint CHK_表名_字段名 check(条件表达式)
  3. 在table已创建后,为表添加check约束

1   alter table 表名
2       add constraint CHK_表名_字段名 check(条件表达式)

  - unique 唯一约束
 用于唯一标识表中的每条记录,通过唯一性性索引强制实体完整性,unique算是对primary key的补充,但是每个表可有多个unique约束且允许null值,创建unique约束的3种方法可参考上述方法:

1  [1].unique
2  [2].constraint UNQ_表名_字段名 unique(字段名)
3  [3].alter table 表名
4        add constraint UNQ_表名_字段名 unique(字段名)

  总结
  · 获取table的约束信息:exec sp_helpconstraint 表名
  · 撤销上述各种约束:alter table 表名 drop constraint 主/外键约束名 
  · 关闭/开启约束检测:nocheck/check constraint 约束名/all
  · 若表中已存在数据,在添加约束之前先使用with nocheck可以禁止对已有数据的检测。
  · 级联更新/删除:on update/delete cascade

4. 某些高级检索技术

where ... union ... group by ... having ... order by ... (limit) ... 

  a. 分组技术
  SQL Server 之几种分组技术介绍
  · group by
  在select中作为分组条件的列名一定要是在group by子句中使用的列列表中。

       select 作为分组条件的列名 聚合统计函数(被统计字段列) from 表名 group by 用于分组的列列表(A,B,C) 

  优先级:C > B > A
  · having
  having 与 where 语句类似,where 是在分类之前过滤,having 是在分类之后过滤,且having条件中经常包含聚合函数。
   group by … having … order by …  
  · rollup ~ cube
  rollup显示所选列的值得某一层次结构的聚合,cube显示所选列的值得所有组合的聚合,且更加细化;两者均需要和group by一起用。
  具体区别详解见:rollup ~ cube,rollup ~ cube - 2
  b. 联合查询
  · union
  并集,用于整合2个以上的结果集,默认去重,union all不去重。但是有列类型和列数量是否对应一致的限制。 
  c. 连接查询
   连接是关系型数据库模型的主要特点,通过连接运算符来实现多个表的联表查询,灵活,语句格式:

   select 表名.列名[列列表...]
      from table_A 连接运算符 table_B [on 联表查询的匹配条件]

  注意,在连接表查询中学会使用别名。以下可参考 连接查询简例,连接关系示意图。
  · 内连接
  inner join,也即普通连接,包括等值连接、自然连接、不等连接。返回的查询结果集合仅仅是select的列列表以及符合查询条件和连接条件的行。其中,自然连接会去掉重复的属性列。  
  · 外连接
  outer join,包括左外连接、右外连接和完全连接。返回的查询结果集合不仅包含select的列列表以及符合查询条件和连接条件的行,还包括左表(左连接)、右表(右连接)或两个连接表(完全连接)中的所有数据行。

      A left join B == B right join A;   
  · 交叉连接
  cross join,连接表中所有数据的笛卡尔积,结果集的数据行数 = 第一个表中符合查询条件的数据行数 * 第二个表中符合查询条件的数据行数。cross join后加条件只能用where,不能用on。  
  · 自连接
  连接关键字的两边都是同一个表,将自身表的一个镜像当作另一个表来对待。自连接可以将需要两次查询的语句综合成一条语句一次执行成功。参考示例:自连接查询,也可参见大话数据库中关于自连接的例子。
  d. 子查询
 即内部查询(inner query),子查询就是位于select、update或delete语句中内部的查询。子查询在主查询执行之前执行一次,主查询使用子查询的结果。参考示例:子查询,各种查询总结. 

  select select_list from table1
      where expression operator(select select_list from table2);

  · 单行子查询
  返回零行或一行。单行比较运算符:= ,>, >= ,< , <= ,<>。
  · 多行子查询 
  返回一行或多行。多行比较运算符:IN/NOT IN,ANY/ALL,EXISTS。
  ANY:匹配子查询得到的结果集中的任意一条数据;
  ALL:匹配子查询得到的结果集中的全部数据;
  EXISTS:返回bool值,只检查行的存在性,而IN检查实际值的存在性(一般情况EXISTS性能高于IN)。
  f. 索引
  此处将索引拿出来作为单独的一章进行总结学习,如下。

5. 索引

索引是对数据库表中一列或多列的值进行排序的一种结构,快速有效查找与键值关联的行,加快对表中记录的查找过滤或排序。索引采用 B树 结构。
优点:
 (1)快速检索读取数据;
 (2)保证数据记录的唯一性;
 (3)实现表与表之间的参照完整性,加速表和表之间的连接;
 (4)在使用order by、group by子句进行数据检索时,利用索引可以减少排序分组时间;
 (5)通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能;
  缺点:
 (1)增加了数据库的存储空间;
 (2)创建索引和维护索引要耗费时间;
 (3)插入和修改数据时要花费较多时间更新索引;
 (4)修改性能和检索性能是相互矛盾的;
分类:根据索引的顺序与数据表的物理顺序是否相同
 · 聚集索引
  索引的顺序与数据表的物理顺序相同,提高多行检索速度。一个表只能包含一个聚集索引。聚集索引的叶级是数据页,数据值的顺序总是按照升序排列。在创建任何非聚集索引之前先创建聚集索引。聚集索引的平均大小约为数据表的5%。
 · 非聚集索引
  索引的顺序与数据表的物理顺序不同,单行检索快。一个表最多249个非聚集索引。非聚集索引的叶级是索引页。索引页中的行标识符(或聚集键)指向数据页中的记录(或表的聚集索引,再通过聚集索引检索数据),行标识符由文件ID、页号和行ID组成,并且是唯一的。数据堆通过使用索引分配图(IAM)页来维护。
特征:
 · 唯一性索引
 保证索引列中的全部数据是唯一的。只能在可以保证实体完整性的列上创建唯一性索引。
 · 复合索引
  一个索引创建在2个或多个列上。不能跨表建立复合列。注意列的排列顺序。复合索引可以提高查询性能,减少在一个表中所创建的索引数量。复合索引键中最多可以组合16列。
创建索引:
 · 直接创建:索引创建向导或create index
 基本方法,灵活易扩展、优化索引。语法:

 create [unique][clustered|nonclustered] index 索引名  
   on {表|视图}(列 [asc|desc][,...n])

 · 间接创建:利用约束间接创建
 主键约束 - -> 唯一性聚集索引,唯一性约束 - ->唯一性非聚集索引。
 利用约束创建索引的优先级高于create index语句创建的索引。
维护索引:
 · 查看索引
  [1]. exec sp_helpindex 表名
  [2]. select * from sysindexes [where name = "索引名"]
 · 修改索引
  [1]. 修改索引名:exec sp_rename ‘表名.索引名’, ‘新索引名’
  [2]. 重新生成索引:alter index 索引名/all on 表名
           rebuild;
     重新生成索引会先删除再重建索引。可以不用rebuild,直接用set设置索引选项。
 · 删除索引
   drop index 索引名 on 表名
   最好在删除之前,利用exists判断索引名的存在性;
 · 统计信息
 统计信息是存储在Sql Server中列数据的样本,Sql Server维护某一索引关键值的分布统计信息。
  [1]. exec sp_updatestats
  [2]. update statistics 表名 [索引名]
 ·dbcc showcontig:显示表的数据和索引的碎片信息。
 ·dbcc dbreindex(表名, 索引名):重建表的一个或多个索引。
 ·showplan_all 和 statistics io:分析索引,查询性能,更好的调整查询和索引。
   set showplan_all on/off
   set statistics io on/off 
参考:
[1]. 数据库索引的实现原理,索引由浅入深
[2]. 表和索引数据结构体系结构,SQL索引学习-索引结构

6. 视图

视图是一种逻辑对象,是由基本表导出的虚拟表,不占用任何数据空间、不存储数据,仅封装预定义的查询语句,其内容由查询定义。视图是查看数据库表数据的一种方式,提供了存储预定义的查询语句作为数据库中的对象以备后用的作用,但视图不能索引。被查询的表称为基表,对视图的数据操作(增、删、改),系统根据视图的定义去操作与视图相关联的基本表。
优点:
 (1)保证数据的逻辑独立性,数据保密;
 (2)隐藏复杂的SQL,SQL语句复用,数据简化操作逻辑,易于检索数据;
 (3)重新格式化检索出来的数据;
创建视图: 
  create view 视图名 [with schemabinding/encryption] as 查询语句  
 (1)对于视图和基表必须紧密结合的情况,利用with schemabinding将视图定义为索引视图;
 (2)对创建视图的SQL语句加密,利用with encryption;
维护视图:
 · 查看视图
  [1]. exec sp_helptext 视图名
  [2]. select definition from sys.sql_modules
      where object_id=object_id(‘视图名’)
 · 修改视图
    alter view 视图名 as 查询语句  
   重命名视图: exec sp_rename 旧视图名, 新视图名  
 · 删除视图
    drop view 视图名1 [, 视图名2, …]   

7. 游标

游标是一种只和一组数据中某一个记录进行交互的方法,是对(select)结果集的一种扩展。将面向集合的数据库管理系统和面向行的程序设计结合,主要用于交互式应用。
Transact-SQL 游标
存储过程、触发器和 T-SQL脚本,服务器端(后台)游标,仅支持单行数据提取,分为;

  • 静态游标:快照游标,在 tempdb 中创建游标;需要临时表保存结果集;
  • 动态游标:打开速度快、不需生成临时内部工作表,但连接速度慢,不支持绝对提取;
  • 只进游标:默认值,顺序提取、不支持滚动,最节省资源;
  • 键集驱动游标:键集唯一标识行,键集是打开游标时在 tempdb 中生成并内置在表 keyset 中;需要临时表保存键集;

注:客户端(前台)游标,仅支持静态游标,默认在客户机上缓存整个结果集、需维护游标位置信息。服务器(后台)游标性能更佳、更精确的定位更新,允许多个基于游标的活动语句。
使用游标的典型过程,分为:

  • 声明游标:declare 游标名称 + SQL检索语句

    declare 游标名称 cursor

     [local|global] [forward_only|scroll] [static|dynamic] ..
    

    for SQL(select)检索语句

  • 打开游标: open [golbal] 游标名称 | 游标变量  ,游标打开的同时检索数据并存储。

  • 提取数据

    fetch [next|prior|first|last | absolute|relative]

        from [global] 游标名称 | 游标变量
        into 结果变量[..]
    

    定位修改和删除数据:前提是用  for update of 列列表; 设置可编辑的列。

  update 表名 set 列名=新值[..] where current of 游标名
  delete from 表名 where current of 游标名
  • 关闭游标: close [golbal] 游标名称 | 游标变量  
  • 删除游标: deallocate [golbal] 游标名称 | 游标变量  

注:游标变量指引用了游标的变量。其他操作:

  select @@CURSOR_ROWS;    // 游标中的数据行数
  select @@FETCH_STATUS;   // fetch执行状态(-2,-1,0)  

8. 存储过程

存储过程(Stored Procedure),数据库架构作用域内的重要对象,是存储在大型数据库系统中一段为了完成特定功能的可复用的代码块,是SQL语句和可选控制流语句的 预编译 集合,经过第一次编译后再次调用不必重新编译。存储过程主要用于返回数据。
.vs 函数

  • 函数不能修改数据库表数据,存储过程可以;
  • 存储过程必须 execute 执行,函数调用更灵活;

优点:简单、安全、高性能

  • 允许标准组件式编程,可移植性、可复用;
  • 简单易用,预编译、执行速度快、效率高;
  • 改善安全机制、保证数据的安全;
  • 节约网络流量、降低网络负载;

分类

  • 系统存储过程:存储在 master 数据库中,以 "sp_"为前缀,用于从系统表中获取信息。
  • 用户自定义存储过程:T-SQL存储过程、CLR存储过程、临时存储过程。不能将CLR存储过程创建为临时存储过程。

创建

1  create proc|procedure 存储过程名
2        (@parameter 参数数据类型 [,...])
3  as
4  begin
5    < SQL语句代码块 
6    return >
7  end

返回值

  • 利用 return 返回一个值;
  • 利用 output 定义返回参数来返回多个值; 

维护
· 查看:
  [1]. exec sp_helptext 存储过程名;
  [2]. sys.sql_modules目录视图;
  [3]. object_definition元数据函数; 
· 加密:with encryption
· 修改:直接将 create 替换为 alter;
· 删除:drop proc 存储过程名;
执行

  • 语法分析阶段
  • 解析阶段
  • 编译阶段:分析存储过程、生成存储过程执行计划。执行计划存储在过程高速缓存区(专门用于存储已经编译过的查询规划的缓冲区)。
    • 重新编译:[1].sp_recompile;[2]. 执行时在 exec 语句中选项 with recompile;
  • 执行阶段

9. 触发器

Trigger,触发器是特殊的存储过程,由 事件 自动触发,不能显式调用,主要用于维护和加强数据的(一致/引用)完整性约束和业务规则([1]. 约束;[2]. 触发器)。触发器可以级联嵌套。常用的 inserted 和 deleted 表是针对当前触发器的局部表,在高速缓存中存储新插入或删除的行数据的副本。可以理解为委托事件。通常触发器只与单个表关联。 
约束 vs 触发器 vs 存储过程
约束主要被用于强制数据的完整性,能提供比触发器更好的性能;触发器常用于验证业务规则或是复杂的数据验证。触发器可以实现约束的一切功能,但优先通过约束实现。

  • 错误信息管理:约束只能使用标准化的系统错误信息,触发器可以自定义错误信息;
  • 性能差异;
  • 管理维护的工作量; 

参考:约束与数据库对象规则、默认值+数据库设计中约束、触发器和存储过程;
事件 - -> 触发器 - -> 存储过程
·  DML 触发器:响应数据操作语言事件,将触发器和触发它的语句作为可在触发器内回滚的单个事务;常用、性能开销小,可以实现相关表数据的级联更改、评估数据修改前后表的状态。
ζ  AFTER 触发器:在 IUD 操作、INSTEAD OF 触发器和约束处理之后被激发;推荐且只能在表上指定; 
ζ  INSTEAD OF 触发器:在约束处理之前被激发(执行预处理补充约束操作),指定执行DML触发器以代替通常的触发动作,优先级高于触发语句的操作;
注:每个表或试图针对每个 DML 触发操作 IUD,有且只能有一个相应的 INSTEAD OF 触发器,可以有多个相应的 AFTER 触发器。
ζ  CLR 触发器:执行在托管代码中的方法;
·  DDL 触发器:响应数据定义语言事件,用于在数据库中执行管理任务;
·  登录触发器:响应 logon 事件,用于审核和控制服务器会话;
优点

  • 预编译、已优化,执行效率高;
  • 已封装,安全、易维护;
  • 可重复使用;

缺点

  • 占用服务器资源多;
  • 后置触发(事后诸葛亮);

创建与维护
·  DDL

  create/alter trigger 触发器名称
        on 作用域(DDL:数据库名database/服务器名all server)
        FOR create|alter|drop|grant 等DDL触发器
   as SQL处理语句

  删除: drop trigger 触发器名;  修改: create - -> alter  
·  DML

  create trigger 触发器名称
       on 作用域(DML:表名/视图名)
       [FOR|AFTER|INSTEAD OF] {[insert [,] update [,] delete]}
   as SQL处理语句

  嵌套:级联触发,递归触发
   ·  直接递归:更新T,触发Trig,Trig更新T,再次触发Trig;
   ·  间接递归:更新T1,触发Trig1,Trig1更新T2,T2触发Trig2,Trig2更新T1;
  参考:如何控制触发器递归;

10. 事务 - 锁

 具体参考 事务和锁 - sqh;

11. 全文索引

全文索引是一种特殊类型的基于标记的功能性索引,用于提高在大数据文本中检索指定关键字的速度,由 全文索引引擎服务 (SQL Server FullText Search)创建和维护。全文索引创建和维护的过程称为填充:完全填充、基于时间戳的增量式填充、基于更改追踪的填充。全文索引只能在数据表上创建。
全文索引 .vs. 普通索引

  • 普通索引采用B-Tree结构,全文索引基于标记生成倒排、堆积且压缩的索引;
  • 普通索引适于字符/字段/短文本查询,全文索引是基于关键字查询的索引,针对语言词语/长文本搜索;
  • 每个表允许有若干个普通索引,全文索引只能有一个;
  • 普通索引自动更新、实时性强,全文索引需要定期维护;

全文目录 - 全文索引
存储全文索引,是创建全文索引的前提。全文目录是虚拟对象,是表示全文索引的逻辑概念。全文目录和全文索引都是为全文搜索查询服务。

  • rebuild:重新生成全文目录;
  • reorganize:优化全文目录;

    create fulltext catalog 全文目录名 create fulltext index on 全文索引基于的表名[索引包含的列列表]

原理:两步走
对文本进行分词,并为每一个出现的单词记录一个索引项以保存出现过该单词的所有记录的信息。全文索引引擎对加入到全文索引的列中的内容按字/词建立索引条目,即先定义一个词库,然后在文章中查找每个词条(term)出现的频率和位置,把这些频率位置信息按词库顺序归纳,完成对文件建立一个以词库为目录的索引。
· 创建基于关键字查询的索引
    - 如何对文本进行分词:二元分词法、最大匹配法和统计方法
    - 建立索引的数据结构:采用倒排索引的结构
· 在索引中搜索定位
   全文谓词:在 select 的 where/having 子句中指定
    - contains:精确。简单词、派生词、加权词、前缀词、邻近词;
    - freetext:模糊。文本拆分,分别搜索;
   行集函数:在 from 子句中指定
    - containstable:
    - freetexttable:

参考:全文索引原理介绍;全文索引原理及范例;

删除触发器

Drop trigger 触发器

示例:

drop trigger mytrgger3
  1. 数据库三范式是什么?
    第一范式:表中每个字段都不能再分。
    第二范式:满足第一范式并且表中的非主键字段都依赖于主键字段。
    第三范式:满足第二范式并且表中的非主键字段必须不传递依赖于主键字段。
  2. 什么是数据库事务?
    事务具有四大特性:一致性、原子性、隔离性、持久性。
    数据库事务是指:几个SQL语句,要么全部执行成功,要么全部执行失败。比如银行转账就是事务的典型场景。
    数据库事务的三个常用命令:Begin Transaction、Commit Transaction、RollBack Transaction。
  3. 什么是视图?
    视图实际上是在数据库中通过Select查询语句从多张表中提取的多个表字段所组成的虚拟表。
    l 视图并不占据物理空间,所以通过视图查询出的记录并非保存在视图中,而是保存在原表中。
    l 通过视图可以对指定用户隐藏相应的表字段,起到保护数据的作用。
    l 在满足一定条件时,可以通过视图对原表中的记录进行增删改操作。
    l 创建视图时,只能使用单条select查询语句。
  4. 什么是索引?
    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
    l 索引分为:聚集索引、非聚集索引、唯一索引等。
    l 一张表可以有多个唯一索引和非聚集索引,但最多只能有一个聚集索引。
    l 索引可以包含多列。
    l 合理的创建索引能够提升查询语句的执行效率,但降低了新增、删除操作的速度,同时也会消耗一定的数据库物理空间。
  5. 什么是存储过程?
    存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。
  6. 什么是触发器?
    触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如,某表上的触发器上包含对另一个表的数据操作,而该操作又会导致该表触发器被触发。
  7. 写出一条Sql语句:取出表A中第31到第40记录 (MS-SQLServer)
    解1:select top 10 * from A where id not in (select top 30 id from A)
    解2:select top 10 * from A where id > (select max(id) from (select top 30 id from A )as A)
    解3:select * from (select *, Row_Number() OVER (ORDER BY id asc) rowid FROM A) as A where rowid between 31 and 40
  8. 写出一条Sql语句:取出表A中第31到第40记录 (Mysql)
    select * from A limit 30, 10
  9. 写出一条Sql语句:取出表A中第31到第40记录 (Oracle)
    select *
    from (select A.*,
    row_number() over (order by id asc) rank
    FROM A)
    where rank >=31 AND rank<=40;
  10. 在关系型数据库中如何描述多对多的关系?
    在关系型数据库中描述多对多的关系,需要建立第三张数据表。比如学生选课,需要在学生信息表和课程信息表的基础上,再建立选课信息表,该表中存放学生Id和课程Id。
  11. 什么是数据库约束,常见的约束有哪几种?
    数据库约束用于保证数据库表数据的完整性(正确性和一致性)。可以通过定义约束索引触发器来保证数据的完整性。
    总体来讲,约束可以分为:
    主键约束:primary key;
    外键约束:foreign key;
    唯一约束:unique;
    检查约束:check;
    空值约束:not null;
    默认值约束:default;
  12. 列举几种常用的聚合函数?
    Sum:求和 Avg:求平均数 Max:求最大值 Min:求最小值 Count:求记录数
  13. 什么是内联接、左外联接、右外联接?
    l 内联接(Inner Join):匹配2张表中相关联的记录。
    l 左外联接(Left Outer Join):除了匹配2张表中相关联的记录外,还会匹配左表中剩余的记录,右表中未匹配到的字段用NULL表示。
    l 右外联接(Right Outer Join):除了匹配2张表中相关联的记录外,还会匹配右表中剩余的记录,左表中未匹配到的字段用NULL表示。
    在判定左表和右表时,要根据表名出现在Outer Join的左右位置关系。
  14. 如何在删除主表记录时,一并删除从表相关联的记录?
    如果两张表存在主外键关系,那么在删除主键表的记录时,如果从表有相关联的记录,那么将导致删除失败。
    在定义外键约束时,可以同时指定3种删除策略:一是将从表记录一并删除(级联删除);二是将从表记录外键字段设置为NULL;三是将从表记录外键字段设置为默认值。
    级联删除示例:
    alter table 从表名
    add constraint 外键名
    foreign key(字段名) references 主表名(字段名)
    on delete cascade
  15. 什么是游标?
    游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录进行处理的机制。
    游标的使用步骤:
  16. 定义游标:declare cursor 游标名称 for select查询语句 [for {readonly|update}]
  17. 打开游标:open cursor
  18. 从游标中操作数据:fetch... ... current of cursor
  19. 关闭游标:close cursor

数据库

数据库系统,Database System,由数据库和数据库管理系统组成。
数据库,DataBase ,是计算机应用系统中的一种专门管理数据资源的系统,按照数据结构来组织、存储和管理数据的仓库。数据表是最基本的数据库对象,是存储数据的逻辑单元。

数据库管理系统,DataBase Management System,DBMS,管理数据库,负责数据的存储、安全、一致性、并发、恢复和访问。

数据模型,通常由数据结构、数据操作和完整性约束三部分组成。

语法:

Alter trigger 触发器名 on 表名 for 操作类型

AS

若干T-SQL语句

GO

示例:

ALTER trigger mytrgger3 on student for update

as

print '这是修改之后的update触发器'

常用SQL代码整理(MS-SQLServer)

SQL-Server Helper

1. 下面给出 SQL-Server 数据库命令执行的几种方式样例

必威 1必威 2

 1 public static bool ExecuteSqlNoResult(string sql)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {
 5         try
 6         {
 7             conn.ConnectionString = RVCConnectingString;
 8             conn.Open();
 9             SqlCommand command = new SqlCommand(sql, conn);
10             command.ExecuteNonQuery();
11             return true;
12         }
13         catch(Exception ex)
14         {
15             // 
16             return false;
17         }
18     }
19 }

[1]. 执行SQL,无返回值

其中,SqlCommand表示要对SQL Server数据库执行的一个Transact-SQL语句或存储过程。无法继承此类。

必威 3必威 4

 1 public static bool ExecuteSqlWithResult(string sql, out DataTable dtResult)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {    
 5         dtResult = new DataTable(); 
 6         try
 7         {
 8             conn.ConnectionString = DatabaseConnectingString;
 9             conn.Open();
10             SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
11             sda.Fill(dtResult);
12             return true;
13         }
14         catch(Exception ex)
15         {
16             // 
17             return false;
18         }
19     }
20 }

[2]. 执行SQL,返回结果

其中,SqlDataAdapter表示用于填充System.Data.DataSet和更新SQL Server数据库的一组数据命令和一个数据库连接。无法继承此类。

必威 5必威 6

 1 public static bool ExecuteSqlTrans(List<string> sqlList)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {
 5         SqlTransaction sqlTrans = null;
 6         try
 7         {
 8             conn.ConnectionString = DatabaseConnectingString;
 9             conn.Open();
10             
11             sqlTrans = conn.BeginTransaction();
12             SqlCommand command = new SqlCommand();    
13             command.Transaction = sqlTrans;
14             command.Connection = conn;
15             
16             string sql = null;
17             foreach(string sqlTmp in sqlList)
18             {
19                 sql = sqlTmp;
20                 command.CommandText = sql;
21                 command.ExecuteNonQuery();
22             }
23             
24             // 提交事务(前面执行无误的情况下)
25             sqlTrans.Commit();
26             return true;
27         }
28         catch(Exception ex)
29         {
30             if(sqlTrans != null)
31             {
32                 // 执行出错,事务回滚
33                 sqlTrans.RollBack();
34             }
35             retrun false;
36         }
37     }
38 }

[3]. 批量执行SQL,以事务方式

其中,SqlTransaction表示要在 SQL Server 数据库中处理的 Transact-SQL 事务。无法继承此类。

2. 判断表、存储过程等的存在性

// 判断普通表
IF NOT EXISTS( SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'TableName') AND OBJECTPROPERTY(ID, 'IsTable')=1 )
BEGIN
CREATE TABLE TableName(
    ... ...
)
END

// 判断存储过程
IF exists(select 1 from sysobjects where id=object_id('ProcName') and xtype='P')
    DROP PROC ProcName
GO

// 判断临时表
IF object_id('tempdb..#TAB_Tmp_Name') is not null 
BEGIN
    DROP table #TAB_Tmp_Name
END;
CREATE table #TAB_Tmp_Name(
  ... ...  
);

3

 

删除规则

drop rule 规则名

示例:

drop rule age_rule

索引 index

索引是一个排列、排序的方式,索引之后的结果就是目录。

比如:新华字典,它就有种三种索引排序方式:拼音、扩偏旁部首,按笔画。

在互联网笔试中,常遇到数据库的问题,遂来简单总结,注意,以 Sql Server 数据库为例。

COMMIT TRAN

提交事条,也就是事务的终点,当执行了commit tran之后,我们所执行的操作就落实保存。

SQL语言

结构化查询语言,Structured Query Language,SQL是一种数据库查询和程序设计语言,用于存储数据以及查询、更新、管理关系数据库系统,高级的非过程化编程语言。Transact-SQL是微软对SQL的扩展,具有SQL的主要特点,同时增加了变量、运算符、函数、流程控制和注释等语言元素。
SQL语言分四类:数据定义语言[DDL]、数据查询语言[DQL]、数据操纵语言[DML]、数据控制语言[DCL]。

– [1].DDL(Data Defination Language)
      style="color: blue;">创建和管理数据库中的对象,定义SQL模式以及数据库、表、视图和索引的创建和撤销。不需COMMIT。
     创建CREAT,  修改ALTER,  删除DROP,  删除TRUNCATE
     TRUNCATE,  RENAME

– [2].DQL(Data Query Language)
     基本结构: SELECT子句、FROM 子句、WHERE子句组成查询块。
     SELECT<字段名表>,  FROM<表或视图名>,   WHERE<查询条件>

– [3].DML(Data Manipulation Language)
      style="color: blue;">直接操作数据表中的数据,根据需要检索、插入、删除数据以及更新数据库.
     操作的单位是记录。DML需要COMMIT显式提交。
     插入INSERT,   删除DELETE,  更新UPDATE

– [4].DCL(Data Control Language)
     用于授予或取消对用户对数据库对象的访问权限,保证数据安全性。
     授权GRANT,  取消授权REVOKE,  显式限制权限集合DENY

事务控制语言 - TCL (Transaction Control Language)
提交COMMIT,回滚ROLLBACK,设置保存点SAVEPOINT
SQL>COMMIT:显式提交
SQL>ROLLBACK:回滚命令使数据库状态回到上次最后提交的状态
SQL>SET AUTOCOMMIT ON:自动提交
利用SQL命令间接完成:隐式提交。

1. 数据类型

 1. 字符数据类型
  a. 字符串:char、varchar、text;
  b. Unicode字符串:nchar、nvarchar、ntext,用N标识,unicode是统一字符编码标准, 双字节对字符(英文,汉字)编码;
   使用Unicode数据类型,可以最大限度地消除字符转换的问题。
 2. 数字数据类型
  a. 整数型:tinyint(1)、smallint(2)、int(4)、bigint(8);
  b. Decimal和numeric:固定精度和小数位数,decimal(p,s)或numeric(p,s),0≤s≤p;
  c. 货币类型:smallmoney(4)、money(8);
  d. 近似数字:float、real(4);
  e. bit类型:0/1序列;
 3. 日期和时间数据类型
  time、date、smalldatetime、datetime、datetime2、datetimeoffset;
 4. 二进制数据类型
  binary、varbinary;
 5. 其他数据类型
  uniqueidentifier:16字节的十六进制数字组成,全局唯一,
  sql_variant:支持各种数据类型;
  还有xml、table等,此外还可以自定义数据类型。

2.1 函数

内置函数详细介绍参考:行集函数、聚合函数、排名函数、标量函数 或者数据库书籍。
函数信息查询
   a. 工具栏“帮助”- -> “动态帮助”;
   b. 开始“文档教程”- -> “SQL server 教程”
系统函数 ~ 自定义函数
a. 系统函数
   允许用户在不直接访问系统表的情况下获取SQL系统表的信息。
b. 自定义函数:User Defined Function
优点

  • 模块化设计;
  • 执行速度快,缓存计划降低编译开销、无需重复解析和优化;
  • 减少网络流量;

分类

  • 标量型函数:Scalar Function,只能返回标量值;
  • 内联表值型函数:Inline table-valued Function,参数化的视图,只能返回 TABLE 类型;
  • 多声明表值型函数:Multi-Statement Table-Valued Function,标量型函数和内联表值型函数的结合;

创建

  create function 函数名(@参数名 参数类型, [..])
      returns 返回值类型
   as
   begin
      SQL语句;
      return 返回的对象;
   end

注:begin…end 块中的语句不能有任何副作用。
查询
函数的定义、架构等。
修改/删除
alter/drop function 函数名

2.2 关键字

  a. set ~ select
  select支持在一个操作内同时为多个变量赋值,但是为变量赋值和数据检索不能同时进行,参考 两者的区别;
  b. cast() ~ convert(): 类型转换函数
  · cast(源值 as 目标类型);
  · convert(目标数据类型,源数据[, 格式化代号]),可以格式化日期和数值;
  c. delete ~ drop ~ truncate
  · delete:DML,删除数据表中的行(一行或所有行)/记录,自动隐式commit,无法回滚;
        delete from 表名 where 条件
  · drop:DDL,显式手动commit,可以回滚,删除数据库、数据表或删除数据表的字段;
        drop table 表名
  · Truncate:快速、无日志记录,删除数据表中的数据、不删除表,不可恢复;
        truncate table 表名
  从删除速度来说,drop> truncate > delete,其他区别详细参考 delete ~ drop ~ Truncate。
  d. insert
  注意区别下面2个insert语句的区别,第一种Product格式,values中必须给出相应的值,其中日期系统默认1900-01-01;第二种格式,values中采用default约束。

   insert into Product(productName,productPrice,productStorage,productDate,productClass) 
                 values('电冰箱', null, 0, '', 1)
   insert into Product(productName,productClass) values('电冰箱',1)

  批量插入数据
  [1]. insert into 目标表表名或列视图 select 检索语句 from 源表名
  [2]. select 列列表 into 目标表表名 from 源表表名     
  e. waitfor
   定时、延时或阻止执行批处理、存储过程或事务。  

3. 数据库表设计问题

  常用表操作格式 
  [a]. 创建数据库与表 
   create database/table 数据库名/表名 
  [b]. 查看表信息 
   exec sp_help 表名   
  [c]. 添加新列、修改列名与类型 
   alter table 表名 
    add 列名 列类型 
   exec sp_rename ‘表名.列名’, ‘新列名’ (注意必须加引号) 
   alter table 表名 
    alter column 列名 新的列数据类型     

  a. E-R模型图
 实体-联系(Entities-Relationships)模型,描述概念数据模型的方法之一,软件生命周期的设计阶段,提供实体、属性、联系的面向用户的表达方法,实体之间存在一对一、一对多、多对多的联系。
  b. 关系规范化 - 数据库完整性
  三大范式:
  · 第一范式 1NF:所有属性(值)是不可分割的原子值;
  · 第二范式 2NF:所有属性数据必须依赖主键;
  · 第三范式 3NF:数据库表中不能包含已在其他表中包含的非主键信息;
 关系型数据库三大完整性:
  · 实体完整性:主键约束 primary key,唯一且非空;
  · 参照完整性:引用完整性,外键约束 foreign key 等关联约束;
  · 用户自定义完整性:域完整性,字段类型等;
  c. 分区表
 按照数据水平方式分区,将数据分布于数据库的多个不同的文件组中:
  - 改善大型表以及具有各种访问模式的表的可伸缩性和可管理性;
  - 对于多CPU系统,支持并行方式对表操作;
 分区函数~分区方案:

  create partition function 函数名(分区条件) 
   as range left/right for values() 
  create partition scheme 方案名 
   as partition 函数名

 一个分区方案只能用一个分区函数,一个分区函数可以被多个分区方案共用。
  d. 文件组 
 在数据库中对文件进行分组的一种管理机制,一个文件不能是多个文件组的成员。文件组只能包含数据文件,事务日志文件不能是文件组的一部分。使用文件组可以隔离用户对文件的依赖,通过文件组间接管理文件,可以使得同一文件组内的文件分布在不同的硬盘中,能提高IO性能。
 具体地可参考 文件和文件组。
  e. 标识符
 每一行数据必须都有一个唯一的可区分的属性作为标识符。
  · identity:本地(表内)唯一,使用方法 identity(初始种子值,增量);
     select @@identity:查看新插入行数据的标识符(的序号)  
     select $identity from 表名:引用(显示)表的唯一标识符列  
  · uniqueidentifier:全局唯一,应用rowguidcol属性作为标识符指示新列为guid列,默认定义使用newid或newsequentialid()函数生成全局唯一值;同理,使用$rowguid引用唯一标识符列。
     ID uniqueidentifier default newsequentialid() rowguidcol   

order by子句

比如倒序排序

SELECT * from studentorder by id DESC

比较运算符

>、<、>=、!=或<>

如:

if 2>3

print '2比较大'

else

print '3比较大'

删除与更新

当我们在数据库中执行增删改操作的时候,系统会在sql server服务器的内存中修改两张临时表Deleted和Inserted临时表。

假如我们现在需要开发一个校园图书管理系统,每个同学前去借书都会生成一个借书记录。

1、首先学生数据表中需要一个sNum学号字段

2、创建借书记录表,其中借书记录需要与学号相关联(不是id)。

CREATE TABLE borrowRecord(

bid int identity primary key not null,

sNum int, --关联学生表学号

borrowDate datetime, --借书日期

returnDate datetime --还书日期

)

字符串连接 +

select '阿姨'+'你好'

索引的相关sql指令

如何添加唯一索引

通过右击指定数据表弹出右键菜单à “设计” à在编辑表结构界面空白处点击右键 à “索引/键” à“添加”或选中指定的索引à选择列à 右侧“是唯一的”这一项上选择“是”à确定。

这样就可以在一个指定字段之上添加唯一索引了。

域约束

域约束用来处理一个或多个字段。

比如:商品价格不能为负数。

当用户插入一行数据时,只要有一字段不符合约束条件,那么整条记录都无法插入。

视图的优缺点

按激活顺序分类

唯一索引

在一个字段中, 不能存在重复的相同的数据。强制约束一个字段中的值不能重复。

练习:

用户CREATE 语句创建一个电影院相关的数据库,其中包含数据表(site)(id、row int、col int)、客户表(customer)(id int,name nvarchar(50)、phoneNum char(20))、电影表(movie)(id int 、name nvarchar(50)、mtime dateTime)

 

其中,用户电话的默认值是12345678

电影的默认时间是当前系统时间

每个表的id都必须是自增的主键

修改site数据表名为userSite

修改customer中的字段phoneNum的类型为char(50)

 

练习

1、模拟提款机,实现一个银行转账的例子,创建帐户表account表(id、accName、accNum、money),实现从一个帐户转5000块到另外一个帐户,如果出现错误,则事务回滚,没有错误则完成转帐。

 

创建表变量

完全连接

完全连接( FULL JOIN 或 FULL OUTER JOIN )

用于显示所连接的所有表的所有数据,即使这条数据没有任何关联关系。

select *from student  s FULL JOIN classInfo  c  ON s.cid = c.cid

当SQL Server身份验证无法登陆时

1、 用windows身份验证(也就是用本机管理员来登陆,不需要输入用户名密码的)。

2、 展开左侧目录中的 SQL Server à 安全性 à 登陆名 à 双击sa à 打开sa 用户的属性窗口。

3、 修改密码

4、 取消”强制实施密码策略”

5、 在“状态”选项卡中,对“是否允许连接到数据引擎”和“登陆”分别选择“授予”和“启动”。

6、 点击确定关闭sa 用户的属性窗口

7、 右键点击服务器根节点,选择属性打开“服务器属性”弹窗。

8、 选择“安全性”选项卡,设置“服务器身份验证”为“SQL Server和Windows身份验证”。

9、 确定并关闭“服务器属性”弹窗,然后在SQL server配置管理器中重启数据服务端,再用sql server 帐户密码来登陆即可。

waitfor等待

当T-SQL执行到waitfor语句时,程序会进入等待状态,等侍指定时间之后,程序再继续执行后面的语句。

在创建数据表的时候添加唯一约束

CREATE TABLE USERINFO(

uid int identity primary key NOT NULL,

uName nvarchar(50),

uPhone char(20) UNIQUE

)

注意:唯一约束与唯一索引达到的效果是一样的。

临时禁用约束

ALTER TABLE employee

NOCHECK

CONSTRAINT CK_ageMoreZero

--这里要指定约束的名字

BEGIN TRAN

表示一个事务单元开始,在此之后没有提交的所有语句都属于事务的一部分。

CHECK约束

通过用户自已定义的条件来对一个或者多个字段进行约束。

CREATE语句

它用来创建数据库对象

得到一张表的字段的总数

select COUNT(1) from syscolumns where id=OBJECT_ID('Customer')

master

存储了数据库的核心对象信息,没有这个数据库Sql Server就不能正常运行。

通过SQL语句来创建外键

开启触发器

enable trigger [触发器名] on表名

示例:

enable trigger mytrgger3 on student

三角函数

sin()、cos()、tan()、cot()

select SIN(0.5*PI()),TAN(0.25*PI())

--sin(90度),tan(45度)

sqlcmd命令行管理工具

通过纯指令的方式来管理SQL server数据库服务端。

开始菜单à 运行à输入cmd打开命令行窗口à输入sqlcmd /? 查看sqlcmd命令的帮助信息。

在该命令行下我们可以通过sql语句来操作数据库。

比如:

sqlcmd

use test

select * from student

go

最后,必须输入go才会开始执行SQL语句。exit退出sqlcmd命令行状态。

getDate()

得到当前时间,比如:

select GETDATE()

很多时候在需要为日期类型的字段添加当前时间为默认值的时候需要使用到该函数。

系统函数

位运算符

&按位逻辑与、|按位逻辑或、^按拉逻辑异或、~按拉逻辑非

备份

相对于分离数据库,备份的时候我们不需要停止数据库的运行。备份可以在用户正在使用数据库的状态下进行。在指定数据库的右键菜单中à任务à备份à在“目录-备份到”区域中指定数据库备份的路径(默认路径是在sql server的安装目录下,如果需要改变备份路径,需要先删除默认路径,再点击添加)

创建带输入参数的存储过程

语法

CREATE PROC[EDURE] 存储过程名

[@参数名1 数据类型 ][, [@参数名2 数据类型 ]]…

AS   SQL语句序列……

实体约束

它用来针对行进行约束。

比如:要求每个学生的姓名、电话、地址都不能出现重复。

同样的值不能在其行出现。

创建UDF

CREATE FUNCTION [模式] 函数名

([@参数名 [AS] 参数类型 [=默认值] [READONLY] [,………]])

RETURNS 返回值的类型

AS

BEGIN

[若干语句]

RETURN (返回单一值)

END

select语句

语法:

SELECT 字段列表 FROM 表名

ON UPDATE NO ACTION

NO ACTION就是指不执行任何执行,默认值。

isNull(表达式1,表达式2)

当表达式1的值不为空时,返回表达式1的值。

如果表达式1的值为null空时,返回表达式2的值。

示例:

select CompanyName,

ISNULL(cast(regTime as varchar),'未添加注册时间') 

from dbo.Customer

按照执行的操作分类

1、数据操纵语言DML触发器,是指触发器所在数据表中发生了insert、update、delete操作时触发。

2、数据定义语言DDL触发器,这类触发器是指当服务器或数据中执行了create、alter、drop语句时被触发。

3、登录触发器:是指当用户登录sql server时触发。

语法

declare @变量名 table(字段列表….)

示例2:

declare @mytable table(

id int identity primary key,

name nvarchar(50),

age int

)

insert into @mytable values

('小强',17),

('小明',13),

('小红',18),

('小丽',1)

select * from @mytable

触发器的使用

练习:

1、 在查询学生数据的时候,根据学生年龄,来显示学生表中的每个学生是否已成年。通过自定函数实现。

2、 在查询学生数据的时候,根据学生的地址,来判断其中是否包含“重庆”两个字,包含显示”本地学生”,不包含则显示“外地学生”。通过自定函数实现。

3、 针对student表,用用户自定义函数,实现每页为10条的分页。传入参数是页数pageIndex,传出参数是存放了每页数据的结果集。

create function getPage(@pindex int,@psize int)

returns table

as

return(select top(@psize) * from student 

where id not in

(

select top(@psize*(@pindex-1)) id from student order by id

) order by id

)

select * from dbo.getPage(3,3)

sqlServer_游标

执行存储过程

EXEC[UTE] 存储过程名

示例:

CREATE PROC  addNum 

@num1 int,@num2 int

AS

select @num1+@num2

execute addNum 25,13

通常外键在外键引用表上添加

首先要区别哪张表是主键表(是指用已经存在的值作为约束范围),哪张表是外键表(是指添加数据时被约束必须符合范围的那张表) 。

 

创建外键的时候,通常是在外键表上创建的。

修改存储过程

语法:

Alter proc[edure] 存储过程名 [@参数1 数据类型],[@参数2 数据类型],[@输出参数名 数据类型] OUTPUT

示例:

ALTER PROC porcTest AS select * from student

SQL Management Studio的界面操作

左侧目录中,我们可以创建数据库数据表。

左上角的新建查询按钮,可以打开一个输入与执行SQL语句的窗口。在此窗口中我们可以通过按F5键或点击“执行”来运行SQL语句。 在输入多条SQL语句的情况下,可以选中需要执行的代码,然后按F5来只执行被选中的部分。

参照完整性约束

某一字段的值,必须包含于(当前表或其他表的)其他字段值的范围内。

@@ROWCOUNT

受影响的行数

示例:

delete from teacher

select @@ROWCOUNT

视图分类

select

一次输出多个变量

示例:

select  @num1 as 总数,@num2

创建带默认值的输入参数的存储过程

带有默认值参数,可以不输入具体的参数值,在不输入值使用默认值。

语法

CREATE PROC[EDURE] 存储过程名

[@参数名1 数据类型=默认值 ]…

AS   SQL语句序列……

top子句

SQL Server Management Studio

它就是SQL server的图形化的管理界面,也就是客户端。

创建不带参数的存储过程

语法:

CREATE PROC[EDURE] 存储过程名 AS   SQL语句序列……

添加字段

ALTER TABLE dbo.student

ADD --这个关键字代表添加

phoneNum char(20) DEFAULT '00000000',

sAddress nvarchar(100) ,

createTime DateTime DEFAULT GETDATE()

--GETDATE()代表获取系统当前时间

事后触发器

当用户执行某种操作完成以后,才会被触发的触发器。

算术函数

操作对象仅限于:int、float、money、smallmoney、decamal

聚集索引

比如:对于新华字典来说其最主要的、物理上的实际排列方式就是拼音顺序。

聚集索引就是数据的最主最的排列方式,对于数据表而言,自增主键id就是聚集索引。

一张数据表只能有一个聚集索引。

数值类型

数据类型

取值范围

存储空间

tinyint

0~255

1字节

smallInt

-2768到32767

2字节

int

-231到231-1

4字节

bigint

-263到263-1

8字节

decimal(p,s)

-1038+1到1038-1

5到17字节

numeric(p,s)

-214748.3648到214748.3647

4字节

smallmoney

-922337203685477.5808到

922337203685477.5807

9字节

money

-3.438到-1.1838,0, 3.438到1.1838

4字节

real

-1.79308到-2.23308, 0, 1.79308到2.23308

4字节或8字节

说明:decimal(8,3) 表示存储了一个8位数字,小数位数是3位。

按唯一性分类

CAST()

示例:

select '我们班上有'+CAST(10 as  varchar(2))+'个同学'

语法:

局部变量必须以@开头

Declare @变量名 类型 [,@变量名2 类型]…..

声明一个或多个变量,示例:

declare @num1 int ,@num2 int

重命名索引

Exec sp_rename ‘表名.原索引名’ , ’新索引名’ ,’index’

示例:

Exec sp_rename 'student.IX_ageMore','IX_AM','index'

开启或关闭触发器

得到一张表中的所有字段的信息

select * from syscolumns where id=OBJECT_ID('Customer')

查看触发器的内容

exec sp_helptext 触发器名称

示例:

exec sp_helptext mytrgger

写法:

@@变量名

例子:

SELECT @@SERVERNAME,@@CONNECTIONS

全局变量只能访问,不能赋值。

 

练习:

1、 用create创建学生表(sid、sname、sage、cid)和班级表(cid、cname、cteacher)

2、 对这两张表添加外键约束,班级表是主键表、学生表是外键引用表。

3、 在学生表中添加一个不存在的班级试一下。

4、 在班级表中删除一个已经被引用的班级试一下。

 

临时表

用于短期存储数据据的表,它使用的是系统的tempdb数据库。

DateLength()

得到数据的实际长度,示例:

select

CompanyName,

DATALENGTH(CompanyName)/2 as '名称长度'

from dbo.Customer

标准视图

就是由一个或多个物理表通过条件查询语句组成的视图,理论上所有用select语句查询出的结果集都可以用来生成视图。

并且,我们对视图中的数据进行修改时会直接影响到其原来的物理数据表。

PRIMARY KEY

设置该字段为主键

如何给一张没有主键的表添加主键

ALTER TABLE Table_1

ADD CONSTRAINT PK_table111

PRIMARY KEY (id)

ALTER TABLE 修改数据表

最常见的操作就是修改数据表名和表中的字段。

 

非唯一索引

在一个字段中,可以存在相同的数据。

DROP删除数据库

DROP DATABASE 数据库名

约束的分类

练习

1、通过游标和循环,查询学生表中的奇数行的数据。

2、通过游标和存储过程来实现对学生表的分页功能。

sqlServer_事务

比如你去银行转账,你从帐户A转5000元到帐户B,实际上这就要分两步来操作,第一步,帐户A上扣除5000元。第二步,在帐户B上存入5000元。如果第一步已完成,但是由于某些原因导致第二步没有正确执行的话。就会造成很严重的损失。

因此,我们就需要一种机制,来确保第一条语句执行之后,第二条语句也会执行。但是实际情况下,我们不可能100%确保这一点。因此退而求其次,用一种机制来确保,如果第二条语句没有正确执行的话,那么我们就撤消第一条语句所执行的操作,这种机制就叫做事务。

可以形象的将其理解为操作软件时的历史记录。

查看一张数据表中的所有索引的相关信息

exec sp_helpindex 数据表名

示例:

exec sp_helpindex student

T-SQL中的流程控制

修改数据库名

ALTER DATABASE test MODIFY NAME = test22

将数据库test改名为test22

联合后返回重复的数据

union联合后的结果自动去除掉多个select结果中的重复数据,如果需要重复显示这些重复数据,我们可以使用union all关键字:

select * from student where age>20

union all

select * from student where sex = 1

 

创建与修改数据库、表

 

视图

在我们数据库中实际上存在很多的物理表。而视图就是根据物理表的查询结果,来生成的一张虚拟的数据表。

语法:

create trigger 触发器名 on 表名 for 操作类型

AS

若干T-SQL语句

GO

注意:DML触发器是针对某张表的某类型操作而触发的。

根据约束的方法

主键约束

外键约束

唯一约束

CHECK约束

DEFAULT约束

规则

默认值

示例:

create Unique nonclustered index

IX_ageMore on student(name desc)

1、定义一个游标

可以理解成在数据集上的指针,我们可以控制这个指针来一条条的将数据集遍历出来,或者也可以仅仅用于得到特定的行,所以游标必须定义在select语句查询的数据集之上。

if..else语句

如果…或者

两段代码中只会执行一段

启动Management Studio

在登陆界面输入相关的信息:

服务器类型:数据库引擎

服务器名称:我们可以输入IP地址, 计算机名称。如果是访问本机的SQL server服务并且没有改变默认端口号的话,只需要输入一个点 ” . ”,它就代表本机的SQL Server正式版的服务端。(体验版是.SQLEXPRESS)

身份验证:SQL Server身份验证

用户名:sa

密码:sa

case语句

相当于swith,就是以一个变量的值来决定执行程序的哪一个部分。

CHECK约束条件示例

限制字段age的数据范围为0到250

age BETWEEN 0 AND 250

限制字段PhoneNum 值必须为电话座机号

PhoneNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

限制字段的值为多个可选值之一,比如:学历(初中、高中、大专、本科、研究生、博士)

xueLi IN('初中','高中','大专','本科','研究生','博士')

限制一个字段的值必须小于另外一个字段,比如年龄必须大于工龄。

(age>workYears)

 

T-SQL变量

T-SQL变量根据使用范围我们可以划分为:全局变量(系统变量)和局部变量(用户变量)

SQL server的管理工具

示例:

CREATE PROC  addNum 
@num1 int,@num2 int,
@result int OUTPUT
--定义输出变量@result,它的值会被自动输出
AS
select @result=@num1+@num2
-------执行存储过程-----
DECLARE @result2 int;
--定义变量@result2
execute addNum 8,1,@result2 OUTPUT
--执行存储过程addNum将其输出结果存放在@result2中
select @result2
--显示@result2中的内容

TEXTIMAGE_ON

与ON的作用类似,但是它只有在表中有Text或Image类型的字段时才有效。

建立索引

FORWARD_ONLY和SCROLL二选一

FORWARD_ONLY只能一行一行的前进,而不能后退或跳过中间的行。

SCROLL定义的游标可以在数据集的任何方向的任何位置移动。

示例:

declare Cursou_test cursor for --未定义移动方式

select id,name from student

declare Cursou_test2 cursor FORWARD_ONLY for --只进游标

select id,name from student

declare Cursou_test3 cursor SCROLL for --滚动游标

select id,name from student

open Cursou_test

open Cursou_test2

open Cursou_test3

FETCH NEXT FROM Cursou_test --只能一行行前进

FETCH NEXT FROM Cursou_test2 --只能一行行前进

FETCH ABSOLUTE 1 FROM Cursou_test3 --表中的绝对位置的正序第条

FETCH RELATIVE 2 FROM Cursou_test3 --相对于当前针对位置前进条

FETCH RELATIVE -2 FROM Cursou_test3 --相对于当前针对位置后退条

FETCH FIRST FROM Cursou_test3 --第一条

FETCH LAST FROM Cursou_test3 --最后一条

创建带输出参数的存储过程

语法

CREATE PROC 存储过程名

[@参数名1 数据类型 ][, [@参数名2 数据类型 ]]…

[@输出参数名 数据类型 ] OUTPUT

AS   SQL语句序列……

通过sql manageMent studio 来添加外键

1、确定需要被限制的数据表。

2、进入被限制的数据表的“设计”视图,在空白处点击右键菜单中的“关系”项。

3、点击添加按钮新建一个约束。

4、选中新添加的约束,在右侧的“表和列规范”后面有一个按钮”…”,点它打开外键关系编辑窗口。

5、选中相应的表的相应字段即可。

 

练习:

创建一个表变量用于存放彩票号码,有两字段(id,num char(8))。

用于随机函数生成50个彩票号码,存入这个表变量中。然后显示出所有的彩票号码。

用户自定义函数UDF

user define function

它非常类似于存储过程或者js中的function,通常而言它都是配合select语句来进行使用的,它的用法很像针对某个字段进行操作的聚合函数。

练习:

创建一个班级表,并与学生表建立级联关系。要求删除班级的时候,这个表中的学生信息也同时被删除。

示例:

比如创建一个触发器mytrigger用来监视student这张表的update操作,只要执行update语句,就会激活触发器mytrigger

create trigger mytrgger3 on student for update
as
print '这是第三个触发器'
update student set name = '小小白' where id=1

注:当我们针对同一张表的同一操作定义了多个触发器的时候,这多个触发器会被同时触发。

语法:

SELECT <字段列表> FROM <第一张表> <连接类型> <第二张表> <ON 连接条件>

语句块

if 9-5=5

 begin --相当于{

print '你说对了'

 end --相当于}

else

begin

print '你说错了'

end

事务的实际操作

当我们开启事务之后,我们操作的实际上都是缓存中的数据。只有当提交事务的时候,操作才会写入日志。

用途:

它的可以起到简化查询语句的作用,避免编写重复的语句。

同时要注意,它还可以返回表

主键约束

确保主键的值是唯一的。

本文由必威发布于必威-数据,转载请注明出处:以 Sql 必威:Server 数据库为例,基础概念

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。