{"id":1429,"date":"2021-09-12T21:53:20","date_gmt":"2021-09-12T13:53:20","guid":{"rendered":"https:\/\/blog.odliken.top\/?p=1429"},"modified":"2021-09-12T21:53:20","modified_gmt":"2021-09-12T13:53:20","slug":"mysql%e5%9f%ba%e7%a1%80%e4%b9%8b%e6%95%b0%e6%8d%ae%e7%ae%a1%e7%90%86","status":"publish","type":"post","link":"https:\/\/blog.odjbinail.cn\/?p=1429","title":{"rendered":"MySQL\u57fa\u7840\u4e4b\u6570\u636e\u7ba1\u7406"},"content":{"rendered":"<h2>\u63d2\u5165\u8bb0\u5f55\u64cd\u4f5c<\/h2>\n<ul>\n<li>\u6dfb\u52a0\u8bb0\u5f55\uff1aINSERT [INTO] tbl_name[(col_name,.\u2026)] {VALUE|VALUES}(VALUES.\u2026.);\n<ul>\n<li>\u4e0d\u6307\u5b9a\u5177\u4f53\u7684\u5b57\u6bb5\u540d\u79f0\uff1aINSERT tbl_name VALUE(value...)<\/li>\n<\/ul>\n<\/li>\n<li>\u5217\u51fa\u6307\u5b9a\u5b57\u6bb5\uff1aINSERT tbl_name(\u5b57\u6bb5\u540d\u79f0,...) VALUES(\u503c,.\u2026.)<\/li>\n<li>INSERT ...SET \u7684\u5f62\u5f0f\uff1aINSERT tbl_name SET \u5b57\u6bb5\u540d\u79f0=\u503c,..;<\/li>\n<li>INSERT...SELECT\uff1a INSERT tbl_name[(\u5b57\u6bb5\u540d\u79f0...)] SELECT \u5b57\u6bb5\u540d\u79f0,....FROM tbl_name [WHERE \u6761\u4ef6]<\/li>\n<li>\u4e00\u6b21\u6dfb\u52a0\u591a\u6761\u8bb0\u5f55\uff1alNSERT tbl_name[(\u5b57\u6bb5\u540d\u79f0,...)] VALUES(\u503c,...),(\u503c,..\u2026.),(\u503c,...)<\/li>\n<\/ul>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5\u6dfb\u52a0\u8bb0\u5f55\nCREATE DATABASE IF NOT EXISTS king DEFAULT CHARACTER SET &#039;UTF8&#039;;\nUSE king;\nCREATE TABLE IF NOT EXISTS user(\n    id INT UNSIGNED AUTO_INCREMENT KEY COMMENT &#039;\u7f16\u53f7&#039;,\n    username VARCHAR(20) NOT NULL UNIQUE COMMENT &#039;\u7528\u6237\u540d&#039;,\n    age TINYINT UNSIGNED DEFAULT 18 COMMENT &#039;\u5e74\u9f84&#039;,\n    email VARCHAR(50) NOT NULL DEFAULT &#039;imooc@qq.com&#039; COMMENT &#039;\u90ae\u7bb1&#039;\n)ENGINE=INNODB CHARSET=UTF8;\n SELECT DATABASE();\n  SHOW TABLES;\n  DESC USER;\n-- \u4e0d\u6307\u5b9a\u5177\u4f53\u7684\u5b57\u6bb5\u540d\u79f0\nINSERT user VALUE(1,&#039;king&#039;,24,&#039;382771946@qq.com&#039;);\nINSERT user VALUES(NULL,&#039;queen&#039;,25,&#039;queen@qq.com&#039;);\nINSERT user VALUES(DEFAULT,&#039;lily&#039;,26,&#039;lily@qq.com&#039;);\nSELECT * FROM user;\n-- \u5217\u51fa\u6307\u5b9a\u5b57\u6bb5\u7684\u5f62\u5f0f\nINSERT user(username,email) VALUES(&#039;rose&#039;,&#039;rose@qq.com&#039;);\nINSERT user(age,email,id,username) VALUES(34,&#039;imooc@qq.com&#039;,5,&#039;imooc&#039;);\n-- \u4e00\u6b21\u63d2\u5165 3 \u6761\u8bb0\u5f55\nINSERT user VALUES(NULL,&#039;a&#039;,DEFAULT,DEFAULT),\n(NULL,&#039;b&#039;,56,&#039;b@qq.com&#039;),\n(NULL,&#039;c&#039;,14,&#039;c@qq.com&#039;);\n-- INSERT ...SET \u7684\u5f62\u5f0f\nINSERT user SET username=&#039;d&#039;,age=45,email=&#039;d@qq.com&#039;;\n-- INSERT SELECT\nINSERT user(username) SELECT a FROM test;<\/code><\/pre>\n<h2>\u4fee\u6539\u548c\u5220\u9664\u8bb0\u5f55\u64cd\u4f5c<\/h2>\n<ul>\n<li>\u4fee\u6539\u8bb0\u5f55\uff1aUPDATE tbl_name SET \u5b57\u6bb5\u540d\u79f0=\u503c,\u5b57\u6bb5\u540d\u79f0=\u503c [WHERE \u6761\u4ef6]\n<ul>\n<li>\u5982\u679c\u4e0d\u6dfb\u52a0\u6761\u4ef6\uff0c\u6574\u4e2a\u8868\u4e2d\u7684\u8bb0\u5f55\u90fd\u4f1a\u88ab\u66f4\u65b0<\/li>\n<\/ul>\n<\/li>\n<li>\u5220\u9664\u8bb0\u5f55\uff1aDELETE FROM tbl_name [WHERE \u6761\u4ef6]\n<ul>\n<li>\u5982\u679c\u4e0d\u6dfb\u52a0\u6761\u4ef6\uff0c\u8868\u4e2d\u6240\u6709\u8bb0\u5f55\u90fd\u4f1a\u88ab\u5220\u9664<\/li>\n<li>DELETE \u6e05\u7a7a\u6570\u636e\u8868\u7684\u65f6\u5019\u4e0d\u4f1a\u91cd\u7f6e AUTO_INCREMENT \u7684\u503c\uff0c\u53ef\u4ee5\u901a\u8fc7 ALTER \u8bed\u53e5\u5c06\u5176\u91cd\u7f6e\u4e3a 1<\/li>\n<li>\u5f7b\u5e95\u6e05\u7a7a\u6570\u636e\u8868\uff1aTRUNCATE [TABLE] tbl_name; \u3010\u6e05\u9664\u8868\u4e2d\u6240\u6709\u8bb0\u5f55\u3001\u4f1a\u91cd\u7f6e AUTO_INCREMENT \u7684\u503c\u3011<\/li>\n<\/ul>\n<\/li>\n<li>\u4fee\u6539 AUTO_INCREMENT \u7684\u503c\uff1a ALTER TABLE tbl_name AUTO_INCREMENT=\u503c;<\/li>\n<\/ul>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5\u66f4\u65b0\u8bed\u53e5\n-- \u4fee\u6539\u7b2c\u4e00\u4e2a\u7528\u6237\u7684\u4fe1\u606f id=1\nUPDATE user SET age=29 WHERE id=1;\n-- \u4fee\u6539 id=3 \u7684\u7528\u6237\uff0cusername age email\nUPDATE user SET age=47,email=&#039;lilys@qq.com&#039;,username=&#039;lilys&#039; WHERE id=3;\n-- \u6240\u6709\u7528\u6237\u5e74\u9f84+10\nUPDATE user SET age=age+10;\n-- \u5c06 id&lt;=5 \u7684\u7528\u6237\u5e74\u9f84\u6539\u4e3a-20\uff0c\u5c06\u90ae\u7bb1\u6539\u4e3a\u9ed8\u8ba4\u503c\nUPDATE user SET age=age-20,email=DEFAULT WHERE id&lt;=5;\n-- \u6d4b\u8bd5\u5220\u9664\u8bed\u53e5\n-- \u5220\u9664\u7528\u6237\u540d\u4e3a king\nDELETE FROM user WHERE username=&#039;king&#039;;\n-- \u5220\u9664\u5e74\u9f84\u4e3a 24 \u7684\u7528\u6237\nDELETE FROM user WHERE age=24;\n-- \u5220\u9664\u8868\u4e2d\u6240\u6709\u8bb0\u5f55\nDELETE FROM user;\nINSERT user VALUES(NULL,&#039;queen&#039;,25,&#039;queen@qq.com&#039;);\nINSERT user VALUES(DEFAULT,&#039;lily&#039;,26,&#039;lily@qq.com&#039;);\n-- \u81ea\u589e\u4ece 1 \u5f00\u59cb\nALTER TABLE user AUTO_INCREMENT=1;<\/code><\/pre>\n<h2>SELECT \u8bed\u53e5\u7684\u57fa\u672c\u5f62\u5f0f<\/h2>\n<ul>\n<li>SELECT select_expr,... FROM tbl_name[WHERE \u6761\u4ef6]<br \/>\n[GROUP BY {col_name|position} HAVING \u4e8c\u6b21\u7b5b\u9009]<br \/>\n[ORDER BY {col_name|position|expr} [ASC|DESC]]<br \/>\n[LIMIT \u9650\u5236\u7ed3\u679c\u96c6\u7684\u663e\u793a\u6761\u6570] <\/li>\n<li>\u67e5\u8be2\u8868\u4e2d\u6240\u6709\u8bb0\u5f55\uff1aSELECT <em> FROM tbl_name;  <\/em> \u6240\u6709\u5b57\u6bb5<\/li>\n<li>\u6307\u5b9a\u5b57\u6bb5\u7684\u4fe1\u606f\uff1a  SELECT \u5b57\u6bb5\u540d\u79f0,... FROM tbl_name<\/li>\n<li>\u5e93\u540d.\u8868\u540d\uff1aSELECT \u5b57\u6bb5\u540d\u79f0,... FROM db_name.tbl_name;<\/li>\n<li>\u7ed9\u5b57\u6bb5\u8d77\u522b\u540d\uff1aSELECT \u5b57\u6bb5\u540d\u79f0 [AS] \u522b\u540d\u540d\u79f0,... FROM db_name.tbl_name; <\/li>\n<li>\u7ed9\u6570\u636e\u8868\u8d77\u522b\u540d\uff1aSELECT \u5b57\u6bb5\u540d\u79f0 ,... FROM tbl_name [AS] \u522b\u540d;<\/li>\n<li>\u8868\u540d.\u5b57\u6bb5\u540d\u7684\uff1aSELECT tbl_name.col_name,... FROM tbl_name;<\/li>\n<\/ul>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5\u67e5\u8be2\u64cd\u4f5c\nCREATE TABLE user1(\nid INT UNSIGNED AUTO_INCREMENT KEY,\nusername VARCHAR(20) NOT NULL UNIQUE COMMENT &#039;\u7f16\u53f7&#039;,\nage TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT &#039;\u5e74\u9f84&#039;,\nsex ENUM(&#039;\u7537&#039;,&#039;\u5973&#039;,&#039;\u4fdd\u5bc6&#039;) NOT NULL DEFAULT &#039;\u4fdd\u5bc6&#039; COMMENT &#039;\u6027\u522b&#039;,\naddr VARCHAR(20) NOT NULL DEFAULT &#039;\u5317\u4eac&#039;,\nmarried TINYINT(1) NOT NULL DEFAULT 0 COMMENT &#039;0 \u4ee3\u8868\u672a\u7ed3\u5a5a\uff0c1 \u4ee3\u8868\u5df2\u5a5a&#039;,\nsalary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT &#039;\u85aa\u6c34&#039;\n)ENGINE=INNODB CHARSET=UTF8;\nINSERT user1 VALUES(1,&#039;king&#039;,23,&#039;\u7537&#039;,&#039;\u5317\u4eac&#039;,1,50000);\nINSERT user1(username,age,sex,addr,married,salary) VALUES(&#039;queen&#039;,27,&#039;\u5973&#039;,&#039;\u4e0a\u6d77&#039;,0,25000);\nINSERT user1 SET username=&#039;imooc&#039;,age=31,sex=&#039;\u5973&#039;,addr=&#039;\u5317\u4eac&#039;,salary=40000;\nINSERT user1 VALUES(NULL,&#039;\u5f20\u4e09&#039;,38,&#039;\u7537&#039;,&#039;\u4e0a\u6d77&#039;,0,15000),\n(NULL,&#039;\u5f20\u4e09\u98ce&#039;,38,&#039;\u7537&#039;,&#039;\u4e0a\u6d77&#039;,0,15000),\n(NULL,&#039;\u5f20\u5b50\u6021&#039;,39,&#039;\u5973&#039;,&#039;\u5317\u4eac&#039;,1,85000),\n(NULL,&#039;\u6c6a\u5cf0&#039;,42,&#039;\u7537&#039;,&#039;\u6df1\u5733&#039;,1,95000),\n(NULL,&#039;\u5218\u5fb7\u534e&#039;,58,&#039;\u7537&#039;,&#039;\u5e7f\u5dde&#039;,0,115000),\n(NULL,&#039;\u5434\u4ea6\u51e1&#039;,28,&#039;\u7537&#039;,&#039;\u5317\u4eac&#039;,0,75000),\n(NULL,&#039;\u5976\u8336\u59b9&#039;,18,&#039;\u5973&#039;,&#039;\u5317\u4eac&#039;,1,65000),\n(NULL,&#039;\u5218\u5609\u73b2&#039;,36,&#039;\u5973&#039;,&#039;\u5e7f\u5dde&#039;,0,15000);\n-- \u67e5\u8be2\u8868\u4e2d\u6240\u6709\u8bb0\u5f55\nSELECT * FROM user1;\n-- username,addr,age\nSELECT username,addr,age FROM user1;\n-- \u67e5\u8be2 king \u6570\u636e\u5e93\u4e0b user1 \u8868\u4e2d\u7684\u6240\u6709\u8bb0\u5f55\nSELECT * FROM king.user1;\n-- \u67e5\u8be2 user1 \u8868\u4e2d\u7684 id \u7f16\u53f7 username \u7528\u6237\u540d sex \u6027\u522b\nSELECT id AS &#039;\u7f16\u53f7&#039;,username AS &#039;\u7528\u6237\u540d&#039;, sex AS &#039;\u6027\u522b&#039;\nFROM user1;\nSELECT id age,age id,username FROM user1;\n-- \u7ed9\u8868\u8d77\u522b\u540d\nSELECT id,username FROM user1 AS u;\n-- \u6d4b\u8bd5\u8868\u540d.\u5b57\u6bb5\u540d\nSELECT user1.id,user1.username,user1.age FROM user1 ;\nSELECT u.id,u.username,u.addr,u.sex FROM user1 AS u;<\/code><\/pre>\n<h2>WHERE \u6761\u4ef6\u7b5b\u9009\u8bb0\u5f55<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091205481292.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091205481292.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091205481292.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5 WHERE \u6761\u4ef6\u7684\u6bd4\u8f83\u8fd0\u7b97\u7b26\n-- \u67e5\u8be2 id,username,age id=5 \u7684\u7528\u6237\nSELECT id,username,age FROM user1\nWHERE id=5;\nSELECT id,username,age FROM user1\nWHERE id=50;\n-- \u6dfb\u52a0 desc \u5b57\u6bb5 VARCHAR(100)\nALTER TABLE user1\nADD userDesc VARCHAR(100);\n-- \u66f4\u65b0 id&lt;=9 \u7684\u7528\u6237 userDesc=&#039;this is a test&#039;\nUPDATE user1 SET userDesc=&#039;this is a test&#039;\nWHERE id&lt;=9;\n-- \u67e5\u8be2\u7528\u6237 userDesc \u4e3a NULL \u7684\u7528\u6237\nSELECT id,username,age,userDesc FROM user1\nWHERE userDesc=NULL;\n-- \u68c0\u6d4b NULL \u503c\nSELECT id,username,age,userDesc FROM user1\nWHERE userDesc&lt;=&gt;NULL;\n-- IS [NOT] NULL \u68c0\u6d4b NULL \u503c\nSELECT id,username,age,userDesc FROM user1\nWHERE userDesc IS NULL;\n-- \u6d4b\u8bd5\u8303\u56f4 BETWEEN AND\n-- \u67e5\u8be2\u5e74\u9f84\u5728 18~30 \u4e4b\u95f4\u7684\u7528\u6237\nSELECT id,username,age,sex FROM user1\nWHERE age BETWEEN 18 AND 30;\n-- \u67e5\u8be2\u85aa\u6c34\u5728 10000~50000 \u4e4b\u95f4\u7684\u7528\u6237\nSELECT id,username,age,salary FROM user1\nWHERE salary BETWEEN 10000 AND 50000;\n-- \u67e5\u8be2\u85aa\u6c34\u4e0d\u5728 10000~50000 \u4e4b\u95f4\u7684\u7528\u6237\nSELECT id,username,age,salary FROM user1\nWHERE salary NOT BETWEEN 10000 AND 50000;\n-- \u6d4b\u8bd5\u6307\u5b9a\u96c6\u5408 IN\n-- \u67e5\u8be2\u7f16\u53f7\u4e3a 1,3,5,7,9\nSELECT id,username,age FROM user1\nWHERE id IN(1,3,5,7,9,29,45,78);\nSELECT id,username,age FROM user1\nWHERE username IN(&#039;king&#039;,&#039;queen&#039;,&#039;lily&#039;,&#039;rose&#039;);\n-- \u6d4b\u8bd5\u903b\u8f91\u8fd0\u7b97\u7b26\n-- \u67e5\u8be2\u6027\u522b\u4e3a\u7537\u5e76\u4e14\u5e74\u9f84&gt;=20 \u7684\u7528\u6237\nSELECT id,username,age,sex FROM user1\nWHERE sex=&#039;\u7537&#039; AND age&gt;=20;\n-- id&gt;=5 &amp;&amp; age&lt;=30\nSELECT id,username,age,sex FROM user1\nWHERE id&gt;=5 AND age&lt;=30;\nSELECT id,username,age,sex FROM user1\nWHERE id&gt;=5 AND age&lt;=30 AND sex=&#039;\u7537&#039;;\n-- \u8981\u6c42 sex=&#039;\u5973&#039; \u5e76\u4e14 addr=&#039;\u5317\u4eac&#039;\nSELECT id,username,age,sex,addr FROM user1\nWHERE sex=&#039;\u5973&#039; AND addr=&#039;\u5317\u4eac&#039;;\n-- \u67e5\u8be2\u85aa\u6c34\u8303\u56f4\u5728 60000~10000 \u5e76\u4e14\u6027\u522b\u4e3a\u7537 addr=&#039;\u5317\u4eac&#039;\nSELECT id,username,age,sex,salary,addr FROM user1\nWHERE salary BETWEEN 60000 AND 100000 AND sex=&#039;\u7537&#039; AND addr=&#039;\u5317\u4eac&#039;;\n-- \u67e5\u8be2 id=1 \u6216\u8005 \u7528\u6237\u540d\u4e3a queen\nSELECT id,username,age FROM user1\nWHERE id=1 OR username=&#039;queen&#039;;\n-- \u6d4b\u8bd5\u6a21\u7cca\u67e5\u8be2\nSELECT id,username,age FROM user1\nWHERE username=&#039;king&#039;;\nSELECT id,username,age FROM user1\nWHERE username LIKE &#039;king&#039;;\n-- \u8981\u6c42\u7528\u6237\u540d\u4e2d\u5305\u542b\u4e09\nSELECT id,username,age,sex FROM user1\nWHERE username LIKE &#039;%\u4e09%&#039;;\n-- \u7528\u6237\u540d\u4e2d\u5305\u542b n\nSELECT id,username,age FROM user1\nWHERE username LIKE &#039;%in%&#039;;\n-- \u8981\u6c42\u67e5\u8be2\u51fa\u59d3\u5f20\u7684\u7528\u6237\nSELECT id,username,age FROM user1\nWHERE username LIKE &#039;\u5f20%&#039;;\n-- \u67e5\u8be2\u4ee5\u98ce\u7ed3\u5c3e\u7684\u7528\u6237\nSELECT id,username,age FROM user1\nWHERE username LIKE &#039;%\u98ce&#039;;\n-- \u7528\u6237\u540d\u957f\u5ea6\u4e3a\u4e09\u4f4d\u7684\u7528\u6237\nSELECT id,username,age,sex FROM user1\nWHERE username LIKE &#039;___&#039;;\nSELECT id,username,age,sex FROM user1\nWHERE username LIKE &#039;\u5f20 _&#039;;\nSELECT id,username,age,sex FROM user1\nWHERE username LIKE &#039;\u5f20 _%&#039;;\nSELECT id,username,age,sex FROM user1\nWHERE username LIKE &#039;%K%&#039;;--king<\/code><\/pre>\n<h2>GROUP BY \u5bf9\u8bb0\u5f55\u8fdb\u884c\u5206\u7ec4<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091206512234.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091206512234.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091206512234.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<pre class=\"prettyprint linenums\" ><code>\n-- \u6d4b\u8bd5\u5206\u7ec4\n-- \u6309\u7167\u6027\u522b\u5206\u7ec4 sex\nSELECT id,username,age,sex FROM user1\nGROUP BY sex;\n-- \u6309\u7167 addr \u5206\u7ec4\nSELECT username,age,sex,addr FROM user1\nGROUP BY addr;\n-- \u6309\u7167\u6027\u522b\u5206\u7ec4\uff0c\u67e5\u8be2\u7ec4\u4e2d\u7684\u7528\u6237\u540d\u6709\u54ea\u4e9b\nSELECT GROUP_CONCAT(username),age,sex,addr FROM user1\nGROUP BY sex;\nSELECT GROUP_CONCAT(username),age,sex,GROUP_CONCAT(addr) FROM user1\nGROUP BY sex;\n-- \u6d4b\u8bd5 COUNT()\nSELECT COUNT(*) FROM user1;\nSELECT COUNT(id) FROM user1;\n-- \u6309\u7167 sex \u5206\u7ec4\uff0c\u5f97\u5230\u7528\u6237\u540d\u8be6\u60c5\uff0c\u5e76\u4e14\u5206\u522b\u7ec4\u4e2d\u7684\u603b\u4eba\u6570\nSELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1\nGROUP BY sex;\n-- \u6309\u7167 addr \u5206\u7ec4\uff0c\u5f97\u5230\u7528\u6237\u540d\u7684\u8be6\u60c5\uff0c\u603b\u4eba\u6570\uff0c\u5f97\u5230\u7ec4\u4e2d\u5e74\u9f84\u7684\u603b\u548c\uff0c\u5e74\u9f84\u7684\u6700\u5927\u503c\u3001\u6700\u5c0f\u503c\u3001\u5e73\u5747\u503c\u548c\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(age) AS sum_age,\nMAX(age) AS max_age,\nMIN(age) AS min_age,\nAVG(age) AS avg_age\nFROM user1\nGROUP BY addr;\n-- \u6309\u7167 sex \u5206\u7ec4\uff0c\u7edf\u8ba1\u7ec4\u4e2d\u603b\u4eba\u6570\u3001\u7528\u6237\u540d\u8be6\u60c5\uff0c\u5f97\u5230\u85aa\u6c34\u603b\u548c\uff0c\u85aa\u6c34\u6700\u5927\u503c\u3001\u6700\u5c0f\u503c\u3001\u5e73\u5747\u503c\nSELECT sex,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(salary) AS sum_salary,\nMAX(salary) AS max_salary,\nMIN(salary) AS min_salary,\nAVG(salary) AS avg_salary\nFROM user1\nGROUP BY sex;\n--\u914d\u5408 WITH ROLLUP \u5173\u952e\u4f7f\u7528 \u4f1a\u5728\u8bb0\u5f55\u672b\u5c3e\u6dfb\u52a0\u4e00\u6761\u8bb0\u5f55\uff0c\u662f\u4e0a\u9762\u6240\u6709\u8bb0\u5f55\u7684\u603b\u548c\nSELECT GROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers\nFROM user1\nGROUP BY sex\nWITH ROLLUP;\n-- \u6309\u7167\u5b57\u6bb5\u7684\u4f4d\u7f6e\u6765\u5206\u7ec4\nSELECT id,sex,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(salary) AS sum_salary,\nMAX(salary) AS max_salary,\nMIN(salary) AS min_salary,\nAVG(salary) AS avg_salary\nFROM user1\nGROUP BY 2;\n-- \u67e5\u8be2 age&gt;=30 \u7684\u7528\u6237\u5e76\u4e14\u6309\u7167 sex \u5206\u7ec4\nSELECT sex,GROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers\nFROM user1\nWHERE age&gt;=30\nGROUP BY sex;\n-- \u6309\u7167 addr \u5206\u7ec4\uff0c\u7edf\u8ba1\u603b\u4eba\u6570\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers\nFROM user1\nGROUP BY addr;\n-- \u5bf9\u4e8e\u5206\u7ec4\u7ed3\u679c\u8fdb\u884c\u4e8c\u6b21\u7b5b\u9009\uff0c\u6761\u4ef6\u662f\u7ec4\u4e2d\u603b\u4eba\u6570&gt;=3\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers\nFROM user1\nGROUP BY addr\nHAVING COUNT(*)&gt;=3;\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers\nFROM user1\nGROUP BY addr\nHAVING totalUsers&gt;=3;\n-- \u6309\u7167 addr \u5206\u7ec4\uff0c\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(salary) AS sum_salary,\nMAX(salary) AS max_salary,\nMIN(salary) AS min_salary,\nAVG(salary) AS avg_salary\nFROM user1\nGROUP BY addr;\n-- \u8981\u6c42\u5e73\u5747\u85aa\u6c34&gt;=40000\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(salary) AS sum_salary,\nMAX(salary) AS max_salary,\nMIN(salary) AS min_salary,\nAVG(salary) AS avg_salary\nFROM user1\nGROUP BY addr\nHAVING avg_salary&gt;=40000;<\/code><\/pre>\n<h2>ORDER BY \u5b9e\u73b0\u6392\u5e8f\u6548\u679c<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091207131879.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091207131879.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091207131879.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5\u6392\u5e8f\n-- \u6309\u7167 id \u964d\u5e8f\u6392\u5217\nSELECT id,username,age\nFROM user1\nORDER BY id DESC;\n-- \u6309\u7167 age \u5347\u5e8f\nSELECT id,username,age\nFROM user1\nORDER BY age ;\n-- \u6309\u7167\u591a\u4e2a\u5b57\u6bb5\u6392\u5e8f\nSELECT id,username,age\nFROM user1\nORDER BY age ASC,id ASC;\n-- \u6d4b\u8bd5\u6761\u4ef6+\u6392\u5e8f\nSELECT id,username,age\nFROM user1\nWHERE age&gt;=30;\nSELECT id,username,age\nFROM user1\nWHERE age&gt;=30\nORDER BY age DESC;\n-- \u5b9e\u73b0\u968f\u673a\u8bb0\u5f55\nSELECT id,username,age\nFROM user1\nORDER BY RAND();<\/code><\/pre>\n<h2>LIMT \u9650\u5236\u7ed3\u679c\u96c6\u7684\u663e\u793a\u6761\u6570<\/h2>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5 LIMIT \u8bed\u53e5\n-- \u663e\u793a\u7ed3\u679c\u96c6\u7684\u524d 5 \u6761\u8bb0\u5f55\nSELECT id,username,age,sex\nFROM user1\nLIMIT 5;\nSELECT id,username,age,sex\nFROM user1\nLIMIT 0,5;\n-- \u663e\u793a\u524d 3 \u6761\u8bb0\u5f55\nSELECT id,username,age,sex\nFROM user1\nLIMIT 0,3;\nSELECT id,username,age,sex\nFROM user1\nLIMIT 3,3;--3 \u4e0b\u6807 3 \u6761\u6570\u636e\n-- \u66f4\u65b0\u524d 3 \u6761\u8bb0\u5f55\uff0c\u5c06 age+5\nUPDATE user1 SET age=age+5 LIMIT 3;\n-- \u6309\u7167 id \u964d\u5e8f\u6392\u5217\uff0c\u66f4\u65b0\u524d\u4e09\u6761\u8bb0\u5f55\uff0c\u5c06 age-10\nUPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3;\n-- \u5220\u9664\u524d\u4e09\u6761\u8bb0\u5f55\nDELETE FROM user1\nLIMIT 3;\n--\u6309\u7167 id \u964d\u5e8f\u6392\u5217,\u5220\u9664\u524d\u4e09\u6761\u8bb0\u5f55\nDELETE FROM user1\nORDER BY id DESC\nLIMIT 3;\n-- \u6d4b\u8bd5\u5b8c\u6574 SELECT \u8bed\u53e5\u7684\u5f62\u5f0f\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(age) AS sum_age,\nMAX(age) AS max_age,\nMIN(age) AS min_age,\nAVG(age) AS avg_age\nFROM user1\nWHERE id&gt;=2\nGROUP BY addr;\n--\u5bf9\u5206\u7ec4\u7ed3\u679c\u8fdb\u884c\u4e8c\u6b21\u7b5b\u9009\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(age) AS sum_age,\nMAX(age) AS max_age,\nMIN(age) AS min_age,\nAVG(age) AS avg_age\nFROM user1\nWHERE id&gt;=2\nGROUP BY addr\nHAVING totalUsers&gt;=2;\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(age) AS sum_age,\nMAX(age) AS max_age,\nMIN(age) AS min_age,\nAVG(age) AS avg_age\nFROM user1\nWHERE id&gt;=2\nGROUP BY addr\nHAVING totalUsers&gt;=2\nORDER BY totalUsers ASC;\nSELECT addr,\nGROUP_CONCAT(username) AS usersDetail,\nCOUNT(*) AS totalUsers,\nSUM(age) AS sum_age,\nMAX(age) AS max_age,\nMIN(age) AS min_age,\nAVG(age) AS avg_age\nFROM user1\nWHERE id&gt;=2\nGROUP BY addr\nHAVING totalUsers&gt;=2\nORDER BY totalUsers ASC\nLIMIT 0,2;<\/code><\/pre>\n<h2>MySQL \u4e2d\u7684\u591a\u8868\u8054\u67e5<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091207541965.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091207541965.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091207541965.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<ul>\n<li>\u5185\u8fde\u63a5\u7528\u7684\u591a<\/li>\n<\/ul>\n<pre class=\"prettyprint linenums\" ><code>CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET &#039;UTF8&#039;;\nUSE test2;\nCREATE TABLE emp(\nid INT UNSIGNED AUTO_INCREMENT KEY,\nusername VARCHAR(20) NOT NULL UNIQUE COMMENT &#039;\u7f16\u53f7&#039;,\nage TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT &#039;\u5e74\u9f84&#039;,\nsex ENUM(&#039;\u7537&#039;,&#039;\u5973&#039;,&#039;\u4fdd\u5bc6&#039;) NOT NULL DEFAULT &#039;\u4fdd\u5bc6&#039; COMMENT &#039;\u6027\u522b&#039;,\naddr VARCHAR(20) NOT NULL DEFAULT &#039;\u5317\u4eac&#039;,\ndepId TINYINT UNSIGNED NOT NULL COMMENT &#039;\u90e8\u95e8\u5bf9\u5e94\u7684\u7f16\u53f7&#039;\n)ENGINE=INNODB CHARSET=UTF8;\nINSERT emp(username,age,depId) VALUES(&#039;king&#039;,24,1),\n(&#039;queen&#039;,25,2),\n(&#039;imooc&#039;,26,1),\n(&#039;lily&#039;,27,1),\n(&#039;rose&#039;,28,3),\n(&#039;john&#039;,29,3);\nINSERT emp(username,age,depId)  VALUES(&#039;\u6d4b\u8bd5\u7528\u6237&#039;,39,6);\nCREATE TABLE dep(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\ndepName VARCHAR(50) NOT NULL UNIQUE,\ndepDesc VARCHAR(100) NOT NULL DEFAULT &#039;&#039;\n)ENGINE=INNODB CHARSET=UTF8;\nINSERT dep(depName,depDesc) VALUES(&#039;PHP \u6559\u5b66\u90e8&#039;,&#039;\u7814\u53d1 PHP \u8bfe\u4ef6&#039;),\n(&#039;JAVA \u6559\u5b66\u90e8&#039;,&#039;\u7814\u53d1 JAVA \u8bfe\u4ef6&#039;),\n(&#039;WEB \u524d\u7aef\u6559\u5b66\u90e8&#039;,&#039;\u7814\u53d1 WEB \u524d\u7aef\u8bfe\u4ef6&#039;),\n(&#039;IOS \u6559\u5b66\u90e8&#039;,&#039;\u7814\u53d1 IOS \u8bfe\u4ef6&#039;);\n-- \u9700\u6c42\uff1a\u67e5\u8be2 emp id username age  \u90e8\u95e8\u540d\u79f0 dep depName\nSELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;\nSELECT e.id,e.username,e.age,d.depName\nFROM emp AS e\nINNER JOIN dep AS d\nON e.depId=d.id;\n-- \u67e5\u8be2 emp id username age addr dep id depName depDesc\nSELECT e.id,e.username,e.age,e.addr,\nd.id,d.depName,d.depDesc\nFROM dep AS d\nJOIN emp AS e\nON d.id=e.depId;\n-- \u6d4b\u8bd5\u5de6\u5916\u8fde\u63a5\nSELECT e.id,e.username,e.age,d.depName,d.depDesc\nFROM emp AS e\nLEFT OUTER JOIN dep AS d\nON e.depId=d.id;\n-- \u6d4b\u8bd5\u53f3\u5916\u8fde\u63a5\nSELECT e.id,e.username,e.age,d.depName,d.depDesc\nFROM emp AS e\nRIGHT JOIN dep AS d\nON e.depId=d.id;<\/code><\/pre>\n<h2>\u591a\u8868\u8054\u67e5\u7684\u64cd\u4f5c<\/h2>\n<pre class=\"prettyprint linenums\" ><code>-- \u521b\u5efa\u7ba1\u7406\u5458\u8868\nCREATE TABLE user(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\nusername VARCHAR(20) NOT NULL UNIQUE,\nemail VARCHAR(50) NOT NULL DEFAULT &#039;382771946@qq.com&#039;,\nproName VARCHAR(10) NOT NULL DEFAULT &#039;\u5317\u4eac&#039;\n);\nINSERT user(username,proName) VALUES(&#039;a&#039;,&#039;\u5317\u4eac&#039;),\n(&#039;b&#039;,&#039;\u54c8\u5c14\u6ee8&#039;),\n(&#039;c&#039;,&#039;\u4e0a\u6d77&#039;),\n(&#039;d&#039;,&#039;\u6df1\u5733&#039;),\n(&#039;e&#039;,&#039;\u5e7f\u5dde&#039;),\n(&#039;f&#039;,&#039;\u91cd\u542f&#039;);\n-- \u521b\u5efa\u7701\u4efd\u8868\nCREATE TABLE provinces(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\nproName VARCHAR(10) NOT NULL UNIQUE\n);\nINSERT provinces(proName) VALUES(&#039;\u5317\u4eac&#039;),\n(&#039;\u4e0a\u6d77&#039;),\n(&#039;\u6df1\u5733&#039;);\n--\nCREATE TABLE user(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\nusername VARCHAR(20) NOT NULL UNIQUE,\nemail VARCHAR(50) NOT NULL DEFAULT &#039;382771946@qq.com&#039;,\nproId TINYINT UNSIGNED NOT NULL\n);\nINSERT user(username,proId) VALUES(&#039;a&#039;,1);\nINSERT user(username,proId) VALUES(&#039;b&#039;,1);\nINSERT user(username,proId) VALUES(&#039;c&#039;,1);\nINSERT user(username,proId) VALUES(&#039;d&#039;,2);\nINSERT user(username,proId) VALUES(&#039;e&#039;,3);\nINSERT user(username,proId) VALUES(&#039;f&#039;,1);\nINSERT user(username,proId) VALUES(&#039;g&#039;,1);\n-- \u67e5\u8be2 user id ,username provinces proName\nSELECT u.id,u.username,p.proName\nFROM user AS u\nJOIN provinces AS p\nON u.proId=p.id;\n--\u56db\u8868\u4e4b\u95f4\u4e09\u8868\u3002\u3002\u8054\u67e5\n-- \u521b\u5efa\u7701\u4efd\u8868\nCREATE TABLE provinces(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\nproName VARCHAR(10) NOT NULL UNIQUE\n);\n-- \u7ba1\u7406\u5458 admin id username email proId\nCREATE TABLE admin(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\nusername VARCHAR(20) NOT NULL UNIQUE,\nemail VARCHAR(50) NOT NULL DEFAULT &#039;382771946@qq.com&#039;,\nproId TINYINT UNSIGNED NOT NULL\n);\nINSERT admin(username,proId) VALUES(&#039;king&#039;,1);\nINSERT admin(username,proId) VALUES(&#039;queen&#039;,2);\n-- \u5546\u54c1\u5206\u7c7b cate id cateName cateDesc\nCREATE TABLE cate(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\ncateName VARCHAR(50) NOT NULL UNIQUE,\ncateDesc VARCHAR(100) NOT NULL DEFAULT &#039;&#039;\n);\nINSERT cate(cateName) VALUES(&#039;\u6bcd\u5a74&#039;);\nINSERT cate(cateName) VALUES(&#039;\u670d\u88c5&#039;);\nINSERT cate(cateName) VALUES(&#039;\u7535\u5b50&#039;);\n-- \u5546\u54c1\u8868 products id productName, price,cateId\nCREATE TABLE products(\nid INT UNSIGNED AUTO_INCREMENT KEY,\nproductName VARCHAR(50) NOT NULL UNIQUE,\nprice FLOAT(8,2) NOT NULL DEFAULT 0,\ncateId TINYINT UNSIGNED NOT NULL,\nadminId TINYINT UNSIGNED NOT NULL\n);\nINSERT products(productName,price,cateId,adminId)\nVALUES(&#039;iphone9&#039;,9888,3,1),\n(&#039;adidas&#039;,388,2,2),\n(&#039;nike&#039;,888,2,2),\n(&#039;\u5976\u74f6&#039;,288,1,1);\n-- \u67e5\u8be2 products id productName price --- cate cateName\nSELECT p.id,p.productName,p.price,c.cateName\nFROM products AS p\nJOIN cate AS c\nON p.cateId=c.id;\n-- \u67e5\u8be2\u7ba1\u7406\u5458 id username email -- provinces proName\nSELECT a.id,a.username,a.email,p.proName\nFROM admin AS a\nJOIN provinces AS p\nON a.proId=p.id;\n-- \u67e5\u8be2 products id productName price\n-- cate cateName\n-- admin username email\nSELECT p.id,p.productName,p.price,c.cateName,a.username,a.email\nFROM products AS p\nJOIN admin AS a\nON p.adminId=a.id\nJOIN cate AS c\nON p.cateId=c.id\nWHERE p.price&lt;1000\nORDER BY p.price DESC\nLIMIT 0,2;\n-- products id productName price\n-- cate cateName\n-- admin username email\n-- provinces proName<\/code><\/pre>\n<h2>\u5916\u952e\u7ea6\u675f\u7684\u4f7f\u7528<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091209191737.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091209191737.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091209191737.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5\u5916\u952e\n-- \u65b0\u95fb\u5206\u7c7b\u8868 news_cate\nCREATE TABLE news_cate(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\ncateName VARCHAR(50) NOT NULL UNIQUE,\ncateDesc VARCHAR(100) NOT NULL DEFAULT &#039;&#039;\n);\nINSERT news_cate(cateName) VALUES(&#039;\u56fd\u5185\u65b0\u95fb&#039;),\n(&#039;\u56fd\u9645\u65b0\u95fb&#039;),\n(&#039;\u5a31\u4e50\u65b0\u95fb&#039;),\n(&#039;\u4f53\u80b2\u65b0\u95fb&#039;);\n-- \u65b0\u95fb\u8868 news\nCREATE TABLE news(\nid INT UNSIGNED AUTO_INCREMENT KEY,\ntitle VARCHAR(100) NOT NULL UNIQUE,\ncontent VARCHAR(1000) NOT NULL,\ncateId TINYINT UNSIGNED NOT NULL\n);\nINSERT news(title,content,cateId) VALUES(&#039;a1&#039;,&#039;aaaa1&#039;,1),\n(&#039;a2&#039;,&#039;aaaa2&#039;,1),\n(&#039;a3&#039;,&#039;aaaa3&#039;,4),\n(&#039;a4&#039;,&#039;aaaa4&#039;,2),\n(&#039;a5&#039;,&#039;aaaa5&#039;,3);\n-- \u67e5\u8be2 news id title content\n-- news_cate cateName\nSELECT n.id,n.title,n.content,c.cateName\nFROM news AS n\nJOIN news_cate AS c\nON n.cateId=c.id;\nINSERT news(title,content,cateId) VALUES(&#039;a6&#039;,&#039;aaaa6&#039;,45);\n-- ------------\u6dfb\u52a0\u5916\u952e\n-- \u65b0\u95fb\u5206\u7c7b\u8868 news_cate\nCREATE TABLE news_cate(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\ncateName VARCHAR(50) NOT NULL UNIQUE,\ncateDesc VARCHAR(100) NOT NULL DEFAULT &#039;&#039;\n)ENGINE=INNODB;\n-- \u65b0\u95fb\u8868 news\nCREATE TABLE news(\nid INT UNSIGNED AUTO_INCREMENT KEY,\ntitle VARCHAR(100) NOT NULL UNIQUE,\ncontent VARCHAR(1000) NOT NULL,\ncateId TINYINT UNSIGNED NOT NULL,\nFOREIGN KEY(cateId) REFERENCES news_cate(id)\n)ENGINE=INNODB;\nINSERT news_cate(cateName) VALUES(&#039;\u56fd\u5185\u65b0\u95fb&#039;),\n(&#039;\u56fd\u9645\u65b0\u95fb&#039;),\n(&#039;\u5a31\u4e50\u65b0\u95fb&#039;),\n(&#039;\u4f53\u80b2\u65b0\u95fb&#039;);\nINSERT news(title,content,cateId) VALUES(&#039;a1&#039;,&#039;aaaa1&#039;,1),\n(&#039;a2&#039;,&#039;aaaa2&#039;,1),\n(&#039;a3&#039;,&#039;aaaa3&#039;,4),\n(&#039;a4&#039;,&#039;aaaa4&#039;,2),\n(&#039;a5&#039;,&#039;aaaa5&#039;,3);\n-- \u6d4b\u8bd5\u975e\u6cd5\u8bb0\u5f55\nINSERT news(title,content,cateId) VALUES(&#039;b1&#039;,&#039;bbbb1&#039;,8);\n-- \u6d4b\u8bd5\u5220\u9664\u7236\u8868\u4e2d\u7684\u8bb0\u5f55 \u548c\u5220\u9664\u7236\u8868\nDELETE FROM news_cate WHERE id=1;\nUPDATE news_cate SET id=10 WHERE id=1;\nINSERT news_cate(cateName) VALUES(&#039;\u6559\u80b2\u65b0\u95fb&#039;);\n-- \u5c06\u6559\u80b2\u65b0\u95fb \u6539\u6210\u6559\u80b2\nUPDATE news_cate SET cateName=&#039;\u6559\u80b2&#039; WHERE id=5;\nUPDATE news_cate SET id=50 WHERE cateName=&#039;\u6559\u80b2&#039;;<\/code><\/pre>\n<h2>\u52a8\u6001\u521b\u5efa\u5916\u952e\u53ca\u5220\u9664\u5916\u952e\u64cd\u4f5c<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212083822.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212083822.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212083822.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<ul>\n<li>RESTRICT \u540c no action, \u90fd\u662f\u7acb\u5373\u68c0\u67e5\u5916\u952e\u7ea6\u675f\uff0c\u62d2\u7edd\u5bf9\u7236\u8868\u505a\u66f4\u65b0\u6216\u8005\u5220\u9664\u64cd\u4f5c<\/li>\n<\/ul>\n<pre class=\"prettyprint linenums\" ><code>-- ------\u6dfb\u52a0\u5916\u952e\u540d\u79f0\n-- \u65b0\u95fb\u5206\u7c7b\u8868 news_cate\nCREATE TABLE news_cate(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\ncateName VARCHAR(50) NOT NULL UNIQUE,\ncateDesc VARCHAR(100) NOT NULL DEFAULT &#039;&#039;\n)ENGINE=INNODB;\n-- \u65b0\u95fb\u8868 news\nCREATE TABLE news(\nid INT UNSIGNED AUTO_INCREMENT KEY,\ntitle VARCHAR(100) NOT NULL UNIQUE,\ncontent VARCHAR(1000) NOT NULL,\ncateId TINYINT UNSIGNED NOT NULL,\nCONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)\n)ENGINE=INNODB;\n-- \u5220\u9664\u5916\u952e\nALTER TABLE news\nDROP FOREIGN KEY cateId_fk_newsCate;\n-- \u6dfb\u52a0\u5916\u952e\nALTER TABLE news\nADD FOREIGN KEY(cateId) REFERENCES news_cate(id);\n-- \u5220\u9664\u5916\u952e\nALTER TABLE news\nDROP FOREIGN KEY news_ibfk_1;\nALTER TABLE news\nADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);\nINSERT news_cate(cateName) VALUES(&#039;\u56fd\u5185\u65b0\u95fb&#039;),\n(&#039;\u56fd\u9645\u65b0\u95fb&#039;),\n(&#039;\u5a31\u4e50\u65b0\u95fb&#039;),\n(&#039;\u4f53\u80b2\u65b0\u95fb&#039;);\nINSERT news(title,content,cateId) VALUES(&#039;a1&#039;,&#039;aaaa1&#039;,1),\n(&#039;a2&#039;,&#039;aaaa2&#039;,1),\n(&#039;a3&#039;,&#039;aaaa3&#039;,4),\n(&#039;a4&#039;,&#039;aaaa4&#039;,2),\n(&#039;a5&#039;,&#039;aaaa5&#039;,8);--\u5220\u9664\u4e86\nDELETE FROM news WHERE id=5;\nALTER TABLE news\nADD FOREIGN KEY(cateId) REFERENCES news_cate(id);\nSHOW CREATE TABLE news;\n-- \u6307\u5b9a\u7ea7\u8054\u64cd\u4f5c DELETE CASCADE UPDATE CASCADE\nALTER TABLE news\nADD FOREIGN KEY(cateId) REFERENCES news_cate(id)\nON DELETE CASCADE ON UPDATE CASCADE;\nSHOW CREATE TABLE news;-- \u591a\u4e86\u4e00\u4e2a ON DELETE CASCADE ON UPDATE CASCADE\nSELECT * FROM news_cate;\nSELECT * FROM news;\nUPDATE news_cate SET id=11 WHERE id=1;\nSELECT * FROM news_cate;-- 1 \u53d8\u4e3a 11 news \u7b2c 1\u30012 \u7684 cateId \u90fd\u53d8\u4e86<\/code><\/pre>\n<h2>\u5b50\u67e5\u8be2\u7684\u4f7f\u7528<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212422321.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212422321.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212422321.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5\u5b50\u67e5\u8be2\n-- \u6d4b\u8bd5\u7531 IN \u5f15\u53d1\u7684\u5b50\u67e5\u8be2\nSELECT * FROM emp\nWHERE depId IN (SELECT id FROM dep);\nSELECT * FROM emp\nWHERE depId NOT IN (SELECT id FROM dep);\n-- \u5b66\u5458 stu\nCREATE TABLE stu(\nid TINYINT UNSIGNED AUTO_INCREMENT KEY,\nusername VARCHAR(20) NOT NULL UNIQUE,\nscore TINYINT UNSIGNED NOT NULL\n);\nINSERT stu(username,score) VALUES(&#039;king&#039;,95),\n(&#039;queen&#039;,75),\n(&#039;zhangsan&#039;,69),\n(&#039;lisi&#039;,78),\n(&#039;wangwu&#039;,87),\n(&#039;zhaoliu&#039;,88),\n(&#039;tianqi&#039;,98),\n(&#039;ceshi&#039;,99),\n(&#039;tiancai&#039;,50);\n-- \u5206\u6570\u7ea7\u522b level\nCREATE TABLE level(\nid tinyint UNSIGNED AUTO_INCREMENT KEY,\nscore TINYINT UNSIGNED COMMENT &#039;\u5206\u6570&#039;\n);\nINSERT level(score) VALUES(90),(80),(70);\n-- \u67e5\u8be2\u51fa\u6210\u7ee9\u4f18\u79c0\u7684\u5b66\u5458\nSELECT score FROM level WHERE id=1;\nSELECT id,username,score FROM stu\nWHERE score&gt;=(SELECT score FROM level WHERE id=1);\n-- \u67e5\u8be2\u51fa\u6ca1\u6709\u5f97\u5230\u8bc4\u7ea7\u7684\u5b66\u5458\nSELECT id,username,score FROM stu\nWHERE score&lt;=(SELECT score FROM level WHERE id=3);\n-- \u7531 EXISTS \u5f15\u53d1\u7684\u5b50\u67e5\u8be2\nSELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10);\nSELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=1);<\/code><\/pre>\n<h2>\u5e26\u6709 ANY\u3001SOME\u3001ALL \u5173\u952e\u5b57\u7684\u5b50\u67e5\u8be2<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212441380.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212441380.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091212441380.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<pre class=\"prettyprint linenums\" ><code>-- \u5e26\u6709 ANY SOME ALL \u5173\u952e\u5b57\u7684\u5b50\u67e5\u8be2\n-- \u5927\u4e8e\u91cc\u9762\u7684\u6700\u5c0f\u503c\nSELECT * FROM stu\nWHERE score&gt;= ANY(SELECT score FROM level);\nSELECT * FROM stu\nWHERE score&gt;= SOME(SELECT score FROM level);\nSELECT * FROM stu\nWHERE score&gt;= ALL(SELECT score FROM level);\nSELECT * FROM stu\nWHERE score&lt; ALL(SELECT score FROM level);\nSELECT * FROM stu\nWHERE score=ANY(SELECT score FROM level);\nSELECT * FROM stu\nWHERE score!=ALL(SELECT score FROM level);<\/code><\/pre>\n<h2>\u8054\u5408\u67e5\u8be2\u7684\u4f7f\u7528<\/h2>\n<p><a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091213185925.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091213185925.png\"  alt=\"\" \/><\/a><br \/>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091213185925.png\" alt=\"\" \/><\/a><br \/><\/noscript>\n<a href=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091213143819.png\" rel=\"box\" class=\"fancybox\"><img data-original=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091213143819.png\"  alt=\"\" \/><\/a><\/p>\n<noscript><img decoding=\"async\" src=\"https:\/\/blog.odjbinail.cn\/wp-content\/uploads\/2021\/09\/2021091213143819.png\" alt=\"\" \/><\/a><\/p><\/noscript>\n<pre class=\"prettyprint linenums\" ><code>-- \u521b\u5efa\u4e00\u4e2a user1 \u8868,id username\nCREATE TABLE user1(\n    id int UNSIGNED AUTO_INCREMENT KEY,\n    username VARCHAR(20)\n)SELECT id,username FROM emp;\nDESC user1;\nSELECT * FROM user1;\n-- \u5c06 user \u8868\u4e2d\u7684\u7528\u6237\u540d\u5199\u5165\u5230 user1 \u8868\u4e2d\nINSERT user1(username) SELECT username FROM user;\nCREATE TABLE user2 LIKE user1;\nINSERT user2 SELECT id,username FROM user1;\nSELECT * FROM user1;\nSELECT * FROM STU;\n-- \u5c06 stu \u8868\u4e2d\u7684 tiancai \u7528\u6237\u540d\u6dfb\u52a0\u5230 user2 \u8868\u4e2d,user2 \u548c user1 \u4e00\u6837\u7684\nINSERT user2 SET username=(SELECT username FROM stu WHERE id=9);\n-- \u53bb\u6389\u5b57\u6bb5\u7684\u91cd\u590d\u503c\nSELECT DISTINCT(username) FROM user2;\n-- \u5c06 user1 \u548c user2 \u6570\u636e\u5408\u5e76\u5230\u4e00\u8d77\nSELECT * FROM user1\nUNION\nSELECT * FROM user2;<\/code><\/pre>\n<h2>\u65e0\u9650\u7ea7\u5206\u7c7b\u6570\u636e\u8868\u7684\u8bbe\u8ba1\u53ca\u5b9e\u73b0<\/h2>\n<ul>\n<li>\u81ea\u8eab\u8fde\u63a5\u67e5\u8be2   \u65e0\u9650\u7ea7\u5206\u7c7b\u7684\u5b9e\u73b0\u5f62\u5f0f<\/li>\n<\/ul>\n<pre class=\"prettyprint linenums\" ><code>-- \u6d4b\u8bd5\u81ea\u8eab\u8fde\u63a5\nCREATE TABLE cate(\n    id SMALLINT UNSIGNED AUTO_INCREMENT KEY,\n    cateName VARCHAR(100) NOT NULL UNIQUE,\n    pId SMALLINT UNSIGNED NOT NULL DEFAULT 0\n);\nINSERT cate(cateName,pId) VALUES(&#039;\u670d\u88c5&#039;,0);\nINSERT cate(cateName,pId) VALUES(&#039;\u6570\u7801&#039;,0);\nINSERT cate(cateName,pId) VALUES(&#039;\u7bb1\u5305&#039;,0);\nINSERT cate(cateName,pId) VALUES(&#039;\u7537\u88c5&#039;,1);\nINSERT cate(cateName,pId) VALUES(&#039;\u5973\u88c5&#039;,1);\nINSERT cate(cateName,pId) VALUES(&#039;\u5185\u8863&#039;,1);\nINSERT cate(cateName,pId) VALUES(&#039;\u7535\u89c6&#039;,2);\nINSERT cate(cateName,pId) VALUES(&#039;\u51b0\u7bb1&#039;,2);\nINSERT cate(cateName,pId) VALUES(&#039;\u6d17\u8863\u673a&#039;,2);\nINSERT cate(cateName,pId) VALUES(&#039;\u7231\u9a6c\u4ed5&#039;,3);\nINSERT cate(cateName,pId) VALUES(&#039;LV&#039;,3);\nINSERT cate(cateName,pId) VALUES(&#039;GUCCI&#039;,3);\nINSERT cate(cateName,pId) VALUES(&#039;\u5939\u514b&#039;,4);\nINSERT cate(cateName,pId) VALUES(&#039;\u886c\u886b&#039;,4);\nINSERT cate(cateName,pId) VALUES(&#039;\u88e4\u5b50&#039;,4);\nINSERT cate(cateName,pId) VALUES(&#039;\u6db2\u6676\u7535\u89c6&#039;,10);\nINSERT cate(cateName,pId) VALUES(&#039;\u7b49\u79bb\u5b50\u7535\u89c6&#039;,10);\nINSERT cate(cateName,pId) VALUES(&#039;\u80cc\u6295\u7535\u89c6&#039;,10);\n-- \u67e5\u8be2\u6240\u6709\u7684\u5206\u7c7b\u4fe1\u606f\uff0c\u5e76\u4e14\u5f97\u5230\u5176\u7236\u5206\u7c7b\nSELECT s.id,s.cateName AS sCateName,p.cateName  AS pCateName\nFROM cate AS s\nLEFT JOIN cate AS p\nON s.pId=p.id;\n-- \u67e5\u8be2\u6240\u6709\u7684\u5206\u7c7b\u53ca\u5176\u5b50\u5206\u7c7b\nSELECT p.id,p.cateName  AS pCateName,s.cateName AS sCateName\nFROM cate AS s\nRIGHT JOIN cate AS p\nON s.pId=p.id;\n-- \u67e5\u8be2\u6240\u6709\u7684\u5206\u7c7b\u5e76\u4e14\u5f97\u5230\u5b50\u5206\u7c7b\u7684\u6570\u76ee\nSELECT p.id,p.cateName  AS pCateName,COUNT(s.cateName) AS count\nFROM cate AS s\nRIGHT JOIN cate AS p\nON s.pId=p.id\nGROUP BY p.cateName\nORDER BY id ASC;\nsCate \u8868\nid   | cateName       | pId |\n+----+-----------------+-----+\n|  1 | \u670d\u88c5            |   0 |\n|  2 | \u6570\u7801            |   0 |\n|  3 | \u73a9\u5177            |   0 |\n|  4 | \u7537\u88c5            |   1 |\n|  5 | \u5973\u88c5            |   1 |\n|  6 | \u5185\u8863            |   1 |\n| 10 | \u7535\u89c6            |   2 |\n| 11 | \u51b0\u7bb1            |   2 |\n| 12 | \u6d17\u8863\u673a          |   2 |\n| 13 | \u7231\u9a6c\u4ed5          |   3 |\n| 14 | LV             |   3 |\n| 15 | GUCCI          |   3 |\n| 16 | \u5939\u514b            |   4 |\n| 17 | \u886c\u886b            |   4 |\n| 18 | \u88e4\u5b50            |   4 |\n| 19 | \u6db2\u6676\u7535\u89c6         |  10 |\n| 20 | \u7b49\u79bb\u5b50\u7535\u89c6       |  10 |\n| 21 | \u80cc\u6295\u7535\u89c6         |  10 |\npCate\nid | cateName        | pId |\n+----+-----------------+-----+\n|  1 | \u670d\u88c5            |   0 |\n|  2 | \u6570\u7801            |   0 |\n|  3 | \u73a9\u5177            |   0 |\n|  4 | \u7537\u88c5            |   1 |\n|  5 | \u5973\u88c5            |   1 |\n|  6 | \u5185\u8863            |   1 |\n| 10 | \u7535\u89c6            |   2 |\n| 11 | \u51b0\u7bb1            |   2 |\n| 12 | \u6d17\u8863\u673a          |   2 |\n| 13 | \u7231\u9a6c\u4ed5          |   3 |\n| 14 | LV              |   3 |\n| 15 | GUCCI           |   3 |\n| 16 | \u5939\u514b            |   4 |\n| 17 | \u886c\u886b            |   4 |\n| 18 | \u88e4\u5b50            |   4 |\n| 19 | \u6db2\u6676\u7535\u89c6        |  10 |\n| 20 | \u7b49\u79bb\u5b50\u7535\u89c6      |  10 |\n| 21 | \u80cc\u6295\u7535\u89c6        |  10  | <\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u63d2\u5165\u8bb0\u5f55\u64cd\u4f5c \u6dfb\u52a0\u8bb0\u5f55\uff1aINSERT [INTO] tbl_name[(col_name,.\u2026)] {VALU [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-1429","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=\/wp\/v2\/posts\/1429","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1429"}],"version-history":[{"count":0,"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=\/wp\/v2\/posts\/1429\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1429"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1429"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.odjbinail.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}