sqlite3,mysql,posgresqlの各情報の一覧を表示する方法が知りたい。

sql

sqlite3,mysql,posgresqlの以下の内容を表示する方法が知りたい。

  • データベース一覧
  • テーブル一覧
  • カラム一覧

いつもどれか忘れてしまう。RDBMS詳しく無いです。

sqlite3

$ sqlite3 foo.db

SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> -- テーブルの作成
sqlite> CREATE TABLE FOO (id int, point int, data varchar(200));
sqlite> -- テーブルの一覧
sqlite> .tables
FOO
sqlite> -- テーブルのスキーマ(カラム一覧？)
sqlite> .schema Foo
CREATE TABLE FOO (id int, point int, data varchar(200));
sqlite> -- データベース一覧
.databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/podhmo/tmp/db-sample/foo.db                            
1    temp

mysql

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 141
Server version: 5.0.51a-24+lenny5 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

(root@localhost) [(none)]> 
(root@localhost) [(none)]> -- user 作成
(root@localhost) [(none)]> grant select,insert,delete,update,create,drop,alter,file,index on *.* to boo identified by 'password';
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> -- DB 作成
(root@localhost) [(none)]> create database foo;
Query OK, 1 row affected (0.00 sec)

(root@localhost) [(none)]> -- DB 一覧
(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| foo                | 
| mysql              | 
+--------------------+
4 rows in set (0.00 sec)

(root@localhost) [(none)]> -- テーブル 作成
(root@localhost) [(none)]> use foo;
Database changed
(root@localhost) [foo]> CREATE TABLE FOO (id int, point int, data varchar(200));
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [foo]> -- テーブル一覧
(root@localhost) [foo]> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| FOO           | 
+---------------+
1 row in set (0.00 sec)

(root@localhost) [foo]> -- カラムの一覧
(root@localhost) [foo]> show columns from FOO;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       | 
| point | int(11)      | YES  |     | NULL    |       | 
| data  | varchar(200) | YES  |     | NULL    |       | 
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

(root@localhost) [foo]>

postgresql

-- DB一覧
foo=# \l
                                   List of databases
     Name     |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
 foo          | Boo      | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 postgres     | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0    | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres
                                                                : postgres=CTc/postgres
 template1    | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres
                                                                : postgres=CTc/postgres
(5 rows)

-- テーブル作成
foo=# CREATE TABLE FOO (id int, point int, data varchar(200));
CREATE TABLE

-- テーブル一覧
foo=# \z
                          Access privileges
 Schema | Name | Type  | Access privileges | Column access privileges
--------+------+-------+-------------------+--------------------------
 public | foo  | table |                   |
(1 row)

-- カラム一覧
foo=# \d foo
             Table "public.foo"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 id     | integer                | 
 point  | integer                | 
 data   | character varying(200) | 

foo=#