Study MySQL(Day05)

表设计之关联关系

• 什么是外键：主键是用于表示数据唯一性的字段，外键是用于建立关系的字段，值通常指向另外一张表的主键

一对一

• 什么是一对一关系： 有A、B两张表，A表中一条数据对应B表中的一条数据，称为一对一关系
• 应用场景： 用户表和用户信息扩展表，商品表和商品信息扩展表
• 如何建立关系：在从表中添加外键指向主表的主键

一对多

• 什么是一对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条对应A表中的一条数据
• 应用场景：员工表和部门表 商品表和商品分类表
• 如何建立关系：在多的表中添加外键指向另外一张表的主键

多对多

• 什么是多对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条对应A表中的多条
• 应用场景： 老师表和学生表
• 如何建立关系：创建一个关系表，两个外键字段，分别指向另外两张表的主键

自关联

• 当前表的外键指向当前表的主键，这种关联方式叫做自关联
• 应用场景：需要保存上下级关系时
• 查询员工姓名和对应的主管姓名 select e.ename,m.ename from emp e join emp m on e.mgr=m.empno;

连接方式和关联关系

• 连接方式：包括等值连接，内连接，外连接 是指查询多张表数据时使用的查询方式
• 关联关系：包括 一对一 一对多 多对多，是指设计表时，两张表之间存在的逻辑关系

表设计案例：权限管理

 

视图

• 什么是视图： 数据库中的表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图本质就是取代了一条SQL查询语句。
• 为什么使用视图：因为有些数据的查询需要书写大量的SQL语句，每次书写效率太低，使用视图可以起到SQL重用的作用，视图可以隐藏敏感信息
• 创建视图的格式： create view 视图名 as 子查询; create table 表名 as 子查询
• 创建一个10号部门员工的视图 create view vemp10 as (select * from emp where deptno=10); -从视图中查询数据 select * from vemp10;
• 创建一个没有工资的员工表视图 create view vempnosal as (select empno,ename,comm,deptno from emp); -查询 select * from vempnosal

视图的分类

1. 简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询。可以对视图中的数据进行增删改查操作
2. 复杂视图：和简单视图相反，只能对视图中的数据进行查询操作
3. 创建一个复杂视图 create view vempinfo as (select avg(sal),max(sal),min(sal) from emp); -查询 select * from vempinfo;

对简单视图进行增删改查，操作方式和table一样

1. 插入数据 insert into vemp10 (empno,ename,deptno) values(10011,'刘备',10); select * from vemp10; select * from emp;
2. 如果插入一条在视图中不可见，但是原表中却可见的数据称为 数据污染。 insert into vemp10 (empno,ename,deptno) values(10012,'关羽',20); select * from vemp10; select * from emp;
3. 通过 with check option 解决数据污染问题 create view vemp20 as (select * from emp where deptno=20) with check option; insert into vemp20 (empno,ename,deptno) values(10013,'赵云',20); //成功 insert into vemp20 (empno,ename,deptno) values(10014,'黄忠',30); //失败
4. 修改和删除视图中的数据（只能修改删除视图中有的数据） update vemp20 set ename='赵云2' where ename='赵云'; update vemp20 set ename='刘备2' where ename='刘备';//修改失败 delete from vemp20 where deptno=10;//没有数据被删除
5. 创建或替换视图 create or replace view vemp10 as (select * from emp where deptno=10 and sal>2000);
6. 删除视图 drop view 视图名; drop view vemp10; show tables;
7. 如果创建视图的子查询中使用了别名 则对视图操作时只能使用别名 create view vemp10 as (select ename name from emp where deptno=10); select name from vemp10;//成功 select ename from vemp10;//失败

视图总结：

1. 视图是数据库中的对象，代表一段SQL语句，可以理解成一张虚拟的表
2. 作用： 重用SQL，隐藏敏感信息
3. 分类：简单视图（创建视图时不使用去重、函数、分组、关联查询，可以对数据进行增删改查）和复杂视图（和简单视图相反，只能对数据进行查询操作）
4. 插入数据时有可能出现数据污染，可以通过with check option解决
5. 删除和修改只能操作视图中存在的数据
6. 起了别名后只能用别名

约束

• 什么是约束： 约束就是给表字段添加的限制条件

主键约束+自增 primary key auto_increment

• 作用：唯一且非空

非空约束 not null

• 作用：该字段的值不能为null create table tnull(id int,age int not null); insert into tnull values(1,18); //成功 insert into t_null values(2,null);//失败

唯一约束 unique

• 作用： 该字段的值不能重复 create table tunique(id int,age int unique); insert into tunique values(1,28);//成功 insert into t_unique values(2,28);//失败 不能重复

默认约束 default

• 作用： 给字段设置默认值 create table tdefault(id int,age int default 20); insert into tdefault (id) values(1); //默认值会生效 insert into tdefault values(2,null);//默认值不会生效 insert into tdefault values(3,30);//可以赋值其它值

外键约束

• 外键：用来建立关系的字段称为外键
• 外键约束： 添加外键约束的字段，值可以为null，可以重复，但是值不能是关联表中不存在的数据，外键指向的数据不能先删除，外键指向的表也不能先删除
• 如何使用外键约束 use db6;
• 创建部门表 create table dept(id int primary key auto_increment,name varchar(10));
• 创建员工表 create table emp(id int primary key autoincrement,name varchar(10),deptid int,constraint fkdept foreign key(deptid) references dept(id)); -格式介绍：constraint 约束名称 foreign key(外键字段名) references 依赖的表名(依赖的字段名)
  • 测试插入数据 insert into dept values(null,'神仙'),(null,'妖怪'); insert into emp values(null,'悟空',1); insert into emp values(null,'赛亚人',3);//失败 delete from dept where id=1;//失败 drop table dept; //失败
• 由于添加外键约束后 会影响测试效率，所以工作中很少使用，一般都是通过java代码实现逻辑外键。

索引

• 什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录
• 为什么使用索引：如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到数据为止，使用索引后会将磁盘块以树桩结构保存，查询数据时会大大降低磁盘块的访问数量，从而提高查询效率。
• 有索引就一定好吗？ 如果表中的数据很少，使用索引反而会降低查询效率
• 索引是越多越好吗？ 不是，索引会占用磁盘空间，只针对查询时常用的字段创建索引
• 导入数据 windows系统： source d:/itembackup.sql linux系统： source /home/soft01/桌面/itembackup.sql
• 导入完后： show tables; 查看是否有item2 这张表 select count(*) from item2; 172万
  • 测试查询耗时 select * from item2 where title='100'; //耗时1.13

如何创建索引 title varchar(10)

• 格式： create index 索引名 on 表名(字段名(字符长度)) create index indexitemtitle on item2(title);
  • 测试查询耗时 select * from item2 where title='100'; //耗时0.04

索引分类

• 聚集索引：通过主键创建的索引称为聚集索引，聚集索引中保存数据，只要给表添加主键约束，则会自动创建聚集索引
• 非聚集索引：通过非主键字段创建的索引称为非聚集索引，非聚集索引中没有数据

如何查看索引

• 格式：show index from 表名; show index from item2;

删除索引

• 格式： drop index 索引名 on 表名; drop index indexitemtitle on item2;

复合索引

• 通过多个字段创建的索引称为复合索引
• 格式： create index 索引名 on 表名(字段1,字段2); create index indexitemtitle_price on item2(title,price);

索引总结

1. 索引是用来提高查询效率的技术，类似目录
2. 因为索引会占用磁盘空间，所以不是越多越好
3. 因为数据量小的时候使用索引会降低查询效率所以不是有索引就一定好
4. 分类：聚集索引和非聚集索引
5. 通过多个字段创建的索引称为复合索引

事务

• 数据库中执行同一业务多条SQL语句的工作单元，可以保证全部执行成功或全部执行失败

事务的ACID特性

• ACID是保证数据库事务正确执行的四大基本要素
• Atomicty：原子性，最小不可拆分，保证全部成功或全部失败
• Consistency：一致性，保证事务从一个一致状态到另外一个一致状态
• Isolation：隔离性，多个事务之间互不影响
• Durablity: 持久性，事务提交之后数据保存到数据库文件中持久生效

事务相关的SQL

1. 开启事务 begin;
2. 回滚 rollback;
3. 提交 commit;
4. 保存回滚点 savepoint s1;
5. 回滚到某个回滚点 rollback to s1;
6. 查看自动提交状态 show variables like '%autocommit%';
7. 修改自动提交状态 set autocommit=0/1;

group_concat()

• 查询每一个部门所有员工的姓名和工资 select deptno,group_concat(ename,'-',sal) from emp group by deptno;
• 查询每个部门的员工姓名，要求每个部门只显示一行 select deptno,group_concat(ename) from emp group by deptno;