{"id":482,"date":"2021-07-28T19:32:38","date_gmt":"2021-07-28T11:32:38","guid":{"rendered":"https:\/\/egonlin.com\/?p=482"},"modified":"2024-05-16T10:33:04","modified_gmt":"2024-05-16T02:33:04","slug":"%e7%ac%ac%e4%ba%8c%e8%8a%82%ef%bc%9asql%e5%ba%94%e7%94%a8","status":"publish","type":"post","link":"https:\/\/egonlin.com\/?p=482","title":{"rendered":"\u7b2c\u4e8c\u8282\uff1aSQL\u5e94\u7528"},"content":{"rendered":"

SQL\u5e94\u7528<\/h1>\n

\u4e00\u3001client<\/h2>\n
mysql> help\n?         (\\?) Synonym for `help'.\nclear     (\\c) Clear the current input statement.\nconnect   (\\r) Reconnect to the server. Optional arguments are db and host.\ndelimiter (\\d) Set statement delimiter.\nedit      (\\e) Edit command with $EDITOR.\nego       (\\G) Send command to mysql server, display result vertically.\nexit      (\\q) Exit mysql. Same as quit.\ngo        (\\g) Send command to mysql server.\nhelp      (\\h) Display this help.\nnopager   (\\n) Disable pager, print to stdout.\nnotee     (\\t) Don't write into outfile.\npager     (\\P) Set PAGER [to_pager]. Print the query results via PAGER.\nprint     (\\p) Print current command.\nprompt    (\\R) Change your mysql prompt.\nquit      (\\q) Quit mysql.\nrehash    (\\#) Rebuild completion hash.\nsource    (\\.) Execute an SQL script file. Takes a file name as an argument.\nstatus    (\\s) Get status information from the server.\nsystem    (\\!) Execute a system shell command.\ntee       (\\T) Set outfile [to_outfile]. Append everything into given outfile.\nuse       (\\u) Use another database. Takes database name as argument.\ncharset   (\\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.\nwarnings  (\\W) Show warnings after every statement.\nnowarning (\\w) Don't show warnings after every statement.\nresetconnection(\\x) Clean session context.\n\n\u5e38\u7528\n\\?  \u548c'help'\u547d\u4ee4\u76f8\u540c\n\\c  \u963b\u6b62\u4e0a\u4e2a\u547d\u4ee4\u8fd0\u884c\n\\G  \u683c\u5f0f\u5316\u8f93\u51fa\uff08\u9010\u884c\u8f93\u51fa\uff0c\u9488\u5bf9\u5217\u7279\u522b\u591a\u7684\u573a\u666f\uff09\n\\q  \u9000\u51fa\u4f1a\u8bdd\uff08ctrl+d\uff09\n\\.  source \u5bfc\u5165SQL\u811a\u672c\uff0c\u7c7b\u4f3c\u4e8e<\n\\!  \u8c03\u7528linux\u547d\u4ee4<\/code><\/pre>\n
\u4e8c\u3001server<\/h2>\n
linux\u5f53\u4e2d\u4e00\u5207\u7686\u547d\u4ee4\uff0c\u4e00\u5207\u7686\u6587\u4ef6\u3002\nmysql\u4e00\u5207\u7686SQL\uff0c\u4e00\u5207\u7686\u5e93\u3001\u8868\u3002<\/code><\/pre>\n
1\u3001DDL<\/h3>\n
\u6570\u636e\u5b9a\u4e49\u8bed\u8a00<\/strong><\/p>\n
1\uff09\u5e93\u5b9a\u4e49<\/h4>\n
\u5e93\u540d\uff0c\u5e93\u5c5e\u6027<\/code><\/pre>\n
\u5efa\u5e93<\/h5>\n
create database \u5e93\u540d charset utf8mb4 collate utf8mb4_bin;\n\u6570\u636e\u5e93\u540d                \u5b57\u7b26\u96c6         \u6392\u5e8f\u89c4\u5219    \n\n\u5efa\u5e93\u89c4\u8303\uff1a\n1.\u5e93\u540d\u4e0d\u80fd\u6709\u5927\u5199\u5b57\u6bcd     #\u591a\u5e73\u53f0\u517c\u5bb9\u95ee\u9898\n2.\u5efa\u5e93\u8981\u52a0\u5b57\u7b26\u96c6         \n3.\u5e93\u540d\u4e0d\u80fd\u6709\u6570\u5b57\u5f00\u5934\n4.\u5e93\u540d\u8981\u548c\u4e1a\u52a1\u76f8\u5173\n5.\u5e93\u540d\u4e0d\u8981\u592a\u957f\n6.\u4e0d\u8981\u4f7f\u7528\u5185\u7f6e\u5b57\u7b26\n\ncreate database xiaowu\uff1b\nshow create database xiaowu\uff1b    #\u67e5\u770b\u5efa\u5e93\u7684\u57fa\u672c\u547d\u4ee4\uff08\u5efa\u5e93\u8bed\u53e5\uff09<\/code><\/pre>\n
\u67e5\u5e93\uff08DQL\uff09<\/h5>\n
show databases\uff1b\nshow create database xiaowu\uff1b    #\u67e5\u770b\u5efa\u5e93\u7684\u57fa\u672c\u547d\u4ee4\uff08\u5efa\u5e93\u8bed\u53e5\uff09<\/code><\/pre>\n
\u4fee\u6539\u5e93<\/h5>\n
show create database school;\nalter database xiaowu charset utf8;\n\n\u6ce8\u610f\uff1a\u4fee\u6539\u5b57\u7b26\u96c6\uff0c\u4fee\u6539\u540e\u7684\u5b57\u7b26\u96c6\u4e00\u5b9a\u662f\u539f\u5b57\u7b26\u96c6\u7684\u4e25\u683c\u8d85\u96c6\n\u53ea\u80fd\u6539\u5e93\u5c5e\u6027\uff0c\u4e0d\u80fd\u6539\u5e93\u540d\u3002<\/code><\/pre>\n
\u5220\u5e93<\/h5>\n
\u751f\u4ea7\u4e2d\u8c28\u614e\u4f7f\u7528<\/strong><\/p>\n
mysql> drop database xiaowu;<\/code><\/pre>\n
2\uff09\u8868\u5b9a\u4e49<\/h4>\n
\u521b\u5efa\u8868<\/h5>\n
create table stu(\n\u52171  \u5c5e\u6027\uff08\u6570\u636e\u7c7b\u578b\u3001\u7ea6\u675f\u3001\u5176\u4ed6\u5c5e\u6027\uff09 \uff0c\n\u52172  \u5c5e\u6027\uff0c\n\u52173  \u5c5e\u6027\n)\n\nUSE school;\nCREATE TABLE stu(\nid      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '\u5b66\u53f7',\nsname   VARCHAR(255) NOT NULL COMMENT '\u59d3\u540d',\nsage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '\u5e74\u9f84',\nsgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '\u6027\u522b' ,\nsfz     CHAR(18) NOT NULL UNIQUE  COMMENT '\u8eab\u4efd\u8bc1',\nintime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '\u5165\u5b66\u65f6\u95f4'\n) ENGINE=INNODB CHARSET=utf8mb4 COMMENT '\u5b66\u751f\u8868';\n<\/code><\/pre>\n\n\n\n\n\n\n\n\n\n\n
\u5217\u540d<\/th>\n\u6570\u636e\u7c7b\u578b<\/th>\n\u957f\u5ea6<\/th>\n\u9ed8\u8ba4<\/th>\n\u4e3b\u952e<\/th>\n\u975e\u7a7a<\/th>\n\u65e0\u7b26\u53f7<\/th>\n\u81ea\u589e<\/th>\n\u5217\u503c\u4e0d\u91cd\u590d<\/th>\n\u6ce8\u91ca<\/th>\n<\/tr>\n<\/thead>\n
id<\/td>\nint<\/td>\n<\/td>\n<\/td>\nPRIMARY KEY<\/td>\nNOT NULL<\/td>\n<\/td>\nAUTO_INCREMENT<\/td>\n<\/td>\nCOMMENT ‘\u5b66\u53f7’<\/td>\n<\/tr>\n
name<\/td>\nvarchar<\/td>\n255<\/td>\n<\/td>\n<\/td>\nNOT NULL<\/td>\n<\/td>\n<\/td>\n<\/td>\nCOMMENT ‘\u59d3\u540d’<\/td>\n<\/tr>\n
age<\/td>\ntinyint<\/td>\n<\/td>\nDEFAULT 0<\/td>\n<\/td>\nNOT NULL<\/td>\nUNSIGNED<\/td>\n<\/td>\n<\/td>\nCOMMENT ‘\u5e74\u9f84’<\/td>\n<\/tr>\n
gender<\/td>\nENUM(‘m’,’f’,’n’)<\/td>\n<\/td>\nDEFAULT ‘n’<\/td>\n<\/td>\nNOT NULL<\/td>\n<\/td>\n<\/td>\n<\/td>\nCOMMENT ‘\u6027\u522b’<\/td>\n<\/tr>\n
sfz<\/td>\nCHAR<\/td>\n18<\/td>\n<\/td>\n<\/td>\nNOT NULL<\/td>\n<\/td>\n<\/td>\nUNIQUE<\/td>\nCOMMENT ‘\u8eab\u4efd\u8bc1’<\/td>\n<\/tr>\n
intinme<\/td>\nTIMESTAMP<\/td>\n<\/td>\nDEFAULT NOW()<\/td>\n<\/td>\nNOT NULL<\/td>\n<\/td>\n<\/td>\n<\/td>\nCOMMENT ‘\u5165\u5b66\u65f6\u95f4’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
ENGINE=INNODB        CHARSET=utf8mb4            COMMENT '\u5b66\u751f\u8868'\n\u5b58\u50a8\u5f15\u64ce                \u5b57\u7b26\u96c6                     \u6ce8\u91ca<\/code><\/pre>\n
\u5efa\u8868\u89c4\u8303\uff1a<\/strong><\/p>\n
1. \u8868\u540d\u5c0f\u5199 #\u591a\u5e73\u53f0\u517c\u5bb9\u95ee\u9898\n2. \u4e0d\u80fd\u662f\u6570\u5b57\u5f00\u5934\n3. \u6ce8\u610f\u5b57\u7b26\u96c6\u548c\u5b58\u50a8\u5f15\u64ce\n4. \u8868\u540d\u548c\u4e1a\u52a1\u6709\u5173\n5. \u9009\u62e9\u5408\u9002\u7684\u6570\u636e\u7c7b\u578b    #\u5408\u9002\uff0c\u7b80\u77ed\uff0c\u8db3\u591f\n6. \u6bcf\u4e2a\u5217\u90fd\u8981\u6709\u6ce8\u91ca\n7. \u6bcf\u4e2a\u5217\u8bbe\u7f6e\u4e3a\u975e\u7a7a\uff0c\u65e0\u6cd5\u4fdd\u8bc1\u975e\u7a7a\uff0c\u9ed8\u8ba4\u503c\u6216\u75280\u6765\u586b\u5145\u3002\n8. \u5fc5\u987b\u8981\u6709\u4e3b\u952e\n9. \u5217\u540d\u4e0d\u8981\u592a\u957f\n<\/code><\/pre>\n
\u7ec3\u4e60\uff1a<\/strong><\/p>\n
\"\"<\/div>
\n
\"\"<\/div><\/p>\n
1\u3001\u8868\u540d\u8fc7\u957f\n2\u3001id bigint\uff0820\uff09\u8fc7\u5927\n3\u3001\u6570\u5b57\u5217\u5e94\u8be5\u7528\u6570\u5b57\u7c7b\u578b\n4\u3001distribution_cost\u5e94\u8be5\u4f7f\u7528\u5c0f\u6570\n5\u3001\u65f6\u95f4\u5217\u5e94\u8be5datatime\u7c7b\u578b\n6\u3001is_deleted\u7528\u679a\u4e3e\u7c7b\u578b<\/code><\/pre>\n
\u67e5\u8be2\u8868(DQL)<\/h5>\n
mysql> show tables;\nmysql> desc wp_users;\nmysql> show create table stu;<\/code><\/pre>\n
\u4fee\u6539\u8868<\/h5>\n
\u4f8b\u5b50\uff1a<\/p>\n
1.\u5728stu\u8868\u4e2d\u6dfb\u52a0\u624b\u673a\u5217\nALTER TABLE stu ADD shouji bigint NOT NULL UNIQUE KEy COMMENT '\u624b\u673a\u53f7';\n\nalter table stu add shouji bigint notnull unique key comment '\u624b\u673a\u53f7' first ;    #\u5217\u9996\u6dfb\u52a0\n\nalter table stu add shouji bigint notnull unique key comment '\u624b\u673a\u53f7' after id ; #\u5728id\u5217\u540e\u6dfb\u52a0\u4e00\u4e2a\u5217\n\n2.\u624b\u673a\u5217\u4fee\u6539\u6570\u636e\u7c7b\u578b\u4e3achar\uff0811\uff09 modefy\nalter table stu modify shouji char(11) not null unique key comment '\u624b\u673a\u53f7';\nalter table stu rename t2;  \u6539\u8868\u540d\n3.\u5220\u9664\u624b\u673a\u53f7\u5217\uff08\u5371\u9669\u64cd\u4f5c\uff09\nmysql> alter table stu drop shouji;\n<\/code><\/pre>\n
\u5220\u9664\u8868\u3001\u5e93<\/h5>\n
mysql> drop table stu;\nmysql> drop database \u5e93\u540d;<\/code><\/pre>\n
3)DDL\u6269\u5145<\/h4>\n
1\u3001\u521b\u5efa\u4e00\u5f20\u8868\ncreate table \u5e93\u540d.\u8868\u540d(\n\u5217\u540d \u6570\u636e\u7c7b\u578b \u7ea6\u675f \u5c5e\u6027,\n\u5217\u540d \u6570\u636e\u7c7b\u578b \u7ea6\u675f \u5c5e\u6027,\n\u5217\u540d \u6570\u636e\u7c7b\u578b \u7ea6\u675f \u5c5e\u6027,\n...\n)engine(\u5f15\u64ce)=innodb charset(\u5b57\u7b26\u96c6)=utf8mb4;\n\n2\u3001\u7ebf\u4e0aDDL\uff08alter\uff09\u64cd\u4f5c\u5bf9\u4e8e\u751f\u4ea7\u7684\u5f71\u54cd\n\u8bf4\u660e\uff1a\u5728MySQL\u4e2d\uff0cDDL\u8bed\u53e5\u5728\u5bf9\u8868\u8fdb\u884c\u64cd\u4f5c\u662f\uff0c\u662f\u8981\u9501\u201c\u5143\u6570\u636e\u8868\u201d\u7684\u3002\n\u6269\u5c55\uff1a\u5143\u6570\u636e\u662f\u4ec0\u4e48\uff1f ---\u300b\u7c7b\u4f3clinux inode\u4fe1\u606f\n      \u5728MySQL\u4e2d\uff0cDDL\u8bed\u53e5\u5bf9\u8868\u8fdb\u884c\u64cd\u4f5c\u65f6\uff0c\u662f\u8981\u9501\u201c\u5143\u6570\u636e\u8868\u201d\u7684\uff0c\u6b64\u65f6\uff0c\u6240\u6709\u4fee\u6539\u7c7b\u547d\u4ee4\u65e0\u6cd5\u6b63\u5e38\u8fd0\u884c\u3002\u6240\u4ee5\u5728\u5bf9\u4e8e\u5927\u8868\u3001\u4e1a\u52a1\u7e41\u5fd9\u7684\u8868\uff0c\u8fdb\u884c\u5148\u4e0aDDL\u64cd\u4f5c\u65f6\uff0c\u8981\u8c28\u614e\u3002\u5c3d\u91cf\u907f\u5f00\u4e1a\u52a1\u7e41\u5fd9\u65f6\u95f4\uff0c\u8fdb\u884cDDL\u64cd\u4f5c\u3002<\/code><\/pre>\n
\"\"<\/div><\/p>\n
\u9762\u8bd5\u9898\u56de\u7b54\u8981\u70b9\uff1a<\/p>\n
1.SQL\u8bed\u53e5\u7684\u610f\u601d\u662f\u4ec0\u4e48\n    \u4ee5\u4e0a\u56db\u6761\u8bed\u53e5\u662f\u8fdb\u884cDDL\u52a0\u5217\u64cd\u4f5c\n2.\u4ee5\u4e0a\u64cd\u4f5c\u5e26\u6765\u7684\u5f71\u54cd\n    \u5728MySQL\u4e2d\uff0cDDL\u8bed\u53e5\u5bf9\u8868\u8fdb\u884c\u64cd\u4f5c\u65f6\uff0c\u662f\u8981\u9501\u201c\u5143\u6570\u636e\u8868\u201d\u7684\uff0c\u6b64\u65f6\uff0c\u6240\u6709\u4fee\u6539\u7c7b\u547d\u4ee4\u65e0\u6cd5\u6b63\u5e38\u8fd0\u884c\u3002\u6240\u4ee5\u5728\u5bf9\u4e8e\u5927\u8868\u3001\u4e1a\u52a1\u7e41\u5fd9\u7684\u8868\uff0c\u8fdb\u884c\u5148\u4e0aDDL\u64cd\u4f5c\u65f6\uff0c\u8981\u8c28\u614e\u3002\u5c3d\u91cf\u907f\u5f00\u4e1a\u52a1\u7e41\u5fd9\u65f6\u95f4\uff0c\u8fdb\u884cDDL\u64cd\u4f5c\u3002\n3.\u6211\u4eec\u7684\u5efa\u8bae\uff1a\n    \uff081\uff09\u5c3d\u91cf\u907f\u5f00\u4e1a\u52a1\u7e41\u5fd9\u65f6\u95f4\uff0c\u8fdb\u884cDDL\u3002\u8d70\u6d41\u7a0b\n    \uff082\uff09\u5efa\u8bae\u4f7f\u7528\uff1apt-online-schema-change\uff08pt-osc\uff09 gh-ost\u5de5\u5177\u8fdb\u884cDDL\u64cd\u4f5c\uff0c\u51cf\u5c11\u9501\u8868\u5f71\u54cd\n    \uff083\uff09\u5982\u679c8.0\u7248\u672c\uff0c\u53ef\u4ee5\u4e0d\u9002\u7528pt\u5de5\u5177\uff0c8.0\u4e4b\u524d\u9700\u8981\u501f\u52a9\u4ee5\u4e0a\u5de5\u5177\n<\/code><\/pre>\n
2\u3001DCL<\/h3>\n
\u6570\u636e\u63a7\u5236\u8bed\u8a00<\/strong><\/p>\n
grant   \u6388\u6743\nrevoke  \u56de\u6536\u6743\u9650<\/code><\/pre>\n
\u6743\u9650\u7ba1\u7406\u7684\u64cd\u4f5c\uff1a<\/h4>\n
\u8bed\u6cd5\uff1a<\/h5>\n
8.0\u4e4b\u524d\uff1a\n    grant \u6743\u9650 on \u5bf9\u8c61 to \u7528\u6237 identified by '\u5bc6\u7801';\n    grant \u6743\u96501,\u6743\u96502,\u6743\u96503... on \u5bf9\u8c61 to \u7528\u6237 identified by '\u5bc6\u7801';\n8.0\u4e4b\u540e\uff1a\n    create user \u7528\u6237 identified by '\u5bc6\u7801';\n    grant \u6743\u9650 on \u5bf9\u8c61 to \u7528\u6237;\n    grant \u6743\u96501,\u6743\u96502,\u6743\u96503... on \u5bf9\u8c61 to \u7528\u6237;<\/code><\/pre>\n
\u6743\u9650\uff1a<\/h5>\n
ALL:SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE\nALL                         #\u4ee5\u4e0a\u6240\u6709\u6743\u9650\uff0c\u4e00\u822c\u662f\u7ba1\u7406\u5458\u624d\u62e5\u6709\u7684\n\u6743\u96501,\u6743\u96502,\u6743\u96503...         #\u666e\u901a\u7528\u6237\ngrant option                #\u8d85\u7ea7\u7ba1\u7406\u5458\uff0c\u7ed9\u522b\u7684\u7528\u6237\u6388\u6743\n    grant \u6743\u96501,\u6743\u96502,\u6743\u96503... on \u5bf9\u8c61 to \u7528\u6237 with grant option;<\/code><\/pre>\n
\u6ce8\u610f\uff1a\u4e0d\u8981\u968f\u610f\u6388\u6743grant option\uff0c\u4e0d\u7136\u8be5\u7528\u6237\u53ef\u4ee5\u5728\u670d\u52a1\u5668\u4e2d\u4e3a\u6240\u6b32\u4e3a\uff0c\u5305\u62ec\u5220\u9664\u81ea\u5e26\u7684\u8d85\u7ea7\u7ba1\u7406\u5458\uff08root@localhost\uff09<\/strong><\/p>\n
\u5bf9\u8c61\uff1a \u5e93\u3001\u8868\uff08\u4f5c\u7528\u8303\u56f4\uff09<\/h5>\n
*.*                  #\u6240\u6709\u5e93\u6240\u6709\u8868\uff0c\u4e00\u822c\u662f\u5bf9\u7ba1\u7406\u5458\nwordpress.*          #wordpress\u5e93\u4e0b\u6240\u6709\u8868\uff0c\u5f00\u53d1\u548c\u5e94\u7528\u7528\u6237\uff0c\nwordpress.t1         #wordpress\u5e93\u4e0bt1\u8868<\/code><\/pre>\n
MySQL\u6388\u6743\u8868\uff1a<\/h5>\n
user        #\u7528\u6237\u5bf9mysql\u670d\u52a1\u7684\u6743\u9650\ndb          #\u7528\u6237\u5bf9\u67d0\u4e2a\u5e93\u7684\u6743\u9650\ntables_priv     #\u7528\u6237\u5bf9\u67d0\u4e2a\u8868\u7684\u6743\u9650\ncolumns_priv    #\u7528\u6237\u5bf9\u67d0\u5217\u7684\u6743\u9650\n\n\u67e5\u8be2\u6240\u6709\u7528\u6237\u5bf9mysql\u670d\u52a1\u7684\u6743\u9650\nselect * from mysql.user\\G\n\n\u67e5\u8be2\u6240\u6709\u7528\u6237\u5bf9\u5e93\u7684\u6743\u9650\nselect * from mysql.db\\G\n\n\u67e5\u8be2\u6240\u6709\u7528\u6237\u5bf9\u67d0\u4e2a\u8868\u7684\u6743\u9650\nselect * from mysql.tables_priv\\G\n\n\u67e5\u8be2\u6240\u6709\u7528\u6237\u5bf9\u67d0\u5217\u7684\u6743\u9650\nselect * from mysql.columns_priv\\G<\/code><\/pre>\n
\u7ec3\u4e60<\/h5>\n
\u9700\u6c421\uff1a\u521b\u5efa\u7ba1\u7406\u5458\u7528\u6237\uff0cwindows\u673a\u5668\u7684navicat\u767b\u5f55\u5230linux\u4e2d\u7684MySQL<\/p>\n
mysql> grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;\n\n\u67e5\u8be2\u521b\u5efa\u7684\u7528\u6237\uff1a\nmysql> select user,host,authentication_string from mysql.user;\n\n\u67e5\u8be2\u67d0\u4e2a\u7528\u6237\u7684\u6743\u9650\uff1a\nmysql> show grants for root@'10.0.0.%';\n+--------------------------------------------------------------------+\n| Grants for root@10.0.0.%                                           |\n+--------------------------------------------------------------------+\n| GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' WITH GRANT OPTION |\n+--------------------------------------------------------------------+\n\n\u67e5\u8be2\u6240\u6709\u7528\u6237\u5bf9mysql\u670d\u52a1\u7684\u6743\u9650\nselect * from mysql.user\\G\n\n\u67e5\u8be2\u6240\u6709\u7528\u6237\u5bf9\u5e93\u7684\u6743\u9650\nselect * from mysql.db\\G\n\n\u67e5\u8be2\u6240\u6709\u7528\u6237\u5bf9\u67d0\u4e2a\u8868\u7684\u6743\u9650\nselect * from mysql.tables_priv\\G\n\n\u67e5\u8be2\u6240\u6709\u7528\u6237\u5bf9\u67d0\u5217\u7684\u6743\u9650\nselect * from mysql.columns_priv\\G\n<\/code><\/pre>\n
\u9700\u6c422\uff1a\u521b\u5efa\u4e00\u4e2a\u5e94\u7528\u7528\u6237app\u7528\u6237\uff0c\u80fd\u4ecewindows\u4e0a\u767b\u5f55mysql\uff0c\u80fd\u591f\u5bf9app\u5e93\u4e0b\u6240\u6709\u5bf9\u8c61\u8fdb\u884ccreate\uff0cselect\uff0cupdate\uff0cdelete\uff0cinsert\u64cd\u4f5c<\/p>\n
mysql> grant create,update,select,insert,delete on app.* to app@'10.0.0.%' identified by '123';\n\nmysql> show grants for app@'10.0.0.%';\n+-----------------------------------------------------------------------------+\n| Grants for app@10.0.0.%                                                     |\n+-----------------------------------------------------------------------------+\n| GRANT USAGE ON *.* TO 'app'@'10.0.0.%'                                      |\n| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `app`.* TO 'app'@'10.0.0.%' |\n+-----------------------------------------------------------------------------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n
\u56de\u6536\u6743\u9650<\/h5>\n
linux:\nchmod -R 644 \/data  ----> chmod -R 755 \/data\n\nMySQL:\nMySQL\u4e2d\u4e0d\u80fd\u901a\u8fc7\u91cd\u590d\u6388\u6743\uff0c\u4fee\u6539\u6743\u9650\uff0c\u53ea\u80fd\u901a\u8fc7\u56de\u6536\u6743\u9650\u7684\u65b9\u5f0f\u8fdb\u884c\u4fee\u6539\n\n\u56de\u6536'app'@'10.0.0.%'\u5bf9app\u5e93\u7684create\u6743\u9650\nrevoke create on app.* from 'app'@'10.0.0.%';\n\n\u6dfb\u52a0'app'@'10.0.0.%'\u5bf9app\u5e93\u7684create\u6743\u9650\ngrant create on app.* to app@'10.0.0.%';\n<\/code><\/pre>\n
3\u3001DML<\/h3>\n
\u6570\u636e\u64cd\u4f5c\u8bed\u8a00<\/strong><\/p>\n
\u4f5c\u7528\uff1a\u5bf9\u8868\u4e2d\u7684\u6570\u636e\u8fdb\u884c\u589e\u3001\u5220\u3001\u6539<\/p>\n
insert<\/h4>\n
--- \u6700\u6807\u51c6\u7684insert\u8bed\u53e5\nmysql> desc stu;     #\u5148\u770b\u770b\u6709\u4ec0\u4e48\u5217\nmysql> insert into stu(id,sname,sage,sgender,sfz,intime)\n    -> values\n    -> (1,'zs',18,'m','123456',now());\nmysql> select * from stu;                                                       \n+----+-------+------+---------+--------+---------------------+\n| id | sname | sage | sgender | sfz    | intime              |\n+----+-------+------+---------+--------+---------------------+\n|  1 | zs    |   18 | m       | 123456 | 2021-02-28 15:27:03 |\n+----+-------+------+---------+--------+---------------------+\n\n--- \u7701\u4e8b\u7684\u5199\u6cd5\nmysql> insert into stu \n    -> values\n    -> (2,'ls',18,'m','1234567',now());\nmysql> select * from stu;                                                       \n+----+-------+------+---------+---------+---------------------+\n| id | sname | sage | sgender | sfz     | intime              |\n+----+-------+------+---------+---------+---------------------+\n|  1 | zs    |   18 | m       | 123456  | 2021-02-28 15:27:03 |\n|  2 | ls    |   18 | m       | 1234567 | 2021-02-28 15:30:45 |\n+----+-------+------+---------+---------+---------------------+\n\n--- \u9488\u5bf9\u6027\u7684\u5f55\u5165\u6570\u636e\nmysql> insert into stu(sname,sfz)\n    -> values \n    -> ('w5','1233232');\nmysql> select * from stu;\n+----+-------+------+---------+---------+---------------------+\n| id | sname | sage | sgender | sfz     | intime              |\n+----+-------+------+---------+---------+---------------------+\n|  1 | zs    |   18 | m       | 123456  | 2021-02-28 15:27:03 |\n|  2 | ls    |   18 | m       | 1234567 | 2021-02-28 15:30:45 |\n|  3 | w5    |    0 | n       | 1233232 | 2021-02-28 15:32:24 |\n+----+-------+------+---------+---------+---------------------+\n\n--- \u540c\u65f6\u5f55\u5165\u591a\u884c\u6570\u636e\nmysql> insert into stu(sname,sfz)\n    -> values\n    -> ('ll','34314314'),\n    -> ('kk','3515315'),\n    -> ('jj','654364365');\nmysql> select * from stu;\n+----+-------+------+---------+-----------+---------------------+\n| id | sname | sage | sgender | sfz       | intime              |\n+----+-------+------+---------+-----------+---------------------+\n|  1 | zs    |   18 | m       | 123456    | 2021-02-28 15:27:03 |\n|  2 | ls    |   18 | m       | 1234567   | 2021-02-28 15:30:45 |\n|  3 | w5    |    0 | n       | 1233232   | 2021-02-28 15:32:24 |\n|  4 | ll    |    0 | n       | 34314314  | 2021-02-28 15:34:37 |\n|  5 | kk    |    0 | n       | 3515315   | 2021-02-28 15:34:37 |\n|  6 | jj    |    0 | n       | 654364365 | 2021-02-28 15:34:37 |\n+----+-------+------+---------+-----------+---------------------+\n\ninsert into \u5e93.\u8868 select concat(user,"@",host) from mysql.user;<\/code><\/pre>\n
update<\/h4>\n
mysql> update stu set sname='zhaosi' where id=1;\n\nmysql> select * from stu;\n+----+--------+------+---------+-----------+---------------------+\n| id | sname  | sage | sgender | sfz       | intime              |\n+----+--------+------+---------+-----------+---------------------+\n|  1 | zhaosi |   18 | m       | 123456    | 2021-02-28 15:27:03 |\n|  2 | ls     |   18 | m       | 1234567   | 2021-02-28 15:30:45 |\n|  3 | w5     |    0 | n       | 1233232   | 2021-02-28 15:32:24 |\n|  4 | ll     |    0 | n       | 34314314  | 2021-02-28 15:34:37 |\n|  5 | kk     |    0 | n       | 3515315   | 2021-02-28 15:34:37 |\n|  6 | jj     |    0 | n       | 654364365 | 2021-02-28 15:34:37 |\n+----+--------+------+---------+-----------+---------------------+\n\n\u6ce8\u610f\uff1aupdate\u8bed\u53e5\u5fc5\u987b\u8981\u52a0where\u3002<\/code><\/pre>\n
delete<\/h4>\n
mysql> delete from stu where id=6;\n\nmysql> select * from stu;\n+----+--------+------+---------+----------+---------------------+\n| id | sname  | sage | sgender | sfz      | intime              |\n+----+--------+------+---------+----------+---------------------+\n|  1 | zhaosi |   18 | m       | 123456   | 2021-02-28 15:27:03 |\n|  2 | ls     |   18 | m       | 1234567  | 2021-02-28 15:30:45 |\n|  3 | w5     |    0 | n       | 1233232  | 2021-02-28 15:32:24 |\n|  4 | ll     |    0 | n       | 34314314 | 2021-02-28 15:34:37 |\n|  5 | kk     |    0 | n       | 3515315  | 2021-02-28 15:34:37 |\n+----+--------+------+---------+----------+---------------------+\n<\/code><\/pre>\n
\u6269\u5c55<\/strong><\/p>\n
1\u3001\u4f2a\u5220\u9664\n\u7528update\u6765\u66ff\u4ee3delete\uff0c\u6700\u7ec8\u4fdd\u8bc1\u4e1a\u52a1\u4e2d\u67e5\u4e0d\u5230\uff08select\uff09\u5373\u53ef\n  \u5220\u9664id\u4e3a1\n    \u539f\u64cd\u4f5c\uff1a\n        mysql> delete from stu where id=1;\n\n    \u4f2a\u5220\u9664\uff1a\n        1.\u6dfb\u52a0\u72b6\u6001\u5217\n        ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;\n        SELECT * FROM stu;\n        2. UPDATE \u66ff\u4ee3 DELETE\n        UPDATE stu SET state=0 WHERE id=6;\n        3. \u4e1a\u52a1\u8bed\u53e5\u67e5\u8be2\n        SELECT * FROM stu WHERE state=1;\n\n2\u3001delete from stu \uff0cdrop table stu\uff0ctruncate table stu\u7684\u533a\u522b\n    1.\u90fd\u53ef\u4ee5\u5220\u9664\u5168\u8868\n\n    2.\u533a\u522b\n    delete\n        \u903b\u8f91\u4e0a\uff0c\u9010\u884c\u5220\u9664\u3002\u6570\u636e\u884c\u591a\uff0c\u64cd\u4f5c\u6162\n        \u5e76\u6ca1\u6709\u771f\u6b63\u4ece\u78c1\u76d8\u5220\u9664\uff0c\u53ea\u662f\u5728\u5b58\u50a8\u5c42\u9762\u6253\u6807\u8bb0\uff0c\u78c1\u76d8\u7a7a\u95f4\u4e0d\u7acb\u5373\u91ca\u653e\u3002HWM\u9ad8\u6c34\u4f4d\u7ebf\uff08\uff09\u4e0d\u4f1a\u964d\u4f4e\u3002\uff08\u81ea\u589e\u5217\u7ee7\u7eed\uff09\n\n    drop\n    \u5c06\u8868\u7ed3\u6784\uff08\u5143\u6570\u636e\uff09\u548c\u6570\u636e\u884c\u7269\u7406\u5c42\u6b21\u5220\u9664\n\n    truncate\n    \u6e05\u7a7a\u8868\u6bb5\u4e2d\u7684\u6240\u6709\u6570\u636e\u9875\u3002\u7269\u7406\u5c42\u6b21\u5220\u9664\u5168\u8868\u6570\u636e\u78c1\u76d8\u7a7a\u95f4\u7acb\u5373\u91ca\u653e\uff0cHWM\u9ad8\u6c34\u4f4d\u4f1a\u964d\u4f4e\u3002\uff08\u81ea\u589e\u5217\u91cd\u65b0\u5f00\u59cb\uff09\n\n#delete\uff0cdrop\uff0ctruncate\u5982\u679c\u4e0d\u5c0f\u5fc3\u5220\u9664\u4e86\uff0c\u4ed6\u4eec\u90fd\u53ef\u4ee5\u6062\u590d\u5417\uff1f\n    \u53ef\u4ee5\n    \u5e38\u89c4\u65b9\u6cd5\uff1a\n    \u90fd\u53ef\u4ee5\u901a\u8fc7 \u5907\u4efd+\u65e5\u5fd7\uff0c\u6062\u590d\u6570\u636e\u3002\n\n    \u7075\u6d3b\u529e\u6cd5\n    delete\u53ef\u4ee5\u901a\u8fc7\uff0c\u7ffb\u8f6c\u65e5\u5fd7\uff08binlog\uff09\n    \u4e09\u79cd\u5220\u9664\u6570\u636e\u60c5\u51b5\uff0c\u4e5f\u53ef\u4ee5\u901a\u8fc7\u300a\u5ef6\u65f6\u4ece\u5e93\u8fdb\u884c\u6062\u590d\u300b\n<\/code><\/pre>\n
4\u3001DQL<\/h3>\n
\u6570\u636e\u67e5\u8be2\u8bed\u8a00<\/strong><\/p>\n
select<\/h4>\n
\u4f5c\u7528\uff1a\u83b7\u53d6\u8868\u4e2d\u7684\u6570\u636e\u884c<\/p>\n
1\u3001select \u5355\u72ec\u4f7f\u7528\uff08MySQL\u72ec\u5bb6\uff09<\/h5>\n
    1.\u914d\u5408\u5185\u7f6e\u51fd\u6570\u4f7f\u7528\n        mysql> select now();     #\u67e5\u770b\u5f53\u524d\u65f6\u95f4\n        mysql> select database();    #\u67e5\u770b\u5f53\u524d\u6240\u5728\u5e93\n        mysql> select concat("hello word!");   #\u547d\u4ee4\u62fc\u63a5\uff0c\u663e\u793a\u67d0\u5b57\u7b26\u4e32\n        +-----------------------+\n        | concat("hello word!") |\n        +-----------------------+\n        | hello word!           |\n        +-----------------------+\n        mysql> select concat(user,"@",host) from mysql.user;\n        +-------------------------+\n        | concat(user,"@",host)   |\n        +-------------------------+\n        | root@10.0.0.1           |\n        | mysql.session@localhost |\n        | mysql.sys@localhost     |\n        | root@localhost          |\n        +-------------------------+\n        mysql> select user();    #\u67e5\u770b\u5f53\u524d\u767b\u5f55\u7528\u6237\n\n    2.\u8ba1\u7b97\n        mysql> select 10*100;    #\u8fdb\u884c\u8ba1\u7b97\n        +--------+\n        | 10*100 |\n        +--------+\n        |   1000 |\n        +--------+\n\n    3.\u67e5\u8be2\u6570\u636e\u5e93\u7684\u53c2\u6570\n        mysql> select @@port;    #\u67e5\u8be2\u5f53\u524d\u7aef\u53e3\n        mysql> select @@datadir; #\u67e5\u770b\u6570\u636e\u5b58\u50a8\u4f4d\u7f6e\n\n        show variables;     ##\u67e5\u770b\u6240\u6709\u53c2\u6570\n        mysql> show variables like '%trx%';    #like \u6a21\u7cca\u67e5\u8be2\n<\/code><\/pre>\n
2\u3001select \u6807\u51c6\u7528\u6cd5\uff08\u914d\u5408\u5176\u4ed6\u5b50\u53e5\u4f7f\u7528\uff09<\/h5>\n
    \u5355\u8868\n    \u524d\u63d0\uff1a\n    select\n        1.from \u88681\uff0c\u88682\uff0c\u3002\u3002\u3002\n        2.where \u8fc7\u6ee4\u6761\u4ef61\uff0c\u8fc7\u6ee4\u6761\u4ef62...\n        3.group by  \u6761\u4ef6\u52171 \u6761\u4ef6\u52172\u3002\u3002\u3002\u5206\u7ec4\u5b57\u6bb5\n        4.select_list \u5217\u540d\n        5.having    \u8fc7\u6ee4\u6761\u4ef61 \u8fc7\u6ee4\u6761\u4ef62\u3002\u3002\u3002\n        6.order by  \u6761\u4ef6\u52171 \u6761\u4ef6\u52172\u3002\u3002\u3002\u6392\u5e8f\u5b57\u6bb5\n        7.limit \u5206\u9875\u9650\u5236<\/code><\/pre>\n
\"\"<\/div><\/p>\n
\u4f7f\u7528\u65b9\u6cd5<\/strong><\/p>\n
\u51c6\u5907\u5b66\u4e60\u73af\u5883[root@Centos7 ~]# mysql -p < world.sql \u5bfc\u5165world\u5e93<\/p>\n
[root@Centos7 ~]# mysql -p < world.sql   #\u5bfc\u5165world\u5e93\nworld\u5e93\u5e38\u89c1\u5355\u8bcd\n    world            ===>\u4e16\u754c\n    city             ===>\u57ce\u5e02\n    country          ===>\u56fd\u5bb6\n    countrylanguage  ===>\u56fd\u5bb6\u8bed\u8a00\n\n    city:\u57ce\u5e02\u8868\n    DESC city;\n    ID :         \u57ce\u5e02ID\n    NAME :       \u57ce\u5e02\u540d\n    CountryCode: \u56fd\u5bb6\u4ee3\u7801\uff0c\u6bd4\u5982\u4e2d\u56fdCHN \u7f8e\u56fdUSA\n    District :   \u7701\u4efd\n    Population : \u4eba\u53e3\u6570\n<\/code><\/pre>\n
1\uff09select\u914d\u5408from\u5b50\u53e5\u4f7f\u7528<\/strong><\/h6>\n
select\u914d\u5408from\u5b50\u53e5\u4f7f\u7528\n    \u8bed\u6cd5\uff1a\n        select \u5217 from \u8868;\n    \u4f8b\u5b50\uff1a\n    #\u67e5\u8be2\u8868\u4e2d\u6240\u6709\u5217\u6240\u6709\u884c\uff0c*\u8c28\u7528\uff01\n    select * from city;\n    #\u67e5\u8be2\u90e8\u5206\u5217\u503c\n    select name,population from city;       <\/code><\/pre>\n
2\uff09select+from+where\u914d\u5408\u4f7f\u7528<\/strong><\/h6>\n
select+from+where\u914d\u5408\u4f7f\u7528\n    where\u914d\u5408\u6bd4\u8f83\u5224\u65ad\u7b26=\uff0c<,>,>=,<=,!=\n    \u4f8b\u5b50\uff1a\n    #\u67e5\u8be2\u5c5e\u4e8e\u4e2d\u56fd\u7684\u6240\u6709\u57ce\u5e02\u4fe1\u606f\n    mysql> select * from world.city where countrycode='CHN';   \n    #\u67e5\u8be2\u4eba\u53e3\u5c0f\u4e8e1000\u7684\u6240\u6709\u57ce\u5e02\u4fe1\u606f\n    mysql> select * from world.city where population < 1000;  <\/code><\/pre>\n
3\uff09select+from+where+like\u914d\u5408\u4f7f\u7528\uff0c\u6a21\u7cca\u67e5\u8be2\uff0c\u9488\u5bf9\u5b57\u7b26\u4e32<\/strong><\/h6>\n
    select+from+where+like\u914d\u5408\u4f7f\u7528\uff0c\u6a21\u7cca\u67e5\u8be2\n    #\u67e5\u8be2city\u4e2d\uff0c\u56fd\u5bb6\u4ee3\u53f7\u662fCH\u5f00\u5934\u7684\u57ce\u5e02\u4fe1\u606f\n    mysql> select * from world.city where countrycode like 'CH%';  \n    mysql> select * from world.city where countrycode like 'CH_';\n        %:\u591a\u4e2a\u4efb\u610f\u5b57\u7b26\n        _:\u4e00\u4e2a\u4efb\u610f\u5b57\u7b26\n\n    \u6ce8\uff1alike\u8bed\u53e5\u5728\u4f7f\u7528\u65f6\uff0c\u5207\u8bb0\u4e0d\u8981\u51fa\u73b0\u524d\u9762\u5e26%\u7684\u6a21\u7cca\u67e5\u8be2\uff0c\u539f\u56e0\uff1a\u4e0d\u8d70\u7d22\u5f15\u3002\n    #\u53ea\u8981\u524d\u9762\u6709%\u7684\u6a21\u7cca\u67e5\u8be2\uff0c\u5c31\u4e0d\u4f1a\u8d70\u7d22\u5f15\n    select * from world.city where countrycode like '%CH%';   <\/code><\/pre>\n
4\uff09select+from+where+\u903b\u8f91\u8fde\u63a5\u7b26\uff08and or\uff09<\/strong><\/h6>\n
select+from+where+\u903b\u8f91\u8fde\u63a5\u7b26\uff08and or\uff09\n#\u4f8b\u5b50\uff1a\u67e5\u8be2\u4e2d\u56fd\u57ce\u5e02\u4eba\u53e3\u8d85\u8fc7500W\u7684\u57ce\u5e02\n    mysql> select * from world.city\n    -> where countrycode='CHN' and population>5000000;\n\n#\u67e5\u8be2\u4e2d\u56fd\u6216\u7f8e\u56fd\u7684\u57ce\u5e02\u4fe1\u606f\nmysql> select * from world.city where countrycode='CHN' or countrycode='USA';\n\n#\u67e5\u8be2\u4e2d\u56fd\u548c\u7f8e\u56fd\u7684\u4fe1\u606f\uff0c\u5e76\u4e14\u4eba\u53e3\u6570\u91cf\u8d85\u8fc7500W\u7684\u57ce\u5e02;\nmysql> select * from world.city where countrycode in ('CHN','USA') and population>5000000;\n<\/code><\/pre>\n
5\uff09where\u914d\u5408between and\uff0c\u53d6\u4e00\u4e2a\u8303\u56f4<\/strong><\/h6>\n
where\u914d\u5408between and\n#\u4f5c\u7528\uff1a\u67e5\u8be2\u6570\u503c\u7684\u4e00\u4e2a\u8303\u56f4\n#\u67e5\u8be2\u4eba\u53e3\u5728100W\u548c\u4e24\u767e\u4e07\u4e4b\u524d\u7684\u57ce\u5e02\u4fe1\u606f\nmysql> select * from world.city where population between 1000000 and 2000000;\nmysql> select * from world.city where population>=1000000 and population<=2000000;\n<\/code><\/pre>\n
3\u3001select+from+where+group by\u805a\u5408\u51fd\u6570<\/strong><\/h5>\n
#\u4f5c\u7528\uff1a\u5bf9\u4e00\u5f20\u8868\uff0c\u6309\u7167\u4e0d\u540c\u6570\u636e\u7279\u70b9\uff0c\u9700\u8981\u5206\u7ec4\u8ba1\u7b97\u7edf\u8ba1\u662f\uff0c\u4f1a\u4f7f\u7528group by+\u805a\u5408\u51fd\u6570\ngroup by \u914d\u5408\u805a\u5408\u51fd\u6570(max(),min(),avg(),count(),sum(),group_concat())\u4f7f\u7528\n\u805a\u5408\u51fd\u6570\uff1a\nmax()           #\u6700\u5927\u503c\nmin()           #\u6700\u5c0f\u503c\navg()           #\u5e73\u5747\u503c\ncount()         #\u7edf\u8ba1\u4e2a\u6570\nsum()           #\u6c42\u548c\ngroup_concat()  #\u5217\u8f6c\u884c\n\n#\u8bf4\u660e\uff1a\u78b0\u5230group_by\u5fc5\u7136\u4f1a\u6709\u805a\u5408\u51fd\u6570\n\n\u8fd0\u884c\u8fc7\u7a0b\uff1a\n    \u63d0\u53d6\u6570\u636e--\u300b\u6392\u5e8f--\u300b\u53bb\u91cd--\u300b\u7edf\u8ba1<\/code><\/pre>\n
\"\"<\/div><\/p>\n
# \u7edf\u8ba1city\u4e2d\uff0c\u6bcf\u4e2a\u56fd\u5bb6\u7684\u57ce\u5e02\u4e2a\u6570\nselect countrycode,count(id) from world.city group by countrycode;\n\n# \u7edf\u8ba1\u4e2d\u56fd\u6bcf\u4e2a\u7701\u7684\u57ce\u5e02\u4e2a\u6570\nmysql> select district,count(id) from world.city where countrycode='CHN' group by district;\n\n#\u7edf\u8ba1\u6bcf\u4e2a\u56fd\u5bb6\u7684\u603b\u4eba\u53e3\nmysql> select countrycode,sum(population) from world.city group by countrycode;\n\n#\u7edf\u8ba1\u4e2d\u56fd\uff0c\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\nmysql> select district,sum(population) from world.city where countrycode='CHN' group by district;\n\n#\u7edf\u8ba1\u4e2d\u56fd\uff0c\u6bcf\u4e2a\u7701\u603b\u4eba\u53e3\uff0c\u57ce\u5e02\u4e2a\u6570\uff0c\u57ce\u5e02\u540d\u5217\u8868\nmysql> select district,sum(population),count(id),group_concat(name) from world.city where countrycode='CHN' group by district;\n<\/code><\/pre>\n
4\u3001select+group by+having\uff08\u540e\u8fc7\u6ee4\uff09<\/h5>\n
#\u4f5c\u7528\uff1a\u4e0ewhere\u4f5c\u7528\u76f8\u4f3c\uff0c\u90fd\u662f\u8fc7\u6ee4\u4f5c\u7528\uff0c\u4f46having\u662f\u540e\u8fc7\u6ee4 where|group by|having\n\n#\u7edf\u8ba1\u4e2d\u56fd\uff0c\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u53ea\u6253\u5370\u603b\u4eba\u53e3\u6570\u5927\u4e8e500W\nmysql> select district,sum(population) from world.city where countrycode='CHN' group by district having sum(population)>5000000;<\/code><\/pre>\n
5\u3001order by \u6392\u5e8f<\/h5>\n
#\u4f5c\u7528\uff1a\u4ece\u5c0f\u5230\u5927\u6392\u5e8f \u9ed8\u8ba4\u7531\u5c0f\u5230\u5927\u6dfb\u52a0desc\u540e\u53d8\u6210\u53c8\u5927\u5230\u5c0f\n#\u7edf\u8ba1\u4e2d\u56fd\uff0c\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u53ea\u6253\u5370\u603b\u4eba\u53e3\u6570\u5927\u4e8e500W\uff0c\u5e76\u4e14\u6309\u7167\u603b\u4eba\u53e3\u4ece\u5927\u5230\u5c0f\u6392\u5e8f\u8f93\u51fa\nselect district,sum(population) from world.city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc;\n\n\u9ed8\u8ba4\u5347\u5e8f\uff1aasc\n    \u964d\u5e8f\uff1adesc<\/code><\/pre>\n
6\u3001limit \u5206\u9875<\/h5>\n
#\u4f5c\u7528\uff1a\u5206\u9875\u8f93\u51fa\n#\u7edf\u8ba1\u4e2d\u56fd\uff0c\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u53ea\u6253\u5370\u603b\u4eba\u53e3\u6570\u5927\u4e8e500W\uff0c\u5e76\u4e14\u6309\u7167\u603b\u4eba\u53e3\u4ece\u5927\u5230\u5c0f\u6392\u5e8f\u8f93\u51fa,\u53ea\u770b\u524d\u4e94\u540d\nmysql> select district,sum(population) from world.city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 5;\n\n#\u7edf\u8ba1\u4e2d\u56fd\uff0c\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u53ea\u6253\u5370\u603b\u4eba\u53e3\u6570\u5927\u4e8e500W\uff0c\u5e76\u4e14\u6309\u7167\u603b\u4eba\u53e3\u4ece\u5927\u5230\u5c0f\u6392\u5e8f\u8f93\u51fa,\u770b6\u523010\u540d\nmysql> select district,sum(population) from world.city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 5,5;\nmysql> select district,sum(population) from world.city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 5 offset 5;     \n\n##\u7edf\u8ba1\u4e2d\u56fd\uff0c\u6bcf\u4e2a\u7701\u7684\u603b\u4eba\u53e3\uff0c\u53ea\u6253\u5370\u603b\u4eba\u53e3\u6570\u5927\u4e8e500W\uff0c\u5e76\u4e14\u6309\u7167\u603b\u4eba\u53e3\u4ece\u5927\u5230\u5c0f\u6392\u5e8f\u8f93\u51fa,\u770b3\u52305\u540d\nmysql> select district,sum(population) from world.city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 2,3;\n\nmysql> select district,sum(population) from world.city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 3 offset 2;\n<\/code><\/pre>\n
7\u3001distinct\uff1a\u53bb\u91cd\u590d<\/h5>\n
#\u53bb\u91cd\u590d\nmysql> select countrycode from world.city;\nmysql> select distinct(countrycode) from world.city;<\/code><\/pre>\n
8\u3001\u8054\u5408\u67e5\u8be2 – union all<\/h5>\n
#\u67e5\u8be2\u4e2d\u56fd\u6216\u7f8e\u56fd\u7684\u57ce\u5e02\u4fe1\u606f\nmysql> select * from world.city where countrycode in ('CHN','USA');\n\nmysql> select * from world.city where countrycode='CHN' union all select * from world.city where countrycode='USA';\n    #\u5148\u67e5\u4e2d\u56fd\u7684\u518d\u67e5\u7f8e\u56fd\u7684\u3002\n\n\u8bf4\u660e:\u4e00\u822c\u60c5\u51b5\u4e0b,\u6211\u4eec\u4f1a\u5c06 IN \u6216\u8005 OR \u8bed\u53e5 \u6539\u5199\u6210 UNION ALL,\u6765\u63d0\u9ad8\u6027\u80fd\nUNION     \u805a\u5408\u4e24\u4e2a\u7ed3\u679c\u96c6\uff0c\u4f1a\u81ea\u52a8\u53bb\u91cd\u590d\nUNION ALL \u805a\u5408\u4e24\u4e2a\u7ed3\u679c\u96c6\uff0c\u4e0d\u53bb\u91cd\u590d<\/code><\/pre>\n
9.join\u591a\u8868\u8fde\u63a5\u67e5\u8be2<\/h5>\n
1\uff09\u6848\u4f8b\u51c6\u5907<\/h6>\n
\u6309\u9700\u6c42\u521b\u5efa\u4e00\u4e0b\u8868\u7ed3\u6784<\/p>\n
use school\nstudent \uff1a\u5b66\u751f\u8868\nsno\uff1a    \u5b66\u53f7\nsname\uff1a\u5b66\u751f\u59d3\u540d\nsage\uff1a \u5b66\u751f\u5e74\u9f84\nssex\uff1a \u5b66\u751f\u6027\u522b\n\nteacher \uff1a\u6559\u5e08\u8868\ntno\uff1a     \u6559\u5e08\u7f16\u53f7\ntname\uff1a\u6559\u5e08\u540d\u5b57\n\ncourse \uff1a\u8bfe\u7a0b\u8868\ncno\uff1a  \u8bfe\u7a0b\u7f16\u53f7\ncname\uff1a\u8bfe\u7a0b\u540d\u5b57\ntno\uff1a  \u6559\u5e08\u7f16\u53f7\n\nscore  \uff1a\u6210\u7ee9\u8868\nsno\uff1a  \u5b66\u53f7\ncno\uff1a  \u8bfe\u7a0b\u7f16\u53f7\nscore\uff1a\u6210\u7ee9\n\n-- \u9879\u76ee\u6784\u5efa\ndrop database school;\nCREATE DATABASE school CHARSET utf8mb4;\nUSE school\n\nCREATE TABLE student(\nsno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '\u5b66\u53f7',\nsname VARCHAR(20) NOT NULL COMMENT '\u59d3\u540d',\nsage TINYINT UNSIGNED  NOT NULL COMMENT '\u5e74\u9f84',\nssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '\u6027\u522b'\n)ENGINE=INNODB CHARSET=utf8;\n\nCREATE TABLE course(\ncno INT NOT NULL PRIMARY KEY COMMENT '\u8bfe\u7a0b\u7f16\u53f7',\ncname VARCHAR(20) NOT NULL COMMENT '\u8bfe\u7a0b\u540d\u5b57',\ntno INT NOT NULL  COMMENT '\u6559\u5e08\u7f16\u53f7'\n)ENGINE=INNODB CHARSET utf8;\n\nalter TABLE sc (\nsno INT NOT NULL COMMENT '\u5b66\u53f7',\ncno INT NOT NULL COMMENT '\u8bfe\u7a0b\u7f16\u53f7',\nscore INT  NOT NULL DEFAULT 0 COMMENT '\u6210\u7ee9'\n)ENGINE=INNODB CHARSET=utf8;\n\nCREATE TABLE teacher(\ntno INT NOT NULL PRIMARY KEY COMMENT '\u6559\u5e08\u7f16\u53f7',\ntname VARCHAR(20) NOT NULL COMMENT '\u6559\u5e08\u540d\u5b57'\n)ENGINE=INNODB CHARSET utf8;\n\nINSERT INTO student(sno,sname,sage,ssex)\nVALUES (1,'zhang3',18,'m');\n\nINSERT INTO student(sno,sname,sage,ssex)\nVALUES\n(2,'zhang4',18,'m'),\n(3,'li4',18,'m'),\n(4,'wang5',19,'f');\n\nINSERT INTO student\nVALUES\n(5,'zh4',18,'m'),\n(6,'zhao4',18,'m'),\n(7,'ma6',19,'f');\n\nINSERT INTO student(sname,sage,ssex)\nVALUES\n('oldboy',20,'m'),\n('oldgirl',20,'f'),\n('oldp',25,'m');\n\nINSERT INTO teacher(tno,tname) VALUES\n(101,'oldboy'),\n(102,'hesw'),\n(103,'oldguo');\n\nDESC course;\nINSERT INTO course(cno,cname,tno)\nVALUES\n(1001,'linux',101),\n(1002,'python',102),\n(1003,'mysql',103);\n\nDESC sc;\nINSERT INTO sc(sno,cno,score)\nVALUES\n(1,1001,80),\n(1,1002,59),\n(2,1002,90),\n(2,1003,100),\n(3,1001,99),\n(3,1003,40),\n(4,1001,79),\n(4,1002,61),\n(4,1003,99),\n(5,1003,40),\n(6,1001,89),\n(6,1003,77),\n(7,1001,67),\n(7,1003,82),\n(8,1001,70),\n(9,1003,80),\n(10,1003,96);\n\nSELECT * FROM student;\nSELECT * FROM teacher;\nSELECT * FROM course;\nSELECT * FROM sc;<\/code><\/pre>\n
2)\u4ecb\u7ecd<\/h6>\n
\u5c06\u591a\u5f20\u8868\u5408\u6210\u4e00\u5f20\u5927\u8868<\/p>\n
\u67e5\u8be2\u5f20\u4e09\u7684\u5bb6\u5ead\u4f4f\u5740<\/p>\n
SELECT A.name,B.address FROM\nA JOIN  B\nON A.id=B.id\nWHERE A.name='zhangsan'<\/code><\/pre>\n
3)\u4f5c\u7528<\/h6>\n
1\u3001\u4e3a\u4ec0\u4e48\u8981\u4f7f\u7528\u591a\u8868\u8fde\u63a5\u67e5\u8be2\uff1f\n\u6211\u4eec\u7684\u67e5\u8be2\u9700\u6c42\uff0c\u9700\u8981\u7684\u6570\u636e\uff0c\u6765\u81ea\u4e8e\u591a\u5f20\u8868\uff0c\u5355\u5f20\u8868\u65e0\u6cd5\u6ee1\u8db3\u3002\n2\u3001\u7b80\u5355\u7406\u89e3\uff1a\n\u591a\u8868\u8fde\u63a5\u5b9e\u9645\u4e0a\u662f\u5c06\u591a\u5f20\u8868\u4e2d\uff0c\u6709\u5173\u8054\u7684\u90e8\u5206\u6570\u636e\uff0c\u5408\u5e76\u6210\u4e00\u5f20\u65b0\u8868\uff0c\u5728\u65b0\u8868\u4e2d\u53bb\u505a where\u3001group\u3001having\u3001order by\u3001limit<\/code><\/pre>\n
4\uff09\u591a\u8868\u8fde\u63a5\u67e5\u8be2\u7684\u7c7b\u578b<\/h6>\n
1.\u7b1b\u5361\u5c14\u4e58\u79ef\uff08\u4e0d\u5e38\u89c1\uff09<\/strong><\/p>\n
mysql> select * from teacher,course;\n+-----+--------+------+--------+-----+\n| tno | tname  | cno  | cname  | tno |\n+-----+--------+------+--------+-----+\n| 101 | oldboy | 1001 | linux  | 101 |\n| 102 | hesw   | 1001 | linux  | 101 |\n| 103 | oldguo | 1001 | linux  | 101 |\n| 101 | oldboy | 1002 | python | 102 |\n| 102 | hesw   | 1002 | python | 102 |\n| 103 | oldguo | 1002 | python | 102 |\n| 101 | oldboy | 1003 | mysql  | 103 |\n| 102 | hesw   | 1003 | mysql  | 103 |\n| 103 | oldguo | 1003 | mysql  | 103 |\n+-----+--------+------+--------+-----+\n9 rows in set (0.00 sec)\n\nmysql> select * from teacher join course;\n+-----+--------+------+--------+-----+\n| tno | tname  | cno  | cname  | tno |\n+-----+--------+------+--------+-----+\n| 101 | oldboy | 1001 | linux  | 101 |\n| 102 | hesw   | 1001 | linux  | 101 |\n| 103 | oldguo | 1001 | linux  | 101 |\n| 101 | oldboy | 1002 | python | 102 |\n| 102 | hesw   | 1002 | python | 102 |\n| 103 | oldguo | 1002 | python | 102 |\n| 101 | oldboy | 1003 | mysql  | 103 |\n| 102 | hesw   | 1003 | mysql  | 103 |\n| 103 | oldguo | 1003 | mysql  | 103 |\n+-----+--------+------+--------+-----+\n9 rows in set (0.00 sec)<\/code><\/pre>\n
2.\u5185\u8fde\u63a5\uff08\u5e94\u7528\u6700\u5e7f\u6cdb\uff09<\/strong><\/p>\n
select \u5217\u540d\u3002\u3002\u3002\nfrom A join B\non A.xx=B.yy\nmysql> select * from teacher join course on teacher.tno=course.tno;\n+-----+--------+------+--------+-----+\n| tno | tname  | cno  | cname  | tno |\n+-----+--------+------+--------+-----+\n| 101 | oldboy | 1001 | linux  | 101 |\n| 102 | hesw   | 1002 | python | 102 |\n| 103 | oldguo | 1003 | mysql  | 103 |\n+-----+--------+------+--------+-----+\n<\/code><\/pre>\n
3.\u5916\u8fde\u63a5<\/strong><\/p>\n
#\u4f5c\u7528\uff1a\u5f3a\u5236\u9a71\u52a8\u8868\n    \u9a71\u52a8\u8868\uff1a\u5728\u591a\u8868\u8fde\u63a5\u4e2d\uff0c\u627f\u5f53for\u5faa\u73af\u4e2d\u5916\u5c42\u5faa\u73af\u7684\u89d2\u8272\uff0c\u6b64\u65f6\uff0cMySQL\u4f1a\u62ff\u7740\u9a71\u52a8\u8868\u7684\u6bcf\u4e2a\u6ee1\u8db3\u6761\u4ef6\u7684\u5173\u8054\u5217\u7684\u503c\uff0c\u53bb\u4e00\u6b21\u627e\u5230for\u5faa\u73af\u5185\u5faa\u73af\u4e2d\u7684\u5173\u8054\u503c\u4e00\u4e00\u8fdb\u884c\u5224\u65ad\u548c\u5339\u914d\u3002(next loop)\u3002\n    \u5efa\u8bae\uff1a\u5c06\u7ed3\u679c\u96c6\u5c0f\u7684\u8868\u8bbe\u7f6e\u4e3a\u9a71\u52a8\u8868\u66f4\u52a0\u5408\u9002\uff0c\u53ef\u4ee5\u964d\u4f4enext loop\u7684\u6b21\u6570\u3002\u5bf9\u4e8e\u5185\u8fde\u63a5\u6765\u8bb2\uff0c\u6211\u4eec\u662f\u6ca1\u6cd5\u63a7\u5236\u9a71\u52a8\u8868\u662f\u8c01\uff0c\u5b8c\u5168\u7531\u4f18\u5316\u5668\u51b3\u5b9a\u3002\u5982\u679c\u9700\u8981\u4eba\u4e3a\u5e72\u9884\uff0c\u9700\u8981\u5c06\u5185\u8fde\u63a5\u5199\u6210\u5916\u8fde\u63a5\u7684\u65b9\u5f0f\u3002\n\n1\u3001left join     #\u5de6\u8fb9\u7684\u6240\u6709\u7684\u6570\u636e\u5217\u90fd\u8981\u53d6\u5230\uff0c\u53f3\u8868\u6ee1\u8db3\u6761\u4ef6\u7684\u6570\u636e\uff0c\u5f3a\u5236\u5de6\u8868\u4e3a\u9a71\u52a8\u8868\nmysql> select city.name,country.name,city.population from city left join country on city.countrycode = country.code and city.population<100 order by city.population desc;\n\n2\u3001right join    #\u53f3\u8fb9\u7684\u6240\u6709\u7684\u6570\u636e\u5217\u90fd\u8981\u53d6\u5230\uff0c\u5de6\u8868\u6ee1\u8db3\u6761\u4ef6\u7684\u6570\u636e\uff0c\u5f3a\u5236\u53f3\u8868\u4e3a\u9a71\u52a8\u8868\nmysql> select city.name,country.name,city.population from city right join country on city.countrycode = country.code and city.population<100;\n<\/code><\/pre>\n
5\uff09\u5168\u5916\u8fde\u63a5<\/h6>\n
mysql> select city.name,country.name,city.population from city left join country on city.countrycode = country.code and city.population<100 order by city.population desc\nunion\nmysql> select city.name,country.name,city.population from city right join country on city.countrycode = country.code and city.population<100;<\/code><\/pre>\n
6\uff09\u591a\u8868\u8fde\u63a5\u67e5\u8be2\u4f8b\u5b50\uff08\u5185\u8fde\u63a5\uff09<\/h6>\n
\u4f8b\u5b50\u4e00\uff1a\u67e5\u8be2wuhan\u8fd9\u4e2a\u57ce\u5e02\uff0c\u56fd\u5bb6\u540d\u3001\u57ce\u5e02\u540d\u3001\u57ce\u5e02\u4eba\u53e3\u6570\u3001\u56fd\u571f\u9762\u79ef\u3002<\/p>\n
1\u3001\u627e\u5173\u8054\u8868\uff1a\n    city\uff1a\n        \u57ce\u5e02\u540d\uff08city.name\uff09\n        \u57ce\u5e02\u4eba\u53e3\uff08city.population\uff09\n    country\uff1a\n        \u56fd\u5bb6\u540d\uff08country.name\uff09\n        \u56fd\u571f\u9762\u79ef\uff08country.surfacearea\uff09\n            from city join country\n\n2\u3001\u627e\u5173\u8054\u6761\u4ef6\n    mysql> desc city;\n    mysql> desc country;\n        #\u53d1\u73b0city.countrycode\u548ccountry.code\u6709\u5173\u8054\n            from city join country on city.countrycode=country.code\n\n3\u3001\u7f57\u5217\u5176\u4ed6\u67e5\u8be2\u6761\u4ef6\n    mysql> \n    select city.name,\n    city.population,\n    country.name,\n    country.surfacearea \n    from city \n    join country \n    on city.countrycode=country.code \n    where city.name='wuhan';\n<\/code><\/pre>\n
\u4f8b\u5b50\u4e8c\uff1a\u7edf\u8ba1\u5b66\u5458zhang3\uff0c\u5b66\u4e60\u4e86\u51e0\u95e8\u8bfe<\/p>\n
1\u3001\u627e\u5173\u8054\u8868\n    student.sname\n    count(sc.cno)\n        from student join sc\n2\u3001\u627e\u5173\u8054\u6761\u4ef6\n    from student join sc\n    on student.sno=sc.sno\n3\u3001\u7f57\u5217\u5176\u4ed6\u6761\u4ef6\n    mysql> select student.sno as \u5b66\u53f7,\n    student.sname as \u5b66\u751f\u59d3\u540d,\n    count(sc.cno) as \u5b66\u4e60\u8bfe\u6570 \n    from sc \n    join student\n    on sc.sno=student.sno \n    where student.sname='zhang3' \n    group by student.sno;\n    +--------+--------------+--------------+\n    | \u5b66\u53f7   | \u5b66\u751f\u59d3\u540d     | \u5b66\u4e60\u8bfe\u6570     |\n    +--------+--------------+--------------+\n    |      1 | zhang3       |            2 |\n    +--------+--------------+--------------+<\/code><\/pre>\n
\u4f8b\u5b50\u4e09\uff1a\u67e5\u8be2zhang3\uff0c\u5b66\u4e60\u7684\u8bfe\u7a0b\u540d\u79f0\u6709\u54ea\u4e9b<\/p>\n
1\u3001\u627e\u5173\u8054\u8868\n    student.sname\n    sc.sno,sc.cno\n    course.cname\n2\u3001\u627e\u5173\u8054\u6761\u4ef6\n    student.sname\n    sc.sno,sc.cno\n    course.cname\n        from student\n        join sc\n        on student.sno = sc.sno\n        join course\n        on sc.cno = course.cno\n3\u3001\u7f57\u5217\u6761\u4ef6\nmysql> select student.sno as \u5b66\u53f7,\n    student.sname as \u5b66\u751f\u59d3\u540d,\n    group_concat(course.cname) as \u8bfe\u7a0b\u540d\u79f0 \n    from student \n    join sc \n    on student.sno=sc.sno \n    join course \n    on sc.cno=course.cno \n    where student.sname='zhang3' \n    group by student.sno;\n    +--------+--------------+--------------+\n    | \u5b66\u53f7   | \u5b66\u751f\u59d3\u540d     | \u8bfe\u7a0b\u540d\u79f0     |\n    +--------+--------------+--------------+\n    |      1 | zhang3       | python,linux |\n    +--------+--------------+--------------+\n<\/code><\/pre>\n
\u4f8b\u5b50\u56db\uff1a\u67e5\u8be2oldguo\u8001\u5e08\u6559\u7684\u5b66\u751f\u540d<\/p>\n
1\u3001\u627e\u5173\u8054\u6761\u4ef6\u3001\u5173\u8054\u8868\n    select teacher.tname,student.sname\n    on teacher.tno = course.tno\n    on course.cno = sc.cno\n    on sc.sno = student.sno\n2\u3001\u7f57\u5217\u6761\u4ef6\n    select teacher.tname,\n    group_concat(student.sname)\n    from teacher\n    join course\n    on teacher.tno = course.tno\n    join sc\n    on course.cno = sc.cno\n    join student\n    on sc.sno = student.sno\n    where teacher.tname='oldguo';\n<\/code><\/pre>\n
\u4f8b\u5b50\u4e94\uff1a\u67e5\u8be2oldguo\u6240\u6559\u8bfe\u7a0b\u7684\u5e73\u5747\u5206\u6570<\/p>\n
select teacher.tname,avg(sc.score)\nfrom teacher\njoin course\non teacher.tno = course.tno\njoin sc\non course.cno = sc.cno\nwhere teacher.tname='oldguo';<\/code><\/pre>\n
\u4f8b\u5b50\u516d\uff1a\u6bcf\u4f4d\u8001\u5e08\u6240\u6559\u8bfe\u7a0b\u7684\u5e73\u5747\u5206\uff0c\u5e76\u6309\u5e73\u5747\u5206\u6392\u5e8f<\/p>\n
mysql> select teacher.tname,\navg(sc.score)\nfrom teacher\njoin course\non teacher.tno = course.tno \njoin sc \non course.cno = sc.cno \ngroup by teacher.tname \norder by avg(sc.score)\ndesc;\n+--------+---------------+\n| tname  | avg(sc.score) |\n+--------+---------------+\n| oldboy |       80.6667 |\n| oldguo |       76.7500 |\n| hesw   |       70.0000 |\n+--------+---------------+\n<\/code><\/pre>\n
\u4f8b\u5b50\u4e03\uff1a\u67e5\u8be2oldguo\u6240\u6559\u7684\u4e0d\u53ca\u683c\u7684\u5b66\u751f\u59d3\u540d<\/p>\n
mysql> select teacher.tname, \nstudent.sname, \nsc.score \nfrom teacher \njoin course \non teacher.tno = course.tno \njoin sc\non course.cno = sc.cno \njoin student \non sc.sno = student.sno \nwhere teacher.tname='oldguo' \nand sc.score < 60;\n+--------+-------+-------+\n| tname  | sname | score |\n+--------+-------+-------+\n| oldguo | li4   |    40 |\n| oldguo | zh4   |    40 |\n+--------+-------+-------+\n<\/code><\/pre>\n
\u4f8b\u5b50\u516b\uff1a\u67e5\u8be2\u6240\u6709\u8001\u5e08\u6240\u6559\u5b66\u751f\u4e0d\u53ca\u683c\u7684\u4fe1\u606f<\/p>\n
mysql> select teacher.tname, student.sname, sc.score from teacher join course on teacher.tno = course.tno join sc on course.cno = sc.cno join student on sc.sno = student.sno where sc.score < 60;\n+--------+--------+-------+\n| tname  | sname  | score |\n+--------+--------+-------+\n| hesw   | zhang3 |    59 |\n| oldguo | li4    |    40 |\n| oldguo | zh4    |    40 |\n+--------+--------+-------+\n<\/code><\/pre>\n
\u4f8b\u5b50\u4e5d\uff1a\u67e5\u8be2\u5e73\u5747\u6210\u7ee9\u5927\u4e8e60\u5206\u7684\u540c\u5b66\u7684\u5b66\u53f7\u548c\u5e73\u5747\u6210\u7ee9<\/p>\n
select student.sno,\nstudent.sname,\navg(sc.score) \nfrom student \njoin sc \non student.sno=sc.sno \ngroup by student.sno \nhaving avg(sc.score)>60;<\/code><\/pre>\n
\u4f8b\u5b50\u5341\uff1a\u67e5\u8be2\u6240\u6709\u540c\u5b66\u7684\u5b66\u53f7\u3001\u59d3\u540d\u3001\u9009\u8bfe\u6570\u3001\u603b\u6210\u7ee9\u3001\u5e73\u5747\u6210\u7ee9<\/p>\n
select student.sno,\nstudent.sname,\ncount(*),\nsum(sc.score),\navg(sc.score)\nfrom student\njoin sc\non student.sno = sc.sno\ngroup by student.sno;\n<\/code><\/pre>\n
\u4f8b\u5b50\u5341\u4e00\uff1a\u67e5\u8be2\u5404\u79d1\u6210\u7ee9\u7684\u6700\u9ad8\u5206\u548c\u6700\u4f4e\u5206\uff1a\u4ee5\u5982\u4e0b\u5f62\u5f0f\u663e\u793a\uff1a\u8bfe\u7a0bID\uff0c\u6700\u9ad8\u5206\uff0c\u6700\u4f4e\u5206***<\/p>\n
select sc.cno as \u8bfe\u7a0bid,\nmax(sc.score) as \u6700\u9ad8\u5206,\nmin(sc.score) as \u6700\u4f4e\u5206\nfrom sc\ngroup by sc.cno;<\/code><\/pre>\n
\u4f8b\u5b50\u5341\u4e8c\uff1a\u7edf\u8ba1\u5404\u4f4d\u8001\u5e08\uff0c\u6240\u6559\u8bfe\u7a0b\u7684\u53ca\u683c\u7387\uff08\u53ca\u683c\u4eba\u6570\/\u603b\u4eba\u6570\uff09***<\/p>\n
##case\u8bed\u6cd5\ncase when \u5224\u65ad then \u7ed3\u679c end\n\n#\nselect teacher.tname as \u6559\u5e08\u59d3\u540d,\nconcat(count(case when sc.score>60 then 1 end)\/count(*)*100,"%") as \u53ca\u683c\u7387\nfrom teacher\njoin course\non teacher.tno = course.tno\njoin sc\non course.cno = sc.cno\ngroup by teacher.tno;<\/code><\/pre>\n
\u4f8b\u5b50\u5341\u4e09\uff1a\u67e5\u8be2\u51fa\u53ea\u9009\u4fee\u4e86\u4e00\u95e8\u8bfe\u7a0b\u7684\u5168\u90e8\u5b66\u751f\u7684\u5b66\u53f7\u548c\u59d3\u540d<\/p>\n
select student.sno ,student.sname,count(*)\nfrom student\njoin sc\non student.sno = sc.sno\ngroup by student.sno\nhaving count(*) = 1;<\/code><\/pre>\n
\u4f8b\u5b50\u5341\u4e94\uff1a\u67e5\u8be2\u9009\u4fee\u8bfe\u7a0b\u95e8\u6570\u8d85\u8fc71\u95e8\u7684\u5b66\u751f\u4fe1\u606f<\/p>\n
select student.sno ,student.sname,count(*)\nfrom student\njoin sc\non student.sno = sc.sno\ngroup by student.sno\nhaving count(*) > 1;<\/code><\/pre>\n
\u4f8b\u5b50\u5341\u516d\uff1a\u7edf\u8ba1\u6bcf\u95e8\u8bfe\u7a0b\u4f18\u79c0\uff0885\u5206\u4ee5\u4e0a\uff09\uff0c\u826f\u597d\uff0870-85\uff09,\u4e00\u822c\uff0860-70\uff09\uff0c\u4e0d\u53ca\u683c\uff08\u5c0f\u4e8e60\uff09\u7684\u5b66\u751f\u5217\u8868***<\/p>\n
select course.cname,\ngroup_concat(\n    case when sc.score>=85 \n    then student.sname \n    end) as \u4f18\u79c0,\ngroup_concat(\n    case when sc.score>=70\n    and sc.score<85\n    then student.sname\n    end) as \u826f\u597d,\ngroup_concat(\n    case when sc.score>=60\n    and sc.score<70\n    then student.sname\n    end) as \u4e00\u822c,\ngroup_concat(\n    case when sc.score<60\n    then student.sname\n    end) as \u4e0d\u53ca\u683c\nfrom student\njoin sc\non student.sno=sc.sno\njoin course\non sc.cno=course.cno\ngroup by course.cno;<\/code><\/pre>\n
\u4f8b\u5b50\u5341\u4e03\uff1a\u67e5\u8be2\u5e73\u5747\u6210\u7ee9\u5927\u4e8e85\u7684\u6240\u6709\u5b66\u751f\u7684\u5b66\u53f7\u3001\u59d3\u540d\u548c\u5e73\u5747\u6210\u7ee9<\/p>\n
select student.sno,\nstudent.sname,\navg(sc.score)\nfrom student\njoin sc\non student.sno=sc.sno\ngroup by student.sno\nhaving avg(sc.score)>85;<\/code><\/pre>\n
\u522b\u540d<\/h5>\n
#\u522b\u540d\u662f\u4e00\u6b21\u6027\u7684\u540d\u5b57\uff0c\u4ec5\u9650\u5f53\u524dselect\u4f7f\u7528\u3002\u4f46\u53ef\u4ee5\u5728\u5168\u5c40\u8c03\u7528\u5b9a\u4e49\u7684\u522b\u540d\n\u5217\u522b\u540d,\u8868\u522b\u540d\nSELECT \na.Name AS an ,\nb.name AS bn ,\nb.SurfaceArea AS bs,\na.Population AS bp\nFROM city AS a  JOIN country AS b\nON a.CountryCode=b.Code\nWHERE a.name ='shenyang';\n\nmysql> select city.name,city.population,country.name,country.surfacearea from city join country on city.countrycode=country.code where city.name='wuhan';\n+-------+------------+-------+-------------+\n| name  | population | name  | surfacearea |\n+-------+------------+-------+-------------+\n| Wuhan |    4344600 | China |  9572900.00 |\n+-------+------------+-------+-------------+\n\nmysql> select city.name as \u57ce\u5e02\u540d,city.population as \u57ce\u5e02\u4eba\u53e3,country.name as \u56fd\u5bb6\u540d,country.surfacearea  as \u56fd\u571f\u9762\u79ef from city join country on city.countrycode=country.code where city.name='wuhan';\n+-----------+--------------+-----------+--------------+\n| \u57ce\u5e02\u540d    | \u57ce\u5e02\u4eba\u53e3     | \u56fd\u5bb6\u540d    | \u56fd\u571f\u9762\u79ef     |\n+-----------+--------------+-----------+--------------+\n| Wuhan     |      4344600 | China     |   9572900.00 |\n+-----------+--------------+-----------+--------------+\n<\/code><\/pre>\n
show\u8bed\u53e5<\/h4>\n
show  databases;                            #\u67e5\u770b\u6240\u6709\u6570\u636e\u5e93\nshow tables;                                #\u67e5\u770b\u5f53\u524d\u5e93\u7684\u6240\u6709\u8868\nSHOW tables from                            #\u67e5\u770b\u67d0\u4e2a\u6307\u5b9a\u5e93\u4e0b\u7684\u8868\nshow create database world                  #\u67e5\u770b\u5efa\u5e93\u8bed\u53e5\nshow create table world.city                #\u67e5\u770b\u5efa\u8868\u8bed\u53e5\nshow grants for  root@'localhost'         #\u67e5\u770b\u7528\u6237\u7684\u6743\u9650\u4fe1\u606f\nshow charset\uff1b                              #\u67e5\u770b\u5b57\u7b26\u96c6\nshow collation                              #\u67e5\u770b\u6821\u5bf9\u89c4\u5219\nshow processlist;                           #\u67e5\u770b\u6570\u636e\u5e93\u8fde\u63a5\u60c5\u51b5\nshow full processlist;                      #\u67e5\u770b\u6570\u636e\u5e93\u8fde\u63a5\u60c5\u51b5,\u4e14\u663e\u793ainfo\u7684\u8be6\u7ec6\u4fe1\u606f\nshow privileges                             #\u67e5\u770b\u652f\u6301\u7684\u6743\u9650\u4fe1\u606f\nshow index from                             #\u8868\u7684\u7d22\u5f15\u60c5\u51b5\nshow status                                 #\u6570\u636e\u5e93\u72b6\u6001\u67e5\u770b\nSHOW STATUS LIKE '%lock%';                    #\u6a21\u7cca\u67e5\u8be2\u6570\u636e\u5e93\u67d0\u4e9b\u72b6\u6001\nSHOW variables                              #\u67e5\u770b\u6240\u6709\u914d\u7f6e\u4fe1\u606f\nSHOW variables LIKE '%lock%';                 #\u6a21\u7cca\u67e5\u770b\u90e8\u5206\u914d\u7f6e\u4fe1\u606f\nshow engines                                #\u67e5\u770b\u652f\u6301\u7684\u6240\u6709\u7684\u5b58\u50a8\u5f15\u64ce\nshow engine innodb status\\G                 #\u67e5\u770bInnoDB\u5f15\u64ce\u76f8\u5173\u7684\u72b6\u6001\u4fe1\u606f\nshow binary logs                            #\u5217\u4e3e\u6240\u6709\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\nshow master status                          #\u67e5\u770b\u6570\u636e\u5e93\u7684\u65e5\u5fd7\u4f4d\u7f6e\u4fe1\u606f\nshow binlog evnets                          #\u67e5\u770b\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e8b\u4ef6\nshow master status;                         #\u67e5\u8be2\u4e8c\u8fdb\u5236\u65e5\u5fd7\u7684\u4f4d\u7f6e\u70b9\u4fe1\u606f\nshow slave status\\G                         #\u67e5\u770b\u4ece\u5e93\u72b6\u6001\nSHOW RELAYLOG EVENTS in                     #\u67e5\u770b\u4ece\u5e93relaylog\u4e8b\u4ef6\u4fe1\u606f\uff0c\u67e5\u770b\u4e2d\u7ee7\u65e5\u5fd7\u4e8b\u4ef6\ndesc  (show colums from city)               #\u67e5\u770b\u8868\u7684\u5217\u5b9a\u4e49\u4fe1\u606f\n\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/show.html<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
SQL\u5e94\u7528 \u4e00\u3001client mysql> help ? (\\?) Synonym for `help& […]<\/p>\n","protected":false},"author":3,"featured_media":483,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[21,19,99,22,100,20],"_links":{"self":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/482"}],"collection":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=482"}],"version-history":[{"count":1,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/482\/revisions"}],"predecessor-version":[{"id":10876,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/482\/revisions\/10876"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/media\/483"}],"wp:attachment":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=482"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=482"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=482"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}