目录
一、MySQL的相关环境配置及基础知识
1.MySQL的默认端口号为3306。
MySQL数据库的超级管理员的用户名是root。
MySQL配置Path环境变量。
在DOS命令窗口运行并登录MySQL。
完美删除MySQL的方法:
①双击安装包,点击下一步,然后点击remove,卸载;
①手动删除C:\Program Files (x86)\MySQL目录;
②手动删除C:\ProgramData\MySQL目录。(ProgramData是隐藏目录)
2.sql、DB、DBMS分别是什么,它们之间的关系?
DB:DataBase。数据库,数据库实际上在硬盘上以文件的形式存在。
DBMS:DataBase Management System。数据库管理系统,常见的有MySQL、Oracle、
SqlServer...
SQL:结构化查询语言,是一门标准通用的语言。标准的sql适合于所有的数据库产品。SQL
属于高级语言。SQL语句在执行的时候,实际上内部也会先进行编译(sql语句的编译由DBMS
完成),然后再执行sql。
DBMS负责执行sql语句,通过执行sql语句来操作DB当中的数据。
DBMS-(执行)->SQL-(操作)->DB
3.什么是表table?
表table:是数据库的基本组成单元,所有的数据都是以表格的形式组织,目的是可读性强。
一个表包括行和列:
①行:被称为数据/记录(data)。
②列:被称为字段。每一个字段包括字段名、数据类型、相关的约束等属性。
4.SQL语句的分类:
①DQL数据查询语言:查询语句,凡是select语句都是DQL。
②DML数据操作语言:insert delete update,对表当中的数据进行增删改。
③DDL数据定义语言:create drop alter,对表结构的增删改。
④TCL事务控制语言:commit提交事务,rollback回滚事务。T是Transaction。
⑤DCL数据控制语言:grant授权、revoke撤销权限等。
5.导入数据:
①登录mysql数据库管理系统。dos命令窗口:mysql -uroot -p***
②查看有哪些数据库:show databases;
③创建自己的数据库:create 数据库名;
④使用创建出的数据库:use 数据库名;
⑤查看当前使用的数据库中有哪些表:show tables;
⑥初始化数据:source sql文件路径;
注意:上述的命令都是MySQL的命令,不是SQL命令。
6.sql脚本:当一个文件的扩展名是.sql结尾,并且该文件中编写了大量的sql语句。
注意:直接使用source命令可以执行sql脚本。sql脚本中的数据量太大的时候,无法打开,请使用source命令完成初始化。
7.查看表结构:desc 表名;
查看创建表的语句:show create table 表名;
结束语句:\c
二、SQL语句
2.1 数据查询语言DQL
2.1.1 简单的查询语句
语法格式:
select 字段名1, 字段名2, 字段名3... from 表名;
注意:任何一条sql语句以";"结尾。sql语句不区分大小写。
字段可以参与数学运算。
字段可以重命名,使用as。(as关键字可以省略)
如:select ename, sal * 12 as yearSal from emp;
select ename, sal * 12 yearSal from emp;
sql语句中的字符串以单引号括起来。
*可以表示所有字段,不建议使用这种方式,效率较低,一般查询自己需要的字段即可。
2.1.2 条件查询
语法格式:select 字段1, 字段2... from 表名 where 条件;
执行顺序:先from,然后where,最后select。
常用的条件运算符:
运算符 | 说明 |
= | 等于 |
<>或!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
between...and... | 两个值之间,等同于>= and <=(闭区间) |
is null | 为空(is not null 不为空),数据库中null不是一个值,代表什么也没有,为空 |
and | 并且 |
or | 或者 |
in | 包含,相当于多个or(not in 不在这个范围中),后面的数据集用"()" |
not | not可以取非,主要用在is或in中 |
like | 模糊查询,支持%或下划线_匹配,%表示任意多个字符,_表示一个字符 |
当运算符的优先级不确定的时候加小括号。
如果在模糊查询中想查找带有%、_的字符串,就使用转义字符\。
2.1.3 排序数据。
语法格式:
select 字段 from 表名 where 条件 order by 字段 [asc/desc];
执行顺序:先执行from,再执行where,然后执行select,最后执行order by。
默认情况下是升序。asc表示升序,desc表示降序。根据多个字段进行排序时,位置越靠前的字段,排序时越起到主导作用,只有当前面的字段无法完成排序的时候,才会启用后面的字段。
例子:
select ename,sal from emp order by sal desc, ename asc;
表示先按照字段sal降序排列,如果sal相同,再按照字段ename升序排列。
2.1.4 分组函数/聚合函数/多行处理函数
count | 取得记录数 |
sum | 求和 |
avg | 取平均 |
max | 取最大的数 |
min | 取最小的数 |
所有的分组函数都对"某一组"数据进行操作的。
分组函数自动忽略NULL。(在数学表达式中如果出现了NULL,这个表达式的值也一定为NULL)
针对NULL数据有一个函数ifnull()空处理函数:
ifnull(可能为NULL的数据(字段名), 被当做什么处理)
分组函数可以组合起来使用。
SQL语句中分组函数不可直接使用在where子句当中。因为group by子语句是在where子语句执行结束之后才执行的,而分组函数是在group by子语句结束之后才执行的。
例子:找出工资高于平均工资的员工?
select ename,sal from emp where sal > avg(sal); 错误写法。
select ename,sal from emp where sel > (select avg(sal) from emp); 利用子查询实现正确写法
count(*)和count(具体某个字段)的区别:
count(*):不是统计某个字段中数据的个数,而是统计表中总记录条数。(和某个字段无关)
count(某个字段):统计该字段中值不为NULL的数据总数量。
2.1.5 分组查询
group by:按照某个字段或者某些字段进行分组。
having:是对分组之后的数据进行再次过滤。
当一条sql语句没有group by的话,整张表的数据会自成一组。
例子:select max(sal) from emp group by job;
找出每个岗位中的最高工资,先按照岗位进行分组,再去寻找每个岗位的最高薪资。
注意:分组函数一般都会和group by联合使用。任何一个分组函数都是在group by语句执行
结束之后才会执行的。
例子:select ename,max(sal),job from emp group by job;
这条语句在MySQL中是会有查询结果的,但是结果没有意义,在Oracle数据库当中这条语句
会报错,语法错误,因为Oracle的语法规则比MySQL语法规则严谨。
因此记住一个规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的
字段。
可以有多个字段联合起来分组。
例子:找出每个部门的最高薪资,要求显示薪资大于2900的数据。
select max(sal),deptno from emp group by deptno having max(sal) > 2900; 效率较低
select max(sal),deptno from emp where sal > 2900 group by deptno; 效率较高
因此建议能够使用where过滤的就使用where进行过滤。
例子:找出每个部分的平均薪资,要求显示薪资大于2000的数据。
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
因为分组函数不能用在where子句中,因此此时只能使用having语句进行过滤。
没有group by就不能使用having。
2.1.6 一条sql语句完整的运行顺序
(重点)首先执行from语句,来自哪张表;再执行where语句,根据条件对记录进行过滤;然后
执行group by语句,对过滤后的数据进行分组;然后执行having语句,对分组后的数组再进行
一次过滤;然后执行select语句,选择要保留的字段;最后执行order by语句,控制输出顺序。
2.1.7 查询结果集的去重
distinct关键字去除重复记录。
在字段名前面加上distinct。如select distinct job from emp;
distinct关键字只能出现在所有字段的最前面。如果distinct后面有多个字段,表明这多个字段联合起来去重。
2.1.8 连接查询
在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询出最终的结果,一般一个业务都会对应多张表。
连接查询的分类:
①内连接:分为等值连接、非等值连接、自连接
②外连接:分为左外连接(左连接)、右外连接(右连接)
③全连接:使用较少
笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两条表记录条数的乘积。为了避免笛卡尔积现象,需要加条件进行过滤,但是加了条件不会减少匹配次数,只不过显示的是有效记录。
关于表的别名:如select e.ename, d.dname from emp e, dmp d;
表的别名的好处:执行效率高;可读性好。
①内连接之等值连接:最大特点是连接条件是等量关系。
案例:查询每个员工的部门名称,要求显示员工名和部门名。
SQL92:select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
SQL99(常用):select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
on后面跟的是表连接的条件。
语法:将表连接条件与where过滤的条件分离了,语法结构更清晰。其中inner可以省略,
inner表示内连接。
... A [inner] join B on 连接条件 where...
②内连接之非等值连接:最大特点是连接条件是非等量关系。
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and
s.hisal;
③内连接之自连接:最大的特点是一张表看作两张表,自己连接自己。
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。
select e1.ename,e2.ename from ename e1 join ename e2 on e1.mgr = e2.empno;
④外连接:最重要的特点是主表的数据无条件地全部查询出来。
内连接与外连接的区别:
内连接:假设A表和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查
询出来,这就是内连接,两张表没有主副之分,两张表是平等的。
外连接:假设A表和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张
表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数
据匹配上,副表自动模拟出NULL与之匹配。即主表中副表匹配不上的,会自动产生NULL
与之匹配。
外连接的分类:
①左外连接(左连接):表示左边的这张表是主表。
②右外连接(右连接):表示右边的这张表是主表。
案例:查询出所有员工的领导(注意是所有员工,即使是boss领导为null也要查询出)
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
left join表示左外连接
select e1.ename,e2.ename from emp e2 right join emp e1 on e1.mgr = e2.empno;
right join表示右外连接
连接时有一个关键字outer可以省略。
⑤三张表的连接查询:
案例1:找出每一个员工的部门名称以及工资等级
select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join
s.salgrade s on e.sal between s.losal and s.hisal;
案例2:找出每一个员工的部门名称、工资等级以及上级领导。
select e.ename,d.dname,s.grade,e1.ename from emp e join dept d on e.deptno =
d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp e1 on e.mgr =
e1.dmpno;
2.1.9 子查询
select语句当中嵌套select语句,被嵌套的select语句是子查询。
语法:
select
...(select)
from
...(select)
where
...(select)
①where子句中使用子查询:
案例:找出高于平均薪资的员工信息。
select * from emp where sal > (select avg(sal) from emp);
②from子句中使用子查询:把from后面的查询结果当成是一张新表,记得重命名。
案例:找出每个部门平均薪水的薪资水平。
select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) t
join salgrade s on t.avgsal between s.losal and s.hisal;
③select子句中使用子查询:
案例:找出每个员工所在的部门名称,要求显示员工名和部门名(不使用连接)
select e.ename,(select d.dname from dept d where e.deptno = d,deptno) as dname from
emp e;
2.1.10 union
可以将查询结果集相加。
案例:找出工作岗位是SALESMAN和MANAGER的员工?
1.select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
2.select ename,job from emp where job in ('MANAGER','SALESMAN');
3.select ename,job from emp where job = 'MANAGER' union select ename,job from emp
where job = 'SALESMAN';
使用union时要保证两个查询结果集的列数相同。
2.1.11 limit
用在分页查询(重点)。
limit是mysql特有的,其他数据库中没有,不通用(Oracle中有一个相同的机制,叫做rownum)。
limit是sql语句最后执行的一个环节。
limit的作用:取结果集中的部分数据。
limit的语法:limit startIndex,length startIndex表示起始位置(从0开始),length表示取几个。startIndex可以省略,默认为0。
案例:取出工资前5名的员工
select ename,sal from ename order by sal desc limit 0,5;
通用的标准分页sql:
假设每页显示3条记录:[startIndex,length]
第1页:0,3
第2页:3,3
第3页:6,3
第4页:9,3
得出结论:每页显示pageSize条记录,则第pageNo页[(pageNo - 1)*pageSize, pageSize]。
2.2 数据定义语言DDL
2.2.1 创建表
建表语句的语法格式:
create table 表名{
字段名1 数据类型 [default] 默认值,
字段名2 数据类型,
字段名3 数据类型,
...
};
定义时可以赋予默认值。
关于mysql当中常见的字段数据类型:
①int:整数型(对应java中的int)
②bigint:长整型(对应java中的long)
③float:浮点型(对应java中的float double)
④char:定长字符串(对应java中的String)
⑤varchar:可变长字符串(对应java中的StringBuffer/StringBuilder,最多255个字符)
⑥date:日期类型(对应java中的java.sql.Date类型)
⑦BLOB:二进制大对象,存储图片、视频等流媒体信息
⑧CLOB:字符大对象,存储较大文本(比如可以存储4G的字符串)
char和varchar的选择:
在实际的开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如性别、生日
等都是采用char;当一个字段的数据长度不确定,例如简介、姓名等都是采用varchar。
表名在数据库当中一般建议以t_或者tbl_开始。
2.2.2 表的复制
语法格式:
create table 表名 as select语句;
将查询结果当作表创建出来。
比如:create table emp1 as select empno,ename from emp;
2.2.3 表的修改alter
语法格式:
alter table 表名 modify 字段名 数据类型 [defaulr 默认值] [修改该列到位置]
在大部分情况下,对于表结构的修改使用工具完成即可,因为在实际开发中表一旦设计好之后,对表结构的修改是很少的,即使需要修改表结构,可以直接使用工具操作。修改表结构的语句不会出现在java代码当中,出现在java代码当中的sql包括:select insert delete update(都是表中的数据操作)。
增删改查有一个术语:CRUD操作。(create 增 retrieve 检索 update 改 delete 删除)
2.2.4 表的删除drop
语法格式:
drop table 表名;
drop table if exists 表名; //Oracle不支持这种写法
2.2.5 约束Constraint
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束种类:
①非空约束(not null):约束的字段不能为NULL。只有列级约束,只能加到字段后面。
②唯一约束(unique):约束的字段不能重复。唯一约束修饰的字段具有唯一性,不能重复,
但可以为NULL。
给两个列或多个列添加unique约束(联合唯一性),这种方式称为表级约束:
如果每一列各自不能重复,就在各自的字段后面加上unique,这种方式称为列级约束。
③主键约束(primary key,简称PK):约束的字段既不能为NULL,也不能重复。
主键相关的术语:
主键约束:primary key
主键字段:id字段添加primary key之后,id叫做主键字段
主键值:id字段中的每一个值都是主键值
主键的作用:主键值是这行记录在这张表当中的唯一标识。表的设计三范式中的第一范式
就要求任何一张表都应该有主键,并且一张表的主键约束只能有一个。
主键的分类:根据主键字段的字段数量划分为单一主键(推荐,比较常用的)和复合主键(多
个主键联合起来添加一个主键约束,不建议使用);根据主键性质划分为自然主键(主键值最
好就是一个和业务没有任何关系的自然数)和业务主键(主键值和业务挂钩,最好不要拿着和
业务挂钩的字段作为主键,因为以后的业务一旦发生变化的时候,主键值可能也需要随之发
生变化,但有的时候主键没法变化,因为变化可能会导致主键值重复)。
使用列级约束创建主键:
使用表级约束创建主键:
mysql提供主键值自增:从1开始,以1递增
Oracle当中也提供了一个自增机制,叫做序列(sequence)对象。
④外键约束(foreign key,简称FK):
外键约束的相关术语:
外键约束:foreign key
外键字段:添加有外键约束的字段
外键值:外键字段中的每一个值
比如以下例子:
t_student中的classno字段引用t_class表中的cno字段,此时t_student表叫做子表,
t_class表叫做父表。
顺序要求:
删除数据的时候,先删除子表,再删除父表;
添加数据的时候,先添加父表,再添加子表;
创建表的时候,先创建父表,再创建子表;
删除表的时候,先删除子表,再删除父表。
外键值可以为NULL。
外键字段引用其他表的某个字段的时候,被引用的字段不一定是主键,但至少具有unique
约束。
⑤检查约束(check):注意Oracle数据库有cheak约束,但是mysql没有,目前mysql不支持改
约束。
2.3 数据操作语言DML
2.3.1 插入数据insert
语法格式:
insert into 表名(字段名1,字段名2,字段名3,...) values (值1,值2,值3,...);
要求:字段的数量和值的数量相同,并且数据类型要对应相同。
字段名可以不按照定义表时的顺序,只要和后面的value值一一对应即可。
字段可以只选择其中的几个进行父赋值,未被赋值的字段值为创建表时定义的默认值。
前面的字段名可以省略,但是这样的话后面的值要严格按照表格的列的顺序来赋值,有几列
就要写几个值,即顺序不变、数量匹配。
一次可以插入多个数据:values (),(),... 一个()内就是一组数据。
可以将查询结果一次性加入某张表中:
insert into 表名 select语句;
比如:create table dept1 as select * from dept;
insert into dept1 select * from dept;
插入时对表的结构有要求。
2.3.2 修改数据update
语法格式:
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... where 条件;
注意:没有条件的话整张表全部更新。
2.3.3 删除表中的数据delete
语法格式:
delete from 表名 where 条件;
注意:没有条件全部删除。
怎么删除大表(数据量很大的表):
truncate table 表名; //表被截断,不可回滚,永久丢失,慎用。
三、存储引擎
3.1 概述及存储引擎分类
建表的时候可以指定存储引擎,也可以指定字符集。mysql默认使用的存储引擎是InnoDB方式。默认采用的字符集是UTF-8。
存储引擎这个名字只有在mysql中存在。oracle中有对应的机制,但是不叫作存储引擎,没有特殊的名字,就是"表的存储方式"。
mysql支持很多存储引擎,每一个存储引擎对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点,需要在合适的时机选择合适的存储引擎。
查看当前mysql(5.5.36版本)支持的存储引擎:show engines \G。当前版本支持九种存储引擎:
①FEDERATED
②MRG_MYISAM
③MyISAM
④BLACKHOLE
⑤CSV
⑥MEMORY
⑦ARCHIVE
⑧InnoDB
⑨PERFORMANCE_SCHEMA
3.2 MyISAM存储引擎
MyISAM这种存储引擎不支持事务。是mysql最常用的存储引擎,但是这种引擎不是默认的。
它管理的表具有以下特征:
①使用三个文件表示每个表:
格式文件----存储表结构的定义(xxx.frm)
数据文件----存储表行的内容(xxx.MYD)
索引文件----存储表上索引(xxx.MYI)
②灵活的AUTO_INCREMENT字段处理
③可被转换为压缩、只读表来节省空间
优点:可被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。
缺电:不支持事务。
3.3 InnoDB存储引擎
InnoDB存储引擎是mysql的缺省引擎。
它管理的表具有下列主要特征:
①每个InnoDB表在数据库目录中以.frm格式文件表示
②InnoDB表空间tablespace被用于存储表的内容(数据),这种形式不能被压缩
③提供一组用来记录事务性活动的日志文件
④用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
⑤提供全ACID兼容
⑥在mysql服务器崩溃后提供自动恢复
⑦多版本(MVCC)和行级锁
⑧支持外键及引用的完整性,包括级联删除和更新
优点:支持事务、行级锁、外键等,这种存储引擎数据的安全得到保障。
缺点:数据存储在tablespace这样的表空间中(逻辑概念),无法被压缩,无法转换成只读。
3.4 MEMORY存储引擎
使用MEMORY存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得MEMORY存储引擎非常快。以前被称为HEAP引擎。
它管理的表具有下列特征:
①在数据库目录内,每个表均以.frm格式的文件表示
②表数据及索引被存储在内存中
③表级锁机制
④不能包含TEXT或BLOB字段
优点:查询速度最快。
缺点:不支持事务。数据容易丢失,因为所有数据和索引都是存储在内存当中的。
四、事务Transaction
4.1 概述
一个事务是一个完整的业务逻辑单元,不可再分。
比如:银行账户转账,从A账户向B账户转账10000.需要执行两天update语句:
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';
以上两条DML语句必须同时成功,或者同时失败,不允许出现一条成功,一条失败。
要想保证以上的两天DML语句同时成功或者同时失败,那么就需要使用数据库的"事务机制"。
和事务相关的语句只有DML语句(insert delete update)。因为这三种操作都是和数据库表中的数据相关的,事务的存在是为了保障数据的完整性、安全性。
4.2 事务的原理
4.3 事务的特性
事务包括四大特性:ACID
①A:原子性。事务是最小的工作单元,不可再分。
②C:一致性。事务必须保证多余DML语句同时成功或者同时失败。
③I:隔离性。事务A与事务B之间具有隔离。
④D:持久性。最终数据必须持久化到硬盘文件中,事务才算成功的结束。
关于事务之间的隔离性:事务隔离性存在隔离级别,理论上隔离级别包括4个:
①第一级别:读未提交(read uncommitted)。对方事务还没有提交,我们当前事务可以读取到
对方未提交的数据。读未提交存在脏读(Dirty Read)现象,即读到了脏数据。
②第二级别:读已提交(read committed)。对方事务提交之后的数据我方可以读取到,解决了
脏读问题。读已提交存在的问题是不可重复读(如果在我这这个事务执行过程中,有其他事务
对表进行了修改,我们就不能再读到之前的数据,即其他事务修改了数据后当前事务能马上
知道)。
③第三级别:可重复读(repeatable read)。解决了不可重复读的问题。存在问题读取到的数据
是幻象的。
④第四级别:序列化读/串行化读。解决了所有问题,但是效率低,需要事务排队。
oracle数据库默认的隔离级别是读已提交(第二级别)。
mysql数据库默认的隔离级别是可重复读(第三级别)。
设置事务的隔离级别:
set global transaction isolation level + 隔离级别;
查看事务的隔离级别:
select @@global.tx_isolation;
4.4 事务的演示
mysql事务默认情况下是自动提交的(只要执行任意一条DML语句则提交一次)。
关闭自动提交机制:start transaction;
演示回滚:
演示提交:
五、索引
5.1 概述
索引就相当于一本书的目录,通过目录可以快速地找到对应的资源。
在数据库方面,查询一张表的时候有两种检索方式:
①全表扫描
②根据索引检索(效率很高):索引最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率,但是不能随意地添加索引,因为索引也是数据库当中的对象,也需要数据库不断地维护,是有维护成本的。比如,表中的数据经常被修改这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护。
添加索引是给某一字段,或者某些字段添加索引。
如:select ename,sal from emp where ename = 'SMITH';
当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有
的值。
当ename字段上添加索引的时候,以上sql语句会根据索引扫描,快速定位。
注意:主键和具有unique约束的字段自动会添加索引。因此根据主键查询效率较高,尽量根据主键检索。
5.2 索引对象的创建与删除
创建索引对象:
create index 索引名称 on 表名(字段名);
删除索引对象:
drop index 索引名称 on 表名;
索引的例子:
5.3 使用索引的条件
给字段添加索引的条件:
①数据量庞大(根据客户的需求、线上的环境);
②该字段进行很少的DML操作(因为字段进行修改操作的同时,也需要维护索引)
③该字段经常出现在where子句中,即经常根据该字段进行查找
5.4 索引的底层数据结构
索引底层采用的数据结构是B+ Tree。
通过B+ Tree缩小扫描范围,底层索引进行了排序、分区,索引会携带数据在表中的"物理地址",最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。
5.5 索引的分类
①单一索引:给单个字段添加索引
②复合索引:给多个字段联合起来添加1个索引
③主键索引:主键上会自动添加索引
④唯一索引:有unique约束的字段上会自动添加索引
...
5.6 索引的失效请况
如:select ename from emp where ename like '%A%';
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的。
六、视图View
6.1 视图概述
视图就是站在不同的角度去看到数据,同一张表的数据,通过不同的角度去看待。
对视图进行增删改查,会影响到原表数据。(通过视图影响原表数据,不是直接操作的原表)
只有DQL语句(select)才能以视图对象的方式创建出来。但是可以对视图进行CRUD操作。
6.2 视图的创建与删除
创建视图:
create view 视图名 as select语句;
删除视图:
drop view 视图名;
6.3 视图的作用
视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,程序员只对视图对象进行CRUD。
七、DBA命令
7.1 新建用户
create user 用户名 identified by '密码';
密码可以为空,如果为空则该用户可以不需要密码登录服务器。
7.2 授权
grant...to...
7.3 回收权限
revoke...from...
7.4 导出数据库当中的数据
在windows的dos命令窗口中执行:
mysqldump 库名>路径名 -uroot -p密码 (导出整个库)
mysqldump 库名 表名>路径名 -uroot -p密码 (导出库中的某个表)
注意是在dos命令窗口执行,不是登录到数据库内之后执行。
导出的文件后缀名.sql。
7.5 导入数据
create database 库名;
use 库名;
source 文件路径;
八、数据库设计三范式
设计范式是设计表的依据。按照这个三范式设计的表不会出现数据冗余。
8.1 三范式的内容
①第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。
②第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。所以尽量不要使用复合主键。
当两个字段之间有多对多的关系时:创建三张表,其中的关系表就是两个外键。
比如:
③第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖。
当两个字段之间一对多的关系:两张表,多的表加外键。
在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度。
当两个字段之间一对一的关系时:两种方案
①主键共享:
②外键唯一:
PS:根据动力节点课程整理,如有侵权,联系删除。