二、MySQL数据库（多表）（1）

1 多表关系

1.1 外键引入

1 外键特点：

• 从表外键的值是对主表主键的引用。
• 从表外键类型，必须与主表主键类型一致。

2 声明外键约束 ：

语法：
    alter table 从表名 add constraint foreign key [约束名] (从表的外键的列名) references 主表名(主键列名)
	[外键名称]用于删除外键约束的，一般建议“fk_”

	alter table 从表 drop foreign key 外键名称

3 案例：

ALTER TABLE product ADD CONSTRAINT FOREIGN KEY fk_category_id (category_id)
REFERENCES category(cid);

1.2 表与表之间的关系

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系。

1 一对多关系：（案例见上个商品——分类）

• 常见实例：客户和订单，分类和商品，部门和员工, 省份和城市
• 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.

2 多对多关系：

• 常见实例：学生和课程、用户和角色, 演员和电影, 商品和订单
• 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

3 案例：

 中间表创建外键约束: 省略约束名
 uid外键,user表的uid主键
 rid外键,role表的rid主键

ALTER TABLE user_role ADD CONSTRAINT FOREIGN KEY (uid)
REFERENCES `user` (uid);

ALTER TABLE user_role ADD CONSTRAINT FOREIGN KEY (rid)
REFERENCES role (rid);

 

---

2 多表查询

2.1 交叉连接查询

开发中不使用，得到的是两个表的乘积

2.2 常用的多表查询方式

2.3 内连接查询

内连接查询(使用的关键字 inner join – inner可以省略)

• 隐式内连接：select * from A,B where 条件;

• 显示内连接：select * from A inner join B on 条件;

/*
  多表查询: 内连接 隐式
  查询语法:
   select 列名 from 表A,表B
   where条件 主表的主键=从表的外键
*/
SELECT * FROM category c ,products p 
WHERE c.cid = p.category_id;

/*
  内连接: 显示内连接
  关键字 inner join
  条件过滤,关键字 on
*/
SELECT * FROM category c INNER JOIN products p
ON c.cid = p.category_id;

2.4 外连接查询

外连接查询(使用的关键字 outer join – outer可以省略)

• 左外连接：left outer join
  • select * from A left outer join B on 条件;
• 右外连接：right outer join
  • select * from A right outer join B on 条件;

/*
  左外连接:参照点就是左边的表!!左表的数据必须都显示.
  右边的表,没有数据,必须显示null!!
*/
SELECT * FROM category c LEFT OUTER JOIN products p
ON c.cid = p.category_id;

/*
 右外连接:参照点就是右边的表!!右边表的数据都要显示
 左边的表多余数据,不能显示
*/
SELECT * FROM category c RIGHT OUTER JOIN products p
ON c.cid = p.category_id;

2.5 子查询

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。
语法：select ....查询字段 ... from ... 表.. where ... 查询条件
案例

3 索引

3.1 索引概述

索引 index 海量数据的年代,高效
MySQL数据库,查询数据的时候,全部数据的扫描,找出你要的数据
索引技术,提升查询效率。

官方定义：MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :

3.2 索引优势劣势

优势：
1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：
1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3.3 索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

• B树索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
  MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

MySQL数据的存储引擎: 数据在硬盘中的存储方式 (文件 010101)
     InnoDB:文件（默认常用）
         支持事务
         支持关系,主外键
         数据增删改慢,查询一般
     MyISAM:文件
         不支持事务
         不支持关系,主外键
         数据增删改,快,查询一般
     
     Memory:存储在内存中,关机,丢失

3.4 索引分类

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
2） 唯一索引 ：索引列的值必须唯一，但允许有空值约束，唯一约束 unique,，自动创建索引
3） 复合索引 ：即一个索引包含多个列

3.5 索引语法

1 创建表 2 创建索引 3 写入数据
建立索引的列将存在另外一个文件中，以B树的形式保存，方便快速查找。

1. 创建索引

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name [USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

实例：

CREATE INDEX index_price ON  products(price);

2. 删除索引

DROP  INDEX  index_name  ON  tbl_name;

3. 查看索引

show index  from  table_name;

4. 修改索引

1.添加PRIMARY KEY（主键索引） 
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
2.添加UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
3.添加INDEX(普通索引) 
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
4.添加FULLTEXT(全文索引) 
ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
5.添加多列索引 
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )