diff options
Diffstat (limited to 'doc/FAQ_chinese_trad')
| -rw-r--r-- | doc/FAQ_chinese_trad | 793 |
1 files changed, 0 insertions, 793 deletions
diff --git a/doc/FAQ_chinese_trad b/doc/FAQ_chinese_trad deleted file mode 100644 index b1908535777..00000000000 --- a/doc/FAQ_chinese_trad +++ /dev/null @@ -1,793 +0,0 @@ - - PostgreSQL 常è¦åé¡ï¼FAQï¼ - - æè¿æ´æ°ï¼2007 å¹´ 2 æ 8 æ¥ ææäº 22:43:13 EST - ä¸æçæè¿æ´æ°ï¼2007 å¹´ 2 æ 12 æ¥ ææä¸ 12:00:04 CST - - ç¶åç¶è·äººå¡ï¼Bruce Momjian (pgman@candle.pha.pa.us) - æ£é«ä¸æçç¶è·äººå¡ï¼éæç(ChaoYi, Kuo)ï¼kuo.chaoyi@gmail.comï¼ - - æ¬ææªçææ°çæ¬å¯ä»¥å¨ - http://www.postgresql.org/files/documentation/faqs/FAQ.html æ¥çã - - èä½æ¥ç³»çµ±å¹³å°ç¸éçåé¡å¯å¨ http://www.postgresql.org/docs/faq/ - 裡æ¾å°çæ¡ã - _________________________________________________________________ - -常è¦åé¡ - - 1.1)PostgreSQL æ¯ä»éº¼ï¼è©²æ麼ç¼é³ï¼ - 1.2)誰æ§å¶å管çPostgreSQL ï¼ - 1.3)PostgreSQLççæ¬æ¯ä»éº¼ï¼ - 1.4)PostgreSQLå¯ä»¥éè¡å¨åªäºä½æ¥ç³»çµ±å¹³å°ä¸ï¼ - 1.5)æå¾åªè£¡è½å¾å°PostgreSQLï¼ - 1.6)ææ°çç PostgreSQL æ¯ä»éº¼ï¼ - 1.7)æå¾åªè£¡è½å¾å°å° PostgreSQL çæ¯æï¼ - 1.8)æå¦ä½æ交ä¸å BUG å ±åï¼ - 1.9)æå¦ä½ç解已ç¥ç BUG ææ«ç¼ºçåè½ï¼ - 1.10)è½å¤ ç²åçææ°ææªæåªäºï¼ - 1.11)ææ該æ樣å¸ç¿ SQL ï¼ - 1.12)å¦ä½æ交è£ä¸ææ¯å å¥éç¼éä¼ï¼ - 1.13)PostgreSQL åå¶ä»è³æ庫系統æ¯èµ·ä¾å¦ä½ï¼ - 1.14)PostgreSQL å¯ä»¥èçæè¿ååå家å¤æå¶çè®ååï¼ - -ç¨æ¶å®¢æ¶ç«¯åé¡ - - 2.1)æåå¯ä»¥ç¨ä»éº¼èªè¨å PostgreSQL é²è¡æºéï¼ - 2.2)æä»éº¼å·¥å·å¯ä»¥æ PostgreSQL ç¨æ¼ Web é é¢ï¼ - 2.3)PostgreSQL ææåå½¢ç¨æ¶çé¢(GUI)åï¼ - -系統管çåé¡ - - 3.1)ææ樣æè½æ PostgreSQL è£å¨ /usr/local/pgsql 以å¤çå°æ¹ï¼ - 3.2)æå¦ä½æ§å¶ä¾èªå¶ä»é»è¦çé£æ¥ï¼ - 3.3)ææ樣調æ´è³æ庫伺æå¨ä»¥ç²å¾æ´å¥½çæ§è½ï¼ - 3.4)PostgreSQL 裡å¯ä»¥ç²å¾ä»éº¼æ¨£ç調試ç¹æ§ï¼ - 3.5)çºä»éº¼å¨è©¦åé£æ¥ç»éææ¶å°ãSorry, too many clientsã è¨æ¯ï¼ - 3.6)PostgreSQL çåç´éç¨æåªäºå§å®¹ï¼ - 3.7)(ä½¿ç¨ PostgreSQL )æéè¦ä½¿ç¨ä»éº¼é»è¦ç¡¬é«ï¼ - -æä½åé¡ - - 4.1) å¦ä½åªé¸æä¸åæ¥è©¢çµæçé å¹¾è¡ï¼ææ¯é¨æ©çä¸è¡ï¼ - 4.2) å¦ä½æ¥ç表ãç´¢å¼ãè³æ庫以åç¨æ¶çå®ç¾©ï¼å¦ä½æ¥ç psql - 裡ç¨å°çæ¥è©¢æ令並顯示å®åï¼ - 4.3) å¦ä½æ´æ¹ä¸åæ¬ä½çè³æé¡åï¼ - 4.4) å®çè¨éï¼å®ä¸è¡¨ï¼ä¸åè³æ庫çæ大éå¶æ¯å¤å°ï¼ - 4.5) åå²ä¸åå¸åçææ¬æ件裡çè³æéè¦å¤å°ç£ç¢ç©ºéï¼ - 4.6) çºä»éº¼æçæ¥è©¢å¾æ¢ï¼çºä»éº¼éäºæ¥è©¢æ²æå©ç¨ç´¢å¼ï¼ - 4.7) æå¦ä½æè½çå°æ¥è©¢åªåå¨æ¯æ樣è©ä¼°èçæçæ¥è©¢çï¼ - 4.8) ææ樣åæ£å表éå¼æç´¢å大å°å¯«ç¡éçæ - £å表éå¼æ¥æ¾ï¼æ樣å©ç¨ç´¢å¼é²è¡å¤§å°å¯«ç¡éæ¥æ¾ï¼ - 4.9) å¨ä¸åæ¥è©¢è£¡ï¼ææ樣檢測ä¸åæ¬ä½æ¯å¦çº - NULLï¼æå¦ä½æè½æºç¢ºæåºèä¸è«ææ¬ä½æ¯å¦å«NULLå¼ï¼ - 4.10) å種å符é¡åä¹éæä»éº¼ä¸åï¼ - 4.11.1) ææ樣åµå»ºä¸ååºåèåææ¯èªåéå¢çæ¬ä½ï¼ - 4.11.2) æå¦ä½ç²å¾ä¸åæå¥çåºåèçå¼ï¼ - 4.11.3) åæä½¿ç¨ currval() æå°è´åå¶ä»ç¨æ¶çè¡çªææ³åï¼ - 4.11.4) çºä»éº¼ä¸å¨äºåç°å¸¸ä¸æ¢å¾éç¨åºåèå¢ï¼çºä»éº¼å¨åºåèæ¬ä½çåå¼ä¸ - åå¨éæ·å¢ï¼ - 4.12) ä»éº¼æ¯ OIDï¼ä»éº¼æ¯ CTID ï¼ - 4.13) çºä»éº¼ææ¶å°é¯èª¤è³è¨ãERROR: Memory exhausted in - AllocSetAlloc()ãï¼ - 4.14) æå¦ä½æè½ç¥éæéè¡ç PostgreSQL ççæ¬ï¼ - 4.15) æå¦ä½åµå»ºä¸åé è¨å¼æ¯ç¶åæéçæ¬ä½ï¼ - 4.16) å¦ä½å·è¡å¤é£æ¥ï¼outer joinï¼æ¥è©¢ï¼ - 4.17) å¦ä½å·è¡æ¶åå¤åè³æ庫çæ¥è©¢ï¼ - 4.18) å¦ä½è®å½æ¸è¿åå¤è¡æå¤åè³æï¼ - 4.19) çºä»éº¼æå¨ä½¿ç¨ PL/PgSQL å½æ¸ååè¨æ表æææ¶å°é¯èª¤è³è¨ãrelation - with OID ##### does not existãï¼ - 4.20) ç®åæåªäºè³æè¤å¯«(replication)æ¹æ¡å¯ç¨ï¼ - 4.21) çºä½æ¥è©¢çµæ顯示ç表åææ¬åèæçæ¥è©¢èªå¥ä¸ - çä¸åï¼çºä½å¤§å¯«çæä¸è½ä¿çï¼ - _________________________________________________________________ - -常è¦åé¡ - - 1.1) PostgreSQL æ¯ä»éº¼ï¼è©²æ麼ç¼é³ï¼ - - PostgreSQL è®ä½ Post-Gres-Q-Lï¼ææåä¹ç°¡ç¨±çºPostgres - ãæ³è½ä¸ä¸å¶ç¼é³ç人å¡å¯å¾é裡ä¸è¼è²é³æä»¶ï¼ MP3 æ ¼å¼ ã - - PostgreSQL æ¯é¢åç®æ¨çéä¿è³æ庫系統ï¼å®å·æå³çµ±åæ¥ - è³æ庫系統çææåè½ï¼åæåå«æå°å¨ä¸ä¸ä»£ DBMS - 系統ç使ç¨çå¢å¼·ç¹æ§ãPostgreSQL - æ¯èªç±åè²»çï¼ä¸¦ä¸æææºä»£ç¢¼é½å¯ä»¥ç²å¾ã - - PostgreSQL - çéç¼éä¼ä¸»è¦çºå¿é¡èï¼ä»åéä½ä¸çåå°ä¸¦ééäºè¯ç¶²é²è¡è¯ç¹«ï¼éæ¯ä¸å社åéç¼é - ç®ï¼å®ä¸è¢«ä»»ä½å¬å¸æ§å¶ã - å¦æ³å å¥éç¼éä¼ï¼è«åè¦éç¼äººå¡å¸¸è¦åé¡ï¼FAQï¼ - http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html - - 1.2) 誰æ§å¶ PostgreSQL ï¼ - - å¦æä½ å¨å°æ¾PostgreSQLçæé人ï¼ææ¯ä»éº¼ä¸ - 央å§å¡æï¼ææ¯ä»éº¼æ屬å¬å¸ï¼ä½ åªè½æ¾æ£äº---å çºä¸åä¹ä¸å - å¨ï¼ä½æåç確æä¸å æ ¸å¿å§å¡æåCVS管ççµï¼ä½éäºå·¥ä½çµçè¨ - ç«ä¸»è¦æ¯çºäºé²è¡ç®¡çå·¥ä½èä¸æ¯å°PostgreSQLé²è¡ç¨ä½å¼æ§å¶ï¼PostgreSQLé - ç®æ¯ç±ä»»ä½äººå - å¯åå çéç¼äººå¡ç¤¾ååææç¨æ¶æ§å¶çï¼ä½ æéè¦åçå°±æ¯è¨é±éµä»¶å表ï¼åèè¨è«å³å - ¯ï¼è¦åèPostgreSQLçéç¼è©³è¦ éç¼äººå¡å¸¸åé¡ (Developer's FAQ) ç²åè³è¨ï¼ã - - 1.3) PostgreSQL ççæ¬æ¯ä»éº¼? - - PostgreSQLçç¼å¸éµå¾ç¶å¸ç BSD çæ¬ãå®å許ç¨æ¶ä¸éç®çå°ä½¿ç¨ - PostgreSQLï¼çè³ä½ å¯ä»¥é·å® PostgreSQL - èä¸å«æºä»£ç¢¼ä¹å¯ä»¥ï¼å¯ä¸çéå¶å°±æ¯ä½ ä¸è½å è»é«èªèº«åé¡èåæå追訴æ³å¾ - 責任ï¼å¦å¤å°±æ¯è¦æ±ææçè»é«æ·è²ä¸é åæ¬ä»¥ä¸çæ¬è²æã - ä¸é¢å°±æ¯æåæ使ç¨çBSDçæ¬è²æå§å®¹ï¼ - - PostgreSQL è³æ庫管ç系統 - - é¨åçæ¬ï¼cï¼1996-2005ï¼PostgreSQL å¨çéç¼å°çµï¼é¨åçæ¬ï¼cï¼1994-1996 - å å·å¤§å¸è£äº - - ï¼Portions copyright (c) 1996-2005,PostgreSQL Global Development Group - Portions Copyright (c) 1994-6 Regents of the University of - Californiaï¼ - - å許çºä»»ä½ç®ç使ç¨ï¼æ·è²ï¼ä¿®æ¹ååç¼éåè»é«åå®çææªèä¸æ¶åä»»ä½è²»ç¨ï¼ - 並ä¸ç¡é 簽署å æ¤èç¢ççèæï¼åææ¯ä¸é¢ççæ¬è²æåæ¬æ®µä»¥åä¸é¢å©æ®µæå - åºç¾å¨æææ·è²ä¸ã - - ï¼Permission to use, copy, modify, and distribute this software and - its documentation for any purpose, without fee, and without a written - agreement is hereby granted, provided that the above copyright notice - and this paragraph and the following two paragraphs appear in all - copies.ï¼ - - å¨ä»»ä½ææ³ä¸ï¼å å·å¤§å¸é½ä¸æ¿æå 使ç¨æ - ¤è»é«åå¶ææªèå°è´çå°ä»»ä½ç¶äºäººçç´æ¥çï¼ - éæ¥çï¼ç¹æ®çï¼éå çæèç¸ä¼´èççæå£ï¼åæ¬å©çæ失ç責任ï¼å³ä½¿å å·å¤§å - ¸å·²ç¶å»ºè°äºéäºæ失çå¯è½æ§æä¹æ¯å¦æ¤ã - - ï¼IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY - PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL - DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS - SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA - HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.ï¼ - - å å·å¤§å¸æ確æ¾æ£ä»»ä½ä¿èï¼åæ¬ä½ä¸å±éæ¼æä¸ç¹å®ç¨éçåæ¥åå©ççé±å«ä¿èã - é裡æä¾çé份è»é«æ¯åºæ¼ãç¶ä½æ¯ãçåºç¤çï¼å èå å·å¤§å¸æ²æ責任æä¾ç¶ - è·ï¼æ¯æï¼æ´æ°ï¼å¢å¼·æèä¿®æ¹çæåã - - ï¼THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, - INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF - MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE - PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF - CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, - UPDATES, ENHANCEMENTS, OR MODIFICATIONS.ï¼ - - 1.4) PostgreSQL å¯ä»¥éè¡å¨åªäºä½æ¥ç³»çµ±å¹³å°ä¸ï¼ - - ä¸è¬èªªä¾ï¼ä»»ä½ç¾å¨å° UNIX ç¸å®¹çä½æ¥ç³»çµ±ä¹ä¸é½è½éè¡ PostgreSQL - ãå¨å®è£æå裡ååºäºç¼ä½æç¶éæ確測試çå¹³å°ã - - PostgreSQL ä¹å¯ä»¥ç´æ¥éè¡å¨åºæ¼å¾®è» Windows-NT çä½æ¥ç³»çµ±ï¼å¦ - Win2000 SP4ï¼WinXP å Win2003ï¼å·²è£½ä½å®æçå®è£åå¯å¾ - http://pgfoundry.org/projects/pginstallerä¸è¼ï¼åºæ¼MSDOSçWindowsä½æ¥ - 系統 ï¼Win95ï¼Win98ï¼WinMeï¼éè¦ééCygwin模æ¬ç°å¢éè¡PostgreSQLã - - åæä¹æä¸åçº Novell Netware 6 éç¼ççæ¬å¯å¾ http://forge.novell.com - ç²åï¼çºOS/2(eComStation)éç¼ççæ¬å¯å¾ - http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&button=Search&key=postgre - SQL&stype=all&sort=type&dir=%2F ä¸è¼ã - - 1.5) æå¾åªè£¡è½å¾å° PostgreSQLï¼ - - ééç覽å¨å¯å¾ http://www.postgresql.org/ftp/ ä¸è¼ï¼ä¹å¯ééFTPï¼å¾ - ftp://ftp.PostgreSQL.org/pub/ ç«é»ä¸è¼ã - - 1.6) ææ°çç PostgreSQL æ¯ä»éº¼ï¼ - - PostgreSQL ææ°ççæ¬æ¯çæ¬ 8.2.3 ã - - æåè¨åæ¯å¹´ç¼ä½ä¸å主è¦åç´çæ¬ï¼æ¯å¹¾åæç¼ä½ä¸åå°çæ¬ã - - 1.7) æå¾åªè£¡è½å¾å°å° PostgreSQL çæ¯æï¼ - - PostgreSQL社åéééµä»¶å表çºå¶å¤§å¤æ¸ç¨æ¶æä¾å¹«å©ï¼è¨é±éµä»¶å表ç主ç«é - »æ¯ - http://www.postgresql.org/community/lists/ï¼ä¸è¬ææ³ä¸ï¼åå å¥General æ - Bugéµä»¶å表æ¯ä¸åè¼å¥½çéå§ã - - 主è¦çIRCé »éæ¯å¨FreeNode(irc.freenode.net)ç#postgresqlï¼çºäºé£ä¸æ - ¤é »éï¼å¯ä»¥ä½¿ç¨ UNIX ç¨åº ircï¼å¶æä»¤æ ¼å¼ï¼ irc -c '#postgresql' - "$USER" irc.freenode.net ï¼æè使ç¨å¶ä»IRC客æ¶ç«¯ç¨åºãå¨æ¤ç¶²çµ¡ä¸éå - å¨ä¸å PostgreSQL ç西ççé »é(#postgersql-es)åæ³èªé »é - (#postgresql-fr)ãå樣å°ï¼å¨ EFNET ä¸ä¹æä¸å PostgreSQL ç交æµé »éã - - å¯æä¾åæ¥æ¯æçå¬å¸å表å¯å¨ http://techdocs.postgresql.org/companies.php - ç覽ã - - 1.8) æå¦ä½æ交ä¸å BUG å ±åï¼ - - å¯è¨ªå http://www.postgresql.org/support/submitbugï¼å¡«å¯« Bug - ä¸å ±è¡¨æ ¼å³å¯ï¼å樣ä¹å¯è¨ªå ftp ç«é» ftp://ftp.PostgreSQL.org/pub/ - 檢æ¥æç¡æ´æ°çPostgreSQL çæ¬æè£ä¸ã - - ééä½¿ç¨ Bug æäº¤è¡¨æ ¼ææ¯ç¼å¾ PostgreSQL éµä»¶å表ç Bug - é常ææ以ä¸ä¹ä¸åè¦ï¼ - * ææ交å§å®¹ä¸æ¯ä¸å Bug åå¶ä¸æ¯ Bug çåå ã - * ææ交å§å®¹æ¯ä¸åå·²ç¥ç Bug 並ä¸å·²ç¶å å¥ TODO å¾èçä»»åå表ã - * ææ交ç Bug å·²å¨ç¶åçæ¬ä¸è¢«ä¿®æ£ã - * ææ交ç Bug 已修æ£ä½å°æªå°è£ä¸å å¥ç¾å¨çç¼å¸è»é«åã - * è«æ±æ交èæä¾æ´è©³ç´°çè³è¨ï¼ - + ä½æ¥ç³»çµ± - + PostgreSQL çæ¬ - + å¯éç¾ Bug ç測試æ¡ä¾ - + 調試è³è¨ - + 調試è·è¹¤è¼¸åº - * ææ交å§å®¹æ¯ä¸åæ° Bugï¼å°å·è¡ä»¥ä¸å·¥ä½ï¼ - + åµå»ºä¸åæ°è£ä¸ä¸¦å°å¶å å¥ä¸ä¸å主è¦çæ¬ææ¯å°çæ¹é²çæ¬ä¸ã - + æ¤ Bug æ«æä¸è½ä¿®æ£ï¼å°è¢«å è³ TODO å¾èçä»»åå表ã - - 1.9) æå¦ä½ç解已ç¥ç BUG ææ«ç¼ºçåè½ï¼ - - PostgreSQL æ¯æä¸åæ´å±ç SQL:2003 çåéãåé±æåç TODO å表ï¼çè§£å·²ç¥ Bug - å表ãæ«ç¼ºçåè½åå°ä¾çéç¼è¨åã - - è¦æ±å¢å æ°åè½çç³è«é常ææ¶å°ä»¥ä¸ä¹ä¸çåè¦ï¼ - * 該åè½å·²å å¥ TODO å¾èçä»»åå表ã - * 該åè½ä¸æ¯å¿é çï¼å çºï¼ - + å®æ¯ç¾æçä¸ç¬¦å SQL æ¨æºçæåè½çéè¤ã - + 該åè½æ§æ大大å¢å 代碼çè¤éç¨åºï¼è帶ä¾ç好èæ¯å¾®ä¸è¶³éçã - + 該åè½æ¯ä¸å®å¨ææ¯ä¸å¯é çã - * 該åè½å°è¢«å å¥ TODO å¾èçä»»åå表ã - - PostgreSQL ä¸ä½¿ç¨ Bug è·è¹¤ç³»çµ±ï¼å çºæåç¼ç¾å¨éµä»¶åè¡¨ä¸ - ç´æ¥åè¦ä»¥åä¿è TODO - ä»»åå表總æ¯èæ¼ææ°çæçæ¹å¼å·¥ä½æçææ´é«ä¸äºãäºå¯¦ä¸ï¼Bugä¸æå¨æåçè»é«ä¸ - åå¨å¾é·æéï¼ å°å½±é¿å¾å¤ç¨æ¶çBugä¹ç¸½æ¯å¾å¿«æ被修æ - £ãå¯ä¸è½æ¾å°æææ¹é²ãæé«åä¿®æ£çå°æ¹æ¯ CVS - çæ¥èªè³è¨ï¼å³ä½¿æ¯å¨è»é«æ°çæ¬çç¼å¸è³è¨ä¸ä¹ä¸æååºæ¯ä¸èçè»é«æ´æ°ã - - 1.10) è½å¤ ç²åçææ°ææªæåªäºï¼ - - PostgreSQL åå«å¤§éçææªï¼ä¸»è¦æ詳細çåèæåï¼æåé åä¸äºç測試ä¾åãåè¦ - /doc ç®éï¼è¯è¨»ï¼æçº $PGHOME/docï¼ã ä½ éå¯ä»¥å¨ç·ç覽 PostgreSQL - çæåï¼å¶ç¶²åæ¯ï¼http://www.PostgreSQL.org/docsã - - æå©æ¬éæ¼PostgreSQLçæ¸å¨ç·æä¾ï¼å¨ - http://www.postgresql.org/docs/books/awbook.html å - http://www.commandprompt.com/ppbook/ ã ä¹æ大éç PostgreSQL - æ¸ç±å¯ä¾è³¼è²·ï¼å¶ä¸æçºæµè¡çä¸æ¬æ¯ç± Korry Douglas 編寫çãå¨ - http://techdocs.PostgreSQL.org/techdocs/bookreviews.phpä¸ ä¸æ大éæé - PostgreSQL æ¸ç±çç°¡ä»ã å¨ http://techdocs.PostgreSQL.org/ ä¸æ¶éäºæé - PostgreSQL ç大éæè¡æç« ã - - 客æ¶ç«¯çå½ä»¤è¡ç¨åºpsqlæä¸äºä»¥ \d éé - çå½ä»¤ï¼å¯é¡¯ç¤ºéæ¼é¡åï¼æä½ç¬¦ï¼å½æ¸ï¼èåçè³è¨ï¼ä½¿ç¨ \? - å¯ä»¥é¡¯ç¤ºææå¯ç¨çå½ä»¤ã - - æåç web ç«é»åå«æ´å¤çææªã - - 1.11) ææ該æ樣å¸ç¿ SQL ï¼ - - é¦åèæ®ä¸è¿°æå°çèPostgreSQLç¸éçæ¸ç±ï¼å¦å¤ä¸æ¬æ¯ Teach Yourself SQL in - 21 Days, Second Editionï¼å¶è©³ç´°ä»ç´¹ç網åæ¯ - http://members.tripod.com/er4ebus/sql/index.htmï¼ æåç許å¤ç¨æ¶åæ¡The - Practical SQL Handbookï¼ Bowman, Judith S. - 編寫ï¼Addison-Wesleyå¬å¸åºçï¼å¶ä»çååæ¡ The Complete Reference SQL, - Groff 編寫ï¼McGraw-Hill å¬å¸åºçã - - å¨ä¸å網åä¸ä¹æå¾å¥½çæç¨ï¼ä»åæ¯ - * http://www.intermedia.net/support/sql/sqltut.shtm - * http://sqlcourse.com. - * http://www.w3schools.com/sql/default.asp - * http://mysite.verizon.net/Graeme_Birchall/id1.html - - 1.12)å¦ä½æ交è£ä¸ææ¯å å¥éç¼éä¼ï¼ - - è©³è¦ éç¼äººå¡å¸¸è¦åé¡ (Developer's FAQ) ã - - 1.13) PostgreSQL åå¶ä»è³æ庫系統æ¯èµ·ä¾å¦ä½ï¼ - - è©å¹è»é«æ好幾種æ¹æ³ï¼åè½ï¼æ§è½ï¼å¯é æ§ï¼æ¯æåå¹æ ¼ã - - åè½ - PostgreSQL ææ大ååç¨è³æ庫æå¤çåè½ï¼ä¾å¦ï¼äºåï¼å - æ¥è©¢ï¼è§¸ç¼å¨ï¼è¦åï¼å¤éµåèå®æ´æ§åè¤éçéå®çã - æåéæä¸äºå®åæ²æçç¹æ§ï¼å¦ç¨æ¶å®ç¾©é¡åï¼ç¹¼æ¿ï¼è¦ååå¤çæ¬ä¸¦è¡æ§å¶ä - »¥æ¸å°éççç¨çã - - æ§è½ - PostgreSQLåå¶ä»åç¨åéæºçè³æ庫å·æé¡ä¼¼çæ§è½ãå°æäºèçå®æ¯è¼å¿«ï¼å° - å¶ä»ä¸äºèçå®æ¯è¼æ¢ã èå¶ä»è³æ庫ç¸æ¯ï¼æåçæ§è½åªå£éå¸¸å¨ +/- - 10%ä¹éã - - å¯é æ§ - æåé½ç¥éè³æ庫å¿é æ¯å¯é çï¼å¦åå®å°±ä¸é»ç¨é½æ²æãæååªååå°ç¼ä½ç¶éèª - ç測試çï¼ç¼ºé·æå°çç©©å®ä»£ç¢¼ãæ¯åçæ¬è³å°æä¸åæç beta - 測試æéï¼ä¸¦ä¸æåçç¼å¸æ - ·å²é¡¯ç¤ºæåå¯ä»¥æä¾ç©©å®çï¼ç¢åºçï¼å¯ç¨æ¼çç¢ä½¿ç¨ççæ¬ãæåç¸ä¿¡å¨é - æ¹é¢æåèå¶ä»çè³æ庫è»é«æ¯ç¸ç¶çã - - æ¯æ - æåçéµä»¶å表æä¾ä¸åé常大çéç¼äººå¡åç¨æ¶ççµä»¥å¹«å©è§£æ±ºæ碰å°ç - ä»»ä½åé¡ãæåä¸è½ä¿è - 總æ¯è½è§£æ±ºåé¡ï¼ç¸æ¯ä¹ä¸ï¼åç¨è³æ庫è»é«ä¹ä¸¦ä¸æ¯ç¸½è½å¤ æä¾è§ - £æ±ºæ¹æ³ã - ç´æ¥èéç¼äººå¡ï¼ç¨æ¶ç¾¤ï¼æååæºç¨åºæ¥è§¸ä½¿PostgreSQLçæ¯ææ¯å¶ä»è³ - æ庫éè¦å¥½ãéæä¸äºåæ¥ - æ§çå¨é¢æè¡æ¯æï¼å¯ä»¥çµ¦æä¾çµ¦é£äºéè¦ç人ãï¼åé±1.7 å°ç¯ï¼ - - å¹æ ¼ - æåå°ä»»ä½ç¨éé½åè²»ï¼åæ¬åç¨åéåç¨ç®çã - ä½ å¯ä»¥ä¸å éå¶å°åä½ çç¢å裡å å¥æåç代碼ï¼é¤äºé£äºæåå¨ä¸é¢ççæ¬ - è²æ裡è²æç BSDçæ¬ä¹å¤çå§å®¹ã - - 1.14) PostgreSQL å¯ä»¥èçæè¿ååå家å¤æå¶çè®åå? - - PostgreSQL 8.0ä¹åççæ¬æ¯ä½¿ç¨ä½æ¥ç³»çµ±ä¸çæåè³æ庫ä¾èçå¤æå¶çè³è¨ï¼èª - 8.0 çå以å¾ççæ¬ PostgreSQL æèªèº«å«æææ°çæåè³è¨ã - _________________________________________________________________ - -ç¨æ¶å®¢æ¶ç«¯åé¡ - - 2.1) æåå¯ä»¥ç¨ä»éº¼èªè¨å PostgreSQL é²è¡æºéï¼ - - PostgreSQL (é è¨ææ³)åªå®è£æ C åå§åµå¼ C - çæ¥å£ï¼å¶ä»çæ¥å£é½æ¯ç¨ç«çé ç®ï¼è½å¤ åå¥ä¸è¼ï¼éäºæ¥å£é ç®ç¨ç«ç好è - æ¯ä»åå¯ä»¥æåèªçç¼å¸è¨åååèªç¨ç«çéç¼çµã - - ä¸äºç·¨ç¨èªè¨å¦ PHP é½æ訪å PostgreSQL çæ¥å£ï¼PerlãTCLãPython - 以åå¾å¤å¶ä»èªè¨çæ¥å£å¨ http://gborg.postgresql.org 網ç«ä¸ç - Drivers/Interfaces å°ç¯å¯æ¾å°ï¼ 並ä¸éé Internet å¾å®¹ææç´¢å°ã - - 2.2) æä»éº¼å·¥å·å¯ä»¥æ PostgreSQL ç¨æ¼ Web é é¢ï¼ - - ä¸åä»ç´¹ä»¥è³æ庫çºå¾å°çæºä¸é¯çç«é»æ¯ï¼http://www.webreview.comã - - å°æ¼ Web éæï¼PHP æ¯ä¸å極好çæ¥å£ãå®å¨ http://www.php.net/ã - - å°æ¼è¤éçä»»åï¼å¾å¤äººæ¡ç¨ Perl æ¥å£å ä½¿ç¨ CGI.pm ç DBD::Pg æ mod_perl - ã - - 2.3)PostgreSQL ææåå½¢ç¨æ¶çé¢åï¼ - - åæ¥ç¨æ¶ææ¯éæºéç¼äººå¡è½æ¾å°å¾å¤çæé PostgreSQLçGUI å形工å·è»é«ï¼å¨ - PostgreSQL社åææª æä¸å詳細çå表ã - _________________________________________________________________ - -系統管çåé¡ - - 3.1)ææ樣è½æ PostgreSQL è£å¨ /usr/local/pgsql 以å¤çå°æ¹ï¼ - - å¨éè¡ configure æå ä¸ --prefix é¸é ã - - 3.2) æå¦ä½æ§å¶ä¾èªå¶ä»é»è¦çé£æ¥ï¼ - - é è¨ææ³ä¸ï¼PostgreSQL åªå許ä¾èªæ¬æ©ä¸éé unix åå¥æ¥åæ TCP/IP - æ¹å¼çé£æ¥ã ä½ åªæå¨ä¿®æ¹äºéç½®æ件 postgresql.conf ä¸ç - listen_addressesï¼ä¸ä¹å¨éç½®æ件 $PGDATA/pg_hba.conf ä¸æéäº - åºæ¼é ç¨é»è¦ï¼ host-based ï¼ç身份èªèï¼ä¸¦éæ°åå - PostgreSQLï¼å¦åå¶ä»é»è¦æ¯ä¸è½èä½ ç PostgreSQL 伺æå¨é²è¡é£æ¥çã - - 3.3) ææ樣調æ´è³æ庫å¼æ以ç²å¾æ´å¥½çæ§è½ï¼ - - æä¸å主è¦æ¹é¢å¯ä»¥æå PostgreSQL çæ½è½ã - - æ¥è©¢æ¹å¼çè®å - é主è¦æ¶åä¿®æ¹æ¥è©¢æ¹å¼ä»¥ç²åæ´å¥½çæ§è½: - - + åµå»ºç´¢å¼ï¼åæ¬è¡¨éå¼åé¨åç´¢å¼ï¼ - + ä½¿ç¨ COPY èªå¥ä»£æ¿å¤å Insert èªå¥ï¼ - + å°å¤åSQLèªå¥çµæä¸åäºå以æ¸å°æ交äºåçéé·ï¼ - + å¾ä¸åç´¢å¼ä¸æåå¤æ¢è¨éæä½¿ç¨ CLUSTERï¼ - + å¾ä¸åæ¥è©¢çµæä¸ååºé¨åè¨éæä½¿ç¨ LIMITï¼ - + 使ç¨é ç·¨è¯å¼æ¥è©¢ï¼Prepared Query)ï¼ - + ä½¿ç¨ ANALYZE 以ä¿æ精確çåªåçµ±è¨ï¼ - + å®æä½¿ç¨ VACUUM æ pg_autovacuum - + é²è¡å¤§éè³ææ´æ¹æååªé¤ç´¢å¼ï¼ç¶å¾é建索å¼ï¼ - - 伺æå¨çéç½® - éç½®æ件 postgres.conf ä¸çå¾å¤è¨ - ç½®é½æå½±é¿æ§è½ï¼ææåæ¸çå表å¯è¦ï¼ - 管çå¡æå/è³æ庫伺æå¨éè¡ç°å¢/è³æ庫伺æå¨éè¡éç½®ï¼ - æéåæ¸ç解éå¯è¦ï¼http://www.varlena.com/varlena/GeneralBits/Tidb - its/annotated_conf_e.html å - http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.htmlã - - 硬é«çé¸æ - é»è¦ç¡¬é«å°æ§è½çå½±é¿å¯ç覽 - http://candle.pha.pa.us/main/writings/pgsql/hw_performance/inde - x.html å http://www.powerpostgresql.com/PerfList/ã - - 3.4)PostgreSQL 裡å¯ä»¥ç²å¾ä»éº¼æ¨£ç調試ç¹æ§ï¼ - - PostgreSQL æå¾å¤é¡ä¼¼ log_* - ç伺æå¨éç½®è®éå¯ç¨æ¼æ¥è©¢çåå°åé²ç¨çµ±è¨ï¼èéäºå·¥ä½å°èª¿è©¦åæ§è½æ¸¬è©¦å - ¾æ幫å©ã - - 3.5) çºä»éº¼å¨è©¦åé£æ¥ææ¶å°ãSorry, too many - clientsï¼å·²æ太å¤ç¨æ¶é£æ¥ï¼ãæ¶æ¯ï¼ - - éè¡¨ç¤ºä½ å·²éå°é è¨ 100 å並ç¼(åä½)å¾å°é²ç¨æ¸çéå¶ï¼ä½ éè¦ééä¿®æ¹ - postgresql.conf æ件ä¸ç max_connections å¼ä¾ å¢å postmaster - çå¾å°ä½µç¼èçæ¸ï¼ä¿®æ¹å¾ééæ°åå postmasterã - - 3.6)PostgreSQL çåç´éç¨æåªäºå§å®¹ ï¼ - - PostgreSQL éç¼çµå°æ¯æ¬¡å°çæ¬çåç´ä¸»è¦åªåäºä¸äº Bug ä¿®æ£å·¥ä½ï¼å æ¤å¾ - 7.4.8 åç´å° 7.4.9 ä¸éè¦ dump å restoreï¼åéè¦åæ - ¢è³æ庫伺æå¨ï¼å®è£æ´æ°å¾çè»é«åï¼ç¶å¾éå伺æå¨å³å¯ã - - ææPostgreSQLçç¨æ¶æ該å¨ææ¥è¿ï¼ä½ æ使ç¨ç主çæ¬ï¼çå°æ¹é²çæ¬ç¼ä½ç¡å¿«åç´ - ãå管æ¯æ¬¡åç´å¯è½é½æä¸é»é¢¨éªï¼PostgreSQLçå°æ¹ é²çååæ¯è¨è¨ç¨ä¾ä¿®æ - £ä¸äº Bug - çï¼ç¨å¼ç¢¼æ¹åè¼å°ï¼æ以風éªéæ¯å¾å°çãPostgreSQL社åèªçºä¸è¬ææ³ä¸ä¸åç´ç - 風éªéæ¯å¤æ¼åç´çã - - 主çæ¬çåç´ï¼ä¾å¦å¾ 7.3 å° 7.4ï¼é常æä¿®æ¹ç³»çµ±è¡¨åè³æ表çå§é¨æ ¼å¼ã - éäºæ¹è®ä¸è¬æ¯è¼è¤éï¼å æ¤æåä¸ç¶æè³ææ件çåå¾å¼å®¹æ§ãå æ¤å¾èçæ¬ä¸ - é²è¡è³æå°åºï¼dumpï¼/ç¶å¾å¨æ°çæ¬ä¸ - é²è¡è³æå°å¥ï¼reloadï¼å°ä¸»çæ¬çåç´æ¯å¿é çã - - 3.7)(ä½¿ç¨ PostgreSQL )æéè¦ä½¿ç¨ä»éº¼é»è¦ç¡¬é« ï¼ - - ç±æ¼é»è¦ç¡¬é«å¤§å¤æ¸æ¯ç¸å®¹çï¼äººå總æ¯å¾åæ¼ç¸ä¿¡ææé»è¦ç¡¬é«è³ªéä¹æ¯ç¸ - åçãäºå¯¦ä¸ä¸æ¯ï¼ ECC RAMï¼å¸¶å¥å¶æ ¡é©çè¨æ¶é«ï¼ï¼SCSI - ï¼ç¡¬ç¢ï¼ååªè³ªç主æ©æ¿æ¯ä¸äºä¾¿å®è²¨è¦æ´å å¯é ä¸å·ææ´å¥½çæ§è½ã - PostgreSQL å¹¾ä¹å¯ä»¥éè¡å¨ä»»ä½ç¡¬é«ä¸ï¼ - ä½å¦æå¯é æ§åæ§è½å°ä½ ç系統å¾éè¦ï¼ä½ å°±éè¦å¨é¢çç 究ä¸ä¸ä½ ç硬é«çµæ - äºãå¨æåçéµä»¶å表ä¸ä¹æéæ¼ ç¡¬é«éç½®åæ§å¹æ¯çè¨è«ã - _________________________________________________________________ - -æä½åé¡ - - 4.1) å¦ä½åªé¸æä¸åæ¥è©¢çµæçé å¹¾è¡ï¼ææ¯é¨æ©çä¸è¡ï¼ - - å¦æä½ åªæ¯è¦æåå¹¾è¡è³æï¼ä¸¦ä¸ä½ å¨å·è¡æ¥è©¢ä¸ - ç¥é確åçè¡æ¸ï¼ä½ å¯ä»¥ä½¿ç¨ LIMIT åè½ã å¦ææä¸åç´¢å¼è ORDER BY ä¸ - çæ¢ä»¶å¹éï¼PostgreSQL å¯è½å°±åªèçè¦æ±çé å¹¾æ¢è¨éï¼ - ï¼å¦åå°å°æ´åæ¥è©¢é²è¡èçç´å°çæéè¦çè¡ï¼ãå¦æå¨å·è¡æ¥è©¢åè½æä¸ç¥é確åçè¨éæ - ¸ï¼ å¯ä½¿ç¨æ¸¸æ¨(cursor)åFETCHåè½ã - - å¯ä½¿ç¨ä»¥ä¸æ¹æ³æåä¸è¡é¨æ©è¨éçï¼ - SELECT cols - FROM tab - ORDER BY random() - LIMIT 1 ; - - 4.2) - å¦ä½æ¥ç表ãç´¢å¼ãè³æ庫以åç¨æ¶çå®ç¾©ï¼å¦ä½æ¥çpsql裡ç¨å°çæ¥è©¢æ令並顯示å - ®åï¼ - - å¨psqlä¸ä½¿ç¨ \dt å½ä»¤ä¾é¡¯ç¤ºè³æ表çå®ç¾©ï¼è¦ç解 psql ä¸ - çå®æ´å½ä»¤å表å¯ä½¿ç¨ \? ï¼å¦å¤ï¼ä½ ä¹å¯ä»¥é±è® psql çæºä»£ç¢¼ æ件 - pgsql/src/bin/psql/describe.cï¼å®åæ¬çºçæ psql åææ§å½ä»¤ç輸åºçææ SQL - å½ä»¤ãä½ éå¯ä»¥å¸¶ -E é¸é åå psqlï¼ é樣å®å°åå°åºä½ å¨ psql ä¸ - æ給åºçå½ä»¤å·è¡æçå§é¨å¯¦é使ç¨ç SQL - æ¥è©¢èªå¥ãPostgreSQLä¹æä¾äºä¸åå¼å®¹ SQLç INFORMATION SCHEMA æ¥å£ï¼ - ä½ å¯ä»¥å¾é裡ç²åéæ¼è³æ庫çè³è¨ã - - å¨ç³»çµ±ä¸ä¹æä¸äºä»¥ pg_ æé ç系統表ä¹æè¿°äºè¡¨çå®ç¾©ã - - ä½¿ç¨ psql -l æ令å¯ä»¥ååºææçè³æ庫ã - - ä¹å¯ä»¥ç覽ä¸ä¸ pgsql/src/tutorial/syscat.source - æ件ï¼å®åèäºå¾å¤å¯å¾è³æ庫系統表ä¸ç²åè³è¨çSELECTèªæ³ã - - 4.3) å¦ä½æ´æ¹ä¸åæ¬ä½çè³æé¡åï¼ - - å¨8.0çæ¬è£¡æ´æ¹ä¸åæ¬ä½çè³æé¡åå¾å®¹æï¼å¯ä½¿ç¨ ALTER TABLE ALTER COLUMN - TYPE ã - - å¨ä»¥åççæ¬ä¸ï¼å¯ä»¥é樣åï¼ - BEGIN; - ALTER TABLE tab ADD COLUMN new_col new_data_type; - UPDATE tab SET new_col = CAST(old_col AS new_data_type); - ALTER TABLE tab DROP COLUMN old_col; - COMMIT; - - ä½ ç¶å¾å¯ä»¥ä½¿ç¨ VACUUM FULL tab æ令ä¾ä½¿ç³»çµ±æ¶åç¡æè³ææä½ç¨ç空éã - - 4.4) å®æ¢è¨éï¼å®å表ï¼å®åè³æ庫çæ大éå¶æ¯å¤å°ï¼ - - ä¸é¢æ¯ä¸äºéå¶ï¼ - - å®åè³æ庫æå¤§å°ºå¯¸ï¼ ç¡éå¶ï¼å·²åå¨æ 32TB çè³æåº«ï¼ - å®å表çæå¤§å°ºå¯¸ï¼ 32 TB - ä¸è¡è¨éçæå¤§å°ºå¯¸ï¼ 1.6 TB - ä¸åæ¬ä½çæ大尺寸? 1 GB - ä¸å表裡æ大åæ¸ï¼ ç¡éå¶ - ä¸å表裡æ大æ¬ä½æ¸ï¼ 250-1600 ï¼èåé¡åæéï¼ - ä¸å表裡çæ大索å¼æ¸éï¼ ç¡éå¶ - - ç¶ç¶ï¼å¯¦éä¸æ²æçæ£çç¡éå¶ï¼éæ¯è¦åå¯ç¨ç£ç¤ç©ºéãå¯ç¨è¨æ¶é«/交æåçå¶ç´ã - äºå¯¦ä¸ï¼ç¶ä¸è¿°éäºæ¸å¼è®å¾ç°å¸¸å°å¤§æï¼ç³»çµ±æ§è½ä¹æåå¾å¤§å½±é¿ã - - å®è¡¨çæå¤§å¤§å° 32 TB ä¸éè¦ä½æ¥ - 系統å°å®åæ件ä¹éé麼大çæ¯æã大表ç¨å¤å 1 GB çæ件åå²ï¼å æ - ¤æ件系統大å°çéå¶æ¯ä¸éè¦çã - - å¦æé è¨çå¡å¤§å°å¢é·å° 32K ï¼æ大çå®è¡¨å¤§å°åæ大åæ¸éå¯ä»¥å¢å å°ååã - - æä¸åéå¶å°±æ¯ä¸è½å°å¤§å°å¤æ¼2000å - ç¯çååµå»ºç´¢å¼ã幸éå°æ¯é樣çç´¢å¼å¾å°ç¨å°ãééå°å¤å - ç¯åçå§å®¹é²è¡MD5åç¨éç®çµæé²è¡å½æ¸ç´¢å¼å¯å°åçå¯ä¸æ§å¾å°ä¿èï¼ - 並ä¸å¨æ檢索å許å°åä¸çå®è©é²è¡æç´¢ã - - 4.5) åå²ä¸åå¸åçææ¬æ件裡çè³æéè¦å¤å°ç£ç¤ç©ºéï¼ - - ä¸å Postgres è³æ庫ï¼å - å²ä¸åææ¬æ件ï¼æä½ç¨ç空éæå¤å¯è½éè¦ç¸ç¶æ¼éåææ¬æ件èªèº«å¤§å°5åçç£ç¤ç©ºé - ã - - ä¾å¦ï¼åè¨æä¸å 100,000 è¡çæ件ï¼æ¯è¡æä¸åæ´æ¸åä¸åææ¬æè¿°ã åè¨ - ææ¬ä¸²çå¹³åé·åº¦çº20ä½åçµ(Byte)ãææ¬æ件ä½ç¨ 2.8 MBãå - æ¾éäºè³æçPostgreSQLè³æ庫æ件大ç´æ¯ 5.2 MB: - 24 ååçµ: æ¯è¡çé ï¼å¤§ç´å¼ï¼ - 24 ååçµç¯: ä¸åæ´æ¸åæ¬ä½åä¸åææ¬åæ¬ä½ - + 4 ååçµç¯: é é¢å§æååçµçæé - ---------------------------------------- - 52 ååçµæ¯è¡ - - PostgreSQL è³æé ç大å°æ¯ 8192 ä½åçµ (8 KB)ï¼åï¼ - - 8192 ååçµæ¯é - ------------------- = 158 è¡/è³æé ï¼åä¸åæ´ï¼ - 52 ååçµæ¯è¡ - - 100000 è³æè¡ - -------------------- = 633 è³æé ï¼åä¸åæ´ï¼ - 146 è¡/è³æé - - 633 è³æé * 8192 åç¯/é = 5,185,536 åç¯ï¼5.2 MBï¼ - - ç´¢å¼ä¸éè¦é麼å¤çé¡å¤æ¶èï¼ä½ä¹ç¢ºå¯¦åæ¬è¢«ç´¢å¼çè³æï¼å æ - ¤å®åä¹å¯è½å¾å¤§ã - - ç©ºå¼ NULL åæ¾å¨ä½åä¸ï¼å æ¤ä½ç¨å¾å°ç空éã - - 4.6) çºä»éº¼æçæ¥è©¢å¾æ¢ï¼çºä»éº¼éäºæ¥è©¢æ²æå©ç¨ç´¢å¼ï¼ - - 並éæ¯åæ¥è©¢é½æèªå使ç¨ç´¢å¼ãåªæå¨è¡¨ç大å°è¶éä¸åæå°å¼ï¼ä¸¦ä¸æ¥è©¢åªæé - ¸ä¸è¡¨ä¸è¼å°æ¯ä¾çè¨éææææ¡ç¨ç´¢å¼ã éæ¯å çºç´¢å¼æçå¼èµ·çé¨å³ç£ç¤å - åå¯è½æ¯ç´æ¥å°è®å表ï¼é åºæçï¼æ´æ¢ã - - çºäºå¤æ·æ¯å¦ä½¿ç¨ç´¢å¼ï¼PostgreSQL - å¿é ç²å¾æé表ççµ±è¨å¼ãéäºçµ±è¨å¼å¯ä»¥ä½¿ç¨ VACUUM ANALYZEï¼æ ANALYZE - ç²å¾ã 使ç¨çµ±è¨å¼ï¼åªåå¨ç¥éè¡¨ä¸ - æå¤å°è¡ï¼å°±è½å¤ æ´å¥½å°å¤æ·æ¯å¦å©ç¨ç´¢å¼ã - çµ±è¨å¼å°ç¢ºå®åªåçé£æ¥é åºåé£æ¥æ¹æ³ä¹å¾æç¨ãå¨è¡¨çå§å®¹ç¼çè®åæï¼æå®æé²è - ¡çµ±è¨å¼çæ´æ°æ¶éã - - ç´¢å¼é常ä¸ç¨æ¼ ORDER BY - æå·è¡é£æ¥ãå°ä¸å大表çä¸æ¬¡é åºæçååä¸æ¬¡æåºé常æ¯ç´¢å¼æçè¦å¿«ãç¶èï¼å¦æ - å° LIMIT å ORDER BY - çµåå¨ä¸èµ·ä½¿ç¨ç話ï¼é常å°æ使ç¨ç´¢å¼ï¼å çºéæåè¿å表ä¸çä¸å°é¨åè¨éã - - å¦æä½ ç¢ºä¿¡PostgreSQLçåªåå¨ä½¿ç¨é åºæçæ¯ä¸æ£ç¢ºçï¼ä½ å¯ä»¥ä½¿ç¨SET - enable_seqscan TO 'off'æ令ä¾ééé åºæçï¼ - ç¶å¾å次éè¡æ¥è©¢ï¼ä½ å°±å¯ä»¥çåºä½¿ç¨ä¸åç´¢å¼æçæ¯å¦ç¢ºå¯¦è¦å¿«ä¸äºã - - ç¶ä½¿ç¨éé符æä½ï¼ä¾å¦ LIKE æ ~ æï¼ç´¢å¼åªè½å¨ç¹å®çææ³ä¸ä½¿ç¨ï¼ - * å符串çéå§é¨åå¿é æ¯æ®éå符串ï¼ä¹å°±æ¯èªªï¼ - + LIKE 模å¼ä¸è½ä»¥ % æé ã - + ~ ï¼æ£å表éå¼ï¼æ¨¡å¼å¿é 以 ^ æé ã - * å符串ä¸è½ä»¥å¹éå¤åå符ç模å¼é¡æé ï¼ä¾å¦ [a-e]ã - * 大å°å¯«ç¡éçæ¥æ¾ï¼å¦ ILIKE å ~* çä¸ä½¿ç¨ç´¢å¼ï¼ä½å¯ä»¥ç¨ 4.8 - ç¯æè¿°ç表éå¼ç´¢å¼ã - * å¨å initdb æå¿é æ¡ç¨é è¨çæ¬å°è¨ç½® C - localeï¼å çºç³»çµ±ä¸å¯è½ç¥éå¨é C locale ææ³æä¸ä¸åæ大å符æ¯ä»éº¼ã - å¨é種ææ³ä¸ï¼ä½ å¯ä»¥åµå»ºä¸åç¹æ®ç text_pattern_ops ç´¢å¼ä¾ç¨æ¼ - LIKE çç´¢å¼ã - - å¨ 8.0 ä¹åççæ¬ä¸ - ï¼é¤éè¦æ¥è©¢çè³æé¡ååç´¢å¼çè³æé¡åç¸å¹éï¼å¦åç´¢å¼ç¶å¸¸æ¯æªè¢«ç¨å°ï¼ç¹å¥æ - ¯å° int2, int8 åæ¸å¼åçç´¢å¼ã - - 4.7) æå¦ä½æè½çå°æ¥è©¢åªåå¨æ¯æ樣è©ä¼°èçæçæ¥è©¢ï¼ - - åè EXPLAIN æåé ã - - 4.8) ææ樣åæ£å表éå¼æç´¢å大å°å¯«ç¡éçæ - £å表éå¼æ¥æ¾ï¼æ樣å©ç¨ç´¢å¼é²è¡å¤§å°å¯«ç¡éæ¥æ¾ï¼ - - æä½ç¬¦ ~ èçæ£å表éå¼å¹éï¼è ~* èç大å°å¯«ç¡éçæ£å表éå¼å¹éã大å°å¯«ç¡éç - LIKE è®ç¨®æçº ILIKEã - - 大å°å¯«ç¡éççå¼æ¯è¼é常寫åï¼ - SELECT * - FROM tab - WHERE lower(col) = 'abc'; - - é樣å°ä¸æ使ç¨æ¨æºçç´¢å¼ãä½æ¯å¯ä»¥åµå»ºä¸åå¨é種ææ³ä¸ä½¿ç¨ç表éå¼ç´¢å¼ - : - CREATE INDEX tabindex ON tab (lower(col)); - - å¦æä¸è¿°ç´¢å¼å¨åµå»ºæå å¥ UNIQUE ç´æï¼éç¶ç´¢å¼æ¬ä½èªèº«å§å®¹å¯ä»¥å - å²å¤§å°å¯«ä¸éçå§å®¹ï¼ä½å¦ææ UNIQUE - ç´æå¾ï¼éäºå§å®¹ä¸è½ååæ¯å¤§å°å¯«ä¸åï¼å¦åæé æè¡çªï¼ãçºäºä¿è - ä¸ç¼çé種ææ³ï¼å¯ä»¥ä½¿ç¨ CHECK ç´ææ¢ä»¶ææ¯è§¸ç¼å¨å¨éå¥æé²è¡éå¶ã - - 4.9) å¨ä¸åæ¥è©¢è£¡ï¼ææ樣檢測ä¸åæ¬ä½æ¯å¦çº NULL - ï¼æå¦ä½æè½æºç¢ºæåºèä¸è«ææ¬ä½æ¯å¦å« NULL å¼ï¼ - - ç¨ IS NULL å IS NOT NULL 測試éåæ¬ä½ï¼å·é«æ¹æ³å¦ä¸ï¼ - SELECT * - FROM tab - WHERE col IS NULL; - - çºäºè½å°å« NULL æ¬ä½æåºï¼å¯å¨ ORDER BY æ¢ä»¶ä¸ä½¿ç¨ IS NULL å IS NOT - NULL 修飾符ï¼æ¢ä»¶çºç true å°æ¯æ¢ä»¶çºå false æå¨åé¢ï¼ä¸é¢çä¾å - å°±æå°å« NULL çè¨éæå¨çµæçä¸é¢é¨åï¼ - SELECT * - FROM tab - ORDER BY (col IS NOT NULL) - - 4.10) å種å符é¡åä¹éæä»éº¼ä¸åï¼ - - é¡å å§é¨å稱 說æ - VARCHAR(n) varchar æå®äºæ大é·åº¦ï¼è®é·å - 符串ï¼ä¸è¶³å®ç¾©é·åº¦çé¨åä¸è£é½ - CHAR(n) bpchar å®é·å符串ï¼å¯¦éè³æä¸è¶³å®ç¾©é·åº¦æï¼ä»¥ç©ºæ ¼è£é½ - TEXT text æ²æç¹å¥çä¸ééå¶ï¼ååè¡çæ大é·åº¦éå¶ï¼ - BYTEA bytea è®é·åç¯åºåï¼ä½¿ç¨NULLå符ä¹æ¯å許çï¼ - "char" char å®åå符 - - å¨ç³»çµ±è¡¨åå¨ä¸äºé¯èª¤è³è¨è£¡ä½ å°çå°å§é¨å稱ã - - ä¸é¢æåçåå種é¡åæ¯ "varlena"ï¼è®é·ï¼é¡åï¼ä¹å°±æ¯èªªï¼éé çååå - ç¯æ¯é·åº¦ï¼å¾é¢ææ¯è³æï¼ã æ¼æ¯å¯¦éä½ç¨ç空éæ¯è²æç大å°è¦å¤ä¸äºã - ç¶èéäºé¡åå¦å®ç¾©å¾é·æé½å¯ä»¥è¢«å£ç¸®åå²ï¼å æ - ¤ç£ç¤ç©ºéä¹å¯è½æ¯é æ³çè¦å°ã - - VARCHAR(n) å¨åå²éå¶äºæ大é·åº¦çè®é·å符串æ¯æ好çã TEXT é©ç¨æ¼å - å²æ大å¯é 1G å·¦å³ä½æªå®ç¾©éå¶é·åº¦çå符串ã - - CHAR(n) æé©åæ¼åå²é·åº¦ç¸åçå符串ã - CHAR(n)ææ ¹ææ給å®çæ¬ä½é·åº¦ä»¥ç©ºæ ¼è£è¶³ï¼ä¸è¶³çæ¬ä½å§å®¹ï¼ï¼ è - VARCHAR(n) åªåå²æ給å®çè³æå§å®¹ã BYTEA ç¨æ¼åå²äºé²å¶è³æï¼å°¤å¶æ¯åå« - NULL åç¯çå¼ãéäºé¡åå·æå·®ä¸å¤çæ§è½ã - - 4.11.1) ææ樣åµå»ºä¸ååºåèææ¯èªåéå¢çæ¬ä½ï¼ - - PostgreSQL æ¯æ SERIAL - è³æé¡åãï¼æ¬ä½å®ç¾©çºSERIALå¾ï¼å°èªååµå»ºä¸ååºåçæå¨ï¼ä¾å¦ï¼ - CREATE TABLE person ( - id SERIAL, - name TEXT - ); - - æèªåè½æçºä»¥ä¸SQLèªå¥ï¼ - CREATE SEQUENCE person_id_seq; - CREATE TABLE person ( - id INT4 NOT NULL DEFAULT nextval('person_id_seq'), - name TEXT - ); - - åè create_sequence æåé ç²åéæ¼åºåçæå¨çæ´å¤è³è¨ã - - 4.11.2) æå¦ä½ç²å¾ä¸åæå¥çåºåèçå¼ï¼ - - ä¸ç¨®æ¹æ³æ¯å¨æå¥ä¹ååç¨å½æ¸ nextval() å¾åºåå°è±¡è£¡æª¢ç´¢åºä¸ä¸å SERIAL - å¼ï¼ç¶å¾åç¨æ¤å¼ç²¾ç¢ºå°æå¥ãä½¿ç¨ 4.11.1 裡çä¾è¡¨ï¼å¯ç¨å½ç¢¼é樣æè¿°ï¼ - new_id = execute("SELECT nextval('person_id_seq')"); - execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')"); - - é樣éè½å¨å¶ä»æ¥è©¢ä¸ä½¿ç¨åæ¾å¨ new_id 裡çæ°å¼ï¼ä¾å¦ï¼ä½çºåç§ person - 表çå¤éµï¼ã 注æèªååµå»ºç SEQUENCE å°è±¡çå稱å°ææ¯ - <table>_<serialcolumn>_seqï¼ é裡 table å serialcolumn - åå¥æ¯ä½ ç表çå稱åä½ ç SERIAL æ¬ä½çå稱ã - - é¡ä¼¼çï¼å¨ SERIAL å°è±¡é è¨æå¥å¾ä½ å¯ä»¥ç¨å½æ¸ currval() 檢索å賦å¼ç - SERIAL å¼ï¼ä¾å¦ï¼ - execute("INSERT INTO person (name) VALUES ('Blaise Pascal')"); - new_id = execute("SELECT currval('person_id_seq')"); - - 4.11.3) åæä½¿ç¨ currval() æå°è´åå¶ä»ç¨æ¶çè¡çªææ³åï¼ - - ä¸æãcurrval() è¿åçæ¯ä½ æ¬æ¬¡æ話é²ç¨æ賦çå¼èä¸æ¯ææç¨æ¶çç¶åå¼ã - - 4.11.4) çºä»éº¼ä¸å¨äºåç°å¸¸ä¸æ¢å¾éç¨åºåèå¢ï¼çºä»éº¼å¨åºåèæ¬ä½çåå¼ä¸åå¨éæ·å¢ï¼ - - çºäºæé«ä½µç¼æ§ï¼åºåèå¨éè¦çæå賦äºæ - £å¨éè¡çäºåï¼ä¸¦ä¸å¨äºåçµæä¹åä¸é²è¡éå®ï¼ éå°±æå°è´ç°å¸¸ä¸ - æ¢çäºåå¾ï¼åºåèæåºç¾ééã - - 4.12) ä»éº¼æ¯ OID ï¼ä»éº¼æ¯ CTID ï¼ - - PostgreSQL 裡åµå»ºçæ¯ä¸è¡è¨éé½æç²å¾ä¸åå¯ä¸ç OIDï¼é¤éå¨åµå»ºè¡¨æä½¿ç¨ - WITHOUT OIDS é¸é ã OID åµå»ºææèªåçæä¸å 4ä½åçµçæ´æ¸ï¼ææ OID å¨ç¸æ - PostgreSQL 伺æå¨ä¸åæ¯å¯ä¸çã ç¶èï¼å®å¨è¶é 40åæå°æº¢åºï¼ OID æ - ¤å¾æåºç¾éè¤ãPostgreSQL å¨å®çå§é¨ç³»çµ±è¡¨è£¡ä½¿ç¨ OID - å¨è¡¨ä¹é建ç«è¯ç¹«ã - - å¨ç¨æ¶çè³æ表ä¸ï¼æ好æ¯ä½¿ç¨ SERIAl ä¾ä»£æ¿ OID å çº SERIAL åªè¦ä¿è - å¨å®å表ä¸çæ¸å¼æ¯å¯ä¸çå°±å¯ä»¥äºï¼é樣å®æº¢åºçå¯è½æ§å°±é常å°äºï¼ - SERIAL8 å¯ç¨ä¾ä¿å8ååçµçåºåæ¸å¼ã - - CTID ç¨æ¼æ¨è帶èè³æå¡ï¼å°åï¼åï¼å¡å§ï¼å移çç¹å®çç©çè¡ã CTID - å¨è¨é被æ´æ¹æéè¼å¾ç¼çæ¹è®ãç´¢å¼è³æ使ç¨å®åæåç©çè¡ã - - 4.13) çºä»éº¼ææ¶å°é¯èª¤è³è¨ãERROR: Memory exhausted in AllocSetAlloc()ãï¼ - - éå¾å¯è½æ¯ç³»çµ±çèæ¬å§åç¨åäºï¼æèå§æ ¸å°æäºè³æºæè¼ä½çéå¶å¼ãå¨åå - postmaster ä¹å試試ä¸é¢çå½ä»¤ï¼ - ulimit -d 262144 - limit datasize 256m - - å決æ¼ä½ ç¨ç - shellï¼ä¸é¢å½ä»¤åªæä¸æ¢è½æåï¼ä½æ¯å®å°æä½ çé²ç¨è³æ段éå¶è¨å¾æ¯è¼é«ï¼ - å èä¹è¨±è½è®æ¥è©¢å®æãéæ¢å½ä»¤æç¨æ¼ç¶åé²ç¨ï¼ä»¥åææå¨éæ¢å½ä»¤éè¡å¾åµå»ºç - åé²ç¨ã - å¦æä½ æ¯å¨éè¡SQL客æ¶ç«¯æå çºå¾å°è¿åäºå¤ªå¤çè³æèåºç¾åé¡ï¼è«å¨éè¡å®¢æ¶ç - «¯ä¹åå·è¡ä¸è¿°å½ä»¤ã - - 4.14) æå¦ä½æè½ç¥éæéè¡çPostgreSQLççæ¬ï¼ - - å¾ psql 裡ï¼è¼¸å¥ SELECT version();æ令ã - - 4.15) æå¦ä½åµå»ºä¸åé è¨å¼æ¯ç¶åæéçæ¬ä½ï¼ - - ä½¿ç¨ CURRENT_TIMESTAMPï¼ - CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); - - 4.16) ææ樣é²è¡ outer join ï¼å¤é£æ¥ï¼ï¼ - - PostgreSQL æ¡ç¨æ¨æºç SQL èªæ³æ¯æå¤é£æ¥ãé裡æ¯å©åä¾åï¼ - SELECT * - FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col); - - ææ¯ - SELECT * - FROM t1 LEFT OUTER JOIN t2 USING (col); - - éå©åçå¹çæ¥è©¢å¨ t1.col å t2.col ä¸åé£æ¥ï¼ä¸¦ä¸è¿å t1 ä¸ - æææªé£æ¥çè¡ï¼é£äºå¨ t2 ä¸æ²æå¹éçè¡ï¼ã å³[å¤]é£æ¥ï¼RIGHT OUTER - JOINï¼å°è¿å t2 ä¸æªé£æ¥çè¡ã å®å¨å¤é£æ¥ï¼FULL OUTER JOINï¼å°è¿å t1 å t2 - ä¸æªé£æ¥çè¡ã ééµå OUTER å¨å·¦[å¤]é£æ¥ãå³[å¤]é£æ¥åå®å¨[å¤]é£æ¥ä¸ - æ¯å¯é¸çï¼æ®éé£æ¥è¢«ç¨±çºå§é£æ¥ï¼INNER JOINï¼ã - - 4.17) å¦ä½ä½¿ç¨æ¶åå¤åè³æ庫çæ¥è©¢ï¼ - - æ²æ辦æ³æ¥è©¢ç¶åè³æ庫ä¹å¤çè³æ庫ã - å çºPostgreSQLè¦å è¼èè³æ庫ç¸éç系統ç®éï¼ç³»çµ±è¡¨ï¼ï¼å æ - ¤è·¨è³æ庫çæ¥è©¢å¦ä½å·è¡æ¯ä¸å®çã - - éå å¢å¼æ¨¡å¡ contrib/dblink - å許æ¡ç¨å½æ¸èª¿ç¨å¯¦ç¾è·¨åº«æ¥è©¢ãç¶ç¶ç¨æ¶ä¹å¯ä»¥åæé£æ¥å°ä¸åçè³æ庫å·è - ¡æ¥è©¢ç¶å¾å¨å®¢æ¶ç«¯åä½µçµæã - - 4.18) å¦ä½è®å½æ¸è¿åå¤è¡æå¤åè³æï¼ - - å¨å½æ¸ä¸è¿åè³æè¨ééçåè½æ¯å¾å®¹æ使ç¨çï¼è©³æåè¦ï¼ - http://techdocs.postgresql.org/guides/SetReturningFunctions - - 4.19) çºä»éº¼æå¨ä½¿ç¨ PL/PgSQL å½æ¸ååè¨æ表æææ¶å°é¯èª¤è³è¨ãrelation with OID - ##### does not existãï¼ - - PL/PgSQL æç·©åå½æ¸çè³æ¬å§å®¹ï¼ç±æ¤å¸¶ä¾çä¸åä¸å¥½çå¯ä½ç¨æ¯è¥ä¸å - PL/PgSQL - å½æ¸è¨ªåäºä¸åè¨æ表ï¼ç¶å¾è©²è¡¨è¢«åªé¤ä¸¦é建äºï¼åå次調ç¨è©²å½æ¸å°å¤ - ±æï¼ å çºç·©åçå½æ¸å§å®¹ä»ç¶æåèçè¨æ表ã解決çæ¹æ³æ¯å¨ PL/PgSQL ä¸ - ç¨EXECUTE å°è¨æ表é²è¡è¨ªåãé樣æä¿èæ¥è©¢å¨å·è¡å總æ被éæ°è§£æã - - 4.20) ç®åæåªäºè³æè¤å¯«(replication)æ¹æ¡å¯ç¨ï¼ - - ãè¤å¯«ãåªæ¯ä¸åè¡èªï¼æ好幾種è¤å¯«æè¡å¯ç¨ï¼æ¯ç¨®é½æåªé»å缺é»ï¼ - - 主/å¾å¼è¤å¯«æ¹å¼æ¯å許ä¸å主伺æå¨æ¥åè®/寫çç³è«ï¼èå¤åå¾ä¼ºæå¨åªè½æ¥å - è®/SELECTæ¥è©¢çç³è«ï¼ ç®åææµè¡ä¸åè²»ç主/å¾PostgreSQLè¤å¯«æ¹æ¡æ¯ - Slony-I ã - - å¤å主伺æå¨çè¤å¯«æ¹å¼å許å°è®/寫çç³è«ç¼é給å¤å°ç主æ©ï¼é種æ¹å¼ç±æ¼é - è¦å¨å¤å°ä¼ºæå¨ä¹éåæ¥è³æè®å å¯è½æ帶ä¾è¼å´éçæ§è½æ失ï¼Pgcluster - æ¯ç®åé種æ¹æ¡ä¸æ好çï¼ä¸¦ä¸éå¯ä»¥åè²»ä¸è¼ã - - ä¹æä¸äºåæ¥éä»è²»ååºæ¼ç¡¬é«çè³æè¤å¯«æ¹æ¡ï¼æ¯æä¸è¿°å種è¤å¯«æ¨¡åã - - 4.21) çºä½æ¥è©¢çµæ顯示ç表åææ¬åèæçæ¥è©¢èªå¥ä¸çä¸åï¼çºä½å¤§å¯«çæä¸è½ä¿çï¼ - - æ常è¦çåå æ¯å¨åµå»ºè¡¨æå°è¡¨åææ¯æ¬å使ç¨äºéå¼è( ' ' - )ï¼ç¶ä½¿ç¨äºéå¼èå¾ï¼è¡¨åææ¬åï¼ç¨±çºæ¨è符ï¼åå²ææ¯åå 大å°å¯«çï¼ - éæè¬èä½ å¨æ¥è©¢æ表åææ¬åä¹æ使ç¨éå¼èï¼ä¸äºå·¥å·è»é«ï¼å pgAdmin - æå¨ç¼åºåµå»ºè¡¨çæ令æèªåå°å¨æ¯åæ¨è符ä¸å éå¼èã å æ¤ï¼çºäºæ¨è - 符ççµ±ä¸ï¼ä½ æè©²ï¼ - * å¨åµå»ºè¡¨æé¿åå°æ¨è符使ç¨éå¼èå¼èµ·ä¾ã - * å¨æ¨è符ä¸åªä½¿ç¨å°å¯«åæ¯ã - * ï¼çºäºèå·²åå¨çæ¨è符ç¸åï¼å¨æ¥è©¢ä¸ä½¿ç¨éå¼èå°æ¨è符å¼èµ·ä¾ã |