MySQL查缺补漏 从无到有 有这一篇就够了
数据库概述
SQL概述
SQL,一般发音为sequel,SQL的全称Structured Query Language,SQL用来和数据库打交道,完成和数据库的通信,SQL是一套标准。但是每一个数据库都有自己的特性别的数据库没有,当使用这个数据库特性相关的功能,这时SQL语句可能就不是标准了.(90%以上的SQL都是通用的)
什么是数据库
数据库,通常是一个或一组文件,保存了一些符合特定规格的数据,数据库对应的英语单词是DataBase,简称:DB,数据库软件称为数据库管理系统(DBMS),全称为DataBase Management System,如:Oracle、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql 。
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的数据库产品线。
SQL的分类
数据查询语言(**DQL**-Data Query Language) 代表关键字:select
数据操纵语言(**DML**-Data Manipulation Language)代表关键字:insert,delete,update
数据定义语言(**DDL**-Data Definition Language)代表关键字:create ,drop,alter,
事务控制语言(**TCL**-Transactional Control Language)代表关键字:commit ,rollback;
数据控制语言(**DCL**-Data Control Language)代表关键字:grant,revoke.
DOS命令窗口使用MySQL的基本命令
查看MySQL的版本
mysql --version
mysql -V
进入MySQL
mysql -uroot -p
导入数据
mysql默认情况是大小写不敏感的
1) 创建数据库 mysql> create database 数据库名称;
2) 选择数据库 mysql> use 数据库名称
3) 导入数据 mysql>source sql文件
终止一条语句
如果想要终止一条正在编写的语句,可以键入 \c
退出MySQL
可使用 \q、QUIT、EXIT
查看现有的数据库
show databases;
指定数据库
use 数据库名称;
查看当前使用的库
use 数据库名称;
show database();
查看当前库中的表
use 数据库名称;
show tables;
查看其他库中的表
show tables from 数据库名称;
查看表结构
desc 表名;
查看表的创建语句
show create table 表名;
查询(select)语句
查询一个或者多个字段
select
字段名,字段名......
from
表名;
查询全部字段
select * from 表名;
采用select * from emp,虽然简单,但是*号不是很明确,建议查询全部字段将相关字段写到select语句的后面,
编写的SQL语句不建议使用select * 这种形式,建议写明字段,这样可读性强.
将查询出来的字段显示为中文
select 字段名 as '别名' , 字段名 as 别名 from 表名;
注意:字符串必须添加单引号 ' '
采用as关键字重命名表字段,其实as也可以省略,例如
select 字段名 别名,字段名 别名 from 表名;
条件查询
条件查询需要用到where语句,where语句必须放到from语句的后面
select
字段名,字段名......
from
表名
where
查询条件;
执行顺序: 先from,然后 where, 最后select
支持如下运算符
= 等于
<>或!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between … and …. 两个值之间,等同于 >= and <=
is null 为null(is not null 不为空)
and 并且
or 或者
in 包含,相当于多个or(not in不在这个范围中)
not not可以取非,主要用在is 或in中
like like称为模糊查询,支持%或下划线匹配,%匹配任意个字符,一个下划线只匹配一个字符
between…and…操作符
第一种写法 采用>=和<=
select 字段名1,字段名2 from 表名 where 字段名 >= 1000 and 字段名 <= 2000;
第二种写法 采用between...and ...
select 字段名1,字段名2 from 表名 where 字段名 between 1000 and 2000;
关于between...and ...他是包含最大值和最小值的
is null
Null为空,但是不是空串,不能使用等号衡量,为null可以设置这个字段不填值,
如果查询为null的字段,采用 is null
如果某个字段的值为null,如下这样是查询不出来结果的,因为null比较特殊,必须使用is来比较
select * from 表名 where 字段名 = null;
应该这样查询
select * from 表名 where 字段名 is null;
and 与 or
and 表示并且的含义,表示所有的条件必须满足
select * from 表名 where 字段名1 = '陆柒捌' and 字段名2 > 8000;
or 表示或者的意思,只要满足一个条件即可
select * from 表名 where 字段名1 = '陆柒捌' or 字段名2 > 8000;
表达式的优先级
有时候会遇到and与or同时是同的情况,这时候就需要注意表达式的优先级了,尽量采用括号,如下
select * from 表名 where 字段名1 > 8000 and (字段名2 = '陆柒捌' or 字段名2 = '一二三');
in
in表示包含的意思,完全可以采用or来表示,采用in会更加简洁一些,如下
select * from 表名 where 字段名1 in ('陆柒捌','一二三');
与
select * from 表名 where 字段名1 = '陆柒捌' or 字段名1 = '一二三';
结果相同
not
下面举一个例子来说明
例如查询出 字段名1 不包含 1000 和 2000 的数据
第一种写法
select * from 表名 where 字段名1 <> 1000 and 字段名1 <> 2000;
第二种写法
select * from 表名 where not (字段名1 = 1000 or 字段名 = 2000);
第三种写法
select * from 表名 where 字段名1 not in (1000,2000);
查询出 字段名1 不为 null 的数据
select * from 表名 where 字段名1 is not null;
like
like可以实现模糊查询,like支持%和下划线查询
例如 查询名字
查询姓名以 M 开头的数据
select * from 表名 where name like 'M%';
查询姓名以 N 结尾的数据
select * from 表名 where name like '%N';
查询姓名中包含 P 的数据
select * from 表名 where name like '%P%';
查询姓名中第二个字母为 A 的数据
select * from 表名 where name like '_A%';
like中 % 和 _ 的区别:
% 匹配任意字符出现的个数
_ 只匹配一个字符
like中的表达式必须放到单引号中,以下写法是错误的:
select * from 表名 where name like _A%;
排序数据
排序采用order by子句,order by后面跟上排序字段,排序字段可以放多个,多个采用逗号间隔,order by默认采用升序,
如果存在where子句那么order by必须放到where语句的后面
根据字段排序,默认是升序
select * from 表名 order by 字段名;
如果有where语句,order by 语句必须放到where语句的后面
select * from 表名 where 字段名 = 1000 order by 字段名;
排序顺序:默认是升序 也可以自己指定,asc为升序,desc为降序
select * from 表名 where 字段名 = 1000 order by 字段名 desc;
按照多个字段排序,会首先按 字段名1 排序,再按照 字段名2 排序
select * from 表名 where 字段名 = 1000 order by 字段名1 , 字段名2 asc;
也可以几个字段分别指定排序方式
select * from 表名 order by 字段名1 desc,字段名2 asc;
执行顺序:
select
字段 ------3
from
表名 ------1
where
条件 ------2
order by
.... ------4
order by 是最后执行的
分组函数/聚合函数/多行处理函数
分组函数还有另一个名字:多行处理函数。
多行处理函数的特点:输入多行,最终输出的结果是1行。
单行处理函数的特点:输入一行,最总输出的结果是1行。
count 取得记录数
sum 求和
avg 取平均
max 取最大值
min 取最小值
count取得记录数
注意!!! 分组函数自动忽略空值,不需要手动的加 where 条件排除空值
select count(*) from 表名 where xxx; 符合条件的所有记录总数,包含null
select count(字段名) from 表名; 该字段中不为空的记录条数
注意!!! 分组函数不能直接使用在 where 关键字后面
mysql> select * from 表名 where xxx > avg(xxx);
ERROR 1111 (HY000): Invalid use of group function
distinct 去重
distinct的作用是去重,例如
select count(distinct 字段名) from 表名;
select 字段名1,distinct 字段名2 from 表名;
以上的sql语句是错误的,记住:dintinct只能出现在所有字段的最前面
例如:
select distinct 字段名2,字段名1 from 表名;
sum 求和
sum可以取得一个列的和,null会被忽略
select sum(字段名) from 表名;
也可以取得几个列的和
select sum(字段名1 + 字段名2) from 表名;
但是如果其中有个字段的中有 null 的话则结果不正确,sum会忽略掉null,可以采用 ifnull() 语法
ifnull 替换null值
用法 ifnull(字段名,如果字段中有null就使用这里的替换值),如下
select sum(字段名1 + ifnull(字段名,0)) from 表名;
avg 求平均值
取得某一列的平均值
select avg(字段名) from 表名;
max 求最大值
取得某列的最大值
select max(字段名) from 表名;
min 求最小值
取得某列的最小值
select min(字段名) from 表名;
组合聚合函数
可以将这些聚合函数都放到select中一起使用
select count(*),sum(字段名),avg(字段名),max(字段名1),min(字段名2) from 表名;
分组查询
分组查询主要涉及到两个子句,分别是: group by 和 having
注意:分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的原因。
并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话,整张表的数据会自成一组。
group by
记住一个规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段。
例如:
select 字段名1 ,max(字段名2) ,字段名3 from 表名 group by 字段名3;
以上select查询出来可能有结果,但是结果是没意义的,因为 字段名1 并不是分组函数也没有参与分组,
正确的写法如下:
select max(字段名1) ,字段名2 from 表名 group by 字段名2;
多个字段可以联合起来一块儿分组
select
字段名1,字段名2,max(字段名3)
from
表名
group by
字段名1,字段名2;
where 后面不能使用分组函数:
select
字段名1,avg(字段名2)
from
表名
group by
字段名1
where
xxx
以上这种情况是错误的,这种情况只能使用having过滤
having 过滤
如果相对分组数据再进行过滤需要使用having
select
字段名1,avg(字段名2)
from
表名
group by
字段名1
having
xxx
总结 一个完整的DQL语句如何写
后面数字为执行顺序
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
有了以上的执行顺序就能很容易的知道为什么 where 不能写在 group by 后面了
连接查询
连接查询的分类(根据表的连接方式来划分),包括:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
关于表的别名:
select
a.字段1,
b.字段2
from
表1 as a,
表2 as b;
表的别名的好处:
第一:执行效率高
第二:可读性好
内连接之等值连接,最大特点是:条件等量关系
语法:
select
a.字段1,
b.字段2
from
表1 as a
join
表2 as b
on
连接条件
等值连接一般几张表中都会有一个外键,
这个值是相等的,可以通过这个值去查询另一张表中相关的数据
比如 a.字段3 = b.字段4
where
xxx;
内连接之非等值连接,最大的特点是:连接条件中的关系是非等量关系
select
a.字段1,
b.字段2
from
表1 as a
join
表2 as b
on
例如:
a.字段3 between b.字段4 and b.字段5
where
xxx;
内连接之自连接,最大的特点是:一张表看作两张表,自己连接自己
select
a.字段1,
b.字段2
from
表1 as a
join
表1 as b
on
例如:
a.字段3 = b.字段2
where
xxx;
外连接
什么是外连接,和内连接有什么区别?
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。
AB两张表没有主副之分,两张表是平等的。
外连接:
假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中
的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
外连接的分类?
左外连接(左连接):表示左边的这张表是主表。
右外连接(右连接):表示右边的这张表是主表。
左连接有右连接的写法,右连接也会有对应的左连接的写法。
左连接: left join on
select
a.字段1,
b.字段2
from
表1 as a
left join
表2 as b
on
例如:
a.字段3 = b.字段4
where
xxx;
右连接:right join on
select
a.字段1,
b.字段2
from
表1 as a
right join
表2 as b
on
例如:
a.字段3 = b.字段4
where
xxx;
外连接最重要的特点是:主表的数据无条件的全部查询出来。
例如上面两个例子,首先是左连接:
表1就是主表,表2是副表,根据连接条件查询出来之后,表1 的数据全部查出来,当表2中的数据没有和表1 中的数据匹配上时,副表会自动模拟出null与之匹配
多张表的连接方式
select
a.字段名1,b.字段名2,c.字段名1
from
表1 a
join
表2 b
on
表1和表2的连接条件
join
表3 c
on
表1和表3的连接条件
where
xxx;
子查询
什么是子查询?
select语句中嵌套select语句,被嵌套的select语句是子查询
子查询可以出现在哪里?
select
...(select)
from
...(select)
where
...(select)
where 中使用子查询
例如:
select
*
from
表名
where
字段1 > (另一条select语句);
from中使用子查询
例如:
select
a.*,s.字段1
from
(子查询select语句) as a
join
表2 s
on
a.字段1 between s.字段2 and s.字段3;
select中使用子查询
例如:
select
e.字段1,(子查询select语句) as b
from
表1 e;
union(可以将查询结果集相加)
例如:
select 字段1,字段2 from 表名 where 字段1 = '陆柒捌';
得到结果:
字段1 字段2
陆柒捌 a
陆柒捌 b
select 字段1,字段2 from 表名 where 字段1 = '张三';
得到结果:
字段1 字段2
张三 c
张三 b
使用union
select 字段1,字段2 from 表名 where 字段1 = '陆柒捌'
union
select 字段1,字段2 from 表名 where 字段1 = '张三';
得到结果:
字段1 字段2
陆柒捌 a
陆柒捌 b
张三 c
张三 b
就算两张毫不相关的表也可以使用union将表中的数据拼接在一起,但是字段的数量必须是对应的
limit 取得结果集中的部分数据
语法机制:
limit startIndex, length
startIndex表示起始位置,从0开始,0表示第一条数据。
startIndex不写的时候就表示从第一条数据开始
length表示取几个
例子:
select
字段
from
表名
limit 2,5
表示从第2条数据开始,取5条数据,不包含第二条数据,取到的是第3,4,5,6,7条数据
limit是sql语句最后执行的一个环节:
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
limit 7
...
创建表
建表语句的语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
....
);
关于MySQL当中字段的数据类型?以下只说常见的
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
date 日期类型 (对应Java中的java.sql.Date类型)
BLOB 二进制大对象(存储图片、视频等流媒体信息) Binary Large OBject (对应java中的Object)
CLOB 字符大对象(存储较大文本,比如,可以存储4G的字符串。)
Character Large OBject(对应java中的Object)
......
char和varchar怎么选择?
在实际的开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char。
当一个字段的数据长度不确定,例如:简介、姓名等都是采用varchar。
表名在数据库当中一般建议以:t_或者tbl_开始。
例子:创建学生表
创建学生表:
学生信息包括:
学号、姓名、性别、班级编号、生日
学号:bigint
姓名:varchar
性别:char
班级编号:int
生日:char
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
insert语句插入数据
语法格式:
字段可以省略不写,但是后面的value对数量和顺序都有要求。
insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
要求:字段的数量和值的数量相同,并且数据类型要对应相同。
如果一个表有三个字段,但是你只插入一个字段,则其他字段自动插入null
insert into 表名(字段名1) values(值1)
得到如下表
字段名1 字段名2 字段名3
值1 null null
需要注意的地方:
当一条insert语句执行成功之后,表格当中必然会多一行记录。
即使多的这一行记录当中某些字段是NULL,后期也没有办法在执行
insert语句插入数据了,只能使用update进行更新。
一次插入多行数据
insert into
表名(字段名1,字段名2,字段名3)
values
(值1,值2,值3),
(值4,值5,值6),
(值7,值8,值9)
......
删除表
当这个表存在的话删除
drop table if exists 表名;
表的复制
语法:
create table 表名 as select语句;
将查询结果当做表创建出来。
将查询结果插入到一张表中
insert into 表1 select * from 表2
将 select * from 表2 这条语句查询出来的数据插入到表1 中
修改数据
语法格式:
update 表名 set 字段名1=值1,字段名2=值2... where 条件;
注意:没有条件整张表数据全部更新。
删除数据
语法格式:
delete from 表名 where 条件;
注意:没有条件全部删除。
删除所有记录?
delete from 表名;
怎么删除大表中的数据?(重点)
truncate table 表名; // 表被截断,不可回滚。永久丢失。
删除表?
drop table 表名; // 这个通用。
drop table if exists 表名;
约束
什么是约束?常见的约束有哪些?
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有哪些呢?
非空约束(not null):约束的字段不能为NULL
唯一约束(unique):约束的字段不能重复
主键约束(primary key):约束的字段既不能为NULL,也不能重复(简称PK)
外键约束(foreign key):...(简称FK)
检查约束(check):注意Oracle数据库有check约束,但是mysql没有,目前mysql不支持该约束。
以下举例子来说明约束
非空约束
非空约束 not null:
create table t_user(
id int,
username varchar(255) not null,
password varchar(255)
);
当插入的username为null的时候会报错
insert into t_user(id,password) values(1,'123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value
唯一性约束
唯一性约束(unique)
* 唯一约束修饰的字段具有唯一性,不能重复。但可以为NULL。
* 案例:给某一列添加unique
create table t_user(
id int,
username varchar(255) unique // 列级约束
);
当插入的username有重复的时候就会报错
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'
案例:给两个列或者多个列添加unique 【表级约束】
create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode,username) // 多个字段联合起来添加1个约束unique
);
意思是 usercode+username 加在一起不能重复
注意:not null约束只有列级约束。没有表级约束。
主键约束
* 怎么给一张表添加主键约束呢? primary key
create table t_user(
id int primary key, // 列级约束
username varchar(255),
email varchar(255)
);
主键的特点:不能为NULL,也不能重复。
* 主键相关的术语?
主键约束 : primary key
主键字段 : id字段添加primary key之后,id叫做主键字段
主键值 : id字段中的每一个值都是主键值。
* 主键有什么作用?
- 表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键。
- 主键的作用:主键值是这行记录在这张表当中的唯一标识。(就像一个人的身份证号码一样。)
* 主键的分类?
根据主键字段的字段数量来划分:
单一主键(推荐的,常用的。)
复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式。)
根据主键性质来划分:
自然主键:主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的)
业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键。(不推荐用)
最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要
随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。
* 一张表的主键约束只能有1个。(必须记住)
主键自增
* mysql提供主键值自增:(非常重要。)
drop table if exists t_user;
create table t_user(
id int primary key auto_increment, // id字段自动维护一个自增的数字,从1开始,以1递增。
username varchar(255)
);
外键约束
* 关于外键约束的相关术语:
外键约束: foreign key
外键字段:添加有外键约束的字段
外键值:外键字段中的每一个值。
* 业务背景:
请设计数据库表,用来维护学生和班级的信息?
第一种方案:一张表存储所有数据
no(pk) name classno classname
-------------------------------------------------------------------------------------------
1 zs1 101 北京大兴区经济技术开发区亦庄二中高三1班
2 zs2 101 北京大兴区经济技术开发区亦庄二中高三1班
3 zs3 102 北京大兴区经济技术开发区亦庄二中高三2班
4 zs4 102 北京大兴区经济技术开发区亦庄二中高三2班
5 zs5 102 北京大兴区经济技术开发区亦庄二中高三2班
缺点:冗余。【不推荐】
第二种方案:两张表(班级表和学生表)
t_class 班级表
cno(pk) cname
--------------------------------------------------------
101 北京大兴区经济技术开发区亦庄二中高三1班
102 北京大兴区经济技术开发区亦庄二中高三2班
t_student 学生表
sno(pk) sname classno(该字段添加外键约束fk)
------------------------------------------------------------
1 zs1 101
2 zs2 101
3 zs3 102
4 zs4 102
5 zs5 102
* 将以上表的建表语句写出来:
t_student中的classno字段引用t_class表中的cno字段,此时t_student表叫做子表。t_class表叫做父表。
顺序要求:
删除数据的时候,先删除子表,再删除父表。
添加数据的时候,先添加父表,在添加子表。
创建表的时候,先创建父表,再创建子表。
删除表的时候,先删除子表,在删除父表。
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
cno int,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)
);
insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',102);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);
select * from t_class;
select * from t_student;
* 外键值可以为NULL?
外键可以为NULL。
* 外键字段引用其他表的某个字段的时候,被引用的字段必须是主键吗?
注意:被引用的字段不一定是主键,但至少具有unique约束。
存储引擎
完整的建表语句
CREATE TABLE `t_x` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意:在MySQL当中,凡是标识符是可以使用飘号括起来的。最好别用,不通用。
建表的时候可以指定存储引擎,也可以指定字符集。
mysql默认使用的存储引擎是InnoDB方式。
默认采用的字符集是UTF8
什么是存储引擎
存储引擎这个名字只有在mysql中存在。(Oracle中有对应的机制,但是不叫做存储引擎。Oracle中没有特殊的名字,
就是“表的存储方式”)
mysql支持很多存储引擎,每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点,需要在合适的时机选择合适的存储引擎。
查看当前mysql支持的存储引擎
mysql 5.5.36版本支持的存储引擎有9个:
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
常见的存储引擎
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎,但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表:
xxx.frm(存储格式的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)
优点:可被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。
缺点:不支持事务。
-----------------------------------------------------------------------------
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
优点:支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中(逻辑概念),无法被压缩,无法转换成只读。
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新。
-------------------------------------------------------------------------------------
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
缺点:不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点:查询速度最快。
以前叫做HEPA引擎。
事务
什么是事务
一个事务是一个完整的业务逻辑单元,不可再分。
比如:银行账户转账,从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)
为什么?因为它们这三个语句都是和数据库表当中的“数据”相关的。
事务的存在是为了保证数据的完整性,安全性。
假设所有的业务都能使用1条DML语句搞定,还需要事务机制吗?
不需要事务。
但实际情况不是这样的,通常一个“事儿(事务【业务】)”需要多条DML语句共同联合完成。
事务的特性
事务包括四大特性:ACID
A: 原子性:事务是最小的工作单元,不可再分。
C: 一致性:事务必须保证多条DML语句同时成功或者同时失败。
I:隔离性:事务A与事务B之间具有隔离。
D:持久性:持久性说的是最终数据必须持久化到硬盘文件中,事务才算成功的结束。
关于事务之间的隔离性
事务隔离性存在隔离级别,理论上隔离级别包括4个:
第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读(Dirty Read)现象:表示读到了脏的数据。
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是:不可重复读。
第三级别:可重复读(repeatable read)
这种隔离级别解决了:不可重复读问题。
这种隔离级别存在的问题是:读取到的数据是幻象。
第四级别:序列化读/串行化读(serializable)
解决了所有问题。
效率低。需要事务排队。
oracle数据库默认的隔离级别是:读已提交。
mysql数据库默认的隔离级别是:可重复读。
索引
什么是索引?索引有什么用?
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源。
在数据库方面,查询一张表的时候有两种检索方式:
第一种方式:全表扫描
第二种方式:根据索引检索(效率很高)
索引为什么可以提高检索效率呢?
其实最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护。是有维护成本的。
比如,表中的数据经常被修改,这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护。
添加索引是给某一个字段,或者说某些字段添加索引。
select ename,sal from emp where ename = 'SMITH';
当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值。
当ename字段上添加索引的时候,以上sql语句会根据索引扫描,快速定位。
怎么创建索引对象?怎么删除索引对象?
创建索引对象:
create index 索引名称 on 表名(字段名);
删除索引对象:
drop index 索引名称 on 表名;
什么时候考虑给字段添加索引?(满足什么条件)
* 数据量庞大。(根据客户的需求,根据线上的环境)
* 该字段很少的DML操作。(因为字段进行修改操作,索引也需要维护)
* 该字段经常出现在where子句中。(经常根据哪个字段查询)
主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高。尽量根据主键检索。
索引底层采用的数据结构是:B + Tree
索引的实现原理
通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,
最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。
select ename from emp where ename = 'SMITH';
通过索引转换为:
select ename from emp where 物理地址 = 0x3;
索引的分类
单一索引:给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引:主键上会自动添加索引
唯一索引:有unique约束的字段上会自动添加索引
....
索引什么时候生效
select ename from emp where ename like '%A%';
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的。
视图
什么是视图?
站在不同的角度去看到数据。(同一张表的数据,通过不同的角度去看待)。
怎么创建视图?怎么删除视图?
创建视图
create view myview as select 字段1,字段2 from 表名;
删除视图
drop view myview;
注意:只有DQL语句才能以视图对象的方式创建出来。
对视图进行增删改查,会影响到原表数据。
(通过视图影响原表数据的,不是直接操作的原表)可以对视图进行CRUD操作。
视图的作用?
视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,程序员只对视图对象进行CRUD。
数据库设计三范式
第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。多对多?三张表,关系表两个外键。
第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖。一对多?两张表,多的表加外键。
在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度。
一对一如何设计
有时候数据量太大,会把一张表分成两张表进行存储
一对一设计有两种方案:
主键共享
t_user_login 用户登录表
id(pk) username password
--------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk+fk) realname tel ....
------------------------------------------------
1 张三 1111111111
2 李四 1111415621
外键唯一
t_user_login 用户登录表
id(pk) username password
--------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk) realname tel userid(fk+unique)....
-----------------------------------------------------------
1 张三 1111111111 2
2 李四 1111415621 1