Section outline

  • 虽然这个主题不是直接关于 SQL 语言的，但它足够接近，可以将关于经常执行的管理任务的信息收集在一起。

    数据库转储

    数据库转储涉及创建一个文件，该文件存储来自所需数据库/表的所有数据（行）和此数据的模式。最常见的是，它通过一系列 SQL 语句来完成，结合使用 CREATE DATABASE / CREATE TABLE 语句来重新创建模式，并使用 INSERT 语句来重新填充行——因此，通常可以使用命令行 SQL 客户端通过简单地执行查询来直接导入生成的文件。然而，有时需要其他输出格式，并且某些数据库（至少是 MSSQL）缺乏从命令行转储客户端将数据库序列化为 SQL 语句的能力。

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

    恢复数据库

    命令行 SQL 查询

    此页面包含可能很大且复杂的比较表格。

    虽然完全可以查看它们的完整状态，但建议安装一个比较表格扩展，该扩展允许选择感兴趣的特定列进行比较。

    Restoring database

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

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