MySQL基礎知識點彙總

本文給大家彙總介紹了mysql的23個基礎的知識點，這些都是學習mysql的必備知識，小夥伴們可以參考下。

1.什麼是SQL語句

sql語言：結構化的查詢語言。（Structured Query Language），是關聯式資料庫管理系統的標準語言。

它是一種解釋語言：寫一句執行一句，不需要整體編譯執行。

語法特點：

1.沒有“ ”，字串使用‘ ‘包含
2.沒有邏輯相等，賦值和邏輯相等都是=
3.型別不再是最嚴格的。任何資料都可以包含在‘ ‘以內
4.沒有bool值的概念，但是在檢視中可以輸入true/false
5.它也有關係運算子：> < >= <= = <> != ,它返回一個bool值
6.它也有邏輯運算子： ！(not) &&(and) ||(or)
7.它不區別大小寫

2.使用sql語句建立資料庫和表

語法：

create database 資料庫名稱
on primary --預設在主檔案組上
(
name='邏輯名稱_data' , --當你發現它不是一句完整的sql語句，而僅僅是一個處理結構中的某一句的時候，就需要新增 ,
size=初始大小,--數值不包含在‘'以內
filegrowth=檔案增長 ,
maxsize=最大容量,
filename='物理路徑'
)
log on
(
name='邏輯名稱_log' , --當你發現它不是一句完整的sql語句，而僅僅是一個處理結構中的某一句的時候，就需要新增 ,
size=初始大小,--數值不包含在‘'以內
filegrowth=檔案增長 ,
maxsize=最大容量, --一般來說日誌檔案不限制最大容量
filename='物理路徑'
)

–判斷資料庫檔案是否已經存在 ：資料庫的記錄都儲存在master庫中的sysdatabases表中
–自動切換當前資料庫
–使用程式碼開啟外圍應該配置器
execsp_configure 'show advanced options',1
RECONFIGURE
execsp_configure 'xp_cmdshell',1
RECONFIGURE

–自定義目錄  xp_cmdshell可以建立出目錄   ‘mkdir f:\project’：指定建立目錄
exec xp_cmdshell ‘mkdir f:\project’

use master
–exists 函式判斷（）中的查詢語句是否返回結果集，如果返回了結果集則得到true,否則得到false

if exists( select* fromsysdatabases wherename='School')
  dropdatabaseSchool --刪除當前指定名稱的資料庫
createdatabaseSchool
onprimary
(
 name='School_data',--邏輯名稱.說明最多能夠儲存100mb資料，如果沒有限制就可以將硬碟儲存滿
 size=3mb,--初始大小
 maxsize=100mb,--最大容量
 filegrowth=10%,--檔案增長一次增長10%
 filename='f:\project\School_data.mdf'
)

–建立檔案組
filegroup mygroup
(
 name='School_data1',--邏輯名稱.說明最多能夠儲存100mb資料，如果沒有限制就可以將硬碟儲存滿
 size=3mb,--初始大小
 maxsize=100mb,--最大容量
 filegrowth=10%,--檔案增長一次增長10%
 filename='F:\qiyi\School_data1.ndf'
)
log on
(
 name='School_log',--邏輯名稱
 size=3mb,--初始大小
 --maxsize=100mb,--最大容量
 filegrowth=10%,--檔案增長一次增長10%
 filename='f:\project\School_log.ldf'
),
(
 name='School_log1',--邏輯名稱
 size=3mb,--初始大小
 --maxsize=100mb,--最大容量
 filegrowth=10%,--檔案增長一次增長10%
 filename='F:\qiyi\School_log1.ldf'
)

3.建立資料表

語法：

create table 表名
(
欄位名稱 欄位型別 欄位特徵(是否為null,預設值 標識列 主鍵 唯一鍵 外來鍵 check約束),
欄位名稱 欄位型別 欄位特徵(是否為null,預設值 標識列 主鍵 唯一鍵 外來鍵 check約束)
)
建立老師表Teacher :Id、Name、Gender、Age、Salary、Birthday

use School
if exists(select* fromsysobjects wherename='Classes')
 droptableClasses
createtableClasses
(
 Classid intidentity(1,1),
 ClassName nvarchar(50) notnull
)
if exists(select* fromsysobjects wherename='teacher')
 droptableteacher
createtableTeacher
(
 Id intidentity(1,1),--可以同時建立多個特徵，用空格 分隔開。 identity是標識列，第一個引數是種子，第二個是增量
Namenvarchar(50) notnull,-- not null標記它的值不能為null--不能不填寫
ClassId intnotnull,
 Gender bitnotnull,
Age int,
Salary money, --如果不標記為 not null.那麼就相當於標記了null
Birthday datetime
)

4.資料完整性約束

實體完整性：實體就是指一條記錄。這種完整性就是為了保證每一條記錄不是重複記錄。是有意義的

主鍵：非空和唯一.一個表只有一個主鍵，但是一個主鍵可以是由多個欄位組成的 組合鍵
標識列：系統自動生成，永遠不重複
唯一鍵：唯一，但是可以為null,只能null一次
域完整性：域就是指欄位，它是為了保證欄位的值是準和有效，合理值

型別 是否null，預設值，check約束，關係
自定義完整性：

check約束 ， 儲存過程 觸發器
引用完整性：一個表的某個欄位的值是引用自另外一個表的某個欄位的值。引用的表就是外來鍵表，被引用的表就是主鍵表

1.建立引用的欄位型別必須一致
2.建立引用的欄位的意義一樣
3.建立主外來鍵關係的時候選擇 外來鍵表 去建立主外來鍵關係
4.建立主外來鍵關係的欄位在主表中必須是主鍵或者唯一鍵

5.對於操作的影響

①.在新增資料時，先新增主鍵表再新增外來鍵表資料
②.在刪除的時候先外來鍵表資料再刪除主鍵表資料

級聯的操作：不建議使用：會破壞資料完整性
不執行任何操作：該報錯就報錯，該刪除就刪除
級聯：刪除主表記錄，從表引用該值的記錄也被刪除
設定null:刪除主表記錄，從表對應的欄位值設定為null,前提是可以為null
設定為default:刪除主表記錄，從表對應的欄位值設定為default,前提是可以為default
主鍵約束（PK Primary key）唯一鍵約束(UQ unique) 外來鍵約束(FK foreign key) 預設值約束(DF default) check約束(CK check)

語法：

alter table 表名
add constraint 字首_約束名稱 約束型別 約束說明（欄位 關係表示式 值)
use School
if exists(select * from sysobjects where name=’PK_Classes_Classid’)
alter table classes  drop constraint PK_Classes_Classid
alter table classes
add constraint PK_Classes_Classid primary key(classid)
–為id新增主鍵
alter table teacher
add constraint PK_teacher_id primary key(id)
–為name新增唯一鍵
alter table teacher
add constraint UQ_Teacher_Name unique(Name)
–同時建立salary的預設約束和age的check約束
alter table teacher
add constraint DF_Teacher_Salary default(5000) for salary,
constraint CK_Teacher_Age check(age>0 and age<=100)
–為teacher表的classid欄位建立主外來鍵
if exists(select * from sysobjects where name=’FK_Teacher_Classes_Classid’)
alter table teacher  drop constraint FK_Teacher_Classes_Classid
alter table teacher
with nocheck –不檢查現有資料
add constraint FK_Teacher_Classes_Classid foreign key(classid) references classes(classid)
–on delete set default  級聯操作
–不執行任何操作：該報錯就報錯，該刪除就刪除  –no action –預設選擇
–級聯：刪除主表記錄，從表引用該值的記錄也被刪除 –cascade
–設定null:刪除主表記錄，從表對應的欄位值設定為null,前提是可以為null   –set null
–設定為default:刪除主表記錄，從表對應的欄位值設定為default,前提是可以為default  –set default

5.四中基本字元型別說明

–len(引數) –獲取指定引數內容的字元個數
select LEN(‘abcd’) 【4】執行結果
select LEN(‘中華人民共和國’) 【7】

–DataLength(引數)：獲取指定內佔據的位元組數–空間大小
select DataLength(‘abcd’) 【4】
select DataLength(‘中華人民共和國’) 【14】

–char型別：當空間分配後，不會因為儲存的內容比分配的空間小就回收分配的空間。但是如果儲存的內容超出了指定的空間大小，就會報錯，當你儲存的內容的長度變化區間不大的時候可以考慮使用char
select LEN(char) from CharTest 【2】
select DataLength(char) from CharTest 【10】

–varchar  var–變化的：當你儲存的內容小於分配的空間的時候，多餘的空間會自動收縮。但是如果儲存的內容超出了指定的空間大小，就會報錯 當儲存的內容波動區間比較大時候使用varchar
select LEN(varchar) from CharTest 【2】
select DataLength(varchar) from CharTest 【2】

–nchar–  n代表它是一個unicode字元。規定不管什麼樣的字元都佔據兩個位元組。  char:空間是固定的
select LEN(nchar) from CharTest 【10】
select DataLength(nchar) from CharTest 【20】

–nvarchar  n  var  char
select LEN(nvarchar) from CharTest 【2】
select DataLength(nvarchar) from CharTest 【4】

6.SQL基本語句

資料插入

呼叫方法 一 一對應原則：型別對應，數量對應，順序對應。

語法： 形參 實參

insert into 表名([欄位列表]) values(值列表) –資料必須要符合資料完整性
插入操作是單個表的操作
插入操作insert一次只能插入一條記錄
use School
–插入teacher所有欄位的資料.如果在表後沒有指定需要插入的欄位名稱，那麼就預設為所有欄位新增值
–但是一定需要注意的是：標識列永遠不能自定義值–不能人為插入值
–僅當使用了列列表並且 IDENTITY_INSERT 為 ON 時，才能為表’Teacher’中的標識列指定顯式值。
insert into Teacher values(‘張三’,5,1,30,4000,’1984-9-11′)
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values(‘張三’,5,1,30,4000,’1984-9-11′)
–不為可以為null的欄位插入值  :可以null的欄位可以不賦值
–列名或所提供值的數目與表定義不匹配
insert into Teacher(Name,ClassId,Gender,Age,Salary) values(‘李四’,5,1,30,4000)
–非空欄位一定需要賦值 ：不能將值 NULL 插入列 ‘Gender’，表 ‘School.dbo.Teacher’；列不允許有 Null 值。INSERT 失敗
insert into Teacher(Name,ClassId,Age,Salary) values(‘李四’,5,30,4000)
–為有預設值的欄位插入值：
–1.不寫這一列讓系統自動賦值
insert into Teacher(Name,ClassId,Gender,Age) values(‘王五’,5,1,30)
–指定 null或者default
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values(‘趙六’,5,1,30,default,null)
–資料必須完全符合表的完整性約束
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values(‘趙六1’,5,1,300,default,null)

–任意型別的資料都可以包含在”以內,     不包括關鍵字
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values(‘馬鵬飛’,’5′,’0′,’15’,default,null)
–但是字串值如果沒有包含在”以內.會報錯   列名 ‘蘭鵬’ 無效。
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values(‘蘭鵬’,’5′,’0′,’15’,default,null)
–但是數值組成的字串可以不使用”包含
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values(123,’5′,’0′,’15’,default,null)
–日期值必須包含在’‘以內，否則就是預設值
insert into Teacher(Name,ClassId,Gender,Age,Salary,Birthday) values(‘鄒元標2′,’5′,’0′,’15’,default,’1991-9-11′)
資料刪除

語法:

delete [from] 表名 where 條件
delete from Teacher where Age<20
–特點:
–1.刪除是一條一條進行刪除的
–2.每一條記錄的刪除都需要將操作寫入到日誌檔案中
–3.標識列不會從種子值重新計算,以從上次最後一條標識列值往下計算
–4.這種刪除可以觸發delete觸發器

–truncate table 表名 –沒有條件，它是一次性刪除所有資料
–特點：
–1.一次性刪除所有資料，沒有條件，那麼日誌檔案只以最小化的資料寫入
–2.它可以使用標識列從種子值重新計算
–3.它不能觸發delete觸發器
truncate table teacher
資料更新(資料修改)：一定需要考慮是否有條件

語法:

update 表名 set 欄位=值，欄位=值 。。where 條件
update Teacher set Gender=’true’
–修改時新增條件
update Teacher set Gender=0 where Id=20
–多欄位修改
update Teacher set ClassId=4,Age+=5,Salary=5000 where Id=22
–修改班級id=4，同時年齡》20歲的人員工資+500
update Teacher set Salary=Salary+500 where ClassId=4 and Age>20
資料檢索–查詢

語法： *代表所有欄位

select */欄位名稱列表 from 表列表
select StudentNo,StudentName,Sex,[Address] from Student
–可以為標題設定  別名，別名可以是中文別名
select StudentNo as 學號,StudentName 姓名,性別=Sex,[Address] from Student
–新增常量列
select StudentNo as 學號,StudentName 姓名,性別=Sex,[Address] ,國籍=’中華人民共和國’ from Student
–select的作用
–1.查詢
–2.輸出
select 1+1
–+是運算子,系統會自動為你做型別轉換
select 1+’1′
select ‘1’+1
–如果+兩邊都是字串,那麼它就是一字串連線符
select ‘1’+’1′
select ‘a’+1
–可以輸出多列值
select 1,2,34,3,545,67,567,6,7
–Top、Distinct
select * from Student
–top可以獲取指定的記錄數,值可以大於總記錄數.但是不能是負值
select top 100 * from Student
–百分比是取ceiling()
select top 10 percent * from Student

–重複記錄與原始的資料表資料無關,只與你查詢的結果集有關係 distinct可以去除結果集中的重複記錄–結果集中每一列的值都一樣
select distinct LoginPwd,Sex,Email from Student
select distinct Sex from Student

select的作用
–聚合函式：
–1.對null過濾
–2.都需要有一個引數
–3.都是返回一個數值
–sum()：求和:只能對數值而言,對字串和日期無效
–avg()：求平均值
–count()：計數：得到滿足條件的記錄數
–max()：求最大值:可以對任意型別的資料進行聚合，如果是字串就比較拼音字母進行排序
–min()：求最小值
–獲取學員總人數
select COUNT(*) from Student
–查詢最大年齡值
select  MIN(BornDate) from Student
select  max(BornDate) from Student

–查詢總分
select SUM(StudentResult) from Result where StudentNo=2
–平均分
select avg(StudentResult) from Result where SubjectId=1
–注意細節：
select  SUM(StudentName) from Student
select  SUM(BornDate) from Student

select  min(StudentName) from Student
select  max(StudentName) from Student

–查詢學號，姓名，性別，年齡，電話，地址 —查詢女生
select StudentNo,StudentName,Sex,BornDate,Address from Student where Sex=’女’ and BornDate >’1990-1-1′ and Address=’廣州傳智播客’
–指定區間範圍
select StudentNo,StudentName,Sex,BornDate,Address from Student where  BornDate >=’1990-1-1′ and BornDate<=’1993-1-1′
–between…and  >=  <=
select StudentNo,StudentName,Sex,BornDate,Address from Student where BornDate  between ‘1990-1-1’ and ‘1993-1-1′
–查詢班級id  1  3 5  7的學員資訊
select * from Student where ClassId=1 or ClassId=3 or ClassId=5 or ClassId=7
–指定具體的取值範圍–可以是任意型別的範圍.值的型別需要一致–可以相互轉換
select * from Student where ClassId in(1,3,’5′,7)
select * from Student where ClassId not in(1,3,’5′,7)

聚合函式
–帶條件的查詢-模糊查詢– 只針對字串而言

–查詢  姓 林 的女生資訊
–=是一種精確查詢，需要完全匹配
select * from Student where Sex=’女’ and StudentName=’林’
–萬用字元–元字元
–%：任意個任意欄位  window:*  正規表示式 ：.*
–_:任意的單個字元
–[]:代表一個指定的範圍，範圍可以是連續也可以是間斷的。與正規表示式完全一樣[0-9a-zA-Z].可以從這個範圍中取一個字元
–[^]:取反值
select * from Student where Sex=’女’ and StudentName=’林%’
–萬用字元必須在模糊查詢關鍵的中才可以做為萬用字元使用，否則就是普通字元
–like   像 。。。。一樣
select * from Student where Sex=’女’ and StudentName  like ‘林%’
select * from Student where Sex=’女’ and StudentName  like ‘林_’
–[]的使用  學號在11~15之間的學員資訊
select * from Student where StudentNo like ‘[13579]’

—處理null值
–null:不是地址沒有分配,而是不知道你需要儲存什麼值  所以null是指   不知道。但是=只能匹配具體的值，而null根本就不是一個值
select COUNT(email) from Student where Email !=null
select COUNT(email) from Student where Email  is null
select count(email) from Student where Email  is not null
–將null值替換為指定的字串值
select StudentName,ISNULL(Email,’沒有填寫電子郵箱’) from Student where ClassId=2

模糊查詢
–當你看到  每一個，，各自，不同，，分別  需要考慮分組
–查詢每一個班級的男生人數
–與聚合函式一起出現在查詢中的列，要麼也被聚合，要麼被分組
select classid,Sex,COUNT(*) from Student where Sex=’男’ group by ClassId,sex
–查詢每一個班級的總人數,顯示人數>=2的資訊
–1.聚合不應出現在 WHERE 子句中–語法錯誤
select ClassId ,COUNT(*) as num from Student where Email is not null   GROUP by ClassId having COUNT(*)>=2 order by num desc
–完整的sql查詢家庭
–5                            1                      2                                 3                                     4                                           6
–select 欄位列表 from 表列表  where 資料來源做篩選 group by 分組欄位列表 having 分組結果集做篩選 Order by  對結果集做記錄重排

select ClassId ,COUNT(*) as num from Student where Email is not null   GROUP by ClassId order by ClassId desc

–關於top的執行順序 排序之後再取top值
select top 1 ClassId ,COUNT(*) as num from Student  GROUP by ClassId  order by num desc

分組統計

7.型別轉換函式

–select :輸出為結果集–虛擬表
–print：以文字形式輸出  只能輸出一個字串值.

print 1+’a’
select 1,2

select * from Student

–型別轉換
–Convert(目標型別,源資料,[格式]) –日期有格式
print ‘我的成績是：’+convert(char(3),100)

print ‘今天是個大日子：’+convert(varchar(30),getdate(),120)
select getdate()
select len(getdate())

–cast(源資料  as  目標型別)  它沒有格式
print ‘我的成績是：’+cast(100 as char(3))

8.日期函式

–getdate():獲取當前伺服器日期
select GETDATE()
–可以在源日期值是追加指定時間間隔的日期數
select DATEADD(dd,-90,GETDATE())
–dateDiff:找到兩個日期之間指定格式的差異值
select StudentName,DATEDIFF(yyyy,getdate(),BornDate) as age from Student order by  age
–DATENAME:可以獲取日期的指定格式的字串表現形式
select DATENAME(dw,getdate())
–DATEPART:可以獲取指定的日期部分
select cast(DATEPART(yyyy,getdate()) as CHAR(4))+’-‘ +cast(DATEPART(mm,getdate()) as CHAR(2))+’-‘ +cast(DATEPART(dd,getdate()) as CHAR(2))

9.數學函式

–rand:隨機數:返回0到1之間的數，理論上說可以返回0但是不能返回1
select RAND()
–abs:absolute:取絕對值
select ABS(-100)
–ceiling:獲取比當前數大的最小整數
select CEILING(1.00)
–floor:獲取比當前數小的最大整數
select floor(1.99999)
power:
select POWER(3,4)
–round():四捨五入.只關注指定位數後一位
select ROUND(1.549,1)
–sign:正數==1  負數 ==-1  0=0
select SIGN(-100)

select ceiling(17*1.0/5)

10.字串函式

–1.CHARINDEX –IndexOf():能夠返回一個字串在源字串的起始位置。找不到就返回0，如果可以找到就返回從1開始的索引–沒有陣列的概念
–第一個引數是指需要查詢的字串，第二個是源字串，第三個引數是指從源字元的那個索引位置開始查詢
select CHARINDEX(‘人民’,’中華人民共和國人民’,4)
–LEN()：可以返回指定字串的字元個數
select LEN(‘中華人民共和國’)
–UPPER():小寫字母轉換為大寫字母  LOWER():大寫轉小寫
select LOWER(UPPER(‘sadfasdfa’))
–LTRIM:去除左空格  RTIRM:去除右空格
select lTRIM(RTRIM(‘                   sdfsd             ‘))+’a’
–RIGHT:可以從字串右邊開始擷取指定位數的字串  如果數值走出範圍，不會報錯，只會返回所有字串值,但是不能是負值
select RIGHT(‘中華人民共和國’,40)
select LEFT(‘中華人民共和國’,2)
–SUBSTRING()
select SUBSTRING(‘中華人民共和國’,3,2)
–REPLACE 第一個引數是源字串，第二個引數是需要替換的字串，第三個引數是需要替換為什麼
select REPLACE(‘中華人民共和國’,’人民’,’居民’)
select REPLACE(‘中        華      人民       共        和       國’,’ ‘,”)
–STUFF:將源字串中從第幾個開始，一共幾個字串替換為指定的字串
select STUFF(‘中華人民共和國’,3,2,’你懂的’)

declare @email varchar(50)=’sudyfsagfyas@12fasdf6.fsadfdsaf’
select CHARINDEX()
select LEFT(@email,CHARINDEX()

–使用right
select right(@email,len(@email)-CHARINDEX())
–使用substring
select SUBSTRING(@email,CHARINDEX())
–使用stuff
select STUFF(@email,1,CHARINDEX()

11.聯合結果集union

–聯合結果集union
select * from Student where Sex=’男’
–union
select * from Student where Sex=’女’

–聯合的前提是：
–1.列的數量需要一致:使用 UNION、INTERSECT 或 EXCEPT 運算子合並的所有查詢必須在其目標列表中有相同數目的表示式
–2.列的型別需要可以相互轉換
select StudentName,Sex from Student –在字串排序的時候，空格是最小的，排列在最前面
union
select cast(ClassId as CHAR(3)),classname from grade

–union和union all的區別
–union是去除重複記錄的
–union all不去除重複 ：效率更高，因為不需要判斷記錄是否重複，也沒有必須在結果庥是執行去除重複記錄的操作。但是可以需要消耗更多的記憶體儲存空間
select * from Student where ClassId=2
union all
select * from Student where ClassId=2

–查詢office這科目的全體學員的成績，同時在最後顯示它的平均分，最高分，最低分
select ‘ ‘+cast(StudentNo as CHAR(3)),cast(SubjectId as CHAR(2)),StudentResult from Result where SubjectId=1
union
select ‘1’,’平均分’,AVG(StudentResult) from Result where SubjectId=1
union
select ‘1’,’最高分’,max(StudentResult) from Result where SubjectId=1
union
select ‘1’,’最低分’,min(StudentResult) from Result where SubjectId=1

–一次性插入多條資料
–1.先將資料複製到另外一個新表中，刪除源資料表，再將新表的資料插入到源資料表中
–1.select */欄位  into 新表 from 源表
–1.新表是系統自動生成的，不能人為建立，如果新表名稱已經存在就報錯
–2.新表的表結構與查詢語句所獲取的列一致，但是列的屬性消失，只保留非空和標識列。其它全部消失，如主鍵，唯一鍵，關係，約束，預設值
select * into newGrade from grade

truncate table grade
select *  from newGrade
–select * into grade from newGrade
–2.insert into  目標表  select 欄位列表/* from  資料來源表
–1、目標表必須先存在，如果沒有就報錯
–2.查詢的資料必須符合目標表的資料完整性
–3.查詢的資料列的數量和型別必須的目標的列的數量和物件完全對應
insert into grade select classname from newGrade
delete from admin
–使用union一次性插入多條記錄
–insert into 表(欄位列表)
–select 值。。。。 使用者自定義資料
–union
–select 值 。。。。
insert into Admin
select ‘a’,’a’
union all
select ‘a’,’a’
union all
select ‘a’,’a’
union all
select ‘a’,null

12.CASE函式用法

相當於switch case—c#中的switch…case只能做等值判斷
這可以對欄位值或者表示式進行判斷，返回一個使用者自定義的值，它會生成一個新列。
2.要求then後面資料的型別一致
1.第一種做等值判斷的case..end

case 欄位或者表示式
when .值..then .自定義值
when .值..then .自定義值
…..
else 如果不滿足上面所有的when就滿足這個else
end
–顯示具體班級的名稱
select StudentNo,StudentName,
case ClassId  –如果case後面接有表示式或者欄位，那麼這種結構就只能做等值判斷，真的相當於switch..case
when 1 then ‘一班’
when 2 then ‘2班’
when 3 then ‘3班’
when null  then ‘aa’ –不能判斷null值
else  ‘搞不清白’
end,
sex
from Student
–2.做範圍判斷，相當於if..else，它可以做null值判斷
–case  –如果沒有表示式或者欄位就可實現範圍判斷
— when  表示式  then 值   –不要求表示式對同一欄位進行判斷
— when  表示式  then 值
— …..
–else  其它情況
–end
select StudentNo,StudentName,
case
when BornDate>’2000-1-1′ then ‘小屁孩’
when BornDate>’1990-1-1′ then ‘小青年’
when BornDate>’1980-1-1′ then ‘青年’
–when Sex=’女’  then ‘是女的’
when BornDate is null then ‘出生不詳’
else  ‘中年’
end
from Student

–百分制轉換為素質教育  90 -A   80–B  70 –C  60 –D  <60 E  NULL–沒有參加考試
select StudentNo,SubjectId,
case
when StudentResult>=90 then ‘A’
when StudentResult>=80 then ‘B’
when StudentResult>=70 then ‘C’
when StudentResult>=60 then ‘D’
when StudentResult is null then ‘沒有參加考試’
else ‘E’
end 成績,
ExamDate
from Result

13.IF ELSE語法

1.沒有｛｝，使用begin..end.如果後面只有一句，可以不使用begin..end包含
2.沒有bool值，只能使用關係運算子表示式
3.也可以巢狀和多重
4.if後面的（）可以省略

declare @subjectname nvarchar(50)=’office’ –科目名稱
declare @subjectId int=(select Subjectid from Subject where ) –科目ID
declare @avg int –平均分
set @avg=(select AVG(StudentResult) from Result where  and StudentResult is not null) –獲取平均分
print @avg
if @avg>=60
begin
print ‘成績不錯，輸出前三名：’
select top 3 * from Result where  order by StudentResult desc
end
else
begin
print ‘成績不好，輸出後三名：’
select top 3 * from Result where  order by StudentResult
end

14.WHILE迴圈語法

1.沒有{},使用begin..end
        2.沒有bool值，需要使用條件表示式
        3.可以巢狀
        4.也可以使用break,continue

15.子查詢

子查詢：一個查詢中包含另外一個查詢。被包含的查詢就稱為子查詢，包含它的查詢就稱父查詢。
1.子查詢的使用方式：使用（）包含子查詢
2.子查詢分類：

獨立子查詢：子查詢可以直接獨立執行.
查詢比“王八”年齡大的學員資訊
select * from Student where BornDate<(select BornDate from Student where StudentName=’王八’)
相關子查詢：子查詢使用了父查詢中的結果
–子查詢的三種使用方式
–1.子查詢做為條件,子查詢接在關係運算子後面  >  < >= <= = <> !=,如果是接這關係運算子後面，必須保證 子查詢只返回一個值
–查詢六期班的學員資訊
select * from Student where ClassId=(select ClassId from grade where classname=’八期班’)
–子查詢返回的值不止一個。當子查詢跟隨在 =、!=、<、<=、>、>= 之後，或子查詢用作表示式時，這種情況是不允許的。
select * from Student where ClassId=(select ClassId from grade)
–查詢八期班以外的學員資訊
–當子查詢返回多個值(多行一列),可以使用in來指定這個範圍
select * from Student where ClassId in(select ClassId from grade where classname<>’八期班’)
–當沒有用 EXISTS 引入子查詢時，在選擇列表中只能指定一個表示式。如果是多行多列或者一行多列就需要使用exists
–使用 EXISTS 關鍵字引入子查詢後，子查詢的作用就相當於進行存在測試。外部查詢的 WHERE 子句測試子查詢返回的行是否存在
select * from Student where  EXISTS(select * from grade)
select * from Student where  ClassId in(select * from grade)

–2.子查詢做為結果集–
select top 5 * from Student –前五條
–使用top分頁
select top 5 * from Student where StudentNo not in(select top 5 studentno from Student)
–使用函式分頁  ROW_NUMBER() over(order by studentno),可以生成行號,排序的原因是因為不同的排序方式獲取的記錄順序不一樣
select ROW_NUMBER() over(order by studentno),* from Student
–查詢擁有新生成行號的結果集  注意:1.子查詢必須的別名  2.必須為子查詢中所有欄位命名,也就意味著需要為新生成的行號列命名
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>0 and temp.id<=5
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>5 and temp.id<=10
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>10 and temp.id<=15

–3.子查詢還可以做為列的值
select (select studentname from student where studentno=result.studentno),(select subjectname from subject where subjectid=result.SubjectId), StudentResult from Result

–使用Row_number over()實現分頁
–1.先寫出有行號的結果集
select ROW_NUMBER() over(order by studentno),* from Student
–2.查詢有行號的結果集 子查詢做為結果集必須新增別名，子查詢的列必須都有名稱
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where id>0 and id<=5
–查詢年齡比“廖楊”大的學員，顯示這些學員的資訊
select * from Student where BornDate<(select BornDate from Student where StudentName=’廖楊’)
–查詢二期班開設的課程
select * from Subject where ClassId=(select ClassId from grade where classname=’二期班’)
–查詢參加最近一次“office”考試成績最高分和最低分
–1查詢出科目 ID
select subjectid fromSubjectwhereSubjectName=’office’–2.查詢出這一科目的考試日期select MAX(ExamDate)fromResultwhereSubjectId=(select subjectid fromSubjectwhereSubjectName=’office’)–3,寫出查詢的框架select MAX(StudentResult),MIN(StudentResult)fromResultwhereSubjectId=()andExamDate=()–4.使用子查詢做為條件select MAX(StudentResult),MIN(StudentResult)fromResultwhereSubjectId=(select subjectid fromSubjectwhereSubjectName=’office’)andExamDate=(select MAX(ExamDate)fromResultwhereSubjectId=(select subjectid fromSubjectwhereSubjectName=’office’))

16.表連線Join

–1.inner join :能夠找到兩個表中建立連線欄位值相等的記錄
–查詢學員資訊顯示班級名稱
select Student.StudentNo,Student.StudentName,grade.classname
from Student
inner join grade on Student.ClassId=grade.ClassId
–左連線: 關鍵字前面的表是左表，後面的表是右表
–左連線可以得到左表所有資料，如果建立關聯的欄位值在右表中不存在，那麼右表的資料就以null值替換
select PhoneNum.*,PhoneType.*
from   PhoneNum
left join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId
–右連線: 關鍵字前面的表是左表，後面的表是右表
–右連線可以得到右表所有資料，如果建立關聯的欄位值在右左表中不存在，那麼左表的資料就以null值替換
select PhoneNum.*,PhoneType.*
from   PhoneNum
right join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId
–full join :可以得到左右連線的綜合結果–去重複
select PhoneNum.*,PhoneType.*
from   PhoneNum
full join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId

17.事務

一種處理機制。以事務處理的操作，要麼都能成功執行，要麼都不執行。

事務的四個特點 ACID：

A：原子性：事務必須是原子工作單元；對於其資料修改，要麼全都執行，要麼全都不執行。它是一個整體，不能再拆分
C：一致性：事務在完成時，必須使所有的資料都保持一致狀態。。某種程度的一致
I：隔離性：事務中隔離，每一個事務是單獨的請求將單獨的處理，與其它事務沒有關係，互不影響
D：永續性：如果事務一旦提交，就對資料的修改永久保留
使用事務：

將你需要操作的sql命令包含在事務中。

1.在事務的開啟和事務的提交之間
2.在事務的開啟和事務的回滾之間

三個關鍵語句：

開啟事務：begin transaction
提交事務：commit transaction
回滾事務：rollback transaction
declare @num int =0 –記錄操作過程中可能出現的錯誤號
begin transaction
update bank set cmoney=cmoney-500 where name=’aa’
set @num=@num+@@ERROR
–說明這一句的執行有錯誤  但是不能在語句執行的過程中進行提交或者回滾
–語句塊是一個整體，如果其中一句進行了提交或者回滾，那麼後面的語句就不再屬於當前事務，
–事務不能控制後面的語句的執行

updatebank setcmoney=cmoney+500 wherename='bb'
set@num=@num+@@ERROR
select* frombank
 if(@num<>0 ) --這個@@ERROR只能得到最近一一條sql語句的錯誤號
  begin
  print '操作過程中有錯誤，操作將回滾'
  rollbacktransaction
 end
 else
  begin
  print '操作成功'
  committransaction
 end

–事務一旦開啟，就必須提交或者回滾
–事務如果有提交或者回滾，必須保證它已經開啟

18.檢視

檢視就是一張虛擬表，可以像使用子查詢做為結果集一樣使用檢視。

select * from vw_getinfo
使用程式碼建立檢視。

語法：

create view vw_自定義名稱
as
查詢命令
go
–查詢所有學員資訊
if exists(select * from sysobjects where name=’vw_getAllStuInfo’)
drop view vw_getAllStuInfo
go –上一個批處理結果的標記
create view vw_getAllStuInfo
as
–可以透過聚合函式獲取所以記錄數
select top (select COUNT(*) from Student) Student.StudentNo,Student.StudentName,grade.ClassId,grade.classname from Student
inner join grade on Student.ClassId=grade.ClassId  order by StudentName –檢視中不能使用order by
–select * from grade –只能建立一個查詢語句
–delete from grade where ClassId>100 –在檢視中不能包含增加刪除修改
go

–使用檢視。。就像使用表一樣
select * from vw_getAllStuInfo
–對檢視進行增加刪除和修改操作–可以對檢視進行增加刪除和修改操作,只是建議不要這麼做:所發可以看到：如果操作針對單個表就可以成功，但是如果 多張的資料就會報錯：不可更新，因為修改會影響多個基表。
update vw_getAllStuInfo set classname=’asdas’ ,studentname=’aa’ where studentno=1

19.觸發器

觸發器：執行一個可以改變表資料的操作（增加刪除和修改），會自動觸發另外一系列（類似於儲存過程中的模組）的操作。

語法：

create trigger tr_表名_操作名稱
on 表名 after|instead of 操作名稱
as
go
if exists(select * from sysobjects where name=’tr_grade_insert’)
drop trigger tr_grade_insert
go
create trigger tr_grade_insert
on grade for  insert  —為grade表建立名稱為tr_grade_insert的觸發器，在執行insert操作之後觸發
as
declare @cnt int
set @cnt = (select count(*) from student)
select * ,@cnt from student
select * from grade
go
–觸發器不是被呼叫的，而是被某一個操作觸 發的，意味著執行某一個操作就會自動觸發 觸發器
insert into grade values(‘fasdfdssa’)
—替換觸 發器：本來需要執行某一個操作，結果不做了，使用觸 發器中的程式碼語句塊進行替代

if exists(select * from sysobjects where name=’tr_grade_insert’)
drop trigger tr_grade_insert
go
create trigger tr_grade_insert
on grade instead of insert  —為grade表建立名稱為tr_grade_insert的觸發器，在執行insert操作之後觸發
as
declare @cnt int
set @cnt = (select count(*) from student)
select * ,@cnt from student
select * from grade
go

insert into grade values(‘aaaaaaaaaaaa’)
go

—觸 發器的兩個臨時表：
–inserted: 操作之後的新表:所有新表與原始的物理表沒有關係，只與當前操作的資料有關
–deleted:操作之前的舊錶：所有新表與原始的物理表沒有關係，只與當前操作的資料有關

if exists(select * from sysobjects where name=’tr_grade_insert’)
drop trigger tr_grade_insert
go
create trigger tr_grade_insert
on grade after insert
as
print ‘操作之前的表：操作之前，這一條記錄還沒有插入，所以沒有資料’
select * from deleted
print ‘操作之後的表：已經成功插入一條記錄，所有新表中有一條記錄’
select * from inserted
go
–測試：
insert into grade values(‘aaaaa’)

if exists(select * from sysobjects where name=’tr_grade_update’)
drop trigger tr_grade_update
go
create trigger tr_grade_update
on grade after update
as
print ‘操作之前的表：儲存與這個修改操作相關的沒有被修改之前的記錄’
select * from deleted
print ‘操作之後的表：儲存這個操作相關的被修改之後 記錄’
select * from inserted
go
–測試
update grade set classname=classname+’aa’ where  ClassId>15

if exists(select * from sysobjects where name=’tr_grade_delete’)
drop trigger tr_grade_delete
go
create trigger tr_grade_delete
on grade after delete
as
print ‘操作之前的表：儲存與這個修改操作相關的沒有被刪除之前的記錄’
select * from deleted
print ‘操作之後的表：儲存這個操作相關的被刪除之後 記錄–沒有記錄’
select * from inserted
go

–測試
delete from grade where ClassId>15

20.儲存過程

儲存過程就相當於c#中的方法
引數，返回值，引數預設值，引數：值的方式呼叫
在呼叫的時候有三個對應：型別對應，數量對應，順序對應。

建立語法：

create proc usp_使用者自定義名稱
對應方法的形參 –(int age, out string name)
as
對應方法體:建立變數,邏輯語句,增加刪除修改和查詢..return返回值
go
呼叫語法：

exec 儲存過程名稱 實參，實參，實參 …
–獲取所有學員資訊
if exists(select * from sysobjects where name=’usp_getAllStuInfo’)
drop proc usp_getAllStuInfo
go
create procedure usp_getAllStuInfo
as
select * from Student
go
–呼叫儲存過程，獲取的有學員資訊
execute usp_getAllStuInfo

–exec sp_executesql  ‘select * from Student’

–查詢指定性別的學員資訊
go
if exists(select * from sysobjects where name=’usp_getAllStuInfoBySex’)
drop proc usp_getAllStuInfoBySex
go
create procedure usp_getAllStuInfoBySex
@sex nchar(1) –性別  引數不需要declare
as
select * from Student where 
go
–呼叫儲存過程，獲取指定性別的學員資訊
Exec usp_getAllStuInfoBySex ‘女’

–建立儲存過程獲取指定班級和性別的學員資訊
go
if exists(select * from sysobjects where name=’usp_getAllStuInfoBySexandClassName’)
drop proc usp_getAllStuInfoBySexandClassName
go
create procedure usp_getAllStuInfoBySexandClassName
@classname nvarchar(50), –班級名稱
@sex nchar(1)=’男’–性別   有預設的引數建議寫在引數列表的最後
as
declare  @classid int —班級ID
set @classid=(select classid from grade where ) –透過引數班級名稱獲取對應的班級ID
select * from Student where  and 
go
–執行儲存過程獲取指定班級和性別的學員資訊
–exec usp_getAllStuInfoBySexandClassName ‘八期班’
exec usp_getAllStuInfoBySexandClassName default, ‘八期班’  –有預設值的引數可以傳遞default
exec usp_getAllStuInfoBySexandClassName @classname=’八期班’    –也可以透過引數=值的方式呼叫
exec usp_getAllStuInfoBySexandClassName @classname=’八期班’  ,@sex=’女’
exec usp_getAllStuInfoBySexandClassName @classname=’八期班’,@sex=’女’

–建立儲存過程,獲取指定性別的學員人數及總人數
go
if exists(select * from sysobjects where name=’usp_getCountBySexandClassName’)
drop proc usp_getCountBySexandClassName
go
create procedure usp_getCountBySexandClassName
@cnt int=100 output, –output標記說明它是一個輸出引數。output意味著你向伺服器請求這個引數的值，那麼在執行的時候，伺服器發現這個引數標記了output，就會將這個引數的值返回輸出
@totalnum int =200output, –總人數
@className nvarchar(50), –輸入引數沒有預設值，在呼叫的時候必須傳入值
@sex nchar(1)=’男’–輸入引數有預設值，使用者可以選擇是否傳入值
as
declare  @classid int —班級ID
set @classid=(select classid from grade where ) –透過引數班級名稱獲取對應的班級ID
select * from Student where  and 
set @cnt= (select COUNT(*) from Student where  and ) –獲取指定班級和性別的總人數
set @totalnum=(select COUNT(*) from Student) —-獲取總人數
go
–呼叫儲存過程,獲取指定性別的學員人數及總人數
declare @num int,@tnum int
exec usp_getCountBySexandClassName @cnt=@num output ,@totalnum=@tnum output , @className=’八期班’
print @num
print @tnum
print ‘做完了’
—獲取指定班級的人數
if exists(select * from sysobjects where name=’usp_getCount’)
drop proc usp_getCount
go
create procedure usp_getCount
@className nvarchar(50)=’八期班’
as
declare @classid int=(select classid from grade where )
declare @cnt int
set @cnt =(select COUNT(*) from Student where )
–return 只能返回int整數值
–return ‘總人數是’+cast(@cnt as varchar(2))
return @cnt
go

–呼叫儲存過程，接收儲存過程的返回值
declare @count int
–set @count=(exec usp_getCount)
exec @count=usp_getCount ‘八期班’
print @countif exists(select*from sysobjects where name=’usp_getClassList’)
drop proc usp_getClassList
go
create procedure usp_getClassList
asselect classid,classname from grade
go

21.分頁儲存過程

if exists(select * from sysobjects where name=’usp_getPageData’)
drop proc usp_getPageData
go
create procedure usp_getPageData
@totalPage int output,–總頁數
@pageIndex int =1 ,–當前頁碼，預設是第一頁
@pageCount int =5 –每一頁顯示的記錄數
as
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>(@pageindex-1)*@pagecount and temp.id<=(@pageindex*@pagecount)
set @totalPage=CEILING((select COUNT(*) from Student)*1.0/@pageCount)
go

22.索引

select * from sysindexes

–create  index IX_Student_studentName
–on 表名(欄位名)

–clustered index:聚集索引  nonclustered index–非聚集索引
if exists(select * from sysindexes where name=’IX_Student_studentName’)
drop index student.IX_Student_studentName
go
create clustered index IX_Student_studentName
on student(studentname)

–如果是先建立主鍵再建立聚集索引就不可以，因為主鍵預設就是聚集索引
–但是如果先建立聚集索引，那麼還可以再建立主鍵，因為主鍵不一定需要是聚集的

23.臨時表

–建立區域性臨時表
create table #newGrade
(
classid int ,
classname nvarchar(50)
)
—區域性臨時表只有在當前建立它的會話中使用，離開這個會話臨時表就失效.如果關閉建立它的會話，那麼臨時表就會消失
insert into #newGrade select * from  grade
select * from #newGrade
select * into #newnewnew from grade
select * into newGrade from #newgrade

–建立全域性臨時表:只要不關閉當前會話，全域性臨時表都可以使用，但是關閉當前會話，全域性臨時表也會消失
create table ##newGrade
(
classid int ,
classname nvarchar(50)
)
drop table ##newGrade
select * into ##newGrade from grade
select * from ##newGrade

–建立表變數
declare @tb table(cid int,cname nvarchar(50))
insert into @tb select * from grade
select * from @tb