【数据库原理与应用 - 第六章】T-SQL 在SQL Server的使用

目录

一、数据库定义语言DDL

1、数据库的定义

（1）创建数据库

（2）管理数据库

2、基本表的定义

（1）创建基本表

（2）修改基本表

3、索引的定义

（1）创建索引

（2）管理索引

4、视图的定义

（1）创建视图

（2）修改删除视图

（3）视图的查询和更新

二、数据查询语言DQL

1、select语句介绍

2、简单查询 

（1）查询表中的若干行

（2）对查询结果进行排序

（3）汇总查询

3、连接查询

（1）内连接 inner join

（2）外连接 outer join

（3）自连接 self join 

（4）交叉连接 cross join

4、嵌套查询

（1）非相关子查询

（2）相关子查询

5、组合查询

（1）union并操作

（2）intersect交操作

（3）except差操作

三、数据更新语言DML

1、插入数据

（1）插入单个元组

（2）插入多个元组

2、修改数据

（1）修改数据语法格式

（2）修改单个元组

（3）修改多个元组

3、删除数据

（1）delete

（2）truncate

4、数据更新操作检查完整性

（1）插入数据时需要分别检查实体完整性与参照完整性

（2）删除数据时需要检查参照完整性

（3）修改数据时需要检查实体完整性与参照完整性

四、数据控制语言DML

1、数据控制方法

2、sql server数据库操作权限

3、对象权限设置

4、语句权限设置

五、数据库初步编程

1、变量

（1）变量的声明

（2）变量的赋值

2、流程控制

（1）begin end

（2）if else

（3）while

（4）case

（5）waitfor

（6）goto

（7）注释

3、存储过程编程

（1）创建存储过程

（2）执行存储过程

（3）存储过程应用实例

（4）删除和修改存储过程

4、触发器编程

（1）DML触发器

（2）DDL触发器

（3）删除和修改触发器

六、嵌入式SQL

---

一、数据库定义语言DDL

1、数据库的定义

（1）创建数据库

create database 数据库名称

[ on --定义数据库的数据文件
    [primary]  --设置主文件组
    <数据文件描述符>[,...n]  --设置数据文件的属性
    [,filegroup 文件组名 <数据文件描述符>[,...n]] --设置次文件组及数据文件属性
]

[ log on                      --定义数据库的日志文件
    {<日志文件描述符>}[,...n]  --设置日志文件的属性
]

其中<数据文件描述符>和<日志文件描述符>为以下属性组合：

( name=逻辑文件名, --设置在SQL Server引用的名称
  filename='物理文件名'  --设置文件中磁盘存放的路径和名称
  [,size=文件初始容量]
  [,maxsize=文件最大容量|unlimited]
  [,filegrowth=文件增长率%]  --设置文件自动增量
)

例：在E盘的data文件夹下建立名为高校图书管理的数据库，主数据文件为高校图书管理_data，初始容量10MB，最大容量为50MB，增幅为10MB。日志文件为高校图管理_log,初始容量大小5MB,最大容量为20MB,增幅为5MB。

create database 高校图书管理

on primary  --设置主文件组
(
    name=高校图书管理_data
    filename='E:\data\高校图书管理_data.mdf',
    size=10MB
    maxsize=50MB
    filegrowth=10%
) --设置数据文件的属性

log on                      --定义数据库的日志文件
(
    name=高校图书管理_log,
    size=5MB,
    maxsize=20MB,
    filegrowth=5%
) --设置日志文件的属性

go

注：如果创建时没有指定日志文件，系统自动创建一个初始容量为0.75MB的日志文件，且无最大容量限制

（2）管理数据库

alter database 数据库名称 --修改数据库

drop database 数据库名称 --删除数据库

sp_helpdb 数据库名称 --查看数据库属性

2、基本表的定义

（1）创建基本表

create table 表名
(
    列名,数据类型[(长度)]
    [default 常量表达式] --定义默认值
    | [ indetity[(初值,增量)] ] --定义标识列
    
    [constraint 约束名]
    {
        [NULL|NOT NULL] --设置空或非空约束

        |[default]  --设置默认值约束

        |[ [primary key|unique]  --设置主键或唯一性约束 
           [clustered|nonclustered] --设置聚集或非聚集索引
         ]  
        
        |[foreign key(外键列)] references 被参照表名(外键列)  --设置外键约束

        |check(逻辑表达式)  --设置检查约束
    }
)

主键约束格式：

[constraint<约束名>]primary key(<列名>...) 

eg:
constraint c3 primary key(dzkh,tsbh)  --设置主键单独列出

dzkh nvarchar(20) primary key  --直接在列后设置主键

外键约束格式：

[constraint<约束名>]foreign key(<列名>) references<主键表名>(<列名>)

eg:
constraint c1 foreign key(类别编号) references 读者类别(类别编号) --单独列出

• 外键设在哪个表？  哪个表的某属性需要被约束，则该表的该属性设为外键
• eg：选修表中学号需要被约束，则外键表为选修表，主键表为学生表

唯一性约束格式：

constraint 约束名 unique(<列名>)

主键与唯一性约束区别

主键	唯一性约束
唯一地标识表中的某一条记录，可以定义一列或多列为主键	限制不受主键约束的列上的数据的唯一性，用于作为访问某行的可选手段，一个表上可以放置多个唯一性约束
没有重复值，不允许空	没有重复值，允许空
可作外键	不可作外键

【例1】创建读者表

create table 读者
(
    读者卡号 nvarchar(10) primary key,
    姓名 nvarchar(16) NOT NULL,
    性别 nvarchar(1) NOT NULL default'男',
    单位 nvarchar(30) NOT NULL,
    办卡日期 date NOT NULL,
    卡状态 nvarchar(5) NOT NULL,
    类别编号 nvarchar(2),
    constraint c1 check(性别 in('男','女')),
    constraint c2 foreign key(类别编号) references 读者类别(类别编号)
)
go

【例2】创建借阅表 

create table 读者
(
    读者卡号 nvarchar(10),
    图书编号 nvarchar(8),
    借书日期 date NOT NULL,
    还书日期 date,
    constraint c3 primary key(读者卡号,图书编号),
    constraint c4 foreign key(读者卡号) references 读者(读者卡号),
    constraint c5 foreign key(图书编号) references 图书(图书编号)
)
go

（2）修改基本表

【例1】在读者表新增整型列id，该列作为每行数据的标识，并自动增加，初始值为1；在读者姓名一列增加唯一性约束，约束名为uk_dz_dzxm

use 高校图书管理
go

alter table 读者
add id int indetity(1,1)
go

alter table 读者
whith nocheck --禁用约束检查
add constraint uk_dz_dzxm unique(姓名)
go

注：

1. identity是“自增标识列”，每个表只能创建一个标识列，数据类型只能是数值型，不能对标识列绑定默认值或default约束
2. 如果想忽略对原有数据的约束检查，使用with nocheck——使增加的约束只对以后更新或插入的数据起作用，不能将with check/with nocheck用于主键和唯一性约束
3. 新增列不能定义为NOT NULL

【例2】将读者类别表中类别编号设为主键

alter table 读者
add primary key(类别编号)

【例3】将读者表的id列和姓名列删除

alter table 读者
drop column id
go


alter table 读者
drop constraint uk_dz_dzxm
go

alter table 读者
drop column 姓名
go

注意：如果某列有约束或默认值，该列无法删除。必须先删约束，再删该列

3、索引的定义

索引机制：执行查询操作时，先查询索引文件得出元组在数据表的地址，再根据这个地址在数据表中直接取出该元组（跟map类似）

聚集索引与非聚集索引的区别

• 聚集索引一个表只能有一个，非聚集索引一个表可以存在多个
• 聚集索引存储记录是物理上连续存在，非聚集索引是逻辑上的连续，物理存储并不连续
• 聚集索引：物理存储按照索引排序，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
• 非聚集索引：物理存储不按照索引排序，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序
• 聚集索引插入数据时速度要慢，查询数据比非聚集数据的速度快。

（1）创建索引

create [unique][clustered][nonclustered] index 索引名
on {表名|视图名}(列名1[ASC|DESC]……)

• unique 唯一性索引：不允许具有索引值相同的行，省略unique时，创建的是非唯一索引
• clustered 聚集性索引：省略clustered时，创建的是非聚集性索引

【例1】在图书表的书名列上，创建一个名称为idx_sm的唯一索引，并依据书名升序排序

use 高校图书管理
go
create unique index idx_sm on 图书(书名)
go

【例2】在读者表的姓名和性别列上，创建一个名称为idx_xmxb的唯一非聚集性复合索引，依据姓名升序、性别降序排序

create unique nonclustered index idx_xmxb on 读者(姓名,性别DESC)

（2）管理索引

① 修改索引

alter index 索引名 on 表名|视图名
{rebuild|reorganize|disable}

• rebuild：指定将相同的列、索引类型、唯一性属性、排列顺序重新生成索引，可以重新启动已禁用的索引
• reorganize：重新组织索引
• disable：将索引标志为禁用

② 删除索引

drop index 索引名

• 如果要删除为了实现主键或唯一约束而创建的索引，必须删除约束
• 在删除聚集索引时，表中所有非聚集性索引都被删除

③ 查看索引

exec sp_helpindex '表名'

【例1】查看读者表定义的索引信息

exec sp_helpindex '读者'

4、视图的定义

（1）创建视图

create view <视图名>(列名……)
as<select 语句>
[with check option]

• select语句中不允许含有compute (by)子句、into子句、distinct子句
• with check option：强制视图中执行所有的数据插入、删除和更新操作必须满足select的where表达式

① 行列子集视图

行列子集视图是指只删除基本表的某些行和某些列，但保留了码的视图

【例1】创建一个名为view_dw的视图，通过该视图，可以查询读者单位为"数计学院"的所有读者信息。该视图可以查询读者单位为"数计学院"的所有读者信息，要求透过该视图进行的更新操作只涉及数计学院的读者

create view view_dw
as select *
   from 读者
   where 单位='数计学院'
   with check option

select *
from view_dw

② 连接视图

连接视图指多个数据表连接起来的视图

【例2】创建一个名为view_dzjy的视图，通过该视图可以查询每一个读者的姓名、性别、单位、图书编号、书名、借书日期、还书日期

create view view_dzjy
as select xm,xb,dw,图书.tsbh,sm,jsrq,hsrq
from 读者,图书,借阅
where 图书.tsbh=借阅.tsbh and 读者.dzkh=借阅.dzkh

③ 分组统计视图

通过使用聚集函数生成指定字段所需的统计数据

【例3】在高校图书管理数据库中，创建一个名为 view_dzjytj 的视图，要求通过该视图，可以查询每一位读者的借书册数

create view view_dzjytj(读者卡号,借书册数)
as select 读者卡号,count(*)
   from 借阅
   group by 读者卡号 --根据读者卡号分组

create view view_dzjytj
as select 读者卡号,count(*) as 借书册数
   from 借阅
   group by 读者卡号 --根据读者卡号分组

【例4】创建一个名为view_dzjyxx的视图，要求通过该视图，可以查询每一位读者的详细信息和借书册数

可以将例3中的视图和读者表连接

create view view_dzjyxx
as select 读者.*,借阅册数
   from view_dzjytj,读者
   where 读者.读者卡号=view_dzjytj.读者卡号

（2）修改删除视图

① 修改视图

alter view 视图名(列名……)
as<select 语句>
[with check option]

alter view 无法更改视图名称

② 删除视图

drop view 视图名

一个视图被删除后，由该视图导出的其他视图也将失效

（3）视图的查询和更新

① 视图更新的限制

• 视图的字段来自字段表达式或常数，则不允许对视图执行INSERT 和UPDATE操作，但允许执行DELETE操作
• 如果视图的字段来自聚合函数，则视图不允许更新
• 创建视图的SELECT语句中使用分组子句GROUP BY，以及TOP、DISTINCT、UNION关键字时，视图不允许更新
• 不能删除依赖于多个数据表的视图
• 视图定义中如果有嵌套查询，且内层查询中涉及了与外层一样的导出该视图的基本表，则视图不能更新
• 因为视图是从基本表导出的，所以对视图的插入、删除和更新操作必须遵守源基本表的完整性约束条件

② 使用视图更新数据

• 由于视图不是实际存储的，是虚表，因此对视图的更新最终要转换为对基本表的更新
• 为了防止用户通过视图对数据进行修改，无意或故意操作不属于视图范围内的基本数据，可在定义视图时加上with check option语句，这样在视图上进行数据更新时，DBMS会进一步检查视图定义中的条件，如果不满足，则拒绝执行该操作

二、数据查询语言DQL

1、select语句介绍

select [all|distinct] [top n[percent]] 目标列
[into 新表名]
from 表组
[where 筛选条件]
[group by<分组列名> [with{cube|rollup}]] [having <组选择条件>]
[order by[all] <排序列名>[asc|desc]]

• select：写什么查询结果就显示什么。all是默认值，distinct去重，top n表示返回前n行，加上percent就是返回结果的前百分之n行
• into：用于创建一个新表，将查询结果插入该新表中。如果select有计算数值的字段，必须指定别名
• group by：当select后有统计函数，如果有group by，则统计为分组统计，否则是对整个结果集进行统计。group by后还可以跟having子句表达组选择条件，缩小查询范围
• order by：对结果集进行排序。ASC升序排序，DESC降序排序

2、简单查询 

（1）查询表中的若干行

① top和distinct

【例1】查询借阅表的前4条记录信息

select top 4 *
from 借阅

【例2】查询借阅了图书的读者卡号（去重）

select distinct 读者卡号
from 借阅

② 查询满足条件的元组

【例3】查询清华大学出版社出版的计算机类或管理类图书信息

select *
from 图书
where 出版社='清华大学出版社' and 类别 in('计算机类','管理类')

select *
from 图书
where 出版社='清华大学出版社' and (类别='计算机类' or 类别='管理类')

③ 模糊查询

[not] like'<匹配串>'[escape '<换码字符>']

        查找指定的属性列值与匹配串相匹配的记录。匹配串可以是一个完整的字符串，也可以是含有通配符的字符串

通配符包含：

• %：表示任意长度的字符串（长度可以为0）
• _：表示单个字符
• [ ]：表示方括号内字符列表的任意一个字符
• [^]：表示不在方括号内字符列表的任意一个字符
• [-]：表示方括号内-范围内的任何一个字符，例如[A-D]
• 如果用户要查询的字符串本身就有通配符，则用escape'换码字符'对通配符进行转义
• 例：查询"DB_1"，查询like 'DB/_1' escape' / '，说明跟在' / '后的'_'不再是通配符，