sqlserver技术知识
SSMS
常见的数据库模型：【数据库管理系统中数据的存储结构】
层次模型
⽹状模型
关系模型【SSMS 使⽤】
1.sqlserver的分离与附加【移动数据库的时候使⽤】
分离：删除链接，可以移动数据库的mdf⽂件
附加：选中mdf⽂件添加，还原数据库
sqlserver数据库的备份与还原
2.数据库创建的时候需要创建两种数据⽂件：
数据⽂件：
主数据⽂件：*.mdf 有且只有⼀个
次数据⽂件：*.ndf 有0~N个【保证数据库不受硬盘容量⼤⼩的限制，⽐如：主数据容量100G，数据庞⼤，添加次数据容量，保证数据正常存储】
⽇志⽂件:
*.ldf 必须有⼀个或者多个
3.数据类型
⼩数类型numeric(15,2)：表⽰整数13位，⼩数部分2位
bit : 0~1
*  varchar(10)：变长字符串：最多存储10位，多于10个截取存储10位，存储5个汉字，10个字母，牺牲性能，节省空间  最长8000 char(10)：固定长度字符串，多余的部分空格补充：浪费空间，提⾼性能    最长8000
*  nvarchar(10) ：双字节结构存储每个字符，存储10个汉字，20个字母
⼤⽂本 text类型
4.sql语句
SELECT TOP 3 * FROM STUDENT WHERE ID='1' ORDER BY NAME6664
插⼊多⾏数据：
⽅法⼀：将现有表中的数据添加到已有存在的表
INSERT  INTO 表名  (列名)  SELECT(源表名对应的类名) FROM 源表名  【不选择列名，全部添加】
⽅法⼆：
SELECT(列名) INTO 表名 FROM 源表名
⽅法三:
通过UNION 关键字，合并数据进⾏插⼊【插⼊多⾏数据】
INSERT INTO 表名 （列名）
SELECT (对应列数据) UNION
SELECT (对应列数据) UNION
SELECT (对应列数据) UNION
SELECT (对应列数据)
4.sqlserver数据的导⼊和导出
右键数据库，任务，选择执⾏数据的导⼊【注意修改表】
导出导⼊数据出现错误：未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序
错误的原因：
这是由于该计算机上没有安装Microsoft Access Database Engine组件，该组件帮助在现有的Microsoft Office⽂件与其他数据源之间传输数据
解决办法:
下载安装：www.microsoft/downloads/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-
84b371ede16d&displayLang=zh-cn
5.sqlserver函数
1.字符串函数
CHARINDEX 寻⼀个指定字符串在另⼀个字符串中的起始位置
SELECT CHARINDEX('JBN','MY JBN COURSE ') 返回4
LEN 返回字符串的长度
SELECT LEN('AAAA') 返回4
UPPER 传递的字符串变成⼤写
SELECT UPPER('aaa课程')  返回AAA课程
LOWER 转换变为⼩写
SELECT LOWER('AAAA') 返回aaaa
LTRIM 和 RTRIM 字符串左右去空格【左右有空格，嵌套使⽤】
SELECT RTRIM('  aaa课程')  返回aaa课程
RIGHT 从字符串的右边开始返回制定数⽬的字符
SELECT RIGHT('ABCDEF',3) 返回DEF
REPLACE 替换⼀个字符串中的字符
SELECT REPLACE('ABCDE','C','1')  返回 AB1DE
STUFF 在⼀个字符串中删除制定长度的字符串，并在该位置插⼊⼀个新的字符串
SELECT STUFF('ABCDEF',2,3,'123')【从第2个开始，删除3个，插⼊123】 返回 A123EF
CAST(变量  AS  NVARCHAR(30))
SELECT CONVERT(NVARCHAR(20),GETDATE(),102)  AS DATETIME --当前的系统时间按照⼀定的格式转变
SELECT CONVERT(DATETIME,'2017-08-28 09:09:09') AS DATETIME  --字符串类型转换成⽇期类型
SUBSTRING
SELECT SUBSTRING('123456',1,4) AS TITLE  返回1234
SELECT SUBSTRING('123456',0,4) AS TITLE  返回123
2.⽇期函数
GETDATE 得到当前系统的⽇期
SELECT GETDATE() 返回 今天的⽇期
select YEAR(GETDATE()) as  mytime
select month(GETDATE()) as  mytime
select day(GETDATE()) as  mytime
DATEADD  将指定的数值添加到指定的⽇期部分后的⽇期
SELECT DATEADD(mm,4,'01/01/2009')【⽉份加4】 返回2009-05-01 00:00:00:000
DATEDIFF 返回⽇期之间指定⽇期部分的间隔
SELECT DATEDIFF(mm,'01/01/2009','05/01/2009')  返回4
DATENAME ⽇期中指定⽇期部分的字符串形式
SELECT DATENAME(dw,'01/01/2009')【dw表⽰weekday】 返回 星期四
SELECT DATENAME(ww,'01/01/2009')【ww表⽰week】[本周是⼀年中的第⼏周 ]返回 1 SELECT DATENAME(yy,'01/01/2009')【返回指定时间所在的年份】 返回 2009 SELECT DATENAME(m,'01/01/2009') 返回 1
SELECT DATENAME(d,'01/01/2009') 返回 1
SELECT DATENAME(dy,'01/01/2009') 返回⼀年中的第⼏天 1
DATEPART ⽇期中指定⽇期部分的整数形式
SELECT DATEPART(day,'01/15/2009')【day：天 month:⽉ year：年 】 返回15
3.数学函数：对数值进⾏代数运算
RAND 返回从0到1之间的随机数
SELECT RAND() 返回0.331898004481053
ABS 取表达式的绝对值
SELECT ABS(-43) 返回43
CEILING 取⼤于或等于指定值、表达式的最⼩整数
SELECT CEILING(44.3)  返回44
FLOOR  取⼩于或等于指定值、表达式的最⼤整数
SELECT FLOOR(44.3)  返回43
POWER 取数值表达式的幂值
SELECT POWER(5,2) 返回25
ROUND 将数值表达式四舍五⼊为指定精度
SELECT ROUND(43.543,1) 【1表⽰⼩数部分保留1位】返回43.500
SIGN 对于正数返回1，负数返回-1，0返回0
SELECT SIGN(-21) 返回-1
SQRT 取浮点表达式的平⽅根
SELECT SQRT(9) 返回3
4.系统函数：获取sqlserver 中对象和设置的系统信息【DBO是每个数据库的默认⽤户】 CONVERT 转换数据类型
SELECT CONVERT(VARCHAR(50),12345) 返回'123456'
CURRENT_USER 返回当前⽤户的名字
SELECT CURRENT_USER 返回登陆的⽤户名【dbo】
DATALENGTH 返回⽤于指定字符串的字节数
SELECT DATALENGTH('中国') 返回4
HOST_NAME 当前⽤户登陆的计算机名字
SELECT HOST_NAME() 返回 DESKTOP-T6R4T1T
SYSTEM_USER 当前登陆SSMS的⽤户名称
SELECT SYSTEM_USER 返回sa
USER_NAME 从给定的⽤户ID返回⽤户名
SELECT USER_NAME(1) 返回 从任意数据库中返回’dbo‘
5.聚合函数【使⽤聚合函数不能和查询列⼀起使⽤，使⽤的话就要使⽤group by】【返回⼀个值】
SUM 求和
SELECT SUM(SCORE) FROM COURSE WHERE ID='9'
注意：
使⽤sum函数的时候，sql server 将忽略null，计算的时候不计算空值
使⽤sum函数，sql server 将集合中的smallint等当作int处理
AVG 求取平均分
SELECT AVG(GRADE) FROM STUDENT
MAX MIN 求最⼤值，最⼩值
SELECT MAX(SCORE) AS MAXS,MIN(SCORE) AS MINS FROM SCORE WHERE GRADE>=60
COUNT()，求列数
SELECT COUNT(GRADE) FROM STUDENT
SELECT COUNT( DISTINCT GRADE) FROM STUDESNT
6.模糊查询
LIKE BETWEEN IN
SELECT * FROM STUDENT WHERE NAME LIKE  '%明%'
SELECT * FROM STUDENT WHERE NO BETWEEN 60 AND 100
SELECT * FROM STUDENT WHERE ADDRESS IN ('北京','上海','天津')
7.分组查询 group by【以指定的列为组】【注意：使⽤聚合函数和其他列组合的话，就需要使⽤分组】
select count(address) as c, address as addre from Table1
group by address
分组筛选:[对分组查询出来的结果再次进⾏筛选]
select count(address) as c, address as addre from Table1
group by address
having count(*)>2
8.链接查询
1.内链接查询 INNER JOIN：做笛卡⼉积，筛选符合条件的
SELECT S.SNAME FROM STUDENT S
INNER JOIN COURSE C
ON S.NO=C.SNO
2.外链接查【在内链接的基础上进⼀步完善】做内链接的时候可能on条件的时候没有匹配的，就会某⼀条的数据不显⽰，外链接会显⽰出来如：
name  grade  score
曲灵风  NULL NULL
主表（左表）【主表的数据⼀定显⽰】
左外连接查询 LEFT JOIN
主表（右表）【主表的数据⼀定显⽰】
右外连接查询 RIGHT JOIN
事务：
保证逻辑数据的⼀致性和可恢复性必不可少的利器
锁：
多⽤户访问同⼀数据资源时，对访问的先后次序权限管理的⼀种机制，没有锁事务将⼀塌糊涂，不能保证数据的安全正确读写
死锁:
不同事务之间抢占数据资源造成的
sql server常见的三种事务sqlserver备份表语句
1.⾃动提交事务：是SQL Server默认的⼀种事务模式，每条Sql语句都被看成⼀个事务进⾏处理
2.显式事务：T-sql标明，由Begin Transaction开启事务开始，由Commit Transaction 提交事务、Rollback Transaction 回滚事务结束显式事务的应⽤：
常⽤语句就四个。
Begin Transaction：标记事务开始。
Commit Transaction：事务已经成功执⾏，数据已经处理妥当。
Rollback Transaction：数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
Save Transaction：事务内部设置的保存点，就是事务可以不全部回滚，只回滚到这⾥，保证事务内部不出错的前提下【出异常的时候会还原到保存点】。
begin tran
--捕捉错误机制 try catch
begin try
--语句正确
insert into Student(id,name,address)  values(11,'11','11')
--加⼊保存点
save tran savePoint_name
--id是int 出错
insert into Student(id,name,address)  values('22','22','22')
--语句正确
insert into Student(id,name,address)  values(33,'33','33')
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  savePoint_name---由于出错，这⾥回滚到开始，第⼀条语句也没有插⼊成功。
end catch
if(@@TRANCOUNT>0)
commit tran --如果成功Student表中，将会有3条数据。
----------------------------游标⽆事务循环
declare @name nvarchar(20),@address nvarchar(20)
declare cur_stu cursor for ---把查询出来的结果集放在游标中
select name,address from Student
-
-for read only  只读游标
--for update of name  --定义只更新name的游标
declare @stu cursor  --把上⾯定义的游标存放在游标变量⾥

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。