Help us understand the problem. What is going on with this article?

PostgreSQLでつかうコマンドメモMysql添え(随時更新)

More than 1 year has passed since last update.

PostgreSQLでつかうコマンドメモ。
MySQLでのコマンドも合わせてのせる。

  • $ command ターミナルのコマンド
  • # command psqlのコマンド
  • > command mysqlのコマンド

コンソール login

$ sudo su postgres
$ psql
or
$ psql [database_name] [user]

$ mysql -u [user] -p

user list

# \du

> select * from mysql.user

database list

#  \l

> show databases
> show schemas

choice database

# \connect [database_name]

> use [database_name]

table list

# \d

> show tables

table description

# \d [table_name]

> desc [table_name]
$ pg_dump [database_name] -U[user_name] -s -t [table_name]

> show create table [table_name]

add column

基本的に同じ。

# alter table [table_name] add column [column_name] [data_type];

> alter table [table_name] add column [column_name] [data_type];

PostgreSQLでは after による位置指定ができない。

change column

NULL 制約

# alter table [table_name] alter column [column_name] [SET|DROP] NOT NULL;

> alter table [table_name] change column [column_name] [data_type] NULL;

RENAME

# alter table [table_name] rename column [old_name] to [new_name];

> alter table [table_name] change column [old_name] [new_name] [data_type];

INDEX 追加

# create index [index_name] on [table_name] ([column_name]);

> alter table [table_name] add index [index_name] ([column_name]);

PRIMARY KEY 削除

-- Primary Key 制約を削除
# alter table [table_name] drop constraint [constraint_name]

> alter table [table_name] drop primary key;

SELECT結果を縦方向に表示する

# \x
# select * from [table_name]]
# \x

> select * from [table_name] \G

PostgreSQLの \x\x [on|off|auto] toggle expanded output は標示フォーマットを切り替えるので戻す時はもう一度 \x を実行する。

MySQLの \Gego (\G) Send command to mysql server, display result vertically. で指定した時だけ縦方向に表示する。

dump

export

$ pg_dump -U [user] -h localhost -p 5431 -t [table_name] [database_name] > output.file.dump

$ mysqldump -u [user] -h localhost -p 3306 -p [database_name] [table_name] > output.file.dump

import

$ psql [database_name] [user] < output.file.dump

$ mysql -u [user] -p [database_name] < output.file.dump
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away