MySQL查询语句链接地址
MySQL是什么？
成为MySQL大神的基础~
数据类型
数值：整数，浮点数(小数)
日期：年，年月日，时分秒，年月日时分秒
字符串：文本类型字符串，二进制类型字符串
整形
| 类型 | 存储需求 | 有符号取值范围 | 无符号取值范围 |
|---|---|---|---|
| tinyint | 1字节 | -128~127 | 0~255 |
| Smallint | 2字节 | -32768~32767 | 0~65535 0~2^16-1 |
| Mediumint | 3字节 | -8399608~8399607 | 0~2^24-1 |
| int | 4字节 | -2147483648~2147483647 | 0~2^32-1 |
| Bigint | 8字节 | -263~263-1 | 0~2^64-1 |
有符号数和无符号数的区别:
(1)有符号数是可以用来区分数值的正负。而无符号数仅有正值,没有负值。
(2)其次当一个数是无符号数时,它的最高位仅用来表示该数的大小。而当一个数是有符号数时,此时的最高位称为符号位。该符号位为1时表示该数为负值，为0时则表示为正值。
(3)最后有符号数和无符号数两者表示的范围不同，即同样长度的字节,有符号数比无符号数的最大值出现缩水(最左一位用于表示符号)。
例:
create table num (
a tinyint,
b smallint,
c mediumint,
d int,
e bigint
);
int(2)中(2)表示显示宽度,也就是显示几位数,但是并不严格。即使写入的数值超过了这个宽度只要值不超过数据类型的取值范围就可以正常写入和显示
mysql> create table num_1 (id int(2)); #针对整数不严格
mysql> create table num_2 (name char(2)) #针对字符串严格
mysql> insert num_2 values ('qqq');
ERROR 1406 (22001): Data too long for column 'name' at row 1
创建带有无符号整数数据类型的表（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
浮点数float
float(x,y)：单精度浮点数,占4字节存储，x为精度表示整数和小数总的位数,y为标度表示可以有几位小数x的有效位是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舍,6入（标度的范围可以不限,但显示的内容只是"标度"的位数）
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(x,y)：双精度浮点数,占8字节存储，x的有效位为16
decimal(小数)
decimal(x,y)：定点数,占16字节存储，x的有效位最多为65位,y也就是小数部分最多为30位.
如果没有手动指定“定点数”的精度和标度,那么默认为decimal(10,0)，该数据类型可用于银行账目计算!
日期类型
year
格式一：以四位字符串格式表示, 范围’1901’-‘2155’
格式二：以四位数字格式表示, 范围1901-2155
格式三：以两位字符串格式表示, 范围’00’-‘99’
‘00’-‘69’表示’2000’-‘2069’
‘70’-‘99’表示’1970’-‘1999’
格式四：以两位数字表示, 范围00-99
00-69表示2000-2069
70-99表示1970-1999
例:
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
格式一：‘HH:MM:SS’，HH表示小时,MM表示分钟,SS表示秒钟
格式二：‘HHMMSS’
格式三：‘D HH:MM:SS’，D表示天数,会计算成对应的小时与HH的值相加【D取值范围为-34~34】
时间的取值范围为-838:59:59-838:59:59（-34*24+24=839）
例:
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
格式一：‘YYYY-MM-DD’，YYYY表示年,MM表示月份,DD表示天，取值范围’1000-01-01~9999-12-03’
格式二：‘YY-MM-DD’，YY表示年,MM表示月,DD表示天，YY取值范围’00’-‘99’，‘00’-‘69’表示’2000’-‘2069’,‘70’-‘99’表示’1970’-‘1999’
格式三：YYMMDD，‘YYMMDD’
例:
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
格式一：‘YYYY-MM-DD HH:MM:SS’，取值范围：‘1010-01-01 00:00:00’~‘9999-12:31 99:99:99’
格式二：‘YY-MM-DD HH:MM:SS’， YY取值范围’00’-‘99’，‘00’-‘69’表示’2000’-‘2069’,‘70’-‘99’表示’1970’-‘1999’
格式三：‘YYYYMMDDHHMMSS’，‘YYMMDDHHMMSS’
格式四：‘YYYY/MM/DD HH/MM/SS’
例:
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
格式：‘YYYY-MM-DD HH-MM-SS’，占4字节,取值范围’1970-01-01 08:00:01 UTC’~‘2038-01-19 03:14:07 UTC’(没有范围)
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)
字符串类型
文本类型字符串：受字符集限制。对于uft8字符集来说：数字,字母,符号都占1字节，汉字占3字节、unicode字符集定义字符都占两字节
char(M)：固定长度文本字符串，M表示显示宽度,表示最多可以显示M个字符,M的取值范围是0-255。如果写入的字符个数小于M的值,那么会以空格进行填充至M的数量,查询时会把填充的空格删除在显示，如果写入的字符个数大于M,那么多出的部分会被截取
create table test12 (a char(3));
insert into test12 values ('a'),('abc'),('abcd'),('哈哈'),('哈哈哈'),('哈?哈？？'); #将插入数据失败
varchar(M)：不定长度文本字符串，M是显示宽度,表示最多可以显示M个字符,M取值范围是1-21844。如果写入的字符个数小于M那么不会进行空格的补充，如果写入的字符个数大于M那么多出的字符会被截取,或者报错。varchar会额外占用1~2字节的大小去记录写入的实际字符长度，字符个数<=255,使用1字节,否则使用2字节
enum：枚举,是一个字符串对象【在给定的范围内选择一个值】
格式:
字段 enum (值1,值2,值3…值n) 如果值后面有空格,会把空格删除，最多可以有65535个值。把值称为元素,每个值都有自己的编号。编号从1开始，写入数据时选择不存的编号会导致字段内容为空
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
| 类型 | 支持存储的大小 |
|---|---|
| tinytext | 最大支持255字节(2^8-1) |
| text | 最大支持65535字节(2^16-1) |
| mediumtext | 最大支持2^24-1字节 |
| longtext | 最大支持2^32-1字节 |
set
集合,是一个字符串对象，可以在给定的范围中选取零个或多个值
格式:
字段 set (值1,值2,值3…值n) 值最大可以有64个，在写入数据时可以选择多个值写入，指定的值的顺序会按照设置的顺序自动排列。如果一个值选了多次,那么会自动去重，如果写入了不存在的值,会把不存在的值删除/直接报错
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)
二进制字符串
属于字节流,不属于字符集的限制，可以指定可写入的字节大小,一般用于存储图片.视频.音频等媒体数据
bit
bit(M)：位,M指的是字段可以存储位的大小，M的取值范围是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)：固定长度二进制字符串，M表示可以存储的位数,M的取值范围是0-255。如果写入的数据小于M的值,那么会以\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)：不定长度二进制字符串，M表示可以存储字节的大小,M的取值范围0-65535(大概到65532)。如果写入的数据大小小于M的值,不会进行补充,会额外占用1-2字节存储写入数据实际的大小
mysql> create table var_1 (a varbinary(7));
mysql> insert var_1 values(1111111),('23333'),('哈哈1');
mysql> insert var_1 values(1111111),('23333'),('哈哈111');
ERROR 1406 (22001): Data too long for column 'a' at row 3
mysql> select * from var_1;
+---------+
| a |
+---------+
| 1111111 |
| 23333 |
| 哈哈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类型的字段用于存储二进制数据
| 类型 | 支持的存储大小 |
|---|---|
| tinyblob | 2^8字节 |
| blob | 2^16字节 |
| mediumblob | 2^24字节 |
| longblob | 2^32字节 |
将选好的照片移动到数据库的数据目录下
cp 1.jpg /usr/local/mysql/data/
# 修改下权限让你的MySQL用户能对照片有一个权限
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 加载的意思
数据库一般不会直接存照片，而是存名字和路径
图片存放在文件系统中。少量可以,大量的话不会往数据库里存,读取太麻烦了！select * from test_image1(有的数据库会显示NULL,Mariadb的数据库目录为/var/lib/mysql)
运算符
算术运算符
+：加法运算(最快)
-：减法运算
*：乘法运算
/：除法运算
%：取余
比较运算符
= 等于
<=> 安全等于
<>,!= 不等于
> 大于
>= 大于等于
< 小于
<= 小于等于
1.=：等于运算符，用来判断数值.字符串和表达式是否相等
如果相等则返回1，如果不等则返回0，如果比较的值为NULL则返回NULL(任何值=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.<=>：安全等于运算符，用来判断数值.字符串和表达式是否相等
可以对空值进行比较，如果相等则返回1，如果不等则返回0。（任何值<=>NULL都是真实的比较，“相等就是相等,不相等就是不相等”）
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)
判断运算符
is null：判断一个值是否为空，如果值为空返回1，不为空返回0
isnull： 判断一个值是否为空，如果值为空返回1，不为空返回0
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：判断一个值是否不为空，如果值为空则返回0，不为空返回1
select NULL is not null,5 is not null,'haha' is not null;
between and：判断一个值是否在给定范围中
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)
最大/小值
least：当有两个或多个数据时，返回当中的最小值，如果有NULL则返回NULL
select least(1,2,3,4,5),least('a','b','d','c'),least('a','A'),least('a',NULL);
greatest：当有两个或多个数据时，返回当中的最大值，如果有NULL则返回NULL
select greatest(1,2,3,4,5),greatest('a','b','d','c'),greatest('a','A'),greatest('a',NULL);
in
in：判断一个值是否在指定的列表中
not in：判断一个值是否不再指定的列表中
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：用来匹配字符串。符号_用于匹配任意一个字符,符号%用于匹配任意多个字符。
如果字符匹配则返回1，如果字符不匹配则返回0
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：正则表达式匹配,用来匹配字符串，如果匹配则返回1，不匹配返回0
^：匹配以..开头的字符串
$：匹配以..结尾的字符串
.：表示任意一个字符
*：表示重复前面的字符任意次
.*：表示任意多个字符
[]：用于匹配括号内的任意字符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)
逻辑运算符
not,!： 逻辑非运算符
and,&&： 逻辑与运算符
or,||： 逻辑或运算符
xor： 逻辑异或运算符
1.(not !) 当操作数为0时返回1，当操作数是不为0的数时返回0，当操作数为NULL时返回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) 当所有的操作数为非零值且不为空时返回1，任意操作数为0返回0，
当操作数都不为零但任意操作数为NULL返回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) 当所有的操作数都为0时返回0,当操作数为0和NULL或者全为NULL时返回NULL，
当任一操作数不为0且不为空返回1
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) 当任意一个操作数为NULL返回NULL，对于非NULL的操作数:
如果两个操作数都为0或都不为0则返回0，如果一个操作数为0一个操作数不为0则返回1
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)
位运算符
|：位或运算符 # (有1则1,全0则0)
&：位与运算符 # (全1则1,有0则0)
^：位异或运算符 # (相同则0,不同则1)
<<：位左移运算符
>>：位右移运算符
~：位取反运算符|:对应的二进制有一个或两个为1,则为1，否则为0
mysql> select 15 | 10,6 | 9 | 12; # 11为1，10为1，所以15|10答案是15
+---------+------------+
| 15 | 10 | 6 | 9 | 12 |
+---------+------------+
| 15 | 15 |
+---------+------------+
1 row in set (0.00 sec)&:对应的二进制都为1则为1，否则为0
mysql> select 21 & 13,7 & 10 & 13;
+---------+-------------+
| 21 & 13 | 7 & 10 & 13 |
+---------+-------------+
| 5 | 0 |
+---------+-------------+
1 row in set (0.00 sec)^:对应的二进制相同时返回0，不同时返回1
mysql> select 10 ^ 20,5 ^ 15 ^ 25;
+---------+-------------+
| 10 ^ 20 | 5 ^ 15 ^ 25 |
+---------+-------------+
| 30 | 19 |
+---------+-------------+
1 row in set (0.00 sec)<<:使指定的二进制数向`高位`移动指定的位数，移动完成之后`低位`空出的位置用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)>>:使指定的二进制数向`低位`移动指定的位数,移动完成之后`低位`将被移除，左边高位空出来的用0补齐
mysql> select 5>>2,6>>4;
+------+------+
| 5>>2 | 6>>4 |
+------+------+
| 1 | 0 |
+------+------+
1 row in set (0.00 sec)~:将对应的二进制逐位反转，1变为0,0变为1，需要和&搭配使用
mysql> select ~1024&5,~5%8,~1;
+---------+------+----------------------+
| ~1024&5 | ~5%8 | ~1 |
+---------+------+----------------------+
| 5 | 2 | 18446744073709551614 |
+---------+------+----------------------+
1 row in set (0.01 sec)
优先级
运算符的优先级(从低到高排)
=：赋值运算符
|| or
xor
&& and
! not
between and
=,<=>,>=,>,<=,<,<>,!=,like,in,regexp
|
&
<<,>>
+,-
*,/,%
-表示负号
()
函数
数学函数
abs (x)
求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)
求圆周率
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
sqrt (x)
求x的平方根。负数没有平方根,会返回NULL
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)
求余函数,x被y除了以后的函数
mysql> select mod(25,2),mod(33,5);
+-----------+-----------+
| mod(25,2) | mod(33,5) |
+-----------+-----------+
| 1 | 3 |
+-----------+-----------+
1 row in set (0.00 sec)
获取整数
ceil(x) 获取的值不小于且最接近与原来的数的整数
ceiling(x) 获取的值不小于且最接近与原来的数的整数
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) 获取的值不大于且最接近与原来的数的整数
select floor(1.111),floor(-1.111),floor(1.666);
获取随机数
rand() 获得的随机数的范围在0-1之间，每次执行得到的就跟都不一样
rand(x) 根据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)
四舍五入
round(x) 对x进行四舍五入，保留整数，不保留小数
round(x,y) 对x进行四舍五入，y是指从第几位数开始，y为正表示小数点之后,y为负表示小数点之前
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)
截取数值
truncate(x,y) 对x进行截取，y为正数表示对小数进行截取并保留几位小数,y为负数表示将小数完全截取并将整数从个位数进行取0，取y位
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)
符号
sign(x)：符号函数，x为正数返回1，x为0返回0，x为负返回-1，x为NULL返回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)
幂运算
pow(x,y) 求x的y次方
power(x,y) 求x的y次方
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)：求e的y方，e取值约为2.71828
select exp(3);
对数运算
求指数
# log()： 以e为底
select log(20),log(30);# log10()：以10为底
mysql> select log10(1000),log10(100);
+-------------+------------+
| log10(1000) | log10(100) |
+-------------+------------+
| 3 | 2 |
+-------------+------------+
1 row in set (0.00 sec)
角度弧度互相转换
# radians(x)：将角度x转换为对应的弧度(1π=180°)
select radians(180),radians(360),radians(90);# degrees(x)：将弧度x转换为对应的角度
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)
正弦与反正弦
sin=对边比斜边
# sin(x)： 求弧度为x(弧度可以转换为角度)的正弦值
select sin(pi()/6),sin(pi()/4);# asin(x)：求正弦值x对应的弧度(弧度可以转换为角度)
mysql> select degrees(asin(0.5));
+--------------------+
| degrees(asin(0.5)) |
+--------------------+
| 30.000000000000004 |
+--------------------+
1 row in set (0.00 sec)
余弦和反余弦
cosA=邻边比斜边
# cos(x)： 求弧度为x的余弦值
select cos(pi()/6),cos(pi()/3);# acos(x)：求余弦值x对应的弧度
select degrees(acos(0.5));
正切，反正切和余切
tanA=对边比邻边
# tan(x)：求弧度为x的正切值
select tan(pi()/6),tan(pi()/4);# atan(x)：求正切值x对应的弧度
select degrees(atan(1));# cot(x)：求弧度为x的余切值
select cot(pi()/6),cot(pi()/3);
字符串函数
1.计算字符长度的函数
# char_length(str)：统计字符串的字符个数,空格也算一个字符
select char_length('wo ai xue '),char_length('yu sheng jie xian zhen ku');# length(str)： 通过字符串的字节大小,一个空格一字节
select length('我爱学习sjk hahaha'),length('羽生结弦真酷呀~~~');
2.合并字符串函数
# concat(s1,s2.....)：值有null则返回null
select concat('mysql-bin','.000001');
select concat('ha','ha',null);# concat_ws(x,s1,s2)：使用x作为分隔符合并字符串，如果分隔符为null则返回null
select concat_ws('@','12346','qq.com');
3.替换字符串的函数
insert(s1,x,len,s2)：从字符串s1的x位开始,使用字符串s2进行替换，替换len个字符
mysql> select insert('woaixuexi',2,-5,'123');
+--------------------------------+
| insert('woaixuexi',2,-5,'123') |
+--------------------------------+
| w123 |
+--------------------------------+
1 row in set (0.00 sec)
4.大小写转换函数
# lower(str)：大写转换成小写
# lcase(str)：大写转换成小写
select lower('SJK'),lcase('Sjk');# upper(str)：小写转换为大写
# ucase(str)：小写转换为大写
select upper('sjk'),ucase('sJk');
5.获取指定长度的字符串
# left(s,n) 从左开始获取字符串n位之后的字符
# right(s,n) 从右开始获取字符串n位之后的字符
select left('woaixuexi',4),right('woaixuexi',4);
6.填充字符串函数
# lpad(s1,len,s2)：将字符串s1通过字符串s2向左填充至len个字符
# rpad(s1,len,s2)：将字符串s1通过字符串s2向右填充至len个字符
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.删除空格的函数
# ltrim(s)：删除字符串s左边的空格
# rtrim(s)：删除字符串s右边的空格
# trim(s)： 删除字符串s两边的空格
select ' hahaha',ltrim(' hahaha');
select 'hahaha ' as a,rtrim('hahaha ') as b,trim(' haha ') as c;
8.trim(s1 from str)：删除指定字符串的函数，删除字符串str两边含有字符串s1的内容
select trim('ab' from 'abababhahahahhababababa'),trim('xy' from 'xxyyhahahayyxxxy');
9.repeat(str,n)：重复生成指定的字符，将字符串str重复n次
select repeat('xi',3),repeat('la',4),repeat('he',2);
10.space(n)：空格函数，生成n个空格
select space(3),space(5),space(7);
11.replace(s,s1,s2)：替换函数，将字符串s中所有的字符s1替换成字符s2，区分大小写
select replace('yuzuru','u','a'),replace('aa bb',' ','');
12.strcmp(s1,s2)：比较字符串之间的大小，s1>s2返回1,s1=s2返回0,s1<s2返回-1
select strcmp('a','A'),strcmp('abc','abd'),strcmp('woaixuexi','anyeyiyang');
13.获取子字符串函数
# substring(str,n,len)：对字符串str的第n位开始获取字符串，获取len个字符。n为正表示从左开始数，n为负表示从右开始数
# mid(str,n,len)：对字符串str的第n位开始获取字符串，获取len个字符。n为正表示从左开始数，n为负表示从右开始数
select substring('nihaome',2,4),mid('wohenhaoxiexie',-2,5),mid('bukeqi',-4,2);
14.匹配字符串开始位置的函数
# 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)：对字符串s中的字符进行反转
mysql> select reverse('abcde'),reverse('ahdadg');
+------------------+-------------------+
| reverse('abcde') | reverse('ahdadg') |
+------------------+-------------------+
| edcba | gdadha |
+------------------+-------------------+
16.elt(n,s1,s2,s3,s4…)：返回指定位置的字符串
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)：返回指定字符串位置的函数
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)：返回字符串s1在字符串集合s2中的位置
mysql> select find_in_set('xi','wo,ai,xue,xi,null');
+---------------------------------------+
| find_in_set('xi','wo,ai,xue,xi,null') |
+---------------------------------------+
| 4 |
+---------------------------------------+
日期函数
1.获取当前日期的函数
curdate()
current_date()
select curdate(),current_date();
2.获取当前时间的函数
curtime()
current_time()
select curtime(),current_time();
3.获取当前日期与时间的函数
current_timestamp()
localtime()
now()
sysdate()
select current_timestamp(),localtime(),now(),sysdate();
4.unix_timestamp()：获取时间戳的函数，时间戳是从1970-01-01整点开始到生成时间戳之间的秒数
select unix_timestamp();
5.from_unixtime()：将时间戳转化为普通格式的时间
select from_unixtime('1594709392');
6.utc_date()：获取utc日期的函数
select utc_date();
7.utc_time()：获取utc时间的函数(获取的是本初子午线的时间)
select utc_time();
8.获取月份的函数
month(date)：获取日期中的月份
monthname(date)：获取日期中的月份，显示的是月份的英文名
select month('2020-07-14'),monthname('2020-07-14');
9.获取星期的函数
dayname(date)：返回日期date对应的星期的英文名
dayofweek(date)：返回日期date对应的一周的索引，1表示周日
weekday(date)：返回日期date对应的工作日的索引，0表示周一
weekofyear(date)：用于计算日期date是一年中的第几周
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.获取天数的函数
dayofyear(date)：返回指定的日期是一年中的第几天
dayofmonth(date)：返回指定的日期是一个月中的第几天
select dayofyear('2020-07-14'),dayofmonth('2020-07-14');
11.获取年份的函数
year(date)：获取日期中的年份
select year('2020-07-14');
12.获取季度的函数
第一季度：1-3
第二季度：4-6
第三季度：7-9
第四季度：10-12
quarter(date)
select quarter('2020-07-14');
13.获取分钟的函数
minute(time)
select minute('15:44:30');
14.获取秒钟的函数
second(time)
select second('15:44:33');
15.获取日期中指定值的函数
extract(type from date)
type为year时表示获取日期中的年份
type为year_month时表示获取日期中的年，月
type为day_minute时表示获取日期中的天，小时，分钟
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_to_sec(time)：将指定的时间转换为秒
sec_to_time(sec)：将指定的秒数转换为时间
select time_to_sec('15:50:30'),sec_to_time('1000');
17.计算日期与时间的函数
date_add(date,interval expr type)：对日期date进行计算，interval是固定关键字，expr表示对日期计算的值，type表示对什么日期进行计算，type常用的值有(second,minute,hour,day,month,year)
adddate(date,interval expr type)：效果同date_add一致
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)：对指定的日期进行相减
subdate(date,interval expr type)： 对指定的日期进行相减
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)：对指定的时间进行增加
select addtime('16:16:30','1'),addtime('16:16:30','1:1'),addtime('16:16:30','1:2:3');
subtime(time,expr)：对指定的时间进行相减
select subtime('16:16:30','2'),subtime('16:16:30','2:2'),subtime('16:16:30','3:4:5');
18.datediff(date1,date2)：计算指定的两个日期之间间隔的天数
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.对日期和时间格式化
date_format(date,format)：对指定的日期进行格式化输出
time_format(time,format)：对指定的时间进行格式化输出
select date_format('2020-07-14','%W,%M,%Y');
select time_format('16:44:30','%H,%k,%i');
常用的格式化参数
%d：显示日期，格式00-31
%e：显示日期，格式0-31
%f：微秒，格式000000-999999
%h：以两位数表示十二小时制，格式01-12
%j：一年中的天数，格式001-366
%l：显示十二小时制，格式1-12
%p：可以表示上午或者下午，上午am，下午pm
%r：时间，12小时制 hh:mm:ss am/pm
%S：以两位数表示秒，格式00-59
%T：显示二十四小时制的时间，格式hh:mm:ss
%U：周，格式00-53，把周末定为每周的第一天
%u：周，格式00-53，把周一定为每周的第一天
%V：周，格式01-53，把周末定为每周的第一天
%v：周，格式01-53，把周一定为每周的第一天
%X：周的年份，把周末定为每周的第一天，格式YYYY，和%v同时使用
%x：周的年份，把周一定位每周的第一天，格式YYYY，和%V同时使用
%y：以两位数表示年份
%W：显示工作日的名字
%M：月份的名称
%Y：以四位数表示年份
%H：用两位数表示二十四小时制
%i：分钟，格式00-59
%k：当小时不满10时使用一位数表示二十四小时制
val_type：表示日期类型或者时间类型或者日期与时间类型的值，format_type:指定格式化的类型
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'));
常用的val_type和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
条件判断函数
1.if(expr,v1,v2)：如果表达式expr的值为true，则返回值v1，如果表达式expr的值为false，则返回值v2
select if(1+1=2,'太棒了，你算对了','???');2.ifnull(v1,v2)：判断v1是否为null，如果v1为null则返回v2,如果v1不为空则返回v1
select ifnull(null,'v1是空的'),ifnull('v1的值是哈哈哈','v1是空的');3.case expr when v1 then r1 [when v2 then r2] [else rn] end：如果表达式expr计算的结果等于vn，则返回vn后then对应的值，如果没有匹配的vn则返回else对应的值，如果没有设置else则返回nullcase 7+15 when 15 then 计算结果是15 when 20 then 计算结果是20 when 22 then 计算结果是22 else 没有匹配到正确的计算结果 endselect case 7+15 when 15 then '计算结果是15' when 20 then '计算结果是20' when 22 then '计算结果是22' else '没有匹配到正确的计算结果' end;
系统信息函数
1.version()：获取mysql版本的函数
select version();
2.connection_id()：查看当前连接数id的函数
select connection_id();
3.show processlist：查看当前用户的连接信息
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：连接id数，每增加一个新连接该值就会增加1
user：客户端通过哪个用户连接的数据库
host：连接数据库的主机，localhost表示的是本地连接
db：客户端正在访问哪个数据库，NULL表示没有切换到数据库中
command：客户端指向的命令类型，sleep表示休眠，query表示查询，connect表示连接
time：状态持续时间
state：显示当前连接sql语句的状态，没有连接为null
info：当前执行的语句是什么
4.查看当前使用的数据库的函数
schema()
database()
select schema(),database();
5.查看当前登录的用户名的函数
user()
current_user()
system_user()
select user(),current_user(),system_user();
6.查看指定字符串字符集的函数
charset(str)
select charset('hello hello');
7.加密函数
password(str)：对字符串进行加密之后可以设置为数据库用户的密码
select password('123456'); #对密码进行加密
update mysql.user set Password='*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' where User='root' and Host='localhost'; #修改用户user@localhost的密码为123456
flush privileges; #刷新权限表
mysql -u root -p123456 #登录数据库验证root用户密码是否修改成功md5(str)：将字符串计算出一个md5128位校验和，把字符串加密成32位16进制的字符串形式
select md5('123456');encode(str1,str2)：对字符串str1进行加密，可以通过字符串str2对加密字符解密
select encode('balala','我是钥匙');
8.解密函数
decode(crypt_str,str2)：使用encode函数中str2对加密后的str1解密
select decode(encode('balala','我是钥匙'),'我是钥匙');
9.last_insert_id()：获取数据库中带有自增字段的表最近一次写入数据时，自增字段的值，用户每次访问数据库时该值会重置为0，当一次性向带有自增字段的表写入多行数据时，只会返回第一行数据自增字段的值，该函数对于每一个连接到数据库的客户端来说都是独立的
select last_insert_id();
10.进制之间互相转换的函数
conv(num,进制1,进制2)
select conv(1010101,2,8),conv(1234,8,10),conv(123,10,16),conv('AB',16,2);
11.ip地址与数字互相转换的函数
inet_aton(ip)：把网络地址转换成数值
inet_ntoa(num)：把数值转换成网络地址
select inet_aton('192.168.10.100');
select inet_ntoa(1613134641);
12.加锁函数和解锁函数
get_lock(name,timeout)：获取一把名字为name的锁，timeout为获取锁的超时时间。如果在timeout时间范围内获取锁成功,则返回1，如果超时则返回0
release_lock(name)：对名字为name的锁进行解锁
is_used_lock(name)：返回正在使用名字为name的锁的连接ID
is_free_lock(name)：返回名字为name的锁的使用情况，如果锁正在使用则返回0，如果没有被使用则返回1
select get_lock('one',100);
select is_used_lock('one');
select is_free_lock('one');
select release_lock('one');
索引
使用索引可以提高数据库对特定数据的查询速度，是单独存储在磁盘上的数据结构，包含对数据库中某一列数据的引用。
唯一索引
允许在定义的索引的列上插入空值,但是值不能重复,可以手动给指定的字段创建唯一索引，如果一个字段带有唯一性约束,那么这个字段会默认创建唯一索引
# 语法:
create table 表名 (
字段 数据类型 [完整性约束]，
.......
unique index 索引名(字段)
);# 例：
create table unique_index (
id int,
name char(3),
unique index ui(id)
);
主键索引
唯一索引的特殊情况。如果一个字段带有主键约束那么这个字段会默认创建主键索引，在定义主键索引的字段上不允许写入空值,值也不能重复
# 语法:
create table 表名 (
字段 数据类型 [完整性约束]，
.......
primary key 索引名(字段)
);# 例：
create table primary_index (
id int,
name char(4),
primary key pi(id)
);
单列索引
对某一字段创建索引，也称之为普通索引
# 语法:
create table 表名 (
字段 数据类型 [完整性约束]，
.......
index 索引名(字段)
);# 例：
create table index_test (
id int,
name char(4),
index i(name)
);
组合索引
多个字段组合在一起创建一个索引
# 语法:
create table 表名 (
字段1 数据类型 [完整性约束],
字段2 数据类型 [完整性约束],
字段3 数据类型 [完整性约束],
key multiidx(字段1,字段2...)
);# 例：
create table multiidx_test (
id int,
name char(4),
age int,
key multiidx(name,age)
);
全文索引
在定义的索引列上支持值的全文查找，允许在索引定义的列上插入重复值和空值，只能对char，varchar，text这三种类型的列上创建全文索引，而且数据表的存储引擎为myisam类型，全文索引一般用于文本类型的数据查找，比如小说，文档等
# 语法:
create table 表名 (
字段 数据类型 [完整性约束],
.....
fulltext index 索引名(字段)
);# 例：
create table fulltext_index (
id int,
text mediumtext,
fulltext index fi(text)
)engine=myisam;
空间索引
对用来表示地理位置的字段可以创建空间索引
# 语法:
create table 表名 (
字段 数据类型 [完整性约束],
.....
spatial index 索引名(字段)
);# 例：
create table spatial_index (
pos geometry not null,
name char(4),
spatial index si(pos)
)engine=myisam;
添加删除索引
对已经创建好的表添加索引
# 语法
alter table 表名 add 索引类型[索引名](字段)
# 例：
alter table sjk_05.students add index n(name);
删除指定的索引
alter table 表名 drop index 索引名
alter table sjk_05.students drop index n;
查询指定数据表的索引信息
mysql> show index from t325.student\G
*************************** 1. row ***************************Table: students //表名Non_unique: 0 //表示该索引是否是唯一索引，1表示不是，0表示是Key_name: PRIMARY //索引名称Seq_in_index: 1 //索引序列号Column_name: id //创建索引的字段名Collation: A //字段的值以什么形式存储在索引中，A表示升序，NULL表示没有分类Cardinality: 11 //索引中唯一值的数目的估值Sub_part: NULL //索引的长度Packed: NULL //是否对关键字进行了压缩，如果为NULL就表示没有压缩Null: //表示索引字段是否可以为空，如果值为YES表示可以为NULLIndex_type: BTREE //索引的类型Comment: //注释
Index_comment: //注释
查询
MySQL语法总结
本人不是专门的DBA，故有哪里不好的地方还希望多多指教！ If you are strong enough, you are the rule!
