# 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

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 | ? | ? | ? | ? | ? |

## Command-line SQL query

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 | ? | sqlcmd options | mysql options [database] | mclient -d database | ? | psql options [database] | sqlite3 filename options | ? |
| ? | Authentication to target server | ? | ? | ? | -uusername -Ppassword -Ggroup -Rrole | ? | -S host -d database -U username -P password | -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 | ? | ? | ? | N/A | ? | -i file | N/A | file | ? | -f file | -init file | ? |
| ? | Output to file instead of stdout | ? | ? | ? | 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

* ↑ Can be also done using shell < redirection
* ↑ Can be also done using shell > redirection