\u3010\u516c\u5f0f\u3011PostgreSQL<\/p>\n
\u3010\u516c\u5f0f\u3011MySQL<\/p>\n
\u5982\u679c\u6ca1\u6709\u6307\u5b9a\u4efb\u4f55\u7248\u672c\uff0c\u9ed8\u8ba4\u5b89\u88c5\u6700\u65b0\u7248\u672c\uff1a<\/p>\n
“`
\n$ brew install postgresql
\n“`<\/p>\n
\u5982\u679c\u5728\u6267\u884c\u65f6\u51fa\u73b0\u4ee5\u4e0b\u9519\u8bef\uff1a<\/p>\n
“`
\npsql -l: error: could not connect to server: could not connect to server: No such file or directory
\nIs the server running locally and accepting
\nconnections on Unix domain socket “\/tmp\/.s.PGSQL.5432”?
\n“`<\/p>\n
\u53ef\u4ee5\u901a\u8fc7\u5347\u7ea7postgreSQL\u6765\u89e3\u51b3\uff1a<\/p>\n
“`
\nbrew postgresql-upgrade-database
\n“`<\/p>\n
\u8fde\u63a5
\n# \u786e\u8ba4\u7248\u672c
\n“`
\n$ psql -V
\n“`<\/p>\n
# \u67e5\u770b\u6570\u636e\u5e93\u5217\u8868
\n“`
\n$ psql -l
\n“`<\/p>\n
# \u8fde\u63a5\u6570\u636e\u5e93
\n“`
\n$ psql \u6570\u636e\u5e93\u540d
\n“`<\/p>\n<\/details>\n
\u5982\u679c\u6ca1\u6709\u6307\u5b9a\u7248\u672c\uff0c\u9ed8\u8ba4\u4e3a\u6700\u65b0\u7248\u672c<\/p>\n
brew install mysql<\/p>\n
\u6307\u5b9a\u7248\u672c
\n$ brew search mysql # \u4f7f\u7528brew search\u786e\u8ba4\u53ef\u5b89\u88c5\u7684\u7248\u672c
\n==> Formulae
\nautomysqlbackup mysql-client@5.7 mysql-search-replace
\nmysql \u2714 mysql-connector-c++ mysql@5.6
\nmysql++ mysql-connector-c++@1.1 mysql@5.7
\nmysql-client mysql-sandbox mysqltuner
\n==> Casks
\nmysql-connector-python mysql-utilities navicat-for-mysql
\nmysql-shell mysqlworkbench sqlpro-for-mysql<\/p>\n
$ brew install mysql @5.6 # \u6307\u5b9a\u5b89\u88c55.6\u7248\u672c<\/p>\n
\u786e\u8ba4\u7248\u672c
\nbrew info mysql<\/p>\n
\u5982\u679c\u5728\u8fd0\u884c\u65f6\u9047\u5230\u4ee5\u4e0b\u9519\u8bef
\n$ mysql.server start
\nERROR! The server quit without updating PID file (\/usr\/local\/var\/mysql\/USER-no-MacBook-Air.local.pid).<\/p>\n
\u5220\u9664\/usr\/local\/var\/mysql\u5e76\u91cd\u65b0\u5b89\u88c5\u5373\u53ef\u89e3\u51b3
\n$ sudo rm -rf \/usr\/local\/var\/mysql
\n$ brew uninstall mysql
\n$ brew install mysql
\n$ mysql.server start
\nStarting MySQL
\n.. SUCCESS!<\/p>\n
\u4ee5root\u8eab\u4efd\u767b\u5f55
\n$ mysql -u root<\/p>\n<\/details>\n
\u64cd\u4f5c
\nPostgreSQL<\/p>\n
\u5e2e\u52a9
\n$ psql –help # \\h # \\?<\/p>\n
\u7248\u672c\u786e\u8ba4
\n$ psql –version $ psql -V<\/p>\n
\u6570\u636e\u5e93\u5217\u8868
\n$ psql –list $ psql -l # \\l<\/p>\n
\u5220\u9664\u6570\u636e\u5e93
\n$ drop \u6570\u636e\u5e93\u540d<\/p>\n
\u6570\u636e\u5e93\u8fde\u63a5
\n$ psql \u6570\u636e\u5e93\u540d<\/p>\n
\u9009\u62e9\u6570\u636e\u5e93
\n# \\c \u6570\u636e\u5e93\u540d<\/p>\n
\u5f53\u524d\u9009\u62e9\u7684\u6570\u636e\u5e93
\n# select current_database();<\/p>\n
\u65ad\u5f00\u8fde\u63a5
\n# \\q #quit;<\/p>\n
\u8868\u683c\u5217\u8868
\n# \\dt<\/p>\n
\u8868\u683c\u8be6\u60c5
\n# \\d \u8868\u683c\u540d<\/p>\n
\u5220\u9664\u8868\u683c
\n# drop table \u8868\u683c\u540d view\u4e2d\u4f7f\u7528\u4e2d\u65e0\u6cd5\u5220\u9664<\/p>\n
\u89c6\u56fe\u5217\u8868
\n# \\dv<\/p>\n
\u7528\u6237\u5217\u8868
\n# \\du<\/p>\n
\u663e\u793a\u767b\u5f55\u7528\u6237
\n# select session_user; # select current_user;<\/p>\n
\u6267\u884c\u5916\u90e8\u6587\u4ef6
\n# \\i \u6587\u4ef6\u540d<\/p>\n
\u6ce8\u91ca
\n— (\u884c\u672b) \/* *\/ (\u591a\u884c)<\/p>\n
\u6269\u5c55\u663e\u793a(\u4ee5\u5782\u76f4\u65b9\u5f0f\u663e\u793a) \u7ea7\u522b \u5168\u5c40\u7ea7\u522b(G) \u6570\u636e\u5e93\u7ea7\u522b(D) \u8868\u7ea7\u522b(T) \u5217\u7ea7\u522b(C) \u4f8b\u7a0b\u7ea7\u522b(R)<\/p>\n CREATE ROUTINE\u3001ALTER ROUTINE\u3001EXECUTE\u3001GRANT OPTION\u6743\u9650\u9002\u7528\u4e8e\u5b58\u50a8\u8fc7\u7a0b\uff0c\u5728\u5168\u5c40\u548c\u6570\u636e\u5e93\u7ea7\u522b\u6388\u6743\uff0c\u5728CREATE ROUTINE\u4e4b\u5916\u53ef\u4ee5\u5728\u6bcf\u4e2a\u4f8b\u7a0b\u4e0a\u5e94\u7528\uff0c\u5b58\u50a8\u5728mysql.procs_priv\u8868\u4e2d<\/p>\n \u4ee3\u7406\u7528\u6237\u6743\u9650(P) **\u6743\u9650\u5217\u8868**<\/p>\n \u6743\u9650 ALL [PRIVILEGES] ALTER ALTER ROUTINE CREATE CREATE ROLE CREATE ROUTINE GD CREATE TABLESPACE CREATE TEMPORARY TABLES CREATE USER CREATE VIEW DELETE DROP DROP ROLE EVENT EXECUTE FILE GRANT OPTION INDEX INSERT LOCK TABLES PROCESS PROXY REFERENCES RELOAD REPLICATION CLIENT REPLICATION SLAVE SELECT SHOW DATABASES SHOW VIEW SHUTDOWN SUPER TRIGGER UPDATE USAGE SELECT\u6743\u9650\u53ef\u4ee5\u5728\u5168\u5c40\u7ea7\u522b\u548c\u5217\u7ea7\u522b\u4e4b\u95f4\u4efb\u610f\u7ea7\u522b\u8bbe\u7f6e\uff0c\u53ef\u4ee5\u8bbe\u7f6e\u5177\u6709\u6743\u9650\u83b7\u53d6\u6240\u6709\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u7684\u6743\u9650\uff0c\u4e5f\u53ef\u4ee5\u8bbe\u7f6e\u4ec5\u5141\u8bb8\u83b7\u53d6\u7279\u5b9a\u5217\u6570\u636e\u7684\u6743\u9650\u3002<\/p>\n \u670d\u52a1\u5668\u548c\u5ba2\u6237\u7aef\u7684\u5b57\u7b26\u7f16\u7801<\/p>\n \u9879\u76ee\u540d character_set_client character_set_results character_set_connection character_set_database character_set_server character_set_system character_sets_dir <\/p>\n id | \u59d3\u540d | \u5206\u6570 | \u56e2\u961f \u53ea\u6709\u5728\u4ee5\u4e0b\u60c5\u51b5\u4e0b\uff0c\u901a\u8fc7LIMIT\u9650\u5236\u83b7\u53d6\u6570\u636e\u7684\u6570\u91cf\u624d\u80fd\u5b9e\u73b0\u66f4\u5feb\u7684\u64cd\u4f5c\uff1a<\/p>\n 1. \u6ca1\u6709\u6307\u5b9aORDER BY \u5982\u679c\u4e0d\u4f7f\u7528WHERE\u5b50\u53e5\u8fdb\u884c\u7b5b\u9009\u6216\u521b\u5efa\u7d22\u5f15\uff0c\u5219\u65e0\u6cd5\u5b9e\u73b0\u66f4\u5feb\u7684\u64cd\u4f5c\u3002<\/p>\n \u7531\u4e8eWHERE\u5b50\u53e5\u5728SELECT\u5b50\u53e5\u4e4b\u524d\u8fdb\u884c\u8bc4\u4f30\uff0c\u56e0\u6b64\u4e0d\u80fd\u4f7f\u7528SELECT\u5b50\u53e5\u4e2d\u4f7f\u7528AS\u6307\u5b9a\u7684\u522b\u540d\u3002 \u4f8b\u5b50 \u4f8b\u5b50 \u4f8b\u5b50 \u4f8b\u5b50 \u56fa\u5b9a\u957f\u5ea6\u6587\u672c \u53ef\u53d8\u957f\u5ea6\u6587\u672c<\/p>\n varchar(255) text<\/p>\n varchar(255) text<\/p>\n \u6574\u6570<\/p>\n integer\u6216int<\/p>\n int (\u53ef\u6b63\u53ef\u8d1f) int unsigned (\u4ec5\u6b63\u6570\u4e14\u8303\u56f4\u66f4\u5927)<\/p>\n \u6d6e\u70b9\u6570 float double<\/p>\n \u5e8f\u5217\u53f7 \u5e03\u5c14\u503c<\/p>\n boolean(\u503c: true, false, t, f)<\/p>\n boolean \u522b\u540d -> tinyint(1) \u5185\u90e8\u8868\u793a true -> 1 false -> 0<\/p>\n \u65e5\u671f(\u4ec5\u65e5\u671f) \u65f6\u95f4(\u4ec5\u65f6\u95f4) \u65e5\u671f\u65f6\u95f4 datetime 2020-01-01 12:00:00<\/p>\n PostgreSQL\u6587\u6863: \u6570\u636e\u7c7b\u578b<\/p>\n<\/details>\n \u63d2\u5165\u6570\u636e\uff1ainsert into users (ranks) values (‘silver’); \u9009\u62e9\u67e5\u8be2\uff1aselect * from users where ranks = ‘gold’; \u9009\u62e9\u67e5\u8be2\uff1aselect * from users where ranks = 1; \u5982\u679c\u60f3\u6307\u5b9a\u7b2c\u4e00\u548c\u7b2c\u4e8c\u500b\u5143\u7d20\uff0c\u53ef\u4ee5\u7528\u201c1 + 2\u201d\u4f86\u8868\u793a\u201c3\u201d\uff0c\u7b2c\u4e09\u548c\u7b2c\u56db\u500b\u5143\u7d20\u53ef\u4ee5\u7528\u201c4 + 8\u201d\u4f86\u8868\u793a\u201c12\u201d<\/p>\n \u5728insert\u548cupdate\u4e2d\uff0c\u4f7f\u7528\u5167\u90e8\u5206\u914d\u7684\u6578\u503c\u4e5f\u662f\u6709\u6548\u7684<\/p>\n MySQL \u63d2\u5165\u6578\u64dausers (coins) values (‘gold’); — (1)\u4e5f\u53ef\u4ee5 \u5f9eusers\u4e2d\u9078\u64c7coins=’gold,silver’; — gold,silver \u8f93\u5165\u5fc5\u987b \u4e0d\u53ef\u91cd\u590d \u8f93\u5165\u68c0\u67e5 \u521d\u59cb\u503c \u4e3b\u952e postgreSQL<\/p>\n postgresql.sql \/* MySQL<\/p>\n mysql.sql \u521b\u5efa\u7528\u6237\u4e0e\u56e2\u961f\u5bf9\u5e94\u7684\u8868\u683c \u67e5\u770b\u7528\u6237\u4e0e\u56e2\u961f\u5bf9\u5e94\u7684\u8868\u683c\u7684\u7ed3\u6784 \u683c\u5f0f\uff1a \u793a\u4f8b\uff1a \u5217 | \u7c7b\u578b | \u6392\u5e8f\u89c4\u5219 | \u662f\u5426\u53ef\u7a7a | \u9ed8\u8ba4\u503c \u901a\u8fc7\u67e5\u8be2\u7ed3\u679c\u590d\u5236<\/p>\n \u590d\u5236\u8868\u7ed3\u6784\u548c\u8bb0\u5f55\uff0c\u4e0d\u590d\u5236\u7ea6\u675f\u3002<\/p>\n \u683c\u5f0f\uff1a \u793a\u4f8b\uff1a \u5217 | \u7c7b\u578b | \u6392\u5e8f\u89c4\u5219 | \u662f\u5426\u53ef\u7a7a | \u9ed8\u8ba4\u503c \u683c\u5f0f \u4f8b\u5b50 \u4f7f\u7528\u62bd\u51fa\u7ed3\u679c\u8fdb\u884c\u590d\u5236 \u683c\u5f0f \u4f8b\u5b50 PostgreSQL \/* \u5220\u9664\u5b57\u6bb5 *\/ \/* \u4fee\u6539\u5b57\u6bb5\u540d *\/ \/* \u4fee\u6539\u5b57\u6bb5\u7c7b\u578b *\/ \/* \u6dfb\u52a0\u7d22\u5f15 *\/ \/* \u5220\u9664\u7d22\u5f15 *\/ MySQL \/* \u5220\u9664\u5217 *\/ \/* \u4fee\u6539\u5217\u540d *\/ \/* \u4fee\u6539\u8868\u540d *\/ \/* \u6dfb\u52a0\u7d22\u5f15 *\/ \/* \u5220\u9664\u7d22\u5f15 *\/ \/* \u67e5\u770b\u7d22\u5f15 *\/ primary key\u306b\u6307\u5b9a\u3055\u308c\u305f\u30ab\u30e9\u30e0\u306f\u81ea\u52d5\u7684\u306b\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3055\u308c\u308b\u3002 \u5728 PostgreSQL \u4e2d\uff0c\u65e0\u6cd5\u5728\u547d\u4ee4\u7ea7\u522b\u5bf9\u5217\u8fdb\u884c\u6392\u5e8f\u3002 <\/p>\n \u5176\u4ed6\u65b9\u5f0f<\/p>\n \u793a\u4f8b \u4f8b\u5b50 \u524d\u63d0\u6761\u4ef6 \u521b\u5efausers\u8868: \u521b\u5efaposts\u8868: \u4e3aposts\u8868\u6dfb\u52a0\u5916\u952e\u7ea6\u675f: \u5411users\u8868\u63d2\u5165\u6570\u636e: \u5411posts\u8868\u63d2\u5165\u6570\u636e: \u4eceusers\u8868\u4e2d\u5220\u9664id\u4e3a2\u7684\u8bb0\u5f55<\/p>\n \u5411posts\u8868\u63d2\u5165\u6570\u636e: \u67e5\u8be2users\u8868\u4e2d\u7684\u6240\u6709\u8bb0\u5f55 \u5220\u9664posts\u8868\u4e2d\u7684\u5916\u952e\u7ea6\u675f<\/p>\n +—-+——+ +—-+———+ \u4f7f\u7528default current_time on update current_timestamp\u6765\u66f4\u65b0\u8bb0\u5f55\u65f6\u4f7f\u7528\u66f4\u65b0\u65f6\u95f4\u3002<\/p>\n \u5982\u679c\u5b58\u5728\u5219\u5220\u9664users\u8868\u3002<\/p>\n \u521b\u5efausers\u8868\uff0c\u5305\u542b\u4ee5\u4e0b\u5217\uff1a \u5411users\u8868\u63d2\u5165\u6570\u636e\uff0cname\u4e3a’name1’\u7684\u8bb0\u5f55\u3002 \u4eceusers\u8868\u4e2d\u67e5\u8be2\u6240\u6709\u5185\u5bb9\u3002<\/p>\n \u66f4\u65b0users\u8868\uff0c\u5c06id\u4e3a2\u7684\u8bb0\u5f55\u7684name\u66f4\u6539\u4e3a’update name’\u3002<\/p>\n \u7b49\u5f855\u79d2\u949f\u3002<\/p>\n \u4eceusers\u8868\u4e2d\u67e5\u8be2\u6240\u6709\u5185\u5bb9\u3002<\/p>\n \u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n +—-+————-+———————+———————+ \u67e5\u8be2\u52300\u884c\u53d7\u5f71\u54cd (5.01 \u79d2)<\/p>\n . +—-+————-+———————+———————+ \u7bc4\u4f8b \u964d\u5e8f \u5347\u5e8f \u62bd\u53d6\u6307\u5b9a\u6570\u91cf\u7684\u8bb0\u5f55 \/* \u5347\u5e8f *\/ \/* \u964d\u5e8f *\/ \/* \u4ece\u7b2cn\u4e2a\u4f4d\u7f6e\u5f00\u59cb\u62bd\u53d6n\u4e2a *\/ \/* \u5206\u6570\u4e3a\u7a7a\u7684\u8bb0\u5f55 *\/ \/* \u5206\u6570\u4e0d\u4e3a\u7a7a\u7684\u8bb0\u5f55 *\/ \u4f8b\u5b50 \u6bd4\u8f83\u8fd0\u7b97\u7b26<\/p>\n \u8fd0\u7b97\u7b26 > < \u5c0f\u4e8e >= <= = <> != \u903b\u8f91\u8fd0\u7b97\u7b26 — or \u6a21\u7cca\u641c\u7d22\/\u901a\u914d\u7b26 \u9ed8\u8ba4\u8bbe\u7f6e<\/p>\n PostgreSQL\u533a\u5206\u5927\u5c0f\u5199 \u901a\u914d\u7b26 _ % a%<\/p>\n \u4ee5a\u5f00\u5934\u7684\u5b57\u7b26\u4e32<\/p>\n %a%<\/p>\n \u5305\u542ba\u7684\u5b57\u7b26\u4e32<\/p>\n PostgreSQL: \u4e0d\u533a\u5206\u5927\u5c0f\u5199\u641c\u7d22 MySQL: \u533a\u5206\u5927\u5c0f\u5199\u641c\u7d22 – * \/ % \/* \u67e5\u8be2id\u4e3a\u5076\u6570\u7684\u8bb0\u5f55 *\/ \/* \u5c06id\u4e3a\u5076\u6570\u7684\u8bb0\u5f55\u7684score\u4e58\u4ee51.2 *\/ count distinct sum avg max min \u4ece\u7528\u6237\u4e2d\u9009\u62e9\u8ba1\u6570\uff08\u79ef\u5206\uff09\uff1b \u4ece\u7528\u6237\u4e2d\u9009\u62e9\u603b\u8ba1\uff08\u79ef\u5206\uff09\uff1b \u4ece\u7528\u6237\u4e2d\u9009\u62e9\u4e0d\u540c\u7684\u56e2\u961f\uff1b<\/p>\n \u4ece\u7528\u6237\u4e2d\u9009\u62e9\u8ba1\u6570\uff08\u4e0d\u540c\u7684\u56e2\u961f\uff09\uff1b– \u56e2\u961f\u7684\u79cd\u7c7b\u6570<\/p>\n<\/details>\n WHERE\u53e5 HAVING\u53e5 \/* \u6bcf\u4e2a\u56e2\u961f\u603b\u5206\u5927\u4e8e10.0\u7684\u8bb0\u5f55 *\/ \/* \u5148\u7b5b\u9009id > 3\u7684\u8bb0\u5f55\uff0c\u518d\u4f7f\u7528group by\u8fdb\u884c\u805a\u5408 *\/ <\/p>\n \u521b\u5efa\u8868users ( \u5411users\u63d2\u5165\u6570\u636e (name) values (‘name1’); \u5f53 id = 2 \u65f6\uff0c\u66f4\u65b0users\u4e2d\u7684created_at = ‘2019-09-17 10:00:00’; — \u66f4\u65b0\u65e5\u671f<\/p>\n \u9009\u62e9 * \u4eceusers\u8868; \u9009\u62e9 * \u4eceusers\u8868\uff0c\u6761\u4ef6\u4e3acreated_at > ‘2019-12-31’; — \u6307\u5b9a\u65e5\u671f\u6761\u4ef6 \u9009\u62e9created_at, date_add(created_at, interval 21 day) \u4eceusers\u8868; — 21\u5929\u540e \u9009\u62e9created_at, date_add(created_at, interval 3 week) \u4eceusers\u8868; — 3\u5468\u540e \u9009\u62e9created_at, date_format(created_at, ‘%W %M %Y’) \u4eceusers\u8868; — \u6307\u5b9a\u63d0\u53d6\u65f6\u7684\u683c\u5f0f \/* \u63d0\u53d6\u7d50\u679c *\/ \u4e32\u806f(\u5b57\u4e32\u9023\u63a5) \/* \u7d50\u679c *\/ \u4f5c\u70ba(\u5225\u540d) \u5b50\u5b57\u4e32(n\u5b57\u5143\u958b\u59cb\u53d6\u51fam\u500b\u5b57\u5143) \/* \u7d50\u679c *\/ \u96a8\u6a5f(\u96a8\u6a5f\u62bd\u53d6) \/* \u7d50\u679c *\/ floor()\uff08\u5411\u4e0b\u53d6\u6574\uff09 rand()\uff08\u968f\u673a\u6570\uff09 length()\uff08\u957f\u5ea6\uff09 substring()\uff08\u83b7\u53d6\u5b57\u7b26\u4e32\u7684\u4e00\u90e8\u5206\uff09 upper()\uff08\u8f6c\u6362\u4e3a\u5927\u5199\uff09 lower()\uff08\u8f6c\u6362\u4e3a\u5c0f\u5199\uff09 concat()\uff08\u5b57\u7b26\u4e32\u8fde\u63a5\uff09 as\uff08\u522b\u540d\uff09 +——+————+ \u4eceusers\u8868\u4e2d\u9009\u62e9 +————+——-+——–+ +————+——-+——+ \u7ed9\u5b9a\u6761\u4ef6\u65f6\u7684\u8fd4\u56de\u503c +————+——-+——–+ UPDATE users SET score = score + 1 WHERE team = ‘red’;<\/p>\n UPDATE users SET name = ‘sasaki’, score = score + 1 WHERE name = ‘taguchi’;<\/p>\n<\/details>\n \/* \u5220\u9664\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55 *\/ \u4f8b\u5b50\uff1a \u4f8b\u5982\uff1a \/* \u4f7f\u7528and\u6dfb\u52a0\u6761\u4ef6 *\/ \u5220\u9664\u89c6\u56fetaguchi_posts;<\/p>\n<\/details>\n \u5f00\u59cb\uff1b — \u5f00\u59cb \u5f00\u59cb\u4e8b\u52a1\uff1b– \u5f00\u59cb \u4f7f\u7528\u4ece\u53e5\u4e2d\u7684\u63d0\u53d6\u7ed3\u679c\u3002<\/p>\n \u524d\u63d0\u6761\u4ef6<\/p>\n \u67e5\u8be2\u8bed\u53e5\u901a\u5e38\u4f7f\u7528SELECT\u8bed\u53e5\u6765\u4ece\u4e00\u4e2a\u6216\u591a\u4e2a\u8868\u4e2d\u68c0\u7d22\u6240\u9700\u7684\u6570\u636e\u3002JOIN\u5b50\u53e5\u7528\u4e8e\u5c06\u4e24\u4e2a\u6216\u591a\u4e2a\u8868\u4e2d\u7684\u884c\u8fde\u63a5\u5728\u4e00\u8d77\uff0c\u57fa\u4e8e\u4e00\u4e2a\u6216\u591a\u4e2a\u5171\u540c\u7684\u5217\u8fdb\u884c\u5339\u914d\u3002<\/p>\n \u4ee5\u4e0b\u662f\u4e00\u4e2a\u4f7f\u7528JOIN\u7684\u793a\u4f8b\u67e5\u8be2\u8bed\u53e5\uff1a<\/p>\n “`sql \u8fd9\u4e2a\u67e5\u8be2\u8bed\u53e5\u5c06\u4e24\u4e2a\u8868\uff08posts\u548ccomments\uff09\u8fde\u63a5\u5728\u4e00\u8d77\uff0c\u901a\u8fc7id\u548cpost_id\u8fd9\u4e24\u4e2a\u5217\u8fdb\u884c\u5339\u914d\u3002\u7ed3\u679c\u5305\u542b\u6765\u81ea\u4e24\u4e2a\u8868\u7684\u5217\uff0c\u5982\u679c\u5339\u914d\u6210\u529f\uff0c\u5219\u4f1a\u8fd4\u56de\u5339\u914d\u7684\u884c\u3002<\/p>\n \u67e5\u8be2\u8bed\u53e5\u4e5f\u53ef\u4ee5\u6307\u5b9a\u8981\u8fd4\u56de\u7684\u5217\uff0c\u5e76\u4e14\u53ef\u4ee5\u4e3a\u5217\u6307\u5b9a\u522b\u540d\u3002\u5728\u8fd9\u4e2a\u793a\u4f8b\u4e2d\uff0c<\/p>\n “`sql \u8fd9\u4e2a\u67e5\u8be2\u8bed\u53e5\u4f7f\u7528\u4e86\u8868\u7684\u522b\u540d\uff0c\u5e76\u4e3a\u6bcf\u4e2a\u8fd4\u56de\u7684\u5217\u6307\u5b9a\u4e86\u4e00\u4e2a\u522b\u540d\uff0c\u4ee5\u4fbf\u66f4\u6e05\u6670\u5730\u8868\u793a\u4e0d\u540c\u5217\u7684\u4f5c\u7528\u3002<\/p>\n<\/details>\n \u4e0b\u9762\u662f\u4e00\u4e9b\u5173\u4e8e\u4f7f\u7528LEFT JOIN\u548cRIGHT JOIN\u7684\u4f8b\u5b50\uff1a<\/p>\n \u4f8b1\uff1a\u5728\u57fa\u4e8e\u5de6\u4fa7\u8868\u7684\u5de6\u5916\u8fde\u63a5\u4e2d\uff0c\u53d6\u51faposts\u8868\u4e2d\u7684\u6240\u6709\u6570\u636e\uff0c\u540c\u65f6\u6839\u636eposts.id\u548ccomments.post_id\u8fdb\u884c\u8fde\u63a5\u3002\u5982\u679ccomments\u8868\u4e2d\u6ca1\u6709post_id = 2\u7684\u6570\u636e\uff0c\u5219\u663e\u793a\u4e3aNULL\u3002<\/p>\n “` +—-+———+——–+——+———+—————————+ \u4f8b2\uff1a\u5728\u57fa\u4e8e\u53f3\u4fa7\u8868\u7684\u53f3\u5916\u8fde\u63a5\u4e2d\uff0c\u53d6\u51facomments\u8868\u4e2d\u7684\u6240\u6709\u6570\u636e\uff0c\u540c\u65f6\u6839\u636eposts.id\u548ccomments.post_id\u8fdb\u884c\u8fde\u63a5\u3002\u5982\u679cposts\u8868\u4e2d\u6ca1\u6709id = 4\u7684\u6570\u636e\uff0c\u5219\u663e\u793a\u4e3aNULL\u3002<\/p>\n “` +——+———+——–+—-+———+—————————+ \u901a\u8fc7\u4f7f\u7528\u4e0d\u540c\u7c7b\u578b\u7684JOIN\u64cd\u4f5c\uff0c\u53ef\u4ee5\u6839\u636e\u4e0d\u540c\u7684\u8fde\u63a5\u6761\u4ef6\u83b7\u53d6\u60f3\u8981\u7684\u7ed3\u679c\u3002<\/p>\n<\/details>\n <\/p>\n <\/p>\n <\/p>\n \u66f8\u5f0f \u4f8b \u4f8b\u5982\uff1a <\/p>\n <\/p>\n <\/p>\n \u89e6\u53d1\u5668\u662f\u4e00\u79cd\u7279\u6b8a\u7684\u5b58\u50a8\u8fc7\u7a0b\uff0c\u5b83\u5728\u6ee1\u8db3\u7279\u5b9a\u6761\u4ef6\u65f6\u81ea\u52a8\u6267\u884c\u3002\u5728MySQL\u4e2d\uff0c\u53ef\u4ee5\u4f7f\u7528CREATE TRIGGER\u8bed\u53e5\u6765\u521b\u5efa\u89e6\u53d1\u5668\u3002\u89e6\u53d1\u5668\u53ef\u4ee5\u5728INSERT\u3001UPDATE\u6216DELETE\u64cd\u4f5c\u4e4b\u524d\u6216\u4e4b\u540e\u89e6\u53d1\uff0c\u53ef\u4ee5\u6307\u5b9a\u89e6\u53d1\u5668\u6240\u9488\u5bf9\u7684\u8868\u4ee5\u53ca\u89e6\u53d1\u65f6\u8981\u6267\u884c\u7684\u64cd\u4f5c\u3002<\/p>\n \u4ee5\u4e0b\u662f\u4e00\u4e9b\u4f7f\u7528\u89e6\u53d1\u5668\u7684\u793a\u4f8b\uff1a<\/p>\n \u793a\u4f8b1\uff1a \u793a\u4f8b2\uff1a \u901a\u8fc7\u4f7f\u7528\u89e6\u53d1\u5668\uff0c\u53ef\u4ee5\u5728\u6570\u636e\u66f4\u6539\u65f6\u6267\u884c\u81ea\u5b9a\u4e49\u64cd\u4f5c\uff0c\u4ece\u800c\u5b9e\u73b0\u66f4\u7075\u6d3b\u548c\u81ea\u52a8\u5316\u7684\u6570\u636e\u5e93\u7ba1\u7406\u3002<\/p>\n<\/details>\n
\n# \\x<\/p>\n<\/details>\n
\n**\u300c>\u300d\u5728\u5185\u90e8\u6267\u884c**
\n| \u64cd\u4f5c | MySQL |
\n|—|—|—|
\n| \u5e2e\u52a9 | $ mysql –help > h; |
\n| \u7248\u672c\u786e\u8ba4 | $ mysql –version |
\n| \u6570\u636e\u5e93\u5217\u8868 | > show databases; > show schemas > show databases like ‘\u641c\u7d22\u5b57\u7b26\u4e32’ |
\n| \u521b\u5efa\u6570\u636e\u5e93 | > create database \u6570\u636e\u5e93\u540d; |
\n| \u5220\u9664\u6570\u636e\u5e93 | > drop database \u6570\u636e\u5e93\u540d; |
\n| \u8fde\u63a5\u6570\u636e\u5e93 | > mysql -u \u7528\u6237\u540d -p \u203b \u5982\u679c\u662froot\u7528\u6237\u4e0d\u9700\u8981-p |
\n| \u9009\u62e9\u6570\u636e\u5e93 | > use \u6570\u636e\u5e93\u540d |
\n| \u5f53\u524d\u9009\u62e9\u7684\u6570\u636e\u5e93 | > select database(); |
\n| \u65ad\u5f00\u8fde\u63a5 | > \\q > quit; > exit |
\n| \u8868\u5217\u8868 | > show tables; > show tables like ‘\u641c\u7d22\u5b57\u7b26\u4e32’; |
\n| \u8868\u8be6\u7ec6\u4fe1\u606f | > desc \u8868\u540d; |
\n| \u5220\u9664\u8868 | > drop table \u8868\u540d; |
\n| \u89c6\u56fe\u5217\u8868 | > show tables; |
\n| \u5220\u9664\u89c6\u56fe | > drop view\u540d; |
\n| \u663e\u793aCREATE VIEW\u8bed\u53e5 | > show create view view\u540d; |
\n| \u7528\u6237\u5217\u8868 | > select Host, User from mysql.user; |
\n| \u663e\u793a\u767b\u5f55\u7528\u6237 | > select user();|
\n| \u5916\u90e8\u6587\u4ef6\u6267\u884c | $ mysql -u \u7528\u6237\u540d < \u6587\u4ef6\u540d(\u8def\u5f84) > source \u6587\u4ef6\u540d(\u8def\u5f84) > \\. \u6587\u4ef6\u540d(\u8def\u5f84) |
\n| \u6ce8\u91ca | #(\u5230\u884c\u672b) — (\u5230\u884c\u672b) \/* *\/ (\u591a\u884c) |
\n| \u6269\u5c55\u663e\u793a(\u4ee5\u7ad6\u76f4\u5f62\u5f0f\u8f93\u51fa) | > select * from \u8868\u540d \\G; \u203b \u5728\u67e5\u8be2\u672b\u5c3e\u6dfb\u52a0\\G \u203b \u5728\u8fde\u63a5mysql\u65f6\u4f7f\u7528-E\u9009\u9879 |
\n| \u786e\u8ba4\u6570\u636e\u5e93\u5b58\u5728 | > create database if not exists \u6570\u636e\u5e93\u540d; |
\n| \u786e\u8ba4\u5b57\u7b26\u7f16\u7801 | > show variables like ‘%char%’; |
\n| \u786e\u8ba4\u8b66\u544a\u4fe1\u606f | > show warnings; |
\n| \u8fd0\u884c\u4e2d\u7ebf\u7a0b\u4fe1\u606f | > show processlist; |
\n| \u53d6\u6d88\u547d\u4ee4 | > \\c |
\n| \u521b\u5efa\u7528\u6237 | > create user \u7528\u6237\u540d@\u8fde\u63a5\u6765\u6e90 identified by ‘\u5bc6\u7801’; |
\n| \u5220\u9664\u7528\u6237 | > drop user \u7528\u6237\u540d@\u8fde\u63a5\u6765\u6e90; |
\n| \u8fde\u63a5 | $ mysql -u \u7528\u6237\u540d -p \u6570\u636e\u5e93\u540d; |
\n| \u663e\u793a\u767b\u5f55\u7528\u6237 | > select user(); |
\n| \u6388\u6743 | > grant \u6743\u9650\u7c7b\u578b on \u6570\u636e\u5e93\u540d.\u76ee\u6807\u7684\u8868 to \u7528\u6237\u540d@\u8fde\u63a5\u6765\u6e90; |
\n**\u6743\u9650\u7ea7\u522b**<\/p>\n
\n\u63cf\u8ff0<\/p>\n
\n\u9002\u7528\u4e8e\u6240\u6709\u6570\u636e\u5e93\uff0c\u5b58\u50a8\u5728mysql.user\u8868\u4e2d<\/p>\n
\n\u9002\u7528\u4e8e\u7279\u5b9a\u6570\u636e\u5e93\u4e2d\u7684\u6240\u6709\u5bf9\u8c61\uff0c\u5b58\u50a8\u5728mysql.db\u8868\u4e2d<\/p>\n
\n\u9002\u7528\u4e8e\u7279\u5b9a\u8868\u4e2d\u7684\u6240\u6709\u5217\uff0c\u5b58\u50a8\u5728mysql.tables_priv\u8868\u4e2d<\/p>\n
\n\u9002\u7528\u4e8e\u7279\u5b9a\u8868\u4e2d\u7684\u5355\u4e2a\u5217\uff0c\u5b58\u50a8\u5728mysql.columns_priv\u8868\u4e2d<\/p>\n
\n\u4e00\u4e2a\u7528\u6237\u53ef\u4ee5\u6210\u4e3a\u53e6\u4e00\u4e2a\u7528\u6237\u7684\u4ee3\u7406\uff0c\u5b58\u50a8\u5728mysql.proxies_priv\u8868\u4e2d<\/p>\n
\n\u7ea7\u522b
\n\u542b\u4e49<\/p>\n
\nGDT
\n\u9664\u4e86GRANT OPTION\u548cPROXY\u4e4b\u5916\u7684\u6240\u6709\u6743\u9650<\/p>\n
\nGDT
\n\u4fee\u6539\u8868<\/p>\n
\nGDR
\n\u4fee\u6539\u548c\u5220\u9664\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
\nGDT
\n\u521b\u5efa\u6570\u636e\u5e93\u548c\u8868<\/p>\n
\nG
\n\u521b\u5efa\u89d2\u8272<\/p>\n
\n\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
\nG
\n\u521b\u5efa\u3001\u4fee\u6539\u548c\u5220\u9664\u8868\u7a7a\u95f4\u548c\u65e5\u5fd7\u7ec4<\/p>\n
\nGD
\n\u4f7f\u7528CREATE TEMPORARY TABLE<\/p>\n
\nG
\n\u4f7f\u7528CREATE USER\u3001DROP USER\u3001RENAME USER\u3001REVOKE ALL PRIVILEGES<\/p>\n
\nGDT
\n\u521b\u5efa\u548c\u4fee\u6539\u89c6\u56fe<\/p>\n
\nGDT
\n\u5220\u9664\u6570\u636e\u5e93\u3001\u8868\u548c\u89c6\u56fe<\/p>\n
\nGDT
\n\u5220\u9664\u8868<\/p>\n
\nG
\n\u5220\u9664\u89d2\u8272<\/p>\n
\nGD
\n\u542f\u7528\u4e8b\u4ef6\u8c03\u5ea6\u7a0b\u5e8f<\/p>\n
\nGDR
\n\u8fd0\u884c\u5b58\u50a8\u8fc7\u7a0b<\/p>\n
\nG
\n\u4f7f\u7528SELECT … INTO OUTFILE\u548cLOAD DATA INFILE<\/p>\n
\nGDTRP
\n\u6388\u4e88\u548c\u64a4\u9500\u5176\u4ed6\u8d26\u6237\u7684\u6743\u9650<\/p>\n
\nGDT
\n\u521b\u5efa\u548c\u5220\u9664\u7d22\u5f15<\/p>\n
\nGDTC
\n\u4f7f\u7528INSERT<\/p>\n
\nGD
\n\u4f7f\u7528LOCK TABLES\u5728\u5177\u6709SELECT\u6743\u9650\u7684\u8868\u4e0a\u9501\u5b9a<\/p>\n
\nG
\n\u4f7f\u7528SHOW FULL PROCESSLIST<\/p>\n
\n—
\n\u4f7f\u7528\u4ee3\u7406<\/p>\n
\nGDTC
\n\u521b\u5efa\u5916\u952e<\/p>\n
\nG
\n\u4f7f\u7528FLUSH<\/p>\n
\nG
\n\u8be2\u95ee\u4ece\u5e93\u548c\u4e3b\u5e93\u7684\u4f4d\u7f6e\u4fe1\u606f<\/p>\n
\nG
\n\u4f7f\u7528\u590d\u5236\u4ece\u5e93\uff08\u8bfb\u53d6\u4e3b\u5e93\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u4e8b\u4ef6\uff09<\/p>\n
\nGDTC
\n\u4f7f\u7528SELECT<\/p>\n
\nG
\n\u4f7f\u7528SHOW DATABASES<\/p>\n
\nGDT
\n\u4f7f\u7528SHOW CREATE VIEW<\/p>\n
\nG
\n\u4f7f\u7528mysqladmin shutdown<\/p>\n
\nG
\n\u4f7f\u7528CHANGE MASTER TO\u3001KILL\u3001PURGE BINARY LOGS\u3001SET GLOBAL\u3001mysqladmin debug\u547d\u4ee4<\/p>\n
\nGDT
\n\u521b\u5efa\u548c\u5220\u9664\u89e6\u53d1\u5668<\/p>\n
\nGDTC
\n\u4f7f\u7528UPDATE<\/p>\n
\nG
\n“\u65e0\u6743\u9650”<\/p>\n
\n\u9002\u7528\u5bf9\u8c61\uff08\u5ba2\u6237\u7aef\/\u670d\u52a1\u5668\uff09
\n\u63cf\u8ff0<\/p>\n
\n\u5ba2\u6237\u7aef
\n\u5728\u5ba2\u6237\u7aef\u5411\u670d\u52a1\u5668\u53d1\u9001\u67e5\u8be2\u65f6\u4f7f\u7528\u7684\u5b57\u7b26\u96c6<\/p>\n
\n\u670d\u52a1\u5668
\n\u5728\u670d\u52a1\u5668\u5411\u5ba2\u6237\u7aef\u53d1\u9001\u67e5\u8be2\u7ed3\u679c\u65f6\u4f7f\u7528\u7684\u5b57\u7b26\u96c6<\/p>\n
\n\u5ba2\u6237\u7aef
\n\u670d\u52a1\u5668\u89e3\u6790\u5ba2\u6237\u7aef\u53d1\u9001\u7684\u67e5\u8be2\u65f6\u4f7f\u7528\u7684\u5b57\u7b26\u96c6<\/p>\n
\n\u670d\u52a1\u5668
\n\u670d\u52a1\u5668\u7684\u9ed8\u8ba4\u5b57\u7b26\u96c6\u3002LOAD DATA INFILE\u8bed\u53e5\u5c06\u4f7f\u7528\u6b64\u6307\u5b9a\u3002\u5728\u542f\u52a8MySQL\u65f6\uff0ccharacter_set_server\u7684\u503c\u5c06\u5e94\u7528\u3002<\/p>\n
\n\u670d\u52a1\u5668
\n\u670d\u52a1\u542f\u52a8\u65f6\u7684\u9ed8\u8ba4\u5b57\u7b26\u96c6<\/p>\n
\n–
\n\u4f7f\u7528\u6b64\u5b57\u7b26\u96c6\u5904\u7406\u6587\u4ef6\u540d<\/p>\n
\n–
\n\u7528\u4e8e\u5904\u7406\u5b57\u7b26\u96c6\u6240\u9700\u6587\u4ef6\u7684\u76ee\u5f55<\/p>\n<\/details>\n\u5173\u4e8e\u6269\u5c55\u8868\u8fbe\u7684\u5185\u5bb9<\/h3>\n
\n
\u5217\u304c\u591a\u304f\u6a2a\u9577\u3060\u3068\u898b\u306b\u304f\u3044\u5834\u5408\u306b\u4fbf\u5229<\/ul>\n<\/li>\n<\/ul>\n
\u6a2a\u304b\u7e26\u304b\u306e\u9055\u3044<\/ul>\n
\n=# \u4ece\u7528\u6237\u9009\u62e9*\/* \u63d0\u53d6\u7ed3\u679c *\/<\/p>\n
\n—-+————+——-+——-
\n1 | taguchi | 5.5 | \u7ea2\u8272
\n2 | fkoji | 8.3 | \u84dd\u8272
\n3 | dotinstall | 2.2 | \u7ea2\u8272
\n4 | sasaki | 5 | \u7eff\u8272
\n5 | sasaki | 4.6 | \u7ea2\u8272
\n6 | kimura | 4.7 | \u7eff\u8272
\n(6 \u884c)<\/p>\n<\/details>\n
\n=# select * from users;-[ RECORD 1 ]—–
\nid | 1
\nname | taguchi
\nscore | 5.5
\nteam | red
\n-[ RECORD 2 ]—–
\nid | 2
\nname | fkoji
\nscore | 8.3
\nteam | blue
\n-[ RECORD 3 ]—–
\nid | 3
\nname | dotinstall
\nscore | 2.2
\nteam | red
\n.
\n.
\n.
\n=# \\x
\n\u62d3\u5c55\u663e\u793a\u5df2\u5173\u95ed\u3002<\/p>\n<\/details>\n
\n\u3010SQL\u8bc4\u4f30\u987a\u5e8f\u3011<\/h1>\n
\n|\u987a\u5e8f| \u8bed\u53e5 | \u52a8\u4f5c |
\n|:—:|—|—|
\n|\u5148| FROM | \u6307\u5b9a\u76ee\u6807\u8868\u683c |
\n|\u2193|ON| \u6307\u5b9a\u8fde\u63a5\u7684\u5217 |
\n|| JOIN | \u6307\u5b9a\u8981\u8fde\u63a5\u7684\u8868\u683c |
\n|| WHERE | \u8fdb\u884c\u76ee\u6807\u6570\u636e\u7b5b\u9009 |
\n|| GROUP BY | \u5bf9\u76ee\u6807\u6570\u636e\u8fdb\u884c\u5206\u7ec4 |
\n|| \u51fd\u6570 | \u8fdb\u884c\u805a\u5408\u64cd\u4f5c |
\n|| HAVING | \u5bf9\u5206\u7ec4\u7ed3\u679c\u8fdb\u884c\u7b5b\u9009 |
\n|| SELECT | \u6307\u5b9a\u8981\u63d0\u53d6\u7684\u5217 |
\n|| DISTINCT | \u6307\u5b9a\u8981\u63d0\u53d6\u7684\u5217 |
\n|| ORDER BY | \u6392\u5e8f |
\n|\u540e| LIMIT | \u6307\u5b9a\u63d0\u53d6\u7684\u6570\u91cf |<\/details>\n
\n\u2467 \u53bb\u91cd\u62bd\u53d6\u7684\u5217
\n\u2460 \u5f9e\u6307\u5b9a\u7684\u76ee\u6a19\u8868\u4e2d\u9078\u64c7
\n\u2461 \u6307\u5b9a\u8981\u7d50\u5408\u7684\u8868
\n\u2462 \u6307\u5b9a\u8981\u7d50\u5408\u7684\u5217
\n\u2463 \u904e\u6ffe\u76ee\u6a19\u6578\u64da
\n\u2464 \u6309\u7d44\u5206\u7d44
\n\u2465 \u5f9e\u7d44\u7d50\u679c\u4e2d\u904e\u6ffe
\n\u2468 \u6309\u6307\u5b9a\u9806\u5e8f\u6392\u5e8f
\n\u2469 \u6307\u5b9a\u62bd\u53d6\u7684\u6578\u91cf\uff1b<\/p>\n<\/details>\n
\n\u6839\u636eHAVING\u5b50\u53e5\u7684\u6761\u4ef6\u6765\u51b3\u5b9aSELECT\u5b50\u53e5\u7684\u6307\u5b9a\uff0c\u56e0\u6b64\u5728\u521b\u5efaHAVING\u5b50\u53e5\u4e4b\u540e\u624d\u80fd\u6307\u5b9aSELECT\u5b50\u53e5\u3002\u5373\u4f7f\u9650\u5236\u83b7\u53d6\u6570\u636e\u7684\u6570\u91cf\u8f83\u5c11\uff0cLIMIT\u5728\u6700\u540e\u624d\u8fdb\u884c\u8bc4\u4f30\uff0c\u5e76\u4e0d\u610f\u5473\u7740\u5904\u7406\u4f1a\u66f4\u5feb\u3002<\/p>\n
\n2. ORDER BY\u4e2d\u6307\u5b9a\u7684\u5217\u5df2\u5b9a\u4e49\u4e3a\u7d22\u5f15<\/p>\n
\n\u8981\u52a0\u5feb\u67e5\u8be2\u901f\u5ea6\uff0c\u5e94\u5c3d\u65e9\u51cf\u5c11\u83b7\u53d6\u7684\u6570\u636e\u91cf\u3002<\/p>\n<\/details>\n
\n\u3010\u684c\u5b50\u64cd\u4f5c\u3011<\/h1>\n
\u521b\u5efa\u8868<\/h3>\n
\n\u521b\u5efa\u8868 \u8868\u540d (\u5217\u540d \u7c7b\u578b(), \u5217\u540d \u7c7b\u578b());<\/p>\n
\n\u521b\u5efa\u8868 users(name varchar(255), age integer);<\/p>\n<\/details>\n
\n\u4fee\u6539\u8868\u540d<\/h3>\n
\n\u5c06\u5f53\u524d\u8868\u540d\u66f4\u6539\u4e3a\u65b0\u8868\u540d<\/p>\n
\n\u5c06\u8868\u540dusers\u66f4\u6539\u4e3aemployees<\/p>\n<\/details>\n
\n\u5c06\u8868 \u73b0\u8868\u540d \u6539\u540d\u4e3a \u65b0\u8868\u540d;<\/p>\n
\n\u5c06\u8868 users \u6539\u540d\u4e3a employees;<\/p>\n<\/details>\n
\n\u5220\u9664\u8868<\/h3>\n
\n\u522a\u9664 \u8868\u540d;<\/p>\n
\n\u522a\u9664 users;<\/p>\n<\/details>\n
\n\u6570\u636e\u7c7b\u578b<\/h3>\n
\nPostgreSQL
\nMySQL<\/p>\n
\nchar(5)
\nchar(5)<\/p>\n
\nreal<\/p>\n
\nserial<\/p>\n
\ndate
\ndate<\/p>\n
\ntime
\ntime<\/p>\n
\ntimestamp<\/p>\n
\n\u5217\u4e3e\u7c7b\u578b<\/h3>\n
\n\u521b\u5efa\u8868\u683cusers (
\nid int unsigned primary key auto_increment,
\nranks enum(‘gold’, ‘silver’, ‘bronze’) — \u679a\u4e3e\u7c7b\u578b\uff0c\u5728\u5185\u90e8\u5206\u522b\u4f7f\u75281\u30012\u30013\u8fdb\u884c\u6570\u5b57\u5206\u914d
\n);<\/p>\n
\n\u63d2\u5165\u6570\u636e\uff1ainsert into users (ranks) values (‘gold’);
\n\u63d2\u5165\u6570\u636e\uff1ainsert into users (ranks) values (‘bronze’);
\n\u63d2\u5165\u6570\u636e\uff1ainsert into users (ranks) values (‘red’); — \u5c06\u4f1a\u63d2\u5165NULL\u503c
\n\u63d2\u5165\u6570\u636e\uff1ainsert into users (ranks) values (1);
\n\u63d2\u5165\u6570\u636e\uff1ainsert into users (ranks) values (2);
\n\u63d2\u5165\u6570\u636e\uff1ainsert into users (ranks) values (3);
\n\u63d2\u5165\u6570\u636e\uff1ainsert into users (ranks) values (4); — \u5c06\u4f1a\u63d2\u5165NULL\u503c<\/p>\n
\n+—-+——-+——-+——-+
\n| id | name | score | ranks |
\n+—-+——-+——-+——-+
\n| 2 | fkoji | 8.2 | gold |
\n+—-+——-+——-+——-+
\n1 row in set (0.00 sec)<\/p>\n
\n+—-+——-+——-+——-+
\n| id | name | score | ranks |
\n+—-+——-+——-+——-+
\n| 2 | fkoji | 8.2 | gold |
\n+—-+——-+——-+——-+
\n1 row in set (0.00 sec)<\/p>\n<\/details>\n
\n\u5728\u5167\u90e8\uff0c\u7b2c\u4e00\u500b\u5143\u7d20\u88ab\u8ce6\u4e88\u201c2\u76840\u6b21\u65b9\u201d\u6578\u503c\uff0c\u7b2c\u4e8c\u500b\u5143\u7d20\u88ab\u8ce6\u4e88\u201c2\u76841\u6b21\u65b9\u201d\u6578\u503c\uff0c\u7b2c\u4e09\u500b\u5143\u7d20\u88ab\u8ce6\u4e88\u201c2\u76842\u6b21\u65b9\u201d\u6578\u503c…<\/p>\n
\n\u5275\u5efa\u8868users (
\nid int unsigned \u4e3b\u9375 auto_increment,
\ncoins set(‘gold’, ‘silver’, ‘bronze’) — [\u7b2c\u4e00\u500b-> 2\u76840\u6b21\u65b9(1), \u7b2c\u4e8c\u500b-> 2\u76841\u6b21\u65b9(2), \u7b2c\u4e09\u500b-> 2\u76842\u6b21\u65b9(4)]
\n);<\/p>\n
\n\u63d2\u5165\u6578\u64dausers (coins) values (‘silver’); — (2)\u4e5f\u53ef\u4ee5
\n\u63d2\u5165\u6578\u64dausers (coins) values (‘bronze’); — (4)\u4e5f\u53ef\u4ee5
\n\u63d2\u5165\u6578\u64dausers (coins) values (‘gold,silver’); — (3)\u4e5f\u53ef\u4ee5 1 + 2
\n\u63d2\u5165\u6578\u64dausers (coins) values (‘gold,bronze’); — (5)\u4e5f\u53ef\u4ee5 1 + 4
\n\u63d2\u5165\u6578\u64dausers (coins) values (‘silver,bronze’); — (6)\u4e5f\u53ef\u4ee5 2 + 4
\n\u63d2\u5165\u6578\u64dausers (coins) values (‘silver,bronze,bronze’); — (7)\u4e5f\u53ef\u4ee5 1 + 2 + 4<\/p>\n
\n\u5f9eusers\u4e2d\u9078\u64c7coins like ‘%gold%’;
\n+—-+——————–+
\n| id | coins |
\n+—-+——————–+
\n| 1 | gold |
\n| 4 | gold,silver |
\n| 5 | gold,bronze |
\n| 7 | gold,silver,bronze |
\n+—-+——————–+
\n\u51714\u884c (0.00 \u79d2)
\n\u5f9eusers\u4e2d\u9078\u64c7coins=1; — gold
\n\u5f9eusers\u4e2d\u9078\u64c7coins=2; — silver
\n\u5f9eusers\u4e2d\u9078\u64c7coins=3; — gold,silver
\n\u5f9eusers\u4e2d\u9078\u64c7coins=4; — bronze
\n\u5f9eusers\u4e2d\u9078\u64c7coins=5; — gold,bronze
\n\u5f9eusers\u4e2d\u9078\u64c7coins=6; — silver,bronze
\n\u5f9eusers\u4e2d\u9078\u64c7coins=7; — gold,silver,bronze
\n\u5f9eusers\u4e2d\u9078\u64c7coins=8; — \u7a7a<\/p>\n<\/details>\n
\n\u9650\u5236<\/h3>\n
\nPostgresSQL
\nMySQL<\/p>\n
\n\u975e\u7a7a
\n\u975e\u7a7a<\/p>\n
\n\u552f\u4e00
\n\u552f\u4e00<\/p>\n
\n\u68c0\u67e5<\/p>\n
\n\u9ed8\u8ba4
\n\u9ed8\u8ba4<\/p>\n
\n\u4e3b\u952e
\n\u4e3b\u952e<\/p>\n
\n\u521b\u5efa\u8868posts (
\nid serial \u4e3b\u952e,
\ntitle varchar(255) \u975e\u7a7a,
\nbody text \u68c0\u67e5(length(body) > 5),
\nis_draft boolean \u9ed8\u8ba4TRUE,
\ncreated timestamp \u9ed8\u8ba4statement_timestamp()
\n);<\/p>\n
\n\u5236\u7ea6
\n\u975e\u7a7a \u8f93\u5165\u5fc5\u987b
\n\u552f\u4e00 \u4e0d\u53ef\u91cd\u590d
\n\u68c0\u67e5
\n\u9ed8\u8ba4 \u9ed8\u8ba4\u503c
\n\u4e3b\u952e (\u975e\u7a7a + \u552f\u4e00) \u53ea\u80fd\u5728\u8868\u4e2d\u8bbe\u7f6e\u4e00\u4e2a
\n*\/
\n\/*
\nstatement_timestamp() -> \u8bb0\u5f55\u63d2\u5165\u65f6\u95f4
\n*\/<\/p>\n
\n\u521b\u5efa\u8868users (
\nid int unsigned \u4e3b\u952e \u81ea\u589e,
\nname varchar(20) \u552f\u4e00,
\nscore float \u9ed8\u8ba40.0
\n);<\/p>\n<\/details>\n
\n\u4f7f\u7528\u68c0\u7d22\u7ed3\u679c\u521b\u5efa\u8868\u683c\u3002<\/h3>\n
\ncreate table \u8868\u683c\u540d\u79f0 as \u67e5\u8be2\u8bed\u53e5;<\/p>\n
\ncreate table users_with_team as
\nselect
\nname,
\nscore,
\ncase
\nwhen score > 8.0 then ‘\u56e2\u961fA’
\nwhen score > 6.0 then ‘\u56e2\u961fB’
\nelse ‘\u56e2\u961fC’
\nend as Team
\nfrom
\nusers;<\/p>\n
\ndesc users_with_team;
\n+———+————-+——+—–+———+——-+
\n| \u5b57\u6bb5\u540d | \u7c7b\u578b | \u53ef\u7a7a | \u952e | \u9ed8\u8ba4 | \u5907\u6ce8 |
\n+———+————-+——+—–+———+——-+
\n| name | varchar(20) | \u662f | | NULL | |
\n| score | float | \u662f | | NULL | |
\n| Team | varchar(6) | \u5426 | | | |
\n+———+————-+——+—–+———+——-+
\n\u51713\u884c\uff080.01 \u79d2)<\/p>\n<\/details>\n
\n\u684c\u5b50\u590d\u5236<\/h3>\n
\ncreate table \u76ee\u6807\u8868\u540d (like \u6e90\u8868\u540d including all);<\/p>\n
\ncreate table users_copy (like users including all);<\/p>\n
\n—-+————————+———+———-+———————————-
\nid | integer | | not null | nextval(‘users_id_seq’::regclass)
\nname | character varying(255) | | not null |
\nscore | real | | | 0
\nteam | character varying(255) | | |
\n\u7d22\u5f15:
\n“users_copy_pkey” PRIMARY KEY, btree (id)<\/p>\n
\ncreate table \u76ee\u6807\u8868\u540d as \u67e5\u8be2\u8bed\u53e5;<\/p>\n
\ncreate table users_copy as select * from users;<\/p>\n
\n—-+————————+———+———-+———
\nid | integer | | |
\nname | character varying(255) | | |
\nscore | real | | |
\nteam | character varying(255) | | |<\/p>\n<\/details>\n
\n\u53ea\u590d\u5236\u8868\u7ed3\u6784\uff08\u4e0d\u590d\u5236\u8bb0\u5f55\uff09
\n\u4fdd\u7559Primary key\u548c\u7d22\u5f15<\/p>\n
\ncreate table \u65b0\u5efa\u8868\u540d like \u6e90\u8868\u540d;<\/p>\n
\ncreate table users_empty like users;
\ndesc users_empty;
\n+——-+————–+——+—–+———+—————-+
\n| Field | Type | Null | Key | Default | Extra |
\n+——-+————–+——+—–+———+—————-+
\n| id | int unsigned | NO | PRI | NULL | auto_increment |
\n| name | varchar(20) | YES | | NULL | |
\n| score | float | YES | | NULL | |
\n+——-+————–+——+—–+———+—————-+
\n3 rows in set (0.00 sec)<\/p>\n
\n\u590d\u5236\u8868\u7ed3\u6784\u548c\u8bb0\u5f55
\n\u4e0d\u590d\u5236\u7ea6\u675f
\n\u4e0d\u7ee7\u627fPrimary key\u548c\u7d22\u5f15
\n\u5c06\u6269\u5c55\u7684\u9ed8\u8ba4\u503c\u66ff\u6362\u4e3a0<\/p>\n
\ncreate table \u65b0\u5efa\u8868\u540d as select * from \u539f\u8868\u540d;<\/p>\n
\ncreate table users_copy select * from users;
\n+——-+————–+——+—–+———+——-+
\n| Field | Type | Null | Key | Default | Extra |
\n+——-+————–+——+—–+———+——-+
\n| id | int unsigned | NO | | 0 | |
\n| name | varchar(20) | YES | | NULL | |
\n| score | float | YES | | NULL | |
\n+——-+————–+——+—–+———+——-+
\n3 rows in set (0.01 sec)<\/p>\n<\/details>\n
\n\u6539\u53d8\u8868\u683c\u7684\u7ed3\u6784<\/h1>\n
\n\/* \u6dfb\u52a0\u5b57\u6bb5 *\/
\nalter table \u8868\u540d add \u5b57\u6bb5\u540d \u7c7b\u578b;
\nalter table users add fullname varchar(255);<\/p>\n
\nalter table \u8868\u540d drop \u5b57\u6bb5\u540d;
\nalter table users drop \u5b57\u6bb5\u540d;<\/p>\n
\nalter table \u8868\u540d rename \u539f\u540d to \u65b0\u540d;
\nalter table users rename name to myname;<\/p>\n
\nalter table \u8868\u540d alter \u5b57\u6bb5\u540d type \u7c7b\u578b;
\nalter table users alter myname type varchar(32);<\/p>\n
\ncreate index \u7d22\u5f15\u540d on \u8868\u540d(\u5b57\u6bb5\u540d);
\ncreate index team_index on users(team);<\/p>\n
\ndrop index \u7d22\u5f15\u540d;
\ndrop index team_index;<\/p>\n
\n\/* \u6dfb\u52a0\u5217 *\/
\nalter table \u8868\u540d add column \u5217\u540d \u7c7b\u578b after \u5217\u540d;
\nalter table users add column email varchar(255) after name; \/* \u5728\u5217name\u4e4b\u540e\u6dfb\u52a0\u5217email *\/<\/p>\n
\nalter table \u8868\u540d drop column \u5217\u540d;
\nalter table users drop column score;<\/p>\n
\nalter table \u8868\u540d change \u65e7\u5217\u540d \u65b0\u5217\u540d \u7c7b\u578b \u7ea6\u675f;
\nalter table users change name user_name varchar(80) default ‘no-name’;<\/p>\n
\nalter table \u65e7\u8868\u540d rename \u65b0\u8868\u540d;
\nalter table users rename persons;<\/p>\n
\nalter table \u8868\u540d add index \u7d22\u5f15\u540d (\u8981\u6dfb\u52a0\u7d22\u5f15\u7684\u5217\u540d);
\nalter table users add index index_score (score);<\/p>\n
\nalter table \u8868\u540d drop index \u7d22\u5f15\u540d;
\nalter table users drop index index_score;<\/p>\n
\nshow index from \u8868\u540d;
\nshow index from users;<\/p>\n<\/details>\n
\n\u5173\u4e8e\u7d22\u5f15<\/h3>\n
\n\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u306e\u8ffd\u52a0\u306f\u3001\u691c\u7d22\u306e\u3068\u304d\u306f\u65e9\u304f\u306a\u308b\u304c\u30ec\u30b3\u30fc\u30c9\u306e\u633f\u5165\u3001\u66f4\u65b0\u6642\u306b\u65b0\u3057\u3044\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u4f5c\u308b\u306e\u3067\u9045\u304f\u306a\u308b\u305f\u3081\u30d0\u30e9\u30f3\u30b9\u3092\u8003\u3048\u308b\u3002<\/p>\n\u6392\u5217\u3002<\/h3>\n
\n\u4ee5\u4e0b\u65b9\u6cd5\u53ef\u5b9e\u73b0\u5bf9\u5217\u8fdb\u884c\u6392\u5e8f\u3002<\/p>\n\n
\u65b0\u5efa\u4e00\u4e2a\u5df2\u7ecf\u8c03\u6574\u5217\u987a\u5e8f\u7684\u8868\u683c\uff0c<\/ol>\n<\/li>\n<\/ol>\n
\u66f4\u65b0\u6570\u636e\u5e76\u5220\u9664\u65e7\u8868\u3002<\/ol>\n
\n\u3010\u63d2\u5165\u3011<\/h1>\n
\n\u63d2\u5165\u8bed\u6cd5\uff1ainsert into \u8868\u540d(\u5217\u540d, \u5217\u540d…) values (\u503c, \u503c…), (\u503c, \u503c…)…;<\/p>\n
\n\u63d2\u5165\u6570\u636e\uff1ainsert into users(name, age)
\nvalues (
\n(‘\u7530\u4e2d’, 20),
\n(‘\u94c3\u6728’, 30),
\n(‘\u4f50\u85e4’, 40)
\n);<\/p>\n<\/details>\n
\n\u5411\u8868\u540d (\u5217\u540d, \u5217\u540d…) \u63d2\u5165\u6570\u636e\uff0c\u503c\u4e3a (\u503c, \u503c…)\uff0c(\u503c, \u503c…)…<\/p>\n
\n\u5411 users \u8868\u63d2\u5165\u6570\u636e:
\n(1, ‘\u7530\u4e2d’, 20),
\n(2, ‘\u94c3\u6728’, 30),
\n(3, ‘\u4f50\u85e4’, 40);<\/p>\n<\/details>\n
\n\u6700\u540e\u63d2\u5165\u7684ID<\/h3>\n
\n\u5982\u679c\u5b58\u5728users\u8868\u5219\u5220\u9664;
\n\u5982\u679c\u5b58\u5728posts\u8868\u5219\u5220\u9664;<\/p>\n
\nid int unsigned \u4e3b\u952e \u81ea\u589e,
\nname varchar(255)<\/p>\n
\nid int unsigned \u4e3b\u952e \u81ea\u589e,
\nuser_id int unsigned \u975e\u7a7a<\/p>\n
\n\u5916\u952e(user_id) \u53c2\u8003users\u8868\u4e2d\u7684id<\/p>\n
\nname\u4e3a”a”\u7684\u8bb0\u5f55,
\nname\u4e3a”b”\u7684\u8bb0\u5f55,
\nname\u4e3a”c”\u7684\u8bb0\u5f55<\/p>\n
\nuser_id\u4e3a1\u7684\u8bb0\u5f55,
\nuser_id\u4e3a1\u7684\u8bb0\u5f55,
\nuser_id\u4e3a3\u7684\u8bb0\u5f55<\/p>\n
\nuser_id\u4e3a\u6700\u8fd1\u63d2\u5165\u7684\u8bb0\u5f55\u7684ID(\u53733)<\/p>\n
\n\u67e5\u8be2posts\u8868\u4e2d\u7684\u6240\u6709\u8bb0\u5f55<\/p>\n
\n| id | name |
\n+—-+——+
\n| 1 | a |
\n| 3 | c |
\n+—-+——+
\n\u51712\u6761\u8bb0\u5f55(0.00\u79d2)<\/p>\n
\n| id | user_id |
\n+—-+———+
\n| 1 | 1 |
\n| 2 | 1 |
\n| 3 | 3 |
\n| 4 | 3 |
\n+—-+———+
\n\u51714\u6761\u8bb0\u5f55(0.01\u79d2)<\/p>\n<\/details>\n\u5728\u63d2\u5165\/\u66f4\u65b0\u65f6\u523b\u521b\u5efa\u8bb0\u5f55<\/h3>\n
\n– id\uff1a\u65e0\u7b26\u53f7\u6574\u6570\u578b\uff0c\u4e3b\u952e\uff0c\u81ea\u52a8\u9012\u589e
\n– name\uff1a\u5b57\u7b26\u4e32\u578b\uff0c\u957f\u5ea6255
\n– created_at\uff1a\u65e5\u671f\u65f6\u95f4\u578b\uff0c\u9ed8\u8ba4\u4e3a\u5f53\u524d\u65f6\u95f4
\n– updated_at\uff1a\u65e5\u671f\u65f6\u95f4\u578b\uff0c\u9ed8\u8ba4\u4e3a\u5f53\u524d\u65f6\u95f4\uff0c\u5728\u66f4\u65b0\u65f6\u81ea\u52a8\u66f4\u65b0<\/p>\n
\n\u5411users\u8868\u63d2\u5165\u6570\u636e\uff0cname\u4e3a’name2’\u7684\u8bb0\u5f55\u3002
\n\u5411users\u8868\u63d2\u5165\u6570\u636e\uff0cname\u4e3a’name3’\u7684\u8bb0\u5f55\u3002<\/p>\n
\n| id | name | created_at | updated_at |
\n+—-+————-+———————+———————+
\n| 1 | name1 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
\n| 2 | update name | 2020-09-17 21:16:12 | 2020-09-17 21:16:17 | — \u66f4\u65b0\u65f6\u95f4\u4f1a\u66f4\u65b0\u4e3a\u8bb0\u5f55\u66f4\u65b0\u65f6\u7684\u65f6\u95f4
\n| 3 | name3 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
\n+—-+————-+———————+———————+
\n\u51713\u884c (0.00 \u79d2)<\/p>\n
\n.
\n.<\/p>\n
\n| id | name | created_at | updated_at |
\n+—-+————-+———————+———————+
\n| 1 | name1 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
\n| 2 | update name | 2020-09-17 21:16:12 | 2020-09-17 21:16:17 | — \u66f4\u65b0\u65f6\u95f4\u4f1a\u66f4\u65b0\u4e3a\u8bb0\u5f55\u66f4\u65b0\u65f6\u7684\u65f6\u95f4
\n| 3 | name3 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
\n+—-+————-+———————+———————+
\n\u51713\u884c (0.00 \u79d2)<\/p>\n<\/details>\n
\n\u3010\u9009\u62e9\u3011<\/h1>\n
\n\u5f9e\u8cc7\u6599\u8868\u4e2d\u9078\u53d6\u6b04\u4f4d\u540d\u7a31\u70ba\u300c\u62bd\u51fa\u3059\u308b\u30ab\u30e9\u30e0\u300d\u7684\u8cc7\u6599\u3002<\/p>\n
\n\u5f9eusers\u8cc7\u6599\u8868\u4e2d\u9078\u53d6\u6240\u6709\u8cc7\u6599\u3002
\n\u5f9eusers\u8cc7\u6599\u8868\u4e2d\u9078\u53d6\u540d\u7a31\u548c\u5206\u6578\u6b04\u4f4d\u7684\u8cc7\u6599\u3002<\/p>\n<\/details>\n
\n\u91cd\u65b0\u6392\u5e8f<\/h3>\n
\n\u683c\u5f0f<\/p>\n
\norder by \u5217\u540d desc;<\/p>\n
\norder by \u5217\u540d asc;<\/p>\n
\nlimit \u62bd\u53d6\u6570\u76ee offset \u4ece\u7b2cn\u4e2a\u4f4d\u7f6e\u5f00\u59cb;<\/p>\n
\nselect * from users order by score;
\nselect * from users order by score asc;<\/p>\n
\nselect * from users order by score desc;<\/p>\n
\nselect * from users limit 3 offset 3;
\n\/* \u5206\u6570\u6392\u540d\u524d3 *\/
\nselect * from users order by score limit 3;
\n\/* \u5206\u6570\u6392\u540d\u7b2c4\u5230\u7b2c6 *\/
\nselect * from users order by score limit 3 offset 3;<\/p>\n
\nselect * from users where score is null;<\/p>\n
\nselect * from users where score is not null;<\/p>\n<\/details>\n
\n\u67e5\u770b\u5728\u62bd\u53d6\u65f6\u4f7f\u7528\u7684\u7d22\u5f15\u3002<\/h3>\n
explain \u62bd\u51fa\u6587;\r\n<\/code><\/pre>\n
\n———————————————————-
\n\u6765\u81ea users \u8868\u7684\u987a\u5e8f\u626b\u63cf (\u6210\u672c=0.00..10.88\uff0c\u884c\u6570=23\uff0c\u5bbd\u5ea6=1040)
\n\u8fc7\u6ee4\u6761\u4ef6: (id > 1)
\n(2 \u884c)<\/p>\n<\/details>\n
\n| \u8868 | \u975e\u552f\u4e00 | \u5173\u952e\u540d\u79f0 | \u7d22\u5f15\u4e2d\u7684\u987a\u5e8f | \u5217\u540d\u79f0 | \u987a\u5e8f | \u57fa\u6570 | \u5b50\u90e8\u5206 | \u6253\u5305 | \u7a7a\u503c | \u7d22\u5f15\u7c7b\u578b | \u8bc4\u8bba | \u7d22\u5f15\u8bc4\u8bba | \u53ef\u89c1 | \u8868\u8fbe\u5f0f |
\n+——-+————+————-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
\n| \u7528\u6237 | 0 | \u4e3b\u952e | 1 | ID | A | 0 | NULL | NULL | | BTREE | | | \u662f | NULL |
\n| \u7528\u6237 | 1 | \u5206\u6570\u7d22\u5f15 | 1 | \u5206\u6570 | A | 0 | NULL | NULL | \u662f | BTREE | | | \u662f | NULL |
\n+——-+————+————-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
\n\u4e24\u884c\u663e\u793a\u7ed3\u679c\uff080.00\u79d2\uff09<\/p>\n<\/details>\n
\n\u3010\u54ea\u91cc\u3011<\/h1>\n
\n\u4ece\u8868\u540d\u9009\u62e9\u5217\u540d \u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55;<\/p>\n
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u6ee1\u8db3\u5206\u6570\u5927\u4e8e1\u7684\u8bb0\u5f55\uff1b
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u6ee1\u8db3\u5206\u6570\u5927\u4e8e2.0\u4e14\u56e2\u961f\u4e3a’red’\u7684\u8bb0\u5f55\uff1b
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u56e2\u961f\u4e3a’red’\u6216\u8005’blue’\u7684\u8bb0\u5f55\uff1b<\/p>\n
\n\u610f\u4e49<\/p>\n
\n\u5927\u4e8e<\/p>\n
\n\u5927\u4e8e\u7b49\u4e8e<\/p>\n
\n\u5c0f\u4e8e\u7b49\u4e8e<\/p>\n
\n\u7b49\u4e8e<\/p>\n
\n\u4e0d\u7b49\u4e8e<\/p>\n
\n\u4e0d\u7b49\u4e8e<\/p>\n
\n— and
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u5206\u6570\u5927\u4e8e\u7b49\u4e8e3.0\u4e14\u5c0f\u4e8e\u7b49\u4e8e6.0\u7684\u8bb0\u5f55\uff1b
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u5206\u6570\u8303\u56f4\u57283.0\u548c6.0\u4e4b\u95f4\u7684\u8bb0\u5f55\uff1b<\/p>\n
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u540d\u79f0\u4e3a’taguchi’\u6216\u8005’fkoji’\u7684\u8bb0\u5f55\uff1b
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u540d\u79f0\u5728’taguchi’\u548c’fkoji’\u4e4b\u95f4\u7684\u8bb0\u5f55\uff1b<\/p>\n
\nPostgreSQL \/ MySQL<\/p>\n
\nMySQL\u4e0d\u533a\u5206\u5927\u5c0f\u5199<\/p>\n
\n\u610f\u4e49<\/p>\n
\n\u5339\u914d\u4efb\u610f1\u4e2a\u5b57\u7b26<\/p>\n
\n\u5339\u914d0\u4e2a\u6216\u591a\u4e2a\u5b57\u7b26<\/p>\n
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u540d\u79f0\u4ee5\u5927\u5199\u5b57\u6bcdA\u5f00\u5934\u7684\u8bb0\u5f55\uff1b<\/p>\n
\n\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u540d\u79f0\u4ee5\u5927\u5199\u5b57\u6bcdA\u5f00\u5934\u7684\u8bb0\u5f55\uff1b<\/p>\n<\/details>\n
\n\u3010\u8bb0\u5f55\u6c47\u603b\u3011<\/h1>\n
\u56db\u5219\u8fd0\u7b97<\/h3>\n
\n\u610f\u4e49+
\n\u52a0\u6cd5<\/p>\n
\n\u51cf\u6cd5<\/p>\n
\n\u4e58\u6cd5<\/p>\n
\n\u9664\u6cd5<\/p>\n
\n\u53d6\u4f59<\/p>\n
\nselect * from users where id % 2 = 0;<\/p>\n
\nupdate users set score = score * 1.2 where id % 2 = 0;<\/p>\n<\/details>\n
\n\u7edf\u8ba1\u51fd\u6570<\/h3>\n
\n\u64cd\u4f5c<\/p>\n
\n\u8bb0\u5f55\u6570<\/p>\n
\n\u6570\u636e\u7c7b\u578b<\/p>\n
\n\u603b\u8ba1<\/p>\n
\n\u5e73\u5747\u503c<\/p>\n
\n\u6700\u5927\u503c<\/p>\n
\n\u6700\u5c0f\u503c<\/p>\n
\n\u4ece\u7528\u6237\u4e2d\u9009\u62e9\u8ba1\u6570\uff08ID\uff09\uff1b
\n\u4ece\u7528\u6237\u4e2d\u9009\u62e9\u8ba1\u6570\uff08*\uff09\uff1b<\/p>\n
\n\u4ece\u7528\u6237\u4e2d\u9009\u62e9\u6700\u5c0f\u503c\uff08\u79ef\u5206\uff09\uff1b
\n\u4ece\u7528\u6237\u4e2d\u9009\u62e9\u6700\u5927\u503c\uff08\u79ef\u5206\uff09\uff1b
\n\u4ece\u7528\u6237\u4e2d\u9009\u62e9\u5e73\u5747\u503c\uff08\u79ef\u5206\uff09\uff1b<\/p>\n
\n\u6309\u7167……\u5206\u7ec4 ……<\/h3>\n
\n\u7ec4 | \u603b\u548c
\n——-+———–
\n\u84dd | 8.3
\n\u7ea2 | 12.299999
\n\u7eff | 9.7<\/p>\n<\/details>\n
\n\u8bf7\u63d0\u4f9b\u66f4\u591a\u7684\u4e0a\u4e0b\u6587\u4ee5\u66f4\u51c6\u786e\u5730\u7ffb\u8bd1\u3002”HAVING\u53e5”\u5728\u4e2d\u6587\u4e2d\u6ca1\u6709\u660e\u786e\u7684\u5bf9\u5e94\u77ed\u8bed\u3002<\/h3>\n
\u96c6\u8a08\u5f8c\u306e\u30c7\u30fc\u30bf\u306b\u5bfe\u3057\u3066\u306e\u7d5e\u308a\u8fbc\u307f<\/ul>\n
\n\u6982\u8981<\/p>\n
\n\u4eceSELECT\u53e5\u7684\u7ed3\u679c\u4e2d\u8fdb\u884c\u7b5b\u9009<\/p>\n
\n\u4ece\u4f7f\u7528group by\u8fdb\u884c\u805a\u5408\u7684\u7ed3\u679c\u4e2d\u8fdb\u884c\u7b5b\u9009<\/p>\n
\nselect team, sum(score) from users group by team having sum(score) > 10.0;<\/p>\n
\nselect sum(score), team from users_with_team where id > 3 group by team;<\/p>\n<\/details>\n\u8ba1\u7b97\u65f6\u95f4<\/h3>\n
\n
date_add()<\/ul>\n<\/li>\n<\/ul>\n
date_format()<\/ul>\n
\nid int unsigned primary key auto_increment,
\nname varchar(255),
\ncreated_at datetime \u9ed8\u8ba4 current_timestamp,
\nupdated_at datetime \u9ed8\u8ba4 current_timestamp on update current_timestamp
\n);<\/p>\n
\n\u5411users\u63d2\u5165\u6570\u636e (name) values (‘name2’);
\n\u5411users\u63d2\u5165\u6570\u636e (name) values (‘name3’);<\/p>\n
\n+—-+——-+———————+———————+
\n| id | name | created_at | updated_at |
\n+—-+——-+———————+———————+
\n| 1 | name1 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 |
\n| 2 | name2 | 2019-09-17 10:00:00 | 2020-09-17 21:35:37 |
\n| 3 | name3 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 |
\n+—-+——-+———————+———————+
\n\u5171 3 \u884c (0.00 sec)<\/p>\n
\n+—-+——-+———————+———————+
\n| id | name | created_at | updated_at |
\n+—-+——-+———————+———————+
\n| 1 | name1 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 |
\n| 3 | name3 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 |
\n+—-+——-+———————+———————+
\n\u5171 2 \u884c (0.00 sec)<\/p>\n
\n+———————+—————————————+
\n| created_at | date_add(created_at, interval 21 day) |
\n+———————+—————————————+
\n| 2020-09-17 21:35:37 | 2020-10-08 21:35:37 |
\n| 2019-09-17 10:00:00 | 2019-10-08 10:00:00 |
\n| 2020-09-17 21:35:37 | 2020-10-08 21:35:37 |
\n+———————+—————————————+
\n\u5171 3 \u884c (0.00 sec)<\/p>\n
\n+———————+—————————————+
\n| created_at | date_add(created_at, interval 3 week) |
\n+———————+—————————————+
\n| 2020-09-17 21:35:37 | 2020-10-08 21:35:37 |
\n| 2019-09-17 10:00:00 | 2019-10-08 10:00:00 |
\n| 2020-09-17 21:35:37 | 2020-10-08 21:35:37 |
\n+———————+—————————————+
\n\u5171 3 \u884c (0.00 sec)<\/p>\n
\n+———————+————————————-+
\n| created_at | date_format(created_at, ‘%W %M %Y’) |
\n+———————+————————————-+
\n| 2020-09-17 21:35:37 | Thursday September 2020 |
\n| 2019-09-17 10:00:00 | Tuesday September 2019 |
\n| 2020-09-17 21:35:37 | Thursday September 2020 |
\n+———————+————————————-+
\n\u5171 3 \u884c (0.00 sec)<\/p>\n<\/details>\n
\n\u51fd\u6570<\/h1>\n
\n\u9078\u64c7 name, length(name) from users;<\/p>\n
\nname | \u9577\u5ea6
\n————+——–
\ntaguchi | 7
\nfkoji | 5
\ndotinstall | 10
\nsasaki | 6
\nsasaki | 6
\nkimura | 6
\n(6 rows)<\/p>\n
\n\u9078\u64c7 concat(name, ‘(‘, team, ‘)’) as from users;<\/p>\n
\n\u4e32\u806f
\n—————–
\ntaguchi(red)
\nfkoji(blue)
\ndotinstall(red)
\nsasaki(green)
\nsasaki(red)
\nkimura(green)
\n(6 rows)<\/p>\n
\nblogapp=# \u9078\u64c7 concat(name, ‘(‘, team, ‘)’) as nameteam from users;
\nnameteam
\n—————–
\ntaguchi(red)
\nfkoji(blue)
\ndotinstall(red)
\nsasaki(green)
\nsasaki(red)
\nkimura(green)
\n(6 rows)<\/p>\n
\nblogapp=# \u9078\u64c7 substring(team, 1, 1) as teaminitiale from users;<\/p>\n
\nteaminitiale
\n————–
\nr
\nb
\nr
\ng
\nr
\ng
\n(6 rows)<\/p>\n
\nblogapp=# \u9078\u64c7 * from users order by random() limit 1;<\/p>\n
\nid | name | score | team
\n—-+——-+——-+——
\n2 | fkoji | 8.3 | blue
\n(1 row)<\/p>\n<\/details>\n
\n
\nselect round(5.355); — 5
\nselect round(5.355, 1); — 5.4 \u6307\u5b9a\u7b2c\u4e8c\u4e2a\u53c2\u6570\u4e3a\u8981\u663e\u793a\u7684\u5c0f\u6570\u4f4d\u6570<\/p>\n
\nselect floor(5.833); — 5
\nselect floor(5.238); — 6<\/p>\n
\nselect rand(); — \u968f\u673a\u6570
\nselect * from users order by rand() limit 1; — \u968f\u673a\u83b7\u53d6\u4e00\u6761\u8bb0\u5f55<\/p>\n
\nselect length(‘Hello’); — 5<\/p>\n
\nselect substring(‘Hello’, 2); — ello
\nselect substring(‘Hello’, 2, 3); — ell<\/p>\n
\nselect upper(‘Hello’); — HELLO<\/p>\n
\nselect lower(‘Hello’); — hello<\/p>\n
\nselect concat(‘Hello’, ‘World’); — HelloWorld<\/p>\n
\nselect length(name) as len, name from users order by len;<\/p>\n
\n| len | name |
\n+——+————+
\n| 5 | fkoji |
\n| 6 | Tanaka |
\n| 6 | yamada |
\n| 7 | taguchi |
\n| 7 | tashiro |
\n| 10 | dotinstall |
\n+——+————+
\n6 rows in set (0.00 sec)<\/p>\n<\/details>\n
\n\u3010\u5206\u5c90\u6761\u4ef6\u3011<\/h1>\n
\u5982\u679c<\/h3>\n
\n\u5982\u679c\u6709users\u8868\uff0c\u5219\u5220\u9664users\u8868\uff1b<\/p>\n
\n\u59d3\u540d\uff0c
\n\u5206\u6570\uff0c
\n\u5982\u679c\uff08\u5206\u6570>5.0\uff0c’\u5408\u683c’\uff0c’\u4e0d\u5408\u683c’\uff09\u4f5c\u4e3a\u7ed3\u679c
\n\uff1b<\/p>\n
\n| \u59d3\u540d | \u5206\u6570 | \u7ed3\u679c |
\n+————+——-+——–+
\n| \u7530\u53e3 | 5.8 | \u5408\u683c |
\n| fkoji | 8.2 | \u5408\u683c |
\n| dotinstall | 6.1 | \u5408\u683c |
\n| \u7530\u4e2d | 4.2 | \u4e0d\u5408\u683c |
\n| \u5c71\u7530 | NULL | \u4e0d\u5408\u683c |
\n| \u7530\u4ee3 | 7.9 | \u5408\u683c |
\n+————+——-+——–+
\n6 \u884c (0.00 \u79d2)<\/p>\n<\/details>\n
\n\u6848\u4f8b\u6587\u4ef6<\/h3>\n
\n\u9009\u62e9
\n\u59d3\u540d,
\n\u5206\u6570,
\ncase floor(score) % 2
\n\u5f53\u4e3a0\u65f6\u5219\u4e3a ‘\u5076\u6570’
\n\u5f53\u4e3a1\u65f6\u5219\u4e3a ‘\u5947\u6570’
\n\u5176\u4ed6\u60c5\u51b5\u4e0b\u4e3a null
\n\u7ed3\u679c\u5c06\u540d\u4e3a \u7c7b\u578b
\n\u4ece
\n\u7528\u6237\u8868\u4e2d;<\/p>\n
\n| \u59d3\u540d | \u5206\u6570 | \u7c7b\u578b |
\n+————+——-+——+
\n| \u7530\u53e3 | 5.8 | \u5947\u6570 |
\n| fkoji | 8.2 | \u5076\u6570 |
\n| dotinstall | 6.1 | \u5076\u6570 |
\n| \u7530\u4e2d | 4.2 | \u5076\u6570 |
\n| \u5c71\u7530 | NULL | NULL |
\n| \u7530\u4ee3 | 7.9 | \u5947\u6570 |
\n+————+——-+——+
\n6 \u884c\u8bb0\u5f55 (0.00 \u79d2)<\/p>\n
\n\u9009\u62e9
\n\u59d3\u540d,
\n\u5206\u6570,
\ncase
\n\u5f53\u5206\u6570 > 8.0\u65f6\u5219\u4e3a ‘\u56e2\u961f-A’
\n\u5f53\u5206\u6570 > 6.0\u65f6\u5219\u4e3a ‘\u56e2\u961f-B’
\n\u5176\u4ed6\u60c5\u51b5\u4e0b\u4e3a ‘\u56e2\u961f-C’
\n\u7ed3\u679c\u5c06\u540d\u4e3a \u56e2\u961f
\n\u4ece
\n\u7528\u6237\u8868\u4e2d;<\/p>\n
\n| \u59d3\u540d | \u5206\u6570 | \u56e2\u961f |
\n+————+——-+——–+
\n| \u7530\u53e3 | 5.8 | \u56e2\u961f-C |
\n| fkoji | 8.2 | \u56e2\u961f-A |
\n| dotinstall | 6.1 | \u56e2\u961f-B |
\n| \u7530\u4e2d | 4.2 | \u56e2\u961f-C |
\n| \u5c71\u7530 | NULL | \u56e2\u961f-C |
\n| \u7530\u4ee3 | 7.9 | \u56e2\u961f-B |
\n+————+——-+——–+
\n6 \u884c\u8bb0\u5f55 (0.00 \u79d2)<\/p>\n<\/details>\n
\n\u3010\u66f4\u65b0\u3011<\/h1>\n
\n\u3010\u5220\u9664\u3011<\/h1>\n
\n\u5220\u9664 users\u8868;<\/p>\n
\n\u5220\u9664 users\u8868\u4e2d score \u5c0f\u4e8e 4.0 \u7684\u8bb0\u5f55;
\n\u5220\u9664 1\u4e2a\u8bb0\u5f55<\/p>\n<\/details>\n
\n\u3010\u5904\u7406\u591a\u5f20\u684c\u5b50\u3011<\/h1>\n
\n\u4ece \u8868A\uff0c\u8868B \u4e2d\u9009\u62e9 \u8868A.\u5217\u540d\uff0c\u8868B.\u5217\u540d
\n\u901a\u8fc7 \u8868A, \u8868B \u8fdb\u884c\u8fde\u63a5\uff0c\u4ee5 \u8868A.\u5217\u540d = \u8868B.\u5217\u540d \u4f5c\u4e3a\u6761\u4ef6
\n\/* \u5728where\u5b50\u53e5\u4e2d\u6307\u5b9a\u8868A\u548c\u8868B\u4e2d\u54ea\u4e9b\u5217\u88ab\u89c6\u4e3a\u76f8\u540c\uff0c\u5e76\u521b\u5efa\u8868\u4e4b\u95f4\u7684\u8fde\u63a5*\/<\/p>\n
\n\u4ece \u7528\u6237\u8868\uff0c\u5e16\u5b50\u8868 \u4e2d\u9009\u62e9 \u7528\u6237\u8868.\u59d3\u540d\uff0c\u5e16\u5b50\u8868.\u6807\u9898
\n\u901a\u8fc7 \u7528\u6237\u8868\uff0c\u5e16\u5b50\u8868 \u8fdb\u884c\u8fde\u63a5\uff0c\u4ee5 \u7528\u6237\u8868.\u7f16\u53f7 = \u5e16\u5b50\u8868.\u7528\u6237\u7f16\u53f7 \u4f5c\u4e3a\u6761\u4ef6
\n\/* \u5c06\u7528\u6237\u8868\u548c\u5e16\u5b50\u8868\u8fde\u63a5\u8d77\u6765\uff0c\u8fde\u63a5\u6761\u4ef6\u662f\u7528\u6237\u8868\u4e2d\u7684\u7f16\u53f7\u4e0e\u5e16\u5b50\u8868\u4e2d\u7684\u7528\u6237\u7f16\u53f7\u76f8\u540c*\/<\/p>\n
\n\/* \u4f7f\u7528\u522b\u540d\u7f29\u77ed\u8bed\u53e5\uff0c\u5728from\u5b50\u53e5\u4e2d\u4e3a\u8868\u547d\u540d\u522b\u540d *\/
\n\/* from\u5b50\u53e5\u662f\u6700\u5148\u6267\u884c\u7684\uff0c\u6240\u4ee5\u522b\u540d\u5728from\u5b50\u53e5\u4e2d\u5b9a\u4e49 *\/
\n\u9009\u62e9 u.\u59d3\u540d, p.\u6807\u9898 \u4ece \u7528\u6237\u8868 u, \u5e16\u5b50\u8868 p where u.\u7f16\u53f7 = p.\u7528\u6237\u7f16\u53f7;<\/p>\n
\n\u9009\u62e9 u.\u59d3\u540d, p.\u6807\u9898 \u4ece \u7528\u6237\u8868 u, \u5e16\u5b50\u8868 p where u.\u7f16\u53f7 = p.\u7528\u6237\u7f16\u53f7 and u.\u7f16\u53f7 = 1;<\/p>\n<\/details>\n
\n\u4f7f\u7528view<\/h1>\n
\u521b\u5efa\u89c6\u56fe<\/h3>\n
\n\u89c2\u70b9\u4f7f\u7528<\/h3>\n
\n\u67e5\u770b\u5220\u9664<\/h3>\n
\n\u3010\u4ea4\u6613\u3011<\/h1>\n
\n\/* \u5904\u7406 *\/
\n\u56de\u6eda\uff1b — \u53d6\u6d88
\n\u63d0\u4ea4\uff1b — \u5b8c\u6210<\/p>\n<\/details>\n
\n\/* \u5904\u7406 *\/
\n\u56de\u6eda\uff1b– \u53d6\u6d88
\n\u63d0\u4ea4\uff1b– \u5b8c\u6210<\/p>\n<\/details>\n
\n\u3010\u5b50\u67e5\u8be2\u3011<\/h1>\n
\n\u4ece
\n(\u9009\u62e9 id, name, score, — \u4ece\u5b50\u67e5\u8be2\u5f00\u59cb
\n\u60c5\u51b5
\n\u5f53\u5f97\u5206 > 8.0 \u5219 ‘A\u961f’
\n\u5f53\u5f97\u5206 > 6.0 \u5219 ‘B\u961f’
\n\u5426\u5219 ‘C\u961f’
\n\u4f5c\u4e3a Team
\n\u4ece\u7528\u6237) as t — \u5230\u6b64\u4e3a\u6b62\u3002
\n\u6309 t.team \u5206\u7ec4\uff1b+——————-+——–+
\n| sum(t.score) | Team |
\n+——————-+——–+
\n| 10 | C\u961f |
\n| 8.199999809265137 | A\u961f |
\n| 14 | B\u961f |
\n+——————-+——–+
\n3 \u884c\u7ed3\u679c (0.00 \u79d2)<\/p>\n<\/details>\n\u3010\u8868\u683c\u7684\u8fde\u63a5\u3011<\/h1>\n
select<\/span> *<\/span> from<\/span> posts<\/span>;<\/span>\r\n\r\n+<\/span>----+---------+--------+<\/span>\r\n|<\/span> id<\/span> |<\/span> title<\/span> |<\/span> body<\/span> |<\/span>\r\n+<\/span>----+---------+--------+<\/span>\r\n|<\/span> 1<\/span> |<\/span> title<\/span> 1<\/span> |<\/span> body<\/span> 1<\/span> |<\/span>\r\n|<\/span> 2<\/span> |<\/span> title<\/span> 2<\/span> |<\/span> body<\/span> 2<\/span> |<\/span>\r\n|<\/span> 3<\/span> |<\/span> title<\/span> 3<\/span> |<\/span> body<\/span> 3<\/span> |<\/span>\r\n+<\/span>----+---------+--------+<\/span>\r\n\r\nselect<\/span> *<\/span> from<\/span> comments<\/span>;<\/span>\r\n+<\/span>----+---------+---------------------------+<\/span>\r\n|<\/span> id<\/span> |<\/span> post_id<\/span> |<\/span> body<\/span> |<\/span>\r\n+<\/span>----+---------+---------------------------+<\/span>\r\n|<\/span> 1<\/span> |<\/span> 1<\/span> |<\/span> first<\/span> comment<\/span> for<\/span> post<\/span> 1<\/span> |<\/span>\r\n|<\/span> 2<\/span> |<\/span> 1<\/span> |<\/span> second<\/span> comment<\/span> for<\/span> post<\/span> 1<\/span> |<\/span>\r\n|<\/span> 3<\/span> |<\/span> 3<\/span> |<\/span> first<\/span> comment<\/span> for<\/span> post<\/span> 3<\/span> |<\/span>\r\n|<\/span> 4<\/span> |<\/span> 4<\/span> |<\/span> first<\/span> comment<\/span> for<\/span> post<\/span> 4<\/span> |<\/span>\r\n+<\/span>----+---------+---------------------------+<\/span>\r\n<\/code><\/pre>\n
\n\u5185\u8fde\u63a5<\/h3>\n
\u30c6\u30fc\u30d6\u30eb\u540c\u58eb\u5171\u901a\u306e\u30c7\u30fc\u30bf\u3060\u3051\u3092\u53d6\u5f97\u3059\u308b<\/ul>\n
\nSELECT * FROM posts JOIN comments ON posts.id = comments.post_id;
\n“`<\/p>\n
\nSELECT posts.id, title, body AS posts_body, comments.body AS comments_body FROM posts JOIN comments ON posts.id = comments.post_id;
\n“`<\/p>\n
\n\u5168\u5916\u8fde\u63a5<\/h3>\n
\u7247\u65b9\u306b\u3057\u304b\u30ec\u30b3\u30fc\u30c9\u304c\u306a\u3044\u5834\u5408\u3067\u3082\u62bd\u51fa\u3059\u308b<\/ul>\n
\nselect * from posts left join comments on posts.id = comments.post_id;<\/p>\n
\n| id | title | body | id | post_id | body |
\n+—-+———+——–+——+———+—————————+
\n| 1 | title 1 | body 1 | 1 | 1 | first comment for post 1 |
\n| 1 | title 1 | body 1 | 2 | 1 | second comment for post 1 |
\n| 2 | title 2 | body 2 | NULL | NULL | NULL |
\n| 3 | title 3 | body 3 | 3 | 3 | first comment for post 3 |
\n+—-+———+——–+——+———+—————————+
\n4 \u884c\u53d7\u5230\u5f71\u54cd (0.01 \u79d2)
\n“`<\/p>\n
\nselect * from posts right join comments on posts.id = comments.post_id;<\/p>\n
\n| id | title | body | id | post_id | body |
\n+——+———+——–+—-+———+—————————+
\n| 1 | title 1 | body 1 | 1 | 1 | first comment for post 1 |
\n| 1 | title 1 | body 1 | 2 | 1 | second comment for post 1 |
\n| 3 | title 3 | body 3 | 3 | 3 | first comment for post 3 |
\n| NULL | NULL | NULL | 4 | 4 | first comment for post 4 |
\n+——+———+——–+—-+———+—————————+
\n4 \u884c\u53d7\u5230\u5f71\u54cd (0.00 \u79d2)
\n“`<\/p>\n\u5916\u90e8\u952e\u7ea6\u675f<\/h1>\n
\u6dfb\u52a0\u5916\u952e\u7ea6\u675f<\/h3>\n
\n
\u5916\u90e8\u30ad\u30fc\u5236\u7d04\u3092\u4ed8\u3051\u308b\u30ab\u30e9\u30e0\u306f\u3001\u7d10\u3065\u304f\u30ab\u30e9\u30e0\u3068\u540c\u3058\u578b\u306b\u3059\u308b\u5fc5\u8981\u304c\u3042\u308b<\/ul>\n<\/li>\n<\/ul>\n
\n
\u7d10\u3065\u304f\u30ec\u30b3\u30fc\u30c9\u304c\u5b58\u5728\u3057\u306a\u3044\u5834\u5408\u30ec\u30b3\u30fc\u30c9\u3092\u633f\u5165\u3067\u304d\u306a\u3044<\/ul>\n<\/li>\n<\/ul>\n
\u30ab\u30e9\u30e0\u306b\u5916\u90e8\u30ad\u30fc\u5236\u7d04\u304c\u4ed8\u3044\u3066\u3044\u308b\u5834\u5408delete\u3067\u524a\u9664\u3067\u304d\u306a\u304f\u306a\u308b<\/ul>\n
\n**\u524d\u63d0**
\n\u521b\u5efa\u8868users (
\nid int unsigned primary key auto_increment,
\nname varchar(255)
\n);\u521b\u5efa\u8868posts (
\nid int unsigned primary key auto_increment,
\nuser_id int unsigned not null
\n);<\/p>\n
\n\/* \u5217A\u5c06\u4e0e\u8868A\u4e2d\u7684\u5217B\u76f8\u5173\u8054 *\/
\n\/* \u8868B\u662f\u8981\u76f8\u5173\u8054\u7684\u8868\u540d *\/
\n\/* \u5217B\u662f\u8981\u76f8\u5173\u8054\u7684\u5217\u540d *\/
\nalter table \u8868A add constraint \u952e\u540d foreign key (\u5217A) references \u8868B (\u5217B);<\/p>\n
\nalter table posts add constraint fk_posts foreign key (user_id) references users (id);<\/p>\n<\/details>\n
\n\u5220\u9664\u5916\u952e\u7ea6\u675f<\/h3>\n
\n\u4fee\u6539\u8868 `\u8868\u540d` \u5220\u9664\u5916\u952e `\u952e\u540d`\uff1b<\/p>\n
\n\u4fee\u6539\u8868 posts \u5220\u9664\u5916\u952e fk_posts\uff1b<\/p>\n<\/details>\n\u3010\u89e6\u53d1\u5668\u3011<\/h1>\n
\n
\u30c8\u30ea\u30ac\u30fc\u8d77\u52d5\u6642\u306b\u5b9f\u884c\u3055\u308c\u308bSQL\u6587\u306b\u304a\u3044\u3066\u3001old.\u30ab\u30e9\u30e0\u540d\u3068NEW.\u30ab\u30e9\u30e0\u540d\u3092\u4f7f\u3063\u3066\u53c2\u7167\u53ef\u80fd\u3002<\/ul>\n<\/li>\n<\/ul>\n
\n
\u30c8\u30ea\u30ac\u30fc\u64cd\u4f5c\u304cINSERT\u306e\u5834\u5408\u306fNEW.\u30ab\u30e9\u30e0\u540d\u3067\u65b0\u3057\u304f\u8ffd\u52a0\u3055\u308c\u305f\u30c7\u30fc\u30bf\u306e\u5024\u3092\u53c2\u7167\u3067\u304d\u308b\u3002<\/ul>\n<\/li>\n<\/ul>\n
\n
\u30c8\u30ea\u30ac\u30fc\u64cd\u4f5c\u304cDELETE\u306e\u5834\u5408\u306f OLD.\u30ab\u30e9\u30e0\u540d\u3067\u524a\u9664\u3055\u308c\u305f\u30c7\u30fc\u30bf\u306e\u5024\u3092\u53c2\u7167\u3067\u304d\u308b\u3002<\/ul>\n<\/li>\n<\/ul>\n
\u30c8\u30ea\u30ac\u30fc\u64cd\u4f5c\u64cd\u4f5c\u304cUPDATE\u306e\u5834\u5408\u306fOLD.\u30ab\u30e9\u30e0\u540d\u3068NEW.\u30ab\u30e9\u30e0\u540d\u306e\u4e21\u65b9\u304c\u4f7f\u7528\u3067\u304d\u66f4\u65b0\u3055\u308c\u308b\u524d\u306e\u5024\u3068\u66f4\u65b0\u3055\u308c\u305f\u5f8c\u306e\u5024\u3092\u53c2\u7167\u3067\u304d\u308b\u3002<\/ul>\n
\u89f8\u767c\u5668\u5efa\u7acb<\/h3>\n
\n\u521b\u5efa\u4e00\u4e2a\u5728\u63d2\u5165\u6570\u636e\u540e\u5c06\u76f8\u5e94\u4fe1\u606f\u63d2\u5165\u65e5\u5fd7\u8868\u7684\u89e6\u53d1\u5668\u3002
\n“`mysql
\nCREATE TRIGGER posts_insert_trigger AFTER INSERT ON posts FOR EACH ROW
\nINSERT INTO logs (msg) VALUES (‘post added!’);
\n“`<\/p>\n
\n\u521b\u5efa\u4e00\u4e2a\u5728\u66f4\u65b0\u7528\u6237\u8868\u65f6\u5c06\u76f8\u5e94\u4fe1\u606f\u63d2\u5165\u65e5\u5fd7\u8868\u7684\u89e6\u53d1\u5668\u3002
\n“`mysql
\nDROP TRIGGER IF EXISTS users_update_logs;
\nDELIMITER \/\/
\nCREATE TRIGGER users_update_logs AFTER UPDATE ON users FOR EACH ROW
\nBEGIN
\nINSERT INTO logs (msg) VALUES (‘users updated’);
\nINSERT INTO logs (msg) VALUES (CONCAT(old.name, ‘->’, new.name));
\nEND \/\/
\nDELIMITER ;
\n“`<\/p>\n
\n\u89e6\u53d1\u5668\u5217\u8868<\/h3>\n
\n\u663e\u793a\u89e6\u53d1\u5668 \\G;<\/details>\n
\n\u89e6\u53d1\u5220\u9664<\/h3>\n