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
–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觸發器
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
–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))
–聯合結果集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
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
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
獨立子查詢：子查詢可以直接獨立運行. 查詢比“王八”年齡大的學員信息 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
開啟事務：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
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
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
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)
–創建局部臨時表 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
