とりあえず備忘録的に書いておこうと思います。
CentOS6にpostgreSQLを導入してとりあえず使えるようにするまでの記事も書いています。↓
CentOS6にpostgreSQLを導入して使えるようにするまで
postgresqlの基本操作
“”ダブルクォーテーションが使えない
コメントアウト
— １行コメントアウト
/**/ 複数行コメントアウト
データ型
数値: integer(int) reak, serial
文字: char(5), varchar(255), text
真偽: boolean TRUE FALSE t f
日付: date, time, timesatmp(日付と時間)
制約
not null 空の値を許さない
unique 重複を許さない
check 内容をチェック check(length(body) > 5)
default defalt TRUEなど default ‘now’など
primary key (not null, unique) 1つだけしか設定できない
postgresqlの起動や停止
#サービスの起動 sudo service postgresql-11 start
#サービスの再起動 sudo service postgresql-11 restart
#サービスの停止 sudo service postgresql-11 stop
#サービスの状態を確認 sudo service postgresql-11 status
#初期化（データベースクラスタの作成）
service postgresql initdb
コマンド
psql -l データベースの一覧表示
createdb dbname データベースの作成
dropdb dbname データベースの削除
psql hoge データベースへの接続
postgresqlの、シェルのコマンド
\l データベース一覧の表示
\q データベースから抜ける
\dt テーブルの一覧を表示
\dv ビューの一覧を表示
\d tablename テーブルの内容を表示
\i hoge.sql 外部ファイルの使用
\x 拡張表示
SQL文
テーブルの作成と削除
create table hoge(カラム名 データ型, カラム名 データ型); drop table hoge;
テーブル名の変更
alter table hoge rename to hogehoge;
テーブルに値の挿入
insert into posts(title, body) values('title1', 'body1111');
where
select * from posts; --レコードをすべて表示
select * from users where score > 4; --条件付き -- < <= >= > = != like('%' '_') and or
select * from users where name = 'taguti';
select * from users where name like '%i';
order
select * from users order by score; --昇順 select * from users order by score desc; --降順 select * from users order by team, score desc; --並べ替えた上でさらに並べ替える
limit
select * from users limit 3; select * from users limit 3 offset3; --4番目から3件を表示 select * from users order by score desc limit 3; --スコアが上位３人
count distinct
select count(*) from users; --行のカウント select distinct team from users; --重複行をまとめる
group by グループ化
select team, sum(score) from users group by team; --グループ化 select team, sum(score) from users group by team having sum(score) > 10.0; --グループ化してさらに条件をしぼる
関数
sum() max() min() avg()
select avg(score) from users;
select name, length(name) from users; --名前の文字数
select concat(name, ' (', team, ')') from users; --文字列の連結
select concat(name, ' (', team, ')') as namelabel from users; --asは名前を変える
select substring(team, 1, 1) from users; --team の 1文字目から1文字目だけを取り出す
select * from users order by random() limit 1; --ランダムに1件だけ取り出す
値の更新
update users set score = 5.8 where name = 'taguti'; update users set score = score + 1 where team = 'red'; --赤チームのものだけ1増やす delete from users where score < 3.0; --scoreが3より低い人を削除する
カラム（フィールドの追加)
alter table users add fullname varchar(255) --追加 alter table users drop fullname; --削除 alter table users rename name to myname; --カラム名の変更 alter table users alter myname type varchar(32); --カラムのタイプを変更 create index hoge_index on users(team); --indexの追加 drop index hoge_index; indexの削除
紐づけ whereで外部キーと主キーの紐づけを行う
select users.name, posts.title from users, posts where users.id = posts.user_id; select u.name, p.title from users u, posts p where u.id = p.user_id and u.id = 1; --省略
View
create view hoge as hogehoge drop view hoge
トランザクション
begin; 開始 commit; コミット rollbak; 取り消し
ユーザーの作成と削除
ユーザーの作成やデータベースの作成について、知識が足りないことがあるので、補足として書いておきます。postgreSQLのユーザーは最初のうちは概念がよくわからなくなるので頭に入れておくとよいかもしれないです。
postgreSQLでユーザを追加するにはcreateuserコマンドを使用して作成しますが、PostgreSQLにユーザ作成するのはスーパーユーザで行ないます。ここでのスーパーユーザーとはpostgreSQLのスーパーユーザーのことを指しています。デフォルトではpostgresユーザーがスーパーユーザーになっています。
つまりはユーザーを作成するには、su – postgres↓の状態になっている必要があります
$ su - postgres パスワード: -bash-4.1$ createuser hoge;
createuserにはいろいろなオプションがあります。あとあと調べることになりそうなので書いておきました↓
| -a(–adduser) | 他のユーザを作成することが許可。 ※このオプションを付けるとスーパーユーザ。 |
|---|---|
| -d(–createdb) | データベースの作成を許可 |
| -U [接続ユーザ名] (–username) | PostgreSQLに接続するユーザ名を指定します |
| -P(–pwprompt) | パスワードのプロンプトを表示します |
これらのオプションを全て使用すると下記のようになりました。
-bash-4.1$ createuser -a -d -U postgres -P sasaki 新しいロールのためのパスワード: もう一度入力してください：
postgreSQLのシェルからユーザーを作成する場合は以下のような書き方になります。
postgres=# create user hoge; CREATE ROLE
データベースの作成
データベースの作成はcreatedbコマンドを使います。
データベースの作成に関して知っておいたほうがよさそうなのは、データベースの使用者を指定する -O オプションです。
その他もいろいろあるので下記に書いておきます。
| -E [エンコーディング名] (–encording) | 作成するデータベース内で使用する文字エンコード方式を指定します。 |
|---|---|
| -O [オーナ名] (–owner) | 作成するデータベースの所有者となるユーザを指定します。 |
| -U [接続ユーザ名] (–username) | PostgreSQLに接続するユーザ名を指定します |
オプションを書く順番ですが、後に書いても先に書いてもよいですし、順不同です。オプション名のあと値をきちんと指定すれば、あとの書き順はなんでも大丈夫です。以下は所有者をhogeさんにした状態でhogehogeというデータベースを作成しています。
-bash-4.1$ createdb -O hoge hogehoge
データベースの作成も postgreSQLのシェルで行うことが可能です。
postgres=# create database hogehoge; CREATE DATABASE
postgresqlは抑えるべき点が多いかんじがしますが重要なのは以下かと思います。
- postgresユーザーでデータベースの作成削除を行う(su – postgresの状態)
- データベース・ユーザーの作成はシェルの外または中から行うことができるが微妙に書き方が異なる
コメント