{"id":1487,"date":"2022-11-06T23:10:04","date_gmt":"2022-11-06T15:10:04","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1487"},"modified":"2022-11-27T12:40:59","modified_gmt":"2022-11-27T04:40:59","slug":"%e7%ae%97%e6%9c%af%e8%bf%90%e7%ae%97%e7%ac%a6%e7%9a%84%e4%bd%bf%e7%94%a8%e3%80%81%e6%af%94%e8%be%83%e8%bf%90%e7%ae%97%e7%ac%a6%e7%9a%84%e4%bd%bf%e7%94%a8%e3%80%81%e9%80%bb%e8%be%91%e8%bf%90%e7%ae%97","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1487","title":{"rendered":"\u7b97\u672f\u8fd0\u7b97\u7b26\u7684\u4f7f\u7528\u3001\u6bd4\u8f83\u8fd0\u7b97\u7b26\u7684\u4f7f\u7528\u3001\u903b\u8f91\u8fd0\u7b97\u7b26\u4e0e\u4f4d\u8fd0\u7b97\u7b26\u7684\u4f7f\u7528\u3001"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c04\u7ae0 \u8fd0\u7b97\u7b26\r\n# 1. \u7b97\u672f\u8fd0\u7b97\u7b26\uff1a+ - * \/ div % mod\r\n\r\nSELECT 100, 100 + 0, 100- 0,100 + 50,100 + 50 -30,100 + 35.5,100- 35.5\r\nFROM dual;\r\n\r\n# \u5728SQL\u4e2d\uff0c+\u6ca1\u6709\u8fde\u63a5\u7684\u4f5c\u7528\uff0c\u5c31\u8868\u793a\u52a0\u6cd5\u8fd0\u7b97\uff0c\u6b64\u65f6\uff0c\u4f1a\u5c06\u5b57\u7b26\u4e32\u8f6c\u6362\u4e3a\u6570\u503c\uff08\u9690\u5f0f\u8f6c\u6362\uff09\r\nSELECT 100 + '1' # \u5728Java\u8bed\u8a00\u4e2d\uff0c\u7ed3\u679c\u662f\uff1a1001\u3002\r\nFROM dual;\r\n\r\nSELECT 100 + 'a' #\u6b64\u65f6\u5c06'a'\u770b\u505a0\u5904\u7406\r\nFROM dual;\r\n\r\nSELECT 100 + NULL # null\u503c\u53c2\u4e0e\u8fd0\u7b97\uff0c\u7ed3\u679c\u4e3anull\r\nFROM dual;\r\n\r\nSELECT 100, 100 * 1, 100 * 1.0, 100 \/ 1.0, 100 \/ 2,\r\n       100 + 2 * 5 \/ 2,100 \/ 3, 100 DIV 0   #  \u5206\u6bcd\u5982\u679c\u4e3a0\uff0c\u5219\u7ed3\u679c\u4e3anull\r\nFROM dual;\r\n\r\n# \u53d6\u6a21\u8fd0\u7b97\uff1a% mod\r\nSELECT 12 % 3, 12 % 5, 12 MOD  -5, -12 % 5, -12 % -5\r\nFROM dual;\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u5458\u5de5id\u4e3a\u5076\u6570\u7684\u5458\u5de5\u4fe1\u606f\r\nSELECT employee_id,last_name,salary\r\nFROM employees\r\nWHERE employee_id % 2 = 0;\r\n\r\n# 2. \u6bd4\u8f83\u8fd0\u7b97\u7b26\r\n# 2.1 = &lt;=> &lt;> != &lt; &lt;= > >=\r\nSELECT 1 = 2, 1 != 2, 1 = '1', 1 = 'a', 0 = 'a' # \u5b57\u7b26\u4e32\u5b58\u5728\u9690\u5f0f\u8f6c\u6362\uff0c\u5982\u679c\u8f6c\u6362\u6570\u503c\u4e0d\u6210\u529f\uff0c\u5219\u770b\u505a0\r\nFROM dual;\r\n\r\nSELECT 'a' = 'a', 'ab' = 'ab', 'a' = 'b' #\u4e24\u8fb9\u90fd\u662f\u5b57\u7b26\u4e32\u7684\u8bdd\uff0c\u5219\u6309\u7167ANSI\u7684\u6bd4\u8f83\u89c4\u5219\u8fdb\u884c\u6bd4\u8f83\u3002\r\nFROM dual;\r\n\r\nSELECT 1 = NULL, NULL = NULL # \u53ea\u8981\u7531null\u53c2\u4e0e\u5224\u65ad\uff0c\u7ed3\u679c\u5c31\u4e3anull\r\nFROM dual;\r\n\r\nSELECT last_name,salary,commission_pct\r\nFROM employees\r\n# WHERE salary = 6000;\r\nWHERE commission_pct = NULL; #\u6b64\u65f6\u6267\u884c\uff0c\u4e0d\u4f1a\u6709\u4efb\u4f55\u7684\u7ed3\u679c\r\n\r\n#  &lt;=> : \u5b89\u5168\u7b49\u4e0e\u3002  \u8bb0\u5fc6\u6280\u5de7\uff1a\u4e3aNULL\u800c\u751f\u3002\r\n\r\nSELECT 1 &lt;=> 2, 1 &lt;=> '1', 1 &lt;=> 'a', 0 &lt;=> 'a'\r\nFROM dual;\r\n\r\nSELECT 1 &lt;=> NULL, NULL &lt;=> NULL\r\nFROM dual;\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u8868\u4e2dcommission_pct\u4e3anull\u7684\u6570\u636e\u6709\u54ea\u4e9b\r\nSELECT last_name,salary,commission_pct\r\nFROM employees\r\nWHERE commission_pct &lt;=> NULL; #\u6b64\u65f6\u6267\u884c\uff0c\u4e0d\u4f1a\u6709\u4efb\u4f55\u7684\u7ed3\u679c\r\n\r\nSELECT 3 &lt;> 2, '4' &lt;> NULL, '' != NULL, NULL != NULL\r\nFROM dual;\r\n\r\n# 2.2\r\n# \u2460 IS NULL \\ IS NOT NULL \\\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u8868\u4e2dcommission_pct\u4e3anull\u7684\u6570\u636e\u6709\u54ea\u4e9b\r\nSELECT last_name,salary,commission_pct\r\nFROM employees\r\nWHERE commission_pct IS NULL;\r\n#\u6216\r\nSELECT last_name,salary,commission_pct\r\nFROM employees\r\nWHERE ISNULL(commission_pct);\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u8868\u4e2dcommission_pct\u4e0d\u4e3anull\u7684\u6570\u636e\u6709\u54ea\u4e9b\r\nSELECT last_name,salary,commission_pct\r\nFROM employees\r\nWHERE commission_pct IS NOT NULL;\r\n#\u6216\r\nSELECT last_name,salary,commission_pct\r\nFROM employees\r\nWHERE NOT commission_pct &lt;=> NULL;\r\n\r\n# \u2461 LEAST() \\ GREATEST\r\n\r\nSELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')\r\nFROM dual;\r\n\r\nSELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))\r\nFROM employees;\r\n\r\n# \u2462 BETWEEN \u6761\u4ef61 AND \u6761\u4ef62 \uff08\u67e5\u8be2\u6761\u4ef61\u548c\u6761\u4ef62\u8303\u56f4\u5185\u7684\u6570\u636e\uff0c\u5305\u542b\u8fb9\u754c\uff09\r\n#\u67e5\u8be2\u5de5\u8d44\u57286000 \u5230 8000 \u7684\u5458\u5de5\u4fe1\u606f\r\nSELECT employee_id, last_name,salary\r\nFROM employees\r\n# WHERE salary BETWEEN 6000 AND 8000;\r\nWHERE salary >= 6000 &amp;&amp; salary &lt;= 8000;\r\n\r\n#\u4ea4\u63626000 \u548c 8000 \u4e4b\u540e\uff0c\u67e5\u8be2\u4e0d\u5230\u6570\u636e\r\nSELECT employee_id, last_name,salary\r\nFROM employees\r\nWHERE salary BETWEEN 8000 AND 6000;\r\n\r\n#\u4ea4\u6362\u5de5\u8d44\u4e0d\u57286000 \u5230 8000\u7684\u5458\u5de5\u4fe1\u606f\r\nSELECT employee_id, last_name,salary\r\nFROM employees\r\nWHERE salary NOT BETWEEN 6000 AND 8000;\r\n# WHERE salary &lt; 6000 OR salary > 8000;\r\n\r\n# \u2463 IN (set) \\ NOT IN (set)\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u90e8\u95e8\u4e3a10,20,30\u90e8\u95e8\u7684\u5458\u5de5\u4fe1\u606f\r\nSELECT last_name, salary,department_id\r\nFROM employees\r\n# WHERE department_id = 10 OR department_id = 20 OR department_id = 30;\r\nWHERE department_id IN (10,20,30);\r\n\r\n# \u7ec3\u4e60\uff1a\u67e5\u8be2\u5de5\u8d44\u4e0d\u662f6000\uff0c7000\uff0c8000\u7684\u5458\u5de5\u4fe1\u606f\r\nSELECT last_name,salary,department_id\r\nFROM employees\r\nWHERE salary NOT IN (6000,7000,8000);\r\n\r\n# \u2464 LIKE : \u6a21\u7cca\u67e5\u8be2\r\n# % : \u4ee3\u8868\u4e0d\u786e\u5b9a\u4e2a\u6570\u7684\u5b57\u7b26 (0\u4e2a\uff0c1\u4e2a\uff0c\u6216\u591a\u4e2a)\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2last_name\u4e2d\u5305\u542b\u5b57\u7b26'a'\u7684\u5458\u5de5\u4fe1\u606f\r\nSELECT last_name\r\nFROM employees\r\nWHERE last_name LIKE '%a%';\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2last_name\u4e2d\u4ee5\u5b57\u7b26'a'\u5f00\u5934\u7684\u5458\u5de5\u4fe1\u606f\r\nSELECT last_name\r\nFROM employees\r\nWHERE last_name LIKE 'a%';\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2last_name\u4e2d\u5305\u542b\u5b57\u7b26'a'\u4e14\u5305\u542b\u5b57\u7b26'e'\u7684\u5458\u5de5\u4fe1\u606f\r\n#\u5199\u6cd51\uff1a\r\nSELECT last_name\r\nFROM employees\r\nWHERE last_name LIKE '%a%' AND last_name LIKE '%e%';\r\n#\u5199\u6cd52\uff1a\r\nSELECT last_name\r\nFROM employees\r\nWHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';\r\n\r\n# _ : \u4ee3\u8868\u4e00\u4e2a\u4e0d\u786e\u5b9a\u7684\u5b57\u7b26\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u7b2c3\u4e2a\u5b57\u7b26\u662f'a'\u7684\u5458\u5de5\u4fe1\u606f\r\nSELECT last_name\r\nFROM employees\r\nWHERE last_name LIKE '__a%';\r\n\r\n#\u7ec3\u4e60\uff1a\u67e5\u8be2\u7b2c2\u4e2a\u5b57\u7b26\u662f_\u4e14\u7b2c3\u4e2a\u5b57\u7b26\u662f'a'\u7684\u5458\u5de5\u4fe1\u606f\r\n#\u9700\u8981\u4f7f\u7528\u8f6c\u4e49\u5b57\u7b26\uff1a\\\r\nSELECT last_name\r\nFROM employees\r\nWHERE last_name LIKE '_\\_a%';\r\n\r\n#\u6216\u8005\r\nSELECT last_name\r\nFROM employees\r\nWHERE last_name LIKE '_$_a%' ESCAPE '$';\r\n\r\n# \u2465 REGEXP \\ RLIKE : \u6b63\u5219\u8868\u8fbe\u5f0f\r\n\r\nSELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'\r\nFROM dual;\r\n\r\nSELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '&#91;ab]]'\r\nFROM dual;\r\n\r\n# 3. \u903b\u8f91\u8fd0\u7b97\u7b26\uff1a OR ||    AND &amp;&amp;    NOT !    XOR\r\n\r\n#OR AND\r\nSELECT last_name,salary,department_id\r\nFROM employees\r\n# WHERE department_id = 10 OR department_id = 20;\r\n# WHERE department_id = 10 AND department_id = 20;\r\nWHERE department_id = 50 AND salary > 6000;\r\n\r\n#NOT\r\nSELECT last_name,salary,department_id\r\nFROM employees\r\n# WHERE salary NOT BETWEEN 6000 AND 8000;\r\n# WHERE commission_pct IS NOT NULL;\r\nWHERE NOT commission_pct &lt;=> NULL;\r\n\r\n#XOR : \u8ffd\u6c42\u7684\"\u5f02\"\r\nSELECT last_name,salary,department_id\r\nFROM employees\r\nWHERE department_id = 50 XOR salary > 6000;\r\n\r\n# \u6ce8\u610f\uff1aAND\u7684\u4f18\u5148\u7ea7\u9ad8\u4e8eOR\r\n\r\n# 4. \u4f4d\u8fd0\u7b97\u7b26\uff1a&amp;    |    ^    ~    >>    &lt;&lt;\r\n\r\nSELECT 12 &amp; 5, 12 | 5,12 ^ 5\r\nFROM DUAL;\r\n\r\nSELECT 10 &amp; ~1\r\nFROM dual;\r\n\r\n#\u5728\u4e00\u5b9a\u8303\u56f4\u5185\u6ee1\u8db3\uff1a\u6bcf\u5411\u5de6\u79fb\u52a81\u4f4d\uff0c\u76f8\u5f53\u4e8e\u4e58\u4ee52\uff1b\u6bcf\u5411\u53f3\u79fb\u52a8\u4e00\u4f4d\uff0c\u76f8\u5f53\u4e8e\u9664\u4ee52\u3002\r\nSELECT 4 &lt;&lt; 1, 8 >> 1\r\nFROM dual;<\/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-1487","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\/1487"}],"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=1487"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1487\/revisions"}],"predecessor-version":[{"id":1488,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1487\/revisions\/1488"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}