SLQ实战语句总结三.docx

1、SLQ实战语句总结三法二【解决上述缺陷】： 1)先找出装在了黑色车上面的发动机的最小马力值；select MIN(ENGINE.power) from car inner join engine on ENGINE.F_carid=car.cid where car.color=black; 2)所有装了发动机的黑色汽车中，马力值等于该最小值的所有记录全出；select * from car inner join engine on car.F_eid=ENGINE.eid where car.color=black and engine.power=(select MIN(ENGINE.po

2、wer) from car inner join engine on ENGINE.F_carid=car.cid where car.color=black); #结论：所以，一对一 表中，能增加关联字段的，尽量增加，以便更多使用 单表查询； =【自连接】 缺点：字段太多，造成冗余大。 技术实质：本表的一条记录的一个字段，和本表的另一条记录的另一个字段，关联。 业务意义：比如，QQ中的用户，如何表达，用户间互为好友？ 可以在用户表增加好友关联字段 uid name gender icq friendid 1274 张三 男 136* * /用户字段越多，好友越多，冗余越大； 1274 张三

3、男 136* * 1274 张三 男 136* * 1274 张三 男 1366243 115 . 1477 王伟 男 137* * 此时，想查出每个人关联的好友情况，怎么办？ 情况1）问：想查出1274号用户的所有好友的uid号？ 答：单表； select friendid from user where uid=1274; 情况2）问：想查出1274号用户的所有好友的名字？ 答：本表的friendid字段和本表的uid字段关联；-自连接。 /-from user as u1 inner join user as u2 on u1.uid=u2.friendid 答案：建一个QQdb，一张u

4、sers表，若干相关好友记录。 /见sql文件：user.sql select u1.uid,u1.name, u2.uid as 好友id,u2.name as 好友名称 from user as u1 inner join user as u2 on u1.friendid=u2.uid where u1.name=张三 group by u2.uid; -添加group by，消去好友也有多个好友时的重复记录；-合并为一组。 -当然，实际应用中，因为用户表的其他字段太多，造成冗余大，这样的设计有缺陷，无实用性。 最终可用*-*添加中间好友表解决。 /-外连接 /所有确定了航班的乘客的情况

5、； select * from passenger where scheduled_f is not null; /所有确定了航班的乘客和航班的情况； select * from passenger inner join flight on passenger.scheduled_f=flight.f_num; /所有乘客的航班情况； select * from passenger left join flight on passenger.scheduled_f=flight.f_num ; /“所有航班的售票（乘客）情况”; select * from flight left join p

6、assenger on passenger.scheduled_f=flight.f_num; =【外连接】 -先交叉连接，然后某一方表的所有行，全部输出，在结果集临时表中显示，另一张表仅关联记录输出。 - 先交叉连接，再左表全显示，右表显示条件匹配值。 /右连接反向。 书写方式） select * from 左表 LEFT JOIN 右表 ON 连接条件; 实体关系在Table上的映射 实体关系：关联，依赖，继承。 =关联关系映射的种类：1-1、1-*、*-*； 1-1 如何映射到表中：-映射技术：1)两表都增加关联字段;-双向关联。 2)两表关联字段对置为主外键，且可以建立约束。 3)简化

7、起见，也可以只建立单边。-单向关联。 1-* 如何映射：映射技术：1)多端表加关联字段;-单向关联。 2)一端表主键作多端表关联字段(外键)值，且可以建立约束。 /问题：为什么不能在一端表添加外键？因为：将导致一端表记录，要么外键字段重复组、要么其余字段数据冗余(字段值重复)； 求平均值：法I）null字段也要加入求平均值，null应改为0； 缺点：动表；优点：快； 法II）则需要用 sum()-算出值总和；用count()-算出记录总数；再作除法； /练习：/所有有了乘客的航班的情况select flight.* ,passenger.name as 该乘客是 from passenger

8、inner join flight on flight.f_num=passenger.scheduled_f;/所有乘客的航班情况select passenger.name,flight.* from passenger left join flight on passenger.scheduled_f=flight.f_num;/所有已经有航班的乘客的情况select * from passenger where scheduled_f is not null;/“所有没有确定航班的乘客的情况”select * from passenger where scheduled_f is nul

9、l;/“王森乘客的航班详细情况” select * from passenger where NAME=(select name from passenger where scheduled_f is null);/“按售票数排名所有航空公司（降序），并列出各公司售票数量”select flight.*, COUNT(scheduled_f) from flight left join passenger on flight.f_num=passenger.scheduled_f group by pany order by scheduled_f desc;/级联插入: 8号车记录，12号发

10、动机记录，且8号车装得是12号发动机insert into car(cid,f_eid,title,color) values (8,12,BMW,red);insert into engine(eid,title,product_date,power,f_carid) values (12,本田,2009-05-26,300,8); update car set F_eid=eid;update engine set F_carid=cid; /查出8号车所装发动机号？select cid as 车号, f_eid as 发动机号 from car where cid = 8;/查出8号车的

11、品牌？select cid as 车号, title as 车牌 from car where cid = 8; /134号车的发动机的功率？(前提：134号车一定装有发动机)select engine.power from engine where engine.eid=(select f_eid as 发动机号 from car where cid = 134);/若果134车没有装发动机select car.cid, engine.power from car left join engineon car.F_eid=engine.eid where car.cid=134;/列出所有B

12、MW车的发动机功率情况？select car.title,engine.power from car left join engine on car.F_eid=engine.eid where car.title=BMW;/BMW车的发动机平均功率？select AVG(engine.power) as 平均功率 from car left join engine on car.F_eid=engine.eid where car.title=BMW; select AVG(engine.power) as 平均功率 from car inner join engine on car.F_e

13、id=engine.eid where car.title=BMW; /-左外连接和内连接记录数不同，但AVG()只对有值字段值求平均值，不包括null；如果null字段也要加入求平均值，null应改为0； 注意：Null值不计算入AVG()平均值； /AVG()只计算有值记录的平均值；-有值平均值； 如果要求绝对平均值-要计算的平均值，是有值的值总和/(有值+没值)记录总数 select car.title,AVG(engine.power) from car Left Join engine on car.f_eid=engine.eid GROUP BY car.title HAVING

14、 car.title=BMW ; 或select car.title,AVG(engine.power) from car Left Join engine on car.f_eid=engine.eid where car.title=BMW GROUP BY car.title ; /单表过滤用where加在前也可以；select car.title,COUNT(*),AVG(engine.power) as 有值平均功率 from car left join engine on car.F_eid=engine.eid where car.title=BMW group by car.t

15、itle; /各种牌子汽车的发动机平均功率select car.title as 品牌,count(*), AVG(engine.power) as 平均功率 from car left join engine on car.F_eid=engine.eid group by car.title;/列出114航班的所有乘客情况？select flight.id as 航班号 ,passenger.* from passenger left join flight on flight.f_num=passenger.scheduled_fwhere passenger.scheduled_f=1

16、2956;/张三所在航班的情况；分析： 乘客情况-passenger表； 114航班-flight表、passenger表(如果卖了票) 如果没有卖票，则仅在flight表中； -多表； 什么连接-可能没卖票-外连接；分析：张三可能坐了飞机，也可能没坐飞机。select passenger.name,flight.* from passenger LEFT JOIN flight on passenger.scheduled_f=flight.f_num where passenger.name=张三; /所有航班的所有乘客的情况。select * from flight left join

17、passenger on flight.f_num=passenger.scheduled_f;/某航空公司的所有航班的所有乘客的情况。select * from flight left join passenger on flight.f_num=passenger.scheduled_f where pany = 中国南方航空;/分析：该航空公司，可能航班还没有乘客-外连接。select * FROM flight as f LEFT JOIN passenger as p ON f.f_num=p.scheduled_f where pany like %南方%; 三大范式，-是老式的数

18、据建模方式，笨拙； 现代按OOP分析实体-关系后，再按OOD映射建立数据模型，之后自然就符合三大范式；无需再单独规范； 原始表-缺陷：记录可能不唯一；可能有重复组；很多查询条件失效；添加、修改都麻烦。 解决方案:第一范式（First-Normal Form, 1NF）规范化操作；缺陷：数据冗余大 1）确立主键。2）消除重复组。但仍然有冗余。 -结果：消除了重复组；记录唯一了（但可能是复合主键）；但数据冗余大；(复合主键下记录大量字段重复) 解决方案：第二范式（Second-Normal Form, 2NF）规范化操作； 消除部分依赖性。/将复合主键拆开，其它主键字段，独立出去，成为一张新表；

19、结果：消除了重复组；记录唯一了（且是单一主键）；消除部分依赖性，（一表变多表）关联表产生；本表记录唯一，重复记录消失了； 缺陷：但传递依赖字段仍可能存在，数据冗余仍有； 仍有一些字段，原来是依赖于分离出去的主键字段而存在于本表中的；其对本表的主键的依赖是通过其它字段传递来的（传递依赖性），其非本表必然字段； 解决方案：第三范式（Third-Normal Form, 3NF）规范化操作； 消除传递依赖性。 结果：消除了重复组；记录唯一了（且是单一主键）；消除部分依赖性，关联表产生；本表记录唯一，重复记录消失了；消除传递依赖字段，数据冗余消失； 简记： 总结：1NF：1）删除重复组。 一格一值。

20、2）必有主键。记录有了唯一性。 -【第一范式】 1NF 2NF：3）消除部分依赖性。 拆开复合主键，各主键字段成为独立的表。 -【第二范式】 2NF 3NF：4) 消除可传递依赖性。 移走不和主键依赖的字段， -多对多-三表联查映射技术：1)选择或添加一张中间表，该表中建立两个关联字段;分别与原两张表主键建立关联。 2)多端表主键可与中间表关联字段(外键)，建立约束。 /一个*-* 变成了两个 1-*/-多对多-三表联查/sid3号同学的姓名？select name ,sid from student where sid=sid3;/Java这门课的课时select name ,Time fr

21、om course where name like %Java%;/sid3号同学选了几门课？ select score.f_sid,COUNT(*) as 所选课程数 from score where score.f_sid=sid3 group by score.f_sid;/改，标准。/分析：如果该同学一门课也没选，学生号只会出现在student表中，所选课程现在score表中-多表； 什么连接？学号必须出现，右边关联选出，-外连接；select student.SID,COUNT(score.f_cid), from student left join score on student

22、.SID=score.f_sid where student.SID=sid3 group by student.SID; /#哪几门课？ /分析：该同学可能一门课也没选，因此学号应该来自student表； 所选课程在score表中，课程详细情况在course表中； 所以-多表，是3表联查：student-score-course 什么连接？没选课的也要出，因此为外连接。 技术点：3表左外连接查询； #3表交叉连接select * from student inner join course inner join score ;#3表内连接select * from student inner

23、 join course inner join score on student.SID=score.f_sid and score.f_cid=course.cid where student.SID =sid3; #3表内连接业务意义：左表与中表内连接-所有选了课的同学选课情况， 该临时表再与右表内连接- 所有选了课的同学的所选课的课程情况（选课情况+课程情况） /连续2次内连接 #3表外连接select * from student left join score on student.SID=score.f_sid left join course on score.f_cid=cou

24、rse.cid where student.SID =sid3;#3表外连接业务意义： 左表与中表外连接-所有同学（选了课的+没选可的）的选课情况 ； 该临时表再与右表外连接-（临时表全保留）所有同学（选了课的+没选可的）的所选课情况及课程情况 /连续2次外连接 select student.sid,course.name,course.time from student left join score on student.SID=score.f_sid left join course on score.f_cid=course.cid where student.SID =sid3; /

25、 sid3号同学选了哪几门课？考了多少分？select student.Name,score.f_cid,score.grade from student left join score on student.SID=score.f_sidwhere score.f_sid=sid3;/原：片面，没有查到课程的名称(需要三表联查)/所有选了课的学生的选课情况select student.Name, course.*, score.grade from student inner join score on student.SID=score.f_sid inner join course on

26、 score.f_cid=course.cid;/张三选了哪些课? select student.Name,score.f_cid,course.name from student left join score on student.SID=score.f_sid left join course on score.f_cid=course.cid where student.Name=张三; /张三选了哪些课，成绩如何? （张三各科考试情况？）/张三选的课,课时是多少? select student.Name,score.f_cid,course.name,course.Time,scor

27、e.grade from student left join score on student.SID=score.f_sid left join course on score.f_cid=course.cid where student.Name=张三; /张三选的课,总课时是多少？平均课时是多少？ select student.Name,score.f_cid,course.name,sum(course.Time), avg(course.Time)from student left join score on student.SID=score.f_sid left join cou

28、rse on score.f_cid=course.cid where student.Name=张三 group by student.Name; /查出各位同学的Java 成绩。/-最难的题 select student.Name,course.name as 课程名,score.grade from student left join score on student.SID=score.f_sid left join course on score.f_cid=course.cid where course.name=java; /原，缺陷左表没有选Java的学生都被过滤掉了。select student.Name,course.name as 课程名,score.grade from student left join ( score inner join course on score.f_cid