Read common MySQL syntax in one article

MySQL query statement link address
What is MySQL?
Become the foundation of MySQL~

data type

Numeric value: integer, floating point number (decimal)
Date: year, month, day, hour, minute and second, month, day, hour, minute and second
String: text type string, binary type string

plastic

typeStorage requirementsSigned value rangeUnsigned value range
tinyint1 byte-128~1270~255
Smallint2 bytes-32768~327670~65535 0~2^16-1
Mediumint3 bytes-8399608~83996070~2^24-1
int4 bytes-2147483648~21474836470~2^32-1
Bigint8 bytes-263~263-10~2^64-1

Difference between signed and unsigned numbers:
(1) Signed numbers are positive and negative numbers that can be used to distinguish numerical values. Unsigned numbers have only positive values and no negative values.
(2) Secondly, when a number is unsigned, its highest bit is only used to represent the size of the number. When a number is a signed number, the highest bit is called the sign bit. When the sign bit is 1, it means the number is negative, and when it is 0, it means positive.
(3) Finally, the range represented by the signed number and the unsigned number is different, that is, for bytes of the same length, the signed number is smaller than the maximum value of the unsigned number (the leftmost bit is used to represent the symbol).
Example:

create table num (
a tinyint,
b smallint,
c mediumint,
d int,
e bigint
);

(2) in int(2) indicates the display width, that is, the display number of digits, but it is not strict. Even if the written value exceeds this width, it can be written and displayed normally as long as the value does not exceed the value range of the data type

mysql> create table num_1 (id int(2)); 			  #Not strict for integers
mysql> create table num_2 (name char(2))	      #Strict for string
mysql> insert num_2 values ('qqq');
ERROR 1406 (22001): Data too long for column 'name' at row 1

Create a table with unsigned integer data types (unsigned:

create table num_3 (id tinyint unsigned);  
mysql> insert num_3 values(300);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

Floating point number

float(x,y): single precision floating-point number, accounting for 4 bytes of storage. X is the precision, indicating the total number of integers and decimals. Y is the scale, indicating the number of decimals. The significant digits of X are 6~7

mysql> create table float_1( a float(3,2), b float(5,0), c float(3,3), d float(4,1));
mysql> insert float_1 values(9.99,12345,0.0006,123.05);	  # 5 rounding, 6 rounding (the scale range can be unlimited, but the displayed content is only the digits of "scale")
mysql> select * from float_1;
+------+-------+-------+-------+
| a    | b     | c     | d     |
+------+-------+-------+-------+
| 9.99 | 12345 | 0.001 | 123.0 |
+------+-------+-------+-------+
1 row in set (0.00 sec)

Double (double)

double(x,y): Double precision floating point number,8 bytes of storage, x The significant bit of is 16

Decimal (decimal)

decimal(x,y): Fixed point number,16 bytes of storage, x The maximum number of significant bits of is 65,y That is, the maximum number of decimal places is 30.
If the precision and scale of "fixed point number" are not specified manually,The default is decimal(10,0),This data type can be used for bank account calculation!

Date type

year

Format 1: expressed in four digit string format, with the range of '1901' - '2155'
Format 2: expressed in four digit format, range 1901-2155
Format 3: expressed in two digit string format, with the range of '00' - '99'
'00' - '69' means' 2000 '-' 2069 '
'70' - '99' means' 1970 '-' 1999 '
Format 4: expressed in two digits, range 00-99
00-69 means 2000-2069
70-99 indicates 1970-1999
Example:

mysql> create table year(nq year);
mysql> insert year values('2020'),(1985),('00'),(99);
mysql> select * from year;
+------+
| nq   |
+------+
| 2020 |
| 1985 |
| 2000 |
| 1999 |
+------+
4 rows in set (0.00 sec)

time

Format 1: 'HH:MM:SS', HH means hour, MM means minute, SS means second
Format 2: 'HHMMSS'
Format 3:'D HH:MM:SS', D refers to the number of days, which is calculated as the sum of the corresponding hour and the value of HH [the value range of D is -34~34]
The value range of time is -838:59:59-838:59:59 (-34*24+24=839)
Example:

mysql> create table time(sj time);
mysql> insert time values('38:59:59'),('1 14:59:59'),('385959');
mysql> select * from time;
+----------+
| sj       |
+----------+
| 38:59:59 |
| 38:59:59 |
| 38:59:59 |
+----------+
3 rows in set (0.00 sec)

date

Format 1: 'YYYY-MM-DD', YYYY means year, MM means month, DD means day, and the value range is' 1000-01-01~9999-12-03 '
Format 2: 'YY-MM-DD', YY refers to year, mm refers to month, DD refers to day, YY value range '00' - '99', '00' - '69' refers to '2000' - '2069', and '70' - '99' refers to '1970' - '1999'
Format 3: YYMMDD 'YYMMDD'
Example:

mysql> create table data(date date);
mysql> insert data values('2021-06-28'),('2021-06-28'),(210628);
mysql> select * from data;
+------------+
| date       |
+------------+
| 2021-06-28 |
| 2021-06-28 |
| 2021-06-28 |
+------------+
3 rows in set (0.00 sec)

datetime

Format 1: 'YYYY-MM-DD HH:MM:SS', value range:' 1010-01-01 00:00:00 '~' 9999-12:31 99:99:99 '
Format 2: 'YY-MM-DD HH:MM:SS', YY value range' 00 '-' 99 ',' 00 '-' 69 'means' 2000' - '2069', and '70' - '99' means' 1970 '-' 1999 '
Format 3: 'YYYYMMDDHHMMSS',' YYMMDDHHMMSS'
Format 4: 'YYYY/MM/DD HH/MM/SS'
Example:

mysql> create table datatime(datetime datetime);
mysql> insert datatime values('2021-06-28 00:00:00'),('20-06-28 15:18:25'),(mysql> insert datatime values('2021-06-28 00:00:00'),('20-18:25'),(20210628151936),(210628151936);
mysql> select * from datatime;
+---------------------+
| datetime            |
+---------------------+
| 2021-06-28 00:00:00 |
| 2020-06-28 15:18:25 |
| 2021-06-28 15:19:36 |
| 2021-06-28 15:19:36 |
+---------------------+
4 rows in set (0.00 sec)

timestamp

Format: 'YYYY-MM-DD HH-MM-SS', accounting for 4 bytes, value range' 1970-01-01 08:00:01 UTC '~' 2038-01-19 03:14:07 UTC '(no range)

mysql> create table timestamp(
a datetime,
b timestamp
);
mysql> insert timestamp values(20210628161430,'20220202161530');
mysql> select * from timestamp;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2021-06-28 16:14:30 | 2022-02-02 16:15:30 |
+---------------------+---------------------+
1 row in set (0.00 sec)

String type

Text type string: restricted by character set. For the uft8 character set, numbers, letters and symbols occupy 1 byte, Chinese characters occupy 3 bytes, and the characters defined in the unicode character set occupy 2 bytes
char(M): fixed length text string. M indicates the display width. It indicates that up to M characters can be displayed. The value range of M is 0-255. If the number of characters written is less than the value of M, the number of M will be filled in with spaces. When querying, the filled spaces will be deleted in the display. If the number of characters written is greater than m, the excess part will be intercepted

create table test12 (a char(3));
insert into test12 values ('a'),('abc'),('abcd'),('Ha ha'),('Ha ha ha'),('Ha?Huh??');		#Failed to insert data

varchar(M): variable length text string. M is the display width, which means that up to M characters can be displayed. The value range of M is 1-21844. If the number of characters written is less than m, no space will be added. If the number of characters written is greater than m, the extra characters will be intercepted or an error will be reported. varchar will take an additional 1~2 bytes to record the actual character length written. If the number of characters is <=255, use 1 byte, otherwise use 2 bytes
enum: enumeration, a string object [select a value within a given range]
Format:
Field enum (value 1, value 2, value 3... Value n) if there is a space after the value, the space will be deleted, and there can be at most 65535 values. Values are called elements, and each value has its own number. The number starts from 1. Selecting a non saved number when writing data will cause the field content to be empty

mysql> create table xs(
name varchar(5),
cj int,
pj enum('nice','good','ok','???'));
mysql> insert xs values ('q',100,1),('w',66,2),('e',-10,4);
mysql> select * from xs;
+------+------+------+
| name | cj   | pj   |
+------+------+------+
| q    |  100 | nice |
| w    |   66 | good |
| e    |  -10 | ???  |
+------+------+------+
3 rows in set (0.00 sec)

text

typeSupported storage size
tinytextMaximum 255 bytes supported (2^8-1)
textMaximum support 65535 bytes (2^16-1)
mediumtextMaximum 2^24-1 bytes supported
longtextMaximum 2^32-1 bytes supported

set

Collection is a string object that can select zero or more values in a given range
Format:
The maximum number of field set (value 1, value 2, value 3... Value n) values can be 64. When writing data, you can select multiple values to write. The order of the specified values will be automatically arranged according to the set order. If a value is selected multiple times, it will be automatically de duplicated. If a non-existent value is written, the non-existent value will be deleted / an error will be reported directly

mysql> create table set1(a set('n','f','s'));
mysql> insert set1 values ('s,f,n');
mysql> insert set1 values ('n,s,f,n');
mysql> select * from set1;
+-------+
| a     |
+-------+
| n,f,s |
+-------+
1 row in set (0.00 sec)

Binary string

It is a byte stream, not a character set limitation. It can specify the size of bytes that can be written. It is generally used to store pictures Video Audio and other media data

bit

bit(M): bit. M refers to the size of bits that can be stored in the field. The value range of M is 1~65

mysql> create table bit_1 (a bit(4));
mysql> insert bit_1 values (1),(8),(10);
mysql> select bin(a) from bit_1;
+--------+
| bin(a) |
+--------+
| 1      |
| 1000   |
| 1010   |
+--------+
3 rows in set (0.00 sec)

mysql> insert bit_1 values (a),(A),(10);
mysql> select bin(a) from bit_1;
+--------+
| bin(a) |
+--------+
| 1      |
| 1000   |
| 1010   |
| NULL   |
| NULL   |
| 1010   |
+--------+
6 rows in set (0.00 sec)

mysql> create table bit_2 (a bit(8));
mysql> insert bit_2 values('a'),('A');
mysql> select bin(a) from bit_2
    -> ;
+---------+
| bin(a)  |
+---------+
| 1100001 |
| 1000001 |
+---------+
2 rows in set (0.00 sec)

binary

binary(M): fixed length binary string. M indicates the number of bits that can be stored. The value range of M is 0-255. If the written data is less than the value of M, it will be filled with \0

mysql> create table binary_1(a binary(4));
mysql> insert binary_1 values(1),('a'),('ab12');
mysql> select bin(a) from binary_1;
+--------+
| bin(a) |
+--------+
| 1      |
| 0      |
| 0      |
+--------+
3 rows in set (0.00 sec)

mysql> insert binary_1 values('5555'),('11');
mysql> select bin(a) from binary_1;
+---------------+
| bin(a)        |
+---------------+
| 1             |
| 0             |
| 0             |
| 1010110110011 |
| 1011          |
+---------------+
5 rows in set (0.00 sec)

varbinary

varbinary(M): variable length binary string. M indicates the size of bytes that can be stored. The value range of M is 0-65535 (about 65532). If the size of the written data is smaller than the value of M, it will not be supplemented, and 1-2 bytes will be used to store the actual size of the written data

mysql> create table var_1 (a varbinary(7));
mysql> insert var_1 values(1111111),('23333'),('Ha ha 1');
mysql> insert var_1 values(1111111),('23333'),('Ha ha 111');
ERROR 1406 (22001): Data too long for column 'a' at row 3
mysql> select * from var_1;
+---------+
| a       |
+---------+
| 1111111 |
| 23333   |
| Ha ha 1   |
+---------+
3 rows in set (0.00 sec)

mysql> select bin(a) from var_1;
+-----------------------+
| bin(a)                |
+-----------------------+
| 100001111010001000111 |
| 101101100100101       |
| 0                     |
+-----------------------+
3 rows in set (0.00 sec)

blob

BLOB type fields are used to store binary data

typeSupported storage sizes
tinyblob2^8 bytes
blob2^16 bytes
mediumblob2^24 bytes
longblob2^32 bytes

Move the selected photos to the data directory of the database

cp 1.jpg /usr/local/mysql/data/
# Modify the permission so that your MySQL user can have a permission for photos
chown mysql:mysql /usr/local/mysql/data/1.jpg
chmod o+r /usr/local/mysql/data/1.jpg
create table test_image1 (
name char(5),
img mediumblob
);
insert into test_image1 values ('1',load_file('/usr/local/mysql/data/1.jpg'));

load Meaning of loading
 Generally, the database does not store photos directly, but the name and path
 Pictures are stored in the file system. A small amount can,A large number of words will not be stored in the database,Reading is too much trouble!

select * from test_image1(Some databases will display NULL,Mariadb The database directory for is/var/lib/mysql)

operator

Arithmetic operator

+: Addition operation(Fastest)
-: Subtraction operation
*: Multiplication operation
/: Division operation
%: Residual

Comparison operator

=		equal to
<=>		Safety equals
<>,!=	Not equal to
>		greater than
>=		Greater than or equal to
<		less than
<=		Less than or equal to

1.=: equals operator, used to judge the value Whether the string and expression are equal
Returns 1 if equal, 0 if unequal, and NULL if the compared value is NULL (any value =NULL equals NULL)

mysql> select 1=1,-1=1,'1'=1,'a'=1,'a'='a','a'=NULL,NULL=NULL,1+2=3*1;
+-----+------+-------+-------+---------+----------+-----------+---------+
| 1=1 | -1=1 | '1'=1 | 'a'=1 | 'a'='a' | 'a'=NULL | NULL=NULL | 1+2=3*1 |
+-----+------+-------+-------+---------+----------+-----------+---------+
|   1 |    0 |     1 |     0 |       1 |     NULL |      NULL |       1 |
+-----+------+-------+-------+---------+----------+-----------+---------+
1 row in set, 1 warning (0.01 sec)

2.<=>: safety equals operator, which is used to judge the value Whether the string and expression are equal
Null values can be compared. If they are equal, 1 is returned. If they are not equal, 0 is returned. (any value < = >null is a real comparison, "equal is equal, unequal is unequal")

mysql> select 1<=>1,-1<=>1,'a'<=>1,'a'<=>'a','a'<=>NULL,NULL<=>NULL,'null'<=>null;
+-------+--------+---------+-----------+------------+-------------+---------------+
| 1<=>1 | -1<=>1 | 'a'<=>1 | 'a'<=>'a' | 'a'<=>NULL | NULL<=>NULL | 'null'<=>null |
+-------+--------+---------+-----------+------------+-------------+---------------+
|     1 |      0 |       0 |         1 |          0 |           1 |             0 |
+-------+--------+---------+-----------+------------+-------------+---------------+
1 row in set, 1 warning (0.00 sec)

Judgement operator

is null: determines whether a value is empty. If the value is empty, 1 is returned. If not, 0 is returned
isnull: determines whether a value is empty. If the value is empty, 1 is returned. If not, 0 is returned

mysql> select NULL is null,5 is null,'haha' is null,isnull(233),isnull(NULL),isnull('null');
+--------------+-----------+----------------+-------------+--------------+----------------+
| NULL is null | 5 is null | 'haha' is null | isnull(233) | isnull(NULL) | isnull('null') |
+--------------+-----------+----------------+-------------+--------------+----------------+
|            1 |         0 |              0 |           0 |            1 |              0 |
+--------------+-----------+----------------+-------------+--------------+----------------+

is not null: determines whether a value is not empty. If the value is empty, it returns 0. If not, it returns 1

select NULL is not null,5 is not null,'haha' is not null;

between and: judge whether a value is within a given range

mysql> select 5 between 3 and 10,20 between 10 and 15,'b' between 'a' and 'c';
+--------------------+----------------------+-------------------------+
| 5 between 3 and 10 | 20 between 10 and 15 | 'b' between 'a' and 'c' |
+--------------------+----------------------+-------------------------+
|                  1 |                    0 |                       1 |
+--------------------+----------------------+-------------------------+
1 row in set (0.00 sec)

Max / min

least: when there are two or more data, the minimum value is returned. If there is NULL, NULL is returned

select least(1,2,3,4,5),least('a','b','d','c'),least('a','A'),least('a',NULL);

greatest: when there are two or more data, the maximum value is returned. If there is NULL, NULL is returned

select greatest(1,2,3,4,5),greatest('a','b','d','c'),greatest('a','A'),greatest('a',NULL);

in

In: judge whether a value is in the specified list
not in: determines whether a value is no longer in the specified list

select  5 in(1,2,3,4,5),null in(1,2,3,4,5),'a' in('a',1,2,'c'),6 in(NULL,1,2,3,6);
select  5 not in(1,2,3,4,5),null not in(1,2,3,4,5),'a' not in(1,2,'c'),6 not in(NULL,1,2,3);

mysql> select 'q' in (null,1,2,'q');
+-----------------------+
| 'q' in (null,1,2,'q') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select 'b' in (null,1,2,'q');
+-----------------------+
| 'b' in (null,1,2,'q') |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set, 1 warning (0.05 sec

like

like: used to match strings. Symbols_ Used to match any one character, the symbol% is used to match any number of characters.
Returns 1 if characters match, 0 if characters do not match

mysql> select 'sjk' like '_jk','sjk' like '_kk','woaixuexi' like 'wo%','woaixuexi' like '%xuexi';
+------------------+------------------+------------------------+---------------------------+
| 'sjk' like '_jk' | 'sjk' like '_kk' | 'woaixuexi' like 'wo%' | 'woaixuexi' like '%xuexi' |
+------------------+------------------+------------------------+---------------------------+
|                1 |                0 |                      1 |                         1 |
+------------------+------------------+------------------------+---------------------------+
1 row in set (0.00 sec)

regexp

regexp: regular expression matching. It is used to match strings. If it matches, it returns 1. If it does not match, it returns 0

^: Match with..String beginning with
$: Match with..a null-terminated string 
.: Represents any character
*: Indicates that the preceding character is repeated any time
.*: Represents any number of characters
[]: Used to match any character within parentheses

mysql> select 'sjk' regexp '^s','sjk' regexp 's$','sjk' regexp '.jk',
'woaixuexi' regexp 'wo.*','abc' regexp '[hahaha]','lala' regexp '[haha]';
+-------------------+-------------------+--------------------+---------------------------+----------------------
| 'sjk' regexp '^s' | 'sjk' regexp 's$' | 'sjk' regexp '.jk' | 'woaixuexi' regexp 'wo.*' | 'abc' regexp '[hahaha]' | 'lala' regexp '[haha]' |
+--------------+--------------+---------------+--------------------+--------------+------------------------+----
|            1 |            0 |             1 |                  1 |            1 |                      1 |
+-------------------+-------------------+--------------------+---------------------------+----------------------
1 row in set (0.00 sec)

Logical operator

not,!:   Logical non operator
and,&&:  logical and operator 
or,||:   logical or operator 
xor:     Logical XOR operator

1.(not!) Returns 1 when the operand is 0, 0 when the operand is not 0, and NULL when the operand is NULL

mysql> select not 0,not 1,not 2,not -1,not 'a',not null;
+-------+-------+-------+--------+---------+----------+
| not 0 | not 1 | not 2 | not -1 | not 'a' | not null |
+-------+-------+-------+--------+---------+----------+
|     1 |     0 |     0 |      0 |       1 |     NULL |
+-------+-------+-------+--------+---------+----------+
1 row in set, 1 warning (0.00 sec

2.(and) returns 1 when all operands are non-zero and not empty, and returns 0 when any operand is 0,
Returns NULL when none of the operands is zero but any operand is NULL

mysql> select 1 and -1,1 and 0,null and 0,null and 1,0 and 0,null and null;
+----------+---------+------------+------------+---------+---------------+
| 1 and -1 | 1 and 0 | null and 0 | null and 1 | 0 and 0 | null and null |
+----------+---------+------------+------------+---------+---------------+
|        1 |       0 |          0 |       NULL |       0 |          NULL |
+----------+---------+------------+------------+---------+---------------+
1 row in set (0.00 sec)

3.(or) returns 0 when all operands are 0. Returns NULL when the operands are 0 and NULL or all are NULL,
Returns 1 when any operand is not 0 and is not empty

select 0 or 0 or 0 or null or -1;
mysql> select null or 1,1 or 1,0 or 1,0 or 0,-1 or 1,0 or null,null or null;
+-----------+--------+--------+--------+---------+-----------+--------------+
| null or 1 | 1 or 1 | 0 or 1 | 0 or 0 | -1 or 1 | 0 or null | null or null |
+-----------+--------+--------+--------+---------+-----------+--------------+
|         1 |      1 |      1 |      0 |       1 |      NULL |         NULL |
+-----------+--------+--------+--------+---------+-----------+--------------+
1 row in set (0.00 sec)

4.(xor) when any operand is NULL, NULL is returned. For non NULL operands:
Returns 0 if both operands are 0 or none are 0, and 1 if one operand is 0 and the other operand is not 0
x xor y: (x and (not y)) or ((not x) and y)

mysql> select  1 xor 1,0 xor 0,10 xor 0,null xor 1,null xor 0,null xor null;
+---------+---------+----------+------------+------------+---------------+
| 1 xor 1 | 0 xor 0 | 10 xor 0 | null xor 1 | null xor 0 | null xor null |
+---------+---------+----------+------------+------------+---------------+
|       0 |       0 |        1 |       NULL |       NULL |          NULL |
+---------+---------+----------+------------+------------+---------------+
1 row in set (0.37 sec)

Bitwise Operators

|: Bit or operator		# (1 if there is 1, 0 if all 0)
&: Bit and operator		# (all 1 means 1, and 0 means 0)
^: Bitwise exclusive or operator   # (0 if the same, 1 if different)
<<: Bit shift left operator
>>: Bit shift right operator
~: Bitwise negation operator

|:One or both of the corresponding binaries are 1,1, otherwise 0
mysql> select 15 | 10,6 | 9 | 12; 		# 11 is 1, 10 is 1, so 15 × 10 is 15
+---------+------------+
| 15 | 10 | 6 | 9 | 12 |
+---------+------------+
|      15 |         15 |
+---------+------------+
1 row in set (0.00 sec)

&:If the corresponding binary is 1, it is 1; otherwise, it is 0
mysql> select 21 & 13,7 & 10 & 13;
+---------+-------------+
| 21 & 13 | 7 & 10 & 13 |
+---------+-------------+
|       5 |           0 |
+---------+-------------+
1 row in set (0.00 sec)

^:Returns 0 if the corresponding binary is the same, and returns 1 if not
mysql> select 10 ^ 20,5 ^ 15 ^ 25;
+---------+-------------+
| 10 ^ 20 | 5 ^ 15 ^ 25 |
+---------+-------------+
|      30 |          19 |
+---------+-------------+
1 row in set (0.00 sec)

<<:Causes the specified binary number to`High order`Move the specified number of digits. After the move is completed`Low order`Fill the empty position with 0
mysql> select  5<<2,20<<3,6<<4;                                                                                 +------+-------+------+
| 5<<2 | 20<<3 | 6<<4 |
+------+-------+------+
|   20 |   160 |   96 |
+------+-------+------+
1 row in set (0.00 sec)

>>:Causes the specified binary number to`Low order`Move the specified number of digits,After the move is completed`Low order`Will be removed, and the left high-order empty will be filled with 0
mysql> select  5>>2,6>>4;
+------+------+
| 5>>2 | 6>>4 |
+------+------+
|    1 |    0 |
+------+------+
1 row in set (0.00 sec)

~:Reverse the corresponding binary bit by bit, and 1 becomes 0,0 Becomes 1, and&Use with
mysql> select ~1024&5,~5%8,~1;
+---------+------+----------------------+
| ~1024&5 | ~5%8 | ~1                   |
+---------+------+----------------------+
|       5 |    2 | 18446744073709551614 |
+---------+------+----------------------+
1 row in set (0.01 sec)

priority

Priority of operators (from low to high)

=: Assignment Operators 
|| or
xor
&& and
! not
between  and
=,<=>,>=,>,<=,<,<>,!=,like,in,regexp
|
&
<<,>>
+,-
*,/,%
-Indicates a minus sign
()

function

Mathematical function

abs (x)

Find the absolute value of x

> select abs(-1),abs(10),abs(0);
+---------+---------+--------+
| abs(-1) | abs(10) | abs(0) |
+---------+---------+--------+
|       1 |      10 |      0 |
+---------+---------+--------+
1 row in set (0.00 sec

pi (x)

Find pi

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

sqrt (x)

Find the square root of x. If a negative number has no square root, NULL will be returned

mysql> select sqrt(25),sqrt(-49),sqrt(9);
+----------+-----------+---------+
| sqrt(25) | sqrt(-49) | sqrt(9) |
+----------+-----------+---------+
|        5 |      NULL |       3 |
+----------+-----------+---------+
1 row in set (0.00 sec)

mod (x,y)

Function after x is divided by y

mysql> select mod(25,2),mod(33,5);
+-----------+-----------+
| mod(25,2) | mod(33,5) |
+-----------+-----------+
|         1 |         3 |
+-----------+-----------+
1 row in set (0.00 sec)

Get integer

ceil(x) an integer whose value is not less than and closest to the original number
ceiling(x) an integer whose value is not less than and closest to the original number

mysql> select ceil(1.111),ceil(-1.111),ceiling(2.456);
+-------------+--------------+----------------+
| ceil(1.111) | ceil(-1.111) | ceiling(2.456) |
+-------------+--------------+----------------+
|           2 |           -1 |              3 |
+-------------+--------------+----------------+
1 row in set (0.00 sec)

float(x) an integer whose value is not greater than and closest to the original number

select floor(1.111),floor(-1.111),floor(1.666);

Get random number

The range of random numbers obtained by rand() is between 0 and 1, and each execution will result in different random numbers
rand(x) returns a certain random number based on the value of X

mysql> select rand(),rand(),rand();
mysql> select rand(6),rand(7),rand(8),rand(6);
+--------------------+--------------------+---------------------+--------------------+
| rand(6)            | rand(7)            | rand(8)             | rand(6)            |
+--------------------+--------------------+---------------------+--------------------+
| 0.6563190842571847 | 0.9065021936842261 | 0.15668530311126755 | 0.6563190842571847 |
+--------------------+--------------------+---------------------+--------------------+
1 row in set (0.00 sec)

Rounding

round(x) rounds x to the nearest whole number without decimal
round(x,y) rounds X. y refers to the number of digits from which y is positive after the decimal point, and Y is negative before the decimal point

mysql> select round(321.456,2),round(321.456,-2),round(321.555,1);
+------------------+-------------------+------------------+
| round(321.456,2) | round(321.456,-2) | round(321.555,1) |
+------------------+-------------------+------------------+
|           321.46 |               300 |            321.6 |
+------------------+-------------------+------------------+
1 row in set (0.00 sec)

Intercept value

truncate(x,y) truncates X. y is a positive number, which means that the decimal is truncated and several decimal places are reserved. Y is a negative number, which means that the decimal is completely truncated and the integer is 0 from the single digit, which means that the Y digit is taken

mysql> select truncate(456.789,2),truncate(456.789,-2),truncate(456.789,3);
+---------------------+----------------------+---------------------+
| truncate(456.789,2) | truncate(456.789,-2) | truncate(456.789,3) |
+---------------------+----------------------+---------------------+
|              456.78 |                  400 |             456.789 |
+---------------------+----------------------+---------------------+
1 row in set (0.00 sec)

symbol

sign(x): a sign function, where x is a positive number and returns 1, X is 0 and returns 0, X is negative and returns -1, and X is NULL and returns NULL

mysql> select sign(10),sign(0),sign(-5),sign(NULL);
+----------+---------+----------+------------+
| sign(10) | sign(0) | sign(-5) | sign(NULL) |
+----------+---------+----------+------------+
|        1 |       0 |       -1 |       NULL |
+----------+---------+----------+------------+
1 row in set (0.01 sec)

exponentiation

pow(x,y) finding the Y power of X
power(x,y) finding the Y power of X

mysql> select pow(2,5),power(3,3);
+----------+------------+
| pow(2,5) | power(3,3) |
+----------+------------+
|       32 |         27 |
+----------+------------+
1 row in set (0.10 sec)

exp(y): find the Y square of e, and the value of e is about 2.71828

select exp(3);

Logarithmic operation

Find index

# log(): Based on e
select log(20),log(30);

# log10(): Base on 10
mysql> select log10(1000),log10(100);
+-------------+------------+
| log10(1000) | log10(100) |
+-------------+------------+
|           3 |          2 |
+-------------+------------+
1 row in set (0.00 sec)

Angle radian conversion

# radians(x): converts the angle X to the corresponding radian (1 π =180 °)
select radians(180),radians(360),radians(90);

# degrees(x): converts radians x to corresponding angles
mysql> select degrees(pi()/2),degrees(pi()),degrees(pi()*2);
+-----------------+---------------+-----------------+
| degrees(pi()/2) | degrees(pi()) | degrees(pi()*2) |
+-----------------+---------------+-----------------+
|              90 |           180 |             360 |
+-----------------+---------------+-----------------+
1 row in set (0.00 sec)

Sine and anti sine

sin= opposite side ratio bevel

# sin(x): 	 Find the sine value whose radian is x (radian can be converted into angle)
select sin(pi()/6),sin(pi()/4);

# asin(x): find the radian corresponding to the sine value x (radian can be converted into angle)
mysql> select degrees(asin(0.5));
+--------------------+
| degrees(asin(0.5)) |
+--------------------+
| 30.000000000000004 |
+--------------------+
1 row in set (0.00 sec)

Cosine and arccosine

cosA= ratio of adjacent edge to beveled edge

# cos(x): 	 Find the cosine value whose radian is x
select cos(pi()/6),cos(pi()/3);

# acos(x): find the radian corresponding to the cosine value x
select degrees(acos(0.5));

Tangent, arctangent and cotangent

tanA= opposite edge next to adjacent edge

# tan(x): find the tangent value of radian x
select tan(pi()/6),tan(pi()/4);

# atan(x): find the radian corresponding to the tangent value x
select degrees(atan(1));

# cot(x): find the cotangent value of radian x
select cot(pi()/6),cot(pi()/3);

String function

1. function for calculating character length

# char_length(str): counts the number of characters in a string. Spaces are also counted as one character
select char_length('wo ai xue '),char_length('yu sheng jie xian zhen ku');

# length(str): 	  By the byte size of the string, one space is one byte
select length('I love learning sjk hahaha'),length('It's cool to be born and tied~~~');

2. merge string functions

# concat(s1,s2.....): Returns NULL if the value has null
select concat('mysql-bin','.000001');
select concat('ha','ha',null);

# concat_ws(x,s1,s2): use X as separator to merge strings. If the separator is null, null will be returned
select concat_ws('@','12346','qq.com');

3. function to replace string

insert(s1,x,len,s2): From string s1 of x Bit start,Use string s2 Replace, replace len Characters
mysql> select insert('woaixuexi',2,-5,'123');
+--------------------------------+
| insert('woaixuexi',2,-5,'123') |
+--------------------------------+
| w123                           |
+--------------------------------+
1 row in set (0.00 sec)

4. case conversion function

# lower(str): convert upper case to lower case
# lcase(str): convert uppercase to lowercase
select lower('SJK'),lcase('Sjk');

# upper(str): convert lowercase to uppercase
# ucase(str): convert lowercase to uppercase
select upper('sjk'),ucase('sJk');

5. get the string of specified length

# left(s,n) 	 Get the characters after n bits of the string from the left
# right(s,n) 	 Get the characters after n bits of the string from the right
select left('woaixuexi',4),right('woaixuexi',4);

6. fill string function

# lpad(s1,len,s2): fill the string s1 to the left through the string s2 to len characters
# rpad(s1,len,s2): fill the string s1 to the right through the string s2 to len characters
mysql> select lpad('woaixuexi',15,'123'),rpad('woaixuexi',15,'123');
+----------------------------+----------------------------+
| lpad('woaixuexi',15,'123') | rpad('woaixuexi',15,'123') |
+----------------------------+----------------------------+
| 123123woaixuexi            | woaixuexi123123            |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

7. function to delete spaces

# ltrim(s): remove the space to the left of string s
# rtrim(s): delete the space to the right of string s
# trim(s): delete spaces around string s
select '   hahaha',ltrim('    hahaha');
select 'hahaha    ' as a,rtrim('hahaha    ') as b,trim('    haha    ') as c;

8.trim(s1 from str): delete the function of the specified string, and delete the contents containing the string s1 on both sides of the string str

select trim('ab' from 'abababhahahahhababababa'),trim('xy' from 'xxyyhahahayyxxxy');

9.repeat(str,n): repeatedly generate the specified character and repeat the string str n times

select repeat('xi',3),repeat('la',4),repeat('he',2);

10.space(n): space function, generating n spaces

select space(3),space(5),space(7);

11.replace(s,s1,s2): a replacement function that replaces all the characters s1 in the string s with the characters s2. It is case sensitive

select replace('yuzuru','u','a'),replace('aa    bb',' ','');

12.strcmp(s1,s2): compare the sizes between strings. S1>s2 returns 1,s1=s2 returns 0, and s1<s2 returns -1

select strcmp('a','A'),strcmp('abc','abd'),strcmp('woaixuexi','anyeyiyang');

13. get substring function

# substring(str,n,len): get the string from the nth bit of the string str, and get len characters. n is a positive number from the left, and n is a negative number from the right
# mid(str,n,len): get the string from the nth bit of the string str, and get len characters. n is a positive number from the left, and n is a negative number from the right
select substring('nihaome',2,4),mid('wohenhaoxiexie',-2,5),mid('bukeqi',-4,2);

14. function matching the start position of the string

# locate(str1,str)
# position(str1 in str)
# instr(str,str1)
mysql> select locate('xue','woaixuexi'),position('fen' in 'luosifenhaochi'),instr('wokezhenlihai','hai');
+---------------------------+-------------------------------------+------------------------------+
| locate('xue','woaixuexi') | position('fen' in 'luosifenhaochi') | instr('wokezhenlihai','hai') |
+---------------------------+-------------------------------------+------------------------------+
|                         5 |                                   6 |                           11 |
+---------------------------+-------------------------------------+------------------------------+

15.reverse(s): reverse the characters in the string s

mysql> select reverse('abcde'),reverse('ahdadg');
+------------------+-------------------+
| reverse('abcde') | reverse('ahdadg') |
+------------------+-------------------+
| edcba            | gdadha            |
+------------------+-------------------+

16.elt(n,s1,s2,s3,s4...): returns the string at the specified position

mysql> select elt(3,'ni','hao','me','?'),elt(1,'wo','bu','hao');
+----------------------------+------------------------+
| elt(3,'ni','hao','me','?') | elt(1,'wo','bu','hao') |
+----------------------------+------------------------+
| me                         | wo                     |
+----------------------------+------------------------+

17.field(s,s1,s2,s3... sn): function that returns the specified string position

mysql> select field('man','wo','ai','kan','dong','man'),field('en','ha','ha');
+-------------------------------------------+-----------------------+
| field('man','wo','ai','kan','dong','man') | field('en','ha','ha') |
+-------------------------------------------+-----------------------+
|                                         5 |                     0 |
+-------------------------------------------+-----------------------+

18.find_in_set(s1,s2): returns the position of string s1 in string set s2

mysql> select find_in_set('xi','wo,ai,xue,xi,null');
+---------------------------------------+
| find_in_set('xi','wo,ai,xue,xi,null') |
+---------------------------------------+
|                                     4 |
+---------------------------------------+

Date function

1. function to get the current date

curdate()
current_date()
select curdate(),current_date();

2. function to obtain the current time

curtime()
current_time()
select curtime(),current_time();

3. function to obtain the current date and time

current_timestamp()
localtime()
now()
sysdate()
select current_timestamp(),localtime(),now(),sysdate();

4.unix_timestamp(): a function to obtain a timestamp. The timestamp is the number of seconds from 1970-01-01 to the time when the timestamp is generated

select unix_timestamp();

5.from_unixtime(): convert timestamp to time in normal format

select from_unixtime('1594709392');

6.utc_date(): function to get UTC date

select utc_date();

7.utc_time(): function to obtain UTC time (the time of prime meridian is obtained)

select utc_time();

8. function for obtaining month
month(date): get the month in the date
monthname(date): gets the month in the date. The English name of the month is displayed

select month('2020-07-14'),monthname('2020-07-14');

9. get week function
dayname(date): returns the English name of the week corresponding to the date
dayofweek(date): returns the index of the week corresponding to the date. 1 means Sunday
weekday(date): returns the index of the working day corresponding to the date. 0 means Monday
weekofyear(date): used to calculate the date. Date is the week of the year

select dayname('2020-07-14');
select dayofweek('2020-07-14');
select weekday('2020-01-14');
select weekofyear('2020-12-31'),weekofyear('2021-12-31');
select weekofyear('2020-07-14');
select weekofyear('2020-12-31'),weekofyear('2024-12-31');

10. function to obtain days
dayofyear(date): returns the day of the year that the specified date is
dayofmonth(date): returns the day of the month on which the specified date is

select dayofyear('2020-07-14'),dayofmonth('2020-07-14');

11. function to obtain year
year(date): get the year in the date

select year('2020-07-14');

12. get the function of quarter
First quarter: 1-3
Second quarter: 4-6
Q3: 7-9
Fourth quarter: 10-12

quarter(date)
select quarter('2020-07-14');

13. function to get minutes
minute(time)

select minute('15:44:30');

14. function to get seconds
second(time)

select second('15:44:33');

15. function to get the specified value in the date
extract(type from date)
When the type is year, it indicates the year in the acquisition date
type is year_ The month time indicates the year and month in the acquisition date
type is day_ The minute time indicates the day, hour and minute in the date

select extract(year from '2020-07-14'),extract(year_month from '2020-07-14'),extract(day_minute from '2020-07-14 15:50:20');

16. time conversion
time_to_sec(time): converts the specified time to seconds
sec_to_time(sec): converts the specified number of seconds to time

select time_to_sec('15:50:30'),sec_to_time('1000');

17. function for calculating date and time
date_add(date,interval expr type): calculates the date. Interval is a fixed keyword. Expr indicates the value of the date. Type indicates the date to calculate. The commonly used values of type are (second,minute,hour,day,month,year)
adddate(date,interval expr type): the effect is the same as that of date_add consistent

select date_add('2020-07-14 15:50:30',interval 5 year),adddate('2020-07-14 15:50:30',interval '5:20' minute_second);

date_sub(date,interval expr type): subtract the specified date
Date (date, interval expr type): subtract the specified date

select date_sub('2020-07-14 15:50:30',interval 5 month),subdate('2020-07-14 15:50:30',interval '3:10' hour_minute);

addtime(time,expr): adds the specified time

select addtime('16:16:30','1'),addtime('16:16:30','1:1'),addtime('16:16:30','1:2:3');

subtime(time,expr): subtracts the specified time

select subtime('16:16:30','2'),subtime('16:16:30','2:2'),subtime('16:16:30','3:4:5');

18.datediff(date1,date2): calculates the number of days between two specified dates

select datediff('2020-07-14','2020-10-01'),datediff('2020-07-14','2021-01-01');
select datediff('2020-10-01','2020-07-14'),datediff('2021-01-01','2020-07-14');

19. format date and time
date_format(date,format): format and output the specified date
time_format(time,format): formats and outputs the specified time

select date_format('2020-07-14','%W,%M,%Y');
select time_format('16:44:30','%H,%k,%i');

Common formatting parameters

%d: Display date, format 00-31
%e: Display date, format 0-31
%f: Microseconds, format 000000-999999
%h: Twelve hour system in two digits, format 01-12
%j: Number of days in a year, format 001-366
%l: Display twelve hour system, format 1-12
%p: It can mean morning or afternoon, morning am,afternoon pm
%r: Time, 12 hour system hh:mm:ss am/pm
%S: Seconds in two digits, format 00-59
%T: Display 24-hour time, format hh:mm:ss
%U: Week, format 00-53,Set the weekend as the first day of the week
%u: Week, format 00-53,Make the week the first day of the week
%V: Week, format 01-53,Set the weekend as the first day of the week
%v: Week, format 01-53,Make the week the first day of the week
%X: The year of the week. Set the weekend as the first day of the week. Format YYYY,and%v Simultaneous use
%x: The year of the week. Position Monday as the first day of the week. Format YYYY,and%V Simultaneous use
%y: Year in two digits
%W: Show workday name
%M: Name of month
%Y: Year in four digits
%H: Two digit system for 24 hours
%i: Minutes, format 00-59
%k: When the hour is less than 10, one digit is used to represent the 24-hour system

val_type: indicates the date type or time type or the value of date and time type, format_type: specify the type of formatting

get_format(val_type,format_type)  
select date_format('2020-07-14',get_format(date,'eur')),time_format('17:01:30',get_format(time,'eur'));
select date_format('2020-07-14 13:33:30',get_format(datetime,'eur'));

Common val_type and format_type

date,eur: %d.%m.%Y
date,usa: %m.%d.%Y
time,eur: %H.%i.%s
time,usa: %H.%i.%s %p
datetime,eur: %Y-%m-%d %H:%i:%s
datetime,usa: %Y-%m-%d %H:%i:%s

Conditional judgment function

1.if(expr,v1,v2): If expression expr The value of is true,Return value v1,If expression expr The value of is false,Return value v2
select if(1+1=2,'Great, you're right','???');

2.ifnull(v1,v2): judge v1 Yes no null,If v1 by null Then return v2,If v1 Return if it is not empty v1
select ifnull(null,'v1 Is empty'),ifnull('v1 The value of is hahaha','v1 Is empty');

3.case expr when v1 then r1 [when v2 then r2] [else rn] end: If expression expr The result of the calculation is equal to vn,Then return vn after then Corresponding value, if there is no matching vn Then return else Corresponding value, if not set else Then return null

case  7+15  when 15 then The result is 15  when 20 then The result is 20  when 22 then The result is 22 else No match to correct calculation result end

select case 7+15 when 15 then 'The result is 15' when 20 then 'The result is 20' when 22 then 'The result is 22' else 'No match to correct calculation result' end;

System information function

1.version(): function to obtain mysql version

select version();

2.connection_id(): function to view the current connection id

select connection_id();

3.show processlist: view the connection information of the current user

show processlist;
+----+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host      | db     | Command | Time | State | Info             |
+----+------+-----------+--------+---------+------+-------+------------------+
|  4 | root | localhost | sjk_03 | Query   |    0 | NULL  | show processlist |
|  5 | root | localhost | NULL   | Sleep   |   85 |       | NULL             |
+----+------+-----------+--------+---------+------+-------+------------------+

id: connect id The value will increase by 1 for each new connection
user: The database through which the client connects
host: The host connected to the database, localhost Indicates a local connection
db: Which database is being accessed by the client, NULL Indicates that you have not switched to the database
command: The type of command the client points to, sleep Indicates sleep, query Represents a query, connect Indicates connection
time: Status duration
state: Show current connection sql Statement, no connection is null
info: What is the currently executed statement

4. view the functions of the currently used database

schema()
database()
select schema(),database();

5. function to view the currently logged in user name

user()
current_user()
system_user()
select user(),current_user(),system_user();

6. view the function of the specified string character set

charset(str)
select charset('hello hello');

7. encryption function

password(str): After encrypting the string, it can be set as the password of the database user
select password('123456');   #Encrypt password
update mysql.user set Password='*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' where User='root' and Host='localhost';   #Modify user user@localhost Password for is 123456
flush privileges;  #Refresh permission table
mysql -u root -p123456  #Log in to the database and verify whether the root user password is successfully modified

md5(str): Computes a string md5128 Bit checksum, encrypts the string into 32-bit hexadecimal string form
select md5('123456');

encode(str1,str2): For string str1 Encryption can be performed by string str2 Decrypt encrypted characters
select encode('balala','I am the key');

8. decryption function
decode(crypt_str,str2): use str2 in the encode function to decrypt the encrypted str1

select decode(encode('balala','I am the key'),'I am the key');

9.last_insert_id(): get the value of the autoincrement field when the table with autoincrement field in the database writes data the last time. Each time the user accesses the database, the value will be reset to 0. When multiple rows of data are written to the table with autoincrement field at one time, only the value of the autoincrement field of the first row of data will be returned. This function is independent for each client connected to the database

select last_insert_id();

10. conversion function between base numbers
conv(num, base 1, base 2)

select conv(1010101,2,8),conv(1234,8,10),conv(123,10,16),conv('AB',16,2);

11.ip address and number conversion function
inet_aton(ip): converts network addresses to numeric values
inet_ntoa(num): convert numeric values to network addresses

select inet_aton('192.168.10.100');
select inet_ntoa(1613134641);

12. lock function and unlock function
get_lock(name,timeout): obtain a lock named name. Timeout is the timeout for obtaining the lock. If the lock is obtained successfully within the timeout time range, 1 is returned. If the lock is timed out, 0 is returned
release_lock(name): Unlock the lock named name
is_used_lock(name): returns the connection ID that is using the lock named name
is_free_lock(name): returns the usage of the lock named name. If the lock is in use, it returns 0. If it is not in use, it returns 1

select get_lock('one',100);
select is_used_lock('one');
select is_free_lock('one');
select release_lock('one');

index

Index can improve the query speed of the database for specific data. It is a data structure stored separately on the disk and contains a reference to a column of data in the database.

unique index

It is allowed to insert a null value into the column of the defined index, but the value cannot be repeated. You can manually create a unique index for the specified field. If a field has a uniqueness constraint, the field will create a unique index by default

# Syntax:
create table Table name (
Field data type [integrity constraint],
.......
unique index Index name(field)
);

# Example:
create table unique_index (
id int,
name char(3),
unique index ui(id)
);

primary key

Special case for unique indexes. If a field has a primary key constraint, the field will create a primary key index by default. It is not allowed to write null values on the fields that define the primary key index, and the values cannot be duplicate

# Syntax:
create table Table name (
Field data type [integrity constraint],
.......
primary key Index name(field)
);

# Example:
create table primary_index (
id int,
name char(4),
primary key pi(id)
);

single column

Create an index on a field, also known as a normal index

# Syntax:
create table Table name (
Field data type [integrity constraint],
.......
index Index name(field)
);

# Example:
create table index_test (
id int,
name char(4),
index i(name)
);

Composite index

Multiple fields combined to create an index

# Syntax:
create table Table name (
Field 1 data type [integrity constraint],
Field 2 data type [integrity constraint],
Field 3 data type [integrity constraint],
key multiidx(Field 1,Field 2...)
);

# Example:
create table multiidx_test (
id int,
name char(4),
age int,
key multiidx(name,age)
);

Full text index

Full text search of values is supported on the defined index columns. Duplicate values and null values are allowed to be inserted into the index defined columns. Full text indexes can only be created on char, varchar and text columns. Moreover, the storage engine of the data table is myisam type. Full text indexes are generally used for text type data search, such as novels and documents

# Syntax:
create table Table name (
Field data type [integrity constraint],
.....
fulltext index Index name(field)
);

# Example:
create table fulltext_index (
id int,
text mediumtext,
fulltext index fi(text)
)engine=myisam;

Spatial index

You can create spatial indexes on fields that represent geographic locations

# Syntax:
create table Table name (
Field data type [integrity constraint],
.....
spatial index Index name(field)
);

# Example:
create table spatial_index (
pos geometry not null,
name char(4),
spatial index si(pos)
)engine=myisam;

Add delete index

Add index to the created table

# grammar
alter table Table name add Index type[Index name](field)
# Example:
alter table sjk_05.students add index n(name);

Delete the specified index

alter table Table name drop index Index name
alter table sjk_05.students drop index n;

Query the index information of the specified data table

mysql> show index from t325.student\G
*************************** 1. row ***************************
        Table: students   	//Table name
   Non_unique: 0    		//Indicates whether the index is unique, 1 indicates no, 0 indicates yes
     Key_name: PRIMARY   	//Index name
 Seq_in_index: 1  		  	//Index serial number
  Column_name: id   		//Field name to create index
    Collation: A    		//In what form are the values of fields stored in the index? A indicates ascending order, and NULL indicates no classification
  Cardinality: 11  			//Estimate of the number of unique values in the index
     Sub_part: NULL  		//Length of index
       Packed: NULL   		//Whether the keyword is compressed. If it is NULL, it means there is no compression
         Null:        		//Indicates whether the index field can be empty. If YES, it can be NULL
   Index_type: BTREE   		//Type of index
      Comment:    			//annotation
Index_comment:    			//annotation

query

MySQL syntax summary


I am not a special DBA, so I hope you can give me more advice on what is wrong! If you are strong enough, you are the rule!

Tags: Database MySQL SQL Back-end DBA

Posted by pohopo on Mon, 30 May 2022 05:52:04 +0530