SLQ实战语句总结三.docx
《SLQ实战语句总结三.docx》由会员分享，可在线阅读，更多相关《SLQ实战语句总结三.docx（17页珍藏版）》请在冰豆网上搜索。
SLQ实战语句总结三
法二【解决上述缺陷】：
1）先找出装在了黑色车上面的发动机的最小马力值；
selectMIN（ENGINE.`power`）fromcarinnerjoinengine
onENGINE.`F_carid`=car.`cid`
wherecar.`color`='black';
2）所有装了发动机的黑色汽车中，马力值等于该最小值的所有记录全出；
select*fromcarinnerjoinengine
oncar.`F_eid`=ENGINE.`eid`
wherecar.`color`='black'andengine.`power`=（selectMIN（ENGINE.`power`）fromcarinnerjoinengine
onENGINE.`F_carid`=car.`cid`wherecar.`color`='black'）;
###结论：
所以，一对一表中，能增加关联字段的，尽量增加，以便更多使用单表查询；
==【自连接】缺点：
字段太多，造成冗余大。
技术实质：
本表的一条记录的一个字段，和本表的另一条记录的另一个字段，关联。
业务意义：
比如，QQ中的用户，如何表达，用户间互为好友？
∴可以在用户表增加好友关联字段
uidnamegendericq〖friendid〗
1274张三男136********//用户字段越多，好友越多，冗余越大；
1274张三男136********
1274张三男136********
1274张三男1366243115
.................................
1477王伟男137********
此时，想查出每个人关联的好友情况，怎么办？
情况1）问：
想查出1274号用户的所有好友的uid号？
答：
单表；
selectfriendidfromuserwhereuid='1274';
情况2）问：
想查出1274号用户的所有好友的名字？
答：
本表的friendid字段和本表的uid字段关联；---自连接。
//---fromuserasu1innerjoinuserasu2onu1.uid=u2.friendid
答案：
建一个QQdb，一张《users》表，若干相关好友记录。
//见sql文件：
\《▲user.sql》
selectu1.uid,u1.name,u2.uidas`好友id`,u2.nameas`好友名称`
fromuserasu1innerjoinuserasu2
onu1.friendid=u2.uidwhereu1.name='张三'
groupbyu2.uid;---添加groupby，消去好友也有多个好友时的重复记录；---合并为一组。
---当然，实际应用中，因为用户表的其他字段太多，造成冗余大，这样的设计有缺陷，无实用性。
最终可用*-*添加中间好友表解决。
/---外连接
//所有确定了航班的乘客的情况；
select*frompassengerwherescheduled_fisnotnull;
//所有确定了航班的乘客和航班的情况；
select*from`passenger`innerjoinflighton`passenger`.`scheduled_f`=flight.`f_num`;
//所有乘客的航班情况；
select*from`passenger`leftjoinflighton`passenger`.`scheduled_f`=flight.`f_num`;
//“所有航班的售票（乘客）情况”;
select*fromflightleftjoin`passenger`on`passenger`.`scheduled_f`=flight.`f_num`;
==【外连接】
------先交叉连接，然后某一方表的所有行，全部输出，在结果集临时表中显示，另一张表仅关联记录输出。
<左外连接>-----先交叉连接，再左表全显示，右表显示条件匹配值。
//右连接反向。
书写方式）select*from左表LEFTJOIN右表ON连接条件;
《实体关系在Table上的映射》
实体关系：
关联，依赖，继承。
==关联关系映射的种类：
1-1、1-*、*-*；
1-1如何映射到表中：
-映射技术：
1）两表都增加关联字段;---双向关联。
2）两表关联字段对置为主外键，且可以建立约束。
3）简化起见，也可以只建立单边。
---单向关联。
1-*如何映射：
映射技术：
1）多端表加关联字段;---单向关联。
2）一端表主键作多端表关联字段（外键）值，且可以建立约束。
//问题：
为什么不能在一端表添加外键？
因为：
将导致一端表记录，要么外键字段重复组、要么其余字段数据冗余（字段值重复）；
求平均值：
法I）null字段也要加入求平均值，null应改为0；
缺点：
动表；优点：
快；
法II）则需要用sum（）---算出值总和；用count（）---算出记录总数；再作除法；
//练习：
//所有有了乘客的航班的情况
selectflight.*,passenger.`name`as'该乘客是'from`passenger`innerjoinflighton`flight`.`f_num`=passenger.`scheduled_f`;
//所有乘客的航班情况
select`passenger`.`name`,flight.*from`passenger`leftjoin`flight`onpassenger.`scheduled_f`=flight.`f_num`;
//所有已经有航班的乘客的情况
select*frompassengerwherescheduled_fisnotnull;
//“所有没有确定航班的乘客的情况”
select*frompassengerwherescheduled_fisnull;
//“王森乘客的航班详细情况”
select*frompassengerwhereNAME=（selectnamefrompassengerwherescheduled_fisnull）;
//“按售票数排名所有航空公司（降序），并列出各公司售票数量”
selectflight.*,COUNT（scheduled_f）fromflightleftjoinpassengeron`flight`.`f_num`=passenger.`scheduled_f`groupbyflight.`company`orderbyscheduled_fdesc;
//级联插入:
8号车记录，12号发动机记录，且8号车装得是12号发动机
insertinto`car`（cid,f_eid,title,color）values（8,12,'BMW','red'）;
insertinto`engine`（eid,title,product_date,power,f_carid）values（12,'本田','2009-05-26',300,8）;
updatecarsetF_eid=eid;
updateenginesetF_carid=cid;
//查出8号车所装发动机号？
selectcidas'车号',f_eidas'发动机号'fromcarwherecid=8;
//查出8号车的品牌？
selectcidas'车号',titleas'车牌'fromcarwherecid=8;
//134号车的发动机的功率？
（前提：
134号车一定装有发动机）
select`engine`.`power`from`engine`where`engine`.eid=（selectf_eidas'发动机号'fromcarwherecid=134）;
//若果134车没有装发动机
selectcar.`cid`,`engine`.powerfromcarleftjoin`engine`oncar.`F_eid`=`engine`.`eid`wherecar.`cid`=134;
//列出所有BMW车的发动机功率情况？
selectcar.`title`,engine.powerfromcarleftjoinengineon`car`.`F_eid`=`engine`.`eid`wherecar.`title`='BMW';
//BMW车的发动机平均功率？
selectAVG（`engine`.`power`）as'平均功率'fromcarleftjoinengineoncar.`F_eid`=engine.`eid`wherecar.`title`='BMW';
selectAVG（`engine`.`power`）as'平均功率'fromcarinnerjoinengineoncar.`F_eid`=engine.`eid`wherecar.`title`='BMW';
//---左外连接和内连接记录数不同，但AVG（）只对有值字段值求平均值，不包括null；如果null字段也要加入求平均值，null应改为0；
注意：
▲Null值不计算入AVG（）平均值；
//AVG（）只计算有值记录的平均值；---[有值平均值]；
▲如果要求[绝对平均值]---要计算的平均值，是有值的值总和/（有值+没值）记录总数
selectcar.title,AVG（engine.power）fromcarLeftJoinengineoncar.f_eid=engine.eidGROUPBYcar.`title`HAVINGcar.title='BMW';
或
selectcar.title,AVG（engine.power）fromcarLeftJoinengineoncar.f_eid=engine.eidwherecar.title='BMW'GROUPBYcar.`title`;
//单表过滤用where加在前也可以；
selectcar.`title`,COUNT（*）,AVG（`engine`.`power`）as'有值平均功率'
fromcarleftjoinengine
oncar.`F_eid`=engine.`eid`
wherecar.`title`='BMW'
groupbycar.`title`;
//各种牌子汽车的发动机平均功率
selectcar.`title`as'品牌',count（*）,AVG（`engine`.`power`）as'平均功率'fromcarleftjoinengineon`car`.`F_eid`=engine.`eid`groupbycar.`title`;
//列出114航班的所有乘客情况？
select`flight`.`id`as'航班号',`passenger`.*frompassengerleftjoinflightonflight.`f_num`=passenger.`scheduled_f`wherepassenger.`scheduled_f`=12956;
//张三所在航班的情况；
分析：
乘客情况---passenger表；
114航班---flight表、passenger表（如果卖了票）
如果没有卖票，则仅在flight表中；
∴---多表；
什么连接---可能没卖票---外连接；
分析：
张三可能坐了飞机，也可能没坐飞机。
selectpassenger.`name`,flight.*frompassengerLEFTJOINflighton`passenger`.`scheduled_f`=`flight`.`f_num`where`passenger`.`name`='张三';
//所有航班的所有乘客的情况。
select*fromflightleftjoinpassengeronflight.`f_num`=passenger.`scheduled_f`;
//某航空公司的所有航班的所有乘客的情况。
select*fromflightleftjoinpassengeron`flight`.`f_num`=passenger.`scheduled_f`whereflight.`company`='中国南方航空';
//分析：
该航空公司，可能航班还没有乘客----外连接。
select*FROMflightasfLEFTJOINpassengeraspONf.`f_num`=p.`scheduled_f`wheref.`company`like'%南方%';
三大范式，---●是老式的数据建模方式，笨拙；
现代按OOP分析实体-关系后，再按OOD映射建立数据模型，之后自然就符合三大范式；无需再单独规范；
《原始表》----缺陷：
记录可能不唯一；可能有重复组；很多查询条件失效；添加、修改都麻烦。
解决方案:
[第一范式]（First-NormalForm,1NF）规范化操作；缺陷：
数据冗余大
1）确立主键。
2）消除重复组。
但仍然有冗余。
-结果：
①消除了重复组；②记录唯一了（但可能是复合主键）；③但数据冗余大；（复合主键下记录大量字段重复）
解决方案：
[第二范式]（Second-NormalForm,2NF）规范化操作；
消除部分依赖性。
//将复合主键拆开，其它主键字段，独立出去，成为一张新表；
结果：
①消除了重复组；②记录唯一了（且是单一主键）；③消除部分依赖性，（一表变多表）关联表产生；本表记录唯一，重复记录消失了；
缺陷：
④但传递依赖字段仍可能存在，数据冗余仍有；
仍有一些字段，原来是依赖于分离出去的主键字段而存在于本表中的；其对本表的主键的依赖是通过其它字段传递来的（传递依赖性），其非本表必然字段；
解决方案：
[第三范式]（Third-NormalForm,3NF）规范化操作；
消除传递依赖性。
结果：
①消除了重复组；②记录唯一了（且是单一主键）；③消除部分依赖性，关联表产生；本表记录唯一，重复记录消失了；④消除传递依赖字段，数据冗余消失；
简记：
总结：
1NF：
1）删除重复组。
一格一值。
2）必有主键。
记录有了唯一性。
---------->【第一范式】1NF
2NF：
3）消除部分依赖性。
拆开复合主键，各主键字段成为独立的表。
---------->【第二范式】2NF
3NF：
4）消除可传递依赖性。
移走不和主键依赖的字段，
-----------------------------------多对多-------------------------------[三表联查]
映射技术：
1）选择或添加一张中间表，该表中建立两个关联字段;分别与原两张表主键建立关联。
2）多端表主键可与中间表关联字段（外键），建立约束。
//一个*-*变成了两个1-*
//--------多对多---------[三表联查]
//sid3号同学的姓名？
selectname,sidfromstudentwheresid='sid3';
//Java这门课的课时
selectname,Timefromcoursewherenamelike'%Java%';
//sid3号同学选了几门课？
select`score`.`f_sid`,COUNT（*）as'所选课程数'fromscorewhere`score`.`f_sid`='sid3'groupbyscore.`f_sid`;//改，标准。
//分析：
如果该同学一门课也没选，学生号只会出现在student表中，所选课程现在score表中----多表；
什么连接？
学号必须出现，右边关联选出，---外连接；
selectstudent.`SID`,COUNT（score.`f_cid`）,fromstudentleftjoinscoreonstudent.`SID`=`score`.`f_sid`wherestudent.`SID`='sid3'groupby`student`.`SID`;
//#哪几门课？
//分析：
该同学可能一门课也没选，因此学号应该来自student表；
所选课程在score表中，课程详细情况在course表中；
所以---多表，是3表联查：
student---score---course
什么连接？
没选课的也要出，因此为外连接。
技术点：
3表左外连接查询；
#3表交叉连接
select*fromstudentinnerjoincourseinnerjoinscore;
#3表内连接
select*fromstudentinnerjoincourseinnerjoinscore
onstudent.`SID`=score.`f_sid`
andscore.`f_cid`=course.`cid`
wherestudent.`SID`='sid3';
#3表内连接业务意义：
左表与中表内连接---所有选了课的同学选课情况，
该临时表再与右表内连接---所有选了课的同学的所选课的课程情况（选课情况+课程情况）
//连续2次内连接
#3表外连接
select*fromstudentleftjoinscoreonstudent.`SID`=score.`f_sid`
leftjoincourseonscore.`f_cid`=course.`cid`
wherestudent.`SID`='sid3';
#3表外连接业务意义：
左表与中表外连接---所有同学（选了课的+没选可的）的选课情况；
该临时表再与右表外连接---（临时表全保留）所有同学（选了课的+没选可的）的所选课情况及课程情况
//连续2次外连接
select`student`.sid,course.name,course.timefromstudentleftjoinscoreonstudent.`SID`=score.`f_sid`
leftjoincourseonscore.`f_cid`=course.`cid`
wherestudent.`SID`='sid3';
//sid3号同学选了哪几门课？
考了多少分？
select`student`.`Name`,`score`.`f_cid`,score.`grade`fromstudentleftjoinscoreonstudent.`SID`=`score`.`f_sid`wherescore.`f_sid`='sid3';//原：
片面，没有查到课程的名称（需要三表联查）
//所有选了课的学生的选课情况
selectstudent.`Name`,course.*,score.`grade`fromstudentinnerjoinscoreonstudent.`SID`=score.`f_sid`
innerjoincourseonscore.`f_cid`=course.`cid`;
//"张三选了哪些课?
"
selectstudent.`Name`,score.`f_cid`,course.`name`fromstudentleftjoinscoreonstudent.`SID`=score.`f_sid`
leftjoincourseonscore.`f_cid`=course.`cid`
wherestudent.`Name`='张三';
//"张三选了哪些课，成绩如何?
（张三各科考试情况？
"）//"张三选的课,课时是多少?
"
selectstudent.`Name`,score.`f_cid`,course.`name`,course.Time,`score`.`grade`fromstudentleftjoinscoreonstudent.`SID`=score.`f_sid`
leftjoincourseonscore.`f_cid`=course.`cid`
wherestudent.`Name`='张三';
//张三选的课,总课时是多少？
平均课时是多少？
selectstudent.`Name`,score.`f_cid`,course.`name`,sum（course.Time）,avg（course.Time）fromstudentleftjoinscoreonstudent.`SID`=score.`f_sid`
leftjoincourseonscore.`f_cid`=course.`cid`
wherestudent.`Name`='张三'
groupbystudent.`Name`;
//◆查出各位同学的Java成绩。
//----最难的题
selectstudent.`Name`,course.nameas'课程名',score.`grade`fromstudentleftjoinscoreonstudent.`SID`=score.`f_sid`leftjoincourseonscore.`f_cid`=course.`cid`wherecourse.name='java';//原，缺陷左表没有选Java的学生都被过滤掉了。
selectstudent.`Name`,course.nameas'课程名',score.`grade`fromstudentleftjoin（scoreinnerjoincourseonscore.`f_cid`
升级会员 