{"id":175,"date":"2026-01-31T15:43:23","date_gmt":"2026-01-31T07:43:23","guid":{"rendered":"http:\/\/47.100.165.17\/wordpress\/?p=175"},"modified":"2026-01-31T15:46:15","modified_gmt":"2026-01-31T07:46:15","slug":"%e5%bf%85%e5%a4%87sql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8%ef%bc%9a%e4%bb%8e%e5%9f%ba%e7%a1%80%e5%88%b0%e5%ae%9e%e6%88%98","status":"publish","type":"post","link":"https:\/\/yunyanglib.cn\/?p=175","title":{"rendered":"\u3010MySQL\u3011\u5fc5\u5907SQL\u8bed\u53e5\u5927\u5168\uff1a\u4ece\u57fa\u7840\u5230\u5b9e\u6218"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"hK5UC\">1.1 \u5e93\u4e0e\u8868\u7684\u521b\u5efa\u65b9\u6cd5<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"GvqNN\">1.1.1 \u5e93\u8868\u5b57\u6bb5\u7684\u6838\u5fc3\u6784\u6210<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ued449003\">\u6570\u636e\u5e93\u8868\u7684\u5b57\u6bb5\u662f\u6570\u636e\u5b58\u50a8\u7684\u57fa\u672c\u5355\u5143\uff0c\u901a\u5e38\u5305\u542b\u4e09\u8981\u7d20\uff1a\u5b57\u6bb5\u540d\uff08\u6807\u8bc6\u6570\u636e\u542b\u4e49\uff09\u3001\u5b57\u6bb5\u7c7b\u578b\uff08\u9650\u5236\u6570\u636e\u683c\u5f0f\uff0c\u5982int\u3001varchar\u7b49\uff09\u3001\u7ea6\u675f\u6761\u4ef6\uff08\u5982\u4e3b\u952e\u3001\u9ed8\u8ba4\u503c\u3001\u975e\u7a7a\u7b49\uff09\u3002\u5408\u7406\u8bbe\u8ba1\u5b57\u6bb5\u662f\u4fdd\u8bc1\u6570\u636e\u5b8c\u6574\u6027\u7684\u57fa\u7840\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"bZI7N\">1.1.2 SQL\u7684\u4e09\u5927\u5206\u7c7b<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>DDL\uff08Data Definition Language\uff0c\u6570\u636e\u5b9a\u4e49\u8bed\u8a00\uff09<\/strong>\uff1a\u7528\u4e8e\u521b\u5efa\u3001\u4fee\u6539\u3001\u5220\u9664\u6570\u636e\u5e93\u5bf9\u8c61\uff08\u5982\u5e93\u3001\u8868\u3001\u7d22\u5f15\uff09\uff0c\u5e38\u89c1\u547d\u4ee4\u6709<code>create<\/code>\u3001<code>alter<\/code>\u3001<code>drop<\/code>\u3002<\/li>\n\n\n\n<li><strong>DML\uff08Data Manipulation Language\uff0c\u6570\u636e\u64cd\u7eb5\u8bed\u8a00\uff09<\/strong>\uff1a\u7528\u4e8e\u5bf9\u8868\u4e2d\u6570\u636e\u8fdb\u884c\u589e\u5220\u6539\u67e5\uff0c\u5e38\u89c1\u547d\u4ee4\u6709<code>insert<\/code>\u3001<code>delete<\/code>\u3001<code>update<\/code>\u3001<code>select<\/code>\u3002<\/li>\n\n\n\n<li><strong>DCL\uff08Data Control Language\uff0c\u6570\u636e\u63a7\u5236\u8bed\u8a00\uff09<\/strong>\uff1a\u7528\u4e8e\u7ba1\u7406\u6570\u636e\u5e93\u6743\u9650\uff0c\u5e38\u89c1\u547d\u4ee4\u6709<code>grant<\/code>\u3001<code>revoke<\/code>\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"ep64e\">1.1.3 \u6570\u636e\u5e93\u7684\u521b\u5efa\u4e0e\u4f7f\u7528<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u521b\u5efa\u6570\u636e\u5e93\uff1a<code>create database [\u5e93\u540d];<\/code><br>\u793a\u4f8b\uff1a<code>create database school_info;<\/code>\uff08\u521b\u5efa\u540d\u4e3a<code>school_info<\/code>\u7684\u6570\u636e\u5e93\uff09<\/li>\n\n\n\n<li>\u67e5\u770b\u6240\u6709\u6570\u636e\u5e93\uff1a<code>show databases;<\/code>\uff08\u5217\u51fa\u5f53\u524dMySQL\u670d\u52a1\u5668\u4e2d\u6240\u6709\u6570\u636e\u5e93\uff09<\/li>\n\n\n\n<li>\u5207\u6362\u6570\u636e\u5e93\uff1a<code>use [\u5e93\u540d];<\/code><br>\u793a\u4f8b\uff1a<code>use school_info;<\/code>\uff08\u5207\u6362\u5230<code>school_info<\/code>\u6570\u636e\u5e93\uff0c\u540e\u7eed\u64cd\u4f5c\u9ed8\u8ba4\u5728\u6b64\u5e93\u4e2d\u6267\u884c\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"G5b5h\">1.1.4 \u8868\u7684\u521b\u5efa\u4e0e\u7ed3\u6784\u67e5\u770b<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"pOogm\">\u8868\u7684\u521b\u5efa\u8bed\u6cd5<\/h4>\n\n\n\n<pre id=\"Pegat\" class=\"wp-block-code\"><code>create table &#91;\u8868\u540d](\n  &#91;\u5b57\u6bb5\u540d1] &#91;\u5b57\u6bb5\u7c7b\u578b] &#91;\u7ea6\u675f\u6761\u4ef6] comment &#91;\u5b57\u6bb5\u6ce8\u91ca],\n  &#91;\u5b57\u6bb5\u540d2] &#91;\u5b57\u6bb5\u7c7b\u578b] &#91;\u7ea6\u675f\u6761\u4ef6] comment &#91;\u5b57\u6bb5\u6ce8\u91ca],\n  ...\n) engine=&#91;\u5b58\u50a8\u5f15\u64ce] charset=&#91;\u5b57\u7b26\u96c6];<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>engine=innodb<\/code>\uff1a\u6307\u5b9a\u5b58\u50a8\u5f15\u64ce\u4e3aInnoDB\uff08\u652f\u6301\u4e8b\u52a1\u3001\u5916\u952e\uff0cMySQL\u9ed8\u8ba4\u5f15\u64ce\uff09\u3002<\/li>\n\n\n\n<li><code>charset=utf8mb4<\/code>\uff1a\u6307\u5b9a\u5b57\u7b26\u96c6\u4e3autf8mb4\uff08\u652f\u6301\u6240\u6709Unicode\u5b57\u7b26\uff0c\u5305\u62ecemoji\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"W2iJf\">\u793a\u4f8b\uff1a\u521b\u5efa\u5b66\u751f\u6210\u7ee9\u8868<\/h4>\n\n\n\n<pre id=\"CbcZa\" class=\"wp-block-code\"><code>create table score_info(\n  id int primary key auto_increment comment '\u4e3b\u952e\uff08\u81ea\u589e\uff09',\n  name varchar(11) default null comment '\u5b66\u751f\u59d3\u540d',\n  score int default null comment '\u5b66\u751f\u5206\u6570'\n) engine=innodb charset=utf8mb4;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"QlbRD\">\u8868\u7ed3\u6784\u76f8\u5173\u64cd\u4f5c<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u67e5\u770b\u5f53\u524d\u5e93\u4e2d\u6240\u6709\u8868\uff1a<code>show tables;<\/code><\/li>\n\n\n\n<li>\u67e5\u770b\u8868\u8be6\u7ec6\u7ed3\u6784\uff1a<br><code>show create table [\u8868\u540d];<\/code>\uff08\u663e\u793a\u5efa\u8868\u5b8c\u6574\u8bed\u53e5\uff09<br>\u6216 <code>desc [\u8868\u540d];<\/code>\uff08\u7b80\u6d01\u5c55\u793a\u5b57\u6bb5\u4fe1\u606f\uff09<br>\u793a\u4f8b\uff1a<code>show create table score_info;<\/code><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"qVcNW\">1.2 \u5b57\u6bb5\u7684\u589e\u5220\u6539\u64cd\u4f5c<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"GwBoZ\">1.2.1 \u65b0\u589e\u5b57\u6bb5<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u672b\u5c3e\u65b0\u589e\u5b57\u6bb5\uff08\u9ed8\u8ba4\u6dfb\u52a0\u5230\u8868\u6700\u540e\uff09\uff1a<br><code>alter table [\u8868\u540d] add column [\u5b57\u6bb5\u540d] [\u5b57\u6bb5\u7c7b\u578b] comment [\u5b57\u6bb5\u6ce8\u91ca];<\/code><br>\u793a\u4f8b\uff1a<code>alter table score_info add column class varchar(10) default null comment '\u73ed\u7ea7';<\/code><\/li>\n\n\n\n<li>\u6307\u5b9a\u4f4d\u7f6e\u65b0\u589e\uff08\u5728\u67d0\u5b57\u6bb5\u540e\uff09\uff1a<br><code>alter table score_info add column course varchar(10) default null comment '\u8bfe\u7a0b' after name;<\/code>\uff08\u5728<code>name<\/code>\u5b57\u6bb5\u540e\u6dfb\u52a0<code>course<\/code>\uff09<\/li>\n\n\n\n<li>\u9996\u90e8\u65b0\u589e\uff08\u6dfb\u52a0\u5230\u8868\u6700\u524d\u9762\uff09\uff1a<br><code>alter table score_info add column stu_id int default null comment '\u5b66\u53f7' first;<\/code><\/li>\n\n\n\n<li>\u6279\u91cf\u65b0\u589e\u5b57\u6bb5\uff1a<br><code>alter table score_info add column sex varchar(10) default null comment '\u6027\u522b', add column age int default null comment '\u5e74\u9f84';<\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"barqS\">1.2.2 \u5220\u9664\u5b57\u6bb5<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5355\u5b57\u6bb5\u5220\u9664\uff1a<code>alter table [\u8868\u540d] drop column [\u5b57\u6bb5\u540d];<\/code><br>\u793a\u4f8b\uff1a<code>alter table score_info drop column stu_id;<\/code><\/li>\n\n\n\n<li>\u591a\u5b57\u6bb5\u5220\u9664\uff1a<br><code>alter table score_info drop column sex, drop column age;<\/code>\uff08\u540c\u65f6\u5220\u9664<code>sex<\/code>\u548c<code>age<\/code>\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"fclIJ\">1.2.3 \u4fee\u6539\u5b57\u6bb5\u7c7b\u578b<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u7bd2c02e\">\u8bed\u6cd5\uff1a<code>alter table [\u8868\u540d] modify column [\u5b57\u6bb5\u540d] [\u65b0\u7c7b\u578b] default [\u9ed8\u8ba4\u503c] comment [\u6ce8\u91ca];<\/code><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u793a\u4f8b1\uff1a\u5c06<code>class<\/code>\u5b57\u6bb5\u4ece<code>varchar<\/code>\u6539\u4e3a<code>int<\/code>\uff1a<br><code>alter table score_info modify column class int default null comment '\u73ed\u7ea7';<\/code><\/li>\n\n\n\n<li>\u793a\u4f8b2\uff1a\u8c03\u6574<code>name<\/code>\u5b57\u6bb5\u957f\u5ea6\u4e3a20\uff1a<br><code>alter table score_info modify column name varchar(20) default null comment '\u5b66\u751f\u59d3\u540d';<\/code><\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"OpN2H\">1.2.4 \u4fee\u6539\u5b57\u6bb5\u540d\u79f0<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"udcfb2fc9\">\u8bed\u6cd5\uff1a<code>alter table [\u8868\u540d] change [\u539f\u5b57\u6bb5\u540d] [\u65b0\u5b57\u6bb5\u540d] [\u65b0\u7c7b\u578b] default [\u9ed8\u8ba4\u503c] comment [\u6ce8\u91ca];<\/code><br>\u793a\u4f8b\uff1a\u5c06<code>name<\/code>\u6539\u4e3a<code>stu_name<\/code>\u5e76\u8c03\u6574\u957f\u5ea6\uff1a<br><code>alter table score_info change name stu_name varchar(20) default null comment '\u5b66\u751f\u59d3\u540d';<\/code><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"saTJk\">1.3 \u589e\u5220\u67e5\u6539\uff08CRUD\uff09\u57fa\u672c\u529f<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"HBliO\">1.3.1 \u63d2\u5165\u6570\u636e\uff08Insert\uff09<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5168\u5b57\u6bb5\u63d2\u5165\uff08\u9700\u6309\u8868\u5b57\u6bb5\u987a\u5e8f\uff09\uff1a<br><code>insert into score_info values (1, 'a', 88);<\/code>\uff08\u5047\u8bbe\u8868\u5b57\u6bb5\u4e3a<code>id, stu_name, score<\/code>\uff09<\/li>\n\n\n\n<li>\u591a\u884c\u957f\u63d2\u5165\uff1a<br><code>insert into score_info values (2, 'b', 90), (3, 'c', 86);<\/code>\uff08\u540c\u65f6\u63d2\u51652\u884c\u6570\u636e\uff09<\/li>\n\n\n\n<li>\u90e8\u5206\u5b57\u6bb5\u63d2\u5165\uff08\u6307\u5b9a\u5b57\u6bb5\u540d\uff09\uff1a<br><code>insert into score_info(stu_name, score) values ('d', 60);<\/code>\uff08\u53ea\u63d2\u5165<code>stu_name<\/code>\u548c<code>score<\/code>\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"RFj72\">1.3.2 \u6570\u636e\u67e5\u8be2\uff08Select\uff09<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5168\u8868\u67e5\u8be2\uff1a<code>select [\u5b57\u6bb5\u540d] from [\u8868\u540d];<\/code><br>\u793a\u4f8b\uff1a<code>select * from score_info;<\/code>\uff08<code>*<\/code>\u8868\u793a\u67e5\u8be2\u6240\u6709\u5b57\u6bb5\uff09<\/li>\n\n\n\n<li>\u6761\u4ef6\u67e5\u8be2\uff1a<code>select * from score_info where id=2;<\/code>\uff08\u67e5\u8be2<code>id=2<\/code>\u7684\u8bb0\u5f55\uff09<\/li>\n\n\n\n<li>\u6307\u5b9a\u5b57\u6bb5\u67e5\u8be2\uff1a<code>select stu_name from score_info where id=2;<\/code>\uff08\u53ea\u67e5\u8be2<code>id=2<\/code>\u7684\u5b66\u751f\u59d3\u540d\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Dl66f\">1.3.3 \u6570\u636e\u4fee\u6539\uff08Update\uff09<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u6761\u4ef6\u4fee\u6539\uff1a<code>update [\u8868\u540d] set [\u5b57\u6bb5\u540d=\u503c] where [\u6761\u4ef6];<\/code><br>\u793a\u4f8b\uff1a<code>update score_info set stu_name='aa' where id=1;<\/code>\uff08\u5c06<code>id=1<\/code>\u7684\u59d3\u540d\u6539\u4e3a<code>aa<\/code>\uff09<\/li>\n\n\n\n<li>\u5168\u8868\u4fee\u6539\uff08\u8c28\u614e\u4f7f\u7528\uff01\uff09\uff1a<br><code>update score_info set stu_name='aa';<\/code>\uff08\u5c06\u8868\u4e2d\u6240\u6709\u8bb0\u5f55\u7684<code>stu_name<\/code>\u6539\u4e3a<code>aa<\/code>\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"TUDlz\">1.3.4 \u6570\u636e\u5220\u9664\uff08Delete\/Truncate\uff09<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u6761\u4ef6\u5220\u9664\uff1a<code>delete from [\u8868\u540d] where [\u6761\u4ef6];<\/code><br>\u793a\u4f8b\uff1a<code>delete from score_info where id=4;<\/code>\uff08\u5220\u9664<code>id=4<\/code>\u7684\u8bb0\u5f55\uff09<\/li>\n\n\n\n<li>\u5168\u8868\u6570\u636e\u5220\u9664\uff1a<\/li>\n\n\n\n<li>\u65b9\u5f0f1\uff1a<code>delete from score_info;<\/code>\uff08\u9010\u884c\u5220\u9664\uff0c\u652f\u6301\u4e8b\u52a1\u56de\u6eda\uff09<\/li>\n\n\n\n<li>\u65b9\u5f0f2\uff1a<code>truncate score_info;<\/code>\uff08\u6e05\u7a7a\u8868\u5e76\u91cd\u7f6e\u81ea\u589e\u4e3b\u952e\uff0c\u6548\u7387\u66f4\u9ad8\u4f46\u4e0d\u652f\u6301\u56de\u6eda\uff09<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"u64NW\">1.4 \u6570\u636e\u8fc7\u6ee4\uff1a\u7cbe\u51c6\u63d0\u53d6\u6240\u9700\u8bb0\u5f55<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"X9OBQ\">\u6bd4\u8f83\u8fd0\u7b97\u7b26<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u7b49\u503c\u5339\u914d\uff1a<code>select * from score_info where stu_name='a';<\/code><\/li>\n\n\n\n<li>\u4e0d\u7b49\u4e8e\uff1a<code>select * from score_info where stu_name&lt;>'a';<\/code> \u6216 <code>stu_name!='a';<\/code><\/li>\n\n\n\n<li>\u8303\u56f4\u5339\u914d\uff1a<br><code>score&lt;80<\/code>\uff08\u5c0f\u4e8e80\uff09\u3001<code>score>80<\/code>\uff08\u5927\u4e8e80\uff09\u3001<code>score&lt;=86<\/code>\uff08\u5c0f\u4e8e\u7b49\u4e8e86\uff09\u3001<code>score>=88<\/code>\uff08\u5927\u4e8e\u7b49\u4e8e88\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"aE28D\">\u903b\u8f91\u4e0e\u8303\u56f4<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u533a\u95f4\u5339\u914d\uff1a<code>select * from score_info where score between 80 and 90;<\/code>\uff08\u67e5\u8be280-90\u5206\u7684\u8bb0\u5f55\uff0c\u5305\u542b\u8fb9\u754c\uff09<\/li>\n\n\n\n<li>\u591a\u6761\u4ef6\u4e14\uff1a<code>select * from score_info where score>=88 and stu_name='a';<\/code>\uff08\u5206\u6570\u226588\u4e14\u59d3\u540d\u4e3a<code>a<\/code>\uff09<\/li>\n\n\n\n<li>\u591a\u6761\u4ef6\u6216\uff1a<code>select * from score_info where score>88 or score&lt;=60;<\/code>\uff08\u5206\u6570>88\u6216\u226460\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"kT6Yb\">\u96c6\u5408\u5339\u914d<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5339\u914d\u96c6\u5408\u5185\u503c\uff1a<code>select * from score_info where stu_name in ('a', 'b');<\/code>\uff08\u59d3\u540d\u4e3a<code>a<\/code>\u6216<code>b<\/code>\uff09<\/li>\n\n\n\n<li>\u6392\u9664\u96c6\u5408\u5185\u503c\uff1a<code>select * from score_info where stu_name not in ('a', 'b');<\/code>\uff08\u59d3\u540d\u4e0d\u662f<code>a<\/code>\u4e14\u4e0d\u662f<code>b<\/code>\uff09<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Ccezf\">1.5 \u5b50\u67e5\u8be2\uff1a\u5d4c\u5957\u67e5\u8be2\u7684\u7075\u6d3b\u5e94\u7528<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"vg5ps\">1.5.1 \u5b9e\u9a8c\u73af\u5883\u51c6\u5907<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"GJTY6\">\u521b\u5efa\u6d4b\u8bd5\u8868<\/h4>\n\n\n\n<pre id=\"r0NWj\" class=\"wp-block-code\"><code>-- \u5b66\u751f\u4fe1\u606f\u8868\ncreate table student_info ( \n  id int primary key auto_increment comment '\u4e3b\u952e',\n  stu_id int(11) default null comment '\u5b66\u751fID',\n  stu_name varchar(30) default null comment '\u5b66\u751f\u59d3\u540d',\n  class varchar(30) default null comment '\u73ed\u7ea7'\n) engine=innodb default charset=utf8mb4;\n\n-- \u6210\u7ee9\u8868\ncreate table score_info(\n  id int primary key auto_increment comment '\u4e3b\u952e',\n  stu_id int(11) default null comment '\u5b66\u751fID',\n  score int default null comment '\u5206\u6570'\n) engine=innodb default charset=utf8mb4;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"IQQo4\">\u63d2\u5165\u6d4b\u8bd5\u6570\u636e<\/h4>\n\n\n\n<pre id=\"MnKKF\" class=\"wp-block-code\"><code>insert into student_info(stu_id, stu_name, class) values \n(1, 'a', '\u4e00\u73ed'), (2, 'b', '\u4e00\u73ed'), (3, 'c', '\u4e8c\u73ed'), (4, 'd', '\u4e8c\u73ed'), (5, 'e', '\u4e8c\u73ed');\n\ninsert into score_info(stu_id, score) values \n(1, 88), (2, 90), (3, 82), (4, 92), (6, 85); -- \u6ce8\u610f\uff1astu_id=6\u5728student_info\u4e2d\u65e0\u5bf9\u5e94<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"ZGjIS\">1.5.2 \u5b50\u67e5\u8be2\u7c7b\u578b\u53ca\u793a\u4f8b<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u5355\u884c\u5b50\u67e5\u8be2<\/strong>\uff08\u8fd4\u56de\u5355\u503c\uff0c\u7528<code>=<\/code>\u5339\u914d\uff09\uff1a<br><code>select * from score_info where stu_id = (select stu_id from student_info where stu_name = 'a');<\/code><br>\uff08\u67e5\u8be2\u59d3\u540d\u4e3a<code>a<\/code>\u7684\u5b66\u751f\u7684\u6210\u7ee9\uff0c\u5b50\u67e5\u8be2\u8fd4\u56de<code>stu_id=1<\/code>\uff09<\/li>\n\n\n\n<li><strong>\u591a\u884c\u5b50\u67e5\u8be2<\/strong>\uff08\u8fd4\u56de\u591a\u503c\uff0c\u7528<code>in<\/code>\u5339\u914d\uff09\uff1a<br><code>select * from score_info where stu_id in (select stu_id from student_info where class = '\u4e00\u73ed');<\/code><br>\uff08\u67e5\u8be2\u4e00\u73ed\u6240\u6709\u5b66\u751f\u7684\u6210\u7ee9\uff0c\u5b50\u67e5\u8be2\u8fd4\u56de<code>stu_id=1,2<\/code>\uff09<\/li>\n\n\n\n<li><strong>FROM\u5b50\u53e5\u5b50\u67e5\u8be2<\/strong>\uff08\u5c06\u5b50\u67e5\u8be2\u7ed3\u679c\u4f5c\u4e3a\u4e34\u65f6\u8868\uff09\uff1a<br><code>select stu_id, score from (select stu_id, score-1 as score from score_info) as score_tmp where score >=90;<\/code><br>\uff08\u67e5\u8be2\u201c\u5206\u6570\u51cf1\u540e\u226590\u201d\u7684\u8bb0\u5f55\uff0c\u5b50\u67e5\u8be2\u751f\u6210\u4e34\u65f6\u8868<code>score_tmp<\/code>\uff09<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"wMKg2\">1.6 \u5173\u8054\u67e5\u8be2\uff1a\u591a\u8868\u6570\u636e\u8054\u52a8\u5206\u6790<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"YZKv7\">\u5185\u8fde\u63a5\uff08Inner Join\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u13c272cb\">\u53ea\u8fd4\u56de\u4e24\u8868\u4e2d\u5339\u914d\u6761\u4ef6\u7684\u8bb0\u5f55\u3002<br>\u8bed\u6cd5\uff1a<code>select [\u5b57\u6bb5] from \u88681 a inner join \u88682 b on a.\u5173\u8054\u5b57\u6bb5 = b.\u5173\u8054\u5b57\u6bb5;<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ue333ef02\">\u793a\u4f8b1\uff1a\u67e5\u8be2\u5b66\u751f\u4fe1\u606f\u4e0e\u5bf9\u5e94\u6210\u7ee9\uff08\u5168\u5b57\u6bb5\uff09\uff1a<br><code>select * from student_info a inner join score_info b on a.stu_id = b.stu_id;<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ud8135378\">\u793a\u4f8b2\uff1a\u67e5\u8be2\u6307\u5b9a\u5b57\u6bb5+\u6761\u4ef6\uff08\u53ea\u67e5\u4e00\u73ed\u5b66\u751f\u7684\u59d3\u540d\u548c\u5206\u6570\uff09\uff1a<br><code>select a.stu_name, b.score from student_info a inner join score_info b on a.stu_id = b.stu_id where a.class = '\u4e00\u73ed';<\/code><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"jeIwu\">\u5de6\u8fde\u63a5\uff08Left Join\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u7f1051e3\">\u8fd4\u56de\u5de6\u8868\u6240\u6709\u8bb0\u5f55\uff0c\u53f3\u8868\u65e0\u5339\u914d\u65f6\u8865<code>null<\/code>\u3002<br>\u793a\u4f8b\uff1a<code>select * from student_info a left join score_info b on a.stu_id = b.stu_id;<\/code><br>\uff08\u5373\u4f7f\u5b66\u751f\u65e0\u6210\u7ee9\uff0c\u4e5f\u4f1a\u663e\u793a\u5176\u57fa\u672c\u4fe1\u606f\uff09<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"EPK2v\">\u53f3\u8fde\u63a5\uff08Right Join\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u5014f931\">\u8fd4\u56de\u53f3\u8868\u6240\u6709\u8bb0\u5f55\uff0c\u5de6\u8868\u65e0\u5339\u914d\u65f6\u8865<code>null<\/code>\u3002<br>\u793a\u4f8b\uff1a<code>select * from student_info a right join score_info b on a.stu_id = b.stu_id;<\/code><br>\uff08\u5373\u4f7f\u6210\u7ee9\u5bf9\u5e94\u7684\u5b66\u751f\u4fe1\u606f\u4e0d\u5b58\u5728\uff0c\u4e5f\u4f1a\u663e\u793a\u6210\u7ee9\u8bb0\u5f55\uff09<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"pPEWX\">1.7 \u805a\u96c6\u51fd\u6570\u4e0e\u5206\u7ec4\u67e5\u8be2\uff1a\u6570\u636e\u805a\u5408\u5206\u6790<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h99CY\">1.7.1 \u5b9e\u9a8c\u8868\u51c6\u5907<\/h3>\n\n\n\n<pre id=\"Uid7w\" class=\"wp-block-code\"><code>create table student_score(\n  id int primary key auto_increment comment '\u4e3b\u952e',\n  stu_id int default null comment '\u5b66\u751fID',\n  class varchar(30) default null comment '\u73ed\u7ea7',\n  score int default null comment '\u5206\u6570'\n) engine=innodb default charset=utf8mb4;\n\ninsert into student_score(stu_id, class, score) values \n(1, '\u4e00\u73ed', 88), (2, '\u4e00\u73ed', 90), (3, '\u4e8c\u73ed', 82), (4, '\u4e8c\u73ed', 92), (5, '\u4e8c\u73ed', 85);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"iINVr\">1.7.2 \u5e38\u7528\u805a\u96c6\u51fd\u6570<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8ba1\u6570\uff1a<code>select count(*) from student_score;<\/code>\uff08\u7edf\u8ba1\u603b\u8bb0\u5f55\u6570\uff09<\/li>\n\n\n\n<li>\u5e73\u5747\u503c\uff1a<code>select avg(score) from student_score;<\/code>\uff08\u6240\u6709\u5b66\u751f\u7684\u5e73\u5747\u5206\uff09<\/li>\n\n\n\n<li>\u6c42\u548c\uff1a<code>select sum(score) from student_score;<\/code>\uff08\u6240\u6709\u5206\u6570\u603b\u548c\uff09<\/li>\n\n\n\n<li>\u6700\u5927\u503c\uff1a<code>select max(score) from student_score;<\/code>\uff08\u6700\u9ad8\u5206\u6570\uff09<\/li>\n\n\n\n<li>\u6700\u5c0f\u503c\uff1a<code>select min(score) from student_score;<\/code>\uff08\u6700\u4f4e\u5206\u6570\uff09<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ued36ef82\">\u5e26\u6761\u4ef6\u7684\u805a\u5408\uff1a<code>select avg(score) from student_score where class='\u4e00\u73ed';<\/code>\uff08\u4e00\u73ed\u7684\u5e73\u5747\u5206\uff09<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"cY2di\">1.7.3 \u5206\u7ec4\u67e5\u8be2\uff08Group By\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ufc351f5d\">\u6309\u6307\u5b9a\u5b57\u6bb5\u5206\u7ec4\uff0c\u5bf9\u6bcf\u7ec4\u5e94\u7528\u805a\u5408\u51fd\u6570\u3002<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u6309\u73ed\u7ea7\u67e5\u5e73\u5747\u5206\uff1a<br><code>select class, avg(score) as avg_score from student_score group by class;<\/code><\/li>\n\n\n\n<li>\u6309\u73ed\u7ea7\u67e5\u6700\u9ad8\u5206\uff1a<br><code>select class, max(score) as max_score from student_score group by class;<\/code><\/li>\n\n\n\n<li>\u6309\u73ed\u7ea7\u67e5\u4eba\u6570\uff1a<br><code>select class, count(*) as stu_count from student_score group by class;<\/code><\/li>\n\n\n\n<li>\u6309\u73ed\u7ea7\u5408\u5e76\u5b66\u751fID\uff1a<br><code>select class, group_concat(stu_id) as stu_ids from student_score group by class;<\/code><br>\uff08\u7ed3\u679c\u793a\u4f8b\uff1a<code>\u4e00\u73ed | 1,2<\/code>\uff1b<code>\u4e8c\u73ed | 3,4,5<\/code>\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"nZMUW\">1.7.4 \u8fc7\u6ee4\u5206\u7ec4\uff08Having\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u61cd4ad7\">\u5bf9\u5206\u7ec4\u540e\u7684\u7ed3\u679c\u8fdb\u4e00\u6b65\u7b5b\u9009\uff08<code>where<\/code>\u7528\u4e8e\u5206\u7ec4\u524d\u8fc7\u6ee4\uff0c<code>having<\/code>\u7528\u4e8e\u5206\u7ec4\u540e\uff09\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u702a81a6\">\u793a\u4f8b\uff1a\u67e5\u8be2\u4eba\u6570\u5927\u4e8e2\u7684\u73ed\u7ea7\u53ca\u5176\u4eba\u6570\uff1a<br><code>select class, count(*) as stu_count from student_score group by class having stu_count &gt; 2;<\/code><br>\uff08\u4e8c\u73ed\u4eba\u6570\u4e3a3\uff0c\u4f1a\u88ab\u7b5b\u9009\u51fa\u6765\uff09<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"wbriB\">1.8 \u6a21\u7cca\u67e5\u8be2\uff1a\u5173\u952e\u8bcd\u5339\u914d<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"MsJlA\">1.8.1 \u6d4b\u8bd5\u8868\u51c6\u5907<\/h3>\n\n\n\n<pre id=\"AXO8u\" class=\"wp-block-code\"><code>create table student_name ( \n  id int primary key auto_increment comment '\u4e3b\u952e',\n  stu_id int default null comment '\u5b66\u751fID',\n  stu_name varchar(30) default null comment '\u59d3\u540d'\n) engine=innodb default charset=utf8mb4;\n\ninsert into student_name(stu_id, stu_name) values (1, 'abc'), (2, 'def'), (3, 'ghi');<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"PRZ9W\">1.8.2 Like\u5339\u914d<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>%<\/code>\uff1a\u5339\u914d\u4efb\u610f\u957f\u5ea6\u5b57\u7b26\uff08\u5305\u62ec0\u4e2a\uff09<\/li>\n\n\n\n<li>\u4ee5<code>a<\/code>\u5f00\u5934\uff1a<code>select * from student_name where stu_name like 'a%';<\/code>\uff08\u5339\u914d<code>abc<\/code>\uff09<\/li>\n\n\n\n<li>\u4ee5<code>c<\/code>\u7ed3\u5c3e\uff1a<code>select * from student_name where stu_name like '%c';<\/code>\uff08\u5339\u914d<code>abc<\/code>\uff09<\/li>\n\n\n\n<li>\u5305\u542b<code>b<\/code>\uff1a<code>select * from student_name where stu_name like '%b%';<\/code>\uff08\u5339\u914d<code>abc<\/code>\uff09<\/li>\n\n\n\n<li><code>_<\/code>\uff1a\u5339\u914d\u5355\u4e2a\u5b57\u7b26<br>\u793a\u4f8b\uff1a<code>select * from student_name where stu_name like 'ab_';<\/code>\uff08\u5339\u914d<code>abc<\/code>\uff0c<code>_<\/code>\u5bf9\u5e94<code>c<\/code>\uff09<\/li>\n\n\n\n<li>\u6392\u9664\u5339\u914d\uff1a<code>select * from student_name where stu_name not like 'a%';<\/code>\uff08\u6392\u9664\u4ee5<code>a<\/code>\u5f00\u5934\u7684\u8bb0\u5f55\uff09<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"FDn7c\">1.8.3 \u6b63\u5219\u5339\u914d\uff08Regexp\uff09<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"ubfaacc31\">\u66f4\u7075\u6d3b\u7684\u6a21\u5f0f\u5339\u914d\uff0c\u652f\u6301\u6b63\u5219\u8868\u8fbe\u5f0f\u3002<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5305\u542b<code>a<\/code>\u6216<code>d<\/code>\uff1a<br><code>select * from student_name where stu_name regexp '[ad]';<\/code> \u6216 <code>regexp 'a|d';<\/code>\uff08\u5339\u914d<code>abc<\/code>\u3001<code>def<\/code>\uff09<\/li>\n\n\n\n<li>\u5305\u542b\u5c0f\u5199\u5b57\u6bcd\uff1a<code>select * from student_name where stu_name regexp '[a-z]';<\/code><\/li>\n\n\n\n<li>\u5b66\u751fID\u5305\u542b\u6570\u5b57\uff1a<code>select * from student_name where stu_id regexp '[0-9]';<\/code><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"oidTR\">1.9 \u7ed3\u679c\u6392\u5e8f\uff1aOrder By<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5347\u5e8f\uff08\u9ed8\u8ba4\uff09\uff1a<code>select * from student_score order by score;<\/code>\uff08\u6309\u5206\u6570\u4ece\u4f4e\u5230\u9ad8\uff09<\/li>\n\n\n\n<li>\u964d\u5e8f\uff1a<code>select * from student_score order by score desc;<\/code>\uff08\u6309\u5206\u6570\u4ece\u9ad8\u5230\u4f4e\uff09<\/li>\n\n\n\n<li>\u591a\u5b57\u6bb5\u6392\u5e8f\uff08\u5148\u6309\u73ed\u7ea7\u5347\u5e8f\uff0c\u518d\u6309\u5206\u6570\u964d\u5e8f\uff09\uff1a<br><code>select * from student_score order by class, score desc;<\/code><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"JBc7S\">1.10 \u7ed3\u679c\u5206\u9875\uff1aLimit<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"uaafb0761\">\u7528\u4e8e\u9650\u5236\u8fd4\u56de\u7ed3\u679c\u7684\u6570\u91cf\uff0c\u5e38\u7528\u4e8e\u5206\u9875\u5c55\u793a\u3002<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u53d6\u524dN\u884c\uff1a<code>select * from student_score limit 2;<\/code>\uff08\u8fd4\u56de\u524d2\u6761\u8bb0\u5f55\uff09<\/li>\n\n\n\n<li>\u5206\u9875\u67e5\u8be2\uff08\u4ece\u7b2c3\u884c\u5f00\u59cb\uff0c\u53d62\u6761\uff09\uff1a<br><code>select * from student_score limit 2, 2;<\/code>\uff08<code>limit \u504f\u79fb\u91cf, \u6761\u6570<\/code>\uff0c\u504f\u79fb\u91cf\u4ece0\u5f00\u59cb\uff0c\u5373\u4ece\u7b2c3\u884c\u53d62\u6761\uff09<\/li>\n\n\n\n<li>\u7ed3\u5408\u6392\u5e8f\uff1a<code>select * from student_score order by score desc limit 3;<\/code>\uff08\u53d6\u5206\u6570\u6700\u9ad8\u76843\u6761\uff09<\/li>\n\n\n\n<li>\u5e26Offset\u8bed\u6cd5\uff1a<code>select * from student_score limit 2 offset 1;<\/code>\uff08\u8df3\u8fc71\u884c\uff0c\u53d62\u6761\uff0c\u7b49\u540c\u4e8e<code>limit 1,2<\/code>\uff09<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"fhyfx\">1.11 \u7ec4\u5408\u67e5\u8be2\uff1aUnion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u370f200e\">\u7528\u4e8e\u5408\u5e76\u591a\u4e2a<code>select<\/code>\u7684\u7ed3\u679c\u96c6\uff0c\u8981\u6c42\u5b57\u6bb5\u6570\u548c\u7c7b\u578b\u4e00\u81f4\u3002<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Union<\/code>\uff08\u81ea\u52a8\u53bb\u91cd\uff09\uff1a<br>\u793a\u4f8b\uff1a\u5408\u5e76\u201c\u4e8c\u73ed\u5b66\u751f\u201d\u548c\u201c\u5206\u6570\u226590\u201d\u7684\u8bb0\u5f55\uff08\u53bb\u91cd\uff09\uff1a<br><code>select * from student_score where class='\u4e8c\u73ed' union select * from student_score where score >=90;<\/code><\/li>\n\n\n\n<li><code>Union All<\/code>\uff08\u4fdd\u7559\u6240\u6709\u8bb0\u5f55\uff0c\u5305\u62ec\u91cd\u590d\uff09\uff1a<br>\u793a\u4f8b\uff1a\u4fdd\u7559\u6240\u6709\u7b26\u5408\u6761\u4ef6\u7684\u8bb0\u5f55\uff08\u542b\u91cd\u590d\uff09\uff1a<br><code>select * from student_score where class='\u4e8c\u73ed' union all select * from student_score where score >=90;<\/code><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"nFBtE\">1.12 \u7efc\u5408\u6848\u4f8b\uff1a\u5b66\u751f\u6210\u7ee9\u5206\u6790<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"PARcR\">1.12.1 \u73af\u5883\u51c6\u5907<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"xf2JC\">\u521b\u5efa\u6570\u636e\u5e93\u4e0e\u8868<\/h4>\n\n\n\n<pre id=\"Xkhmo\" class=\"wp-block-code\"><code>-- \u521b\u5efa\u5b66\u751f\u5e93\ncreate database student;\nuse student;\n\n-- \u5b66\u751f\u4fe1\u606f\u8868\ncreate table student_info(\n  id int primary key auto_increment comment '\u4e3b\u952e',\n  stu_num int default null comment '\u5b66\u53f7',\n  stu_name varchar(10) default null comment '\u59d3\u540d',\n  stu_class varchar(10) default null comment '\u73ed\u7ea7'\n) engine=innodb charset=utf8mb4;\n\n-- \u6210\u7ee9\u8868\ncreate table student_score(\n  id int primary key auto_increment comment '\u4e3b\u952e',\n  stu_num int default null comment '\u5b66\u53f7',\n  stu_course varchar(10) default null comment '\u5b66\u79d1',\n  stu_score int default null comment '\u5206\u6570'\n) engine=innodb charset=utf8mb4;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"dPMQ5\">\u63d2\u5165\u6d4b\u8bd5\u6570\u636e<\/h4>\n\n\n\n<pre id=\"eUCgP\" class=\"wp-block-code\"><code>-- \u5b66\u751f\u4fe1\u606f\ninsert into student_info(stu_num, stu_name, stu_class) values \n(1101, '\u5468\u661f\u661f', '\u9ad8\u4e09\u4e00\u73ed'), (1102, '\u4ed9\u8482', '\u9ad8\u4e09\u4e00\u73ed'), \n(1201, '\u9ec4\u5c0f\u9f9f', '\u9ad8\u4e09\u4e8c\u73ed'), (1202, '\u963f\u654f', '\u9ad8\u4e09\u4e8c\u73ed');\n\n-- \u6210\u7ee9\u6570\u636e\ninsert into student_score(stu_num, stu_course, stu_score) values \n(1101, '\u8bed\u6587', 88), (1101, '\u6570\u5b66', 90), (1102, '\u8bed\u6587', 91), (1102, '\u6570\u5b66', 87),\n(1201, '\u8bed\u6587', 89), (1201, '\u6570\u5b66', 80), (1202, '\u8bed\u6587', 92), (1202, '\u6570\u5b66', 89);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"AHhyl\">1.12.2 \u5178\u578b\u573a\u666f\u67e5\u8be2<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u67e5\u8be2\u5468\u661f\u661f\u7684\u5b66\u53f7<\/strong>\uff1a<br><code>select stu_num from student_info where stu_name='\u5468\u661f\u661f';<\/code><\/li>\n\n\n\n<li><strong>\u67e5\u8be2\u59d3\u9ec4\u7684\u5b66\u751f<\/strong>\uff1a<br><code>select * from student_info where stu_name like '\u9ec4%';<\/code><\/li>\n\n\n\n<li><strong>\u7edf\u8ba1\u9ad8\u4e09\u4e00\u73ed\u7684\u4eba\u6570<\/strong>\uff1a<br><code>select count(*) as \u4e00\u73ed\u4eba\u6570 from student_info where stu_class='\u9ad8\u4e09\u4e00\u73ed';<\/code><\/li>\n\n\n\n<li><strong>\u67e5\u8be2\u5468\u661f\u661f\u7684\u6240\u6709\u6210\u7ee9<\/strong>\uff08\u4e24\u79cd\u65b9\u5f0f\uff09\uff1a<\/li>\n<\/ol>\n\n\n\n<pre id=\"klLe3\" class=\"wp-block-code\"><code>-- \u5b50\u67e5\u8be2\u65b9\u5f0f\nselect * from student_score where stu_num = (select stu_num from student_info where stu_name='\u5468\u661f\u661f');\n\n-- \u5173\u8054\u67e5\u8be2\u65b9\u5f0f\nselect * from student_score a inner join student_info b on a.stu_num = b.stu_num where b.stu_name='\u5468\u661f\u661f';<\/code><\/pre>\n\n\n\n<ol start=\"5\" class=\"wp-block-list\">\n<li><strong>\u67e5\u8be2\u5206\u6570>90\u7684\u5b66\u751f\u59d3\u540d\u548c\u5b66\u79d1<\/strong>\uff1a<br><code>select b.stu_name, a.stu_course, a.stu_score from student_score a inner join student_info b on a.stu_num = b.stu_num where a.stu_score>90;<\/code><\/li>\n\n\n\n<li><strong>\u67e5\u8be2\u6bcf\u4e2a\u5b66\u751f\u7684\u603b\u5206<\/strong>\uff1a<br><code>select stu_num, sum(stu_score) as \u603b\u5206 from student_score group by stu_num;<\/code><\/li>\n\n\n\n<li><strong>\u67e5\u8be2\u603b\u5206\u524d\u4e24\u540d\u7684\u5b66\u751f\u59d3\u540d\u548c\u5206\u6570<\/strong>\uff1a<br><code>select b.stu_name, sum(a.stu_score) as \u603b\u5206 from student_score a inner join student_info b on a.stu_num = b.stu_num group by a.stu_num order by \u603b\u5206 desc limit 2;<\/code><\/li>\n\n\n\n<li><strong>\u67e5\u8be2\u5404\u5b66\u79d1\u7684\u5e73\u5747\u5206<\/strong>\uff1a<br><code>select stu_course, avg(stu_score) as \u5e73\u5747\u5206 from student_score group by stu_course;<\/code><\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"mEUSe\">1.13 SQL\u6a21\u5f0f\u914d\u7f6e\uff08sql_mode\uff09<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"uc2b7d30c\"><code>sql_mode<\/code>\u7528\u4e8e\u63a7\u5236MySQL\u7684\u8bed\u6cd5\u6821\u9a8c\u548c\u6570\u636e\u6821\u9a8c\u89c4\u5219\uff0c\u5efa\u8bae\u914d\u7f6e\u4e3a\u4e25\u683c\u6a21\u5f0f\uff1a<\/p>\n\n\n\n<pre id=\"QTIep\" class=\"wp-block-code\"><code>-- \u67e5\u770b\u5f53\u524d\u6a21\u5f0f\nshow global variables like 'sql_mode';\n\n-- \u8bbe\u7f6e\u4e25\u683c\u6a21\u5f0f\uff08\u9700\u91cd\u542f\u8fde\u63a5\u751f\u6548\uff09\nset global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"wkFY9\">1.14 \u672c\u7ae0\u603b\u7ed3<\/h2>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table class=\"has-fixed-layout\"><tbody><tr><td>\u77e5\u8bc6\u70b9<\/td><td>\u9700\u8981\u638c\u63e1\u7684\u5185\u5bb9<\/td><\/tr><tr><td>\u5e93\u8868\u521b\u5efa<\/td><td>\u521b\u5efa\u548c\u67e5\u770b database\u3001\u521b\u5efa\u8868\u3001\u67e5\u770b\u8868\u7ed3\u6784<\/td><\/tr><tr><td>\u5b57\u6bb5\u7ba1\u7406<\/td><td>\u589e\u5220\u5b57\u6bb5\u3001\u6539\u5b57\u6bb5\u7c7b\u578b\u3001\u6539\u5b57\u6bb5\u540d<\/td><\/tr><tr><td>\u589e\u5220\u67e5\u6539<\/td><td>\u5199\u5165\u5355\u884c\u548c\u591a\u884c\u6570\u636e\u3001\u5168\u8868\u67e5\u8be2\u548c\u6309\u6761\u4ef6\u67e5\u8be2\u3001\u6309\u6761\u4ef6\u66f4\u65b0\u548c\u5220\u9664\u6570\u636e<\/td><\/tr><tr><td>\u6570\u636e\u8fc7\u6ee4<\/td><td>\u7b49\u503c\u5339\u914d\u3001\u8303\u56f4\u67e5\u8be2\u3001\u591a\u4e2a\u503c\u5339\u914d<\/td><\/tr><tr><td>\u5b50\u67e5\u8be2<\/td><td>\u5355\u884c\u3001\u591a\u884c\u5b50\u67e5\u8be2\u3001FROM \u5b50\u67e5\u8be2<\/td><\/tr><tr><td>\u5173\u8054\u67e5\u8be2<\/td><td>\u5185\u8fde\u63a5\u3001\u5de6\u8fde\u63a5\u3001\u53f3\u8fde\u63a5<\/td><\/tr><tr><td>\u805a\u96c6\u51fd\u6570\u548c\u5206\u7ec4\u51fd\u6570<\/td><td>count()\u3001avg()\u3001sum()\u3001max()\u3001min()\u3001group by<\/td><\/tr><tr><td>\u6a21\u7cca\u67e5\u8be2<\/td><td>like\u3001regexp<\/td><\/tr><tr><td>\u6392\u5e8f<\/td><td>\u6b63\u5e8f\u3001\u5012\u5e8f\u3001\u591a\u4e2a\u5b57\u6bb5\u6392\u5e8f<\/td><\/tr><tr><td>\u5206\u9875\u67e5\u8be2<\/td><td>limit\u3001offset<\/td><\/tr><tr><td>\u7ec4\u5408\u67e5\u8be2<\/td><td>union\u3001union all<\/td><\/tr><tr><td>\u6848\u4f8b\u6f14\u793a<\/td><td>\u590d\u4e60\u4e86\u5e38\u7528\u7684\u4e00\u4e9b SQL \u8bed\u53e5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"u2e034f35\">\u672c\u6587\u6db5\u76d6\u4e86\u65e5\u5e38\u5de5\u4f5c\u4e2d\u5fc5\u5907\u7684SQL\u6280\u80fd\uff0c\u4ece\u5e93\u8868\u521b\u5efa\u3001\u5b57\u6bb5\u64cd\u4f5c\u5230\u6570\u636e\u589e\u5220\u67e5\u6539\uff0c\u518d\u5230\u9ad8\u7ea7\u67e5\u8be2\uff08\u5b50\u67e5\u8be2\u3001\u5173\u8054\u67e5\u8be2\u3001\u5206\u7ec4\u805a\u5408\u7b49\uff09\uff0c\u901a\u8fc7\u5b9e\u4f8b\u6f14\u793a\u4e86\u5404\u7c7b\u573a\u666f\u7684\u5b9e\u73b0\u65b9\u5f0f\u3002\u638c\u63e1\u8fd9\u4e9b\u57fa\u7840\u540e\uff0c\u53ef\u8fdb\u4e00\u6b65\u5b66\u4e60\u7d22\u5f15\u4f18\u5316\u3001\u4e8b\u52a1\u7ba1\u7406\u7b49\u8fdb\u9636\u5185\u5bb9\uff0c\u63d0\u5347\u6570\u636e\u5e93\u64cd\u4f5c\u6548\u7387\u4e0e\u7a33\u5b9a\u6027\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>1.1 \u5e93\u4e0e\u8868\u7684\u521b\u5efa\u65b9\u6cd5 1.1.1 \u5e93\u8868\u5b57\u6bb5\u7684\u6838\u5fc3\u6784\u6210 \u6570\u636e\u5e93\u8868\u7684\u5b57\u6bb5\u662f\u6570\u636e\u5b58\u50a8\u7684\u57fa\u672c\u5355\u5143\uff0c\u901a\u5e38\u5305\u542b\u4e09\u8981\u7d20\uff1a<\/p>\n<div class=\"more-link\">\n\t\t\t\t <a href=\"https:\/\/yunyanglib.cn\/?p=175\" class=\"link-btn theme-btn\"><span>Read More <\/span> <i class=\"fa fa-caret-right\"><\/i><\/a>\n\t\t\t<\/div>\n","protected":false},"author":1,"featured_media":176,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[16],"class_list":["post-175","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-mysql"],"_links":{"self":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=175"}],"version-history":[{"count":2,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/175\/revisions"}],"predecessor-version":[{"id":179,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/posts\/175\/revisions\/179"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=\/wp\/v2\/media\/176"}],"wp:attachment":[{"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yunyanglib.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}