--not null約束字段時候。會創建一個系統內置的約束鍵值，并且這種非空判斷，通過索引查詢實現 --的，索引默認創建一個系統索引
create unique index STUsno
on student(sno)
create unique index COUcno
on course(cno)
create unique index SCno
on sc(sno asc,cno desc)
drop index SCno on sc
--顯示表的數據和索引的碎塊信息 DBCC SHOWCONTIG
--插入數據 select * from student
alter table student alter column sno char(10)
insert into student values('10021','張三','男',20,'計科系')
insert into student values('10022','王朝','女',18,'軟件')
insert into student values('10023','朱元璋','男',20,'管理')
insert into student values('10024','劉徹','男',18,'軍事')
insert into student values('10025','劉表','男',20,'商學系')
insert into student values('10026','白居易','男',19,'文法')
insert into student values('10027','李清照','女',24,'文法')
select * from course insert into course values('001','數據庫','005',4)
insert into course values('002','高等數學','',2)
insert into course values('003','信息系統','001',4)
insert into course values('004','操作系統','006',2)
insert into course values('005','數據結構','007',3)
insert into course values('006','數據處理','',2)
insert into course values('007','C語言','006',5)
select * from sc insert into sc values('10021','002',100)
insert into sc values('10021','001',88)
insert into sc values('10021','006',100)
insert into sc values('10021','007',68)
insert into sc values('10022','002',100)
insert into sc values('10023','005',30)
insert into sc values('10024','002',100)
insert into sc values('10024','006',56)
select * from student --查詢操作
--查詢 select * from student select * from course select * from sc
--去掉重復行 select distinct sno from sc
--格式化查詢
select sname as '姓名',2013-sage as '出生日期' from student
select sname,'出生日期',2013-sage from student
select 姓名=sname,出生日期=2013-sage from student
--條件查詢
select * from course where ccredit>3
select * from course where ccredit between 2 and 5
select * from course where ccredit> 2 and ccredit5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
--匹配查詢
select * from student where sname like '劉__'
select * from student where sname like '_表__'
select * from student where sname like '%表%'
--算術元算查詢
select grade*(1+0.2) as 總成績,grade/(10) as 績點 from sc
--分組函數查詢
select COUNT(*) as 總人數 from student
select COUNT(distinct sno) as '選修的總人數' from sc
select AVG(grade) as '平均成績' from sc where sno='10021'
select MAX(grade) as 'MAX成績' from sc where sno='10021'
select MIN(grade) as 'MIN成績' from sc where sno='10021'
select SUM(grade) as '總成績' from sc where sno='10021'
select SUM(grade)/COUNT(grade) as '平均成績' from sc where sno='10021'
select SUM(grade) as '總成績' from sc group by sno having sum(grade)>100 -
-連接查詢、
--等值連接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
--自身連接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno
select B.sname as '同一個系' from student A,student B where A.sname='白居易' and A.sdept=B.sdept
--外連接
select A.*,B.* from student A left join sc B on A.sno=B.sno
select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
--復合條件連接
select * from sc select * from course
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade>99 and B.cno='002'
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade>99 and B.cno='002'
--字符串連接查詢
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade60) group by sname
--子查詢
select * from student where sage>(select AVG(sage) from student)
--是否存在的查詢
select * from student where exists(select * from sc where sno=student.sno)
select * from student where not exists(select * from sc where sno=student.sno)
--sql創建用戶 sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
--權限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
--視圖的創建
create view VIEW_STUGrade(學號,姓名,課程,成績)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept='軟件'
--查看視圖
select * from VIEW_STUGrade
--視圖修改
alter view VIEW_STUGrade(學號,姓名,課程,成績)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept='軟件'
with check option
--更新失敗后不影響視圖查看
--視圖更新
update VIEW_STUGrade set 姓名='王超' where 學號='10022' select * from student where sno='10022'
/* 1,可更新視圖： a,單個基本表導出的 2，不可更新視圖 a 兩個以上基本表導出的 b 視圖字段來自表達式或者函數 c 嵌套查詢的表 d 分組子句使用distinct */
--刪除視圖 drop view VIEW_STUGrade
--高級sql編程
--數據類型1,int 2,smallint 3,tinyint (0--255) 4,bigint 5char固定長度800.如：學號，姓名 6，varchar可變長度小于800 7,text 2GB 8，nvarchar1--4000 */
--運算符和通配符
select GETDATE()-1 昨天,GETDATE() 今天,GETDATE()+1 明天
select 5912
select 59|12
select 59^12
--模糊查詢
select * from student where sname like '%劉%'
select * from student where sno like '1002[5-9]'
--控制流程語句
declare @name char(10) set @name='司馬相如'
print @name
--輸出一個表達式，不能進行查詢 select @name
--輸出多個表達式
declare @a nvarchar(50),@b nvarchar(50)
set @a=33 set @b=34 ---簡寫select@a=33，@b=34
if @a>@b
print '最小值是：'+@a
else
print '最大值是：'+@b
--waitfor間隔一段時間執行
waitfor delay '00:00:04' print '推遲4秒執行'
waitfor time '17:45:50' print '等待這一時刻執行'
--創建函數
CREATE FUNCTION GetTime ( @date1 datetime, @date2 datetime )
RETURNS TABLE
AS RETURN (
select datediff(dd,@date1,@date2) 日差,datediff(mm,@date1,@date2) 月差, datediff(yy,@date1,@date2) 年差
)
--創建存儲過程，
--查看
GO create proc [dbo].[sel] (
@sno char(10)
)
as
select * from student where sno=@sno
exec sel @sno='10021'
--查看
GO create proc sel2
as
select * from student
exec sel2
--修改
GO create proc updat @sno char(10), @sex char(2)
as
update student set sex=@sex where sno=@sno
select * from student exec updat @sno='10021', @sex='女'
--刪除
GO create proc dele @sno char(10)
as
delete student where sno=@sno
select * from student
exec dele @sno='10029'
--插入
GO create proc inser @sno char(10), @sname char(20), @sex char(2), @sage smallint, @sdept char(15)
as
insert into student values(@sno,@sname,@sex,@sage,@sdept)
exec inser @sno='10029', @sname='tom', @sex='男', @sage=100, @sdept='sc' select * from student
--查詢操作
--查詢
select * from student select * from course select * from sc
--去掉重復行 select distinct sno from sc
--格式化查詢
select sname as '姓名',2013-sage as '出生日期' from student
select sname,'出生日期',2013-sage from student
select 姓名=sname,出生日期=2013-sage from student
--條件查詢
select * from course where ccredit>3
select * from course where ccredit between 2 and 5
select * from course where ccredit> 2 and ccredit5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
--匹配查詢
select * from student where sname like '劉__'
select * from student where sname like '_表__'
select * from student where sname like '%表%'
--算術元算查詢
select grade*(1+0.2) as 總成績,grade/(10) as 績點 from sc
--分組函數查詢
select COUNT(*) as 總人數 from student
select COUNT(distinct sno) as '選修的總人數' from sc select AVG(grade) as '平均成績' from sc where sno='10021'
select MAX(grade) as 'MAX成績' from sc where sno='10021'
select MIN(grade) as 'MIN成績' from sc where sno='10021'
select SUM(grade) as '總成績' from sc where sno='10021'
select SUM(grade)/COUNT(grade) as '平均成績' from sc where sno='10021'
select SUM(grade) as '總成績' from sc group by sno having sum(grade)>100
--連接查詢、 --等值連接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
--自身連接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno select B.sname as '同一個系' from student A,student B where A.sname='白居易' and A.sdept=B.sdept
--外連接
select A.*,B.* from student A left join sc B on A.sno=B.sno select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
-復合條件連接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade>99 and B.cno='002'
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade>99 and B.cno='002'
--字符串連接查詢
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade60) group by sname
--子查詢
select * from student where sage>(select AVG(sage) from student)
--是否存在的查詢
select * from student where exists(select * from sc where sno=student.sno)
select * from student where not exists(select * from sc where sno=student.sno)
--sql創建用戶
sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
--權限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
--視圖的創建
create view VIEW_STUGrade(學號,姓名,課程,成績)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept='軟件'
--查看視圖
select * from VIEW_STUGrade
--視圖修改
alter view VIEW_STUGrade(學號,姓名,課程,成績) as select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept='軟件'
with check option
--更新失敗后不影響視圖查看 --視圖更新
update VIEW_STUGrade set 姓名='王超' where 學號='10022' select * from student where sno='10022'
/* 1,可更新視圖： a,單個基本表導出的 2，不可更新視圖 a 兩個以上基本表導出的 b 視圖字段來自表達式或者函數 c 嵌套查詢的表 d 分組子句使用distinct */
--刪除視圖 drop view VIEW_STUGrade
--觸發器
use Studets
GO create trigger insert_Tri
ON student after
insert as print '有新數據插入！'
GO create trigger update_Tri
on student after
update as print '有數據更新！'
GO create trigger delete_Tri
on student after
delete as print '有數據刪除！'
--修改觸發器
GO alter trigger delete_Tri
on student after delete
as
if '王帥' in (select sname from deleted)
print '該信息不許刪除！'
rollback transaction
--執行存儲過程查看觸發器使用情況
exec sel @sno='10021'
exec inser @sno='10029', @sname='王帥', @sex='男', @sage=25, @sdept='國貿'
exec updat @sno='10029', @sex='女'
exec dele @sno='10029'
--查看,修改,刪除觸發器
/* sp_*+觸發器名稱
sp_helptext：觸發器正文信息 sp_help:查看一般信息，觸發器名稱，屬性，創建時間，類型 sp_depends：引用或指定表的所有觸發器 sp_helptrigger：指定信息 */ sp_help delete_Tri
sp_helptext delete_Tri
sp_depends delete_Tri
sp_helptrigger student
--刪除觸發器
drop trigger delete_Tri
