MYSQL入門 １

　-------------TABLEの作成--------------
mysql> create table tb1(bang varchar(10),name varchar(10),tosi int);



-------------インサート--------------
mysql>INSERT INTO TB1 VALUES('TEST2','KAWA',11),('TEST3','TOSI',44),('TEST4','YAMA',20);

-------------テーブルのカラム構造のコピー--------------

CREATE TABLE TB1_COP LIKE TB1;

-------------テーブルのレコードのコピー--------------
INSERT INTO TB1_COP SELECT * FROM TB1;


-------------カラムデータ型を変更する--------------
mysql> ALTER TABLE tb1 mODIFY BANG varchar(200);


-------------カラムを追加--------------
mysql> ALTER TABLE tb1 ADD birthday DATETIME;


-------------主キー--------------
mysql> CREATE TABLE TB2(ID INT PRIMARY KEY, CATE VARCHAR(10));

-------------主キーオートインクリメント--------------
mysql> CREATE TABLE TB3(ID INT AUTO_INCREMENT PRIMARY KEY, CATE2 VARCHAR(10));


-------------主キーオートインクリメントにインサート--------------
mysql> INSERT INTO TB3(CATE2) VALUES('1'),('2'),('3');

-----------日付関数--------------
mysql> CREATE TABLE NOWTIME(ID INT AUTO_INCREMENT PRIMARY KEY,SHOW_TIME DATETIME);
mysql> INSERT INTO NOWTIME(SHOW_TIME) VALUES(NOW());

+----+---------------------+
| ID | SHOW_TIME           |
+----+---------------------+
|  1 | 2012-01-04 16:27:11 |
|  2 | 2012-01-04 16:27:13 |
|  3 | 2012-01-04 16:27:14 |
|  4 | 2012-01-04 16:27:15 |
|  5 | 2012-01-04 16:27:16 |
+----+---------------------+

----------リミット句--------------
mysql>SELECT * FROM NOWTIME LIMIT 3;

---------WHERE句--------------
mysql> SELECT * FROM NOWTIME WHERE ID = 3;
mysql> SELECT * FROM NOWTIME WHERE ID > 2 and id < 5


---------case when--------------
SELECT ID,
  CASE
    WHEN ID > 2 THEN '小さい'
    WHEN ID > 3 THEN 'すこし小さい'
    WHEN ID > 4 THEN '中ぐらい'
    WHEN ID > 5 THEN '大きい'
    ELSE '図れません'
  END
FROM TB3;
--------+
|  1 | 図れません

                |
|  2 | 図れません

                |
|  3 | 小さい

              |
|  4 | 小さい

              |
|  5 | 小さい

              |
|  6 | 小さい

              |
              |

---------UPDATE--------------
mysql> UPDATE 「テーブル名」  SET「カラム名」=「設定値」;
mysql> UPDATE 「テーブル名」  SET「カラム名」=「設定値」WHERE 条件;

---------複数テーブルのレコードを合わせて表示--------------
mysql> CREATE TABLE TEST4(ID1 INT AUTO_INCREMENT PRIMARY KEY, TIMES1 DATETIME);
mysql> CREATE TABLE TEST5(ID2 INT AUTO_INCREMENT PRIMARY KEY, TIMES2 DATETIME);


mysql> INSERT INTO TEST4(TIMES1) VALUES(NOW()),(NOW()),(NOW()),(NOW());
mysql> INSERT INTO TEST5(TIMES2) VALUES(NOW()),(NOW()),(NOW()),(NOW());


mysql> (SELECT ID1 FROM TEST4)
UNION
(SELECT ID2 FROM TEST5);

+-----+
| ID1 |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
4 rows in set (0.00 sec)


mysql> (SELECT ID1 FROM TEST4)
UNION ALL
(SELECT ID2 FROM TEST5);

+-----+
| ID1 |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
8 rows in set (0.00 sec)


---------複数テーブルを結合して表示   内部結合　JOIN と INNER JOINはキーが一致しているレコードを取り出します--------------
SELECT * FROM TEST4
JOIN TEST5
ON TEST4.ID1 = TEST5.ID2;

+-----+---------------------+-----+---------------------+
| ID1 | TIMES1              | ID2 | TIMES2              |
+-----+---------------------+-----+---------------------+
|   1 | 2012-01-04 17:47:05 |   1 | 2012-01-04 17:47:38 |
|   2 | 2012-01-04 17:47:05 |   2 | 2012-01-04 17:47:38 |
|   3 | 2012-01-04 17:47:05 |   3 | 2012-01-04 17:47:38 |
|   4 | 2012-01-04 17:47:05 |   4 | 2012-01-04 17:47:38 |
+-----+---------------------+-----+---------------------+

SELECT * FROM TEST4
JOIN TEST5 ON ID2 > 1
WHERE TEST4.ID1 = TEST5.ID2;
+-----+---------------------+-----+---------------------+
| ID1 | TIMES1              | ID2 | TIMES2              |
+-----+---------------------+-----+---------------------+
|   2 | 2012-01-04 17:47:05 |   2 | 2012-01-04 17:47:38 |
|   3 | 2012-01-04 17:47:05 |   3 | 2012-01-04 17:47:38 |
|   4 | 2012-01-04 17:47:05 |   4 | 2012-01-04 17:47:38 |
+-----+---------------------+-----+---------------------+
3 rows in set (0.00 sec)