{"id":1536,"date":"2022-11-10T21:12:39","date_gmt":"2022-11-10T13:12:39","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1536"},"modified":"2022-11-27T12:40:58","modified_gmt":"2022-11-27T04:40:58","slug":"%e7%ac%ac10%e7%ab%a0%e5%88%9b%e5%bb%ba%e7%ae%a1%e7%90%86%e8%a1%a8%e8%af%be%e5%90%8e%e7%bb%83%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1536","title":{"rendered":"\u7b2c10\u7ae0\u521b\u5efa\u7ba1\u7406\u8868\u8bfe\u540e\u7ec3\u4e60"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c10\u7ae0_\u521b\u5efa\u548c\u7ba1\u7406\u8868\u8bfe\u540e\u7ec3\u4e60\r\n\r\n# \u7ec3\u4e601\r\n#1. \u521b\u5efa\u6570\u636e\u5e93test01_office,\u6307\u660e\u5b57\u7b26\u96c6\u4e3autf8\u3002\u5e76\u5728\u6b64\u6570\u636e\u5e93\u4e0b\u6267\u884c\u4e0b\u8ff0\u64cd\u4f5c\r\nCREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';\r\n\r\nUSE test01_office;\r\n\r\n#2. \u521b\u5efa\u8868dept01\r\n\/*\r\n\u5b57\u6bb5        \u7c7b\u578b\r\nid          INT(7)\r\nNAME        VARCHAR(25)\r\n*\/\r\nCREATE TABLE IF NOT EXISTS dept01(\r\n    id INT(7),\r\n    NAME VARCHAR(25)\r\n);\r\n\r\n#3. \u5c06\u8868departments\u4e2d\u7684\u6570\u636e\u63d2\u5165\u65b0\u8868dept02\u4e2d\r\nCREATE TABLE dept02\r\nAS\r\n    SELECT *\r\nFROM atguigudb.departments;\r\n\r\n#4. \u521b\u5efa\u8868emp01\r\n\/*\r\n\u5b57\u6bb5        \u7c7b\u578b\r\nid          INT(7)\r\nfirst_name  VARCHAR (25)\r\nlast_name   VARCHAR(25)\r\ndept_id     INT(7)\r\n*\/\r\nCREATE TABLE emp01(\r\n    id INT(7),\r\n    first_name VARCHAR(25),\r\n    last_name VARCHAR(25),\r\n    dept_id INT(7)\r\n);\r\n\r\n#5. \u5c06\u5217last_name\u7684\u957f\u5ea6\u589e\u52a0\u523050\r\nDESC emp01;\r\n\r\nALTER TABLE emp01\r\nMODIFY last_name VARCHAR(50);\r\n\r\n#6. \u6839\u636e\u8868employees\u521b\u5efaemp02\r\nCREATE TABLE emp02\r\nAS\r\n    SELECT *\r\nFROM atguigudb.employees;\r\n\r\nSHOW TABLES FROM test01_office;\r\n\r\n#7. \u5220\u9664\u8868emp01\r\nDROP TABLE IF EXISTS emp01;\r\n\r\n#8. \u5c06\u8868emp02\u91cd\u547d\u540d\u4e3aemp01\r\n# ALTER TABLE emp02 RENAME TO emp01;\r\nRENAME TABLE emp02\r\nTO emp01;\r\n\r\n#9.\u5728\u8868dept02\u548cemp01\u4e2d\u6dfb\u52a0\u65b0\u5217test_column\uff0c\u5e76\u68c0\u67e5\u6240\u4f5c\u7684\u64cd\u4f5c\r\nALTER TABLE emp01\r\nADD test_column VARCHAR(15);\r\n\r\nALTER TABLE dept02\r\nADD test_column VARCHAR(15);\r\n\r\nDESC dept02;\r\n\r\n#10.\u76f4\u63a5\u5220\u9664\u8868emp01\u4e2d\u7684\u5217 department_id\r\nALTER TABLE emp01\r\nDROP COLUMN department_id;\r\n\r\n# \u7ec3\u4e602\r\n# 1\u3001\u521b\u5efa\u6570\u636e\u5e93 test02_market\r\nCREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';\r\n\r\nUSE test02_market;\r\n\r\nSHOW CREATE DATABASE test02_market;\r\n\r\n# 2\u3001\u521b\u5efa\u6570\u636e\u8868 customers\r\n\r\n# \u5b57\u6bb5\u540d          \u6570\u636e\u7c7b\u578b\r\n# c_num           int\r\n# c_name          varchar(50)\r\n# c_contact       varchar(50)\r\n# c_city          varchar(50)\r\n# c_birth         date\r\nCREATE TABLE IF NOT EXISTS customers(\r\n    c_num INT,\r\n    c_name VARCHAR(50),\r\n    c_contact VARCHAR(50),\r\n    c_city VARCHAR(50),\r\n    c_birth DATE\r\n);\r\n\r\nSHOW TABLES;\r\n\r\n# 3\u3001\u5c06 c_contact \u5b57\u6bb5\u79fb\u52a8\u5230 c_birth \u5b57\u6bb5\u540e\u9762\r\nDESC customers;\r\n\r\nALTER TABLE customers\r\nMODIFY c_contact VARCHAR(50) AFTER c_birth;\r\n\r\n# 4\u3001\u5c06 c_name \u5b57\u6bb5\u6570\u636e\u7c7b\u578b\u6539\u4e3a varchar(70)\r\nALTER TABLE customers\r\nMODIFY c_name VARCHAR(70);\r\n\r\n# 5\u3001\u5c06c_contact\u5b57\u6bb5\u6539\u540d\u4e3ac_phone\r\nALTER TABLE customers\r\nCHANGE c_contact c_phone VARCHAR(50);\r\n\r\n# 6\u3001\u589e\u52a0c_gender\u5b57\u6bb5\u5230c_name\u540e\u9762\uff0c\u6570\u636e\u7c7b\u578b\u4e3achar(1)\r\nALTER TABLE customers\r\nADD c_gender CHAR(1) AFTER c_name;\r\n\r\n# 7\u3001\u5c06\u8868\u540d\u6539\u4e3acustomers_info\r\nRENAME TABLE customers\r\nTO customers_info;\r\n\r\nDESC customers_info;\r\n\r\n# 8\u3001\u5220\u9664\u5b57\u6bb5c_city\r\nALTER TABLE customers_info\r\nDROP COLUMN c_city;\r\n\r\n# \u7ec3\u4e603\r\n# 1\u3001\u521b\u5efa\u6570\u636e\u5e93test03_company\r\nCREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';\r\n\r\nUSE test03_company;\r\n\r\n# 2\u3001\u521b\u5efa\u8868offices\r\n\r\n# \u5b57\u6bb5\u540d              \u6570\u636e\u7c7b\u578b\r\n# officeCode          int\r\n# city                varchar(30)\r\n# address             varchar(50)\r\n# country             varchar(50)\r\n# postalCode          varchar(25)\r\nCREATE TABLE IF NOT EXISTS offices(\r\n    officeCode INT,\r\n    city VARCHAR(30),\r\n    address VARCHAR(50),\r\n    country VARCHAR(50),\r\n    postalCode VARCHAR(25)\r\n);\r\n\r\nDESC offices;\r\n\r\n# 3\u3001\u521b\u5efa\u8868employees\r\n\r\n# \u5b57\u6bb5\u540d              \u6570\u636e\u7c7b\u578b\r\n# empNum              int\r\n# lastName            varchar(50)\r\n# firstName           varchar(50)\r\n# mobile              varchar(25)\r\n# code                int\r\n# jobTitle            varchar(50)\r\n# birth               date\r\n# note                varchar(255)\r\n# sex                 varchar(5)\r\nCREATE TABLE IF NOT EXISTS employees(\r\n    empNum INT,\r\n    lastName VARCHAR(50),\r\n    firstName VARCHAR(50),\r\n    mobile VARCHAR(25),\r\n    `code` INT,\r\n    jobTitle VARCHAR(50),\r\n    birth DATE,\r\n    note VARCHAR(255),\r\n    sex VARCHAR(5)\r\n);\r\n\r\nDESC employees;\r\n\r\n# 4\u3001\u5c06\u8868employees\u7684mobile\u5b57\u6bb5\u4fee\u6539\u5230code\u5b57\u6bb5\u540e\u9762\r\nALTER TABLE employees\r\nMODIFY mobile VARCHAR(25) AFTER `code`;\r\n\r\n# 5\u3001\u5c06\u8868employees\u7684birth\u5b57\u6bb5\u6539\u540d\u4e3abirthday\r\nALTER TABLE employees\r\nCHANGE birth birthday DATE;\r\n\r\n# 6\u3001\u4fee\u6539sex\u5b57\u6bb5\uff0c\u6570\u636e\u7c7b\u578b\u4e3achar(1)\r\nALTER TABLE employees\r\nMODIFY sex CHAR(1);\r\n\r\n# 7\u3001\u5220\u9664\u5b57\u6bb5note\r\nALTER TABLE employees\r\nDROP COLUMN note;\r\n\r\n# 8\u3001\u589e\u52a0\u5b57\u6bb5\u540dfavoriate_activity\uff0c\u6570\u636e\u7c7b\u578b\u4e3avarchar(100)\r\nALTER TABLE employees\r\nADD favoriate_activity VARCHAR(100);\r\n\r\n# 9\u3001\u5c06\u8868employees\u7684\u540d\u79f0\u4fee\u6539\u4e3a employees_info\r\nRENAME TABLE employees\r\nTO employees_info;\r\n\r\nDESC employees_info;<\/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-1536","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\/1536"}],"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=1536"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1536\/revisions"}],"predecessor-version":[{"id":1537,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1536\/revisions\/1537"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1536"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1536"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1536"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}