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.

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

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	?