SQL server个人整理超级实用版.docx
- 文档编号:9010619
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:21
- 大小:21.17KB
SQL server个人整理超级实用版.docx
《SQL server个人整理超级实用版.docx》由会员分享,可在线阅读,更多相关《SQL server个人整理超级实用版.docx(21页珍藏版)》请在冰豆网上搜索。
SQLserver个人整理超级实用版
select*fromstuden
----------------------------更改数据中人的年龄,每个人的年龄都加---------
updatestudent
setsage=sage+1
select*fromstudent
----------------------------新建一个表-------------------
createtabletongji2班("学号"char(10)primarykey,"姓名"char(10))
select*fromtongji2班
insertintotongji2班---------------插入字符型的量要用单引号'',而且是要在英文状态下输入
values('i','高健')
values('02','于海秋')
insertintotongji2班
values('01','于海秋')
-------------求出系别的名称,不显示重复的---
selectdistinctsdeptfromstudent
selctdistinctsdeptfromstudent-----错误!
!
!
!
蓝色的是命令语句,而绿色的是变量或者表格
-------------求各系别的平均年龄--------
selectsdept,AVG(sage)fromstudent
groupbysdept
------------在表中增加列名--------
altertabletongji2班
add总分char(10)-----增加"总分"列名的时候,必须要说明数据类型"char()"
go
select*fromtongji2班
-------------删除一列---------注释时,只能用"------"符号,与R语言中"#"则不同
altertabletongji2班
dropcolumn总分----删除时,语法为"dropcolumn"
go
select*fromtongji2班
-------------删除某一行记录---
deletefromtongji2班
where"学号"='01'----列名无论在创建时,还是在读取数据时,都应该加双引号"",列当中的值,加单引号''
-------------删除一个表----
createtable经济("学号"char(10),"姓名"char(10))
insertinto经济
values(1,'梁建华')
select*from经济
droptable经济-----------删除表的命令
------------两个表之间的连接----------
select*fromstudentjoinsconstudent.sno=sc.sno
------------查询号学生选修的课程名----
selectcnamefromcourse
wherecnoin(selectcnofromsc
wheresno=1
)
-------------查询号课程的学生姓名----
selectsnamefromstudent
wheresnoin(selectsnofromsc
wherecno=1)
select*fromsc
selectcno,COUNT(sno)fromsc
groupbycno
-------------
-------查询号学生选修的课程名-----
selectcnamefromcoursejoinscono=o
wheresno=1
---------选修号课的学生的姓名
selectsnamefromstudentjoinsconstudent.sno=sc.sno
wherecno=1
------------------------三表连接----------
------------join法林冲的选修课程的名----
selectcnamefromcoursejoinscono=o
joinstudentonsc.sno=student.sno
wheresname='林冲'
------------嵌套法林冲----------
selectcnamefromcourse
wherecnoin(selectcnofromsc
wheresnoin(selectsnofromstudent
wheresname='林冲'
))
-----------嵌套法查询9号学生选修的课程名-----
selectcnamefromcourse
wherecnoin(selectcnofromsc
wheresno='9'
)
-----------join法选修统计学,并且是"三国vampire"的人的姓名
selectsnamefromstudentjoinsconstudent.sno=sc.sno
joincourseono=o
wherecname='统计学'andsdept='vampire'
-----选修统计学的人数不够,增加数据
insertintosc
values(9,10,99,10)
select*fromsc
----------嵌套法---
selectsnamefromstudent
wheresnoin(selectsnofromsc
wherecnoin(selectcnofromcourse
wherecname='统计学'
))
andsdept='vampire'
--------------------------------damon选修的课程的数目
selectCOUNT(cno)fromsc
wheresnoin(selectsnofromstudent
wheresname='damon'
)
----------------列出stefan的平均分----------
---join法
selectAVG(score)fromstudentjoinsc
onstudent.sno=sc.sno
wheresname='stefan'
---嵌入法
selectAVG(score)fromsc
wheresnoin(selectsnofromstudent
wheresname='stefan'
)
----------------列出统计学课程的平均分------
---join法
selectAVG(score)fromscjoincourseono=o
wherecname='统计学'
---嵌入法
selectAVG(score)fromsc
wherecnoin(selectcnofromcourse
wherecname='统计学'
)
--------------------------加入主码约束-----------
altertablestudent
altercolumnsnamenvarchar(255)notnull-----------主码不能为空---
go
altertablestudent
addconstraintconstraint1primarykey(sname)
go
--------------------------查询stefan选修的学分-----
----join法
selectccreditfromcoursejoinscono=o
joinstudentonsc.sno=student.sno
wheresname='stefan'
----嵌入法
selectccreditfromcourse
wherecnoin(selectcnofromsc
wheresnoin(selectsnofromstudent
wheresname='stefan'
)
)
----------------------------查询与stefan至少有一门选修相同的人----------
selectsnamefromstudent
wheresnoin(selectsnofromsc
wherecnoin(selectcnofromsc
wheresnoin(selectsnofromstudent
wheresname='stefan'
)
)
)
----------------------------查询vampire系中同学的平均分------
----join法
selectAVG(score)fromscjoinstudentonsc.sno=student.sno
wheresdept='vampire'
----嵌入法
selectAVG(score)fromsc
wheresnoin(selectsnofromstudent
wheresdept='vampire'
)
---------------------------------统计学课程的方差-------
---查询选统计学的人数
selectsnamefromstudent
wheresnoin(selectsnofromsc
wherecnoin(selectcnofromcourse
wherecname='统计学'
)
)
---2个人人数略少,增加人数
select*fromsc
insertintosc
values(8,10,88,10)
insertintosc
values(2,10,78,10)
insertintosc
values(1,10,89,10)
insertintosc
values(5,10,91,10)
---------计算方差----
selectvar(score)fromsc
wherecnoin(selectcnofromcourse
wherecname='统计学'
)
-----------------------------查询stefan的总成绩-----
selectSUM(score)fromsc
wheresnoin(selectsnofromstudent
wheresname='stefan'
)
-------查看stefan各科的成绩--
selectscorefromstudentjoinsconstudent.sno=sc.sno
joincourseono=o
wheresname='stefan'
-----------------------------查询与stefan一个系的同学姓名---
selectsnamefromstudent
wheresdeptin(selectsdeptfromstudent
wheresname='stefan'
)
------------------------------至少跟stefan选修一门课程的同学---
selectsnamefromstudent
wheresnoin(selectsnofromsc
wherecnoin(selectcnofromsc
wheresnoin(selectsnofromstudent
wheresname='stefan'
)
)
)
------------------------------每门课程的平均分-----------
selectcname,AVG(score)fromscjoincourseono=o
groupbycname
---------------------------------关于"书"的SQLSERVER查询-------
select*frombook
select*fromusers
select*fromorders
---------------------------------用户stefan购买的书的总价------
selectSUM(price*number*discount)frombook
wherebnoin(selectbnofromorders
whereunoin(selectunofromusers
whereuname='stefan'
)
)
---------------宋江购买的书的总价------
selectSUM(price*number*discount)frombookjoinordersonbook.bno=orders.bno
joinusersonorders.uno=users.uno
whereuname='宋江'
-------select*frombookjoinordersonbook.bno=orders.bno
joinusersonorders.uno=users.uno
----------------购买sas的性别比例-------
selectugender,COUNT(ugender)fromusersjoinordersonusers.uno=orders.uno
joinbookonorders.bno=book.bno
wherebname='sas'
groupbyugender
-----------下面的方法不行,可能不能识别函数括号中的等于号
selectCOUNT(ugender=0):
count(ugender=1)fromusersjoinordersonusers.uno=orders.uno
joinbookonorders.bno=book.bno
wherebname='sas'
------------------------------列出各出版社的销售情况-------
selectpress,SUM(price*number*discount)frombookjoinorders
onbook.bno=orders.bno
groupbypress
---查看北京大学出版社出版的书名,购书者,以及购书数量
selectSUM(number)frombookjoinorders--------只能查询数量,不能同时查询书名,购书者,折扣,需要分开查询
onbook.bno=orders.bno
wherepress='北京大学出版社'
-------------------------------简单函数-----
createfunctionfun1(@aint,@bint)returnsint
as
begin
declare@nint
set@n=@a+@b
return@n
end
printdbo.fun1(2,3)
-----------------------------查询某系学生平均年龄-----
createfunctionfun2(@sdeptchar(10))
returnsint
as----------------最后与return@age相照应
begin----------------最后与end相照应
declare@ageint
set@age=(selectavg(sage)fromstudent
wheresdept=@sdept
)
return@age
end
printdbo.fun2('vampire')
-----------------------------------输入某人的姓名,显示他选修课数目------
createfunctionfun3(@snamechar(10))returnsint
as
begin
declare@nint
set@n=(selectcount(cno)fromsc
wheresnoin(selectsnofromstudent
wheresname=@sname
)
)
return@n
end
printdbo.fun3('stefan')
------------------------------返回给定学号的选修课列表-------
createfunctionfun4(@snochar(10))returnstable
as
return
selectcnamefromcourse
wherecnoin(selectcnofromsc
wheresno=@sno
)
go
select*fromdbo.fun4('9')
--------------------------------输入某系,将得到男女比例-----
createfunctionfun5(@sdeptchar(10))
returns@distable(sgenderchar(12),profloat)
as
begin
declare@sumfloat
set@sum=(selectcount(*)fromstudent
wheresdept=@sdept
)
insertinto@dis
selectsgender,'人数'=count(sno)/@sumfromstudent
wheresdept=@sdept
groupbysgender
return
end
select*fromdbo.fun5('三国')
----------------------------列出某系各同学的成绩-------
----方法----
alterfunctionfun6(@sdeptchar(10))returnstable
as
return
selectsname,'score'=avg(score)fromscjoinstudent
onsc.sno=student.sno
wheresdept=@sdept
groupbysname
go
select*fromdbo.fun6('vampire')
----方法------
createfunctionfun7(@sdeptchar(10))
returns@dis2table(snamechar(10),scorechar(10))
as
begin
insertinto@dis2
selectsname,avg(score)fromscjoinstudent-------只适合用join法连接,不适合嵌入法,因为sname,score不是在同一个表里,而一般只默认from后的第一个表示他们的来源处,所以会出现错误
onsc.sno=student.sno
wheresdept=@sdept
groupbysname
return
end
select*fromdbo.fun7('vampire')
-----------------------给定用户名,返回用户买书话费的钱-----
createfunctionfun8(@unamechar(10))returnsint
as
begin
declare@moneyint
set@money=(selectsum(price*number*discount)frombookjoinorders
onbook.bno=orders.bnojoinusersonorders.uno=users.uno
whereuname=@uname
)--------------不能忘记单引号''
return@money
end
printdbo.fun8('stefan')
-------------------------------静态游标-------------
declarecursor2cursorstatic
for
select*fromstudent
go
opencursor2
go
fetchfirstfromcursor2
go
updatestudent-----------------更新数据
setsage=sage+1
fetchfirstfromcursor2------------------读出的数据时仍是原来的数据
---------------------------动态游标----
declarecursor3cursordynamic
for
select*fromstudent
opencursor3
fetchfirstfromcursor3
updatestudent--------------更新数据
setsage=sage+1
fetchfirstfromcursor3-------------读出的数据是更新之后的数据----
-------------------------------只进游标(默认)--------
declarecursor_forwardcursorforward_only-------------forward_only可以不写
for
select*fromstudent
opencursor_forward
fetchnextfromcursor_forward
-------------------------------任意滚动游标---------
declarecursor_scrollcu
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL server个人整理超级实用版 server 个人 整理 超级 实用