MySQL基础

1. 基本操作

1.1 创建表：create table ...

# 创建用户表t_user
mysql> create table t_user(
    -> id bigint(20) primary key,
    -> name varchar(20),
    -> mobile varchar(20) comment '手机号',
    -> username varchar(20) unique not null,
    -> passwd varchar(100) not null,
    -> create_time datetime comment '创建时间',
    -> last_login datetime comment '上次登录时间'
    -> );
# 查看用户表（查看简要信息可以用 desc TABLE）
mysql> show full columns from t_user;
+-------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+--------------------+
| Field       | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment            |
+-------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+--------------------+
| id          | bigint(20)   | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |                    |
| name        | varchar(20)  | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |                    |
| mobile      | varchar(20)  | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references | 手机号              |
| username    | varchar(20)  | utf8_general_ci | NO   | UNI | NULL    |       | select,insert,update,references |                    |
| passwd      | varchar(100) | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references |                    |
| create_time | datetime     | NULL            | YES  |     | NULL    |       | select,insert,update,references | 创建时间            |
| last_login  | datetime     | NULL            | YES  |     | NULL    |       | select,insert,update,references | 上次登录时间         |
+-------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+--------------------+
# 创建角色表
mysql> create table t_role (
    -> id int primary key,
    -> role varchar(20) nuique
    -> );
# 查看角色表
mysql> show full columns from t_role;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11)     | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |         |
| role  | varchar(20) | utf8_general_ci | YES  | UNI | NULL    |       | select,insert,update,references |         |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

1.2 修改：alter ...

# 增加字段
mysql> alter table t_user add column role_id int after id;
mysql> alter table t_role add column remark varchar(50) after `role`;
# 调整字段
mysql> alter table t_user change column name name varchar(20) comment '姓名' after passwd;
# 删除字段
mysql> alter table t_role drop column remark;

1.3 插入：insert into ...

# 向t_user插入单条
mysql> insert into t_user (id,name,mobile,username,passwd,create_time) values (10001,'用户1','13900000001','user1','123456',now());
# 向t_user插入多条
mysql> insert into t_user (id,username,passwd) values (10002,'user2','123456'),(10003,'user3','123456');
# 向t_role插入多条
mysql> insert into t_role (id,role) values (1,'管理员'),(2,'VIP'),(3,'普通用户');

1.4 更新：update ... where

# column = value
mysql> update t_user set last_login=now() where id=10001;
# and / like / is null
mysql> update t_user set role_id = 2 where username like '%user%' and role_id is null;

1.5 查询：select ... where

select.png

# 无where
mysql> select id,username,name from t_user;
+-------+----------+---------+
| id    | username | name    |
+-------+----------+---------+
| 10001 | user1    | 用户1   |
| 10002 | user2    | NULL    |
| 10003 | user3    | NULL    |
+-------+----------+---------+
# where name is not null
mysql> select * from t_user where name is not null;
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+
| id    | role_id | mobile      | username | passwd | name    | create_time         | last_login          |
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+
| 10001 |       1 | 13900000001 | user1    | 123456 | 用户1   | 2017-05-11 04:36:43 | 2017-05-11 04:38:11 |
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+
# where role_id in (2,3)
mysql> select id,username from t_user where role_id in (2,3);
+-------+----------+
| id    | username |
+-------+----------+
| 10002 | user2    |
| 10003 | user3    |
| 10004 | user4    |
+-------+----------+
# distinct
mysql> select distinct role_id from t_user;
+---------+
| role_id |
+---------+
|       1 |
|       2 |
+---------+

1.6 删除：delete ...

mysql> delete from t_user where id = 10003;
Query OK, 1 row affected (0.01 sec)

1.7 排序：order by ... asc|desc

order by 后不可以加desc反向排序，asc或不加修饰则为正向排序。

mysql> mysql> select id,username,name from t_user order by id desc;
+-------+----------+---------+
| id    | username | name    |
+-------+----------+---------+
| 10004 | user4    | NULL    |
| 10003 | user3    | NULL    |
| 10002 | user2    | NULL    |
| 10001 | user1    | 用户1   |
+-------+----------+---------+
4 rows in set (0.00 sec)

1.8 limit

# limit n
mysql> select id,username from t_user limit 2;
+-------+----------+
| id    | username |
+-------+----------+
| 10001 | user1    |
| 10002 | user2    |
+-------+----------+
# limit m,n
mysql> select id,username from t_user limit 1,2;
+-------+----------+
| id    | username |
+-------+----------+
| 10002 | user2    |
| 10003 | user3    |
+-------+----------+
2 rows in set (0.00 sec)

1.9 分组查询 group by ... having

这里需要注意下having与where的区别：
having可以用在group by之后对分组查询的结果进行筛选;
where可以用在group by之前，对分组前的数据进行筛选;
where ... group by ... having ...这样的形式是允许的，它做了2次筛选。

# 查询各角色的用户数量
mysql> select count(1) as role_users,role_id from t_user group by role_id;
+------------+---------+
| role_users | role_id |
+------------+---------+
|          1 |       1 |
|          3 |       2 |
+------------+---------+
# group by ... having
mysql> select count(1) as role_users,role_id from t_user group by role_id having role_users > 1;
+------------+---------+
| role_users | role_id |
+------------+---------+
|          3 |       2 |
+------------+---------+
1 row in set (0.00 sec)

1.10 外键约束 FOREIGN KEY

说实话这玩意儿N年前用得比较多，尤其是喜欢在数据库里面做编程的老程序员，用上外键可以保证数据准确性和一致性。但近几年开发中倾向于使用逻辑上的外键约束，即不指定FOREIGN KEY但通过程序和代码来保证数据一致性。为什么？因为作用不大，且特别难用，特别是维护的时候，纯粹给自己找麻烦。

这里就介绍下概念吧，实在不会还可以通过图形界面工具来处理不是吗？（比如：workbench、navicat等等）

首先需要提醒的是：存在外键约束关系的两表必须都是InnoDB引擎，MyISAM不支持外键。另外，外键列的数据类型与外键表主键的数据类型需要一致。

# 创建表时添加约束
mysql> create table t_user(
    -> id bigint(20) primary key,
    -> ...
    -> role_id int,
    -> constraint `fk_role_id` foreign key (`role_id`) references `t_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> );
# 注意下最后的 ON DELETE、ON UPDATE，这是外键约束参照
# 外键约束参照（如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT）
CASCADE：从外键表更新或删除数据自动更新或删除当前表的外键约束列；
SET NULL：从外键表更新或删除数据自动将当前表的外键约束列设为NULL（需要保证此列没有指定NOT NULL）；
RESTRICT：拒绝外键表更新或删除；
NO ACTION：在MySQL中同RESTRICT。
# 查看外键约束
show create table 表名；
# 创建表以后添加外键约束
ALTER TABLE `表名` ADD CONSTRAINT `外键约束名` FOREIGN KEY ( `外键列` ) REFERENCES `外键表` ( `主键` ) ON DELETE CASCADE ON UPDATE CASCADE；
# 删除外键
ALTER TABLE `表名` DROP FOREIGN KEY `外键约束名`；

1.11 其它操作

基本操作上述内容基本够用了，当然MySQL支持的命令远不止这些，不过有些不常用，仅给出命令格式不演示了。

# 修改列属性
alter table `表名` modify `列名` varchar(20) default '无' not null;
# 修改列名及属性）
alter table `表名` change column  `列名` `新列名` 属性（可省略，表示不修改属性）;
# 修改表名
alter table 表名 rename to 新表名;
# 查看所有约束
select * from information_schema.`TABLE_CONSTRAINTS` where TABLE_SCHEMA = '数据库名' and TABLE_NAME = '表名';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+

2. 子查询

# 查询用户及其角色
mysql> select id,username,name,(select role from t_role where id = t_user.role_id) as role from t_user;
+-------+----------+---------+-----------+
| id    | username | name    | role      |
+-------+----------+---------+-----------+
| 10001 | user1    | 用户1   | 管理员    |
| 10002 | user2    | NULL    | VIP       |
| 10003 | user3    | NULL    | VIP       |
| 10004 | user4    | NULL    | VIP       |
| 10005 | user5    | NULL    | NULL      |
+-------+----------+---------+-----------+
# 查询所有角色，并统计各角色用户数量
mysql> select id,role,(select count(1) from t_user where role_id = t_role.id) as count from t_role order by id;
+----+--------------+-------+
| id | role         | count |
+----+--------------+-------+
|  1 | 管理员        |     1 |
|  2 | VIP          |     3 |
|  3 | 普通用户      |     0 |
+----+--------------+-------+
# update语句中也可以使用子查询
# 将t_user表中不存在角色的用户的角色设为3（普通用户）
mysql> update t_user set role_id = (select id from t_role order by id desc limit 1) where role_id is null;
# insert语句也支持子查询，它的格式如下
insert into 表名 (column1,column2...) select ...

3. 连接查询 join

说明：各种join查询后还可以加上where语句再筛选，以满足我们的实际需要。

# 为了效果明显，先在t_user中插入一条role_id为null的记录
mysql> insert into t_user (id,username,passwd) values (10005,'user5','123456');
# 同时将上面子查询的修改还原，方便接下来的演示
update t_user set role_id = null where id = 10005;

以下关于join的描述中A表示左表，B表示右表。

3.1 inner join：A与B的交集

join前不加任何修饰则默认为inner join

# inner join查询具有角色的用户及用户角色
mysql> select u.id,u.username,r.role from t_user as u inner join t_role as r on u.role_id = r.id;
+-------+----------+-----------+
| id    | username | role      |
+-------+----------+-----------+
| 10001 | user1    | 管理员    |
| 10002 | user2    | VIP       |
| 10003 | user3    | VIP       |
| 10004 | user4    | VIP       |
+-------+----------+-----------+

3.2 left [outer] join：A表所有+B与A重合部分

# 查询所有用户及其角色信息（无法关联的角色将为null）
mysql> select u.id,u.username,r.role from t_user as u left join t_role as r on u.role_id = r.id;
+-------+----------+-----------+
| id    | username | role      |
+-------+----------+-----------+
| 10001 | user1    | 管理员    |
| 10002 | user2    | VIP       |
| 10003 | user3    | VIP       |
| 10004 | user4    | VIP       |
| 10005 | user5    | NULL      |
+-------+----------+-----------+
# where role.id is null 查询角色不为空的用户及其角色信息
# 可以取代not in...提高sql效率，因为in不会使用索引
mysql> select u.id,u.username,r.role from t_user as u left join t_role as r on u.role_id = r.id where r.id is not null;
+-------+----------+-----------+
| id    | username | role      |
+-------+----------+-----------+
| 10001 | user1    | 管理员    |
| 10002 | user2    | VIP       |
| 10003 | user3    | VIP       |
| 10004 | user4    | VIP       |
+-------+----------+-----------+

3.3 right [outer] join：B表所有+A与B重合部分


# 查询所有角色及各角色对应的用户
mysql> select r.id,r.role,u.username from t_user as u right join t_role as r on u.role_id = r.id;
+----+--------------+----------+
| id | role         | username |
+----+--------------+----------+
|  1 | 管理员        | user1    |
|  2 | VIP          | user2    |
|  2 | VIP          | user3    |
|  2 | VIP          | user4    |
|  3 | 普通用户      | NULL     |
+----+--------------+----------+
# where user.id is null 查询无用户的角色
mysql> select r.id,r.role,u.username from t_user as u right join t_role as r on u.role_id = r.id where u.id is null;
+----+--------------+----------+
| id | role         | username |
+----+--------------+----------+
|  3 | 普通用户     | NULL     |
+----+--------------+----------+
1 row in set (0.00 sec)

3.4 full join：A与B的并集 | 在A或B中存在的记录

MySQL中原生并不支持full join，但可以利用union实现full join效果。

# 查询所有用户及角色信息
mysql> select u.id,u.username,r.role from t_user as u left join t_role as r on u.role_id = r.id                                     
    -> union
    -> select u.id,u.username,r.role from t_user as u right join t_role as r on u.role_id = r.id
    -> ;
+-------+----------+--------------+
| id    | username | role         |
+-------+----------+--------------+
| 10001 | user1    | 管理员       |
| 10002 | user2    | VIP          |
| 10003 | user3    | VIP          |
| 10004 | user4    | VIP          |
| 10005 | user5    | NULL         |
|  NULL | NULL     | 普通用户     |
+-------+----------+--------------+
# 查询无角色的用户及无用户的角色
mysql> select u.id,u.username,r.role from t_user as u left join t_role as r on u.role_id = r.id where r.id is null                                    
    -> union
    -> select u.id,u.username,r.role from t_user as u right join t_role as r on u.role_id = r.id where u.id is null
    -> ;
+-------+----------+--------------+
| id    | username | role         |
+-------+----------+--------------+
| 10005 | user5    | NULL         |
|  NULL | NULL     | 普通用户     |
+-------+----------+--------------+

3.5 cross join：交叉查询（数学上成为笛卡尔积）

这种方式用得相对较少，但特殊情况下也是特殊作用的，取决于业务需要和数据结构的设计。

mysql> select * from t_user cross join t_role;
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+----+--------------+
| id    | role_id | mobile      | username | passwd | name    | create_time         | last_login          | id | role         |
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+----+--------------+
| 10001 |       1 | 13900000001 | user1    | 123456 | 用户1   | 2017-05-11 04:36:43 | 2017-05-11 04:38:11 |  2 | VIP          |
| 10001 |       1 | 13900000001 | user1    | 123456 | 用户1   | 2017-05-11 04:36:43 | 2017-05-11 04:38:11 |  3 | 普通用户     |
| 10001 |       1 | 13900000001 | user1    | 123456 | 用户1   | 2017-05-11 04:36:43 | 2017-05-11 04:38:11 |  1 | 管理员       |
| 10002 |       2 | NULL        | user2    | 123456 | NULL    | NULL                | NULL                |  2 | VIP          |
| 10002 |       2 | NULL        | user2    | 123456 | NULL    | NULL                | NULL                |  3 | 普通用户     |
| 10002 |       2 | NULL        | user2    | 123456 | NULL    | NULL                | NULL                |  1 | 管理员       |
| 10003 |       2 | NULL        | user3    | 123456 | NULL    | NULL                | NULL                |  2 | VIP          |
| 10003 |       2 | NULL        | user3    | 123456 | NULL    | NULL                | NULL                |  3 | 普通用户     |
| 10003 |       2 | NULL        | user3    | 123456 | NULL    | NULL                | NULL                |  1 | 管理员       |
| 10004 |       2 | NULL        | user4    | 123456 | NULL    | NULL                | NULL                |  2 | VIP          |
| 10004 |       2 | NULL        | user4    | 123456 | NULL    | NULL                | NULL                |  3 | 普通用户     |
| 10004 |       2 | NULL        | user4    | 123456 | NULL    | NULL                | NULL                |  1 | 管理员       |
| 10005 |    NULL | NULL        | user5    | 123456 | NULL    | NULL                | NULL                |  2 | VIP          |
| 10005 |    NULL | NULL        | user5    | 123456 | NULL    | NULL                | NULL                |  3 | 普通用户     |
| 10005 |    NULL | NULL        | user5    | 123456 | NULL    | NULL                | NULL                |  1 | 管理员       |
+-------+---------+-------------+----------+--------+---------+---------------------+---------------------+----+--------------+
15 rows in set (0.00 sec)

3.6 使用join更新表

先思考一个问题，如何按照筛选条件（筛选条件需要使用连接查询）更新表中部分数据呢？
例如，清空存在角色、且无姓名的用户的上次登录时间。
正常的思路如下：

# 先用inner join查出存在角色且无姓名的用户，再用in根据id更新t_user表中的last_login字段
mysql> update t_user set last_login = null where id in                                                                              
    -> (
    -> select u.id from t_user as u inner join t_role as r on u.role_id = r.id where u.name is null
    -> );
ERROR 1093 (HY000): You can't specify target table 't_user' for update in FROM clause
# 很遗憾，MySQL不支持这种方式！天哪，头疼！
# 那么还有办法吗？当然可以，既然提示说不能将更新的表放在from从句中，那我们可以把连接查询结果再与需要更新的表进行join
mysql> update t_user a join
    -> (select u.id,u.username,r.role from t_user u join t_role r on u.role_id = r.id where u.name is null) b
    -> on a.id = b.id
    -> set a.last_login = null;
Query OK, 1 row affected (0.01 sec)
Rows matched: 3  Changed: 1  Warnings: 0
# 太好了，这种方式是可行的！

可见join不仅可以用在select语句中，还可以用在update及其它语句中。

3.7 使用join优化子查询

子查询会将查询到的每一条结果再依次查询匹配，数据量较大时，花费的时间时挺恐怖的。

# 子查询：查询用户及其角色 
select id,`name`,username,(select role from t_role where id = t_user.role_id) as role from t_user;
# left join：
select u.id,u.`name`,u.username,r.role from t_user as u left join t_role as r on r.id = u.role_id;

多次执行时间分别为0.001sec和0.000xsec，这里数据量太小效果不明显，数据量越大区别越大。

3.8 使用join优化聚合

现在有以下业务场景：t_pay_day表中记录了用户每天付款总和，现在要统计各个用户付款最多的日期。

# 创建表t_pay_day
mysql> create table t_pay_day(
    -> pay_date date not null,
    -> user_id bigint(20) not null,
    -> pay_amount bigint,
    -> primary key (`pay_date`,`user_id`)
    -> );
# 准备测试数据
mysql> select * from t_pay_day;
+------------+---------+------------+
| pay_date   | user_id | pay_amount |
+------------+---------+------------+
| 2017-01-01 |   10002 |        101 |
| 2017-01-02 |   10002 |        102 |
| 2017-01-03 |   10002 |        103 |
| 2017-01-04 |   10002 |        104 |
| 2017-01-05 |   10002 |        105 |
| 2017-01-06 |   10003 |        201 |
| 2017-01-07 |   10003 |        202 |
| 2017-01-08 |   10003 |        203 |
| 2017-01-09 |   10003 |        204 |
| 2017-01-10 |   10003 |        205 |
| 2017-01-11 |   10004 |        301 |
| 2017-01-12 |   10004 |        302 |
| 2017-01-13 |   10004 |        303 |
| 2017-01-14 |   10004 |        304 |
| 2017-01-15 |   10004 |        305 |
| 2017-01-16 |   10005 |        401 |
| 2017-01-17 |   10005 |        402 |
| 2017-01-18 |   10005 |        403 |
| 2017-01-19 |   10005 |        404 |
| 2017-01-20 |   10005 |        405 |
+------------+---------+------------+
20 rows in set (0.00 sec)

子查询中使用max()

# 子查询中使用max()
mysql> select u.id,u.username,u.name,p.pay_date,p.pay_amount from t_user as u
    -> left join t_pay_day as p on u.id = p.user_id
    -> where pay_amount =  (select max(pay_amount) from t_pay_day where user_id = u.id);
+-------+----------+------+------------+------------+
| id    | username | name | pay_date   | pay_amount |
+-------+----------+------+------------+------------+
| 10002 | user2    | NULL | 2017-01-05 |        105 |
| 10003 | user3    | NULL | 2017-01-10 |        205 |
| 10004 | user4    | NULL | 2017-01-15 |        305 |
| 10005 | user5    | NULL | 2017-01-20 |        405 |
+-------+----------+------+------------+------------+
4 rows in set (0.00 sec)

使用join优化聚合子查询

# 使用join优化聚合子查询
mysql> select u.id,u.username,u.name,p.pay_date,p.pay_amount from t_user as u
    -> left join t_pay_day as p on p.user_id = u.id
    -> left join t_pay_day as pp on pp.user_id = p.user_id
    -> group by u.id,p.pay_date,p.pay_amount
    -> having p.pay_amount = max(pp.pay_amount);
+-------+----------+------+------------+------------+
| id    | username | name | pay_date   | pay_amount |
+-------+----------+------+------------+------------+
| 10002 | user2    | NULL | 2017-01-05 |        105 |
| 10003 | user3    | NULL | 2017-01-10 |        205 |
| 10004 | user4    | NULL | 2017-01-15 |        305 |
| 10005 | user5    | NULL | 2017-01-20 |        405 |
+-------+----------+------+------------+------------+
4 rows in set (0.00 sec)

3.9 使用join实现分组选择

现有以下业务场景：从用户支付记录中查询每个人支付金额最大的2天。
1）多次查询

# 按照用户遍历查询
mysql> select u.id,u.username,u.name,p.pay_date,p.pay_amount from t_user as u
    -> right join t_pay_day as p
    -> on p.user_id = u.id
    -> where u.id = 10002
    -> order by p.pay_amount
    -> limit 2;
+-------+----------+------+------------+------------+
| id    | username | name | pay_date   | pay_amount |
+-------+----------+------+------------+------------+
| 10002 | user2    | NULL | 2017-01-05 |        105 |
| 10002 | user2    | NULL | 2017-01-04 |        104 |
+-------+----------+------+------------+------------+
2 rows in set (0.00 sec)

...有多少个用户就需要查询多少次...

这种方式缺点很多：
需要首先获取用户id；
多次执行，不符合批量处理的原则，分组（用户）非常多的时候很恐怖；
增加应用程序与数据库连接交互次数；
增加了网络IO。

2）聚合查询+join

# 思路：某用户的支付记录中大于等于此记录中金额的记录数为2即表示当前金额从大到小排序为2
# 1.利用group by + count()查询每条支付记录对应的该用户的所有支付记录中金额>=当前记录的数量，记为pay_count
# 2.使用join将1的结果与t_user连接，筛选出满足条件（pay_count<=2）的记录
mysql> select u.id,u.username,a.pay_date,a.pay_amount from
    -> (select p.user_id,p.pay_date,pay_amount,
    -> (
    -> select count(1) from t_pay_day where user_id = p.user_id and p.pay_amount<=pay_amount
    -> ) as pay_count
    -> from t_pay_day as p 
    -> group by p.user_id,p.pay_date,pay_amount
    -> ) as a 
    -> join t_user as u on u.id = a.user_id 
    -> where pay_count <=2;
+-------+----------+------------+------------+
| id    | username | pay_date   | pay_amount |
+-------+----------+------------+------------+
| 10002 | user2    | 2017-01-05 |        105 |
| 10002 | user2    | 2017-01-04 |        104 |
| 10003 | user3    | 2017-01-10 |        205 |
| 10003 | user3    | 2017-01-09 |        204 |
| 10004 | user4    | 2017-01-15 |        305 |
| 10004 | user4    | 2017-01-14 |        304 |
| 10005 | user5    | 2017-01-20 |        405 |
| 10005 | user5    | 2017-01-19 |        404 |
+-------+----------+------------+------------+
8 rows in set (0.00 sec)

4. 运算符与函数

字符函数

# length():   计算字符串长度（中文：3；数字、字母等：1）
mysql> select length('中文');
+------------------+
| length('中文')   |
+------------------+
|                6 |
+------------------+
# char_length()：计算字符串长度（中文也是1个字符）
mysql> select char_length('中文');
+-----------------------+
| char_length('中文')   |
+-----------------------+
|                     2 |
+-----------------------+
# concat()：连接字符串
mysql> select concat('a','b','c');
+---------------------+
| concat('a','b','c') |
+---------------------+
| abc                 |
+---------------------+
# concat_ws()：使用分隔符连接字符串
mysql> select concat_ws('-','a','b','c','d');
+--------------------------------+
| concat_ws('-','a','b','c','d') |
+--------------------------------+
| a-b-c-d                        |
+--------------------------------+
# format()：数字格式化
mysql> select format(1234.5678,3);
+---------------------+
| format(1234.5678,3) |
+---------------------+
| 1,234.568           |
+---------------------+
# lower()：转小写字母
mysql> select lower("ABC");
+--------------+
| lower("ABC") |
+--------------+
| abc          |
+--------------+
# upper()：转大些字母
mysql> select upper("abc");
+--------------+
| upper("abc") |
+--------------+
| ABC          |
+--------------+
# left()：取左侧字符
mysql> select left('abcde',2);
+-----------------+
| left('abcde',2) |
+-----------------+
| ab              |
+-----------------+
# right()：取右侧字符
mysql> select right('abcde',2);
+------------------+
| right('abcde',2) |
+------------------+
| de               |
+------------------+
# length()：字符串长度
# ltrim()：删除左侧空格
# rtrim()：删除右侧空格
# trim()：删除两侧空格
mysql> select concat('***',trim('  abc  '),'***');
+-------------------------------------+
| concat('***',trim('  abc  '),'***') |
+-------------------------------------+
| ***abc***                           |
+-------------------------------------+
# substring()：截取字符串
mysql> select substring('abcdefg',2);
+------------------------+
| substring('abcdefg',2) |
+------------------------+
| bcdefg                 |
+------------------------+
mysql> select substring('abcdefg',2,3);
+--------------------------+
| substring('abcdefg',2,3) |
+--------------------------+
| bcd                      |
+--------------------------+
# substring_index(str,delim,count)：按分隔符截取字符串
mysql> select substring_index('192.168.1.110','.',1);
+----------------------------------------+
| substring_index('192.168.1.110','.',1) |
+----------------------------------------+
| 192                                    |
+----------------------------------------+
mysql> select substring_index('192.168.1.110','.',2);
+----------------------------------------+
| substring_index('192.168.1.110','.',2) |
+----------------------------------------+
| 192.168                                |
+----------------------------------------+
mysql> select substring_index('192.168.1.110','.',-1);
+-----------------------------------------+
| substring_index('192.168.1.110','.',-1) |
+-----------------------------------------+
| 110                                     |
+-----------------------------------------+
mysql> select substring_index('192.168.1.110','.',-2);
+-----------------------------------------+
| substring_index('192.168.1.110','.',-2) |
+-----------------------------------------+
| 1.110                                   |
+-----------------------------------------+
# replace()：字符替换
mysql> select replace('a-b-c','-','**');
+---------------------------+
| replace('a-b-c','-','**') |
+---------------------------+
| a**b**c                   |
+---------------------------+

数值运算

# ceil()：进一取整
+-----------+
| ceil(1.2) |
+-----------+
|         2 |
+-----------+
# floor()：舍一取整
mysql> select floor(1.8);
+------------+
| floor(1.8) |
+------------+
|          1 |
+------------+
# round(浮点数,小数位)：四舍五入
mysql> select round(1234.5678,2);
+--------------------+
| round(1234.5678,2) |
+--------------------+
|            1234.57 |
+--------------------+
# power()：幂运算
mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
|          8 |
+------------+
# DIV：整数除法（区别于／）
mysql> select 5 div 3;
+---------+
| 5 div 3 |
+---------+
|       1 |
+---------+
mysql> select 5/3;
+--------+
| 5/3    |
+--------+
| 1.6667 |
+--------+
# truncate()：数字截断
mysql> select truncate(1234.5678,2);
+-----------------------+
| truncate(1234.5678,2) |
+-----------------------+
|               1234.56 |
+-----------------------+
mysql> select truncate(1234.5678,-1);
+------------------------+
| truncate(1234.5678,-1) |
+------------------------+
|                   1230 |
+------------------------+

日期时间

# now()：当前日期和时间（yyyy-mm-dd HH:MM:ss）
# curdate()：当前日期（yyyy-mm-dd）
# curtime()：当前时间（HH:MM:ss）
# date_add()：日期变化（year、month、day、week...）
mysql> select date_add('2016-07-08',interval -365 day);
+------------------------------------------+
| date_add('2016-07-08',interval -365 day) |
+------------------------------------------+
| 2015-07-09                               |
+------------------------------------------+
# datediff()：日期差值
mysql> select datediff('2015-07-09','2016-07-08');
+-------------------------------------+
| datediff('2015-07-09','2016-07-08') |
+-------------------------------------+
|                                -365 |
+-------------------------------------+
# date_format()：日期格式化
mysql> select date_format('2017-01-02','%Y%m%d');
+------------------------------------+
| date_format('2017-01-02','%Y%m%d') |
+------------------------------------+
| 20170102                           |
+------------------------------------+

元数据

# connection_id()：当前连接ID
# database()：当前数据库
# last_insert_id()：最后插入记录ID
# user()：当前用户
# version()：版本信息
mysql> select concat_ws(':',connection_id(),database(),last_insert_id(),user(),version());
+-----------------------------------------------------------------------------+
| concat_ws(':',connection_id(),database(),last_insert_id(),user(),version()) |
+-----------------------------------------------------------------------------+
| 10:test:0:root@localhost:5.7.17                                             |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

聚合函数

# avg()：平均值
# count()：计数
# max()：最大值
# min()：最小值
# sum()：求和

其它函数

# least()：计算列表中最小值
mysql> select least(123,456);
+----------------+
| least(123,456) |
+----------------+
|            123 |
+----------------+
# greatest()：计算列表中最大值
mysql> select greatest('AA','BB','CC');
+--------------------------+
| greatest('AA','BB','CC') |
+--------------------------+
| CC                       |
+--------------------------+

5. 自定义函数

这个用得也不多，特殊需要基本都在应用程序处理了，偷个懒有空再来补充吧...

6. 存储过程

procedure某些情况下有神奇的效果，它可以处理业务逻辑，比如写一个登录的存储过程完成以下动作：
1.检查用户名是否存在；
2.若用户名存在，检查密码是否正确（有的网站统一提示用户名或密码错误，估计是为了节省一次网络IO，强烈鄙视这种投机取巧的方法！）
3.记录登录信息；
4.此过程中可能还会增加访问限制，比如一小时内密码错误几次会锁定等等。
这些动作都放在应用程序中的话会增加与数据库的交互次数。
但是存储过程用多了或者经常做变动的话，维护起来很抓狂，所以还是尽量不用。

7. 索引

索引包括普通索引（index）、主键索引（primary key）、唯一索引（unique）、全文索引（fulltext）;
索引可以为单个列设置，还可以为多个列设置（多列索引）。

查看索引

show index|keys from 表名;

删除索引

# 这种方式能够删除所有类型的索引
alter table 表名 drop index 索引名;

添加索引

# 添加普通索引
alter table 表名 add index 索引名(`索引列`);
# 添加主键索引（主键约束）
alter table 表名 add primary key(`主键列`);
# 添加唯一索引（唯一约束）
alter table 表名 add unique(`列名`);
# 添加全文索引
alter table 表名 add fulltext(`列名`);
# 添加多列索引
alter table 表名 add index 索引名(`列名1`,`列名2`...);

最后编辑于：2018.01.23 10:12:42

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 211,423评论 6赞 491
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,147评论 2赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,019评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,443评论 1赞 283
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,535评论 6赞 385
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,798评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,941评论 3赞 407
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,704评论 0赞 266
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,152评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,494评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,629评论 1赞 340
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,295评论 4赞 329
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,901评论 3赞 313
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,742评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,978评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,333评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,499评论 2赞 348