{"id":1561,"date":"2022-11-12T22:58:56","date_gmt":"2022-11-12T14:58:56","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1561"},"modified":"2022-11-27T12:40:58","modified_gmt":"2022-11-27T04:40:58","slug":"%e7%ac%ac14%e7%ab%a0%e8%a7%86%e5%9b%be%e8%af%be%e5%90%8e%e7%bb%83%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1561","title":{"rendered":"\u7b2c14\u7ae0\u89c6\u56fe\u8bfe\u540e\u7ec3\u4e60"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c14\u7ae0_\u89c6\u56fe(View)\u8bfe\u540e\u7ec3\u4e60\r\n\r\nUSE dbtest14;\r\n# \u7ec3\u4e601\r\n#1. \u4f7f\u7528\u8868employees\u521b\u5efa\u89c6\u56feemployee_vu\uff0c\u5176\u4e2d\u5305\u62ec\u59d3\u540d\uff08LAST_NAME\uff09\uff0c\u5458\u5de5\u53f7\uff08EMPLOYEE_ID\uff09\uff0c\u90e8\u95e8\u53f7(DEPARTMENT_ID)\r\nCREATE OR REPLACE VIEW employee_vu(lname,emp_id,dept_id)\r\nAS\r\n    SELECT last_name,employee_id,department_id\r\nFROM emps;\r\n\r\n#2. \u663e\u793a\u89c6\u56fe\u7684\u7ed3\u6784\r\nDESC employee_vu;\r\n\r\n#3. \u67e5\u8be2\u89c6\u56fe\u4e2d\u7684\u5168\u90e8\u5185\u5bb9\r\nSELECT *\r\nFROM employee_vu;\r\n\r\n#4. \u5c06\u89c6\u56fe\u4e2d\u7684\u6570\u636e\u9650\u5b9a\u5728\u90e8\u95e8\u53f7\u662f80\u7684\u8303\u56f4\u5185\r\nCREATE OR REPLACE VIEW employee_vu(lname,emp_id,dept_id)\r\nAS\r\n    SELECT last_name,employee_id,department_id\r\nFROM emps\r\nWHERE department_id = 80;\r\n\r\n# \u7ec3\u4e602\r\nCREATE TABLE emps\r\nAS\r\nSELECT * FROM atguigudb.employees;\r\n#1. \u521b\u5efa\u89c6\u56feemp_v1,\u8981\u6c42\u67e5\u8be2\u7535\u8bdd\u53f7\u7801\u4ee5\u2018011\u2019\u5f00\u5934\u7684\u5458\u5de5\u59d3\u540d\u548c\u5de5\u8d44\u3001\u90ae\u7bb1\r\nCREATE OR REPLACE VIEW emp_v1\r\nAS\r\n    SELECT last_name,salary,email\r\nFROM emps\r\nWHERE phone_number LIKE '011%';\r\n\r\n#2. \u8981\u6c42\u5c06\u89c6\u56fe emp_v1 \u4fee\u6539\u4e3a\u67e5\u8be2\u7535\u8bdd\u53f7\u7801\u4ee5\u2018011\u2019\u5f00\u5934\u7684\u5e76\u4e14\u90ae\u7bb1\u4e2d\u5305\u542b e \u5b57\u7b26\u7684\u5458\u5de5\u59d3\u540d\u548c\u90ae\u7bb1\u3001\u7535\u8bdd\u53f7\u7801\r\nCREATE OR REPLACE VIEW emp_v1\r\nAS\r\n    SELECT last_name,email,phone_number,salary\r\nFROM emps\r\nWHERE phone_number LIKE '011%'\r\nAND email LIKE '%e%';\r\n\r\nSELECT * FROM emp_v1;\r\n\r\n#3. \u5411 emp_v1 \u63d2\u5165\u4e00\u6761\u8bb0\u5f55\uff0c\u662f\u5426\u53ef\u4ee5\uff1f\r\n\r\nDESC emps;\r\n\r\n#\u5b9e\u6d4b\uff1a\u5931\u8d25\u4e86\r\nINSERT INTO emp_v1\r\nVALUES ('Tom','tom@126.com','01012345');\r\n\r\n#4. \u4fee\u6539emp_v1\u4e2d\u5458\u5de5\u7684\u5de5\u8d44\uff0c\u6bcf\u4eba\u6da8\u85aa1000\r\nSELECT *\r\nFROM emp_v1;\r\n\r\nUPDATE emp_v1\r\nSET salary = salary + 1000;\r\n\r\n#5. \u5220\u9664emp_v1\u4e2d\u59d3\u540d\u4e3aOlsen\u7684\u5458\u5de5\r\nDELETE FROM emp_v1\r\nWHERE last_name = 'Olsen';\r\n\r\n#6. \u521b\u5efa\u89c6\u56feemp_v2\uff0c\u8981\u6c42\u67e5\u8be2\u90e8\u95e8\u7684\u6700\u9ad8\u5de5\u8d44\u9ad8\u4e8e 12000 \u7684\u90e8\u95e8id\u548c\u5176\u6700\u9ad8\u5de5\u8d44\r\nCREATE OR REPLACE VIEW emp_v2(dept_id,max_sal)\r\nAS\r\n    SELECT department_id,MAX(salary)\r\nFROM emps\r\nGROUP BY department_id\r\nHAVING MAX(salary) > 12000;\r\n\r\nSELECT * FROM emp_v2;\r\n\r\n#7. \u5411 emp_v2 \u4e2d\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\uff0c\u662f\u5426\u53ef\u4ee5\uff1f\r\n\r\n# \u4e0d\u53ef\u4ee5\r\n# \u9519\u8bef\uff1aThe target table emp_v2 of the INSERT is not insertable-into\r\nINSERT INTO emp_v2(dept_id,max_sal)\r\nVALUES (4000,20000);\r\n\r\n#8. \u5220\u9664\u521a\u624d\u7684emp_v2 \u548c emp_v1\r\nDROP VIEW IF EXISTS emp_v1,emp_v2;\r\n\r\nSHOW TABLES;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[47],"class_list":["post-1561","post","type-post","status-publish","format-standard","hentry","category-mysql-code","tag-mysql"],"_links":{"self":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1561"}],"collection":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1561"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1561\/revisions"}],"predecessor-version":[{"id":1562,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1561\/revisions\/1562"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}