MySQLとPostgreSQLのこと(実践編)

88f4e84b94fe07cddbd9e6479d689192?s=47 soudai sone
February 29, 2016

 MySQLとPostgreSQLのこと(実践編)

第13回 中国地方DB勉強会 in 山口の発表資料です。

https://dbstudychugoku.github.io/

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

February 29, 2016
Tweet

Transcript

  1. 37.
  2. 39.

    ࣮ߦܭը CREATE TABLE `demo`.`users` ( `id` INT NOT NULL AUTO_INCREMENT

    COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', `age` INT NOT NULL COMMENT '', `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '', PRIMARY KEY (`id`) COMMENT '' ); ——શ݅Λબ୒͢ΔͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users; ——PRIMARY KEYʢINDEXʣΛར༻ͨ͠ྫ SELECT * FROM demo.users WHERE id > 100; ——INDEX͕ແ͍ͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users WHERE age > 20;
  3. 40.
  4. 41.
  5. 43.

    αϒΫΤϦ —— INDEXͱͯ͠PRIMARY KEY͕ޮ͍͍ͯΔ SELECT * FROM users WHERE id

    < 1000 AND id > 800 —— 1ճ͔࣮͠ߦ͞Εͳ͍ SELECT * FROM (SELECT * FROM users WHERE id < 1000 AND id > 800) AS dummy
  6. 44.
  7. 46.
  8. 48.
  9. 49.
  10. 52.

    JOIN SELECT * FROM users INNER JOIN users AS tmp

    ON tmp.id = users.id AND tmp.id BETWEEN 10 AND 100000 WHERE users.created = '2016-02-27 04:31:32'
  11. 53.
  12. 76.

    ෳ਺ར༻ͨ͠INDEX CREATE TABLE public.users ( id integer NOT NULL DEFAULT

    nextval('users_id_seq'::regclass), name text NOT NULL, age integer NOT NULL, created timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT users_pkey PRIMARY KEY (id)); CREATE INDEX users_created_idx ON public.users USING tree (created); ——idͱcreatedͷINDEXΛར༻͢Δ SELECT * FROM users WHERE id < 100 AND created < '2016-02-27 05:41:28';
  13. 82.

    ૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age

    FROM users WHERE id BETWEEN 10 AND 100000)
  14. 92.

    ΢Πϯυ΢ؔ਺ SELECT rank() OVER (PARTITION BY age ORDER BY id)

    , * FROM users WHERE age BETWEEN 10 AND 30 LIMIT 100