sql学习记录（二）

腹泻式更新了属于是

（一）看这里：sql学习记录（一）

分组

group by

用于将结果集进行分组，往往结合各种sql函数使用，要注意分组的先后顺序

#筛选出各subject中最早的年份
select subject,min(yr)
from nobel
group by subject
#Show the number of different winners for each subject.
select subject,count(distinct winner)
from nobel
group by subject

having

having相当于用在groupby后的where，要注意的是条件筛选的顺序。

select winner
from nobel
group by winner
having count(*)>1 #看每个分组的数目是否大于一
#下面这个是先把yr>=2000先选出，再按年份，学科进行分组
select yr,subject
from nobel
where yr>=2000
group by yr,subject
having count(*)=3

限制返回数据记录数

top

SELECT TOP 2 * FROM Persons #前两条
SELECT TOP 50 PERCENT * FROM Persons#前50%

limit

适用于mysql，在限制返回记录数时，往往排序orderby很重要，而且配合Offset使用，offset指偏移，即排除前几行。

#返回前5行
SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 5;
#跳过前三行，获取之后的5行
SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 5 OFFSET 3;
#mysql环境下，意思同上
SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 3 , 5;
#获取全公司薪水第二高的员工
SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

fetch

格式不一样，但是用法同上

#语法
OFFSET offset_rows { ROW | ROWS }
FETCH { FIRST | NEXT } [ fetch_rows ] { ROW | ROWS } ONLY
#{}内的词可互相替换

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM employees
ORDER BY 
    salary DESC
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;

case…when..

相当于if…else

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK #注意基本格式，而且往往会给个别名
FROM
    TABLE
#下面这个例子也说明了case的作用往往是相当于部分where
SELECT 
	SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT, #男生个数
	SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
	SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
	THTF_STUDENTS

join

join用于基于一些限制条件连接多个表，以查询数据

基础join

select player
from game join goal on id=matchid #on后加限制条件，如果不加就相当于两个表做笛卡尔积，即每一行都连接
where stadium='National Stadium, Warsaw'
#下面是查POL得分的比赛
SELECT matchid, mdate,count(*)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
group by matchid,mdate#注意这里分组的条件

left join

left join是指在根据条件连接两个表的时候，会在左表中返回所有的行，那些没有在右表中匹配成功的行会留空。

SELECT matchid,mdate,
team1,
sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END ) as score1, #顺便看看case
team2,
sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END ) as score2
  FROM game left JOIN goal ON matchid = id #用leftjoin是因为有要统计那些没得分的比赛
group by matchid,mdate, team1,team2
order by mdate, matchid, team1,team2

right join

把left join中的左表换成右表

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders#包括那些没人订购的订单
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

full join

left join和right join的结合（不是笛卡尔积）

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

两个表以上的join

#查询与Art Garfunkel共事过的演员
SELECT distinct name FROM
  movie JOIN casting ON movie.id=movieid #注意这里的格式
        JOIN actor   ON actorid=actor.id
where movieid in (SELECT movieid FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
where name='Art Garfunkel') and name<>'Art Garfunkel'
#查询Julie Andrews参演过的电影的主角
SELECT title,name FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE movieid in (SELECT movieid FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
where name='Julie Andrews')
GROUP BY title,name,ord
HAVING ord=1

natural join

将多个表中名字和数据类型的属性列相等的连接起来，相当于自动给你加了限制条件，注意的是如果存在多个名字和数据类型的属性，需要满足这些属性同时相等才能被连接起来。

cross join

生成两个或多个表的笛卡尔积。（直接用逗号默认是笛卡尔积）

而且有m+n列。

自连接

我们将一张表连接到自身来评估同一个表中其他行的行。

#展现上下级关系，用left join是因为最上级没有manager
SELECT 
    CONCAT(e.first_name, ' ', e.last_name) as employee,
    CONCAT(m.first_name, ' ', m.last_name) as manager 
FROM
    employees e
        LEFT JOIN
    employees m ON m.employee_id = e.manager_id
ORDER BY manager;

多查询组合

用下面的运算符来连接多个查询结果，要注意的是这多个查询结果间必须拥有相同数量的列。列也必须拥有相似的数据类型，同时，每条 SELECT 语句中的列的顺序必须相同。

union

取并集，相当于or

SELECT E_Name FROM Employees_China
UNION ALL #加上all代表相同的值可重复列出
SELECT E_Name FROM Employees_USA

Intersect

取交集，相当于and

SELECT Txn_Date FROM Store_Information
INTERSECT
SELECT Txn_Date FROM Internet_Sales;

except/minus

两者意思相同，minus是Oracle用的，A except B相当于在A的结果集中除去B的结果集，和not exists配合有奇效

#找银行中有账户但无贷款的人
(select customer_name from depositor) 
except
(select customer_name from borrower) 

关于嵌套查询的一些补充

unique

如果unique内结果中不包含重复行，则返回true

#找出2009年只开过一次的课
select I.course id
from course as T
where unique (select R.course_id
from section as R
where T.course id=R.course_id
and R.year 2009);

with

用于临时给一条查询的结果生成了一个表，以便于重复使用

with max_budget (value) as (select max(budget) from department)
select budget
from department,max budget
where department.budget max_budget.value;

exists

当exists内的结果不为空集，则返回true，not exists则相反

#找至少教一名学生的instrutor
select ID
from instructor as S
where exists (select from advisor as T where S.ID=T.i_d);

重点用法：用not exists来判断一些集合是否满足包含关系，比如当A包含于B，则代表当A-B为空集，因此用not exists和except配合

select distinct customer_name
from depositor as d1
where not exists(
(select branch_name from branch where branch_city='Brooklyn')
except 
(select branch_name from account as a ,depositor as d2 where d2.customer_name=d1.customer_name and a.account_name=d2.account_name)
)

上面这个例子是找出在位于“Brooklyn”的所有支行都有账户的所有客户，这题的切入点就在于，既然要找在位于“Brooklyn”的所有支行都有账户的客户，那么位于“Brooklyn”的所有支行这个集合必包含于该客户办的所有账户的所有支行这个集合，这里相当于一个二层循环，外层循环提供当前客户的名字，内层循环去找这个客户办的所有账户的所有支行。下面再给出一个例子自己悟：

select distinct S.ID,S.name
from student as S
where not exists(
                (select course_id from course where dept name ='Biology')
                except
                (select I.course_id from takes as T where S.ID=T.ID)
                )

关于NULL的一些补充

COALESCE

虽然（一）已经提到过了，但写到这才真正发现它的用法

COALESCE(x,y,z) = x if x is not NULL
COALESCE(x,y,z) = y if x is NULL and y is not NULL
COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
COALESCE(x,y,z) = NULL if x and y and z are all NULL

#例子
SELECT teacher.name, COALESCE(dept.name,'None')
 FROM teacher LEFT JOIN dept
           ON (teacher.dept=dept.id)

join

left或者right join都有可能会造成查询结果中NULL值的存在，要注意对它们的处理，还有count的计数此时最好不要用count(*)，因为会把null值统计进去

参考资料

sqlzoo：强烈推荐的一个可以一边练习一边学习的sql学习网站，建议对基础语法有一定了解后来使用

w3school：十分著名的w3school，用于了解sql基础语法

易百教程：跟w3school差不多，但个人觉得讲的更为详细一点