个人整理版真题数据库SQL部分答案.docx

绸写S SQL查询奈实现分数排名.如果两个分数相同，则两个分规3电(Rank)相同.清注意.平分后 的下一个W次应该是下一个逐渎的暨值。换句话说，名次之间不应该有“间隔”。
I I I
I I
I
I Id | Score |
| 1 | 3.50
| 2 | 3.65
| 3|4.00
| 4|3.85
| 5 | 4.00
| 6 | 3.65例如，根症上述给定的Scores表，你的查词应该退回(按分裁从高到俗冽)：
| Score | Rank |
00 | 1
00 | 13
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3

3
3
3
3
85 | 265 | 365 | 350 | 4select o.score.p.Rank from scores o,(select score,(@row:=@row +1) as Rank from (select @row:=0) r,(select e.score from scores e group by e.score order by e.score desc) b )Pwhere o.score = p.scoreorder by o.score desc思路：首先进行分数分组排序，算出成绩高低；接着引用变量存储行号，此时成绩是分组 排序好了，所以行号就是排名，把这两个查询合并为P ；接着就是让每一个成绩都与P表比 较，取O的成绩，取P表排名，这样就有多个同分同排名了；最后按分数在降序就ok总结下sql细节in+()中间有无空格都可以，括号中的子查询不用完后不要加别名from表A,(子查询)AS B ,如果是from后加复杂的子查询一定要加别名update 表 A set 字段二’’where …insert into表A Values(字段一，…)这种是每个字段都要加，如果只是插入某个字段,
则 INSERT INTO table.name (列 1,列 2,...) VALUES (值 1,值 2，.…)VALUES 前有空格delete from A where …创建视图的写法CREATE view视图名as后面加查询语句真题难点解析2013、2015年都不算难，主要是16、17、18年难度逐渐递增一、2016年真题统计每个顾客在2015年度的累计购买金额的视图CREATE view CustSum asselect c.cid,ame,t.sum_dols total_dols from c,(select cid.sum(dols) sum_dols from o where year(o.orddate)二year(date_sub(now(),interval 1 year)) group by cid) twhere c.cid = t.cid思路：先找出2015年的订单，然后按客户id分组，就可以算出最大值，然后作为一个表跟 客户表关联，去客户名字就可以了时间的相关总结：
day(date)、month(date)、year(date)分别可以把去具体日期中的天/月/年选取时间各部分SELECT now(),date(now());--日期SELECT now(),time(now());--时间SELECT nowQ,year(nowO)；--年SELECT now(),quarter(now());--季度SELECT now(),month(now()); - ■月SELECT now(),week(now());--周SELECT now(),day(now());--日SELECT now(),hour(now());--小时SELECT now(),minute(now());--分钟SELECT now(),second(now());--秒SELECT now(),microsecond(now());--微秒date_add(date, interval数字类型)：表示对date加多少天/月/周，具体根据类型来定等 date_sub(date, interval数字 类型):用法和加相似，只不过是对date减 type的值如下获得当前日期：now() 2008-08-08 22:20:46时间转换：date_format(date,format)datediff(datel,date2):日期相减，是datel-date2只能精确到天数如:select datediffC2008-08-08'z '2008-08-01'); - 7TIMESTAMPDIFF 函数,可以精确到天(DAY)、小时(HOUR),分钟(MINUTE)和秒(SECOND), 使用起来比datediff函数更加灵活--相差1天select TIMESTAMPDIFF(DAYZ '2018-03-20 23:59:00', *2015-03-22 00:00:00');-■相差49小时select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00'); ■■相差2940分钟select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00； *2018-03-22 10:00:00'); ■■相差176400秒select TIMESTAMPDIFF(SECONDZ '2018-03-20 09:00:00', '2018-03-22 10:00:00*);查询只向南京市的顾客销售过商品的供应商的编号select DISTINCT aid from ozc where c.cid = o. cid and c.city ='南京'and o.aid not in (select o2.aid from o o2,c where o2.cid = c.cid and c.cityo'南京')思路：通过内层查询先找出给不是南京市的顾客供应过商品的供应商A,外层查询向南京市的顾 客供应过商品的供应商B,但是B中有包含只向南京市供应的，还包含不止向南京市供应的， 所以再通过not in A,就可以把不止向南京市供应的供应商去掉
总结：碰到只查询C。。。一般可以转为not in(oC)的这种情况统计查询每一个供应商的累计销售金额，结果返回供应商编号及其累计销售金额，并按照 累计销售金额从高到低降序输出查询结果select a.aid,sum(o.dols) from a LEFT join o on a.aid = o.aid group by a.aid ORDER BY sum(o.dols) desc这个主要是提一下排序是可以用sum () 查询在2018年2月份到8月份累计购买金额超过500元的顾客的编号和名称 注意年 的用法select c.cid,ame from czo where c.cid = o.cid and year(o.orddate)=2018 and month(o.orddate) BETWEEN 2 and 8 group by o.cid HAVING sum(dols)>280 主要是between a and b的用法查询每一个顾客的最后一份订单，结果返回顾客的编号、订单编号、订单日期select o.cidzo.ordno,o.orddate from oz(select cidzmax(orddate) orddate from o group by cid ) t where o.orddate = t.orddate and o.cid = t.cid分组查最大的思路：先按正常分组取最大值，然后把结果令为一个子表，做关联查询，让值=分 组最大值，让id=分组id查询向所有顾客都销售过商品的供应商的编号select aid from a where not EXISTS(
select * from c where not EXISTS(select * from o where o.aid= a.aid and o.cid = c.cid
))除法思路查询一个表包含另一个表的信息，即除法的思路，那我们皇不犹豫转为两个not exist, 一般都是3个表，最外层是要查的，中间的辅助的，最内是能关联外层和中间层的二、2017年真题1 .求员工工资中位数(难点)select avg(salary) from(select eno,salary from(select emp.eno, @index:=@index+l as myindex, emp.salary from emp,(select @index:=0) as initvar order by emp.salary) twhere floor(@index/2+l)=myindex or ceil(@index/2) = myindex) x思路：通过变量来设置行号，然后向上和两下取整取中间数据(select @index:=0) as initvar这个是初始化变量，并设置为0,而且要作为一个子表， 而且是在from后面所以要起别名，不起别名语句报错@index: = @index+l as myindex这个就是设置行号，注意变量的设置都是:=的形式， 而且是要起别名，因为后面要用到排序完后变量@index就为行数(比如10行，那最后排序完就等于10)以上作为一个子表，然后从这个子表中取中间的数据，取法就是依靠floor向下取整和ceil向上取整。比如行数为10,中位数应该是5和6的平均值，也就是我们要取第5行和低6 行的数据,floor( 10/2+l)=floor(6)=6 取 myindex 为 6 的行，ceil(10/2) = 5 取 myindex为5的行。如果行数是5,要取第3行，同理floor(5/2+l)=floor(3.5)=3„ ceil(5/2)=ceil(2.5)=3,所以只会取到1行，取到第3行然后再把取到的行作为一个子表，最后取平均值即可求各部门去除最高工资和最低工资的员工外，每个部门所有员工的平均工资(偏难)select emp.dno,avg(emp.salary) from emp, (select dnozmin(emp.salary) as lowzmax(emp.salary) as high from emp GROUP BY dno) ewhere emp.dno = e.dno and (emp.salary<>e.low and emp.salaryoe.high) GROUP BY dno思路：首先按部门分组，取各部门号、各部门最低和最高工资，然后作为子表做关联查询，并且 工资不能等于最高和最低.这样取到的数据就是去除最高和最低的了，别忘了部门号也要做关联求各部门最大■最小入职时间的转换，多少年/多少月select TRUNCATE(TIMESTAMPDIFF(DAY,min(hire),max(hire)) /365,2) as yearsl,
TRUNCATE(TIMESTAMPDIFF(DAY,min(hire)/max(hire))/30z2) as monthsl from emp GROUP BY dno思路：因为如果用TIMESTAMPDIFF在转为年的话，会舍去小数点，所以我们先要化为天，在 转为年和月，但是不是很准确，但至少比转接转为年精确主要介绍TRUNCATE (100.223344,2) =100.22可以保留小数点后几位还有一个函数也需要记一下Round。,是做四舍五入21
22
23
24
25
26
27
28
29
21
22
23
24
25
26
27
28
29
21
22
23
24
25
26
27
28
29

21
22
23
24
25
26
27
28
29
#按时间排序，求最大-最小入职时间的转换，换成年和换成月，不精确；第二种做法相对I： select dno,
TIMESTAMPDIFF(YEAR,min(hire),max(hire)) as years, TIMESTAMPDIFF(HONTH,min(hire),max(hire)) as months, TIMESTAMPDIFF(DAY,min(hire),max(hire)) /365 as yearsl, TIMESTAMPDIFF(DAY,min(hire),max(hire)) /30 as monthsl , TRUNCATE(TIMESTAMPDIFF(DAY,min(hire),max(hire)) /365,2) as years2, TRUNCATECTIMESTAMPDIFFCDAY^minChire^maxChire)) /30,2) as months? from emp GROUP BY dno三、2018年真题查询同时工作于硬件和软件部门的每个员工的名字和年龄(除法，可以转为not exists) select e.ename,e.age from emp2018 e where not EXISTS(
信息
Result 1剖析状态
dno
years months yearsl monthsl years2 months3
1 2282.33728.43332.3328.43
2 080.66588.10.668.1
select * from dept2018 d where (d.dname="硬件”or d.dname="软件")and not EXISTS(select * from works2018 w where e.eid=w.eid and w.did = d.did
))思路：只要看到有除的关系，都可以转为not exist.只不过要注意OR条件要括起来，不然不找出那些工资超过他所在部门预算20%的员工select distinct e.eid from emp2018 ezdept2018 d,works2018 w where e.eid=w.eid and w.did = d.did and e.salary >d.budget * 0.2思路：做关联就可以了找出预算最高的前2个部门管理者的管理员id (有难有易)select managerid from dept2018 ORDER BY budget desc limit 2注意：limit 2只能取两条，如果有多个相同的最高，这样做不行select DISTINCT dl.managerid from dept2018 dl,(select d. budget,(@row: = @row+l) as myrow from(select * from dept2018 GROUP BY budget ORDER BY budget desc) deselect @row:=0) as rowl)xwhere dl.budget = x.budget and (x.myrow=l or x.myrow=2)分析：这种方式可以取相同最高的前2位。
思路：我们按预算分组，然后把分组结果作为一个表，在关联变量表，变量表主要做行号，因为 我们想去最大、和第二大的预算值，切记不能再分组的时候就加行号，这样结果不对。然后再令 为一个表，和原表关联取预算相同，并且myrow等于1或2,就可以取到多人预算相同且都是 最高或第二高的情况一个管理员管理多个部门，那么他控制的部门预算就是这些部门的预算的总和，找出总和大于5000的管理者idselect managerid from dept2018 GROUP BY managerid HAVING sum(budget)>5000分析：这个考点应该是having的用法计算2017年中有多少个星期个数(难点)，并给出设计的表结构select DATE_FORMAT(DATE_ADD(CAST( '2017-01-01' as date),INTERVALreadrinfo.Rdr_Id-l DAY)/%W') day ,count(*)from readrinfowhere readrinfo.Rdr_Id <= DATEDIFF( cast('2018-01-01' as date), cast('2017-01- Ol'as date))group by DATE_FORMAT(DATE_ADD(CAST( ‘2017-01-01，as date),INTERVAL readrinfo.Rdr_Id-l DAY)/%W')#group by day可以替换group by后面那一大串，本质就是用星期分组，而且我们也给这 个字段起别名，如果没起的话，那就不能替换思路：表readrinfo只有一个Rdr_Id字段，取值为1-357,或者大于356都可以，如果只计 算一年的话356够了，因为一年最多356天，注意id・l因为date_add(date,interval数字type)第一个参数是日期型，所以要用case函数做转化， Cast(字段名as转换的类型),可以转换的类型见下图。我们用2017-01-01分别加表readrinfo中每一个id值・1,然后用date_format可以转为对应的星期几(英文)，接下来我们需要分组，分 组前计算下一年的天数，用了 datediff算了 2018-01-01到2017-01-01的天数，这样可以 确定之前date_add应该加多少行的id值，然后根据算出的结果分组就可以了，如果我们在取" / I MU *9*77
from readrinfowhere readrinfo.Rdr_Id <= DATEDIFF( cast('2018-01-01' as date), cast('2017-01- Ol'as date))group by DATE_FORMAT(DATE_ADD(CAST( ‘2017-01-01，as date),INTERVAL readrinfo.Rdr_Id-l DAY)/%W')#group by day可以替换group by后面那一大串，本质就是用星期分组，而且我们也给这 个字段起别名，如果没起的话，那就不能替换思路：表readrinfo只有一个Rdr_Id字段，取值为1-357,或者大于356都可以，如果只计 算一年的话356够了，因为一年最多356天，注意id・l因为date_add(date,interval数字type)第一个参数是日期型，所以要用case函数做转化， Cast(字段名as转换的类型),可以转换的类型见下图。我们用2017-01-01分别加表readrinfo中每一个id值・1,然后用date_format可以转为对应的星期几(英文)，接下来我们需要分组，分 组前计算下一年的天数，用了 datediff算了 2018-01-01到2017-01-01的天数，这样可以 确定之前date_add应该加多少行的id值，然后根据算出的结果分组就可以了，如果我们在取" / I MU *9*77

from readrinfowhere readrinfo.Rdr_Id <= DATEDIFF( cast('2018-01-01' as date), cast('2017-01- Ol'as date))group by DATE_FORMAT(DATE_ADD(CAST( ‘2017-01-01，as date),INTERVAL readrinfo.Rdr_Id-l DAY)/%W')#group by day可以替换group by后面那一大串，本质就是用星期分组，而且我们也给这 个字段起别名，如果没起的话，那就不能替换思路：表readrinfo只有一个Rdr_Id字段，取值为1-357,或者大于356都可以，如果只计 算一年的话356够了，因为一年最多356天，注意id・l因为date_add(date,interval数字type)第一个参数是日期型，所以要用case函数做转化， Cast(字段名as转换的类型),可以转换的类型见下图。我们用2017-01-01分别加表readrinfo中每一个id值・1,然后用date_format可以转为对应的星期几(英文)，接下来我们需要分组，分 组前计算下一年的天数，用了 datediff算了 2018-01-01到2017-01-01的天数，这样可以 确定之前date_add应该加多少行的id值，然后根据算出的结果分组就可以了，如果我们在取" / I MU *9*77
思路：表readrinfo只有一个Rdr_Id字段，取值为1-357,或者大于356都可以，如果只计 算一年的话356够了，因为一年最多356天，注意id・l因为date_add(date,interval数字type)第一个参数是日期型，所以要用case函数做转化， Cast(字段名as转换的类型),可以转换的类型见下图。我们用2017-01-01分别加表readrinfo中每一个id值・1,然后用date_format可以转为对应的星期几(英文)，接下来我们需要分组，分 组前计算下一年的天数，用了 datediff算了 2018-01-01到2017-01-01的天数，这样可以 确定之前date_add应该加多少行的id值，然后根据算出的结果分组就可以了，如果我们在取" / I MU *9*77
思路：表readrinfo只有一个Rdr_Id字段，取值为1-357,或者大于356都可以，如果只计 算一年的话356够了，因为一年最多356天，注意id・l因为date_add(date,interval数字type)第一个参数是日期型，所以要用case函数做转化， Cast(字段名as转换的类型),可以转换的类型见下图。我们用2017-01-01分别加表readrinfo中每一个id值・1,然后用date_format可以转为对应的星期几(英文)，接下来我们需要分组，分 组前计算下一年的天数，用了 datediff算了 2018-01-01到2017-01-01的天数，这样可以 确定之前date_add应该加多少行的id值，然后根据算出的结果分组就可以了，如果我们在取" / I MU *9*77

思路：表readrinfo只有一个Rdr_Id字段，取值为1-357,或者大于356都可以，如果只计 算一年的话356够了，因为一年最多356天，注意id・l因为date_add(date,interval数字type)第一个参数是日期型，所以要用case函数做转化， Cast(字段名as转换的类型),可以转换的类型见下图。我们用2017-01-01分别加表readrinfo中每一个id值・1,然后用date_format可以转为对应的星期几(英文)，接下来我们需要分组，分 组前计算下一年的天数，用了 datediff算了 2018-01-01到2017-01-01的天数，这样可以 确定之前date_add应该加多少行的id值，然后根据算出的结果分组就可以了，如果我们在取" / I MU *9*77
" / I MU *9*77
" / I MU *9*77
" / I MU *9*77

" / I MU *9*77
Rdr Id
3
4
5
6
7
的时候起了别名，比如day,那group by day是可以的，就没必要重复写后面那一串CHAR[(N)]字符型DATE日段里DATETIME日期和时间型DECIMAL float型SIGNED intTIME时间型查询尚未完成的状态订单(假设除了 3都是未完成)，列出oid,cname,以及最后的状态 status 和设置 statusdate (较难)select amezt.status,t.statusdate from orders od,customers c,(select os.* from orderstatus os /select oidzmax(statusdate) statusdate from orderstatus group by oid ) tswhere os.oid = ts.oid and os.statusdate = ts.statusdate and os.status<>3)twhere od.oid = t.oid and od.cid = c.cid分析：首先按oid分组取最大的订单时间，然后作为一个子表关联订单表，取状态不是3的，这 样就取到关键的数据，最后在关联一次取相应的信息就可以了计算每个员工的deptno、ename、sal以及与同一个部门员工之间的sal的差值，这个 差值是在当前员工与同部门内紧随其后的员工之间计算，对聘用的而员工这个是N/S (难题) select c.empno,c.ename,c.deptno,c.hiredate,c.curr as salary, (c.curr-c.pre) as 'N/S' from(select b.empnozb.ename,b.deptno,b.hiredate,b.curr;if(b.rr<>b.rri;Ozb.pre) as pre from(SELECT e.empno ze.ename ,e.deptno, e.hiredate ,e.sal AS cun;@temp AS pre,@temp:= e.salz@templ as rr;@templ :=e.deptno as rrrFROM emp2018_2 e,(SELECT @temp: = 0)「,(SELECT @templ:=0)rl ORDER BY deptno,hiredate desc)b)cORDER BY c.deptnozc.hiredate后面补充.这个题主要针对变量的使用，而且对变量的位置有要求四、2012年期末试卷的好题选择查询每个部门中工资最高的职工，返回部门编号和该职工工号select e.dno,e.eno from e, (select dno,max(salary) as salary from e GROUP BY dno) el where e.dno=el.dno and e.salary= el.salary分析：查最大思路都是先分组取最大和关联的id,然后把查询的结果令为一个表，再跟关联id 的那个表进行关联，取id和工资都相等的，这样可以取到多个相同且都是最大的人，不要单纯 想只是一个人2 .检索只参加过一个项目的职工的姓名select e.ename from e where e.eno in (select eno from w GROUP BY w.eno HAVING count(*) = l)思路：分组后having count (*) =1是这个题的关键
3.检索只参加过项目P1的职工姓名select ename from e,w where e.eno = w.eno and e.eno not in (select eno from w where w.pno <>1)思路：首先取没有参加过Pl项目的员工，但有可能有两种情况：只参加了 P1以及都没参加的 情况，然后我们做关联e.eno = w.eno,这样就去掉了都没参加的项目的员工五、作业题精选(来源于text_20140324. ch3[l],网上找的刘嘉老师的部分作业题) 查询供应商每一份订单的销售金额都小于50的供应商select a.aname from agents a,(select aid from orders GROUP BY aid HAVING max(dollars)<50) al where a.aid = al.aid思路：选这个题是因为出现小于的情况，每一份小于50,那就意味最大要小于50查询满足下述条件的订单:销售金额高于该商品所有订单的平均销售金额。结果返回商品编 号，订单编号和销售金额，并按照商品编号的升序和销售金额的降序输出查询结果select o.pidzo.ordnozo.dollars from orders o "select pid,avg(dollars) as avg_dols from orders group by pid) olwhere o.pid=ol.pid and o.dollars>ol.avg_dolsorder by o.pid asc zo.dollars desc分析：这个题不难，主要是order by的多个排序规则，要注意顺序找出购买的每一类商品的平均订单数量都大于300的客户编号(注意:、、都超过")select T.cid from(select cid,pid,AVG(qty) as q_avg from orders group by cidzpid) TGROUP BY T.cidHAVING min(T.q_avg) >= 300分析：这个题和1题有异曲同工之妙，不过有点灵活，需要按照客户编号和商品编号分组，然后 求平均值，然后再按客户编号分组找最小的平均值大于300的，这样就是题目所要求的都超过查询仅通过1和2经销商订购过商品的客户编号select DISTINCT ol.cid from orders ol where ol.cid not in(select o2.cid from orders o2 where o2.aid ol and o2.aid<>2)分析：这个题是表自关联、先从订单表找出不是1和2的客户编号，然后再从订单表找不在其 结果的客户编号。思路就是看到"仅"，一般都是用notin做双重否定只通过2号经销商订购过的商品的客户编号，并给出客户的订购总金额select cidzsum(dollars) from orders where cid not in(select cid from orders where cid <>2)GROUP BY cid分析：同样出现了 “只"，用not inz只不过是要对结果分组统计6 .检索居住在海口和北京的所有客户订购过同一商品的经销商编号select o.aid from orders o where not exists(select * from customers c where (c.city='海口' or c・city='北京')and not EXISTS( select * from orders ol where ol.aid = o.aid and ol.cid = c.cid and ol.pid = o.pid) )分析：典型的除法，只不过要注意or的条件，要括号起来，不然结果不对查询同时购买nike品牌2019年新上市的最贵的外套和裤子的顾客姓名select ame from customer cl where not EXISTS(select * from clothes c2 /select c.cid,max(c.price) from clothes c where c.brand = 'nike' and c.launchYear = 2019 and (c.type=2 or c.type=3) group by c.type) c3 where c2.cid = c3.cid and not EXISTS(select * from 'order' o where cl.cuid = o.cuid and o.cid = c2.cid )
)分析：复杂的除法情况，两个not exist少不了，主要是中间那层需要找出最贵的外套和裤子需 要做关联.找出cid.这里的用法主要是c.cid,max(c.price),这个在查询时cid就是对应的最 大价格那个。
1. 查询号这天销量前三的服装名称、总销量、以及品牌SELECT c.' name' zt.totalQuantityzc.brand from clothes c,(select o.cidzsum(o.quantity) as totalQuantity from ' order' owhere DATE_FORMAT(o.orderTime,'%Y・%m.%d') = ,2019-03-06, GROUPBY o.cid ORDER BY sum(o.quantity) desc LIMIT 3) twhere c.cid =t.cid分析:主要是看下DATE.FORMAT的用法，其中'％Y・％m-%d'这个一定要记住、而且后面的 时间一定要加气题的思路就是分组然后按销量降序取前三个，然后关联取相应的值