标签：mysql

子查询是将一个查询语句嵌套在另一个查询语句中

内层查询语句的查询结果，可以为外层查询语句提供查询条件

因为在特定情况下，一个查询语句的条件需要另一个查询语句来获取

参考表：employee

参考表：department

带IN关键字的子查询

mysql> SELECT * FROM employee
    -> WHERE d_id IN
    -> (SELECT d_id FROM department);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海淀区         |
|    2 | 1001 | 李四   |   24 | 女   | 北京市昌平区         |
|    3 | 1002 | 王五   |   25 | 男   | 湖南长沙市           |
+------+------+--------+------+------+--------------------+
 rows in set (0.00 sec)

此处首先查询出department表中所有d_id字段的信息，并将结果作为条件

接着查询employee表中以d_id为条件的所有字段信息

NOT IN的效果与上面刚好相反

带比较运算符的子查询

mysql> SELECT d_id, d_name FROM department
    -> WHERE d_id!=
    -> (SELECT d_id FROM employee WHERE age=24);
+------+-----------+
| d_id | d_name    |
+------+-----------+
| 1002 | 生产部    |
| 1003 | 销售部    |
+------+-----------+
 rows in set (0.00 sec)

这里查询出了哪些部门没有年龄为24岁的员工，看起来有点复杂

此外，运算符还有很多，这里不再赘述

带EXISTS关键字的子查询

EXISTS关键字表示存在。使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，如果内层查询语句查询到满足条件的记录，就返回true，否则返回false

当返回的值为true时，外层查询语句将进行查询，否则不进行查询

mysql> SELECT * FROM employee
    -> WHERE EXISTS
    -> (SELECT d_name FROM department WHERE d_id=1004);
Empty set (0.00 sec)

此处内层循环并没有查询到满足条件的结果，因此返回false，外层查询不执行

NOT EXISTS刚好与之相反

当然，EXISTS关键字可以与其他的查询条件一起使用

条件表达式与EXISTS关键字之间用AND或者OR来连接

mysql> SELECT * FROM employee
    -> WHERE age>24 AND EXISTS
    -> (SELECT d_name FROM department WHERE d_id=1003);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海淀区         |
|    3 | 1002 | 王五   |   25 | 男   | 湖南长沙市           |
+------+------+--------+------+------+--------------------+
 rows in set (0.00 sec)

带ANY关键字的子查询

ANY关键字表示满足其中任一条件

mysql> SELECT * FROM employee
    -> WHERE d_id!=ANY
    -> (SELECT d_id FROM department);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海淀区         |
|    2 | 1001 | 李四   |   24 | 女   | 北京市昌平区         |
|    3 | 1002 | 王五   |   25 | 男   | 湖南长沙市           |
|    4 | 1004 | Aric   |   15 | 男   | England            |
+------+------+--------+------+------+--------------------+
 rows in set (0.00 sec)

带ALL关键字的子查询

ALL关键字表示满足其中所有条件

mysql> SELECT * FROM employee
    -> WHERE d_id>=ALL
    -> (SELECT d_id FROM department);
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    4 | 1004 | Aric |   15 | 男   | England  |
+------+------+------+------+------+----------+
 row in set (0.00 sec)

暂时不明白这两条语句是什么意思，到时候再补上

UNION合并查询结果

mysql> SELECT d_id FROM employee
    -> UNION
    -> SELECT d_id FROM department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1004 |
| 1003 |
+------+
 rows in set (0.00 sec)

合并比较好理解，也就是将多个查询的结果合并在一起，然后去除其中的重复记录。

如果想保存重复记录可以使用UNION ALL语句。

创建触发器

创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW 执行语句

其中，触发器名参数指要创建的触发器的名字

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());
Query OK, 0 rows affected (0.09 sec)

上面创建了一个名为trig1的触发器，一旦在work中有插入动作，就会自动往time表里插入当前时间

创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突

为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||

当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> INSERT INTO time VALUES(NOW());
    -> INSERT INTO time VALUES(NOW());
    -> END
    -> ||
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;

上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

就会执行BEGIN和END中的语句，接着使用||结束

最后使用DELIMITER ; 将结束符号还原

查看触发器

SHOW TRIGGERS语句查看触发器信息

mysql> SHOW TRIGGERSG;
*************************** 1. row ***************************
             Trigger: trig1
               Event: INSERT
               Table: work
           Statement: INSERT INTO time VALUES(NOW())
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

结果会显示所有触发器的基本信息

tips：SHOW TRIGGERS语句无法查询指定的触发器

在triggers表中查看触发器信息

mysql> SELECT * FROM information_schema.triggersG
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: person
              TRIGGER_NAME: trig1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: person
        EVENT_OBJECT_TABLE: work
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: INSERT INTO time VALUES(NOW())

结果显示了所有触发器的详细信息，同时，该方法可以查询制定触发器的详细信息

mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='trig1'G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: person
              TRIGGER_NAME: trig1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: person
        EVENT_OBJECT_TABLE: work

tips：所有触发器信息都存储在information_schema数据库下的triggers表中

可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询

删除触发器

mysql> DROP TRIGGER trig1;
Query OK, 0 rows affected (0.04 sec)

删除触发器之后最好使用上面的方法查看一遍

同时，也可以使用database.trig来指定某个数据库中的触发器

tips：如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作

字符串操作在程序设计中是非常重要的组成部分，而MySQL数据库中的字符串操作却相当简单

需要注意的是：下面所有的函数只是将修改后的结果当查询返回，均不对原数据做出改变

选出指定数量字符

mysql> SELECT RIGHT('nihao',3);
+------------------+
| RIGHT('nihao',3) |
+------------------+
| hao              |
+------------------+
 row in set (0.00 sec)

这里的RIGHT()函数代表从字符串中选定从右往左数的三个字符

与此类似，还有LEFT()函数

SUBSTRING_INDEX截取字符串

mysql> SELECT SUBSTRING_INDEX('HH,MM,SS',',',2);
+-----------------------------------+
| SUBSTRING_INDEX('HH,MM,SS',',',2) |
+-----------------------------------+
| HH,MM                             |
+-----------------------------------+
 row in set (0.00 sec)

此函数内部的第一个参数代表需要截取的内容，第二个参数代表按什么截取

最后一个是截取到第几个，1是截取到第一个逗号，2是截取到第二个

SUBSTRING截取字符串

mysql> SELECT SUBSTRING('helloworld',1,5);
+-----------------------------+
| SUBSTRING('helloworld',1,5) |
+-----------------------------+
| hello                       |
+-----------------------------+
 row in set (0.00 sec)

此处截取字符串中1～5的内容

UPPER字符串改大写

mysql> SELECT UPPER('hello');
+----------------+
| UPPER('hello') |
+----------------+
| HELLO          |
+----------------+
 row in set (0.00 sec)

LOWER字符串改小写

mysql> SELECT LOWER('HELLO');
+----------------+
| LOWER('HELLO') |
+----------------+
| hello          |
+----------------+
 row in set (0.00 sec)

REVERSE反转字符串

mysql> SELECT REVERSE('hello');
+------------------+
| REVERSE('hello') |
+------------------+
| olleh            |
+------------------+
 row in set (0.00 sec)

LTRIM清除左边多余空格

mysql> SELECT LTRIM('  hello     ');
+-----------------------+
| LTRIM('  hello     ') |
+-----------------------+
| hello                 |
+-----------------------+
 row in set (0.00 sec)

此外还有RTRIM清除右边空格，TRIM清除两边全部空格

LENGTH返回字符串中的字符数量

mysql> SELECT LENGTH('helo');
+----------------+
| LENGTH('helo') |
+----------------+
|              4 |
+----------------+
 row in set (0.00 sec)

连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据

当不同的表中存在表示相同意义的字段时，可以通过该字段来连接这几张表

参考表：employee

参考表：department

可以看到，上面两张表都有同一个字段d_id

当两张表含有相同意义的字段（可以不同名）时就可以进行连接查询

内连接查询

mysql> SELECT num, name, employee.d_id, sex, d_name, function
    -> FROM employee, department
    -> WHERE employee.d_id=department.d_id;
+------+--------+------+------+-----------+--------------+
| num  | name   | d_id | sex  | d_name    | function     |
+------+--------+------+------+-----------+--------------+
|    1 | 张三   | 1001 | 男   | 科技部    | 研发产品     |
|    2 | 李四   | 1001 | 女   | 科技部    | 研发产品     |
|    3 | 王五   | 1002 | 男   | 生产部    | 生产产品     |
+------+--------+------+------+-----------+--------------+
 rows in set (0.00 sec)

内连接查询只会查询完全匹配的结果，此处使用d_id字段进行连接

下面的表中也同样使用这一字段

外连接查询

外查询也需要通过指定字段来进行连接，当该字段取值相等时，可以查询出该记录

而且，该字段取值不相等的记录也可以查询出来

外连接查询包括左连接查询和右连接查询

左连接查询

mysql> SELECT num, name, employee.d_id, d_name, function
    -> FROM employee LEFT JOIN department
    -> ON employee.d_id=department.d_id;
+------+--------+------+-----------+--------------+
| num  | name   | d_id | d_name    | function     |
+------+--------+------+-----------+--------------+
|    1 | 张三   | 1001 | 科技部    | 研发产品     |
|    2 | 李四   | 1001 | 科技部    | 研发产品     |
|    3 | 王五   | 1002 | 生产部    | 生产产品     |
|    4 | Aric   | 1004 | NULL      | NULL         |
+------+--------+------+-----------+--------------+
 rows in set (0.00 sec)

此处不仅查询出了两表中d_id字段相匹配的信息

并且通过LEFT JOIN查询出了employee表中所有指定字段的信息

由于Aric没有对应d_name和function信息，所以显示null

右连接查询

mysql> SELECT num, name, employee.d_id, d_name, function
    -> FROM employee RIGHT JOIN department
    -> ON employee.d_id=department.d_id;
+------+--------+------+-----------+--------------+
| num  | name   | d_id | d_name    | function     |
+------+--------+------+-----------+--------------+
|    1 | 张三   | 1001 | 科技部    | 研发产品     |
|    2 | 李四   | 1001 | 科技部    | 研发产品     |
|    3 | 王五   | 1002 | 生产部    | 生产产品     |
| NULL | NULL   | NULL | 销售部    | 策划销售     |
+------+--------+------+-----------+--------------+
 rows in set (0.00 sec)

与上面相反，这里查询出了匹配的信息和department表中的所有指定字段的信息

但是由于employee表中部分字段没有对应，因此最后一行记录有显示NULL

复合条件连接查询

mysql> SELECT num, name, employee.d_id, sex, age, address
    -> FROM employee, department
    -> WHERE employee.d_id=department.d_id
    -> AND age>=25;
+------+--------+------+------+------+-------------+
| num  | name   | d_id | sex  | age  | address     |
+------+--------+------+------+------+-------------+
|    1 | 张三   | 1001 | 男   |   26 | 3号楼5层    |
|    3 | 王五   | 1002 | 男   |   25 | 5号楼1层    |
+------+--------+------+------+------+-------------+
 rows in set (0.00 sec)

复合条件连接查询是在进行连接查询的时候加入限制条件，此处的age>=25便是

通常情况下，限制条件越多，查询越精确，限制条件可用AND累加

此外，还可以用复合条件进行ORDER BY 排序

tips：连接查询中使用最多的是内连接查询，而外连接查询使用频率较低

参考表

count()用来统计记录的条数

mysql> select count(*) from score;
mysql> select count(*) from score where stu_id=901;

sum()求和函数

mysql> select sum(grade) from score;
mysql> select id,sum(grade) from score where stu_id=901;

avg()求平均值函数

mysql> select avg(grade) from score where c_name='计算机';
mysql> select c_name,avg(grade) from score group by c_name;

max()求最大值函数

mysql> select c_name,max(grade) from score where c_name='英语';
mysql> select c_name,max(grade) from score group by c_name;

min()求最小值函数

mysql> select c_name,min(grade) from score where c_name='中文';
mysql> select c_name,min(grade) from score group by c_name;

Concat拼接函数

mysql> select Concat(c_name, '(', stu_id, ')')
    -> from score order by stu_id;

-- 创建库
CREATE TABLE `rate` (
 `uname` VARCHAR (300),
 `object` VARCHAR (300),
 `score` VARCHAR (300)
); 

-- 插入数据
INSERT INTO test.rate (uname, object, score)
 VALUES('aaa', 'chinese', '67'),
 ('aaa', 'math', '89'),
 ('aaa', 'physical', '89'),
 ('bbb', 'chinese', '67'),
 ('bbb', 'math', '75'),
 ('bbb', 'physical', '89');

-- 查询
SELECT DISTINCT uname AS '姓名',
SUM(CASE object
WHEN 'chinese' THEN score END)  AS '语文',
SUM(CASE object
WHEN 'math' THEN score END)  AS '数学',
SUM(CASE object
WHEN 'physical' THEN score END)  AS '物理' FROM rate GROUP BY uname;

前端时间跟一个DB相关的项目，alanc反馈有一个查询，使用索引比不使用索引慢很多倍，有点毁三观。所以跟进了一下，用explain，看了看2个查询不同的结果。

不用索引的查询的时候结果如下，实际查询中速度比较块。

mysql> explain select * from rosterusers limit 10000,3 ; 

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+ 

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+ 

| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010066 | | 

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+ 

而使用索引order by的查询结果如下，速度反而慢的惊人。

mysql> explain select * from rosterusers order by username limit 10000,3 ; 

+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+ 

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 

+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+ 

| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010087 | Using filesort | 

+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+ 

区别在于，使用索引查询的Extra变成了，Using filesort。居然用了使用外部文件进行排序。这个当然慢了。

但数据表上在username，的确是有索引的。怎么会反而要Using filesort?

看了一下数据表定义。是一个开源聊天服务器ejabberd的一张表。初看以为主键i_rosteru_user_jid是username，和jid的联合索引，那么使用order by username时应该是可以使用到索引才对呀？

CREATE TABLE `rosterusers` ( 
`username` varchar(250) NOT NULL, 
`jid` varchar(250) NOT NULL, 
UNIQUE KEY `i_rosteru_user_jid` (`username`(75),`jid`(75)), 
KEY `i_rosteru_jid` (`jid`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

仔细检查突然发现其主键定义，不是定义的完整的主键名称，而跟了一个75的长度描述，稍稍一愣，原来用的是前缀索引，而不是整个字段都是索引。（我的记忆里面InnoDB还不支持这玩意，估计是4.0后什么版本加入的），前缀索引就是将数据字段中前面N个字节作为索引的一种方式。

发现了这个问题后，我们开始怀疑慢查询和这个索引有关，前缀索引的主要用途在于有时字段过程，而MySQL支持的很多索引长度是有限制的。

首先不带order by 的limit 这种查询，本质可能还是和主键相关的，因为MySQL 的INNODB的操作实际都是依靠主键的（即使你没有建立，系统也会有一个默认的），而limit这种查询，使用主键是可以加快速度，（explain返回的rows 应该是一个参考值），虽然我没有看见什么文档明确的说明过这个问题，但从不带order by 的limit 查询的返回结果基本可以证明这点。

但当我们使用order by username的时候，由于希望使用的是username的排序，而不是username（75）的排序，但实际索引是前缀索引，不是完整字段的索引。所以反而导致了order by的时候完全无法利用索引了。（我在SQL语句里面增加强制使用索引i_rosteru_user_jid也不起作用）。而其实使用中，表中的字段username 连75个都用不到，何况定义的250的长度。完全是自己折腾导致的麻烦。由于这是其他产品的表格，我们无法更改，暂时只能先将就用不不带排序的查询讲究。

总结：

• 前缀索引，并不是一个万能药，他的确可以帮助我们对一个写过长的字段上建立索引。但也会导致排序（order by ，group by）查询上都是无法使用前缀索引的。

• 任何时候，对于DB Schema定义，合理的规划自己的字段长度，字段类型都是首要的事情。