{"id":1540,"date":"2022-11-10T21:13:21","date_gmt":"2022-11-10T13:13:21","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1540"},"modified":"2022-11-27T12:40:58","modified_gmt":"2022-11-27T04:40:58","slug":"%e7%ac%ac11%e7%ab%a0%e5%a2%9e%e5%88%a0%e6%94%b9%e8%af%be%e5%90%8e%e7%bb%83%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1540","title":{"rendered":"\u7b2c11\u7ae0\u589e\u5220\u6539\u8bfe\u540e\u7ec3\u4e60"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c11\u7ae0_\u6570\u636e\u5904\u7406\u4e4b\u589e\u5220\u6539\u8bfe\u540e\u7ec3\u4e60\r\n\r\n# \u7ec3\u4e601\r\n#1. \u521b\u5efa\u6570\u636e\u5e93dbtest11\r\nCREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';\r\n\r\n#2. \u8fd0\u884c\u4ee5\u4e0b\u811a\u672c\u521b\u5efa\u8868my_employees\r\nUSE dbtest11;\r\n\r\nCREATE TABLE my_employees(\r\n    id INT(10),\r\n    first_name VARCHAR(10),\r\n    last_name VARCHAR(10),\r\n    userid VARCHAR(10),\r\n    salary DOUBLE(10,2)\r\n);\r\n\r\nCREATE TABLE users(\r\n    id INT,\r\n    userid VARCHAR(10),\r\n    department_id INT\r\n);\r\n\r\n#3. \u663e\u793a\u8868my_employees\u7684\u7ed3\u6784\r\nDESC my_employees;\r\n\r\nDESC USERS;\r\n\r\n#4. \u5411my_employees\u8868\u4e2d\u63d2\u5165\u4e0b\u5217\u6570\u636e\r\n# ID  FIRST_NAME  LAST_NAME   USERID      SALARY\r\n# 1   patel       Ralph       Rpatel      895\r\n# 2   Dancs       Betty       Bdancs      860\r\n# 3   Biri        Ben         Bbiri       1100\r\n# 4   Newman      Chad        Cnewman     750\r\n# 5   Ropeburn    Audrey      Aropebur    1550\r\nINSERT INTO my_employees\r\nVALUES(1,'patel','Ralph','Rpatel',895);\r\n\r\nINSERT INTO my_employees\r\nVALUES(2,'Dancs','Betty','Bdancs',860),\r\n       (3,'Biri','Ben','Bbiri',1100),\r\n       (4,'Newman','Chad','Cnewman',750),\r\n       (5,'Ropeburn','Audrey','Aropebur',1550);\r\n\r\nSELECT * FROM my_employees;\r\n\r\nDELETE FROM my_employees;\r\n\r\n#\u65b9\u5f0f2\uff1a\r\nINSERT INTO my_employees\r\nSELECT 1,'patel','Ralph','Rpatel',895 UNION ALL\r\nSELECT 2,'Dancs','Betty','Bdancs',860 UNION ALL\r\nSELECT 3,'Biri','Ben','Bbiri',1100 UNION ALL\r\nSELECT 4,'Newman','Chad','Cnewman',750 UNION ALL\r\nSELECT 5,'Ropeburn','Audrey','Aropebur',1550;\r\n\r\n#5. \u5411users\u8868\u4e2d\u63d2\u5165\u6570\u636e\r\n# 1 Rpatel    10\r\n# 2 Bdancs    10\r\n# 3 Bbiri     20\r\n# 4 Cnewman   30\r\n# 5 Aropebur  40\r\nINSERT INTO users\r\nVALUES (1,'Rpatel',10),\r\n       (2,'Bdancs',10),\r\n       (3,'Bbiri',20),\r\n       (4,'Cnewman',30),\r\n       (5,'Aropebur',40);\r\n\r\nSELECT * FROM users;\r\n\r\n#6. \u5c063\u53f7\u5458\u5de5\u7684last_name\u4fee\u6539\u4e3a\u201cdrelxer\u201d\r\nUPDATE my_employees\r\nSET last_name = 'drelxer'\r\nWHERE id = 3;\r\n\r\n#7. \u5c06\u6240\u6709\u5de5\u8d44\u5c11\u4e8e900\u7684\u5458\u5de5\u7684\u5de5\u8d44\u4fee\u6539\u4e3a1000\r\nUPDATE my_employees\r\nSET salary = 1000\r\nWHERE salary &lt; 900;\r\n\r\n#8. \u5c06userid\u4e3aBbiri\u7684user\u8868\u548cmy_employees\u8868\u7684\u8bb0\u5f55\u5168\u90e8\u5220\u9664\r\n#\u65b9\u5f0f1\uff1a\r\nDELETE FROM my_employees\r\nWHERE userid = 'Bbiri';\r\n\r\nDELETE FROM users\r\nWHERE userid = 'Bbiri';\r\n\r\n# \u65b9\u5f0f2\uff1a\r\nDELETE m,u\r\nFROM my_employees m\r\nJOIN users u\r\nON m.userid = u.userid\r\nWHERE m.userid = 'Bbiri';\r\n\r\nSELECT * FROM my_employees;\r\nSELECT * FROM users;\r\n\r\n#9. \u5220\u9664my_employees\u3001users\u8868\u6240\u6709\u6570\u636e\r\nDELETE FROM my_employees;\r\nDELETE FROM users;\r\n\r\n#10. \u68c0\u67e5\u6240\u4f5c\u7684\u4fee\u6b63\r\nSELECT *\r\nFROM my_employees;\r\nSELECT *\r\nFROM users;\r\n\r\n#11. \u6e05\u7a7a\u8868my_employees\r\nTRUNCATE TABLE my_employees;\r\n\r\n##################################################################\r\n\r\n# \u7ec3\u4e602\r\n# 1. \u4f7f\u7528\u73b0\u6709\u6570\u636e\u5e93dbtest11\r\nUSE dbtest11;\r\n\r\n# 2. \u521b\u5efa\u8868\u683cpet\r\n# \u5b57\u6bb5\u540d      \u5b57\u6bb5\u8bf4\u660e        \u6570\u636e\u7c7b\u578b\r\n# name        \u5ba0\u7269\u540d\u79f0        VARCHAR(20)\r\n# owner       \u5ba0\u7269\u4e3b\u4eba        VARCHAR(20)\r\n# species     \u79cd\u7c7b            VARCHAR(20)\r\n# sex         \u6027\u522b            CHAR(1)\r\n# birth       \u51fa\u751f\u65e5\u671f        YEAR\r\n# death       \u6b7b\u4ea1\u65e5\u671f        YEAR\r\nCREATE TABLE pet(\r\n    name VARCHAR(20),\r\n    owner VARCHAR(20),\r\n    species VARCHAR(20),\r\n    sex CHAR(1),\r\n    birth YEAR,\r\n    death YEAR\r\n);\r\n\r\nDESC pet;\r\n\r\n# 3. \u6dfb\u52a0\u8bb0\u5f55\r\n# name    owner   species     sex     birth   death\r\n# Fluffy  harold  Cat         f       2003    2010\r\n# Claws   gwen    Cat         m       2004\r\n# Buffy           Dog         f       2009\r\n# Fang    benny   Dog         m       2000\r\n# bowser  diane   Dog         m       2003    2009\r\n# Chirpy          Bird        f       2008\r\nINSERT INTO pet\r\nVALUES('Fluffy','harold','Cat','f','2003','2010'),\r\n       ('Claws','gwen','Cat','m','2004',NULL),\r\n       ('Buffy',NULL,'Dog','f','2009',NULL),\r\n       ('Fang','benny','Dog','m','2000',NULL),\r\n       ('bowser','diane','Dog','m','2003','2009'),\r\n       ('Chirpy',NULL,'Bird','f','2008',NULL);\r\n\r\nSELECT *\r\nFROM pet;\r\n\r\n# 4. \u6dfb\u52a0\u5b57\u6bb5:\u4e3b\u4eba\u7684\u751f\u65e5owner_birth DATE\u7c7b\u578b\u3002\r\nALTER TABLE pet\r\nADD owner_birth DATE;\r\n\r\n# 5. \u5c06\u540d\u79f0\u4e3aClaws\u7684\u732b\u7684\u4e3b\u4eba\u6539\u4e3akevin\r\nUPDATE pet\r\nSET owner = 'kevin'\r\nWHERE name = 'Claws' AND species = 'Cat';\r\n\r\n# 6. \u5c06\u6ca1\u6709\u6b7b\u7684\u72d7\u7684\u4e3b\u4eba\u6539\u4e3aduck\r\nUPDATE pet\r\nSET owner = 'duck'\r\nWHERE death IS NULL AND species = 'Dog';\r\n\r\n# 7. \u67e5\u8be2\u6ca1\u6709\u4e3b\u4eba\u7684\u5ba0\u7269\u7684\u540d\u5b57\uff1b\r\nSELECT name\r\nFROM pet\r\nWHERE owner IS NULL;\r\n\r\n# 8. \u67e5\u8be2\u5df2\u7ecf\u6b7b\u4e86\u7684cat\u7684\u59d3\u540d\uff0c\u4e3b\u4eba\uff0c\u4ee5\u53ca\u53bb\u4e16\u65f6\u95f4\uff1b\r\nSELECT name,owner,death\r\nFROM pet\r\nWHERE death IS NOT NULL AND species = 'Cat';\r\n\r\n# 9. \u5220\u9664\u5df2\u7ecf\u6b7b\u4ea1\u7684\u72d7\r\nDELETE FROM pet\r\nWHERE death IS NOT NULL\r\nAND species = 'Dog';\r\n\r\n# 10. \u67e5\u8be2\u6240\u6709\u5ba0\u7269\u4fe1\u606f\r\nSELECT *\r\nFROM pet;\r\n\r\n############################################################\r\n\r\n# \u7ec3\u4e603\r\n# 1. \u4f7f\u7528\u5df2\u6709\u7684\u6570\u636e\u5e93dbtest11\r\nUSE dbtest11;\r\n\r\n# 2. \u521b\u5efa\u8868employee\uff0c\u5e76\u6dfb\u52a0\u8bb0\u5f55\r\n# id      name      sex   tel             addr       salary\r\n# 10001   \u5f20\u4e00\u4e00     \u7537   13456789000     \u5c71\u4e1c\u9752\u5c9b    1001.58\r\n# 10002   \u5218\u5c0f\u7ea2     \u5973   13454319000     \u6cb3\u5317\u4fdd\u5b9a    1201.21\r\n# 10003   \u674e\u56db       \u7537   0751-1234567    \u5e7f\u4e1c\u4f5b\u5c71    1004.11\r\n# 10004   \u5218\u5c0f\u5f3a     \u7537   0755-5555555    \u5e7f\u4e1c\u6df1\u5733    1501.23\r\n# 10005   \u738b\u8273       \u5973   020-1232133     \u5e7f\u4e1c\u5e7f\u5dde    1405.16\r\nCREATE TABLE IF NOT EXISTS employee(\r\n    id INT,\r\n    name VARCHAR(25),\r\n    sex CHAR(1),\r\n    tel VARCHAR(25),\r\n    addr VARCHAR(25),\r\n    salary DOUBLE(10,2)\r\n);\r\n\r\nINSERT INTO employee(id, name, sex, tel, addr, salary)\r\nVALUES(10001,'\u5f20\u4e00\u4e00','\u7537','13456789000','\u5c71\u4e1c\u9752\u5c9b',1001.58),\r\n       (10002,'\u5218\u5c0f\u7ea2','\u5973','13454319000','\u6cb3\u5317\u4fdd\u5b9a',1201.21),\r\n       (10003,'\u674e\u56db','\u7537','0751-1234567','\u5e7f\u4e1c\u4f5b\u5c71',1004.11),\r\n       (10004,'\u5218\u5c0f\u5f3a','\u7537','0755-5555555','\u5e7f\u4e1c\u6df1\u5733',1501.23),\r\n       (10005,'\u738b\u8273','\u5973','020-1232133','\u5e7f\u4e1c\u5e7f\u5dde',1405.16);\r\n\r\nSELECT * FROM employee;\r\n\r\n# 3. \u67e5\u8be2\u51fa\u85aa\u8d44\u57281200~1300\u4e4b\u95f4\u7684\u5458\u5de5\u4fe1\u606f\u3002\r\nSELECT *\r\nFROM employee\r\nWHERE salary BETWEEN 1200 AND 1300;\r\n\r\n# 4. \u67e5\u8be2\u51fa\u59d3\u201c\u5218\u201d\u7684\u5458\u5de5\u7684\u5de5\u53f7\uff0c\u59d3\u540d\uff0c\u5bb6\u5ead\u4f4f\u5740\u3002\r\nSELECT id,name,addr\r\nFROM employee\r\nWHERE name LIKE '\u5218%';\r\n\r\n# 5. \u5c06\u201c\u674e\u56db\u201d\u7684\u5bb6\u5ead\u4f4f\u5740\u6539\u4e3a\u201c\u5e7f\u4e1c\u97f6\u5173\u201d\r\nUPDATE employee\r\nSET addr = '\u5e7f\u4e1c\u97f6\u5173'\r\nWHERE name = '\u674e\u56db';\r\n\r\n# 6. \u67e5\u8be2\u51fa\u540d\u5b57\u4e2d\u5e26\u201c\u5c0f\u201d\u7684\u5458\u5de5\r\nSELECT *\r\nFROM employee\r\nWHERE name LIKE '%\u5c0f%';<\/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-1540","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\/1540"}],"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=1540"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1540\/revisions"}],"predecessor-version":[{"id":1541,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1540\/revisions\/1541"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1540"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1540"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1540"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}