mysql> select abs(-4);+---------+| abs(-4)|+---------+|4|+---------+1 row in set (0.00 sec)
rand():返回0-1的随机数,有效位有16位,字符型为双精度
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)
mod(x,y):返回x/y的余数
mysql> select mod(5,3);+----------+| mod(5,3)|+----------+|2|+----------+1 row in set (0.00 sec)
power(x,y):返回x的y次方
mysql> select power(2,8);+------------+| power(2,8)|+------------+|256|+------------+1 row in set (0.00 sec)
round(x):返回离x最近的整数,四舍五入
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)
round(x,y):返回x的y位小数的值,四舍五入
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)
sqrt(x):返回x的算数平方根,字符型为双精度
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)
truncate(x,y):返回x的后y位小数,不四舍五入
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)
ceil(x):返回大于或等于x的最小整数
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)
floor(x):返回小于或等于x的最大整数
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)
greatest(x1,x2,x3):返回集合中最大值
mysql> select greatest(2,3,5.34);+--------------------+| greatest(2,3,5.34)|+--------------------+|5.34|+--------------------+1 row in set (0.00 sec)
least(x1,x2,x3):返回集合中最小值
mysql> select least(2,3,5.34);+-----------------+| least(2,3,5.34)|+-----------------+|2.00|+-----------------+1 row in set (0.00 sec)
mysql中的聚合函数
avg():返回指定列的平均值
mysql> select avg(score) from test1;+------------+| avg(score)|+------------+|71.000000|+------------+1 row in set (0.00 sec)
count():返回指定列的中的非null个数
mysql> select count(address) from test1;+----------------+| count(address)|+----------------+|8|+----------------+1 row in set (0.00 sec)
min():返回指定列中的最小值
mysql> select min(score) from test1;+------------+| min(score)|+------------+|40.00|+------------+1 row in set (0.08 sec)
max(): 返回指定列中的最大值
mysql> select max(score) from test1;+------------+| max(score)|+------------+|100.00|+------------+1 row in set (0.00 sec)
sum():返回指定列所有值之和
mysql> select sum(score) from test1;+------------+| sum(score)|+------------+|568.00|+------------+1 row in set (0.00 sec)
mysql的字符串函数
length(x):返回字符串x的长度
mysql> select length('asdqwfq');+-------------------+| length('asdqwfq')|+-------------------+|7|+-------------------+1 row in set (0.00 sec)
trim():返回去除的指定格式的值
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)
concat(x,y):将x与y字符串拼接成一个字符串
mysql> select concat(trim('sdas '),trim(' asdf '));+--------------------------------------+| concat(trim('sdas '),trim(' asdf '))|+--------------------------------------+| sdasasdf |+--------------------------------------+1 row in set (0.00 sec)
upper(x):将字符串所有字母变成大写
mysql> select upper('sadqfA');+-----------------+| upper('sadqfA')|+-----------------+| SADQFA |+-----------------+1 row in set (0.00 sec)
lower(x):将字符串所有字母变成小写
mysql> select lower(upper('sadqfA'));+------------------------+| lower(upper('sadqfA'))|+------------------------+| sadqfa |+------------------------+1 row in set (0.00 sec)
left(x,y):返回字符串x的前y个字符
mysql> select left('safqw',2);+-----------------+| left('safqw',2)|+-----------------+| sa |+-----------------+1 row in set (0.00 sec)
right(x,y):返回字符串x的后y个字符
mysql> select right(left('safqw',6),3);+--------------------------+| right(left('safqw',6),3)|+--------------------------+| fqw |+--------------------------+1 row in set (0.00 sec)
repeat(x,y):将字符串x重复y次
mysql> select repeat(right(left('safqw',6),3),3);+------------------------------------+|repeat(right(left('safqw',6),3),3)|+------------------------------------+| fqwfqwfqw |+------------------------------------+1 row in set (0.00 sec)
space(x):返回x个空格
mysql> select length(space(5));+------------------+| length(space(5))|+------------------+|5|+------------------+1 row in set (0.00 sec)
replace(x,y,z):将字符串z替代字符串x中的字符串y
mysql> select replace('lei','i','zhangsan');//将i替换成zhangsan
+-------------------------------+| replace('lei','i','zhangsan')|+-------------------------------+| lezhangsan |+-------------------------------+1 row in set (0.00 sec)
strcmp(x,y):比较x,y,大于返回1,等于返回0。小于返回-1,是逐位比较
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)
substring(x,y,z):获取从字符串x中的第y位开始长度为z的字符串
mysql> select substring('sadqwf',2,9);+-------------------------+| substring('sadqwf',2,9)|+-------------------------+| adqwf |+-------------------------+1 row in set (0.00 sec)
reverse(x):将字符串x反转
mysql> select reverse('qwfqwdasd');+----------------------+| reverse('qwfqwdasd')|+----------------------+| dsadwqfwq |+----------------------+1 row in set (0.00 sec)
mysql的日期和时间函数
curdate():返回当前时间的年月日
mysql> select curdate();+------------+| curdate()|+------------+|2021-07-17|+------------+1 row in set (0.00 sec)
curtime():返回当前时间的时分秒
mysql> select curtime();+-----------+| curtime()|+-----------+|17:55:19|+-----------+1 row in set (0.07 sec)
now():返回当前时间的日期和时间
mysql> select now();+---------------------+| now()|+---------------------+|2021-07-1717:56:08|+---------------------+1 row in set (0.00 sec)
month(x):返回日期x的的月份值
mysql> select month(now());+--------------+| month(now())|+--------------+|7|+--------------+1 row in set (0.00 sec)
week(x):返回日期x是年度的第几个星期
mysql> select week(now());+-------------+| week(now())|+-------------+|28|+-------------+1 row in set (0.00 sec)
hour(x):返回x中的小时值
mysql> select hour(now());+-------------+| hour(now())|+-------------+|17|+-------------+1 row in set (0.00 sec)
minute(x):返回x中的分钟值
mysql> select minute(now());+---------------+| minute(now())|+---------------+|59|+---------------+1 row in set (0.00 sec)
second(x);返回x中的秒钟值
mysql> select second(now());+---------------+| second(now())|+---------------+|47|+---------------+1 row in set (0.00 sec)
dayofweek(x):返回x是星期几,1是星期天,2是星期一
mysql> select dayofweek(now());+------------------+| dayofweek(now())|+------------------+|7|+------------------+1 row in set (0.00 sec)
dayofmonth(x):计算日期x是本月第几天
mysql> select dayofmonth(now());+-------------------+| dayofmonth(now())|+-------------------+|17|+-------------------+1 row in set (0.00 sec)
dayofyear(x):计算日期x是本年第几天
mysql> select dayofyear(now());+------------------+| dayofyear(now())|+------------------+|198|+------------------+1 row in set (0.00 sec)
mysql高阶语句(函数)mysql中的数学函数mysql中的聚合函数mysql的字符串函数mysql的日期和时间函数mysql中的数学函数abs(x):返回x的绝对值mysql> select abs(-4);+---------+| abs(-4) |+---------+| 4 |+---------+1 row in set (0.00 sec)rand():返回0-1的随机数,有效位有16位,字符型为双精度mysql> select ran