MySql子查询、多表查询、索引、视图、存储过程、触发器

接上篇 基础操作
此篇一样仅挑选自己常用的来讲（基础入门
并且所说的知识点并不完整

子查询

子查询例子

已知两表，人员信息表c，工资表w。

mysql> select * from c;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  2 | Ajie | NULL |
|  3 | ajie |   35 |
|  4 | ahua |   41 |
|  5 | ago  |   21 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select * from w;
+----+-------+
| id | wages |
+----+-------+
|  1 | 20000 |
|  2 | 996   |
|  3 | 30000 |
|  4 | 27000 |
|  5 | 23000 |
+----+-------+
5 rows in set (0.00 sec)

查询工资最高员工的名称
可以这样构筑语句：查询 c 表名称，查询条件为 id 等于 w 表工资字段最大值的id
我这里使用排序配合限制返回数量，来完成子查询

mysql> select name from c where
    -> id=(select id from w order by wages desc limit 1);
+------+
| name |
+------+
| ajie |
+------+
1 row in set (0.00 sec)

要是不嫌麻烦还能写的更长点，嵌套两个子查询
别提效率什么的，仅用于演示

mysql> select name from c where
    -> id=(select id from w where
    -> wages=(select max(wages) from w));
+------+
| name |
+------+
| ajie |
+------+
1 row in set (0.00 sec)

由比较运算符号引发的子查询

如：select 字段 from 表名 where 字段>=(子查询语句)
  此时,若是子查询，返回了多个结果，数据库就会报错，这时就需要用
   any、some、all 对子查询进行修饰
   以下是这三个关键字对子查询信息进行筛选的结果

	any	some	all
>、>=	最小值	最大值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、!=			任意值

测试：

mysql> select wages from w where
    -> id < (select id from c where name='ajie');
ERROR 1242 (21000): Subquery returns more than 1 row

查询 id 小于 名字为 阿杰 员工id 的工资，此时有两位员工名字叫阿杰，返回了id 1、2和5，就报错了
（嗯，这句子看起来很奇怪，仅用于演示）

mysql> select id from w where wages<25000;
+----+
| id |
+----+
|  1 |
|  2 |
|  5 |
+----+
3 rows in set (0.00 sec)

此时就可以用到上面三个关键字进行修饰

--any
mysql> select name from c where
    -> id < any (select id from w where wages<25000);
+------+
| name |
+------+
| tom  |
| Ajie |
| ajie |
| ahua |
+------+
4 rows in set (0.00 sec)
这里子查询返回值有三个 加上了 any 等同于
   -> id < 返回值1 or < 返回值2 or < 返回值3        即 id < 1 or id < 2 or id < 5
   配合上面的表格和数据来看，也就是小于最大值即可，其他同理

多表查询

笛卡尔乘积

多表查询的语法为

select 字段,字段... from  表1,表2... [where 条件]

！ 但是如果不加条件直接进行查询，则会出现以下效果，这种结果我们称之为笛卡尔乘积

mysql> select * from c,w;
+----+------+------+----+-------+
| id | name | age  | id | wages |
+----+------+------+----+-------+
|  1 | tom  |   20 |  1 | 20000 |
|  2 | Ajie | NULL |  1 | 20000 |
|  3 | ajie |   35 |  1 | 20000 |
|  4 | ahua |   41 |  1 | 20000 |
|  5 | ago  |   21 |  1 | 20000 |
|  1 | tom  |   20 |  2 |   996 |
|  2 | Ajie | NULL |  2 |   996 |
|  3 | ajie |   35 |  2 |   996 |
|  4 | ahua |   41 |  2 |   996 |
|  5 | ago  |   21 |  2 |   996 |
|  1 | tom  |   20 |  3 | 30000 |
|  2 | Ajie | NULL |  3 | 30000 |
|  3 | ajie |   35 |  3 | 30000 |
|  4 | ahua |   41 |  3 | 30000 |
|  5 | ago  |   21 |  3 | 30000 |
|  1 | tom  |   20 |  4 | 27000 |
|  2 | Ajie | NULL |  4 | 27000 |
|  3 | ajie |   35 |  4 | 27000 |
|  4 | ahua |   41 |  4 | 27000 |
|  5 | ago  |   21 |  4 | 27000 |
|  1 | tom  |   20 |  5 | 23000 |
|  2 | Ajie | NULL |  5 | 23000 |
|  3 | ajie |   35 |  5 | 23000 |
|  4 | ahua |   41 |  5 | 23000 |
|  5 | ago  |   21 |  5 | 23000 |
+----+------+------+----+-------+
25 rows in set (0.00 sec)

笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积
就造就了一大串部分正确，部分错误的数值

多表联合查询

这个时候就需要找到两张表关联的字段，作为条件进行查询，称为多表联合查询

mysql> select * from c,w where c.id = w.id;
+----+------+------+----+-------+
| id | name | age  | id | wages |
+----+------+------+----+-------+
|  1 | tom  |   20 |  1 | 20000 |
|  2 | Ajie | NULL |  2 |   996 |
|  3 | ajie |   35 |  3 | 30000 |
|  4 | ahua |   41 |  4 | 27000 |
|  5 | ago  |   21 |  5 | 23000 |
+----+------+------+----+-------+
5 rows in set (0.00 sec)

多表链接查询

语法

select * from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段;

inner 内连接查询

示意图：

select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;

内连接查询的效果与多表联合查询的效果一样

left 左外连接查询

示意图：

select * from 表1 left join 表2 on 表1.字段 = 表2.字段;

表1数据全部显示，表2符合条件数据显示，不符合条件的会以 null 进行填充。

right 左外连接查询

示意图：

select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

表2数据全部显示，表1符合条件数据显示，不符合条件的会以 null 进行填充。

natural 自然连接

查询效果等同于inner即内联查询，作用是会自动匹配字段相同的列，连接时候不需要使用on或者using关键字
测试

mysql> select * from c natural join w;
+----+------+------+-------+
| id | name | age  | wages |
+----+------+------+-------+
|  1 | tom  |   20 | 20000 |
|  2 | Ajie | NULL |   996 |
|  3 | ajie |   35 | 30000 |
|  4 | ahua |   41 | 27000 |
|  5 | ago  |   21 | 23000 |
+----+------+------+-------+
5 rows in set (0.00 sec)

索引

本文只提索引的增删查

索引的目的：进行查询性能优化。

查看索引

语法

--查看表里的索引
select index from 表名;

！注：通常会在后面加一个 \G 方便查看

创建索引

索引的创建有三种方式

create直接添加索引：

create index 索引名 on 表名(字段名)

alter 修改表 添加索引：

alter table 表名 add index 索引名(字段名) 

或者在创建表的时候直接添加索引：

create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
index 索引名 (字段)
);

主键索引

主键索引是一种特殊的唯一索引
在创建表时，若是有主键，那么mysql会自动为主键添加一个主键索引。
主键索引不允许有空值，
且无法通过 create 来创建，
可使用 alite 添加主键，来使其自动添加主键索引

普通索引

最基本的索引，它没有任何限制。

语法：

create index 索引名 on 表名(字段名)

alter table 表名 add index 索引名(字段名) 

create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
index 索引名 (字段)
);

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

语法：

create unique index 索引名 on 表名(字段名)

alter table 表名 add unique 索引名(字段名) 

create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
unique 索引名 (字段)
);

全文索引

语法：

create fulltext index 索引名 on 表名(字段名)

alter table 表名 add fulltext 索引名(字段名) 

create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
fulltext 索引名 (字段)
);

全文索引的使用

全文索引有自己的语法格式，使用 match 和 against 关键字
如：

select * from 表名  where match(字段,字段) against('xxx xxx');

注意： match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。

多列索引

  多列建索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。
 另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。
语法：

create index 索引名 on 表名(字段名,字段名)

alter table 表名 add index 索引名(字段名,字段名) 

create table 表名(
字段 数值类型 约束,
字段 数值类型 约束,
index 索引名 (字段,字段)
);

删除索引

语法：

drop index 索引名称 on 表名;

视图

视图本身是一张虚拟的表

视图的作用：
  1.提高了重用性，对数据库重构，却不影响程序的运行，
  2.提高了安全性，可以对不同的用户，让数据更加清晰，特别是查询操作，减少复杂的SQL语句，
  3.增强可读性；更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；

如：
有信息表和工资表两张表，
信息表有员工的：员工号，姓名，年龄，家庭住址等信息
工资表有员工的：员工号，工资

此时，我想方便快捷的查看员工姓名和对应的工资，就可以创建这两个字段是视图，以后想查看时就可以直接查看此视图

创建视图

语法:

create view 视图名
as		      
select 列名1,列名2,...
from 表名1,表名2,表名...
where 表连接条件;

使用视图

使用视图与查询语句相同

select * from 视图名;

删除视图

drop view 视图名;

存储过程

  存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

如：我想知道工资表里工资大于平均值的员工的员工号，就可以创建一个存储过程，储存一个查询的过程，然后每次需要的时候，我只要调用此存储过程即可。

  因为在写存储过程时，需要用到 “ ; ” 分号，而mysql检查你输入分号且回车时，会自动执行语句，所以在写存储过程时，需要先用 delimiter 来临时更改结束符，或者简写为\d
  delimiter语法：

delimiter *  ( * 为你自定的字符)
\d *

创建存储过程

语法：

delimiter (自定义字符,临时更改结束符)
create procedure 存储过程名(输入参数|或输出参数|或为空) 
begin 
select 字段 from 表名 where 条件;
end 自定义字符

例子：
  此存储过程，可同时向两张表插入数据

mysql> desc staff_info;
+-------+-------------------+------+-----+---------+----------------+
| Field | Type              | Null | Key | Default | Extra          |
+-------+-------------------+------+-----+---------+----------------+
| id    | int               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(6)        | NO   |     | NULL    |                |
| age   | int               | YES  |     | NULL    |                |
| sex   | enum('男','女')   | YES  |     | NULL    |                |
+-------+-------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc wages;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int        | NO   | PRI | NULL    | auto_increment |
| name  | varchar(6) | NO   |     | NULL    |                |
| wages | int        | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> \d $
mysql> create procedure entry (in id int,in name varchar(6),in age int,in sex enum('男','女'),in wages int)
    -> begin
    -> insert into staff_info values(id,name,age,sex);
    -> insert into wages values(id,name,wages);
    -> end $
Query OK, 0 rows affected (0.03 sec)

查看存储过程

语法：

show create procedure 存储过程名;

调用存储过程

语法：

call 存储过程名(输入参数|或输出参数|或为空);

删除存储过程

语法：

drop procedure 存储过程名;

触发器

触发器本身就是特殊的存储过程，用特定的操作去触发

如：员工 a 离职，那么需要在多个表里都删除他的信息，就可以写一个触发器，在信息表删除 a 的信息时，触发 触发器，触发器自动把其他表里 a 的信息也删除掉。

创建触发器

语法：

create trigger 触发器名称 after 事件(delete\insert\update)
on 触发事件的表名 
for each row   --固定不变的
begin
触发语句
end $$

如：

create trigger a_delete after delete
on a
for each row   --固定不变的
begin
  （语句）
end $$

此触发器，是在 a 表进行了删除事件后触发语句。

查看触发器

语法：

show create trigger 触发器名;

删除触发器

语法：

drop trigger 触发器名;