データ検索
- SELECT 構文は、指定した「列のデータ」を操作する。
SELECT <列名> AS <別列名>, ...:指定した列名に別名をつける
$ SELECT COUNT(id) AS number FROM entries; 日本語は、"" をつける $ SELECT COUNT(id) AS "応募数" FROM entries;
SELECT DISTINCT(<列名>):重複データを除外してユニークな値として抽出する。
$ SELECT DISTINCT(pref_id) FROM address;
mysql> SELECT -> dept_no -> FROM -> dept_manager; +---------+ | dept_no | +---------+ | d001 | | d001 | | d002 | | d002 | | d003 | | d003 | | d004 | | d004 | | d004 | | d004 | | d005 | | d005 | | d006 | | d006 | | d006 | | d006 | | d007 | | d007 | | d008 | | d008 | | d009 | | d009 | | d009 | | d009 | +---------+ 24 rows in set (0.02 sec) mysql> SELECT -> DISTINCT(dept_no) ※<-ここ!!! -> FROM -> dept_manager; +---------+ | dept_no | +---------+ | d001 | | d002 | | d003 | | d004 | | d005 | | d006 | | d007 | | d008 | | d009 | +---------+ mysql> SELECT -> DISTINCT(dept_no) AS uniq_dept_no -> FROM -> dept_manager; +--------------+ | uniq_dept_no | +--------------+ | d001 | | d002 | | d003 | | d004 | | d005 | | d006 | | d007 | | d008 | | d009 | +--------------+
LIMIT <行数>:指定した行数のみ取得
抽出するデータ量が、1万とか100万とかになってくると、サーバーが落ちる可能性が出てくるので、制限をかけて、操作するためのもの。
SELECT <列名> FROM <テーブル名> LIMIT <行数>
$ SELECT id, name FROM companies LIMIT 10;
mysql> SELECT -> COUNT(emp_no) -> FROM -> employees; +---------------+ | COUNT(emp_no) | +---------------+ | 300024 | <- 30万行 データがあるって言う意味 +---------------+ 1 row in set (0.45 sec) mysql> SELECT -> * -> FROM -> employees -> LIMIT 10; <- ※ここ!!! 10行 に制限している。 +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.02 sec)
WHERE <行数>:取得する 行 を指定する
「=」:指定したものと等しいものを取得する
「<>」:指定したもの と等しくないものを取得する
「>, >=」:指定したもの 「より」大きい or 「以上」 を取得する
「<, <=」:指定したもの 「より」小さい or 「以下」 を取得する
IS NULL:値が、NULL のものを取得する。つまり、空のものを取得する
IS NOT NULL:値が、NULL 「ではない」のものを取得する。つまり、空でないものを取得する
LIKE:部分一致するレコードを取得する
- %:0文字以上の文字列
NOT LIKE:部分一致しないレコードを取得する
7, 8 は、検索の際によく使われる。
$ SELECT * FROM employees WHERE first_name LIKE '%da%'; employees テーブルの中で、first_name に 'da' が含まれているレコードを取得する。
mysql> SELECT -> * -> FROM -> employees -> WHERE ※<- ここ!!! -> emp_no < 10016; ※<- ここ!!! +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | | 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 | | 10012 | 1960-10-04 | Patricio | Bridgland | M | 1992-12-18 | | 10013 | 1963-06-07 | Eberhardt | Terkki | M | 1985-10-20 | | 10014 | 1956-02-12 | Berni | Genin | M | 1987-03-11 | | 10015 | 1959-08-19 | Guoxiang | Nooteboom | M | 1987-07-02 | +--------+------------+------------+-----------+--------+------------+ 15 rows in set mysql> SELECT -> * -> FROM -> employees -> WHERE ※<- ここ!!! -> gender <> 'F' ※<- ここ!!! -> LIMIT 10; +--------+------------+------------+-------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-------------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10012 | 1960-10-04 | Patricio | Bridgland | M | 1992-12-18 | | 10013 | 1963-06-07 | Eberhardt | Terkki | M | 1985-10-20 | | 10014 | 1956-02-12 | Berni | Genin | M | 1987-03-11 | | 10015 | 1959-08-19 | Guoxiang | Nooteboom | M | 1987-07-02 | | 10016 | 1961-05-02 | Kazuhito | Cappelletti | M | 1995-01-27 | +--------+------------+------------+-------------+--------+------------+ 10 rows in set mysql> SELECT -> * -> FROM -> employees -> WHERE -> first_name -> LIKE '%eor%' ※<- ここ!!! -> LIMIT 10 -> ; +--------+------------+------------+----------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+----------------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10055 | 1956-06-06 | Georgy | Dredge | M | 1992-04-27 | | 10909 | 1954-11-11 | Georgi | Atchley | M | 1985-04-21 | | 11029 | 1962-07-12 | Georgi | Itzfeldt | M | 1992-12-27 | | 11152 | 1962-11-13 | Georgy | Walstra | F | 1988-02-18 | | 11430 | 1957-01-23 | Georgi | Klassen | M | 1996-02-27 | | 11514 | 1963-08-17 | Georgy | Iwayama | M | 1988-07-30 | | 11788 | 1962-04-11 | Georgy | Gopalakrishnan | M | 1986-10-03 | | 12157 | 1960-03-30 | Georgi | Barinka | M | 1985-06-04 | | 12591 | 1956-05-10 | Georgy | Makrucki | M | 1995-09-09 | +--------+------------+------------+----------------+--------+------------+ 10 rows in set
AND, OR:複数条件を指定する
AND:A かつ B
OR:A または B
*AND式 $ SELECT * FROM employees WHERE first_name = 'Parto' AND gender = 'F'; *OR式 $ SELECT * FROM employees WHERE first_name = 'Parto' OR gender = 'F'; mysql> SELECT -> * -> FROM -> employees -> WHERE -> birth_date = '1964-06-02' -> AND gender = 'F' <- ※ ここ!!! -> LIMIT 10; +--------+------------+------------+----------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+----------------+--------+------------+ | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 20735 | 1964-06-02 | Tetsushi | Stassinopoulos | F | 1985-05-14 | | 20773 | 1964-06-02 | Takahiro | Miyakawa | F | 1989-12-13 | | 26531 | 1964-06-02 | Sarita | Matzat | F | 1993-02-07 | | 33718 | 1964-06-02 | Gennady | Heiserman | F | 1991-12-24 | | 45299 | 1964-06-02 | Rasikan | Aumann | F | 1985-05-25 | | 49071 | 1964-06-02 | True | Taubenfeld | F | 1993-01-03 | | 49868 | 1964-06-02 | Slavian | Billawala | F | 1987-06-04 | | 81497 | 1964-06-02 | Mohammad | Trachtenberg | F | 1987-07-25 | | 94656 | 1964-06-02 | Nikolaus | Negoita | F | 1991-10-08 | +--------+------------+------------+----------------+--------+------------+ 10 rows in set
BETWEEN A AND B:A 以上 B 以下
mysql> SELECT -> * -> FROM -> employees -> WHERE -> birth_date BETWEEN '1959-06-05'AND'1959-06-07' -> LIMIT 20; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 17740 | 1959-06-07 | Valeska | Klyachko | M | 1998-02-18 | | 20567 | 1959-06-05 | Etsuo | Kroon | M | 1986-08-27 | | 21888 | 1959-06-06 | Neelam | Schaap | M | 1988-10-07 | | 22231 | 1959-06-06 | Dayanand | Willoner | M | 1991-11-16 | | 28028 | 1959-06-05 | Howell | Czap | M | 1986-04-03 | | 31116 | 1959-06-05 | Vitali | Pagter | M | 1988-08-12 | | 31515 | 1959-06-06 | Masato | Viele | F | 1990-07-26 | | 31628 | 1959-06-06 | Gilbert | Cummings | M | 1989-06-26 | | 33763 | 1959-06-05 | Siamak | Lores | F | 1991-05-27 | | 33819 | 1959-06-06 | Jayson | Kornatzky | F | 1990-01-23 | | 35530 | 1959-06-06 | Along | Bauknecht | M | 1987-07-28 | | 36752 | 1959-06-07 | Trygve | Spieker | F | 1990-03-12 | | 36811 | 1959-06-05 | Seshu | Rissland | F | 1986-07-20 | | 37754 | 1959-06-07 | Sachin | Langford | F | 1992-08-23 | | 38709 | 1959-06-06 | Kasidit | Luan | M | 1990-10-16 | | 39021 | 1959-06-07 | Muneo | Majewski | F | 1988-08-16 | | 41078 | 1959-06-06 | Yunming | Denna | M | 1994-06-16 | | 42034 | 1959-06-05 | Basil | Coullard | F | 1998-12-15 | | 45410 | 1959-06-07 | Ishfaq | Mitchem | F | 1987-05-05 | | 45595 | 1959-06-07 | Maya | Demizu | M | 1987-02-16 | +--------+------------+------------+-----------+--------+------------+ 20 rows in set
IN, NOT IN:含む、含まない
$ SELECT * FROM employees WHERE emp_no IN ('10001', '10010', '10015'); $ SELECT * FROM employees WHERE emp_no NOT IN ('10045', '10004', '10080');
IN は、OR の発展バージョン
emp_no = '10001' OR emp_no = '10010' OR emp_no = '10015';
IN の方が、OR より短く書ける mysql> SELECT -> * -> FROM -> employees -> WHERE -> birth_date = '1962-06-03' -> OR birth_date = '1964-06-02' -> OR birth_date = '1959-06-05' -> LIMIT 20; +--------+------------+------------+----------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+----------------+--------+------------+ | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 11511 | 1964-06-02 | Arlette | Mikschl | M | 1991-09-06 | | 19685 | 1962-06-03 | Kasturi | Frolund | F | 1993-03-07 | | 20567 | 1959-06-05 | Etsuo | Kroon | M | 1986-08-27 | | 20735 | 1964-06-02 | Tetsushi | Stassinopoulos | F | 1985-05-14 | | 20773 | 1964-06-02 | Takahiro | Miyakawa | F | 1989-12-13 | | 23155 | 1962-06-03 | Kenton | Piveteau | M | 1989-01-18 | | 25760 | 1962-06-03 | Shuho | Baaleh | F | 1990-11-22 | | 26531 | 1964-06-02 | Sarita | Matzat | F | 1993-02-07 | | 28028 | 1959-06-05 | Howell | Czap | M | 1986-04-03 | | 29264 | 1964-06-02 | Seongbin | Wroclawski | M | 1985-09-25 | | 31116 | 1959-06-05 | Vitali | Pagter | M | 1988-08-12 | | 32915 | 1962-06-03 | Jianwen | Conta | M | 1992-10-08 | | 33718 | 1964-06-02 | Gennady | Heiserman | F | 1991-12-24 | | 33763 | 1959-06-05 | Siamak | Lores | F | 1991-05-27 | | 36811 | 1959-06-05 | Seshu | Rissland | F | 1986-07-20 | | 40827 | 1962-06-03 | Yinghua | Bouloucos | F | 1991-04-07 | | 42034 | 1959-06-05 | Basil | Coullard | F | 1998-12-15 | | 45299 | 1964-06-02 | Rasikan | Aumann | F | 1985-05-25 | | 49071 | 1964-06-02 | True | Taubenfeld | F | 1993-01-03 | +--------+------------+------------+----------------+--------+------------+ 20 rows in set mysql> SELECT -> * -> FROM -> employees -> WHERE -> birth_date IN ('1959-06-05', '1962-06-03','1964-06-02') -> LIMIT 20; +--------+------------+------------+----------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+----------------+--------+------------+ | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 11511 | 1964-06-02 | Arlette | Mikschl | M | 1991-09-06 | | 19685 | 1962-06-03 | Kasturi | Frolund | F | 1993-03-07 | | 20567 | 1959-06-05 | Etsuo | Kroon | M | 1986-08-27 | | 20735 | 1964-06-02 | Tetsushi | Stassinopoulos | F | 1985-05-14 | | 20773 | 1964-06-02 | Takahiro | Miyakawa | F | 1989-12-13 | | 23155 | 1962-06-03 | Kenton | Piveteau | M | 1989-01-18 | | 25760 | 1962-06-03 | Shuho | Baaleh | F | 1990-11-22 | | 26531 | 1964-06-02 | Sarita | Matzat | F | 1993-02-07 | | 28028 | 1959-06-05 | Howell | Czap | M | 1986-04-03 | | 29264 | 1964-06-02 | Seongbin | Wroclawski | M | 1985-09-25 | | 31116 | 1959-06-05 | Vitali | Pagter | M | 1988-08-12 | | 32915 | 1962-06-03 | Jianwen | Conta | M | 1992-10-08 | | 33718 | 1964-06-02 | Gennady | Heiserman | F | 1991-12-24 | | 33763 | 1959-06-05 | Siamak | Lores | F | 1991-05-27 | | 36811 | 1959-06-05 | Seshu | Rissland | F | 1986-07-20 | | 40827 | 1962-06-03 | Yinghua | Bouloucos | F | 1991-04-07 | | 42034 | 1959-06-05 | Basil | Coullard | F | 1998-12-15 | | 45299 | 1964-06-02 | Rasikan | Aumann | F | 1985-05-25 | | 49071 | 1964-06-02 | True | Taubenfeld | F | 1993-01-03 | +--------+------------+------------+----------------+--------+------------+ 20 rows in set
ちょいと、応用 emp_no が、20,000 の人の first_name と last_name を取得する場合 mysql> SELECT -> first_name, last_name -> FROM -> employees -> WHERE -> emp_no = '20000'; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Jenwei | Matzke | +------------+-----------+ 1 row in set