SQL・Databeseを基本からまとめてみた【MySQL / CREATE TABLE・INSERT・UPDATE・DELETE】

CREATE TABLE で新しいテーブルを作成する

MySQL で新しいテーブルを作成するには CREATE TABLE 文を使う。
いろんなオプションが指定できるが、一番基本の CREATE 文の構文は下記になる。

basic.sql

CREATE TABLE テーブル名 (
    カラム名1 データ型 [NOT NULL | NULL], 
    カラム名2 データ型 [NOT NULL | NULL], 
    カラム名3 データ型 [NOT NULL | NULL]  
);

sample.sql

CREATE TABLE SAMPLE_3_1_1 (
  ID                INTEGER      NOT NULL PRIMARY KEY,
  NAME              VARCHAR(30)  NOT NULL,
  GENDER            CHAR(1)      NOT NULL,
  BIRTHDAY          DATE         NOT NULL,
  WEIGHT            DECIMAL(4,1),
  REGIST_TIMESTAMP  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT 文でデータを 1 行挿入する

MySQL でテーブルにデータを挿入するには INSERT 文を使う。
1 レコードを挿入する、基本の INSERT 文の構文は次の通り。

basic.sql

INSERT INTO テーブル名 
        (カラム名1, カラム名2, カラム名3, ...) 
    VALUES 
        (値1, 値2, 値3, ...);

sample.sql

INSERT INTO SAMPLE_3_1_1 (ID , NAME     , GENDER , BIRTHDAY     , WEIGHT , REGIST_TIMESTAMP      )
                  VALUES (1  , 'MOCO'   , 'F'    , '2014-05-04' , 3.5    , '2020-08-01 00:00:00' ) ;
INSERT INTO SAMPLE_3_1_1 (ID , NAME     , GENDER , BIRTHDAY     , WEIGHT , REGIST_TIMESTAMP      )
                  VALUES (2  , 'CHOCO'  , 'M'    , '2011-08-25' , 5.2    , '20200801000000'      ) ;
INSERT INTO SAMPLE_3_1_1 (ID , NAME     , GENDER , BIRTHDAY     , WEIGHT , REGIST_TIMESTAMP      )
                  VALUES (3  , 'TARO'   , 'M'    , '2013-01-02' , 6.2    , CURRENT_TIMESTAMP     ) ;
INSERT INTO SAMPLE_3_1_1 (ID , NAME     , GENDER , BIRTHDAY              , REGIST_TIMESTAMP      )
                  VALUES (4  , 'RINRIN' , 'F'    , '2015-12-12'          , NOW()                 ) ;
INSERT INTO SAMPLE_3_1_1 (ID , NAME     , GENDER , BIRTHDAY     , WEIGHT                         )
                  VALUES (5  , 'CHAMP'  , 'M'    , '2013-01-02' , 10.9                           ) ;

カラム1 に値1 が、カラム2 に値2 が、と順番に挿入される。

NULL のカラムや、NOT NULL でもデフォルト値の指定されているカラムや AUTO_INCREMENT が指定されているカラムは指定しなくてもよい。

UPDATE 文でテーブルのデータを更新する

MySQL でテーブルのデータを更新するには UPDATE 文を使る。
テーブルのレコードを更新する、基本の UPDATE 文の構文は次の通り。

basic.sql

UPDATE  テーブル名
SET     カラム名1 = 値1,
        カラム名2 = 値2,
        カラム名3 = 値3,
        ...
WHERE   条件文;

sample.sql

UPDATE SAMPLE_3_1_1
   SET NAME   = 'MOCOMOCO',
       WEIGHT = 4.5
 WHERE ID     = 1;

条件文に合致するレコードのカラム1 の値が値1 に、カラム2 は値2 に ... と更新される。
デフォルトでは Safe Updates モードが有効化されていて、WHERE 句をつけなかったり、WHERE 句の条件文にキーカラムを使わないとエラーになる。

WHERE 句は必須ではないが、WHERE 句をつけないと、Safe Updates モードを OFF にしている場合は、テーブルの全レコードが更新されるのでお気をつける。

DELETE 文でテーブルのデータを削除する

MySQL でテーブルのデータを削除するには DELETE 文を使う。
テーブルのレコードを更新する、基本の DELETE 文の構文は次の通り。

basic.sql

DELETE
FROM    テーブル名
WHERE   条件文;

sample.sql

DELETE FROM SAMPLE_3_1_1
 WHERE ID = 2 ;

テーブルの条件文に合致するレコードを削除する。
デフォルトでは Safe Updates モードが有効化されていて、WHERE 句をつけなかったり、WHERE 句の条件文にキーカラムを使わないとエラーになる。

WHERE 句は必須ではありませんが、WHERE 句をつけないと、Safe Updates モードを OFF にしている場合は、テーブルの全レコードが削除されるので注意する。

DELETEの特徴

• 条件付き削除が可能
• 処理スピードが速い
• DML（データ操作言語）
• やり直し（ROLLBACK）できる
• DELETE権限が必要
• ログや処理情報が残る

ALTER TABLE でテーブルにカラムを追加する

createテーブル文で定義した情報を修正変更する

■カラムの追加

basic.sql

-- 最後尾に追加する場合(ADD)

ALTER TABLE テーブル名
 ADD 追加カラム名 追加カラム型 追加カラム名のオプション;

-- 先頭に追加する場合(ADD-FIRST)

ALTER TABLE テーブル名
 ADD 追加カラム名 追加カラム型 追加カラム名のオプション FIRST;

-- 既存カラムの後に追加する場合(ADD-AFTER)

ALTER TABLE テーブル名
 ADD 追加カラム名 追加カラム型 追加カラム名のオプション AFTER 既存カラム名;

sample.sql

-- カラムの追加
ALTER TABLE SAMPLE_3_1_1
ADD COLUMN COLOR VARCHAR(15) NOT NULL DEFAULT 'WHITE' COMMENT '毛色' AFTER WEIGHT;

-- カラム定義（型＋オプション）の変更
ALTER TABLE SAMPLE_3_1_1
MODIFY COLUMN FUR_COLOR VARCHAR(30) DEFAULT 'BROWN';

-- カラム名＋カラム定義（型＋オプション）の変更
ALTER TABLE SAMPLE_3_1_1
CHANGE COLUMN FUR_COLOR DOGS_FUR_COLOR VARCHAR(30) NOT NULL DEFAULT 'BROWN' COMMENT '犬の毛色';

■カラム情報の変更

basic.sql

-- カラム名の変更（RENAME COLUMN）
ALTER TABLE テーブル名
 RENAME COLUMN 対象カラム名 TO 変更後のカラム名 ;

-- カラム定義（型＋オプション）の変更（MODIFY）
ALTER TABLE テーブル名
 MODIFY 対象カラム名 変更後のカラム定義 ;

-- 全カラム情報（カラム名＋型＋オプション）の変更（CHANGE）
ALTER TABLE テーブル名
 CHANGE 対象カラム名 変更後のカラム名 変更後のカラム定義 ;

sample.sql

-- カラム名の変更
ALTER TABLE SAMPLE_3_1_1
RENAME COLUMN COLOR TO FUR_COLOR;

■カラムの削除

basic.sql

-- カラムの削除(DROP COLUMN)

ALTER TABLE テーブル名
 DROP 削除対象のカラム名 ;

sample.sql

ALTER TABLE SAMPLE_3_1_1
DROP DOGS_FUR_COLOR ;

TRUNCATEで表内のデータを全削除する

SQLのTRUNCATE文を使うことで、表内のデータを全削除することができる。

basic.sql

TRUNCATE  TABLE  テーブル名;

TRUNCATEの特徴

• 全件削除しかできない
• 処理スピードが速い
• DDL(データ定義言語)
• やり直し（ROLLBACK）できない
• DROP権限が必要
• ログや処理情報が残らない

DROP TABLE でテーブルを削除する

MySQL でテーブルを作成するには DROP TABLE 文を使う。
基本の DROP TABLE 文の構文は次の通り。

basic.sql

DROP TABLE テーブル名;

テーブル名をカンマで続けて、複数のテーブルを同時に削除することもできる。

参考サイト

【SQL】未経験者もこれ一本でOK！MySQLで学ぶ「SQL」「データベース基礎」講座【プログラミング初心者向け】
MySQL