ログイン・ログアウト

ログイン ログアウト
MySQL $ mysql -u myuser -pxxx mytable sql> QUIT (EXITも可)
Oracle $ sqlplus
$ sqlplus myuser@myhost
$ sqlplus myuser/xxxxx@myhost
$ sqlplus myuser/xxxxx@myhost/xe 		sql> EXIT (QUITも可)
PostgreSQL $ psql mydatabase myuser sql> \q
SQLite $ sqlite3 mydatabase.sqlite3 sql> .exit
MySQL Oracle PostgreSQL SQLite
ログイン $ mysql -u myuser -pxxx mytable $ sqlplus
$ sqlplus myuser@myhost
$ sqlplus myuser/xxxxx@myhost
$ sqlplus myuser/xxxxx@myhost/xe 		$ psql mydatabase myuser $ sqlite3 mydatabase.sqlite3
ログアウト sql> QUIT (EXITも可) sql> EXIT (QUITも可) sql> \q sql> .exit

管理

ユーザ管理

ユーザ作成 ユーザ削除 ユーザ一覧
MySQL CREATE USER 'myuser'@'myhost';
CREATE USER 'myuser'@'myhost' IDENTIFIED BY 'xxx';
GRANT ALL ON *.* TO myuser@localhost IDENTIFIED BY 'xxxxxx'; 		$ dropuser myuser
sql> DROP USER 'myuser'@'myhost'; 		SELECT User, Host from mysql.user;
Oracle CREATE USER myuser IDENTIFIED BY "xxx"
   DEFAULT TABLESPACE users; 		sql> DROP USER myuser;
sql> DROP USER myuser CASCADE; 		SELECT USERNAME FROM USER_USERS;
SELECT USERNAME FROM DBA_USERS;
SELECT USERNAME FROM ALL_USERS;
PostgreSQL $ createuser myuser -P
sql> CREATE USER myuser;
sql> CREATE USER myuser WITH LOGIN PASSWORD 'xxx'; 		$ dropuser myuser
sql> DROP USER myuser; 		sql> select usename from pg_user;
sql> select rolname from pg_roles;
SQLite - - -
MySQL Oracle PostgreSQL SQLite
ユーザ作成 CREATE USER 'myuser'@'myhost';
CREATE USER 'myuser'@'myhost' IDENTIFIED BY 'xxx';
GRANT ALL ON *.* TO myuser@localhost IDENTIFIED BY 'xxxxxx'; 		CREATE USER myuser IDENTIFIED BY "xxx"
   DEFAULT TABLESPACE users; 		$ createuser myuser -P
sql> CREATE USER myuser;
sql> CREATE USER myuser WITH LOGIN PASSWORD 'xxx'; 		-
ユーザ削除 $ dropuser myuser
sql> DROP USER 'myuser'@'myhost'; 		sql> DROP USER myuser;
sql> DROP USER myuser CASCADE; 		$ dropuser myuser
sql> DROP USER myuser; 		-
ユーザ一覧 SELECT User, Host from mysql.user; SELECT USERNAME FROM USER_USERS;
SELECT USERNAME FROM DBA_USERS;
SELECT USERNAME FROM ALL_USERS; 		sql> select usename from pg_user;
sql> select rolname from pg_roles; 		-

データベース管理

データベース作成 データベース削除 データベース一覧
MySQL sql> CREATE DATABASE mydatabase; sql> DROP DATABASE mydatabase; sql> SHOW DATABASES;
Oracle - - sql> SELECT INSTANCE_NAME FROM V$INSTANCE;
PostgreSQL $ createdb mydatabase
sql> CREATE DATABASE mydatabase; 		$ dropdb mydatabase
sql> DROP DATABASE mydatabase; 		sql> \l
SQLite $ sqlite3 mydatabase.sqlite3 $ rm mydatabase.sqlite3 -
MySQL Oracle PostgreSQL SQLite
データベース作成 sql> CREATE DATABASE mydatabase; - $ createdb mydatabase
sql> CREATE DATABASE mydatabase; 		$ sqlite3 mydatabase.sqlite3
データベース削除 sql> DROP DATABASE mydatabase; - $ dropdb mydatabase
sql> DROP DATABASE mydatabase; 		$ rm mydatabase.sqlite3
データベース一覧 sql> SHOW DATABASES; sql> SELECT INSTANCE_NAME FROM V$INSTANCE; sql> \l -

テーブル定義

テーブル作成・削除

テーブル作成 テーブル削除
MySQL sql> CREATE TABLE mytable(ColA INT, ColB VARCHAR(20)); sql> DROP TABLE mytable;
Oracle sql> CREATE TABLE mytable(ColA NUMBER, ColB VARCHAR2(20)); sql> DROP TABLE mytable;
PostgreSQL sql> CREATE TABLE mytable(ColA INT, ColB VARCHAR); sql> DROP TABLE mytable;
SQLite sql> CREATE TABLE mytable(ColA INT, ColB VARCHAR); sql> DROP TABLE mytable;
MySQL Oracle PostgreSQL SQLite
テーブル作成 sql> CREATE TABLE mytable(ColA INT, ColB VARCHAR(20)); sql> CREATE TABLE mytable(ColA NUMBER, ColB VARCHAR2(20)); sql> CREATE TABLE mytable(ColA INT, ColB VARCHAR); sql> CREATE TABLE mytable(ColA INT, ColB VARCHAR);
テーブル削除 sql> DROP TABLE mytable; sql> DROP TABLE mytable; sql> DROP TABLE mytable; sql> DROP TABLE mytable;

テーブル定義変更

テーブル名変更 カラム名変更
MySQL sql> ALTER TABLE mytable RENAME TO mytable2; sql> ALTER TABLE mytable CHANGE ColA ColX 元の型;
Oracle sql> ALTER TABLE mytable RENAME TO mytable2; sql> ALTER TABLE mytable RENAME COLUMN ColA TO ColX;
PostgreSQL sql> ALTER TABLE mytable RENAME TO mytable2; sql> ALTER TABLE mytable RENAME ColA TO ColX;
SQLite sql> ALTER TABLE mytable RENAME TO mytable2; -
MySQL Oracle PostgreSQL SQLite
テーブル名変更 sql> ALTER TABLE mytable RENAME TO mytable2; sql> ALTER TABLE mytable RENAME TO mytable2; sql> ALTER TABLE mytable RENAME TO mytable2; sql> ALTER TABLE mytable RENAME TO mytable2;
カラム名変更 sql> ALTER TABLE mytable CHANGE ColA ColX 元の型; sql> ALTER TABLE mytable RENAME COLUMN ColA TO ColX; sql> ALTER TABLE mytable RENAME ColA TO ColX; -

テーブル情報参照

テーブル定義表示 テーブル一覧表示
MySQL sql> DESC mytable; sql> SHOW TABLES;
Oracle sql> DESC mytable; sql> SELECT TABLE_NAME FROM USER_TABLES;
PostgreSQL sql> \d mytable sql> \dt
SQLite sql> .schema mytable sql> .tables
MySQL Oracle PostgreSQL SQLite
テーブル定義表示 sql> DESC mytable; sql> DESC mytable; sql> \d mytable sql> .schema mytable
テーブル一覧表示 sql> SHOW TABLES; sql> SELECT TABLE_NAME FROM USER_TABLES; sql> \dt sql> .tables

テーブル操作

テーブル操作概要

選択 挿入 更新 削除
MySQL sql> SELECT * FROM mytable; sql> INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY'); sql> UPDATE mytable SET ColA = 8 WHERE ColB = 'YY'; sql> DELETE FROM mytable WHERE ColA = 10;
Oracle sql> SELECT * FROM mytable; sql> INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY'); sql> UPDATE mytable SET ColA = 8 WHERE ColB = 'YY'; sql> DELETE FROM mytable WHERE ColA = 10;
PostgreSQL sql> SELECT * FROM mytable; sql> INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY'); sql> UPDATE mytable SET ColA = 8 WHERE ColB = 'YY'; sql> DELETE FROM mytable WHERE ColA = 10;
SQLite sql> SELECT * FROM mytable; sql> INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY'); sql> UPDATE mytable SET ColA = 8 WHERE ColB = 'YY'; sql> DELETE FROM mytable WHERE ColA = 10;
MySQL Oracle PostgreSQL SQLite
選択 sql> SELECT * FROM mytable; sql> SELECT * FROM mytable; sql> SELECT * FROM mytable; sql> SELECT * FROM mytable;
挿入 sql> INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY'); sql> INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY'); sql> INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY'); sql> INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY');
更新 sql> UPDATE mytable SET ColA = 8 WHERE ColB = 'YY'; sql> UPDATE mytable SET ColA = 8 WHERE ColB = 'YY'; sql> UPDATE mytable SET ColA = 8 WHERE ColB = 'YY'; sql> UPDATE mytable SET ColA = 8 WHERE ColB = 'YY';
削除 sql> DELETE FROM mytable WHERE ColA = 10; sql> DELETE FROM mytable WHERE ColA = 10; sql> DELETE FROM mytable WHERE ColA = 10; sql> DELETE FROM mytable WHERE ColA = 10;

テーブル選択

条件 並び 件数
MySQL sql> SELECT * FROM mytable WHERE ColA = 10; sql> SELECT * FROM mytable ORDER BY ColA;
sql> SELECT * FROM mytable ORDER BY ColA DESC; 		sql> SELECT * FROM mytable LIMIT 10;
Oracle sql> SELECT * FROM mytable WHERE ColA = 10; sql> SELECT * FROM mytable ORDER BY ColA;
sql> SELECT * FROM mytable ORDER BY ColA DESC; 		sql> SELECT * FROM mytable WHERE ROWNUM <= 10;
PostgreSQL sql> SELECT * FROM mytable WHERE ColA = 10; sql> SELECT * FROM mytable ORDER BY ColA;
sql> SELECT * FROM mytable ORDER BY ColA DESC; 		sql> SELECT * FROM mytable LIMIT 10;
SQLite sql> SELECT * FROM mytable WHERE ColA = 10; sql> SELECT * FROM mytable ORDER BY ColA;
sql> SELECT * FROM mytable ORDER BY ColA DESC; 		sql> SELECT * FROM mytable LIMIT 10;
MySQL Oracle PostgreSQL SQLite
条件 sql> SELECT * FROM mytable WHERE ColA = 10; sql> SELECT * FROM mytable WHERE ColA = 10; sql> SELECT * FROM mytable WHERE ColA = 10; sql> SELECT * FROM mytable WHERE ColA = 10;
並び sql> SELECT * FROM mytable ORDER BY ColA;
sql> SELECT * FROM mytable ORDER BY ColA DESC; 		sql> SELECT * FROM mytable ORDER BY ColA;
sql> SELECT * FROM mytable ORDER BY ColA DESC; 		sql> SELECT * FROM mytable ORDER BY ColA;
sql> SELECT * FROM mytable ORDER BY ColA DESC; 		sql> SELECT * FROM mytable ORDER BY ColA;
sql> SELECT * FROM mytable ORDER BY ColA DESC;
件数 sql> SELECT * FROM mytable LIMIT 10; sql> SELECT * FROM mytable WHERE ROWNUM <= 10; sql> SELECT * FROM mytable LIMIT 10; sql> SELECT * FROM mytable LIMIT 10;

テーブル挿入

自動連番
MySQL sql> CREATE TABLE mytable(
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   ColA VARCHAR(20)
);
sql> INSERT INTO mytable(ColA) VALUES('XX');
Oracle sql> CREATE TABLE mytable(
   id NUMBER NOT NULL,
   ColA VARCHAR2(20)
);
sql> CREATE SEQUENCE myseq;
sql> INSERT INTO mytable(id, ColA) VALUES(myseq.NEXTVAL, 'XX');
PostgreSQL sql> CREATE TABLE mytable(
   id SERIAL NOT NULL,
   ColA VARCHAR
);
sql> INSERT INTO mytable(ColA) VALUES('XX');
SQLite sql> CREATE TABLE mytable(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   ColA VARCHAR
);
sql> INSERT INTO mytable(ColA) VALUES('XX');
MySQL Oracle PostgreSQL SQLite
自動連番 sql> CREATE TABLE mytable(
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   ColA VARCHAR(20)
);
sql> INSERT INTO mytable(ColA) VALUES('XX'); 		sql> CREATE TABLE mytable(
   id NUMBER NOT NULL,
   ColA VARCHAR2(20)
);
sql> CREATE SEQUENCE myseq;
sql> INSERT INTO mytable(id, ColA) VALUES(myseq.NEXTVAL, 'XX'); 		sql> CREATE TABLE mytable(
   id SERIAL NOT NULL,
   ColA VARCHAR
);
sql> INSERT INTO mytable(ColA) VALUES('XX'); 		sql> CREATE TABLE mytable(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   ColA VARCHAR
);
sql> INSERT INTO mytable(ColA) VALUES('XX');

トランザクション

トランザクション開始 トランザクション確定 トランザクション破棄
MySQL sql> BEGIN; sql> COMMIT; sql> ROLLBACK;
Oracle (自動) sql> COMMIT; sql> ROLLBACK;
PostgreSQL sql> BEGIN; sql> COMMIT; sql> ROLLBACK;
SQLite sql> BEGIN; sql> COMMIT; sql> ROLLBACK;
MySQL Oracle PostgreSQL SQLite
トランザクション開始 sql> BEGIN; (自動) sql> BEGIN; sql> BEGIN;
トランザクション確定 sql> COMMIT; sql> COMMIT; sql> COMMIT; sql> COMMIT;
トランザクション破棄 sql> ROLLBACK; sql> ROLLBACK; sql> ROLLBACK; sql> ROLLBACK;