當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

sqlserver笔记

發布時間：2024/1/8 编程问答 33 豆豆

生活随笔收集整理的這篇文章主要介紹了 sqlserver笔记小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

CTRL + K + C :注釋選中行

安裝前需要.net Framwork3.5，安裝2008會自動更新安裝

1.sqlserver是一個關系型數據庫

關系數據庫管理系統（Relational Database Management System：RDBMS）是指包括相互聯系的邏輯組織和存取這些數據的一套程序 (數據庫管理系統軟件)。關系數據庫管理系統就是管理關系數據庫，并將數據邏輯組織的系統。

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 BEGINSELECT * 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 beginINSERT 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 = @IDEND 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 @addressidend --使用完游標后需要關閉和釋放 --關閉游標 close cur_Course --釋放游標 deallocate cur_Course

6.創建函數

sqlserver中函數分為標量函數和表值函數

表值函數（可以增加參數，返回一個表），return 為一個table 表

--創建表查詢函數 CREATE FUNCTION VIEW_USER() RETURNS TABLE AS RETURN ( SELECT * FROM [USER] );--查詢函數 SELECT * FROM VIEW_USER()

標量函數（接收一個或者多個參數返回一個值）

-- 標量函數 --創建函數名字并且設置參數 CREATE FUNCTION FN_GETUSER(@USERID INT) --標量函數的返回值為一個字符 RETURNS VARCHAR(50) AS BEGIN --設置變量DECLARE @NAME VARCHAR(50)--查詢賦值給變量SELECT @NAME=name FROM [USER] WHERE ID=@USERID--把值返回RETURN @NAMEEND;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=@idUPDATE [CashTable] SET CASH=(CASH+@money) WHERE UID=@toid--使用@@ERROR全局變量來判斷上班sql是否出錯，0為成功IF @@ERROR <> 0BEGINROLLBACK TRAN --回滾到事務執行前恢復轉賬前的狀態PRINT('轉賬失敗，已回滾')set @code = 0ENDELSE--上邊sql如果執行成功則提交事務BEGINCOMMIT TRAN --提交事務 PRINT('轉賬成功')set @code = 1END SELECT * FROM [CashTable] DECLARE @CODE INT EXEC ExchangeCash 2,1,10,@code OUTPUT SELECT @code

8.觸發器

觸發器主要分為：dml觸發器和ddl觸發器

dml觸發器（在以下幾個操作之后觸發）

afert觸發器

insert觸發器

update觸發器

delete觸發器

--創建觸發器，插入時候的觸發器 -- 關鍵詞觸發器名字 CREATE TRIGGER dbo.stu_TRIGGER_INSERT --給哪一個表增加觸發器ON dbo.Stu--給什么操作增加觸發器AFTER INSERT AS BEGINSET 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]) ajoin [dbo].[Stu_sum] b on a.Name=b.Name--把不存在的添加進[Stu_sum]INSERT into [dbo].[Stu_sum](Name,number)select a.Name,a.creditfrom (select [Name],sum(credit) credit from INSERTED group by [Name]) aleft join [dbo].[Stu_sum] b on a.Name=b.Namewhere b.Name is null END GO--刪除時候的觸發器 CREATE TRIGGER dbo.stu_TRIGGER_DELETEON dbo.StuAFTER DELETE AS BEGINSET 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]) ajoin [dbo].[Stu_sum] b on a.Name=b.Name END GO--修改時候的觸發器 CREATE TRIGGER dbo.stu_TRIGGER_UPDATEON dbo.StuAFTER UPDATE AS BEGINSET 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]) djoin [dbo].[Stu_sum] b on d.Name=b.Name END GO

ddl觸發器（之后觸發）

instead of 觸發器

--在插入操作之前進行的操作 CREATE TRIGGER dbo.stu_TRIGGER_INSTEADOFON dbo.StuINstead of INSERT AS BEGINif exists(select ID from inserted where credit>100)beginselect '插入成績錯誤' 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() ：函數用于返回日期/時間的單獨部分，比如年、月、日、小時、分鐘等等。

?```SQLSELECT DATEPART(YY,GETDATE()) :返回年

case when then

SELECT REPLICATE(‘132’,3):讓指定字段重復顯示幾遍

1.字符串函數

SELECT * FROM [user]

SELECT LEN('123 ') FROM [user] --獲取指定字段的字符數,包含前邊的空格但是不包含后邊的

SELECT DATALENGTH(‘123’) FROM [user]–獲取指定字段的字符數,包含前后兩邊的數據庫

SELECT SUBSTRING(‘123’,1,1) FROM [user] --從下標1開始截取1個字符

SELECT RIGHT(‘123’,3) FROM [user] --從右邊開始根據傳遞參數返回[index]3個字符

SELECT LEFT(‘123’,3) FROM [user] --從左邊開始根據傳遞參數返回[index]3個字符

SELECT ASCII(name) FROM [USER] --返回字符串最左側字符的ASCLL碼

SELECT CHAR(98) FROM [USER] --把對應的ASCLL碼轉換為字母

SELECT CHARINDEX(‘3’,‘1234’) --獲取指定字符在字段中的開始位置

SELECT CHARINDEX(‘1’,‘12341’,2) --如果指定了第三個參數，那么就是從第三個參數位置開始查看第一個指定參數的位置

SELECT DIFFERENCE(‘5748’,‘123’) – 返回一個0 到 4 的整數值，指示兩個字符表達式的之間的相似程度。

–0 表示幾乎不同或完全不同，

–4 表示幾乎相同或完全相同。

SELECT LOWER(‘ASDAS’)_–把大寫的轉換為小寫的字母

SELECT UPPER(‘asdasd’) --把小寫的轉換為大寫的字母

SELECT LTRIM(’ 123’) --把字符串左邊的空格清除

SELECT RTRIM('123 ') --把字符串右邊的空格清除

SELECT REVERSE(‘123456’) --把指定字符反轉

SELECT ‘A’+SPACE(20)+‘B’ --返回指定數量的空格放到字符串中

-STR([指定float字段],[需要顯示數的長度],[小數點后保留幾位小數])，會四舍五入

SELECT STR(‘123.123’,3) --返回指定長度的數字

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.848696888739493SELECT 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則返回0SELECT 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。如果出錯，則為 -17、@@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身份驗證連接到SQL32、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 觸發器。在第一個

總結

以上是生活随笔為你收集整理的sqlserver笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： 134. 加油站（JS实现）
下一篇： Spark GraphX 图算法的理解