sqlserver笔记

表结构：

一，字段类型
sqlserver jdbc java

char char String
nchar nchar String
varchar varchar String
nvarchar nvarchar String
varchar(max) longvarchar String
nvarchar(max) longnvarchar String

bit bit boolean

int integer int
bigint bigint long
smallint SMALLINT short
tinyint tinyint short
float double double
real REAL float

data data java.sql.Date
datatime TIMESTAMP java.sql.Timestamp
smalldatetime TIMESTAMP java.sql.Timestamp
datetime2 TIMESTAMP java.sql.Timestamp
timestamp binary byte

binary binary byte
text longvarbinary String
image longvarbinary byte

decimal decimal java.math.bigdecimal
numeric NUMERIC java.math.BigDecimal

money decimal java.math.bigdecimal
smallmoney decimal java.math.bigdecimal

二，字段类型详解
整型：
1.bit 整型
bit数据类型是整型，其值只能是0,1或空值。适合存储只有两种可能值的数据。非常节省空间
2.tinyint 整型
存储0到255之间的整数，存储有限数目的数值时很有用，在数据库中占用一个字节
3.smallint 整型
可以存储 -2的15次幂(-32768)到2的15次幂(32767)之间的整数。占用两个字节
4.int 整型
可以存储从 -2的31次幂(-2147483648)到2的31次幂 (2147483 647)之间的整数。
存储到数据库的几乎所有数值型的数据都可以用这种数据类型，在数据库里占用4个字节
近似数值型：
1.float 近似数值型
float 数据类型是一种近似数值类型，供浮点数使用。说浮点数是近似的，是因为在其范围内不是所有的数都能精确表示。浮点数可以是从-1.79E+308到1.79E+308 之间的任意数
2.real 近似数值型
real 数据类型像浮点数一样，是近似数值类型。它可以表示数值在-3.40E+38到3.40E+38之间的浮点数

二进制型：

1.binary 二进制数据类型
binary数据类型用来存储可达8000 字节长的定长的二进制数据。当输入表的内容接近相同的长度时，你应该使用这种数据类型.
2.varbinary 二进制数据类型
varbinary 数据类型用来存储可达8000 字节长的变长的二进制数据。当输入表的内容大小可变时，你应该使用这种数据类型
3.image 二进制数据类型
image 数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节

字符型：
1.char 字符型
char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，你必须指定列长。当你总能知道要存储的数据的长度时，此数据类型很有用。例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。此数据类型的列宽最大为8000 个字符.
2.varchar (n|max) 字符型
varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。与char 型不一样，此数据类型为变长。当定义一列为该数据类型时，你要指定该列的最大长度。它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度。n为存储字符的最大长度，其取值范围是1~8000.
3.nchar 统一编码字符型
nchar 数据类型用来存储定长统一编码字符型数据。统一编码用双字节结构来存储每个字符，而不是用单字节(普通文本中的情况)。它允许大量的扩展字符。此数据类型能存储4000种字符，使用的字节空间上增加了一倍.
4.nvarchar 统一编码字符型
nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍.
5.text 字符型
text 数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有231-1或20亿个字符.

日期时间型：
1.datetime 日期时间型
datetime数据类型用来表示日期和时间。这种数据类型存储从1753年1月1日到9999年12月31日间所有的日期和时间数据，精确到三百分之一秒或3.33毫秒 .
2.Smalldatetime 日期时间型
smalldatetime 数据类型用来表示从1900年1月1日到2079年6月6日间的日期和时间，精确到一分钟

特殊数据型：
1.timestamp 特殊数据型
timestamp 数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一数码。一个表中只能有一个timestamp列。每次插入或修改一行时，timestamp列的值都会改变。尽管它的名字中有“time”，但timestamp列不是人们可识别的日期。在一个数据库里，timestamp值是唯一的

精确数值类型
1.decimal 精确数值型
decimal 数据类型能用来存储从-10的38次幂-1到10的38次幂-1的固定精度和范围的数值型数据。使用这种数据类型时，必须指定范围和精度。范围是小数点左右所能存储的数字的总位数。精度是小数点右边存储的数字的位数
2.numeric 精确数值型
numeric数据类型与decimal 相似。

三，相关博客
https://blog.csdn.net/qq_34721505/article/details/72954234
https://www.cnblogs.com/cunkouzh/p/5504052.html

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sqlserver-sql语句的优化分析

一，sql性能低的原因
1.网速不好，不稳定
2.服务器内存不够，或sql被分配的内存不够
3.sql语句设计不合理
4.没有相应的索引，索引不合理
5.没有有效的索引视图
6.表数据量过大，没有有效的分区设计
7.数据库设计不好，存在大量冗余数据
8.索引列上缺少相应的统计信息，或者统计信息过期
二，分析比较执行时间计划读取情况
1.查看执行时间和cpu占用时间
set statistics time on
select * from dbo.Product
set statistics time off
2.查看查询对I/O的操作情况
set statistics io on
select * from dbo.Product
set statistics io off
3.

sqlserver-Tsql

一，SQL Server用于操作数据库的编程语言为Transaction-SQL，简称T-SQL.
T-SQL包括以下4个部分：
1.DDL：定义和管理数据库及其对象，例如create、alter和drop等。
2.DML：实现对数据库表各对象的操作，例如insert、update等。
3.DCL：数据控制语言，实现对数据库进行安全管理和权限管理等控制，例如grant、revoke、deny等。
4.附加的语言元素。T-SQL的附加语言元素，包括变量、运算符、函数、注释和流程控制语句等
二，编程基础
1.标识符
(1).规则标识符
由字母、数字、下划线、@、#、$符号组成，其中字母可以是a-z或A-Z，也可以是来自其他语言的字母字符。
(2).界定标识符
对于不符合标识符规则的标识符，则要使用界定符方括号([])或双引号(“”)将标识符括起来。如标识符[My Table]、“select”内分别使用了空格和保留字select。
2.数据类型
(1).系统数据类型
精确数字数据类型
int
bigint
smallint
tinyint
decimal/numeric
近似数字数据类型
float
real
货币数据类型
money
smallmoney
字符数据类型
char
nchar
varchar
nvarchar
日期和时间数据类型
date
time
datetime
datetime2
smalldatetime
二进制数据类型
binary
varbinary
专用数据类型
bit
uniqueidentifier 用于存储16位全局唯一标识符(UUID)。
xml
(2)程序中的数据类型
cursor
table
sql_varint
3.表达式
(1).变量
局部变量：局部变量由用户定义，一般出现在批处理、存储过程和触发器中，其作用范围仅在程序内部。

声明变量：declare @变量1 [as] datatype,@变量2 [as] datatype...
局部变量名称必须以@开始开头
as可以省略
赋初值NULL
局部变量赋值，三种：
declare @变量1 [as] datatype = value,@变量2 [as] datatype = value...
select @变量1 = 表达式1,@变量2 = 表达式2...
set @变量 = 表达式

全局变量：全局变量由SQL Server系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值。可以通过访问全局变量来了解系统目前的一些状态信息。
全局变量名以@@开头。下面给出一些常用的全局变量。

全局变量说明

@@error 上一条SQL语句报告的错误号

@@nestlevel 当前存储过程或触发器的嵌套级别

@@rowcount 上一条SQL语句处理的行数

@@servername 本地服务器名称

@@identity 最后插入的标识值

@@spid 当前用户进程的会话id

@@fetch_status 上一条游标fetch语句的状态

@@cpu_busy SQL Server自上次启动后的时间状态
2.函数
(1).数学函数
abs() :绝对值
round
power(m,m)：m的n次幂
trunc():将数字截断到指定的位数
%:求余数， sqlserver没有mod(m,n)，而是用m%n代替
(2).字符串函数
substring(str,position,length):求子字符串
replace(str,search_str,rep_str):替换一个字符串的子串
left(str,n):返回字符串从左边开始的指定个数的字符
len():求字符串长度
(3).日期和时间函数
getdate()：返回当前的日期和时间
year(日期)：返回指定日期的“年”部分的整数
month(日期)：返回指定日期的“月”部分的整数
day(日期)：返回指定日期的“日”部分的整数
datepart(日期元素，日期)：返回日期元素指定的日期部分的整数

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

datediff(日期元素，日期1，日期2)：返回两个日期间的差值并将其转换为指定日期元素的形式
dateadd(日期元素，数值，日期) ：按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期
(4).数据类型转换函数
格式：convert(数据类型(长度),表达式[,n])
convert(char(7),sal)

sqlserver-查询语句

一，字符串匹配
1._（下划线）匹配任意一个字符

2.%（百分号）匹配0个或多个字符
SELECT * FROM Student WHERE Sname LIKE '张%'
3.[] 匹配[]中的任意一个字符。如[abcd]表示匹配abcd其中任何一个，若是连续的，可以用 - 表示，如[a-d]

SELECT * FROM Student WHERE Sname LIKE '[张李]%'//（查询姓“张”、“李”的学生详细信息）
4.[^] 不匹配[]中的任意一个字符。如[^abcd]表示不匹配abcd其中任何一个，若是连续的，可以用 - 表示，如[^a-d]
SELECT * FROM Student WHERE Sname LIKE '_[小大]%' //（查询名字的第二个字为“小” 或 “大”的学生详细信息）
二，多表连接查询
1.自连接
from table1 as t1 join table2 as t2 on
2.内连接
使用内连接时，如果两个表的相关字段满足条件，则从两个表中提取数据组成新的记录
from table1 join table2 on
和普通的where方法一样：
select * from table1,table2 where table1.id = table2.sid
3.外连接
外连接只限制一张表中的数据必须满足条件，而另一张表的数据可以不满足条件
from table1 left|right join table2 on

4.并集运算：union和union all
select * StuMark where Score >=90 union select * StuMark where StuId=3
5.交集运算：intersect
select * StuMark where Score >=90 intersect select * StuMark where StuId=3
6.差集(减集)运算：except
select * StuMark where Score >=90 except select * StuMark where StuId=3

三，使用top限制结果集行数
1.top(expression) [percent] [with ties]

2.expression：指定返回行数的数值表达式。如果指定了PERCENT，expression将隐式转换成float，否则是bigint
3.PERCENT：指定只返回结果集中前 expression% 行数据。
4.SELECT top 10 PERCENT * FROM [dbo].[nps_source_analysis]; //查询前10%的数据
5.WITH TIES：指定从基本结果集中返回额外的数据行（只有在SELECT子句中包含了ORDER BY子句时，才能使用）。
（查询考试成绩最高的3个成绩。列出学号、课程号、成绩）
SELECT TOP 3 Sno,Cno,Grade FROM SC ORDER BY Grade DESC
若要包括并列第3名的成绩：
SELECT TOP 3 Sno,Cno,Grade WITH TIES FROM SC ORDER BY Grade DESC

注意：
TOP谓词写在SELECT单词的后面（如果有DISTINCT，则在DISTINCT后面）。

四，DML语句
1，————-select…into.. 插入多行数据————————–
—-将表stuinfo表中，女生的信息插入到stuinf2中
SELECT * INTO StuInfo2 from StuInfo where StuSex=’女’
2.————-insert into… select 一次插入多行数据————————–
INSERT INTO StuInfo2(StuName,StuSex) Select StuName,StuSex FROM StuInfo

五，子查询
1.将查询的结果作为子表，连表查询
select * from StuInfo s1,(select * from StuMarks where Score >80)s2 where s1.StuID=s2.StuID AND s1.StuName=’李四’
2.将查询结果作为条件使用
select * from StuInfo WHERE StuID>(select StuID from StuInfo where StuName=’王五’)
3.将子查询作为临时表使用
select s1.,s2 Score from StuInfo s1 left outer join (select from StuMarks where Subject=’HTML’) s2 on s1.StuID=s2.StuID
4.使用in和not in 完成子查询
select StuName from StuInfo where StuID In (select StuID from StuMarks where Score >85 and Subject=’java’)

sqlserver-查询执行顺序

一.实例准备：
用户表：订单表
id,name,phone id ,status,time,user_id

二.sql查询语句
select top(4) status , max(u.id) as maxMemberID
from user as u right outer join order as o
on u.id=o.user_id
where u.id>0
group by status
having status>=0
order by maxMemberID asc

三.语句分析
执行过程中也会相应的产生多个虚拟表（下面会有提到），以配合最终的正确查询
1.从from开始
(1)加载左表 : from user as m
查询结果 : user 表中的所有数据
(2).这里应该是 right outer join ，但是这里在sql中被定义分解为2个步骤，即join ，right outer join 。表达式关键字从左到右，依次执行。
查询结果：两个表的笛卡尔集合，存入虚拟表vt1
(3).on筛选器：on m.id = o.user_id
查询结果：从上一步的笛卡尔集合中删除掉不匹配的行，存入虚拟表vt2
(4).添加外部行(outer row) : right outer join order as o
查询结果：右表(order)作为保留表，把剩余的数据重新添加到上一步的虚拟表vt2中，生成虚拟表vt3
2.进入where阶段 : where m.id > 0
查询结果：把筛选的条件为true的结果集，存入虚拟表vt4。
注意：where的筛选删除为永久的，而on的筛选删除为暂时的，因为on筛选过后，有可能会经过outer添加外部行，重新把数据加载回来，而
where则不能
3.group by分组 : group by status
查询结果:存入vt2,以status列的数值开始分组，即status列，值一样的分为一组，
4.having筛选器：having status > 0
查询结果：筛选分好组的组数据，把不满足条件的删除掉
5.select查询挑拣计算列：
(1).计算表达式：select status,max(m.id)
查询结果：从分过组的数据中计算各个组中的最大 m.id,列出要筛选显示的列
(2).distinct过滤重复
(3).top结合order by 筛选多少行，
6.order by 排序显示

sqlserver-事务与锁

1.显示事务

---开启事务
begin tran
--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
begin try
--语句正确
insert into user_test(age,versionName) values (24,'小a')
--Numb为int类型，出错
insert into user_test(age,versionName) values ('aa','小b')
--语句正确
insert into user_test(age,versionName) values (1,'小c')
end try
begin catch
select Error_number() as ErrorNumber, --错误代码
Error_severity() as ErrorSeverity, --错误严重级别，级别小于10 try catch 捕获不到
Error_state() as ErrorState , --错误状态码
Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
Error_line() as ErrorLine, --发生错误的行号
Error_message() as ErrorMessage --错误的具体信息
if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
rollback tran ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
end catch
if(@@trancount>0)
commit tran --如果成功Lives表中，将会有3条数据。

--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
select * from user_test

2.set xact_abort

set xact_abort off
begin tran
--语句正确
insert into user_test(age,versionName) values (24,'小a')
--Numb为int类型，出错
insert into user_test(age,versionName) values (112232424242525252522,'小b')
--语句正确
insert into user_test(age,versionName) values (1,'小c')
commit tran
select * from user_test

3.事务把死锁给整出来啦
打开两个查询窗口，把下面的语句，分别放入2个查询窗口，在5秒内运行2个事务模块。
begin tran
update user_test set versionName='羽毛球'
waitfor delay '0:0:5'
update order_test set product='老虎'
commit tran

begin tran
update order_test set product='老虎'
waitfor delay '0:0:5' --等待5秒执行下面的语句
update user_test set versionName='羽毛球'
commit tran
select * from user_test
select * from order_test

[Err] 40001 - [SQL Server]事务(进程 ID 67)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

sqlserver-游标

参数说明：

cursor_name：游标名称。
Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
[Local | Global]:默认为local。
Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only
Static:静态游标
KeySet：键集游标
Dynamic：动态游标，不支持Absolute提取选项
Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
Read_Only:不能通过游标对数据进行删改。
Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
For Update[of column_name ,....] :定义游标中可更新的列。

2.声明一个动态游标
declare nps_cursor cursor scroll
for select * from nps_source_analysis

3.打开游标
--打开游标语法
open [ Global ] cursor_name | cursor_variable_name
cursor_name:游标名，cursor_variable_name：游标变量名称，该变量引用了一个游标。
--打开游标
open orderNum_02_cursor

参数说明：
Frist：结果集的第一行
Prior：当前位置的上一行
Next：当前位置的下一行
Last：最后一行
Absoute n：从游标的第一行开始数，第n行。
Relative n：从当前位置数，第n行。
Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。
--例子：
fetch first from nps_cursor //第一行
5.@@Fetch_Status
通过检测全局变量@@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。
这个状态值可以帮你判断提取数据的成功与否。
--例子：
while @@Fetch_Status=0 --提取成功，进行下一条数据的提取操作
begin
SELECT '1' --打印数据（处理逻辑）
fetch next from nps_cursor --移动游标
end

6.利用游标更新删除数据
--游标修改当前数据语法
Update 基表名 Set 列名=值[,...] Where Current of 游标名
--例子：
Update nps_source_analysis Set product= 'HIMA' Where Current of nps_cursor --修改当前行
--游标删除当前数据语法
Delete 基表名 Where Current of 游标名
--例子：
Delete from nps_source_analysis Where Current of nps_cursor --删除当前行

7.关闭游标
游标打开后，服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集，同时使用游标也会对某些数据进行封锁。所以游标一旦用过，应及时关闭，避免服务器资源浪费。
--关闭游标语法
close [ Global ] cursor_name | cursor_variable_name
--关闭游标
close nps_cursor
关闭后如果再操作游标会报错：游标未打开。
8.删除游标
删除游标，释放资源
--释放游标语法
deallocate [ Global ] cursor_name | cursor_variable_name
--释放游标
deallocate nps_cursor

sql语句执行时间

[SQL]set statistics time on
SELECT * FROM [dbo].[nps_analysis_devide]
SQL Server 执行时间:
CPU 时间 = 530 毫秒，占用时间 = 31180 毫秒。

受影响的行: 0
时间: 31.913s

[SQL]
set statistics time off

SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

受影响的行: 0
时间: 0.007s

1.cpu

[SQL]set statistics time on
SELECT * FROM [dbo].[nps_analysis_devide]
SQL Server 执行时间:
CPU 时间 = 530 毫秒，占用时间 = 31180 毫秒。

受影响的行: 0
时间: 31.913s

[SQL]
set statistics time off

SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

受影响的行: 0
时间: 0.007s

2.io

[SQL]-- set statistics time on
-- SELECT * FROM [dbo].[nps_analysis_devide];
-- set statistics time off

set statistics io on
SELECT * FROM [dbo].[nps_analysis_devide]
表 'nps_analysis_devide'。扫描计数 1，逻辑读取 10665 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

受影响的行: 0
时间: 32.058s

[SQL]
set statistics io off

受影响的行: 0
时间: 0.002s