{"id":239,"date":"2024-06-18T18:16:07","date_gmt":"2024-06-18T10:16:07","guid":{"rendered":"http:\/\/zhaoyanqi.cn\/?p=239"},"modified":"2024-06-18T18:16:09","modified_gmt":"2024-06-18T10:16:09","slug":"%e5%ae%9e%e9%aa%8c%ef%bc%9asql%e7%bb%bc%e5%90%88%e7%bb%83%e4%b9%a02","status":"publish","type":"post","link":"https:\/\/zhaoyanqi.cn\/?p=239","title":{"rendered":"\u5b9e\u9a8c\uff1aSQL\u7efc\u5408\u7ec3\u4e602"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"xep60\">\u5b9e\u9a8c\u4e00\uff1a\u901a\u8fc7\u5b58\u50a8\u8fc7\u7a0b\u521b\u5efa300w\u6761\u6570\u636e<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"shtYe\">\u521b\u5efa\u8868<\/h3>\n\n\n\n<p id=\"u2e0169c8\">\u5047\u8bbe\u8868\u7684\u540d\u79f0\u4e3a<code>test_table<\/code>\uff0c\u4e14\u8868\u7ed3\u6784\u5982\u4e0b\uff1a<\/p>\n\n\n\n<pre id=\"0ec66cfa\" class=\"wp-block-code\"><code>CREATE TABLE test_table (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(50),\n    age INT,\n    email VARCHAR(100)\n);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"dlez6\">\u7f16\u5199\u5b58\u50a8\u8fc7\u7a0b<\/h3>\n\n\n\n<p id=\"u4fde80e4\">\u7f16\u5199\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\uff0c\u8be5\u8fc7\u7a0b\u4f7f\u7528\u5faa\u73af\u63d2\u5165300\u4e07\u6761\u6570\u636e\u3002<\/p>\n\n\n\n<pre id=\"9f6b4d5e\" class=\"wp-block-code\"><code>DELIMITER $$\n\nCREATE PROCEDURE InsertData()\nBEGIN\n    DECLARE i INT DEFAULT 1;\n\n    WHILE i &lt;= 3000000 DO\n        INSERT INTO test_table (name, age, email)\n        VALUES (CONCAT('Name', i), FLOOR(10 + (RAND() * 60)), CONCAT('email', i, '@example.com'));\n        SET i = i + 1;\n    END WHILE;\nEND$$\n\nDELIMITER ;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"9a2586ba\">\u6267\u884c\u5b58\u50a8\u8fc7\u7a0b<\/h3>\n\n\n\n<pre id=\"4bb8480b\" class=\"wp-block-code\"><code>CALL InsertData();<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"ATz6H\">\u6267\u884c\u7ed3\u679c<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"mTgC9\">\u8bb0\u5f55\u6570\u636e\u5199\u5b8c\u6240\u8017\u8d39\u7684\u65f6\u95f4<\/h4>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-37.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"694\" height=\"741\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-37.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-247\"  sizes=\"auto, (max-width: 694px) 100vw, 694px\" \/><\/div><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"bkjsH\">\u8bb0\u5f55\u8868\u5bf9\u5e94\u786c\u76d8\u4e0a\u6587\u4ef6\u7684\u5927\u5c0f<\/h4>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-38.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"771\" height=\"575\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-38.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-248\"  sizes=\"auto, (max-width: 771px) 100vw, 771px\" \/><\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"b6b9ceb5\">\u8bf4\u660e<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>DELIMITER $$<\/strong>: \u5c06\u5206\u9694\u7b26\u66f4\u6539\u4e3a<code>$$<\/code>\u4ee5\u5141\u8bb8\u5728\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u4f7f\u7528<code>\uff1b<\/code>\u3002<\/li>\n\n\n\n<li><strong>CREATE PROCEDURE InsertData()<\/strong>: \u521b\u5efa\u4e00\u4e2a\u540d\u4e3a<code>InsertData<\/code>\u7684\u5b58\u50a8\u8fc7\u7a0b\u3002<\/li>\n\n\n\n<li><strong>DECLARE i INT DEFAULT 1<\/strong>: \u58f0\u660e\u4e00\u4e2a\u540d\u4e3a<code>i<\/code>\u7684\u6574\u578b\u53d8\u91cf\u5e76\u521d\u59cb\u5316\u4e3a1\u3002<\/li>\n\n\n\n<li><strong>WHILE i &lt;= 3000000 DO<\/strong>: \u5faa\u73af\u6267\u884c\u63d2\u5165\u64cd\u4f5c\uff0c\u76f4\u5230<code>i<\/code>\u8fbe\u5230300\u4e07\u3002<\/li>\n\n\n\n<li><strong>INSERT INTO test_table (name, age, email)<\/strong>: \u5411<code>test_table<\/code>\u8868\u4e2d\u63d2\u5165\u4e00\u6761\u8bb0\u5f55\u3002<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>name<\/code>\u5b57\u6bb5\u4f7f\u7528<code>CONCAT('Name', i)<\/code>\u751f\u6210\u4e00\u4e2a\u540d\u79f0\u3002<\/li>\n\n\n\n<li><code>age<\/code>\u5b57\u6bb5\u4f7f\u7528<code>FLOOR(10 + (RAND() * 60))<\/code>\u751f\u6210\u4e00\u4e2a\u4ecb\u4e8e10\u523070\u4e4b\u95f4\u7684\u968f\u673a\u5e74\u9f84\u3002<\/li>\n\n\n\n<li><code>email<\/code>\u5b57\u6bb5\u4f7f\u7528<code>CONCAT('email', i, '@example.com')<\/code>\u751f\u6210\u4e00\u4e2a\u7535\u5b50\u90ae\u4ef6\u5730\u5740\u3002<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\" start=\"6\">\n<li><strong>SET i = i + 1<\/strong>: \u6bcf\u6b21\u5faa\u73af\u540e\u5c06<code>i<\/code>\u7684\u503c\u52a01\u3002<\/li>\n\n\n\n<li><strong>END WHILE<\/strong>: \u7ed3\u675f\u5faa\u73af\u3002<\/li>\n\n\n\n<li><strong>END$$<\/strong>: \u7ed3\u675f\u5b58\u50a8\u8fc7\u7a0b\u3002<\/li>\n\n\n\n<li><strong>DELIMITER ;<\/strong>: \u5c06\u5206\u9694\u7b26\u66f4\u6539\u56de\u9ed8\u8ba4\u7684<code>\uff1b<\/code>\u3002<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"138a6766\">\u6ce8\u610f<\/h3>\n\n\n\n<p id=\"ub80b1fd3\">\u63d2\u5165\u5927\u6279\u91cf\u6570\u636e\u65f6\uff0c\u8bf7\u6ce8\u610f\u4ee5\u4e0b\u51e0\u70b9\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u63d2\u5165\u8fc7\u7a0b\u53ef\u80fd\u4f1a\u82b1\u8d39\u8f83\u957f\u65f6\u95f4\uff0c\u5177\u4f53\u53d6\u51b3\u4e8e\u60a8\u7684\u786c\u4ef6\u914d\u7f6e\u548cMySQL\u914d\u7f6e\u3002<\/li>\n\n\n\n<li>\u5728\u5b9e\u9645\u751f\u4ea7\u73af\u5883\u4e2d\u63d2\u5165\u5927\u6279\u91cf\u6570\u636e\u65f6\uff0c\u5efa\u8bae\u5206\u6279\u63d2\u5165\u4ee5\u51cf\u5c11\u5bf9\u6570\u636e\u5e93\u6027\u80fd\u7684\u5f71\u54cd\u3002<\/li>\n\n\n\n<li>\u786e\u4fdd\u6709\u8db3\u591f\u7684\u78c1\u76d8\u7a7a\u95f4\u548c\u9002\u5f53\u7684\u7d22\u5f15\u4ee5\u5904\u7406\u5927\u6570\u636e\u91cf\u3002<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Lsy47\">\u5b9e\u9a8c\u4e8c\uff1a\u5e26ALL\u548cANY\u5173\u952e\u5b57\u7684\u5b50\u67e5\u8be2<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"abf7a404\">\u4e00\u3001\u5b9e\u9a8c\u4ecb\u7ecd<\/h3>\n\n\n\n<p id=\"ub5f63be5\">\u57fa\u4e8e\u4e00\u4e2a\u540d\u4e3a <code>employee<\/code> \u7684\u8868\u6267\u884c\u5b50\u67e5\u8be2\uff0c\u4ee5\u67e5\u627e\u6ee1\u8db3\u7279\u5b9a\u6761\u4ef6\u7684\u5458\u5de5\u6570\u636e\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"d555d1a7\">\u521b\u5efa <code>employee<\/code> \u8868\u548c\u63d2\u5165\u6570\u636e<\/h4>\n\n\n\n<p id=\"u983769fe\">\u9996\u5148\uff0c\u521b\u5efa <code>employee<\/code> \u8868\uff0c\u5e76\u63d2\u5165\u4e00\u4e9b\u6570\u636e\uff1a<\/p>\n\n\n\n<pre id=\"1dd6b9a9\" class=\"wp-block-code\"><code>CREATE TABLE employee (\n    id INT NOT NULL UNIQUE AUTO_INCREMENT,\n    name VARCHAR(20) NOT NULL,\n    sex ENUM('male', 'female') NOT NULL DEFAULT 'male',\n    age INT(3) UNSIGNED NOT NULL DEFAULT 28,\n    hire_date DATE NOT NULL,\n    post VARCHAR(50),\n    post_comment VARCHAR(100),\n    salary DOUBLE(15,2),\n    office INT,\n    depart_id INT\n);\n\nINSERT INTO employee (name, sex, age, hire_date, post, salary, office, depart_id) VALUES\n('egon', 'male', 18, '2017-03-01', 'teacher', 7300.33, 401, 1),\n('alex', 'male', 78, '2015-03-02', 'teacher', 1000000.31, 401, 1),\n('wupeiqi', 'male', 81, '2013-03-05', 'teacher', 8300, 401, 1),\n('yuanhao', 'male', 73, '2014-07-01', 'teacher', 3500, 401, 1),\n('liwenzhou', 'male', 28, '2012-11-01', 'teacher', 2100, 401, 1),\n('jingliyang', 'female', 18, '2011-02-11', 'teacher', 9000, 401, 1),\n('jinxin', 'male', 18, '1900-03-01', 'teacher', 30000, 401, 1),\n('\u6210\u9f99', 'male', 48, '2010-11-11', 'teacher', 10000, 401, 1),\n('\u6b6a\u6b6a', 'female', 48, '2015-03-11', 'sale', 3000.13, 402, 2),\n('\u4e2b\u4e2b', 'female', 38, '2010-11-01', 'sale', 2000.35, 402, 2),\n('\u4e01\u4e01', 'female', 18, '2011-03-12', 'sale', 1000.37, 402, 2),\n('\u661f\u661f', 'female', 18, '2016-05-13', 'sale', 3000.29, 402, 2),\n('\u683c\u683c', 'female', 28, '2017-01-27', 'sale', 4000.33, 402, 2),\n('\u5f20\u91ce', 'male', 28, '2016-03-11', 'operation', 10000.13, 403, 3),\n('\u7a0b\u54ac\u91d1', 'male', 18, '1997-03-12', 'operation', 20000, 403, 3),\n('\u7a0b\u54ac\u94f6', 'female', 18, '2013-03-11', 'operation', 19000, 403, 3),\n('\u7a0b\u54ac\u94dc', 'male', 18, '2015-04-11', 'operation', 18000, 403, 3),\n('\u7a0b\u54ac\u94c1', 'female', 18, '2014-05-12', 'operation', 17000, 403, 3);<\/code><\/pre>\n\n\n\n<p id=\"ua42c07e6\">\u521b\u5efa <code>department<\/code> \u8868\u5e76\u63d2\u5165\u6570\u636e\uff1a<\/p>\n\n\n\n<pre id=\"caf30db8\" class=\"wp-block-code\"><code>CREATE TABLE department (\n    id INT NOT NULL UNIQUE,\n    name VARCHAR(20) NOT NULL\n);\n\nINSERT INTO department (id, name) VALUES\n(1, '\u6559\u5b66'),\n(2, '\u9500\u552e'),\n(3, '\u8fd0\u8425'),\n(4, '\u6280\u672f');<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"7abe870d\">\u663e\u793a\u8868\u7ed3\u6784<\/h4>\n\n\n\n<pre id=\"82e66ac0\" class=\"wp-block-code\"><code>DESC employee;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"522db141\">\u4e8c\u3001\u4f7f\u7528\u5e26ALL\u548cANY\u5173\u952e\u5b57\u7684\u5b50\u67e5\u8be2\u8fdb\u884c\u67e5\u8be2<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"5e744425\">1. \u67e5\u8be2\u85aa\u8d44\u6bd4\u6240\u6709\u90e8\u95e8\u7684\u5e73\u5747\u85aa\u8d44\u90fd\u9ad8\u7684\u5458\u5de5<\/h4>\n\n\n\n<pre id=\"e113eb79\" class=\"wp-block-code\"><code>SELECT * \nFROM employee \nWHERE salary &gt; ALL(SELECT AVG(salary) FROM employee GROUP BY depart_id);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"a48cd5a9\">2. \u67e5\u8be2\u85aa\u8d44\u6bd4\u6240\u6709\u90e8\u95e8\u7684\u5e73\u5747\u85aa\u8d44\u90fd\u4f4e\u7684\u5458\u5de5<\/h4>\n\n\n\n<pre id=\"38b64062\" class=\"wp-block-code\"><code>SELECT * \nFROM employee \nWHERE salary &lt; ALL(SELECT AVG(salary) FROM employee GROUP BY depart_id);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"356d7483\">3. \u67e5\u8be2\u85aa\u8d44\u4e0d\u57ab\u5e95\u7684\u5458\u5de5: \u85aa\u8d44\u5728\u4efb\u4e00\u90e8\u95e8\u5e73\u5747\u7ebf\u4ee5\u4e0a\u7684\u5458\u5de5<\/h4>\n\n\n\n<pre id=\"d2625086\" class=\"wp-block-code\"><code>SELECT * \nFROM employee \nWHERE salary &gt; ANY (SELECT AVG(salary) FROM employee GROUP BY depart_id);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"0c7f13ae\">4. \u67e5\u8be2\u85aa\u8d44\u4e0d\u5192\u5c16\u7684\u5458\u5de5: \u85aa\u8d44\u5728\u4efb\u4e00\u90e8\u95e8\u5e73\u5747\u7ebf\u4ee5\u4e0b\u7684\u5458\u5de5<\/h4>\n\n\n\n<pre id=\"4e03e001\" class=\"wp-block-code\"><code>SELECT * \nFROM employee \nWHERE salary &lt; ANY (SELECT AVG(salary) FROM employee GROUP BY depart_id);<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"S6MmU\">\u5b9e\u9a8c\u4e09\uff1a\u94fe\u8868\u67e5\u8be2\u548c\u5b50\u67e5\u8be2\u5bf9\u6bd4<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"XAXse\">1. \u5b9e\u9a8c\u4ecb\u7ecd<\/h3>\n\n\n\n<p id=\"u510b6e5f\">\u5728\u672c\u5b9e\u9a8c\u4e2d\uff0c\u6211\u4eec\u5c06<strong>\u4e0a\u4e00\u4e2a\u5b9e\u9a8c\u7684 <\/strong><code><strong>employee<\/strong><\/code><strong> \u7684\u8868<\/strong>\uff0c\u901a\u8fc7\u5b50\u67e5\u8be2\u548c\u94fe\u8868\u8fde\u63a5\u4e24\u79cd\u65b9\u5f0f\uff0c\u67e5\u8be2\u6bcf\u4e2a\u90e8\u95e8\u6700\u65b0\u5165\u804c\u7684\u5458\u5de5\u3002\u5177\u4f53\u793a\u4f8b\u4ee3\u7801\u5c06\u5c55\u793a\u5982\u4f55\u4f7f\u7528\u8fd9\u4e24\u79cd\u65b9\u6cd5\u6765\u5b9e\u73b0\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"nZly7\">2. \u4f7f\u7528\u5b50\u67e5\u8be2\u67e5\u8be2\u6bcf\u4e2a\u90e8\u95e8\u6700\u65b0\u5165\u804c\u7684\u5458\u5de5<\/h3>\n\n\n\n<pre id=\"b438be78\" class=\"wp-block-code\"><code>SELECT t3.name, t3.post, t3.hire_date \nFROM employee AS t3 \nWHERE id IN (\n    SELECT (\n        SELECT id \n        FROM employee AS t2 \n        WHERE t2.depart_id = t1.depart_id \n        ORDER BY hire_date DESC \n        LIMIT 1\n    ) \n    FROM employee AS t1 \n    GROUP BY depart_id\n);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"GHgl5\">3. \u4f7f\u7528\u94fe\u8868\u8fde\u63a5\u67e5\u8be2\u6bcf\u4e2a\u90e8\u95e8\u6700\u65b0\u5165\u804c\u7684\u5458\u5de5<\/h3>\n\n\n\n<pre id=\"5d061d64\" class=\"wp-block-code\"><code>SELECT e.id, e.name, e.depart_id, e.hire_date\nFROM employee e\nINNER JOIN (\n    SELECT depart_id, MAX(hire_date) AS max_hire_date\n    FROM employee\n    GROUP BY depart_id\n) AS subquery\nON e.depart_id = subquery.depart_id AND e.hire_date = subquery.max_hire_date;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"iTcMn\">\u5b9e\u9a8c\u56db\uff1a\u89c6\u56fe\u5b9e\u9a8c<\/h2>\n\n\n\n<p id=\"uf83ca555\">\u89c6\u56fe\u662f\u4e00\u4e2a\u865a\u62df\u8868\uff0c\u5b83\u662f\u4ece\u6570\u636e\u5e93\u4e2d\u4e00\u4e2a\u6216\u591a\u4e2a\u8868\u4e2d\u901a\u8fc7\u67e5\u8be2\u8bed\u53e5\u751f\u6210\u7684\u3002\u89c6\u56fe\u63d0\u4f9b\u4e86\u4e00\u79cd\u7b80\u5316\u590d\u6742\u67e5\u8be2\u548c\u63d0\u9ad8\u6570\u636e\u5b89\u5168\u6027\u7684\u65b9\u6cd5\u3002\u672c\u5b9e\u9a8c\u8fd8\u662f\u57fa\u4e8e<strong>\u4e0a\u4e00\u4e2a\u5b9e\u9a8c\u7684 <\/strong><code><strong>employee<\/strong><\/code><strong> \u7684\u8868\u3002<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"n9SAa\">1. \u521b\u5efa\u89c6\u56fe<\/h3>\n\n\n\n<p id=\"u3bd4483d\">\u4ee5\u4e0b\u793a\u4f8b\u5c55\u793a\u4e86\u5982\u4f55\u521b\u5efa\u4e00\u4e2a\u663e\u793a\u5458\u5de5\u4fe1\u606f\u7684\u89c6\u56fe\uff1a<\/p>\n\n\n\n<pre id=\"fCANF\" class=\"wp-block-code\"><code>CREATE VIEW employee_view AS\nSELECT \n    e.id, \n    e.name, \n    e.sex, \n    e.age, \n    e.hire_date, \n    e.post, \n    e.salary, \n    d.name AS department\nFROM \n    employee e\nJOIN \n    department d ON e.depart_id = d.id;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"pAo4F\">2. \u67e5\u8be2\u89c6\u56fe<\/h3>\n\n\n\n<p id=\"ub080604e\">\u521b\u5efa\u89c6\u56fe\u540e\uff0c\u53ef\u4ee5\u50cf\u67e5\u8be2\u666e\u901a\u8868\u4e00\u6837\u67e5\u8be2\u89c6\u56fe\uff1a<\/p>\n\n\n\n<pre id=\"PQn3H\" class=\"wp-block-code\"><code>SELECT * FROM employee_view;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"H9hwq\">3. \u66f4\u65b0\u89c6\u56fe<\/h3>\n\n\n\n<p id=\"u630081cf\">\u89c6\u56fe\u672c\u8eab\u662f\u4e0d\u80fd\u76f4\u63a5\u66f4\u65b0\u7684\uff0c\u4f46\u662f\u53ef\u4ee5\u901a\u8fc7\u89c6\u56fe\u66f4\u65b0\u57fa\u7840\u8868\u7684\u6570\u636e\u3002\u524d\u63d0\u662f\u89c6\u56fe\u5fc5\u987b\u6ee1\u8db3\u4e00\u5b9a\u7684\u6761\u4ef6\uff0c\u4f8b\u5982\u89c6\u56fe\u4e2d\u4e0d\u80fd\u5305\u542b\u805a\u5408\u51fd\u6570\u3001\u5b50\u67e5\u8be2\u7b49\u3002<\/p>\n\n\n\n<pre id=\"2d9dc56f\" class=\"wp-block-code\"><code>UPDATE employee_view\nSET salary = salary + 1000\nWHERE name = 'egon';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"eq2MG\">4. \u5220\u9664\u89c6\u56fe<\/h3>\n\n\n\n<p id=\"u9e6bf58c\">\u5982\u679c\u4e0d\u518d\u9700\u8981\u67d0\u4e2a\u89c6\u56fe\uff0c\u53ef\u4ee5\u5c06\u5176\u5220\u9664\uff1a<\/p>\n\n\n\n<pre id=\"ed0fb02f\" class=\"wp-block-code\"><code>DROP VIEW employee_view;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"d1r5l\">5. \u67e5\u770b\u6240\u6709\u89c6\u56fe<\/h3>\n\n\n\n<p id=\"u9c23393e\">\u53ef\u4ee5\u67e5\u8be2 <code>information_schema<\/code> \u6570\u636e\u5e93\u4e2d\u7684 <code>VIEWS<\/code> \u8868\uff0c\u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u7684\u6240\u6709\u89c6\u56fe\uff1a<\/p>\n\n\n\n<pre id=\"a9219fc4\" class=\"wp-block-code\"><code>SELECT table_name\nFROM information_schema.views\nWHERE table_schema = 'your_database_name';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"IEIit\">6. \u67e5\u770b\u89c6\u56fe\u5b9a\u4e49<\/h3>\n\n\n\n<p id=\"u81923c64\">\u53ef\u4ee5\u4f7f\u7528 <code>SHOW CREATE VIEW<\/code> \u547d\u4ee4\u67e5\u770b\u89c6\u56fe\u7684\u5b9a\u4e49\uff1a<\/p>\n\n\n\n<pre id=\"79e6de02\" class=\"wp-block-code\"><code>SHOW CREATE VIEW employee_view;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"2782f47e\">\u5b8c\u6574\u793a\u4f8b\u64cd\u4f5c<\/h3>\n\n\n\n<p id=\"ud6449952\">\u4e0b\u9762\u662f\u521b\u5efa\u3001\u4f7f\u7528\u548c\u5220\u9664\u89c6\u56fe\u7684\u4e00\u7cfb\u5217\u5b8c\u6574\u64cd\u4f5c\uff1a<\/p>\n\n\n\n<pre id=\"4fb32e49\" class=\"wp-block-code\"><code>-- \u521b\u5efa\u89c6\u56fe\nCREATE VIEW employee_view AS\nSELECT \n    e.id, \n    e.name, \n    e.sex, \n    e.age, \n    e.hire_date, \n    e.post, \n    e.salary, \n    d.name AS department\nFROM \n    employee e\nJOIN \n    department d ON e.depart_id = d.id;\n\n-- \u67e5\u8be2\u89c6\u56fe\nSELECT * FROM employee_view;\n\n-- \u66f4\u65b0\u89c6\u56fe\u4e2d\u7684\u6570\u636e\uff08\u5b9e\u9645\u66f4\u65b0\u57fa\u7840\u8868\uff09\nUPDATE employee_view\nSET salary = salary + 1000\nWHERE name = 'egon';\n\n-- \u67e5\u770b\u6240\u6709\u89c6\u56fe\nSELECT table_name\nFROM information_schema.views\nWHERE table_schema = 'your_database_name';\n\n-- \u67e5\u770b\u89c6\u56fe\u5b9a\u4e49\nSHOW CREATE VIEW employee_view;\n\n-- \u5220\u9664\u89c6\u56fe\nDROP VIEW employee_view;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"1bbbb204\">\u6ce8\u610f\u4e8b\u9879<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u6027\u80fd<\/strong>\uff1a\u89c6\u56fe\u662f\u4e00\u4e2a\u865a\u62df\u8868\uff0c\u6bcf\u6b21\u67e5\u8be2\u89c6\u56fe\u65f6\uff0c\u6570\u636e\u5e93\u90fd\u4f1a\u6267\u884c\u89c6\u56fe\u5b9a\u4e49\u4e2d\u7684\u67e5\u8be2\u3002\u56e0\u6b64\uff0c\u590d\u6742\u7684\u89c6\u56fe\u53ef\u80fd\u4f1a\u5f71\u54cd\u67e5\u8be2\u6027\u80fd\u3002<\/li>\n\n\n\n<li><strong>\u5b89\u5168\u6027<\/strong>\uff1a\u901a\u8fc7\u89c6\u56fe\u53ef\u4ee5\u9650\u5236\u7528\u6237\u8bbf\u95ee\u57fa\u7840\u8868\u4e2d\u7684\u67d0\u4e9b\u5217\u6216\u884c\uff0c\u4ece\u800c\u63d0\u9ad8\u6570\u636e\u5b89\u5168\u6027\u3002<\/li>\n\n\n\n<li><strong>\u7ef4\u62a4<\/strong>\uff1a\u89c6\u56fe\u7684\u5b9a\u4e49\u4f9d\u8d56\u4e8e\u57fa\u7840\u8868\u7ed3\u6784\uff0c\u5982\u679c\u57fa\u7840\u8868\u7ed3\u6784\u53d1\u751f\u53d8\u5316\uff0c\u53ef\u80fd\u9700\u8981\u76f8\u5e94\u5730\u4fee\u6539\u89c6\u56fe\u5b9a\u4e49\u3002<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"RSfoK\">\u5b9e\u9a8c\u4e94\uff1a\u89e6\u53d1\u5668\u5b9e\u9a8c<\/h2>\n\n\n\n<p id=\"u3b8b8333\">\u89e6\u53d1\u5668\u662f\u4e00\u79cd\u7279\u6b8a\u7684\u5b58\u50a8\u8fc7\u7a0b\uff0c\u5f53\u8868\u4e2d\u7684\u6570\u636e\u53d1\u751f\u67d0\u4e9b\u7279\u5b9a\u4e8b\u4ef6\u65f6\uff08\u5982\u63d2\u5165\u3001\u66f4\u65b0\u6216\u5220\u9664\uff09\uff0c\u89e6\u53d1\u5668\u4f1a\u81ea\u52a8\u6267\u884c\u3002\u89e6\u53d1\u5668\u5e38\u7528\u4e8e\u5b9e\u73b0\u6570\u636e\u7684\u81ea\u52a8\u5ba1\u8ba1\u3001\u6570\u636e\u4e00\u81f4\u6027\u68c0\u67e5\u7b49\u529f\u80fd\u3002\u672c\u5b9e\u9a8c\u8fd8\u662f\u57fa\u4e8e<strong>\u4e0a\u4e00\u4e2a\u5b9e\u9a8c\u7684 <\/strong><code><strong>employee<\/strong><\/code><strong> \u7684\u8868\u3002<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"kuzvO\">1. \u521b\u5efa <code>employee_log<\/code> \u8868<\/h3>\n\n\n\n<p id=\"u3a0bfe77\">\u5728\u521b\u5efa\u89e6\u53d1\u5668\u4e4b\u524d\uff0c\u9700\u8981\u4e00\u4e2a\u65e5\u5fd7\u8868 <code>employee_log<\/code> \u6765\u8bb0\u5f55\u5458\u5de5\u7684\u63d2\u5165\u64cd\u4f5c\u3002<\/p>\n\n\n\n<pre id=\"853f0a9e\" class=\"wp-block-code\"><code>CREATE TABLE employee_log (\n    id INT PRIMARY KEY AUTO_INCREMENT,\n    employee_id INT,\n    action VARCHAR(50),\n    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"FECQX\">2. \u521b\u5efa\u89e6\u53d1\u5668<\/h3>\n\n\n\n<p id=\"uf82a1e24\">\u4ee5\u521b\u5efa\u4e00\u4e2a\u5728\u63d2\u5165\u65b0\u5458\u5de5\u65f6\u8bb0\u5f55\u65e5\u5fd7\u7684\u89e6\u53d1\u5668\uff1a<\/p>\n\n\n\n<pre id=\"d2e46a18\" class=\"wp-block-code\"><code>DELIMITER \/\/\n\nCREATE TRIGGER after_employee_insert\nAFTER INSERT ON employee\nFOR EACH ROW\nBEGIN\n    INSERT INTO employee_log (employee_id, action) VALUES (NEW.id, 'INSERT');\nEND \/\/\n\nDELIMITER ;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"mYjDc\">3. \u67e5\u770b\u89e6\u53d1\u5668<\/h3>\n\n\n\n<p id=\"u058a3570\">\u53ef\u4ee5\u67e5\u8be2 <code>information_schema<\/code> \u6570\u636e\u5e93\u4e2d\u7684 <code>TRIGGERS<\/code> \u8868\uff0c\u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u7684\u6240\u6709\u89e6\u53d1\u5668\uff1a<\/p>\n\n\n\n<pre id=\"aec58557\" class=\"wp-block-code\"><code>SELECT trigger_name\nFROM information_schema.triggers\nWHERE trigger_schema = 'your_database_name';<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-39.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"424\" height=\"257\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-39.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-249\"  sizes=\"auto, (max-width: 424px) 100vw, 424px\" \/><\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"xKikZ\">4. \u67e5\u770b\u89e6\u53d1\u5668\u5b9a\u4e49<\/h3>\n\n\n\n<p id=\"uf4c67a54\">\u53ef\u4ee5\u4f7f\u7528 <code>SHOW TRIGGERS<\/code> \u547d\u4ee4\u67e5\u770b\u89e6\u53d1\u5668\u7684\u5b9a\u4e49\uff1a<\/p>\n\n\n\n<pre id=\"bce790dc\" class=\"wp-block-code\"><code>SHOW TRIGGERS FROM your_database_name;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"vjrZm\">5. \u5220\u9664\u89e6\u53d1\u5668<\/h3>\n\n\n\n<p id=\"u4b07d9db\">\u5982\u679c\u4e0d\u518d\u9700\u8981\u67d0\u4e2a\u89e6\u53d1\u5668\uff0c\u53ef\u4ee5\u5c06\u5176\u5220\u9664\uff1a<\/p>\n\n\n\n<pre id=\"e47bd7ab\" class=\"wp-block-code\"><code>DROP TRIGGER after_employee_insert;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"feAWd\">6. \u89e6\u53d1\u5668\u89e6\u53d1\u7ed3\u679c<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"ZEkrC\">6.1. <strong>\u63d2\u5165\u6570\u636e\u5e76\u89e6\u53d1\u89e6\u53d1\u5668\uff1a<\/strong><\/h4>\n\n\n\n<pre id=\"3096169a\" class=\"wp-block-code\"><code>INSERT INTO employee (name, sex, age, hire_date, post, salary, office, depart_id) \nVALUES ('new_employee', 'male', 25, '2023-06-01', 'developer', 6000.00, 404, 4);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"oLITE\">6.2. <strong>\u67e5\u770b\u65e5\u5fd7\uff1a<\/strong><\/h4>\n\n\n\n<pre id=\"8f83b393\" class=\"wp-block-code\"><code>SELECT * FROM employee_log;<\/code><\/pre>\n\n\n\n<p id=\"ue7e79934\">\u67e5\u8be2\u7ed3\u679c\uff1a<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-40.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"941\" height=\"316\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-40.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-250\"  sizes=\"auto, (max-width: 941px) 100vw, 941px\" \/><\/div><\/figure>\n\n\n\n<p id=\"ub2e2ea3b\">\u5728\u63d2\u5165\u4e00\u6761\u65b0\u5458\u5de5\u8bb0\u5f55\u540e\uff0c<code>employee_log<\/code> \u8868\u4e2d\u8bb0\u5f55\u4e86\u4e00\u6761\u5bf9\u5e94\u7684\u65e5\u5fd7\uff0c\u663e\u793a\u8be5\u5458\u5de5\u7684ID\u3001\u64cd\u4f5c\u7c7b\u578b\uff08INSERT\uff09\u4ee5\u53ca\u64cd\u4f5c\u65f6\u95f4\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"ETp5o\">\u6ce8\u610f\u4e8b\u9879<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u89e6\u53d1\u5668\u7c7b\u578b<\/strong>\uff1a\u89e6\u53d1\u5668\u5206\u4e3a <code>BEFORE<\/code> \u548c <code>AFTER<\/code> \u4e24\u79cd\u7c7b\u578b\uff0c\u5206\u522b\u8868\u793a\u5728\u6570\u636e\u4fee\u6539\u4e4b\u524d\u548c\u4e4b\u540e\u6267\u884c\u89e6\u53d1\u5668\u3002<\/li>\n\n\n\n<li><strong>\u89e6\u53d1\u4e8b\u4ef6<\/strong>\uff1a\u89e6\u53d1\u5668\u53ef\u4ee5\u5728 <code>INSERT<\/code>\u3001<code>UPDATE<\/code> \u548c <code>DELETE<\/code> \u4e09\u79cd\u4e8b\u4ef6\u4e0a\u89e6\u53d1\u3002<\/li>\n\n\n\n<li><strong>NEW \u548c OLD \u5173\u952e\u5b57<\/strong>\uff1a\u5728 <code>INSERT<\/code> \u89e6\u53d1\u5668\u4e2d\uff0c<code>NEW<\/code> \u5173\u952e\u5b57\u7528\u4e8e\u5f15\u7528\u65b0\u63d2\u5165\u7684\u6570\u636e\u3002\u5728 <code>UPDATE<\/code> \u89e6\u53d1\u5668\u4e2d\uff0c<code>NEW<\/code> \u5f15\u7528\u66f4\u65b0\u540e\u7684\u6570\u636e\uff0c<code>OLD<\/code> \u5f15\u7528\u66f4\u65b0\u524d\u7684\u6570\u636e\u3002\u5728 <code>DELETE<\/code> \u89e6\u53d1\u5668\u4e2d\uff0c<code>OLD<\/code> \u5173\u952e\u5b57\u7528\u4e8e\u5f15\u7528\u88ab\u5220\u9664\u7684\u6570\u636e\u3002<\/li>\n\n\n\n<li><strong>\u6027\u80fd<\/strong>\uff1a\u89e6\u53d1\u5668\u4f1a\u5f71\u54cd\u6570\u636e\u4fee\u6539\u64cd\u4f5c\u7684\u6027\u80fd\uff0c\u56e0\u4e3a\u6bcf\u6b21\u6570\u636e\u4fee\u6539\u65f6\u89e6\u53d1\u5668\u90fd\u4f1a\u88ab\u6267\u884c\u3002<\/li>\n\n\n\n<li><strong>\u8c03\u8bd5<\/strong>\uff1a\u8c03\u8bd5\u89e6\u53d1\u5668\u53ef\u80fd\u6bd4\u8f83\u56f0\u96be\uff0c\u56e0\u4e3a\u89e6\u53d1\u5668\u662f\u5728\u6570\u636e\u4fee\u6539\u64cd\u4f5c\u671f\u95f4\u81ea\u52a8\u6267\u884c\u7684\u3002\u5efa\u8bae\u901a\u8fc7\u65e5\u5fd7\u8bb0\u5f55\u7684\u65b9\u5f0f\u8fdb\u884c\u8c03\u8bd5\u3002<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"d0wLO\">\u5b9e\u9a8c\u516d\uff1a\u81ea\u5b9a\u4e49\u51fd\u6570\u5b9e\u9a8c<\/h2>\n\n\n\n<p id=\"u878d8548\">\u81ea\u5b9a\u4e49\u51fd\u6570\u5141\u8bb8\u4f60\u521b\u5efa\u53ef\u91cd\u7528\u7684SQL\u4ee3\u7801\u5757\uff0c\u53ef\u4ee5\u5728SQL\u8bed\u53e5\u4e2d\u8c03\u7528\u8fd9\u4e9b\u51fd\u6570\u3002\u81ea\u5b9a\u4e49\u51fd\u6570\u5728MySQL\u4e2d\u975e\u5e38\u6709\u7528\uff0c\u5b83\u4eec\u53ef\u4ee5\u7528\u4e8e\u5c01\u88c5\u590d\u6742\u7684\u8ba1\u7b97\u6216\u4e1a\u52a1\u903b\u8f91\uff0c\u4f7f\u5f97\u4ee3\u7801\u66f4\u52a0\u7b80\u6d01\u548c\u53ef\u7ef4\u62a4\u3002\u672c\u5b9e\u9a8c\u8fd8\u662f\u57fa\u4e8e<strong>\u4e0a\u4e00\u4e2a\u5b9e\u9a8c\u7684 <\/strong><code><strong>employee<\/strong><\/code><strong> \u7684\u8868\u3002<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"a1UVO\">1. \u521b\u5efa\u81ea\u5b9a\u4e49\u51fd\u6570<\/h3>\n\n\n\n<p id=\"udab689a2\">\u521b\u5efa\u4e00\u4e2a\u8ba1\u7b97\u5458\u5de5\u5e74\u85aa\u7684\u81ea\u5b9a\u4e49\u51fd\u6570\uff1a<\/p>\n\n\n\n<pre id=\"6b14adc2\" class=\"wp-block-code\"><code>DELIMITER \/\/\n\nCREATE FUNCTION calculate_annual_salary(monthly_salary DOUBLE)\nRETURNS DOUBLE\nDETERMINISTIC\nBEGIN\n    RETURN monthly_salary * 12;\nEND \/\/\n\nDELIMITER ;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"sPo0w\">2. \u4f7f\u7528\u81ea\u5b9a\u4e49\u51fd\u6570<\/h3>\n\n\n\n<p id=\"u3466111a\">\u521b\u5efa\u51fd\u6570\u540e\uff0c\u53ef\u4ee5\u5728SQL\u67e5\u8be2\u4e2d\u4f7f\u7528\u5b83\uff1a<\/p>\n\n\n\n<pre id=\"OhiA8\" class=\"wp-block-code\"><code>SELECT \n    name, \n    calculate_annual_salary(salary) AS annual_salary \nFROM \n    employee;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-41.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"435\" height=\"510\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-41.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-251\"  sizes=\"auto, (max-width: 435px) 100vw, 435px\" \/><\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"fyKYz\">3. \u67e5\u770b\u81ea\u5b9a\u4e49\u51fd\u6570<\/h3>\n\n\n\n<p id=\"ub2a3003c\">\u53ef\u4ee5\u67e5\u8be2 <code>information_schema<\/code> \u6570\u636e\u5e93\u4e2d\u7684 <code>ROUTINES<\/code> \u8868\uff0c\u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u7684\u6240\u6709\u81ea\u5b9a\u4e49\u51fd\u6570\uff1a<\/p>\n\n\n\n<pre id=\"07b80819\" class=\"wp-block-code\"><code>SELECT routine_name\nFROM information_schema.routines\nWHERE routine_type = 'FUNCTION' AND routine_schema = 'your_database_name';<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-42.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"854\" height=\"140\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-42.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-252\"  sizes=\"auto, (max-width: 854px) 100vw, 854px\" \/><\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"aj7fJ\">4. \u67e5\u770b\u81ea\u5b9a\u4e49\u51fd\u6570\u5b9a\u4e49<\/h3>\n\n\n\n<p id=\"u717b928d\">\u53ef\u4ee5\u4f7f\u7528 <code>SHOW CREATE FUNCTION<\/code> \u547d\u4ee4\u67e5\u770b\u81ea\u5b9a\u4e49\u51fd\u6570\u7684\u5b9a\u4e49\uff1a<\/p>\n\n\n\n<pre id=\"c4cd8ab8\" class=\"wp-block-code\"><code>SHOW CREATE FUNCTION calculate_annual_salary;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"NozCU\">5. \u5220\u9664\u81ea\u5b9a\u4e49\u51fd\u6570<\/h3>\n\n\n\n<p id=\"u676d0bf7\">\u5982\u679c\u4e0d\u518d\u9700\u8981\u67d0\u4e2a\u81ea\u5b9a\u4e49\u51fd\u6570\uff0c\u53ef\u4ee5\u5c06\u5176\u5220\u9664\uff1a<\/p>\n\n\n\n<pre id=\"2fc52123\" class=\"wp-block-code\"><code>DROP FUNCTION calculate_annual_salary;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"mCKLF\">\u6ce8\u610f\u4e8b\u9879<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u51fd\u6570\u7c7b\u578b<\/strong>\uff1a\u81ea\u5b9a\u4e49\u51fd\u6570\u8fd4\u56de\u4e00\u4e2a\u503c\uff0c\u5e76\u4e14\u53ef\u4ee5\u5728SQL\u8bed\u53e5\u4e2d\u4f7f\u7528\u3002\u4e0e\u5b58\u50a8\u8fc7\u7a0b\u4e0d\u540c\uff0c\u5b58\u50a8\u8fc7\u7a0b\u53ef\u4ee5\u8fd4\u56de\u591a\u4e2a\u7ed3\u679c\u96c6\u6216\u6ca1\u6709\u8fd4\u56de\u503c\u3002<\/li>\n\n\n\n<li><strong>\u53c2\u6570<\/strong>\uff1a\u81ea\u5b9a\u4e49\u51fd\u6570\u53ef\u4ee5\u63a5\u53d7\u591a\u4e2a\u53c2\u6570\uff0c\u53c2\u6570\u7c7b\u578b\u53ef\u4ee5\u662f\u4efb\u4f55\u6709\u6548\u7684MySQL\u6570\u636e\u7c7b\u578b\u3002<\/li>\n\n\n\n<li><strong>\u8fd4\u56de\u7c7b\u578b<\/strong>\uff1a\u81ea\u5b9a\u4e49\u51fd\u6570\u5fc5\u987b\u6307\u5b9a\u8fd4\u56de\u7c7b\u578b\u3002<\/li>\n\n\n\n<li><strong>DETERMINISTIC \u548c NON-DETERMINISTIC<\/strong>\uff1a<code>DETERMINISTIC<\/code> \u8868\u793a\u51fd\u6570\u7684\u8fd4\u56de\u503c\u4ec5\u53d6\u51b3\u4e8e\u8f93\u5165\u53c2\u6570\uff0c\u800c <code>NON-DETERMINISTIC<\/code> \u8868\u793a\u51fd\u6570\u7684\u8fd4\u56de\u503c\u53ef\u80fd\u53d6\u51b3\u4e8e\u8f93\u5165\u53c2\u6570\u4ee5\u5916\u7684\u56e0\u7d20\uff08\u4f8b\u5982\uff0c\u968f\u673a\u6570\u3001\u5f53\u524d\u65f6\u95f4\u7b49\uff09\u3002<\/li>\n\n\n\n<li><strong>\u6027\u80fd<\/strong>\uff1a\u4f7f\u7528\u81ea\u5b9a\u4e49\u51fd\u6570\u53ef\u4ee5\u63d0\u9ad8\u67e5\u8be2\u7684\u53ef\u8bfb\u6027\u548c\u91cd\u7528\u6027\uff0c\u4f46\u5728\u67d0\u4e9b\u60c5\u51b5\u4e0b\uff0c\u9891\u7e41\u8c03\u7528\u81ea\u5b9a\u4e49\u51fd\u6570\u53ef\u80fd\u4f1a\u5f71\u54cd\u6027\u80fd\u3002<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"jjT7Y\">\u5b9e\u9a8c\u4e03\uff1a\u6d41\u7a0b\u63a7\u5236\u5b9e\u9a8c<\/h2>\n\n\n\n<p id=\"ub3f0c7e0\">\u6d41\u7a0b\u63a7\u5236\u8bed\u53e5\u7528\u4e8e\u63a7\u5236\u5b58\u50a8\u8fc7\u7a0b\u6216\u51fd\u6570\u7684\u6267\u884c\u6d41\u7a0b\uff0c\u5982\u6761\u4ef6\u8bed\u53e5\u3001\u5faa\u73af\u8bed\u53e5\u7b49\u3002MySQL \u652f\u6301\u591a\u79cd\u6d41\u7a0b\u63a7\u5236\u8bed\u53e5\uff0c\u4f7f\u5f97\u5b58\u50a8\u8fc7\u7a0b\u548c\u51fd\u6570\u80fd\u591f\u5904\u7406\u590d\u6742\u7684\u903b\u8f91\u3002\u672c\u5b9e\u9a8c\u8fd8\u662f\u57fa\u4e8e<strong>\u4e0a\u4e00\u4e2a\u5b9e\u9a8c\u7684 <\/strong><code><strong>employee<\/strong><\/code><strong> \u7684\u8868\u3002<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"NGFZx\">1. \u6761\u4ef6\u8bed\u53e5<\/h3>\n\n\n\n<p id=\"u94cc3bc1\">\u6761\u4ef6\u8bed\u53e5\u7528\u4e8e\u6839\u636e\u4e0d\u540c\u7684\u6761\u4ef6\u6267\u884c\u4e0d\u540c\u7684\u4ee3\u7801\u5757\u3002<\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"pZ7Jc\">\u793a\u4f8b\uff1a\u521b\u5efa\u4e00\u4e2a\u5e26\u6709\u6761\u4ef6\u8bed\u53e5\u7684\u5b58\u50a8\u8fc7\u7a0b<\/h5>\n\n\n\n<pre id=\"LIAHA\" class=\"wp-block-code\"><code>DELIMITER \/\/\n\nCREATE PROCEDURE check_salary(IN emp_id INT)\nBEGIN\n    DECLARE emp_salary DOUBLE;\n    \n    SELECT salary INTO emp_salary \n    FROM employee \n    WHERE id = emp_id;\n    \n    IF emp_salary &gt; 50000 THEN\n        SELECT 'High salary' AS status;\n    ELSE\n        SELECT 'Low salary' AS status;\n    END IF;\nEND \/\/\n\nDELIMITER ;<\/code><\/pre>\n\n\n\n<p id=\"ud4671cdd\">\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b\uff1a<\/p>\n\n\n\n<pre id=\"yIHji\" class=\"wp-block-code\"><code>CALL check_salary(1);<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><div class='fancybox-wrapper lazyload-container-unload' data-fancybox='post-images' href='https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-43.png'><img class=\"lazyload lazyload-style-1\" src=\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\"  loading=\"lazy\" decoding=\"async\" width=\"419\" height=\"498\" data-original=\"https:\/\/zhaoyanqi.cn\/wp-content\/uploads\/2024\/06\/image-43.png\" src=\"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB\/AAffA0nNPuCLAAAAAElFTkSuQmCC\" alt=\"\" class=\"wp-image-253\"  sizes=\"auto, (max-width: 419px) 100vw, 419px\" \/><\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"lXfKh\">2. \u5faa\u73af\u8bed\u53e5<\/h3>\n\n\n\n<p id=\"uc44a18cb\">\u5faa\u73af\u8bed\u53e5\u7528\u4e8e\u91cd\u590d\u6267\u884c\u4e00\u7ec4\u8bed\u53e5\uff0c\u76f4\u5230\u6ee1\u8db3\u67d0\u4e2a\u6761\u4ef6\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"pDDVV\">\u793a\u4f8b\uff1a\u4f7f\u7528 <code>WHILE<\/code> \u5faa\u73af\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b<\/h4>\n\n\n\n<pre id=\"ee8921ab\" class=\"wp-block-code\"><code>DELIMITER \/\/\n\nCREATE PROCEDURE list_employees() # \u6253\u5370\u524d10\u4e2aemployee\nBEGIN\n    DECLARE emp_id INT DEFAULT 1;\n    \n    WHILE emp_id &lt;= 10 DO\n        SELECT * FROM employee WHERE id = emp_id;\n        SET emp_id = emp_id + 1;\n    END WHILE;\nEND \/\/\n\nDELIMITER ;<\/code><\/pre>\n\n\n\n<p id=\"u648b9cf0\">\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b\uff1a<\/p>\n\n\n\n<pre id=\"39af2c76\" class=\"wp-block-code\"><code>CALL list_employees();<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"ad0Ut\">\u793a\u4f8b\uff1a\u4f7f\u7528 <code>REPEAT<\/code> \u5faa\u73af\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b<\/h4>\n\n\n\n<pre id=\"641c0298\" class=\"wp-block-code\"><code>DELIMITER \/\/\n\nCREATE PROCEDURE repeat_example() # \u4ece1\u5f00\u59cb\u6253\u5370\u76f4\u5230i\u5927\u4e8e5\nBEGIN\n    DECLARE i INT DEFAULT 1;\n    \n    REPEAT\n        SELECT i;\n        SET i = i + 1;\n    UNTIL i &gt; 5\n    END REPEAT;\nEND \/\/\n\nDELIMITER ;<\/code><\/pre>\n\n\n\n<p id=\"uda232a92\">\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b\uff1a<\/p>\n\n\n\n<pre id=\"0cb32a7a\" class=\"wp-block-code\"><code>CALL repeat_example();<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"cd1c448c\">\u793a\u4f8b\uff1a\u4f7f\u7528 <code>LOOP<\/code> \u5faa\u73af\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b<\/h5>\n\n\n\n<pre id=\"97369826\" class=\"wp-block-code\"><code>DELIMITER \/\/\n\nCREATE PROCEDURE loop_example()  # \u4ece1\u5f00\u59cb\u6253\u5370\u76f4\u5230i\u5927\u4e8e5\nBEGIN\n    DECLARE i INT DEFAULT 1;\n    loop_label: LOOP\n        SELECT i;\n        SET i = i + 1;\n        IF i &gt; 5 THEN\n            LEAVE loop_label;\n        END IF;\n    END LOOP loop_label;\nEND \/\/\n\nDELIMITER ;<\/code><\/pre>\n\n\n\n<p id=\"u14ebef17\">\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b\uff1a<\/p>\n\n\n\n<pre id=\"64d984c8\" class=\"wp-block-code\"><code>CALL loop_example();<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"AylqN\">\u6ce8\u610f\u4e8b\u9879<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u6761\u4ef6\u8bed\u53e5<\/strong>\uff1a<code>IF<\/code>\u3001<code>CASE<\/code> \u7b49\u6761\u4ef6\u8bed\u53e5\u53ef\u4ee5\u6839\u636e\u4e0d\u540c\u7684\u6761\u4ef6\u6267\u884c\u4e0d\u540c\u7684\u4ee3\u7801\u5757\u3002<\/li>\n\n\n\n<li><strong>\u5faa\u73af\u8bed\u53e5<\/strong>\uff1a<code>WHILE<\/code>\u3001<code>REPEAT<\/code> \u548c <code>LOOP<\/code> \u5faa\u73af\u53ef\u4ee5\u91cd\u590d\u6267\u884c\u4e00\u7ec4\u8bed\u53e5\uff0c\u76f4\u5230\u6ee1\u8db3\u67d0\u4e2a\u6761\u4ef6\u3002<\/li>\n\n\n\n<li><strong>\u5f02\u5e38\u5904\u7406<\/strong>\uff1a\u4f7f\u7528 <code>DECLARE ... HANDLER<\/code> \u8bed\u53e5\u53ef\u4ee5\u5728\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u5904\u7406\u5f02\u5e38\uff0c\u786e\u4fdd\u5728\u53d1\u751f\u9519\u8bef\u65f6\u53ef\u4ee5\u6267\u884c\u7279\u5b9a\u7684\u4ee3\u7801\u3002<\/li>\n\n\n\n<li><strong>\u6027\u80fd<\/strong>\uff1a\u5728\u4f7f\u7528\u5faa\u73af\u548c\u6761\u4ef6\u8bed\u53e5\u65f6\uff0c\u9700\u8981\u6ce8\u610f\u6027\u80fd\u95ee\u9898\uff0c\u907f\u514d\u7f16\u5199\u4f4e\u6548\u7684\u4ee3\u7801\u3002<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"umjv0\">\u5b9e\u9a8c\u516b\uff1a\u5e26EXISTS\u5173\u952e\u5b57\u7684\u5b50\u67e5\u8be2<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"bLGWt\">1. \u521b\u5efa\u6570\u636e\u5e93\u548c\u8868<\/h3>\n\n\n\n<p id=\"u97919b66\">\u9996\u5148\uff0c\u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u6570\u636e\u5e93 <code>db13<\/code> \u5e76\u5207\u6362\u5230\u8be5\u6570\u636e\u5e93\uff0c\u7136\u540e\u521b\u5efa <code>student<\/code>\u3001<code>course<\/code> \u548c <code>student2course<\/code> \u4e09\u4e2a\u8868\u3002<\/p>\n\n\n\n<pre id=\"2b945764\" class=\"wp-block-code\"><code>CREATE DATABASE db13;\n\nUSE db13;\n\nCREATE TABLE student (\n    id INT PRIMARY KEY AUTO_INCREMENT,\n    name VARCHAR(16)\n);\n\nCREATE TABLE course (\n    id INT PRIMARY KEY AUTO_INCREMENT,\n    name VARCHAR(16),\n    comment VARCHAR(20)\n);\n\nCREATE TABLE student2course (\n    id INT PRIMARY KEY AUTO_INCREMENT,\n    sid INT,\n    cid INT,\n    FOREIGN KEY (sid) REFERENCES student(id),\n    FOREIGN KEY (cid) REFERENCES course(id)\n);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"UIcMZ\">2. \u63d2\u5165\u6570\u636e<\/h3>\n\n\n\n<p id=\"u5f8554c2\">\u5411 <code>student<\/code>\u3001<code>course<\/code> \u548c <code>student2course<\/code> \u8868\u4e2d\u63d2\u5165\u4e00\u4e9b\u6d4b\u8bd5\u6570\u636e\u3002<\/p>\n\n\n\n<pre id=\"87409119\" class=\"wp-block-code\"><code>INSERT INTO student(name) VALUES\n('egon'),\n('lili'),\n('jack'),\n('tom');\n\nINSERT INTO course(name, comment) VALUES\n('\u6570\u636e\u5e93', '\u6570\u636e\u4ed3\u5e93'),\n('\u6570\u5b66', '\u6839\u672c\u5b66\u4e0d\u4f1a'),\n('\u82f1\u8bed', '\u9e1f\u8bed\u82b1\u9999');\n\nINSERT INTO student2course(sid, cid) VALUES\n(1, 1),\n(1, 2),\n(1, 3),\n(2, 1),\n(2, 2),\n(3, 2);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"m3eXN\">3. \u8fdb\u884c\u67e5\u8be2<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"GKSLi\">3.1 \u67e5\u8be2\u9009\u4fee\u4e86\u6240\u6709\u8bfe\u7a0b\u7684\u5b66\u751fid\u3001name<\/h4>\n\n\n\n<p id=\"u07e83612\">\u8be5\u67e5\u8be2\u67e5\u627e\u90a3\u4e9b\u9009\u4fee\u4e86\u6240\u6709\u8bfe\u7a0b\u7684\u5b66\u751f\u3002<\/p>\n\n\n\n<p id=\"uf3ecf587\">\u65b9\u6cd51\uff1a<\/p>\n\n\n\n<pre id=\"99701094\" class=\"wp-block-code\"><code>SELECT * \nFROM student s \nWHERE NOT EXISTS (\n    SELECT * \n    FROM course c \n    WHERE NOT EXISTS (\n        SELECT * \n        FROM student2course sc \n        WHERE sc.sid = s.id AND sc.cid = c.id\n    )\n);<\/code><\/pre>\n\n\n\n<p id=\"ucb997fa8\">\u65b9\u6cd52\uff1a<\/p>\n\n\n\n<pre id=\"842800c9\" class=\"wp-block-code\"><code>SELECT s.name \nFROM student AS s\nINNER JOIN student2course AS sc ON s.id = sc.sid\nGROUP BY s.name \nHAVING COUNT(sc.id) = (SELECT COUNT(id) FROM course);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"Yxte2\">3.2 \u67e5\u8be2\u6ca1\u6709\u9009\u62e9\u6240\u6709\u8bfe\u7a0b\u7684\u5b66\u751f\uff0c\u5373\u6ca1\u6709\u5168\u9009\u7684\u5b66\u751f<\/h4>\n\n\n\n<p id=\"u0d39e41f\">\u8be5\u67e5\u8be2\u67e5\u627e\u90a3\u4e9b\u81f3\u5c11\u6709\u4e00\u95e8\u8bfe\u7a0b\u672a\u9009\u4fee\u7684\u5b66\u751f\u3002<\/p>\n\n\n\n<pre id=\"eddf70cf\" class=\"wp-block-code\"><code>SELECT * \nFROM student s \nWHERE EXISTS (\n    SELECT * \n    FROM course c \n    WHERE NOT EXISTS (\n        SELECT * \n        FROM student2course sc \n        WHERE sc.sid = s.id AND sc.cid = c.id\n    )\n);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"BPqmj\">3.3 \u67e5\u8be2\u4e00\u95e8\u8bfe\u4e5f\u6ca1\u6709\u9009\u7684\u5b66\u751f<\/h4>\n\n\n\n<p id=\"ufdcefda1\">\u8be5\u67e5\u8be2\u67e5\u627e\u90a3\u4e9b\u4e00\u95e8\u8bfe\u4e5f\u6ca1\u6709\u9009\u7684\u5b66\u751f\u3002<\/p>\n\n\n\n<pre id=\"e6877733\" class=\"wp-block-code\"><code>SELECT * \nFROM student s \nWHERE NOT EXISTS (\n    SELECT * \n    FROM student2course sc \n    WHERE sc.sid = s.id\n);<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"UenVw\">3.4 \u67e5\u8be2\u81f3\u5c11\u9009\u4fee\u4e86\u4e00\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f<\/h4>\n\n\n\n<p id=\"ua413bd4e\">\u8be5\u67e5\u8be2\u67e5\u627e\u90a3\u4e9b\u81f3\u5c11\u9009\u4fee\u4e86\u4e00\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u3002<\/p>\n\n\n\n<pre id=\"2d072945\" class=\"wp-block-code\"><code>SELECT * \nFROM student s \nWHERE EXISTS (\n    SELECT * \n    FROM student2course sc \n    WHERE sc.sid = s.id\n);<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5b9e\u9a8c\u4e00\uff1a\u901a\u8fc7\u5b58\u50a8\u8fc7\u7a0b\u521b\u5efa300w\u6761\u6570\u636e \u521b\u5efa\u8868 \u5047\u8bbe\u8868\u7684\u540d\u79f0\u4e3atest_table\uff0c\u4e14\u8868\u7ed3\u6784\u5982\u4e0b\uff1a \u7f16\u5199\u5b58\u50a8\u8fc7 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,12],"tags":[10],"class_list":["post-239","post","type-post","status-publish","format-standard","hentry","category-mysql","category-sql","tag-mysql_lab"],"_links":{"self":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=239"}],"version-history":[{"count":1,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/239\/revisions"}],"predecessor-version":[{"id":254,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/239\/revisions\/254"}],"wp:attachment":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}