SQL习题集.docx
- 文档编号:5073287
- 上传时间:2022-12-13
- 格式:DOCX
- 页数:47
- 大小:28.04KB
SQL习题集.docx
《SQL习题集.docx》由会员分享,可在线阅读,更多相关《SQL习题集.docx(47页珍藏版)》请在冰豆网上搜索。
SQL习题集
SQL习题集
数据库拆表原则
1.简单字段中的复合字段:
解决方法:
向上打通,或拆成两表
2.完全依赖:
知道a就能知道b,返过来就不一定.实现:
将同一个主题的东西放在一起.主题可以是一个实体、名词、概念。
3.无传递依赖。
将传递依赖转为直接依赖。
原因:
减少插入冗余。
减少增删异常。
4.当出现一对多时不能横拆,一定拆成两个表,特别的固定范围可以横拆(签到管理)
5.多对多必有中间表
6.代码表,大量重复的或枚举型可以用代码表.
7.能计算的字段不要
数据定义语句DDL
createalterdrop
数据控制语句DCL
grantdenyrevoke
数据操纵语句DML
selectupdateinsertdelete
一.SQLServer2000
Select变化集锦
1.查询所有字段(效率低)
查询所有的老师
select*
fromdbo.教师
2.字段枚举
查询教师的ID,及姓名两项
select教师_ID,教师名
fromdbo.教师
3.字段取别名方法有二
1)as可以省略
select教师名asteacher
fromdbo.教师
2)
selectteacher=教师名
fromdbo.教师
4.字段的可计算性
1)简单字段计算
教师工资的10%
select工资*0.1
fromdbo.教师
2)把一个检索结果作为查询字段
本学校的师生比例
select
(
selectcount(*)
fromdbo.教师
)
/1.0/
(
selectcount(*)
fromdbo.学生
)
5 取检索结果的前几个
selecttop3教师_ID
fromdbo.教师
6 取检索结果总数的百分比
selecttop30percent教师_ID
fromdbo.教师
7去掉重复记录
selectdistinct性别_ID
fromdbo.教师
8在聚合统计函数中统计不重复指定字段
selectcount(distinct系_ID)
fromdbo.教师
9casewhenthenelseend在select中的应用
从教师表中查询,将性别_id是1的显示为男,是2的显示为女
select
(
case
when性别_ID=1then'男'
when性别_ID=2then'女'
End
)
fromdbo.教师
横向查询男女人数
select
sum(
case
when性别_ID=1then1
else0
end
)as'nan'
sum(
case
when性别_ID=2then1
else0
end
)as'nv'
fromdbo.教师
10可以重复列出表的的字段
select*,*,*
from教师
11检索结果放常量
select教师名,'出生于',出生日期
fromdbo.教师
-----------------------------------
From变化集锦
1.从一个表中检索
select*
fromdbo.教师
2从一个检索结果中再检索即从临时表中检索
select*
from
(
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
)lin–sqlserver2000中临时表通常要起别名
wherelin.教师_ID<4
3.从多表中检索,构成迪卡尔乘积(效率低)
selectdbo.教师.*,系名
fromdbo.教师,dbo.系
wheredbo.教师.系_ID=dbo.系.系_ID
4多表的联接查询
1)左连接(尊重左边)
select*
fromdbo.系leftjoindbo.教师
ondbo.系.系_ID=dbo.教师.系_ID
2)右连接(尊重右边)
select*
fromdbo.系rightjoindbo.教师
ondbo.系.系_ID=dbo.教师.系_ID
3)内连接(都不尊重,即两边都得有)下例为两个以上表的连接写法
select*
fromdbo.系innerjoindbo.教师
ondbo.系.系_ID=dbo.教师.系_IDinnerjoindbo.教师认课
ondbo.教师.教师_ID=dbo.教师认课.教师_ID
4)全连接(都尊重)
select*
fromdbo.系fulljoindbo.教师
ondbo.系.系_ID=dbo.教师.系_ID
5.给被检索表起别名注意:
起了别名就一定用别名引用字段
错误的
selectdbo.教师.教师名
fromdbo.教师js
正确的
selectjs.教师名
fromdbo.教师js
where集锦
1.常规关系运算>、>=、<、<=、!
=、<>、=
select教师_ID
fromdbo.教师
where教师_ID<>1
只检索表结构不要任何数据
Select*
Fromdbo.教师
Where1!
=1
2.常规逻辑运算not、and、or
select教师_ID
fromdbo.教师
wherenot(教师_ID>=1and教师_ID<=8)
3.区间
教师_ID>=1并且教师_ID<=8
select教师_ID
fromdbo.教师
where教师_IDbetween1and8
4.检索null值
select教师_ID
fromdbo.教师
where出生日期isnull
5.关于字符的模糊查询
1)姓张的所有老师
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
where教师名like'%张'--%代表任意多个字
2)姓张的但名字只有两个字的老师
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
where教师名like'_张'--_只代表一个字
3)名字中含有国字的老师
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
where教师名like'%国%'
4)教师名的第一个字母在a-m之间的教师
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
where教师名like'[a-m]%'
5)教师名的第一个字母在不在a-m之间的教师
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
where教师名like'[^a-m]%'
6)教师名的第一个字母是a或b或m的教师
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
where教师名like'[a,b,m]%'
7)教师名的第一个字母是a到k之间或o到x之间的教师
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
where教师名like'[a-k,o-x]%'
6.关于IN的用法
1)枚举型
select*
fromdbo.教师
where教师_IDin(1,3,5)
select*
fromdbo.教师
where教师_IDnotin(1,3,5)
2)嵌套子查询型
有课上的老师
select*
fromdbo.教师
where教师_IDin
(
select教师_ID
fromdbo.教师认课
)
7.exists判断子查询是否有结果(强调有无,不关心具体是什么)
select*
fromdbo.教师
whereexists--存在
(
select*
fromdbo.教师认课
wheredbo.教师认课.教师_ID=dbo.教师.教师_ID
)
8.any其中一个
教师ID大于所有教师ID中任意一个的教师(相当于大于最小的教师ID)
select*
fromdbo.教师
where教师_ID>any
(
select教师_ID
fromdbo.教师认课
)
9.all全部
相当于大于最大max
select*
fromdbo.教师
where教师_ID>all
(
select教师_ID
fromdbo.教师认课
)
Groupby集锦
1.groupby通常与聚合函数(avg,sum,count,max,min)配合使用
查询出学生表中男女生人数
selectcount(*)asrenshu
fromdbo.学生
groupby性别_ID
2针对多字段的分组
每个系的男女生人数
selectcount(*)asrenshu
fromdbo.学生
groupby性别_ID,系_ID
3多表查询中的分组应用
selectcount(*)asrs,系名
fromdbo.学生xsinnerjoindbo.系xi
onxs.系_ID=xi.系_ID
groupby系名
4对于计算字段的分组应用
查询出计算机系学生数和其它系的学生数
selectcount(*),
(
case
when系_ID=1then'jsjx'
else'qt'
end
)
fromdbo.学生
groupby
(
case
when系_ID=1then'jsjx'
else'qt'
end
)
5分组条件having的用法
系人数在10人以上的系
selectcount(*)asshu,系_ID
fromdbo.学生
groupby系_ID
havingcount(*)>10
或写成
select*
from
(
selectcount(*)asshu,系_ID
fromdbo.学生
groupby系_ID
)linshi
wherelinshi.shu>10
orderby集锦
1.对单个字段的排序(asc升序可省略,desc降序)
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
orderby教师名
2.多字段的排序(先按第一个字段排序,如果相同再按第二个字段排序)
写法1。
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
orderby性别_ID,系_IDdesc
写法2。
select教师_ID,教师名,出生日期,系_IDasxxx,职称_ID
fromdbo.教师
orderby4,性别_IDdesc–4代表select后面第四个字段
3.按笔画排序
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
orderby教师名CollateChinese_PRC_Stroke_ci_asdesc
4.orderby可按给定表达式计算结果排序
按教师年龄大小降序排序
select教师_ID,教师名,出生日期,系_ID,性别_ID,职称_ID
fromdbo.教师
orderbyDATEDIFF(yy,出生日期,getdate())desc
将本年入学新生排在前面,老生排在后面
select学生_ID,姓名,出生日期,性别_ID,籍贯_ID,系_ID,届,班级,
(
case
when届=2006then1
else0
end
)
fromdbo.学生
orderby
(
case
when届=2006then1
else0
end
)desc
其它:
--union合并
将本校学生与教师名字全部查询出来(去掉重复)。
select教师名
fromdbo.教师
union
select姓名
fromdbo.学生
将本校学生与教师名字全部查询出来(不去掉重复)。
select姓名
fromdbo.学生
unionall
select姓名
fromdbo.学生
常用函数
--数学函数
selectABS(-9)
selectCEILING(9.00001)
selectFLOOR(9.9)
selectPI()
selectPOWER(2,3)
selectSQRT(9)
selectSIGN(0)
selectROUND(892.35,1,7)
selectROUND(892.35,1,0)
--几位数
select1+FLOOR(LOG10(48091))
--随机数
selectFLOOR(RAND()*10)
--
-----字符串函数------------------------------------------------------------------
selectLEFT('aaaaa',2)
selectRIGHT('123456',2)
selectLEN('1')
selectLOWER('aaBN')
selectUPPER('aaBN')
selectrtrim(LTRIM('aaa'))+'**'--trim().ltrim()
selectSUBSTRING('abcdefg',2,3)
selectREPLACE('aba','a','ccc')
selectSTUFF('aaaaa',1,2,'bbbbbb')
---------------------日期时间----------------------------
selectGETDATE()
selectYEAR(GETDATE())
selectmonth(GETDATE())
selectday(GETDATE())
selectDATEADD(mm,100,'1990-10-10')
selectDATEDIFF(yy,'1988-8-8',getdate())
select教师名,DATEDIFF(yy,出生日期,getdate())as年龄
fromdbo.教师
selectDATENAME(yy,getdate())+'年'
selectDATEPART(yy,getdate())
selectDATENAME(yy,getdate())+'年'+DATENAME(mm,getdate())+'月'+DATENAME(dd,getdate())+'日'
--本周/月/季/年的一是哪一天
selectdateadd(day,2-DATEPART(weekday,getdate()),getdate())
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)
SELECTDATEADD(mm,DATEDIFF(mm,0,getdate()),0)
SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
--上周/月/季/年最后一天
SELECTdateadd(ms,-3,DATEADD(wk,DATEDIFF(wk,0,getdate()),0))
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
SELECTdateadd(ms,-3,DATEADD(qq,DATEDIFF(qq,0,getdate()),0))
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
--本月的第一个星期一
selectDATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0)
--上个月多少天
--上月最后一天
declare@adatetime
set@a=dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
print1+month(getdate())%12
printconvert(char
(2),1+month(getdate())%12)+'-'
--selectdateadd(mm,DATEDIFF(mm,0,getdate()),0)
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
--通用时间差
declare@adatetime
set@a=dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
declare@bdatetime
set@b=dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate())-1,0))
printdatediff(day,@b,@a);
-------转换函数--------------------------------------------------------------------------
selectcast('666'asint)+100
selectconvert(varchar(13),999)+'***'--Integer.parseInt()
selectconvert(char(4),year(getdate()))+'-'+convert(char
(2),month(getdate()))+'-'+convert(char
(2),day(getdate()))
insertinto用法
1.向教师表中加入一个记录,字段的顺序与数目和表中一致
insertinto教师values();
2.字段的数目与顺序自己指定
Insertinto教师(姓名,性别_id)values(‘张’,1)
3.从select检索中插入成批记录
insertintodbo.学生(姓名)
select教师名
fromdbo.教师
deletefromdbo.xueshengwhereminglike'z%'
update用法
1.更新一个字段
Update教师
Set教师名=’李四’
Where教师_id=3;
教师基本工资上调5%
Update教师
Set基本工资=基本工资*(1.05)
2.多字段更新,每一个用,号隔开
Update教师
Set教师名=’李四’,
性别_id=1
Where教师_id=3;
集合论
交集
条件AAND条件B
selectfromA表whereexist(B查询)
SELECT语句AINTERSECTSELECT语句B
并集
条件AOR条件B
SELECT语句AUNIONSELECT语句B
补集
NOT条件A
[HAVING]NOT统计分组条件
SELECT语句AUNIONSELECT语句B
差集
selectfromA表wherenotexist(B查询)
---------------------------------------------------------------------------------------------------------------------------------------
集合相等
集合包含
A包含B
B中的元素一定在A中
不在A中的元素一定不在B中
1.用一句SQL语句获取学生表中年龄最大的学生
--1.是一句SQL语句,
--2.绝对不能使用任何统计函数,如sum(),max(),min(),avg(),count()以及所有DBMS提供的函数。
方法一
selecttop1年龄
from学生
orderby年龄desc
方法二
select年龄
from学生
where年龄>=all(select年龄from学生)
2.-学生表中年龄第三大的学生
selecttop1年龄
from学生
where年龄notin
(
selecttop2年龄
from学生
orderby年龄desc
)
orderby年龄desc
3.分页sql
4.删除除了自动编号不同,其他都相同的学生冗余信息
自动编号学号姓名课程编号课程名称分数
12005001张三0001数学69
22005002李四0001数学89
32005001张三0001数学69
deletefromtablename
where自动编号notin
(
selectmin(自动编号)
fromtablename
groupby学号,姓名,课程编号,课程名称,分数
)
5.有学生信息表{学号,班级,姓名,学科,成绩},
请写出每班语文成绩在前10名并且数学成绩不在倒数10名的学生的个人信息
select学号,班级,姓名,学科,成绩
from学生
where学号in
(
selecttop10学号
from学生
where学科='语文'
orderby成绩desc
)
and学号notin
(
selecttop10学号
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 习题集