[edit]
1 譟壱蠍 #
PostgreSQL ANSI-SQL(SQL92) 襷蟆 り. 蠏碁覩襦 ANSI-SQL 伎手鍵 覲伎ろる觀磯ゼ 螻牛.
c:\>psql UserDB yasi
Password:
Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
UserDB=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-------
public | barcode | table | yasi
public | customer | table | yasi
public | item | table | yasi
public | orderinfo | table | yasi
public | orderline | table | yasi
public | stock | table | yasi
(6 rows)
UserDB=> select table_catalog, table_schema, table_name
UserDB-> from information_schema.tables
UserDB-> where table_type = 'BASE TABLE'
UserDB-> and table_schema = 'public';
table_catalog | table_schema | table_name
---------------+--------------+------------
UserDB | public | customer
UserDB | public | item
UserDB | public | orderinfo
UserDB | public | stock
UserDB | public | orderline
UserDB | public | barcode
(6 rows)
UserDB=> select table_catalog, table_schema, table_name
UserDB-> from information_schema.tables
UserDB-> where table_type = 'base table'
UserDB-> and table_schema = 'public';
table_catalog | table_schema | table_name
---------------+--------------+------------
(0 rows)
UserDB=>
伎 覲企 psql覈轟企 \dt襯 覃 企 覈襦 覲 . るジ 覦覯朱 information_schema.tables 觀磯ゼ 伎覃 企 覈襦 覲 . 襦 覲 where 譟郁唄朱 覓語襯 蟲覿 蟆 . 覓語 碁語襦(;) 覿 蟆 覲 . 願 覓語レ る 蟆 覩誤. MSSQL Server Go 覩語企. psql \g襯 ロ企 碁語襦 螻朱ゼ 螳讌 .
UserDB=> select * from item;
item_id | description | cost_price | sell_price
---------+---------------+------------+------------
1 | Wood Puzzle | 15.23 | 21.95
2 | Rubic Cube | 7.45 | 11.49
3 | Linux CD | 1.99 | 2.49
4 | Tissues | 2.11 | 3.99
5 | Picture Frame | 7.54 | 9.95
6 | Fan Small | 9.23 | 15.75
7 | Fan Large | 13.36 | 19.95
8 | Toothbrush | 0.75 | 1.45
9 | Roman Coin | 2.34 | 2.45
10 | Carrier Bag | 0.01 | 0.00
11 | Speakers | 19.73 | 25.32
(11 rows)
UserDB=> select sell_price - cost_price as "企"
UserDB-> from item
UserDB-> where description in ('Linux CD', 'Wood Puzzle');
企
------
6.72
0.50
(2 rows)
UserDB=>
觝(-) 一郁骸 IN 一一襯 蟆 覲伎譯朱 企. 觝 一一朱 蠍磯蓋朱 螳讌螻 貉殊 企 伎. 蠏碁覩襦 ALIAS襯 譴 蟆企.
UserDB=> select sell_price "襷り蟆" from item;
ERROR: syntax error at or near ""襷り蟆"" at character 19
LINE 1: select sell_price "襷り蟆" from item;
^
UserDB=> select sell_price '襷り蟆' from item;
ERROR: type "sell_price" does not exist
UserDB=> select sell_price 襷り蟆 from item;
ERROR: syntax error at or near "襷り蟆" at character 19
LINE 1: select sell_price 襷り蟆 from item;
^
UserDB=>
譟郁 曙 蟆 覲伎碁. るジ DBMS 蟆 讌 蟆 覲 . 覘 蠏碁 伎覃 暑 . 伎 覿ｸ 訖伎..
UserDB=> explain select distinct town from customer;
QUERY PLAN
-------------------------------------------------------------------------
Unique (cost=69.83..74.83 rows=200 width=66)
-> Sort (cost=69.83..72.33 rows=1000 width=66)
Sort Key: town
-> Seq Scan on customer (cost=0.00..20.00 rows=1000 width=66)
(4 rows)
UserDB=> select distinct town from customer;
town
-----------
Bingham
Hightown
Histon
Lowtown
Milltown
Nicetown
Oahenham
Oxbridge
Tibsville
Welltown
Winersby
Yuleville
(12 rows)
UserDB=>
EXPLAIN れ襯 覃 ろ螻 覲 . 覘...讌 ろ螻 豢襷 螳 ″讌 襦 ″讌 . Primary Key襯 襷れ 碁煙り 焔蟾? 襷 碁煙れ 覲願 企螳 蟆企.
UserDB=> select table_name
UserDB-> from information_schema.tables
UserDB-> where table_name like '%index%';
table_name
------------------------
pg_index
pg_indexes
pg_stat_all_indexes
pg_stat_sys_indexes
pg_stat_user_indexes
pg_statio_all_indexes
pg_statio_sys_indexes
pg_statio_user_indexes
(8 rows)
覲伎碁. 襷 る狩願骸 觜訣蟆 sys, user, all襦 蟆 覲企 豢 企螳 願 覲願 讌 . 願 覃一危一 煙企. ..UserDB=> select table_name, column_name, ordinal_position, data_type, is_nullable UserDB-> from information_schema.columns UserDB-> where table_name = 'customer' UserDB-> and table_schema = 'public' UserDB-> and table_catalog = 'UserDB'; table_name | column_name | ordinal_position | data_type | is_nullable ------------+-------------+------------------+-------------------+------------- customer | customer_id | 1 | integer | NO customer | zipcode | 7 | character | NO customer | title | 2 | character | YES customer | phone | 8 | character varying | YES customer | town | 6 | character varying | YES customer | addressline | 5 | character varying | YES customer | lname | 4 | character varying | NO customer | fname | 3 | character varying | YES (8 rows) UserDB=>朱 貉殊 覲企ゼ 危ｴ覲伎. 碁煙 覲企ゼ 覲伎.
UserDB=> select * from pg_statio_user_indexes UserDB-> ; relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit -------+------------+------------+-----------+--------------+---------------+-------------- 17245 | 17248 | public | customer | customer_pk | 0 | 0 17252 | 17255 | public | item | item_pk | 0 | 0 17259 | 17262 | public | orderinfo | orderinfo_pk | 0 | 0 17264 | 17266 | public | stock | stock_pk | 0 | 0 17268 | 17270 | public | orderline | orderline_pk | 0 | 0 17272 | 17274 | public | barcode | barcode_pk | 0 | 0 (6 rows) UserDB=>
ロ 覲願 . 覓企 願 螳.
UserDB=> select indexdef from pg_indexes
UserDB-> where tablename = 'customer';
indexdef
-----------------------------------------------------------------------
CREATE UNIQUE INDEX customer_pk ON customer USING btree (customer_id)
(1 row)
UserDB=>
. !! 覘螳 るジ 覓語レ . 碁煙れ 譬襯螳 覓企 螳讌 Using 牛 伎語. 襷 覲伎.
CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ WHERE predicate ]
轟危 蟆襷覲伎. 覓朱 豐讌 覲語語蟆 轟危蟆 覲伎企 蟆企.
method The name of the method to be used for the index. Choices are btree, hash, rtree, and gist. The default method is btree. opclass The name of an operator class. See below for details. predicate The constraint expression for a partial index.
~ 覈襯願. 蠍一 襷. 豈 覺手. 覦 る 朱...給. 蠏碁 螳 PostgresSQL 伎 database.sarang.net 谿場覲伎給. 企蟆 る 蟲一.
<蟆蠍>
btree 吴碁 蟇碁Μ 碁煙れ願 譯朱 企 覓語 蟇碁Μ 碁煙れ.
btree 吴碁 蟇碁Μ 碁煙れ願 譯朱 企 覓語 蟇碁Μ 碁煙れ.
rtree れ姶一危壱 一企 碁煙れ願, 譯朱 2谿 螳讌 一危 point polygon 一 一危磯ゼ ロ 企 蟇碁Μ 碁煙れ.
hash hashing 覦覯 伎 碁煙 覦覯. hash 碁Μ蟲譟磯ゼ 螳讌讌 螻 hash function 伎蟆 轟.
gist GeneraIzed Search Tree 曙願, 螳覦れ 碁煙 れ手 螳覃 曙給. contrib 启 螳覃 れ れ 讌一... 蠏碁 れ 碁煙るゼ 襷り 苦 覈 貊 覿 讌 朱 gist朱 伎覃 index襯 觜蟲 所 襷 譯... 蠏碁, gist 蠏 豌企 碁煙る 覲願鹸 企旧給. 讌襷, gist襯 伎伎 rtree on gist 轟 btree on gist, int array on gist 煙 碁煙るゼ 襷 螻, Create Index 覓語レ 企蟆 蟲豌危 碁煙るゼ 給...
</蟆蠍>
</蟆蠍>
磯 蠍磯蓋朱 焔 碁煙る btree手 給. rtree れ姶企朱 蟆 覲企 PostgreSQL 一危 襴曙 覈視慨 碁煙りれ. 讌 一危壱 危ｴ覲伎 朱 覘.. 蠏碁る 螻 願.
hash 磯Μ螳 牛 螻 hash襯 伎 蟆願譯. 覯蟆 一 覲襦 蟆. 狩 蟆 襷れ 蟆譯.. 一一 蟆企 CPU 磯 襷 焔レ姶襯 覲伎 蟆.
gist 蟲豌危 碁煙る手 蟆 覲企 MSSQL Server 企ろ磯 碁煙 豁れ朱 螳覃 蟆 螳給. 覘..讌蟾讌 讌 訖. 讌襴覲DB襯 蟆企讌.. 襷 願碓碁..
UserDB=> create index idx_fn_substr_town UserDB-> on customer UserDB-> using btree (substr(town,1,1)) UserDB-> where (title = 'MR'); CREATE INDEX
~~ 蠍磯レ. 蠍磯 碁煙る 燕 螻, 碁煙 譬襯 螳讌願, 轟 覿覿襷 碁煙るゼ 蟆 給.
UserDB=> create index idx_town
UserDB-> on customer(town);
CREATE INDEX
UserDB=> explain select town from customer
UserDB-> where town = 'Hightown';
QUERY PLAN
---------------------------------------------------------
Seq Scan on customer (cost=0.00..1.19 rows=1 width=66)
Filter: ((town)::text = 'Hightown'::text)
(2 rows)
UserDB=> explain select town from customer
UserDB-> where town = 'Lowtown';
QUERY PLAN
---------------------------------------------------------
Seq Scan on customer (cost=0.00..1.19 rows=1 width=66)
Filter: ((town)::text = 'Lowtown'::text)
(2 rows)
UserDB=>
＜;;; Index朱 願 覲伎. 覓企 一危郁 渚螳 覺. 覈豌蟇伎 讌 ｌ伎 . る 碁煙 蟆襷 覲願 伎 蟆蟲一... 
4_Item_Insert_Data.txt 襯 れ企 ｌ殊語.
4_Item_Insert_Data.txt 襯 れ企 ｌ殊語.
UserDB=> create table big_item
UserDB-> (
UserDB(> item_id varchar(64) not null,
UserDB(> cost_price numeric ,
UserDB(> sell_price numeric ,
UserDB(> CONSTRAINT big_item_pk PRIMARY KEY(item_id)
UserDB(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "big_item_pk" for table "big_item"
CREATE TABLE
UserDB=> select count(*) from big_item;
count
-------
4961
(1 row)
UserDB=> select * from big_item
UserDB-> where item_id = 'W59201';
item_id | cost_price | sell_price
---------+------------+------------
W59201 | 50 | 50
(1 row)
UserDB=> explain select * from big_item
UserDB-> where item_id = 'W59201';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using big_item_pk on big_item (cost=0.00..4.82 rows=1 width=178)
Index Cond: ((item_id)::text = 'W59201'::text)
(2 rows)
UserDB=>
碁煙 蟆 覲伎企蟲一...壱~
鏤