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

What sentences are collected in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Export database

When it comes to exporting the database, you can definitely mysqldump. Because there are many parameters involved in mysqldump, you can't remember these parameters completely when using mysqldump. Let's first familiarize ourselves with the commonly used parameters, such as the following table:

(1) Export the entire database

Mysqldump-uroot-p111111-h272.30.14.242 dbname > mydb.sql

(2) derive stored procedures and functions

Mysqldump-uroot-p111111-h272.30.14.242-ntd-R dbname > prorfunc.sql

(3) Export database tables

Mysqldump-uroot-p111111-h272.30.14.242 dbnametablename > mydb.sql

If you only need to export the table structure, you can add the parameter-d or-no-data

If you only need to export table data, you can add the parameter-t or-- no-create-info

Import database

(1) Import when the database is not connected

Mysql-uroot-p111111-h272.30.14.242 dbname < mydb.sql

(2) Import when connecting to the database

Connect to the database mysql-uroot-p111111-h272.30.14.242 specify the encoding rules for passing characters between the client and the server: utf8 set names utf8 imports the database source / tmp/mydb.sql using the database use dbname

Other data export / import methods

SELECT INTO... The OUTFILE statement exports the table data to a text file, and uses LOAD DATA. The INFILE statement restores data. However, this method can only export or import the contents of the data, not including the structure of the table.

(1) SELECT INTO... OUTFILE export data

SELECT * FROM mytable INTO OUTFILE'/ tmp/mytable.txt'

You can set the specified format of the data output through the command options, and the following statement is the export CSV format:

SELECT * FROM mytable INTO OUTFILE'/tmp/mytable.txt' FIELDS TERMINATED BY', 'ENCLOSED BY' "'LINESTERMINATED BY'\ r\ n'

FIELDS clause: there are three subclauses in the FIELDS clause: TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY. If the FIELDS clause is specified, at least one of the three subclauses must be specified.

TERMINATED BY is used to specify symbols between field values

The symbol used by the ENCLOSED BY clause to specify the character value in the package file

The ESCAPED BY clause is used to specify escape characters.

LINES clause: use TERMINATED BY in the LINES clause to specify the flag at the end of a line.

(2) LOAD DATA... INFILE Import data

LOAD DATA LOCAL INFILE 'dump.txt' INTOTABLE mytable FIELDS TERMINATED BY': 'LINES TERMINATED BY'\ r\ n'

By default, LOAD DATA inserts data in the order of the columns in the data file, and if the columns in the data file are inconsistent with the columns in the inserted table, you need to specify the order of the columns. For example, if the column order in the data file is a _ c, but the column order in the insert table is _, the syntax of data import is as follows:

LOAD DATA LOCAL INFILE 'mytable.txt' INTO TABLE mytable (b, c, a)

String operation

(1) string concatenation

1) CONCAT (S1 and S2...) Function

Concatenate all strings together

Sentence: SELECT CONCAT ('2019 June 18); execution result: 20190618

2) CONCAT_WS (s _ 1, s _ 1, 2, etc.) Function

Concatenate all the following strings by * string s

Sentence: SELECT CONCAT_WS ('-', '2019, 06pm, 18'); execution result: 2019-06-18

(2) string substitution

REPLACE (s _ 1 ~ 2) function

Replace S1 string in string s with S2 string

Statement: SELECT REPLACE ('helloname','name','world'); execution result: hello world

Time function

(1) time transfer string

The DATE_FORMAT function is used to display date / time data in different formats

Syntax: DATE_FORMAT (date,format)

Format specifies the output format of the date / time.

Example:

Statement: select DATE_FORMAT (now (),'% Y-%m-%d% T'); execution result: 2019-07-09 15:35:19 statement: select DATE_FORMAT (now (),'% Y-%m-%d% Hpurs% iY-%m-%d% s'); execution result: 2019-07-09 15:36:02

(2) string transfer time

STR_TO_DATE is used to convert characters to date / time format

Syntax: STR_TO_DATE (string, format)

Statement: SELECT STR_TO_DATE ("2019 07 07", "% Y% m d"); execution result: 2019-07-07

(3) time operation

Function from the date plus the specified time interval

DATE_ADD (date,INTERVAL expr type)

Function subtracts the specified interval from the date

DATE_SUB (date,INTERVAL expr type)

Available values for type are:

SECOND 、 MINUTE 、 HOUR 、 DAY 、 WEEK 、 MONTH 、 QUARTER 、 EAR

Example:

Statement: SELECT DATE_ADD (now (), INTERVAL 2 DAY); execution result: 2019-07-11 15:48:36 statement: SELECT DATE_SUB (now (), INTERVAL 2 DAY); execution result: 2019-07-07 15:48:36

Precision control after floating point number operation

(1) convert the string with specified precision

The FORMAT function is formatted in mysql for data content, which can be formatted as integers or floating-point numbers, separated by commas when the integer part exceeds three digits, and the returned result is of type string.

Statement: SELECT FORMAT (1012.232 Personal2); execution result: 1012.23

(2) convert floating-point numbers with specified precision

The FORMAT function returns data as a string, and to return a floating-point number, we use the CONVERT function.

Sentence: SELECT CONVERT (1012.232, decimal (12jin2)); execution result: 1012.23 after reading the above, have you mastered the methods of sentence collection in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.