Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to write the mysql command for you. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Mysql command

1) call the mysql client

Provide credentials on the command line:

Shell > mysql-u-p

Provide credentials in the login path:

Shell > mysql-- login-path=

Execute the statement:

Shell > mysql-- login-path=-e ""

Use a specific options file to execute:

Shell > mysql-- defaults-file=...

Execute using a text file that contains SQL statements:

Shell > mysql...

< -u（或 --username=）：选项后面可带或不带空格。 -p（或 --password=）：选项后面不带空格。如果对该选项使用空值，则系统会提示您输入口令。您也可以将其放在选项文件中而不是命令行中，或者在登录路径中提供凭证。 --login-path=：使用此登录路径（通过mysql_config_editor 创建）的凭证。 -e ""（或 --execute=""）：调用 mysql 客户机，然后执行 SQL 语句。 例如，要查看当前的服务器版本： shell>

Mysql-login-path=admin-e "SELECT VERSION ()"

+-- +

| | VERSION () |

+-- +

| | 5.6.10-enterprise-commercial-advanced-log |

+-- +

Redirect the output to a file by adding >.

By adding mysql...-- safe-updates

Prevent users from issuing potentially dangerous statements:

-UPDATE and DELETE can only be used with WHERE or LIMIT.

-WHERE must use a key value to specify the record to modify.

-SELECT output is limited.

You may accidentally issue statements that modify multiple rows in the table or return a particularly large result set.

The safe-updates option helps prevent these problems. Setting the secure update mode imposes the following restrictions on SQL statements:

UPDATE and DELETE are allowed only if they contain a WHERE clause (which clearly identifies the record to be updated or deleted by a key value) or the LIMIT clause.

Limit the output in a single table SELECT statement to no more than 1000 rows, except when the statement contains a LIMIT clause.

Multi-table SELECT statements are allowed only if MySQL checks no more than 1000000 rows to process the query.

3) output format

By default, output is generated whether you use mysql in interactive mode or batch mode:

Interaction: when mysql is called in interactive mode, the query output is displayed in tabular format, using long bars and dashes to display the values listed in the box column.

-table (or-t): generates a table output format, even when running in batch mode. This is the default format for interaction mode.

Batch processing: when mysql is called by using a file as the input source on the command line, mysql runs in batch mode and uses tabs to separate data values in the displayed query output.

-batch (or-B): generates batch mode (tab-delimited) output, even when running in interactive mode, without using history files. This is the default format for batch mode. In batch mode, use the-- raw or-r option to disable character conversion (for example, converting newline and carriage return characters to escape sequences such as\ n or\ r). In the original mode, characters are output by literal value.

Use the following options to select an output format that is different from any of the default formats above:

-- html (or-H): generates output in HTML format

-- xml (or-X): generate output in XML format

Example of how to use it

Mysql-login-path=local-t

Mysql-login-path=local-table

Mysql-login-path=local-B

Mysql-- login-path=local-batch

Mysql-login-path=local-H

Mysql-login-path=local-html

Mysql-- login-path=local-X

Mysql-login-path=local-xml

4) get common commands

List all MySQL client-level commands:

Mysql > HELP

Displays session state information:

Mysql > STATUS

Log session query and its output:

Mysql > tee my_tee_file.txt

5) help on SQL statements

View the complete list of SQL categories:

Mysql > HELP CONTENTS

...

Account Management

Administration

Compound Statements

Data Definition

Data Manipulation

Data Types

...

Help on specific SQL categories or statements:

Mysql > HELP Data Manipulation

Mysql > HELP JOIN

Help on state-related SQL statements:

Mysql > HELP STATUS

You can access server-side help in the mysql client. Server-side help can be found in the MySQL reference Manual for a specific topic (directly through the mysql > prompt). Use HELP followed by keywords to access information. To display entries at the top level of the help system, use the CONTENTS keyword. You do not have to step through the items listed in the catalog list to get help on a specific topic. Just give the topic as a keyword to get some hints. For example, HELP STATUS can generate a list of state-related SQL statements:

...

SHOW

SHOW ENGINE

SHOW MASTER STATUS

...

For more information about HELP statements, see the MySQL reference Manual:

Http://dev.mysql.com/doc/refman/5.6/en/help.html .

6) SQL statement Terminator

Common SQL Terminator

-; or\ g

-\ g (display output vertically)

Zero abort statement

-use the\ c Terminator

Mysql > SELECT VERSION ()\ c

Mysql >

The SQL statement requires a Terminator:

; and\ g: common Terminators, which are equivalent and can be used interchangeably.

\ G: used to terminate the query and display the query results vertically, with each column value of each output row displayed in a separate row. This Terminator is useful when the output lines generated by the query are very wide (because the vertical format makes the results easier to read).

\ C: if you decide to discard the statement you are writing, you can cancel the statement and return to the new mysql > prompt.

7) Special statement Terminator

When using multiple-line statements:

-Terminator is required at the end.

-the prompt was changed from mysql > to->.

In mysql, you can enter a single query using multiple input lines. This makes it easier to issue a long query because you can use multiple rows to enter a long query. Mysql does not send a query for execution until it sees the statement Terminator, as shown in the following example:

Mysql > SELECT Name, Population FROM City

-> WHERE CountryCode = 'IND'

-> AND Population > 3000000

If the statement causes an error, mysql displays the error message returned by the server:

Mysql > This is an invalid statement

ERROR 1064 (42000): You have an error in your SQL syntax; check

The manual that corresponds to your MySQL server version...

@ end the session and exit

-use the\ Q Terminator or QUIT or EXIT.

Mysql >\ Q

Bye

Add other commands:

Edit (\ e): use the $EDITOR editing command.

Pager (\ P): set PAGER to [to_pager]. Output the query results through PAGER.

Rehash (\ #): refactoring completes the hash.

These other commands can be run on UNIX and Linux operating systems, but are not supported on Windows.

8) redefine the prompt

Redefine the prompt:

Mysql > PROMPT term 1 >

Term 1 >

Add information to the prompt:

Mysql > PROMPT (\ u @\ h) [\ d]\ >

PROMPT set to'(\ u @\ h) [\ d]\ >'

(root@localhost) [test] >

Restore the original prompt:

(root@localhost) [test] >

Mysql > PROMPT

Mysql >

The mysql > prompt is the primary (or default) prompt. It indicates that the mysql client is ready to enter new statements. You can change the default prompt to put the current information in the prompt, such as user (\ u), host (\ h), and database (\ d), as shown in the example in this slide.

The example assumes that the database was previously set to test.

Everything after the first space after the PROMPT keyword becomes part of the prompt string, including its

He's blank. The string can contain a special sequence. To restore the prompt to its default value, specify the

PROMPT or\ R.

9) use script files

Deal with the input file in mysql:

-if these files contain SQL statements, they are called:

-"script file"

-"batch File"

Use the SOURCE command:

Mysql > SOURCE / usr/stage/world_innodb.sql

Query OK, 0 rows affected (0.00 sec)

...

When running in interactive mode, mysql can read queries entered through the keyboard. Mysql also accepts input from files. The MySQL server executes the query in the file and displays any output generated. The input text that contains the SQL statement to execute is called a script file or a batch file. The script file should be a plain text file that contains statements in the same format as statements entered in interactive mode. Specifically, each statement must end with a Terminator.

The file name after the SOURCE command does not need to be enclosed in quotation marks.

This is the end of the article on "how to write mysql commands". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.