文章目录
1、数据库概述
1.1 SQL概述
SQL,一般发音为sequel, SQL的全称(Structured Query Language), SQL用来和数据库打交道,完成和数据库的通信,SQL是一套标准。但是每一个数据库都有自己的特性,别的数据库没有,当使用这个数据库特性相关的功能,这时SQL语句可能就不是标准了.(90%以上的SQL都是通用的)
1.2 什么是数据库
数据库,通常是一个或一组文件,保存了一些符合特定规格的数据数据库对应的英语单词是DataBase,简称:DB,数据库软件称为数据库管理系统( DBMS),全称为DataBase Management System,如: Oracle、 SQL Server、MySq|、 Sybase、informix、 DB2、interbase、PostgreSq|。
1.3 MySql概述
MySQL最初是由“MySQL AB” 公司开发的一套关系型数据库管理系统( RDBMS-Relational
Database Mangerment System)。MySQL不仅是最流行的开源数据库,而且是业界成长最快的数据库,每天有超过7万次的下载量,其应用范围从大型企业到专有的嵌入应用系统。
MySQL AB是由两个瑞典人和一个芬兰人: David Axmark、Allan Larsson 和Michael "Monty"Widenius在瑞典创办的。在2008年初,Sun Microsystems 收购了MySQL AB公司。在2009年,Oracle 收购Sun 公司,使MySQL并入Oracle的数据库产品线。
1.4 sql、DB、DBMS
DB:DataBase (数据库,数据库实际上在硬盘上以文件的形式存在)
DBMS :DataBase Management System ( 数据库管理系统,常见的有: MySQL oracle DB2 Sybase sqlserver. … )
SQL :结构化查询语言,是一门标准通用的语言。标准的sql适合于所有的数据库产品。
SQL属于高级语言。只要能看懂英语单词的,写出来的sql语句,可以读懂什么意思。
SQL语句在执行的时候,实际上内部也会先进行编译,然后再执行sql。( sql语句的编译由DBMS完成。)
2、SQL语句
2.1 对SQL语句进行分类
- DQL(数据查询语言):查询语句,凡是
selec
t语句都是DQL。 - DML(数据操作语言):
insert delete update
,对表中的的数据进行增删改。 - DDL(数据定义语言):
create drop alter
,对表结构的增删改 - TCL(事务控制语言):
commit
提交事物,rollback
回滚事务(T是Transaction) - DCL(数据控制语言):
grant
授权、revoke
撤销权限等
重点:任何一条sql语句以;
结尾。
SQL语句不区别大小写。
2.2 导入初始化数据
-
第1步.dos窗口启动mysql:
1.Windows + R 输入 cmd 启动 dos 窗口;
输入:
net start mysql
启动 mysql 服务
net stop mysql
关闭 mysql 服务 -
第2步.输入:
mysql -hlocalhost -ur账号 -p密码
进入mysql数据库,
其中-h表示服务器名,localhost表示本地;-u为数据库用户名,root是mysql默认用户名;-p为密码,如果设置了密码,可直接在-p后直接输入。
注意:
你的mysql没有安装在C盘下(有时安装在C盘也会出现mysql无法进入的情况),会出现mysql无法进入的情况,需要先使用DOS命令进入mysql的安装目录下的bin目录中。以我的电脑为例:
进入D盘:输入
cd D:\Tools\MySQL5.5.25\bin进入到mysql的bin目录下才可以输入
mysql -hlocalhost -uroot -p123456
-
第3步.输入
show databases;
显示你有的数据库(mysql数据库中的命令必须以分号结尾“;”);
-
第4步:
use databases名字
;选择要操作的数据库; -
第5步:
show tables
; 显示所选数据库中的表;(show tables from 其他数据库名字
查看其他库中的表) -
第6步:创建自己的数据库:
create databases 表名字
(这个不是SQL语句,是MySQL的命令。) -
第7步:如果要退出mysql数据库,输入
exit;
回车。
2.3 对SQL脚本的理解
bjpowernode.sql, 这个文件以sq1结尾,这样的文件被称为“sql脚本”。
什么是sql脚本呢?
当一个文件的扩展名是 .sql,并且该文件中编写了大量的sql语句,我们称这样的文件为sql脚本。
注意:直接使用source
命令可以执行SQL脚本。
SQL脚本中的数据量太大的时候,无法打开,请使用source
命令完成初始化。
3、MySQL语句
3.1 MySQL常用命令
删除数据库:drop database 数据库名字;
查看表结构的详细信息:desc 表名称;
;
查看表中的数据:select *from 字符名称
查看当前使用的是哪个数据库:select dataabase();
查看MySQL的版本号:select version();
结束一条语句:\c
退出MySQL:exit
查看创建建表语句:show create table emp;
3.2 查询语句(DQL)
3.2.1 简单查询语句
语法格式:select 字段名1,字段名2,字段名3,...,from 表名
查询所有字段select * from emp
查询语句可以参与数学运算。
例如 select ename,sal*12 from emp
,查看emp表的员工名称和薪水的12倍
给查询结果的列重命名(yearsal):select ename,sal*12 as yearsal from emp
标准SQL语句中要求字符串(varchar
)使用单引号括起来,虽然MySQL支持双引号,建议别用。
3.2.2 条件查询语句
语法格式:select 字段,字段... from 表名 where 条件;
先执行from,然后where,最后select。
between and
between...and..
是闭区间[ ]
,除了可以使用在数字方面之外,还可以使用在字符串方面是左闭右开[ )
,在使用的时候必须左小右大。
例如:找出工资在1000和3000之间的员工,包括1000和3000
select ename,sal from emp where sal between 1000 and 3000;
is null 和is not null
0和null不是一个概念,0也代表有数字。
找出哪些人没有津贴
select enamel,sal,comm from emp where comm is null or comm = 0;
and 和or的优先级
找出工作岗位是MANAGER和SALESMAN的员工:select ename,job from emp where job ='MANAGER' or 'SALESMAN'
and和or联合使用:优先级较高的是and,所以尽量使用括号。
in(在)
in等同于or,找出工作岗位是MANAGER和SALESMAN的员工:select ename,job from emp where job in('MANAGER' , 'SALESMAN')
in 后面的值不是区间,是具体的值。
模糊查询like
在模糊查询中,必须掌握的两个特殊符号,一个是%
,一个是_
%代表任意多个字符,_代表任意一个字符。
找出名字包含O的:select ename from emp where ename like '%O%';
找出名字第二个字母是A的:select ename from emp where ename like '_A%';
找出名字中带有下划线的:select ename from emp where ename like '%\_%';
3.2.3 排序语句(order by)
order by
默认升序排序,其中asc
表示升序,desc
表示降序
按照工资的降序排列,当工资相同时再按照名字的升序排列:select ename,sal from emp order by sal sac,ename desc;
注意:越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候,才会启用后面的字段
3.2.4 分组语句(聚合函数、多行处理函数)
count
计数
sum
求和
avg
平均值
max
最大值
min
最小值
记住:所有的分组函数都是对“某一组”数据进行操作的,分组函数自动忽略NULL
其中SQL有一条语法规则:分组函数不可直接使用在where子句当中
找出总人数:select count(*) from emp;
找出工资总和:select sum(sal) from emp;
找出最高工资:select max(sal)from emp;
找出最低工资:select min(sal) from emp;
找出平均工资:select avg(sal) from emp;
多行处理函数:输入多行,最终输出一行。
count(*)和count(某个字段)的区别:
- count(*):是不是统计某个字段中数据的个数,而是统计总记录的条数。
- count(某个字段):表示统计某个字段中不为NULL的元素。
3.2.5 单行处理函数
特点:输入一行,输出一行。
ifnull()
空处理函数:ifnull(可能为NULL的数据,被当做什么处理):属于单行处理函数。
select ename,ifnull(comm,0) as comm from emp;
(把null当做0处理)
3.2.6 分组查询 (group by 和 having)
having
和group by
搭档。
group by
:按照某个字段或者某些字段进行分组
having
:having是对分组之后的数据进行再次过滤。
找到每个工作岗位的最高薪资:select max(sal) from emp group by job
注意: 分组函数一般都会和group by
联合使用,这也是为什么它被称为分组函数的原因。
并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by
的话,整张表的数据会自成一组。
where
后面不能使用分组函数,所以要使用having过滤。
找出每个部门的平均薪资,要求显示薪资大于2000的数据:select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
3.2.7 DQL语句的执行顺序
select ------- 5
…
from --------- 1
…
where ------- 2
…
group by ---- 3
…
having ------- 4
…
order by ----- 6
…
3.2.8 去除重复记录(distinct)
distinct
只能出现在所有字段的最前面。
正确:select distinct job from emp;
错误:select enamel,distinct job from emp;
案例:统计岗位的数量:select count(distinct job) from emp;
3.3 连接查询
在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的结果。
在实际开发中,一般一个业务都会对应多张表,比如:学生和班级,起码两张表。
stuno | Stuname | classno | classname |
---|---|---|---|
1 | zs | 1 | 北京大兴区亦庄经济技术开发区第二中学高三1班 |
2 | ls | 1 | 北京大兴区亦庄经济技术开发区第二中学高三1班 |
学生和班级信息存储到一张表中,结果就像上面一样,数据会存在大量的重复,导致数据的冗余。
3.3.1 连接查询的分类
根据语法出现的年代来划分的话,包括:
- SQL92 (一些老的DBA可能还在使用这种语法。DBA: DataBase Administrator,数据库管理员)
- SQL99(比较新的语法)
根据表的连接方式来划分,包括:
- 内连接:
①等值连接
②非等值连接
③自连接 - 外连接:
①左外连接( 左连接)
②右外连接(右连接)
③全连接(这个很少用! )
3.3.2 笛卡尔积现象
如有两个集合A和B,A = {0,1} B = {2,3,4}。
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式:
A×B = {(0,2),(1,2),(0,3),(1,3),(0,4),(1,4)};
B×A = {(2,0),(2,1),(3,0),(3,1),(4,0),(4,1)};
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。基于以上的结果我们得到两个结论:
- 两个集合相乘,不满足交换率,既 A×B ≠ B×A;
- A集合和B集合相乘,包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;
数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积,表与表之间的连接可以看成是在做乘法运算。
比如:现在有两张表EMP员工表和DEPT部门表如下图,现要求找出每位员工的部门名称,并显示员工姓名和部门名。
看到题目我们首先可能想到的是这条语句。从emp表中找出员工的姓名,从dept表中找出员工所在部门的名称。SQL语句可能是这样select ename,dname from emp,dept;
执行结果如下:
mysql> select ename,dname from emp,dept;
±-------±-----------+
| ename | dname |
±-------±-----------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
|…………省略……………|
| MILLER | ACCOUNTING |
| MILLER | RESEARCH |
| MILLER | SALES |
| MILLER | OPERATIONS |
±-------±-----------+
56 rows in set (0.00 sec)
从运算结果来看,SQL在两张表之间连接查询时执行了乘法运算,表emp中共有员工14个,表dept中共有部门4个。因此执行上述语句后的结果就为14*4=56个组合。即:当两张表进行连接查询时,若没有任何条件进行限制,最终的查询结果是两张表记录条数的成绩。这种现象在SQL中被称作为笛卡尔积现象。
关于表的别名:
select e.ename,d.dname from emp e ,dept d;
优点:
- 第一:执行效率高
- 第二:可读性好
3.3.3 等值连接
内连接之等值连接:最大的特点是条件是等量关系。
语法:
select
e.name,d.name
from
emp e
inner join//inner可以省略的,带着inner的目的是可读性好一些
dept d
on
e.deptno = d.deptno;
3.3.4 非等值连接
特点:连接条件中的关系是非等量关系。
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。
工资等级对应在s.losal
和s.hisal
之间
select e.name,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
3.3.5 自连接
最大的特点:一张表看做两张表,自己连接自己。
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。
员工的领导编号 = 领导的员工编号
select a.ename as'员工名',b.ename as'领导名' from emp a join emp b on a.mgr = b.empno;
3.3.6 外连接
- 内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。
AB两张表没有主副之分,两张表是平等的。 - 外连接:
假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表, 一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
外连接的分类?
- 左外连接(左连接) :表示左边的这张表是主表。
- 右外连接(右连控)。去示右动的这张表是主表。
内连接的写法:
select a.ename as'员工名',b.ename as'领导名' from emp a join emp b on a.mgr = b.empno;
左外连接(left
左边是主表):
select a.ename as'员工名',b.ename as'领导名' from emp a left join emp b on a.mgr = b.empno;
右外连接(right
右边是主表):
select a.ename as'员工名',b.ename as'领导名' from emp a right join emp b on a.mgr = b.empno;
外连接最重要的特点是:主表的数据无条件的全部查询出来。
3.3.7 多表连接
... A join B on ... join C on..
表示:A表先和B表进行表连接,连接之后A表继续和C表进行连接。
例如:找出每一个员工的部门名称以及工资等级
select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;
3.4 子查询
select语句当中嵌套select语句,被嵌套的select语句是子查询。
select .. (select)..from ..(select).. where..(select)..;
where后面子查询:找出高于平均薪资的员工信息
select * from emp where sal > (select avg(sal) from emp);
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 ;
3.5 union
union
可以将查询结果集相加
找出工作岗位是SALESMAN和MANAGER的员工
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
3.6 limit的使用(重点:分页查询)
limit
取结果集中的部分数据,是MySQL中特有的。
语法机制:
limit startIndex ,length
- startIndex :表示起始位置。
- length:表示取几个。
取出工资前5名的员工:
select ename,sal from emp order by sal desc limit 0,5;
limit是sql语句最后执行的的一个环节。
3.6.1 通用的标准分页sql
每页显示pageSize
条记录:第pageNo
页:(pageNo - 1) * pageSize
pageSize
:每页显示多少条记录。pageNo
:显示第几页。
3.7 insert插入语句
语法格式:insert into 表名 (字段名1,字段名2,字段名3,...) value(值1,值2,值3,...)
要求:字段的数量和值的数量相同,并且数据类型要对应相同。
注意:当一条inser
t语句执行成功之后,表格中必然会多一行记录。即使多的这一行记录当中某些字段是NULL,后期也没有办法在执行insert
语句插入数据了,只能使用update
进行更新。
一次插入多行数据
insert into t_student (no,name,sex,classno,birth) values
(3,'rose','1','gaosi','1999-11-22'),(4,'jack','1','gaosan','1998-1-2')
3.8 update修改数据
语法格式
update 表名 set 字段名1 = 值1,字段名2 = 值2...where 条件;
注意:如果没有条件整张表数据进行更新。
案例:将部门10的LOC修改为SHANGHAI,将部门名称修改为RENSHIBU
update dept set loc = 'SHANGHAI',dname = 'RENSHIBU' where deptno = 10;
3.9 delete删除数据
语法格式:
delete from 表名 where 条件;
注意:没有条件全部删除。delete from 表名 ;
3.10 删除大表中的数据
怎样删除大表的数据:truncate table 表名;
表被截断,不可回滚。永久丢失。
4、表(DDL)
4.1 表的创建
建表语句的语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
...
);
4.2 常见的数据类型
类型 | 描述 |
---|---|
int | 整数型 |
bigint | 长整形(java中的long) |
float | 浮点型 |
char | 定长字符串(String) |
varchar | 可变长字符串(StringBuffer/StringBuilder) |
date | 日期类型 |
BLOB | 二进制大对象(存储图片、视频等流媒体信息) |
CLOB | 字符大对象(存储比较大文本,比如,可以存储4G的字符串) |
char和varchar怎么选择?
- 在实际开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char。当一个字段的数据长度不确定,例如:简介。姓名等都是采用varchar。
4.3 创建学生表
create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1 //设置默认值为1,
classno varchar(255),
birth char(10)
);
4.4 删除表
语法格式
drop table if exists t_students;
4.5 表的复制
将查询结果当做表创建出来
语法:
create table 表名 as select语句;
4.6 将查询结果插入到一张表中
insert into 表名 select *from dept;
4.7 修改表结构
对于表结构的修改,这里不讲了,大家使用工具完成即可,因为在实际开发中表一旦设计好之后,对表结构的修改是很少的,修改表结构就是对之.前的设计进行了否定,即使需要修改表结构,我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括: insert delete update select ( 这些都是表中的数据操作。)
增删改查有一个术语: CRUD操作
即:Create
(增)Retrieve
(检索) Update
( 修改) Delete
(删除)
5、约束
5.1 约束的作用
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
5.2 常见的约束
- 非空约束(not null):约束的字段不能为NULL
- 唯一性约束(unique):约束的字段不能重复,但可以为NULL
- 主键约束(primary key):约束的字段既不能是NULL,也不能重复(简称PK)
- 外键约束(foreign key) :简称FK
- 检查约束(check):注意oracle数据库有check约束,但是mysql没有,目前mysql不支持该约束。
5.3 非空约束
用not null
约束的字段不能为null值,必须给定具体的数据
创建表,给字段添加非空约束(创建用户表,用户名不能为空)
mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null
-> );
Query OK, 0 rows affected (0.08 sec)
如果没有插入name字段数据,则会报错
mysql> insert into t_user (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
5.4 唯一性约束
unique
约束的字段,具有唯一性,不可重复。但可以为null
案例:
create table t_user(
id int,
username varchar(255) unique
);
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');
会报错:ERREO 1062(23000):Duplicate entry 'zhangsan' for key 'username'
5.5 主键约束
语法:在字段后面加primary key
表设计时一定要有主键,且一张表的主键约束只能有一个
。主键约束的字段既不能是NULL,也不能重复
主键有什么作用?
- 表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键。
- 主键的作用:主键值是这行记录在这张表当中的唯一标识。(就像一个人的身份证号码一样。)
5.5.1 主键相关术语
- 主键约束:primary key
- 主键字段:id字段添加primary key之后,id叫做主键字段
- 主键值:id字段中的每一个值都是主键值。
5.5.2 主键的分类
根据主键字段的字段数量来划分:
单一主键
(推荐的,常用的。)复合主键
(多个字段联合起来添加一一个主键约束) (复合主键不建议使用,因为复合主键违背三范式。)
根据主键性质来划分:
自然主键
:主键值最好就是一一个和业务没有任何关系的自然数。(这种方式是推荐的)业务主键
:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键。(不推荐用)最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。
5.5.3 主键值自增
在MySQL数据库提供了一个自增的数字,专门用来自动生成主键值,主键值不用用户维护,自动生成,自增数从1开始,以1递增(auto_increment
)
create table t_user(
id int(10) primary key auto_increment,
name varchar(32) not null
);
5.6 外键约束
外键可以为NULL。
foreign key(classno) references t_class(cno)
若有两个表A、B,id是A的主键,而B中也有id字段,则id就是表B的外键,外键约束主要用来维护两个表之间数据的一致性。
A为基本表,B为信息表
5.6.1 外键约束相关术语
- 外键约束:
foreign key
- 外键字段:添加有外键约束的字段
- 外键值:外键字段中的每一个值
5.6.2 场景描述
mysql> create table t_class(
-> cno int(10) primary key,
-> cname varchar(128) not null unique
-> );
cno(pk) cname
100 浙江省第一中学高三1班
200 浙江省第一中学高三2班
mysql> create table t_student(
-> sno int(10) primary key auto_increment,
-> sname varchar(32) not null,
-> classno int(3),
-> foreign key(classno) references t_class(cno)
-> );
sno(pk) sname classno(fk)
1 jack 100
2 lucy 100
3 king 200
mysql> insert into t_class(cno,cname) values(100,'aaaaaaxxxxxx');
mysql> insert into t_class(cno,cname) values(200,'oooooopppppp');
mysql> insert into t_student(sname,classno) values('jack',100);
mysql> insert into t_student(sname,classno) values('lucy',100);
mysql> insert into t_student(sname,classno) values('king',200);
运行结果:
mysql> select s.*,c.* from t_student s join t_class c on s.classno=c.cno;
+-----+-------+---------+-----+--------------+
| sno | sname | classno | cno | cname |
+-----+-------+---------+-----+--------------+
| 1 | jack | 100 | 100 | aaaaaaxxxxxx |
| 2 | lucy | 100 | 100 | aaaaaaxxxxxx |
| 3 | king | 200 | 200 | oooooopppppp |
+-----+-------+---------+-----+--------------+
注意:
- 外键值可以为null
- 外键字段去引用一张表的某个字段的时候,被引用的字段必须具有unique约束
- 有了外键引用之后,表分为父表和子表
- 班级表:父表
- 学生表:子表
- 创建先创建父表
- 删除先删除子表数据
- 插入先插入父表数据
6、存储引擎(ENGINES)
数据库存储引擎是数据库底层软件组件,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不多的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
MySQL提供了多个不同的存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎。MySQL5.7支持的存储引擎有:InnoDB
,MyISAM
,Memory
,Merge
,Archive
,Federate
,CSV
,BLACKHOLE
等。可以使用SHOW ENGINES
语句查看系统支持的引擎类型,结果如下:
mysql> SHOW ENGINES \G
*************************** 1. row ***************************
Engine: MyISAM
Support: YES
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
Engine: BLACKHOLE
Support: NO
Comment: /dev/null storage engine (anything you write to it disappear
*************************** 6. row ***************************
Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 8. row ***************************
Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 9. row ***************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 10. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
*************************** 11. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 12. row ***************************
Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows in set (0.00 sec)
mysql>
6.1 MyISAM存储引擎
MyISAM 基于 ISAM 的存储引擎,并对其进行扩展。它是在Web、数据存储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度,但不支持事务。在 MySQL5.5.5 之前的版本中,MyISAM 是默认的存储引擎。MyISAM 主要特性有:
- 不支持事务
- 使用表级锁,并发性差
- 主机宕机后,MyISAM表易损坏,灾难恢复性不佳
- 可以配合锁,实现操作系统下的复制备份、迁移
- 只缓存索引,数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
- 数据紧凑存储,因此可获得更小的索引和更快的全表扫描性能
- 可以把数据文件和索引文件放在不同目录
- 使用 MyISAM 引擎创建数据库,将产生3个文件。文件的名字以表的名字开始,扩展名指出文件类型:
.frm
文件存储表定义,数据文件的扩展名为.MYD
(MYData),索引文件的扩展名是.MYI
(MYIndex)。
6.2 InnoDB存储引擎
InnoDB 是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键。MySQL5.5.5之后,InnoDB 作为默认的存储引擎,InnoDB 主要特性有:
- 支持事务
- 灾难恢复性好
- 为处理巨大数据量的最大性能设计
- 实现了缓冲管理,不仅能缓冲索引也能缓冲数据,并且会自动创建散列索引以加快数据的获取
- 支持外键完整性约束。存储表中的数据时,每张表的存储都按逐渐顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6B的ROWID,并以此作为主键。
- 被用在众多需要高性能的大型数据库站点上
6.3 MEMORY存储引擎
MEMORY 存储引擎将表中的数据存储在内存中,为查询和引用其他表数据提供快速访问。MEMORY 主要特性有:
- 使用表级锁,虽然内存访问快,但如果频繁的读写,表级锁会成为瓶颈
- 只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型,浪费空间
- 不支持TEXT、BLOB字段。当有些查询需要使用到临时表(使用的也是MEMORY存储引擎)时,如果表中有TEXT、BLOB字段,那么会转换为基于磁盘的MyISAM表,严重降低性能
- 由于内存资源成本昂贵,一般不建议设置过大的内存表,如果内存表满了,可通过清除数据或调整内存表参数来避免报错
- 服务器重启后数据会丢失,复制维护时需要小心
7、事务(Transaction)
- 事务:一个最小的不可再分的工作单元;通常一个事务对应一个完整的业务(例如银行账户转账业务,该业务就是一个最小的工作单元)
- 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
- 事务只和DML语句有关,或者说DML语句才有事务。这个和业务逻辑有关,业务逻辑不同,DML语句的个数不同
7.1 事务的四大特性(ACID)
- 原子性(A):事务是最小单位,不可再分
- 一致性©:事务要求所有的DML语句操作的时候,必须保证同时成功或者同时失败
- 隔离性(I):事务A和事务B之间具有隔离性
- 持久性(D):是事务的保证,事务终结的标志(内存的数据持久到硬盘文件中)
7.2 事务的隔离性
事物A和事物B之间具有一定的隔离性
隔离性有隔离级别(4个)
- 第一级别:读未提交:
read uncommitted
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读(Dirty Read) 现象:表示读到了脏的数据。
- 第二级别:读已提交:
read committed
对方事务提交之,后的数据我方可以读取到。
这种隔离级别解决了:脏读现象没有了。
读已提交存在的问题是:不可重复读。
- 第三级别:可重复读:
repeatable read
这种隔离级别解决了:不可重复读问题。
这种隔离级别存在的问题是:读取到的数据是幻象。
- 第四级别:序列化读/串行化读:
serializable
解决了问题。
效率低,需要事务排队
Oracle数据库默认的隔离级别是:读已提交。
MySQL数据库默认的隔离级别是:可重复读。
7.3 隔离级别与一致性关系
8、索引
8.1 什么是索引
-
官方介绍索引是帮助MySQL 高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
-
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)。
-
我们通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉的)的索引。
8.2 怎么创建索引
创建索引对象:
create index 索引名称 on 表名(字段名);
8.3 怎么删除索引
删除索引对象:
drop index 索引名称 on 表名(字段名);
8.4 什么时候添加索引
- 该数据量庞大(根据客户的需求,根据线上的环境)
- 该字段很少的DML操作(因为字段进行修改操作,索引也需要维护)
- 该字段经常出现在where子句中(经常根据哪个字段查询)
注意:主键和具有unique约束的字段会自动添加索引
根据主键查询效率较高。尽量根据主键索引
8.5 索引的实现原理
索引底层采用的数据结构是:B+ Tree
通过BTree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的"物理地址”,
最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率
是最高的。
select ename from emp where ename = ' SMITH' ;
通过索引转换为:
select ename from emp where物理地址= 0x3 ;
8.6 索引的分类
MySQL 的索引有两种分类方式:逻辑分类和物理分类。
逻辑分类
有多种逻辑划分的方式,比如按功能划分,按组成索引的列数划分等
按功能划分:
- 主键索引:一张表只能有一个主键索引,不允许重复、不允许为 NULL;
ALTER TABLE TableName ADD PRIMARY KEY(column_list);
- 唯一索引:数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,但是一个唯一索引只能包含一列,比如身份证号码、卡号等都可以作为唯一索引;
CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD UNIQUE (column_list);
- 普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许 NULL 值插入;
CREATE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));
- 全文索引:它查找的是文本中的关键词,主要用于全文检索。
按列数划分
- 单例索引:一个索引只包含一个列,一个表可以有多个单例索引。
- 组合索引:一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则,即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。
物理分类
分为聚簇索引和非聚簇索引(有时也称辅助索引或二级索引)
聚簇是为了提高某个属性(或属性组)的查询速度,把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。
-
聚簇索引(clustered index)不是单独的一种索引类型,而是一种数据存储方式。这种存储方式是依靠B+树来实现的,根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时,方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块,找到索引也就找到了数据。
-
非聚簇索引:数据和索引是分开的,B+树叶子节点存放的不是数据表的行记录。
虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引,但是只有InnoDB的主键索引才是聚簇索引,InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。
8.7 索引失效
- 模糊查询
like %xx
- 范围查询
> <
- 对索引列进行运算
or
条件索引问题- 数据类型不一致(隐式类型转换导致的索引失效)
!=
问题- 联合索引违背最左匹配原则
order by
问题
9、视图(View)
9.1 什么是视图
站在不同的角度去看到数据。(同一张表的数据,通过不同的角度去看待)
9.2 创建与删除视图
CREATE VIEW 视图名 AS SELECT 语句;
DROP VIEW 视图名;
只有DQL语句才能以视图对象的方式创建出来。
9.3 视图的作用
1、简单性
看到的就是需要的。视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
2、安全性
视图的安全性可以防止未授权用户查看特定的行或列,使有权限用户只能看到表中特定行的方法,如下:
-
(1)在表中增加一个标志用户名的列。
-
(2)建立视图,使用户只能看到标有自己用户名的行。
-
(3)把视图授权给其他用户。
3、逻辑数据独立性
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图,程序一定是建立在表上的。有了视图之后,程序可以建立在视图之上,从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立。
-
(1)如果应用建立在数据库表上,当数据库表发生变化时,可以在表上建立视图,通过视图屏蔽表的变化,从而使应用程序可以不动。
-
(2)如果应用建立在数据库表上,当应用发生变化时,可以在表上建立视图,通过视图屏蔽应用的变化,从而使数据库表不动。
-
(3)如果应用建立在视图上,当数据库表发生变化时,可以在表上修改视图,通过视图屏蔽表的变化,从而使应用程序可以不动。
-
(4)如果应用建立在视图上,当应用发生变化时,可以在表上修改视图,通过视图屏蔽应用的变化,从而使数据库可以不动。
10、数据库设计三范式
10.1 什么是设计范式
设计表的依据。按照这个三范式设计的表不会出现数据冗余
10.2 第一范式(1NF)
任何一张表都应该有主键
,并且每一个字段原子性不可再分
。
10.3 第二范式(2NF)
建立在第一范式的基础上,所有非关键字段完全依赖主键
,不能产生部分依赖。
10.4 第三范式(3NF)
建立在第二范式的基础上,所有非主键字段直接依赖主键
,不能产生传递依赖