sqlserver笔记

CTRL + K + C :注释选中行

安装前需要.net Framwork3.5，安装2008会自动更新安装

1.sqlserver是一个关系型数据库

关系数据库管理系统（Relational Database Management System：RDBMS）是指包括相互联系的逻辑组织和存取这些数据的一套程序 (数据库管理系统软件)。关系数据库管理系统就是管理关系数据库，并将数据逻辑组织的系统。

2.创建数据库

1. 利用图形化界面，在数据库分类右键点击创建即可

2. 使用sql脚本创建数据库

   USE [master]
   GO
   
   /****** Object:  Database [yin]    Script Date: 08/03/2022 18:42:48 ******/
   CREATE DATABASE [yin] ON  PRIMARY  --创建数据库，设置数据库名字
   ( NAME = N'yin', FILENAME  --设置数据库文件存放数据的位置
   = N'D:\gongju\sqlserver\SqlServer2008_MSSQLSERVER\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\yin.mdf' , 
   SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) -- 设置初始大小，最大大小，每次增长大小
    LOG ON 
   ( NAME = N'yin_log',  --设置数据库的日志文件
   FILENAME --日志文件的存放位置
   = N'D:\gongju\sqlserver\SqlServer2008_MSSQLSERVER\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\yin_log.ldf' ,
    SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) --日志文件的初始大小，最大大小，每次增长百分之十
   GO
   
   

3.创建表结构

SELECT [id]
      ,[name]
      ,[password]
      ,[age]
	  ,[addressid]
  FROM [yin].[dbo].[user]

--给空表增加字段
ALTER TABLE [USER] ADD addressid int not null
--清空表数据
TRUNCATE table [user]
--插入数据
insert into [yin].[dbo].[user] values('李四','123456',25,1);
--修改数据
UPDATE [yin].[dbo].[user] SET name='王五' where id=1
--删除数据
DELETE FROM [yin].[dbo].[user] WHERE ID =1
--在当前数据库下可以直接使用[表名]
--连表查询
SELECT
*
FROM [user] U
JOIN [ADDRESS] A ON U.addressid=A.id
--查询
SELECT * FROM [ADDRESS]
--插入
INSERT INTO [address] VALUES ('河南')

4.储存过程

--创建储存过程，不带参数
CREATE PROC PROC_GET_USER 
	AS 
		BEGIN
		SELECT * FROM [USER]
	END

--执行调用储存过程
EXEC PROC_GET_USER
--修改储存过程
ALTER PROC PROC_GET_USER AS ELETE FROM [USER];

ALTER PROC PROC_GET_USER AS SELECT * FROM [USER];


--创建储存过程带参数
CREATE PROC PROC_SET_USER(@NAME VARCHAR(50),@PWD VARCHAR(50),@AGE INT,@ADDRESS INT)
 AS 
	begin
		INSERT into [user] VALUES (@NAME,@PWD,@AGE,@ADDRESS)
	end
--调用带参数的储存过程
EXEC PROC_SET_USER '王五','123456',25,1

--创建储存过程，带参数，带返回值
CREATE PROC PROC_GET_USER1(@ID INT,@AGE INT OUTPUT)
AS 
	BEGIN 
		SELECT @AGE=age FROM [user] WHERE ID = @ID
	END	

DECLARE @AGE INT --声明一个变量接收返回值
EXEC PROC_GET_USER1 2,@AGE OUTPUT    接收储存过程的返回值
SELECT @AGE --查看返回值

SELECT * FROM [USER]

--删除储存过程
DROP PROCEDURE [dbo].[PROC_GET_USER1]

5.创建游标

1.游标对数据进行遍历执行操作的

--定义变量接收游标的数据
declare @addressid int
--创建游标
--关键字 游标名    创建游标的关键字
declare cur_Course cursor
for
	--把查询出来的全部id赋值给游标
	SELECT addressid FROM [user]
--打开游标
open cur_Course 	
--从游标中拿到第一行的id赋值给变量
fetch from cur_Course into @addressid
--使用了fetch后可以根据@@FETCH_STATUS获取一个状态，0为获取到游标中的值
	while @@FETCH_STATUS = 0 
	begin
		--根据用户表获取到的地址id查询对应的地址
		 select * from [address] where id = @addressid
		 --再次从游标中拿到第下一行的id赋值给变量，然后再重新查询
		fetch from cur_Course into @addressid
	end
--使用完游标后需要关闭和释放
--关闭游标
close cur_Course
--释放游标
deallocate cur_Course

6.创建函数

1. sqlserver中函数分为标量函数和表值函数

2. 表值函数（可以增加参数，返回一个表），return 为一个table 表

   --创建表查询函数
   CREATE FUNCTION VIEW_USER()
   RETURNS TABLE
   AS 
   RETURN
   (
   SELECT * FROM [USER]
   );
    --查询函数
   SELECT * FROM VIEW_USER()
   

3. 标量函数（接收一个或者多个参数返回一个值）

   -- 标量函数
   --创建函数名字并且设置参数
   CREATE FUNCTION FN_GETUSER(@USERID INT)
   --标量函数的返回值为一个字符
   RETURNS VARCHAR(50)
   AS 
   	BEGIN 
   	--设置变量
   	DECLARE @NAME VARCHAR(50)
   	--查询赋值给变量
   	SELECT @NAME=name FROM [USER] WHERE ID=@USERID
   	--把值返回
   	RETURN @NAME
   	END;
   
   SELECT DBO.FN_GETUSER(5)
   

7.事务

CREATE TABLE [DBO].[CashTable](
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	UID INT NOT NULL,
	CASH BIGINT NOT NULL
)

INSERT INTO [CashTable] VALUES (1,100);
INSERT INTO [CashTable] VALUES (2,100);

SELECT * FROM CashTable
--创建转出存储过程

ALTER PROC ExchangeCash(
--转出的id
@id int,
--转入的id
@toid int,
--需要转的金额
@money bigint,
--接收返回的状态
@code int OUTPUT
)
AS 
 BEGIN TRAN --TRAN 开启事务
  --转出的id金额减少
  UPDATE [CashTable] SET CASH=(CASH - @money) WHERE UID=@id
  UPDATE [CashTable] SET CASH=(CASH+@money) WHERE UID=@toid
  --使用@@ERROR全局变量来判断上班sql是否出错，0为成功
  IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRAN --回滚到事务执行前恢复转账前的状态
			PRINT('转账失败，已回滚')
			set @code = 0
		END
  ELSE
    --上边sql如果执行成功则提交事务
		BEGIN
			COMMIT TRAN --提交事务 
			PRINT('转账成功')
			set @code = 1
		END  	
		
SELECT * FROM [CashTable]
DECLARE @CODE INT 
EXEC ExchangeCash 2,1,10,@code OUTPUT
SELECT @code 

8.触发器

触发器主要分为：dml触发器和ddl触发器

1. dml触发器（在以下几个操作之后触发）

   1. afert触发器

   2. insert触发器

   3. update触发器

   4. delete触发器

      --创建触发器，插入时候的触发器
      --     关键词  触发器名字
      CREATE TRIGGER dbo.stu_TRIGGER_INSERT
      --给哪一个表增加触发器
         ON dbo.Stu
         --给什么操作增加触发器
         AFTER  INSERT
      AS 
      BEGIN
      	SET NOCOUNT ON;--不返回影响行数
      	--插入数据时会产生INSERTED表(表结构和Stu一样)
      	--把[Stu_sum]存在的数据先更新掉
      	update b set b.number=isnull(b.number,0)+a.credit from 
      	(select [Name],sum(credit) credit  from  INSERTED group by [Name]) a
      	join [dbo].[Stu_sum] b on a.Name=b.Name
      	--把不存在的添加进[Stu_sum]
      	INSERT into [dbo].[Stu_sum](Name,number)
      	select a.Name,a.credit
      	 from 
      	(select [Name],sum(credit) credit  from  INSERTED group by [Name]) a
      	left join [dbo].[Stu_sum] b on a.Name=b.Name
      	where b.Name is null
      END
      GO
      
      
      --删除时候的触发器
      CREATE TRIGGER dbo.stu_TRIGGER_DELETE
         ON dbo.Stu
         AFTER  DELETE
      AS 
      BEGIN
      	SET NOCOUNT ON;--不返回影响行数
      	--删除数据时会产生DELETEED表(表结构和Stu一样)
      	--把[Stu_sum]存在的数据更新掉
      	update b set b.number=isnull(b.number,0)-a.credit from 
      	(select [Name],sum(credit) credit  from  DELETED group by [Name]) a
      	join [dbo].[Stu_sum] b on a.Name=b.Name
      END
      GO
      
      --修改时候的触发器
      CREATE TRIGGER dbo.stu_TRIGGER_UPDATE
         ON dbo.Stu
         AFTER  UPDATE
      AS 
      BEGIN
      	SET NOCOUNT ON;--不返回影响行数
      	--更新数据时会产生INSERTED 、DELETEED表(表结构和Stu一样)
      	--把[Stu_sum]存在的数据更新掉
      	update b set b.number=isnull(b.number,0)+d.credit from 
      	(select Name,sum(credit) credit from
      	(select a.[Name],b.credit-a.credit credit  from  DELETED a join INSERTED b on a.ID=b.ID) c group by [name]) d
      	join [dbo].[Stu_sum] b on d.Name=b.Name
      END
      GO
      

2. ddl触发器（之后触发 ）

   1. instead of 触发器

      --在插入操作之前进行的操作
      CREATE TRIGGER dbo.stu_TRIGGER_INSTEADOF
         ON dbo.Stu
         INstead of INSERT
      AS 
      BEGIN
      	if exists(select ID from inserted where credit>100)
      	begin
      		select '插入成绩错误' as 错误原因
      	end
      END
      GO
      

9.分离和附加、备份和还原

1、分离数据库就是将某个数据库（如student_Mis）从SQL Server数据库列表中删除，使其不再被SQL Server管理和使用，但该数据库的文件（.MDF）和对应的日志文件（.LDF）完好无损。分离成功后，我们就可以把该数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到其它磁盘中作为备份保存。

• 分离就是把某个数据从当前的连接汇总分离出去，然后可以对这个数据库进行复制备份

2、附加数据库就是将一个备份磁盘中的数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到需要的计算机，并将其添加到某个SQL Server数据库服务器中，由该服务器来管理和使用这个数据库。

• 附加就是把分离的数据库重新还原到连接中

3、备份就是把当前数据库备份为后缀是bak的文件，然后数据库的还原

4、设置自动备份

• 在管理–维护计划–右键维护计划向导，然后进行设置

  • 如果维护计划提示xp什么的执行

    • sp_configure 'show advanced options', 1;   
      GO   
      RECONFIGURE;   
      GO   
      sp_configure 'Agent XPs', 1;   
      GO   
      RECONFIGURE   
      GO
      

10.创建视图

IF EXISTS(SELECT * FROM SYS.VIEWS WHERE NAME='VIEW_USER_ADDRESS')
PRINT('视图已删除');
ELSE 
PRINT('视图不存在');


CREATE VIEW VIEW_USER_ADDRESS AS 
SELECT 
U.ID AS 用户id,
U.NAME 用户名字,
U.PASSWORD 密码,
U.AGE 年龄,
DZ.NAME AS 地址
FROM
[user] U
JOIN [address] DZ ON U.addressid=DZ.ID;

.常用函数

–CONVERT([数据类型] [目标值] [固定数字])： 函数是把日期转换为新数据类型的通用函数，函数可以用不同的格式显示日期/时间数据

• 第一个参数为，要转换的类型，第二个为目标值，第三个为固定参数

  • 常用SELECT CONVERT(VARCHAR(10),GETDATE(),120)

    • 把获取的日期转换为年/月/日

• 常用SELECT CONVERT(VARCHAR(19),GETDATE(),120)

  • 把获取的日期转换为年/月/日 时/分/秒

• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u5esOGcD-1668654894728)(C:\Users\LENOVO\AppData\Roaming\Typora\typora-user-images\image-20220804152929275.png)]

GETDATE():获取当前时间

CAST(列名 AS [数据类型]) ：数据类型转换函数

• --创建变量
  DECLARE @TIME DATETIME 
  --给变量赋值
  SET @TIME = (SELECT CONVERT(VARCHAR(10), GETDATE(), 120) + ' 08:00:00.00')
  --查询变量
  SELECT @TIME
  --给变量转换数据类型
  SELECT (CAST(@TIME AS FLOAT)-CAST(@TIME AS FLOAT))
  

DATEPART() ：函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

• 

• ​```SQL
      SELECT DATEPART(YY,GETDATE()) :返回年
  

case when then

SELECT REPLICATE(‘132’,3):让指定字段重复显示几遍

1.字符串函数

1. SELECT * FROM [user]
2. SELECT LEN('123 ') FROM [user] --获取指定字段的字符数,包含前边的空格但是不包含后边的
3. SELECT DATALENGTH(‘123’) FROM [user]–获取指定字段的字符数,包含前后两边的数据库
4. SELECT SUBSTRING(‘123’,1,1) FROM [user] --从下标1开始截取1个字符
5. SELECT RIGHT(‘123’,3) FROM [user] --从右边开始根据传递参数返回[index]3个字符
6. SELECT LEFT(‘123’,3) FROM [user] --从左边开始根据传递参数返回[index]3个字符
7. SELECT ASCII(name) FROM [USER] --返回字符串最左侧字符的ASCLL码
8. SELECT CHAR(98) FROM [USER] --把对应的ASCLL码转换为字母
9. SELECT CHARINDEX(‘3’,‘1234’) --获取指定字符在字段中的开始位置
10. SELECT CHARINDEX(‘1’,‘12341’,2) --如果指定了第三个参数，那么就是从第三个参数位置开始查看第一个指定参数的位置
11. SELECT DIFFERENCE(‘5748’,‘123’) – 返回一个0 到 4 的整数值，指示两个字符表达式的之间的相似程度。

    1. –0 表示几乎不同或完全不同，

    2. –4 表示几乎相同或完全相同。

12. SELECT LOWER(‘ASDAS’)_–把大写的转换为小写的字母
13. SELECT UPPER(‘asdasd’) --把小写的转换为大写的字母
14. SELECT LTRIM(’ 123’) --把字符串左边的空格清除
15. SELECT RTRIM('123 ') --把字符串右边的空格清除
16. SELECT REVERSE(‘123456’) --把指定字符反转
17. SELECT ‘A’+SPACE(20)+‘B’ --返回指定数量的空格放到字符串中
18. -STR([指定float字段],[需要显示数的长度],[小数点后保留几位小数])，会四舍五入
    1. SELECT STR(‘123.123’,3) --返回指定长度的数字
    2. SELECT STR(‘123.153’,6,1) --返回指定长度的数字

2.日期函数

--日期部分参数及缩写
--1.yy,yyyy    --年                    (year)
--2.qq,q        --季度                   (quarter)
--3.mm,m        --月                    (month)
--4.dy,y        --一年中的某一天            (dayofyear)
--5.dd,d        --日                    (day)
--6.wk,ww        --周                    (week)
--7.dw,w        --星期                    (weekday)
--8.hh        --小时                    (hour)
--9.mi,n        --分钟                    (minute)
--10.ss,s        --秒                    (second)
--11.ms        --毫秒                    (millisecond)
--日期中0代表了1900-01-01
SELECT GETDATE() --获取当前系统日期

SELECT DATEADD(YYYY,4,'5/10/2022') --根据第一个参数类型，把第三个参数时间增加指定第二个参数数值

SELECT DATEDIFF(DY,'5/10/2022','5/19/2022') -- 根据第一个参数类型，获取后边两个日期的对应时间差

SELECT DATENAME(DW,'2022-08-05') --根据第一个参数类型，和第二个日期返回对应的星期几

SELECT DATEPART(DAY,'2022-08-05') --根据第一个参数类型，和第二个日期返回对应的整数日期

--DATEDIFF(MM,0,GETDATE()) --求出本月和1900年一月差几个月，然后再使用DATEADD增加日期把时间求出每月的1号
SELECT DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) 
--获取每周的星期一
SELECT DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0) 

3.数学函数

--数学函数
--常用数学函数
--1.rand()        --返回0到1之间的随机float值
--2.abs()            --获取绝对值
--3.ceiling()        --向上取整，
--4.floor()        --向下取整
--5.power()        --取数值表达式的幂
--6.round()        --取数值表达式四舍五入为指定精度
--7.sign()        --对于整数返回+1，对于负数返回-1，对于0则返回0
--8.sqrt()        --取浮点数表达式的平方根

SELECT RAND()   --返回0~1之间的浮点数 0.848696888739493

SELECT ABS(-123) --取绝对值

SELECT CEILING(12.1) --向上取值

SELECT FLOOR(12.1) --向下取值

SELECT POWER(4,2)  --取数值表达式的幂

SELECT ROUND(4.15,1) --四舍五入，保留多少位小数

SELECT SIGN(-2) --对于整数返回+1，对于负数返回-1，对于0则返回0

SELECT SQRT(9) --返回平方根

4.系统函数

--常用系统函数
--1.convert()            --用来转变数据类型
--2.current_user        --返回你登录的用户名
--3.datalength()        --返回指定表达式的字节数
--4.host_name()        --返回当前用户所登录的计算机名称
--5.system_user        --返回你当前所登录的用户名
--6.user_name()        --从给定的用户名ID返回用户名  

SELECT CONVERT(VARCHAR(10),GETDATE(),120) --把日期转换为字符串

SELECT CURRENT_USER --获取登录的用户

SELECT DATALENGTH('张') --返回指定数据的字节数

SELECT HOST_NAME() --返回当前用户所登录的计算机名称
SELECT SYSTEM_USER  --获取当前登录数据库的用户

SELECT USER_NAME(1) --从给定的用户名ID返回用户名  
SELECT USER_ID()

5.聚合函数

--常用聚合函数
1.sum()            --和
2.avg()            --平均分
3.max()            --最大值
4.min()            --最小值
5.count()        --计数

6.全局函数

1、@@CONNECTIONS

--服务器上次启动以来创建的连接数


2、@@CPU_BUSY  

--自 SQL Server 启动至今，系统持续运行的毫秒数。


3、@@CURSOR_ROWS  

--最近打开的游标中的行数


4、@@DATEFIRST  

--SET DATEFIRST 参数的当前值，该参数用于设置一个星期的第一天为哪一天。


5、@@ERROR  

--最后一个 T-SQL 错误的错误号


6、@@FETCH_STATUS

--如果最后一次提取的状态为成功状态，则为 0。如果出错，则为 -1


7、@@IDENTITY  

--最后一次插入的标识值


8、@@LANGUAGE  

--当前使用的语言的名称


9、@@MAX_CONNECTIONS

--可以创建的同时连接的最大数


10、@@ROWCOUNT  

--受上一个 SQL 语句影响的行数


11、@@SERVERNAME  

--本地服务器的名称


12、@@SERVICENAME  

--该计算机上的 SQL 服务的名称


13、@@TIMETICKS  

--当前计算机上每指令周期的微秒数


14、@@TRANSCOUNT  

--当前连接打开的事务数


15、@@VERSION  

--SQL Server 的版本信息

7.自带储存过程

1、sp_databases --列出服务器上的所有数据库

2、sp_server_info --列出服务器信息，如字符集，版本和排列顺序

3、sp_stored_procedures--列出当前环境中的所有存储过程

4、sp_tables --列出当前环境中所有可以查询的对象

5、sp_start_job --立即启动自动化任务

6、sp_stop_job --停止正在执行的自动化任务

7、sp_password --添加或修改登录帐户的密码

8、sp_configure --显示(不带选项)或更改(带选项)当前服务器的全局配置设置

9、sp_help --返回表的列名，数据类型，约束类型等

10、sp_helptext --显示规则，默认值，未加密的存储过程，用户定义的函数，

11、sp_helpfile --查看当前数据库信息

12、sp_dboption --显示或更改数据库选项

13、sp_detach_db --分离数据库

14、sp_attach_db --附加数据库

15、sp_addumpdevice --添加设备

16、sp_dropdevice --删除设备

17、sp_pkeys --查看主键

18、sp_fkeys --查看外键

19、sp_helpdb --查看指定数据库相关文件信息

20、sp_addtype --自建数据类型

21、sp_droptype --删除自建数据类型

22、sp_rename --重新命名数据库

23、sp_executesql --执行SQL语句

24、sp_addlogin --添加登陆

25、sp_droplogin --删除登录

26、sp_grantdbaccess --把用户映射到登录，即添加一个数据库安全帐户并授予塔访问权限

27、sp_revokedbaccess--撤销用户的数据访问权,即从数据库中删除一个安全帐户

28、sp_addrole --添加角色

29、sp_addrolemember --向角色中添加成员，使其成为数据库角色的成员

30、sp_addsrvrolemember--修改登录使其成为固定服务器角色的成员

31、sp_grantlogin --允许使用组帐户或系统用户使用Windows身份验证连接到SQL

32、sp_defaultdb --修改一个登录的默认数据库

33、sp_helpindex --用于查看表的索引

34、sp_cursoropen --定义与游标和游标选项相关的SQL语句，然后生成游标

35、sp_cursorfetch --从游标中提取一行或多行

36、sp_cursorclose --关闭并释放游标

37、sp_cursoroption --设置各种游标选项

38、sp_cursor --用于请求定位更新

39、sp_cursorprepare --把与游标有关的T-SQL语句或批处理编译成执行计划，但并不创建游标

40、sp_cursorexecute --从由sp_cursorprepare创建的执行计划中创建并填充游标

41、sp_cursorunprepare --废弃由sp_cursorprepare生成的执行计划

42、sp_settriggerorder --指定第一个或最后一个激发的、与表关联的 AFTER 触发器。在第一个