操作符优先级（从高到低，同一行优先级相同）：
INERVAL
VINARY,COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*,/,DIV,%,MOD
-,+
<<,>>
&
|
=(COMARISON),<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
between,case,when,then,else
not
and,&&
xor
or,||
=(assignment),:=
lower函数使用：
mysql> select lower('AbcDR');
+----------------+
| lower('AbcDR') |
+----------------+
| abcdr |
+----------------+
1 row in set (0.04 sec)
greatest函数使用：
mysql> select greatest(1,5,10,20);
+---------------------+
| greatest(1,5,10,20) |
+---------------------+
| 20 |
+---------------------+
1 row in set (0.03 sec
变量赋值：
mysql> set @=1024
-> ;
ERROR 3061 (42000): User variable name '' is illegal
mysql> set @a=1024;
Query OK, 0 rows affected (0.00 sec)
mysql> select @a;
+------+
| @a |
+------+
| 1024 |
+------+
1 row in set (0.00 sec)
case使用方法：
mysql> select * from students;
+----------+--------+---------+--------+------+
| sname | gender | dept_id | master | sid |
+----------+--------+---------+--------+------+
| narisu | man | 1 | NULL | NULL |
| gutianle | man | 2 | NULL | NULL |
| bbb | feman | 1 | NULL | NULL |
| ccc | feman | 2 | NULL | NULL |
| night | night | 1 | NULL | NULL |
+----------+--------+---------+--------+------+
5 rows in set (0.00 sec)
mysql> select sid,sname,case gender when 1 then 'male' else 'female' end gender,dept_id from students;
+------+----------+--------+---------+
| sid | sname | gender | dept_id |
+------+----------+--------+---------+
| NULL | narisu | female | 1 |
| NULL | gutianle | female | 2 |
| NULL | bbb | female | 1 |
| NULL | ccc | female | 2 |
| NULL | night | female | 1 |
+------+----------+--------+---------+
5 rows in set, 5 warnings (0.02 sec)
mysql> select sid,sname,case when gender <=1 then 'male' when gender>='2' then 'female' end gender,dept_id from students;
+------+----------+--------+---------+
| sid | sname | gender | dept_id |
+------+----------+--------+---------+
| NULL | narisu | male | 1 |
| NULL | gutianle | male | 2 |
| NULL | bbb | male | 1 |
| NULL | ccc | male | 2 |
| NULL | night | male | 1 |
+------+----------+--------+---------+
5 rows in set, 5 warnings (0.00 sec)
if/ifnull流程控制使用
mysql> select if(1>2,'true','false');
+------------------------+
| if(1>2,'true','false') |
+------------------------+
| false |
+------------------------+
1 row in set (0.00 sec)
mysql> select * from students;
+----------+--------+---------+--------+------+
| sname | gender | dept_id | master | sid |
+----------+--------+---------+--------+------+
| narisu | 1 | 1 | NULL | NULL |
| gutianle | 1 | 2 | NULL | NULL |
| bbb | 2 | 1 | NULL | NULL |
| ccc | 2 | 2 | NULL | NULL |
| night | 3 | 1 | NULL | NULL |
+----------+--------+---------+--------+------+
5 rows in set (0.00 sec)mysql> select gender,if(gender=1,'male','female') from students;
+--------+------------------------------+
| gender | if(gender=1,'male','female') |
+--------+------------------------------+
| 1 | male |
| 1 | male |
| 2 | female |
| 2 | female |
| 3 | female |
+--------+------------------------------+
5 rows in set (0.00 sec)
mysql> select * from students;
+----------+--------+---------+--------+------+
| sname | gender | dept_id | master | sid |
+----------+--------+---------+--------+------+
| narisu | 1 | 1 | NULL | NULL |
| gutianle | 1 | 2 | NULL | NULL |
| bbb | 2 | 1 | NULL | NULL |
| ccc | 2 | 2 | NULL | NULL |
| night | NULL | 1 | NULL | NULL |
+----------+--------+---------+--------+------+
5 rows in set (0.02 sec)
mysql>
mysql>
mysql> select sname,gender,ifnull(gender,'unkown') from students;
+----------+--------+-------------------------+
| sname | gender | ifnull(gender,'unkown') |
+----------+--------+-------------------------+
| narisu | 1 | 1 |
| gutianle | 1 | 1 |
| bbb | 2 | 2 |
| ccc | 2 | 2 |
| night | NULL | unkown |
+----------+--------+-------------------------+
5 rows in set (0.02 sec)
concat使用方式：
遇到NULL时返回NULL
mysql> select concat('My','s','ql');
+-----------------------+
| concat('My','s','ql') |
+-----------------------+
| Mysql |
+-----------------------+
1 row in set (0.00 sec)
mysql> select concat('My',null,'ql');
+------------------------+
| concat('My',null,'ql') |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select concat(14.3);
+--------------+
| concat(14.3) |
+--------------+
| 14.3 |
+--------------+
1 row in set (0.03 sec
mysql> select concat ('dept_id:',dept_id,'@@','sname:',sname) from students;
+-------------------------------------------------+
| concat ('dept_id:',dept_id,'@@','sname:',sname) |
+-------------------------------------------------+
| dept_id:1@@sname:narisu |
| dept_id:2@@sname:gutianle |
| dept_id:1@@sname:bbb |
| dept_id:2@@sname:ccc |
| dept_id:1@@sname:night |
+-------------------------------------------------+
5 rows in set (0.01 sec)
trim函数使用方法：
mysql> select trim(' abc ');
+--------------------+
| trim(' abc ') |
+--------------------+
| abc |
+--------------------+
1 row in set (0.00 sec)
substring函数使用：
mysql> select substring('1234567',4,5); ##从第几位开始后面几位是想要的
+--------------------------+
| substring('1234567',4,5) |
+--------------------------+
| 4567 |
+--------------------------+
1 row in set (0.00 sec)
like _ \
mysql> select * from students where sname like 'a_%';
+-----------+--------+---------+--------+------+
| sname | gender | dept_id | master | sid |
+-----------+--------+---------+--------+------+
| aa_aa | 1 | 2 | NULL | NULL |
| avvvvaccc | 1 | 2 | NULL | NULL |
| ab | 1 | 2 | NULL | NULL |
| abcad | 1 | 2 | NULL | NULL |
+-----------+--------+---------+--------+------+
4 rows in set (0.00 sec)
mysql> select * from students where sname like 'a_';
+-------+--------+---------+--------+------+
| sname | gender | dept_id | master | sid |
+-------+--------+---------+--------+------+
| ab | 1 | 2 | NULL | NULL |
| ad | 1 | 2 | NULL | NULL |
+-------+--------+---------+--------+------+
2 rows in set (0.00 sec)
mysql> select * from students where sname like 'a\%';
+-------+--------+---------+--------+------+
| sname | gender | dept_id | master | sid |
+-------+--------+---------+--------+------+
| a% | 1 | 2 | NULL | NULL |
+-------+--------+---------+--------+------+
1 row in set (0.00 sec)
时间函数：
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-12-19 14:51:24 |
+---------------------+
1 row in set (0.00 sec)
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2021-12-19 |
+----------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-12-19 14:51:37 |
+---------------------+
1 row in set (0.00 sec)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 14:55:03 |
+----------------+
1 row in set (0.02 sec)
date_add()
mysql> select date_add('2021-12-19 14:57:00',interval 1 second);
+---------------------------------------------------+
| date_add('2021-12-19 14:57:00',interval 1 second) |
+---------------------------------------------------+
| 2021-12-19 14:57:01 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2021-12-19 14:57:00',interval 1 day);
+------------------------------------------------+
| date_add('2021-12-19 14:57:00',interval 1 day) |
+------------------------------------------------+
| 2021-12-20 14:57:00 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2021-12-19 14:57:00',interval 1 minute);
+---------------------------------------------------+
| date_add('2021-12-19 14:57:00',interval 1 minute) |
+---------------------------------------------------+
| 2021-12-19 14:58:00 |
+---------------------------------------------------+
1 row in set (0.02 sec)
mysql> select date_add('2021-12-19 14:57:00',interval 1 hour);
+-------------------------------------------------+
| date_add('2021-12-19 14:57:00',interval 1 hour) |
+-------------------------------------------------+
| 2021-12-19 15:57:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2021-12-19 14:57:00',interval 1 month);
+--------------------------------------------------+
| date_add('2021-12-19 14:57:00',interval 1 month) |
+--------------------------------------------------+
| 2022-01-19 14:57:00 |
+--------------------------------------------------+
1 row in set (0.00 sec)
date_format()
mysql> select date_format(now(),'%Y%M%D');
+-----------------------------+
| date_format(now(),'%Y%M%D') |
+-----------------------------+
| 2021December19th |
+-----------------------------+
1 row in set (0.03 sec)
mysql> alter table students add tstamp datetime default now();
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from students;
+-----------+--------+---------+--------+------+---------------------+
| sname | gender | dept_id | master | sid | tstamp |
+-----------+--------+---------+--------+------+---------------------+
| narisu | 1 | 1 | NULL | NULL | 2021-12-19 15:10:49 |
| gutianle | 1 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
| bbb | 2 | 1 | NULL | NULL | 2021-12-19 15:10:49 |
| ccc | 2 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
| night | NULL | 1 | NULL | NULL | 2021-12-19 15:10:49 |
| aa_aa | 1 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
| avvvvaccc | 1 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
| ab | 1 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
| abcad | 1 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
| a | 1 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
| ad | 1 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
| a% | 1 | 2 | NULL | NULL | 2021-12-19 15:10:49 |
+-----------+--------+---------+--------+------+---------------------+
12 rows in set (0.00 sec)
mysql> select tstamp,date_format(tstamp,'%Y%m%d') from students;
+---------------------+------------------------------+
| tstamp | date_format(tstamp,'%Y%m%d') |
+---------------------+------------------------------+
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
| 2021-12-19 15:10:49 | 20211219 |
+---------------------+------------------------------+
12 rows in set (0.00 sec)
最后修改时间：2021-12-19 15:16:20
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
