++11查询可用内存总数_SQL学习-多表查询

1.表的加法

union（重复值保留一个）

union all（保留重复值）

运算对象的列数与列的类型必须一致。

select 课程号, 课程名称
from course
union
select 课程号, 课程名称
from course1

2.表的联结

cross join 交叉联结

交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，返回结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

inner join 内联结

也称为等值连接，返回两张表都同时存在的部分。会丢失数据。

select a.学号, a.姓名, b.课程号
from student as a inner join score as b
on a.学号 = b.学号

left join 左联结

结果包括左表（出现在JOIN子句最左边）中的所有行，不包括右表中的不匹配行。

select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号=b.学号

right join 右联结

结果包括右表（出现在JOIN子句最右边）中的所有行，不包括左表中的不匹配行。

select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号=b.学号;

full join 全联结

返回左表和右表的全部结果。

mysql不支持full join。

汇总：

3.联结应用案例

4.case表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

WHEN 子句中的“< 判断表达式 >”就是类似“列 = 值”这样，返回值为真值（TRUE/FALSE/UNKNOWN）的表达式。我们也可以将其看作使用 =、!= 或者 LIKE、BETWEEN 等谓词编写出来的表达式。

CASE 表达式会从对最初的 WHEN 子句中的“< 判断表达式 >”进行求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果为真（TRUE），那么就返回 THEN 子句中的表达式，CASE 表达式的执行到此为止。如果结果不为真，那么就跳转到下一条 WHEN 子句的求值之中。

如果直到最后的 WHEN 子句为止返回结果都不为真，那么就会返回 ELSE中的表达式，执行终止。

练习：

sqlzoo练习：

The JOIN operation

1.第一個例子列出球員姓氏為'Bender'的入球數據。 * 表示列出表格的全部欄位，簡化了寫matchid, teamid, player, gtime語句。修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = 'GER'

SELECT matchid, player
FROM goal 
WHERE teamid = 'GER'

2.由以上查詢，你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。
留意在 goal 表格中的欄位 matchid ，是對應表格game的欄位id。我們可以在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2
  FROM game
where id = 1012

3.我們可以利用JOIN來同時進行以上兩個步驟。
語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON 表示如何找出 game中每一列應該配對goal中的哪一列 -- goal的 id 必須配對game的 matchid 。 簡單來說，就是 ON (game.id=goal.matchid)

以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)

修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。

SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
where teamid = 'GER';

4.使用上題相同的 JOIN語句，

列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

select team1,team2,player
from goal join game on (matchid=id)
where player like 'Mario%';

5.表格eteam 貯存了每一國家隊的資料，包括教練。你可以使用語句 goal JOIN eteam on teamid=id來合拼 JOIN 表格goal 到 表格eteam。

列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

SELECT player, teamid,coach, gtime
FROM goal join eteam on teamid=id
WHERE gtime<=10;

6.要合拼JOIN 表格game 和表格 eteam，你可以使用game JOIN eteam ON (team1=eteam.id)
或game JOIN eteam ON (team2=eteam.id)
注意欄位id同時是表格game 和表格 eteam的欄位，你要清楚指出eteam.id而不是只用id

列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。

select mdate,teamname
from game join eteam on team1=eteam.id
where coach = 'Fernando Santos';

7.列出場館 'National Stadium, Warsaw'的入球球員。

select player
from game join goal on id=matchid
where stadium = 'National Stadium, Warsaw';

8.以下例子找出德國-希臘Germany-Greece 的八強賽事的入球

修改它，只列出全部賽事，射入德國龍門的球員名字。

select distinct b.player
from game as a inner join goal as b on a.id = b.matchid
where (b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER');

9.列出隊伍名稱teamname和該隊入球總數

SELECT teamname, count(b.gtime) as 入球总数
FROM eteam as a inner JOIN goal as b ON a.id=b.teamid
group by teamname
ORDER BY teamname;

10.列出場館名和在該場館的入球數字。

select stadium, count(b.gtime) as 入球总数
from game as a inner join goal as b on a.id=b.matchid
group by stadium
order by stadium;

11.每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。

SELECT a.id,a.mdate,count(b.gtime) as 进球总数
FROM game as a inner JOIN goal as b ON a.id =b.matchid 
WHERE (team1 = 'POL' OR team2 = 'POL')
group by a.id,a.mdate;

12.每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

SELECT a.id,a.mdate,count(b.gtime) as 进球总数
FROM game as a inner JOIN goal as b ON a.id =b.matchid 
WHERE b.teamid = 'GER'
group by a.id,a.mdate;

13.List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT a.mdate,
a.team1, sum(case when a.team1=b.teamid then 1 else 0 end) as score1,
a.team2, sum(case when a.team2=b.teamid then 1 else 0 end) as score2
from game as a left join goal as b on a.id = b.matchid
group by a.id,a.mdate,a.team1,a.team2
order by a.mdate,a.id,a.team1,a.team2;