If you normally operate the database on the command line, it is highly recommended that you use the following command-line tools.
mycli
Mycli is a MySQL based command-line tool, which is directly installed by pip install mycli.
For details, see https://github.com/dbcli/mycli
$ mycli --help Usage: mycli [OPTIONS] [DATABASE] A MySQL terminal client with auto-completion and syntax highlighting. Examples: - mycli my_database - mycli -u my_user -h my_host.com my_database - mycli mysql://my_user@my_host.com:3306/my_database Options: -h, --host TEXT Host address of the database. -P, --port INTEGER Port number to use for connection. Honors $MYSQL_TCP_PORT. -u, --user TEXT User name to connect to the database. -S, --socket TEXT The socket file to use for connection. -p, --password TEXT Password to connect to the database. --pass TEXT Password to connect to the database. --ssh-user TEXT User name to connect to ssh server. --ssh-host TEXT Host name to connect to ssh server. --ssh-port INTEGER Port to connect to ssh server. --ssh-password TEXT Password to connect to ssh server. --ssh-key-filename TEXT Private key filename (identify file) for the ssh connection. --ssl-ca PATH CA file in PEM format. --ssl-capath TEXT CA directory. --ssl-cert PATH X509 cert in PEM format. --ssl-key PATH X509 key in PEM format. --ssl-cipher TEXT SSL cipher to use. --ssl-verify-server-cert Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default. -V, --version Output mycli's version. -v, --verbose Verbose output. -D, --database TEXT Database to use. -d, --dsn TEXT Use DSN configured into the [alias_dsn] section of myclirc file. --list-dsn list of DSN configured into the [alias_dsn] section of myclirc file. -R, --prompt TEXT Prompt format (Default: "\t \u@\h:\d> "). -l, --logfile FILENAME Log every query and its results to a file. --defaults-group-suffix TEXT Read MySQL config groups with the specified suffix. --defaults-file PATH Only read MySQL options from the given file. --myclirc PATH Location of myclirc file. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -t, --table Display batch output in table format. --csv Display batch output in CSV format. --warn / --no-warn Warn before running a destructive query. --local-infile BOOLEAN Enable/disable LOAD DATA LOCAL INFILE. --login-path TEXT Read this path from the login file. -e, --execute TEXT Execute command and quit. --help Show this message and exit. λ mycli -u root Password: mysql 5.5.53 mycli 1.19.0 Chat: https://gitter.im/dbcli/mycli Mail: https://groups.google.com/forum/#!forum/mycli-users Home: http://mycli.net Thanks to the contributor - Jialong Liu mysql root@localhost:(none)> show data databases create database
Syntax highlighting and auto completion
ps: what if you want to execute Linux command after entering MySQL command line, exit execution first and then log in MySQL? In fact, you can use system directly.
mysql> system pwd /root mysql> system ll sh: ll: command not found mysql> system ls -al total 40160 dr-xr-x---. 39 root root 4096 Jul 10 14:47 . dr-xr-xr-x. 29 root root 4096 May 5 18:55 ..
litecli
Litecli is a command-line tool based on sqlite, which is installed directly with pip install -U litecli. For details, see
https://github.com/dbcli/litecli
$ litecli --help Usage: litecli [OPTIONS] [DATABASE] A SQLite terminal client with auto-completion and syntax highlighting. Examples: - litecli lite_database Options: -V, --version Output litecli's version. -D, --database TEXT Database to use. -R, --prompt TEXT Prompt format (Default: "\d> "). -l, --logfile FILENAME Log every query and its results to a file. --liteclirc PATH Location of liteclirc file. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -t, --table Display batch output in table format. --csv Display batch output in CSV format. --warn / --no-warn Warn before running a destructive query. -e, --execute TEXT Execute command and quit. --help Show this message and exit. λ litecli Version: 1.0.0 Mail: https://groups.google.com/forum/#!forum/litecli-users Github: https://github.com/dbcli/litecli (none)> .tables Not connected to database. (none)> .databases Not connected to database. (none)> use test You are now connected to database "test" Time: 0.001s test> .tables Time: 0.000s test> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Query OK, 0 rows affected Time: 0.077s test> .tables +---------+ | name | +---------+ | COMPANY | +---------+ Time: 0.040s
See for usage grammar https://www.runoob.com/sqlite/sqlite-insert.html
test> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); Query OK, 1 row affected Time: 0.083s test> select *from `COMPANY` +----+------+-----+------------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+------------+---------+ | 1 | Paul | 32 | California | 20000.0 | +----+------+-----+------------+---------+ 1 row in set Time: 0.023s test> SELECT CURRENT_TIMESTAMP; +---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2019-07-10 06:49:09 | +---------------------+ 1 row in set Time: 0.017s test> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 1; Query OK, 1 row affected Time: 0.097s test> select *from `COMPANY` +----+------+-----+---------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+---------+---------+ | 1 | Paul | 32 | Texas | 20000.0 | +----+------+-----+---------+---------+ 1 row in set Time: 0.019s
mssql-cli
mssql cli is a command-line tool based on mssql, which is directly installed by PIP install mssql cli. For details, see https://github.com/dbcli/mssq... https://www.ctolib.com/dbcli-...
$ mssql-cli --help usage: mssql-cli [-h] [-U] [-P] [-d] [-S] [-E] [-v] [--mssqlclirc] [--row-limit] [--less-chatty] [--auto-vertical-output] [-N] [-C] [-l] [-K] [-M] [-a] [-A] [--enable-sqltoolsservice-logging] [--prompt] Microsoft SQL Server CLI. Version 0.15.0 optional arguments: -h, --help show this help message and exit -U , --username Username to connect to the database -P , --password If not supplied, defaults to value in environment variable MSSQL_CLI_PASSWORD. -d , --database database name to connect to. -S , --server SQL Server instance name or address. -E, --integrated Use integrated authentication on windows. -v, --version Version of mssql-cli. --mssqlclirc Location of mssqlclirc config file. --row-limit Set threshold for row limit prompt. Use 0 to disable prompt. --less-chatty Skip intro on startup and goodbye on exit. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -N, --encrypt SQL Server uses SSL encryption for all data if the server has a certificate installed. -C, --trust-server-certificate The channel will be encrypted while bypassing walking the certificate chain to validate trust. -l , --connect-timeout Time in seconds to wait for a connection to the server before terminating request. -K , --application-intent Declares the application workload type when connecting to a database in a SQL Server Availability Group. -M, --multi-subnet-failover If application is connecting to AlwaysOn AG on different subnets, setting this provides faster detection and connection to currently active server. -a , --packet-size Size in bytes of the network packets used to communicate with SQL Server. -A, --dac-connection Connect to SQL Server using the dedicated administrator connection. --enable-sqltoolsservice-logging Enables diagnostic logging for the SqlToolsService. --prompt Prompt format (Default: \d>
pgcli
Pgcli is a command-line tool based on PostgreSQL, which supports automatic completion and syntax highlighting. It can be installed directly using pip install -U pgcli. For details, see https://github.com/dbcli/pgcli
$ pgcli --help Usage: pgcli [OPTIONS] [DATABASE] [USERNAME]Options: -h, --host TEXT Host address of the postgres database. -p, --port INTEGER Port number at which the postgres instance is listening. -U, --user TEXT User name to connect to the postgres database. -W, --password Force password prompt. -w, --no-password Never prompt for password. -v, --version Version of pgcli. -d, --dbname TEXT database name to connect to. --pgclirc TEXT Location of pgclirc file. --help Show this message and exit.
Recommended reading:
Use hugo to build a blog in 5 minutes
Python document generator mkdocs
cheat, a more powerful command-line tool than man
Command line recording tool ASCII NEMA
Public number: Su Sheng
