小说阅读网,大主宰之灵路天蚕土豆,有声小说下载

復(fù)制代碼代碼如下:

--sql基本操作

--創(chuàng)建數(shù)據(jù)庫(kù)

create database Studets

--創(chuàng)建表

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 學(xué)號(hào) 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

--完整性約束實(shí)現(xiàn)

--sno 非空唯一，ssex檢查約束， sage默認(rèn)大小

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 )

--關(guān)聯(lián)表主鍵已經(jīng)存在，可以如下操作添加主鍵和外鍵約束

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]

--創(chuàng)建sc后，通過(guò)如下修改主外鍵

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)

--創(chuàng)建索引。

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

復(fù)制代碼代碼如下:

--not null約束字段時(shí)候。會(huì)創(chuàng)建一個(gè)系統(tǒng)內(nèi)置的約束鍵值，并且這種非空判斷，通過(guò)索引查詢實(shí)現(xiàn) --的，索引默認(rèn)創(chuàng)建一個(gè)系統(tǒng)索引

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

--顯示表的數(shù)據(jù)和索引的碎塊信息 DBCC SHOWCONTIG

--插入數(shù)據(jù) select * from student

alter table student alter column sno char(10)

insert into student values('10021','張三','男',20,'計(jì)科系')

insert into student values('10022','王朝','女',18,'軟件')

insert into student values('10023','朱元璋','男',20,'管理')

insert into student values('10024','劉徹','男',18,'軍事')

insert into student values('10025','劉表','男',20,'商學(xué)系')

insert into student values('10026','白居易','男',19,'文法')

insert into student values('10027','李清照','女',24,'文法')

select * from course insert into course values('001','數(shù)據(jù)庫(kù)','005',4)

insert into course values('002','高等數(shù)學(xué)','',2)

insert into course values('003','信息系統(tǒng)','001',4)

insert into course values('004','操作系統(tǒng)','006',2)

insert into course values('005','數(shù)據(jù)結(jié)構(gòu)','007',3)

insert into course values('006','數(shù)據(jù)處理','',2)

insert into course values('007','C語(yǔ)言','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

--去掉重復(fù)行 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 '%表%'

--算術(shù)元算查詢

select grade*(1+0.2) as 總成績(jī),grade/(10) as 績(jī)點(diǎn) from sc

--分組函數(shù)查詢

select COUNT(*) as 總?cè)藬?shù) from student

select COUNT(distinct sno) as '選修的總?cè)藬?shù)' from sc

select AVG(grade) as '平均成績(jī)' from sc where sno='10021'

select MAX(grade) as 'MAX成績(jī)' from sc where sno='10021'

select MIN(grade) as 'MIN成績(jī)' from sc where sno='10021'

select SUM(grade) as '總成績(jī)' from sc where sno='10021'

select SUM(grade)/COUNT(grade) as '平均成績(jī)' from sc where sno='10021'

select SUM(grade) as '總成績(jī)' 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 '同一個(gè)系' 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

--復(fù)合條件連接

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創(chuàng)建用戶 sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc

--權(quán)限分配和收回

grant select on student to bnc

select * from student

revoke select on student from bnc

--視圖的創(chuàng)建

create view VIEW_STUGrade(學(xué)號(hào),姓名,課程,成績(jī))

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(學(xué)號(hào),姓名,課程,成績(jī))

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 學(xué)號(hào)='10022' select * from student where sno='10022'

/* 1,可更新視圖： a,單個(gè)基本表導(dǎo)出的 2，不可更新視圖 a 兩個(gè)以上基本表導(dǎo)出的 b 視圖字段來(lái)自表達(dá)式或者函數(shù) c 嵌套查詢的表 d 分組子句使用distinct */

--刪除視圖 drop view VIEW_STUGrade

--高級(jí)sql編程

--數(shù)據(jù)類型1,int 2,smallint 3,tinyint (0--255) 4,bigint 5char固定長(zhǎng)度800.如：學(xué)號(hào)，姓名 6，varchar可變長(zhǎng)度小于800 7,text 2GB 8，nvarchar1--4000 */

--運(yùn)算符和通配符

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]'

--控制流程語(yǔ)句

declare @name char(10) set @name='司馬相如'

print @name

--輸出一個(gè)表達(dá)式，不能進(jìn)行查詢 select @name

--輸出多個(gè)表達(dá)式

declare @a nvarchar(50),@b nvarchar(50)

set @a=33 set @b=34 ---簡(jiǎn)寫select@a=33，@b=34

if @a>@b

print '最小值是：'+@a

else

print '最大值是：'+@b

--waitfor間隔一段時(shí)間執(zhí)行

waitfor delay '00:00:04' print '推遲4秒執(zhí)行'

waitfor time '17:45:50' print '等待這一時(shí)刻執(zhí)行'

--創(chuàng)建函數(shù)

CREATE FUNCTION GetTime ( @date1 datetime, @date2 datetime )

RETURNS TABLE

AS RETURN (

select datediff(dd,@date1,@date2) 日差,datediff(mm,@date1,@date2) 月差, datediff(yy,@date1,@date2) 年差

)

--創(chuàng)建存儲(chǔ)過(guò)程，

--查看

GO create proc [dbo].[sel] (

@sno char(10)

)

select * from student where sno=@sno

exec sel @sno='10021'

--查看

GO create proc sel2

select * from student

exec sel2

--修改

GO create proc updat @sno char(10), @sex char(2)

update student set sex=@sex where sno=@sno

select * from student exec updat @sno='10021', @sex='女'

--刪除

GO create proc dele @sno char(10)

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)

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

--去掉重復(fù)行 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 '%表%'

--算術(shù)元算查詢

select grade*(1+0.2) as 總成績(jī),grade/(10) as 績(jī)點(diǎn) from sc

--分組函數(shù)查詢

select COUNT(*) as 總?cè)藬?shù) from student

select COUNT(distinct sno) as '選修的總?cè)藬?shù)' from sc select AVG(grade) as '平均成績(jī)' from sc where sno='10021'

select MAX(grade) as 'MAX成績(jī)' from sc where sno='10021'

select MIN(grade) as 'MIN成績(jī)' from sc where sno='10021'

select SUM(grade) as '總成績(jī)' from sc where sno='10021'

select SUM(grade)/COUNT(grade) as '平均成績(jī)' from sc where sno='10021'

select SUM(grade) as '總成績(jī)' 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 '同一個(gè)系' 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

-復(fù)合條件連接

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創(chuàng)建用戶

sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc

--權(quán)限分配和收回

grant select on student to bnc

select * from student

revoke select on student from bnc

--視圖的創(chuàng)建

create view VIEW_STUGrade(學(xué)號(hào),姓名,課程,成績(jī))

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(學(xué)號(hào),姓名,課程,成績(jī)) 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 學(xué)號(hào)='10022' select * from student where sno='10022'

--刪除視圖 drop view VIEW_STUGrade

--觸發(fā)器

use Studets

GO create trigger insert_Tri

ON student after

insert as print '有新數(shù)據(jù)插入！'

GO create trigger update_Tri

on student after

update as print '有數(shù)據(jù)更新！'

GO create trigger delete_Tri

on student after

delete as print '有數(shù)據(jù)刪除！'

--修改觸發(fā)器

GO alter trigger delete_Tri

on student after delete

if '王帥' in (select sname from deleted)

print '該信息不許刪除！'

rollback transaction

--執(zhí)行存儲(chǔ)過(guò)程查看觸發(fā)器使用情況

exec sel @sno='10021'

exec inser @sno='10029', @sname='王帥', @sex='男', @sage=25, @sdept='國(guó)貿(mào)'

exec updat @sno='10029', @sex='女'

exec dele @sno='10029'

--查看,修改,刪除觸發(fā)器

/* sp_*+觸發(fā)器名稱

sp_helptext：觸發(fā)器正文信息 sp_help:查看一般信息，觸發(fā)器名稱，屬性，創(chuàng)建時(shí)間，類型 sp_depends：引用或指定表的所有觸發(fā)器 sp_helptrigger：指定信息 */ sp_help delete_Tri

sp_helptext delete_Tri

sp_depends delete_Tri

sp_helptrigger student

--刪除觸發(fā)器

drop trigger delete_Tri

您可能感興趣的文章:

Sql Server 和 Access 操作數(shù)據(jù)庫(kù)結(jié)構(gòu)Sql語(yǔ)句小結(jié)
SQLSERVER 2005中使用sql語(yǔ)句對(duì)xml文件和其數(shù)據(jù)的進(jìn)行操作(很全面)
六條比較有用的MySQL數(shù)據(jù)庫(kù)操作的SQL語(yǔ)句小結(jié)
用SQL語(yǔ)句添加刪除修改字段、一些表與字段的基本操作、數(shù)據(jù)庫(kù)備份等
sql語(yǔ)句之?dāng)?shù)據(jù)操作介紹
淺析SQL數(shù)據(jù)操作語(yǔ)句

標(biāo)簽：新鄉(xiāng) 江蘇貴州重慶呼和浩特上海昭通拉薩

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《SQL Server 數(shù)據(jù)庫(kù)基本操作語(yǔ)句總結(jié)》，本文關(guān)鍵詞 SQL,Server,數(shù)據(jù)庫(kù),基本操作,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。