SQL Server

基本介绍：美国Microsoft公司推出的一种关系型数据库系统。SQL Server是一个可扩展的、高性能的、为分布式客户机/服务器计算所设计的数据库管理系统，实现了与WindowsNT的有机结合，提供了基于事务的企业级信息管理系统方案。

服务的启动方式：

• 命令行：net start mssqlserver

• 任务管理器：找到服务再找到mssqlserver

• 右击此电脑打开管理：

打开SQL server管理工具的方式：

1. 在开始菜单搜索：SSMS(SQL Server Management Studio)

连接不到数据库的原因：

1. 服务没有开启

2. 密码或账号错误

3. 防火墙没有关闭

4. 网络原因（没有使用tcp/ip协议 在SQL server配置管理器里面打开网络配置然后开启tcpip协议）

身份验证

windows身份验证：

使用windows账号登录

混合身份验证(sqlserver的身份验证模式和windows身份验证)：

既可以用windows账号登录也可以用SQL server的登录名登录

设置身份验证：

版本

Microsoft SQL Server Management Studio 10.50.6000.34
Microsoft Analysis Services 客户端工具 10.50.6000.34
Microsoft 数据访问组件 (MDAC) 10.0.19041.4291
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.19041.0
Microsoft .NET Framework 2.0.50727.9174
操作系统 6.3.19045

端口号：1433

SQL server的用户和角色

在SQL server数据里面分为两种级别分别是服务器级别和数据库级别

服务器级别

为帮助您管理服务器上的权限，SQL Server 提供了若⼲角色，这些角色是⽤于对其他主体进⾏分组的安全主体。 服务器级角色的权限作⽤域为服务器范围。SQL Server 提供了九种固定服务器角色， ⽆法更改授予固定服务器角色的权限，这9组角色分别如下：

sysadmin —— 在SQL Server中进⾏任何活动，该角色的权限跨越所有其它固定服务器角色

serveradmin —— 更改服务器范围内的配置选项并关闭服务器

setupadmin —— 添加和删除链接服务器，并执⾏某些系统存储过程（如 sp_serveroption）

securityadmin —— 授予数据库引擎的访问权限和配置⽤户权限的能⼒使得安全管理员可以分配⼤多数服务器权限，securityadmin 角色应视为与sysadmin 角色等效

processadmin —— 管理在 SQL Server 实例中运⾏的进程

dbcreator —— 创建和改变数据库，但是没有读取权限

diskadmin —— 管理磁盘⽂件

bulkadmin —— 执⾏ BULK INSERT 语句

public —— 每个 SQL Server 登录名均属于 public 服务器角色。 如果未向某个服务器主体授予或拒绝对某个安全对象的特定权限，该⽤户将继承授予该对象的 public 角色的权限。当您希望该对象对所有⽤户可⽤时，只需对任何对象分配 public 权限即可。 您⽆法更改 public 中的成员关系。public 的实现⽅式与其他角色不同，但是，可以从 public 授予、拒绝或撤销权限。

注意：

>>登录名⼀定属于某些角色，默认为public

**>>**服务器角色不容许更改

**>>**登录后也不⼀定有权限操作数据库

数据库角色

数据库角色：SQL Server 中有两种类型的数据库级角色：数据库中预定义的**“固定数据库角色”和您可以创建的“灵活数据库角色”**

固定的有某些权限的数据库角色，所有数据库中都有这些角色，默认有10个，分别如下；

public

–public 角色是⼀个特殊的数据库角色，每个数据库⽤户都属于它。public 角色：

**>>**捕获数据库中⽤户的所有默认权限。

**>>**⽆法将⽤户、组或角色指派给它，因为默认情况下它们即属于该角色。

**>>**包含在每个数据库中，包括 master、msdb、tempdb、model 和所有⽤户数据库。

**>>**⽆法除去。

db_owner

**–**进⾏所有数据库角色的活动，以及数据库中的其它维护和配置活动。

**–**该角色的权限跨越所有其它固定数据库角色。

db_accessadmin

**–**在数据库中添加或删除 Windows NT 4.0 或 Windows 2000 组和⽤户以及 SQL Server ⽤户。

db_datareader

**–**查看来⾃数据库中所有⽤户表的全部数据。

db_datawriter

**–**添加、更改或删除来⾃数据库中所有⽤户表的数据

db_ddladmin

**–添加、修改或除去数据库中的对象(**运⾏所有 DDL)

db_securityadmin

**–**管理 SQL Server 2000 数据库角色的角色和成员，并管理数据库中的语句和对象权限

db_backupoperator

**–**有备份数据库的权限

db_denydatareader

**–**拒绝选择数据库数据的权限

db_denydatawriter

**–**拒绝更改数据库数据的权限

数据库角色具体位置在 数据库**——>某个具体库——>安全性——>角色——>**数据库角色

SQL server架构

架构：架构（Schema）是⼀组数据库对象的集合，它被单个负责⼈（可以是⽤户或角色）所拥有并构成唯⼀命名空间。可以将架构看成是对象的容器。

**>>**数据库⽤户对应于服务器登录名以便登录者可以操作数据库

**>>**数据库角⾊可以添加，可以定制不同权限

**>>**数据库架构，类似于数据库对象的命名空间，⽤户通过架构访问数据库对象

服务器级 -> 数据库级 -> 架构级 - > 数据对象级

注意：

⼀个⽤户可以拥有多个架构，但是不能对没有拥有的架构进⾏操作。⼀个数据库角色，是对不同架构⾥

⾯数据对象的权限组织，也有可能涉及到多个架构，当某⼀个⽤户被转换成⼀种数据库角色的时候，假如这个⽤户本⾝不拥有某⼀个架 构⽽该数据库角色拥有，那当它对那个架构进⾏操作的时候就会出错。

相互之间的关系

1. 登录名与数据库⽤户在服务器级别是⼀对多的，在数据库级别是⼀对⼀的⼀个登录名可以和多个不同库下的⽤户做映射，但是在同⼀个库下只能和⼀个⽤户做映射，并且⼀ 个⽤户名可以和多个登录名有映射关系， 真正有权限的是⽤户名，只不过是⽤登录名去登进数据库，然后去找映射的⽤户名，这样就有了相应的权限，刚开始建⽴的登录名只要把该登录名的服务器角⾊设置为Database Creator，就可以创建新的数据库了，并且新建⽴的这个数据库中会⾃动⽣成两个⽤户名：dbo和guest。并且刚刚建⽴的登录名就和dbo映射了，还有就是rbo也和sa映射了，因为sa作为 管理员的登录名和每个库中的rbo⽤户映射。

2. ⼀个数据库角色有可能涉及多个架构，数据库角色对应的是权限

3. ⼀个⽤户对应⼀个数据库角色

4. ⼀个数据库⽤户可以对应多个架构（架构是表容器），架构⾥⾯包含的是数据库表。

SQL server系统库

SQL Server提供了四个系统数据库，包括master、msdb、model和tempdb。

• master系统数据库存储SQL Server实例的系统级信息。

• msdb数据库由SQL Server代理用于作业和警报。

• model数据库用作创建其他数据库的模板。

• tempdb系统数据库存储临时对象，并在每次SQL Server启动时重新创建。

TRANSAC-SQL (T-SQL)

标识符

SQL Server的所有对象，包括服务器、数据库以及数据库对象，如表、视图、列、索引、触发器、存储过程、规则、默认值和约束等都可以有一个标识符。就是对象的名字。

数据库对象的全名应该由服务器名、数据库名、拥有者名和对象名这四个部分组成，格式如下：

[[[server.][database].][owner_name].]object_name 命名必须都要符合标识符的规定。

命名规则

规则标识符：符合标识符命名规则的

标识符不允许是T-SQL的保留字（T-SQL不区分大小写所以不论是大小写的保留字都不能使用）

标识符内部不允许有空格或特殊字符（例如[],‘’）

• 首字母命名规则

  • 英文大小写A-z
  • 其他语言，比如汉字
  • 特殊符号：_，@，#

• 首字母之后

  • 英文大小写A-z
  • 数字0-9
  • 其他语言，比如汉字
  • 特殊符号：_、@、#、$

界定标识符：可以不符合标识符命名规则，但是用界定符号包裹起来的

界定符号：[]或者是’’

注：无论是界定标识符还是规则标识符都最多只能容纳128个字符，对于本地的临时表最多可以有116个字符。

以某些特殊符号开头的标识符在SQL Server系统中具有特定的含义

@：局部变量 用户自己定义的

@@：全局变量 系统定义和维护的

#：局部临时表

##：全局临时表

SQL server创建数据库

数据库文件包括：数据文件(mdf)和日志文件(ldf)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

use master
go

if exists(select * from sysdatabases where name='sql_wei')    -- 判断数据库是否存在
begin
    print '数据库已存在，现在开始删除--------------------'
    drop database sql_wei
    print '数据库删除成功，结束删除----------------------'
end
else
begin
    create database sql_wei
    on primary(
    name='sql_wei',   -- 文件的逻辑名称
    filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\data\sql_wei.mdf', --文件的路径
    size=5mb, -- 初始化大小
    maxsize=100mb, -- 最大的大小
    filegrowth=5%  -- 自增率
    )
    log on(
    name='sql_wei_log',
    filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\data\sql_wei_log.ldf',
    size=5mb,
    maxsize=100mb,
    filegrowth=5%
)
end
go

自增属性

identity：标识列 identity(起始值,每次增长值)

关于主键、候选键、超键

理解超键、候选键、主键概念及关系
1.基本概念
超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键/码。
候选键(candidate key):不含有多余属性的超键称为候选键，即其真子集不再是超键。
主键(primary key):用户选作元组标识的一个候选键称为主键，是候选键之一。
2.关系
候选键是超键的子集，主键是候选键中的一个。
3.例子:
考虑属性集（身份证号，姓名 ，性别 ，年龄），假设无重名
a.其中超键有：身份证号、姓名、（姓名，性别）、（姓名，性别，年龄）等
–这里可以看出，超键是能唯一确定一个人的属性组

b.超键中的候选键身份证号、姓名唯一，而且没有多余属性，所以是一个候选键
–这里可以看出，候选键是没有多余属性的超键
c.选择主键用户可根据自己喜好考虑选择姓名或者身份证号作为主键
–主键是选中的一个候选键

约束

• 主键 primary key

• 默认 default

• 非空 not null

• 检查 check

• 外键 foreign key

• 唯一 unique

数据类型

第一大类:整数数据

bit:bit数据类型代表0,1或NULL,就是表示true,false.占用1byte.
int:以4个字节来存储正负数.可存储范围为:-2^31至231-1.
smallint:以2个字节来存储正负数.存储范围为:-2^15至215-1
tinyint: 是最小的整数类型,仅用1字节,范围:0至此2^8-1

第二大类:精确数值数据

numeric:表示的数字可以达到38位,存储数据时所用的字节数目会随着使用权用位数的多少变化.
decimal:和numeric差不多

第三大类:近似浮点数值数据

float:用8个字节来存储数据.最多可为53位.范围为:-1.79E+308至1.79E+308.
real:位数为24,用4个字节,数字范围:-3.04E+38至3.04E+38

第四大类:日期时间数据

datetime:表示时间范围可以表示从1753/1/1至9999/12/31,时间可以表示到3.33/1000秒.使用8个字节.
smalldatetime:表示时间范围可以表示从1900/1/1至2079/12/31.使用4个字节.

第五大类:字符串数据

char:长度是设定的,最短为1字节,最长为8000个字节.不足的长度会用空白补上.
varchar:长度也是设定的,最短为1字节,最长为8000个字节,尾部的空白会去掉.
text:长宽也是设定的,最长可以存放2G的数据.

第六大类:Unincode字符串数据

nchar:长度是设定的,最短为1字节,最长为4000个字节.不足的长度会用空白补上.储存一个字符需要2个字节.
nvarchar:长度是设定的,最短为1字节,最长为4000个字节.尾部的空白会去掉.储存一个字符需要2个字节.
ntext:长度是设定的,最短为1字节,最长为2G.尾部的空白会去掉,储存一个字符需要2个字节.

第七大类:货币数据类型

money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8 个字节.
smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节.

第八大类:标记数据

timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新.
uniqueidentifier:用于识别数据库里面许多个表的唯一一个记录.

第九大类:二进制码字符串数据

binary:固定长度的二进制码字符串字段,最短为1,最长为8000.

image:为可变长度的二进制码字符串,最长2G.

varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉

SQL server语法操作、

运算符号

• 算数运算符

  • +、-、*、\、%

• 拼接运算符

    * 
  

• 比较运算符

  • <、>、=、>=、<=、<>，!=

• 赋值运算符

  • = --赋值方式 set 和 select

• 逻辑运算符

  • and、or、not

运算符优先级：()>算术运算符>比较运算符>逻辑运算符(not>and>or)>拼接运算符>赋值运算符

DDL（data definition language） – 数据定义语言

对库内的所有对象的结构做操作的语法称为数据定义语言–定义对象结构

[] ---------- 代表可以写也可以省略

create – 创建

数据内的所有对象(表-table、视图-view、存储过程-procedrue、存储函数-function、触发器-trigger、用户user、数据库-database等等)都可以用create创建

基本语法：create 对象关键字 名字;

创建表的语法：create table 表名(

列名 列类型 [约束]，

列名 列类型 [约束]，

…

列名 列类型 [约束] – 最后一个列不需要，隔开

)；

自增：标识列，identity(起始值，增长值)

约束 – 六大约束 都可以用作维护和保证数据一致性和完整性

• 主键约束（primary key）:用来标识数据行，唯一并且非空，一个主键可以是多个列的组合也可以是一个列，但是表里只能有一个主键。

• 唯一约束（unique）：可以为空，但是数据必须没有重复

• 非空约束（not null）：不可以为空

• 检查约束（check）：检查添加或修改的数据符合规则

• 默认约束（default）：当添加或修改时没有给定值，就会填入默认值

• 外键约束（foreign key）：连接另外一个表保证数据一致，有外键的表叫做子表，被连接的表叫父表，子表的外键列可以重复，但是父表被连接的列不可以重复，删除数据是先删除子表的数据再删除父表的数据

DROP – 删除

drop 对象关键字 对象名; – 不可以撤销

truncate table 表名; – 清空表数据 不可撤销

Alter – 修改

语法：alter table 表名 …

添加约束：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

create table StudentInfo(
    ID int not null identity(1,1),--编号
    Number char(10) not null,--学号
    Name varchar(20) not null,--姓名
    Sex char(2) null,--性别
    Age int,--年龄
    Birthday date,--出生日期
    Address varchar(50),--家庭住址
    Class varchar(10)--班级
)

create table Score(
    Number char(10) not null ,--学号
    Subject varchar(20) not null,--科目
    Score decimal(4,1)--分数
)

• – 删除约束
  alter table studentinfo drop constraint st_pk

• – 添加主键约束
  alter table studentinfo add constraint st_pk primary key (id)

• – 外键约束
  alter table score add constraint st_fk foreign key (number) references studentinfo(number)

• – 唯一
  alter table studentinfo add constraint st_uk unique (number)

• – 检查
  alter table studentinfo add constraint st_ck check (age between 6 and 18)

• – 默认
  alter table studentinfo add constraint st_dk default ‘男’ for sex

• – 非空
  alter table studentinfo alter column address varchar(50) not null

DML – 数据操纵语言

insert

insert into 表名 values (所有列的数据除去identity列),…

insert into 表名 select ‘李平’,‘男’,18

insert into 表名 (指定列) values (指定列数据)，…

update

update 表名 set 列=值 where 条件

delete

delete from 表名 where 条件

select

复制表：select * into 表2 from 表1；复制表1给表2 会表结构和表数据

函数

https://learn.microsoft.com/zh-cn/sql/t-sql/functions/cursor-rows-transact-sql?view=sql-server-2016

转换函数 convert(数据类型(长度),表达式[,n])

例子：select convert(nvarchar(32),getdate(),120) – （目标数据类型，要转换的数据，格式代码）

CONVERT 函数支持广泛的数据类型转换，包括整数、浮点数、日期、时间、字符串等。你可以根据具体的需求选择合适的数据类型和样式。

需要注意的是，CONVERT 函数执行显式的数据类型转换，而在某些情况下可能会导致数据截断或格式错误。在使用 CONVERT 函数时，要确保转换操作符合目标数据类型的规范，并小心处理可能出现的错误情况。

数学函数

T-SQL中提供的常用的数学函数如下：

· abs()：返回绝对值

· round(数值表达式,长度,[,类型])：舍入到指定长度或精度。类型为0，表示舍入，类型为非0，表示截断

· power(m,n)：返回m的n次幂

· trunc()：将数字截断到指定的位数 *

· %：求余数，SQL Server没有mod(m,n)，而用m%n代替 取模

      ceiling()：向上取整函数

  floor()

字符串函数

· ltrim(str,substr)/rtrim(str,substr)：str表示要操作的字符串，substr表示要裁剪的子串，若裁剪空格，则可以省略

· substring(str,position,length)：截取字符串

· replace(str,search_str,rep_str)：替换一个字符串中的子串。search_str表示要搜索的子字符串，rep_str表示要替换的目标字符串

· left(str,n)：返回字符串从左边开始的指定个数的字符

· right(str,n)：返回字符串从右边开始的指定个数的字符

· len()：求字符串长度

日期和时间函数

首先列出日期时间函数中的重要参数。

T-SQL中提供下列日期函数：

· getdate()：返回当前的日期和时间 – mysql中的now()

· year(日期)：返回指定日期的“年”部分的整数

· month(日期)：返回指定日期的“月”部分的整数

· day(日期)：返回指定日期的“日”部分的整数

· datepart(日期元素，日期)：返回日期元素指定的日期部分的整数

· datename(日期元素，日期)：以字符串的形式返回日期元素指定时间的日期名称

· datediff(日期元素，日期1，日期2)：返回两个日期间的差值并将其转换为指定日期元素的形式

· dateadd(日期元素，数值，日期) ：按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期

涉及日期时间常量时，SQL Server建议使用与dateformat及语言环境设置无关的字符串格式，通常这样的字符串常量符合下面两种形式：

· 日期之间不使用分隔符，格式为yyyymmdd[ hh:mi:[:ss][,mmm]]，如’20070703’，‘20070703 17:53:00.997’。

· ISO 8601标准形式，格式为yyyy-mm-ddThh:mi:ss[.mmm]，日期各个部分之间使用“-”分隔符，日期和时间部分用T分隔，并且时间部分不能省略，如’2007-07-03T17:53:10’。

批处理-GO

批处理是指从应用程序一次性地发送一组完整SQL语句到SQL Server上执行。

批处理的所有语句被当作一个整体，被成批地分析，编译和执行。

所有的批处理指令以GO作为结束的标志

GO的特点:

1、GO语句必须自成一行，只有注释可以在同一行上

2、每个批处理单独发送到服务器

3、GO语句不是T-SQL命令

事务-TRANSACTION

概念：成批的sql语句要么全部执行，要么全部不执行；

特点：

• 原子性：事务是数据库的逻辑工作单位。不可分割，要么全部都做要么都不做

• 一致性：从一个正确的状态到另外一个正确的状态

• 隔离性：事务与事务之间互不干扰

• 持久性：事务一旦提交，结果就是永久的

事务和批的区别

编程时，一定要区分事务和批的差别。

1）批是一组整体编译的SQL语句，事务是一组作为单个逻辑工作单元执行的SQL语句。

2）批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。

3）当在编译时，批中某个语句存在语法错误，系统将取消整个批中所有语句执行，而在运行时刻，如果事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。

事务分类

SQL Server 的默认事务隔离级别是“读已提交”（READ COMMITTED）

显式事务

就是有明确的开始和结束：

	开启事务：BEGIN TRANSACTION/TRAN

	设置事务节点：save transaction 名称

	提交事务：COMMIT TRANSACTION/TRAN  ,COMMIT WORK

	回滚事务：ROLLBACK TRANSACTION/TRAN ,ROLLBACK WORK

隐式事务

需要开启隐式事务：SET IMPLICIT_TRANSACTIONS ON -- 开启隐式事务

没有明确的开始事务，事务提交之后默认开启下一个事务

只有提交和回滚：COMMIT/ROLLBACK  TRANSACTION/TRAN

自动化事务

SQL server默认的事务模式：不需要自己开启或结束事务，默认自动提交

设置事务保存节点：SAVE TRANSACTION 事务名称 节点以下的是事务节点内的操作

事务个数全局变量：@@TRANCOUNT

回滚事务不能回滚指定事务 只能回滚部分事务 需要save tran 事务节点名 回滚事务节点这一部分的事务

提交事务可以提交指定事务名 但是不能提交部分事务

存储过程

概念：是一组预先编译的sql语句

 ⼀组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，⽤户通过指定存储过程的名称并给出参数来执行。

Transact-SQL中的存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存在内存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。

调用方式：exec

存储过程的优点

• 简化操作，提高了sql语句的重用性，减少了开发程序员的压力，增加了可移植性

• 减少操作过程中的失误，提高效率，执行速度快

• 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）减轻网络负担

• 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

SQL server的系统存储过程

都是以sp_开头

概念：系统存储过程是系统创建的存储过程，⽬的在于能够⽅便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有⼀些系统存储过程会在创建新的数据库的时候被⾃动创建在当前数据库中。

常用的系统存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13

exec sp_databases; --查看数据库
exec sp_tables; --查看表 
exec sp_columns student;--查看列 
exec sp_helpIndex student;--查看索引 
exec sp_helpConstraint student;--约束 
exec sp_stored_procedures; 
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句 
exec sp_rename studentinfo,student -- 修改表名、索引（index）、列（column）
exec sp_rename 'richinfouser.id','rid','column'
exec sp_renamedb myTempDB, myDB;--更改数据库名称 
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库 
exec sp_helpdb;--数据库帮助，查询数据库信息 
exec sp_helpdb master;

判断存储过程是否存在的两种方式：

if exists(select * from sys.objects where name=‘存储过程名’ )

if (object_id(‘存储过程名’, ‘P’) is not null) --P是指定存储过程类型

无参无返

例子：create proc/procedure 过程名

as

存储过程体

有参无返

create proc proc_find_stu(
@id int – 入参
)
as
select * from students where id=@id; – 存储过程体

无参有返

create proc proc_find_stu1(
@id int output,
@name varchar(20) out
)
as
select @id=2,@name=name from students where id=@id
go

调用

declare
@id int=1,
@name varchar(20)
exec proc_find_stu1 @id output ,@name out
select @id ‘编号’,@name

有参有返

create proc proc_find_stu2(
@id int,
@name varchar(20) out
)
as
select @name=name from students where id=@id
go

调用

declare
@id int=2,
@name varchar(20)
exec proc_find_stu1 @id ,@name out
select @id ‘编号’,@name

sp_helptext是显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。

– with recomplie 不缓存

– with encryption 加密

分页存储过程 SQL server没有limit

分页sql：

select * from

(select *,row_number() over(order by 根据排序列 desc) 行号 from 表名) s

where s.行号 between 起始条 and 终止条;、

declare @page int–第几页
declare @count int–每页显示多少条
set @page = 4
set @count = 5
select top (@count) * from games where 游戏ID not in
(select top ((@page-1)*@count) 游戏ID from games

select top (@条数) * from 表名 where id not in (selecct top (@页数-1)*@条数) id from 表名

动态的执行sql

EXEC sp_executesql @stmt ,@params ,

@stmt = ,–-类似存储过程主体：需要执行的sql

@params = , —类似存储过程参数部分，入参、出参、出入参

—类似存储过程调用

1
2
3
4
5
6
7
8
9
10

例子：

set @sqlName='select @i = COUNT(0) from students';
exec sp_executesql @sqlName,N'@i int output',@i output

@sqlName：需要执行的sql

N'@i int output'：参数，相当于定义时候的参数 N开头意思为后⾯的数据类型为NChar或者NVarchar 使⽤N前缀 

@i output：相当于调用，调用之后就能获取到数据

异常处理–exception

raiserror：返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。

1
2
3
4
5

RAISERROR ( { msg_id | msg_str | @local_variable }    
      { ,severity ,state }    
      [ ,argument [ ,...n ] ] 
     )    
  [ WITH option [ ,...n ] ]

第一个参数：{ msg_id | msg_str | @local_variable }

  msg_id：表示可以是一个sys.messages表中定义的消息代号；  
          使用 sp_addmessage 存储在 sys.messages 目录视图中的用户定义错误消息号。  
          用户定义错误消息的错误号应当大于 50000。

 msg_str：表示也可以是一个用户定义消息，该错误消息最长可以有 2047 个字符；  
         （如果是常量，请使用N'xxxx'，因为是nvarchar的）  
          当指定 msg_str 时，RAISERROR 将引发一个错误号为 50000 的错误消息。

 @local_variable：表示也可以是按照 msg_str 方式的格式化字符串变量。           

第二个参数：severity

       用户定义的与该消息关联的严重级别。（这个很重要）  
        任何用户都可以指定 0 到 18 之间的严重级别。  
       [0,10]的闭区间内，不会跳到catch；  
        如果是[11,19],则跳到catch；  
        如果[20,无穷)，则直接终止 数据库 连接；

第三个参数：state

        如果在多个位置引发相同的用户定义错误，  
        则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。  
        介于 1 至 127 之间的任意整数。（state 默认值为1）  
   当state 值为 0 或 javascript 大于 127 时会生成错误！

第四个参数：argument

        用于代替 msg_str 或对应于 msg_id 的消息中的定义的变量的参数。

第五个参数：option

        错误的自定义选项，可以是下表中的任一值：  
        LOG ：在错误日志和 应用 程序日志中记录错误；  
        NOWAIT：将消息立即发送给 客户 端；  
        SETERROR：将 @@ERROR 值和 ERROR_NUMBER 值设置为 msg_id 或 50000；

select * from sys.messages;

–使⽤sysmessages中定义的消息

raiserror(33003, 16, 1); raiserror(33006, 16, 1);

1
2
3
4
5
6
7
8

BEGIN TRY
print '123'
select 123              -- 可能会发生异常的代码快
select 1/0
END TRY
BEGIN CATCH
raiserror('1不能除以0',16,5)    -- 处理捕获的异常
END CATCH;

备份

• 全备份(完整备份)：创建备份完成时数据库内存在的数据的副本。

• 差异备份：只记录自上次数据库备份后发生更改的数据。差异数据库备份比数据库备份小，而且备份速度快，因此可以更经常地备份，经常备份将减少丢失数据的危险。

• 日志备份：是自上次备份事务日志后对数据库执行的所有事务的一系列记录。可以使用事务日志备份将数据库恢复到特定的即时点（如输入多余数据前的那一点）或恢复到故障点。

• 文件组备份：可以备份和还原数据库中的个别文件。可以只还原已损坏的文件，而不用还原数据库的其余部分，从而加快了恢复速度。

索引

• 聚集索引：聚集索引是索引结构和数据一起存放的索引。类似于字典的正文，当我们根据拼音直接就能找到那个字。

• 非聚集索引：非聚集索引是索引结构和数据分开存放的索引。类似于根据偏旁部首找字，首先找到该字所在的地址，再根据地址找到这个字的信息。

区别及优缺点

区别：

1. 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个

2. 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续

3. 聚集索引:物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。

4. 非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。

5. 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

优势与缺点：

聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。

索引优化策略：

1. 为经常用于查询条件的列创建索引。

2. 对于频繁需要排序的列，可以创建索引以加速排序操作。

3. 为经常需要更新的表创建索引时，考虑索引维护的代价。

4. 对于有大量数据更新的表，避免在频繁更新的列上创建索引。

5. 对于包含多个列的查询条件，创建复合索引，确保索引的列顺序与查询条件匹配。

6. 使用特定的索引类型，如全文索引，适用于文本搜索。

1
2
3
4
5
6
7
8
9
10
11
12

-- 确保数据库已启用全文搜索功能
EXEC sp_fulltext_database 'enable';
 -- 创建一个全文目录
EXEC sp_fulltext_catalog 'MyFullTextCatalog', 'create';
 -- 为目标表启用全文索引支持
EXEC sp_fulltext_table 'MyTable', 'create', 'MyFullTextCatalog';
 -- 为表中的列指定全文索引列
EXEC sp_fulltext_column 'MyTable', 'MyColumn', 'add';
 -- 为全文目录填充改变
EXEC sp_fulltext_table 'MyTable', 'activate';
 -- 启动填充
EXEC sp_fulltext_catalog 'MyFullTextCatalog', 'start_full';

1
2
3
4
5

创建索引
create [unique|clustered|nonclustered] index 索引名 on 表(列,...)

删除索引
drop index 表.索引名

备份

1
2
3
4
5
6
7
8
9

完全备份（Full backup）：此备份类型包括整个数据库的所有数据和对象，可用于还原整个数据库到备份时的状态。完全备份需要较多的时间和存储空间。

差异备份（Differential backup）：差异备份只备份自上次完全备份或差异备份以来发生更改的数据和对象，可以更快地还原数据库到差异备份时的状态。

日志备份（Log backup）：日志备份仅备份自上次日志备份以来的事务日志，可以用于恢复到某个时间点或事务结束时的状态。

文件/文件组备份（File/Filegroup backup）：此备份类型是针对数据库的一部分，而不是整个数据库。可以对数据库中的一个或多个文件组进行备份，而不影响其他文件组。

部分备份（Partial backup）：部分备份是备份整个数据库的一部分，包括一组文件或一个文件组。这种备份类型用于在运行整个数据库的完全备份时，备份较大的数据库的子集。

mysql和sqlserver的区别

1. 价格：MySQL是免费软件，而SQL Server需要购买授权，因此在使用成本上有所不同。

2. 性能与扩展性：

• SQL Server通常被认为是更适合大型企业级应用程序的选择，支持更大的数据量和更高的并发性能，可以更好地处理复杂的查询。

• MySQL则更适合中小型应用程序，尤其是Web应用和小型企业使用，拥有更好的扩展性和灵活性。

1. 安全性：

• SQL Server提供了丰富的安全功能，包括高级的权限管理、加密和审计，对企业级应用程序的安全需求提供了良好的支持。3

• MySQL的安全功能相对较少，但对于一般的应用程序来说已经足够。不过，也有观点认为MySQL的安全性相对较弱，需要额外注意数据备份和安全措施。

1. 功能差异：

• SQL Server对存储过程的支持更加完善，支持多种语言编写。

• MySQL则支持多种存储引擎，给开发人员提供了很大的灵活性。

1. 平台支持：

• SQL Server是由微软公司开发的，主要适用于Windows操作系统。

• MySQL则适用于多个操作系统，包括Windows、Linux和UNIX。

1. 管理复杂性：

• SQL Server的管理相对简单，可以由普通管理员进行管理。

• MySQL的管理比较复杂，可能需要专业的DBA进行管理。

综上所述，MySQL和SQL Server各有优劣，选择哪个数据库软件主要取决于具体的应用场景和需求。例如，对于大型企业级应用，SQL Server可能更合适；而对于中小型网站和Web应用开发，MySQL可能更为适合。