SQL Server 資料庫基本動作陳述式總結_MsSql

複製代碼 代碼如下:

--sql基本操作

--建立資料庫

create database Studets

--建立表

create table student ( sno char(5), sname char(20), ssex char(2), sage smallint, sdept char(15) )

create table course ( cno char(3), cname char(30), cpno char(3), ccredit smallint )

create table sc ( sno char(5), cno char(3), grade int )

--查看錶資訊

select * from student select sno as 學號 from student select * from course select * from sc

--修改表

--插入列

alter table student add  scome  datetime

--修改列的欄位類型 alter table student alter column scome  char(50)

--刪除 --刪除列

alter table student drop column scome

--刪除表 drop table student drop table course drop table sc

--完整性條件約束實現

--sno 非空唯一，ssex檢查約束， sage預設大小

create table student ( sno char(5) not null unique, sname char(20), sex char(2), sage smallint default 20, sdept char(15), constraint sex check(sex in('男','女')), )

--刪除表的約束 alter table student drop  constraint ssex

--添加欄位約束 alter table student add constraint ssex check(sex in('男','女'))

--添加主鍵約束 alter table student add constraint PK_SNO primary key(sno) create table course ( cno char(3) not null unique, cname char(30), cpno char(3), ccredit smallint )

--關聯表主鍵已經存在，可以如下操作添加主鍵和外鍵約束

alter table course add constraint PK_CNO primary key(cno), constraint FK_CPNO foreign key(cpno) REFERENCES sc(cno)

create table sc

(

sno char(5) foreign key references student(sno),

cno char(3) foreign key references course(cno),

grade int,

constraint PK_SC primary key(sno,cno)

)

ALTER TABLE [dbo].[sc] DROP CONSTRAINT [FK__sc__sno__0F975522]

ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]

ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]

--建立sc後，通過如下修改主外鍵

alter table sc add constraint PK_SC primary key(sno,cno),

constraint FK_SNO foreign key(sno) references student(sno),

constraint FK_CNO foreign key(cno) references course(cno)

--建立索引。

分為聚簇索引（clustered物理順序）和非聚簇索引（nonclustered邏輯順序，可多個）

複製代碼 代碼如下:

--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 ccredit<5

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 grade<60) 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 59&12

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 ccredit<5

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 grade<60) 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