SQL Server 數據庫根本操作語句總結。本站提示廣大學習愛好者：（SQL Server 數據庫根本操作語句總結）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server 數據庫根本操作語句總結正文
--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
