MYSQL入門 ２

-------------OFFSET--------------

mysql> SELECT * FROM TEST5;
+-----+---------------------+
| ID2 | TIMES2              |
+-----+---------------------+
|   1 | 2012-01-04 17:47:38 |
|   2 | 2012-01-04 17:47:38 |
|   3 | 2012-01-04 17:47:38 |
|   4 | 2012-01-04 17:47:38 |
|   5 | 2012-01-04 18:19:34 |
|   6 | 2012-01-04 18:19:34 |
|   7 | 2012-01-04 18:19:34 |
|   8 | 2012-01-04 18:19:34 |
+-----+---------------------+

mysql> SELECT * FROM TEST5 LIMIT 4 OFFSET 2;
+-----+---------------------+
| ID2 | TIMES2              |
+-----+---------------------+
|   3 | 2012-01-04 17:47:38 |
|   4 | 2012-01-04 17:47:38 |
|   5 | 2012-01-04 18:19:34 |
|   6 | 2012-01-04 18:19:34 |
+-----+---------------------+

---------------テストデータの作成------------------

mysql> CREATE TABLE USER(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(20),Unit INT);

mysql> INSERT INTO USER(NAME,UNIT)VALUES('ASO',1),('KATO',4),('YAMADA',2),('SUZUKI',1),('MIYAMOTO',3),('TANAKA',4);

mysql> SELECT * FROM USER;
+----+----------+------+
| ID | NAME     | Unit |
+----+----------+------+
|  1 | ASO      |    1 |
|  2 | KATO     |    4 |
|  3 | YAMADA   |    2 |
|  4 | SUZUKI   |    1 |
|  5 | MIYAMOTO |    3 |
|  6 | TANAKA   |    4 |
+----+----------+------+

CREATE TABLE UNIT(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(20));

INSERT INTO UNIT(NAME) VALUES('SOUMU'),('EIGYO'),('JIMU'),('GIJYUTSU');

mysql> SELECT * FROM UNIT;
+----+----------+
| ID | NAME     |
+----+----------+
|  1 | SOUMU    |
|  2 | EIGYO    |
|  3 | JIMU     |
|  4 | GIJYUTSU |
+----+----------+


-----グループ分けしカウント-----
mysql> SELECT UNIT.ID,UNIT.NAME ,COUNT(USER.UNIT) AS 'BUSYO_COUNT'
FROM USER
JOIN UNIT ON  USER.UNIT = UNIT.ID
GROUP BY USER.UNIT;

+----+----------+-------------+
| ID | NAME     | BUSYO_COUNT |
+----+----------+-------------+
|  1 | SOUMU    |           2 |
|  2 | EIGYO    |           1 |
|  3 | JIMU     |           1 |
|  4 | GIJYUTSU |           2 |
+----+----------+-------------+


-----グループ分けした後の抽出-----
mysql> SELECT UNIT.ID,UNIT.NAME ,COUNT(USER.UNIT) AS 'BUSYO_COUNT'
    -> FROM USER
    -> JOIN UNIT ON  USER.UNIT = UNIT.ID
    -> GROUP BY USER.UNIT
    -> HAVING BUSYO_COUNT >= 2;
+----+----------+-------------+
| ID | NAME     | BUSYO_COUNT |
+----+----------+-------------+
|  1 | SOUMU    |           2 |
|  4 | GIJYUTSU |           2 |
+----+----------+-------------+

-----------UPDATE-----------
ALTER TABLE USER ADD KOZUKAI INT;

mysql> SELECT * FROM USER;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |    NULL |
|  2 | KATO     |    4 |    NULL |
|  3 | YAMADA   |    2 |    NULL |
|  4 | SUZUKI   |    1 |    NULL |
|  5 | MIYAMOTO |    3 |    NULL |
|  6 | TANAKA   |    4 |    NULL |
+----+----------+------+---------+

UPDATE USER SET KOZUKAI = 10000;

mysql> SELECT * FROM USER;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |   10000 |
|  2 | KATO     |    4 |   10000 |
|  3 | YAMADA   |    2 |   10000 |
|  4 | SUZUKI   |    1 |   10000 |
|  5 | MIYAMOTO |    3 |   10000 |
|  6 | TANAKA   |    4 |   10000 |
+----+----------+------+---------+

--------条件が一致するレコードを修正---------
UPDATE USER SET KOZUKAI = 90000 WHERE UNIT = 4;

mysql>  SELECT * FROM USER;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |   10000 |
|  2 | KATO     |    4 |   90000 |
|  3 | YAMADA   |    2 |   10000 |
|  4 | SUZUKI   |    1 |   10000 |
|  5 | MIYAMOTO |    3 |   10000 |
|  6 | TANAKA   |    4 |   90000 |
+----+----------+------+---------+

UPDATE USER SET KOZUKAI = 50000 WHERE UNIT = 3;

mysql> SELECT * FROM USER;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |   10000 |
|  2 | KATO     |    4 |   90000 |
|  3 | YAMADA   |    2 |   10000 |
|  4 | SUZUKI   |    1 |   10000 |
|  5 | MIYAMOTO |    3 |   50000 |
|  6 | TANAKA   |    4 |   90000 |
+----+----------+------+---------+

SELECT * FROM USER ORDER BY KOZUKAI;
+----+----------+------+---------+
| ID | NAME     | Unit | KOZUKAI |
+----+----------+------+---------+
|  1 | ASO      |    1 |   10000 |
|  3 | YAMADA   |    2 |   10000 |
|  4 | SUZUKI   |    1 |   10000 |
|  5 | MIYAMOTO |    3 |   50000 |
|  2 | KATO     |    4 |   90000 |
|  6 | TANAKA   |    4 |   90000 |
+----+----------+------+---------+

-------------CASEを使ったUPDATE文----------------
ALTER TABLE USER ADD BIKOU VARCHAR(100);
UPDATE USER
SET BIKOU =
CASE
 WHEN KOZUKAI <= 10000 THEN 'SUKUNAI'
 WHEN (10000 < KOZUKAI AND KOZUKAI < 51000) THEN 'FUTU'
 WHEN 51000 < KOZUKAI THEN 'OUI'
END;

mysql> SELECT * FROM USER ORDER BY KOZUKAI;
+----+----------+------+---------+---------+
| ID | NAME     | Unit | KOZUKAI | BIKOU   |
+----+----------+------+---------+---------+
|  1 | ASO      |    1 |   10000 | SUKUNAI |
|  3 | YAMADA   |    2 |   10000 | SUKUNAI |
|  4 | SUZUKI   |    1 |   10000 | SUKUNAI |
|  5 | MIYAMOTO |    3 |   50000 | FUTU    |
|  2 | KATO     |    4 |   90000 | OUI     |
|  6 | TANAKA   |    4 |   90000 | OUI     |
+----+----------+------+---------+---------+