{"id":227,"date":"2024-06-18T12:23:19","date_gmt":"2024-06-18T04:23:19","guid":{"rendered":"http:\/\/zhaoyanqi.cn\/?p=227"},"modified":"2024-06-18T12:23:20","modified_gmt":"2024-06-18T04:23:20","slug":"mysql%e5%87%bd%e6%95%b0","status":"publish","type":"post","link":"https:\/\/zhaoyanqi.cn\/?p=227","title":{"rendered":"mysql\u51fd\u6570"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\" id=\"eUhKI\">MySQL \u5185\u7f6e\u51fd\u6570<\/h3>\n\n\n\n<p id=\"u0fb2a980\">MySQL\u4e2d\u63d0\u4f9b\u4e86\u8bb8\u591a\u5185\u7f6e\u51fd\u6570\uff0c\u4ee5\u4e0b\u662f\u4e00\u4e9b\u5e38\u7528\u7684\u51fd\u6570\u53ca\u5176\u793a\u4f8b\uff1a<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"1f995d8a\">\u4e00\u3001\u6570\u5b66\u51fd\u6570<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>ROUND(x, y)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u53c2\u6570x\u7684\u56db\u820d\u4e94\u5165\u7684\u6709y\u4f4d\u5c0f\u6570\u7684\u503c\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"6de61922\" class=\"wp-block-code\"><code>SELECT ROUND(123.456, 2);  -- \u7ed3\u679c\uff1a123.46<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>RAND()<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de0\u52301\u5185\u7684\u968f\u673a\u503c\uff0c\u53ef\u4ee5\u901a\u8fc7\u63d0\u4f9b\u4e00\u4e2a\u53c2\u6570\uff08\u79cd\u5b50\uff09\u4f7fRAND()\u968f\u673a\u6570\u751f\u6210\u5668\u751f\u6210\u4e00\u4e2a\u6307\u5b9a\u7684\u503c\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"5301f31c\" class=\"wp-block-code\"><code>SELECT RAND(); -- \u7ed3\u679c\uff1a\u968f\u673a\u6570<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"7f675895\">\u4e8c\u3001\u805a\u5408\u51fd\u6570\uff08\u5e38\u7528\u4e8eGROUP BY\u4ece\u53e5\u7684SELECT\u67e5\u8be2\u4e2d\uff09<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>AVG(col)<\/strong> \u8fd4\u56de\u6307\u5b9a\u5217\u7684\u5e73\u5747\u503c\u3002<\/li>\n\n\n\n<li><strong>COUNT(col)<\/strong> \u8fd4\u56de\u6307\u5b9a\u5217\u4e2d\u975eNULL\u503c\u7684\u4e2a\u6570\u3002<\/li>\n\n\n\n<li><strong>MIN(col)<\/strong> \u8fd4\u56de\u6307\u5b9a\u5217\u7684\u6700\u5c0f\u503c\u3002<\/li>\n\n\n\n<li><strong>MAX(col)<\/strong> \u8fd4\u56de\u6307\u5b9a\u5217\u7684\u6700\u5927\u503c\u3002<\/li>\n\n\n\n<li><strong>SUM(col)<\/strong> \u8fd4\u56de\u6307\u5b9a\u5217\u7684\u6240\u6709\u503c\u4e4b\u548c\u3002<\/li>\n\n\n\n<li><strong>GROUP_CONCAT(col)<\/strong> \u8fd4\u56de\u7531\u5c5e\u4e8e\u4e00\u7ec4\u7684\u5217\u503c\u8fde\u63a5\u7ec4\u5408\u800c\u6210\u7684\u7ed3\u679c\u3002<\/li>\n<\/ol>\n\n\n\n<pre id=\"c9d5c998\" class=\"wp-block-code\"><code>SELECT AVG(salary) FROM employee;\nSELECT COUNT(id) FROM employee;\nSELECT MIN(salary) FROM employee;\nSELECT MAX(salary) FROM employee;\nSELECT SUM(salary) FROM employee;\nSELECT GROUP_CONCAT(name) FROM employee GROUP BY department_id;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"3afa1db0\">\u4e09\u3001\u5b57\u7b26\u4e32\u51fd\u6570<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>CHAR_LENGTH(str)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u5b57\u7b26\u4e32str\u7684\u957f\u5ea6\uff0c\u957f\u5ea6\u7684\u5355\u4f4d\u4e3a\u5b57\u7b26\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"32fb8f4e\" class=\"wp-block-code\"><code>SELECT CHAR_LENGTH('Hello World');  -- \u7ed3\u679c\uff1a11<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>CONCAT(str1, str2, &#8230;)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5b57\u7b26\u4e32\u62fc\u63a5\uff0c\u5982\u6709\u4efb\u4f55\u4e00\u4e2a\u53c2\u6570\u4e3aNULL\uff0c\u5219\u8fd4\u56de\u503c\u4e3aNULL\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"ea53af92\" class=\"wp-block-code\"><code>SELECT CONCAT('Hello', ' ', 'World');  -- \u7ed3\u679c\uff1aHello World<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li><strong>CONCAT_WS(separator, str1, str2, &#8230;)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5b57\u7b26\u4e32\u62fc\u63a5\uff08\u81ea\u5b9a\u4e49\u8fde\u63a5\u7b26\uff09\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"c00bf29e\" class=\"wp-block-code\"><code>SELECT CONCAT_WS('-', '2024', '06', '18');  -- \u7ed3\u679c\uff1a2024-06-18<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li><strong>CONV(N, from_base, to_base)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fdb\u5236\u8f6c\u6362\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"d307db18\" class=\"wp-block-code\"><code>SELECT CONV('a', 16, 2);  -- \u628a16\u8fdb\u5236\u6570\u2018a\u2019\u8f6c\u6362\u62102\u7981\u6b62\uff0c\u7ed3\u679c\uff1a1010<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"5\">\n<li><strong>FORMAT(X, D)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5c06\u6570\u5b57X\u7684\u683c\u5f0f\u5199\u4e3a&#8217;#,###,###.##&#8217;\uff0c\u4ee5\u56db\u820d\u4e94\u5165\u7684\u65b9\u5f0f\u4fdd\u7559\u5c0f\u6570\u70b9\u540eD\u4f4d\uff0c\u5e76\u5c06\u7ed3\u679c\u4ee5\u5b57\u7b26\u4e32\u7684\u5f62\u5f0f\u8fd4\u56de\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"371421bf\" class=\"wp-block-code\"><code>SELECT FORMAT(12332.1, 4);  -- \u7ed3\u679c\uff1a12,332.1000<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"6\">\n<li><strong>INSERT(str, pos, len, newstr)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5728str\u7684\u6307\u5b9a\u4f4d\u7f6e\u63d2\u5165\u5b57\u7b26\u4e32\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"0c04cf43\" class=\"wp-block-code\"><code>SELECT INSERT('Hello World', 7, 5, 'MySQL');  -- \u7ed3\u679c\uff1aHello MySQL<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"7\">\n<li><strong>INSTR(str, substr)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u5b57\u7b26\u4e32str\u4e2d\u5b50\u5b57\u7b26\u4e32\u7684\u7b2c\u4e00\u4e2a\u51fa\u73b0\u4f4d\u7f6e\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"f6101790\" class=\"wp-block-code\"><code>SELECT INSTR('Hello World', 'World');  -- \u7ed3\u679c\uff1a7<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"8\">\n<li><strong>LEFT(str, len)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u5b57\u7b26\u4e32str\u4ece\u5f00\u59cb\u7684len\u4f4d\u7f6e\u7684\u5b50\u5e8f\u5217\u5b57\u7b26\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"48bf7688\" class=\"wp-block-code\"><code>SELECT LEFT('Hello World', 5);  -- \u7ed3\u679c\uff1aHello<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"9\">\n<li><strong>LOWER(str)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u53d8\u5c0f\u5199\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"535a11ff\" class=\"wp-block-code\"><code>SELECT LOWER('Hello World');  -- \u7ed3\u679c\uff1ahello world<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"10\">\n<li><strong>UPPER(str)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u53d8\u5927\u5199\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"7165dc62\" class=\"wp-block-code\"><code>SELECT UPPER('Hello World');  -- \u7ed3\u679c\uff1aHELLO WORLD<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"11\">\n<li><strong>REVERSE(str)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u5b57\u7b26\u4e32str\uff0c\u987a\u5e8f\u548c\u5b57\u7b26\u987a\u5e8f\u76f8\u53cd\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"b9e00ede\" class=\"wp-block-code\"><code>SELECT REVERSE('Hello World');  -- \u7ed3\u679c\uff1adlroW olleH<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"12\">\n<li><strong>SUBSTRING(str, pos)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u4ece\u5b57\u7b26\u4e32str\u7684pos\u4f4d\u7f6e\u5f00\u59cb\u7684\u5b50\u5b57\u7b26\u4e32\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"262754bd\" class=\"wp-block-code\"><code>SELECT SUBSTRING('Quadratically', 5);  -- \u7ed3\u679c\uff1aratically<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"692bb6b4\">\u56db\u3001***\u65e5\u671f\u548c\u65f6\u95f4\u51fd\u6570\uff08\u91cd\u70b9\uff1a<strong>DATE_FORMA<\/strong>\uff09<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>CURDATE() \u6216 CURRENT_DATE()<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u5f53\u524d\u7684\u65e5\u671f\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"05966788\" class=\"wp-block-code\"><code>SELECT CURDATE();  -- \u7ed3\u679c\uff1a\u5f53\u524d\u65e5\u671f<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>CURTIME() \u6216 CURRENT_TIME()<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u5f53\u524d\u7684\u65f6\u95f4\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"adf4543b\" class=\"wp-block-code\"><code>SELECT CURTIME();  -- \u7ed3\u679c\uff1a\u5f53\u524d\u65f6\u95f4<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li><strong>DAYOFWEEK(date)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56dedate\u6240\u4ee3\u8868\u7684\u4e00\u661f\u671f\u4e2d\u7684\u7b2c\u51e0\u5929(1~7)\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"d5ab8cfc\" class=\"wp-block-code\"><code>SELECT DAYOFWEEK('2024-06-18');  -- \u7ed3\u679c\uff1a3\uff08\u661f\u671f\u4e8c\uff09<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li>***<strong>DATE_FORMAT(date, format)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u6839\u636eformat\u5b57\u7b26\u4e32\u683c\u5f0f\u5316date\u503c\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"39b13030\" class=\"wp-block-code\"><code>SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');  -- \u7ed3\u679c\uff1aSunday October 2009<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"77dfcf56\">\u4e94\u3001\u52a0\u5bc6\u51fd\u6570<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>MD5()<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8ba1\u7b97\u5b57\u7b26\u4e32str\u7684MD5\u6821\u9a8c\u548c\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"f0965434\" class=\"wp-block-code\"><code>SELECT MD5('password');  -- \u7ed3\u679c\uff1a5f4dcc3b5aa765d61d8327deb882cf99<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>PASSWORD(str)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u8fd4\u56de\u5b57\u7b26\u4e32str\u7684\u52a0\u5bc6\u7248\u672c\uff0c\u8fd9\u4e2a\u52a0\u5bc6\u8fc7\u7a0b\u662f\u4e0d\u53ef\u9006\u8f6c\u7684\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"fbb0f971\" class=\"wp-block-code\"><code>SELECT PASSWORD('password');  -- \u7ed3\u679c\uff1a*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"fd7fc5f2\">\u516d\u3001\u63a7\u5236\u6d41\u51fd\u6570<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>CASE WHEN [test1] THEN [result1]&#8230; ELSE [default] END<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5982\u679ctestN\u662f\u771f\uff0c\u5219\u8fd4\u56deresultN\uff0c\u5426\u5219\u8fd4\u56dedefault\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"45b018e5\" class=\"wp-block-code\"><code>SELECT CASE WHEN age &lt; 18 THEN 'Minor' ELSE 'Adult' END FROM persons;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>IF(test, t, f)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5982\u679ctest\u662f\u771f\uff0c\u8fd4\u56det\uff1b\u5426\u5219\u8fd4\u56def\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"64371822\" class=\"wp-block-code\"><code>SELECT IF(salary &gt; 5000, 'High', 'Low') FROM employee;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li><strong>IFNULL(arg1, arg2)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5982\u679carg1\u4e0d\u662f\u7a7a\uff0c\u8fd4\u56dearg1\uff0c\u5426\u5219\u8fd4\u56dearg2\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"fba9a8bc\" class=\"wp-block-code\"><code>SELECT IFNULL(email, 'noemail@example.com') FROM employee;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li><strong>NULLIF(arg1, arg2)<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u5982\u679carg1=arg2\u8fd4\u56deNULL\uff1b\u5426\u5219\u8fd4\u56dearg1\u3002<\/li>\n<\/ul>\n\n\n\n<pre id=\"01daffad\" class=\"wp-block-code\"><code>SELECT NULLIF(1, 1);  -- \u7ed3\u679c\uff1aNULL\nSELECT NULLIF(1, 2);  -- \u7ed3\u679c\uff1a1<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"qyuFW\">\u63a7\u5236\u6d41\u51fd\u6570\u5c0f\u7ec3\u4e60<\/h5>\n\n\n\n<pre id=\"712c9281\" class=\"wp-block-code\"><code>#\u51c6\u5907\u8868\nDROP TABLE IF EXISTS `course`;\nCREATE TABLE `course` (\n  `c_id` int(11) NOT NULL,\n  `c_name` varchar(255) DEFAULT NULL,\n  `t_id` int(11) DEFAULT NULL,\n  PRIMARY KEY (`c_id`),\n  KEY `t_id` (`t_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\nINSERT INTO `course` VALUES ('1', 'python', '1');\nINSERT INTO `course` VALUES ('2', 'java', '2');\nINSERT INTO `course` VALUES ('3', 'linux', '3');\nINSERT INTO `course` VALUES ('4', 'web', '2');\n\nDROP TABLE IF EXISTS `score`;\nCREATE TABLE `score` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `s_id` int(10) DEFAULT NULL,\n  `c_id` int(11) DEFAULT NULL,\n  `num` double DEFAULT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;\n\nINSERT INTO `score` VALUES \n('1', '1', '1', '79'),\n('2', '1', '2', '78'),\n('3', '1', '3', '35'),\n('4', '2', '2', '32'),\n('5', '3', '1', '66'),\n('6', '4', '2', '77'),\n('7', '4', '1', '68'),\n('8', '5', '1', '66'),\n('9', '2', '1', '69'),\n('10', '4', '4', '75'),\n('11', '5', '4', '66.7');\n\nDROP TABLE IF EXISTS `student`;\nCREATE TABLE `student` (\n  `s_id` varchar(20) NOT NULL,\n  `s_name` varchar(255) DEFAULT NULL,\n  `s_age` int(10) DEFAULT NULL,\n  `s_sex` char(1) DEFAULT NULL,\n  PRIMARY KEY (`s_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\nINSERT INTO `student` VALUES \n('1', '\u9c81\u73ed', '12', '\u7537'),\n('2', '\u8c82\u8749', '20', '\u5973'),\n('3', '\u5218\u5907', '35', '\u7537'),\n('4', '\u5173\u7fbd', '\n\n34', '\u7537'),\n('5', '\u5f20\u98de', '33', '\u5973');\n\nDROP TABLE IF EXISTS `teacher`;\nCREATE TABLE `teacher` (\n  `t_id` int(10) NOT NULL,\n  `t_name` varchar(50) DEFAULT NULL,\n  PRIMARY KEY (`t_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\nINSERT INTO `teacher` VALUES \n('1', '\u5927\u738b'),\n('2', 'alex'),\n('3', 'egon'),\n('4', 'peiqi');\n\n# \u7edf\u8ba1\u5404\u79d1\u5404\u5206\u6570\u6bb5\u4eba\u6570.\u663e\u793a\u683c\u5f0f:\u8bfe\u7a0bID,\u8bfe\u7a0b\u540d\u79f0,&#91;100-85],&#91;85-70],&#91;70-60],&#91; &lt;60]\nSELECT  \n    score.c_id,\n    course.c_name, \n    SUM(CASE WHEN num BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '&#91;100-85]',\n    SUM(CASE WHEN num BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '&#91;85-70]',\n    SUM(CASE WHEN num BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '&#91;70-60]',\n    SUM(CASE WHEN num &lt; 60 THEN 1 ELSE 0 END) AS '&#91; &lt;60]'\nFROM \n    score\nJOIN \n    course \nON \n    score.c_id = course.c_id \nGROUP BY \n    score.c_id;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"b3339e54\">DATE_FORMAT\u51fd\u6570<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"b42e0d75\">1. \u57fa\u672c\u4f7f\u7528<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u5c06\u65e5\u671f\u683c\u5f0f\u5316\u4e3a\u661f\u671f\u3001\u6708\u4efd\u548c\u5e74\u4efd<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"28f035a6\" class=\"wp-block-code\"><code>SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');\n-- \u7ed3\u679c\uff1a'Sunday October 2009'<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>\u5c06\u65f6\u95f4\u683c\u5f0f\u5316\u4e3a\u5c0f\u65f6\u3001\u5206\u949f\u548c\u79d2<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"f3ed864e\" class=\"wp-block-code\"><code>SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');\n-- \u7ed3\u679c\uff1a'22:23:00'<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li><strong>\u5c06\u65e5\u671f\u548c\u65f6\u95f4\u683c\u5f0f\u5316\u4e3a\u591a\u4e2a\u683c\u5f0f<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"bca5adc0\" class=\"wp-block-code\"><code>SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');\n-- \u7ed3\u679c\uff1a'4th 00 Thu 04 10 Oct 277'<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li><strong>\u5c06\u65f6\u95f4\u683c\u5f0f\u5316\u4e3a\u4e0d\u540c\u7684\u65f6\u95f4\u8868\u793a<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"dfb61b0f\" class=\"wp-block-code\"><code>SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');\n-- \u7ed3\u679c\uff1a'22 22 10 10:23:00 PM 22:23:00 00 6'<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"5\">\n<li><strong>\u5c06\u65e5\u671f\u683c\u5f0f\u5316\u4e3a\u5e74\u4efd\u548c\u5468\u6570<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"7c3bfdde\" class=\"wp-block-code\"><code>SELECT DATE_FORMAT('1999-01-01', '%X %V');\n-- \u7ed3\u679c\uff1a'1998 52'<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"6\">\n<li><strong>\u5c06\u65e5\u671f\u683c\u5f0f\u5316\u4e3a\u5929<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"dd6726ac\" class=\"wp-block-code\"><code>SELECT DATE_FORMAT('2006-06-00', '%d');\n-- \u7ed3\u679c\uff1a'00'<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"8387b275\">2. DATE_FORMAT\u51fd\u6570\u5b9e\u9a8c<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u521b\u5efa\u8868\u548c\u63d2\u5165\u6570\u636e<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"1384cca2\" class=\"wp-block-code\"><code>CREATE TABLE blog (\n    id INT PRIMARY KEY AUTO_INCREMENT,\n    NAME CHAR (32),\n    sub_time DATETIME\n);\n\nINSERT INTO blog (NAME, sub_time) VALUES\n    ('\u7b2c1\u7bc7','2015-03-01 11:31:21'),\n    ('\u7b2c2\u7bc7','2015-03-11 16:31:21'),\n    ('\u7b2c3\u7bc7','2016-07-01 10:21:31'),\n    ('\u7b2c4\u7bc7','2016-07-22 09:23:21'),\n    ('\u7b2c5\u7bc7','2016-07-23 10:11:11'),\n    ('\u7b2c6\u7bc7','2016-07-25 11:21:31'),\n    ('\u7b2c7\u7bc7','2017-03-01 15:33:21'),\n    ('\u7b2c8\u7bc7','2017-03-01 17:32:21'),\n    ('\u7b2c9\u7bc7','2017-03-01 18:31:21');<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>\u63d0\u53d6sub_time\u5b57\u6bb5\u7684\u503c\uff0c\u6309\u7167\u683c\u5f0f\u540e\u7684\u7ed3\u679c\u5373&#8221;\u5e74\u6708&#8221;\u6765\u5206\u7ec4<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"MHcB5\" class=\"wp-block-code\"><code>SELECT DATE_FORMAT(sub_time,'%Y-%m'), COUNT(1) #COUNT(col) \u8fd4\u56de\u6307\u5b9a\u5217\u4e2d\u975eNULL\u503c\u7684\u4e2a\u6570\nFROM blog \nGROUP BY DATE_FORMAT(sub_time, '%Y-%m');<\/code><\/pre>\n\n\n\n<p id=\"u04a4bc60\"><strong>\u7ed3\u679c<\/strong><\/p>\n\n\n\n<pre id=\"qGw2J\" class=\"wp-block-code\"><code>+-------------------------------+----------+\n| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |\n+-------------------------------+----------+\n| 2015-03                       |        2 |\n| 2016-07                       |        4 |\n| 2017-03                       |        3 |\n+-------------------------------+----------+\n3 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n<p id=\"uc2ed6bbf\">\u66f4\u591a\u5173\u4e8eMySQL\u51fd\u6570\u7684\u8be6\u7ec6\u4fe1\u606f\uff0c\u53ef\u4ee5\u53c2\u8003<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/date-and-time-functions.html\" target=\"_blank\" rel=\"noreferrer noopener\">MySQL\u5b98\u65b9\u6587\u6863<\/a>\u3002<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" id=\"gXbBN\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"OlN4A\">\u81ea\u5b9a\u4e49\u51fd\u6570<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"1bbbb204\">\u6ce8\u610f\u4e8b\u9879<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u51fd\u6570\u4e2d\u4e0d\u8981\u5199SQL\u8bed\u53e5\uff08\u5426\u5219\u4f1a\u62a5\u9519\uff09\uff0c\u51fd\u6570\u4ec5\u4ec5\u53ea\u662f\u4e00\u4e2a\u529f\u80fd\uff0c\u662f\u4e00\u4e2a\u5728SQL\u4e2d\u88ab\u5e94\u7528\u7684\u529f\u80fd\u3002<\/li>\n\n\n\n<li>\u82e5\u8981\u60f3\u5728<code>BEGIN...END...<\/code>\u4e2d\u5199SQL\uff0c\u8bf7\u7528\u5b58\u50a8\u8fc7\u7a0b\u3002<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"cef91f9b\">1. \u57fa\u672c\u81ea\u5b9a\u4e49\u51fd\u6570<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u521b\u5efa\u7b80\u5355\u7684\u52a0\u6cd5\u51fd\u6570<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"cfb502b8\" class=\"wp-block-code\"><code>DELIMITER \/\/\nCREATE FUNCTION f1(i1 INT, i2 INT) RETURNS INT\nBEGIN\n    DECLARE num INT;\n    SET num = i1 + i2;\n    RETURN num;\nEND \/\/\nDELIMITER ;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>\u8c03\u7528\u81ea\u5b9a\u4e49\u51fd\u6570<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"76fbadaa\" class=\"wp-block-code\"><code>SELECT f1(10, 20);  -- \u7ed3\u679c\uff1a30<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"07eda3d0\">2. \u6761\u4ef6\u5224\u65ad\u81ea\u5b9a\u4e49\u51fd\u6570<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u521b\u5efa\u5e26\u6709\u6761\u4ef6\u5224\u65ad\u7684\u51fd\u6570<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"f05012e9\" class=\"wp-block-code\"><code>DELIMITER \/\/\nCREATE FUNCTION f5(i INT) RETURNS INT\nBEGIN\n    DECLARE res INT DEFAULT 0;\n    IF i = 10 THEN\n        SET res = 100;\n    ELSEIF i = 20 THEN\n        SET res = 200;\n    ELSEIF i = 30 THEN\n        SET res = 300;\n    ELSE\n        SET res = 400;\n    END IF;\n    RETURN res;\nEND \/\/\nDELIMITER ;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>\u8c03\u7528\u5e26\u6709\u6761\u4ef6\u5224\u65ad\u7684\u51fd\u6570<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"2dac0361\" class=\"wp-block-code\"><code>SELECT f5(10);  -- \u7ed3\u679c\uff1a100\nSELECT f5(20);  -- \u7ed3\u679c\uff1a200\nSELECT f5(30);  -- \u7ed3\u679c\uff1a300\nSELECT f5(40);  -- \u7ed3\u679c\uff1a400<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"70a900b4\">3. \u5220\u9664\u51fd\u6570<\/h4>\n\n\n\n<pre id=\"153edba5\" class=\"wp-block-code\"><code>DROP FUNCTION IF EXISTS func_name;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"c2b32c38\">4. \u6267\u884c\u51fd\u6570<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u83b7\u53d6\u8fd4\u56de\u503c<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"2f68e76a\" class=\"wp-block-code\"><code>SELECT UPPER('egon') INTO @res;\nSELECT @res;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>\u5728\u67e5\u8be2\u4e2d\u4f7f\u7528<\/strong><\/li>\n<\/ol>\n\n\n\n<pre id=\"de519856\" class=\"wp-block-code\"><code>SELECT f1(11, nid), name FROM tb2;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>MySQL \u5185\u7f6e\u51fd\u6570 MySQL\u4e2d\u63d0\u4f9b\u4e86\u8bb8\u591a\u5185\u7f6e\u51fd\u6570\uff0c\u4ee5\u4e0b\u662f\u4e00\u4e9b\u5e38\u7528\u7684\u51fd\u6570\u53ca\u5176\u793a\u4f8b\uff1a \u4e00\u3001\u6570\u5b66\u51fd\u6570 \u4e8c\u3001\u805a\u5408\u51fd [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,12],"tags":[],"class_list":["post-227","post","type-post","status-publish","format-standard","hentry","category-mysql","category-sql"],"_links":{"self":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/227","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=227"}],"version-history":[{"count":1,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/227\/revisions"}],"predecessor-version":[{"id":228,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=\/wp\/v2\/posts\/227\/revisions\/228"}],"wp:attachment":[{"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=227"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhaoyanqi.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}