用PostgreSQL运行文件中的SQL程序（实验讲解）

用postgresql运行文件中的sql程序

首先文件内容如下：

$ ls
barcode.sql                 drop_tables.sql  orderline.sql
create_tables-bpsimple.sql  item.sql         postgresql.md
customer.sql                orderinfo.sql    stock.sql

然后创建bpsimple

$ su
密码：
# su - postgres
$ createdb bpsimple
password: 

然后可以两次退出exit，返回原来的用户进行操作，比较安全。

首先在数据库bpsimple创建中创建各种表：

$ psql -u postgres -d bpsimple -f create_tables-bpsimple.sql 
password for user postgres: 
create table
create table
create table
create table
create table
create table

其中create_tables-bpsimple.sql中的内容如下：

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

其他文件也按照此方法即可创建这个数据库。下面见结果（此处注意语句末尾的“;”）：

$ psql -u postgres -d bpsimple
password for user postgres: 
psql.bin (10.4)
type "help" for help.

bpsimple=# table item
bpsimple-# ;
 item_id |  description  | cost_price | sell_price 
---------+---------------+------------+------------
       1 | wood puzzle   |      15.23 |      21.95
       2 | rubik 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)
bpsimple=# \dt
           list of relations
 schema |   name    | type  |  owner   
--------+-----------+-------+----------
 public | barcode   | table | postgres
 public | customer  | table | postgres
 public | item      | table | postgres
 public | orderinfo | table | postgres
 public | orderline | table | postgres
 public | stock     | table | postgres
(6 rows)
bpsimple=# table customer;
 customer_id | title |   fname   |  lname  |   addressline    |   town    |  zip
code   |  phone   
-------------+-------+-----------+---------+------------------+-----------+-----
-------+----------
           1 | miss  | jenny     | stones  | 27 rowan avenue  | hightown  | nt2 
1aq    | 023 9876
           2 | mr    | andrew    | stones  | 52 the willows   | lowtown   | lt5 
7ra    | 876 3527
           3 | miss  | alex      | matthew | 4 the street     | nicetown  | nt2 
2tx    | 010 4567
           4 | mr    | adrian    | matthew | the barn         | yuleville | yv67
 2wr   | 487 3871
           5 | mr    | simon     | cozens  | 7 shady lane     | oakenham  | oa3 
6qw    | 514 5926
           6 | mr    | neil      | matthew | 5 pasture lane   | nicetown  | nt3 
7rt    | 267 1232
           7 | mr    | richard   | stones  | 34 holly way     | bingham   | bg4 
2we    | 342 5982
           8 | mrs   | ann       | stones  | 34 holly way     | bingham   | bg4 
2we    | 342 5982
           9 | mrs   | christine | hickman | 36 queen street  | histon    | ht3 
5em    | 342 5432
          10 | mr    | mike      | howard  | 86 dysart street | tibsville | tb3 
7fg    | 505 5482
          11 | mr    | dave      | jones   | 54 vale rise     | bingham   | bg3 
8gd    | 342 8264
          12 | mr    | richard   | neill   | 42 thatched way  | winersby  | wb3 
6gq    | 505 6482
          13 | mrs   | laura     | hardy   | 73 margarita way | oxbridge  | ox2 
3hx    | 821 2335
          14 | mr    | bill      | neill   | 2 beamer street  | welltown  | wt3 
8gm    | 435 1234
          15 | mr    | david     | hudson  | 4 the square     | milltown  | mt2 
6rt    | 961 4526
(15 rows)