sql 语句运用汇总.docx
《sql 语句运用汇总.docx》由会员分享,可在线阅读,更多相关《sql 语句运用汇总.docx(19页珍藏版)》请在冰豆网上搜索。
sql语句运用汇总
一、创建数据库
createdatabaseuserdb1
on
(name=userdb4_data,--数据文件的逻辑名称,注意不能与日志逻辑同名
filename='g:
\xygl\userdb4.mdf',--物理名称,注意路径必须存在
size=5,--数据初始长度为5M
maxsize=10,--最大长度为10M
filegrowth=1)--数据文件每次增长1M
logon
(name=userdb4_log,
filename='g:
\xygl\userdb4.ldf',
size=2,
maxsize=5,
filegrowth=1)
二、修改数据库
1、添加文件
alterdatabasetest
addfile
(
name=text1,--数据文件的逻辑名称,注意不能与日志逻辑同名
filename='g:
\xygl\userdb4.ndf',--物理名称,注意路径必须存在
size=5,--数据初始长度为5M
maxsize=10,--最大长度为10M
filegrowth=1
)
Go
2、添加事务日志文件
Alterdatabasetest
Addlogfile
(
Name=textlog1,
Filename=’F:
\练习日志、textlog1.ldf’
Size=5MB,
Maxsize=100MB,
Filegrowth=5MB
)
go
三、删除数据库
Dropdatabasetext
四、创建表
1、普通
CREATETABLEstudent
(
snochar(8)PRIMARYKEY,--学号(主键)
snamechar(8)NOTNULL,--姓名
sexchar
(2),--性别
nativechar(20),--籍贯
birthdaysmalldatetime,--出生日期
dnochar(6),--所在院系
spnochar(8),--专业代码(外键)
classnochar(4),--班级号
entimesmalldatetime,--入校时间
homevarchar(40),--家庭住址
telvarchar(40)--联系电话
)
2、外键、约束等
Foreingkey(typeID)referencesReaderType(TypeID)ondeletenoaction
——外键,不级联删除
Pricedecimal(7,2)nullcheck(Price>0)
——检查约束
五、修改表
1、修改属性
Uselibrary
Go
AltertableBook
AltercolumnPubCompvarchar(30)notnull
Go
2、添加或删除列
Uselibrary
Go
AltertableBook
AddISBNvarchar(17)null
Go
Uselibrary
Go
AltertableBook
DropcolumnISBN
Go
3、添加或删除约束
Uselibrary
Go
AltertableBorrow
AddconstraintPK_BHprimarykey(RID,BID,LendDate)
Go
——PK_BH为主键约束名
Uselibrary
Go
AltertableBorrow
DropconstraintPK_BH
Go
Uselibrary
Go
AltertableBorrow
AddconstraintFK_Borrow_BIDforeingkey(BID)
ReferencesBook(BID)
Go
Uselibrary
Go
AltertableBook
AddconstraintCK_Book_PRcheck(price>0)
Go
3、删除表
Uselibrary
Go
Droptabletext
4、添加数据行
Uselibrary
Go
InsertintoReaderTypevalues(1,’教师’,20,90)
Go
Uselibrary
Go
InsertReader(RID,Rname,TypeID,Email)
Values(‘200186010’,’张子健’,1,‘zhangzi@’)
Go
5、修改数据行
Uselibrary
Go
UpdateReaderType
SetLimitNum=LimitNum+2,LimitDays=LimitDays+5
WhereTypeName=’学生’
Uselibrary
Go
UpdateReader
SetLendnum=(
Selectcount(*)fromBorrow
WhereReturnDateisnullandReader.RID=Borrow.RID)
Go
6、删除数据行
UseEDUC
Go
DeletestudentIM
WhereSID=’2005216118’
Go
——删除某行
UseEDUC
Go
DeletestudentIM
——删除全部行
7、创建索引
UseEDUC
Go
CreatenonclusterdindexStudentindex2
Onstudent(Snameasc)
——创建一个不唯一、非聚集、索引键为Sname升序排列的索引
8、删除索引
UseEDUC
Go
DropindexStudent.studentindex
六、Select语句
1、普通
UseEDUC
Go
SelectSID,Sname,Sex
Fromstudent
WhereSex=’男’
2、top关键字限制返回行数
UseEDUC
Go
Selecttop5BID,Bname,Author//selecttop20percentSID,Sname,Sex,Birthday
FromBook
3、是否消除重复数据行
UseEDUC
Go
Selectdistinctspecialty
Fromstudent
4、通配符*
UseEDUC
Go
Select*fromreadertype
5、使用表达式计算列值
UseEDUC
Go
Selectbid,bname,author,pubcomp,price,price*0.9
//有精度要求:
表达式可写为str(price*0.9,6,2)
Frombook
6、自定义列标题
UseEDUC
GO
SelectBIDas读者编号,bnameas书名,authoras作者,
出版社=PubComp,原价=Price,折价=str(Price*0.9,6,2)+’元’
FromBook
7、列函数(聚集函数)
Uselibrary
Go
Selectcount(*)as册数
FromBook
WherepubComp=’高等教育出版社’
Uselibrary
Go
Selectcount(distinct(PubComp))as出版社个数
FromBook
Uselibrary
Go
Selectcount(price)as册数,
Max(price)as最高价,min(price)as最低价,
Sum(price)as总价值,str(sum(price*0.9),8,2)as折后总价值,
Str(avg(price),6,2)as平均价
FromBook
UseEDUC
Go
Selectstudent.SID,Sname,Grade
Fromstudent,sc,course
Wherestudent.SID=sc.SIDandsc.CID=course.CID
8、为基本表指定临时别名
UseEDUC
Go
Selectx.SID,Sname,Cname,Grade
Fromstudentasx,scasy,courseasz
Wherex.SID=y.SIDandy.CID=z.CID
9、日期、时间相关的
UseEDUC
Go
Select*fromstudent
Wheredatepart(year,getdate())-datepart(year,birthday)+1>24
AndSex=’女’
UseDEUC
Go
Select*fromstudent
Wherenot(datepart(year,getdate())-datepart(year,birthday)+1>24)andnot(Sex=’女’)
10、范围运算符
Uselibrary
Go
SelectBIDas图书编号,Bnameas书名,priceas定价
FromBook
Wherepricebetween10and15
11、模糊查询
UseEDUC
Go
Select*fromstudent
WhereSnamelike‘李%’
——通配符‘_’代表一个任意字符,‘%’代表任意多个字符
12、列表运算符
UseEDUC
Go
Select*fromstudent
WhereSIDIN(‘2005216007’,’2006216578’)
13、字句分组统计查询
Uselibrary
Go
SelectBook
GroupbyPubcomp
UseEDUC
Go
Select‘专业’=Speciality,’人数’=count(*)
Fromstudent
GroupbySpeciality
UseEDUC
Go
SelectSC.SID,student.Sname,’总成绩’=sum(grade)
FromSC,student
WhereSC.SID=student.SID
GroupbySC.SID,student.Sname
14、字句限定查询
UseEDUC
Go
SelectSC.SID,student.Sname,’总成绩’=SUM(grade)
FromSC.SID=student.Sname
WhereSC.SID=student.SID
GroupbySC.SID,student.Sname
HavingSUM(grade)>150——对学生总成绩进行筛选
15、orderby字句排序查询
Orderbysum(grade)desc——desc为降序,asc为升序
OrderbyPubcompasc,pricedesc——按照出版社升序,再按照价格降序排列
16、into语句保存查询
UseEDUC
Go
SelectSID.CID
Intostudent_course——创建新表
FromSC
六、连接查询
1、内连接
Uselibrary
Go
Selectreader.*,borrow.*
Fromreaderinnerjoinborrowonreader.RID=borrow.RID——等值连接
USElibrary
Go
SelectReader.RID,Reader.Rname,Readertype.TypeID,BID,LendDate,ReturnDate
FromReaderinnerjionBorrowonReader.RID=Borrow.RID
InnerjoinReadertypeonReader.TypeID=Readertype.TypeID
2、外连接
USELibrary
Go
SelectReader.*,Borrow.RID,BID
FromReaderleftouterjoinBorrow
OnReader.RID=Borrow.RID——左外链接
USELibrary
Go
SelectBorrowrightouterjionBook
OnBorrow.BID=Book.BID——右外连接
Uselibrary
Go
Selectreader.*,borrow.RID,borrow.BID
Fromborrowfullouterjoinreader
Onborrow.RID=reader.RID——全外连接
3、自连接
Uselibrary
Go
Selecta.Bname,a.Author,b.BID,b.Bname,b.Author
FromBookajoinBookbona.Bname=b.Bname
Uselibrary
Go
Selecta.BID,a.bname,a.Author
FromBookajoinBookbona.Bname=b.Bname
Wherea.Author<>b.Author——自连接加选择条件
UseEDUC
Go
Selectx.SID//消除重复行:
selectdistinctx.SIDas学号
FromSC.x,SC.y
Wherex.SID=y.SIDandx.CID<>y.CID
4、交叉连接
Uselibrary
Go
Selecttop5reader.*,borrow.RID,BID
Fromreadercrossjoinborrow
5、多表连接
Uselibrary
Go
Selectt1.RID,Rname,Bname,LendDate
Fromreadert1,joinBorrowt2ont1.RID=t2.RIDjoinBookt3ont2.BID=t3.BID
七、子查询
1、[not]in子查询
Uselibrary
Go
SelectdistinctRID
FromBorrow
WhereBIDin(selectBIDfromBookwherePubComp=’高等教育出版社’)
Uselibrary
Go
Select*
Fromreader
WhereRIDnotin(selectdistinctRIDfromBorrow)
2、比较子查询
Uselibrary
Go
Select*
FromBorrow
WhereRID>=all(selectRIDfromreader)——all比较子查询
Uselibrary
Go
SelectSname,Grade
FromStudentasxinnerjoinSCasyonx.SID=y.SID
WhereCID=any(selectCIDfromCoursewhereCname=’C语言程序设计’)
——any/some比较子查询
UseEDUC
Go
Select*
Fromstudent
Whereexists(select*fromSCwherestudent.SID=SC.SID)
——exists子查询
Uselibrary
Go
SelectdistinctRID
Fromborrow
Whereexists
(select*frombookwhereborrow.BID=book.BIDandPubComp=’人民出版社’)——exists子查询
Uselibrary
Go
Updatereader
Setlendnum=(selectcount(*)fromborrowwherereturndateidnullandreader.RID=borrow.RID)——子查询在其他语句中的使用
八、联合查询
Uselibrary
Go
Selectbanmefrombookwherepubcomp=’人民邮电出版社’
Union
Selectbnamefrombookwherepubcomp=’清华大学出版社’
Uselibrary
Go
Selectbnamefrombookwherepubcomp=’人民邮电出版社’
Unionall
Selectbnamefrombookwherepubcomp=’清华大学出版社’
OrderbyBID
Go——联合查询结果排序
UselibraryBIDas图书编号,Bnameas书名
Frombookwhereprice=(selectmin(price)frombook)
Go
九、创建视图
Uselibrary
Go
Createviewv1_books
Asselectdistinctauthor,bname
Frombook
Go
Uselibrary
Go
Createviewsreturndate(读者编号,姓名,图书编号,图书名,应还日期)
As
Selectreader.rid,reader.name,book.bid,book.bname,borrow.lenddate+readertype.limitdays
Fromreader
innerjoinBorrowonreader,rid=borrow.rid
Innerjoinreadertypeonreader.typeid=readertype.typeid
Innerjoinbookonborrow.bid=book.bid
Where(borrow.returndateisnull)
十、修改视图
Uselibrary
Go
Alterviewv1_books(作者,书名)
Asselctdistinctauthor,bname
Frombook
Go
十一、删除视图
Dropviewv1_books
十二、使用视图
1、添加数据行
UseEDUC
Go
Insertintoview_eb(sid,sname,sex,birthday,specialty)
Values(‘2005216322’,’李小燕’,‘女’,‘1986-9-6’,‘电子商务’)
Go
2、修改数据行
UseEDUC
Go
Updateview_eb
Setbirthday=’1989-4-27’
Wheresname=’郭洪亮’
Go
3、删除数据行
UseEDUC
Go
Deletefromview_eb
Wheresname=’李小燕’
Go