MySQL サンプル

COUNT

テーブルのレコード総数を取得します。

MariaDB [world]> SELECT COUNT(*) FROM city;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.001 sec)

SQL

SELECT COUNT(*) FROM city;
SELECT COUNT(*) FROM country;
SELECT COUNT(*) FROM countrylanguage;

テーブルのカラムの確認

SQL

DESC city;
DESC country;
DESC countrylanguage;

上位１０件を抽出

指定されたテーブルの上位10件のレコードを抽出します。

SQL

SELECT * FROM city LIMIT 10;
SELECT * FROM country LIMIT 10;
SELECT * FROM countrylanguage LIMIT 10;

SELECT * FROM city ORDER BY Population ASC LIMIT 10;

条件に合致したレコードを取得

SQL

SELECT * FROM city WHERE CountryCode = 'NLD';
SELECT Name, Population FROM country WHERE Population >= 100000 LIMIT 10;
SELECT * FROM countrylanguage WHERE Language = 'English';

INNER ... JOIN

SQL

SELECT
  c.ID,
  c.Name,
  c.CountryCode,
  c.Population,
  cy.Continent
FROM
  city AS c
INNER JOIN
  country AS cy
ON
  c.CountryCode = cy.Code
LIMIT 10;

LEFT JOIN

SQL

SELECT
  c.ID,
  c.Name,
  c.CountryCode,
  c.Population,
  cy.Continent
FROM
  city AS c
LEFT JOIN
  country AS cy
ON
  c.CountryCode = cy.Code;

RIGHT JOIN

SQL

SELECT
  c.ID,
  c.Name,
  c.CountryCode,
  c.Population,
  cy.Continent
FROM
  city AS c
RIGHT JOIN
  country AS cy
ON
  c.CountryCode = cy.Code;

並び替え

データを昇順にしたい場合は「 ASC 」、降順にしたい場合は「 DESC 」を指定します。

SQL

SELECT * FROM  city ORDER BY Population ASC LIMIT 10;

WHERE句

WHERE句は、テーブルの検索条件を指定するための構文です。

SQL - AND

SELECT * FROM city WHERE Population >= 1000 AND Population <= 2000 ORDER BY Population ASC;

SQL - OR

SELECT * FROM city WHERE Population = 1000 OR Population = 2000 ORDER BY Population ASC;

SQL - BETWEEN

SELECT * FROM city WHERE Population BETWEEN 1000 AND 2000 ORDER BY Population ASC;

SQL - IN

SELECT * FROM city WHERE CountryCode IN ("JPN", "USA") ORDER BY Population ASC;

# count
SELECT COUNT(*) FROM city WHERE CountryCode IN ("JPN", "USA") ORDER BY Population ASC;

IS NULL / IS NOT NULL

IS NULL 演算子は、「 NULL かどうかをチェックする演算子」です。

NULL 以外を探す場合は、IS NOT NULL 演算子を使います。

SQL

SELECT COUNT(*) FROM city WHERE ContryCode IS NULL;
SELECT COUNT(*) FROM city WHERE ContryCode IS NOT NULL;

DISTINCT

重複した行を取り除きます。

SQL

SELECT DISTINCT CountryCode FROM city;

// COUNTと組み合わせる
SELECT COUNT(DISTINCT CountryCode) FROM city;

集計関数

SQL で利用できる集計関数には、次のようなものがあります。

AVG	平均値
COUNT	件数
MAX	最大値
MIN	最小値
SUM	合計値

SQL

SELECT AVG(Population) FROM city;
SELECT COUNT(Population) FROM city;
SELECT MAX(Population) FROM city;
SELECT MIN(Population) FROM city;
SELECT SUM(Population) FROM city;

SELECT CountryCode, AVG(Population), name FROM city GROUP BY CountryCode;
SELECT CountryCode, AVG(Population), name FROM city GROUP BY CountryCode ORDER BY AVG(Population) ASC;

MySQL サンプル - SQL

Other MySQL Documentation