{"id":913,"date":"2022-03-19T22:08:08","date_gmt":"2022-03-19T14:08:08","guid":{"rendered":"https:\/\/www.buyao007.icu\/?p=913"},"modified":"2022-03-20T12:24:51","modified_gmt":"2022-03-20T04:24:51","slug":"6-sql%e8%af%ad%e5%8f%a5%e4%b9%8bdqldml%e6%95%b0%e6%8d%ae%e6%9f%a5%e8%af%a2%e8%af%ad%e8%a8%80","status":"publish","type":"post","link":"https:\/\/www.buyao007.icu\/?p=913","title":{"rendered":"6.SQL\u8bed\u53e5\u4e4bDQL(DML)\u6570\u636e\u67e5\u8be2\u8bed\u8a00"},"content":{"rendered":"\n

\u5b59\u5bcc\u9633\uff0c \u6c5f\u6e56\u4eba\u79f0\u6ca1\u4eba\u79f0\u3002\u591a\u5e74\u4e92\u8054\u7f51\u8fd0\u7ef4\u5de5\u4f5c\u7ecf\u9a8c\uff0c\u66fe\u8d1f\u8d23\u8fc7\u5b59\u5e03\u65af\u5927\u89c4\u6a21\u96c6\u7fa4\u67b6\u6784\u81ea\u52a8\u5316\u8fd0\u7ef4\u7ba1\u7406\u5de5\u4f5c\u3002\u64c5\u957fWeb\u96c6\u7fa4\u67b6\u6784\u4e0e\u81ea\u52a8\u5316\u8fd0\u7ef4\uff0c\u66fe\u8d1f\u8d23\u56fd\u5185\u67d0\u5927\u578b\u535a\u5ba2\u7f51\u7ad9\u8fd0\u7ef4\u5de5\u4f5c\u3002<\/p>\n\n\n\n

1.\u67e5\u8be2select<\/h2>\n\n\n\n

1.select\u5355\u72ec\u4f7f\u7528<\/em><\/strong><\/p>\n\n\n\n

1.\u67e5\u8be2\u7cfb\u7edf\u53d8\u91cf\uff08\u914d\u7f6e\u53c2\u6570\uff09<\/strong>\n#\u67e5\u7aef\u53e3\nmysql> select @@port;\n#\u67e5\u770b\u5de5\u4f5c\u76ee\u5f55\nmysql> select @@basedir;\n#\u67e5\u8be2\u6570\u636e\u76ee\u5f55\nmysql> select @@datadir;\n#\u592a\u9ebb\u70e6\u4e86\u8fd8\u4e0d\u5982\u4f7f\u7528show\u6765\u67e5\u8be2\n#\u67e5\u8be2\u6240\u6709\u53d8\u91cfshow variables \nmysql> show variables;\n#\u6a21\u7cca\u67e5\u8be2\u53d8\u91cf\nmysql> show variables like '%safe%';\n2.\u8c03\u7528\u51fd\u6570<\/strong>\n#\u67e5\u8be2\u7cfb\u7edf\u5f53\u524d\u65f6\u95f4\nmysql> select now();\n#\u67e5\u8be2\u7528\u6237\nmysql> select user();\n#\u67e5\u8be2\u5f53\u524d\u6240\u5728\u5e93\nmysql> select database();\n<\/code><\/pre>\n\n\n\n
2.\u6807\u51c6select\u5e94\u7528<\/em><\/strong><\/p>\n\n\n\n
select \u5217\u2026 from \u8868\nwhere\ngroup by\nhaving\norder by\nlimit\n<\/code><\/pre>\n\n\n\n
3.select+from\u4f7f\u7528<\/em><\/strong><\/p>\n\n\n\n
#\u5bfc\u5165\u4e00\u4e2aworld\u5e93\uff08mysql\u5b98\u65b9\u7684\u6587\u4ef6\uff09\n\u4e0b\u8f7d\u5730\u5740https:\/\/downloads.mysql.com\/docs\/world-db.tar.gz\nmysql> source \/root\/world.sql\n##\u67e5\u8be2\u8868\u4e2d\u6240\u6709\u6570\u636e\nmysql> select * from world.city;\n##\u67e5\u8be2\u8868\u4e2d\u90e8\u5206\u5217\u7684\u6570\u636e\nmysql> select name,population from world.city;\n###\u6ce8\u610f\u8fd9\u662f\u5168\u8868\u626b\u63cf\uff0c\u6027\u80fd\u975e\u5e38\u5dee\uff0c\u5c0f\u8868\u8fd8\u597d\uff0c\u5927\u8868\u4f1a\u5f88\u8017\u5185\u5b58\n<\/code><\/pre>\n\n\n\n
4.select+from+where\u4f7f\u7528<\/em><\/strong><\/p>\n\n\n\n
###where+\u7b49\u503c\u67e5\u8be2<\/strong>\n   #\u67e5\u8be2\u4e2d\u56fd\u6240\u6709\u7684\u57ce\u5e02\n   mysql> select * from world.city where countrycode='chn';\n   #\u67e5\u8be2\u4e2d\u56fd\u57ce\u5e02\u540d\u548c\u4eba\u53e3\u6570\n   mysql> select name,population from world.city where countrycode='chn';\n###where+\u4e0d\u7b49\u503c\u67e5\u8be2\uff08\u6761\u4ef6\u5224\u65ad\u7b26\uff08<> <= >= !=\uff09\uff09<\/strong>\n   #\u67e5\u8be2\u5c11\u4e8e100\u4eba\u7684\u57ce\u5e02\u4fe1\u606f\n   mysql> select * from world.city where population<'100';\n### where+\u4e0e\u6216\u975e\u67e5\u8be2\uff08AND OR\uff09<\/strong>\n   ###\u67e5\u8be2\u4e2d\u56fd\u4eba\u53e3\u6570\u8d85\u8fc7500\u4e07\u7684\u4eba\u6570\n   mysql> select * from world.city where population>'500' and countrycode='chn';\n   ##\u67e5\u8be2\u57ce\u5e02\u540d\u5b57\u4e3ayantai\u6216\u8005tianjin\u7684\u4fe1\u606f\n   mysql> select * from world.city where name='yantai' or name='tianjin';\n###where+like\u6a21\u7cca\u67e5\u8be2<\/strong>\n   #\u67e5\u8be2\u56fd\u5bb6\u4ee3\u53f7\u662fch\u5f00\u5934\u7684\u57ce\u5e02\u4fe1\u606f\n   mysql> select * from world.city where  countrycode like 'ch%';\n###where+not in + like\u53d6\u53cd<\/strong>\n   mysql> select * from world.city where countrycode not in (select countrycode from world.city where countrycode like 'ch%');\n### where+between and<\/strong>\n   ##\u67e5\u8be2\u4eba\u53e3\u6570\u5728100W\u5230200\u4e07\u4e4b\u95f4\u7684\u57ce\u5e02\u4fe1\u606f\n   select * from city where population between 1000000 and 2000000;\n   \u7b49\u4ef7\u4e0eselect * from city where population >=1000000 and population <=2000000;\n<\/code><\/pre>\n\n\n\n
5.select\u7684group by+\u805a\u5408\u51fd\u6570\u5b50\u53e5\u4f7f\u7528<\/em><\/strong><\/p>\n\n\n\n
select + from + where + group by +\u805a\u5408\u51fd\u6570\uff08count\uff08\uff09\uff0csum\uff08\uff09\uff0cavg\uff08\uff09\uff0cmax\uff08\uff09\uff0cmin\uff08\uff09\uff0cgroup_concat()\uff09<\/code><\/pre>\n\n\n\n
1.group by \u4f5c\u7528<\/p>\n\n\n\n
\u6309\u7167\u56fa\u5b9a\u6761\u4ef6\u8fdb\u884c\u5206\u7ec4\r\n##\u4f8b\u5982\u67e5\u8be2city\u8868\u6ca1\u4e2a\u56fd\u5bb6\u51fa\u73b0\u7684\u6b21\u6570\r\nmysql> select CountryCode,count(Name) from world.city group by CountryCode;\r<\/code><\/pre>\n\n\n\n
2.sql_mode\u53cagroup by\u7528\u6cd5<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
##\u7b97\u4e00\u4e0b\u6bcf\u4e2a\u57ce\u5e02\u7684\u4eba\u53e3\u6570\uff0c\u6b63\u786e\u5199\u6cd5\uff0c<\/strong>\r\nmysql> select countrycode ,sum(population) from world.city group by countrycode;\r\n###\u5982\u679c\u6709group by \uff0cselect\u540e\u7684\u5217\uff0c\u8981\u4e48\u662fgroup by\u6761\u4ef6\uff0c\u8981\u4e48\u662f\u5728\u805a\u5408\u51fd\u6570\u91cc\u5904\u7406\r<\/code><\/pre>\n\n\n\n
###\u67e5\u4e00\u4e0b\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u4eba\u53e3\u603b\u6570<\/strong>\nmysql> select countrycode , district,sum(population) from world.city where countrycode='chn' group by countrycode,district ;\r\n\r\n##\u67e5\u8be2\u4e2d\u56fd\u6bcf\u4e2a\u7701\u57ce\u5e02\u7684\u4e2a\u6570\r\nmysql> select countrycode , district,count(population) from world.city  where countrycode='chn'   group by countrycode,district ;\r\n###\u67e5\u8be2\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u57ce\u5e02\u4e2a\u6570\uff0c\u5e76\u663e\u793a\u51fa\u57ce\u5e02\u540d\r\nmysql> select district,group_concat(name),count(population) from world.city where countrycode='chn' group by district ;\r<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
6.select\u7684having\u00a0 group by\uff08\u540e\u8fc7\u6ee4\uff09<\/em><\/strong><\/p>\n\n\n\n
##\u67e5\u8be2\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u57ce\u5e02\u4e2a\u6570\uff0c\u663e\u793a\u51fa\u57ce\u5e02\u540d\uff0c\u8fc7\u6ee4\u51fa\u57ce\u5e02\u6570\u5c0f\u4e8e5\u5e76\u4e14\u57ce\u5e02\u4ee5ch\u5f00\u5934\u7684\u7701\r\nmysql> select district,group_concat(name),count(population) from world.city where countrycode='chn' group by district having count(population)<5 and group_concat(name) like 'Ch%' ;\r\n#####\u6ce8\u610f\u5c3d\u91cf\u4e0d\u8981\u4f7f\u7528\u8fd9\u4e2a\u5b50\u53e5\uff0c\u5bf9\u6027\u80fd\u5f71\u54cd\u5341\u5206\u5927\r<\/code><\/pre>\n\n\n\n
7.select\u7684order by\uff08\u6392\u5e8f\uff09<\/em><\/strong><\/p>\n\n\n\n
#\u6309\u4e2d\u56fd\u7684\u4eba\u53e3\u6570\u8fdb\u884c\u6392\u540d<\/strong>\r\nmysql> select * from world.city where countrycode='chn' order by population;\r\n#\u6309\u4e2d\u56fd\u7684\u4eba\u53e3\u6570\u4ece\u5927\u5230\u5c0f\u8fdb\u884c\u6392\u540d<\/strong>\r\nmysql> select * from world.city where countrycode='chn' order by population desc ;\r\n#\u7edf\u8ba1\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u8fc7\u6ee4\u8f93\u51fa\u603b\u4eba\u53e3\u8d85\u8fc71000w\uff0c\u4ece\u5927\u5230\u5c0f\u6392\u5e8f<\/strong>\r\nmysql> select District,sum(Population),group_concat(Name) from world.city  where CountryCode='chn' group by District having sum(Population)>10000000 order by sum(Population) desc;\r<\/code><\/pre>\n\n\n\n
8.select\u7684limit\uff08\u9650\u5236\u8f93\u51fa\uff09<\/em><\/strong><\/p>\n\n\n\n
#\u7edf\u8ba1\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u8fc7\u6ee4\u8f93\u51fa\u603b\u4eba\u53e3\u8d85\u8fc71000w\uff0c\u4ece\u5927\u5230\u5c0f\u6392\u5e8f\uff0c\u7136\u540e\u53ea\u8f93\u51fa\u524d5\u884c<\/strong>\r\nmysql> select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) desc limit 5 ;\r\n#\u7edf\u8ba1\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u8fc7\u6ee4\u8f93\u51fa\u603b\u4eba\u53e3\u8d85\u8fc71000w\uff0c\u4ece\u5927\u5230\u5c0f\u6392\u5e8f\uff0c\u7136\u540e\u53ea\u8f93\u51fa\u524d6-10\u884c<\/strong>\r\nmysql> select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) desc limit 5,5;\r\n####\u4e0a\u9762\u4e00\u6761\u547d\u4ee4\u7b49\u540c\u4e8e\u4e0b\u9762\u7684\u547d\u4ee4\uff0c\u610f\u601d\u662f\u8df3\u8fc75\u884c\uff0c\u663e\u793a5\u884c<\/strong>\r\nmysql> select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) desc limit 5 offset 5;\r<\/code><\/pre>\n\n\n\n
9.select\u7684union\uff08\u5c06\u591a\u4e2a\u67e5\u8be2\u5408\u5e76\uff09<\/em><\/strong><\/p>\n\n\n\n
#\u7edf\u8ba1\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u8fc7\u6ee4\u8f93\u51fa\u603b\u4eba\u53e3\u8d85\u8fc71000w\uff0c\u4ece\u5927\u5230\u5c0f\u6392\u5e8f\uff0c\u7136\u540e\u53ea\u8f93\u51fa\u524d3\u548c\u540e3\u884c\r\nmysql> (select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) desc limit 3) union all (select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) limit 3) ;\r<\/code><\/pre>\n\n\n\n
2.\u591a\u8868\u8fde\u63a5<\/h2>\n\n\n\n
1.\u7b1b\u5361\u5c14\u4e58\u79ef\u8fde\u63a5\u65b9\u5f0f<\/em><\/strong><\/p>\n\n\n\n
\u521b\u5efa\u4e24\u4e2a\u8868<\/strong>\ncreate table a (id int not null primary key auto_increment,name varchar(20) not null ,age tinyint);\r\ncreate table b (aid int not null,addr varchar(20) not null ,telnum char(11));\r\n\u5206\u522b\u63d2\u5165\u6570\u636e<\/strong>\nInsert into a values(1,\u2019zs\u2019,\u201918\u2019),(2,\u2019ls\u2019,\u201919\u2019),(3,\u2019w5\u2019,\u201920\u2019),(5,\u2019chunchun\u2019,\u201925\u2019);\r\nInsert into b values(1,\u2019bj\u2019,\u2019110\u2019),(2,\u2019bj\u2019,\u2019120\u2019),(3,\u2019sh\u2019,\u2019119\u2019),(4,\u2019sz\u2019,\u2019114\u2019);\r\n\u7b1b\u5361\u5c14\u4e58\u79ef\u793a\u4f8b<\/strong>\nmysql> select * from test.a,test.b;<\/code><\/pre>\n\n\n\n
\"\"
\u7b1b\u5361\u5c14\u4e58\u79ef\u793a\u4f8b\u622a\u56fe<\/figcaption><\/figure>\n\n\n\n
2.\u5185\u8fde\u63a5<\/em><\/strong><\/p>\n\n\n\n
\"\"
\u5728\u7b1b\u5361\u5c14\u4e58\u79ef\u57fa\u7840\u4e0a\u53d6\u7684\u662f\u96c6\u5408<\/figcaption><\/figure>\n\n\n\n
\u5185\u8fde\u63a5\u793a\u4f8b\nmysql> select * from test.a,test.b where test.a.id=test.b.aid;\n\u4e0b\u9762\u6548\u679c\u4e0e\u4e0a\u9762\u7684\u7ed3\u679c\u4e00\u6837\nmysql> select * from test.a join test.b on test.a.id=test.b.aid;\r\n<\/code><\/pre>\n\n\n\n
3\u5916\u8fde\u63a5<\/em><\/strong><\/p>\n\n\n\n
\u5916\u8fde\u63a5\u5206\u4e3a\u5de6\u5916\u8fde\u548c\u53f3\u5916\u8fde<\/strong>\n\u53d6\u7684\u5c31\u4e0d\u662f\u96c6\u5408\u4e86\uff0c\u5de6\u8fde\u63a5\u53d6\u7684\u662f\u5de6\u8fb9\u7684\u503c\u548c\u4ed6\u4eec\u7684\u96c6\u5408\uff0c\u53f3\u8fde\u63a5\u53d6\u7684\u662f\u53f3\u8fb9\u7684\u503c\u548c\u4ed6\u4eec\u7684\u96c6\u5408\n###\u5de6\u8fde\u63a5<\/strong>\uff0c\u5de6\u8fde\u63a5\u5c31\u662f\u5de6\u9762\u7684\u5168\u90e8\u6253\u5370\u51fa\u6765\uff0c\u53f3\u9762\u7684\u7b26\u5408\u6761\u4ef6\u7684\u6253\u5370\u51fa\u6765\uff0c\u53f3\u8fde\u63a5\u540c\u7406<\/strong>\nmysql> select * from test.a left join test.b on test.a.id=test.b.aid;\n###\u53f3\u8fde\u63a5<\/strong>\nmysql> select * from test.a right join test.b on test.a.id=test.b.aid;\n###\u4e09\u4e2a\u8868\u600e\u4e48\u67e5\u8be2\u5462\uff1f<\/strong>\nmysql> select * from a left join b  on  a.id=b.aid left join c on b.aid=c.cid;\n\u53d6\u5dee\u79ef<\/strong>\r\nmysql> select * from test.a left join test.b on test.a.id=test.b.aid where aid is null;\r\nPS<\/strong>\nmysql> select * from world.city where Name in ('yantai','tianjin');\r\n\u7b49\u6548\u4e8ewhere name=yantai or name=tianjin\r<\/code><\/pre>\n\n\n\n
4.\u591a\u8868\u8fde\u63a5\u4e4bcase\u8bed\u53e5<\/em><\/strong><\/p>\n\n\n\n
mysql> select course.cname,group_concat(case when score.num>80 then student.sname end)'\u4f18\u79c0' from course join score on course.cid=score.course_id join student on score.student_id=student.sid group by course.cname ;<\/code><\/pre>\n\n\n\n
3.\u522b\u540d\u5e94\u7528<\/h2>\n\n\n\n
1.\u7ed9\u5217\u8bbe\u7f6e\u522b\u540d<\/em><\/strong><\/p>\n\n\n\n
mysql>  select student.sname,avg(score.num) from student join score on student.sid=score.student_id group by student.sname ;<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
mysql>  select student.sname,avg(score.num) as '\u5e73\u5747\u5206' from student join score on student.sid=score.student_id group by student.sname ;<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
##\u6ce8\u610f\uff0c\u5217\u522b\u540d\u8981\u653e\u5728group by\u4e4b\u540e\u7684\u5b50\u53e5\u624d\u80fd\u5f15\u7528\uff0cwhere\u65e0\u6cd5\u4f7f\u7528\u5217\u7684\u522b\u540d\r\nmysql>  select student.sname as '\u59d3\u540d',avg(score.num) as '\u5e73\u5747\u5206' from student join score on student.sid=score.student_id  group by student.sname having \u5e73\u5747\u5206>60;\r<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
2.\u7ed9\u8868\u8bbe\u7f6e\u522b\u540d<\/em><\/strong><\/p>\n\n\n\n
from \u6216\u8005join\u540e\u5b9a\u4e49\u7684\u8868\u7684\u522b\u540d\uff0c\u800c\u4e14\u8868\u522b\u540d\u652f\u6301\u6240\u6709\u5730\u65b9\u8c03\u7528\r\nmysql>  select student.sname as '\u59d3\u540d',avg(score.num) as '\u5e73\u5747\u5206' from student join score on student.sid=score.student_id  group by student.sname having \u5e73\u5747\u5206>60;\r<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
mysql> select a.sname as '\u59d3\u540d',avg(b.num) as '\u5e73\u5747\u5206' from student as a join score as b on a.sid=b.student_id group by a.sname having \u5e73\u5747\u5206>60;<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u5b59\u5bcc\u9633\uff0c \u6c5f\u6e56\u4eba\u79f0\u6ca1\u4eba\u79f0\u3002\u591a\u5e74\u4e92\u8054\u7f51\u8fd0\u7ef4\u5de5\u4f5c\u7ecf\u9a8c\uff0c\u66fe\u8d1f\u8d23\u8fc7\u5b59\u5e03\u65af\u5927\u89c4\u6a21\u96c6\u7fa4\u67b6\u6784\u81ea\u52a8\u5316\u8fd0\u7ef4\u7ba1\u7406\u5de5\u4f5c\u3002\u64c5\u957fWeb\u96c6 […]<\/p>\n","protected":false},"author":1,"featured_media":246,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"_links":{"self":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/913"}],"collection":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=913"}],"version-history":[{"count":12,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/913\/revisions"}],"predecessor-version":[{"id":943,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/posts\/913\/revisions\/943"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=\/wp\/v2\/media\/246"}],"wp:attachment":[{"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=913"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=913"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.buyao007.icu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}