话不多说了,今天我要把SQL zoo一顿暴刷!Today is the day! 搞他

附上链接:Welcome to SQL Zoo

一、SELECT from WORLD Tutorial

table内容:

de

in的使用

select name, population
from world
where name in ('Sweden','Norway','Denmark')

image-20210411100744424

between的用法

select name,area
from world
where area between 200000 and 250000

image-20210411100623041

like的用法

主要是 % 和 _ 的使用

image-20210411101522058

逻辑运算符

下面列出了 SQL 中可用的逻辑运算符

运算符 描述
ALL ALL 运算符用于将一个值同另一个值集中所有的值进行比较。
AND AND 运算符使得在 WHERE 子句中可以同时存在多个条件。
BETWEEN 给定最小值和最大值,BETWEEN 运算符可以用于搜索区间内的值。
EXISTS EXISTS 运算符用于在表中搜索符合特定条件的行。
IN IN 运算符用于将某个值同指定的一列字面值相比较。
LIKE LIKE运算符用于使用通配符对某个值和与其相似的值做出比较。
NOT NOT 操作符反转它所作用的操作符的意义。例如,NOT EXISTS、NOT BETWEEN、NOT IN 等。这是一个求反运算符。
OR OR 运算符用于在 SQL 语句中连接多个条件。
IS NULL NULL Operator 用于将某个值同 NULL 作比较。
UNIQUE UNIQUE 运算符检查指定表的所有行,以确定没有重复。

Exclusive OR(XOR)

查找只满足两个条件中的某一个的情况,剔除同时满足和同时不满足的数据

select name,population,area
from world
where (area>3000000 or population>250000000)
  and (not(area>3000000 and population>250000000))

image-20210411120114791

round函数

select name,round(population/1000000,2),round(gdp/1000000000,2)
from world
where continent ='South America'

第二个参数是保留几位小数,例如 -2 是指精确到100,2 指精确到0.01

image-20210411120501247

left函数

第一个参数是字段,第二个参数是位数

<>表示"不等于"

image-20210411122537751

字符包含问题,like

image-20210411123348282


二、SELECT from Nobel Tutorial

table内容:

image-20210411142452288

单引号转义

image-20210412102702630

order by 后边插入语句

image-20210412122706914


三、SELECT within SELECT

基于world表:

image-20210412232956977

image-20210412232920104

cast(xxx as int)  --类型转换函数
concat (str1,str2)  --字符串拼接函数
round(number,3)  --保留有效数字函数

image-20210412234011308

 ">=ALL(......)" 的使用,注意括号内不能有空值
 所以6题中用 "gdp>0" 约束

选出分组中最大值的经典题

image-20210413153446472

-- 查询每个洲中,面积最大国家的信息
-- 相关子查询:依靠表别名来标识同一表的两种不同用途,一种用于外部查询,另一种用于子查询。

-- 解法一
select continent,name,area
from world x
where area>=all(select area from world y 
  where x.continent=y.continent and y.area>0
)

-- 解法二
select continent,name,area
from world x
where area=all(select max(area) from world y 
  where x.continent=y.continent and y.area>0
)

image-20210413161526509

-- 不知道为什么,显示limit语法错误
select continent,name
from world x
where name=(select y.name from world y
  where x.continent=y.continent
  order by y.name limit 1
)

image-20210413162522276

-- 重点题 
-- 每个国家人口都小于25000000的大洲,其中包含的所有国家
select name,continent,population from world x
where 25000000>=all(select population from world y
                    where y.continent=x.continent)

image-20210413163318200

select name,continent from world x
where (population/3)>all(select population from world y
                       where y.continent=x.continent and y.name<>x.name)
-- 最后比较时候,记得剔除自己

四、Sum and Count

表world内容:

image-20210413170943418

  1. Show the total population of the world.

    select sum(population) from world
    • sum 求和函数
  2. List all the continents - just once each.

    select distinct(continent) from world
    • distinct 去重函数
  3. Give the total GDP of Africa

    select sum(gdp) from world
    where continent='Africa'
  4. How many countries have an area of at least 1000000

    select count(name) from world
    where area>=1000000
  5. What is the total population of ('Estonia', 'Latvia', 'Lithuania')

    select sum(population) from world
    where name in ('Estonia', 'Latvia', 'Lithuania')
  6. For each continent show the continent and number of countries.

    select continent,count(name) from world
    group by continent
  7. For each continent show the continent and number of countries with populations of at least 10 million.

    select x.continent,count(x.continent)
    from (select * from world
          where population>10000000) x
    group by x.continent
  8. List the continents that have a total population of at least 100 million.

    select continent
    from(select continent,sum(population) as totalpl from world
         group by continent) x
    where x.totalpl>100000000

五、Join

image-20210414183456677

image-20210414183511372

image-20210414183526328

game表的主键是id表示比赛的场次,mdate是比赛日期,stadium是比赛场地,team1和team2是比赛双方的名字,是eteam表的外键

goal表主键matchid是比赛场次,和game表的id是相同意思,teamid,player和gtime分别表示哪个球队的哪位球员在比赛开始后多少分钟进球得分

eteam表id是球队编码,和game表中的team1,team2是一个意思,teamname和coach分别表示球队名称和教练

  1. show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'

    select matchid,player from goal
    where teamid='GER'
  2. Show id, stadium, team1, team2 for just game 1012

    sekect * from game
    where id=1012
  3. Modify it to show the player, teamid, stadium and mdate for every German goal.

    select player,teamid,stadium,mdate
    from goal left join game on id=matchid
    where teamid='GER'
  4. Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

    select team1,team2,player
    from goal left join game on matchid=id
    where player like 'Mario%'
  5. Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

    select player,teamid,coach,gtime
    from goal left join eteam on teamid=id
    where gtime<=10
  6. List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

    select mdate,teamname
    from game left join eteam on team1=eteam.id
    where coach='Fernando Santos'
    -- 名字重复时,用表名加“.”区分
  7. List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

    select player
    from goal left join game on matchid=id
    where stadium='National Stadium, Warsaw'
  8. show the name of all players who scored a goal against Germany.

    select distinct player
    from goal left join game on matchid=id
    where teamid!='GER' and (team1='GER' or team2='GER')
    -- 注意去重
  9. Show teamname and the total number of goals scored.

    select teamname,count(teamname)
    from goal left join eteam on teamid=id
    group by teamname
    -- 如果是group by teamid 会报错:
    -- teamname 既不在聚合函数中,也不在group by语句中
  10. Show the stadium and the number of goals scored in each stadium.

     select stadium, count(stadium)
     from goal left join game on id=matchid
     group by stadium
     -- 与9题一样
  11. For every match involving 'POL', show the matchid, date and the number of goals scored.

     select id,mdate,count(1)
     from goal left join game on id=matchid
     where team1='POL' or team2='POL'
     group by id,mdate
     -- group by 多列分组,用逗号间隔开
  12. For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

     select matchid,mdate,count(1)
     from goal left join game on id=matchid
     where teamid='GER'
     group by matchid,mdate
     -- 这里group by要写两个,不然mdate会报错
  13. 得到如图所示的结果

    image-20210414205537548

     select mdate,team1,     
         SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,     
         team2,     
         SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2     
     from game LEFT JOIN goal ON matchid = id
     group by mdate,matchid,team1,team2
     -- 非常重要的 case when 与 sum 合用的方法,配合 order by 使用 

    13题知识,学习参考:https://blog.csdn.net/WuLex/article/details/115055979?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-0&spm=1001.2101.3001.4242

六、More Join

表结构如图所示

image-20210423155630420

movie表:id是电影的编号,title是电影名称,yr是放映日期,score是评分,votes是投票人数,director是导演人数(??director没看懂)

image-20210423161245517

actor表:id是演员编号,name是演员姓名

image-20210423161333360

casting表:movieid与movie表中id对应,actorid与actor表中的id对应,ord表示该演员在电影中是几号演员,如1表示主角

image-20210423161440999

  1. List the films where the yr is 1962 [Show id, title]

    select id,title
    from movie
    where yr=1962
  2. Give year of 'Citizen Kane'.

    select yr
    from movie
    where title='Citizen Kane'
  3. List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

    select id,title,yr
    from movie
    where title like '%Star Trek%'
  4. What id number does the actor 'Glenn Close' have?

    select id from actor
    where name = 'Glenn Close'
  5. What is the id of the film 'Casablanca'

    select id from movie
    where title = 'Casablanca'
  6. Obtain the cast list for 'Casablanca'.

    what is a cast list?

    The cast list is the names of the actors who were in the movie.

    select distinct(name)
    from actor a left join casting c
    on a.id=c.actorId
    where c.movieId = (select id from movie where title= 'Casablanca')
  7. Obtain the cast list for the film 'Alien'

    select distinct(name)
    from actor a left join casting c
    on a.id=c.actorId
    where c.movieId = (select id from movie where title= 'Alien')
  8. List the films in which 'Harrison Ford' has appeared

    select distinct(title)
    from movie m left join casting c
    on m.id=c.movieId
    where c.actorId=(select id from actor where name = 'Harrison Ford')
  9. List the films where 'Harrison Ford' has appeared - but not in the starring role.

    select distinct(title)
    from movie m left join casting c
    on m.id=c.movieId
    where c.ord<>1
        and c.actorId = (select id from actor where name = 'Harrison Ford')
  10. List the films together with the leading star for all 1962 films.

     select title,name
     from casting c
     left join movie m on m.id = c.movieId
     right join actor a on a.id = c.actorId  -- 这里是三个表的连接,第一个是主表
     where ord = 1 and yr = 1962
    
     -- 一个电影可能有多个主演
  11. Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

     select yr,count(title)
     from movie m
     join casting c on m.id = c.movieId
     join actor a on c.actorId = a.id
     where a.name = 'Rock Hudson'
     group by m.yr
     having count(m.title)>2
  12. List the film title and the leading actor for all of the films 'Julie Andrews' played in.

     select title,name
     from movie m
     join casting c on m.id=c.movieId
     join actor a on c.actorId=a.id
     where m.id in (select movieId
                    from casting
                    where actorId=(select id from actor where name='Julie Andrews'))
     and ord=1
  13. Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

     select name
     from actor a
     join casting c on a.id=c.actorId
     where ord=1
     group by name
     having count(name)>=15
     order by name
  14. List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

     select title,count(title)
     from movie m
     join casting c on m.id=c.movieId
     where yr=1978
     group by title
     order by count(title) desc,title
  15. List all the people who have worked with 'Art Garfunkel'.

     select distinct(name)
     from actor a
     join casting c on a.id=c.actorId
     where movieId in (select movieId
                       from casting
                       where actorId=(select id from actor where name='Art Garfunkel'))
     and name<>'Art Garfunkel'

七、Using NULL

表结构如图所示

image-20210423194437873

teacher表中的dept字段与dept表中的id字段对应

  1. List the teachers who have NULL for their department.

    select name
    from teacher
    where dept is NULL
  2. Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

    select t.name,d.name
    from teacher t
    join dept d on t.dept=d.id
  3. Use a different JOIN so that all teachers are listed.

    select t.name,d.name
    from teacher t
    left join dept d on t.dept=d.id
  4. Use a different JOIN so that all departments are listed.

    select t.name,d.name
    from teacher t
    right join dept d on t.dept=d.id
  5. Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'

    -- coalesce函数,返回第一个不为NULL的值
    select name,coalesce(mobile,'07986 444 2266')
    from teacher

img

  1. Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.

    select t.name,coalesce(d.name,'None')
    from teacher t
    left join dept d on t.dept=d.id
  2. Use COUNT to show the number of teachers and the number of mobile phones.

    select count(name),count(mobile)
    from teacher
  3. Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

    select d.name,count(t.name)
    from dept d
    left join teacher t on d.id=t.dept
    group by d.name
  4. Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.

    -- 题目意思:如果是在1或2学院,第二列是Sci,否则第二列是Art
    
    select name,case when dept in (1,2) then 'Sci' else 'Art' end
    from teacher
  5. Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.

     select name,
     case when dept in (1,2) then 'Sci' when dept=3 then 'Art' else 'None' end
     from teacher

八、Self Join

表结构如图所示

image-20210423203209513

  • stops表

image-20210423233317748

  • route表

image-20210423233429948

route表:num是公交车号(注意部分有字母,是字符串格式),company是公交公司,num和company两者组成复合主键,pos表示停站的顺序,stop表示站点,与 stops表中的id对应

  1. How many stops are in the database.

    select count(id)
    from stops
  2. Find the id value for the stop 'Craiglockhart'

    select id
    from stops
    where name='Craiglockhart'
  3. Give the id and the name for the stops on the '4' 'LRT' service.

    -- 查找的是公交车号为4,公司是LRT的车经过的站点id和名字
    
    select id,name from stops
    where id in(select stop
    from route
    where num='4' and company='LRT'
    )
  4. The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

    -- 对于这两个站点(149)和(53),看看那一辆车同时经过这一个站点
    -- 注意:车辆是复合主键(num,company)
    
    SELECT company, num, COUNT(*)
    FROM route WHERE stop=149 OR stop=53
    GROUP BY company, num
    having count(*)>1  -- 这是加的那一句
  5. Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

    /*
    这是初始给的代码,意思是可以得到所有的从53号出发可以到达的站点(b.stop)
    SELECT a.company, a.num, a.stop, b.stop
    FROM route a JOIN route b ON
      (a.company=b.company AND a.num=b.num)
    WHERE a.stop=53
    */
    
    SELECT a.company, a.num, a.stop, b.stop
    FROM route a JOIN route b ON
      (a.company=b.company AND a.num=b.num)
    WHERE a.stop=53 and b.stop=(select id from stops where name='London Road')
    
  6. The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'

    SELECT a.company, a.num, stopa.name, stopb.name
    FROM route a JOIN route b ON
      (a.company=b.company AND a.num=b.num)
      JOIN stops stopa ON (a.stop=stopa.id)
      JOIN stops stopb ON (b.stop=stopb.id)
    WHERE stopa.name='Craiglockhart' and stopb.name='London Road'
    
    -- 跟上一个题没什么变化,只是后边加了两个stops表的连接,这样可以直接显示站点的名字
  7. Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')

    -- 这里存疑,关于下图这样的内容怎么去重,distinct放在哪
    
    select distinct(t1.company),t1.num
    from route t1
    join route t2 on t1.num=t2.num and t1.company=t2.company
    where t1.stop=115 and t2.stop=137

    image-20210423211035870

  8. Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'

    select t1.company,t1.num
    from route t1
    join route t2 on t1.num=t2.num and t1.company=t2.company
    join stops s1 on t1.stop=s1.id
    join stops s2 on t2.stop=s2.id
    where s1.name='Craiglockhart' and s2.name='Tollcross'
  9. Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services.

    -- 从 'Craiglockhart' 出发可以到达的站点,包括自己,公司是LRT
    
    select s2.name,t1.company,t1.num
    from route t1
    join route t2 on t1.num=t2.num and t1.company=t2.company
    join stops s1 on t1.stop=s1.id
    join stops s2 on t2.stop=s2.id
    where s1.name='Craiglockhart' and t1.company='LRT'
  10. 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.

     -- 根据提示,首先做两个表,第一个是从Craiglockhart出发的,第二个是到达Lochend的
     -- 然后根据中转车站名相同,将两个表连接,再输出想要的结果,(车1,换乘车站name,车2)
    
     select b1.num,b1.company,b1.name,b2.num,b2.company
     from (select t1.num as num,t1.company as company,s2.name as name
           from route t1
           join route t2 on t1.num=t2.num and t1.company=t2.company
           join stops s1 on t1.stop=s1.id
           join stops s2 on t2.stop=s2.id
           where s1.name='Craiglockhart'
          ) b1
     join (select t1.num as num,t1.company as company,s1.name as name
           from route t1
           join route t2 on t1.num=t2.num and t1.company=t2.company
           join stops s1 on t1.stop=s1.id
           join stops s2 on t2.stop=s2.id
           where s2.name='Lochend'
          ) b2 on b1.name=b2.name

到最后还是用了好几天才做完,看着简单,其实需要耗费时间去琢磨。这只是第一遍,记得多回来巩固,一遍是不够的。