Zenn
Closed10

SQLZOO 学習記録

SQL
Kaito SugimotoKaito Sugimoto

0 SELECT basics

基本

SELECT 列名1, 列名2, ...
FROM テーブル名
WHERE 条件式

例: population 列から name が Germany と等しいレコードを検索する

SELECT 
  population 
FROM 
  world
WHERE 
  name = 'Germany'


指定した複数の値に一つ以上一致するレコードを検索するには IN 句を用いる。

SELECT 列名1 ,列名2, ...
FROM テーブル名
WHERE 列名 IN (条件1, 条件2, ...)

例: name 列と population 列から name が Sweden, Norway, Denmark のいずれかであるレコードを検索する

SELECT 
  name, population 
FROM 
  world
WHERE 
  name IN ('Sweden', 'Norway', 'Denmark')


指定した範囲のレコードを検索するには BETWEEN 句を使用する。

SELECT 列名1 ,列名2, ...
FROM テーブル名
WHERE 列名 BETWEEN 下限 AND 上限

例: name 列と area 列から area が 200,000 から 250,000 のレコードを検索する

SELECT 
  name, area 
FROM 
  world
WHERE 
  area BETWEEN 200000 AND 250000
Kaito SugimotoKaito Sugimoto

1 SELECT name

LIKE 句を用いることで文字列検索ができる。


% は0文字以上の任意の文字列を表すワイルドカード。

例: name 列から name が Y から始まるレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE 'Y%'

例: name 列から name が y で終わるレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE '%y'

例: name 列から name が x を含むレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE '%x%'

例: name 列から name が land で終わるレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE '%land'

例: name 列から name が C で始まり ia で終わるレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE 'C%ia'

例: name 列から o を2連続で含む name のレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE '%oo%'

例: name 列から a を3つ以上含む name のレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE '%a%a%a%'


_ は任意の1文字を表すワイルドカード。

例: name 列から name の2番目の文字が t のレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE '_t%'

例: name 列から、o を2つ含み、その2つの o の間に別の2文字を含むような name のレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE '%o__o%'

例: name 列から name が 4 文字のレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  name LIKE '____'


例: 国名と首都名が一致する国名のレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  capital LIKE name

文字列の連結は DBMS ごとに異なる。
MySQL であれば CONCAT(文字列1, 文字列2, 文字列3)
PostgreSQL であれば 文字列1 || 文字列2

例: "Mexico" に対する "Mexico City" のように、首都名に City が付く国名のレコードを検索する

SELECT 
  name 
FROM 
  world
WHERE 
  capital LIKE CONCAT(name, ' City')

例: 首都名が国名を含むような、首都名と国名のレコードを検索する

SELECT 
  capital, name 
FROM 
  world
WHERE 
  capital LIKE CONCAT('%', name, '%')

例: 首都名が国名の延長であるような、首都名と国名のレコードを検索する

SELECT 
  capital, name 
FROM 
  world
WHERE 
  capital LIKE CONCAT(name, '_%')

REPLACE 関数で文字列を置換することができる。

例: 首都名が国名の延長であるデータを検索して、国名と延長部分のレコードを表示する

SELECT 
  name, REPLACE(capital, name, '') 
FROM
  world
WHERE
  capital LIKE concat(name, '_%')
Kaito SugimotoKaito Sugimoto

2 SELECT from World

例: population が2億以上の国名を表示する

SELECT 
  name
FROM 
  world
WHERE
  population >= 200000000

例: population が2億以上の国名と人口あたりGDPを表示する

SELECT
  name, gdp / population
FROM
  world
WHERE
  population >= 200000000

例: continent が South America の国名と人口（百万単位）を表示する

SELECT
  name, population / 1000000
FROM
  world
WHERE
  continent LIKE '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%'

例: 面積が300万以上か、人口が2億5千万以上の国名を表示する

SELECT
  name, population, area
FROM
  world
WHERE
  area > 3000000 OR population > 250000000

例: 面積が300万以上か、人口が2億5千万以上のうちいずれかのみ当てはまっている国名を表示する

SELECT
  name, population, area
FROM
  world
WHERE
  area > 3000000 XOR population > 250000000
Kaito SugimotoKaito Sugimoto

2 SELECT from World（続き）

SQL で四捨五入を用いるには ROUND 関数を使う。

ROUND(number, length)

length が小数部の桁数


例: 南アメリカの国について、国名と、人口100万単位を小数2桁までで四捨五入したものと、GDP10億単位を小数2桁までで四捨五入したものを表示する

SELECT
  name, 
  ROUND(population / 1000000, 2), 
  ROUND(gdp / 1000000000, 2)
FROM
  world
WHERE
  continent LIKE 'South America'

例: GDPが一兆はある国について、名前と、人口あたりGDPを1000単位で表したものを表示する

SELECT
  name, ROUND(gdp / population, -3)
FROM
  world
WHERE
  gdp >= 1000000000000
Kaito SugimotoKaito Sugimoto

2 SELECT from World（続き）

文字列の文字数は LEN や LENGTH などで取れる。DBMS によって異なる: http://www.sql-reference.com/string/char_length.html

例: name と length の長さが同じ国

SELECT
  name, capital
FROM
  world
WHERE
  LEN(name) = LEN(capital)


SQL Server などでは

LEFT(名前, 文字数)

で名前の左から文字数分だけ文字を取得できる。

https://docs.microsoft.com/ja-jp/sql/t-sql/functions/left-transact-sql?view=sql-server-ver15


「等しくない」の演算子は != または <>

例: name と capital の先頭文字が同じだが、name と capital が一致してはいない国

SELECT
  name, capital
FROM
  world
WHERE
  LEFT(name, 1) = LEFT(capital, 1) and name <> capital

例: 名前にa, i, u, e, o全て含まれていて空白文字を含まない国

SELECT
  name
FROM
  world
WHERE
  name LIKE '%a%' and name LIKE '%i%' and name LIKE '%u%' and
  name LIKE '%e%' and name LIKE '%o%' and name NOT LIKE '% %'
Kaito SugimotoKaito Sugimoto

3 SELECT from Nobel Tutorial

例: 1950年のノーベル賞を表示

SELECT
  *
FROM
  nobel
WHERE
  yr = 1950

例: 1962年に文学賞を受賞した人

SELECT 
  winner
FROM 
  nobel
WHERE 
  yr = 1962 AND subject LIKE 'Literature'

例: アインシュタインはいつ、何のノーベル賞を受賞したか

SELECT
  yr, subject
FROM
  nobel
WHERE
  winner LIKE 'Albert Einstein'

例: 2000年以降にノーベル平和賞を受賞した人

SELECT
  winner
FROM
  nobel
WHERE
  yr >= 2000 AND subject LIKE 'Peace'

例: 1980~1989年のノーベル文学賞の詳細

SELECT
  *
FROM
  nobel
WHERE
  subject LIKE 'Literature' AND yr BETWEEN 1980 AND 1989
Kaito SugimotoKaito Sugimoto

3 SELECT from Nobel Tutorial（続き）

例: 以下のノーベル賞受賞者の詳細を表示する

  • Theodore Roosevelt
  • Woodrow Wilson
  • Jimmy Carter
  • Barack Obama
SELECT
  *
FROM
  nobel
WHERE
  winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama')

例: John から始まるノーベル賞受賞者一覧

SELECT
  winner
FROM
  nobel
WHERE
  LEFT(winner, 4) LIKE 'John'

例: 1980年の物理学賞と1984年の化学賞の詳細

SELECT
  *
FROM
  nobel
WHERE
  (subject LIKE 'Physics' AND yr = 1980) OR
  (subject LIKE 'Chemistry' AND yr = 1984)

例: 1980年の化学賞、医学賞以外の詳細

SELECT
  *
FROM
  nobel
WHERE
  yr = 1980 AND subject NOT IN ('Chemistry', 'Medicine')

例: 1910年より前の医学賞と、2004年以降の医学賞の詳細

SELECT
  *
FROM
  nobel
WHERE
  (yr < 1910 AND subject LIKE 'Medicine') OR
  (yr >= 2004 AND subject LIKE 'Literature')
Kaito SugimotoKaito Sugimoto

3 SELECT from Nobel Tutorial（続き）

例: PETER GRÜNBERG が受賞したノーベル賞の詳細

SELECT
  *
FROM
  nobel
WHERE
  winner LIKE 'PETER GRÜNBERG'

（ウムラウトが入っているような特殊文字でも大丈夫...？）

例: EUGENE O'NEILL が受賞したノーベル賞の詳細

SELECT
  *
FROM
  nobel
WHERE
  winner LIKE 'EUGENE O''NEILL'

SQL内の文字列のアポストロフィー（single quote）は、'' というように2回重ねる


ORDER BY 句は複数繰り返すことができる

ORDER BY ソート1 [ASC | DESC], ソートキー2 [ASC | DESC], ...

例: Sir で名前が始まり、新しい年からソートし、同じ年の場合は名前順に表示

SELECT
  winner, yr, subject
FROM
  nobel
WHERE
  LEFT(winner, 4) LIKE 'Sir '
ORDER BY
  yr DESC, winner

例: 1984年のノーベル賞受賞者を winner、subject でソートして表示するが、Chemistry と Physics は最後に表示する

SELECT 
  winner, subject
FROM 
  nobel
WHERE 
  yr = 1984
ORDER BY 
  subject IN ('Physics','Chemistry'), subject, winner

subject IN ('Physics','Chemistry') は subject が Physics か Chemistry の場合のみ 1 で、あとは 0 に評価されるので、これをソートすると Physics か Chemistry の場合のみ後に来る、というのがポイント

このスクラップは3ヶ月前にクローズされました