Sponsored Link

データベース操作のコマンドメモです。

データベース一覧
テーブル一覧
カラム一覧
ユーザ一覧
ユーザ設定
自動連番と現在日時
ファイル入力

データベース一覧
// MySQL
 [Prompt]mysqlshow
SHOW DATABASES;
// MS SQL SERVER
 sp_databases
sp_helpdb
SELECT * FROM master.dbo.sysdatabases
// PostgreSQL
 \l
SELECT * FROM pg_database;
// ORACLE
// DB2
 LIST DATABASE DIRECTORY

テーブル一覧
// MySQL
 [Prompt]mysqlshow DATABASE_NAME
SHOW TABLES [FROM DATABASE_NAME];
// MS SQL SERVER
 sp_tables
SELECT * FROM sysobjects
  WHERE xtype = 'S' OR xtype = 'U' OR xtype = 'V'
  ORDER BY xtype, name
// PostgreSQL
 \d SELECT * FROM pg_tables;
// ORACLE
 SELECT * FROM TAB;
SELECT * FROM USER_TABLES;
// DB2
 LIST TABLES

カラム一覧
// MySQL
 [Prompt]mysqlshow DATABASE_NAME TABLE_NAME
SHOW COLUMNS FROM [DATABASE_NAME.]TABLE_NAME;
// MS SQL SERVER
 sp_columns TABLE_NAME
// PostgreSQL
 \d TABLE_NAME
// ORACLE
 DESC TABLE_NAME
// DB2
 DESCRIBE TABLE TABLE_NAME

ユーザ一覧
// MySQL
 SELECT * FROM mysql.user;
// MS SQL SERVER
 sp_helplogins
// PostgreSQL
 SELECT * FROM pg_user;
// ORACLE
 SELECT USERNAME FROM DBA_USERS;
// DB2

ユーザ設定
// MySQL
 GRANT [ALL PRIVILEGES, CREATE, SELECT ...]
  ON DATABASE_NAME.TABLE_NAME
  TO USER_NAME@HOST_NAME IDENTIFIED BY 'PASSWORD';
REVOKE [ALL PRIVILEGES, CREATE, SELECT ...]
  ON DATABASE_NAME.TABLE_NAME
  FROM USER_NAME@HOST_NAME;
DELETE FROM mysql.user WHERE user='USER_NAME';
// MS SQL SERVER
 sp_addlogin USER_NAME [,PASSWORD] [,DATABASE_NAME]
sp_grantdbaccess USER_NAME
GRANT [ALL, CREATE DATABASE, CREATE TABLE ...]
      [ALL PRIVILEGES, SELECT, INSERT ...]
 [ON TABLE_NAME, VIEW_NAME]
  TO USER_NAME
REVOKE [ALL, CREATE DATABASE, CREATE TABLE ...]
       [ALL PRIVILEGES, SELECT, INSERT ...]
 [ON TABLE_NAME, VIEW_NAME]
  FROM USER_NAME
sp_revokedbaccess USER_NAME
sp_droplogin USER_NAME
// PostgreSQL
 [Prompt]createuser USER_NAME
[Prompt]dropuser USER_NAME
CREATE USER USER_NAME WITH PASSWORD 'PASSWORD'
GRANT [CREATE, TEMPORARY, TEMP ...]
      [ALL PRIVILEGES, SELECT, INSERT ...]
  ON [DATABASE DATABASE_NAME] [TABLE TABLE_NAME]
  TO USER_NAME;
REVOKE [CREATE, TEMPORARY, TEMP ...]
      [ALL PRIVILEGES, SELECT, INSERT ...]
  ON [DATABASE DATABASE_NAME] [TABLE TABLE_NAME]
  FROM USER_NAME;
DROP USER USER_NAME;
// ORACLE
 CREATE USER USER_NAME IDENTIFIED BY PASSWORD;
GRANT [CONNECT, ALL PRIVILEGES, CREATE TABLE ...]
      [ALL, SELECT, INSERT ...]
  [ON TABLE_NAME, VIEW_NAME]
  TO USER_NAME [IDENTIFIED BY PASSWORD];
REVOKE [CONNECT, ALL PRIVILEGES, CREATE TABLE ...]
       [ALL, SELECT, INSERT ...]
  [ON TABLE_NAME, VIEW_NAME]
  FROM USER_NAME;
DROP USER USER_NAME [CASCADE];
// DB2
 GRANT [CONNECT, DBADM, CREATETAB ...]
      [SELECT, INSERT, UPDATE ...]
  ON [DATABASE] [TABLE TABLE_NAME]
  TO USER_NAME
REVOKE [CONNECT, DBADM, CREATETAB ...]
       [SELECT, INSERT, UPDATE ...]
  ON [DATABASE] [TABLE TABLE_NAME]
  FROM USER_NAME

自動連番と現在日時
// MySQL
 CREATE TABLE new_tbl
(
  sname  VARCHAR(32),
  sid    INT AUTO_INCREMENT PRIMARY KEY,
  sdate  DATETIME
);
INSERT INTO new_tbl(sname, sdate) VALUES ('name', NOW());
// MS SQL SERVER
 CREATE TABLE new_tbl
(
  sname  VARCHAR(32),
  sid    INT IDENTITY(1,1),
  sdate  DATETIME DEFAULT GETDATE()
);
INSERT INTO new_tbl(sname) VALUES ('name');
// PostgreSQL
 CREATE TABLE new_tbl
(
  sname  VARCHAR(32),
  sid    SERIAL,
  sdate  TIMESTAMP DEFAULT NOW()
);
INSERT INTO new_tbl(sname) VALUES ('name');
// ORACLE
 CREATE TABLE new_tbl
(
  sname  VARCHAR2(32),
  sid    NUMBER,
  sdate  DATE DEFAULT SYSDATE
);
CREATE SEQUENCE new_seq
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 99999
  CYCLE NOCACHE;
INSERT INTO new_tbl(sname, sid) VALUES ('name', new_seq.nextval);
// DB2
 CREATE TABLE new_tbl
(
  sname  VARCHAR(32),
  sid    INT GENERATED ALWAYS AS IDENTITY
            (START WITH 1, INCREMENT BY 1, NO CACHE ),
  sdate  TIMESTAMP DEFAULT CURRENT TIMESTAMP
)
INSERT INTO new_tbl(sname) VALUES('name')

ファイル入力
// MySQL
 [Prompt]mysql -uUSER_NAME -pPASSWORD [DATABASE_NAME] < FILE_NAME
// MS SQL SERVER
 [Prompt]osql -UUSER_NAME -PPASSWORD [-dDATABASE_NAME] -i"FILE_NAME"
// PostgreSQL
 [Prompt]psql [-U USER_NAME -d DATABASE_NAME] -f FILE_NAME
\i FILE_NAME
// ORACLE
 [Prompt]imp [USER_NAME/PASSWORD] [FILE=FILE_NAME]
start FILE_NAME
// DB2
 IMPORT FROM FILE_NAME OF [IXF, ASC, DEL, WSF] ...

戻る