MySQL 运算符和函数

字符函数

在这里插入图片描述

CONCAT() 字符连接
  • 可以两个字符串连接也可多个字符串连接。
mysql> SELECT CONCAT('immoc', 'MySQL');
+--------------------------+
| CONCAT('immoc', 'MySQL') |
+--------------------------+
| immocMySQL               |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('immoc', '-', 'MySQL');
+-------------------------------+
| CONCAT('immoc', '-', 'MySQL') |
+-------------------------------+
| immoc-MySQL                   |
+-------------------------------+
1 row in set (0.00 sec)
  • 创建数据表
mysql> CREATE TABLE IF NOT EXISTS test(
    -> first_name VARCHAR(20) DEFAULT NULL,
    -> last_name VARCHAR(20) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
  • 查看表结构
mysql> DESC test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(20) | YES  |     | NULL    |       |
| last_name  | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
  • 添加数据
mysql> INSERT test (first_name, last_name) VALUES('A', 'B');
mysql> INSERT test (first_name, last_name) VALUES('C', 'D');
mysql> INSERT test (first_name, last_name) VALUES('tom%', '123');
mysql> INSERT test (last_name) VALUES( '11');

mysql> SELECT * FROM test;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A          | B         |
| C          | D         |
| tom%       | 123       |
| NULL       | 11        |
+------------+-----------+
4 rows in set (0.00 sec)
  • 连接表中的字符串 first_name 和 last_name
mysql> SELECT CONCAT(first_name, last_name) AS fillname FROM test;
+----------+
| fillname |
+----------+
| AB       |
| CD       |
| tom%123  |
| NULL     |
+----------+
4 rows in set (0.00 sec)
CONCAT_WS() 使用指定的分隔符进行字符连接
mysql> SELECT CONCAT_WS('|', 'A', 'B', 'C');
+-------------------------------+
| CONCAT_WS('|', 'A', 'B', 'C') |
+-------------------------------+
| A|B|C                         |
+-------------------------------+
1 row in set (0.00 sec)
FORMAT() 数字格式化

保留多少位小数

mysql> SELECT FORMAT(1256.75, 1);
+--------------------+
| FORMAT(1256.75, 1) |
+--------------------+
| 1,256.8            |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(1256.75, 0);
+--------------------+
| FORMAT(1256.75, 0) |
+--------------------+
| 1,257              |
+--------------------+
1 row in set (0.00 sec)
LOWER() 转换为小写字母
mysql> SELECT LOWER('MySQL');
+----------------+
| LOWER('MySQL') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)
UPPER() 转换为大写字母
mysql> SELECT UPPER('mysql');
+----------------+
| UPPER('mysql') |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.00 sec)
LEFT() 获取左侧字符
mysql> SELECT LEFT('MySQL', 2);
+------------------+
| LEFT('MySQL', 2) |
+------------------+
| My               |
+------------------+
1 row in set (0.00 sec)
RIGHT() 获取右侧字符
mysql> SELECT RIGHT('MySQL', 3);
+-------------------+
| RIGHT('MySQL', 3) |
+-------------------+
| SQL               |
+-------------------+
1 row in set (0.00 sec)

在这里插入图片描述

LENGTH() 获取字符串长度

字符串中的空格也计算在长度之内。

mysql> SELECT LENGTH('My SQL');
+------------------+
| LENGTH('My SQL') |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)
LTRIM() 删除前导空格
mysql> SELECT LTRIM('    MySQL    ');
+------------------------+
| LTRIM('  MySQL    ') |
+------------------------+
| MySQL                  |
+------------------------+
1 row in set (0.00 sec)
RTRIM() 删除后续空格
mysql> SELECT LENGTH(RTRIM('  MySQL    '));
+------------------------------+
| LENGTH(RTRIM('  MySQL    ')) |
+------------------------------+
|                            7 |
+------------------------------+
1 row in set (0.00 sec)
TRIM() 删除前导和后续空格
mysql> SELECT LENGTH(TRIM('  MySQL    '));
+-----------------------------+
| LENGTH(TRIM('  MySQL    ')) |
+-----------------------------+
|                           5 |
+-----------------------------+
1 row in set (0.00 sec)

删除指定的前导符号

mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???');
+-------------------------------------+
| TRIM(LEADING '?' FROM '??MySQL???') |
+-------------------------------------+
| MySQL???                            |
+-------------------------------------+
1 row in set (0.00 sec)

删除指定的后续符号

mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???');
+--------------------------------------+
| TRIM(TRAILING '?' FROM '??MySQL???') |
+--------------------------------------+
| ??MySQL                              |
+--------------------------------------+
1 row in set (0.00 sec)

删除指定的前导和后续符号

mysql> SELECT TRIM(BOTH '?' FROM '??MySQL???');
+----------------------------------+
| TRIM(BOTH '?' FROM '??MySQL???') |
+----------------------------------+
| MySQL                            |
+----------------------------------+
1 row in set (0.00 sec)
REPLACE() 字符串替换
mysql> SELECT REPLACE('??My?SQL???', '?', '');
+---------------------------------+
| REPLACE('??My?SQL???', '?', '') |
+---------------------------------+
| MySQL                           |
+---------------------------------+
1 row in set (0.00 sec)
SUBSTRING() 字符串截取

字符串编号从0开始。

mysql> SELECT SUBSTRING('MySQL', 1, 2);
+--------------------------+
| SUBSTRING('MySQL', 1, 2) |
+--------------------------+
| My                       |
+--------------------------+
1 row in set (0.00 sec)

// 从第3位到最后
mysql> SELECT SUBSTRING('MySQL', 3);
+-----------------------+
| SUBSTRING('MySQL', 3) |
+-----------------------+
| SQL                   |
+-----------------------+
1 row in set (0.00 sec)

// 起始位置可以为负值,表示从倒数第3个开始取到最后
mysql> SELECT SUBSTRING('MySQL', -3);
+------------------------+
| SUBSTRING('MySQL', -3) |
+------------------------+
| SQL                    |
+------------------------+
1 row in set (0.00 sec)

// 从倒数第3个开始取2个
mysql> SELECT SUBSTRING('MySQL', -3, 2);
+---------------------------+
| SUBSTRING('MySQL', -3, 2) |
+---------------------------+
| SQ                        |
+---------------------------+
1 row in set (0.00 sec)
[NOT] LIKE 模式匹配

%(百分号): 代表任意个字符

**_(下划线):**代表任意一个字符

mysql> SELECT 'MySQL' LIKE 'M%';
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
|                 1 |   # 1代表ture
+-------------------+
1 row in set (0.00 sec)
  • 查找 test 表中 first_name 包含 'o’的行:
mysql> SELECT * FROM test WHERE first_name LIKE '%o%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom%       | 123       |
+------------+-----------+
1 row in set (0.00 sec)
  • 匹配通配符 ESCAPE
mysql> SELECT * FROM test WHERE first_name LIKE '%2%%' ESCAPE 2;  # 字符2也可以为其他任意字符
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom%       | 123       |
+------------+-----------+
1 row in set (0.00 sec)

数值运算符和函数

在这里插入图片描述

算数符 + - * /
mysql> SELECT 8 / 3;
+--------+
| 8 / 3  |
+--------+
| 2.6667 |
+--------+
1 row in set (0.00 sec)
CEIL() 向上取整
mysql> SELECT CEIL(3.01);
+------------+
| CEIL(3.01) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)
FLOOR() 向下取整
mysql> SELECT FLOOR(3.99);
+-------------+
| FLOOR(3.99) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)
DIV 整数除法

DIV 是运算符,不是函数。

mysql> SELECT 5 DIV 4;
+---------+
| 5 DIV 4 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
MOD 取余数(取模)

MOD 是运算符, 等价于==%==,不是函数, 既可以对整数取模,也可以对浮点数取模。

mysql> SELECT 5 MOD 3;
+---------+
| 5 MOD 3 |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT 5 % 3;
+-------+
| 5 % 3 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT 5.4 % 3;
+---------+
| 5.4 % 3 |
+---------+
|     2.4 |
+---------+
1 row in set (0.00 sec)
POWER() 幂运算
mysql> SELECT POWER(2, 10);
+--------------+
| POWER(2, 10) |
+--------------+
|         1024 |
+--------------+
1 row in set (0.00 sec)
ROUND() 四舍五入
mysql> SELECT ROUND(3.248, 2);
+-----------------+
| ROUND(3.248, 2) |
+-----------------+
|            3.25 |
+-----------------+
1 row in set (0.00 sec)
TRUNCATE() 数字截取
mysql> SELECT TRUNCATE(125.89, 1);
+---------------------+
| TRUNCATE(125.89, 1) |
+---------------------+
|               125.8 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(125.89, -1);
+----------------------+
| TRUNCATE(125.89, -1) |
+----------------------+
|                  120 |
+----------------------+
1 row in set (0.00 sec)

比较运算符和函数

在这里插入图片描述

[NOT] BETWEEN … AND … [不] 在范围内
mysql> SELECT 15 BETWEEN 1 AND 20;
+---------------------+
| 15 BETWEEN 1 AND 20 |
+---------------------+
|                   1 |  # true 
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 25 BETWEEN 1 AND 20;
+---------------------+
| 25 BETWEEN 1 AND 20 |
+---------------------+
|                   0 |  #false
+---------------------+
1 row in set (0.00 sec)
[NOT] IN() [不] 在列出值内
mysql> SELECT 10 IN (5, 10, 15, 20);
+-----------------------+
| 10 IN (5, 10, 15, 20) |
+-----------------------+
|                     1 |  // true
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT 12 IN (5, 10, 15, 20);
+-----------------------+
| 12 IN (5, 10, 15, 20) |
+-----------------------+
|                     0 |  // false
+-----------------------+
1 row in set (0.00 sec)
IS [NOT] NULL [不] 为空

‘’(空串),0 不是NULL。

mysql> SELECT NULL is NULL
+--------------+
| NULL is NULL |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT '' is NULL;
+------------+
| '' is NULL |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT 0 is NULL;
+-----------+
| 0 is NULL |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

SELECT first_name, last_name FROM test WHERE first_name IS NULL;
SELECT first_name, last_name FROM test WHERE first_name IS NOT NULL;

时间和日期函数

在这里插入图片描述

NOW() 当前时间和日期
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-02-20 16:51:16 |
+---------------------+
1 row in set (0.01 sec)
CURDATE() 当前日期
mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-02-20 |
+------------+
1 row in set (0.00 sec)
CURTIME() 当前时间
mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 16:52:26  |
+-----------+
1 row in set (0.01 sec)
DATE_ADD() 日期变化
// 在指定日期前加上365天
mysql> SELECT DATE_ADD('2019-2-20', INTERVAL 365 DAY);
+-----------------------------------------+
| DATE_ADD('2019-2-20', INTERVAL 365 DAY) |
+-----------------------------------------+
| 2020-02-20                              |
+-----------------------------------------+
1 row in set (0.00 sec)

// 在指定日期前减去365天
mysql> SELECT DATE_ADD('2019-2-20', INTERVAL -365 DAY);
+------------------------------------------+
| DATE_ADD('2019-2-20', INTERVAL -365 DAY) |
+------------------------------------------+
| 2018-02-20                               |
+------------------------------------------+
1 row in set (0.00 sec)
DATEDIFF() 日期差值
mysql> SELECT DATEDIFF('2018-2-20', '2019-2-20');
+------------------------------------+
| DATEDIFF('2018-2-20', '2019-2-20') |
+------------------------------------+
|                               -365 |
+------------------------------------+
1 row in set (0.00 sec)
DATE_FORMAT() 日期格式化
mysql> SELECT DATE_FORMAT('2019-2-20', '%m/%d/%Y');
+--------------------------------------+
| DATE_FORMAT('2019-2-20', '%m/%d/%Y') |
+--------------------------------------+
| 02/20/2019                           |
+--------------------------------------+
1 row in set (0.00 sec)

信息函数

在这里插入图片描述

CONNECTION_ID() 当前用户连接的ID
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)
DATABASE() 当前数据库
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
LAST_INSERT_ID() 最后一次插入记录的ID号

返回的上一次往数据库插入数据的自动排序的且为主键的ID(没有不显示),同时写入多条时返回多条中的第一条的ID。

mysql> SELECT LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
USER() 当前用户
mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
VERSION()
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.37    |
+-----------+
1 row in set (0.00 sec)

聚合函数(多行函数)

在这里插入图片描述

AVG() 平均值
mysql> SELECT AVG(goods_price) AS avg_price FROM tdb_goods;
COUNT() 计数
mysql> SELECT COUNT(goods_id) AS counts FROM tdb_goods;
MAX() 最大值
mysql> SELECT MAX(goods_price) AS expensive_price FROM tdb_goods;
MIN() 最小值
mysql> SELECT Min(goods_price) AS cheap_price FROM tdb_goods;
SUM() 求和
mysql> SELECT SUM(goods_price) AS sum_price FROM tdb_goods;

加密函数

在这里插入图片描述

MD5() 信息摘要算法
mysql> SELECT MD5('MySQL');
+----------------------------------+
| MD5('MySQL')                     |
+----------------------------------+
| 62a004b95946bb97541afa471dcca73a |
+----------------------------------+
1 row in set (0.00 sec)
PASSWORD() 密码算法

修改数据库登录密码

mysql> SET PASSWORD = PASSWORD('111');
Query OK, 0 rows affected (0.01 sec)
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值