《深入浅出mysql数据库开发优化与管理维护》笔记(二)
《深入浅出mysql数据库开发优化与管理维护》笔记(二)
小猪头 发表于4个月前
《深入浅出mysql数据库开发优化与管理维护》笔记(二)
  • 发表于 4个月前
  • 阅读 8
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

摘要: 运算符,常用函数

运算符:

    =运算符,这个比较两侧的操作数是否相等。但是null不能用这个进行比较

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

<>和=运算符相反。

<=>和=运算符类似,null可以用这个进行比较。

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

<运算符,<=运算符,>运算符,>=运算符,between ... and ... ,in,is null ,is not null,like

regexp运算符使用格式:"str regexp str_pat"当str中含有str_pat相匹配的字符串时返回1否则返回0

mysql> select 'abcdef' regexp 'ab','abcdef' regexp 'k';
+----------------------+---------------------+
| 'abcdef' regexp 'ab' | 'abcdef' regexp 'k' |
+----------------------+---------------------+
|                    1 |                   0 |
+----------------------+---------------------+
1 row in set (0.00 sec)

逻辑运算符

    not或!,返回和操作数相反的结果。如果是not null的返回值为null。

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

    and或&&,都为真时返回真,数据方有一方为null则返回null。

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

or或||,有一方为真返回真

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

xor逻辑异或,当任意一个操作数为null时,返回值为null。如果两个运算数真假相异则返回真。

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

常用函数

    字符串函数:

        concat(s1,s2,...sn)

mysql> select concat('a','b','c');
+---------------------+
| concat('a','b','c') |
+---------------------+
| abc                 |
+---------------------+
1 row in set (0.00 sec)

        insert(str,x,y,instr)

mysql> select insert('abcdefghi',2,4,'zyxw');
+--------------------------------+
| insert('abcdefghi',2,4,'zyxw') |
+--------------------------------+
| azyxwfghi                      |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select insert('abcde',2,4,'zyxwxxx');
+-------------------------------+
| insert('abcde',2,4,'zyxwxxx') |
+-------------------------------+
| azyxwxxx                      |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select insert('abc',2,4,'zyxwxxx');
+-----------------------------+
| insert('abc',2,4,'zyxwxxx') |
+-----------------------------+
| azyxwxxx                    |
+-----------------------------+
1 row in set (0.00 sec)

lower(str),upper(str)

left(str,x),right(str,x)

mysql> select left('abcdefghi',2);
+---------------------+
| left('abcdefghi',2) |
+---------------------+
| ab                  |
+---------------------+
1 row in set (0.00 sec)

lpad(str,n,pad),rpad(str,n,pad)

mysql> select lpad('abc',5,'d');
+-------------------+
| lpad('abc',5,'d') |
+-------------------+
| ddabc             |
+-------------------+
1 row in set (0.00 sec)

ltrim(str),rtrim(str),trim(str)

mysql> select ltrim('     dbc');
+-------------------+
| ltrim('     dbc') |
+-------------------+
| dbc               |
+-------------------+
1 row in set (0.00 sec)

repeat(str,x)

mysql> select repeat('a',4);
+---------------+
| repeat('a',4) |
+---------------+
| aaaa          |
+---------------+
1 row in set (0.00 sec)

replace(str,a,b)

mysql> select replace('abcada','a','f');
+---------------------------+
| replace('abcada','a','f') |
+---------------------------+
| fbcfdf                    |
+---------------------------+
1 row in set (0.00 sec)

strcmp(s1,s2)

mysql> select strcmp('abcd','efgh');
+-----------------------+
| strcmp('abcd','efgh') |
+-----------------------+
|                    -1 |
+-----------------------+
1 row in set (0.01 sec)

mysql> select strcmp('abcd','abcd');
+-----------------------+
| strcmp('abcd','abcd') |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select strcmp('abcd','abc');
+----------------------+
| strcmp('abcd','abc') |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> select strcmp('ab','abc');
+--------------------+
| strcmp('ab','abc') |
+--------------------+
|                 -1 |
+--------------------+
1 row in set (0.00 sec)

substring(str,x,y)

mysql> select substring('abcdefg',3,5);
+--------------------------+
| substring('abcdefg',3,5) |
+--------------------------+
| cdefg                    |
+--------------------------+
1 row in set (0.00 sec)

数值函数

abs(x)取绝对值,ceil(x)返回大于x的最小整数

floor(x)返回小于x的最大整数,mod(x/y)返回x/y的摸

rand()返回0-1随机数

round(x,y)返回参数x的四舍五入的有y位的小数

mysql> select round(2.015462,4);
+-------------------+
| round(2.015462,4) |
+-------------------+
|            2.0155 |
+-------------------+
1 row in set (0.00 sec)

mysql> select round(2.015462,2);
+-------------------+
| round(2.015462,2) |
+-------------------+
|              2.02 |
+-------------------+
1 row in set (0.00 sec)

mysql> select round(2.015462,1);
+-------------------+
| round(2.015462,1) |
+-------------------+
|               2.0 |
+-------------------+
1 row in set (0.00 sec)

truncate(x,y)返回数字x截断为y位小数的结果

mysql> select truncate(2.015462,1);
+----------------------+
| truncate(2.015462,1) |
+----------------------+
|                  2.0 |
+----------------------+
1 row in set (0.00 sec)

mysql> select truncate(2.015462,2);
+----------------------+
| truncate(2.015462,2) |
+----------------------+
|                 2.01 |
+----------------------+
1 row in set (0.00 sec)

日期和时间函数

curdate()返回当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2017-08-28 |
+------------+
1 row in set (0.01 sec)

curtime()返回当前时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 22:57:49  |
+-----------+
1 row in set (0.00 sec)

now()返回当前日期和时间

unix_timestamp(date)返回日期date的unix时间戳

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1503932331 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('2017-05-06 12:34:00');
+---------------------------------------+
| unix_timestamp('2017-05-06 12:34:00') |
+---------------------------------------+
|                            1494045240 |
+---------------------------------------+
1 row in set (0.00 sec)

from_unixtime返回unix时间戳的日期

mysql> select from_unixtime(1494045240);
+---------------------------+
| from_unixtime(1494045240) |
+---------------------------+
| 2017-05-06 12:34:00       |
+---------------------------+
1 row in set (0.01 sec)

week(date)返回一年中的第几周

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          35 |
+-------------+
1 row in set (0.00 sec)

year(date)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2017 |
+-------------+
1 row in set (0.01 sec)

hour(time) 

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          23 |
+-------------+
1 row in set (0.00 sec)

minute(time)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|             4 |
+---------------+
1 row in set (0.00 sec)

date_formart(date,fmt)

mysql> select date_format(now(),'%M,%D,%Y');
+-------------------------------+
| date_format(now(),'%M,%D,%Y') |
+-------------------------------+
| August,28th,2017              |
+-------------------------------+
1 row in set (0.01 sec)

流程函数

if(value,t,f)如果value是真返回t,否则返回f

ifnull(value1,value2)如果value1不为空返回value1,否则返回value2

case when [value1] then [result1] else [result2]

    如果value1是真返回result1否则返回result2

case [expr] when [value1] then [result1] else [default] end

    如果expr等于value1返回result1否则返回default

database()返回当前数据库名,user()返回当前登录用户

version()返回数据库版本,inet_aton(ip)返回IP地址的数字表示

mysql> select inet_aton('127.0.0.1');
+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.01 sec)

inet_ntoa(num)返回数字代表的IP值

mysql> select inet_ntoa(2130706433);
+-----------------------+
| inet_ntoa(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.00 sec)

password(str):只用来设置用户密码,不能用来对应用的数据加密。

mysql> select password('a');
+-------------------------------------------+
| password('a')                             |
+-------------------------------------------+
| *667F407DE7C6AD07358FA38DAED7828A72014B4E |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

md5(str)

mysql> select md5('a');
+----------------------------------+
| md5('a')                         |
+----------------------------------+
| 0cc175b9c0f1b6a831c399e269772661 |
+----------------------------------+
1 row in set (0.00 sec)

 

 

共有 人打赏支持
粉丝 0
博文 11
码字总数 3489
×
小猪头
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: