MySQL数据库学习笔记【多表操作部分】

外键约束

• 外键特点：

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

• 声明外键约束

语法：
alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

[外键名称]用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称

多对多操作

分析

• 商品和订单多对多关系，将拆分成两个一对多。
• products商品表，为其中一个一对多的主表，需要提供主键pid
• orders 订单表，为另一个一对多的主表，需要提供主键oid
• orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid, 用来维护商品与分类的关系

多表查询

同时查询两张及以上的表
语法：select * from 表一，表二

**出现的结果：**两个表的的结果相乘之后再呈现（笛卡尔积）

内连接查询(多表)

    查询语法：
     select 列名 from 表A,表b where 主表的主键=从表的外键
     注意：如果主键和外键列名相同，则用表明.列名
     -显示内连接
     关键字：inner join
     条件过滤关键字 on
     select  列名 from 表A inner join 表b on 主表.主键=从表.

外链接查询

左外： left outer join
右外： right outer join

左外连接：
select 列名 from 表A left outer join 表B on 主表.主键= 从表.外键
--注意：参照点是左边的表，左边的表数据全部显示，右边的表若无数据则显示NULL
右外连接：
select 列名 from 表A right outer join 表B on 主 表.主键= 从表.外键
--注意：参照点是右边的表！！右边的表的数据都要显示，左边的表多余的数据，不能显示

子查询

两个查询放在一起，一个是子，一个是父；子查询的结果可以让另一个查询来使用，可以作为条件也可以作为另一个表
子查询练习：

#查询“化妆品”和“家电”两个分类上架商品详情
select
	*
from 
	products 
WHERE
	category_id in (select cid from category where cname='家电' or cname='化妆品');  -- 'c001', 'c003'


-- select cid from category where cname='家电' or cname='化妆品';
-- select cid from category where cname in ('家电', '化妆品');

索引

提升查询效率

优势
1.类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本
2、通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
劣势
1、索引也是一张表，该表中保存了主键和索引字段，并指向实体类的记录，也需要占用空间
2、提升了查询效率， 但却降低了更新表的速率

mysql数据的存储引擎：

数据在硬盘中的存储方式
-InnoDB：存储在文件中
                支持事务、支持关系，主外键
                增删改慢，查询一般
-MyISAM：存储在文件中
                不支持事务、不支持关系，主外键
                增删改快，查询一般
-Memory：存储在内存中

4 索引分类

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引

2） 唯一索引 ：索引列的值必须唯一，但允许有空值

3） 复合索引 ：即一个索引包含多个列

5 索引语法

索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。

准备环境:

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

5.1 创建索引

语法 ：

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX 索引名
[USING  index_type]
ON 表名(创建索引的列)


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

5.2 查看索引

语法：

show index  from  表名;

5.3 删除索引

语法 ：

DROP  INDEX  索引名  ON  表名;

5.4 ALTER命令

1). alter  table  tb_name  add  primary  key(column_list); 

	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
	
2). alter  table  tb_name  add  unique index_name(column_list);
	
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);

	添加普通索引， 索引值可以出现多次。