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
更多相關文章
  • 占座 ...
  • 轉自:http://www.maomao365.com/?p=6864 摘要: 下文講述採用sql腳本批量刪除所有存儲過程的方法,如下所示: 實驗環境:sqlserver 2008 R2 平常使用sql腳本,刪除存儲過程,我們只可以使用刪除命令一條一條的刪除存儲過程,下文介紹一種簡便方法,可以對系統 ...
  • 有三張百萬級數據表 知識點表(ex_subject_point)9,316條數據 試題表(ex_question_junior)2,159,519條數據 有45個欄位 知識點試題關係表(ex_question_r_knowledge)3,156,155條數據 測試資料庫為:mysql (5.... ...
  • 由於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; 的恢復方法: 0、停業務數據寫入。【iptables封禁】 1、從備份伺服器上拉取最新的一個全備文件,恢復到一個臨時的伺服器上,解壓並啟動mysqld。 2、在這台新的slave上執行如下命令: 2.1 先配置好複製關係, c ...
  • 資料庫崩潰恢復表結構的方法 如果資料庫發生崩潰,無法登陸資料庫,想要快速恢復表結構的話有一個很方便的方法。 通過mysqlfrm工具就可以快速解析.frm文件,找到create table 語句。 安裝mysqlfrm 安裝mysqlfrm的話,需要兩個安裝包。mysql-utilities和mys ...
  • 分散式文件系統概述 相對於傳統的本地文件系統而言,分散式文件系統(Distribute File System)是一種通過網路實現文件在多台主機上進行分散式存儲的文件系統。分散式文件系統的設計一般採用“客戶/服務機”模式,客戶端以特定的通信協議通過網路與伺服器建立連接,提出文件訪問請求,客戶端和服務 ...
  • 1.為什麼要重寫SQL語句 ProxySQL在收到前端發送來的SQL語句後,可以根據已定製的規則去匹配它,匹配到了還可以去重寫這個語句,然後再路由到後端去。 什麼時候需要重寫SQL語句? 對於下麵這種簡單的讀、寫分離,當然用不上重寫SQL語句。 這樣的讀寫分離,實現起來非常簡單。如下: 但是,複雜一 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...