MySQL基础六：运算符和函数

本文参考：http://www.imooc.com/video/2477

mysql> CREATE DATABASE imooc;Query OK, 1 row affected (0.01 sec)mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || imooc              || mysql              || performance_schema || sakila             || sys                || t2                 || world              |+--------------------+8 rows in set (0.02 sec)mysql> #连接两个字符；mysql> SELECT CONCAT('imooc','mysql');+-------------------------+| CONCAT('imooc','mysql') |+-------------------------+| imoocmysql              |+-------------------------+1 row in set (0.02 sec)mysql> SELECT CONCAT('imooc','-','mysql');+-----------------------------+| CONCAT('imooc','-','mysql') |+-----------------------------+| imooc-mysql                 |+-----------------------------+1 row in set (0.00 sec)mysql> CREATE DATABASE test;Query OK, 1 row affected (0.00 sec)mysql> DESC test;+----------+---------------------+------+-----+---------+----------------+| Field    | Type                | Null | Key | Default | Extra          |+----------+---------------------+------+-----+---------+----------------+| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)         | YES  |     | NULL    |                |+----------+---------------------+------+-----+---------+----------------+2 rows in set (0.06 sec)mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      |+----+----------+2 rows in set (0.00 sec)mysql> SELECT CONCAT(id,username) AS fullname FROM test;+----------+| fullname |+----------+| 1John    || 2111     |+----------+2 rows in set (0.00 sec)mysql> SELECT CONCAT_WS('|','A','B','C');+----------------------------+| CONCAT_WS('|','A','B','C') |+----------------------------+| A|B|C                      |+----------------------------+1 row in set (0.02 sec)mysql> SELECT CONCAT_WS('-','imooc','mysql','Function');+-------------------------------------------+| CONCAT_WS('-','imooc','mysql','Function') |+-------------------------------------------+| imooc-mysql-Function                      |+-------------------------------------------+1 row in set (0.00 sec)mysql> #将数字格式化；mysql> SELECT FORMAT(12560.355632,2);+------------------------+| FORMAT(12560.355632,2) |+------------------------+| 12,560.36              |+------------------------+1 row in set (0.01 sec)mysql> #大小写转换；mysql> SELECT LOWER('MySQL');+----------------+| LOWER('MySQL') |+----------------+| mysql          |+----------------+1 row in set (0.01 sec)mysql> SELECT UPPER('MySQL');+----------------+| UPPER('MySQL') |+----------------+| MYSQL          |+----------------+1 row in set (0.00 sec)mysql> #字符串左侧或右侧字符的获取；mysql> SELECT LEFT('MySQL',2);+-----------------+| LEFT('MySQL',2) |+-----------------+| My              |+-----------------+1 row in set (0.01 sec)mysql> SELECT LOWER(LEFT('MySQL',2));+------------------------+| LOWER(LEFT('MySQL',2)) |+------------------------+| my                     |+------------------------+1 row in set (0.00 sec)mysql> SELECT RIGHT('MySQL',3);+------------------+| RIGHT('MySQL',3) |+------------------+| SQL              |+------------------+1 row in set (0.00 sec)mysql> #获取字符串的长度；mysql> SELECT LENGTH('MySQL');+-----------------+| LENGTH('MySQL') |+-----------------+|               5 |+-----------------+1 row in set (0.01 sec)mysql> SELECT LENGTH('My  SQL');+-------------------+| LENGTH('My  SQL') |+-------------------+|                 7 |+-------------------+1 row in set (0.00 sec)mysql> #删除前导和后续空格；mysql> SELECT LTRIM('   MySQL   ');                      +----------------------+| LTRIM('   MySQL   ') |+----------------------+| MySQL                |+----------------------+1 row in set (0.00 sec)mysql> SELECT LENGTH('   MySQL   ');+-----------------------+| LENGTH('   MySQL   ') |+-----------------------+|                    11 |+-----------------------+1 row in set (0.00 sec)mysql> SELECT LENGTH(LTRIM('   MySQL   '));+------------------------------+| LENGTH(LTRIM('   MySQL   ')) |+------------------------------+|                            8 |+------------------------------+1 row in set (0.00 sec)mysql> SELECT LENGTH(RTRIM('   MySQL   '));+------------------------------+| LENGTH(RTRIM('   MySQL   ')) |+------------------------------+|                            8 |+------------------------------+1 row in set (0.00 sec)mysql> SELECT LENGTH(TRIM('   MySQL   '));+-----------------------------+| LENGTH(TRIM('   MySQL   ')) |+-----------------------------+|                           5 |+-----------------------------+1 row in set (0.01 sec)mysql> #删除前导的问号；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.02 sec)

mysql> #字符替换；mysql> SELECT REPLACE('??My??SQL???','?','');+--------------------------------+| REPLACE('??My??SQL???','?','') |+--------------------------------+| MySQL                          |+--------------------------------+1 row in set (0.00 sec)mysql> SELECT REPLACE('??My??SQL???','?','!*');+----------------------------------+| REPLACE('??My??SQL???','?','!*') |+----------------------------------+| !*!*My!*!*SQL!*!*!*              |+----------------------------------+1 row in set (0.00 sec)mysql> SELECT REPLACE('??My??SQL???','??','!');+----------------------------------+| REPLACE('??My??SQL???','??','!') |+----------------------------------+| !My!SQL!?                        |+----------------------------------+1 row in set (0.00 sec)mysql> #字符串的截取；mysql> SELECT SUBSTRING('MySQL',1,2);+------------------------+| SUBSTRING('MySQL',1,2) |+------------------------+| My                     |+------------------------+1 row in set (0.00 sec)mysql> #字符串是从1开始；mysql> SELECT SUBSTRING('MySQL',3);+----------------------+| SUBSTRING('MySQL',3) |+----------------------+| SQL                  |+----------------------+1 row in set (0.00 sec)mysql> SELECT SUBSTRING('MySQL',-3);+-----------------------+| SUBSTRING('MySQL',-3) |+-----------------------+| SQL                   |+-----------------------+1 row in set (0.01 sec)mysql> #模式匹配；mysql> SELECT 'MySQL' LIKE 'M%';+-------------------+| 'MySQL' LIKE 'M%' |+-------------------+|                 1 |+-------------------+1 row in set (0.02 sec)mysql> # 1是True、mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      |+----+----------+2 rows in set (0.00 sec)mysql> SELECT * FROM test WHERE username LIKE '%o%';+----+----------+| id | username |+----+----------+|  1 | John     |+----+----------+1 row in set (0.01 sec)mysql> INSERT test VALUES(NULL,'tom%');Query OK, 1 row affected (0.04 sec)mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      ||  3 | tom%     |+----+----------+3 rows in set (0.00 sec)mysql> SELECT * FROM test WHERE username LIKE '%%%';+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      ||  3 | tom%     |+----+----------+3 rows in set (0.00 sec)mysql> #特殊情况下，用百分号定位需要特殊说明；mysql> SELECT * FROM test WHERE username LIKE '%1%%' ESCAPE '1';+----+----------+| id | username |+----+----------+|  3 | tom%     |+----+----------+1 row in set (0.00 sec)mysql> # %代表任意字符，_ 代表任意一个字符；

数值运算符和函数；

mysql> SELECT 5+9*9;+-------+| 5+9*9 |+-------+|    86 |+-------+1 row in set (0.00 sec)mysql> #进一取整；mysql> SELECT CEIL(3.01);+------------+| CEIL(3.01) |+------------+|          4 |+------------+1 row in set (0.01 sec)mysql> SELECT FLOOR(3.99);+-------------+| FLOOR(3.99) |+-------------+|           3 |+-------------+1 row in set (0.00 sec)mysql> #整数除法；mysql> SELECT 3/4;+--------+| 3/4    |+--------+| 0.7500 |+--------+1 row in set (0.00 sec)mysql> SELECT 3 DIV 4;+---------+| 3 DIV 4 |+---------+|       0 |+---------+1 row in set (0.00 sec)mysql> #取余数；mysql> SELECT 3 * 4;+-------+| 3 * 4 |+-------+|    12 |+-------+1 row in set (0.00 sec)mysql> SELECT 3 MOD 4;+---------+| 3 MOD 4 |+---------+|       3 |+---------+1 row in set (0.00 sec)mysql> SELECT 5.3 MOD 4;+-----------+| 5.3 MOD 4 |+-----------+|       1.3 |+-----------+1 row in set (0.00 sec)mysql> #幂运算mysql> SELECT POWER(3,3);+------------+| POWER(3,3) |+------------+|         27 |+------------+1 row in set (0.03 sec)mysql> #四舍五入；mysql> SELECT ROUND(3.1415926,3);+--------------------+| ROUND(3.1415926,3) |+--------------------+|              3.142 |+--------------------+1 row in set (0.00 sec)mysql> SELECT ROUND(3.1415926,0);+--------------------+| ROUND(3.1415926,0) |+--------------------+|                  3 |+--------------------+1 row in set (0.00 sec)mysql> #数字截取；mysql> SELECT TRUNCATE(3.1415926,3);+-----------------------+| TRUNCATE(3.1415926,3) |+-----------------------+|                 3.141 |+-----------------------+1 row in set (0.00 sec)mysql> SELECT TRUNCATE(3.1415926,-1);+------------------------+| TRUNCATE(3.1415926,-1) |+------------------------+|                      0 |+------------------------+1 row in set (0.00 sec)

比较运算符和函数；

mysql> SELECT 15 BETWEEN 1 AND 22;+---------------------+| 15 BETWEEN 1 AND 22 |+---------------------+|                   1 |+---------------------+1 row in set (0.02 sec)mysql> SELECT 35 BETWEEN 1 AND 22;+---------------------+| 35 BETWEEN 1 AND 22 |+---------------------+|                   0 |+---------------------+1 row in set (0.00 sec)mysql> SELECT 35 NOT BETWEEN 1 AND 22;+-------------------------+| 35 NOT BETWEEN 1 AND 22 |+-------------------------+|                       1 |+-------------------------+1 row in set (0.00 sec)mysql> SELECT 10 IN(5,10,15,20);+-------------------+| 10 IN(5,10,15,20) |+-------------------+|                 1 |+-------------------+1 row in set (0.00 sec)mysql> SELECT 13 IN(5,10,15,20);+-------------------+| 13 IN(5,10,15,20) |+-------------------+|                 0 |+-------------------+1 row in set (0.00 sec)mysql> #查看是否为空；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)mysql> INSERT test VALUE(NULL,NULL);Query OK, 1 row affected (0.07 sec)mysql> SELECT * FROM test;+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      ||  3 | tom%     ||  4 | NULL     |+----+----------+4 rows in set (0.00 sec)mysql> SELECT * FROM test WHERE username IS NULL;+----+----------+| id | username |+----+----------+|  4 | NULL     |+----+----------+1 row in set (0.00 sec)mysql> SELECT * FROM test WHERE username IS NOT NULL;+----+----------+| id | username |+----+----------+|  1 | John     ||  2 | 111      ||  3 | tom%     |+----+----------+3 rows in set (0.00 sec)

日期时间函数；

mysql> SELECT NOW();+---------------------+| NOW()               |+---------------------+| 2016-04-13 20:10:57 |+---------------------+1 row in set (0.03 sec)mysql> SELECT CURDATE();+------------+| CURDATE()  |+------------+| 2016-04-13 |+------------+1 row in set (0.01 sec)mysql> SELECT CURTIME();+-----------+| CURTIME() |+-----------+| 20:11:39  |+-----------+1 row in set (0.02 sec)mysql> #日期的增加和减少；mysql> SELECT DATE_ADD('2016-04-13',INTERVAL 365 DAY);+-----------------------------------------+| DATE_ADD('2016-04-13',INTERVAL 365 DAY) |+-----------------------------------------+| 2017-04-13                              |+-----------------------------------------+1 row in set (0.01 sec)mysql> SELECT DATE_ADD('2016-04-13',INTERVAL -365 DAY);+------------------------------------------+| DATE_ADD('2016-04-13',INTERVAL -365 DAY) |+------------------------------------------+| 2015-04-14                               |+------------------------------------------+1 row in set (0.01 sec)mysql> SELECT DATE_ADD('2016-04-13',INTERVAL 1 YEAR);+----------------------------------------+| DATE_ADD('2016-04-13',INTERVAL 1 YEAR) |+----------------------------------------+| 2017-04-13                             |+----------------------------------------+1 row in set (0.00 sec)mysql> SELECT DATE_ADD('2016-04-13',INTERVAL 3 WEEK);+----------------------------------------+| DATE_ADD('2016-04-13',INTERVAL 3 WEEK) |+----------------------------------------+| 2016-05-04                             |+----------------------------------------+1 row in set (0.00 sec)mysql> #求两个日期的差值；mysql> SELECT DATEDIFF('2013-3-12','2015-3-15');+-----------------------------------+| DATEDIFF('2013-3-12','2015-3-15') |+-----------------------------------+|                              -733 |+-----------------------------------+1 row in set (0.02 sec)mysql> #日期格式化；mysql> SELECT DATE_FORMAT('2013-3-12','%M/%D/%Y');+-------------------------------------+| DATE_FORMAT('2013-3-12','%M/%D/%Y') |+-------------------------------------+| March/12th/2013                     |+-------------------------------------+1 row in set (0.01 sec)

信息函数；

mysql>  #返回当前连接的ID；mysql> SELECT CONNECTION_ID();+-----------------+| CONNECTION_ID() |+-----------------+|               2 |+-----------------+1 row in set (0.00 sec)mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| world      |+------------+1 row in set (0.00 sec)mysql> #最后插入记录；mysql> DESC test;+----------+---------------------+------+-----+---------+----------------+| Field    | Type                | Null | Key | Default | Extra          |+----------+---------------------+------+-----+---------+----------------+| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(20)         | YES  |     | NULL    |                |+----------+---------------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                4 |+------------------+1 row in set (0.01 sec)mysql> #写入多条语句的id只能得到第一个id；mysql> INSERT test VALUES(NULL,'AA'),(NULL,'BB');Query OK, 2 rows affected (0.07 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                5 |+------------------+1 row in set (0.00 sec)mysql> SELECT USER();+----------------+| USER()         |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)mysql> SELECT VERSION();+------------+| VERSION()  |+------------+| 5.7.10-log |+------------+1 row in set (0.00 sec)

聚合函数；

mysql> #求平均，只能在表中求平均值；mysql> SELECT AVG(id) FROM test;+---------+| AVG(id) |+---------+|  3.5000 |+---------+1 row in set (0.00 sec)mysql> SELECT * FROM tdb_goods LIMIT 1;+----------+-----------------------+---------+----------+-------------+---------+------------+| goods_id | goods_name            | cate_id | brand_id | goods_price | is_show | is_saleoff |+----------+-----------------------+---------+----------+-------------+---------+------------+|        1 | R510VC 15.6英寸笔记本 |       5 |        2 |    3399.000 |       1 |          0 |+----------+-----------------------+---------+----------+-------------+---------+------------+1 row in set (0.00 sec)mysql> SELECT AVG(goods_price) AS avg_price FROM tdb_goods;+--------------+| avg_price    |+--------------+| 5654.8095238 |+--------------+1 row in set (0.00 sec)mysql> SELECT ROUND(AVG(goods_price)) AS avg_price FROM tdb_goods;+-----------+| avg_price |+-----------+|      5655 |+-----------+1 row in set (0.00 sec)mysql> SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;+-----------+| avg_price |+-----------+|   5654.81 |+-----------+1 row in set (0.00 sec)mysql> #求记录个个数；mysql> SELECT COUNT(goods_id) AS counts FROM tdb_goods;+--------+| counts |+--------+|     21 |+--------+1 row in set (0.00 sec)mysql> SELECT MAX(goods_price) AS counts FROM tdb_goods;+-----------+| counts    |+-----------+| 28888.000 |+-----------+1 row in set (0.01 sec)mysql> SELECT MIN(goods_price) AS counts FROM tdb_goods;+--------+| counts |+--------+| 99.000 |+--------+1 row in set (0.01 sec)mysql> SELECT SUM(goods_price) AS counts FROM tdb_goods;+------------+| counts     |+------------+| 118751.000 |+------------+1 row in set (0.00 sec)

加密函数；

mysql> #加密函数；mysql> SELECT MD5('admin');+----------------------------------+| MD5('admin')                     |+----------------------------------+| 21232f297a57a5a743894a0e4a801fc3 |+----------------------------------+1 row in set (0.00 sec)mysql> SELECT PASSWORD('admin');+-------------------------------------------+| PASSWORD('admin')                         |+-------------------------------------------+| *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |+-------------------------------------------+1 row in set, 1 warning (0.02 sec)mysql> #改密码；mysql> SET PASSWORD=PASSWORD('1111');Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> exitByeC:\WINDOWS\system32>mysql -u root -p1111mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.10-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SET PASSWORD=PASSWORD('1234');Query OK, 0 rows affected, 1 warning (0.00 sec)