Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL & PostgreSQL

MySQL & PostgreSQL

オープンソースデータベース比較セミナーの資料です
https://osscons-database.connpass.com/event/56187/

soudai sone

May 25, 2017
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. ࣮ߦܭը 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;
  2. αϒΫΤϦ —— 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
  3. 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'
  4. ෳ਺ར༻ͨ͠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';
  5. ૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age

    FROM users WHERE id BETWEEN 10 AND 100000)
  6. ΢Πϯυ΢ؔ਺ SELECT rank() OVER (PARTITION BY age ORDER BY id)

    , * FROM users WHERE age BETWEEN 10 AND 30 LIMIT 100
  7. ࣮ફͰϋϚΔ᠘ τϥϯβΫγϣϯ෼཭Ϩϕϧ ໊લ ෼཭Ϩϕϧ આ໌ 4&3*"-*;"#-& ͍ͭ͞Α ௚ྻతʹॲཧ 3&1&"5"#-&3&"% ڧ͍

    ಡΈऔΓର৅ͷσʔλ ΛৗʹಡΈऔΔ 3&"%$0..*55&% ·͊·ڧ͍ ֬ఆͨ͠࠷৽σʔλΛ ৗʹಡΈऔΔ 3&"% 6/$0..*55&% ऑ͍ ଞͷॲཧʹΑͬͯߦΘ Ε͍ͯΔɺॻ͖͔͚ͷ σʔλ·ͰಡΈऔΔɻ
  8. ࣮ફͰϋϚΔ᠘ τϥϯβΫγϣϯ෼཭Ϩϕϧ ໊લ ෼཭Ϩϕϧ આ໌ 4&3*"-*;"#-& ͍ͭ͞Α ௚ྻతʹॲཧ 3&1&"5"#-&3&"% ڧ͍

    ಡΈऔΓର৅ͷσʔλ ΛৗʹಡΈऔΔ 3&"%$0..*55&% ·͊·ڧ͍ ֬ఆͨ͠࠷৽σʔλΛ ৗʹಡΈऔΔ 3&"% 6/$0..*55&% ऑ͍ ଞͷॲཧʹΑͬͯߦΘ Ε͍ͯΔɺॻ͖͔͚ͷ σʔλ·ͰಡΈऔΔɻ .Z42-ͷσϑΥϧτ͸͜͜
  9. ࣮ફͰϋϚΔ᠘ τϥϯβΫγϣϯ෼཭Ϩϕϧ ໊લ ෼཭Ϩϕϧ આ໌ 4&3*"-*;"#-& ͍ͭ͞Α ௚ྻతʹॲཧ 3&1&"5"#-&3&"% ڧ͍

    ಡΈऔΓର৅ͷσʔλ ΛৗʹಡΈऔΔ 3&"%$0..*55&% ·͊·ڧ͍ ֬ఆͨ͠࠷৽σʔλΛ ৗʹಡΈऔΔ 3&"% 6/$0..*55&% ऑ͍ ଞͷॲཧʹΑͬͯߦΘ Ε͍ͯΔɺॻ͖͔͚ͷ σʔλ·ͰಡΈऔΔɻ 1PTUHSF42- 0SBDMF%# 42-4FSWFS ͷσϑΥϧτ͸͜͜
  10. ࣮ફͰϋϚΔ᠘ τϥϯβΫγϣϯ෼཭Ϩϕϧ ໊લ ෼཭Ϩϕϧ આ໌ 4&3*"-*;"#-& ͍ͭ͞Α ௚ྻతʹॲཧ 3&1&"5"#-&3&"% ڧ͍

    ಡΈऔΓର৅ͷσʔλ ΛৗʹಡΈऔΔ 3&"%$0..*55&% ·͊·ڧ͍ ֬ఆͨ͠࠷৽σʔλΛ ৗʹಡΈऔΔ 3&"% 6/$0..*55&% ऑ͍ ଞͷॲཧʹΑͬͯߦΘ Ε͍ͯΔɺॻ͖͔͚ͷ σʔλ·ͰಡΈऔΔɻ 1PTUHSF42- 0SBDMF%# 42-4FSWFS ͷσϑΥϧτ͸͜͜ ϑΝϯτϜϦʔυ΍μʔςΟϦʔυͳͲͷ࿩͸ࠓ೔͸͠·ͤΜ ʮ͑ʁͳʹͦΕʯͬͯਓ͸ࠓ͙͢άάοͯษڧͨ͠ํ͕͍͍Ͱ͢