字符函数，时间和日期函数

SQL语句分类
数据查询语言（Data Query Language, DQL）
负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。
数据定义语言 (Data Definition Language, DDL)
负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成
数据操纵语言（Data Manipulation Language, DML）
负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。
数据控制语言 (Data Control Language)
它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成

字符函数
mysql> select name,email,length(email),char_length(name) from employees where name='杨金凤';
+-----------+------------------------+---------------+-------------------+
| name      | email                  | length(email) | char_length(name) |
+-----------+------------------------+---------------+-------------------+
| 杨金凤    | yangjinfeng@tarena.com |            22 |                 3 |
+-----------+------------------------+---------------+-------------------+
1 row in set (0.00 sec)

mysql> select name,email,length(email),length(name)
    -> from
    -> employees where
    -> name='杨金凤';
+-----------+------------------------+---------------+--------------+
| name      | email                  | length(email) | length(name) |
+-----------+------------------------+---------------+--------------+
| 杨金凤    | yangjinfeng@tarena.com |            22 |            9 |
+-----------+------------------------+---------------+--------------+
1 row in set (0.00 sec)

mysql> select concat
    -> (dept_id,'-',dept_name) as 部门
    -> from
    -> departments;
+-------------+
| 部门        |
+-------------+
| 1-人事部    |
| 2-财务部    |
| 3-运维部    |
| 4-开发部    |
| 5-测试部    |
| 6-市场部    |
| 7-销售部    |
| 8-法务部    |
+-------------+
8 rows in set (0.00 sec)

mysql> select name,email
    -> from
    -> employees
    -> where
    -> name like '李%';
+-----------+----------------------+
| name      | email                |
+-----------+----------------------+
| 李玉英    | liyuying@tedu.cn     |
| 李平      | liping@tedu.cn       |
| 李建华    | lijianhua@tarena.com |
| 李莹      | liying@tedu.cn       |
| 李柳      | liliu@tarena.com     |
| 李慧      | lihui@tarena.com     |
| 李静      | lijing@tarena.com    |
| 李瑞      | lirui@tarena.com     |
+-----------+----------------------+
8 rows in set (0.00 sec)

mysql> select name,upper(email) from  employees where name like '李%'; 显示大写
+-----------+----------------------+
| name      | upper(email)         |
+-----------+----------------------+
| 李玉英    | LIYUYING@TEDU.CN     |
| 李平      | LIPING@TEDU.CN       |
| 李建华    | LIJIANHUA@TARENA.COM |
| 李莹      | LIYING@TEDU.CN       |
| 李柳      | LILIU@TARENA.COM     |
| 李慧      | LIHUI@TARENA.COM     |
| 李静      | LIJING@TARENA.COM    |
| 李瑞      | LIRUI@TARENA.COM     |
+-----------+----------------------+
8 rows in set (0.00 sec)

mysql> select lower('HELLO');  显示小写
+----------------+
| lower('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

mysql> select
    -> substr('hello wrold',7); 
+-------------------------+
| substr('hello wrold',7) |
+-------------------------+
| wrold                   |
+-------------------------+
1 row in set (0.00 sec)

mysql> select substr('hello world',7,4)
    -> ;
+---------------------------+
| substr('hello world',7,4) |
+---------------------------+
| worl                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> select
    -> instr('hello world','or');
+---------------------------+
| instr('hello world','or') |
+---------------------------+
|                         8 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select trim('       hello world        ');
+------------------------------------+
| trim('       hello world        ') |
+------------------------------------+
| hello world                        |
+------------------------------------+
1 row in set (0.00 sec)
数学函数
绝对值
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)
圆周率（默认六位）
mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
取余
mysql> select
    -> mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
最接近当前数的整数（上）
mysql> select
    -> ceil(-10.999);
+---------------+
| ceil(-10.999) |
+---------------+
|           -10 |
+---------------+
1 row in set (0.00 sec)
最接近当前数的整数（下）
mysql> select floor(10.1);
+-------------+
| floor(10.1) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)
取整数
mysql> select round(10.444);
+---------------+
| round(10.444) |
+---------------+
|            10 |
+---------------+
1 row in set (0.00 sec)
保留3位小数，逗号后数字决定
mysql> select round(10.444,3);
+-----------------+
| round(10.444,3) |
+-----------------+
|          10.444 |
+-----------------+
1 row in set (0.00 sec)

时间和日期函数
mysql> select curdate();        显示当前时间
+------------+
| curdate()  |
+------------+
| 2021-07-07 |
+------------+
1 row in set (0.00 sec)
mysql> select curdate()+0;  显示换成数字格式
+-------------+
| curdate()+0 |
+-------------+
|    20210707 |
+-------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp();  1970-01-01 0:0到当前经过的秒数
+------------------+
| unix_timestamp() |
+------------------+
|       1625629221 |
+------------------+
1 row in set (0.00 sec)
mysql> select year(20051001); 返回年份
+----------------+
| year(20051001) |
+----------------+
|           2005 |
+----------------+
1 row in set (0.00 sec)

mysql> select year('2005-10-01');
+--------------------+
| year('2005-10-01') |
+--------------------+
|               2005 |
+--------------------+
1 row in set (0.00 sec)
返回月份
mysql> select month(20051010);
+-----------------+
| month(20051010) |
+-----------------+
|              10 |
+-----------------+
1 row in set (0.00 sec)

mysql> select monthname(10051010);
+---------------------+
| monthname(10051010) |
+---------------------+
| October             |
+---------------------+
1 row in set (0.00 sec)
返回天
mysql> select day(20051010);
+---------------+
| day(20051010) |
+---------------+
|            10 |
+---------------+
1 row in set (0.00 sec)

mysql> select dayname(20210707);返回周几
+-------------------+
| dayname(20210707) |
+-------------------+
| Wednesday         |
+-------------------+
1 row in set (0.00 sec)
查看当前周是全年第几周
mysql> select          week(curdate());
+-----------------+
| week(curdate()) |
+-----------------+
|              27 |
+-----------------+
1 row in set (0.00 sec)
查看今天是全年第几天
mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
|                  188 |
+----------------------+
1 row in set (0.00 sec)
显示当前时间
mysql> select
    -> curdate(),now(),year(20210707),month(20210707),day(20210707);
+------------+---------------------+----------------+-----------------+---------------+
| curdate()  | now()               | year(20210707) | month(20210707) | day(20210707) |
+------------+---------------------+----------------+-----------------+---------------+
| 2021-07-07 | 2021-07-07 11:56:24 |           2021 |               7 |             7 |
+------------+---------------------+----------------+-----------------+---------------+
1 row in set (0.00 sec)