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語句創建數據庫和表

語法：

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

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

3.創建數據表

語法：

create table 表名
(
字段名稱 字段類型 字段特征(是否為null,默認值 標識列 主鍵 唯一鍵 外鍵 check約束),
字段名稱 字段類型 字段特征(是否為null,默認值 標識列 主鍵 唯一鍵 外鍵 check約束)
)
創建老師表Teacher :Id、Name、Gender、Age、Salary、Birthday

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,’你懂的’)

–sudyfsagfyas@12fasdf6.fsadfdsaf

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

–使用right
select right(@email,len(@email)-CHARINDEX(‘@’,@email))
–使用substring
select SUBSTRING(@email,CHARINDEX(‘@’,@email)+1,LEN(@email))
–使用stuff
select STUFF(@email,1,CHARINDEX(‘@’,@email),”)

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 SubjectName=@subjectname) –科目ID
declare @avg int –平均分
set @avg=(select AVG(StudentResult) from Result where SubjectId=@subjectId and StudentResult is not null) –獲取平均分
print @avg
if @avg>=60
begin
print ‘成績不錯，輸出前三名：’
select top 3 * from Result where SubjectId=@subjectId order by StudentResult desc
end
else
begin
print ‘成績不好，輸出后三名：’
select top 3 * from Result where SubjectId=@subjectId 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
–說明這一句的執行有錯誤  但是不能在語句執行的過程中進行提交或者回滾
–語句塊是一個整體，如果其中一句進行了提交或者回滾，那么后面的語句就不再屬于當前事務，
–事務不能控制后面的語句的執行

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 Sex=@sex
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 classname=@classname) –通過參數班級名稱獲取對應的班級ID
select * from Student where Sex=@sex and ClassId=@classid
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 classname=@classname) –通過參數班級名稱獲取對應的班級ID
select * from Student where Sex=@sex and ClassId=@classid
set @cnt= (select COUNT(*) from Student where Sex=@sex and ClassId=@classid) –獲取指定班級和性別的總人數
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 classname=@className)
declare @cnt int
set @cnt =(select COUNT(*) from Student where ClassId=@classid)
–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