0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

【mysql】初心者向けいろいろ

Last updated at Posted at 2022-03-11

ほぼ自分用備忘録です。
詳しい説明は参考URLを見てね。

取り出したい系(抽出)

・ユニークな値を取り出したい。 DISTINCT

参考:https://www.dbonline.jp/mysql/select/index13.html

DISTINCT
SELECT DISTINCT col_name from table_name;

・条件に一致した行だけ欲しい。WHERE

参考:https://www.sejuku.net/blog/51583

WHERE
SELECT * FROM table1 WHERE col_name = 'hoge';

・どれかに一致した行だけ欲しい。WHERE IN()

参考:https://www.dbonline.jp/mysql/select/index6.html

WHERE IN ()
SELECT * FROM table_name
WHERE name in ('スライム', 'ドラキー', 'ももんじゃ');

・ANDとOR両方使いたいよ。(col1 = 'a' OR col1 = 'b') AND col2 = 0

参考:https://kino-code.com/sql13/

AND OR
SELECT * FROM table_name
WHERE (name = 'スライム' OR name = 'ドラキー') AND address = 'トラペッタ';

・あいまい検索したいよ。LIKE

参考:https://qiita.com/chihiro/items/46da8571d4cf37d3123c

LIKE
SELECT * FROM  WHERE name LIKE 'スラ%';
SELECT * FROM  WHERE name LIKE '%イム';

・IDをKeyにした配列がほしいよ。

参考:https://qiita.com/mpyw/items/d52351bd1a8068344cc2

入れたい系(挿入)

・データを挿入したいよ。INSERT INTO

参考:https://www.dbonline.jp/mysql/insert/index1.html

INSERT INTO
INSERT INTO table_name (col1, col2) VALUES ('a', 'b');

・複数レコード挿入。(col1,col2) VALUES (1,'a'),(2,'b')...

参考:https://johobase.com/multiple-insert-sql/

VALUES
INSERT INTO table_name
(number, name, datetime)
VALUES
(1, 'a', '2022-01-01'),
(2, 'b', '2022-01-02'),
(3, 'c', '2022-01-03');

変更したい系(更新)

・テーブルのすべてのレコードの任意列に値を設定したい。UPDATE

UPSATE
UPDATE table_name SET col_name = 0;

・条件に合ったレコードを変更したい。UPDATE+WHERE

参考:https://style.potepan.com/articles/23919.html

UPDATE+WHERE
UPDATE table_name
SET
col1 = 0, col2 = 0	
WHERE
col1 = 1 AND col2 = 2;

・複数列を変更(更新)したい。CASE ~ END, CASE ~ END...

参考:https://blog.trippyboy.com/2014/mysql/mysql%ef%bc%91%e3%82%af%e3%82%a8%e3%83%aa%e3%83%bc%e3%81%a7%e8%a4%87%e6%95%b0%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%81%aeupdate%e3%80%81%e3%81%95%e3%82%89%e3%81%ab%e3%81%afupdate%e3%81%a8insert/2/

CASE
UPDATE table_name SET 
monster = CASE
WHEN number = 1 THEN 'スライム'
WHEN number = 2 THEN 'ドラキー'
WHEN number = 3 THEN 'ももんじゃ'
END,
hp = CASE
WHEN number = 1 THEN 8
WHEN number = 2 THEN 10
WHEN number = 3 THEN 12
END;

・連番を振りなおしたいよ。SET @ ~~

参考:https://qiita.com/MochiMochiChip/items/f05bc3fc9612b94f8004

SET @
SET @cnt_i:=0;
UPDATE table_name SET number = (@cnt_i := @cnt_i + 1 );

テーブルを操作したい系(操作)

・テーブルのレコードを全削除したい。 TRUNCATE

参考:https://johobase.com/truncate-table-sql/#TRUNCATE_TABLE

TRUNCATE
TRUNCATE TABLE table_name;

・テーブルをコピーしたいけどデータはいらない。CREATE TABLE ~ LIKE

参考:https://www.dbonline.jp/mysql/table/index15.html

CREATE TABLE ~ LIKE
CREATE TABLE new_table LIKE table_name;
0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?