数据库多表查询

多表查询之外键：
主键：唯一标识一条记录，不能有重复，不允许为空，用来保证数据完整性




外键是另一个表的主键，外键可以有重复的，可以是控制，用来和其他表建立联系。
例如
部门表中的主键id
员工表的dept_id就是外键：代表的就是dept的id主键（通过这个dept_id来获取员工的除了员工表之外的相关的部门的详细的信息）。
添加外键约束之前，要先创建约束的表，练习一下，先创建表（navicat查询处创建了表之后，要重新连接才能出现）：
创建部门表：
create table dept (
id int primary key auto_increment,
name varchar(50) not null,
description varchar(100)
);
创建员工表：
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender char(2) not null,
salary float(10,2) ,
age int(2) not null,
gmr int ,
dept_id int
);


相关的语法的解释：
restrict：这里是部门id下面有员工外键和id相等的时候不可以修改
cascade：这里是删除部门的话，外键为其部门id的所有员工就会全部被删除[级联，删除，删掉部门，部门里的员工级联被删除]
set null：部门删掉了，员工表就部门id为空（前提是外键可以为空）
no action：也就是删除的话是删不成功的，就不执行你的动作


外键是用的条件：
1.两张表都必须是InnoDB表，并且没有临时表：可以在菜单表信息中可以看到，engine=InnoDB（一般都是这个格式，除非你自己修改）
其他的类型的引擎表不支持外键
2.建立外键关系的对应列必须具有相似的InnoDB内部数据类型（数据类型一致）
3.建立外键关系的对应列必须建立了索引（外键为另一个表的主键，主键就是索引.......）
4.加入显示的给出了constraint  symbol，那么symbol在数据库中必须是唯一的，假如没有给出，InnoDB会自动创建（也就是外键名，如果不取就默认一个）


哪个表有外键，哪个表就用来维护之间的关系，就好像明星和粉丝的关系是靠粉丝维护的【有外键的表称为从表，】


添加外键的语法：
在创建表的时候添加：
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender char(2) not null,
salary float(10,2) ,
age int(2) not null,
gmr int ,
dept_id int,
constraint emp_dept_id foreign key(dept_id) references dept(id) 
on delete cascade on update cascade
);


表创建好之后，修改的方式：
[在你的从表中如果已经输入了值，并且该值不存在主表中的话，插入外键是会失败的，因为插入不会成功，已经存在了主表中不存在的值]
alter table emp add foreign key(dept_id) references dept(id) on delete cascade on update cascade；
外键创建之后，在设计表处就可以查看到外键处有一个记录，就可以认为创建成功了。
然后从表的dept_id就必须是dept中有的id，否则就会修改或者创建员工出错，创建外键的时候，默认的delete/update on restrict，在外键菜单中可以进行修改。


通过查看表定义查看外键：
show create table emp


删除外键：
alter table emp drop foreign key emp_dept_id;




多表查询之表连接：
内连接[两个表之间的连接，只列出这些连接表中雨连接条件相匹配的数据行(如果部门id为空的话，该员工就不会显示出来)]：
在这里inner可以省略
select *  from emp inner join dept on emp.dept_id=dept.id;
显示员工的全部信息
select e.name 'empname',d.name 'deptname' from emp e inner join dept d on e.dept_id=d.id;
或者select e.name 'empname',d.name 'deptname' from emp e，dept d where e.dept_id=d.id; [两种写法都是内连接]
仅显示员工的名字和部门名字


外连接：
分为左右全连接：
不仅列出与连接条件相匹配的行，还列出左表（左连接）或右表（右连接）或者两个表（全连接）中所有的数据行
左连接：
select e.name 'empname',d.name 'deptname' from emp e left join dept d on e.dept_id=d.id;
在这里，如果emp表中所有记录全列出来，哪怕它的部门id为空[员工全出来了，部门不一定全出来]
右连接：
select e.name 'empname',d.name 'deptname' from emp e right join dept d on e.dept_id=d.id;
在这里，如果dept表中所有记录全列出来，哪怕它的部门中没有员工[部门全出来了，员工不一定全出来]
全连接/交叉连接：
select e.name 'empname',d.name 'deptname' from emp e cross join dept d;
没有on之后的子句，返回连接表中所有数据行的笛卡尔积
也就是符合查询条件的第一个表中的数据行数乘以符合查询条件的第二个表中的数据行数
例如emp中有4条记录，dept中有三条记录，那么交叉连接的记录数就是12条记录，每个员工与每个部门都有相应的一条记录


自连接【在web开发中用的特别的多！！！】：
参与连接的表示同一张表（通过别名把一张表虚拟出两张可以进行连接的表）
select e1.name,e2.name from emp e1 left join emp e2 on e1.gmr=e2.id;-------这里的gmr就是对应的员工的id 
select e2.name from emp e1 right join emp e2 on e1.id=e2.gmr;
---------在这里e1表示的是经理表，e2表示的是员工表，员工表的经理的id就是经理表的经理的id那就是e1.name就是经理的名字


MySQL子查询[能用连接查询代替子查询，就尽量不用子查询！！！]：
某些情况下，当查询时需要的条件是另一个查询的结果，这时候就用到了子查询
为了给主查询（外部查询）提供数据而首先执行的查询（内部查询）被叫做子查询
用于子查询的关键字包括：in、notin、exist、notexist、=、<>(不等于)等。


连接查询能做到的子查询一定可以做到，但是子查询做得到的连接查询不一定做得到。[但是子查询的效率低于连接查询]
子查询的例子：[子查询一定是优于主查询的]
查询薪水最高的员工的名字：[这里的子查询为：select max(salary) from emp]
select name,salary from emp where salary = (select max(salary) from emp);
查询出每个部门的平均月薪：[这个是连接查询，利用了聚合函数avg()来简化查询，提高效率]
select avg(salary) as max_salary,dept_id from emp where dept_id is not null group by dept_id;
查询月薪比平均月薪高的员工的名字：[这里的子查询为：select avg(salary) from emp]
select name,salary from emp where salary>(select avg(salary) from emp);


多表查询之Mysql的索引：
索引时数据库中用来提高查询性能的最常用的工具。
Mysql所有的列类型都可以被索引，对相关列使用索引时提高select操作性能的最佳途径，可以用来快速的找到那些具有特定值的记录
所有Mysql索引都以B-树的形式保存。
在使用以下操作符时，都会用到相关列上的索引：
>、<、>=、<=、<>、in、between、like 'pattern'(pattern不能以通配符开始，也就是开始的数据要精确，后面可以使用通配符模糊数据)等
索引最好不要滥用，因为索引的建立之后，也需要维护，过多的索引也会造成维护的困难程度。
频繁修改和查询的数据才建立索引，数据量少并且不经常修改的就不需要建立索引，执行起来反而会更快。


索引的分类[indexName是索引名，tableName是表名，column是字段名/列名，length索引长度]：
①普通索引
最基本的索引类型，没有唯一性之类的限制，普通索引的创建方式为
直接创建：create index indexName on tableName(column(length)...)；
[长度是指定索引的长度，如果前15个字符就可以确定唯一性的话，length就可以设定为15，以减少空间的占用，也提高效率]
修改表时添加索引：alter table tableName add index indexName(column(length)...);
创建表的时候指定索引：create table tableName(...,...,...,index indexName(column(length)...));


如果要创建索引的列的类型是char、varchar类型，length可以小于字段的实际长度
如果是blob、text类型[因为是大文本类型的数据，不指定会占完空间的...]，则必须指定length。
②唯一索引【什么是能不能重名啊？】
这种索引和普通索引基本相同，但是这种索引的索引列的所有值都只能出现一次，即必须唯一，它的创建方式为
直接创建索引：create unique index indexName on tableName(column(length)...);
修改表时添加索引：alter table tableName add  unique index indexName(column(length)...);
创建表的时候指定索引：create table tableName(...,...,..., unique index indexName(column(length)...));
③主键索引
主键索引时一种特殊的唯一索引，一般在创建表的时候指定
创建表的时候添加索引：create table tableName(...,...,... primary key(column));
[在Mysql中，建立主键的同时，主键索引也建立起来了，不需要重复设置。一个表只有一个主键，也只有一个主键索引]
修改表的方式添加主键索引alter table tableName add constraint indexName primary key(column);
④全文索引[MySql3.2之后开始支持全文索引，全文索引的类型为fulltext]
这种索引只能在char、varchar和text类型的列上创建
创建方式：
直接创建索引：create fulltext index indexName on tableName(column(length)...);
修改表时添加索引：alter table tableName add  fulltext index indexName(column(length)...);
创建表的时候指定索引：create table tableName(...,...,..., fulltext index indexName(column(length)...));


删除、禁用索引：
语法：
drop index indexName on tableName；
对于MyISAM表在做大批量导入的时，会边插入数据变建索引。为了提高执行效率，应先禁用索引在完全导入之后再开启索引
而InnoDB对索引都是单独处理的，无需禁用索引【一般都用这种，基本上用不着禁用和打开的操作】。
禁用索引：alter table tableName disable keys；
打开索引：alter table tableName enable keys；


设计索引的原则：
①最适合索引的列是出现在where子句中的列，或连接子句中指定的列，而不是出现在select后的列。
②索引列的值越多不同，索引效果越好。
③对于char和varchar列，只用它的部分来创建索引可以节省索引空间，提高查询效率。
④利用最左前缀
⑤根据搜索关键字建立多列索引
⑥不要过度索引（维护成本）


多表查询之MySql的视图：
视图：从一个或几个基本表中根据用户的需求或需要而做成一个虚表，视图是虚表，它在存储时只存储视图的定义，
而没有存储对应的数据，只有在使用时才从基本表中收集数据，再展现给用户。


优点：简单[简化SQL语句的复杂度]；安全[防止未经许可的用户访问敏感数据]；数据独立[降低数据库的复杂程度]；视图中数据的更新就是表数据的更新。


创建视图：
语法：
create [replace] [algorithm={undefined | merge | temptable}] view 视图名 as select语句 [with[cascaded | local] check option]
replace：就是替换视图 ，undefined：未定义，自动选择merge类型，merge把select语句替换成视图然后再进行查询再生成视图，可以进行更新
temptable：视图是把查询的结果生成临时视图进行存储，不可以更新
with就是验证表的数据的合法性和视图的数据的合法性，local就只验证视图的数据的合法性。
例如：create/replace view viewName as select语句
create view viewName as select emp.id,emp.name ename,dept.name dname from emp,dept where emp.dept_id=dept.id;
[在emp和dept中都有name列，所以必须要创建别名来进行区分。]


使用视图：
select * from view viewName；[在这个表中只有三列，分别是emp.id，ename，dname，相当于视图代替了子查询]


不可更新的视图，有下面任何一种结构，就不可以更新：
[查询出来的列是原始列就可以更新，如果是通过原始列推断或者是计算出来的列就不能够更新了]
聚合函数（avg，min，max，sum）[不是原始数据]；
distinct，group by，having，union和union all [不是原始数据]；
仅引用文字值 [不存在基本表]；
位于选择列表中的子查询[子查询已经不是原始数据]；
join结构 [不是原始数据]；
from子句 [不是原始数据]；
where子句 [不是原始数据]；
引用from子句中的表 [不是原始数据]；
algorithm= temptable；


查看视图：
show tables：显示表的时候也会显示视图
desc viewName：查看表结构
show create view viewName：查看视图的创建信息


修改视图：
alter [algorithm={undefined | merge | temptable}] view 已存在的视图名 as select语句 [with[cascaded | local] check option]
alter view viewName as select语句


删除视图：
要有该视图的drop权限[root是超级管理员，所有权限都有]：
drop view [if exists] viewName1，viewName2，......；