SQL Server 資料庫基礎筆記分享（下）

来源:https://www.cnblogs.com/OneManStep/archive/2018/07/14/9309523.html
-Advertisement-
Play Games

前言 本文是個人學習SQL Server 資料庫時的以往筆記的整理，內容主要是對資料庫的基本增刪改查的SQL語句操作和約束，視圖，存儲過程，觸發器的基本瞭解。 註：內容比較基礎，適合入門者對SQL Server 資料庫的瞭解！！！ 正文 1.子查詢 --把一個查詢結果作為另外一個查詢的查詢源 sel ...


前言

本文是個人學習SQL Server 資料庫時的以往筆記的整理，內容主要是對資料庫的基本增刪改查的SQL語句操作和約束，視圖，存儲過程，觸發器的基本瞭解。

註：內容比較基礎，適合入門者對SQL Server 資料庫的瞭解！！！

正文

1.子查詢

--把一個查詢結果作為另外一個查詢的查詢源
select * from (select * from Student where tbage between 3 and 5)
as ct where tbname=5 --ct是新創的表名

--把另外一個查詢的結果作為當前查詢的條件來使用。
--子查詢中=、!= 、< 、> 、<= 、>=之後只能返回單個值，如果多個值就會報錯
--解決辦法 可以用in 代替
select * from Student
where tbage in(select tbage from Student where tbname=3)

select * from Student
where tbage=(select tbage from Student where tbname=3)

》》》》》》子查詢分頁《《《《《《

--1》顯示第一頁的數據
--分頁查詢的時候首先是將數據排序
select * from Student order by id desc

--2》第一頁 顯示5條數據
select Top 5 * from Student order by id desc

--3》第二頁
select top 5 * from Student
where id not in (select top 5 * from Student order by id desc)
order by id desc

--4》第三頁
select top 5 * from Student
where id not in (select top (2*5) * from Student order by id desc)
order by id desc

》》》開窗函數分頁《《《

--第七頁數據 每頁5條數據
--over屬於開窗函數

select * from
(
select * ,row_number() over( order by id desc) as paixu from Student
) as tbl
where tbl.paixu between 6*5+1 and 7*5

2.連表查詢

--查詢所有學生的姓名、年齡及所在班級 （班級在另一個表中）
--當多個列在不同的表中時，要跨表查詢，所以一般可以使用inner join
--tc ts是對錶名起的別名
select
ts.tsname,
ts.tsage,
tc.tclassname
from TblStudent as ts
inner join TblClass as tc on ts.tsclassid=tc.tclassid（只查詢兩個表中都有的數據）

--》》》full join 是查詢所有的數據（沒有的為空）

---子查詢寫法
select
tsname,
tsage,
班級名稱=(select tclassname from TblClass where TblClass.tclassid=TblStudent.tsclassid)
from TblStudent

--查詢學生姓名、年齡、班級及成績（成績屬於第三張表）
select
ts.tsname,
ts.tsage,
tc.tclasssname,
tscore.tenglish,
tscore.tmath
from TblStudent as ts
inner join TblClass as tc on ts.tsclassid=tc.tclassid 
inner join TblScore as tscore on tscore.tsid=ts.tsid

 

--》》》左外聯接（左聯接）

--查詢沒有參加考試的學生的姓名與編號
--把左表（left join 關鍵字左邊的表）中的全部記錄都顯示出來，對於那些在右表中能找到匹配的記錄，顯示對應匹配數據，對於那些右表中找不到匹配的記錄顯示為null
select
ts.tsid,
ts.tsname,
TblScore.*
from TblStudent as ts
left outer join TblSore.tsid=ts.tsid   --outer可以不寫

--》》》右外聯接
--表示要將右表（right join 右邊的表）中的所有數據都顯示，左表中只顯示那些匹配的數據。

select
ts.tsid,
ts.tsname,
TblScore.*
from TblStudent as ts
right outer join TblSore.tsid=ts.tsid

--右外聯與左外聯都是先將匹配的數據找到，然後再將那些沒有匹配的數據添加進來，（註意：不是一起查詢出來的，有先後順序）

--》》》練習：查詢所有學生（參加和未參加的考試）的學生姓名、年齡、成績，如果沒有參加考試顯示缺考，如果小於english或者math 小於60分顯示不及格
select
ts.tsname,
ts.tsage,
tscore.tsid,
case
when tscore.tenglish is null then '缺考'
else convert(varchar(10),tscore.tenglish)
end as 英語成績,
case
when tscore.tmath id null then '缺考'
else convert (varchar(10),tscore.tmath)
end as 數學成績,
是否報考=
case
when tscore.tscoreid is null then '是'
else '否'
end
from TblStudent as ts
left join TblScore as tscore on ts.tsid=tscore.tsid

3.視圖

視圖本身並不存儲數據，只是存儲的查詢語句，如果把真實表中的數據修改後，則通過視圖查詢到的結果也變了。

視圖的目的是方便查詢，所以一般情況下不能對視圖進行增刪改查


--在視圖中的查詢語句，必須為每一列創建一個列名
create view vw2
as
select
tsname,
case
when tsage>13 and tsage<=16 then '少年'
when tsage>50 then '老年'
else '青壯年'
end as 稱呼
from TblStudent

--在視圖中不能使用order by語句。除非：另外還指定了top 或for xml
--錯誤
create view vw3
as
select * from TblStudent order by tsage desc

--正確
create view vw3
as
select top 3 * from TblStudent order by tsage desc

4.聲明變數與使用

--》》》局部變數
--聲明變數
declare @name varchar(10)
declare @age int

--賦值
set @name='yhz'
set @age=17

--輸出值
print @name
print @age

--使用set與select為變數賦值的區別
declare @rcount int
set @rcount=(select count(*) from TblStudent)
print @rcount

declare @rcount int
select @rcount=count(*) from TblStudent
print @rcount


--》》》全局變數
print @@language
print @@version
print 'aaa'+100
--通過判斷@@error變數中是否不為0，就可以判斷上一條sql語句執行是否出錯了
--如果@@error為0，表示上一條sql語句執行沒出錯，如果@@error不為0，則表示上一條sql語句出錯了。
print@@error

--》》》通過while計算1-100之間所有奇數的和

--聲明變數並初始化

declare @sum int=0
declare @i int =1
while @i<=100
begin
if @i%2<>0
begin
set @sum=@sum+@i
end
end
print @sum

5.事務

事務有四個屬性：原子性 一致性 隔離性 持久性
原子性：對於數據修改，要麼全都執行，要麼全都不執行
一致性：當數據完成時，數據必須處於一致狀態
隔離性：對數據進行修改的所有併發事務時彼此隔離的。這表明事務必須是獨立的，它不應以任何方式依賴於或影響其他事務
永久性：事務完成後，他對資料庫的修改被永久保持，事務日誌能夠保持事務的永久性

--打開事務
begin transaction

--提交事務
commit transaction

--回滾事務
rollback transaction


--賬戶A給賬戶B轉賬 當一方出問題時，兩個語句都不執行
begin tran
declare @sum int=0
update bank set balance =balance-1000 where cid='0001'
set @sum=@sum+@@error
update banl set balance =balance+1000 where cid='0002'
set @sum=@sum+@@error

if @sum<>0
begin
rollback tran
print '回滾'
end
else
begin
commit tran
print '提交了'
end

6.存儲過程

--創建一個自定義的存儲過程
create proc usp_HelloWorld
as
begin
print 'hello world'
end

--輸出存儲過程
exec usp_HelloWorld

--創建一個存儲過程計算兩個數的和
create procedure usp_Add
@num1 int,
@num2 int
as
begin
print @num1+@num2
end

--輸出值
exec usp_Add 100,230


--存儲過程中的參數的問題
--存儲過程如果有參數，則調用的時候必須為參數賦值
exec usp_Add --不傳參數則報錯


--第二個參數如果用戶不傳，則有一個預設值
create procedure usp_Add
@num1 int,
@num2 int 1000 --為存儲過程的參數設置預設值
as
begin
print @num1+@num2
end

--創建分頁存儲過程
create proc usp_PageBum
@pageSize int, --每頁顯示的數量
@pageIndex int --第幾頁
as
begin
select * from (select *,row_number()over (order by CityID asc)as num from S_City )as s
where s.num between (@pageIndex -1)*@pageSize +1 and @pageIndex *@pageSize
end
--查詢第5頁內容每頁顯示10條數據
exec usp_PageBum 10,5

--刪除一個存儲過程
drop proc usp_Add

7.觸發器

儘量避免在觸發器中執行耗時操作，因為觸發器會與sql語句認為在同一個事務中（事務不結束，就無法釋放鎖）

--創建插入數據觸發器
create trigger tri_Teacher_insert_after
on Teacher after insert
as
begin
declare @id int
declare @name varchar(10)
declare @phone int
declare @mail varchar(50)
select @id=tcid,@name=tcname,@phone=tcphone,@mail=tcmail from inserted

print @id
print @name
print @phone
print @mail
end

--插入數據
insert into Teacher values('網名好','12352536','[email protected]')


--創建刪除數據觸發器
--不能有主鍵
create trigger tri_Teacher_after
on Teacher after delete
as
begin
insert into TeacherBak
select * from deleted
end

--刪除數據
--sql server中的觸發器是表級觸發器，無論刪除多少行或者插入多少行，只觸發一次
--是按語句來觸發的，每次執行一次語句，觸發一次觸發器
delete from Teacher where tcid>18

8.游標

--1.定義游標
declare cur_Student cursor fast_forward for select * from Student

--2.打開游標
open cur_Student

--2.1 對游標的操作
--將每條數據讀取並輸出

--2.1.1將游標向後移動一條
fetch next from cur_Student

--將游標迴圈向後移動，直到末尾
while @@fetch_status=0
begin
fetch next from cur_Student
end


--3.關閉游標
close cur_Student

--4.釋放資源
deallocate cur_Student

9.（補充）全局臨時表，局部臨時表

局部臨時表：表名以#為開頭。只在當前會話中有效，不能跨連接訪問。如果直接在連接會話中創建，則當前連接斷開後刪除，如果是在存儲過程中創建的，則存儲過程執行完畢後刪除

全局臨時表：表名以##為開頭。多個會話可共用全局臨時表。當創建全局臨時表的會話斷開，並且沒有用戶正在訪問全局臨時表時刪除

10.（補充）約束

--刪除一列（EmpAddress列）
alter table Class drop column EmpAddress

--增加一列（增加一列EmpAddr varchar（1000））
alter table Class Add EmpAddr varchar(1000)

--修改一下Emp 的數據類型（varchar（200））
alter table Class alter column Emp varchar(200)

--為EmpId增加一個主鍵約束
alter table Class add constraint PK_Class_EmpId primary key(EmpId)

--為EmpName增加一個唯一約束
alter table Class add constraint UQ_Class_EmpName unique(EmpName)

--為性別增加一個預設約束，預設為男
alter table Class add constraint DF_Class_EmpGender default('男') for EmpGender

--為年齡增加一個檢查約束，年齡必須在1—120歲之間（包含）
alter table Class add constraint CK_Class_EmpAge check(EmpAge>=0 and EmpAge<=120)

--增加外鍵約束，表Class中有一列EmpDeptId引用Student表中的DeptId
alter table Class add EmpDeptId int not null
alter table Student add constraint PK_Student_DeptId primary key(DeptId)

alter table Class add constraint FK_Class_Student foreign key(EmpDeptId)
references Student(DeptId)


--一條語句刪除多個約束，約束名用 逗號 隔開
alter table Class drop constraint
PK_Student_DeptId,
FK_Class_Student,
CK_Class_EmpAge


--用一條語句為表增加多個約束
alter table Class add
constraint PK_Student_DeptId primary key(DeptId),
constraint CK_Class_EmpAge check(EmpAge>=0 and EmpAge<=120),
add constraint DF_Class_EmpGender default('男') for EmpGender

後記

筆記不全，還請見諒！希望對你有所提高。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL增強半同步幾個重要參數搭配的測試
    占座 ...
  • mssql sqlserver 批量刪除所有存儲過程的方法分享
    轉自:http://www.maomao365.com/?p=6864 摘要: 下文講述採用sql腳本批量刪除所有存儲過程的方法，如下所示: 實驗環境:sqlserver 2008 R2 平常使用sql腳本，刪除存儲過程，我們只可以使用刪除命令一條一條的刪除存儲過程，下文介紹一種簡便方法，可以對系統 ...
  • 資料庫sql優化總結之2-百萬級資料庫優化方案+案例分析
    有三張百萬級數據表 知識點表（ex_subject_point）9,316條數據 試題表（ex_question_junior）2,159,519條數據 有45個欄位 知識點試題關係表（ex_question_r_knowledge）3,156,155條數據 測試資料庫為：mysql （5.... ...
  • maven添加oracle依賴失敗
    由於Oracle授權問題，Maven3不提供oracle JDBC driver 可以到maven中央倉庫去下載依賴,網址:http://repo.spring.io/plugins-release/com/oracle/ojdbc6/11.2.0.3/ 複製到本地倉庫對應目錄即可 ...
  • MySQL5.7下麵，誤操作導致的drop table db1.tb1; 的恢復方法：
    MySQL5.7下麵，誤操作導致的drop table db1.tb1; 的恢復方法： 0、停業務數據寫入。【iptables封禁】 1、從備份伺服器上拉取最新的一個全備文件，恢復到一個臨時的伺服器上，解壓並啟動mysqld。 2、在這台新的slave上執行如下命令： 2.1 先配置好複製關係， c ...
  • 資料庫崩潰恢復表結構的方法
    資料庫崩潰恢復表結構的方法 如果資料庫發生崩潰，無法登陸資料庫，想要快速恢復表結構的話有一個很方便的方法。 通過mysqlfrm工具就可以快速解析.frm文件，找到create table 語句。 安裝mysqlfrm 安裝mysqlfrm的話，需要兩個安裝包。mysql-utilities和mys ...
  • 大數據技術原理與應用——分散式文件系統HDFS
    分散式文件系統概述 相對於傳統的本地文件系統而言，分散式文件系統（Distribute File System）是一種通過網路實現文件在多台主機上進行分散式存儲的文件系統。分散式文件系統的設計一般採用“客戶/服務機”模式，客戶端以特定的通信協議通過網路與伺服器建立連接，提出文件訪問請求，客戶端和服務 ...
  • MySQL中間件之ProxySQL(8)：SQL語句的重寫規則
    1.為什麼要重寫SQL語句 ProxySQL在收到前端發送來的SQL語句後，可以根據已定製的規則去匹配它，匹配到了還可以去重寫這個語句，然後再路由到後端去。 什麼時候需要重寫SQL語句？ 對於下麵這種簡單的讀、寫分離，當然用不上重寫SQL語句。 這樣的讀寫分離，實現起來非常簡單。如下： 但是，複雜一 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類