\u8457\u8005\u306f\u3001Write for DOnations\u30d7\u30ed\u30b0\u30e9\u30e0\u306e\u4e00\u74b0\u3068\u3057\u3066\u3001\u5bc4\u4ed8\u3092\u53d7\u3051\u308b\u305f\u3081\u306bDiversity in Tech Fund\u3092\u9078\u629e\u3057\u307e\u3057\u305f\u3002<\/p>\n
\u30ea\u30ec\u30fc\u30b7\u30e7\u30ca\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3068Structured Query Language\uff08SQL\uff09\u3092\u4f7f\u7528\u3057\u3066\u4f5c\u696d\u3059\u308b\u969b\u3001\u30c7\u30fc\u30bf\u306e\u307b\u3068\u3093\u3069\u306e\u64cd\u4f5c\u306f\u660e\u793a\u7684\u306b\u5b9f\u884c\u3055\u308c\u305f\u30af\u30a8\u30ea\uff08SELECT\u3001INSERT\u3001\u307e\u305f\u306fUPDATE\u306a\u3069\uff09\u306e\u7d50\u679c\u3068\u3057\u3066\u5b9f\u884c\u3055\u308c\u307e\u3059\u3002<\/p>\n
\u3057\u304b\u3057\u306a\u304c\u3089\u3001SQL\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3067\u306f\u3001\u30c8\u30ea\u30ac\u3092\u901a\u3058\u3066\u7279\u5b9a\u306e\u30a4\u30d9\u30f3\u30c8\u304c\u767a\u751f\u3057\u305f\u969b\u306b\u4e8b\u524d\u5b9a\u7fa9\u3055\u308c\u305f\u30a2\u30af\u30b7\u30e7\u30f3\u3092\u81ea\u52d5\u7684\u306b\u5b9f\u884c\u3059\u308b\u3088\u3046\u6307\u793a\u3059\u308b\u3053\u3068\u3082\u3067\u304d\u307e\u3059\u3002\u305f\u3068\u3048\u3070\u3001\u30c8\u30ea\u30ac\u3092\u4f7f\u7528\u3057\u3066\u3001\u524a\u9664\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306e\u5168\u3066\u306e\u64cd\u4f5c\u30ed\u30b0\u3084\u3001\u884c\u304c\u66f4\u65b0\u307e\u305f\u306f\u8ffd\u52a0\u3055\u308c\u308b\u305f\u3073\u306b\u96c6\u8a08\u7d71\u8a08\u306e\u8981\u7d04\u3092\u81ea\u52d5\u7684\u306b\u66f4\u65b0\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n
\u3053\u306e\u30c1\u30e5\u30fc\u30c8\u30ea\u30a2\u30eb\u3067\u306f\u3001\u884c\u304c\u633f\u5165\u3055\u308c\u305f\u3001\u66f4\u65b0\u3055\u308c\u305f\u3001\u307e\u305f\u306f\u524a\u9664\u3055\u308c\u305f\u5834\u5408\u306b\u81ea\u52d5\u7684\u306b\u30a2\u30af\u30b7\u30e7\u30f3\u3092\u5b9f\u884c\u3059\u308b\u305f\u3081\u306b\u3001\u7570\u306a\u308bSQL\u30c8\u30ea\u30ac\u30fc\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n
\u3053\u306e\u30ac\u30a4\u30c9\u306b\u5f93\u3046\u305f\u3081\u306b\u306f\u3001SQL\u30d9\u30fc\u30b9\u306e\u95a2\u4fc2\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u7ba1\u7406\u30b7\u30b9\u30c6\u30e0\uff08RDBMS\uff09\u304c\u52d5\u4f5c\u3059\u308b\u30b3\u30f3\u30d4\u30e5\u30fc\u30bf\u304c\u5fc5\u8981\u3067\u3059\u3002\u3053\u306e\u30ac\u30a4\u30c9\u306e\u624b\u9806\u3068\u4f8b\u306f\u3001\u4ee5\u4e0b\u306e\u74b0\u5883\u3067\u691c\u8a3c\u3055\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n
Note<\/p>\n
\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u3001\u3044\u304f\u3064\u304b\u306e\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u3092\u30ed\u30fc\u30c9\u3057\u305f\u30c6\u30fc\u30d6\u30eb\u3092\u6301\u3064\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3082\u5fc5\u8981\u3067\u3059\u3002\u95a2\u6570\u306e\u4f7f\u7528\u65b9\u6cd5\u3092\u7df4\u7fd2\u3059\u308b\u305f\u3081\u306b\u306f\u3001MySQL\u30b5\u30fc\u30d0\u30fc\u306b\u63a5\u7d9a\u3057\u3001\u3053\u306e\u30ac\u30a4\u30c9\u5168\u4f53\u3067\u4f7f\u7528\u3055\u308c\u308b\u30c6\u30b9\u30c8\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3092\u4f5c\u6210\u3059\u308b\u65b9\u6cd5\u306b\u3064\u3044\u3066\u8a73\u7d30\u306a\u624b\u9806\u304c\u3001\u4ee5\u4e0b\u306e\u300cMySQL\u3078\u306e\u63a5\u7d9a\u3068\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u8a2d\u5b9a\u300d\u30bb\u30af\u30b7\u30e7\u30f3\u3067\u8aac\u660e\u3055\u308c\u3066\u3044\u307e\u3059\u306e\u3067\u3001\u305d\u3061\u3089\u3092\u3054\u53c2\u7167\u304f\u3060\u3055\u3044\u3002<\/p>\n
\u3053\u306e\u30bb\u30af\u30b7\u30e7\u30f3\u3067\u306f\u3001MySQL\u30b5\u30fc\u30d0\u30fc\u306b\u63a5\u7d9a\u3057\u3001\u4ee5\u4e0b\u306e\u30bb\u30af\u30b7\u30e7\u30f3\u3067\u306e\u4f8b\u306b\u5f93\u3063\u3066\u30b5\u30f3\u30d7\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n
\u3053\u306e\u6848\u5185\u3067\u306f\u3001\u67b6\u7a7a\u306e\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u30a2\u30a4\u30c6\u30e0\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002\u73fe\u5728\u6240\u6709\u3057\u3066\u3044\u308b\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u30a2\u30a4\u30c6\u30e0\u306e\u8a73\u7d30\u3092\u4fdd\u5b58\u3057\u3001\u7dcf\u984d\u3092\u3059\u3050\u306b\u5229\u7528\u53ef\u80fd\u306b\u3057\u3001\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u30a2\u30a4\u30c6\u30e0\u3092\u524a\u9664\u3057\u3066\u3082\u5e38\u306b\u75d5\u8de1\u304c\u6b8b\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002<\/p>\n
\u3082\u3057\u3042\u306a\u305f\u306eSQL\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30b7\u30b9\u30c6\u30e0\u304c\u30ea\u30e2\u30fc\u30c8\u30b5\u30fc\u30d0\u30fc\u4e0a\u3067\u7a3c\u50cd\u3057\u3066\u3044\u308b\u5834\u5408\u3001\u30ed\u30fc\u30ab\u30eb\u30de\u30b7\u30f3\u304b\u3089\u30b5\u30fc\u30d0\u30fc\u306bSSH\u63a5\u7d9a\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- ssh<\/span> sammy<\/mark>@your_server_ip<\/mark><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u305d\u306e\u5f8c\u3001MySQL\u30b5\u30fc\u30d0\u30fc\u30d7\u30ed\u30f3\u30d7\u30c8\u3092\u958b\u304d\u3001MySQL\u306e\u30e6\u30fc\u30b6\u30fc\u30a2\u30ab\u30a6\u30f3\u30c8\u540d\u3067\u3042\u308b\u300csammy\u300d\u3092\u7f6e\u304d\u63db\u3048\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- mysql -u<\/span> sammy<\/mark> -p<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u300c\u30b3\u30ec\u30af\u30bf\u30d6\u30eb\u300d\u3068\u3044\u3046\u540d\u524d\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3092\u4f5c\u6210\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- CREATE DATABASE collectibles;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u3082\u3057\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u304c\u6b63\u5e38\u306b\u4f5c\u6210\u3055\u308c\u305f\u5834\u5408\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u51fa\u529b\u304c\u8868\u793a\u3055\u308c\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\nQuery OK, 1 row affected (0.01 sec)<\/p>\n
<\/code><\/pre>\n\u30b3\u30ec\u30af\u30bf\u30d6\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3092\u9078\u629e\u3059\u308b\u306b\u306f\u3001\u4ee5\u4e0b\u306eUSE\u6587\u3092\u5b9f\u884c\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- USE collectibles;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u4ee5\u4e0b\u306e\u51fa\u529b\u3092\u53d7\u3051\u53d6\u308a\u307e\u3059: (Shita no shutsuryoku o uketorimasu)<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\nDatabase changed<\/p>\n
<\/code><\/pre>\n\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3092\u9078\u629e\u3057\u305f\u5f8c\u3001\u305d\u306e\u4e2d\u306b\u30b5\u30f3\u30d7\u30eb\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u30c6\u30fc\u30d6\u30eb\u300c\u53ce\u96c6\u54c1\u300d\u306f\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u5185\u306e\u53ce\u96c6\u54c1\u306b\u95a2\u3059\u308b\u7c21\u7565\u306a\u30c7\u30fc\u30bf\u3092\u4fdd\u6301\u3057\u307e\u3059\u3002\u6b21\u306e\u30ab\u30e9\u30e0\u304c\u542b\u307e\u308c\u307e\u3059\uff1a<\/p>\n
\n- name: This column holds the name for each collectible, expressed using the varchar data type with a maximum of 50 characters.<\/li>\n
- value: This column stores the collectible\u2019s market value using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.<\/li>\n<\/ul>\n
\u4ee5\u4e0b\u306e\u30b3\u30de\u30f3\u30c9\u3092\u4f7f\u7528\u3057\u3066\u3001\u30b5\u30f3\u30d7\u30eb\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- CREATE TABLE collectibles (<\/span><\/li>\n
- name varchar(<\/span>50<\/span>)<\/span>,<\/li>\n
- value decimal(<\/span>5<\/span>, 2<\/span>)<\/span><\/li>\n
- )<\/span>;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u3082\u3057\u4ee5\u4e0b\u306e\u51fa\u529b\u304c\u8868\u793a\u3055\u308c\u308b\u306a\u3089\u3070\u3001\u30c6\u30fc\u30d6\u30eb\u306f\u4f5c\u6210\u3055\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n
<\/code><\/pre>\n\u6b21\u306e\u30c6\u30fc\u30d6\u30eb\u306fcollectibles_stats\u3068\u547c\u3070\u308c\u3001\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u5185\u306e\u3059\u3079\u3066\u306e\u30b3\u30ec\u30af\u30bf\u30d6\u30eb\u306e\u7d2f\u7a4d\u4fa1\u5024\u3092\u8ffd\u8de1\u3059\u308b\u305f\u3081\u306b\u4f7f\u7528\u3055\u308c\u307e\u3059\u3002\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u306f\u3001\u4ee5\u4e0b\u306e\u30ab\u30e9\u30e0\u3092\u6301\u3064\u5358\u4e00\u306e\u30c7\u30fc\u30bf\u884c\u304c\u4fdd\u5b58\u3055\u308c\u307e\u3059\u3002<\/p>\n
\n- count: This column holds the number of owned collectibles, expressed using the int data type.<\/li>\n
- value: This column stores the accumulated worth of all collectibles using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.<\/li>\n<\/ul>\n
\u4ee5\u4e0b\u306e\u30b3\u30de\u30f3\u30c9\u3092\u4f7f\u7528\u3057\u3066\u30b5\u30f3\u30d7\u30eb\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- CREATE TABLE collectibles_stats (<\/span><\/li>\n
- count int,<\/li>\n
- value decimal(<\/span>5<\/span>, 2<\/span>)<\/span><\/li>\n
- )<\/span>;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u3082\u3057\u4ee5\u4e0b\u306e\u51fa\u529b\u304c\u8868\u793a\u3055\u308c\u305f\u3089\u3001\u30c6\u30fc\u30d6\u30eb\u306f\u4f5c\u6210\u3055\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n
<\/code><\/pre>\n\u7b2c\u4e09\u30c6\u30fc\u30d6\u30eb\u306fcollectibles_archive\u3068\u547c\u3070\u308c\u3001\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u304b\u3089\u524a\u9664\u3055\u308c\u305f\u3059\u3079\u3066\u306e\u30b3\u30ec\u30af\u30c6\u30a3\u30d6\u30eb\u306e\u8ffd\u8de1\u3092\u884c\u3044\u3001\u305d\u308c\u3089\u304c\u6d88\u3048\u306a\u3044\u3088\u3046\u306b\u3057\u307e\u3059\u3002\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u306fcollectibles\u30c6\u30fc\u30d6\u30eb\u3068\u4f3c\u305f\u30c7\u30fc\u30bf\u3092\u4fdd\u6301\u3057\u3001\u524a\u9664\u3055\u308c\u305f\u65e5\u4ed8\u304c\u8ffd\u52a0\u3055\u308c\u307e\u3059\u3002\u4ee5\u4e0b\u306e\u5217\u304c\u4f7f\u7528\u3055\u308c\u307e\u3059\u3002<\/p>\n
\n- name: This column holds the name for each removed collectible, expressed using the varchar data type with a maximum of 50 characters.<\/li>\n
- value: This column stores the collectible\u2019s market value at the moment of deletion using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.<\/li>\n
- removed_on: This column stores the date and time of deletion for each archived collectible using the timestamp data type with the default value of NOW(), meaning the current date whenever a new row is inserted into this table.<\/li>\n<\/ul>\n
\u4ee5\u4e0b\u306e\u30b3\u30de\u30f3\u30c9\u3092\u4f7f\u7528\u3057\u3066\u3001\u30b5\u30f3\u30d7\u30eb\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- CREATE TABLE collectibles_archive (<\/span><\/li>\n
- name varchar(<\/span>50<\/span>)<\/span>,<\/li>\n
- value decimal(<\/span>5<\/span>, 2<\/span>)<\/span>,<\/li>\n
- removed_on timestamp DEFAULT CURRENT_TIMESTAMP<\/li>\n
- )<\/span>;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u3082\u3057\u4ee5\u4e0b\u306e\u51fa\u529b\u304c\u8868\u793a\u3055\u308c\u308b\u306a\u3089\u3001\u30c6\u30fc\u30d6\u30eb\u306f\u4f5c\u6210\u3055\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n
<\/code><\/pre>\n\u305d\u306e\u5f8c\u3001\u6b21\u306eINSERT INTO\u64cd\u4f5c\u3092\u5b9f\u884c\u3057\u3066\u3001\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u306e\u521d\u671f\u72b6\u614b\u3067\u3042\u308b\u7a7a\u306ecollectibles_stats\u30c6\u30fc\u30d6\u30eb\u306b\u30c7\u30fc\u30bf\u3092\u8ffd\u52a0\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- INSERT INTO collectibles_stats SELECT COUNT(<\/span>name)<\/span>, SUM(<\/span>value)<\/span> FROM collectibles;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\nINSERT INTO\u64cd\u4f5c\u306f\u3001\u96c6\u3081\u3089\u308c\u305f\u53ce\u96c6\u54c1\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5358\u4e00\u306e\u884c\u3092\u8ffd\u52a0\u3057\u307e\u3059\u3002\u3053\u306e\u884c\u306e\u5024\u306f\u3001\u53ce\u96c6\u54c1\u30c6\u30fc\u30d6\u30eb\u306e\u3059\u3079\u3066\u306e\u884c\u3092\u30ab\u30a6\u30f3\u30c8\u3059\u308b\u305f\u3081\u306e\u96c6\u8a08\u95a2\u6570\u3068\u3001value\u5217\u3068SUM\u95a2\u6570\u3092\u4f7f\u7528\u3057\u3066\u53ce\u96c6\u54c1\u306e\u4fa1\u5024\u306e\u5408\u8a08\u3092\u8a08\u7b97\u3057\u305f\u7d50\u679c\u3067\u3059\u3002\u6b21\u306e\u51fa\u529b\u306f\u3001\u884c\u304c\u8ffd\u52a0\u3055\u308c\u305f\u3053\u3068\u3092\u793a\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\nQuery OK, 1 row affected (0.002 sec) Records: 1 Duplicates: 0 Warnings: 0<\/p>\n
<\/code><\/pre>\n\u30c6\u30fc\u30d6\u30eb\u4e0a\u3067SELECT\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3092\u5b9f\u884c\u3059\u308b\u3053\u3068\u3067\u3001\u305d\u308c\u3092\u78ba\u8a8d\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\n\n- SELECT * FROM collectibles_stats;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u307e\u3060\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u30a2\u30a4\u30c6\u30e0\u304c\u306a\u3044\u305f\u3081\u3001\u30a2\u30a4\u30c6\u30e0\u306e\u521d\u671f\u6570\u306f0\u3067\u3001\u7d2f\u7a4d\u3057\u305f\u5024\u306fNULL\u3068\u8868\u793a\u3055\u308c\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\n+——-+——-+ | count | value | +——-+——-+ | 0 | NULL | +——-+——-+ 1 row in set (0.000 sec)<\/p>\n
<\/code><\/pre>\n\u305d\u308c\u3067\u306f\u3001\u3042\u306a\u305f\u306f\u3053\u306e\u30ac\u30a4\u30c9\u306e\u6b8b\u308a\u3092\u8ffd\u3044\u3001MySQL\u3067\u30c8\u30ea\u30ac\u30fc\u3092\u4f7f\u7528\u3057\u59cb\u3081\u308b\u6e96\u5099\u304c\u6574\u3044\u307e\u3057\u305f\u3002<\/p>\n
\u30c8\u30ea\u30ac\u30fc\u306e\u7406\u89e3<\/h2>\n
\u30c8\u30ea\u30ac\u30fc\u306f\u3001\u7279\u5b9a\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5b9a\u7fa9\u3055\u308c\u305f\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3067\u3042\u308a\u3001\u305d\u306e\u30c6\u30fc\u30d6\u30eb\u5185\u3067\u7279\u5b9a\u306e\u30a4\u30d9\u30f3\u30c8\u304c\u767a\u751f\u3059\u308b\u305f\u3073\u306b\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u3088\u3063\u3066\u81ea\u52d5\u7684\u306b\u5b9f\u884c\u3055\u308c\u307e\u3059\u3002\u30c8\u30ea\u30ac\u30fc\u306f\u3001\u7279\u5b9a\u306e\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u304c\u30c6\u30fc\u30d6\u30eb\u3067\u5b9f\u884c\u3055\u308c\u308b\u305f\u3073\u306b\u3001\u4e00\u8cab\u3057\u3066\u7279\u5b9a\u306e\u30a2\u30af\u30b7\u30e7\u30f3\u304c\u5b9f\u884c\u3055\u308c\u308b\u3053\u3068\u3092\u4fdd\u8a3c\u3059\u308b\u305f\u3081\u306b\u4f7f\u7528\u3055\u308c\u307e\u3059\u3002\u305d\u308c\u3092\u624b\u52d5\u3067\u5b9f\u884c\u3059\u308b\u5fc5\u8981\u306f\u306a\u304f\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u30e6\u30fc\u30b6\u30fc\u304c\u899a\u3048\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n
\u30c6\u30fc\u30d6\u30eb\u306b\u95a2\u9023\u4ed8\u3051\u3089\u308c\u305f\u3059\u3079\u3066\u306e\u30c8\u30ea\u30ac\u30fc\u306f\u3001\u30e6\u30fc\u30b6\u30fc\u5b9a\u7fa9\u306e\u540d\u524d\u3068\u30c8\u30ea\u30ac\u30fc\u3092\u5b9f\u884c\u3059\u308b\u30bf\u30a4\u30df\u30f3\u30b0\u3092\u6307\u793a\u3059\u308b\u4e00\u7d44\u306e\u6761\u4ef6\u3067\u8b58\u5225\u3055\u308c\u307e\u3059\u3002\u3053\u308c\u3089\u306f\u3001\u4e8c\u3064\u306e\u5225\u3005\u306e\u30af\u30e9\u30b9\u306b\u30b0\u30eb\u30fc\u30d7\u5316\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n
\n- Database event: The trigger can be executed when INSERT, UPDATE, or DELETE statements are run on a table.<\/li>\n
- Event time: Additionally, triggers can be executed BEFORE or AFTER the statement in question.<\/li>\n<\/ul>\n
2\u3064\u306e\u6761\u4ef6\u30b0\u30eb\u30fc\u30d7\u3092\u7d44\u307f\u5408\u308f\u305b\u308b\u3053\u3068\u3067\u3001\u5408\u8a086\u3064\u306e\u7570\u306a\u308b\u30c8\u30ea\u30ac\u30fc\u306e\u53ef\u80fd\u6027\u304c\u767a\u751f\u3057\u307e\u3059\u3002\u3053\u308c\u3089\u306e\u30c8\u30ea\u30ac\u30fc\u306f\u3001\u5171\u6709\u306e\u6761\u4ef6\u304c\u6e80\u305f\u3055\u308c\u308b\u305f\u3073\u306b\u81ea\u52d5\u7684\u306b\u5b9f\u884c\u3055\u308c\u307e\u3059\u3002\u6761\u4ef6\u3092\u6e80\u305f\u3059\u524d\u306b\u767a\u751f\u3059\u308b\u30c8\u30ea\u30ac\u30fc\u306b\u306f\u3001BEFORE INSERT\u3001BEFORE UPDATE\u3001BEFORE DELETE\u304c\u3042\u308a\u307e\u3059\u3002\u3053\u308c\u3089\u306f\u3001\u30c7\u30fc\u30bf\u304c\u30c6\u30fc\u30d6\u30eb\u306b\u633f\u5165\u307e\u305f\u306f\u66f4\u65b0\u3055\u308c\u308b\u524d\u306b\u30c7\u30fc\u30bf\u3092\u64cd\u4f5c\u304a\u3088\u3073\u691c\u8a3c\u3059\u308b\u305f\u3081\u306b\u4f7f\u7528\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u307e\u305f\u3001\u524a\u9664\u3055\u308c\u305f\u884c\u306e\u8a73\u7d30\u3092\u76e3\u67fb\u307e\u305f\u306f\u30a2\u30fc\u30ab\u30a4\u30d6\u306e\u76ee\u7684\u3067\u4fdd\u5b58\u3059\u308b\u3053\u3068\u3082\u3067\u304d\u307e\u3059\u3002<\/p>\n
\u6761\u4ef6\u306b\u5408\u81f4\u3059\u308b\u6587\u306e\u5b9f\u884c\u5f8c\u306b\u767a\u751f\u3059\u308b\u30c8\u30ea\u30ac\u30fc\u306f\u3001AFTER INSERT\u3001AFTER UPDATE\u3001\u304a\u3088\u3073AFTER DELETE\u3067\u3059\u3002\u3053\u308c\u3089\u306f\u3001\u6587\u306e\u5b9f\u884c\u5f8c\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u6700\u7d42\u72b6\u614b\u306b\u57fa\u3065\u3044\u3066\u5225\u306e\u30c6\u30fc\u30d6\u30eb\u306e\u96c6\u8a08\u5024\u3092\u66f4\u65b0\u3059\u308b\u305f\u3081\u306b\u4f7f\u7528\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n
\u30c8\u30ea\u30ac\u30fc\u5185\u304b\u3089\u30c7\u30fc\u30bf\u306e\u5024\u306b\u30a2\u30af\u30bb\u30b9\u3059\u308b\u3053\u3068\u306b\u3088\u308a\u3001\u5165\u529b\u30c7\u30fc\u30bf\u306e\u691c\u8a3c\u3084\u64cd\u4f5c\u3001\u524a\u9664\u3055\u308c\u305f\u884c\u306e\u30a2\u30fc\u30ab\u30a4\u30d6\u306a\u3069\u306e\u30a2\u30af\u30b7\u30e7\u30f3\u3092\u5b9f\u884c\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002INSERT\u30c8\u30ea\u30ac\u30fc\u3067\u306f\u3001\u65b0\u3057\u304f\u633f\u5165\u3055\u308c\u305f\u30c7\u30fc\u30bf\u306e\u307f\u3092\u4f7f\u7528\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002UPDATE\u30c8\u30ea\u30ac\u30fc\u3067\u306f\u3001\u5143\u306e\u30c7\u30fc\u30bf\u3068\u66f4\u65b0\u5f8c\u306e\u30c7\u30fc\u30bf\u306e\u4e21\u65b9\u306b\u30a2\u30af\u30bb\u30b9\u3067\u304d\u307e\u3059\u3002\u6700\u5f8c\u306b\u3001DELETE\u30c8\u30ea\u30ac\u30fc\u3067\u306f\u524a\u9664\u3055\u308c\u305f\u884c\u306e\u5143\u306e\u30c7\u30fc\u30bf\u306e\u307f\u304c\u5229\u7528\u53ef\u80fd\u3067\u3059\uff08\u65b0\u3057\u3044\u30c7\u30fc\u30bf\u304c\u53c2\u7167\u3067\u304d\u306a\u3044\u305f\u3081\uff09\u3002<\/p>\n
\u30c8\u30ea\u30ac\u672c\u4f53\u5185\u3067\u4f7f\u7528\u3059\u308b\u30c7\u30fc\u30bf\u306f\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u5185\u306e\u73fe\u5728\u306e\u30c7\u30fc\u30bf\u306b\u5bfe\u3057\u3066\u306fOLD\u30ec\u30b3\u30fc\u30c9\u3001\u30af\u30a8\u30ea\u304c\u4fdd\u5b58\u3059\u308b\u30c7\u30fc\u30bf\u306b\u5bfe\u3057\u3066\u306fNEW\u30ec\u30b3\u30fc\u30c9\u306e\u4e0b\u306b\u516c\u958b\u3055\u308c\u307e\u3059\u3002\u500b\u3005\u306e\u5217\u306b\u306f\u3001OLD.column_name\u304a\u3088\u3073NEW.column_name\u3068\u3044\u3046\u69cb\u6587\u3092\u4f7f\u3063\u3066\u53c2\u7167\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n
\u6b21\u306e\u4f8b\u306f\u3001\u65b0\u3057\u3044\u30c8\u30ea\u30ac\u30fc\u3092\u4f5c\u6210\u3059\u308b\u305f\u3081\u306b\u4f7f\u7528\u3055\u308c\u308bSQL\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306e\u4e00\u822c\u7684\u306a\u69cb\u6587\u3092\u793a\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\n\n- CREATE TRIGGER trigger_name<\/mark> trigger_condition<\/mark><\/li>\n
- ON table_name<\/mark><\/li>\n
- FOR EACH ROW<\/li>\n
- trigger_actions<\/mark>;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u6587\u6cd5\u3092\u5c0f\u3055\u306a\u90e8\u5206\u306b\u5206\u89e3\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n
\n- CREATE TRIGGER is the name of the SQL statement used to create a new trigger in the database.<\/li>\n
- trigger_name is the user-defined name of the trigger, used to describe its role, similar to how table names and column names are used to describe their meaning.<\/li>\n
- ON table_name tells the database that the trigger should monitor events happening on the table_name table.<\/li>\n
- trigger_condition is one of the six possible choices defining when the trigger should run, for example, BEFORE INSERT.<\/li>\n
- FOR EACH ROW tells the database that the trigger should be run for each row affected by the triggering event. Some databases support additional patterns of execution other than FOR EACH ROW; however, in the case of MySQL, running the statements from the trigger body for each row affected by the statement that caused the trigger to execute is the only option.<\/li>\n
- trigger_actions is the trigger\u2019s body and defines what happens when the trigger executes. It\u2019s typically a single valid SQL statement. It is possible to include multiple statements in the trigger body to perform complex data operations using the BEGIN and END keywords to enclose the list of statements in a block. This is, however, out of the scope of this tutorial. Check out the official documentation for triggers to learn more about the syntax used to define triggers.<\/li>\n<\/ul>\n
\u4ee5\u4e0b\u306e\u30bb\u30af\u30b7\u30e7\u30f3\u3067\u306f\u3001INSERT\u304a\u3088\u3073UPDATE\u64cd\u4f5c\u524d\u306b\u30c7\u30fc\u30bf\u3092\u64cd\u4f5c\u3059\u308b\u30c8\u30ea\u30ac\u30fc\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n
BEFORE INSERT \u3068 BEFORE UPDATE \u30c8\u30ea\u30ac\u30fc\u3092\u4f7f\u3063\u3066\u30c7\u30fc\u30bf\u3092\u64cd\u4f5c\u3059\u308b\u3002<\/h2>\n
\u3053\u306e\u30bb\u30af\u30b7\u30e7\u30f3\u3067\u306f\u3001INSERT\u304a\u3088\u3073UPDATE\u6587\u304c\u5b9f\u884c\u3055\u308c\u308b\u524d\u306b\u3001\u30c8\u30ea\u30ac\u30fc\u3092\u4f7f\u7528\u3057\u3066\u30c7\u30fc\u30bf\u3092\u64cd\u4f5c\u3057\u307e\u3059\u3002<\/p>\n
\u3053\u306e\u4f8b\u3067\u306f\u3001\u30c8\u30ea\u30ac\u30fc\u3092\u4f7f\u7528\u3057\u3066\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u5185\u306e\u3059\u3079\u3066\u306e\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u304c\u4e00\u8cab\u6027\u306e\u3042\u308b\u5927\u6587\u5b57\u306e\u540d\u79f0\u3092\u4f7f\u7528\u3059\u308b\u3088\u3046\u306b\u3057\u307e\u3059\u3002\u30c8\u30ea\u30ac\u30fc\u3092\u4f7f\u7528\u3057\u306a\u3044\u5834\u5408\u3001\u5404INSERT\u304a\u3088\u3073UPDATE\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3067\u5927\u6587\u5b57\u306e\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u540d\u3092\u4f7f\u7528\u3059\u308b\u3053\u3068\u3092\u899a\u3048\u306a\u3051\u308c\u3070\u306a\u308a\u307e\u305b\u3093\u3002\u5fd8\u308c\u308b\u3068\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306f\u30c7\u30fc\u30bf\u3092\u305d\u306e\u307e\u307e\u4fdd\u5b58\u3057\u3001\u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u306b\u53ef\u80fd\u306a\u30df\u30b9\u3092\u5f15\u304d\u8d77\u3053\u3057\u307e\u3059\u3002<\/p>\n
\u6700\u521d\u306b\u300112.50\u30c9\u30eb\u306e\u4fa1\u5024\u304c\u3042\u308b\u5b87\u5b99\u8239\u30e2\u30c7\u30eb\u3068\u3044\u3046\u4f8b\u306e\u30b3\u30ec\u30af\u30bf\u30fc\u30a2\u30a4\u30c6\u30e0\u3092\u633f\u5165\u3057\u307e\u3059\u3002\u554f\u984c\u3092\u793a\u3059\u305f\u3081\u306b\u3001\u30a2\u30a4\u30c6\u30e0\u540d\u306f\u5c0f\u6587\u5b57\u3067\u66f8\u304b\u308c\u307e\u3059\u3002\u4ee5\u4e0b\u306e\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u3092\u5b9f\u884c\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- INSERT INTO collectibles VALUES (<\/span>‘spaceship model<\/mark>‘<\/span>, 12.50<\/span>)<\/span>;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u4ee5\u4e0b\u306e\u30e1\u30c3\u30bb\u30fc\u30b8\u306f\u3001\u30a2\u30a4\u30c6\u30e0\u304c\u8ffd\u52a0\u3055\u308c\u305f\u3053\u3068\u3092\u78ba\u8a8d\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\nQuery OK, 1 row affected (0.009 sec)<\/p>\n
<\/code><\/pre>\nSELECT\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u3057\u3066\u3001\u884c\u304c\u633f\u5165\u3055\u308c\u305f\u3053\u3068\u3092\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\n\n- SELECT * FROM collectibles;<\/span><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u4ee5\u4e0b\u306e\u51fa\u529b\u306f\u753b\u9762\u306b\u8868\u793a\u3055\u308c\u307e\u3059\u3002<\/p>\n
<\/code><\/pre>\nOutput<\/div>\n<\/code><\/pre>\n+—————–+——-+ | name | value | +—————–+——-+ | spaceship model<\/mark> | 12.50 | +—————–+——-+ 1 row in set (0.000 sec)<\/p>\n<\/code><\/pre>\n\u305d\u306e\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u30a2\u30a4\u30c6\u30e0\u306f\u3001\u305d\u306e\u307e\u307e\u306e\u5f62\u3067\u4fdd\u5b58\u3055\u308c\u3001\u540d\u524d\u306f\u3059\u3079\u3066\u5c0f\u6587\u5b57\u3067\u30b9\u30da\u30eb\u3055\u308c\u307e\u3057\u305f\u3002<\/p>\n
\u3059\u3079\u3066\u306e\u5c06\u6765\u306e\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u30a2\u30a4\u30c6\u30e0\u304c\u5e38\u306b\u5927\u6587\u5b57\u3067\u66f8\u304b\u308c\u308b\u3053\u3068\u3092\u78ba\u8a8d\u3059\u308b\u305f\u3081\u306b\u3001BEFORE INSERT\u30c8\u30ea\u30ac\u30fc\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002\u30c8\u30ea\u30ac\u30fc\u306f\u3001\u30c8\u30ea\u30ac\u30ea\u30f3\u30b0\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u304c\u5b9f\u884c\u3055\u308c\u308b\u524d\u306b\u30c7\u30fc\u30bf\u3092\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u6e21\u3059\u524d\u306b\u64cd\u4f5c\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u305f\u3081\u3001\u30c8\u30ea\u30ac\u30ea\u30f3\u30b0\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306e\u524d\u306b\u5b9f\u884c\u3055\u308c\u308b\u30c8\u30ea\u30ac\u30fc\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n
\u4ee5\u4e0b\u306e\u6587\u3092\u5b9f\u884c\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n
<\/code><\/pre>\n\n- CREATE TRIGGER uppercase_before_insert BEFORE INSERT<\/mark><\/li>\n
- ON collectibles<\/li>\n
- FOR EACH ROW<\/li>\n
- SET NEW.name =<\/span> UPPER(<\/span>NEW.name)<\/span>;<\/span><\/mark><\/li>\n<\/ol>\n
<\/code><\/pre>\n <\/p>\n
<\/code><\/pre>\n\u3053\u306e\u30b3\u30de\u30f3\u30c9\u306f\u3001collectibles\u3068\u3044\u3046\u540d\u524d\u306e\u30c6\u30fc\u30d6\u30eb\u4e0a\u306e\u3059\u3079\u3066\u306eINSERT\u6587\u306e\u524d\u306b\u5b9f\u884c\u3055\u308c\u308btrigger\u3067\u3042\u308buppercase_before_insert\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n
\u30c8\u30ea\u30ac\u30fc\u5185\u306e\u6587\u300cSET NEW.name = UPPER(NEW.name)\u300d\u306f\u3001\u633f\u5165\u3055\u308c\u308b\u884c\u3054\u3068\u306b\u5b9f\u884c\u3055\u308c\u307e\u3059\u3002SET SQL\u30b3\u30de\u30f3\u30c9\u306f\u53f3\u8fba\u306e\u5024\u3092\u5de6\u8fba\u306b\u5272\u308a\u5f53\u3066\u307e\u3059\u3002\u3053\u306e\u5834\u5408\u3001NEW.name\u306f\u633f\u5165\u6587\u306b\u4fdd\u5b58\u3055\u308c\u308b\u540d\u524d\u5217\u306e\u5024\u3092\u8868\u3057\u307e\u3059\u3002\u53ce\u96c6\u53ef\u80fd\u306a\u540d\u524d\u306b\u5bfe\u3057\u3066UPPER\u95a2\u6570\u3092\u9069\u7528\u3057\u3001\u30ab\u30e9\u30e0\u306e\u5024\u306b\u623b\u3057\u3066\u3044\u308b\u3053\u3068\u3067\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u4fdd\u5b58\u3055\u308c\u308b\u5024\u306e\u5927\u6587\u5b57\u30fb\u5c0f\u6587\u5b57\u3092\u5909\u63db\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
\n