目錄

目錄

一、數(shù)據(jù)庫概念

1.1 什么是數(shù)據(jù)庫

1.2 為什么用數(shù)據(jù)庫

1.3 數(shù)據(jù)庫的發(fā)展歷史

1.4 數(shù)據(jù)庫分類

1.5 DBMS數(shù)據(jù)庫管理系統(tǒng)

二、 SQL(Structured Query Language)

2.1? 定義

2.2? SQL分類

2.3 SQL與T-SQL

三、SqlServer下載與安裝

四、數(shù)據(jù)庫表各類操作

1 創(chuàng)建數(shù)據(jù)庫登錄用戶

2 使用DCL賦予數(shù)據(jù)庫用戶操作數(shù)據(jù)表的權(quán)限

3 使用DDL創(chuàng)建數(shù)據(jù)庫表等對(duì)象

3.1創(chuàng)建/刪除數(shù)據(jù)庫

3.2創(chuàng)建/刪除/修改數(shù)據(jù)表

3.3了解數(shù)據(jù)類型

3.4約束條件

4 數(shù)據(jù)表數(shù)據(jù)的操作（INSERT,UPDATE,DELETE）

5 數(shù)據(jù)表數(shù)據(jù)的查詢操作

5.1 基本查詢

5.2 關(guān)鍵字的查詢應(yīng)用

5.3 系統(tǒng)函數(shù)（重點(diǎn)聚合函數(shù)）

5.4 子查詢

5.5 分頁實(shí)例?

5.5 行轉(zhuǎn)列，列轉(zhuǎn)行

5.6?多表查詢?

5.7遞歸查詢

?一、數(shù)據(jù)庫概念

1.1 什么是數(shù)據(jù)庫

數(shù)據(jù)庫是結(jié)構(gòu)化信息或數(shù)據(jù)的有序集合，一般以電子形式存儲(chǔ)在計(jì)算機(jī)系統(tǒng)中

通俗點(diǎn)說：就是存儲(chǔ)和管理數(shù)據(jù)的倉庫

1.2 為什么用數(shù)據(jù)庫

1) 數(shù)據(jù)庫可以結(jié)構(gòu)化存儲(chǔ)大量的數(shù)據(jù)信息，方便用戶進(jìn)行有效的檢索和訪問。
數(shù)據(jù)庫可以對(duì)數(shù)據(jù)進(jìn)行分類保存，并且能夠提供快速的查詢。例如，我們平時(shí)使用百度搜索內(nèi)容時(shí)，百度也是基于數(shù)據(jù)庫和數(shù)據(jù)分類技術(shù)來達(dá)到快速搜索的目的。

2) 數(shù)據(jù)庫可以有效地保持?jǐn)?shù)據(jù)信息的一致性、完整性、降低數(shù)據(jù)冗余。
可以很好地保證數(shù)據(jù)有效、不被破壞，而且數(shù)據(jù)庫自身有避免重復(fù)數(shù)據(jù)的功能，以此來降低數(shù)據(jù)的冗余。

3) 數(shù)據(jù)庫可以滿足應(yīng)用的共享和安全方面的要求，把數(shù)據(jù)放在數(shù)據(jù)庫中在很多情況下也是出于安全的考慮。
例如，如果把所有員工信息和工資數(shù)據(jù)都放在磁盤文件上，則工資的保密性就無從談起。如果把員工信息和工資數(shù)據(jù)放在數(shù)據(jù)庫中，就可以只允許查詢和修改員工信息，而工資信息只允許指定人（如財(cái)務(wù)人員）查看，從而保證數(shù)據(jù)的安全性。

4) 數(shù)據(jù)庫技術(shù)能夠方便智能化地分析，產(chǎn)生新的有用信息。
例如，超市中把物品銷售信息保存在數(shù)據(jù)庫中，每個(gè)月銷售情況的排名決定了下半月的進(jìn)貨數(shù)量。數(shù)據(jù)庫查詢的結(jié)果實(shí)際上產(chǎn)生了新的數(shù)據(jù)信息。
數(shù)據(jù)挖掘、聯(lián)機(jī)分析等技術(shù)近年來發(fā)展非?？?，其核心意義在于從一堆數(shù)據(jù)中分析出有用的信息。

簡(jiǎn)單理解：安全，高效，便捷

1.3 數(shù)據(jù)庫發(fā)展歷史

1 層次數(shù)據(jù)庫和網(wǎng)狀數(shù)據(jù)庫技術(shù)階段，使用指針來表示數(shù)據(jù)之間的聯(lián)系

2?關(guān)系型數(shù)據(jù)庫技術(shù)階段，代表的DBMS有：Oracle、DB2、MySQL、SQL Server、SyBase等

3?后關(guān)系型數(shù)據(jù)庫技術(shù)階段，由于關(guān)系型數(shù)據(jù)庫中存在數(shù)據(jù)模型、性能、拓展伸縮性差的缺點(diǎn)，所以出現(xiàn)了ORDBMS（面向?qū)ο髷?shù)據(jù)庫技術(shù)），NoSQL（非關(guān)系數(shù)據(jù)庫）。

1.4 數(shù)據(jù)庫分類

一般分為關(guān)系型數(shù)據(jù)庫和非關(guān)系數(shù)據(jù)庫（NOSQL)

關(guān)系型數(shù)據(jù)庫常見的有Sqlserver,Mysql,Sqlite,Oracle等

非關(guān)系型數(shù)據(jù)庫（NOSQL）有redis、MongoDB,HBase、BigTable、CouchDB、Neo4J等

1.5 DBMS數(shù)據(jù)庫管理系統(tǒng)

數(shù)據(jù)庫通常離不開完備的數(shù)據(jù)庫軟件程序，也就是數(shù)據(jù)庫管理系統(tǒng) (DBMS)。

DBMS 充當(dāng)數(shù)據(jù)庫與其用戶或程序之間的接口，允許用戶檢索、更新和管理信息的組織和優(yōu)化方式。此外，DBMS 還有助于監(jiān)督和控制數(shù)據(jù)庫，提供各種管理操作，例如性能監(jiān)視、調(diào)優(yōu)、備份和恢復(fù)。

常見的數(shù)據(jù)庫軟件或 DBMS 有 MySQL、Microsoft Access、Microsoft SQL Server、FileMaker Pro、Oracle Database 和 dBASE。我們常說的數(shù)據(jù)庫，大多都是說的DBMS。

二、 SQL(Structured Query Language)

2.1? 定義

結(jié)構(gòu)化查詢語句，是一項(xiàng)標(biāo)準(zhǔn)，適用于關(guān)系型數(shù)據(jù)庫，雖說是一項(xiàng)標(biāo)準(zhǔn)，但是不同數(shù)據(jù)庫在一些地方還是由自己的sql標(biāo)準(zhǔn)，好在大多還是遵循SQL中的標(biāo)準(zhǔn)。

2.2? SQL分類

1 DCL 數(shù)據(jù)控制語言，一般使用GRANT或REVOKE關(guān)鍵字獲得許可，確定單個(gè)用戶或用戶組對(duì)數(shù)據(jù)庫對(duì)象的對(duì)象的訪問

2 DDL 數(shù)據(jù)定義語言，使用CREATE,DROP,ALTER,操作數(shù)據(jù)庫，表，視圖，索引，函數(shù)，存儲(chǔ)過程，觸發(fā)器等

3 DML數(shù)據(jù)操作語言，使用INSERT,UPDATE,DALETE,對(duì)數(shù)據(jù)庫數(shù)據(jù)進(jìn)行操作

4 DQL數(shù)據(jù)查詢語言，使用SELECT對(duì)數(shù)據(jù)進(jìn)行查詢操作，最常用

5 TPL 事務(wù)處理語言，它的語句能確保被DML語句影響的表的所有行及時(shí)得以更新。TPL語句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

6?CCL?指針控制語言，DECLARE CURSOR，F(xiàn)ETCH INTO和UPDATE WHERE CURRENT用于對(duì)一個(gè)或多個(gè)表單獨(dú)行的操作

2.3 SQL與T-SQL

?SQL與T-SQL,SQL是T-SQL都是語言，不過T-SQL是SQL在Microsoft SQL Server上的增強(qiáng)版，他允許用戶使用變量，申明常量，運(yùn)行函數(shù)，儲(chǔ)存過程等進(jìn)行數(shù)據(jù)庫編程

編寫SQL須知：不同于C#，SQL是不區(qū)分大小寫，沒有雙引號(hào)的使用

三、SqlServer下載與安裝

1、SqlServer2019（下載Developer版本）
下載路徑：https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads

2、SQL Server Management Studio（數(shù)據(jù)庫管理工具）
下載路徑：https://learn.microsoft.com/zh-cn/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

四、數(shù)據(jù)庫表各類操作

1 創(chuàng)建數(shù)據(jù)庫登錄用戶

--創(chuàng)建數(shù)據(jù)庫登錄用戶（僅登錄權(quán)限）
exec sp_addlogin 'username','password'

--賦予登錄用戶連接數(shù)據(jù)的權(quán)限
exec sp_addgrantdbaccess 'username','dbname'  

--注：文字涉及到username，password,dbname等均為可以自定義的內(nèi)容，可以自行修改

2 使用DCL賦予數(shù)據(jù)庫用戶操作數(shù)據(jù)表的權(quán)限

--賦予用戶創(chuàng)建數(shù)據(jù)表的權(quán)限
grant create table to uesrname

--賦予用戶刪除數(shù)據(jù)表的權(quán)限
grant drop table to username

--同時(shí)賦予數(shù)據(jù)表創(chuàng)建和刪除的權(quán)限
grant create,drop table to username

--賦予用戶在數(shù)據(jù)表中插入數(shù)據(jù)的權(quán)限
grant insert on table to username

--賦予用戶在數(shù)據(jù)表中更新數(shù)據(jù)的權(quán)限
grant update on table to username

--賦予用戶在數(shù)據(jù)表中刪除數(shù)據(jù)的權(quán)限
grant delete on table to username

--賦予用戶在數(shù)據(jù)表中查詢數(shù)據(jù)的權(quán)限
grant select on table to username

--同時(shí)賦予多個(gè)數(shù)據(jù)表操作權(quán)限
grant insert,update,delete,select on table to username

3 使用DDL創(chuàng)建數(shù)據(jù)庫表等對(duì)象

3.1創(chuàng)建/刪除數(shù)據(jù)庫

--指定默認(rèn)連接的數(shù)據(jù)
use [dbname]

--批處理,應(yīng)將當(dāng)前的SQL批處理語句發(fā)送給SQLServer的信號(hào)
go

--創(chuàng)建數(shù)據(jù)庫(默認(rèn)方式)
create database [dbname]

--創(chuàng)建數(shù)據(jù)庫（指定存儲(chǔ)數(shù)據(jù)庫文件和日志文件的方式）
create database [dbname]
on
(
name='db_name',--數(shù)據(jù)庫文件名稱
filename='d:\xx\db_name_data.mdf',--目錄
size=1mb,--初始大小
maxsize=10mb,--最大限制
filegrowth=10%--增長(zhǎng)方式，每次增長(zhǎng)10%
)log on
(
name='dblog_name',--數(shù)據(jù)庫日志文件名稱
filename='d:\xx\dblog_name_log.ldf',--目錄
size=1mb,--初始大小
maxsize=10mb,--最大限制
filegrowth=10%--增長(zhǎng)方式，每次增長(zhǎng)10%
); 

--刪除數(shù)據(jù)庫
drop database [daname]

--一般平?？梢栽趧?chuàng)建數(shù)據(jù)庫之前判斷一下，具體情況具體操作
if exists (select * from sys.databases where name='dbname')
drop database [daname]

3.2創(chuàng)建/刪除/修改數(shù)據(jù)表

--創(chuàng)建數(shù)據(jù)表
create table [tbname]
(
id int not null,--[字段名] 數(shù)據(jù)類型 約束條件
name varchar(50) unique,--[字段名] 數(shù)據(jù)類型 約束條件
age tinyint,
);

--建表的時(shí)候，一般格式是字段名，數(shù)據(jù)類型，約束條件，
--但是大多數(shù)只會(huì)定義字段名和數(shù)據(jù)類型，最多定義非空約束，
--關(guān)于約束都是單獨(dú)定義和維護(hù)，后文中會(huì)講解

--刪除數(shù)據(jù)表
drop table [tbname]

--修改表，添加一列
alter table [tbname]
add [columnname] int not null--同理add 后面接上 字段名，數(shù)據(jù)類型，約束條件（可單獨(dú)添加）

--修改表，修改一列的數(shù)據(jù)類型
alter table [tbname]
alter column [columnname] varchar(50) --這里是將上面的int 修改為varcahr

--修改表，刪除一列
alter table [tbname]
drop column [columnname]

3.3了解數(shù)據(jù)類型

--數(shù)據(jù)類型SQL Server 中分為4個(gè)大類
--【string類】
char(n),varchar(n),text(n),
nchar(n),nvarchar(n),ntext(n),
bit,binary(n),varbinary,image

--【number類】
tinyint,smallint,int,bigint,
decimal(p,s),numeric(p,s),
smallmoney,money,
float(n),real(n)

--【date類】
datetime,datetime2,smalldatetime,
date,time,datetimeoffset,timestamp

--【其他】：
sql_variant,uniqueidentifier,xml,cursor,table

--注意，varchar如果不填寫長(zhǎng)度，默認(rèn)長(zhǎng)度為1
--這里大概了解，具體還是需要落實(shí)到使用中去熟悉各類數(shù)據(jù)的特性

3.4約束條件

--數(shù)據(jù)表中保存的數(shù)據(jù)，數(shù)據(jù)必須滿足數(shù)據(jù)完整性的要求；
--而為了實(shí)現(xiàn)數(shù)據(jù)完整性就需要用約束條件來實(shí)現(xiàn)

--【數(shù)據(jù)完整性】
--1 實(shí)體完整性，規(guī)定數(shù)據(jù)表中的數(shù)據(jù)必須是唯一的實(shí)體
--2 值（域）完整性，是指數(shù)據(jù)庫表中的列必須滿足某種特定的數(shù)據(jù)類型或約束
--3 參照完整性，是指兩個(gè)表的主關(guān)鍵字和外關(guān)鍵字的數(shù)據(jù)應(yīng)對(duì)應(yīng)一致
--4 自定義完整性，是針對(duì)某個(gè)特定關(guān)系數(shù)據(jù)庫的約束條件，
--它反映某一具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的語義要求

--【約束條件】記憶口訣：NPC服（F）毒(DU)
N=>not null        --非空約束
P=>primary key     --主鍵約束=>簡(jiǎn)寫PK
C=>check           --檢查約束=>簡(jiǎn)寫CK
F=>foreign key     --外鍵約束=>簡(jiǎn)寫PK
D=>default         --默認(rèn)約束=>簡(jiǎn)寫DF
U=>unique          --唯一約束=>簡(jiǎn)寫UQ

--約束名命名規(guī)則
[約束簡(jiǎn)寫_表名_列名]，如[pk_user_id],表示是為user表中id這一列添加了主鍵

--添加主鍵約束（示例）
alter table [user] 
add constraint [pk_user_id] primary key(id);

--添加外鍵約束（示例）
alter table [user] 
add constraint [fk_user_stuId] foreign key(stuId) references [stuTb] (id);

--添加檢查約束
alter table [user]
add constraint [ck_user_age] check(age>10 and age<20)

--添加唯一約束
alter table [user]
add constraint [uq_user_logid] unique(logid)

--添加默認(rèn)約束
alter table [user]
add constraint [df_user_sex] default '保密' for sex

--非空約束無法添加，建表的時(shí)候沒有創(chuàng)建非空約束，可以在編輯界面修改或者通過以下命令
alter table [user]
alter column id int not null--通過修改列去添加

--刪除約束
drop constraint [constraintname]

--【注意】
--1 約束只有創(chuàng)建和刪除，沒有修改
--2 約束最好單獨(dú)定義，單獨(dú)定義有利于后期的維護(hù)和擴(kuò)展
--3 不在建表的時(shí)候添加過多的約束條件

4 數(shù)據(jù)表數(shù)據(jù)的操作（INSERT,UPDATE,DELETE）

--插入數(shù)據(jù)
insert into [tbname] values ('value1','value2',...);

--插入指定列的數(shù)據(jù)
insert into [tbname](column1,column2,...)values('value1','value2',...)

--更新數(shù)據(jù)
update [tbname] set columnname=value where ....
update [tbname] set age=10, sex='保密' where id=1;--示例

--刪除數(shù)據(jù)
delete from [tbname] where ...--from 可省略

--更新和刪除語句都會(huì)配合where條件，具體where的使用會(huì)在select中說明


--復(fù)制新表(直接將userinfo整個(gè)表復(fù)制一份命名為userinfo2)
--一般用于測(cè)試語句，但是又不想線上數(shù)據(jù)收到影響
select * into userinfo2 from userinfo

演變：
select * from userinfo --查出所有數(shù)據(jù)
select * into userinfo2 from userinfo --將userinfo中查到的所有數(shù)據(jù)插入到userinfo2
--使用的時(shí)候記住，查出來，然后into至對(duì)應(yīng)的表即可

--兩個(gè)表結(jié)構(gòu)相同的表，需要將表1的數(shù)據(jù)插入到表2中
insert into userinfo2 values select * from userinfo--values 可省略

5 數(shù)據(jù)表數(shù)據(jù)的查詢操作

5.1 基本查詢

--查詢所有數(shù)據(jù)（示例）
select * from userinfo--查詢所有userinfo表的信息

--查詢指定列的信息（示例）
select [id],[name],[sex] from userinfo --查詢id,name,sex 三列的信息

5.2 關(guān)鍵字的查詢應(yīng)用

--指定別名（均以u(píng)serinfo示例）
--1 使用 =
select [編號(hào)]=[id] from userinfo

--2 省略 =
select [id] [編號(hào)] from userinfo

--3 使用as (推薦使用)
select [id] as [編號(hào)]，name from userinfo

-------------------------------------------------------------

--as 關(guān)鍵字的使用
--1 申明變量
declare @name as varchar(50)  --這里的as 可以省略

--2 指定別名
select [id] as [編號(hào)]，name from userinfo--指定列別名

select * from userinfo as uinfo--指定表別名

select * from (select id,name from userinfo) as uuinfo--為結(jié)果集指定別名

--3 一次指定多列別名(注意要字段一一對(duì)應(yīng)號(hào))
select * from (select id,name,sex from userinfo as uinfo(編號(hào),姓名,性別))  



--where 關(guān)鍵字的使用
--where 后可讓用戶自定義各種條件，條件得到true,獲取，條件得到false,排除；
select * from userinfo where id=1;

--top 獲取指定條數(shù)的數(shù)據(jù)
select top 1 * from userinfo
select top 1 [id],[name] from userinfo--獲取指定條數(shù)指定列

--distinct 去重復(fù)
select distinct(name) from userinfo 

--in /not in  包括/不包括
select * from userinfo where id in (1,2,3)--如同查詢 where id=1 or id=2 or id =3
select * from userinfo where id not in (1,2,3)

--between...and... 指定范圍內(nèi)
select * from userinfo where age between 10 and 20




--like 模糊查詢
--模糊查詢通常與通配符 % 占位符_ 選擇符[] 排除符[^] 使用
--% 通配符
select * from userinfo where name like '張%'--查詢所有姓張的
select * from userinfo where name like '%和%'--查詢名稱中含和的
select * from userinfo where name like '%軍'--查詢以軍結(jié)尾的人名

--占位符_
select * from userinfo where name like '_歡' --查以歡結(jié)尾，且只有兩個(gè)字的名字

--選擇符[]
select * from userinfo where name like '[張王李]%' --查詢姓張王李中的任何一個(gè)

--排除符[^]
select * from userinfo where name like '[^張王李]%'--查詢排除張王李的人員信息

--is null/is not null 是否為空，用于對(duì)數(shù)據(jù)null值的判斷
--數(shù)據(jù)庫中只能用 is null 判斷空值數(shù)據(jù)，is not null同理
select * from userinfo where name is null 

--order by [columnname] asc/desc 按照某一列，或者多列排序
select * from userinfo order by sex,age asc --按照性別和姓名排序 

--group by--分組，一般配合聚合函數(shù)使用
select sex, count(*) as 人數(shù) from userinfo group by sex--統(tǒng)計(jì)男女分別有多少人

--having，對(duì)分組進(jìn)行篩選
--篩選總分大于200分的學(xué)生的名字
select name,sum(score) as 總分 from userinfo group by name having sum(score)>200

--[注意]having 和where 都是用來篩選用的 having 是篩選組 而where是篩選記錄 ; 
--用having就一定要和group by連用，用group by不一有having
--SQL中增加having 子句的原因是，where關(guān)鍵字無法與聚合函數(shù)一起使用

--case when..then判斷
--用法1，使用case when 判斷分?jǐn)?shù)，新增一列 等級(jí)

select id ,name,course,score,
              case when score >=90 then '優(yōu)秀'
                   when score <90 and score>60 then '及格'
                   else '不及格'
              end as 評(píng)級(jí) 
from userinfo order by score asc

--注意case when ... then...end是配套使用的

--用法2,將指定列的值進(jìn)行判斷，新增一列 文理方向
select id ,name ,course,score,
               case course when '數(shù)學(xué)' then '理科'
                           when '語文' then '文科'
                           else '不知道'
               end as '文理方向'
from userinfo order by score asc

with 的使用
with tempuserinfo as (select * from userinfo where age>50)
select * from tempuserinfo 
--with的作用，可以將查詢結(jié)果作為一個(gè)臨時(shí)結(jié)果集，提供查詢

5.3 系統(tǒng)函數(shù)（重點(diǎn)聚合函數(shù)）

聚合函數(shù)

--聚合函數(shù)，都是用于統(tǒng)計(jì)數(shù)值使用，相對(duì)使用起來比較簡(jiǎn)單
--1 count()  統(tǒng)計(jì)數(shù)量
--2 sum()    統(tǒng)計(jì)和
--3 avg()    統(tǒng)計(jì)平均值
--4 max()    統(tǒng)計(jì)最高分
--5 min()    統(tǒng)計(jì)最低分

select count(*),sum(score),avg(score),max(score),min(score) from userinfo

NEWID生成唯一標(biāo)識(shí)

#newid 無參數(shù)
#返回一個(gè)GUID(全局唯一表示符）值
例如：select newid
返回：2E6861EF-F4DB-4FFE-86EB-637482FE982J2

--將id為1 的用戶信息id 更改為唯一標(biāo)識(shí)
update userinfo set id =newid() where id =1;

?isnumeric（任意表達(dá)式)

#isnumeric（任意表達(dá)式)
#判斷表達(dá)式是否為數(shù)值類型或者是否可以轉(zhuǎn)換成數(shù)值。
#是：返回1，不是：返回0
select isnumeric(11)             --返回 1
select isnumeric('123rr')        --返回 0 
select isnumeric('123')          --返回 1

?isdate(任意表達(dá)式)

#isdate（任意表達(dá)式）
#確定輸入表達(dá)式是否為有效目期或可轉(zhuǎn)成有效的日期:
#是：返回1，不是：返回0
例如：
setect isdate(getdate())        --返回 1
select isdate('2013-01-02')     --返回 1
select isdate('198')            --返回 0

ISNULL函數(shù)用法

ISNULL(value1,value2) 如果value1為空，則將value2的值返回，不為空，返回自己的值

--實(shí)例，查詢?nèi)藛T表，將性別為NULL的均賦值為保密
select name,isnull(sex,'保密') from userinfo

--注意：
--value1 :一般是字段名
--value1 要和value2的數(shù)據(jù)類型一致

ROW_NUMBER() 排名函數(shù)

row_number()就是給每一條記錄分配一個(gè)數(shù)字，從1 開始遞增，用于生產(chǎn)序號(hào)

--示例1 
row_number() over (order by createtime desc)
#根據(jù)創(chuàng)建時(shí)間降序，再為降序以后的每條記錄返回一個(gè)序號(hào)

--示例2 
row_number() over (partition by sex order by createtime desc)
#根據(jù)sex分組，在分組內(nèi)部根據(jù)createtime排序，
#而此函數(shù)計(jì)算的值就表示每組內(nèi)部排序后的順序編號(hào)（組內(nèi)連續(xù)的唯一的)

--示例3select
select *,row_number() over (order by createtime desc) as 序號(hào) from userinfo
#將userinfo 表格查詢結(jié)果集中新增一列序號(hào)，并且該結(jié)果集按照創(chuàng)建時(shí)間降序排列

--示例4
select *,row_number() over (partition by sex order by createtime desc) as 序號(hào) 
from userinfo
#將userinfo首先按照性別分組，然后按照時(shí)間排序?yàn)榻Y(jié)果集新增一序號(hào)列
#注意：分組后，每組的序號(hào)均是從1開始的

OFFSET/FETCH NEXT

1 在在SQL Server 2012版本中，T-SQL在Order By子句中新增 Offset-Fetch子句
2 用于從有序的結(jié)果集中，跳過一定數(shù)量的數(shù)據(jù)行，獲取指定數(shù)量的數(shù)據(jù)行，從而達(dá)到數(shù)據(jù)行分頁的目的
3 分頁效率比row_number()高
4 Offset子句必須在Order By 子句以后執(zhí)行，F(xiàn)etch子句必須在Offset子句以后執(zhí)行；

--示例
--使用Offset跳過指定數(shù)目的數(shù)據(jù)行
select * from userinfo order by id offset 2 rows
--查詢用戶名中按照id 排序，跳過前2條記錄的所有數(shù)據(jù)

--使用Offset-Fetch跳過指定數(shù)目的數(shù)據(jù)行以后，獲取指定數(shù)目的數(shù)據(jù)行
select * from userinfo order by id offset 10 rows  fetch next 10 rows only
--查詢用戶表中按照id排序，跳過前10條數(shù)據(jù)，獲取后面的10條數(shù)據(jù)

5.4 子查詢

--什么叫子查詢
--在查詢語句中的查詢語句，稱之為子查詢

--子查詢分別兩種
--1 嵌套子查詢，位于form 前面
select *,（select max（[score]） from [scoretb]） from [student];


--相關(guān)子查詢，位于where后面
--查詢成績(jī)表，只要前5名中的3,4,5
select top 3 * from [scoretb] where [stuid] not in 
(select top 2 [stuid] from [scoretb] order by [score] desc) 
order by [score] desc;

--all(),any的使用
--all()和any()基本都用于子查詢中，其作用相當(dāng)于某些聚合函數(shù)的作用
--all相當(dāng)于 max; any 相當(dāng)于 min
--all和any的括號(hào)中只能放子查詢語句
--使用all
select * from [user1] where [age] >all (select [age] from [user2] );
--使用max
select * from [user1] where [age] >(select max(age) from [user2])

--exists 和 not exists
--1 exists 和not exixts 后面括號(hào)中放 子查詢語句，
--用于檢測(cè)子查詢語句的查詢結(jié)果是否存在，如果存在返回true,反之，false
select * from user1 where 
exists(select * from user1 where stu_name='123');


--where 1=1 多用于sql 拼接時(shí)的占位
select * from user1 where 1=1 --后面接其他條件

5.5 分頁實(shí)例?

#【分頁方式1】必須帶有主鍵Id，且主鍵Id是標(biāo)識(shí)列，必須是自增的

declare @pagesize int; 
select @pagesize=10;--每頁10條

declare @pageindex int;
select @pageindex=3;--當(dāng)前頁碼 

select top (@pagesize) * from userinfo where id not in
( select top (paesize*(paeindex-1) id from userinfo order by id )
order by id

#【分頁方式2】沒有主鍵的時(shí)候，就需要使用row_number將表中所有的數(shù)據(jù)加上一列序號(hào)
# 然后按照該序號(hào)排序，進(jìn)行分頁（只支持2005版本以上的）

declare @pagesize int; 
select @pagesize=10;

declare @pageindex int; 
select @pageindex=3;

select top (@pagesize) * from
( select row_number() over(order by id) as rownumber,* from userinfo ) as temptb
where rownumber>((@pagesize)*((@pageindex)-1))


#【分頁方式3】 要求必須在sq1server2012版本之后方可支持

declare @pagesize int: 
select @pagesize=10;

declare @pageindex int; 
select @pageindex=10:

select * from userinfo order by id  offset(@pagesize*(@pageindex-1)) rows 
fetch next (@pagesize) rows only

5.5 行轉(zhuǎn)列，列轉(zhuǎn)行

#【行轉(zhuǎn)列】
#比如有這樣的一張表，記錄的是學(xué)生所有學(xué)課的分?jǐn)?shù)
#數(shù)據(jù)列是 id name course score ，課程有語文，數(shù)學(xué)，物理(學(xué)課有限)
#正常查詢這張表，則是可以根據(jù)name 排序，但是無法將同一個(gè)學(xué)習(xí)的成績(jī)并到一行展示
#這時(shí)候就需要用到行轉(zhuǎn)列

select name,
isnu11(sum(case course when '語文' then score end),0) as '語文', 
isnu11(sum(case course when '數(shù)學(xué)' then score end),0) as '數(shù)學(xué)', 
isnu11(sum(case course when '物理' then score end),0) as '物理', 
from score group by name

--通過case when ..then 的條件進(jìn)行判斷和賦值，創(chuàng)建學(xué)課的新列，
--再利用group by 和sum(),去除 多余無用的數(shù)據(jù)


#【列轉(zhuǎn)行】
# 比如有這樣的一張表，記錄的是學(xué)生所有學(xué)課的分?jǐn)?shù)
#數(shù)據(jù)列是 id name yuwen_score shuxue_score wuli_score  
#如果我們想查詢各個(gè)學(xué)課的成績(jī)，那么就需要列轉(zhuǎn)行（與上面需求相反）

with temptb as
(
select id,[name],course='語文',score=[yuwen_score] from score
union a11
select id,[name],course='數(shù)學(xué)',score=[shuxue_score] from score
union a11
select id,[name],course='物理',score=[wuli_score] from score
)

select * from temptb where score<>0 order by name desc 

5.6?多表查詢?

1 聯(lián)合查詢（又稱簡(jiǎn)易內(nèi)連接），多張表同時(shí)操作，內(nèi)存消耗大，但是速度快

--示例
select * from student as a,score as b where a.id=b.stuid

--查詢指定列
 select id,name,score,class from student as a,score as b where a.id=b.stuid

--student 顯示所有，score顯示指定字段
 select student.*,score from student as a,score as b where a.id=b.stuid

?2 連接查詢,一張張表進(jìn)行操作，內(nèi)存消耗少，但是耗時(shí)

#【連接查詢】

#【內(nèi)連接】
# inner join ...on....  無主從之分，只顯示連接表中有關(guān)聯(lián)的信息
# inner join ...on... 可以省略inner 直接使用join ...on...
select * from student inner join score on student.id=score.stuid

#外連接-左連接  有主表從表之分，主表顯示所有信息，從表顯示關(guān)聯(lián)信息;左邊主表
select * from student left join score on student.id=score.stuid

#外連接-右連接 同理，右邊主表
select * from student right join score on student.id=score.stuid

#全連接，無主從之分，所有的信息均顯示，沒有的信息使用null 補(bǔ)上
select * from student full outer join score on student.id=score.stuid

3 union/union all

--union/union all
--union 使用的時(shí)候默認(rèn)會(huì)去除重復(fù)的數(shù)據(jù)
--union all 不會(huì)去除重復(fù)的數(shù)據(jù)
--要求union的表結(jié)構(gòu)需要一一對(duì)應(yīng)
select * from teacher
union
select * from emp

#當(dāng)有多張表的時(shí)候，后面直接接著 使用 union即可
#注意：如果使用union，表1，表2 分別有3條數(shù)據(jù)，其中有1條重復(fù)，那么結(jié)果就是5條；
#      如果使用union all 結(jié)果就是會(huì)6條

?4 注意：select 語句執(zhí)行順序大致是：

? ? ?where（數(shù)據(jù)查詢） -> group by（數(shù)據(jù)編組） -> having（結(jié)果過濾）

? ? ?-> order by（排序）====》最后使用聚合函數(shù)將數(shù)據(jù)結(jié)果進(jìn)行統(tǒng)計(jì)

5.7遞歸查詢

#菜單目錄表 有id ,menuName,parentId三個(gè)字段
--輸入id,得出下面的子節(jié)點(diǎn)

--案例1
declare @menuid int;
set @menuid=2; --查詢菜單id 為2的所有子節(jié)點(diǎn)


with Con(Id,MenuName,ParentId,level )as
(
select Id,MenuName,ParentId,level=1 from [menuinfo] WHERE Id=@menuid 
union a11
select a.Id,a.MenuName,a.ParentId,level=level+1 from [menuinfo] as a 
join Con on a.ParentId=con.Id
)
select Id,MenuName,ParentId,level from Con


--由父項(xiàng)遞歸下級(jí)
with cte(Id,MenuName,ParentId)
as
(
--父項(xiàng)
select Id,MenuName,ParentId from menuinfo where ParentId = 450
union all
--遞歸結(jié)果集中的下級(jí)
select t.Id,t.ParentId,t.MenuName from menuinfo as t
inner join cte as c on t.ParentId= c.Id
)
select Id,MenuName,ParentId from cte


------------------------------


--由子級(jí)遞歸父項(xiàng)
with cte(Id,MenuName,ParentId)
as
(
--下級(jí)父項(xiàng)
select Id,MenuName,ParentId from menuinfo where Id = 450
union all
--遞歸結(jié)果集中的父項(xiàng)
select t.id,t.parentid,t.text from treeview as t
inner join cte as c on t.Id = c.ParentId
)
select Id,MenuName,ParentId from cte

遞歸查詢可參考：https://blog.csdn.net/qq_40640228/article/details/106588313文章來源地址http://www.zghlxwxcb.cn/news/detail-406366.html

到了這里，關(guān)于SQL Server數(shù)據(jù)庫基礎(chǔ)知識(shí)匯總的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！