这篇博客详细介绍了MySQL的数据库设计，包括内连接、视图和索引原理。重点讲解了视图的创建、分类及其在数据访问限制和简化查询中的作用，同时也讨论了主键、外键和唯一约束等数据库约束。最后提到了事务的概念和ACID性质。
复习
内连接
- 表结构,内容存在上下级的关系
把这张表看做两张表进行关联查询
内链接
查询员工名及对应的领导名
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
查看'SMITH'上司在哪个城市工作
select e.ename,m.ename,d.loc from emp e join emp m on e.mgr=m.empno join dept d on m.deptno=d.deptno where e.ename='SMITH';
数据库设计
一对多关系
学生与班级，员工和部门都是一对多关联关系.
A表的一条数据,可以对应B表的多条数据,A表就是1 B表的多条数据,可以对应A表的一条数据,B表就是多
解决方案,1的这张表是主表,多的这张表是从表 在从表中(多)创建一个字段,存放主表(1)的主键,形成主外键关系
多对多关系
- 学生与课程,角色与功能都是对对多关联关系.
- 设计方案:增加中间表,保存双方的主键.
视图
视图概述
1. 视图是一张虚拟表,使用方法和使用表一样
2. 视图不是真实存在的表
3. 视图中对应一个select语句的查询结果集
4. 创建视图的语法
create view 视图名 as 子查询;
create view v_emp_10
as
select empno,ename,deptno
from emp
where deptno=10;
5. 使用视图的目的就是简化sql语句的复杂程度
重用子查询
- 查看视图
select * from 视图名
select * from v_emp_10;
select * from (select empno,ename,deptno
from emp
where deptno=10) t;
- 视图本身不包含数据
- 视图只是映射到基表的一个查询语句
- 当基表数据发生变化时,视图显示的数据也随之发生变化
如果创建视图时,子查询起了别名,那么视图只认识别名
create view v_emp_10_1
as
select empno a1,ename a2,deptno a3
from emp
where deptno=10;
修改视图
create or replace view v_emp_10_1
as
select empno id,ename name,deptno
from emp
where deptno=10;
视图的分类
- 视图分为简单视图和复杂视图
- 简单视图:创建视图的子查询中,不含有关联查询,查询的字段不包含函数和表达式,没有分组,没有去重
- 反之就是复杂视图
对视图进行DML操作,只能针对简单视图可以使用
insert into v_emp_10_1 values(2001,'lily',10);
对视图进行dml操作,要遵循基表的约束,不然会失败
-- 视图只能看到10号部分,但是通过视图插入了20号部门的员工,数据是偷渡进去的,对基表进行了污染 insert into v_emp_10_1 values(2002,'lilei',20);对视图的操作就是对基表的操作
操作不当会对基表产生数据污染
update v_emp_10_1 set name="bbb" where deptno=10; update v_emp_10_1 set deptno=20; -- mysql更新视图不会数据污染 update v_emp_10_1 set name="bbb" where deptno=30; -- 删除视图中的数据,不会产生数据污染 delete from v_emp_10_1 where deptno=30;
在mysql中,通过视图能够产生数据污染,只有insert
为视图增加检查选项
可以保证对视图进行DML操作后
视图必须对改变的部分可见(不可见的不许改)
否则不允许进行DML操作,这样避免数据污染
create or replace view v_emp_10_1
as
select empno id,ename name,deptno
from emp
where deptno=20
with check option;
-- 成功操作
insert into v_emp_10_1 values(2003,'lili', 20);
-- 操作错误
insert into v_emp_10_1 values(2003,'hanmeimei', 30);
视图简化复杂查询语句,重用子查询
1. 简化复杂查询
2. 如果需要经常执行某项复杂查询,可以基于这个复杂查询创建视图
限制数据访问
1. 视图本质上就是一条select语句
2. 当访问视图时,只能访问到select语句中涉及到的列
3. 对基表中其它列起到安全和保密的作用
工作中,对视图一般只进行DQL操作,不进行DML操作
复杂视图
创建一个复杂视图
- 创建一个含有公司部门工资情况的视图
- 内容如下
- 部门编号,部门名称
- 部门的最高,最低,平均工资
- 工资总和 create view vdeptsal as select d.deptno,d.dname, max(e.sal) maxsal, min(e.sal) minsal, avg(e.sal) avgsal, sum(e.sal) sumsal from emp e join dept d on e.deptno=d.deptno group by d.deptno;
查询出 比自己所在部门的平均工资 高的员工
select e.ename,e.sal,e.deptno,v.avg_sal from emp e,v_dept_sal v where e.deptno=v.deptno and e.sal>v.avg_sal;
删除视图
drop table 表名;
drop view 视图名;
drop view v_emp_10_1;
索引原理
索引概述
- 用来加快查询的技术有很多,最重要的就是索引
- 通常索引可以大幅度提高查询速度
- 如果不使用索引,mysql会从第一条数据开始,读完整个表,直到找到相关的数据,表越大,花费时间越多
- 索引可以用来改善性能,但是有时索引也可以降低性能
- 索引的统计和应用是数据库自动完成
使用索引的位置 - where deptno=10 - order by - distinct - like 不会使用索引
create index 索引名 on 表名(字段);
create index idxempename on emp(ename);
只要数据库认为可以使用某个已经创建的索引,索引就会自动应用
我们只需要决定要不要给某张表的某个字段创建索引
mysql innodb B+TREE 3次磁盘IO就可以找到
复合索引
create index idx_emp_job_sal on emp(job,sal)
select * from emp
order by job,sal;
创建表的时候加索引
create table mytable(
id int not null,
uname varchar(6) not null,
index idx_mytable_uname (uname)
);
更改表的索引
alter table mytable add index idx_mytable_uname1 (uname);
删除索引
drop index idx_mytable_uname1 on mytable;
索引总结
- 经常出现在where子句中的列创建索引
- 经常出现在order by子句中列创建索引
- 经常出现在distinct后面的列,创建索引
- 如果创建的是复合索引,索引的字段顺序和关键字顺序要一致
- 为经常做为表连接条件的列创建索引
- 不要在经常做DML操作的表和列上建立索引
- 不要在小表上创建索引
- 索引不是越多越好
- 删除很少使用,不合理的索引
MySQL 约束
主键约束(PRIMARY KEY)
- 不允许重复,不允许空值
创建主键约束
- 列级语法 create table student1( id int primary key, name varchar(20) );
- 表级语法 constraint 约束名 约束类型(列名) create table student( id int, name varchar(20) ); create table student2( id int, name varchar(20), primary key(id) );
在表创建之后,添加主键约束--效果相当于表级语法
alter table student add primary key(id);
alter table student modify id int primary key;
删除主键约束
alter table student drop primary key;
**mysql不起作用,oracle可以**
**alter table student modify id int;**
主键自增
create table t1(
id int primary key auto_increment,
name varchar(10)
);
insert into t1 values(null,'aaa');
insert into t1 values(10,'bbb');
delete from t1 where id=13;
主键自增长的细节
1. 插入数据时,使用null,自动增长
2. 插入数据时,如果插入一个比较大数,那么下次自增长从这个数开始累加
3. 删除末尾的条目,这个条目的id,不会再次在表中通过自增长出现
外键约束(FOREIGN KEY)
- 工作中,除非特殊情况,一般不使用外键,使用逻辑外键
- 外键约束是保证一个或两个表之间的参照完整性,保持数据一致
- 表的外键可以是另一张表的主键,也可以是唯一的索引
外键可以重复,可以是null
使用外键约束的条件
- 父表和子表必须使用相同的存储引擎
- 存储引擎必须是innodb
- 外键列和参照列必须具有相似的数据类型,数字长度,若是有符号,必须相同----字符类型长度可以不同
- 外键列和参照列必须创建索引,如果外键列不存在索引,mysql自动为其创建
创建外键约束
1. 注意,虽然mysql支持外键列级语法创建外键,但是无效
2. 表级语法创建外键
constraint 外键约束名 foreign key(本表列名) references 目标表(目标列)
create table teacher_table(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
insert into teacher_table values(null,'t1');
insert into teacher_table values(null,'t2');
insert into teacher_table values(null,'t3');
insert into teacher_table values(null,'t4');
create table student_table(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
foreign key(java_teacher) references teacher_table(teacher_id)
);
insert into student_table values(null,'s1',1);
insert into student_table values(null,'s2',1);
insert into student_table values(null,'s3',2);
insert into student_table values(null,'s4',3);
唯一约束(UNIQUE)
- 指定表中某一列或者多列不能有重复的值
- 唯一约束可以保证记录的唯一性
- 唯一约束的字段可以为空值
- 每张表可以存在多个唯一约束的列
创建唯一约束
create table t2(
name varchar(20) unique
)
create table t3(
a int,
b int,
constraint uk_name_pass unique(a)
);
create table temp(
id int not null,
name varchar(20),
password varchar(20),
constraint uk_name_pwd unique(name,password)
);
删除约束
alter table temp drop index uk_name_pwd;
创建表之后再添加唯一约束
alter table temp add unique uk_name_pwd(name,password);
alter table temp modify name varchar(25) unique;
非空约束(NOT NULL)
create table t4(
id int not null,
name varchar(25) not null default 'abc'
);
表建好后,修改非空约束
alter table t4 modify id int null;
alter table t4 modify id int not null;
默认约束(DEFAULT)
1. 用于设定列的默认值
2. 要求
- 定义的默认值的常量,必须与这个列的数据类型,精度等相匹配
- 每个列只能定义一个default约束
create table t5(
id int ,
name varchar(20),
sex char(10) default '男'
);
insert into t5 (id,name) values(1,'aaa');
CHECK约束
检查约束的作用,验证数据
create table t6( id int, name varchar(20), age int, check(age>20) ); insert into t6 values(1,'aaa',15);
- mysql不支持检查约束,但是可以创建,并且不报错.只不过没有任何效果.
什么是事务
- 事务是一组原子性的sql查询,在事务内的语句,要么全都执行,要么全都不执行
事务的 ACID 性质
- 原子性:最小的不可分割的业务单元
- 一致性:都执行或者都不执行,保持同一个状态
- 隔离性:多个事务并发,相互不影响
- 持久性:commit之后,数据保存在数据库中
MySQL事务
使用事务的要求
- 在mysql众多的引擎中,innodb 和NDB Cluster支持事务
- mysql默认自动提交事务,想手动提交,需要把默认提交关闭
关闭默认提交
show variables like 'autocommit';
set autocommit=0;
start transaction;
....业务逻辑....
commit; rollback;
事务案例
create table account(
id int,
name varchar(20),
money float
);
insert into account values(1,'aaa',1000);
insert into account values(2,'bbb',1000);
start transaction;
update account set money=money-100 where name='aaa';
update account set money=money+100 where name='bbb';
- 此时没有输入commit,直接关闭终端
- 再次打开终端,把自动提交关闭
- 查询account账户,之前的操作回滚了
- 再次开启事务
- 完成两次update
- 输入commit--->数据真正保存在表中
作业
- 上课完成的案例都重新敲一遍
练习
1.案例：创建一张表customer2，id number(4),
name varchar2(50)，password varchar2(50)
,age number(3),address varchar2(50),修改
customer2表的时候设置主键约束
pk_id_name_cus2修饰id和name列。
2.案例：创建一张book3表,id number(4),
name varchar2(50),author varchar2(50),
pub varchar2(50),numinput number(10)。
修改book3的时候，设置主键约束
pk_id_name_b3修饰id和name列，设置唯一约束uq_author_pub_b3修饰author和pub列
3.案例:删除temp中的唯一约束uk_name_pwd
4.案例：在book表中author和pub列上添加索引index_author和index_pub
5.案例：删除book中在pub和author上的索引
6.案例：创建一个视图emp_view1，查询emp表中所有的数据，查询语句作为视图emp_view1
7.案例：创建一个视图dept_view,查询dept表中所有的数据，查询语句作为视图dept_view
8.案例：创建一个视图emp_view2,查询emp表中所有员工的编号，姓名，职位，工资，上级领导的编号以及工资的等级，该等级的最低工资和最高工资,查询语句作为emp_view2
9.案例：查询emp表中10,20号部门员工的编号，姓名，职位，工资，所属部门的编号,使用查询语句来修改视图emp_view1
10.案例：删除视图emp_view1,emp_view2
扫一扫
6250
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
