{"id":511,"date":"2020-08-22T00:18:24","date_gmt":"2020-08-21T16:18:24","guid":{"rendered":"https:\/\/www.notown.club\/?p=511"},"modified":"2020-08-22T00:18:24","modified_gmt":"2020-08-21T16:18:24","slug":"mysql%e5%9f%ba%e6%9c%ac%e5%91%bd%e4%bb%a4%e7%94%a8%e6%b3%95%ef%bc%88%e4%ba%8c%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.notown.top\/?p=511","title":{"rendered":"Mysql\u57fa\u672c\u547d\u4ee4\u7528\u6cd5\uff08\u4e8c\uff09"},"content":{"rendered":"\n<p class=\"has-medium-font-size\"><strong>\u4e00\u3001\u67e5\u8be2<\/strong><\/p>\n\n\n\n<p>\u6570\u636e\u51c6\u5907\uff0c\u4ee5\u4e0b\u4ee5\u8fd9\u56db\u5f20\u8868\u6765\u505a\u4f8b\u5b50<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">1.\u5b66\u751f\u8868\nselect * from student;\n +------------+-------+--------+---------------------+---------+\n | Num        | Name  | Sex    | birth               | class   |\n +------------+-------+--------+---------------------+---------+\n | 2017052455 | Tom   | male   | 1999-05-07 00:00:00 | class_1 |\n | 2017052456 | Jenny | female | 1998-05-07 00:00:00 | class_3 |\n | 2017052457 | Bob   | male   | 1989-05-17 00:00:00 | class_3 |\n | 2017052458 | Alice | female | 1999-01-07 00:00:00 | class_2 |\n | 2017052459 | Eve   | male   | 1999-05-27 00:00:00 | class_1 |\n | 2017052460 | Anny  | female | 1998-10-27 00:00:00 | class_4 |\n | 2017052461 | Endw  | female | 1999-11-07 00:00:00 | class_2 |\n | 2017052462 | Julie | male   | 1999-08-02 00:00:00 | class_1 |\n | 2017052463 | Luero | female | 1999-01-27 00:00:00 | class_3 |\n +------------+-------+--------+---------------------+---------+\n2.\u8bfe\u7a0b\u8868\nselect * from course;;\n +-------+------------------+------------+\n | Num   | Name             | Teacher_ID |\n +-------+------------------+------------+\n | 3-105 | computer science | 1000001    |\n | 3-245 | operator system  | 1000001    |\n | 6-166 | math             | 1000003    |\n | 9-888 | english          | 1000003    |\n +-------+------------------+------------+\n3.\u6559\u5e08\u8868\nselect * from teacher;\n +---------+----------+--------+---------------------+--------+--------+\n | Num     | Name     | Sex    | birth               | prof   | depart |\n +---------+----------+--------+---------------------+--------+--------+\n | 1000001 | Mr.Zhang | male   | 1988-05-06 00:00:00 | Doctor | D1     |\n | 1000002 | Mr.Huang | male   | 1978-03-06 00:00:00 | Doctor | D1     |\n | 1000003 | Mr.Liu   | female | 1977-05-06 00:00:00 | Doctor | D2     |\n | 1000004 | Mr.Wu    | male   | 1975-05-16 00:00:00 | Doctor | D3     |\n | 1000005 | Mr.Li    | female | 1976-02-06 00:00:00 | Doctor | D3     |\n | 1000006 | Mr.Kong  | male   | 1987-05-26 00:00:00 | Doctor | D1     |\n | 1000007 | Mr.Yu    | male   | 1982-11-06 00:00:00 | Doctor | D2     |\n +---------+----------+--------+---------------------+--------+--------+\n4.\u6210\u7ee9\u8868\nselect * from score;;\n +------------+-----------+--------+\n | Student_ID | Course_ID | degree |\n +------------+-----------+--------+\n | 2017052455 | 3-245     |     96 |\n | 2017052456 | 3-105     |     76 |\n | 2017052457 | 3-245     |     86 |\n | 2017052458 | 6-166     |     88 |\n | 2017052459 | 9-888     |     96 |\n | 2017052460 | 3-105     |     80 |\n | 2017052461 | 3-245     |     82 |\n | 2017052462 | 6-166     |     83 |\n | 2017052463 | 9-888     |     83 |\n +------------+-----------+--------+<\/pre>\n\n\n\n<p>1.\u67e5\u8be2\u6240\u6709\u8bb0\u5f55<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select * from student; --\u67e5\u8be2\u5b66\u751f\u8868\u6240\u6709\u8bb0\u5f55<\/pre>\n\n\n\n<p>2.\u67e5\u770b\u7279\u5b9a\u67d0\u4e9b\u5217<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select Name,Sex from student; --\u67e5\u8be2\u540d\u5b57\u548c\u6027\u522b\u5217<\/pre>\n\n\n\n<p>3.\u67e5\u8be2\u67d0\u5217\u7684\u4e0d\u91cd\u590d\u9879\u6709\u51e0\u4e2a<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select distinct depart from teacher;  --\u67e5\u8be2\u6559\u5e08\u6240\u6709\u7684\u4e0d\u91cd\u590ddepart\u5217\n +--------+\n | depart |\n +--------+\n | D1     |\n | D2     |\n | D3     |\n +--------+<\/pre>\n\n\n\n<p>4.\u67e5\u8be2\u503c\u5728\u67d0\u4e00\u533a\u95f4\u7684\u6240\u6709\u8bb0\u5f55<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select * from score where degree between 80 and 85;  --\u67e5\u8be2score\u8868degree\u5217\u7684\u503c\u7684\u533a\u95f4\u572880\u523085\u4e4b\u95f4\n+------------+-----------+--------+\n | Student_ID | Course_ID | degree |\n +------------+-----------+--------+\n | 2017052460 | 3-105     |     80 |\n | 2017052461 | 3-245     |     82 |\n | 2017052462 | 6-166     |     83 |\n | 2017052463 | 9-888     |     83 |\n +------------+-----------+--------+\nselect * from score where degree >= 80 and degree &lt;= 85;  --\u4f5c\u7528\u540c\u4e0a\n<\/pre>\n\n\n\n<p>5.\u67e5\u8be2\u67d0\u6307\u5b9a\u503c\u5f97\u6240\u6709\u8bb0\u5f55<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select * from score where degree in(83,86);   --\u67e5\u8be2score\u8868degree\u5217\u503c\u4e3a83\u621686\u7684\u8bb0\u5f55\n+------------+-----------+--------+\n | Student_ID | Course_ID | degree |\n +------------+-----------+--------+\n | 2017052457 | 3-245     |     86 |\n | 2017052462 | 6-166     |     83 |\n | 2017052463 | 9-888     |     83 |\n +------------+-----------+--------+<\/pre>\n\n\n\n<p>6.\u6216\u6761\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select * from student where Sex='male' or  class = 'class_1';     --\u67e5\u8be2\u6027\u522b\u4e3a\u7537\u7684\u6216\u8005\u662f\u4e00\u73ed\u7684\u4eba\n+------------+-------+------+---------------------+---------+\n | Num        | Name  | Sex  | birth               | class   |\n +------------+-------+------+---------------------+---------+\n | 2017052455 | Tom   | male | 1999-05-07 00:00:00 | class_1 |\n | 2017052457 | Bob   | male | 1989-05-17 00:00:00 | class_3 |\n | 2017052459 | Eve   | male | 1999-05-27 00:00:00 | class_1 |\n | 2017052462 | Julie | male | 1999-08-02 00:00:00 | class_1 |\n +------------+-------+------+---------------------+---------+<\/pre>\n\n\n\n<p>7.\u5347\u5e8f\u964d\u5e8f<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select * from score order by degree asc;     --\u6309\u6210\u7ee9\u5347\u5e8f\u6392\u5217\n+------------+-----------+--------+\n | Student_ID | Course_ID | degree |\n +------------+-----------+--------+\n | 2017052456 | 3-105     |     76 |\n | 2017052460 | 3-105     |     80 |\n | 2017052461 | 3-245     |     82 |\n | 2017052462 | 6-166     |     83 |\n | 2017052463 | 9-888     |     83 |\n | 2017052457 | 3-245     |     86 |\n | 2017052458 | 6-166     |     88 |\n | 2017052455 | 3-245     |     96 |\n | 2017052459 | 9-888     |     96 |\n +------------+-----------+--------+\nselect * from score order by degree desc;     --\u6309\u6210\u7ee9\u964d\u5e8f\u6392\u5217\n +------------+-----------+--------+\n | Student_ID | Course_ID | degree |\n +------------+-----------+--------+\n | 2017052455 | 3-245     |     96 |\n | 2017052459 | 9-888     |     96 |\n | 2017052458 | 6-166     |     88 |\n | 2017052457 | 3-245     |     86 |\n | 2017052462 | 6-166     |     83 |\n | 2017052463 | 9-888     |     83 |\n | 2017052461 | 3-245     |     82 |\n | 2017052460 | 3-105     |     80 |\n | 2017052456 | 3-105     |     76 |\n +------------+-----------+--------+\nselect * from score order by Course_ID asc,degree desc;    --\u6309\u8bfe\u7a0b\u7f16\u53f7\u5347\u5e8f\u6392\u540e\u518d\u6309\u6210\u7ee9\u964d\u5e8f\u6392\n +------------+-----------+--------+\n | Student_ID | Course_ID | degree |\n +------------+-----------+--------+\n | 2017052460 | 3-105     |     80 |\n | 2017052456 | 3-105     |     76 |\n | 2017052455 | 3-245     |     96 |\n | 2017052457 | 3-245     |     86 |\n | 2017052461 | 3-245     |     82 |\n | 2017052458 | 6-166     |     88 |\n | 2017052462 | 6-166     |     83 |\n | 2017052459 | 9-888     |     96 |\n | 2017052463 | 9-888     |     83 |\n +------------+-----------+--------+<\/pre>\n\n\n\n<p>8.\u67e5\u8be2\u67d0\u5217\u62e5\u6709\u76f8\u540c\u503c\u7684\u6570\u76ee<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select count(*) from student where class='class_1';     --\u67e5\u8be2\u4e00\u73ed\u7684\u4eba\u6570\n+----------+\n | count(*) |\n +----------+\n |        3 |\n +----------+<\/pre>\n\n\n\n<p>9. \u67e5\u8be2\u67d0\u5217\u4e2d\u6700\u5927\u503c\u7684\u90a3\u4e00\u884c\u7684\u8bb0\u5f55<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select * from score where degree=(select max(degree) from score); --\u9009\u62e9\u5206\u6570\u6700\u9ad8\u7684\u90a3\u884c\u8bb0\u5f55\uff08\u6b64\u6cd5\u7528\u7684\u662f\u662f\u5b50\u67e5\u8be2\uff0c\u5148\u627e\u51fa\u5206\u6700\u5927\u7684\u90a3\u4e2adegree\u518d\u627edegree\u6240\u5728\u90a3\u884c\uff0c\u6709\u4e24\u4e2a\u6700\u9ad8\u5206\u6545\u4e24\u884c\uff09\n +------------+-----------+--------+\n | Student_ID | Course_ID | degree |\n +------------+-----------+--------+\n | 2017052455 | 3-245     |     96 |\n | 2017052459 | 9-888     |     96 |\n +------------+-----------+--------+\nselect * from score order by degree desc limit 0,1;  --\u5148\u6309degree\u964d\u5e8f\u6392\u5217\uff0c\u518d\u627e\u51fa\u4ece0\u5f00\u59cb\u7684\u7b2c\u4e00\u9879\u3002\n+------------+-----------+--------+\n | Student_ID | Course_ID | degree |\n +------------+-----------+--------+\n | 2017052455 | 3-245     |     96 |\n +------------+-----------+--------+<\/pre>\n\n\n\n<p>10. \u8ba1\u7b97\u5e73\u5747\u503c<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select Course_ID,avg(degree) from score where Course_ID='3-245'; --\u7b97\u51fa\u8bfe\u7a0b3-245\u7684\u5e73\u5747\u5206\n +-----------+-------------+\n | Course_ID | avg(degree) |\n +-----------+-------------+\n | 3-245     |     88.0000 |\n +-----------+-------------+\nselect Course_ID,avg(degree) from score group by Course_ID; --\u6309\u8bfe\u7a0b\u7f16\u53f7\u5206\u7ec4\u518d\u7b97\u51fa\u5404\u81ea\u5e73\u5747\u5206\n+-----------+-------------+\n | Course_ID | avg(degree) |\n +-----------+-------------+\n | 3-105     |     78.0000 |\n | 3-245     |     88.0000 |\n | 6-166     |     85.5000 |\n | 9-888     |     89.5000 |\n +-----------+-------------+<\/pre>\n\n\n\n<p>11.\u67e5\u8be2\u62e5\u6709\u76f8\u540c\u503c\uff0c\u4e14\u76f8\u540c\u6b21\u6570\u5927\u4e8e\u67d0\u5b9a\u503c\u7684\u7ec4<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select Course_ID,count() from score group by Course_ID having count(Course_ID)>=2;             --\u67e5\u8be2\u8bfe\u7a0b\u4eba\u6570\u5927\u4e8e2\u7684\u8bfe\u7a0b\n +-----------+----------+ \n | Course_ID | count() |\n +-----------+----------+\n | 3-105     |        2 |\n | 3-245     |        3 |\n | 6-166     |        2 |\n | 9-888     |        2 |\n +-----------+----------+<\/pre>\n\n\n\n<p>12.\u6a21\u7cca\u67e5\u8be2<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select * from course where Num like '3%';               --\u67e5\u8be2\u4ee53\u5f00\u5934\u7684\u8bfe\u7a0b\uff08%\u662f\u901a\u914d\u7b26\uff09\n +-------+------------------+------------+\n | Num   | Name             | Teacher_ID |\n +-------+------------------+------------+\n | 3-105 | computer science | 1000001    |\n | 3-245 | operator system  | 1000001    |\n +-------+------------------+------------+<\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u67e5\u8be2 \u6570\u636e\u51c6\u5907\uff0c\u4ee5\u4e0b\u4ee5\u8fd9\u56db\u5f20\u8868\u6765\u505a\u4f8b\u5b50 1.\u5b66\u751f\u8868 select * from student; +&#8212;&hellip;<a href=\"https:\/\/www.notown.top\/?p=511\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Mysql\u57fa\u672c\u547d\u4ee4\u7528\u6cd5\uff08\u4e8c\uff09<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[15],"tags":[],"class_list":["post-511","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.notown.top\/index.php?rest_route=\/wp\/v2\/posts\/511","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.notown.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.notown.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.notown.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.notown.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=511"}],"version-history":[{"count":1,"href":"https:\/\/www.notown.top\/index.php?rest_route=\/wp\/v2\/posts\/511\/revisions"}],"predecessor-version":[{"id":512,"href":"https:\/\/www.notown.top\/index.php?rest_route=\/wp\/v2\/posts\/511\/revisions\/512"}],"wp:attachment":[{"href":"https:\/\/www.notown.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=511"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.notown.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=511"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.notown.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=511"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}