{"id":1612,"date":"2021-10-25T10:10:39","date_gmt":"2021-10-25T02:10:39","guid":{"rendered":"https:\/\/www.xinot.icu\/?p=1612"},"modified":"2022-08-25T13:58:22","modified_gmt":"2022-08-25T05:58:22","slug":"%e6%97%a7%e6%97%b6%e6%96%87%e7%ab%a0mysql","status":"publish","type":"post","link":"https:\/\/www.xinot.icu\/?p=1612","title":{"rendered":"\u65e7\u65f6\u7b14\u8bb0-MySQl"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"1612\" class=\"elementor elementor-1612\" data-elementor-settings=\"[]\">\n\t\t\t\t\t\t\t<div class=\"elementor-section-wrap\">\n\t\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-3ed05e6 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"3ed05e6\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-68b4dfd\" data-id=\"68b4dfd\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t\t\t<div class=\"elementor-element elementor-element-920d723 elementor-widget elementor-widget-heading\" data-id=\"920d723\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">\u65e7\u65f6\u7b14\u8bb0-MySQL<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-ffaf2d5 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"ffaf2d5\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-ba43c8f\" data-id=\"ba43c8f\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t\t\t<div class=\"elementor-element elementor-element-ab138c2 elementor-widget elementor-widget-heading\" data-id=\"ab138c2\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">\u524d\u8a00<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-fd908ea elementor-widget-divider--view-line elementor-widget elementor-widget-global elementor-global-1396 elementor-widget-divider\" data-id=\"fd908ea\" data-element_type=\"widget\" data-settings=\"{&quot;_animation&quot;:&quot;none&quot;,&quot;_animation_delay&quot;:1000}\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-b7087d3 elementor-widget elementor-widget-text-editor\" data-id=\"b7087d3\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>\u8be5\u6587\u7ae0\u662f\u7ad9\u4e3b\u524d\u671f\u5b66\u4e60\u7b14\u8bb0\uff0c\u642c\u8fd0\u4e8e\u6b64\u65b9\u4fbf\u67e5\u8be2\u3002<\/p><p>\u6682\u6ca1\u6709\u4f5c\u683c\u5f0f\u5904\u7406\u3002<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-8f5efc8 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"8f5efc8\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-a26f3da\" data-id=\"a26f3da\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t\t\t<div class=\"elementor-element elementor-element-e46e620 elementor-widget elementor-widget-heading\" data-id=\"e46e620\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">\u6b63\u6587<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-1a1efeb elementor-widget-divider--view-line elementor-widget elementor-widget-global elementor-global-1396 elementor-widget-divider\" data-id=\"1a1efeb\" data-element_type=\"widget\" data-settings=\"{&quot;_animation&quot;:&quot;none&quot;,&quot;_animation_delay&quot;:1000}\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-55ccc3e elementor-widget elementor-widget-text-editor\" data-id=\"55ccc3e\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p><strong>\u6570\u636e\u5e93\u6982\u8ff0<\/strong><\/p><ul><li>MySQL\u5c31\u662f\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\uff0c\u4e13\u95e8\u7528\u4e8e\u7ba1\u7406\u6570\u636e\u7684\u3002<\/li><li>\u76ee\u7684\u662f\u7b80\u5316\u5bf9\u6587\u4ef6\u7684\u64cd\u4f5c\uff0c\u901a\u8fc7\u7b80\u5355\u7684SQL\u8bed\u53e5\u5373\u53ef\u505a\u5230\u5bf9\u6587\u4ef6\u7684\u589e\u5220\u6539\u67e5\u3002<\/li><li>\u5e38\u89c1\u7684\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\uff1a<\/li><\/ul><p>Oracle\u3001MySQl\u3001DB2\u3001Sybase\u3001MS SqlServer\uff08\u652f\u6301\u6807\u51c6SQL\u7684\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\uff09<\/p><ul><li>\u5176\u4e2d\uff0cOracle\u662f\u6602\u8d35\u7684\uff0c\u6570\u636e\u5b89\u5168\u7ea7\u522b\u8f83\u9ad8\u7684\uff0c\u4f20\u7edf\u4f01\u4e1a\u5e38\u7528\u7684<\/li><li>MySQL\u7684\u4fbf\u6377\u514d\u8d39\u5f00\u6e90\uff0c\u8ba9\u5176\u6210\u4e3a\u4e92\u8054\u7f51\u4ea7\u4e1a\u6700\u5e38\u7528\u7684\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\u3002<\/li><li>MySQL\u6570\u636e\u5e93\u7684\u7aef\u53e3\uff1a3306<\/li><li>\u8868\uff1a\u53c2\u8003Excel<\/li><\/ul><p>\u00a0<\/p><p>SQL\uff1a<\/p><p>\u7ed3\u6784\u5316\u67e5\u8be2\u8bed\u8a00\uff0c\u662f\u4e00\u95e8\u6807\u51c6\u901a\u7528\u7684\u9ad8\u7ea7\u8bed\u8a00\uff0c\u9002\u7528\u4e8e\u6240\u6709\u6570\u636e\u5e93\u4ea7\u54c1\u3002<\/p><p>DBMS\uff1a<\/p><p>Database Management System\uff0c\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf<\/p><p>DB\uff1a<\/p><p>DataBase\uff0c\u6570\u636e\u5e93\uff0c\u5b9e\u9645\u5728\u786c\u76d8\u4e0a\u4ee5\u6587\u4ef6\u7684\u5f62\u5f0f\u5b58\u5728\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>MySQL\u90e8\u5206\u547d\u4ee4<\/p><ul><li>\u767b\u5f55<\/li><\/ul><p>mysql -u\u7528\u6237\u540d -p\u5bc6\u7801<\/p><ul><li>\u5217\u51fa\u6570\u636e\u5e93\uff1a<\/li><\/ul><p>show databases\uff1b<\/p><ul><li>\u521b\u5efa\u6570\u636e\u5e93\uff08\u4f20\u5165\u7684\u540d\u5b57\u90fd\u4f1a\u8f6c\u6362\u4e3a\u5c0f\u5199\uff09<\/li><\/ul><p>create database \u540d\u5b57\uff1b<\/p><ul><li>\u4f7f\u7528\u6570\u636e\u5e93<\/li><\/ul><p>ues \u6570\u636e\u5e93\u540d\u5b57\uff1b<\/p><ul><li>\u5217\u51fa\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u7684\u8868<\/li><\/ul><p>show tables\uff1b<\/p><ul><li>\u521d\u59cb\u5316\u6570\u636e\uff1a\uff08\u521d\u59cb\u5316\u6570\u636e\u524d\u8981\u5148\u9009\u62e9\u4f7f\u7528\u6570\u636e\u5e93\uff09<\/li><\/ul><p>source sql\uff1b<\/p><ul><li>\u5220\u9664\u6570\u636e\u5e93<\/li><\/ul><p>drop database \u6570\u636e\u5e93\u540d\u5b57\uff1b<\/p><ul><li>\u67e5\u770b\u8868\u7ed3\u6784<\/li><\/ul><p>desc \u8868\u540d\u5b57\uff1b<\/p><p>9\u3001\\c \u7ec8\u6b62\u8bed\u53e5<\/p><p>10\u3001show creat table emp\uff1b<\/p><p>\u67e5\u770b\u5efa\u8868\u8bed\u53e5<\/p><p>11\u3001distinct\uff0c\u53bb\u91cd<\/p><p>12\u3001union\uff0c\u94fe\u63a5<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u8fd9\u4e2a\u5173\u952e\u5b57\u5e38\u7528\u4e8e\u62fc\u63a5\u4e24\u5f20\u4e0d\u5173\u8054\u7684\u8868\u3002\u6709\u5173\u8054\u6570\u636e\u7684\u62fc\u63a5\u53ef\u4ee5\u4f7f\u7528\u5176\u4ed6\u5173\u952e\u5b57\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>SQL\u8bed\u53e5\u5206\u7c7b<\/p><p>DQL \uff1a<\/p><p>Data Query Language \u6570\u636e\u67e5\u8be2\u8bed\u53e5\uff0c\u7528\u6765\u5728\u6570\u636e\u5e93\u4e2d\u67e5\u8be2\u7684\u8bed\u8a00\uff0cselect\u5c31\u5728\u8fd9\u91cc\uff1b<\/p><p>DML\uff1a<\/p><p>Data Manipulation Language \u6570\u636e\u64cd\u4f5c\u8bed\u8a00\uff0c\u4e00\u822c\u7528\u6765\u505a\u8868\u4e2d\u6570\u636e\u7684\u589e\u52a0\u548c\u5220\u9664\uff1b<\/p><p>insert\u3001delete\u3001updata<\/p><p>DDL\uff1a<\/p><p>Data Difinition Language \u6570\u636e\u5b9a\u4e49\u8bed\u8a00\uff0c\u7528\u4e8e\u5bf9\u8868\u7684\u7ed3\u6784\u8fdb\u884c\u589e\u5220\u6539\uff1b<\/p><p>create\u3001drop\u3001alter<\/p><p>TCL\uff1a<\/p><p>\u4e8b\u52a1\u63a7\u5236\u8bed\u8a00<\/p><p>DCL\uff1a<\/p><p>Data Control Language \u6570\u636e\u63a7\u5236\u8bed\u8a00\uff0c\u4e00\u822c\u662fDBA\u624d\u4f1a\u7528zd\u5230\u7684\u6570\u636e\u5e93\u7528\u6237\u7ba1\u7406\u53ca\u65e5\u5e38\u7ef4\u62a4\u8bed\u8a00\u3002grant\u6388\u6743\u3001revoke\u64a4\u9500\u6743\u9650<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>SQL\u8bed\u53e5\u4e2d\u7684\u6267\u884c\u987a\u5e8f<\/p><p>select\u00a0\u00a0\u00a0 5\u3001\u5217\u51fa\/\u627e\u51fa<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026.<\/p><p>from\u00a0\u00a0\u00a0\u00a0 1\u3001\u5728\u2026\u8868\u4e2d<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026<\/p><p>where\u00a0\u00a0\u00a0 2\u3001\u6761\u4ef6<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026<\/p><p>group by\u00a0 3\u3001\u5206\u7ec4<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026<\/p><p>having\u00a0\u00a0\u00a0 4\u3001\u8fc7\u6ee4<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026<\/p><p>order by\u00a0 6\u3001\u6392\u5e8f<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026<\/p><p>limit\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7\u3001\u53d6\u51e0\u4e2a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>\u8868\u67e5\u8be2<\/strong><\/p><p>\u7b80\u5355\u7684DQL<\/p><ul><li>\u67e5\u8be2\uff1a<\/li><\/ul><p>select \u5b57\u6bb5\u540d1,\u5b57\u6bb5\u540d2\u2026\u2026\u2026\u2026.from \u8868\u540d\uff1b<\/p><ul><li>\u67e5\u51fa\u7684\u6708\u85aa\u4e5812<\/li><\/ul><p>select empno, ename, sal*12 from emp;<\/p><ul><li>\u67e5\u8be2\u51fa\u6765\u7684\u5b57\u6bb5\u663e\u793a\u4e3a\u4e2d\u6587\u6216\u5176\u4ed6\u5b57\u7b26<\/li><\/ul><p>select empno as \u2018\u5458\u5de5\u7f16\u53f7\u2019, ename as \u2018\u5458\u5de5\u59d3\u540d\u2019, sal*12 as \u2018\u5e74\u85aa\u2019 from emp;<\/p><ul><li>\u6761\u4ef6\u67e5\u8be2\uff1a<\/li><\/ul><p>select \u8981\u68c0\u7d22\u7684\u5b57\u6bb5\u540d\u2026\u2026\u2026..from \u8868\u660e where \u6761\u4ef6\uff1b<\/p><ul><li>\u6a21\u7cca\u67e5\u8be2\uff1a<\/li><\/ul><p>ename like &#8216;A%&#8217;<\/p><p>\u6240\u6709\u540d\u5b57\u5f00\u5934\u662fA\u7684\u5458\u5de5<\/p><p>ename like &#8216;_A%&#8217;<\/p><p>\u6240\u6709\u540d\u5b57\u7b2c\u4e8c\u4e2a\u5b57\u6bcd\u662fA\u7684\u5458\u5de5<\/p><p>ename like \u2018%\\_%\u2019<\/p><p>\u6240\u6709\u540d\u5b57\u5305\u542b\u4e0b\u5212\u7ebf\u7684\u5458\u5de5<\/p><ul><li>\u5305\u542bin<\/li><\/ul><p>select * from emp where job in (&#8216;manager&#8217;,&#8217;salesman&#8217;);<\/p><p>\u804c\u4e1a\u4e3a&#8217;manager&#8217;,&#8217;salesman&#8217;\u7e41\u8363\u5458\u5de5\uff1b<\/p><ul><li>\u6392\u5e8f\uff1a<\/li><\/ul><p>\u6392\u5e8f\u4f7f\u7528order by\u8bed\u53e5\uff0c\u540e\u9762\u8ddf\u4e0a\u6392\u5e8f\u5b57\u6bb5<\/p><p>select * from emp order by sal;<\/p><p>\u6309\u7167\u85aa\u6c34\u5347\u5e8f\u6392\u5e8f<\/p><p>select * from emp order by sal,job\uff1b<\/p><p>\u6309\u7167\u85aa\u6c34\u5347\u5e8f\u6392\u5e8f\uff0c\u85aa\u6c34\u76f8\u540c\u65f6\u518d\u6309\u7167\u5de5\u4f5c\u7c7b\u578b\u6392\u5e8f<\/p><p>\u82e5\u8981\u624b\u52a8\u6307\u5b9a\u6392\u5e8f\u987a\u5e8f\uff0c\u5728\u5b57\u6bb5\u540d\u540e\u52a0asc\u4ee3\u8868\u5347\u5e8f\uff0c\u52a0desc\u4ee3\u8868\u964d\u5e8f\u3002<\/p><ul><li>\u8f6c\u6362\u4e3a\u5176\u4ed6\u5b57\u6bb5\u540d\uff1a<\/li><\/ul><p>as \u5176\u4ed6\u60f3\u8981\u7f16\u7a0b\u7684\u540d\u5b57\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>SQL\u4e2d\u7684\u8fd0\u7b97\u7b26<\/p><table><tbody><tr><td width=\"284\"><p>\u8fd0\u7b97\u7b26<\/p><\/td><td width=\"284\"><p>\u8bf4\u660e<\/p><\/td><\/tr><tr><td width=\"284\"><p>=<\/p><\/td><td width=\"284\"><p>\u7b49\u4e8e<\/p><\/td><\/tr><tr><td width=\"284\"><p>&lt;&gt;\u6216!=<\/p><\/td><td width=\"284\"><p>\u4e0d\u7b49\u4e8e<\/p><\/td><\/tr><tr><td width=\"284\"><p>&lt;\u00a0<\/p><\/td><td width=\"284\"><p>\u5c0f\u4e8e<\/p><\/td><\/tr><tr><td width=\"284\"><p>&lt;=<\/p><\/td><td width=\"284\"><p>\u5c0f\u4e8e\u7b49\u4e8e<\/p><\/td><\/tr><tr><td width=\"284\"><p>&gt;\u00a0<\/p><\/td><td width=\"284\"><p>\u5927\u4e8e<\/p><\/td><\/tr><tr><td width=\"284\"><p>&gt;=<\/p><\/td><td width=\"284\"><p>\u5927\u4e8e\u7b49\u4e8e<\/p><\/td><\/tr><tr><td width=\"284\"><p>between \u2026 and \u2026.<\/p><\/td><td width=\"284\"><p>\u4e24\u4e2a\u503c\u4e4b\u95f4,<strong>\u7b49\u540c\u4e8e &gt;= and &lt;=<\/strong><\/p><\/td><\/tr><tr><td width=\"284\"><p>is null<\/p><\/td><td width=\"284\"><p>\u4e3anull\uff08is not null \u4e0d\u4e3a\u7a7a\uff09<\/p><\/td><\/tr><tr><td width=\"284\"><p><strong>and<\/strong><\/p><\/td><td width=\"284\"><p>\u5e76\u4e14<\/p><\/td><\/tr><tr><td width=\"284\"><p><strong>or<\/strong><\/p><\/td><td width=\"284\"><p>\u6216\u8005<\/p><\/td><\/tr><tr><td width=\"284\"><p>in<\/p><\/td><td width=\"284\"><p>\u5305\u542b\uff0c\u76f8\u5f53\u4e8e\u591a\u4e2aor\uff08not in\u4e0d\u5728\u8fd9\u4e2a\u8303\u56f4\u4e2d\uff09<\/p><\/td><\/tr><tr><td width=\"284\"><p>not<\/p><\/td><td width=\"284\"><p>not\u53ef\u4ee5\u53d6\u975e\uff0c\u4e3b\u8981\u7528\u5728is \u6216in\u4e2d<\/p><\/td><\/tr><tr><td width=\"284\"><p>like<\/p><\/td><td width=\"284\"><p>like\u79f0\u4e3a\u6a21\u7cca\u67e5\u8be2\uff0c\u652f\u6301%\u6216\u4e0b\u5212\u7ebf\u5339\u914d<\/p><p>%\u5339\u914d\u4efb\u610f\u4e2a\u5b57\u7b26<\/p><p>\u4e0b\u5212\u7ebf\uff0c\u4e00\u4e2a\u4e0b\u5212\u7ebf\u53ea\u5339\u914d\u4e00\u4e2a\u5b57\u7b26<\/p><p>\u82e5\u9700\u8981\u8f6c\u610f\uff0c\u5219\u5728\u7b26\u53f7\u524d\u52a0\u659c\u6760<\/p><\/td><\/tr><\/tbody><\/table><p>\u00a0<\/p><p>\u00a0<\/p><p>SQL\u4e2d\u7684\u5355\u884c\u51fd\u6570<\/p><table><tbody><tr><td width=\"284\"><p>Lower<\/p><\/td><td width=\"284\"><p>\u8f6c\u6362\u5c0f\u5199<\/p><\/td><\/tr><tr><td width=\"284\"><p>upper<\/p><\/td><td width=\"284\"><p>\u8f6c\u6362\u5927\u5199<\/p><\/td><\/tr><tr><td width=\"284\"><p>substr<\/p><\/td><td width=\"284\"><p>\u53d6\u5b50\u4e32\uff08<strong>substr(\u88ab\u622a\u53d6\u7684\u5b57\u7b26\u4e32,\u8d77\u59cb\u4e0b\u6807,\u622a\u53d6\u7684\u957f\u5ea6)<\/strong>\uff09<\/p><\/td><\/tr><tr><td width=\"284\"><p>length<\/p><\/td><td width=\"284\"><p>\u53d6\u957f\u5ea6<\/p><\/td><\/tr><tr><td width=\"284\"><p>trim<\/p><\/td><td width=\"284\"><p>\u53bb\u7a7a\u683c<\/p><\/td><\/tr><tr><td width=\"284\"><p>str_to_date<\/p><\/td><td width=\"284\"><p>\u5c06\u5b57\u7b26\u4e32\u8f6c\u6362\u6210\u65e5\u671f<\/p><\/td><\/tr><tr><td width=\"284\"><p>date_format<\/p><\/td><td width=\"284\"><p>\u683c\u5f0f\u5316\u65e5\u671f<\/p><\/td><\/tr><tr><td width=\"284\"><p>format<\/p><\/td><td width=\"284\"><p>\u8bbe\u7f6e\u5343\u5206\u4f4d<\/p><\/td><\/tr><tr><td width=\"284\"><p>round<\/p><\/td><td width=\"284\"><p>\u56db\u820d\u4e94\u5165<\/p><\/td><\/tr><tr><td width=\"284\"><p>rand()<\/p><\/td><td width=\"284\"><p>\u751f\u6210\u968f\u673a\u6570<\/p><\/td><\/tr><tr><td width=\"284\"><p>Ifnull<\/p><\/td><td width=\"284\"><p>\u53ef\u4ee5\u5c06null\u8f6c\u6362\u6210\u4e00\u4e2a\u5177\u4f53\u503c<\/p><\/td><\/tr><\/tbody><\/table><ul><li>\u7528\u6cd5\uff1a\u5728\u5b57\u6bb5\u540d\u4e2d\u7528\u5c0f\u62ec\u53f7\u62ec\u8d77\u6765\uff0c\u586b\u5165\u51fd\u6570\u540d\u3002<\/li><li>\u591a\u884c\u5904\u7406\u51fd\u6570\uff1a<\/li><li>select ifnull(comm,0) from emp;<\/li><\/ul><p>\u5217\u51fa\u6d25\u8d34\uff0c\u82e5\u6d25\u8d34\u4e3anull\u5219\u8f6c\u6362\u4e3a0\u3002<\/p><p>select ename,(sal + ifnull(comm,0))*12 as yearsal from emp;<\/p><p>\u5217\u51fa\u6240\u6709\u4eba\u7684\u5e74\u85aa\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>SQL\u4e2d\u7684\u591a\u884c\u5904\u7406\u51fd\u6570<\/p><table width=\"568\"><tbody><tr><td width=\"284\"><p>count<\/p><\/td><td width=\"284\"><p>\u53d6\u5f97\u8bb0\u5f55\u6570<\/p><\/td><\/tr><tr><td width=\"284\"><p>sum<\/p><\/td><td width=\"284\"><p>\u6c42\u548c<\/p><\/td><\/tr><tr><td width=\"284\"><p>avg<\/p><\/td><td width=\"284\"><p>\u53d6\u5e73\u5747<\/p><\/td><\/tr><tr><td width=\"284\"><p>max<\/p><\/td><td width=\"284\"><p>\u53d6\u6700\u5927\u7684\u6570<\/p><\/td><\/tr><tr><td width=\"284\"><p>min<\/p><\/td><td width=\"284\"><p>\u53d6\u6700\u5c0f\u7684\u6570<\/p><\/td><\/tr><tr><td width=\"284\"><p>distinct<\/p><\/td><td width=\"284\"><p>\u53bb\u9664\u91cd\u590d<\/p><\/td><\/tr><\/tbody><\/table><p>\u6ce8\u610f\uff1a\u591a\u884c\u5904\u7406\u51fd\u6570\u81ea\u52a8\u5ffd\u7565null<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 where\u5173\u952e\u5b57\u540e\u9762\u4e0d\u80fd\u4f7f\u7528\u5206\u7ec4\u51fd\u6570<\/p><p>\u4f7f\u7528\u65b9\u6cd5\uff1a\u5199\u51fa\u5173\u952e\u5b57\u7136\u540e\u52a0\u5c0f\u62ec\u53f7\uff0c\u5c0f\u62ec\u53f7\u4e2d\u586b\u5165\u5b57\u7b26\u540d\u3002<\/p><p>select max(sal) from emp;<\/p><p>\u5728emp\u8868\u4e2d\u627e\u51fa\u85aa\u6c34\u6700\u9ad8\u7684\u4eba\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u5206\u7ec4\u67e5\u8be2<\/p><ul><li>\u4e3b\u8981\u6d89\u53ca\u5230\u7684\u4e24\u4e2a\u5173\u952e\u5b57\uff1agroup by\u548chaving<\/li><li>select sum(sal),job from emp group by job;<\/li><\/ul><p>\u627e\u51fa\u6bcf\u4e2a\u5de5\u4f5c\u5c97\u4f4d\u7684\u6708\u85aa\u603b\u8ba1<\/p><ul><li>\u5f53\u4e00\u6761SQl\u8bed\u53e5\u4e2d\u542b\u6709group by\u7684\u8bdd\uff0cselect\u540e\u9762\u53ea\u80fd\u8ddf\u6709\u5206\u7ec4\u51fd\u6570\u4ee5\u53ca\u53c2\u4e0e\u5206\u7ec4\u7684\u5b57\u6bb5\u30025\u4ee5\u540e\u7684MySQL\u4ee5\u53caOracle\u4f1a\u62a5\u9519\u3002<\/li><li>group by\u53ef\u4ee5\u8054\u5408\u5206\u7ec4\uff0c\u540e\u9762\u8ddf\u591a\u4e2a\u5b57\u6bb5\u540d\u3002<\/li><li>having\u53ef\u4ee5\u7406\u89e3\u4e3a\uff0c\u5206\u7ec4\u540e\u518d\u6267\u884c\u7684where\u3002<\/li><\/ul><p>\u00a0<\/p><p>\u5217\u51fa\u6240\u6709\u5de5\u4f5c\u5c97\u4f4d\u7684\u5e73\u5747\u5de5\u8d44\u5e76\u964d\u5e8f\u6392\u5217<\/p><p>\u5217\u51fa\u5de5\u8d44\u9ad8\u4e8e\u5e73\u5747\u5de5\u8d44\u7684\u5458\u5de5<\/p><p>\u5217\u51fa\u4e0d\u540c\u90e8\u95e8\u4e2d\uff0c\u4e0d\u540c\u804c\u4e1a\u7684\u6700\u9ad8\u6708\u85aa\uff0c\u5e76\u6309\u7167\u90e8\u95e8\u964d\u5e8f\u6392\u5217\u3002<\/p><p>select job,deptno,max(sal) from emp group by deptno,job order by deptno desc;<\/p><p>\u00a0<\/p><p>\u627e\u51fa\u6bcf\u4e2a\u90e8\u95e8\u7684\u5e73\u5747\u85aa\u8d44\uff0c\u5e76\u4e14\u53ea\u663e\u793a\u5e73\u5747\u85aa\u8d44\u5927\u4e8e2000\u7684\u6570\u636e\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>\u8fde\u63a5\u67e5\u8be2<\/strong><\/p><ul><li>\u8fde\u63a5\u67e5\u8be2\u5c31\u662f\u591a\u5f20\u8868\u8054\u5408\u53d6\u51fa\u6570\u636e\uff0c\u5b9e\u9645\u5f00\u53d1\u4e0d\u4f1a\u53ea\u6709\u4e00\u5f20\u8868\u3002<\/li><li>\u53ea\u6709\u4e00\u5f20\u8868\u7684\u8bdd\u4f1a\u9020\u6210\u6570\u636e\u5197\u4f59\u3002\u5c31\u50cf\u4e00\u76f4\u91cd\u590d\u4e00\u7fa4\u4eba\u90fd\u662f\u4e2d\u56fd\u4eba\u4e00\u6837\u3002<\/li><li>\u94fe\u63a5\u67e5\u8be2\u7684\u5206\u7c7b\uff1a<\/li><\/ul><p>\u5185\u8fde\u63a5\uff1a<\/p><p>\u7b49\u503c\u94fe\u63a5\u3001\u975e\u7b49\u503c\u94fe\u63a5\u3001\u81ea\u94fe\u63a5<\/p><p>\u5916\u8fde\u63a5\uff1a<\/p><p>\u00a0\u00a0\u00a0 \u5de6\u5916\u94fe\u63a5\u3001\u53f3\u5916\u8fde\u63a5<\/p><p>\u5168\u8fde\u63a5<\/p><p>\u6309\u7167\u5e74\u4ee3\u5206\u7c7b\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQL92\uff08\u57fa\u672c\u4e0d\u7528\uff0c\u8001\u65e7\u7684\u8bed\u6cd5\uff09<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQL99\uff08\u4e3b\u6d41\uff09<\/p><ul><li>\u8868\u7684\u522b\u540d\uff1b<\/li><\/ul><p>from \u540e\u9762\u8868\u7684\u540d\u5b57\u6dfb\u52a0\u5176\u4ed6\u7684\u5b57\u7b26\u4e32\uff1aelect e.ename,d,dname from emp e,dept d\uff1b<\/p><p>\u6dfb\u52a0\u522b\u540d\u53ef\u4ee5\u63d0\u9ad8\u6267\u884c\u6548\u7387\uff0c\u8ba9\u53ef\u8bfb\u6027\u66f4\u597d<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u5185\u8fde\u63a5<\/p><ul><li>\u591a\u8868\u94fe\u63a5\u67e5\u8be2\u4f1a\u51fa\u73b0\u7b1b\u5361\u5c14\u79ef\u73b0\u8c61\uff0c\u4e5f\u5c31\u662f\u67e5\u8be2\u51fa\u6765\u7684\u7ed3\u679c\u662f\u8fd9\u51e0\u5f20\u8868\u7684\u8bb0\u5f55\u6761\u6570\u4e4b\u79ef\u3002<\/li><li>\u907f\u514d\u7b1b\u5361\u5c14\u79ef\u73b0\u8c61\u7684\u65b9\u6cd5\uff1a\u6dfb\u52a0\u6761\u4ef6\u8fdb\u884c\u8fc7\u6ee4<\/li><\/ul><p>select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;\uff08\u8001\u8bed\u6cd5\uff09<\/p><p>\u7b49\u503c\u8fde\u63a5\uff1a\u6761\u4ef6\u662f\u7b49\u91cf\u5173\u7cfb\u7684\u94fe\u63a5\u3002\uff08join on\uff09<\/p><p>select<\/p><p>e.ename,d.dname<\/p><p>from<\/p><p>dept d<\/p><p>inner join\uff08inner\u53ef\u4ee5\u7701\u7565\uff0c\u4f46\u52a0\u4e0a\u53bb\u53ef\u8bfb\u6027\u66f4\u597d\uff09<\/p><p>emp e<\/p><p>on<\/p><p>e.deptno = d.deptno;<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u975e\u7b49\u503c\u94fe\u63a5\uff1a\u5c31\u662f\u8fde\u63a5\u6761\u4ef6\u4e0d\u662f\u6bd4\u8f83\u662f\u5426\u76f8\u7b49<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ename,s.grade<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp e<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 inner join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 salgrade s<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.sal between s.losal and s.hisal;<\/p><ul><li>\u5c06\u8868emp\u8fde\u63a5\u81f3dept\uff0con\u540e\u9762\u52a0\u8fde\u63a5\u6761\u4ef6\u3002<\/li><li>\u8fd9\u79cd\u8bed\u6cd5\u6bd492\u597d\u7684\u539f\u56e0\u662f\u7ed3\u6784\u66f4\u6e05\u6670\uff0c\u8fde\u63a5\u6761\u4ef6\u4e0e\u7b5b\u9009\u6761\u4ef6\u5206\u5f00\uff0c\u8981\u5bf9\u94fe\u63a5\u540e\u7684\u6570\u636e\u5728\u8fdb\u884c\u7b5b\u9009\uff0c\u540e\u9762\u53ef\u4ee5\u6dfb\u52a0\u989d\u5916\u7684where\u5173\u952e\u5b57\u3002<\/li><li>\u81ea\u94fe\u63a5\uff1a\u81ea\u5df1\u94fe\u63a5\u81ea\u5df1\uff0c\u4e00\u5f20\u8868\u6839\u636e\u4e0d\u540c\u7684\u6570\u636e\u770b\u6210\u4e24\u5f20\u8868<\/li><\/ul><p>\u9700\u6c42\uff1a\u5c06\u8868\u4e2d\u7684\u9886\u5bfc\u7f16\u53f7\u5168\u90e8\u6362\u4e3a\u9886\u5bfc\u540d\u5b57\u3002<\/p><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ename as \u2018\u5458\u5de5\u2019, m.ename as \u2018\u9886\u5bfc\u2019<\/p><p>from<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp m<\/p><p>inner join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp e<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m.empno = e.mgr;<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u5916\u8fde\u63a5<\/p><ul><li>\u5916\u94fe\u63a5\u4e0e\u5185\u8fde\u63a5\u7684\u533a\u522b\uff1a<\/li><\/ul><p>\u5185\u94fe\u63a5\u6ca1\u6709\u4e3b\u526f\u4e4b\u5206\uff0c\u4e24\u5f20\u8868\u4e2d\u7684\u6570\u636e\u4e0d\u5339\u914d\u5c31\u4e0d\u663e\u793a\u3002<\/p><p>\u5916\u8fde\u63a5\u6709\u4e3b\u526f\u4e4b\u5206\uff0c\u4e3b\u8981\u67e5\u4e3b\u8868\uff0c\u634e\u5e26\u67e5\u9644\u8868\uff0c\u6570\u636e\u5339\u914d\u5219\u4e00\u8d77\u663e\u793a\uff0c\u6570\u636e\u4e0d\u5339\u914d\uff0c\u5219\u9644\u8868\u4f1a\u6a21\u62df\u51fanull\u6765\u5339\u914d\u4e3b\u8868<\/p><ul><li>\u5916\u8fde\u63a5\u6709\u5de6\u53f3\u8fde\u63a5\u4e4b\u5206\uff0c\u5de6\u8fde\u63a5\u6709\u5bf9\u5e94\u7684\u53f3\u8fde\u63a5\u7684\u5199\u6cd5\uff0c\u53f3\u8fde\u63a5\u4e5f\u6709\u76f8\u5e94\u7684\u5de6\u8fde\u63a5\u7684\u5199\u6cd5\u3002<\/li><li>\u6848\u4f8b\uff1a<ol><li>\u627e\u51fa\u6ca1\u6709\u5458\u5de5\u7684\u90e8\u95e8\uff08\u4e3b\u8868\u90e8\u95e8\uff09<\/li><\/ol><\/li><\/ul><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 d.dname,e.deptno<\/p><p>from<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp e<\/p><p>right join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dept d<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.deptno = d.deptno<\/p><p>where<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.deptno is null;<\/p><ol><li>\u627e\u51fa\u6240\u6709\u5458\u5de5\uff0c\u9644\u5e26\u67e5\u8be2\u4ed6\u4eec\u7684\u9886\u5bfc\uff08\u4e3b\u8868\u5458\u5458\u5de5\uff0c\u9644\u8868\u9886\u5bfc\uff09<\/li><\/ol><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ename \u2018\u5458\u5de5\u2019,m.ename \u2018\u4e0a\u7ea7\u2019<\/p><p>from<\/p><p>emp e<\/p><p>left join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp m<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.mgr = m.empno;<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u591a\u8868\u67e5\u8be2<\/p><ul><li>\u8bed\u6cd5\uff1a\u5c31\u662f\u591a\u5199\u51e0\u4e2ajoin\u2026on\u7ed3\u6784\u3002<\/li><li>\u6848\u4f8b\uff1a<ol><li>\u5217\u51fa\u6bcf\u4e00\u4e2a\u5458\u5de5\u7684\u90e8\u95e8\u540d\u79f0\u4ee5\u53ca\u5de5\u8d44\u7b49\u7ea7<\/li><\/ol><\/li><\/ul><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ename,d.dname,s.grade<\/p><p>from<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp e<\/p><p>join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dept d<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.deptno = d.deptno<\/p><p>join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 salgrade s<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.sal between s.losal and s.hisal;<\/p><ol><li>\u5217\u51fa\u6bcf\u4e2a\u5458\u5de5\u7684\u90e8\u95e8\u540d\u79f0\u3001\u5de5\u8d44\u7b49\u7ea7\u4ee5\u53ca\u4e0a\u7ea7\u9886\u5bfc\u7684\u540d\u5b57<\/li><\/ol><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ename,d.dname,s.grade,e2.ename<\/p><p>from<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp e<\/p><p>join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dept d<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.deptno = d.deptno<\/p><p>join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 salgrade s<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.sal between s.losal and s.hisal<\/p><p>left join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp e2<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.mgr = e2.empno;<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u5b50\u67e5\u8be2<\/p><ul><li>\u5b50\u67e5\u8be2\u5c31\u662fselect\u8bed\u53e5\u5d4c\u5957select\u8bed\u53e5<\/li><li>select\u7684\u5d4c\u5957\u53ef\u4ee5\u7528\u4e8e\uff1a<\/li><\/ul><p>where\u3001 from\u3001 select\u5173\u952e\u5b57\u540e\u9762<\/p><ul><li>\u6848\u4f8b\uff1a<ol><li>\u5217\u51fa\u6bcf\u4e2a\u90e8\u95e8\u5e73\u5747\u85aa\u6c34\u7684\u85aa\u8d44\u7b49\u7ea7\uff08P38\uff09<\/li><\/ol><\/li><\/ul><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.dname,s.grade<\/p><p>from<\/p><p>(select avg(e.sal) avgsal,d.dname from emp e join dept d on e.deptno = d.deptno group by e.deptno) as a<\/p><p>join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 salgrade s<\/p><p>on<\/p><ol><li>avgsal between s.losal and s.hisal<\/li><\/ol><p>\u6bcf\u4e2a\u90e8\u95e8\u7684\u5e73\u5747\u85aa\u6c34\uff1a<\/p><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 avg(e.sal) avgsal,d.dname<\/p><p>from<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp e<\/p><p>join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dept d<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.deptno = d.deptno<\/p><p>group by<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.deptno;<\/p><ol><li>\u5217\u51fa\u6bcf\u4e2a\u90e8\u95e8\u5e73\u5747\u7684\u85aa\u6c34\u7b49\u7ea7<\/li><\/ol><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 avg(l.grade),d.dname<\/p><p>from<\/p><p>(select e.ename,s.grade,e.deptno from emp e join salgrade s on e.sal between s.losal and s.hisal) l<\/p><p>join<\/p><p>dept d<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 d.deptno = l.deptno<\/p><p>group by<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 l.deptno<\/p><p>;<\/p><p>\u6bcf\u4e2a\u4eba\u7684\u85aa\u6c34\u7b49\u7ea7\uff1a<\/p><p>select<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ename,s.grade,e.deptno<\/p><p>from<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp e<\/p><p>join<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 salgrade s<\/p><p>on<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.sal between s.losal and s.hisal<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>Limit<\/p><ul><li>limit\u662fMySQL\u7279\u6709\u7684\uff0c\u5176\u4ed6\u6570\u636e\u5e93\u4e0d\u901a\u7528<\/li><li>\u4f5c\u7528\u662f\u53d6\u51fa\u67e5\u8be2\u7ed3\u679c\u96c6\u7684\u90e8\u5206\u6570\u636e\u3002<\/li><li>\u8bed\u6cd5\uff1a<\/li><\/ul><p>limit startIndex\uff0clength<\/p><p>stratIndex\u8868\u793a\u8d77\u59cb\u4f4d\u7f6e\uff0c\u4ece0\u5f00\u59cb\u3002<\/p><p>length\u8868\u793a\u53bb\u591a\u5c11\u4e2a\u6570\u636e\u3002<\/p><ul><li>\u901a\u7528\u7684\u6807\u51c6\u5206\u9875SQL\u8bed\u53e5\uff1a<\/li><\/ul><p>\u6bcf\u9875\u663e\u793apageSize\u6761\u8bb0\u5f55<\/p><p>\u7b2cpageNo\u9875\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (pageNo \u2013 1)*pageSize,paageSize<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>\u8868\u64cd\u4f5c<\/strong><\/p><p>\u521b\u5efa\u8868<\/p><p>\u8bed\u6cd5\uff1a<\/p><p>creat table \u8868\u540d(<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d1 \u6570\u636e\u7c7b\u578b default \u9ed8\u8ba4\u503c,<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d2 \u6570\u636e\u7c7b\u578bdefault \u9ed8\u8ba4\u503c.<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d3 \u6570\u636e\u7c7b\u578bdefault \u9ed8\u8ba4\u503c,<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026\u2026<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ENGINE = \u5f15\u64ce\u540d DEFAULT CHARSET = \u5b57\u7b26\u96c6\u540d;<\/p><p>\u00a0<\/p><p>\u5176\u4e2d\uff0c\u5f15\u64ce\u540d\u9ed8\u8ba4\u662fInnoDB\uff0c\u5b57\u7b26\u96c6\u540d\u9ed8\u8ba4\u662fUTF8\u3002<\/p><p>\u82e5\u4e0d\u4f7f\u7528default\u5173\u952e\u5b57\uff0c\u5219\u9ed8\u8ba4\u503c\u4e00\u822c\u90fd\u662fnull<\/p><p>\u00a0<\/p><p>MySQL\u5e38\u7528\u6570\u636e\u7c7b\u578b<\/p><table><tbody><tr><td width=\"284\"><p>\u7c7b\u578b<\/p><\/td><td width=\"284\"><p>\u63cf\u8ff0<\/p><\/td><\/tr><tr><td width=\"284\"><p>Char(\u957f\u5ea6)<\/p><\/td><td width=\"284\"><p>\u5b9a\u957f\u5b57\u7b26\u4e32\uff0c\u5b58\u50a8\u7a7a\u95f4\u5927\u5c0f\u56fa\u5b9a\uff0c\u9002\u5408\u4f5c\u4e3a\u4e3b\u952e\u6216\u5916\u952e<\/p><\/td><\/tr><tr><td width=\"284\"><p>Varchar(\u957f\u5ea6)<\/p><\/td><td width=\"284\"><p>\u53d8\u957f\u5b57\u7b26\u4e32\uff0c\u5b58\u50a8\u7a7a\u95f4\u7b49\u4e8e\u5b9e\u9645\u6570\u636e\u7a7a\u95f4\uff0c\u6700\u591a\u53ef\u5b58\u50a8256\u4e2a\u5b57\u7b26\u4e32\u3002<\/p><\/td><\/tr><tr><td width=\"284\"><p>double(\u6709\u6548\u6570\u5b57\u4f4d\u6570\uff0c\u5c0f\u6570\u4f4d)<\/p><\/td><td width=\"284\"><p>\u6570\u503c\u578b<\/p><\/td><\/tr><tr><td width=\"284\"><p>Float(\u6709\u6548\u6570\u5b57\u4f4d\u6570\uff0c\u5c0f\u6570\u4f4d)<\/p><\/td><td width=\"284\"><p>\u6570\u503c\u578b<\/p><\/td><\/tr><tr><td width=\"284\"><p>Int(\u00a0\u957f\u5ea6)<\/p><\/td><td width=\"284\"><p>\u6574\u578b<\/p><\/td><\/tr><tr><td width=\"284\"><p>bigint(\u957f\u5ea6)<\/p><\/td><td width=\"284\"><p>\u957f\u6574\u578b<\/p><\/td><\/tr><tr><td width=\"284\"><p>Date<\/p><\/td><td width=\"284\"><p>\u65e5\u671f\u578b\uff0c\u5bf9\u5e94Java\u4e2djava.sql.Date\u7c7b\u578b<\/p><\/td><\/tr><tr><td width=\"284\"><p>BLOB<\/p><p>Binary Large Object<\/p><\/td><td width=\"284\"><p>Binary Large OBject\uff08\u4e8c\u8fdb\u5236\u5927\u5bf9\u8c61\uff09<\/p><\/td><\/tr><tr><td width=\"284\"><p>CLOB<\/p><p>Character Large Object<\/p><\/td><td width=\"284\"><p>Character Large OBject\uff08\u5b57\u7b26\u5927\u5bf9\u8c61\uff0c\u6700\u591a\u53ef\u5b58\u50a84G\u7684\u5b57\u7b26\u4e32\uff09<\/p><\/td><\/tr><tr><td width=\"284\"><p>\u5176\u5b83\u2026\u2026\u2026\u2026\u2026\u2026\u2026<\/p><\/td><td width=\"284\"><p>\u00a0<\/p><\/td><\/tr><\/tbody><\/table><p>\u00a0<\/p><p>char\u4e0evarhar\u7684\u9009\u62e9\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u82e5\u8981\u5b58\u5165\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u662f\u957f\u5ea6\u4e0d\u4f1a\u53d8\u7684\u6570\u636e\uff0c\u5982\u751f\u65e5\uff0c\u6027\u522b\u7b49\uff0c\u53ef\u4ee5\u4f7f\u7528char\u3002<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u82e5\u5b58\u5165\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u957f\u5ea6\u662f\u4e0d\u786e\u5b9a\u7684\uff0c\u5efa\u8bae\u4f7f\u7528varchar\uff0c\u5982\u59d3\u540d\u5730\u5740\u7b49\u3002<\/p><p>\u00a0<\/p><p>BLOB\u4ee5\u53caCOLB\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u4e0d\u80fd\u76f4\u63a5\u63d2\u5165\u6570\u636e\uff0c\u8981\u4ee5\u6d41\u7684\u65b9\u5f0f\u653e\u5165\u6570\u636e\u3002<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BLOB\u53ef\u4ee5\u7528\u4e8e\u5b58\u50a8\u56fe\u7247\uff0c\u89c6\u9891\u7b49\u3002<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CLOB\u53ef\u4ee5\u7528\u4e8e\u5b58\u50a8\u7b80\u4ecb\u7b49\u957f\u5ea6\u8f83\u957f\u7684\u5b57\u7b26\u4e32\u5bf9\u8c61\u3002<\/p><p>\u00a0<\/p><p>\u5efa\u8868\u65f6\u5019\u7684\u8868\u540d\uff1a<\/p><p>\u4e00\u822c\u4ee5t_\u6216\u8005tbl_\u4e3a\u5f00\u5934\uff0c\u53ef\u8bfb\u6027\u66f4\u9ad8\u3002\u56e0\u4e3a\u6570\u636e\u5e93\u4e2d\u8fd8\u6709\u8868\u4ee5\u5916\u7684\u6570\u636e\u5f62\u8bc6<\/p><p>\u00a0<\/p><p>\u5220\u9664\u8868\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 drop table if exists \u8868\u540d;<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5982\u679c\u8868\u5b58\u5728\u5219\u5220\u9664\u8be5\u8868\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u63d2\u5165\u3001\u4fee\u6539\u3001\u5220\u9664\u6570\u636e<\/p><p>\u63d2\u5165\u8bed\u6cd5\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insert into \u8868\u540d(\u5b57\u6bb5\u540d1,\u5b57\u6bb5\u540d2,\u5b57\u6bb5\u540d3\u2026\u2026) values(\u503c1,\u503c2,\u503c3\u2026\u2026);<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insert into \u8868\u540d\u5b57 values(\u503c1,\u503c2,\u503c3\u2026\u2026);<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insert into \u8868\u540d\u5b57 select\u8bed\u53e5;<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u7b2c\u4e8c\u4e2d\u5199\u6cd5\u4e2d\u8981\u6ce8\u610f\u503c\u7684\u987a\u5e8f\u9ed8\u8ba4\u5bf9\u5e94\u9ed8\u8ba4\u7684\u5b57\u6bb5\u540d\u3002<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u800c\u518d\u7b2c\u4e00\u79cd\u5199\u6cd5\u4e2d\u4f20\u5165\u7684\u503c\u7684\u987a\u5e8f\u53ea\u9700\u8981\u5bf9\u5e94\u4f20\u5165\u7684\u5b57\u6bb5\u540d\u3002<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u7b2c\u4e09\u949f\u5199\u6cd5\u662f\u5c06\u67e5\u8be2\u7ed3\u679c\u63d2\u5165\u8868\u4e2d\u3002<\/p><p>\u00a0<\/p><p>\u5728\u7b2c\u4e00\u79cd\u5199\u6cd5\u4e2d\uff0c\u82e5\u4e0d\u4f20\u5165\u5bf9\u5e94\u7684\u503c\uff0c\u5219\u8868\u4f1a\u63d2\u5165\u5728\u521b\u5efa\u8868\u65f6\u7684\u9ed8\u8ba4\u503c<\/p><p>\u00a0<\/p><p>\u6ce8\u610f\uff1a\u4f7f\u7528\u4e86insert\u5173\u952e\u5b57\u540e\uff0c\u4e0d\u80fd\u518d\u6b21\u63d2\u5165\u8bed\u53e5\u3002\u53ea\u80fd\u4f7f\u7528update\u8fdb\u884c\u4fee\u6539\u3002<\/p><p>\u00a0<\/p><p>\u53e6\u5916\u4e5f\u53ef\u4ee5\u4e00\u6b21\u63d2\u5165\u591a\u884c\u6570\u636e\uff0c\u8981\u5728values\u5173\u952e\u5b57\u540e\u9762\u591a\u7528\u51e0\u4e2a\u5c0f\u62ec\u53f7\u4f20\u5165\u6570\u636e\u5373\u53ef\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u4fee\u6539\u8bed\u6cd5\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update \u8868\u660e set \u5b57\u6bb5\u540d1 = \u503c1\uff0c\u5b57\u6bb5\u540d2 = \u503c2\u2026\u2026where \u4fee\u6539\u6761\u4ef6;<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u82e5\u4e0d\u6dfb\u52a0where\u5173\u952e\u5b57\uff0c\u5219\u4f1a\u4fee\u6539\u8868\u4e2d\u6240\u6709\u7684\u6570\u636e\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u5220\u9664\u8bed\u6cd5\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 delete from \u8868\u540d where \u6761\u4ef6;<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u82e5\u4e0d\u6dfb\u52a0where\u5173\u952e\u5b57\uff0c\u5219\u4f1a\u5220\u9664\u8868\u4e2d\u6240\u6709\u6570\u636e\u3002<\/p><p>\u00a0<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u82e5\u8981\u5220\u9664\u5927\u8868<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 truncate table \u8868\u540d;<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u8fd9\u79cd\u65b9\u5f0f\u9002\u5408\u5220\u9664\u6570\u636e\u91cf\u5f88\u5927\u7684\u8868\uff0c\u4f46\u8fd9\u79cd\u5220\u9664\u65b9\u5f0f\u4e0d\u53ef\u56de\u6eda\uff0c\u6570\u636e\u6c38\u4e45\u4e22\u5931\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>\u7ea6\u675f<\/strong><\/p><ul><li>\u521b\u5efa\u8868\u7684\u65f6\u5019\uff0c\u53ef\u4ee5\u7ed9\u8868\u7684\u5b57\u6bb5\u6dfb\u52a0\u76f8\u5e94\u7684\u7ea6\u675f\uff0c\u6dfb\u52a0\u7ea6\u675f\u7684\u76ee\u7684\u662f\u4fdd\u8bc1\u6570\u636e\u7684\u5b8c\u6574\u6027\u3001\u5408\u6cd5\u6027\u4ee5\u53ca\u6709\u6548\u6027<\/li><li>\u5e38\u89c1\u7684\u7ea6\u675f<ol><li>\u975e\u7a7a\u7ea6\u675f\uff08not null\uff09\uff1a\u7ea6\u675f\u7684\u5b57\u6bb5\u4e0d\u80fd\u4e3anull<\/li><li>\u552f\u4e00\u7ea6\u675f\uff08unique\uff09\uff1a\u7ea6\u675f\u7684\u5b57\u6bb5\u4e0d\u80fd\u91cd\u590d<\/li><li>\u4e3b\u952e\u7ea6\u675f\uff08primary key\uff09\uff1a\u7ea6\u675f\u7684\u5b57\u6bb5\u4e0d\u80fd\u4e3anull\u4e5f\u4e0d\u80fd\u91cd\u590d<\/li><li>\u5916\u952e\u7ea6\u675f\uff08foreign key\uff09\uff1a<\/li><li>\u68c0\u67e5\u7ea6\u675f\uff08check\uff09\uff1a\u4ec5\u5728Oracle\u6570\u636e\u5e93\u4e2d\u652f\u6301\u7684\u7ea6\u675f\uff0cMySQL\u4e0d\u652f\u6301\u8be5\u7ea6\u675f\u3002<\/li><\/ol><\/li><li>\u5217\u7ea7\u7ea6\u675f\uff1a\u76f4\u63a5\u5728\u5b57\u6bb5\u540e\u9762\u52a0\u7ea6\u675f\u5173\u952e\u5b57<\/li><\/ul><p>creat table \u8868\u540d(<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d1 \u6570\u636e\u7c7b\u578b unique,<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d2 \u6570\u636e\u7c7b\u578bnot null.<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d3 \u6570\u636e\u7c7b\u578bdefault \u9ed8\u8ba4\u503c,<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026\u2026<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 );<\/p><ul><li>\u8868\u7ea7\u7ea6\u675f\uff1a\u58f0\u660e\u5b8c\u5b57\u6bb5\u540d\u5b57\u540e\u5355\u72ec\u6dfb\u52a0\u7ea6\u675f\u5173\u952e\u5b57\u4ee5\u53ca\u8981\u88ab\u7ea6\u675f\u7684\u5b57\u6bb5<\/li><\/ul><p>creat table \u8868\u540d(<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d1 \u6570\u636e\u7c7b\u578b default \u9ed8\u8ba4\u503c,<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d2 \u6570\u636e\u7c7b\u578bdefault \u9ed8\u8ba4\u503c.<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b57\u6bb5\u540d3 \u6570\u636e\u7c7b\u578bdefault \u9ed8\u8ba4\u503c,<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026\u2026<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 unique(\u5b57\u6bb5\u540d1,\u5b57\u6bb5\u540d2\u2026\u2026)<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 );<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u4e3b\u952e\u7ea6\u675f(PK)<\/p><ul><li>\u4e3b\u952e\u7ea6\u675f\u53ef\u4ee5\u4ee5\u5217\u7ea7\u7ea6\u675f\u6216\u8005\u8868\u7ea7\u7ea6\u675f\u7684\u65b9\u5f0f\u6dfb\u52a0\u3002<\/li><li>\u76f8\u5173\u672f\u8bed\uff1a<ol><li>\u4e3b\u952e\u7ea6\u675f<\/li><li>\u4e3b\u952e\u5b57\u6bb5<\/li><li>\u4e3b\u952e\u503c<\/li><\/ol><\/li><li>\u4e3b\u952e\u7684\u4f5c\u7528\uff1a<ol><li>\u8868\u7684\u4e09\u8303\u5f0f\u8bbe\u8ba1\u4e2d\u6709\u8981\u6c42\uff0c\u6bcf\u4e2a\u8868\u90fd\u5e94\u8be5\u6709\u4e3b\u952e\u3002<\/li><li>\u76f8\u5f53\u4e8e\u4e00\u6761\u8bb0\u5f55\u5728\u4e00\u5f20\u8868\u4e2d\u7684\u552f\u4e00\u6807\u8bc6\u3002<\/li><\/ol><\/li><li>\u4e3b\u952e\u7684\u5206\u7c7b\uff1a<ol><li>\u6309\u7167\u6570\u91cf\u5206\u7c7b\uff1a<ol><li>\u552f\u4e00\u4e3b\u952e\uff08\u63a8\u8350\u4f7f\u7528\uff09<\/li><li>\u590d\u5408\u4e3b\u952e\uff08\u591a\u4e2a\u5b57\u6bb5\u8054\u5408\u8d77\u6765\u505a\u4e00\u4e2a\u4e3b\u952e\uff09<\/li><\/ol><\/li><li>\u6309\u7167\u6027\u8d28\u5206\u7c7b\uff1a<ol><li>\u81ea\u7136\u4e3b\u952e\uff08\u4e0e\u8be5\u8868\u5bf9\u5e94\u7684\u4e1a\u52a1\u65e0\u4efb\u4f55\u5173\u7cfb\u7684\u81ea\u7136\u6570\uff09<\/li><li>\u4e1a\u52a1\u4e3b\u952e\uff08\u4e3b\u952e\u503c\u4e0e\u8868\u7684\u4e1a\u52a1\u6709\u5173\u8054\u3002\u6bd4\u5982\u94f6\u884c\u5361\u53f7\u6216\u8eab\u4efd\u8bc1\u53f7\u505a\u4e3b\u952e\uff09<\/li><\/ol><\/li><\/ol><\/li><\/ul><ul><li>\u4e00\u5f20\u8868\u7684\u4e3b\u952e\u53ea\u80fd\u6709\u4e00\u4e2a<\/li><li>\u4e3b\u952e\u81ea\u589e\uff1a<\/li><\/ul><p>MySQL\u4e2d\u652f\u6301\u4e3b\u952e\u81ea\u589e\u673a\u5236\uff0c\u5728primary key\u5173\u952e\u5b57\u540e\u9762\u6dfb\u52a0auto_increment\u5373\u53ef\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u5916\u952e\u7ea6\u675f(FK)<\/p><ul><li>\u6dfb\u52a0\u8bed\u6cd5\uff1a<\/li><\/ul><p>foreign key(\u5b57\u6bb5\u540d) reference \u7236\u8868\u540d(\u5b57\u6bb5\u540d)<\/p><p>\u542b\u4e49\uff1a\u8be5\u5b57\u6bb5\u4e2d\u7684\u503c\u5168\u90e8\u5f15\u7528\u4e0e\u7236\u8868\u4e2d\u7684\u67d0\u4e00\u5b57\u6bb5\u540d\u3002<\/p><ul><li>\u76f8\u5173\u672f\u8bed\uff1a<ol><li>\u5916\u952e\u7ea6\u675f<\/li><li>\u5916\u952e\u5b57\u6bb5<\/li><li>\u5916\u952e\u503c<\/li><\/ol><\/li><li>\u5916\u952e\u7684\u4f5c\u7528\uff1a<ol><li>\u8ba9\u4e00\u5f20\u8868\u7684\u5b50\u8868\u7684\u6570\u636e\u76f8\u5bf9\u7236\u8868\u66f4\u5177\u6709\u89c4\u8303\u6027\uff0c\u6709\u5229\u4e8e\u8054\u5408\u67e5\u8be2\u3002<\/li><li>\u7ef4\u62a4\u8868\u4e4b\u95f4\u7684\u5173\u7cfb\uff0c\u4fdd\u8bc1\u53c2\u7167\u7684\u5b8c\u6574\u6027\u3002<\/li><\/ol><\/li><li>\u4e00\u4e2a\u8868\u7684\u5916\u952e\u5fc5\u987b\u6765\u6e90\u4e8e\u7236\u8868\u7684\u4e3b\u952e\uff0c\u6216\u8005\u662f\u88ab\u4fee\u9970\u4e3a\u4e0d\u53ef\u91cd\u590d\u7684\u5b57\u6bb5\u3002<\/li><li>\u5916\u952e\u5b57\u6bb5\u53ef\u4ee5\u4e3anull\uff0c\u4f46\u4e0d\u63a8\u8350\u8bbe\u7f6e\u4e3anull\u3002<\/li><li>\u8054\u5408\u67e5\u8be2\u65f6\uff0c\u4e00\u822c\u4f1a\u4ee5\u8868\u7684\u5916\u952e\u6765\u8fdb\u884c\u8fde\u63a5\u3002<\/li><\/ul><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>\u4e8b\u52a1<\/strong><\/p><ul><li>\u4e8b\u52a1\u7684\u5b9a\u4e49\uff1a<\/li><\/ul><p>\u4e8b\u52a1\u662f\u5b8c\u6574\u7684\u903b\u8f91\u5355\u5143\uff0c\u4e0d\u53ef\u518d\u5206\u3002<\/p><p>\u6bd4\u5982\u8981\u6267\u884c\u4e24\u6761\u540c\u65f6\u6210\u529f\u6216\u5931\u8d25\u7684DML\u8bed\u53e5\uff0c\u4e0d\u53ef\u518d\u5206\uff0c\u5fc5\u987b\u540c\u65f6\u6210\u529f\u6216\u5931\u8d25\u3002<\/p><p>\u90a3\u4e48\u8fd9\u4e24\u6761DML\u8bed\u53e5\u5c31\u662f\u4e00\u4e2a\u4e0d\u53ef\u518d\u5206\u7684\u5b8c\u6574\u903b\u8f91\u5355\u5143\u3002<\/p><ul><li>\u4e0e\u4e8b\u52a1\u76f8\u5173\u7684\u8bed\u53e5\u53ea\u6709DML\u8bed\u53e5\uff0c\u56e0\u4e3aDML\u8bed\u53e5\u4e0e\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u7684\u5b89\u5168\u6709\u76f4\u63a5\u7684\u5173\u7cfb\u3002\u4e3a\u4e86\u4fdd\u8bc1\u6570\u636e\u7684\u51c6\u786e\u6027\u3001\u5b8c\u6574\u6027\u3001\u5b89\u5168\u6027\uff0c\u6211\u4eec\u9700\u8981\u4f7f\u7528\u4e8b\u52a1\u673a\u5236\u3002<\/li><li>\u76f8\u5173\u672f\u8bed\uff1a\u63d0\u4ea4commit\u3001\u56de\u6edarollback\u3001\u4fdd\u5b58\u70b9savepoint<\/li><li>\u4e8b\u52a1\u673a\u5236\u7684\u5e94\u7528\u662f\u5efa\u7acb\u5728\u591a\u6761DML\u8bed\u53e5\u540c\u65f6\u6267\u884c\u7684\u524d\u63d0\u4e0a\u7684\u3002<\/li><li>\u4e8b\u7269\u7684\u7279\u6027\uff08ACID\uff09\uff1a<ol><li>\u539f\u5b50\u6027Atomicity\uff1a\u4e8b\u52a1\u662f\u6700\u5c0f\u7684\u5de5\u4f5c\u5355\u5143\uff0c\u4e0d\u53ef\u518d\u5206\u3002<\/li><li>\u4e00\u81f4\u6027Consistency\uff1a\u4fdd\u8bc1\u4e8b\u52a1\u4e2d\u7684DML\u8bed\u53e5\u5fc5\u987b\u540c\u65f6\u6210\u529f\u6216\u5931\u8d25\uff0c\u4e8b\u52a1\u524d\u540e\u6570\u636e \u7684\u5b8c\u6574\u6027\u5fc5\u987b\u4e00\u81f4<\/li><li>\u9694\u79bb\u6027Isolation\uff1a\u4e0d\u540c\u7684\u4e8b\u52a1\u95f4\u5177\u6709\u9694\u79bb\u3002<\/li><li>\u6301\u4e45\u6027Durability\uff1a\u6570\u636e\u6700\u540e\u5fc5\u987b\u8981\u6301\u4e45\u5316\u5230\u786c\u76d8\u4e2d\uff0c\u4e8b\u52a1\u624d\u7b97\u7ed3\u675f\u3002<\/li><\/ol><\/li><li>MySQL\u4e2d\u7684\u7684\u4e8b\u52a1\u9ed8\u8ba4\u662f\u81ea\u52a8\u63d0\u4ea4\u7684\u3002\u6267\u884c\u4efb\u610f\u4e00\u6761DML\u8bed\u53e5\u5c31\u4f1a\u63d0\u4ea4\u3002<\/li><\/ul><p>\u5173\u95ed\u81ea\u52a8\u63d0\u4ea4\u5219\u8981\u8f93\u5165start transaction\u6765\u5f00\u542f\u4e8b\u52a1\uff0c\u4e8b\u52a1\u7ed3\u675f\u540e\u8981\u91cd\u65b0\u8f93\u5165\u3002<\/p><p>\u4e8b\u52a1\u7ed3\u675f\u524d\u53ef\u4ee5\u4f7f\u7528rollback\u8fdb\u884c\u56de\u6eda\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u4e8b\u52a1\u7684\u9694\u79bb\u7ea7\u522b<\/p><p>\u8bbe\u7f6e\u4e8b\u52a1\u7684\u5168\u5c40\u9694\u79bb\u7ea7\u522b\uff1a<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set global transaction isolation level \u7ea7\u522b\u540d\u79f0;<\/p><p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u9700\u8981\u91cd\u65b0\u767b\u9646\u624d\u4f1a\u751f\u6548\u3002<\/p><p>\u67e5\u770b\u9694\u79bb\u7ea7\u522b\uff1a<\/p><p>\u8001\u7248\u672c\uff1aselect @@global.tx_isolation;<\/p><p>\u65b0\u7248\u672c\uff1bselect @@global.transaction_isolation,@@transaction_isolation;<\/p><p>\u00a0<\/p><p>\u7b2c\u4e00\u7ea7\u522b\uff1a\u8bfb\u672a\u63d0\u4ea4read uncommitted<\/p><p>\u5373\u5bf9\u65b9\u4e8b\u52a1\u5c1a\u672a\u63d0\u4ea4\u7684\u6570\u636e\uff0c\u5f53\u524d\u4e8b\u52a1\u53ef\u4ee5\u8bfb\u53d6\u5230\u3002<\/p><p>\u5bb9\u6613\u53d1\u751f\u810f\u8bfb\uff08Dirty read\uff09\u73b0\u8c61\uff0c\u8868\u793a\u8bfb\u5230\u4e86\u810f\u6570\u636e\u3002<\/p><p>\u8fd9\u79cd\u7ea7\u522b\u57fa\u672c\u4e0d\u7528\u3002<\/p><p>\u00a0<\/p><p>\u810f\u6570\u636e\uff1a\u810f\u6570\u636e\u8868\u793a\u7684\u662f\u53e6\u4e00\u4e2a\u4e8b\u52a1\u7684\u4e34\u65f6\u6570\u636e\uff0c\u8fd9\u79cd\u6570\u636e\u6781\u4e0d\u95ee\u9898\uff0c\u5f53\u53e6\u4e00\u4e8b\u52a1\u7684\u6570\u636e\u63d0\u4ea4\u6216\u56de\u6eda\u540e\uff0c\u810f\u6570\u636e\u5c31\u4f1a\u53d8\u5316\u3002\u4f9d\u636e\u810f\u6570\u636e\u6240\u4f5c\u7684\u64cd\u4f5c\u662f\u4e0d\u6b63\u786e\u7684\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u7b2c\u4e8c\u7ea7\u522b\uff1a\u8bfb\u5df2\u63d0\u4ea4read committed<\/p><p>\u5bf9\u65b9\u4e8b\u52a1\u63d0\u4ea4\u540e\u7684\u6570\u636e\u624d\u53ef\u4ee5\u88ab\u6211\u65b9\u4e8b\u52a1\u8bfb\u53d6\u5230\u3002<\/p><p>\u8fd9\u79cd\u65b9\u5f0f\u89e3\u51b3\u4e86\u810f\u8bfb\u73b0\u8c61\u3002<\/p><p>\u4f46\u6ca1\u6709\u89e3\u51b3\u4e0d\u53ef\u91cd\u590d\u8bfb\u7684\u95ee\u9898\u3002<\/p><p>\u8fd9\u79cd\u7ea7\u522b\u662f\u4e3b\u6d41\u7684\u5b89\u5168\u7ea7\u522b\uff0c\u9002\u7528\u4e8e\u5927\u591a\u6570\u7cfb\u7edf\u3002<\/p><p>\u00a0<\/p><p>\u4e0d\u53ef\u91cd\u590d\u8bfb\uff1a\u4e8b\u52a1A\u9996\u5148\u8bfb\u53d6\u4e86\u4e00\u6761\u6570\u636e\uff0c\u7136\u540e\u6267\u884c\u903b\u8f91\u7684\u65f6\u5019\uff0c\u4e8b\u52a1B\u5c06\u8fd9\u6761\u6570\u636e\u6539\u53d8\u4e86\uff0c\u7136\u540e\u4e8b\u52a1A\u518d\u6b21\u8bfb\u53d6\u7684\u65f6\u5019\uff0c\u53d1\u73b0\u6570\u636e\u4e0d\u5339\u914d\u4e86\uff0c\u5c31\u662f\u6240\u8c13\u7684\u4e0d\u53ef\u91cd\u590d\u8bfb\u4e86\u3002\u7b80\u5355\u5730\u8bf4\u5c31\u662f\u522b\u7684\u4e8b\u52a1\u4f1a\u5f71\u54cd\u5f53\u524d\u4e8b\u52a1\u7684\u6570\u636e\uff0c\u5bfc\u81f4\u5f53\u524d\u4e8b\u52a1\u5bf9\u6570\u636e\u6267\u884c\u903b\u8f91\u7684\u65f6\u5019\uff0c\u6bcf\u6b21\u8bfb\u5230\u7684\u6570\u636e\u53ef\u80fd\u4f1a\u4e0d\u4e00\u6837\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u7b2c\u4e09\u7ea7\u522b\uff1a\u53ef\u91cd\u590d\u5ea6repeatable read<\/p><p>\u8bfb\u53d6\u6570\u636e\u524d\u4f1a\u5bf9\u5f53\u524d\u6570\u636e\u4f5c\u5907\u4efd\uff0c\u53ea\u8bfb\u53d6\u5907\u4efd\u7684\u6570\u636e\u3002<\/p><p>\u56e0\u4e3a\u6bcf\u6b21\u8bfb\u53d6\u7684\u90fd\u662f\u81ea\u5df1\u5907\u4efd\u7684\u6570\u636e\uff0c\u6240\u4ee5\u89e3\u51b3\u4e86\u4e0d\u53ef\u91cd\u590d\u8bfb\u5f97\u95ee\u9898\u3002<\/p><p>\u4f46\u6ca1\u6709\u89e3\u51b3\u5e7b\u8bfb (phantom read)\u95ee\u9898\u3002<\/p><p>\u8d44\u6e90\u6d88\u8017\u8f83\u9ad8\u7684\u7ea7\u522b\uff0c\u5e26\u6765\u4e86\u66f4\u591a\u7684\u6027\u80fd\u6d88\u8017\u3002<\/p><p>\u00a0<\/p><p>\u5e7b\u8bfb\uff1a\u4e8b\u52a1A\u9996\u5148\u6839\u636e\u6761\u4ef6\u7d22\u5f15\u5f97\u5230N\u6761\u6570\u636e\uff0c\u7136\u540e\u4e8b\u52a1B\u6539\u53d8\u4e86\u8fd9N\u6761\u6570\u636e\u4e4b\u5916\u7684M\u6761\u6216\u8005\u589e\u6dfb\u4e86M\u6761\u7b26\u5408\u4e8b\u52a1A\u641c\u7d22\u6761\u4ef6\u7684\u6570\u636e\uff0c\u5bfc\u81f4\u4e8b\u52a1A\u518d\u6b21\u641c\u7d22\u53d1\u73b0\u6709N+M\u6761\u6570\u636e\u4e86\uff0c\u5c31\u4ea7\u751f\u4e86\u5e7b\u8bfb\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u7b2c\u56db\u7ea7\u522b\uff1a\u5e8f\u5217\u5316\/\u4e32\u884c\u5316\u8bfb<\/p><p>\u6240\u6709\u4e8b\u52a1\u6392\u961f\u6267\u884c\uff0c\u6700\u4e25\u683c\u7684\u7ea7\u522b\u3002<\/p><p>\u89e3\u51b3\u6240\u6709\u95ee\u9898\u3002<\/p><p>\u8d44\u6e90\u6d88\u8017\u6700\u5927\u7684\u7ea7\u522b\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>\u7d22\u5f15<\/strong><\/p><ul><li>\u7d22\u5f15\u7684\u6982\u8ff0\uff1a<\/li><\/ul><p>\u7d22\u5f15\u76f8\u5f53\u4e8e\u4e00\u672c\u4e66\u7684\u76ee\u5f55\uff0c\u53ef\u4ee5\u5feb\u901f\u5b9a\u4f4d\u6570\u636e\u8d44\u6e90\u3002\u5728\u6570\u636e\u91cf\u5e9e\u5927\u7684\u65f6\u5019\u5feb\u901f\u68c0\u7d22\u3002<\/p><p>\u67e5\u8be2\u4e00\u5f20\u8868\u7684\u65f6\u5019\u6709\u4e24\u79cd\u65b9\u5f0f\uff1a\u5168\u8868\u626b\u63cf\u3001\u4f9d\u636e\u7d22\u5f15\u68c0\u7d22\u3002<\/p><ul><li>\u7f29\u5c0f\u4e86\u626b\u63cf\u8303\u56f4\u662f\u7d22\u5f15\u63d0\u9ad8\u68c0\u7d22\u6548\u7387\u7684\u6839\u672c\u539f\u56e0\uff0c\u5e95\u5c42\u4f7f\u7528\u7684\u6570\u636e\u7ed3\u6784\u662fB+Tree\uff08\u591a\u8def\u641c\u7d22\u6811\uff09\u3002<\/li><li>\u4f7f\u7528\u7d22\u5f15\u8981\u8003\u8651\u591a\u65b9\u9762\u56e0\u7d20\uff0c\u56e0\u4e3a\u6570\u636e\u4e00\u65e6\u6539\u52a8\uff0c\u7d22\u5f15\u662f\u8981\u91cd\u65b0\u7ef4\u62a4\u7684\uff0c\u6d88\u8017\u7ef4\u62a4\u6210\u672c\u3002<\/li><li>\u9002\u5408\u6dfb\u52a0\u7d22\u5f15\u7684\u73af\u5883\uff1a<ol><li>\u6570\u636e\u91cf\u76f8\u5bf9\u5e9e\u5927<\/li><li>\u6dfb\u52a0\u7d22\u5f15\u7684\u5b57\u6bb5\u6781\u5c11\u8fdb\u884cDML\u64cd\u4f5c\uff0c\u907f\u514d\u7d22\u5f15\u7ef4\u62a4\u3002<\/li><li>\u6dfb\u52a0\u7d22\u5f15\u7684\u5b57\u6bb5\u5e38\u51fa\u73b0\u5728where\u5b50\u53e5\u4e2d\u3002<\/li><\/ol><\/li><li>\u6ce8\u610f\uff1a\u4e3b\u952e\u4ee5\u53ca\u5177\u6709unique\u7ea6\u675f\u7684\u5b57\u6bb5\u81ea\u52a8\u6dfb\u52a0\u7d22\u5f15\u3002<\/li><li>\u67e5\u770b\u5f53\u524dSQL\u7684\u6267\u884c\u8ba1\u5212\uff1a<\/li><\/ul><p>explain SQL\u8bed\u53e5;<\/p><p>\u5728\u51fa\u6765\u7684\u8868\u683c\u4e2d\u7684type\u5b57\u6bb5\u4e2d\u53ef\u4ee5\u770b\u5230\u626b\u63cf\u7c7b\u578b\uff0call\u4e3a\u5168\u8868\u626b\u63cf\u3002<\/p><p>\u8868\u683c\u4e2d\u7684rows\u5b57\u6bb5\u53ef\u4ee5\u67e5\u770b\u68c0\u7d22\u4e86\u591a\u5c11\u6761\u6570\u636e\u3002<\/p><ul><li>\u7ed9\u5b57\u6bb5\u6dfb\u52a0\u7d22\u5f15;<\/li><\/ul><p>creat index \u7d22\u5f15\u540d\u79f0 on \u8868\u540d(\u5b57\u6bb5\u540d)\uff1b<\/p><ul><li>\u5220\u9664\u7d22\u5f15\uff1a<\/li><\/ul><p>DROP INDEX \u7d22\u5f15\u540d\u79f0 ON \u8868\u540d;<\/p><ul><li>\u5b9e\u73b0\u539f\u7406\uff1a<\/li><\/ul><p>\u5f53\u6dfb\u52a0\u7d22\u5f15\u540e\uff0c\u4f1a\u5728\u7d22\u5f15\u6240\u5728\u5b57\u6bb5\u751f\u6210\u4e00\u4e2a\u5730\u57ce\u5b9e\u73b0\u4e86\u6392\u5e8f\u5206\u533a\u7684B_tree\u6570\u636e\u7ed3\u6784\u7684\u6392\u5e8f\u8868\u3002\u7d22\u5f15\u4f1a\u83b7\u53d6\u6570\u636e\u5728\u8868\u4e2d\u7684\u201c\u7269\u7406\u5730\u5740\uff0c\u68c0\u7d22\u5230\u6570\u636e\u540e\u8fd4\u56de\u76f8\u5173\u7684\u7269\u7406\u5730\u5740\uff0c\u76f4\u63a5\u901a\u8fc7\u7269\u7406\u5730\u5740\u5b9a\u4f4d\u5230\u786c\u76d8\u4e2d\u7684\u76f8\u5173\u6570\u636e\uff0c\u4ee5\u83b7\u5f97\u66f4\u9ad8\u7684\u6548\u7387\u201d<\/p><ul><li>\u7d22\u5f15\u7684\u5206\u7c7b\uff1a<ol><li>\u5355\u4e00\u7d22\u5f15\uff1a\u5355\u4e2a\u5b57\u6bb5\u6dfb\u52a0\u7d22\u5f15<\/li><li>\u590d\u5408\u7d22\u5f15\uff1a\u591a\u4e2a\u5b57\u6bb5\u8054\u5408\u6dfb\u52a0\u4e00\u4e2a\u7d22\u5f15<\/li><li>\u4e3b\u952e\u7d22\u5f15\uff1a\u4e3b\u952e\u4e0a\u81ea\u52a8\u6dfb\u52a0\u7d22\u5f15<\/li><li>\u552f\u4e00\u7d22\u5f15\uff1a\u6709unique\u7ea6\u675f\u7684\u5b57\u6bb5\u81ea\u52a8\u6dfb\u52a0\u7d22\u5f15<\/li><\/ol><\/li><\/ul><p>10\u3001\u7d22\u5f15\u5931\u6548\uff1a<\/p><p>\u5f53\u4f7f\u7528\u6a21\u7cca\u67e5\u8be2\uff0c\u4e14\u7b2c\u4e00\u4e2a\u901a\u914d\u7b26\u4f7f\u7528\u7684\u662f\u201c%\u201c\uff0c\u6b64\u65f6\u7d22\u5f15\u5931\u6548\uff0c\u53ea\u80fd\u4f7f\u7528\u5168\u8868\u626b\u63cf\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>\u89c6\u56fe<\/strong><\/p><ul><li>\u89c6\u56fe\u662f\u4e00\u79cd\u4ee5\u4e0d\u540c\u65b9\u5f0f\u770b\u5f85\u8868\u7684\u65b9\u6cd5\u3002\u6709\u65f6\u5019\u4e5f\u88ab\u79f0\u4e3a\u865a\u62df\u8868\uff0c\u4f46\u662f\u5bf9\u89c6\u56fe\u8fdb\u884cCRUD\u4f1a\u5f71\u54cd\u539f\u8868\u4e2d\u7684\u6570\u636e\u3002<\/li><li>\u89c6\u56fe\u7684\u4f5c\u7528\uff1a<ol><li>\u901a\u8fc7\u7b80\u5316\uff0c\u8bbf\u95ee\u6570\u636e\u53d8\u5f97\u7b80\u5355<\/li><li>\u5bf9\u4e0d\u540c\u7528\u6237\u5c55\u793a\u4e0d\u540c\u7684\u5185\u5bb9<\/li><li>\u9690\u85cf\u8868\u7684\u5177\u4f53\u7ec6\u8282\uff0c\u53ea\u5c55\u793a\u8868\u7684\u5927\u81f4\u7ed3\u6784\u3002\uff08\u9762\u5411\u89c6\u56fe\u68c0\u7d22\uff09<\/li><\/ol><\/li><li>\u521b\u5efa\u89c6\u56fe\uff1a<\/li><\/ul><p>create view \u89c6\u56fe\u540d as DQL\u8bed\u53e5;<\/p><p>\u521b\u5efa\u89c6\u56fe\u53ea\u80fd\u901a\u8fc7DQL\u8bed\u53e5\u8fdb\u884c\u521b\u5efa\u3002<\/p><p>\u53ef\u5728DQL\u8bed\u53e5\u4e2d\u5bf9\u5b57\u6bb5\u8fdb\u884c\u6539\u540d\u4ee5\u5b9e\u73b0\u9690\u85cf\u539f\u8868\u5b57\u6bb5\u540d\u7684\u6548\u679c\u3002<\/p><ul><li>\u5220\u9664\u89c6\u56fe\uff1a<\/li><\/ul><p>drop view \u89c6\u56fe\u540d;<\/p><ul><li>\u89c6\u56fe\u7684CRUD:<\/li><\/ul><p>\u4e0e\u5bf9\u8868\u7684CRUD\u76f8\u540c\uff0c\u5c06from\u540e\u7684\u8868\u540d\u6539\u4e3a\u89c6\u56fe\u540d\u5373\u53ef\u3002<\/p><p>6\u3001\u901a\u8fc7\u89c6\u56fe\u67e5\u8be2\u4e0d\u4f1a\u771f\u6b63\u610f\u4e49\u4e0a\u7684\u63d0\u9ad8\u68c0\u7d22\u6548\u7387\uff0c\u771f\u8981\u63d0\u9ad8\u8fd8\u5f97\u770b\u7d22\u5f15.\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>DBA<\/strong><strong>\u547d\u4ee4<\/strong><\/p><ul><li>\u5e38\u7528\u4e8e\u5bfc\u51fa\u5bfc\u5165\u6570\u636e\u8fdb\u884c\u6570\u636e\u5907\u4efd\u4ee5\u53ca\u6570\u636e\u8f6c\u79fb\u3002<\/li><li>\u5bfc\u51fa\uff1a\uff08\u5728dos\u547d\u4ee4\u7a97\u53e3\u4e2d\u6267\u884c\uff09<ol><li>\u5bfc\u51fa\u6574\u4e2a\u6570\u636e\u5e93\uff1a<\/li><\/ol><\/li><\/ul><p>mysqldump \u6570\u636e\u5e93\u540d&gt;\u5bfc\u51fa\u8def\u5f84\u4ee5\u53ca\u6587\u4ef6\u540d.sql -u\u7528\u6237\u540d -p\u5bc6\u7801<\/p><p>mysqldump sinot&gt; D:\\JAVA\\sinot.sql -uroot -p159326<\/p><ol><li>\u5bfc\u51fa\u6570\u636e\u5e93\u4e2d\u7684\u67d0\u4e2a\u8868\uff1a<\/li><\/ol><p>mysqldump \u6570\u636e\u5e93\u540d \u8868\u540d&gt;\u5bfc\u51fa\u8def\u5f84\u4ee5\u53ca\u6587\u4ef6\u540d.sql -u\u7528\u6237\u540d -p\u5bc6\u7801<\/p><p>mysqldump sinot demp&gt; D:\\JAVA\\sinot_demp.sql -uroot -p159326<\/p><ul><li>\u5bfc\u5165\uff1a<ol><li>\u5148\u521b\u5efa\u6570\u636e\u5e93\u5e76\u4f7f\u7528<\/li><li>source sql\u811a\u672c\u6240\u5728\u8def\u5f84;<\/li><\/ol><\/li><\/ul><p>\u00a0<\/p><p>\u00a0<\/p><p><strong>\u4e09\u8303\u5f0f<\/strong><\/p><ul><li>\u8bbe\u8ba1\u8303\u5f0f\uff0c\u5c31\u662f\u8bbe\u8ba1\u8868\u7684\u4f9d\u636e\uff0c\u4e3b\u8981\u76ee\u7684\u662f\u51cf\u5c11\u6570\u636e\u5197\u4f59\u3002<\/li><li>\u4e09\u8303\u5f0f\u7684\u7684\u7b2c\u4e8c\u8303\u5f0f\u5efa\u7acb\u5728\u4e00\u8303\u5f0f\u7684\u57fa\u7840\u4e4b\u4e0a\uff0c\u7b2c\u4e09\u8303\u5f0f\u5efa\u7acb\u5728\u7b2c\u4e8c\u8303\u5f0f\u4e4b\u4e0a\u3002<\/li><li>\u4e00\u8303\u5f0f\uff1a<\/li><\/ul><p>\u4efb\u4f55\u4e00\u5f20\u8868\u90fd\u8981\u6709\u4e3b\u952e\uff0c\u4e14\u6bcf\u4e2a\u5b57\u6bb5\u90fd\u67091\u539f\u5b50\u6027\uff0c\u4e0d\u53ef\u518d\u5206\u3002<\/p><ul><li>\u4e8c\u8303\u5f0f\uff1a<\/li><\/ul><p>\u6240\u6709\u975e\u4e3b\u952e\u5b57\u6bb5\u8981\u5b8c\u5168\u4f9d\u8d56\u4e3b\u952e\uff0c\u4e0d\u80fd\u4ea7\u751f\u90e8\u5206\u4f9d\u8d56\u3002<\/p><p>\u591a\u5bf9\u591a\uff0c\u4e09\u5f20\u8868\uff0c\u5173\u7cfb\u8868\u6709\u4e24\u5916\u952e\u3002<\/p><ul><li>\u4e09\u8303\u5f0f\uff1a<\/li><\/ul><p>\u6240\u6709\u975e\u4e3b\u952e\u5b57\u6bb5\u8981\u76f4\u63a5\u4f9d\u8d56\u4e3b\u952e\uff0c\u4e0d\u80fd\u4ea7\u751f\u4f20\u9012\u4f9d\u8d56\u3002<\/p><p>\u4e00\u5bf9\u591a\uff0c\u4e24\u5f20\u8868\uff0c\u591a\u7684\u8868\u8981\u52a0\u5916\u952e\u3002<\/p><ul><li>\u63d0\u9192\uff1a\u8bbe\u8ba1\u8868\u662f\u5e76\u4e0d\u662f\u4e00\u5b9a\u8981\u5b8c\u5168\u9075\u5b88\u4e09\u8303\u5f0f\uff0c\u8868\u7684\u8054\u5408\u8d8a\u591a\uff0c\u67e5\u8be2\u6548\u7387\u8d8a\u4f4e\uff0c\u6709\u65f6\u5019\u9700\u8981\u727a\u7272\u6570\u636e\u5e93\u7a7a\u95f4\u4ee5\u83b7\u53d6\u67e5\u8be2\u901f\u5ea6\uff0c\u4e3b\u8981\u4ee5\u5ba2\u6237\u9700\u6c42\u4e3a\u4e3b\u3002<\/li><li>\u4e00\u5bf9\u4e00\u7684\u8bbe\u8ba1<ol><li>\u4e3b\u952e\u5171\u4eab\uff1a\u4e24\u5f20\u8868\u516c\u7528\u4e00\u4e2a\u4e3b\u952e\uff0c\u5176\u4e2d\u4e00\u5f20\u8868\u7684\u4e3b\u952e\u65e2\u6709\u5916\u952e\u7ea6\u675f\u4e5f\u6709\u4e3b\u952e\u7ea6\u675f\u3002<\/li><li>\u5916\u952e\u552f\u4e00\uff1a\u4e24\u5f20\u8868\u90fd\u6709\u5404\u81ea\u7684\u4e3b\u952e\uff0c\u5176\u4e2d\u4e00\u5f20\u8868\u6709\u5355\u72ec\u7684\u5916\u952e\uff0c\u8be5\u5916\u952e\u4ee5\u53e6\u4e00\u5f20\u8868\u7684\u4e3b\u952e\u4f5c\u7236\u8868\u5b57\u6bb5\u540d\uff0c\u5e76\u4e14\u6dfb\u52a0unique\u7ea6\u675f\u3002\u4e5f\u5c31\u662f\u4e00\u5bf9\u591a\u7684\u8bbe\u8ba1\uff0c\u8868\u989d\u5916\u52a0\u4e86\u552f\u4e00\u6027\u7ea6\u675f\u53d8\u4e3a\u4e00\u5bf9\u4e00\u7684\u8bbe\u8ba1\u3002<\/li><\/ol><\/li><\/ul><p>\u00a0<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u6ce8\u610f\uff1a<\/p><p>SQL\u8bed\u53e5\u90fd\u662f\u4ee5\u5206\u53f7\u7ed3\u5c3e<\/p><p>SQL\u8bed\u53e5\u4e0d\u533a\u5206\u5927\u5c0f\u5199<\/p><p>SQL\u4e2d\u5b57\u7b26\u4e32\u7528\u5355\u5f15\u53f7\u62ec\u8d77\u6765<\/p><p>\u00a0<\/p><p>count(*)\u4e0ecount(\u5b57\u6bb5\u540d)\u7684\u533a\u522b\uff1a\u52a0\u661f\u53f7\u4ee3\u8868\u8ba1\u7b97\u67d0\u4e2a\u5b57\u6bb5\u7684\u603b\u8bb0\u5f55\u6761\u6570\uff0c\u52a0\u5177\u4f53\u5b57\u6bb5\u540d\u4ee3\u8868\u8ba1\u7b97\u8be5\u5b57\u6bb5\u540d\u4e2d\u4e0d\u4e3anull\u7684\u6570\u636e\u6570\u91cf\u3002<\/p><p>\u00a0<\/p><p>\u53ea\u80fd\u7528\u4e8e\u6240\u6709\u5b57\u6bb5\u540d\u524d\u3002<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u00a0<\/p><p>\u00a0<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>\u65e7\u65f6\u7b14\u8bb0-MySQL \u524d\u8a00 \u8be5\u6587\u7ae0\u662f\u7ad9\u4e3b\u524d\u671f\u5b66\u4e60\u7b14\u8bb0\uff0c\u642c\u8fd0\u4e8e\u6b64\u65b9\u4fbf\u67e5\u8be2\u3002 \u6682\u6ca1\u6709\u4f5c\u683c\u5f0f\u5904\u7406\u3002 \u6b63\u6587 \u6570\u636e\u5e93\u6982\u8ff0 &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.xinot.icu\/?p=1612\"> <span class=\"screen-reader-text\">\u65e7\u65f6\u7b14\u8bb0-MySQl<\/span> \u67e5\u770b\u5168\u6587 &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":1615,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[],"class_list":["post-1612","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-24"],"_links":{"self":[{"href":"https:\/\/www.xinot.icu\/index.php?rest_route=\/wp\/v2\/posts\/1612","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.xinot.icu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xinot.icu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xinot.icu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xinot.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1612"}],"version-history":[{"count":21,"href":"https:\/\/www.xinot.icu\/index.php?rest_route=\/wp\/v2\/posts\/1612\/revisions"}],"predecessor-version":[{"id":1663,"href":"https:\/\/www.xinot.icu\/index.php?rest_route=\/wp\/v2\/posts\/1612\/revisions\/1663"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.xinot.icu\/index.php?rest_route=\/wp\/v2\/media\/1615"}],"wp:attachment":[{"href":"https:\/\/www.xinot.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1612"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xinot.icu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1612"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xinot.icu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1612"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}