医院类网站建设与维护,江山网站建设,wordpress浮动小人,单页推广网站模版在学习《SQL进阶教程学习》的记录笔记#xff0c;现学现用效率真的很高#xff0c;带着问题学习#xff0c;记忆会深很多很多。 CASE表达式
CASE表达式有简单CASE表达式#xff08;simple caseexpression#xff09;和搜索CASE表达式#xff08;searched case expressi…在学习《SQL进阶教程学习》的记录笔记现学现用效率真的很高带着问题学习记忆会深很多很多。 CASE表达式
CASE表达式有简单CASE表达式simple caseexpression和搜索CASE表达式searched case expression两种写法。 --简单CASE表达式CASE sexWHEN 1 THEN ’男’WHEN 2 THEN ’女’ELSE ’其他’ END--搜索CASE表达式CASE WHEN sex 1THEN’男’WHEN sex 2THEN’女’ELSE ’其他’ END
tips
因为GROUP BY子句比SELECT语句先执行所以在GROUP BY子句中引用在SELECT子句里定义的别称是不被允许的。
用一条SQL语句进行不同条件的统计
进行不同条件的统计是CASE表达式的著名用法之一。
新手用WHERE子句进行条件分支高手用SELECT子句进行条件分支 -- 男性人口SELECT pref_name,SUM(population)FROM PopTbl2WHERE sex 1GROUP BY pref_name;-- 女性人口SELECT pref_name,SUM(population)FROM PopTbl2WHERE sex 2GROUP BY pref_name; SELECT pref_name,--男性人口SUM( CASE WHEN sex 1THEN population ELSE 0 END) AS cnt_m,--女性人口SUM( CASE WHEN sex 2THEN population ELSE 0 END) AS cnt_fFROM PopTbl2GROUP BY pref_name;
用CHECK约束定义多个列的条件关系 在UPDATE语句里进行条件分支 --条件1UPDATE SalariesSET salary salary 0.9WHERE salary 300000;--条件2UPDATE SalariesSET salary salary 1.2WHERE salary 250000 AND salary 280000;
两个UPDATE函数依次执行的话可能会反复操作同一个数据因此会造成错误。因此使用UPDATE与CASE函数结合而且因为只需执行一次所以速度也更快 --用CASE表达式写正确的更新操作UPDATE SalariesSET salary CASE WHEN salary 300000THEN salary 0.9WHEN salary 250000 AND salary 280000THEN salary 1.2ELSE salary END; 主键值调换 --1将a转换为中间值dUPDATE SomeTableSET p_key dWHERE p_key a;--2将b调换为aUPDATE SomeTableSET p_key aWHERE p_key b;--3将d调换为bUPDATE SomeTableSET p_key bWHERE p_key d;
没有必要执行3次UPDATE操作因此可以用UPDATE结合CASE --用CASE表达式调换主键值UPDATE SomeTableSET p_key CASE WHEN p_key aTHEN bWHEN p_key bTHEN aELSE p_key ENDWHERE p_key IN (a, b);
表之间的数据匹配 --表的匹配使用IN谓词SELECT course_name,CASE WHEN course_id IN(SELECT course_id FROM OpenCoursesWHERE month 200706) THEN○ELSE×END AS 6月,CASE WHEN course_id IN(SELECT course_id FROM OpenCoursesWHERE month 200707) THEN○ELSE×END AS 7月,CASE WHEN course_id IN(SELECT course_id FROM OpenCoursesWHERE month 200708) THEN○ELSE×END AS 8月FROM CourseMaster;--表的匹配使用EXISTS谓词SELECT CM.course_name,CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE month 200706AND OC.course_id CM.course_id) THEN○ELSE×END AS 6月,CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE month 200707AND OC.course_id CM.course_id) THEN○ELSE×END AS 7月,CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE month 200708AND OC.course_id CM.course_id) THEN○ELSE×END AS 8月FROM CourseMaster CM;
tips
使用EXISTS时还要把两个表格的相同列表示出来。 自连接 排序
方法一、使用窗口函数 --排序使用窗口函数SELECT name, price,RANK() OVER (ORDER BY price DESC) AS rank_1,DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2FROM Products;
tips:
rank_1跳过了之后的位次rank_2没有跳过而是连续排序。
方法二、使用自连接 --排序从1开始。如果已出现相同位次则跳过之后的位次SELECT P1.name,P1.price,(SELECT COUNT(P2.price)FROM Products P2WHERE P2.price P1.price) 1 AS rank_1FROM Products P1ORDER BY rank_1;
三值逻辑和NULL
二值逻辑true、false
三值逻辑three-valued logictrue、false、unknown
两种NULL分别指的是“未知”unknown和“不适用”not applicable,inapplicable。“未知”指的是“虽然现在不知道但加上某些条件后就可以知道”而“不适用”指的是“无论怎么努力都无法知道”。
三个真值之间的优先级顺序
AND的情况 false unknown trueOR的情况 true unknown false HAVING子句的力量
SQL是一种基于“面向集合”思想设计的语言同样具备这种设计思想的语言很少最开始学习过了某种理念的语言后心理上会形成思维定式从而妨碍我们理解另一种理念的语言。
寻找缺失的编号 -- 如果有查询结果说明存在缺失的编号SELECT’存在缺失的编号’AS gapFROM SeqTblHAVING COUNT() MAX(seq);
如果这个查询结果有1行说明存在缺失的编号如果1行都没有说明不存在缺失的编号。 -- 查询缺失编号的最小值SELECT MIN(seq 1) AS gapFROM SeqTblWHERE (seq 1) NOT IN ( SELECT seq FROM SeqTbl);
tips:
HAVING子句是可以单独使用的
用HAVING子句进行子查询求众数
众数mode:指的是在群体中出现次数最多的值. --求众数的SQL语句(1)使用谓词SELECT income, COUNT() AS cntFROM GraduatesGROUP BY incomeHAVING COUNT() ALL ( SELECT COUNT()FROM GraduatesGROUP BY income); --求众数的SQL语句(2)使用极值函数SELECT income, COUNT() AS cntFROM GraduatesGROUP BY incomeHAVING COUNT() ( SELECT MAX(cnt)FROM ( SELECT COUNT() AS cntFROM GraduatesGROUP BY income) TMP ) ;
用HAVING子句进行自连接求中位数
中位数median指的是将集合中的元素按升序排列后恰好位于正中间的元素。 --求中位数的SQL语句在HAVING子句中使用非等值自连接SELECT AVG(DISTINCT income)FROM (SELECT T1.incomeFROM Graduates T1, Graduates T2GROUP BY T1.income--S1的条件HAVING SUM(CASE WHEN T2.income T1.income THEN 1 ELSE 0 END) COUNT() / 2--S2的条件AND SUM(CASE WHEN T2.income T1.income THEN 1 ELSE 0 END) COUNT() / 2 ) TMP;
查询不包含NULL的集合
COUNT函数的使用方法有COUNT和COUNT列名两种它们的区别有两个
第一个是性能上的区别第二个是COUNT可以用于NULL而COUNT列名与其他聚合函数一样要先排除掉NULL的行再进行统计。第二个区别也可以这么理解COUNT查询的是所有行的数目而COUNT列名查询的则不一定是。 --查询“提交日期”列内不包含NULL的学院(1)使用COUNT函数SELECT dptFROM StudentsGROUP BY dptHAVING COUNT() COUNT(sbmt_date); --查询“提交日期”列内不包含NULL的学院(2)使用CASE表达式SELECT dptFROM StudentsGROUP BY dptHAVING COUNT() SUM(CASE WHEN sbmt_date IS NOT NULLTHEN 1ELSE 0 END);
tips
表不是文件记录也没有顺序所以SQL不进行排序。SQL不是面向过程语言没有循环、条件分支、赋值操作。这个思维模式真的很难改变我的第一反应就是循环、条件分支SQL通过不断生成子集来求得目标集合。SQL不像面向过程语言那样通过画流程图来思考问题而是通过画集合的关系图来思考。SQL是集合语言。 外连接的用法 EXISTS 让SQL飞起来
参数是子查询时使用EXISTS代替IN
tips
如果IN的参数是“1, 2, 3”这样的数值列表一般还不需要特别注意。但是如果参数是子查询那么就需要注意了。但是从代码的可读性上来看IN要比EXISTS好。使用IN时的代码看起来更加一目了然易于理解。因此如果确信使用IN也能快速获取结果就没有必要非得改成EXISTS了。当IN的参数是子查询时数据库首先会执行子查询然后将结果存储在一张临时的工作表里内联视图然后扫描整个视图。很多情况下这种做法都非常耗费资源。使用EXISTS的话数据库不会生成临时的工作表。
使用EXISTS时更快的原因有以下两个
如果连接列id上建立了索引那么查询Class_B时不用查实际的表只需查索引就可以了。如果使用EXISTS那么只要查到一行数据满足条件就会终止查询不用像使用IN时一样扫描全表。在这一点上NOT EXISTS也一样。 tips
极值函数在统计时会把为NULL的数据排除掉。
不定期更新。。。
