by joining two copies of the stops table we can refer to stops by name rather than by number

sql通配符

在搜索数据库中的数据时,SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。
在 SQL 中,可使用以下通配符:

通配符 描述
% 替代一个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]或者[!charlist] 不在字符列中的任何单一字符

concat函数 ,连接多个字符串,好处在于 concat里面的数是变量,变量可以是select 的字段 也可以是 通配符


image.png

  1. select name from world
  2. where name like "C%ia"

image.png

image.png注意city前面的空格

image.png

ROUND: ROUND(column_name,要保留的位数)
round 为负数达到四舍五入的效果:
round(num,0)=>整数
round(num,-1)=> 153.4=>150 (看个位数3进行四舍五入
round(num,-2)=> 153.4=>200 (看十位数5进行四舍五入

子查询与分组

子查询

https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

  1. SELECT name, continent FROM world x
  2. WHERE population >= ALL(SELECT population*3
  3. FROM world y
  4. WHERE x.continent = y.continent
  5. and y.name != x.name)

對於每一個洲份,顯示洲份和國家的數量。

  1. SELECT continent, COUNT(name) FROM world
  2. GROUP BY continent

join

image.png

image.png

SELECT name FROM casting (居然是casting 从连接表开始查询
JOIN actor ON (actor.id=actorid)
JOIN movie ON (movie.id=movieid) WHERE title = ‘Alien’
—- 思考:1、在movie里找到最具象的 然后不必纠结选哪个列属性 ,一般这种都是由内向外;
2、然后向外搜索其他的表,看能根据====哪个的‘’、=

列出電影北非諜影 ‘Casablanca’的演員名單(即是電影中各演員的真實姓名清單)使用 movieid=11768

  1. select name from actor
  2. where id in
  3. (
  4. select actorid from casting where movieid= 11768
  5. )

显示 演员x 演的电影

  1. SELECT name FROM casting
  2. JOIN actor ON (actor.id=actorid)
  3. JOIN movie ON (movie.id=movieid)
  4. WHERE title = 'Alien'
  1. --Which were the busiest years for 'Rock Hudson',
  2. --show the year and the number of movies he made each year for any year
  3. --in which he made more than 2 movies.
  4. 找到演员Rock Hudson忙碌的年 ,(以年为小组划分 ,忙碌的定义:那一年他至少拍了2部电影
  5. show year and 电影数量
  6. 关键技术点:
  7. 在全部拍的电影里过滤为 该演员拍的 (连接两个表)
  8. 以年yr为小组划分 having count(movie)>2
  9. ===》 join 之后会得到 yrcount
  10. 写的顺序:
  11. 1、因为要找到某个演员演的所有电影,所以三个表都需要连接起来
  12. 再加点筛选条件 WHERE name = 'Rock Hudson'
  13. 2、现在我们知道了该演员的所有电影,一个二维数组
  14. 然后要统计出忙碌年份
  15. group by yr having count(title)>2
  16. 3、精简列属性
  17. select yr,count(title) from casting
  18. JOIN actor ON (actor.id=actorid)
  19. JOIN movie ON (movie.id=movieid)
  20. WHERE name = 'Rock Hudson'
  21. group by yr having count(title)>2

找出演员xxx最忙碌的一年,显示年份和拍的电影数

  1. -- 演员xx拍的电影
  2. -- group by yr
  3. -- 找出最多的那一年 ===> 这个怎么排
  4. select yr, count(*) from casting
  5. join actor on (actor.id = actorid)
  6. join movie on (movie.id = movieid)
  7. where name='John Travolta'
  8. group by yr order by count(*) desc

image.png
怎么求最大?

  1. -- 演员xx拍的电影
  2. -- group by yr
  3. -- 找出最多的那一年 ===> 这个怎么排
  4. select yr, count(*) from casting
  5. join actor on (actor.id = actorid)
  6. join movie on (movie.id = movieid)
  7. where name='John Travolta'
  8. group by yr having count(*) =
  9. (
  10. select max(c) from
  11. (
  12. select yr, count(title) c from casting
  13. join actor on (actor.id = actorid)
  14. join movie on (movie.id = movieid)
  15. where name='John Travolta'
  16. group by yr
  17. ) as t -- 必须别名一下
  18. )
  1. -- List the film title and the leading actor
  2. -- for all of the films 'Julie Andrews' played in.
  3. 列出演员xx参演的电影及电影的第一主角
  4. 注意电影的title并不是唯一标示应该以movieid区分,
  5. 建议create a table of IDs in your subquery
  6. 关键点:
  7. 1、找到演员演的电影=>id
  8. 2、在电影里过滤 她是主演
  9. 3、精简列属性
  10. select name,title from casting
  11. join actor on (actior.id = actorid)
  12. join movie on (movie.id = movieid)
  13. WHERE ord = 1 and movieid in (===> 因为要在movie里找nametitle
  14. (
  15. SELECT movie.id FROM movie
  16. JOIN casting ON movie.id = movieid
  17. JOIN actor ON actor.id = actorid
  18. WHERE actor.name = 'Julie Andrews'
  19. )
  20. 为什么这样不对?
  21. SELECT title, name,ord FROM casting
  22. join actor on (actior.id = actorid)
  23. join movie on (movie.id = movieid)
  24. WHERE actor.name = 'Julie Andrews' and ord=1
  25. 她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與
  1. 列出按字母順序,列出哪一演員曾作30次第1主角。
  2. 关键点:
  3. 1、做30次第1主角
  4. SELECT actorid FROM casting
  5. WHERE ord = 1
  6. GROUP BY actorid
  7. HAVING COUNT(actorid) >= 30
  8. 然后再在演员表里查演员的name
  9. select name from actor
  10. where id in
  11. (
  12. SELECT actorid FROM casting
  13. WHERE ord = 1
  14. GROUP BY actorid
  15. HAVING COUNT(actorid) >= 30
  16. )
  17. order by name

way2

  1. select name from
  2. (
  3. select actorid from casting
  4. where ord= 1
  5. group by actorid having count(*)>=30
  6. ) as a
  7. join actor b on (a.actorid = b.id)

====> 由此可以总结出两种思路
1、使用查询得到的半结果为临时表 join 额外表得到信息;
2、将查询得到的半结果的关键信息提出,然后select xx where xx in (半结果)

$列出1978年首影的電影名稱及角色數目,按此數目由多至少排列。

  1. select title,count(actorid) from casting
  2. join movie on (movie.id = movieid)
  3. where yr = 1978
  4. group by movieid ,title
  5. order by count(actorid) desc

为啥不对呢
===》 可能是因为 groupbytitle压缩了一些数据?实际上有id不同 title相同的数据?
因为需要 title所以我把title放入了group by里

  1. select title,count(*) from movie ===> 报错
  2. where movie.id in
  3. ( select movieid from casting
  4. join movie on (movie.id = movieid)
  5. where yr = 1978
  6. group by movieid
  7. order by count(actorid) desc
  8. )

Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

===> 如果需要count计算就必须group by;所以这里不适合select in 的方式 还是join比较适合

  1. select title,count from
  2. (select movieid,count(*) count from casting
  3. join movie on (movie.id = movieid)
  4. where yr = 1978
  5. group by movieid
  6. ) t join movie on(movie.id = t.movieid)
  7. order by count desc

结果还是错的 我哭了
我不管 答案也是给的这个。我不改了口亨

  1. -- 列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。
  2. 找到演员xx演出的所有电影==>movieid
  3. movieid 演职表里找到actoriddistinc一下
  4. actorid=>id
  5. select distinct name from casting
  6. join actor on (actorid = actor.id)
  7. where movieid in (-- 找到拍过的所有movie,根据movieidcasting里找 找到的演员可能还是重复的
  8. 1distinc2、怎么排除演员本身的重复?(不知道演员id
  9. select movieid from actor
  10. join casting on (actorid = actor.id)
  11. join movie on (movie.id = movieid)
  12. where actor.name = 'Art Garfunkel'
  13. )
  14. ==>
  15. select distinct name from casting
  16. join actor on (actorid = actor.id)
  17. where
  18. name != 'Art Garfunkel' -- 排除和自己一样的
  19. and movieid in (-- 找到拍过的所有movie,根据movieidcasting里找 找到的演员可能还是重复的
  20. select movieid from actor
  21. join casting on (actorid = actor.id)
  22. join movie on (movie.id = movieid)
  23. where actor.name = 'Art Garfunkel'
  24. )

using null

COALESCE can be useful when you want to replace a NULL value with some other value

COALESCE(party,’None’) AS aff // party这个字段的娄底方案 如果这个字段的数值为空那么 aff

inner join

内连接:
内连接就是关联的两张或多张表中,根据关联条件,显示所有匹配的记录,匹配不上的,不显示

内连接是将 只连接双方都存在的情况
排除了没有部门的老师和没有老师的部门
image.png

  1. SELECT name, CASE WHEN dept IN (1,2) THEN 'Sci'
  2. WHEN dept = 3 THEN 'Art'
  3. ELSE 'None'
  4. END
  5. FROM teacher
  6. ===>这个栗子就是对表格数据的列渲染的筛选 (突然一个脑洞,excel数据也应该开一个这样的接口
  7. (像vuetemplate那样)

left join

外连接,分为:
LEFT OUTER JOIN/LEFT JOIN:显示左表的所有项,右表没有匹配的项,则以null显示。
2) RIGHT OUTER JOIN/RIGHT JOIN:显示右表的所有项,左表没有匹配的项,则以null显示。
3) FULL OUTER JOIN/FULL JOIN:显示所有匹配和不匹配的项,左右两张表没有匹配的,都以null显示。

left join / right join / inner join /
left outer join /
之间的差别
image.png

self join

我们只有一张表。你也许说我们现在在建一张表,把同样的数据拷贝过去不就可以了吗?是的,这样可以,但我们不会采用,因为这样就会很麻烦,而且数据严重冗余等等很多弊端。
这里有更好的方法,那就是自连接。
自连接,就是把一张表取两个别名,当做两张表来使用,自己和自己关联。

自己连接自己
什么情况下会用自己连接自己呢~
栗子:
表:
stops(id, name) name:车站名,一个片区的站台至少会有一辆bus经过

This is a list of areas served by buses —

id:数据库id
name:站台名
route(num, company, pos, stop)
num:车牌号
company:这个路线的负责公司
stop:该条路线的某个站台 通过route.stop = stops.id 连接两个表

This references the stops table

pos:该站台是这个路线上的第几个,次序

This indicates the order of the stop within the route. Some routes may revisit a stop. Most buses go in both directions.

select * from stops join route on (stops.id = route.stop)

id name num company pos stop
6 ASDA 142 SMJ 1 6
6 ASDA 63 LRT 1 6

表明。一个站台可能对应两个公交车,而且他们的路线是一样的,在路线上的次序也是一样的;

栗1:单表-同时经过某两站的车辆

栗1:我们想要找到同时经过了某站到某站的车牌号(这里某站与某站之间是在一个路线上的,不需要经过换乘)

答案如下试试读的懂么~(提示:这里利用了单表查询 简化了多表联查

  1. SELECT company, num, COUNT(*)
  2. FROM route WHERE stop=149 OR stop=53
  3. GROUP BY company, num having count(*)=2

解析:
1、

  1. SELECT * FROM route WHERE stop=149 OR stop=53

经过了149或者 53站台的所有车辆信息
image.png

2、以公司为分组,公司旗下有几个车辆经过了该两站点

  1. SELECT company ,count(*)
  2. FROM route WHERE stop=149 OR stop=53
  3. group by company

image.png 以group为分组,就只能筛选出 group列 和以基于分组的聚合函数 比如count

3、以车牌为分组,得到同时经过了两个站点的车

  1. SELECT num ,count(*)
  2. FROM route WHERE stop=149 OR stop=53
  3. group by num having count(*) =2

image.png

所以按理论上来说,按照车牌号统计就能得到既能车经过149和经过53并存的车站;
(好聪明 使用 stop or连接然后根据分组判断count 来得到一个车经过了两个车站。。。
因为不能单表查询里使用 stop = 149 and stop = 53来得到同时经过两个车站的车;

4、结合company得到该车辆的公司信息
然后,在此基础上我们还想知道 这个车的公司是啥。
所以gourp的时候使用多个值
image.png

总结:这种方法省空间(数据表空间)因为是单表查询
但是,信息有限,因为并不包含车的name等等

如果要包含name 那么需要join stops表
===》但是并不行啊。因为stops作为了筛选条件,肯定不能group;
然后,stop又是表之间连接的桥梁是必须字段、
那么肿么办呢?

栗2:self join-从某站到某站

我们试试自表连接~

找到经过53站到”London Road“站的车

  1. select a.company, a.num, a.stop, b.stop
  2. -- join on的条件相当于 是约束了同一辆车 而且还会显示公司
  3. from route a join route b on (a.company=b.company and a.num=b.num)
  4. where a.stop = 53 and b.stop =
  5. (
  6. select id from stops where name = 'London Road'
  7. )

image.png

进阶:如果是两个都是name匹配呢~

  1. -- 上个栗子我们知道了通过d找两个站点之间的路线,下面我们尝试通过name来查找;
  2. -- (提示:通过拷贝两个一样的表 来实现name的转换
  3. -- Craiglockhart London Road
  4. select a.company, a.num, a.stop, b.stop
  5. from route a join route b on (a.company = b.company and a.num = b.num)
  6. where a.stop = (select id from stops where name = 'Craiglockhart')
  7. and b.stop = (select id from stops where name = 'London Road')

现在需要显示 name。而不是简单的在内查询里使用下name那么怎么弄呢

即怎么把这个变为这个
image.png

// 写不下去。而且也报错

  1. select c.company c.num
  2. (
  3. select a.company,a.num,a.stop,b.stop
  4. from route a join route b on (a.company = b.company and a.num = b.num)
  5. where a.stop = (select id from stops where name = 'Craiglockhart')
  6. and b.stop = (select id from stops where name = 'London Road')
  7. ) c join stops s on (stops.id = s.num)

因为需要两个转换 ,两个id转换为name
by joining two copies of the stops table we can refer to stops by name rather than by number

  1. select a.company,a.num,stopa.name,stopb.name
  2. from route a join route b on (a.company = b.company and a.num = b.num)
  3. join stops stopa on a.stop = stopa.id
  4. join stops stopb on b.stop = stopb.id
  5. where stopa.name = 'Craiglockhart' and stopb.name = 'London Road'
  6. --where a.stop = (select id from stops where name = 'Craiglockhart')
  7. --and b.stop = (select id from stops where name = 'London Road')

栗3:求某车经过115 和 137

提示:使用self join

  1. -- 连接115站和137 route route连接
  2. select a.company,a.num from route a join route b
  3. on (a.num = b.num)
  4. where (a.stop = 115 and b.stop=137)

但是这样是错的
image.png image.png正确的是右边的数据

左边有重复的
===> 分组一下 定向去重

  1. select a.company,a.num from route a join route b
  2. on (a.num = b.num)
  3. where (a.stop = 115 and b.stop=137)
  4. group by a.company ,a.num

其他解法

  1. SELECT a.company, a.num
  2. FROM route a, route b
  3. WHERE a.num = b.num AND (a.stop = 115 AND b.stop = 137)
  4. GROUP BY num;

==>还可以 直接from两个表?

所以这题的关键是:
1、route表连查
2、num相等==》某车
3、group by去重

栗4:求某车经过某name和某name

  1. select a.company , a.num from route a
  2. join route b on a.num = b.num --补充 on(a.num = b.num and a.company = b.company)
  3. join stops stopa on stopa.id = a.stop
  4. join stops stopb on stopb.id = b.stop
  5. where stopa.name = 'Craiglockhart' and stopb.name = 'Tollcross'

栗5:

Find the routes involving two buses that can go from Craiglockhart to Lochend. Show the bus no. and company for the first bus, the name of the stop for the transfer, and the bus no. and company for the second bus. Hint Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

  1. SELECT a.num, a.company, stopb.name, c.num, c.company
  2. FROM route a
  3. JOIN route b ON (a.company = b.company AND a.num = b.num)
  4. JOIN (route c JOIN route d ON (c.company = d.company AND c.num = d.num))
  5. JOIN stops stopa ON a.stop = stopa.id
  6. JOIN stops stopb ON b.stop = stopb.id
  7. JOIN stops stopc ON c.stop = stopc.id
  8. JOIN stops stopd ON d.stop = stopd.id
  9. WHERE stopa.name = 'Craiglockhart'
  10. AND stopd.name = 'Lochend'
  11. AND stopb.name = stopc.name
  12. ORDER BY LENGTH(a.num), b.num, stopb.name, LENGTH(c.num), d.num;

这个答案是错的

cross join

CROSS JOIN连接用于生成两张表的笛卡尔集。
1、返回的记录数为两个表的记录数乘积。比如,A表有n条记录,B表有m条记录,则返回n*m条记录。
2、将A表的所有行分别与B表的所有行进行连接。

其实所有的连接都是从 cross join演化而来的

image.png