Contents

1 PostgreSQL 覃伎
2
3 一危磯伎 襷り鍵
4 ろ碁ゼ 一危磯j鍵


1 PostgreSQL 覃伎 #

2004 10 豐.. PostgreSQL豈 覲 蠍磯螳 蟲. 豈 覈谿襯 覲願 蠍一 螳豢 螳 . 伎 襷る螻 DBMS 蟆譯殊 螻旧 DBMS朱 る(?) 蟆 殊螻 襷. DBMS 讌螻 蠍磯レ 覿覿 讌螻 朱, 轟 DBMS 蠍磯レ 螻 螻 .

伎 覓語れ 蠍磯蓋 誤 蟆企. 蠍磯蓋蠍磯レ 螳襦 磯Μ螳 牛 螻 SELECt, UPDATE, DELETE, INSERT, GROUP BY, HAVING炎骸 螳 蠍磯蓋れ 企 蠍磯レ 螻給螻 る 蟆襷 覲伎譴 蟆企. 襷 覿覿 襦 , 蠏碁Μ螻 碁Μ蟇一 麹 蟆企. 伎SQL 伎 襷 麹 蟆企.

れ襦 覲語語 PostgreSQL 豌 れ 覲伎. 蠏碁Μ螻 豌 蟆企. 螻 蟆企手 蠍一ヾ るジ DBMS襯 る 蟆螻 一危磯伎れ 蠍一企 訖企. 覦焔語企狩!! 覯 企慨 蟆 譴. 襷一る 炎碓る瓦 覘螳 一 蟆 蟆曙 讙 り規朱 螳 企慨. 蠏碁 瑚 PostgreSQL 螳覦れ 蟆 . 蠍語 .

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

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=>

..壱~

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);