MySQL database learning manual-MySQL Language Reference

MysqlSELECTThisnIsnFournlines; + -------------------- + ThisIsFourlines + ------------------ + note that the rule has changed since MySQL3.23.6. Now we introduce the referenced identifier (database, table, and column name) (If you run in ANSI mode, it will also be used to reference the identifier ). Identifier

Mysql SELECT "This \ nIs \ nFour \ nlines"; + -------------------- + This Is Four lines + ------------------ + note that the rule has changed since MySQL3.23.6, in this case, we introduce the reference identifier (database, table, and column name) (if you run in ANSI mode, "will also be used to reference the identifier ). Identifier

Mysql> SELECT "This \ nIs \ nFour \ nlines ";
+ -------------------- +
This
Is
Four
Lines
+ -------------------- +

Note that the rule has changed since MySQL3.23.6. At this time, we introduce the reference identifier (database, table, and column name) (if you run it in ANSI mode, "will also be used to reference identifiers ).
Maximum characters allowed by the identifier
Database 64 can contain any character in a directory name /.
Table 64 contains any characters in the file name except/or.
Column 64 All characters
Alias 255 all characters

Mysql> SELECT col_name FROM tbl_name AS
WHERE l_name = 1 OR l_name = 2;

TINYINT [(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is-128 to 127, and the unsigned range is 0 to 255.
SMALLINT [(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is-32768 to 32767, And the unsigned range is 0 to 65535.
MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]
An integer of medium size. The signed range is-8388608 to 8388607, And the unsigned range is 0 to 16777215.
INT [(M)] [UNSIGNED] [ZEROFILL]
A normal integer. The signed range is-2147483648 to 2147483647, And the unsigned range is 0 to 4294967295.
INTEGER [(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for INT.
BIGINT [(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is-9223372036854775808 to 9223372036854775807, And the unsigned range is 0 to 18446744073709551615. Note that all arithmetic operations are completed with signed BIGINT or DOUBLE values. Therefore, you should not use signed big integers greater than 9223372036854775807 (63 bits), except for bitwise functions! Note that when the two parameters are INTEGER values,-, +, and * use BIGINT operations! This means that if you take two big integers (or from the function that returns integers) and the result is greater than 9223372036854775807, you can get unexpected results. A floating-point number cannot be unsigned. For a single-precision floating-point number, its precision can be <= 24. For a double-precision floating-point number, it is between 25 and 53, these types, such as FLOAT and DOUBLE, are immediately described below. FLOAT (X) has the same FLOAT and DOUBLE ranges, but the display size and decimal places are undefined. In MySQL3.23, this is a real floating point value. In earlier MySQL versions, FLOAT (precision) always has two decimal places. This syntax is provided for ODBC compatibility.
FLOAT [(M, D)] [ZEROFILL]
A small (single precision) floating point number. It cannot be unsigned. The allowed values are-3.402823466E + 38 to-1.175494351E-38,0 and 1.175494351E-38 to 3.402823466E + 38. M indicates the display width, and D indicates the decimal digits. FLOAT without parameters or a parameter with <24 represents a single precision floating point number.
DOUBLE [(M, D)] [ZEROFILL]
A normal-sized (double-precision) floating point number. It cannot be unsigned. The allowed values are-1.7976931348623157E + 308 to-2.225074255072014e-308, 0, and 2.225074255072014e-308 to 1.7976931348623157E + 308. M indicates the display width, and D indicates the number of decimal places. A double or FLOAT (X) (25 <= X <= 53) without a parameter represents a DOUBLE-precision floating point number.
Double precision [(M, D)] [ZEROFILL]
　
REAL [(M, D)] [ZEROFILL]
These are DOUBLE synonyms.
DECIMAL [(M [, D])] [ZEROFILL]
Unpack floating point number. It cannot be unsigned. The behavior is like a CHAR column: "uncompress" means that the number is stored as a string, and each bit of the value uses one character. Decimal point. For negative numbers, the "-" symbol is not calculated in M. If D is 0, there will be no decimal point or decimal part. The maximum range of a DECIMAL value is the same as that of a DOUBLE value. However, for a given DECIMAL column, the actual range can be limited through the selection of M and D. If D is omitted, it is set to 0. If M is saved, it is set to 10. Note: In MySQL3.22, the M parameter includes the symbol and the decimal point.
NUMERIC (M, D) [ZEROFILL]
This is a synonym for DECIMAL.
DATE
A date. The supported range is '2017-01-01 'to '2017-12-31 '. MySQL displays the DATE value in 'yyyy-MM-DD 'format, but allows you to assign the value to the DATE column using strings or numbers.
DATETIME
A combination of dates and times. The supported range is '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '. MySQL displays DATETIME values in 'yyyy-MM-DD HH: MM: ss' format, but allows you to assign values to DATETIME columns using strings or numbers.
TIMESTAMP [(M)]
A time stamp. The value range is '2017-01-01 00:00:00 'to a certain time on January 1, 1970. MySQL uses YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD to display the TIMESTAMP value, depending on whether M is 14 (or omitted), 12, 8, or 6, however, you can use strings or numbers to assign values to the TIMESTAMP column. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation, because if you do not assign a value to it yourself, it is automatically set to the date and time of the most recent operation. You can assign it a NULL value to set it to the current date and time. See 7.3.6 Date and Time types.
TIME
A time. The value range is '-838: 59: 59' to '2014: 59: 59 '. MySQL displays the TIME value in 'hh: MM: ss' format, but allows you to assign the value to the TIME column using a string or number.
YEAR [(24)]
A year in the format of two or four digits (4 digits by default ). The allowed values are 1901 to 2155, and 0000 (4-digit year format), if you use 2 bits, 1970-2069 (70-69 ). MySQL displays the YEAR Value in YYYY format, but allows you to assign a string or number value to the YEAR column. (YEAR is a new type in MySQL3.22 .)
CHAR (M) [BINARY]
A fixed-length string, when stored, is always filled with spaces to the right to the specified length. The range of M is 1 ~ It can contain 255 characters. When the value is retrieved, the tail of the space is deleted. CHAR values are sorted and compared in a case that is not distinguished by the default Character Set, unless BINARY keywords are given. National char (short form NCHAR) is an ansi SQL method to define CHAR columns should use the default character set. This is the default value of MySQL. CHAR is an abbreviation of CHARACTER.
[NATIONAL] VARCHAR (M) [BINARY]
A variable-length string. Note: When the value is stored, spaces at the end are deleted (different from the ansi SQL Specification ). The range of M is 1 ~ It can contain 255 characters. VARCHAR values are sorted and compared based on the default character set in uppercase/lowercase, unless BINARY keyword values are given. For details, see 7.7.1 specified implicit column change. VARCHAR is an abbreviation of character varying.
TINYBLOB
　
TINYTEXT
A blob or TEXT column with a maximum length of 255 (2 ^ 8-1) characters. For details, see 7.7.1 specified implicit column change.
BLOB
　
TEXT
A blob or TEXT column, with a maximum length of 65535 (2 ^ 16-1) characters. For details, see 7.7.1 specified implicit column change.
MEDIUMBLOB
　
MEDIUMTEXT
A blob or TEXT column with a maximum length of 16777215 (2 ^ 24-1) characters. For details, see 7.7.1 specified implicit column change.
LONGBLOB
　
LONGTEXT
A blob or TEXT column, with a maximum length of 4294967295 (2 ^ 32-1) characters. See 7.7.1 specified implicit column change
ENUM ('value1', 'value2 ',...)
Enumeration. A String object with only one value. This value type is set to 'value1 ', 'value2',..., or NULL. An ENUM can have a maximum of 65535 different values.
SET ('value1', 'value2 ',...)
A collection. A String object with zero or multiple values, each of which must be selected from the Value List 'value1 ', 'value2. A set can have up to 64 members.

The storage requirements for each column type supported by MySQL are listed by class below.
7.3.2 numeric type
Storage required for column types
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
INTEGER 4 bytes
BIGINT 8 bytes
FLOAT (X) 4 if X <= 24 or 8 If 25 <= X <= 53
FLOAT 4 bytes
DOUBLE 8 bytes
Double precision 8 bytes
REAL 8 bytes
DECIMAL (M, D) M bytes (D + 2, if M <D)
NUMERIC (M, D) M bytes (D + 2, if M <D)
7.3.3 Date and Time types
Storage required for column types
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 byte
7.3.4 string type
Storage required for column types
CHAR (M) M bytes, 1 <= M <= 255
VARCHAR (M) L + 1 byte, where L <= M and 1 <= M <= 255
TINYBLOB, tinytext l + 1 byte, here L <2 ^ 8
BLOB, text l + 2 bytes, here L <2 ^ 16
MEDIUMBLOB, mediumtext l + 3 bytes, here L <2 ^ 24
LONGBLOB, longtext l + 4 bytes, here L <2 ^ 32
ENUM ('value1 ', 'value2',...) 1 or 2 bytes, depending on the number of enumerated values (maximum 65535)
SET ('value1 ', 'value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of SET members (up to 64 members)

For alter table, load data infile, UPDATE, and multi-row INSERT statements, the transformation due to the cut is reported as a "warning.
7.3.6 Date and Time types

* The annual value in the range of 2000-69 is changed to 2069.
* The annual value in the range of 70-99 is changed to 1970-1999.

The TIMESTAMP value retrieved and displayed in MySQL depends on the display size format in the following table. The "complete" TIMESTAMP format is 14 bits, but the TIMESTAMP column can be created with a shorter display size:
Column Type display format
TIMESTAMP (14) YYYYMMDDHHMMSS
TIMESTAMP (12) YYMMDDHHMMSS
TIMESTAMP (10) YYMMDDHHMM
TIMESTAMP (8) YYYYMMDD
TIMESTAMP (6) YYMMDD
TIMESTAMP (4) YYMM
TIMESTAMP (2) YY

* Always specify the year, month, and day, even if your column type is TIMESTAMP (4) or TIMESTAMP (2 ). Otherwise, the value is not a valid date and 0 is stored.
* If you use alter table to broaden a narrow TIMESTAMP column, previously "concealed" information will be displayed.
* Similarly, narrowing down a TIMESTAMP column will not cause loss of information, except when the upper value is displayed, less information is displayed.
* Although the TIMESTAMP value is stored as a complete precision, the only function that directly operates the stored value is UNIX_TIMESTAMP (). Other function operations are performed on the formatted retrieval value, this means that you cannot use functions such as HOUR () or SECOND () unless the related part of the TIMESTAMP value is included in the formatted value. For example, the HH part of a TIMESTAMP column is displayed, unless the display size is at least 10, so try to use HOUR () on a shorter TIMESTAMP value to generate a meaningless result.

* As a string in 'hh: MM: ss' format. The "loose" syntax is allowed-any punctuation can be used as a time separator. For example, '10: 11: 12' is equivalent to '10. 12.
* It is a string in the 'hhmms' format without separators if it is interpreted as a time. For example, '000000' is interpreted as '10: 11: 12', but '000000' is invalid (it has no meaning in minutes) and becomes '00: 00: 00 '.
* As a number in HHMMSS format, if it can be interpreted as a time. For example, 101112 is interpreted as '10: 11: 12 '.
* The return value is a function that can be accepted in a TIME context, for example, CURRENT_TIME.

The following table shows the differences between the two types of columns. It demonstrates how to store variable-length string values to CHAR (4) and VARCHAR (4) columns:
Value CHAR (4) storage requirements VARCHAR (4) storage requirements
''' 4 byte ''' 1 byte
'AB' 4 bytes 'AB' 3 bytes
'Abcd' 4 bytes 'abcd' 5 bytes
'Abcdefgh' 'abccd' 4 bytes 'abccd' 5 bytes

Mysql> select id, substring (blob_col, 1,100) from tbl_name
Group by 2;
Mysql> select id, substring (blob_col, 1,100) as B from tbl_name
Group by B;

If you want to get all possible values of an ENUM column, you should use: show columns from table_name LIKE enum_column_name and analyze the ENUM definition in the second column.
7.3.7.4 SET type

For high precision, you can always change to the fixed point type stored in a BIGINT type. This allows you to use integers for all calculations and only convert the results back to floating point values if necessary. See 10.6 select a table type.
7.3.9 column Indexes

Mysql> SELECT * FROM test WHERE last_name = "Widenius"
AND first_name> = "M" AND first_name <"N ";

(...)
Brackets. Use them to force the calculation order of an expression.

Mysql> select BIT_COUNT (29 );
-> 4

Mysql> SELECT 1> '6x ';
-> 0
Mysql> SELECT 7> '6x ';
-> 1
Mysql> SELECT 0> 'x6 ';
-> 0
Mysql> SELECT 0 = 'x6 ';
-> 1

Mysql> select 1 BETWEEN 2 AND 3;
-> 0
Mysql> select 'B' BETWEEN 'a 'AND 'C ';
-> 1
Mysql> select 2 BETWEEN 2 AND '3 ';
-> 1
Mysql> select 2 BETWEEN 2 AND 'x-3 ';
-> 0

Expr LIKE pat [ESCAPE 'escape-char ']
Use a simple regular expression of SQL to compare the pattern matching. Returns 1 (TRUE) or 0 (FALSE ). With LIKE, you can use the following two wildcard characters in the Mode:
% Matches any number of characters, or even zero characters
_ Exact match of one character

Mysql> select 'monty! 'Regexp'm % y % ';
-> 0
Mysql> select 'monty! 'Regexp '.*';
-> 1
Mysql> select 'new * \ n * line 'regexp' new \ *. \ * line ';
-> 1
Mysql> select "a" REGEXP "A", "a" regexp binary "";
-> 1 0

Mysql> select IF (1> 2, 2, 3 );
-> 3
Mysql> select IF (1 <2, 'yes', 'no ');
-> 'Yes'
Mysql> select IF (strcmp ('test', 'test1'), 'yes', 'no ');
-> 'No'

Mysql> select SIGN (-32 );
->-1
Mysql> select SIGN (0 );
-> 0
Mysql> select SIGN (1, 234 );
-> 1

Mysql> select LOG10 (2 );
-> 0.301030
Mysql> select LOG10 (100 );
-> 2.000000
Mysql> select LOG10 (-100 );
-> NULL

Mysql> select ATAN (-2, 2 );
->-0.785398
Mysql> select ATAN (PI (), 0 );
-> 1.570796

Mysql> select GREATEST (2, 0 );
-> 2
Mysql> select GREATEST (34.0, 3.0, 5.0, 767.0 );
-> 767.0
Mysql> select GREATEST ("B", "A", "C ");
-> "C"

Mysql> select CONV ("a", 16, 2 );
-> '123'
Mysql> select CONV ("6E", 18, 8 );
-> '123'
Mysql> select CONV (-17,10,-18 );
-> '-H'
Mysql> select CONV (10 + "10" + '10' + 0xa, 10, 10 );
-> '40'

Note that for multi-byte characters, its CHAR_LENGTH () is calculated only once.
LOCATE (substr, str)
　
POSITION (substr IN str)
Returns the position of the substring substr In the first occurrence of the str. If the substring is not in the str, the return value is 0.

Mysql> select SUBSTRING ('quadratically ', 5, 6 );
-> 'Ratica'

Mysql> select SOUNDEX ('hello ');
-> 'H400'
Mysql> select SOUNDEX ('quadratically ');
-> 'Q36324'

Mysql> select FIELD ('ej', 'hej', 'ej', 'heja ', 'hej', 'foo ');
-> 2
Mysql> select FIELD ('fo', 'hej', 'ej', 'heja ', 'hej', 'foo ');
-> 0

This function is reliable for multiple bytes.
LOAD_FILE (file_name)
Read the file and return the file content as a string. The file must be on the server, you must specify the full path name of the file, and you must have the file permission. All content of the file must be readable and smaller than max_allowed_packet. If the file does not exist or cannot be read due to one of the above reasons, the function returns NULL.