MySQL数据库学习
1.数据库概述
数据库（数据库管理系统：简单的sql语句）
ObjectOutputStream --> Java对象（张三 20 …) -->序列化到文件当中。
反序列化
new java对象
readObject()
writeObject()
常见的有哪些数据库管理系统呢？
IBM -->eclipse
Oracle 甲骨文 （SUN：太阳)
Oracle做数据库起家的。
Oracle --> MySQL AB 公司
Oracle MySQL DB2 SyBase “MS SqlServer 支持标准sql的数据库管理系统”
2.MySQL的安装与配置
3.DB DBMS SQL的关系
3.1 sql 、DB、DBMS分别是什么，他们之间的关系？
DB:DataBase（数据库，数据库实际上在硬盘的形式存在）
DBMS:DataBase Management System(数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer…)
SQL:
结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。
SQL属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。
SQL语句在执行的时候，实际上内部也会先进行编译，然后在执行sql.(sql语句的编译由DBMS完成。)
DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。
DBMS -(执行) -> SQL ->DB
## 4.对表的理解
表：table
表：table是数据库的基本组成单元。所有的数据都以表格的形式组织，母的是可读性强。
一个表包括行和列：
行：表示数据（data)
列：表示字段 (column)
学号(int) 姓名(varchar) 年龄(int)
110 张三 20
120 李四 21
每一个字段应该包括哪些属性？
字段名、数据类型、相关的约束
5.对SQL语句的分类
学习MySQL主要还是学习通用的SQL语句，那么SQL语句包括增删改查，SQL语句怎么分类呢？
DQL（数据查询语言）:查询语句，凡是select语句都是DQL
DML（数据操作语言）:insert delete update,对表当中的数据进行增删改
DDL（数据库定义语言）:create drop alter,对表结构的增删改。
TCL (事务控制语言) :commit 提交事务，rollback回滚事务(TCL中的T是transactional)
DCL（数据控制语言）:grant授权、revoke撤销权限等。
6.导入初始化数据
第一步：登录mysql数据库管理系统
dos命令窗口：
mysql -uroot -proot
第二步：查看有哪些数据库
show databases;（这个不是SQL语句，属于MySQL的命令。）
第三步：创建属于我们的数据库
create database bjpowernode;（这个不是SQL语言，属于MySQL的命令）
第四步：使用bjpowernode数据
use bjpowernode;（这个不是SQL语句，属于MySQL的命令）
第五步：查看当前使用的数据库中有哪些表？
第六步：初始化数据
source …/…/…/*.sql
注意：数据初始化完成之后，就可以看到数据库中有表了
6.对SQL脚本的理解
*.sql，这个文件以sql结尾，这样的文件被称为“sql脚本”。什么是sql脚本呢？
当一个文件的扩展名是.sql,并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
注意:直接使用source命令可以执行sql脚本。
sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。
删除数据库： drop database bjpowernode
7.查看表结构及表中数据
desc 表名：查看表结构
8.MySQL的常用命令
select database(); 查询当前使用的数据库
select version(); 查询数据库版本也可以使用
\c 命令，结束一条语句
exit 退出MySQL
show create table emp; 查看创建表的语句
9.简单查询
简单的查询语句（DQL)
语法格式：
select 字段名1，字段名2，字段名3，… from 表名;
提示：
1.任何一条sql语句以";"结尾。
2.sql语句不区分大小写。
给查询结果的列重命名？
select id*10 as ‘id乘以10’ from hr;
select name as ‘姓名’ from role;
注意：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用
as关键字可以省略吗？ 可以
select name myname from role;
10.条件查询
语法格式：
select 字段，字段，… from 表名 where 条件；
执行顺序：先from,然后where,最后select
查询id等于3的角色的名字？
select name from role where id = 3;
找出id大于3的角色的名字？
select name from role where id>3;
找出id不等于3信息？
select * from role where id <> 3; 或 select * from role where id != 3;
找出id在4和17的信息？
select * from role where id between 4 and 17;
11.条件查询 is null 和is not null
在数据库当中NULL不是一个值，代表什么也没有，为空。
空不是一个值，不能用等号衡量。
必须使用is null 或者 is not null
select * from role where name is not null;
select * from role where name is null;
select * from emp where comm is null or comm=0;
12.and和or的优先级问题
select * from role where id < 10 and id > 14;
13. 条件查询in
in等同于or
select * from role where name in(‘ROLE_test3’,‘ROLE_test5’);
not in:不在这几个值当中
select * from role where name not in(‘ROLE_test3’,‘ROLE_test5’);
14.模糊 查询like?
找出名字当中含有L的？
(在模糊查询当中，必须掌握两个特殊的符号，一个是%,一个是_)
%代表任意多个字符，_代表任意1个字符。
select * from role where name like ‘%L%’;
select * from role where name like ‘%\_%’;
15.数据排序(生序和降序)
select * from role order by id asc; //升序
select * from role order by id desc,nameZh desc;//降序
select
字段 3
from
tablename 1
where
条件 2
order by
… 4
order by 是最后执行的。
16.分组函数
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
记住：所有的分组函数都是对“某一组”数据进行操作的。
找出工资总和？
select sum(sal) from emp;
找出最高工资？
select max(sal) from emp;
找出最低工资？
select min(sal) from emp;
找出平均工资？
select avg(sal) from emp;
找出总人数？
select count(*) from emp;
select count(ename) from emp;
分组函数一共有5个，count, sum,avg,max,min
分组函数还有另一个名字：多行处理函数。
多行处理函数的特点：输入多行，最终输出的结果是1行。
分组函数自动忽略null.
select count(comm) from emp;
select ename,sal from emp where sal>avg(sal); //ERROR 1111(HY000):Invalid use of group function
思考以上的错误信息：无效的使用了分组函数？
原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。why???
怎么解释？
因为group by 是在where执行之后才会执行的。
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
count(*)和count(具体的某个字段），它们有什么区别？
count(*)：不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(comm):表示统计comm字段中不为NULL的数据总数量。
分组函数也能组合起来用：
select count(*),sum(*) from role;
单行处理函数
什么是单行处理行数？
输入一行，输出一行。
计算每个员工的年薪？
select ename,(sal+comm)*12 as yearsal from emp;
重点：所有数据库都是这样规定的，只要有null参与的运算结果一定是null.
ifnull() 空处理函数？
ifnull(可能为null的数据，被当做什么处理)\
使用ifnull函数
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
17.group by 和having
group by:按照某个字段或者某些字段进行分组。
having:having是对分组之后的数据进行再次过滤。
案例：找出每个工作岗位的最高薪资。
select max(sal) from emp group by job;
注意：分组函数一般都会和group by 联合使用，这也是为什么他被称为分组函数的原因。
并且任意一个分组函数（count sum avg max min )都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。
select ename,max(sal),job from emp group by job;
以上在mysql当中，查询结果是有的，但是结果没有意义，在Oracle数据库当中会报错。语法错误。
Oracle的语法规则比MySQL语法规则严谨。
记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
每个工作岗位的平均薪资？
select job,avg(sal) from emp group by job;
多个字段能不能联合起来一块分组？
案例：找出每个部门不同工作岗位的最高薪资。
select max(sal),deptno,job from emp group by deptno,job;
找出每个部门的最高薪资，要求显示薪资大于2900的数据。
select max(sal),deptno from emp group by deptno having max(sal)>2900; //这种方式效率低
select max(sal),deptno from emp group by where sal > 2900 group by deptno;//效率较高，建议能够使用where过滤的尽量使用where。
找出每个部门的平均薪资，要求显示薪资大于2000
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
where后面不能使用分组函数：
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno; //错误了。
这种情况就只能用having进行过滤。
18.总结DQL语句的执行顺序
总结一个完整的DQL语句怎么写？
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
关于查询结果集的去重？
select distinct job from emp; //distinct关键字去除重复记录。
select ename,distinct job from emp; \\X
以上的sql语句是错误的。
记住：distinct 只能出现在所有字段的最前面。
select distinct deptno,job from emp; //deptno,job两者进行联合去重。
案例：统计岗位的数量？
select count(distinct job) from emp;
19.连接查询概述
1.什么是连接查询？
在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。
stuno stuname classno classname
===================================================
1 zs 1 高三1班
2 ls 1 高三1班
学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。
2.连接查询的分类？
根据语法出现的年代来划分的话，包括：
SQL92 (一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator,数据库管理员)
SQL99 (比较新的语法)
根据表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（这个不讲，很少用！）
3.在表的连接查询方面有一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）
案例：找出每一个员工的部门名称，要求显示员工名和部门名。
select ename,dname from emp,dept;
笛卡尔现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表的乘积。
关于标的别名：
select e.ename,d.dname from emp e,dept d;
表的别名有什么好处？
第一：执行效率高。
第二：可读性好。
怎么避免笛卡尔积现象？当然是加进行过滤。
思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
不会，次数还是56次。只不过显示的是有效记录。
案例：找出每一个员工的部门名称，要求显示员工名和部门名。
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno; //SQL92，以后不用。
20.内连接的等值连接
特点：最大特点是，条件是等量关系。
案例：查询每个员工的部门名称，要求显示员工名和部门。
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; //太老了，不用了
改为：
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
语法：
select
…
from
A
(inner) join //内连接，inner可以省略
B
on
连接条件
where
…
21.内连接的非等值连接
特点：连接条件中的关系是非等量关系。
案例：找出每个员工的工资等级，要求显示员工名，工资，工资等级。
select
e.ename,e.sal,s.grade
from
emp join salgrade
on
e.sal between s.losal and s.hisal;
22.内连接的自连接
特点：最大的特点是，一张表看做两张表。自己连接自己。
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。
select e1.ename,e2.name from emp e1 join emp e2 on e1.mgr=e2.empno;
23.外连接
什么是外连接，和内连接有什么区别？
内连接：
假设A和B进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内 连接。A，B两张表没有主副之分，两张表示平等的。
外连接：
假设A和B表进行连接，使用外连接的话，A,B两张表中有一张表示主表，一张表示副表，主要 查询主表中的数据，稍带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动 模拟出NULL与之匹配。
外连接的分类？
左外连接（左连接）：表示左边的这张表示主表。
右外连接（右连接）：表示右边的这张表示主表。
左连接有右连接的写法，右连接也会有左连接的写法。
案例：找出每个员工的上级领导？（所有员工必须全部查询出来）
//左外连接
select e1.ename ‘员工’,e2.name ‘领导’ from emp e1 left join emp e2 on e1.mgr=e2.empno;
//右外连接,外连接outer关键字可以省略；内连接inner关键字可以省略
select e1.ename ‘员工’,e2.name ‘领导’ from emp e2 (outer) right join emp e1 on e1.mgr=e2.empno;
外连接最重要的特点是：主表的数据无条件的全部查询出来
23.三张以上表连接查询
三张表怎么连接查询？
注意，解释一下：
…
A
join
B
join
C
表示A先与B表连接，然后在于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;
案例：找出每一个员工的部门名称、工资等级、以及上级领导。
select
e1.ename ‘员工’,e2.name ‘领导’,d.dname,s.grade,
from
emp e1
join
dept d
on
e1.deptno = d.deptno
join
salgrade s
on e.sal between s.losal and s.hisal
left join
emp e2
on e1.mgr = e2.empno;
24.navicat工具简单介绍
25.子查询
什么是子查询？子查询都可以出现在哪里？
select 语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select
…(select)
from
…(select)
where
…(select)
1.where 子句中使用子查询
案例：找出高于平均薪资的员工信息
select * from emp where sal > avg(sal); //错误的写法，where后面不能直接使用分组函数。
第一步：找出平均薪资
select avg(sal) from emp;
第二步：where 过滤
select * from emp where sal > 2073.214286;
第三步：第一步与第二步合并
select * from emp where sal>(select avg(sal) from emp);
2.from 后面嵌套子查询
案例：找出每个部门平均薪水的平均等级。
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) avgsal from emp group by deptno;
第二步：将第一步的查询结果当做临时表t,让t表和salgrade表连接，条件是：t.avgsal between s.losal and hosal
select
t.* ,s.grade
from
(select deptno,avg(sal) avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
案例：找出每个部门平均的薪水等级。
第一步：找出每个员工的薪水等级。
select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
第二步：
select
e.deptno,avg(s.grade )
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
3.在select后面嵌套查询。
案例：找出每个员工所在的部门名称，要求显示员工名和部门名。
写法一：
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
写法二：
select
e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
26.union的用法
案例：找出工作岗位是SALESMAN和MANAGER的员工。
第一种：
select ename,job from emp where job=‘MANAGER’ or job=‘SALESMAN’;
第二种：
select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’);
第三种：union
select ename,job from emp where job=‘MANAGER’
union
select ename,job from emp where job=‘SALESMAN’;
两张不相干的表中的数据拼接在一起显示？
select ename from emp
union
select dname from dept;
//上面union前后的两个查询，起字段的个数要相同，否则不能加在一起。
27.limit以及通用分页SQL
1.limit是mysql特有的，其他数据库中没有。不通用。（Oracle中有一个相同的机制，叫做rownum)
2.limit取结果集中的部分数据，这是它的作用。
3.语法机制：
limit startIndex,length
startIndex表示其实位置
length表示取几个
案例：取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc;
取前5个：
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;
4.limit是sql语句最后执行的一个环节：
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit 7
…;
5.案例：找出工资排名在第4到第9名的员工？
select ename,sal from emp order by sal desc limit 3,6;
6.通用的标准分页sql?
每页显示3条记录：
第一页：0, 3
第二页：3, 3
…
第n页： 3(n-1),3;
每页显示pageSize条记录：
第pageNo页：3(pageNo-1),pageSize
pageSize是什么？显示第几页
java代码{
int pageNo = 2; //页码是2
int pageSize = 10;//每页显示10条
limit (pageNo-1)*pageSize,pageSize;
}
28.表的创建
建表语句的语法格式：
create table 表名(
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型，
…
);
关于MySQL当中字段的数据类型？以下只说常见的
int 整数型
bigint 长整型
float 浮点型
char 定长字符串
varchar 可变长字符串
date 日期类型
BLOB 二进制大对象（存储图片，视频等流媒体信息）Binary Large OBject
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。）Charcter Large OBject
…
char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生变化的时候，是定长的，例如：性别，生日等都是采用char.当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。
BLOB和CLOB类型的使用？
电影表：t_movie
id(int) name(varchar) playtime(date/char) haibao(BLOB) history(CLOB)
============================================================================
1 蜘蛛侠
2
3
表名在数据库当中一般建议以：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)
);
29.向表中插入数据
insert语句插入数据
语法格式：
insert into 表名(字段名1，字段名2，字段名3,…) values (值1，值2，值3，…)
要求：字段的数量和值得数量相同，并且数据类型要匹配。
需要注意的地方：
当一条insert语句执行成功之后，表格当中必然会多一行记录。
即使多的这一行记录当中某些字段是NULL，后期也没有办法执行insert语句插入数据了，只能使 用update更新数据。
//字段可以省略不写，但是后面的value对数量和顺序都有要求。
insert into t_student values(1,‘jack’,‘0’,‘gaosan2ban’,‘1986-10-23’);
//一次插入多行数据
insert into t_student(no,name,sex,classno,birth) values(3,‘rose’,‘1’,‘gaosi2ban’,‘1994-12-26’),
(4,‘tom’,‘0’,‘gaosi2ban’,‘1999-10-26’)
30.表的复制以及批量插入
表的复制：
语法：
create table 表名 as select 语句；
将查询结果当做表创建出来。
将查询结果插入到一张表中？
insert into dept1 select * from dept;
31.修改表中的数据
修改数据：update
语法格式：
update 表名 set 字段名1=值1，字段名2=值2，… where 条件；
注意：没有条件整张表数据全部更新。
案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU
update dept1 set loc=‘SHANGHAI’,dname=‘RENSHIBU’ where deptno=10;
更新所有记录
update dept1 set loc=‘x’,dname=‘y’;
32.删除表中的数据
删除数据：
语法格式：
delete from 表名 where 条件；
注意：没有条件全部删除；
删除10部门数据？
delete from dept1 where deptno=10;
删除所有记录？
delete from dept1;
怎么删除大表中的数据？(重点)
truncate table emp1;//表被截断，不可回滚。永久丢失。
33.关于表结构的修改
对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select (这些都是表中的数据操作）
增删改查有一个术语：CRUD操作
Create(增) Retrieve(检索) Update(修改) Delete(删除)
34.约束作用及常见约束
什么是约束？常见的约束有哪些？
在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有哪些呢？
非空约束（not null)：约束的字段不能为NULL
唯一约束（unique):约束的字段不能重复
主键约束（primary key):约束的字段既不能为NULL，也不能重复（简称PK)
外键约束（foreign key): …(简称FK)
检查约束（check):注意Oracle数据中有checkh约束，但是mysql中没有，目前mysql不支持该约束。
表格
id username(唯一性约束) password(非空约束)
=================================================
1 张三 123456
非空约束 not null
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) not null,
password varchar(255)
);
insert into t_user(id,password) values(1,‘123’);//报错，Field ’username’ doesn’t have a default value
改为：
insert into t_user(id,username,password) values(1,‘lisi’,‘123’);
唯一性约束（unique)
唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。
案例：
drop table if exists t_user;
create table t_user(
id it,
username varchar(255) unique //列级约束
);
案例：给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int,
uercode varchar(255),
username varchar(255),
unique(usercode,username) //表级约束
);
注意：not null约束只有列级约束，没有表级约束
主键约束：
怎么给一张表添加主键约束呢？
drop table if exists t_user;
create table t_user(
id int primary key,
username varchar(255),
email varchar(255)
);
id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。
主键特点：不能为NULL，也不能重复。
主键相关的术语？
主键约束：primary key
主键字段：id字段添加primary key之后，id叫做主键字段
主键值：id字段中的每一个值都是主键值。
主键有什么作用？
表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）
主键的分类？
根据主键字段的字段数量来划分：
单一主键
复合主键（多个字段联合起来添加一个主键约束）（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分：
自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
业务主键： 主键值和系统的业务挂钩，例如：拿着银行卡卡号做主键，拿着身份证号码作为主键。（不推荐使用）最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。
一张表的主键约束只能有一个。（必须记住）。
使用标记约束方式定义主键：
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
);
insert into t_user(id,username) values(1,‘zs’);
insert into t_user(id,username) values(2,‘ls’);
insert into t_user(id,username) values(3,‘ww’);
insert into t_user(id,username) values(4,‘jack’);
以下内容是演示以下复合主键，不需要掌握。
drop table if exists t_user(
id int,
username varchar(255),
password varchar(255),
primary key(id,username)
);
insert …
mysql提供主键值自增（auto_increment)
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);
insert into t_user(username) values(‘a’);
insert into t_user(username) values(‘b’);
insert into t_user(username) values(‘b’);
insert into t_user(username) values(‘c’);
select * from t_user;
提示：Oracle当中也提供了一个自增机制，叫做：序列（sequence)对象。
外键约束
关于外键约束的相关术语：
外键约束：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 103 高三2班
缺点：冗余。【不推荐】
第二种方案：两张表（班级表和学生表）
t_class 班级表
cno(pk) cname
=====================
101 高三1班
102 高三2班
t_student 学生表
sno(pk) sname cno(该字段添加外键约束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,‘高三1班’);
insert into t_class vlaues(102,‘高三2班’);
insert into t_student values(1,‘zs1’,101);
insert into t_student values(2,‘zs2’,102);
外键可以为NULL吗？
外键可以为NULL。
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具有unique约束。
35.存储引擎
存储引擎
完整的建表语句
create table t_x (
id int (11) default null
)engine=InnoDB default charset=utf8;
注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好不用，不通用。
建表的时候可以指定存储引擎，也可以指定字符集。
mysql默认适用男的存储引擎是InnoDB方式。
默认采用的字符集是UTF8.
什么是存储引擎？
存储引擎这个名字只有在mysql中存在，（Oracle中对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“标的存储方式”
mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。
查看当前mysql支持的存储引擎？
show engines \g
mysql 5.7.29-log支持的存储引擎有：
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
常见的存储引擎：
Engine: MyISAM
Support: Yes
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savapoints: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
Savapoints:NO
缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。
以前叫做HEPA引擎。
36.事务概述（Transaction)
什么是事务？
一个事务是一个完成的业务逻辑单元，不可再分。
比如：银行账户转账，从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语句共同联合完成。
37.事务的原理
假设一个事儿，需要先执行一条insert,在执行一条update,最后执行一条delete,这个事儿才算完成。
开启事务机制：
执行insert语句–>insert…(这个执行成功之后，把这个执行记录到数据库中的操作历史当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。)
执行update语句–>update…(这个执行也是记录一下历史记录，不会真正的修改硬盘上的数据)
执行delete语句–>delete…(这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据)
提交事务或者回滚事务（结束）
38.事务的特性
事务包括四大特性：ACID
A:原子性：事务是最小的工作单元，不可再分。
C:一致性：事务必须保证多条DML语句同时成功或者同时失败。
I:隔离性：事务A与事务B之间具有隔离。
D:持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。
39.事务的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括4个：
第一级别：读未提交（read uncommited)
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读（Dirty Read)现象：表示读到了脏的数据。
第二级别：读已提交（read committed)
对方事务提交之后的数据我方可以读取到。
读已提交存在的问题是：不可重复读。
第三级别：可重复读（repeatable read)
这种隔离级别解决了：不可重复读问题。
这种隔离级别存在的问题是：读取到的数据是幻象。
第四级别：序列化读、串行化读(serializable)
解决了所有问题。
效率低。需要事务排队。
oracle数据库默认的隔离级别是：读已提交。
mysql数据库默认的隔离级别是：可重复读。
40.演示事务
mysql事务默认情况下是自动提交的。
（什么是自动提交？只要执行任意一条DML语句则提交一次。）怎么关闭自动提交？start transaction;
准备表：
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);
演示：mysql中的事务是支持自动提交的，
只要执行一条DML语句，则自动提交一次。
使用两个事务演示以上的隔离级别
第一：演示read uncommitted
设置事务的隔离级别：set global transaction isolation level read uncommitted;
查看事务的全局隔离级别：select @@global.tx_isolation;
第二：演示read committed
第三：演示repeatable read
第四：演示serializable
41.索引
1.什么是索引？有什么用？
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式。
第一种方式：全变扫描
第二种方式：根据索引检索（效率很高）
索引为什么可以提高检索效率呢？
其实最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断地维护。是有维护成本的。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
添加索引是给某一个字段，或者说某些字段添加索引。
select ename,sal from emp where ename = ‘SMITH’;
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
当ename字段上添加了索引的时候，以上sql语句会根据索引扫描，快速定位。
2.怎么创建索引对象？怎么删除索引对象？
创建索引对象：
create index 索引名称 on 表名(字段名);
删除索引对象：
drop index 索引名称 on 表名;
3.什么时候考虑给字段添加索引？（满足什么条件）
数据量庞大。（根据客户的需求，根据线上的环境）
该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
该字段经常出现在where子句中。（经常根据哪个字段查询）
4.注意：主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高。尽量根据主键检索。
5.查看sql语句的执行执行计划：
explain select ename,sal from emp where sal = 5000;
给薪资sal字段添加索引
create index emp_sal_index on emp(sal);
6.索引底层采用的数据结构是：B+ Tree
7.索引的实现原理？
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
select ename from emp where ename='SMITH ';
通过索引转换为：
select ename from emp where 物理地址 = 0x3;
8.索引的分类？
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加1个索引
主键索引：主键上回自动添加索引
唯一索引：有unique约束的字段上会自动添加索引
…
9.索引什么时候失效？
select ename from emp where ename like ‘%A%’;
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。
42.视图(view)
1.什么是视图？
站在不同的角度去看待数据。（同一张表的数据，通过不同的角度去看待）。
2.怎么创建视图？怎么删除视图？
create view myview as select empno,ename from emp;
drop view myview;
3.对视图进行增删改查，会影响到原表数据（通过视图影响原表数据的，不是直接操作的原表）。
可以对视图进行CRUD操作。
4.面向视图操作？
select * from myview;
5.视图的作用？
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。
43.数据库数据的导入导出
DBA命令
将数据库中数据导出
在Windows的dos命令窗口中执行（导出整个库）：
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123
在Windows的dos命令窗口中执行（导出指定数据库当中的指定表）
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123
导入数据
create database bjpowernode;
user bjpowernode;
source D:\bjpowernode.sql
44.数据库设计三范式
什么是设计范式？
设计表的依据，按照这个三范式设计的表不会出现数据冗余。
三范式都是哪些？
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖。
多对多？三张表，关系表两个外键。
t_student学生表
sno(pk) sname
====================
1 张三
2 李四
3 王五
t_teacher 讲师表
tno(pk) tname
======================
1 王老师
2 张老师
3 李老师
t_student_teacher_relation 学生讲师关系表
id(pk) sno(fk) tno(fk)
=============================
1 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3
第三范式：建立在第二范式的基础之上，所有非主键字段不能传递依赖于主键字段。（不能产生传递依赖）。
一对多？ 两张表，多的表加外键
班级t_class
cno(pk) cname
============================
1 班级1
2 班级2
学生t_student
sno(pk) sname classno(fk)
====================================
101 张1 1
102 张2 1
103 张3 2
104 张4 2
105 张5 2
提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。
45.表的设计经典设计方案
一对一怎么设计？
这张表很大？
一对一设计有两种方案：主键共享
t_user_login 用户登录表
id(pk) username password
==================================
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk+fk) realname tel
================================
1 张三 11111111111
2 李四 12244445111
一对一设计有两种方案：外键唯一
t_use_login 用户登录表
id(pk) username password
==================================
1 zs 123
2 ls 456
t_user_detail
id(pk) realname tel userId(fk+unique)
========================================================
1 张三 11111111111111 1
2 李四 11111122222555 2
46.MySQL优化
1.为什么要学习MySql优化
2.查询过慢原因：
2.1有规律速度过慢：用户访问量激增 mysql集群
2.2无规律速度过慢：跟表中数据量变化有关系。 查询语句优化
3.判断查询语句是否需要优化依赖工具
3.1慢查询记录
3.2explain执行计划
3.3相关日志文件
4.mysql查询语句中七个查询命令特征（根据七个查询执行优先级）
4.1from：
【作用】：1）将硬盘上的表文件加载到内存中，生成一个全新的临时表
2）定位内存中已经存在的临时表
【注意】：1）在一个查询语句中，第一个执行命令永远都是from
2）from定位的是内存中的一个临时表，这个临时表必须手动指定表名
4.2where:
【作用】：1）where命令操作的有from命令生成的临时表
2）where命令循环遍历当前临时表中的每一个数据行，将满足条件的数据行 保存到一个全新的临时表
【注意】：
由于where命令每次操作只是一个数据行，因此在where使用过程中，是 无法使用聚合函数作为判断条件
4.3group by
【作用】：1）首先对临时表中的数据进行一次排序处理
2） 然后将具有相同特征的数据行保存到同一个临时表
【注意】：
1）多字段分组
首先，分组字段执行顺序对于查询结果来说没有任何影响的，然后，从第 二个分组字段开始，操作临时表示上一个分组字段生成的临时表
2）如果select操作临时表是由group by 提供。
此时select将遍历group by 生成的每一个临时表
在操作具体临时表时，select只会读取指定字段中每一个数据行内容
4.4having:
【作用】：
负责将groupby生成的临时表中不满足条件的临时表进行删除
【注意】：
1）.having命令不能独立出现的，只能出现在group by 命令后面
2）having 命令每次操作的是一个临时表，因此选择判断条件应该来自聚 合函数
4.5select:
【作用】：1）select操作的临时表，由from或者where命令来提供的。
select将制定字段中所有内容读取出来。
将读取的内容组成一个全新的临时表
2）select操作的 临时表，由group by或者having命令提供的
此时select将遍历生成的每一个临时表
在操作具体临时表示，select只会读取制定字段中
在第一个数据行内容
4.6order by
【作用】
专门针对select生成的临时表中数据行进行排序
将排序后内容组成一个全新的临时表
【注意】：
如果order by 使用字段名进行排序时，字段名可以不出现在
select生成的临时表
如果order by使用字段顺序进行排序时，索引位置必须在
select查询语句中出现
4.7limit
【作用】：
对临时表中数据行进行截取
5.查询语句特征：
5.1 七个查询命令中，除了having命令之外。剩下的六个查询命令执行完毕后都会生成全新的临时表。
5.2 七个查询命令中，出来from命令之外，剩下的六个查询命令操作的临时表都是上一个查询命令生成临时表
5.3 在当前查询命令执行完毕后，mysql自动的将上一个查询命令生成的临时表进行销毁处理，所以在一个查询语句执行完毕后，用户只能看到最后一个查询命令生成的临时表
5.4 在进行多字段分组查询时，从第二个分组字段开始，操作的临时表是上一个分组字段生成的临时表
6.七个查询命令中，那些命令执行效率过慢：
6.1 执行速度最慢的：group by
【原因】：group by 执行时首先将临时表中数据进行【排序】
然后再进行分组
6.2 order by:
【原因】：需要对select生成的临时表中数据行进行一次【排序】
然后将排好顺序的数据行组成一个全新的临时表
6.3 where：
【原因】：需要对表中所有数据行进行遍历。如果临时表中数据行过多的话，导 致where执行效率非常慢。
6.4 limit:
【原因】如果指定起始行数过大，导致查询速度变慢
6.5 对于多表查询来说，没有指定表文件加载顺序，也会导致查询速度过慢
7.查询命令执行过慢的原因：
7.1 执行时需要对所有的数据进行排序
7.2 执行时需要对表中所有数据进行排序
7.3 I/O留加载表文件次数
47.索引
1.索引 介绍
1.1.【定义】：索引存在于对应的索引文件中，索引本质就是一组排好顺序的一组数据
1.2.【作用】：
1）在索引数据中进行排序是，避免再次排序
2）在索引数据中对数据行进行定位时，避免全表扫描
1.3.【索引文件位置】
1）如果当前表依赖于InnoDB引擎，索引存在FRM文件
2）如果当前表依赖于MYISAM存储引擎，索引存在MYI文件
1.4.【索引基本操作】：
1）查看表文件中存在的索引
show index from 表名
2) 创建索引
create index 索引名 on 表名(字段名)
3)删除索引
drop index 索引名 on表名
2.索引分类：
2.1聚簇索引与非聚簇索引
2.2主键索引，唯一性索引
2.3单个索引，复合索引
3.聚簇索引与非聚簇索引
3.1 聚簇索引：
1）只能来自语采用Innodb存储引擎表的数据。
2）MySql自动采用了InnoDB存储引擎表中主键建立索引，这个索引就是聚簇索 引。
3）如果当前表中没有主键，MySql将会选择一个添加唯一性结束的字段作为聚簇 索引。
4）如果当前表中既没有主键字段，也没有添加唯一性约束字段，MySql将随机选 取一个字段作为聚簇索引。
5）在采用InnoDB存储引擎的表文件中，必然会存在一个聚簇索引。
6）在采用InnoDB存储引擎的表文件中，只能有一个聚簇索引。
7）在表文件中其他字段上建立的索引都是非聚簇索引。
3.2 非聚簇索引：
1）是由开发人员自行创建
2）对于采用了InnoDB存储引擎表，除了一个聚簇索引之外，其他字段上创建的 索引都是非聚簇索引
3）在采用MyIsam存储引擎的表中，创建的所有索引都是非聚簇索引
3.3 聚簇索引与非聚簇索引区别：
1）聚簇索引：数据节点存储的【当前数据所在行数】以及【当前数据所在数据 行内容】
因为聚簇索引的这种结构，所以在查询时可以直接定位数据节点 上，读取当前数据所在数据行中字段信息，不需要使用I/O流到 硬盘上表文上进行读取，因此执行效率较快
2）非聚簇索引：数据节点存储的【当前数据所在行数】
由于数据节点存储的当前数据主在行数，没有其相关内容， 所以定位之后，需要使用I/O流到硬盘上表文件中定位数据行 其他字段内容。因此执行效率相对较慢
4.主键索引与唯一性索引
4.1 主键索引：
如果当前表文件中字段添加了逐渐约束，mysql主动地将当前字段上数据进行排 序，其生成的索引被称为主键索引。
4.2 唯一性索引：
如果当前表文件中字段添加了唯一性约束，mysql主动的将当前字段上数据进行 排序，其生成的索引被称为唯一性索引。唯一性索引不包括NULL
4.3 普通索引：如果当前表文件中字段上没有添加任何索引，此时在这个字段上创建的索引就 是普通索引。
*** 执行效率
主键索引 > 唯一性索引 > 普通索引
-
单字段索引与复合索引
5.1 单字段索引创建 create index 索引名 on(字段名)
5.2 复合索引创建 create index 索引名 on(字段名1，字段名2，字段名3…)
48.二分查找算法：
将一组数据中平均值作为【根节点】，将小于【平均值】数据放在【根节点】左边，将大于【平均值】数据放在【根节点】右边，定位数据时，避免整体数据扫描，只需要对一半的数据进行比较。
如果数据量较大时，虽然可以节省将近一半的遍历数据量，但是依然会消耗较多时间。
49.平衡二叉树算法：
1.是二分查找算法的升级版。
2.【特点】：
1）产生【数据节点】与【叶子节点】概念
2）每一个【数据节点】只能有两个指针。一个指针指向小于当前【数据节点】数 据。另一个指针指向大于当前【数据节点】的数据。
3.【缺点】：
1）如果索引相关的字段中数据发生变化时，导致平衡二叉树内容失效，需要重新 创建这个平衡二叉树。因此
49.B Tree算法
1.【介绍】：
1）是平衡二叉树算法升级版
2）是目前各种数据库数据在索引上使用的主要算法
2.【特点】：
1）使用区间数据来代替数据节点上具体的数据，降低维护难度
2）在连续叶子节点之间使用指针来进行连接，降低遍历次数
50.hash算法与hash索引
51.explain介绍
1.explain中id属性：
【作用】：通过id属性支出当前查询语句中相关表文件的加载顺序
【含义】：
情况1：如果被加载的表文件的ID属性是相同的，
则根据自上而下的顺序进行加载的。
情况2：如果被夹在的表文件的ID属性内容是不相同的。
则ID较大的表，就先被加载。ID 较小的表，则后被加载。
情况3：如果被加载的表文件的ID属性内容既有相同又存在不同时。
则ID较大的表，就先被加载。ID相同的表则按照自上而下顺序进行 加载
2.explain中select_type属性：
Simple:简单查询，不包含子查询
Primary：联合查询或者包含子查询
Dependent SubQuery:依赖子查询
SubQuery:独立子查询
3.explain中type属性
3.1type内容分类：
1）all:全表扫描
2）index:定位数据行时，没有使用到索引（全表扫描），但是已经定位的数据行中 读取读取数据的时候，却是使用索引，也就是说使用select命令时，根据索 引读取数据。（执行效率较低）
3）range:定位数据行，使用到索引。使用区间定位方式 where age<=23
这种状态实际上是一种不稳定的状态。如果定位的数据行超过了表中总行 数30%以上。导致mysql放弃使用索引定位。
4）ref:定位数据行，使用到索引，采用直等方式 where ename='mike'
同时满足定位的条件的数据行只有一行。
5）const:定位数据行，使用到索引，采用主键索引直等方式 where empno=10
6) system：操作表只有一行数据，并且数据内容是固定不变的。
***SQL优化是，索引级别最低保证达到range级别
索引级别最高达到ref
4.explain中的possible_keys与key的区别？
possible_key是可能用到的索引，而key表示实际上用的索引。
5.索引的使用规则
5.1 如果索引字段上使用了函数，导致索引失效
explain select * from emp where upper(ename)=‘SMITH’
5.2 如果索引字段上使用运算，导致索引失效
explain select * from emp where empno+1=109;
5.3 如果索引字段上产生了（隐式类型转换），导致索引失效
explain select * from emp where ename=20;
5.4 如果索引字段上进行了模糊查询，只支持前置条件的模糊查询。
explain select * from emp where ename like ‘m%’;
explain select * from emp where ename like ‘%e’;
explain select * from emp where ename like ‘%m%’;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
