postgresql+command

postgresql

データを入れる

• insert into {table} (**, **, **, **, **) values (*, *, *, *, *)

  test=# insert into weather (city, temp_lo, temp_hi, prcp, date)
  values ('san fr ancisco', 43, 57, 0.0, '2007-07-10');

テーブル内容を表示

• Select * from {db};

抽出する

• 例：SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
• 例：

  test=# select * from weather where city = 'san francisco' and prcp > 0;
      city      | temp_lo | temp_hi | prcp |    date
  ---------------+---------+---------+------+------------
  san francisco |      46 |      50 | 0.25 | 2006-07-12
  (1 row)

結果から重複行を除くように指定

• select distinct {column} from {db};

  test=# select distinct city from weather;
      city
  ---------------
  hayward
  new jearsy
  new york
  philadelphia
  san francisco
  (5 rows)

テーブルを結合する

• select * from {table_id}, {table_id_2} where {column} = {column}

  test=# select * from weather, cities where city = name;
      city      | temp_lo | temp_hi | prcp |    date    |     name      |   location
  ---------------+---------+---------+------+------------+---------------+-------------
   san francisco |      46 |      50 | 0.25 | 2006-07-12 | san francisco | (-194,53.9)
  san francisco |      43 |      57 |    0 | 2007-07-10 | san francisco | (-194,53.9)
  (2 rows)

選ぶ

• select {選定するキーワード} from {table};

  test=# select * from weather where temp_lo = (select max(temp_lo) from weather);
      city     | temp_lo | temp_hi | prcp |    date
  --------------+---------+---------+------+------------
  philadelphia |      50 |      90 |  5.1 | 2006-07-05
  (1 row)

更新する

• update {table} set {column}={新しい値}, {column}={新しい値} where {column} =/ > / < {選択値}

  test=# update weather set temp_hi = temp_hi +2, temp_lo = temp_lo +2 where date > '2006-07-12';
  UPDATE 3

コマンドをviewに登録して楽をする

• create view {viewの名前} select {内容} from {table} where {column}={値};
• select * from {viewの名前}で参照する事が出来る

  test=# create view myview as select city, temp_lo, temp_hi, prcp, date, location from weather, cities where city = name;
  CREATE VIEW
  test=# select * from myview;
      city      | temp_lo | temp_hi | prcp |    date    |  location
  ---------------+---------+---------+------+------------+-------------
  san francisco |      46 |      50 | 0.25 | 2006-07-12 | (-194,53.9)
  san francisco |      41 |      55 |    0 | 2007-07-10 | (-194,53.9)
  (2 rows)