{"id":6189,"date":"2024-07-29T11:04:52","date_gmt":"2024-07-29T02:04:52","guid":{"rendered":"https:\/\/taitan916.info\/blog\/?p=6189"},"modified":"2024-05-18T14:32:31","modified_gmt":"2024-05-18T05:32:31","slug":"post-6189","status":"publish","type":"post","link":"https:\/\/taitan916.info\/blog\/archives\/6189","title":{"rendered":"MySQL\u306eview(\u30d3\u30e5\u30fc)\u3067\u4eee\u60f3\u7684\u306a\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b\u65b9\u6cd5"},"content":{"rendered":"

MySQL\u95a2\u9023\u306e\u30b5\u30a4\u30c8\u3092\u898b\u3066\u3044\u308b\u3068view(\u30d3\u30e5\u30fc)\u304c\u4e91\u3005\u3068\u3044\u3046\u8a18\u4e8b\u3092\u898b\u304b\u3051\u305f\u3002\u8abf\u3079\u305f\u3068\u3053\u308d\u4eee\u60f3\u7684\u306a\u30c6\u30fc\u30d6\u30eb\u3089\u3057\u3044\u3002\u305f\u3060\u3001\u3060\u3044\u3076\u524d\u306b\u4f7f\u3063\u305fTEMPORARY TABLE<\/a>\u3068\u306f\u9055\u3063\u3066\u4e00\u6642\u7684\u306a\u30c6\u30fc\u30d6\u30eb\u3067\u306f\u306a\u304f\u3001\u660e\u793a\u7684\u306b\u524a\u9664\u3057\u306a\u3044\u9650\u308a\u4fdd\u5b58\u3055\u308c\u308b\u30c6\u30fc\u30d6\u30eb\u307f\u305f\u3044\u3002\u4ee5\u4e0b\u306b\u5229\u7528\u65b9\u6cd5\u3092\u30e1\u30e2\u3002<\/p>\n

 <\/p>\n

\u4f7f\u7528\u3059\u308b\u30c6\u30fc\u30d6\u30eb\u60c5\u5831<\/h2>\n

\u30c6\u30fc\u30d6\u30eb\u69cb\u9020<\/h3>\n
mysql> SHOW COLUMNS FROM `test_table`;\r\n+-------+--------------+------+-----+---------+----------------+\r\n| Field | Type         | Null | Key | Default | Extra          |\r\n+-------+--------------+------+-----+---------+----------------+\r\n| id    | int          | NO   | PRI | NULL    | auto_increment |\r\n| name  | varchar(255) | NO   |     | NULL    |                |\r\n| age   | int          | NO   |     | NULL    |                |\r\n| flg   | tinyint      | NO   |     | NULL    |                |\r\n+-------+--------------+------+-----+---------+----------------+<\/pre>\n
\u4fdd\u5b58\u3055\u308c\u3066\u3044\u308b\u30c7\u30fc\u30bf<\/h3>\n
mysql> SELECT * FROM `test_table`;\r\n+----+-----------+-----+-----+\r\n| id | name      | age | flg |\r\n+----+-----------+-----+-----+\r\n|  1 | \u5c71\u7530      |  20 |   1 |\r\n|  2 | \u9234\u6728      |  25 |   0 |\r\n|  3 | \u4f50\u3005\u6728    |  30 |   1 |\r\n|  4 | \u5927\u5730      |  35 |   0 |\r\n|  5 | \u4f50\u85e4      |  40 |   0 |\r\n|  6 | \u9ad8\u6a4b      |  45 |   1 |\r\n|  7 | \u4f0a\u85e4      |  50 |   0 |\r\n+----+-----------+-----+-----+<\/pre>\n
 <\/p>\n
\u5229\u7528\u65b9\u6cd5<\/h2>\n
view\u306e\u4f5c\u6210<\/h3>\n
\u57fa\u672c\u69cb\u6587\u306f\u4ee5\u4e0b\u306e\u901a\u308a\u3002<\/p>\n
CREATE VIEW \u3010view\u540d\u3011 \r\nAS \u3010SELECT\u6587\u3011<\/pre>\n
\u4eca\u56de\u306f\u4ee5\u4e0bSQL\u3067view\u3092\u4f5c\u6210\u3057\u305f\u3002<\/p>\n
CREATE VIEW test_view AS \r\nSELECT * \r\nFROM `test_table`\r\nWHERE `flg` = 1;<\/pre>\n
view\u306e\u78ba\u8a8d<\/h3>\n
\u57fa\u672c\u7684\u306a\u78ba\u8a8d\u65b9\u6cd5<\/h4>\n
\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u666e\u901a\u306eSELECT\u6587\u3067\u78ba\u8a8d\u3067\u304d\u308b\u3002<\/p>\n
SELECT * \r\nFROM test_view;<\/pre>\n
\u4ee5\u4e0b\u304c\u7d50\u679c\u306b\u306a\u308b\u3002<\/p>\n
+----+-----------+-----+-----+\r\n| id | name      | age | flg |\r\n+----+-----------+-----+-----+\r\n|  1 | \u5c71\u7530      |  20 |   1 |\r\n|  3 | \u4f50\u3005\u6728    |  30 |   1 |\r\n|  6 | \u9ad8\u6a4b      |  45 |   1 |\r\n+----+-----------+-----+-----+<\/pre>\n
\u5143\u30c6\u30fc\u30d6\u30eb\u3092\u7de8\u96c6\u3057\u305f\u5834\u5408<\/h4>\n
\u3061\u306a\u307f\u306b\u5143\u30c6\u30fc\u30d6\u30eb(test_table)\u306b\u30c7\u30fc\u30bf\u3092\u767b\u9332\u3057\u305f\u5834\u5408\u3001<\/p>\n
INSERT INTO `test_table` \r\n    (`name`, `age`, `flg`) \r\nVALUES \r\n    (\"\u6e21\u8fba\", 55, 1);<\/pre>\n
\u4ee5\u4e0b\u306e\u3088\u3046\u306bview\u5074\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u3082\u53cd\u6620\u3055\u308c\u308b\u3002<\/p>\n
mysql> SELECT * FROM test_view;\r\n+----+-----------+-----+-----+\r\n| id | name      | age | flg |\r\n+----+-----------+-----+-----+\r\n|  1 | \u5c71\u7530      |  20 |   1 |\r\n|  3 | \u4f50\u3005\u6728    |  30 |   1 |\r\n|  6 | \u9ad8\u6a4b      |  45 |   1 |\r\n|  8 | \u6e21\u8fba      |  55 |   1 |\r\n+----+-----------+-----+-----+<\/pre>\n
WHERE\u53e5\u3067\u691c\u7d22\u3082\u53ef\u80fd<\/h4>\n
\u4ee5\u4e0b\u306e\u3088\u3046\u306bview\u5074\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066WHERE\u53e5\u3067\u691c\u7d22\u3082\u53ef\u80fd\u3002<\/p>\n
SELECT * \r\nFROM test_view\r\nWHERE `age` > 20;\r\n\r\n+----+-----------+-----+-----+\r\n| id | name      | age | flg |\r\n+----+-----------+-----+-----+\r\n|  3 | \u4f50\u3005\u6728    |  30 |   1 |\r\n|  6 | \u9ad8\u6a4b      |  45 |   1 |\r\n|  8 | \u6e21\u8fba      |  55 |   1 |\r\n+----+-----------+-----+-----+<\/pre>\n
view\u306e\u524a\u9664<\/h3>\n
DROP VIEW test_view;<\/pre>\n
 <\/p>\n
\u30ea\u30d5\u30a1\u30ec\u30f3\u30b9<\/h2>\n
https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/create-view.html<\/a><\/p>\n
 <\/p>\n
\u6240\u611f<\/h2>\n
\u4f7f\u3044\u3069\u3053\u308d<\/h3>\n
\u5b9f\u904b\u7528\u3060\u3068\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u30b1\u30fc\u30b9\u3067\u4f7f\u3048\u305d\u3046\u3002<\/p>\n