SQL练习

最新推荐文章于 2024-06-23 15:30:17 发布

阿波策得

最新推荐文章于 2024-06-23 15:30:17 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/sort_man/article/details/103651850

版权

SQL基础练习

一、在线练习
二、系统学习

一、在线练习

SQLzoo网站练习题：https://zh.sqlzoo.net/wiki/SELECT_basics/zh

1、Select basics

select basics

显示 Germany 的人口
select population from world where name = 'Germany';
备注：字符串应该在单引号中
查询面积为5,000,000 以上平方公里的国家,显示每个国家的名字和人均国内生产总值(gdp/population)
select name, gdp/population from world where area >= 5000000;
备注：用公式(population/area)計算出人均国内生产总值
顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。
select name,population from world where name in ('Ireland', 'Iceland', 'Denmark');
备注：单词“IN”可以让我们检查一個項目是否在列表中
顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積
select name, area from world where area between 200000 and 250000;
备注：between 允許範圍檢查 - 注意,這是包含性的

SELECT Name

找出以 Y 为首的国家
select name from world where name like 'Y%';
备注：你可以用where name like 'B%'來找出以 B 為開首的國家。
%是萬用字元,可以用代表任何字完。
找出以 Y 为结尾的国家
select name from world where name like '%Y';
找出所有國家,其名字包括字母x
`select name from world where name like ‘%x%’;
找出所有國家,其名字以 land 作結尾
select name from world where name like '%land';
找出所有國家,其名字以 C 作開始,ia 作結尾
select name from world where name like 'C%ia';
找出所有國家,其名字包括字母oo
select name from world where name like '%oo%';
找出所有國家,其名字包括三個或以上的a。
select name from world where name like '%a%a%a%';
找出所有國家,其名字以t作第二個字母
select name from world where name like '_t%';
select name from world where name like '_t%' order by name;
备注：用下划线_当作单一个字母的万用字元
找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着。
elect name from world where name like '%o__o%';
找出所有國家,其名字都是 4 個字母的
select name from world where name like '____';
显示所有国家名字,其首都和国家名字是相同的
select name from world where name = capital;
顯示所有國家名字,其首都是國家名字加上”City”
select name from world where capital like concat(name, ' City');
**函数concat 可以用來合拼兩個或以上的字串
找出所有首都和其國家名字,而首都要有國家名字中出現
select capital,name from world where capital like concat('%',name,'%');
找出所有首都和其國家名字,而首都是國家名字的延伸。
你應顯示 Mexico City,因它比其國家名字 Mexico 長。
你不應顯示 Luxembourg,因它的首都和國家名相是相同的。
select name,capital from world where capital like concat(name,'_%') and capital != name;
“Monaco-Ville"是合併國家名字 “Monaco” 和延伸詞”-Ville".顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
select name, replace(capital, name, '') as name1 from world where capital like concat(name, '_%');
备注：replace（f，s1，s2）返回字符串f，其中所有出现的s1被s2替换。

2、Select from World Tutorial

在这里插入图片描述

觀察運行一個簡單的SQL命令的結果
select name, continent, population from world;
顯示具有至少2億人口的國家名稱。 2億是200000000，有八個零
select name from world where population >= 200000000;
找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值。
select name, gdp/population from world where population >= 200000000;
顯示’South America’南美洲大陸的國家名字和以百萬為單位人口數。將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。
select name, population/1000000 from world where continent = 'South America';
顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口
select name, population from world where name in ('France', 'Germany', 'Italy');
顯示包含單詞“United”為名稱的國家
select name from world where name like '%United%';
成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。展示大國的名稱，人口和面積
select name,population,area from world where area > 3000000 or population > 250000000;
美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積
select name,population,area from world where (area > 3000000 and population < 250000000) or (area < 3000000 and population > 250000000);
除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。對於南美顯示以百萬計人口，以十億計2位小數GDP
select name,round(population/1000000,2), round(gdp/1000000000,2) from world where continent = 'South America';
顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000
select name,round(gdp/population/1000)*1000 from world where gdp >= 1000000000000;
所示的CASE语句用于在第三栏中用北美代替加勒比海地区。显示名称-以N开头的国家/地区，但要用大洋洲代替Australasia。
select name , (case when continent='Oceania' then 'Australasia' else continent end) as newName from world where name like 'N%';
备注：case 的用法：
> https://blog.csdn.net/rongtaoup/article/details/82183743
显示名称和大陆-但用欧亚大陆代替欧洲和亚洲；美国替代-北美，南美或加勒比海地区的每个国家/地区。显示以A或B开头的国家

select name,
      (case when continent='Asia' 
            then 'Eurasia'
            when continent='Europe' 
            then 'Eurasia'
            when continent = 'North America' 
            then 'America'
            when continent = 'South America' 
            then 'America'
            when continent = 'Caribbean' 
            then 'America'
            else continent
        end)as newName
from world where name like 'A%' or name like 'B%';

Put the continents right…
Oceania becomes Australasia
Countries in Eurasia and Turkey go to Europe/Asia
Caribbean islands starting with ‘B’ go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.

# 当前结果网站上提交不正确
select name,continent,
(case when continent = 'Oceania' then 'Australasia'
     when continent in ('Eurasia','Turkey') then 'Europe/Asia'            
     when continent = 'Caribbean' and name like 'B%' then 'North America'
     when continent = 'Caribbean' and name not like 'B%' then 'South America'
     else continent end) as newContinent
from world order by name;

3、Select from Nobel Tutorial

在这里插入图片描述

更改查詢以顯示1950年諾貝爾獎的獎項資料。

select yr, subject, winner
from nobel
where yr = 1950;

顯示誰贏得了1962年文學獎(Literature)

select winner
from  nobel
where yr = 1962
and subject = 'Literature';

顯示“愛因斯坦”(‘Albert Einstein’) 的獲獎年份和獎項

select yr, subject 
from nobel 
where winner = 'Albert Einstein';

顯示2000年及以後的和平獎(‘Peace’)得獎者。

select winner 
from nobel
where yr >= 2000 and subject = 'Peace';

顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。

select yr,subject,winner
from nobel
where yr>=1980 and yr <= 1989 and subject = 'Literature';

顯示總統獲勝者的所有細節：
西奧多•羅斯福 Theodore Roosevelt
伍德羅•威爾遜 Woodrow Wilson
吉米•卡特 Jimmy Carter

select * 
from nobel where 
winner in ('Theodore Roosevelt',
           'Woodrow Wilson',
           'Jimmy Carter');

顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)

select winner
from nobel
where winner like 'John%';

顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。

select *
from nobel
where (yr = 1980 and subject = 'physics')
or (yr = 1984 and subject = 'chemistry');

查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)

select *
from nobel
where subject not in ('Chemistry','Medicine') and yr = 1980;

顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年)

select *
from nobel
where (yr<1910 and subject = 'Medicine') or (yr >= 2004 and subject = 'Literature');

Find all details of the prize won by PETER GRÜNBERG

select * 
from nobel
where winner = 'PETER GRÜNBERG';

查找尤金•奧尼爾EUGENE O’NEILL得獎的所有細節 Find all details of the prize won by EUGENE O’NEILL

select * 
from nobel
where winner = 'EUGENE O''NEILL';

騎士列隊 Knights in order
列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。

select winner,yr,subject
  from nobel
where winner like 'Sir%'
order by yr desc,winner asc;

显示1984年获奖者和主题（按主题和获奖者名称排序）；但最后列出化学和物理。

select winner, subject 
  from nobel
where yr=1984 
order by subject in ('Chemistry','Physics'),subject,winner asc;

在这里插入图片描述

4、Select within Select Tutorial

在这里插入图片描述

列出每個國家的名字 name，當中人口 population 是高於俄羅斯’Russia’的人口

select name
from world where population >
(select population from world where name = 'Russia');

列出歐州每國家的人均GDP，當中人均GDP要高於英國’United Kingdom’的數值。

select name
from world 
where gdp/population > (select gdp/population from world where name = 'United Kingdom') and continent = 'Europe';

在阿根廷Argentina 及澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序

select name,continent
from world 
where continent in (select continent from world where name in ('Argentina','Australia')) order by name asc;

哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population

select name,population
from world 
where population > (select population from world where name = 'Canada') and population < (select population from world where name = 'Poland');

Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。
顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。
小數位數
百分號 %

select name, 
concat(round(population*100/(select population from world where name = 'Germany')),'%') 
from world 
where continent = 'Europe';

备注：concat 将两个字符连接为一个字符，例如concat(‘A’, ‘B’) = ‘AB’

哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)
备注：我們可以用ALL 這個詞對一個列表進行>=或>或<或<=充當比較

select name
from world 
where gdp > all(select gdp from world 
               where gdp >0 and continent = 'Europe');

在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)
备注：我們可以在子查詢，參閱外部查詢的數值。我們為表格再命名，便可以分別內外兩個不同的表格。

select continent, name, area from world as x
where area >= all
    (select area from world as y
        where y.continent=x.continent
          and population>0);

列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
备注：limit子句用于限制查询结果返回的数量，常用于分页查询，limit n 等同于 limit 0,n

select continent,name
from world as x
where name = (select name from world as y 
              where y.continent=x.continent
              order by name asc limit 1)

找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

select name,continent,population 
from world as x 
where 25000000 >=all(select y.population 
from world as y where x.continent=y.continent and population>0);

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

select name,continent
from world as x
where x.population/3 >= all(select population from world as y 
                 where x.continent=y.continent 
                 and x.name != y.name and y.population > 0);

5、SUM and COUNT

在这里插入图片描述

展示世界的總人口。

SELECT sum(population)
FROM world where population > 0;

列出所有的洲份, 每個只有一次。

select distinct continent
from world;

找出非洲(Africa)的GDP總和

select sum(gdp)
from world 
where continent = 'Africa';

有多少個國家具有至少百萬(1000000)的面積

select count(name) 
from world 
where area >  1000000;

(‘France’,‘Germany’,‘Spain’)（“法國”，“德國”，“西班牙”）的總人口是多少？

select sum(population)
from world 
where name in ('France','Germany','Spain');

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

select continent,count(name) from world  group by continent

對於每一個洲份，顯示洲份和至少有1000萬人(10,000,000)口國家的數目。

select continent,count(name) 
from world 
where population >=10000000 group by continent;

列出有至少100百萬(1億)(100,000,000)人口的洲份

select continent 
from world  
group by continent
having sum(population)>100000000;

6、The JOIN operation

在这里插入图片描述

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

select matchid,player  
from goal 
where teamid like 'GER';

由以上查詢，你可見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;

select *
from game join goal on (id=matchid);

我們可以利用JOIN來同時進行以上兩個步驟。
SELECT *
FROM game JOIN goal ON (id=matchid)
語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON 表示如何找出 game中每一列應該配對goal中的哪一列 – goal的 id 必須配對game的 matchid 。簡單來說，就是
ON (game.id=goal.matchid)
以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)
修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。

select player, teamid, stadium, mdate
from goal join game on (id = matchid)
where teamid = 'GER';

使用上題相同的 JOIN語句，
列出球員名字叫Mario (player LIKE ‘Mario%’)有入球的隊伍1 team1, 隊伍2 team2 和球員名 player

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

表格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;

要合拼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';

SELECT mdate, teamname
FROM eteam e JOIN game g ON (e.id = g.team1)
WHERE coach = 'Fernando Santos';

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

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

以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
修改它，只列出全部賽事，射入德國龍門的球員名字。
找非德國球員的入球，德國可以在賽事中作team1 隊伍１（主）或team2隊伍２（客）。你可以用teamid!=‘GER’ 來防止列出德國球員。你可以用DISTINCT來防止球員出現兩次以上。

select distinct(player)
from  game as ga join goal as go on (ga.id = go.matchid)
where teamid!='GER' and (team1 = 'GER' OR team2 = 'GER');

列出隊伍名稱 teamname 和該隊入球總數
COUNT and GROUP BY
你應該在SELECT語句中使用COUNT(*)和使用GROUP BY teamname

select teamname, count(player)
from eteam join goal on (id=teamid)
group by teamname;

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

select stadium, count(player)
from game as ga join goal as go on (ga.id=go.matchid)
group by stadium;

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

select matchid,mdate, count(matchid)
from game JOIN goal ON matchid = id 
where (team1 = 'POL' OR team2 = 'POL')
group by matchid,mdate;

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

select matchid,mdate,count(matchid) 
from goal join game on (id =matchid)
where (team1 = 'GER' or team2 = 'GER') and teamid = 'GER'
group by matchid, mdate;

列出每场比赛，每个球队得分，如图所示。这将使用“ CASE WHEN”（在以前的练习中未曾说明）。
时间>>>团队1>>>得分1>>>团队2>>>得分2
2012年7月1日>>>ESP>>>4>>>ITA >>>0
2012年6月10日>>>ESP>>>1>>>ITA>>>1
2012年6月10日>>>IRL>>>1>>>CRO>>>3
…
注意在查询中列出了每个目标。如果是team1目标，那么得分1中将显示1，否则为0。您可以对这一列求和以获取team1得分的目标计数。按mdate，matchid，team1和team2对结果进行排序。

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;

备注：

内连接是最常见的一种连接，只连接匹配的行。
INNER JOIN与JOIN是相同

LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。
在某些数据库中，LEFT JOIN 称为LEFT OUTER JOIN

RIGHT JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。
在某些数据库中，RIGHT JOIN 称为RIGHT OUTER JOIN

FULL JOIN 会从左表和右表那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替

7、More JOIN operations

在这里插入图片描述

列出1962年首影的電影， [顯示 id, title]

select id, title
from movie
where yr = 1962;

電影大國民 ‘Citizen Kane’ 的首影年份。

select yr
from movie
where title like 'Citizen Kane';

列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。

select id,title,yr
from movie
where title like 'Star Trek%'
order by yr asc;

id是 11768, 11955, 21191 的電影是什麼名稱?

select title
from movie
where id in (11768,11955,21191);

女演員’Glenn Close’的編號 id是什麼?

select id 
from actor
where name = 'Glenn Close';

電影北非諜影’Casablanca’ 的編號 id是什麼?

select id
from movie
where title = 'Casablanca';

列出電影北非諜影 'Casablanca’的演員名單。
什麼是演員名單?
演員名單,即是電影中各演員的真實姓名清單。
使用 movieid=11768, 這是你上一題得到的結果。

select name 
from actor
where id IN (
  select actorid
  from movie m join casting c on m.id = c.movieid
  where id = 11768);

顯示電影異型’Alien’ 的演員清單。

select name
from actor
where id in (
     select actorid
     from movie m join casting c on m.id = c.movieid
     where title = 'Alien'); 

SELECT name 
FROM actor
WHERE id IN (
  SELECT actorid
  FROM movie m JOIN casting c ON m.id = c.movieid
  WHERE id = (
    SELECT id
    FROM movie
    WHERE title LIKE 'Alien'));

列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。

select title
from movie join casting on (movie.id = casting.movieid)
where casting.actorid = (
      select id from actor 
      where name = 'Harrison Ford');

列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影，但他不是第1主角。

select title
from movie join casting on (movie.id = casting.movieid)
where casting.ord != 1 and casting.actorid = (
      select id from actor 
      where name = 'Harrison Ford');

列出1962年首影的電影及它的第1主角。

select title,name
from movie join casting on (movie.id = casting.movieid)
           join actor on (actor.id = casting.actorid)
where yr = 1962 and ord =1;

尊·特拉華達’John Travolta’最忙是哪一年? 顯示年份和該年的電影數目。

select yr,count(title) 
from movie join casting on movie.id=movieid
           join actor   on actorid=actor.id
where name='John Travolta'
group by yr 
order by count(title) desc limit 1;

SELECT yr, COUNT(title) 
FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name='John Travolta'
GROUP BY yr HAVING COUNT(title)=(
  SELECT MAX(c) FROM (
    SELECT yr,COUNT(title) AS c 
    FROM movie 
    JOIN casting ON movie.id=movieid
    JOIN actor   ON actorid=actor.id
    WHERE name='John Travolta'
    GROUP BY yr) AS t)

列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。
是否列了電影 "Little Miss Marker"兩次?
她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。電影名稱不是獨一的。在子查詢中使用電影編號。

SELECT title, name    /* 電影名稱 及 其第1主角*/
FROM casting c 
JOIN movie m ON (m.id= c.movieid)
JOIN actor a ON (a.id= c.actorid)
WHERE c.ord = 1 AND c.movieid IN (
  SELECT movieid /*Julie Andrews 参加过的电影, 需要获取到 movieid */
  FROM casting c 
  JOIN movie m ON (m.id= c.movieid)
  JOIN actor a ON (a.id= c.actorid)
  WHERE name = 'Julie Andrews')

列出按字母順序，列出哪一演員曾作30次第1主角。

select name,count(actorid) /*仅得到演员做第1主角的统计排序*/
from movie join casting on (movie.id = casting.movieid)
           join actor on (actor.id = casting.actorid)
where ord =1 group by name
order by count(actorid) desc;

select name
from movie join casting on (movie.id = casting.movieid)
           join actor on (actor.id = casting.actorid)
where ord =1 group by name
having count(actorid) = 30;

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

select movie.title,count(actorid) 
from movie join casting on (movie.id = casting.movieid)
           join actor on (actor.id = casting.actorid)
where yr = 1978 group by movie.title
order by count(actorid) desc;

列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。

select name
from casting join actor on (actor.id = casting.actorid)
where name not like 'Art Garfunkel' and movieid in (
   select movieid 
   from casting join actor on (actor.id = casting.actorid)
   where name = 'Art Garfunkel'  )

8、Using Null

在这里插入图片描述

列出所属部门为空的教师。
为什么我们不能使用=
您可能会认为词组dept = NULL在这里可以使用，但是不行-您可以使用词组dept IS NULL
那不是一个恰当的解释。

select name
from teacher
where dept is null;

请注意，INNER JOIN会错过没有部门的老师和没有老师的部门。

SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id);

使用其他JOIN，以便列出所有教师

select teacher.name,dept.name 
from teacher left join dept on (teacher.dept=dept.id);

使用其他JOIN，以便列出所有部门

select teacher.name,dept.name 
from teacher right join dept on (teacher.dept=dept.id);

使用COALESCE打印手机号码。如果没有给出数字，则使用数字“ 07986 444 2266”。显示老师的姓名和手机号码或“ 07986 444 2266”
备注：COALESCE是一个函数， (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

select name, coalesce(mobile,'07986 444 2266')
from teacher;

使用COALESCE功能和LEFT JOIN打印教师姓名和部门名称。在没有部门的地方使用字符串“ None”。

select teacher.name, coalesce(dept.name,'None')
from teacher left join dept on (teacher.dept= dept.id);

使用COUNT显示教师人数和手机数量。

select count(name),count(mobile)
from teacher;

使用COUNT和GROUP BY dept.name可以显示每个部门和员工人数。使用RIGHT JOIN来确保列出了工程部门。

select dept.name, count(teacher.name)
from dept left join teacher on (dept.id = teacher.dept)
group by dept.name;

如果教师在部门1或2中，请使用CASE显示每个教师的姓名，然后显示“科学”，否则显示“艺术”。

select teacher.name,
case when dept.id in (1,2) then 'Sci' else 'Art' end
from teacher left join dept on (dept.id = teacher.dept);

使用CASE显示每位教师的姓名，如果教师在部门1或2中，则显示“ Sci”，如果教师部门是3，则显示“ Art”，否则显示“ None”。

select teacher.name,
    case when dept.id in (1,2) then 'Sci' 
         when dept.id in (3) then 'Art'
    else 'None' end
from teacher left join dept on (dept.id = teacher.dept);

9、Using Null

跳转到搜索
爱丁堡巴士
数据库细节查看数据
站号（id，name）
路线（num，company，pos，stop）

How many stops are in the database

select count(id)
from stops;

Find the id value for the stop ‘Craiglockhart’

select id 
from stops
where name = 'Craiglockhart';

输入“ 4”“ LRT”服务上站点的ID和名称

select distinct stops.id,stops.name
from stops join route on (stops.id = route.stop)
where route.num = '4' and route.company = 'LRT';

显示的查询给出了访问伦敦路（149）或克雷格洛克（53）的路线数量。运行查询，并注意链接到这些站点的两个服务的计数为2。添加HAVING子句以将输出限制为这两个路线。

select company, num, count(*)
from route where stop=149 or stop=53
group by company, num
having count(*) = 2;

执行所示的自连接，并观察b.stop可以提供从Craiglockhart可以到达的所有位置，而无需更改路线。更改查询，使其显示从Craiglockhart到London Road的路线

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=149

所显示的查询与上一个查询类似，但是通过联接两个Stops表副本，我们可以按名称而不是按编号来引用Stops。更改查询，以便显示“克雷格洛克哈特”和“伦敦路”之间的路线。如果您厌倦了这些地方，请尝试使用“ Fairmilehead”对“ 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'

Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)

SELECT DISTINCT a.company, a.num 
FROM route a JOIN route b ON (a.num=b.num AND a.company=b.company) 
WHERE a.stop=115 AND b.stop=137

Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’

SELECT a.company, a.num 
FROM route a JOIN route b ON (a.num=b.num AND a.company=b.company) JOIN stops stopa ON a.stop=stopa.id JOIN stops stopb ON b.stop=stopb.id 
WHERE stopa.name='Craiglockhart' AND stopb.name='Tollcross'

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.

SELECT DISTINCT S2.name, R2.company, R2.num
FROM stops S1, stops S2, route R1, route R2
WHERE S1.name='Craiglockhart'
AND S1.id=R1.stop
AND R1.company=R2.company AND R1.num=R2.num
AND R2.stop=S2.id

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.
HintSelf-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.
备注：此题未找到笑脸解答

SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company FROM   
(SELECT start1.num, start1.company, stop1.stop 
FROM route AS start1 JOIN route AS stop1 ON start1.num = stop1.num     AND start1.company = stop1.company  AND start1.stop != stop1.stop     WHERE start1.stop = (
SELECT id FROM stops WHERE name = 'Craiglockhart')) AS bus1 JOIN (SELECT start2.num, start2.company, start2.stop FROM   
route AS start2 JOIN route AS stop2 ON   start2.num = stop2.num   AND start2.company = stop2.company   AND start2.stop != stop2.stop   WHERE stop2.stop = (SELECT id FROM stops WHERE name = 'Sighthill')) 
AS bus2   ON bus1.stop = bus2.stop   JOIN stops ON bus1.stop = stops.id

tps://blog.csdn.net/q370835062/article/details/83025363

二、系统学习

学习网址链接：W3school

2.1 SQL 基础教程

SQL 对大小写不敏感
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

select # 查询
select * from 表名; # 查询所有列数据
select 列名1, 列名2 from 表名; # 查询表中，列名1，列名2的数据
distinct # 去重
SELECT DISTINCT 列名称 FROM 表名称; # 保留列名称中的唯一值
where # 子句用于规定选择的标准
SELECT 列名称 FROM 表名称 WHERE 列运算符值
操作符： =、<>、>、<、>=、<=、between、like、in
and 和 or 运算符用于基于一个以上的条件对记录进行过滤
SELECT * FROM Persons WHERE FirstName=‘Thomas’ AND LastName=‘Carter’
betwwen and 连用
–查找sal在1500和3000之间的信息（包括1500和3000）
select * from emp where sal >= 1500 and sal <=3000
–等价于
select * from emp where sal between 1500 and 3000
–查找sal小于1500或大于3000之间的信息（不包括1500和3000）
select * from emp where sal < 1500 or sal >3000
–等价于
select * from emp where sal not between 1500 and 3000
order by 语句用于对结果集进行排序
默认升序（asc），降序（desc）
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
insert into 语句用于向表格中插入新的行
INSERT INTO 表名称 VALUES (值1, 值2,…) # 值与列数相同
INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…) # 指定列插入数据
Update 语句用于修改表中的数据
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
我们为 lastname 是 “Wilson” 的人添加 firstname ‘Fred’：
UPDATE Person SET FirstName = ‘Fred’ WHERE LastName = ‘Wilson’
DELETE 语句用于删除表中的行
DELETE FROM 表名称 WHERE 列名称 = 值 # 删除指定行
DELETE FROM table_name 或者 DELETE * FROM table_name # 删除所有行

2.2 SQL 高级教程

规定要返回的记录的数目
SQL server 使用Top
SELECT TOP number|percent column_name(s) FROM table_name
Mysql 使用limit
SELECT column_name(s) FROM table_name LIMIT number
Oracle 语法使用 ROWNUM
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number
SQL TOP 实例
SELECT TOP 2 * FROM Persons # 取两个样本数，
SELECT TOP 50 PERCENT * FROM Persons # 取50% 样本数。
like 与 % 操作符用于在 WHERE 子句中搜索列中的指定模式
备注：常与%号通配符一起使用
SELECT * FROM Persons WHERE City LIKE ‘%g’
in 操作符允许我们在 WHERE 子句中规定多个值
SELECT * FROM Persons WHERE LastName IN (‘Adams’,‘Carter’)
操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期
SELECT * FROM Persons
WHERE LastName
BETWEEN ‘Adams’ AND ‘Carter’
通过使用 SQL，可以为列名称和表名称指定别名（Alias）as
表别名：SELECT column_name(s)
FROM table_name AS alias_name
列别名：SELECT column_name AS alias_name
FROM table_name
join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据
备注：join必须有主键，有inner、left、right、full四种
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
备注：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
SQL SELECT INTO 语句可用于创建表的备份复件
SELECT *
INTO Persons_backup
FROM Persons
备注：IN 子句可用于向另一个数据库中拷贝表
SELECT *
INTO Persons IN ‘Backup.mdb’
FROM Persons
CREATE DATABASE 用于创建数据库
CREATE DATABASE database_name
CREATE TABLE 语句用于创建数据库中的表
备注：数据类型不在本文叙述
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
…
)
约束 (Constraints)
约束主要有以下几种：
NOT NULL_____不接受 NULL 值;
UNIQUE________约束唯一标识数据库表中的每条记录;
PRIMARY KEY___约束唯一标识数据库表中的每条记录(每个表均有一个主键，主键必须为唯一值且值不为null);
FOREIGN KEY___一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY;
CHECK_________用于限制列中的值的范围;
DEFAULT_______用于向列中插入默认值。
CREATE INDEX 语句用于在表中创建索引
drop 删除索引、表和数据库
13.1 删除索引
用于 Microsoft SQLJet (以及 Microsoft Access) 的语法:
DROP INDEX index_name ON table_name
用于 MS SQL Server 的语法:
DROP INDEX table_name.index_name
用于 IBM DB2 和 Oracle 语法:
DROP INDEX index_name
用于 MySQL 的语法:
ALTER TABLE table_name DROP INDEX index_name
13.2 删除表
DROP TABLE 表名称
使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）
TRUNCATE TABLE 表名称
13.3 删除数据库
DROP DATABASE 数据库名称
ALTER TABLE 语句用于在已有的表中添加、修改或删除列
14.1 添加列
ALTER TABLE table_name
ADD column_name datatype
14.2 删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name
14.3 改变表中列的数据类型
ALTER TABLE table_name
ALTER COLUMN column_name datatype
Auto-increment 每次插入新记录时，自动地创建主键字段的值
SQL VIEW（视图）
内建日期处理函数 SQL Date相关函数
NULL 值代表遗漏的未知数，不与0等价
选取在 “Address” 列中带有 NULL 值的记录：
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
选取在 “Address” 列中不带有 NULL 值的记录
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
ISNULL() 函数用于规定如何处理 NULL 值
NVL(), IFNULL() 和 COALESCE() 函数也可以达到相同的结果
我们希望 NULL 值为 0
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
MySQL 中，有三种主要的类型：文本、数字和日期/时间类型

2.3 SQL 函数

用于计算和计数的函数
用法： SELECT function(列) FROM 表
1.1 Aggregate （合计函数）

1，2 Scalar 函数（单一值操作函数）

AVG 函数，返回数值列的平均值。NULL 值不包括在计算中
SELECT AVG(column_name) FROM table_name
COUNT() 函数返回匹配指定条件的行数
返回指定列的值的数目（NULL 不计入）
SELECT COUNT(column_name) FROM table_name
返回表中的记录数
SELECT COUNT(*) FROM table_name
返回指定列的不同值的数目
SELECT COUNT(DISTINCT column_name) FROM table_name
FIRST() 函数返回指定的字段中第一个记录的值
SELECT FIRST(column_name) FROM table_name
LAST() 函数返回指定的字段中最后一个记录的值
SELECT LAST(column_name) FROM table_name
MAX 函数返回一列中的最大值。NULL 值不包括在计算中
SELECT MAX(column_name) FROM table_name
MIN 函数返回一列中的最小值。NULL 值不包括在计算中
SELECT MIN(column_name) FROM table_name
备注：注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。
SUM 函数返回数值列的总数（总额）
SELECT SUM(column_name) FROM table_name
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组
希望查找每个客户（Customer）的总金额（总订单（OrderPrice））
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
希望查找订单总金额少于 2000 的客户
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000
希望查找客户 “Bush” 或 “Adams” 拥有超过 1500 的订单总金额
SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Bush' OR Customer='Adams' GROUP BY Customer HAVING SUM(OrderPrice)>1500
UCASE 函数把字段的值转换为大写
SELECT UCASE(column_name) FROM table_name
LCASE 函数把字段的值转换为小写
SELECT LCASE(column_name) FROM table_name
MID 函数用于从文本字段中提取字符
SELECT MID(column_name,start[,length]) FROM table_name
LEN 函数返回文本字段中值的长度
SELECT LEN(column_name) FROM table_name
ROUND 函数用于把数值字段舍入为指定的小数位数
SELECT ROUND(column_name,decimals) FROM table_name
NOW 函数返回当前的日期和时间
提示：如果您在使用 Sql Server 数据库，请使用 getdate() 函数来获得当前的日期时间。
SELECT NOW() FROM table_name
FORMAT 函数用于对字段的显示进行格式化
SELECT FORMAT(column_name,format) FROM table_name

阿波策得

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
SQL练习

SQL基础练习一、在线练习一、在线练习SQLzoo网站练习题：https://zh.sqlzoo.net/wiki/SELECT_basics/zh显示 Germany 的人口select population from world where name = ‘Germany’;查询面积为5,000,000 以上平方公里的国家,显示每个国家的名字和人均(gdp/populatio...
复制链接

扫一扫