本文详细介绍了SQLServer的连接方式，包括Windows身份认证和SQLServer身份认证，以及各种数据类型，如整数、浮点型、货币型、位型、字符串型、Unicode字符型、文本型、二进制型和日期时间类型。此外，还讲解了查询语句的使用、索引、视图、T-SQL语言、存储过程、触发器和备份恢复等高级特性。
抄录修改自：学习SQL Server这一篇就够了_轻松的小希的博客-CSDN博客_sqlserver安装程序无法通过windows update
SqlServer
连接方式
1、Windows 身份认证（必须在安装着SqlServer的计算机上，不需要用户名和密码）
2、SQL Server 身份认证（需要使用用户名和密码）
数据类型
整数
| 类型 | 范围 |
|---|---|
| bigint | 大整数，数范围为-263（-9223372036854775808）～263-1（9223372036854775807），长度为8字节。 |
| int | 整数，数范围为-231（-2147483648）～231-1（2147483647），长度为4字节。 |
| smallint | 短整数，数范围为-215（-32768）～215-1（32767），长度为2字节。 |
| tinyint | 微短整数，数范围为 0～255，长度为1字节。 |
精确数值型
分为：1、decimal ；2、numeric 。他们可存储从-1038+1到1038-1的数字数据。它们的存储长度随精度（位数）变化而变化，最少为5字节，最多为17字节：
-
精度为1～9时，存储字节长度为5。
-
精度为10～19时，存储字节长度为9。
-
精度为20～28时，存储字节长度为13。
-
精度为29～38时，存储字节长度为17。
浮点型
分为：1、real；2、float[(n)]，两者通常都使用科学计数法表示数据，例如：5.6432E20、-2.98E10、1.287659E-9等。
| 类型 | 描述 |
|---|---|
| real | 使用4字节存储数据，表数范围为-3.40E+38～3.40E+38，数据精度为7位有效数字。 |
| float[(n)] | 表数范围为-1.79E+308～1.79E+308。定义中的n取值范围是1～53，用于指示其精度和存储大小。 |
当n在1～24之间时，实际上是定义了一个real型数据，存储长度为4字节，精度为7位有效数字。当n在25～53之间时，存储长度为8字节，精度为15位有效数字。当缺省n时，代表n在25～53之间。
货币型
专门用于处理货币类型的数据，分为：1、money ；2、smallmoney。它们用十进制数表示货币值。
| 类型 | 描述 |
|---|---|
| money | 数据的数范围为-263（-922337203685477.5808）～263-1（922337203685477.5807），精度为19，小数位数为4，长度为8字节。 |
| smallmoney | 数据的数范围为-231（-214748.3648）～231-1（214748.3647），精度为10，小数位数为4，长度为4字节 |
位型
位型只存储0和1，长度为一个字节。但要注意，SQL Server对表中bit类型列的存储做了优化：如果一个表中有不多于8个的bit列，这些列将作为一个字节存储；如果表中有9到16个bit列，这些列将作为两个字节存储；更多列的情况依次类推。
字符串值TRUE和FALSE可以转换为以下bit 值：TRUE转换为1，FALSE转换为0。
字符型
字符型用于存储字符串，在输入字符串时，需将串中的符号用单引号括起来，如‘abc’。
| 类型 | 描述 |
|---|---|
| char[(n)] | 定长字符数据类型，其中n在1到8000之间，缺省为1。当实际存储的串长度不足n时，则在串的尾部添加空格。 |
| varchar[(n)] | 变长字符数据类型，这里n表示的是字符串可达到的最大长度。 |
Unicode字符型
Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理，包括nchar[(n)]和nvarchar[(n)] 。
Unicode字符型包括两类：nchar是固定长度的数据类型，nvarchar是可变长度的数据类型，二者均使用UNICODE UCS-2字符集。
| 类型 | 描述 |
|---|---|
| nchar[(n)] | n的值在1与4000之间，缺省为1，长度2n字节。若输入的字符串长度不足n，将以空白字符补足。 |
| nvarchar[(n)] | nvarchar[(n)]为最多包含n个字符的可变长度Unicode字符型数据，n的值在1与4000之间，缺省为1。长度是所输入字符个数的两倍。 |
实际上，nchar、nvarchar与char、varchar的使用非常相似，只是字符集不同（前者使用Unicode字符集，后者使用ASCII字符集）。
文本型
文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符。
| 类型 | 描述 |
|---|---|
| text | 类型可以表示最大长度为 231-1（2147483647）个字符，其数据的存储长度为实际字符数个字节。 |
| ntext | 类型可表示最大长度为 230-1（1073741823）个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。 |
二进制型
二进制数据类型表示的是位数据流，包括binary（固定长度）和varbinary（可变长度）两种。
| 类型 | 描述 |
|---|---|
| binary [(n)] | 固定长度的n个字节二进制数据。n取值范围为1到8000，缺省为1。 |
| varbinary [(n)] | n个字节变长二进制数据。n取值范围为1到8000，缺省为1。 |
日期时间类型
日期时间类型包括datetime和smalldatetime两类。
datetime：可表示的日期范围从1753 年1月1日到9999年12月31日的日期和时间数据。
日期部分常用的表示格式如下：
时间部分常用的表示格式如下：
smalldatetime：可表示从1900年1月1日到2079年6月6日的日期和时间，数据精确到分钟。
时间戳类型
每当对该表加入新行或修改已有行时，都由系统自动修改该列的值，将原来的时间戳值加上一个增量，最后增加或修改的列该值最大。
timestamp 列可反映系统对该记录修改的相对顺序。一个表只能有一个timestamp 列。timestamp类型数据的长度为8字节。
图像型
图像型是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，
介于0与231-1（2147483647）字节之间。
在SQL Server 2005中该类型是为了向下兼容而保留的数据类型，微软推荐用户使用varbinary(MAX)数据类型来替代image类型。
查询语句的大的区别
1、sql的多表查询与mysql、oracle有些区别
语法格式如下：
select t1.*,t2.*,... from 表1 t1, 表2 t2, ... where 连接条件 【and 查询条件】 【group by 分组条件】 【having 过滤条件】 【order by 排序字段 asc|desc】; 案例如下： select x.学号,x.姓名,k.课程名,c.成绩 from XSB x,CJB c,KCB k where x.学号 = c.学号 and c.课程号 = k.课程号; select x.学号,x.姓名,k.课程名,c.成绩 from XSB x,CJB c,KCB k where x.学号 = c.学号 and c.课程号 = k.课程号 and x.学号 = '081101'; select x.学号,x.姓名,k.课程名,c.成绩 from XSB x,CJB c,KCB k where x.学号 = c.学号 and c.课程号 = k.课程号 and x.学号 = '081101' order by c.成绩 asc;
特色功能：限制查询
语法格式如下：
select top 获取前几条 【*/字段名,...】 from 【表名/查询结果集】 【where 查询条件】 【group by 分组条件】 【having 过滤条件】 【order by 排序字段 asc|desc】; 案例如下： #查询前10条数据 select top 10 * from XSB; #查询11~20条数据 select top 10 * from XSB where 学号 not in (select top 10 学号 from XSB);
SqlServer高级语法
索引
1、索引概念
索引是帮助SQL Server高效获取数据的一种有序的数据结。它有以下两种分类：
①、聚簇索引 ：聚簇索引的顺序就是数据的物理存储顺序。每个表只有一个聚簇索引，SQL Server 2005是按B树（BTREE）方式组织聚簇索引的，聚簇索引的叶节点就是数据节点，由于数据记录按聚簇索引键的次序存储，因此查找效率高。除非在创建主键时特别指定，否则创建主键时会自动创建聚簇索引。
②、非聚簇索引 ：非聚簇索引的索引顺序与数据物理顺序无关 。非聚簇索引也是按B树方式组织的，但非聚簇索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。一个表中可有多个非聚集索引，创建索引时，可指定是按升序还是降序。
索引语法
创建索引
create 【unique】 index 索引名称 on 表名(要创建索引的字段1,要创建索引的字段2,...);
重建索引
alter index 索引名称 on 表名 rebuild;
删除索引
drop index 表名.索引名称;
视图
视图是一个虚拟表，其内容由查询定义。但视图并不存储数据，视图数据来自由定义视图的查询所引用的表。
使用视图有下列优点：
1、简单性：如可屏蔽表连接等复杂操作。
2、安全性：简化用户权限的管理，将用户限制在数据的不同子集上。
3、逻辑数据独立性：基本表的改变只需改变视图，而无需修改应用程序。
视图语法
创建视图
create view 视图名称 as 查询语句;
调用视图
select * from 视图名称;
删除视图
drop view 视图名称;
修改视图
先删除，再创建
T-SQL语言
概念
SQL语言的全名是结构化查询语言（Structured Query Language），1986年10月，美国ANSI对 SQL进行规范后，以此作为关系数据库管理系统的标准语言。不同的数据库管理系统对SQL规范做了某些编改和扩充。例如，微软公司的SQL Server支持的是T-SQL，而甲骨文公司的Oracle数据库所使用的SQL语言则是PL-SQL。T-SQL是SQL语言的一种版本，只能用于微软SQL Server以及Sybase Adaptive Server系列数据库。T-SQL除了提供标准的SQL命令之外，还提供了变量说明、流程控制、功能函数等。在SQL Server数据库中，T-SQL语言由DQL、DCL、DDL、 DML 及流控制语句组成。
常量
按类型分为：字符串常量、整型常量、实型常量、日期时间常量、货币常量、唯一标识常量等。
变量
SQL Server中变量可分为两类：全局变量，局部变量。
全局变量 ：全局变量由系统提供且预先声明，以“@@”开头。T-SQL全局变量作为函数引用。例如，@@ERROR返回执行的上一个T-SQL语句的错误号；@@CONNECTIONS返回自上次启动SQL Server以来连接或试图连接的次数。
局部变量 ：局部变量以@开头。例如，保存运算的中间结果，作为循环变量等。
局部变量定义
declare { @变量名 数据类型 }[,...n]
局部变量赋值语句
set语法格式：一个set语句只能给一个变量赋值
set @变量名＝表达式
select语法格式：一个select语句可以给多个变量赋值
select { @变量名＝表达式 }[,...n]
流程控制语句
流程控制语句可以改变计算机的执行顺序，SqlServer的流程控制语句如下图：
| 控制语句 | 说明 |
|---|---|
| BEGIN…END | 语句块 |
| IF...ELSE | 条件语句 |
| CASE | 分支语句 |
| GOTO | 无条件转移语句 |
| WHILE | 循环语句 |
| CONTINUE | 用于重新开始下一次循环 |
| BREAK | 用于退出最内层的循环 |
| RETURN | 无条件返回 |
| WAITFOR | 为语句的执行设置延迟 |
系统内置函数
数学函数
| 函数案例 | 作用 |
|---|---|
| select ABS(-1); | 求绝对值函数 |
| select PI(); | 获取pi的值。 |
| select FLOOR(3.14); | 向下取整。 |
| select CEILING(3.14); | 向上取整。 |
| select ROUND(3.1415,2); | 按小数位数规定的精度四舍五入。 |
| select RAND(); | 产生一个随机数，产生范围是0-1之间的一个小数。 |
| select sqrt(4); | 开平方根。 |
字符串转换函数
| 函数案例 | 作用 |
|---|---|
| select LOWER('Hello World'); | 将字符串全部转为小写。 |
| select UPPER('Hello World'); | 将字符串全部转为大写。 |
| select STR(100); | 把数值型数据转换为字符型数据。 |
| select CHAR(97); | 将ASCII码转换为字符。输入0 ~ 255之间的ASCII 码值，否则返回NULL 。 |
| select ASCII('a'); | 返回字符表达式最左端字符的ASCII码值。 |
字符串处理函数
| 函数案例 | 作用 |
|---|---|
| select LTRIM(' Hello '); | 去掉字符串左部空格。 |
| select RTRIM(' Hello '); | 去掉字符串右部空格。 |
| select LEFT('helloworld',3); | 返回字符串左起的第n个字符。 这个案例输出：hel |
| select RIGHT('helloworld',3); | 返回字符串右起的第n个字符。 |
| select SUBSTRING('helloworld',2,3); | 返回从字符串左边第i个字符起的n个字符的部分。 这个案例输出：ell |
数据类型转换函数
常用的类型转换有：日期型→字符型、字符型→日期型、数值型→字符型、字符型→数值型等。
CAST (表达式 AS 新类型) CONVERT (新类型 ,表达式)
案例如下：
-- 日期转字符型 --
select CAST('2022-09-02' as varchar(30));
select CONVERT(varchar(30),'2022-09-02');
-- 字符转日期型 --
select CAST('2022-09-02' as datetime);
select CONVERT(datetime,'2022-09-02');
-- 数值转字符型 --
select CAST(2022 as varchar(30));
select CONVERT(varchar(30),2022);
-- 字符转数值型 --
select CAST('2022' as int);
select CONVERT(int,'2022');
时间日期函数
getdate()：返回当前系统日期和时间，返回值类型为datetime。
select GETDATE();
year()：返回指定日期的年部分，返回值为整数。
select YEAR(GETDATE());
month()：返回指定日期的月部分，返回值为整数。
select MONTH(GETDATE());
select DAY(GETDATE());
select DAY(GETDATE());
dateiff()：返回两个指定日期在datepart（年、月、日）方面date2超过date1的差距值，其结果值是一个带有正负号的整数值。
select DATEDIFF(DAY,'2022-02-09','2022-09-2'); select DATEDIFF(MONTH,'2022-02-09','2022-09-02'); select DATEDIFF(YEAR,'2019-01-01','2022-01-01');
元数据函数
db_id()：根据数据库名，返回数据库标识（ID）号。
select DB_ID('TEST01');
db_name()：根据数据库ID，返回数据库名。
select DB_NAME(DB_ID('TEST01'));
object_id()：返回数据库中对象的ID号 ，返回值类型为smallint。
select OBJECT_ID('XSB');
用户函数
创建函数语法
create function 函数名称 ( [@参数名 参数类型 [=默认值]],... )
returns 返回值类型
as
begin
函数体
return 表达式;
end;
调用函数
// dbo 这里根据实际情况来看 select dbo.函数名(实参1,实参2,...,实参n);
删除函数
drop function 函数名称;
修改函数
先删除，再新建
简单案例如下：
-- 创建函数 --
create function F_GET_AVG (@学号 char(6)) returns int
as
begin
declare @avg int;
select @avg=(select AVG(成绩) from CJB where 学号=@学号);
return @avg;
end;
-- 调用函数 --
select dbo.F_GET_AVG('081102');
-- 删除函数 --
drop function F_GET_AVG;
触发器
SQL Server触发器分为DML触发器和DDL触发器这两种。
DML触发器，执行INSERT、 DELETE 、UPDATE语句时触发。DML触发器可以实现数据的完整性、多个表间数据的一致性等。比如，实现外键的功能，当向CJB中插入记录时，保证学号是XSB表中已存在的；再如，可通过对XSB表定义DELETE触发器，实现在XSB表中删除一个学生时，同时删除CJB表中所有该学生的记录。
DDL触发器，只由T-SQL语句触发。是SQL Server 2005新增的功能，也是由相应的事件触发，但DDL触发器在执行CREATE、ALTER、DROP等语句时触发。
在触发器中可以使用两个特殊的虚拟表inserted和deleted：
inserted表存放新增的记录
deleted表存放被删除的记录
Update操作时：新的记录存入inserted表，旧的记录存入deleted表。
创建触发器的语法如下：
-- 创建DML触发器（掌握） --
create trigger 触发器名 on 表名/视图名
{ for | after | instead of }
{ insert [,] | update [,] | delete }
as
begin
触发器需要执行的逻辑
end;
-- 创建DDL触发器（了解） --
create trigger 触发器名 on 数据库作用域/服务器作用域
{ for | after }
{ 事件类型 }
as
begin
触发器需要执行的逻辑
end;
-- 事件类型：CREATE_对象类型、DROP_对象类型、ALTER_对象类型 --
-- 对象类型：DATABASE、TABLE、VIEW等 --
删除触发器
-- 删除DML触发器 -- drop trigger 触发器名称; -- 删除DDL触发器 -- drop trigger 触发器名称 on 数据库作用域/服务器作用域;
修改触发器
先删除触发器，再创建修改
简单案例如下：
-- 创建触发器，在删除学生的时候删除关于这个学生的所有成绩 --
CREATE TRIGGER T_XSB_DELETE ON XSB after DELETE AS BEGIN
DELETE
FROM
CJB
WHERE
学号 IN ( SELECT 学号 FROM deleted ) END;
-- 先查询学号为 081101 的学生 --
select * from XSB where 学号 = '081101';
-- 删除这个学生 --
delete from XSB where 学号 = '081101';
-- 在学生表中查询这个学生 空 --
select * from XSB where 学号 = '081101';
-- 在成绩表中查询这学生的成绩 空 --
select * from CJB where 学号 = '081101';
-- 删除触发器 --
drop trigger T_XSB_DELETE;
-- 查看当前数据库总所有的触发器 --
select * from sysobjects where xtype='TR'
-- 查看单个触发器 --
exec sp_helptext '触发器名'
存储过程
存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中。
使用存储过程的优点如下：
1、在数据库服务器中只有首次对存储过程中的命令进行编译，以后直接调用无需编译，加快执行速度。也就是在第一次调用存储过程时对存储过程进行编辑，后续就不用编辑了加快执行速度。
2、存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量sql语句的代码流量。
3、维护性高，更新存储过程通常比更改、测试以及重新部署程序需要较少的时间和精力。
4、自动完成需要预先执行的任务（存储过程可以在SQL Server启动时自动执行）。
常见存储过程的主要分类：
1、系统存储过程。系统预定义的存储过程，可以在任何一个数据库中作为命令执行，系统存储过程定义在系统数据库master中，其前缀是“sp_”。例如，sp_help ----显示系统对象信息。
2、扩展存储过程。是指在SQL Server环境之外，使用编程语言（例如C++语言）创建的外部例程形成的动态链接库（DLL）。例如，EXEC xp_cmdshell ‘dir c:’ ----显示目录信息。
3、用户存储过程。可以使用T-SQL语言编写，也可以使用CLR方式编写。本教程中常说的存储过程一般是指用T-SQL语言编写的存储过程，而使用CLR方式编写的存储过程称为CLR存储过程。CLR存储过程就是使用Microsoft Visual Studio 2005环境下的语言作为脚本编写的、可以对Microsoft .NET Framework公共语言运行时（CLR）方法进行引用的存储过程。
创建存储过程语法
create procedure 存储过程名称 [ { @参数名 数据类型 [varying][=default][ouput] },... ]
as
begin
存储过程需要执行的逻辑
end;
参数解读：
=default：表示为该参数设定的默认值，定义默认值后，不必指定该参数的值即可执行过程。
ouput：表示该参数是输出值。
varying ouput：表示该参数是游标参数。
注：存储过程中不能包含一些特殊语句，如：创建及修改视图、创建及修改函数、创建及修改触发器、创建及修改存储过程、创建DEFAULT、创建SCHEMA、USE 数据库等。
调用存储过程
execute 存储过程名称 { [@参数=]{常量|@变量 [output]|[default]},... };
删除存储过程
drop procedure 存储过程名称;
修改存储过程
先删除再新建，先毁掉再重建
简单案例如下：
-- 创建存储过程：计算指定学号的学生所选课程的平均成绩，要求输入参数为学号，输出参数为平均成绩 --
CREATE PROCEDURE P_GET_AVG @num CHAR ( 6 ),@avgScore FLOAT OUTPUT AS BEGIN
SELECT AVG
( 成绩 )
FROM
CJB
GROUP BY
学号
HAVING
学号=@num END;
-- 调用存储过程 方式一 --
declare @avg float;
execute P_GET_AVG @num='081102',@avgScore=@avg OUTPUT;
-- 方式二 --
declare @avg float;
execute P_GET_AVG '081102',@avg;
-- 删除存储过程 --
drop procedure P_GET_AVG;
-- 查询所有的存储过程，返回存储过程名及定义语句 --
SELECT
name,
definition
FROM
sys.sql_modules AS m
INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id
WHERE
o.[type] = 'P'
SqlServer备份与恢复
数据备份
语法格式：
USE master GO EXEC sp_addumpdevice 'disk', --磁盘 '逻辑名称', --逻辑名 '备份地址' --物理名 backup database 数据库名称 to 逻辑名称 --备份
案例演示：
USE master GO EXEC sp_addumpdevice 'disk', --磁盘 'mybackupfile', --逻辑名 'D:\mybackupfile.bak' --物理名 backup database 学生信息数据库 to mybackupfile --备份
数据恢复
语法格式
restore database 数据库名称 from 逻辑名称 with file=1, REPLACE
案例演示：
restore database 学生信息数据库 from mybackupfile with file=1, REPLACE
注：只有数据库被删除后并且在删除前进行了完全备份，才能使用下边这条命令恢复。
扫一扫
942
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
