{"id":1557,"date":"2022-11-12T22:58:12","date_gmt":"2022-11-12T14:58:12","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1557"},"modified":"2022-11-27T12:40:58","modified_gmt":"2022-11-27T04:40:58","slug":"%e7%ac%ac13%e7%ab%a0%e7%ba%a6%e6%9d%9f%e8%af%be%e5%90%8e%e7%bb%83%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1557","title":{"rendered":"\u7b2c13\u7ae0\u7ea6\u675f\u8bfe\u540e\u7ec3\u4e60"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c13\u7ae0_\u7ea6\u675f\u8bfe\u540e\u7ec3\u4e60\r\n\r\n# \u7ec3\u4e601\r\n# \u5df2\u7ecf\u5b58\u5728\u6570\u636e\u5e93test04_emp\uff0c\u4e24\u5f20\u8868emp2\u548cdept2\r\nCREATE DATABASE test04_emp;\r\n\r\nuse test04_emp;\r\n\r\nCREATE TABLE emp2(\r\n    id INT,\r\n    emp_name VARCHAR(15)\r\n);\r\n\r\nCREATE TABLE dept2(\r\n    id INT,\r\n    dept_name VARCHAR(15)\r\n);\r\n\r\n#1.\u5411\u8868emp2\u7684id\u5217\u4e2d\u6dfb\u52a0PRIMARY KEY\u7ea6\u675f\r\nALTER TABLE emp2\r\nADD CONSTRAINT pk_emp2_id PRIMARY KEY(id);\r\n\r\n#2. \u5411\u8868dept2\u7684id\u5217\u4e2d\u6dfb\u52a0PRIMARY KEY\u7ea6\u675f\r\nALTER TABLE dept2\r\nADD PRIMARY KEY(id);\r\n\r\n#3. \u5411\u8868emp2\u4e2d\u6dfb\u52a0\u5217dept_id\uff0c\u5e76\u5728\u5176\u4e2d\u5b9a\u4e49FOREIGN KEY\u7ea6\u675f\uff0c\u4e0e\u4e4b\u76f8\u5173\u8054\u7684\u5217\u662fdept2\u8868\u4e2d\u7684id\u5217\u3002\r\nALTER TABLE emp2\r\nADD dept_id INT;\r\n\r\nALTER TABLE emp2\r\nADD CONSTRAINT fk_emp2_dept2_id FOREIGN KEY(dept_id) REFERENCES dept2(id);\r\n\r\n# \u7ec3\u4e602\r\n# \u627f\u63a5\u300a\u7b2c11\u7ae0_\u6570\u636e\u5904\u7406\u4e4b\u589e\u5220\u6539\u300b\u7684\u7efc\u5408\u6848\u4f8b\u3002\r\n# 1\u3001\u521b\u5efa\u6570\u636e\u5e93test01_library\r\nUSE test01_library;\r\n\r\nDESC books;\r\n\r\n# \u7b2c1\u30012\u95ee\u4e0d\u7528\u505a\r\nCREATE DATABASE IF NOT EXISTS test01_library;\r\n\r\n# 2\u3001\u521b\u5efa\u8868 books\uff0c\u8868\u7ed3\u6784\u5982\u4e0b\uff1a\r\n\r\n# \u5b57\u6bb5\u540d      \u5b57\u6bb5\u8bf4\u660e      \u6570\u636e\u7c7b\u578b\r\n# id          \u4e66\u7f16\u53f7        INT\r\n# name        \u4e66\u540d          VARCHAR(50)\r\n# authors     \u4f5c\u8005          VARCHAR(100)\r\n# price       \u4ef7\u683c          FLOAT\r\n# pubdate     \u51fa\u7248\u65e5\u671f      YEAR\r\n# note        \u8bf4\u660e          VARCHAR(100)\r\n# num         \u5e93\u5b58          INT\r\n\r\nCREATE TABLE books(\r\n    id INT,\r\n    name VARCHAR(50),\r\n    authors VARCHAR(100),\r\n    price FLOAT,\r\n    pubdate YEAR,\r\n    note VARCHAR(100),\r\n    num INT\r\n);\r\n\r\n# 3\u3001\u4f7f\u7528ALTER\u8bed\u53e5\u7ed9books\u6309\u5982\u4e0b\u8981\u6c42\u589e\u52a0\u76f8\u5e94\u7684\u7ea6\u675f\r\n\r\n# \u5b57\u6bb5\u540d      \u5b57\u6bb5\u8bf4\u660e      \u6570\u636e\u7c7b\u578b         \u4e3b\u952e     \u5916\u952e     \u975e\u7a7a     \u552f\u4e00     \u81ea\u589e\r\n# id          \u4e66\u7f16\u53f7        INT(11)         \u662f       \u5426       \u662f       \u662f       \u662f\r\n# name        \u4e66\u540d          VARCHAR(50)     \u5426       \u5426       \u662f       \u5426       \u5426\r\n# authors     \u4f5c\u8005          VARCHAR(100)    \u5426       \u5426       \u662f       \u5426       \u5426\r\n# price       \u4ef7\u683c          FLOAT           \u5426       \u5426       \u662f       \u5426       \u5426\r\n# pubdate     \u51fa\u7248\u65e5\u671f      YEAR            \u5426       \u5426       \u662f       \u5426       \u5426\r\n# note        \u8bf4\u660e          VARCHAR(100)    \u5426       \u5426       \u5426       \u5426       \u5426\r\n# num         \u5e93\u5b58          INT(11)         \u5426       \u5426       \u662f       \u5426       \u5426\r\n#\u65b9\u5f0f1\uff1a\r\nALTER TABLE books\r\nADD PRIMARY KEY(id);\r\n\r\nALTER TABLE books\r\nMODIFY id INT AUTO_INCREMENT;\r\n#\u65b9\u5f0f2\uff1a\r\nALTER TABLE books\r\nMODIFY id INT(11) PRIMARY KEY AUTO_INCREMENT;\r\n\r\n#\u9488\u5bf9\u975eid\u5b57\u6bb5\u7684\u64cd\u4f5c\r\n#\u65b9\u5f0f1\uff1a\r\nALTER TABLE books\r\nMODIFY name VARCHAR(50) NOT NULL;\r\n\r\nALTER TABLE books\r\nMODIFY authors VARCHAR(100) NOT NULL;\r\n\r\nALTER TABLE books\r\nMODIFY price FLOAT NOT NULL;\r\n\r\nALTER TABLE books\r\nMODIFY pubdate YEAR NOT NULL;\r\n\r\nALTER TABLE books\r\nMODIFY note VARCHAR(100);\r\n\r\nALTER TABLE books\r\nMODIFY num INT(11) NOT NULL;\r\n#\u65b9\u5f0f2\uff1a\r\nALTER TABLE books\r\n    MODIFY name VARCHAR(50) NOT NULL,\r\n    MODIFY authors VARCHAR(100) NOT NULL,\r\n    MODIFY price FLOAT NOT NULL,\r\n    MODIFY pubdate YEAR NOT NULL,\r\n    MODIFY note VARCHAR(100),\r\n    MODIFY num INT(11) NOT NULL;\r\n\r\nDESC books;\r\n\r\n# \u7ec3\u4e603\r\n\r\n#1. \u521b\u5efa\u6570\u636e\u5e93test04_company\r\nCREATE DATABASE IF NOT EXISTS test04_company CHARACTER SET 'utf8';\r\n\r\nUSE test04_company;\r\n\r\n#2. \u6309\u7167\u4e0b\u8868\u7ed9\u51fa\u7684\u8868\u7ed3\u6784\u5728test04_company\u6570\u636e\u5e93\u4e2d\u521b\u5efa\u4e24\u4e2a\u6570\u636e\u8868offices\u548cemployees\r\n\r\n# offices\u8868\uff1a\u7565\r\n\r\nCREATE TABLE IF NOT EXISTS offices(\r\n    officeCode INT(10) PRIMARY KEY,\r\n    city VARCHAR(50) NOT NULL,\r\n    address VARCHAR(50),\r\n    country VARCHAR(50) NOT NULL,\r\n    postalCode VARCHAR(15),\r\n    CONSTRAINT uk_off_poscode UNIQUE(postalCode)\r\n);\r\n\r\nDESC offices;\r\n\r\n# employees\u8868\uff1a\u7565\r\n\r\nCREATE TABLE employees(\r\n    employeesNumber INT PRIMARY KEY AUTO_INCREMENT,\r\n    lastName VARCHAR(50) NOT NULL,\r\n    firstName VARCHAR(50) NOT NULL,\r\n    mobile VARCHAR(25) UNIQUE,\r\n    officeCode INT(10) NOT NULL,\r\n    jobTitle VARCHAR(50) NOT NULL,\r\n    birth DATETIME NOT NULL,\r\n    note VARCHAR(255),\r\n    sex VARCHAR(5),\r\n\r\n    CONSTRAINT fk_emp_offcode FOREIGN KEY(officeCode) REFERENCES offices(officeCode)\r\n);\r\n\r\nDESC employees;\r\n\r\n#3. \u5c06\u8868employees\u7684mobile\u5b57\u6bb5\u4fee\u6539\u5230officeCode\u5b57\u6bb5\u540e\u9762\r\nALTER TABLE employees\r\nMODIFY mobile VARCHAR(25) AFTER officeCode;\r\n\r\n#4. \u5c06\u8868employees\u7684birth\u5b57\u6bb5\u6539\u540d\u4e3aemployee_birth\r\nALTER TABLE employees\r\nCHANGE birth employee_birth DATETIME;\r\n\r\n#5. \u4fee\u6539sex\u5b57\u6bb5\uff0c\u6570\u636e\u7c7b\u578b\u4e3aCHAR(1)\uff0c\u975e\u7a7a\u7ea6\u675f\r\nALTER TABLE employees\r\nMODIFY sex CHAR(1) NOT NULL;\r\n\r\n#6. \u5220\u9664\u5b57\u6bb5note\r\nALTER TABLE employees\r\nDROP COLUMN note;\r\n\r\n#7. \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#8. \u5c06\u8868employees\u540d\u79f0\u4fee\u6539\u4e3aemployees_info\r\nRENAME TABLE employees\r\nTO employees_info;\r\n\r\n# \u9519\u8bef\uff1aTable 'test04_company.employees' doesn't exist\r\nDESC employees;\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-1557","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\/1557"}],"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=1557"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1557\/revisions"}],"predecessor-version":[{"id":1558,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1557\/revisions\/1558"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1557"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1557"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1557"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}