Mysql基本常識點匯總。本站提示廣大學習愛好者：（Mysql基本常識點匯總）文章只能為提供參考，不一定能成為您想要的結果。以下是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表中
--主動切換以後數據庫
--應用代碼開啟核心應當設置裝備擺設器
exec sp_configure 'show advanced options' ,1 RECONFIGURE exec sp_configure 'xp_cmdshell',1 RECONFIGURE
--自界說目次 xp_cmdshell可以創立出目次 'mkdir f:\project'：指定創立目次
exec xp_cmdshell 'mkdir f:\project'
use master
--exists 函數斷定（）中的查詢語句能否前往成果集，假如前往了卻果集則獲得true,不然獲得false
if exists( select * from sysdatabases where name='School') drop database School --刪除以後指命名稱的數據庫 create database School on primary ( 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 * from sysobjects where name='Classes') drop table Classes create table Classes ( Classid int identity(1,1), ClassName nvarchar(50) not null ) if exists(select * from sysobjects where name='teacher') drop table teacher create table Teacher ( Id int identity(1,1),--可以同時創立多個特點，用空格 分離隔。 identity是標識列，第一個參數是種子，第二個是增量 Name nvarchar(50) not null,-- not null標志它的值不克不及為null--不克不及不填寫 ClassId int not null, Gender bit not null, 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)='[email protected]'
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
go
declare @subjectName nvarchar(50)='office' --科目稱號
declare @subjectId int--科目ID
declare @classid int =(select classid from Subject where SubjectName=@subjectName) --查詢以後科目屬於那一個班級
set @subjectId=(select SubjectId from Subject where SubjectName=@subjectName) --獲得科目ID
declare @totalCount int --總人數 :那一個班級須要測驗這一科目
set @totalCount=(select COUNT(*) from Student where ClassId=@classid)
print @totalcount --14
declare @unpassNum int --不合格人數
set @unpassNum=(select COUNT(distinct Studentno) from Result where SubjectId=@subjectId and StudentNo in(select StudentNo from Student where ClassId=@classid) and StudentResult<60)
while(@unpassNum>@totalCount/2)
begin
--履行輪回加分
update Result set StudentResult+=2 where SubjectId=@subjectId and StudentNo in(select StudentNo from Student where ClassId=@classid) and StudentResult<=98
--從新盤算不合格人數
set @unpassNum=(select COUNT(distinct Studentno) from Result where SubjectId=@subjectId and StudentNo in(select StudentNo from Student where ClassId=@classid) and StudentResult<60)
end
go
declare @subjectName nvarchar(50)='office' --科目稱號
declare @subjectId int--科目ID
declare @classid int =(select classid from Subject where SubjectName=@subjectName) --查詢以後科目屬於那一個班級
set @subjectId=(select SubjectId from Subject where SubjectName=@subjectName) --獲得科目ID
declare @totalCount int --總人數
set @totalCount=(select COUNT(*) from Student where ClassId=@classid)
print @totalcount --14
declare @unpassNum int --不合格人數
while(1=1)
begin
set @unpassNum=(select COUNT(distinct Studentno) from Result where SubjectId=@subjectId and StudentNo in(select StudentNo from Student where ClassId=@classid) and StudentResult<60)
if(@unpassNum>@totalCount/2)
update Result set StudentResult+=2 where SubjectId=@subjectId and StudentNo in(select StudentNo from Student where ClassId=@classid) and StudentResult<=98
else
break
end
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
--解釋這一句的履行有毛病 然則不克不及在語句履行的進程中停止提交或許回滾
--語句塊是一個全體，假如個中一句停止了提交或許回滾，那末前面的語句就不再屬於以後事務，
--事務不克不及掌握前面的語句的履行
update bank set cmoney=cmoney+500 where name='bb' set @num=@num+@@ERROR select * from bank if(@num<>0 ) --這個@@ERROR只能獲得比來逐個條sql語句的毛病號 begin print '操作進程中有毛病，操作將回滾' rollback transaction end else begin print '操作勝利' commit transaction 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 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
