{"id":1520,"date":"2022-11-07T22:46:57","date_gmt":"2022-11-07T14:46:57","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1520"},"modified":"2022-11-27T12:40:59","modified_gmt":"2022-11-27T04:40:59","slug":"%e7%ac%ac7%e7%ab%a0%e5%8d%95%e8%a1%8c%e5%87%bd%e6%95%b0%e8%af%be%e5%90%8e%e7%bb%83%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1520","title":{"rendered":"\u7b2c7\u7ae0\u5355\u884c\u51fd\u6570\u8bfe\u540e\u7ec3\u4e60"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>#\u7b2c07\u7ae0_\u5355\u884c\u51fd\u6570\u8bfe\u540e\u7ec3\u4e60\r\n\r\n# 1.\u663e\u793a\u7cfb\u7edf\u65f6\u95f4(\u6ce8\uff1a\u65e5\u671f+\u65f6\u95f4)\r\nSELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() # \u5927\u5bb6\u53ea\u9700\u8981\u638c\u63e1\u4e00\u4e2a\u51fd\u6570\u5c31\u53ef\u4ee5\u4e86\r\nFROM dual;\r\n\r\n# 2.\u67e5\u8be2\u5458\u5de5\u53f7\uff0c\u59d3\u540d\uff0c\u5de5\u8d44\uff0c\u4ee5\u53ca\u5de5\u8d44\u63d0\u9ad8\u767e\u5206\u4e4b20%\u540e\u7684\u7ed3\u679c\uff08new salary\uff09\r\nSELECT employee_id,last_name,salary,salary * 1.2 AS 'new salary'\r\nFROM employees;\r\n\r\n# 3.\u5c06\u5458\u5de5\u7684\u59d3\u540d\u6309\u9996\u5b57\u6bcd\u6392\u5e8f\uff0c\u5e76\u5199\u51fa\u59d3\u540d\u7684\u957f\u5ea6\uff08length\uff09\r\nSELECT last_name,LENGTH(last_name) AS \"name_length\"\r\nFROM employees\r\n# ORDER BY last_name ASC;\r\nORDER BY name_length ASC;\r\n\r\n# 4.\u67e5\u8be2\u5458\u5de5id,last_name,salary\uff0c\u5e76\u4f5c\u4e3a\u4e00\u4e2a\u5217\u8f93\u51fa\uff0c\u522b\u540d\u4e3aOUT_PUT\r\nSELECT CONCAT(employee_id,',',last_name,',',salary) AS \"OUT_PUT\"\r\nFROM employees;\r\n\r\n# 5.\u67e5\u8be2\u516c\u53f8\u5404\u5458\u5de5\u5de5\u4f5c\u7684\u5e74\u6570\u3001\u5de5\u4f5c\u7684\u5929\u6570\uff0c\u5e76\u6309\u5de5\u4f5c\u5e74\u6570\u7684\u964d\u5e8f\u6392\u5e8f\r\nSELECT employee_id,DATEDIFF(CURDATE(),hire_date) \/ 365 AS \"worked_years\",DATEDIFF(CURDATE(),hire_date) AS \"worked_days\",\r\nTO_DAYS(CURDATE()) - TO_DAYS(hire_date) AS \"worked_days1\"\r\nFROM employees\r\nORDER BY worked_years DESC;\r\n\r\n# 6.\u67e5\u8be2\u5458\u5de5\u59d3\u540d\uff0chire_date , department_id\uff0c\u6ee1\u8db3\u4ee5\u4e0b\u6761\u4ef6\uff1a\u96c7\u7528\u65f6\u95f4\u57281997\u5e74\u4e4b\u540e\uff0cdepartment_id\u4e3a80 \u6216 90 \u6216110, commission_pct\u4e0d\u4e3a\u7a7a\r\nSELECT last_name,hire_date,department_id\r\nFROM employees\r\nWHERE department_id IN (80,90,110)\r\nAND commission_pct IS NOT NULL\r\n# AND hire_date >= '1997-01-01';  #\u5b58\u5728\u7740\u9690\u5f0f\u8f6c\u6362\r\n# AND DATE_FORMAT(hire_date,'%Y-%m-%d') >= '1997-01-01' # \u663e\u793a\u8f6c\u6362\u64cd\u4f5c\uff0c\u683c\u5f0f\u5316\uff1a\u65e5\u671f ---> \u5b57\u7b26\u4e32\r\n# AND DATE_FORMAT(hire_date,'%Y') >= '1997';  # \u663e\u793a\u8f6c\u6362\u64cd\u4f5c\uff0c\u683c\u5f0f\u5316\r\nAND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d'); # \u663e\u5f0f\u8f6c\u6362\u64cd\u4f5c\uff0c\u89e3\u6790\uff1a\u5b57\u7b26\u4e32 ---> \u65e5\u671f\r\n\r\n# 7.\u67e5\u8be2\u516c\u53f8\u4e2d\u5165\u804c\u8d85\u8fc710000\u5929\u7684\u5458\u5de5\u59d3\u540d\u3001\u5165\u804c\u65f6\u95f4\r\nSELECT last_name,hire_date\r\nFROM employees\r\nWHERE DATEDIFF(CURDATE(),hire_date) >= 10000;\r\n\r\n# 8.\u505a\u4e00\u4e2a\u67e5\u8be2\uff0c\u4ea7\u751f\u4e0b\u9762\u7684\u7ed3\u679c\r\n# &lt;lastname> earns &lt;salary> monthly but wants &lt;salary*3>\r\nSELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0),' monthly but wants ',TRUNCATE(salary * 3,0)) AS \"Dream Salary\"\r\nFROM employees;\r\n\r\n# 9.\u4f7f\u7528case-when\uff0c\u6309\u7167\u4e0b\u9762\u7684\u6761\u4ef6\uff1a\r\n\/*\r\n  job     grade\r\n  AD_PRES     A\r\n  ST_MAN      B\r\n  IT_PROG     C\r\n  SA_REP      D\r\n  ST_CLERK    E\r\n\r\n\u4ea7\u751f\u4e0b\u9762\u7684\u7ed3\u679c:\r\n *\/\r\n\r\nSELECT last_name AS \"Last_name\",job_id AS \"Job_id\",CASE job_id WHEN 'AD_PRES' THEN 'A'\r\n                                                               WHEN 'ST_MAN' THEN 'B'\r\n                                                               WHEN 'IT_PROG' THEN 'C'\r\n                                                               WHEN 'SA_REP' THEN 'D'\r\n                                                               WHEN 'ST_CLERK' THEN 'E'\r\n                                                               ELSE 'undefined' END AS \"Grade\"\r\nFROM employees;\r\n\r\n\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[47],"class_list":["post-1520","post","type-post","status-publish","format-standard","hentry","category-mysql-code","tag-mysql"],"_links":{"self":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1520"}],"collection":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1520"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1520\/revisions"}],"predecessor-version":[{"id":1521,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1520\/revisions\/1521"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1520"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}