| _覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
|
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 伎 燕 蠏碁９ 燕襦 .
template1=# create group "user" template1-# with sysid 5000 template1-# user yasi; CREATE GROUP sibal user 蠏碁９ 襦 企慨. 蠏碁Μ螻 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 ろ碁ゼ 一危磯ｊ鍵 # 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);
鏤
|
磯Μ 殊 . る 朱 朱 訖企. ( ) |