SQL Dialects Reference/Administration

While this topic is not directly about SQL language, it is sufficiently close to gather information about frequently undertaken administrative tasks together.

Dumping database edit

Dumping the database involves creation of a file that stores all the data (rows) and the schema for this data from the desired databases/tables. Most usually it's done as a series of SQL statements, combining CREATE DATABASE / CREATE TABLE statements to recreate the schema and INSERT statements to refill the rows — thus, usually the resulting file can be directly imported using command-line SQL client by simple execution of queries. However, sometimes other output formats are desired and some databases (at least MSSQL) lack the ability to serialize the database as SQL statements from command-line dumper client.

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Basic invocation ? ? ? copydb dbname options ? bcp database out file options mysqldump options database msqldump -d database ? pg_dump options database sqlite3 dbfile .dump ?
? Authentication to target server ? ? ? -uusername -P ? -U username -P password -S host -uusername -ppassword -hhost -Pport -uusername ? -Uusername -Wpassword N/A ?
? Save dump into file ? ? ? ? ? in basic invocation -rfile / --result-file=file redirect msqldump output ? -ffile / --file=file .output file
.once file
?
? Dump only schema ? ? ? ? ? N/A -d / --no-data msqldump -d "database" -D ? -s / --schema-only .schema ?
? Add DROP schema at start ? ? ? -add_drop ? N/A N/A ? -c N/A ?
? Prevent dumping of access privileges ? ? ? ? ? N/A N/A N/A ? -x / --no-privileges / --no-acl N/A ?
? Dump only data ? ? ? ? ? default -n -t / --no-create-db --no-create-info -t tablename ? -s / --data-only N/A ?
? Dump data as tab-separated ? ? ? ? ? default --tab=path / -Tpath ? ? ? .mode tabs ?
? Dump data as INSERT statements ? ? ? ? ? N/A default default ? -d .mode insert ?
? Dump everything as XML file ? ? ? ? ? N/A --xml / -X N/A ? N/A N/A ?
? Ordering of data ? ? ? ? ? -h "ORDER (column, ...)" --order-by-primary ? ? ? ? ?

Restoring database edit

Command-line SQL query edit

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Basic invocation ? ? ? sql options database ? isql options [-d database] mysql options [database] mclient -d database ? psql options [database] sqlite3 filename options ?
? Authentication to target server ? ? ? -uusername -Ppassword -Ggroup -Rrole ? -U username -P password -S instance -H host -uusername -ppassword -hhost -Pport ? ? -Uusername -Wpassword -hhost -pport N/A ?
? Execute query from command line and exit ? ? ? N/A ? -Q "query" -e query -sstatement ? -c query query ?
? Execute query from command line and continue with interactive prompt ? ? ? N/A ? -q "query" N/A -i ? N/A N/A ?
? Input file[1] ? ? ? N/A ? -i file N/A file ? -f file -init file ?
? Output to file instead of stdout[2] ? ? ? N/A ? -o file N/A ? ? -o file N/A ?
? Copy output also to file ? ? ? N/A ? N/A --tee=file ? ? -L file N/A ?

References edit

  1. Can be also done using shell < redirection
  2. Can be also done using shell > redirection