MySQL学习笔记
1.常用SQL命令
sql语句是不见 “ ; ” 不执行，并且不区分大小写。
使用管理员打开DOS窗口：win+R打开以后使用CTRL+shift+enter打开
显示密码登录：mysql -uroot -p密码
隐藏密码登录：mysql -uroot -p，接下来会让输入密码
退出：exit
查看数据库：show databases；
创建数据库：create database balabalaba(数据库名字)；
使用数据库：use balabala(数据名字库)
查看数据库下有哪些表：show tables;
导入数据库：source balabala（绝对路径）
注意：路径不能存在中文
查看表中数据：select * from bala（表名）;
查看表的结构，不关注数据：desc bala(表名);
查看MySQL的版本号：select version();
查看当前使用的数据：select database();
2.表的理解
2.1基础知识
数据库中最基本的元素是表：table。
因为表的直观性，所以数据库当中是以表的形式展示数据的
任何一张表都有行和列：
行（row）：被称为数据/记录
列（Column）：被称为字段
例如：姓名字段，年龄字段。
每一个字段都有字段名，数据类型，约束等属性
2.2约束(重点)
约束对应的单词：constraint
在创建表的时候，我们给字段加上约束，来保证输入数据的完整性，有效性，目的就是为了保证表中的数据正确有效。
约束的种类：
（1）非空约束：not null
create table t_vip (
id int,
name varchar(255) not null
);
在此约束为用户的名字不能为空
**插曲：**以sql为后缀的文件是sql脚本，里面包含了大量的sql语句，当我们执行脚本文件的时候，里面所有的sql语句都会执行，我们 可以通过 source 绝对路径 来执行sql脚本。
（2）唯一性约束：unique
create table t_vip (
id int unique , <—unique直接加到字段后面，被称为列级约束
name varchar(255)
);
在此约束中，id不可重复。
**注意：**多个字段联合唯一
create table t_vip (
id int ,
name varchar(255) ,
unique(id,name) <—像这样的被称为表级约束
)
同时unique和not null可以同时使用，在MySQL中，被两者同时修饰的字段会被默认为主键字段。
（3）主键约束：primary key(PK)
被主键约束的字段被称为主键字段，主键字段中的每一个值被称为主键值。
主键值是每一行数据的唯一标识。
语法：
create table t_vip (
id int primary key ,
name varchar(255)
);
除了单一主键和联合主键，主键还可以被分为自然主键（例如一个自然数）和业务主键（与业务密切相关，例如银行卡号）。
在MySQL中，使用auto-increment可以自动的帮我们维护主键。其表示自增，从一开始。
As：
create table t_vip(
id int auto-increment,
name varchar(255)
);
insert table t_vip value(“lihua”)
insert table t_vip value(“lihua”)
result：生成两个lihua但是一个id是1一个是2。
**注意：**一张表只能拥有一个主键。建议使用int、bigint、char作为主键，主键值一般都是定长的数字。
（4）外键约束：foreign key(FK)
添加了外键约束的被称为外键字段，外键字段中的每一个值被称为外键值。
外键约束就是一张表引用另一张表的一个字段作为约束条件。
As：
create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
no int primary key auto_increment,
name varchar(255),
cno int,
foreign key(cno) reference t_class(classno)
);
这里表示表t_student中的字段cno受表t_class中的字段classno约束
**注意：**被引用(reference)外键约束的字段至少包含唯一性
（5）检查约束：check（MySQL不支持，Oracle支持）
总结：
主键约束和唯一性约束都存在表级约束和列级约束。
表级约束和列级约束要注意区分开，列级约束是独立的，而表级约束是多个字段联合约束，表级约束的主键被称为联合主键。
在实际开发中建议使用单一主键。
3.SQL语句的分类
DQL：
数据查询语言（凡是带有select关键字的都是查询语句）
select . . .
1.简单查询
查询单个字段：select 字段名 from 表名；
查询多个字段：select 字段名1,字段名2 from 表名；
查询所有字段：select * from 表名；或者 select a,b,c from 表名；
注意：其中select和from类似Java被称为关键字，字段名和表名被称为标识符。
查询所有字段建议使用后者，因为前者效率低，可读性差。
给查询的列起别名：select 字段名 as 别名 from 表名；
查询字段去重：使用关键字distinct：select distinct job from emp;
多个字段去重：select distinct job,dept from emp;
注意：这个只是将显示中的字段名改变，并不会改变真的表名。
as可以省略，例如：select 字段名 别名；
使用单双引号将别名括起来可以解决别名包含空格、汉字的问题，例如：select dname “dept name” from 表;
接上，单引号是标准，但是在Oracle的数据库里面，” 使用不了，但可以在MySQL中使用
字段是支持运算的，例如select sal*12 from 表名；
2.条件查询
语法格式：
select
字段1,字段2,字段3
from
表名
where
条件；
用于条件查询中的符号：
<> 或 !=：不等于
between…and… 或 >= and =<：介于某某之间
is null：为空（is not null不为空）
and，or，in：且，或，在某范围中。in相当于多个or。
not not ：可以取非，主要用于is或in中
like（模糊查询，支持 % 和 _ 匹配）：模糊查询
注意：%可以匹配任意个字符，一个下划线只匹配单个字符
类似java等编程语言，像具有特殊含义的符号我们可以使用 \ 进行转义表达
例如：select ename from emp where ename like ‘%t’（查询名字以t结尾的员工）
大于小于等于不过多阐述，同Java类似，我们可以使用（）来提升优先级
3.排序
语法：
select
字段
from
表名
order by
字段；（默认是升序排序）
desc：降序 asc：升序
按照多个字段排序：
select 字段1,字段2 from 表名 order by 字段2 asc,字段1 asc（根据字段一进行升序排列，当字段一相同，则根据字段2排序）
4.数据处理函数
##### 4.1概念：
数据处理函数又被成为单行处理函数，其特点为一个输入对应一个输出，同时与单行处理函数对应的是多行处理函数（多个输入对应一个输出）。
4.2常见数据处理函数：
（1）ower 转换小写
As：select lower(ename) from emp;
（2）upper 转换大写
（3）substr 取子串（substr(被截取的字符串，起始下标，截取长度)）
As：select substr(ename,1,1) as ename from emp;
**注意：**下标是从1开始的！！！
（4）concat() 字符串拼接
（5）length 取长度
（6）trim 去空格
（7）str_to_date 将字符串转化为日期
（8）date_format 格式化日期
（9）format 设置千分位
case…when…then…when…then…else…end 相当于Java里面的分支选择语句
As：select job,sal,ename,(case job when ’SALEMAN‘ then sal*1.5 else sal end) from emp
将岗位为saleman的职员工资提升50%，其他的不做变化。
**注意：**该操作只是将提薪展示出来，并不会修改数据库里面的值
（10）round 四舍五入
如果使用字面量查询（select “abc” from emp;）
结果如下：
生成一个与原表行数相同的子段
round(1234.567，0)：将生成的字段进行四舍五入，第二个参数表示小数点后几位小数。
如果第二个参数是负数则表示十百千位
As：
如图，-1表示四舍五入到十位，可以此类推
（11）rand() 生成随机数
ifnull 将null转化成一个具体值
比如需要把null与其他整数相加，我们可以使用ifnull(字段，0)进行运算，这样表达的意思是，如果字段中存在null，则当作0处理
As：select ename, (sal + if(comm, 0)) * 12 from emp;
**注意：**在sql中有null参加的运算，结果统统为null。
5.分组函数
特点：输入多行最终输出一行
函数包括：
count 计数
sum 求和
avg 求平均值
max 最大值
min 最小值
As：示例：
分组函数注意事项：
分组函数必须先进行分组才能够使用，如果不进行分组，那么整张表默认为一组。
分组函数在使用之前就会自动忽略null，意味着我们不再需要处理null。
count(字段)与count(*)分别表示字段中不为null的行数，后者表示该表有多少行。
分组函数可以组合使用：
分组函数不能使用在条件查询里面，原因是分组函数必须在分组之后才能够使用（分组函数必须在group by之后执行）
至于为什么select sum(sal) from emp;可以执行，是因为select是在group by之后执行的
6.分组查询(重点)
概念：对数据先进行分组，在进行查询。
格式：
select
…
from
…
where
…
group by
…
order by
…
**注意：**以上代码执行顺序为：from where group by select order by
如果一条select函数中包含group by，那么select后面只能跟参加分组的字段和分组函数。
As：select job,sum(sal) from emp group by job;
对多个进行分组，则像排序那样
select dept,job max(sal) from emp group by dept,job
释义：先对部门分组，再对职业进行分组，最后找出每个部门不同职业最高薪资。
另外having可与group by组合使用，可以对分组查询出来的数据继续过滤。
As：select job,max(sal) from emp group by job having max(sal)>2000
挑选出不同职业最高薪资，且大于2000
不过该方法效率较低，可以这样做：
select job max(sal) from emp where sal>2000 group by job
**注意：**having不可代替where，只能与group by组合使用。
在实际操作中，优先使用where，where完成不了，我们在考虑使用having。
7.连接查询(重点)
概念：多张表联合起来查询数据被称为连接查询。
分类：
根据语法的年代分类：
SQL92：1992年出现的语法。
SQL99：1999年出现的语法。
根据表连接的方式分类：
1.内连接：
（1）等值连接
案例：查询每个员工所在部门名称，显示员工名和部门名，emp e和dept d进行连接。条件是：e.deptno = d.deptno
SQL92：
select
d.dname,e.ename
from
dept d,emp e
where
e.deptno = d.deptno;(还可以继续添加条件)
缺点：结构不清晰，表的连接条件和后期筛选的条件都放到了where后面。
SQL99:
select
e.ename,d.dname
from
emp e
inner(可省略) join
dept d
on
e.deptno = d.deptno（连接条件是等值关系，所以被称为等值连接）
where
后期筛选条件；
优点：结构清晰，表的连接条件和后期筛选的条件相互独立。
（2）非等值连接：条件为=以外。
（3）自连接：一张表看作两张表。
（4）特点：将能够满足匹配条件的数据查询出来。
2.外连接
（1）左外连接
格式：
select
e.ename,d.dname
from
emp e
left outer(可省略)
dept d
on
d.dname = e.ename;
（2）右外连接
（3）left/right代表关键字join的左/右的这张表作为主表。主要是为了将这张表全部查询出来，然后顺带查询右/左边的表。
所谓主表就是以哪张表的数据为基准开始查询
（4）在外连接的过程中，两张表产生了主次关系。不同于内连接，内连接的表之间都是对等关系。
3.全连接
**注意：**内连接和外连接可以同时出现混合使用，并且外连接查询次数>=内连接查询次数。
**注意：**在连接时我们必须要进行条件限制，否则就会发生笛卡儿积现象。
效率问题：
可以对字段添加表名，同时对字段起别名。
As：select dept.dname,emp.ename from dept d,emp e where d.deptno=e.deptno(SQL92语法);
笛卡尔积是集合的乘法，如果多个表进行连接，那么就会导致查询次数增多，效率降低，所以避免多个表进行连接。
8.子查询
概念：select语句中镶嵌select语句，被嵌套的select语句被称为子查询。子查询可以放在select、from、where里面。
实例：select ename,sal from emp where sal > ((select min(sal) from emp));
找出比最低工资高的员工的姓名和工资
9.union合并查询结果集
实例：查询职业为SALESMAN和MANGER的员工
select ename,job from emp where job in (“SALESMAN”,“MANGER”);
使用union实现：select ename,job from emp where job = “SALESMAN”
union
select ename,job from emp where job = “MANGER”;
在此，union的效率要更高一些，对于表连接来说，每一次连接匹配的次数都要满足笛卡尔积，匹配次数会成倍的翻，使用union可减少匹配次数。
union的使用注意事项：
结果集合并时，要求两个结果集列数相同、在Oracle的数据库中列和列的数据类型也要求相同。
10.limit
limit会将查询结果中的一部分取出来，通常用在分页查询里面。
使用方法：limit startIndex,length（意思为：(startIndex,startIndex+length] ）
分页公式：limit (pageNo-1)*pageSize , pageSize
**注意：**limit在order by之后执行。
DML：
数据操作语言（凡是对表中的数据进行增删改查的都是DML）
insert delete update
1.修改数据
语法格式：update 表名 set 字段1=值1，字段2=值2，字段3=值3 where 条件
**注意：**要注意一定要加限制条件，否则会导致所有的值更新。
2.删除数据
（1）语法：delete from 表名 where 限制条件
**注意：**如果没有限制条件，整张表的所有数据都会被删除。
（2）快速删除表中数据：delete from 表名；
**注意：**表中的数据是删除了，但是空间并没有被释放。
优点与缺点：
优点：支持回滚（rollback），后悔了还可以删除数据
缺点：删除效率比较低
同truncate对比。
3.插入数据
（1）语法：insert into 表名(字段1，字段2，字段3) values(值1，值2，值3)；
**注意：**字段名和值要一一对应。
如果没有给其他字段指定值，则默认是null。insert into 表名(name) values (“李华”)
如果存在子表父表，先插入父表，再插入子表。
插入多条数据：insert into 表名 (字段1，字段2，字段3) values (),(),();
（2）指定默认值：
create table 表名 (
字段名1 数据类型 default “balabala”，
字段名2 数据类型
)；
表示字段1的默认值是balabala。
（3）省略写法：
insert into 表名 values（值1，值2 …）
**注意：**values后面的括号要给每一个字段都赋值，如果有多个字段，而值只有一个，那么每个字段的值都会被唯一的值赋值，而与 规定的数据类型不匹配就会报错。
（4）插入日期：
数字格式化：format(数字, “格式”)
As：
str_to_date：将字符串varchar类型转为date类型
格式：str_to_date(字符串，“日期格式”)
MySQL中的日期格式：
%Y年
%m月
%d日
%h时
%i分
%s秒
str_to_date(“1990-10-11”,"%d-%m-%Y")
**注意：**如果接受的字符串就是“%Y-%m-%d”格式，那么则不需要调用该函数，当我们想要其他格式的日期，才需要使用该函数
date_format：将date类型转为varchar类型。
格式：date_format(date, “%Y/%m/%d”)
**注意：**在MySQL中，存在命名规范，如果标识符是多个单词，那么单词之间使用下划线隔开_。
MySQL默认的日期格式为："%Y/%m/%d"
（5）datetime和date的区别
date是短日期，只表示年月日
datetime是长日期，表示年月日时分秒
**注意：**MySQL里面，我们可以用now()函数来获取当前时间，并且这个值是datetime类型的。
DDL：
数据定义语言
凡是带有create、drop、alter的都是DDL语句
这里的增删改不同于DML语句，主要是对表的结构进行操作
1.建表
（1）语法格式：
create table 表名 (字段名1 数据类型，字段名2 数据类型)；
建议：表名可以以t_ 或者tbl_开头见名知意，字段名也是要见名知意，表名和字段名都属于标识符。
（2）mysql常见数据类型：
varchar长度可变字符串
优点：会根据实际需要动态分布内存，比较智能
确定：需要时间动态分布空间，速度慢
char定长字符串
优点：速度快，比较无脑。
缺点：分配固定空间存储数据，使用不当会造成资源浪费。
注意：：char和varchar一样，最长是255
int
数字中的整型，相当于Java里面的int
**注意：**最长10
bigint
数字中的长整型，相当于Java里面的long
float
单精度浮点型数据
double
双精度浮点型数据
date
短日期类型
datetime
长日期类型
clob（Character Large Object）
字符大对象，最多可以储存4g的字符串，比如一篇文章。
超过255的字符串都要用clob存储
blob（Binary Large Object）
二进制大对象，用来存储图片声音视频等媒体数据
**注意：**往blob类型的数据插入数据要使用IO流才行。
（3）快速创建表：create table emp2 as select * from emp;
实质就是把查询的表复制一下。
2.删除表
语法：drop table 表名
如果表不存在会报错，所以可以使用drop table if exist 表名。
**注意：**关于drop和truncate要注意区分。
删除表的时候先删子表，再删父表。
3.删除表中所有数据
truncate一次截断，在物理上全部删除
语法：truncate table 表名；
优点：快。
缺点：不支持回滚。
删除数据时使用truncate会比较快，使用delete会比较慢。
4.对表结构的增删改
（1）在实际开发中，我们很少会对表的结构进行增删改，因为成本较高
（2）如果真的需要修改，我们可以使用工具来进行实现
TCL：
事务控制语言：事务提交（commit）、事务回滚（rollback）
DCL：
数据控制语言
例如：授权grant、撤销权限revoke . . .
4.存储引擎(了解)
存储引擎是Mysql中独有的术语，其他数据库没有，Oracle有类似的东西，但是不叫这个。
我们可以使用show create table t_class;可以查看创建表的sql语句
As：
最后一行就是使用的存储引擎语句，其中ENGINE指定存储引擎，CHARSET指定编码模式。
其中MySQL默认的引擎是InnoDB，默认的编码方式是utf-8。
4.1查看引擎
MySQL支持九大存储引擎。
4.2常见存储引擎
（1）MYISAM引擎：
三个特征：
· 使用三个文件表示每个表：
格式文件：存储表结构的定义（mytable.frm）
数据文件：存储表行的内容（mytable.MYD）
索引文件：存储表上的索引（mytable.MYI）索引是一本书的目录，缩小搜索范围，提高查询效率。
· 可被转换为压缩、只读来节约空间
优点：
可被转换为压缩、只读来节约空间
**注意：**对于一张表，只要是主键或者是被unique约束的，会自动创建索引。
（2）InnoDB引擎：
前言：InnoDB是MYSQL默认的存储引擎，是一个重量级的存储引擎，其支持事务、数据库崩溃后的自动恢复机制，其主要特点就是安 全
特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理 – 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定 – 支持外键及引用的完整性，包括级联删除和更新
**注意：**InnoDB最大的特点就是支持事务，能够保证数据的安全。它不能只读、压缩，效率也并不高，不能很好的节约空间。
（3）MEMORY存储引擎：
前言：MEMORY引擎将数据存储在内存里面，且行的长度固定，所以其特点是很快。
特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。 （查的快正是因此）
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。
**注意：**MEMORY引擎以前被称为HEAP引擎。其优点是效率高，快速，缺点是关机后数据都会消失。
5.事务(重要)
5.1前言
概念：事务就是一个完整的业务流程，是一个最小的工作单元，不可再分。简单来说事务就是多个DML语句同时成功或者同时失败
注意：只有DML语句才会有事务这么一说，只有insert、delete、update才会对数据进行增删改，才会考虑到数据的安全性，要记 住数据安全第一位。
在InnoDB引擎中会提供一个记录事物的日志文件：
As：事务开始了 insert delete insert update 事务结束了
5.2事务提交与事务回滚
在事务的执行过程中，每一条DML语句都会被记录到 “ 事务性活动的日志文件 ” 中，并且我们可以提交事务也可以回滚事务。
（1）提交事务：清空事务性活动的日志文件，将数据全部持久化到数据库中，提交事务标志着事务全部成功的结束。
语法：commit；
（2）回滚事务：将之前的DML操作全部撤销，并清空事务性活动的日志文件，回滚事务标志着事务全部失败的结束
语法：rollback；
**注意：**回滚是回到上一次提交的位置。
在MySQL中，在执行完一条DML语句后，MySQL会自动提交事务，但是这样是不符合我们的开发习惯的，所以我们在事务开始前需要关闭事务的自动提交，通过执行：start transaction；
5.3事务的四个特性
A：原子性
说明事务是最小的工作单元，不可再分。
C：一致性
所有的事务要求，在同一个事务当中，所有的操作必须同时成功，或者同时失败，以保证数据的一致性。
I：隔离性
A事务和B事务之间具有一定的隔离。比如两个教室之间的墙，墙的厚都代表隔离级别。
D：持久性
事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。
5.4隔离级别
select @@tx_isolation ；可以查看当前的隔离级别。
set global transaction isolation level read uncommitted(隔离等级)；设置全局隔离等级。
**注意：**在修改隔离等级之后我们需要退出（exit）Mysql再重启才能够生效。
隔离等级分四个：
（1）读未提交：read uncommitted（最低的隔离级别）
读未提交就是事务A可以读取到事务B未提交的数据。
这种隔离级别存在脏读现象（Dirty Read）
这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是从读已提交开始的。
（2）读已提交：read committed
读已提交就是事务A可以读到事务B提交之后的数据。
该隔离级别解决了脏读现象
但是该隔离级别也存在问题：
不可重复读取数据。
释义：假如一张表，事务A对其进行操作，事务B也对其进行操作，我们第一次进行读取，读到了三个数据，第二次进行读 取，此时另一个事务已经对表进行了修改，我们读到了4条数据，这时我们第一次读取的数据如果想进行第二次读取就 没有办法了。
这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
**注意：**oracle数据库默认的隔离级别是：read committed
（3）可重复读：repeatable read
什么是可重复读取？
事务A开启之后，不管是多久，每一次在事务A中读取到的数据
都是一致的。即使事务B将数据已经修改，并且提交了，事务A
读取到的数据还是没有发生改变，这就是可重复读。
可重复读解决了什么问题？
解决了不可重复读取数据。
可重复读存在的问题是什么？
可以会出现幻影读。
每一次读取到的数据都是幻象。不够真实！
早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！
读到的是假象。不够绝对的真实。
**注意：**mysql中默认的事务隔离级别就是这个
（4）序列化/串行化：serializable（最高的隔离级别）
这是最高隔离级别，效率最低。解决了所有的问题。
这种隔离级别表示事务排队，不能并发！
synchronized，线程同步（事务同步）
每一次读取到的数据都是最真实的，并且效率是最低的。
6.索引
6.1前言
（1）概念：
索引是在数据库的字段上添加的，为了提高查询效率的一种机制。一张表的一个字段可以添加索 引，当然多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而 存在的一种机制。
（2）MySQL查询的两种方式：
全表扫描、根据索引检索。
**注意：**当我们查询某个数据时，如果又索引或者有条件限制，MySQL会到相应的字段进行查询，但是如果没有那么就会一个一个查 询，效率极低。
（3）底层原理
MySQL中索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同。TreeSet底层是一个自平衡的二叉树！在MySQL中索引是一个B-Tree数据结构。
6.2索引的实现原理
数据库中被unique和primary key修饰的字段会自动创建索引。数据库记录在硬盘里的每一条数据都会有一个物理存储编号。
其本质是B-tree，通过索引定位一个节点，该节点包含了所查数据的物理存储编号。
6.3需要我们主动添加索引的情况
（1）数据量庞大（至于到底多庞大，这个需要测试，因为每一个硬件环境不同）
（2）该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
（3）该字段很少的DML操作。（因为DML之后索引需要重新排序。）
**注意：**建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统性能，建议通过主键查询，或者unique约束的字 段进行查询，效率都是比较高的。
6.4索引的创建与删除
（1）创建：create index emp_ename_index on emp(ename);
（2）删除：drop index emp_ename_index on emp;
**注意：**我们可以使用explain select * from 表名; 查看type这一项，如果是ALL那么就没有索引，是REF就有索引。
6.5索引失效的几种情况
（1）当模糊匹配以”%“开头
select * from emp where ename like “%T”;
这时即使ename添加了索引，也不会走索引，因为模糊匹配以”%“开头。所以我们在实际操作中尽可能的避免模糊查询以%开头，这 是一种优化策略。
mysql> explain select * from emp where ename like '%T';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
（2）当使用or时，里面包含了没有使用索引的字段
如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个
字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | emp_ename_index | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
（3）使用复合索引的时候，没有使用左侧的列查找，索引失效（复合索引查询具有最左原则）
什么是复合索引？
两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。
create index emp_job_sal_index on emp(job,sal);
mysql> explain select * from emp where job = 'MANAGER';
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | emp | ref | emp_job_sal_index | emp_job_sal_index | 30 | const | 3 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
（4）在where当中索引列参加了运算，索引失效
mysql> create index emp_sal_index on emp(sal);
explain select * from emp where sal = 800;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
mysql> explain select * from emp where sal+1 = 800;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
（5）在where当中索引列使用了函数
mysql> explain select * from emp where lower(ename) = 'smith';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
（6）…
7.视图
7.1前言
什么是视图：以不同的角度去看同一份数据
我们对视图进行的操作，会影响到原表数据的改变。
7.2视图的创建与删除
创建视图对象：
create view dept2_view as select * from dept2;
删除视图对象：
drop view dept2_view;
注意：只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;
7.3试图存在的意义
create view
emp_dept_view
as
（只能写查询语句DQL）
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
问：假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
可以把这条复杂的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。并且利 于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。
我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。
注意：
视图对应的语句只能是DQL语句。
但是视图对象创建完成之后，可以对视图进行增删改查等操作。
小插曲：
增删改查，又叫做：CRUD。
CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。
一般都说CRUD。
C:Create（增）
R:Retrive（查：检索）
U:Update（改）
D:Delete（删）
8.DBA常用命令
（1）重点掌握：
数据的导入和导出（数据的备份）
其它命令了解一下即可。（这个培训日志文档留着，以后忘了，可以打开文档复制粘贴。）
（2）数据导出？
注意：在windows的dos命令窗口中：
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
可以导出指定的表吗？
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456
（3）数据导入？
注意：需要先登录到mysql数据库服务器上。
然后创建数据库：create database bjpowernode;
使用数据库：use bjpowernode
然后初始化数据库：source D:\bjpowernode.sql
9.数据设计三范式
9.1数据库三个范式
（1）第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
（2）第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
（3）第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。
9.2第一范式
最核心，最重要的范式，所有表的设计都需要满足。必须有主键，并且每一个字段都是原子性不可再分。
As：
学生编号 学生姓名 联系方式
------------------------------------------
1001 张三 zs@gmail.com,1359999999
1002 李四 ls@gmail.com,13699999999
1001 王五 ww@163.net,13488888888
以上是学生表，不满足第一范式，第一：没有主键。第二：联系方式可以分为邮箱地址和电话
9.3第二范式
建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖。
多对多关系数据库设计口诀：
多对多，三张表，关系表两个外键。
As：
学生编号 学生姓名 教师编号 教师姓名
----------------------------------------------------
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师
这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）
这是非常典型的：多对多关系！
分析以上的表是否满足第一范式？
不满足第一范式。
怎么满足第一范式呢？修改
学生编号+教师编号(pk) 学生姓名 教师姓名
----------------------------------------------------
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）
经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？
不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
产生部分依赖有什么缺点？
数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。
为了让以上的表满足第二范式，你需要这样设计：
使用三张表来表示多对多的关系！！！！
学生表
学生编号(pk) 学生名字
------------------------------------
1001 张三
1002 李四
1003 王五
教师表
教师编号(pk) 教师姓名
--------------------------------------
001 王老师
002 赵老师
学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
------------------------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002
9.4第三范式
第三范式建立在第二范式的基础之上，要求所有非主键字典必须直接依赖主键，不要产生传递依赖。
一对多数据库设计口诀：
一对多，两张表，多的表加外键
As：
学生编号（PK） 学生姓名 班级编号 班级名称
---------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
以上表的设计是描述：班级和学生的关系。很显然是1对多关系！
一个教室中有多个学生。
分析以上表是否满足第一范式？
满足第一范式，有主键。
分析以上表是否满足第二范式？
满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。
分析以上表是否满足第三范式？
第三范式要求：不要产生传递依赖！
一年一班依赖01，01依赖1001，产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。
那么应该怎么设计一对多呢？
班级表：一
班级编号(pk) 班级名称
----------------------------------------
01 一年一班
02 一年二班
03 一年三班
学生表：多
学生编号（PK） 学生姓名 班级编号(fk)
-------------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
9.5写在最后
数据库设计三范式是理论上的。实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。
王五
教师表
教师编号(pk) 教师姓名
--------------------------------------
001 王老师
002 赵老师
学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
------------------------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002
9.4第三范式
第三范式建立在第二范式的基础之上，要求所有非主键字典必须直接依赖主键，不要产生传递依赖。
一对多数据库设计口诀：
一对多，两张表，多的表加外键
As：
学生编号（PK） 学生姓名 班级编号 班级名称
---------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
以上表的设计是描述：班级和学生的关系。很显然是1对多关系！
一个教室中有多个学生。
分析以上表是否满足第一范式？
满足第一范式，有主键。
分析以上表是否满足第二范式？
满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。
分析以上表是否满足第三范式？
第三范式要求：不要产生传递依赖！
一年一班依赖01，01依赖1001，产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。
那么应该怎么设计一对多呢？
班级表：一
班级编号(pk) 班级名称
----------------------------------------
01 一年一班
02 一年二班
03 一年三班
学生表：多
学生编号（PK） 学生姓名 班级编号(fk)
-------------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
9.5写在最后
数据库设计三范式是理论上的。实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。
扫一扫
166
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
