Mysql高阶语句(函数)
Mysql中的数学函数
mysql> select abs(-4);
+---------+
| abs(-4) |
+---------+
| 4 |
+---------+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.5491841859642269 |
+--------------------+
1 row in set (0.00 sec)
mysql> create table a as select rand();
Query OK, 1 row affected (0.22 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc a;
+--------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------+------+-----+---------+-------+
| rand() | double | NO | | 0 | |
+--------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select mod(5,3);
+----------+
| mod(5,3) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select power(2,8);
+------------+
| power(2,8) |
+------------+
| 256 |
+------------+
1 row in set (0.00 sec)
mysql> select round(3.1);
+------------+
| round(3.1) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
mysql> select round(3.5);
+------------+
| round(3.5) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select round(3.535,2);
+----------------+
| round(3.535,2) |
+----------------+
| 3.54 |
+----------------+
1 row in set (0.00 sec)
mysql> select round(3.535,1);
+----------------+
| round(3.535,1) |
+----------------+
| 3.5 |
+----------------+
1 row in set (0.00 sec)
mysql> select round(3.535,0);
+----------------+
| round(3.535,0) |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
mysql> select sqrt(2);
+--------------------+
| sqrt(2) |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set (0.00 sec)
mysql> create table b as select sqrt(2);
Query OK, 1 row affected (0.37 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc b;
+---------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------+------+-----+---------+-------+
| sqrt(2) | double | YES | | NULL | |
+---------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select truncate(3.25,2);
+------------------+
| truncate(3.25,2) |
+------------------+
| 3.25 |
+------------------+
1 row in set (0.00 sec)
mysql> select truncate(3.25,1);
+------------------+
| truncate(3.25,1) |
+------------------+
| 3.2 |
+------------------+
1 row in set (0.00 sec)
mysql> select ceil(3);
+---------+
| ceil(3) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
mysql> select ceil(3.212413);
+----------------+
| ceil(3.212413) |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
mysql> select floor(3);
+----------+
| floor(3) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> select floor(2.994);
+--------------+
| floor(2.994) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
mysql> select greatest(2,3,5.34);
+--------------------+
| greatest(2,3,5.34) |
+--------------------+
| 5.34 |
+--------------------+
1 row in set (0.00 sec)
mysql> select least(2,3,5.34);
+-----------------+
| least(2,3,5.34) |
+-----------------+
| 2.00 |
+-----------------+
1 row in set (0.00 sec)
Mysql中的聚合函数
mysql> select avg(score) from test1;
+------------+
| avg(score) |
+------------+
| 71.000000 |
+------------+
1 row in set (0.00 sec)
mysql> select count(address) from test1;
+----------------+
| count(address) |
+----------------+
| 8 |
+----------------+
1 row in set (0.00 sec)
mysql> select min(score) from test1;
+------------+
| min(score) |
+------------+
| 40.00 |
+------------+
1 row in set (0.08 sec)
mysql> select max(score) from test1;
+------------+
| max(score) |
+------------+
| 100.00 |
+------------+
1 row in set (0.00 sec)
mysql> select sum(score) from test1;
+------------+
| sum(score) |
+------------+
| 568.00 |
+------------+
1 row in set (0.00 sec)
Mysql的字符串函数
mysql> select length('asdqwfq');
+-------------------+
| length('asdqwfq') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)
mysql> select trim('asd ');
+---------------+
| trim('asd ') |
+---------------+
| asd |
+---------------+
1 row in set (0.00 sec)
mysql> select length(trim('asd '));
+-----------------------+
| length(trim('asd ')) |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select length(trim(' asd '));
+--------------------------+
| length(trim(' asd ')) |
+--------------------------+
| 3 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select length(trim(' % das'));
+-------------------------+
| length(trim(' % das')) |
+-------------------------+
| 5 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select concat(trim('sdas '),trim(' asdf '));
+--------------------------------------+
| concat(trim('sdas '),trim(' asdf ')) |
+--------------------------------------+
| sdasasdf |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select upper('sadqfA');
+-----------------+
| upper('sadqfA') |
+-----------------+
| SADQFA |
+-----------------+
1 row in set (0.00 sec)
mysql> select lower(upper('sadqfA'));
+------------------------+
| lower(upper('sadqfA')) |
+------------------------+
| sadqfa |
+------------------------+
1 row in set (0.00 sec)
mysql> select left('safqw',2);
+-----------------+
| left('safqw',2) |
+-----------------+
| sa |
+-----------------+
1 row in set (0.00 sec)
mysql> select right(left('safqw',6),3);
+--------------------------+
| right(left('safqw',6),3) |
+--------------------------+
| fqw |
+--------------------------+
1 row in set (0.00 sec)
mysql> select repeat(right(left('safqw',6),3),3);
+------------------------------------+
| repeat(right(left('safqw',6),3),3) |
+------------------------------------+
| fqwfqwfqw |
+------------------------------------+
1 row in set (0.00 sec)
mysql> select length(space(5));
+------------------+
| length(space(5)) |
+------------------+
| 5 |
+------------------+
1 row in set (0.00 sec)
mysql> select replace('lei','i','zhangsan'); //将i替换成zhangsan
+-------------------------------+
| replace('lei','i','zhangsan') |
+-------------------------------+
| lezhangsan |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select strcmp(2144,2156);
+-------------------+
| strcmp(2144,2156) |
+-------------------+
| -1 |
+-------------------+
1 row in set (0.01 sec)
mysql> select strcmp(2144,2136);
+-------------------+
| strcmp(2144,2136) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select strcmp(2144,2144);
+-------------------+
| strcmp(2144,2144) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)
mysql> select strcmp(2144,3);
+----------------+
| strcmp(2144,3) |
+----------------+
| -1 |
+----------------+
1 row in set (0.00 sec)
mysql> select substring('sadqwf',2,9);
+-------------------------+
| substring('sadqwf',2,9) |
+-------------------------+
| adqwf |
+-------------------------+
1 row in set (0.00 sec)
mysql> select reverse('qwfqwdasd');
+----------------------+
| reverse('qwfqwdasd') |
+----------------------+
| dsadwqfwq |
+----------------------+
1 row in set (0.00 sec)
Mysql的日期和时间函数
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-07-17 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 17:55:19 |
+-----------+
1 row in set (0.07 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-07-17 17:56:08 |
+---------------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 7 |
+--------------+
1 row in set (0.00 sec)
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 28 |
+-------------+
1 row in set (0.00 sec)
mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 17 |
+-------------+
1 row in set (0.00 sec)
mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 59 |
+---------------+
1 row in set (0.00 sec)
mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
| 47 |
+---------------+
1 row in set (0.00 sec)
mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 7 |
+------------------+
1 row in set (0.00 sec)
mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
| 17 |
+-------------------+
1 row in set (0.00 sec)
mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
| 198 |
+------------------+
1 row in set (0.00 sec)