_覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
FrontPage › PostgreSQL豢企慨蠍
|
|
[edit]
1 PostgreSQL 覃伎 #2004 10 豐.. PostgreSQL豈 覲 蠍磯螳 蟲. 豈 覈谿襯 覲願 蠍一 螳豢 螳 . 伎 襷る螻 DBMS 蟆譯殊 螻旧 DBMS朱 る(?) 蟆 殊螻 襷. DBMS 讌螻 蠍磯レ 覿覿 讌螻 朱, 轟 DBMS 蠍磯レ 螻 螻 .
伎 覓語れ 蠍磯蓋 誤 蟆企. 蠍磯蓋蠍磯レ 螳襦 磯Μ螳 牛 螻 SELECt, UPDATE, DELETE, INSERT, GROUP BY, HAVING炎骸 螳 蠍磯蓋れ 企 蠍磯レ 螻給螻 る 蟆襷 覲伎譴 蟆企. 襷 覿覿 襦 , 蠏碁Μ螻 碁Μ蟇一 麹 蟆企. 伎SQL 伎 襷 麹 蟆企.
れ襦 覲語語 PostgreSQL 豌 れ 覲伎. 蠏碁Μ螻 豌 蟆企. 螻 蟆企手 蠍一ヾ るジ DBMS襯 る 蟆螻 一危磯伎れ 蠍一企 訖企. 覦焔語企狩!! 覯 企慨 蟆 譴. 襷一る 炎碓る瓦 覘螳 一 蟆 蟆曙 讙 り規朱 螳 企慨. 蠏碁 瑚 PostgreSQL 螳覦れ 蟆 . 蠍語 .
[edit]
2 #覓碁 れ螻 螳.
CREATE USER name [ [ WITH ] option [ ... ] ] where option can be: SYSID uid | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] | VALID UNTIL 'abstime' c:\>psql template1 postgres 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 template1=# create user yasi template1-# with sysid 5001 password '1234' template1-# valid until 'infinity'; CREATE USER template1=# create user sibal template1-# with sysid 5002 template1-# valid until 'infinity'; CREATE USER 伎 燕 蠏碁9 燕襦 .
template1=# create group "user" template1-# with sysid 5000 template1-# user yasi; CREATE GROUP sibal user 蠏碁9 襦 企慨. 蠏碁Μ螻 sibal れ れ.
template1=# alter group "user" add user sibal; ALTER GROUP template1=# alter user sibal with password '1234'; ALTER USER [edit]
3 一危磯伎 襷り鍵 #覓碁 れ螻 螳.
CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner ] [ LOCATION [=] 'dbpath' ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] ] れ螻 螳 psql 伎 template1 一危磯伎る 蠏狩.
c:\>psql template1 postgres 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 template1=# 一危磯伎るゼ 襷り鍵 襾殊 企ろ伎るゼ 襷. 企ろ伎る 朱Μ 一危磯伎 企, 螳 殊 . 朱Μ朱 覲 企ろ伎れ 蠏狩 蟆豌 覲伎伎襷 れ襦 螳 殊 蠏狩 蟆企. 讌讀蟆 襷 企ろ伎れ 覓碁 . 企ろ伎るゼ 襷る 襾殊 蟆暑 襴襯 襷.
template1=# CREATE TABLESPACE UserTBS template1-# OWNER postgres template1-# LOCATION 'C:\\PostgresData'; CREATE TABLESPACE 伎 一危磯伎るゼ 襷れ 覲企襦 . 覈麹襦語 createdb 覈轟企ゼ ろ. 譯殊 蟆 template1 螳 伎 る 蟆企. 襷 るジ 螳 蠏殊企朱 れ螻 螳 覃語襯 訖襴磯.
c:\>createdb -DUserTBS -EEUC_KR -Oyasi -Uyasi UserDB Password: createdb: database creation failed: ERROR: source database "template1" is being accessed by other users讒...伎 れ襷 るジ 一危磯伎るゼ 燕 譯殊伎 . c:\>createdb --help createdb creates a PostgreSQL database. Usage: createdb [OPTION]... [DBNAME] [DESCRIPTION] Options: -D, --tablespace=TABLESPACE default tablespace for the database -E, --encoding=ENCODING encoding for the database -O, --owner=OWNER database user to own the new database -T, --template=TEMPLATE template database to copy -e, --echo show the commands being sent to the server -q, --quiet don't write any messages --help show this help, then exit --version output version information, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as -W, --password prompt for password By default, a database with the same name as the current user is created. Report bugs to <pgsql-bugs@postgresql.org>. c:\>createdb -DUserTBS -EEUC_KR -Oyasi -Uyasi UserDB Password: CREATE DATABASE c:\> 燕 DB 蠏狩 覲企襦 .
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=> ..壱~
[edit]
4 ろ碁ゼ 一危磯j鍵 # Databases with PostgreSQL , 覲企誤, Richard Neil 螻旧, 伎狩語 3レ 企. pgAddminIII襯 郁 企 DB襯 襷一る 企Ν覃 SQL企朱 蟲覯殊 燕. 蠏瑚 企Ν覃 SQL ロ螻, ろ螻, 蟆郁骸襯 覲 企殊伎誤 伎 覲 . る 覘襴語 覈襯願覃 襷一 瑚るゼ 牛 給企. 覃 るジ 豪語..覃 るジ豈...覓危 讌企慨る 螳 蟆企.
覘..蠏碁Μ 譬讌襷 . 一危郁 れ襦 郁屋 讌 蠍 覓語 曙^蟇伎 蟇碁Μ讌 . 覘..豢 殊螳 ERD襯 蠏碁る慨. 蠏碁碁り鍵 覲企る 蠏碁 覈螳 蠏語企慨. SQLろ襴渚 蟯螻螳 襷碕伎 讌 蟆 覲 朱.. 蠏碁殊 殊螳 蟯螻襯 襷碕る 螳 蠏碁る蓋 蟆企.. customer
item orderinfor stock orderline barcode 企蟆 6螳 企企.
create table customer ( customer_id serial , title char(4) , fname varchar(32) , lname varchar(32) not null, addressline varchar(64) , town varchar(32) , zipcode char(10) not null, phone varchar(16) , CONSTRAINT customer_pk PRIMARY KEY(customer_id) ); create table item ( item_id serial , description varchar(64) not null, cost_price numeric(7,2) , sell_price numeric(7,2) , CONSTRAINT item_pk PRIMARY KEY(item_id) ); create table orderinfo ( orderinfo_id serial , customer_id integer not null, date_placed date not null, date_shipped date , shipping numeric(7,2) , CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id) ); create table stock ( item_id integer not null, quantity integer not null, CONSTRAINT stock_pk PRIMARY KEY(item_id) ); create table orderline ( orderinfo_id integer not null, item_id integer not null, quantity integer not null, CONSTRAINT orderline_pk PRIMARY KEY(orderinfo_id, item_id) ); create table barcode ( barcode_ean char(13) not null, item_id integer not null, CONSTRAINT barcode_pk PRIMARY KEY(barcode_ean) ); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Miss','Jenny','Stones','27 Rowan Avenue','Hightown','NT2 1AQ','023 9876'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Andrew','Stones','52 The Willows','Lowtown','LT5 7RA','876 3527'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Miss','Alex','Matthew','4 The Street','Nicetown','NT2 2TX','010 4567'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Adrian','Matthew','The Barn','Yuleville','YV67 2WR','487 3871'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Simon','Cozens','7 Shady Lane','Oahenham','OA3 6QW','514 5926'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Neil','Matthew','5 Pasture Lane','Nicetown','NT3 7RT','267 1232'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Richard','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Ann','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Christine','Hickman','36 Queen Street','Histon','HT3 5EM','342 5432'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Mike','Howard','86 Dysart Street','Tibsville','TB3 7FG','505 5482'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Dave','Jones','54 Vale Rise','Bingham','BG3 8GD','342 8264'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Richard','Neill','42 Thached way','Winersby','WB3 6GQ','505 6482'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Laura','Hendy','73 Margeritta Way','Oxbridge','OX2 3HX','821 2335'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Bill','O\''Neill','2 Beamer Street','Welltown','WT3 8GM','435 1234'); insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','David','Hudson','4 The Square','Milltown','MT2 6RT','961 4526'); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(3,'2000-03-13','2000-03-17', 2.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(8,'2000-06-23','2000-06-24', 0.00); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(15,'2000-09-02','2000-09-12', 3.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(13,'2000-09-03','2000-09-10', 2.99); insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(8,'2000-07-21','2000-07-24', 0.00); insert into stock(item_id, quantity) values(1,12); insert into stock(item_id, quantity) values(2,2); insert into stock(item_id, quantity) values(4,8); insert into stock(item_id, quantity) values(5,3); insert into stock(item_id, quantity) values(7,8); insert into stock(item_id, quantity) values(8,18); insert into stock(item_id, quantity) values(10,1); insert into barcode(barcode_ean, item_id) values('6241527836173', 1); insert into barcode(barcode_ean, item_id) values('6241574635234', 2); insert into barcode(barcode_ean, item_id) values('6264537836173', 3); insert into barcode(barcode_ean, item_id) values('6241527746363', 3); insert into barcode(barcode_ean, item_id) values('7465743843764', 4); insert into barcode(barcode_ean, item_id) values('3453458677628', 5); insert into barcode(barcode_ean, item_id) values('6434564564544', 6); insert into barcode(barcode_ean, item_id) values('8476736836876', 7); insert into barcode(barcode_ean, item_id) values('6241234586487', 8); insert into barcode(barcode_ean, item_id) values('9473625532534', 8); insert into barcode(barcode_ean, item_id) values('9473627464543', 8); insert into barcode(barcode_ean, item_id) values('4587263646878', 9); insert into barcode(barcode_ean, item_id) values('9879879837489', 11); insert into barcode(barcode_ean, item_id) values('2239872376872', 11); insert into item(description, cost_price, sell_price) values('Wood Puzzle', 15.23, 21.95); insert into item(description, cost_price, sell_price) values('Rubic Cube', 7.45, 11.49); insert into item(description, cost_price, sell_price) values('Linux CD', 1.99, 2.49); insert into item(description, cost_price, sell_price) values('Tissues', 2.11, 3.99); insert into item(description, cost_price, sell_price) values('Picture Frame', 7.54, 9.95); insert into item(description, cost_price, sell_price) values('Fan Small', 9.23, 15.75); insert into item(description, cost_price, sell_price) values('Fan Large', 13.36, 19.95); insert into item(description, cost_price, sell_price) values('Toothbrush', 0.75, 1.45); insert into item(description, cost_price, sell_price) values('Roman Coin', 2.34, 2.45); insert into item(description, cost_price, sell_price) values('Carrier Bag', 0.01, 0.0); insert into item(description, cost_price, sell_price) values('Speakers', 19.73, 25.32); insert into orderline(orderinfo_id, item_id, quantity) values(1, 4, 1); insert into orderline(orderinfo_id, item_id, quantity) values(1, 7, 1); insert into orderline(orderinfo_id, item_id, quantity) values(1, 9, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 10, 1); insert into orderline(orderinfo_id, item_id, quantity) values(2, 7, 2); insert into orderline(orderinfo_id, item_id, quantity) values(2, 4, 2); insert into orderline(orderinfo_id, item_id, quantity) values(3, 2, 1); insert into orderline(orderinfo_id, item_id, quantity) values(3, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(4, 5, 2); insert into orderline(orderinfo_id, item_id, quantity) values(5, 1, 1); insert into orderline(orderinfo_id, item_id, quantity) values(5, 3, 1);
鏤
|
螻糾鍵 螳. 蠏碁 伎螻 蠏 譴 蟾. |