| Oracle | PostgreSQL | MySQL | Sybase ASE |
|---|
| List databases | select name from v$database;
| select datname from pg_database;
orpsql -l on cmdline
or\l
| show databases;
| sp_helpdb
go
|
|---|
| List tables | select table_name from tabs;
| \dt
orselect tablename from pg_tables where schemaname='public';
| show tables;
| sp_help
go
|
|---|
| List tablespaces | select name from v$tablespace;
| select spcname from pg_tablespaces;
or\db
| | |
|---|
| List actual data locations | select ts#,name from v$datafile;
| select spcname,spclocation from pg_tablespaces;
| | sp_helpdevice
go
|
|---|
| Show active queries | select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address;
| select * from pg_stat_activity
| show processlist
| |
|---|
| List users | | select usename from pg_user
| select distinct user from user where user<>''
| |
|---|
| Connect to database | connect dbname as username
| \connect dbname;
| use dbname
| |
|---|
| Database export | exp FILE=outfile.dmp
| pg_dumpall > outfile.dump
| mysqldump --all-databases > outfile.dump
| dump database dbname to "/tmp/dump.dmp"
go
|
|---|
| Normal primary key | | id serial not null
| id int unsigned not null auto_increment
| |
|---|
| Database authentication | | edit pg_hba.conf
| grant all privileges on database.* to username@hostname identified by "abc123";
| |
|---|
| Database authorization | | grant all privileges on table to username
| grant all privileges on table to username
| |
|---|
| Commandline client | sqlplus
| psql
| mysql
| isql -S DBNAME -Uuser -Ppass
|
|---|
| Describe table | describe table;
| \d table
| explain table;
| sp_help <tablename>
go
|
|---|
| Limit 10 & offset 100 | select ... where rownum > 100 and rownum <= 110;
-- (doesn't work with "order by" or "group by")
orSELECT ... other columns ... FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ... key ... ASC) AS rn,
... other columns ...
)
WHERE rn > 100 AND rn <= (10+100)
orselect a,b,c from
(select a,b,c, rownum rn
from (select a,b,c from tablename
where... order by...)
where rownum <= 110)
where rn > 100;
| select ... limit 10 offset 100;
| select ... limit 100,10;
| |
|---|
| Show schema | set long 32000
set heading OFF
SELECT (SELECT dbms_metadata.get_ddl('TABLE', table_name) FROM dual)
FROM tabs;
| | | |
|---|