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: 0

mysql> 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> exit
Bye

C:\WINDOWS\system32>mysql -u root -p1111
mysql: [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 3
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.

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)