MySQL 从删库到跑路
技术事简单报，看老王带你入门MySQL，从删库到跑路
本篇文章适用于大学学习MySQL的入门同学，理解不深，欢迎交流
目录
limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制rownum）
12、DQL（select）DML(insert delete update)
和事务相关的语句只有：DML语句。（insert delete update）
对视图进行怎删改查，会影响原表数据。（通过视图影响原表数据的，不是直接操作的原表）
一.认识SQL语句
老王刚上大学的时候，SQL语句属实是给老王的心里留下了不小的阴影，各种多表联查各种join属实是给老王整不会了，接下来，老王就用自己的理解，带着大家逐步的了解SQL语句
1）什么是SQL语句，什么是表？
老规矩，看看度娘你会发现你什么都没看懂，通俗的来说，你可以把SQL语句当成数据库的各个管家，有查询的，有删除（删库）的等等，你需要他们做事情的时候，只需要通过一些逻辑的设定，从而达到操纵数据库的效果。
提到数据库，不得不提到一个重要的东西，表
什么是表？别问老王，看图说话
在数据库中，表即为table ，table是数据库的基本组成单元，所有的数据都用表格的方式呈现给我们，说白了就是方便看
一个合格成熟的数据库里全是表，那么一个成熟的表里有什么？
行：被称为数据/记录（data）
列：被称为字段（column）也可以记作列名
每一个字段应该包括那些属性？
字段名、数据类型、相关的约束（后续说，字面意思就是约束这一行，例如这一行只能写文字或者数字，后续老王会给大家详细的解释什么是约束）
2）SQL语句的分类
在SQL语句中，所有的SQL语句分为五大类
DQL（数据查询语言）：查询语句，凡是select语句都是DQL
DML（数据操作语言）：insert delete update，对表中的数据进行增删改
DDL（数据定义语言）：create drop alter，对表结构的增删改
TCL（事务控制语言）：commit提交事务，rollback回滚事务。（T：transaction）
DCL（数据控制语言）：grant授权、revoke撤销权限等
老王没记住过，都是考试背的，在老王的印象中，SQL类型就是增删改查
既然是为了给广大大学生的考试秘籍，就不得不提到MySQL的机制了（划重点，必考）
DB,DBMS,SQL之间的关系
DB： Database(数据库，数据库实际上在硬盘上以文件的形式存在)
DBMS：Database Management System （数据库管理系统，常见的有：MySQL Oracle DB2...）
SQL：结构化查询语言，是一门标准通用的语言，标准的SQL适合于所有的数据库产品
SQL属于高级语言，只要能看懂英语单词写出来的SQL语句，可以读懂什么意思，SQL在执行的时候，实际上内部也会先进行编译，然后再执行SQL，编译由DBMS完成
关系：DBMS——（执行）——》SQL——（操作）——》DB
划重点，这个要考，也是MySQL的跑码的顺序,也是学习数据库的根本，简而言之DBMS负责执行SQL语句，通过执行SQL语句来操作DB中的数据 。
二.了解MySQL
1）通过CMD访问MySQL
在电脑装好MySQL数据库之后（百度，切记别点错，依稀想起被数据库卸载支配的恐惧）， 按下电脑的windows键+R，打开运行，输入cmd即可进入黑窗口。
这就是黑窗口，在黑窗口中，我们可以通过一个简单的语句访问MySQL
mysql -uroot -p
Enter password:password后面就是自己设置的Mysql密码，数据即可（记得-p后面按下回车，这样密码就可以像上面代码块一样隐藏起来）
当你进入看到Welcome to the MySQL monitor.这句话的时候，说明你的Mysql已经安装成功了，这个时候我们就可以用黑窗口完成一系列的SQL语句
常用的语句
查看有哪些数据库
show databases；（这个不是sql语句，属于 mysql的命令）
创建属于我们自己的数据库
create database 数据库名
进入数据库
use 数据库名;（这个不是sql语句，属于mysql的命令。）
查看当前使用的数据库中有那些表？
show tables;
导入sql文件（文件以sql结尾，这样的文件被称为“sql脚本”。 什么是sql脚本呢？
当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
注意：直接使用source命令可以执行sql脚本。
sql脚本中的数据量太大时，无法打开，请使用source命令完成初始化。）
source sql文件绝对路径
删除数据库：drop database 需要删除的数据库名字；
查看表结构：desc dept;
表中的数据 ：select * from emp;(查看表中的数据)
mysql> select databases; 查看当前使用的是哪个数据库
mysql> select version(); 查看mysql的版本号
C 命令结束一条语句
exit 退出mysql
注意奥：SQL语句是不区分大小写的，但是建议还是规范书写，因为你永远不知道在后面的JavaWeb中，你的不规范命名和书写会带来多大的麻烦（老王的亲身体会）
三.SQL查询语句
在SQL语句中，查询语句是最难的，因为有N多的表等你拼接，绝对不要把查询想得太简单，但是当你发现查询的奥妙，你会觉得这玩意跟数独一样有意思
1）简单的查询语句
注：sql语句不区分大小写。粗体均为关键字，其余为查询或需要处理的名字；
语法格式：
select 字段名1，字段名2，。。。 from 表名;注意，在SQL语句中， 任何一条sql语句以“;” 结尾。不然你会发现一个尴尬的事情
老王当年因此以为电脑坏了来着。学习编程的时候，你要把CMD当成傻子，你不告诉它该我写完了该执行了就会出现这么尴尬的事情
2）简单查询
在练习查询语句之前，先给大家看一下老王练习SQL语句的表
英语好的同学们请自行翻译并创建表格进行练习，有了这些表，就可以通过上面简单的查询语法做到一些简单的题目
例如：分别查询三个表格
查询salgrade表
select * from salgrade;
查询emp表
select * from emp;
查询dept表
select * from dept;3） 重命名查询结果的列
select xx更改前x as xx更改后x from emp；
select xx更改前x xx更改后x from emp；（as关键字可以省略）
注：MySQL中可以使用中文作为列的名称，例如年薪，在用中文更改名称时，强调给字符加单引号，双引号也可以通过编译并使用，但仅限于MySQL，Oracle 及其其他数据库中不承认双引号修饰的字符。
4）查询全部字段
select * from 表名；（*表示全部）
实际开发中不建议使用*，效率很低。
5）条件查询
语法格式：
select 字段，字段.. from 表名 where 条件；
执行顺序：先from，然后where，最后select
先给大家解析一下，SQL语句中有什么逻辑条件；
6）between
查询数据范围：select sal from emp where between 1000 and 3000；
也可以用作字符串方面
查询字符串的开头：select name from emp where between 'A' and 'B' ；
左闭右开，查询字符串开头
7）and 与 or 的优先级问题
例如 找出 工作岗是AAA以及BBB的员工
select ename，job from emp where job = 'manager' or job = 'salesman'；
and 永远被翻译为 并且 ，or 永远被翻译为 或者
and与or 联合使用的例：找出薪资大于一千并且部门编号是20或30部门的员工
select name，jobId ，sal from emp where sal>1000 and (jobId = 20 or jobId =30);
加括号，小心乱套，不加？小心他给你查询工资大于100或者id=20的（离谱的优先级），容我吐槽这个优先级就很迷，四舍五入就是从左往右运行，所以，在这种参杂着and or 的逻辑运算的时候，亲，建议您加括号叭！
8）in与or
例：找出工作岗位是AAA和BBB的员工
select ename，job from emp where job='AAA' or job='BBB'；
当然他不只一种写法，这玩意都给你CV下来了，肯定有意义
select ename，job from emp where job in （'AAA'，'BBB');
你看看这个or，是不是事儿贼多，他俩效率没有区别，但是我更喜欢第二种，可读性更好一点点
注意奥，in后面的每一项，都是一个值，不是区间，例如下面这玩意
select ename ， job from emp where sal in(1000，5000);
这玩意要找的是，1000块和5000块，这可不是1000-5000，就离谱；
9）模糊查询like
重点来了，like 就离谱
在这里面，你需要搞明白两个特殊的符号 第一个是% 第二个是_（像极了正则表达式）
%:任意多个字符 ； _ ： 任意一个字符；
例：找出名字所有姓张的员工；
select ename from emp like '张%'；
例：找出第二个字母是A的员工；
select ename from emp like '_A%';
例：（过分的来了，就离谱）找出名字里有下划线的；
select ename from emp like '% _%';
这世界上有个东西叫转义符，只需要在前面打一个捺斜杠就完事了（）;
10）数据排序：
升序，降序，例如，按照工资的升序排序
select enamel，sal from emp order by sal；
在没有指定升降的情况下， 关键字order by 默认升序排序，asc表示升序，desc表示降序，那么如果我想降序或升序，就变成了这样；
select enamel，sal from emp order by sal asc；//升序
select enamel，sal from emp order by sal desc；//降序
那么问题来了，按照工资的升序排列，当工资相同的时候，再按名字的升序排列；怎么做？
select enamel，sal from emp order by sal desc，ename asc；
我也不知道语法怎么规定的，但是这个逗号后面的原理，大概就是从左到右，越靠右越弟弟。当前面的字段相等（无法排序），后面这个弟弟才会启用，相当于是个备胎；啥也不是；
11）分组函数
分组函数还有另外一个名字：多行处理函数；
特点：输入多行，最终输出的结果为一行,自动忽略null；
注意：分组函数不能直接卸载where后面，会报错；
但是可以嵌套，至于为什么，这部分，往下看，会有的；
所有分组求和的函数都是对某一组的数据进行操作
找出工资总和：select sum(sal) from emp;
找出最高工资: select max(sal) from emp;
找出平均工资：select avg(sal) from emp;
找出平均人数：select count（ename）from emp；
......
分组函数也可以组合起来用；
例如：
select count(*),sum*(*) from emp;
12） count
count(*)和count(具体的某个字段)，他们有什么区别？
count(*)：不是统计某个字段的个数；统计的一定是总记录条数；
而count(comm)：表示统计comm不为NULL的数据总数量；
13）group by和having
group by:按照某些字段或者某个字段进行分组；
having: 对分组之后的数据进行再次过滤；
案例：找出每个工作岗位(job)的最高薪资(sal)。
select max(sal) from emp group by job;
注意：分组函数一般都会和group by联合使用，任何一个分组函数（max min count）都是在group by语句执行完才会执行，当一条select里面没有group by的时候，整张表的数据会自成一组。
多字段能不能联合起来一块分组？
例：找出每个部门（deptno），不同工作岗位（job），的最高薪资(max(sal))；
句子都断好了，这个怎么写？
select deptno,job,max(sal) from emp group by deptno,job;
例：找出每个部门(deptno)的最高薪资，要求显示最高薪资大于2500的
select max(sal),deptno from emp group by deptno having max(sal)>2900;
?虽然这种用法效率低，但是胜在思路清晰，没毛病，好用就行了；
14）单行处理函数
什么是单行处理函数？ 输入一行，输出一行。
计算每个员工的年薪？（工资+补助X12=年薪）
select ename，（sal+comm）*12 as yearsal from emp；
但是，如果工资和年薪，有一个是空值，那么他的年薪可就变成null了；
那么重点来了，所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL；
ifnull() 空处理函数
语法：ifnull(可能为Null的数据，当作什么看）；
那么计算员工的年薪，为了保证不出NULL ，就变成了
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
练习以及答案：
查询员工工资等于5000的姓名？
select ename from emp where sal=5000;
查询SMITH的工资？
select sal from emp where ename='SMITH'；
找出工资高于3000的员工？
select ename,sal from emp where sal>3000;
找出工资不等于3000的？
select ename,sal from emp where sal <> 3000;
找出工资在1100和3000的员工，包括110和3000？
select ename,sal from emp where sal >=1100 and sal<=3000;
//between...and...是闭区间【1100,3000】
也可以写成
select ename,sal from emp where sal between 1100 and 3000
注意：between ...and... 为XX到XX之间，老王英语不好，理解这个直接理解为[1100，3000]闭区间了。
15）关于查询结果去重
啥玩意？去重不用说了吧，语法如下(关键字 distinct)：
select distinct job from emp;
distinct 只能出现在所有字段的最前面！ 别出BUG了；
例：统计岗位的数量？
select count（distinct job） from emp;
套娃警告！
sql语句执行顺序
一个完整的查询语句语句 怎么写？
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
后面的小数字，就是所谓的执行顺序，语法顺序不能乱，后面的数字是语句的执行顺序
而在前面提到的，where后面为什么不能使用max等函数，就是因为执行顺序的原因，在where后还没有调用group等分组函数，所以无法使用
四.连接查询
1.连接查询
1.1什么是连接查询？
在实际开发中，大部分情况下，都不是从单表中查询数据，一般都是在多张表联合查询取出最终的结果。实际中，一般一个业务都会对应多张表，比如：学生和班级两张表
1.2 连接查询的分类？
根据语法出现的年代来划分，包括：
SQL 92（老龄化DBA可能还在用，DBA：数据库管理员，反正我不懂）；
SQL 99（比较新的语法，吐槽20年前的语言）；
根据表的连接方式划分，包括：
内连接：等值连接，非等值连接，自连接；
外连接：左外连接，右外连接；
全连接(很少用)
1.3 笛卡尔乘积现象
笛卡尔发现的，别问，不懂
案例：找出每一个员工的部门名称，要求显示员工名和部门名
//两个表在一起，需要从一个表取员工名字，另一个表取部门名称 select ename，dname from emp,dept; //?这个是错的奥。但是这个叫笛卡尔乘积现象 他会把员工表的每个人，和部门表相匹配，相当于 一个SMTIH 一个人跑了四次部门表的查询，如果在查询中，没有任何限制的话，那么查询结果条数将会是两张表的乘积，这个现象就叫笛卡尔乘积现象。四舍五入在还数学的债 //结果就这样的?
插入：关于表的别名：
select e.name,d.dname from emp e,dept d；
执行效率高，可读性好（以后起别名，效率高省内存）；
言归正传，那么问题来了，怎么避免笛卡尔积现象呢？
加条件进行限制！！
//既然一起显示，就意味着两个表的部分要连在一起，那么：
select
e.ename,d.dname
from
emp e,dept d
where //判定条件，避免了笛卡尔积现象
e.deptno=d.deptno;
白学，上面这些这个以后不用，经典的92语法（滑稽），但是，这个是原理。
2.内连接
2.1 内连接中的等值连接
最大的特点：条件是等量关系。
案例：查询每个员工的部门名称，要求显示员工名和部门名；
//SQL92语法：
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno=d.deptno;
问题是92 现在都不用，老了终归是老了
//SQL99：
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno; //由于连接关系是等值，所以叫等值连接
join...on 语法： 表A join 表B on 连接条件 ；（后面还可以跟where）
SQL99语法 结构更加清晰，表的连接条件和后面的where条件分离开了
2.2 内连接的非等值连接
最大的特点：连接条件中的关系是非等量关系；
案例：找出每个员工的工资等级，要求显示员工名字，工资，工资等级
select
e.name,e.sal,s.grade
from
emp e
join
salfrade s
on
e.sal between s.losal and s.hisal;
2.3 内连接中的自连接
最大特点：一张表看作两张表，自己连自己
案例，找出每个员工的上级领导，要求显示员工名和对应的领导名。
员工表 empa：
//自连接
select
a.ename,b.ename
from
empa a
join
empa b
on
a.empno = b.mgr;
3.外连接
什么是外连接？他和内连接有什么区别？
内连接：假设A和B表进行连接，使用内连接，凡是A表和B表能匹配的记录查询出来，这就是内连接，AB两个表没有主副之分，两张表是平等的；
外连接：假设A和B表进行连接，使用外连接的话，AB两张表有一张是主，一张是副，主要查询主表，捎带查询副表，当副表中的数据没有和主表中的数据匹配上，附表自动模拟出Null与之匹配；
3.1外连接的分类
左外连接：左边的是主表
右外连接：右边的是主表
任何一个左(右)外连接都有一个右(左)外连接的写法；
XXX left join XXX on xxxxxx；
这个就是左外连接，左边为主表?，副表匹配不上，会自己创造一个NULL塞到主表；
XXX right join XXX on xxxxxx；
这个就是右边为主表；
外连接最重要的特点是：主表的数据无条件的全部查询出来。比如有的表里无数据，副表没有查到相应的数据，内连接就会直接把这行删掉不要，而外连接会在上面匹配一个null，不会不显示主表当前行
2.9、三张表怎么连接查询？
案例：找出每一个员工的部门名称以及工资等级。
注意，解释一下：
A
join
B
join
C
on
表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接 on后写匹配条件。
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
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
left join
emp e1
on
e.mgr=e1.empno;方法二：
select e.ename,d.dname,s.grade,q.ename
from emp e
join dept d
on e.deptno=d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
join emp q
on
e.mgr=q.empno
Order by s.grade;
3、子查询
3.1、什么是子查询？子查询都可以出现在哪里？
select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select
..(select)
From
..(select)
where
..(select)
3.2、where子句当中使用子查询
案例：找出高于平均薪资的员工信息。
select *from emp where sal>avg(sal) //错误，因为where后面不能跟avg函数
第一步：找出平均薪资
select avg(sal) from emp;
第二步：where 过滤
select * from emp where sal>2073;
第一步和第二步合并：
select *from emp where sal>(select avg(sal) from emp);
3.3、from后面嵌套子查询
案例：找出每个部门平均薪水的薪水等级
第一步：找出每个部门的平均薪水（按照部门编号分组，求sal的平均值）
select deptno ,avg(sal) as avgsal from emp group by deptno;
第二步：将以上的查询结果当做临时表t，让t表和salgrade s 表连接诶，条件是：t.avgral between
s.losal and s.histol;
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;
3.4、在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;
4.union(可以将查询结果集相加)
案例：找出工作岗位是SALSMAN和MANAGER的员工？
第一种：select ename，job from emp where job='MANAGER' or job='SALSMAN'；
第二种：select ename,job from emp where job in ('MANAGER','SALSMAN')；
第三种：UNION
select ename,job from emp where job ='MANAGER'
UNION
select ename,job from emp where job ='SALSMAN';
两张不相干的表中的数据拼接在一起显示。
select ename from emp
union
select dname from dept;
注意：查询的列数要相等。
5、limit(重点中的重点，以后分页查询全靠它了)
limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制rownum）
limit取结果集中的部分数据，这时它的作用。
5.1语法机制
limit startIndex，length
startIndex表示起始位置，从0开始，0表示第一条数据
length表示取几个
案例：取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc;
取前五个：
select ename ,sal from emp order by sal desc limit 0,5
select ename,sal from emp order by sal desc limit 5;
limit是sql语句的最后一个执行环节：
5.2、案例：找出工资排名在第四到第九名的员工？
select ename ,sal from emp order by sal desc limit 3,6;
5.3、通用的标准分页sql？
每页显示3条记录：
第一页：0, 3
第二页：3, 3
第三页：6, 3
第四页：9, 3
第五页：12,3
每页显示pageSize条记录：
第pageNo页 （pageNo-1）*pageSize，pageSize
pageSize是什么？是每页显示多少条记录
pageNo是什么？显示第几页
Java代码{
int pageNo=2; //页码是2
int pageSize=10;//每页显示10条
limit (pageNo-1)*pageSize,pageSize
}
6、创建表：
建表语句的语法格式：
create table 表名（
字段名1 数据类型
字段名2 数据类型
字段名3 数据类型
....
）；
关于MYSQL当中字段的数据类型？以下只说常见的
int 整数型（java中的int）
bigint 长整型（Java中的long）
float 浮点型（Java中的float double）
char 定长浮点型（string）
varchar 可变长字符串（stringbuffer/stringBuilder）
data 日期类型（对应java.sql. data类型 ）
BLOB 二进制大对象（存储图片、视频等流媒体信息）Binary large OBject （对应Java中的object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串） Character Large OBject（对应java中的Object）
........
char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。
BLOB和CLOB类型的使用？
eg：
电影表：t_movie
haibao（BLOB）
history（CLOB）
表名在数据库当中一般建议以：t_或者tb1_开始。
创建学生表：
学生信息包括：
学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char
create table t_student(
no bigint,
name varchar(255),
sex char(1)，
classno varchar(255)，
birth char(10)
);
7、insert语句插入数据
语法格式：
insert into 表名（字段名1，字段名2，字段名3....）values(值1，值2，值3)
要求：字段的数量和值的数量相同 ，并且数据类型要对应相同 ,只要能对应上，顺序无所谓。
insert into t_student (no,name,sex,classno,birth) values(1,'zahngsan','1','gaosan1ban','1950-10-12');
insert into t_student （name）values（‘wangwu’）// 除name字段外，剩下的所有字段自动插入NULL。
drop table if exists t_student；//当这个表存在的话删除。
create table t_student (
no bigint,
name varcahr(255),
sex char(1) default 1,
classno varchar (255),
birth char(10)
);
insert into t_student (name) values('zhagnsan');
需要注意的地方：
当一条insert语句执行成功之后，表格当中必然会多一行记录。及时多的这一行记录当中某些字段是NULL ，后期也无法在执行insert 语句插入数据了，智能使用update进行更新。
insert into t_student values(1,'jack','0','gaosan2ban','1999-10-06');
//注意：字段可以省略不写，但是后面的value对数量和顺序都有要求。
//一行插入多行数据
insert into t_student (no,name,sex,classno,birth) values(3,'rose','1','gaosna2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1999-10-06');
8、表的复制
语法：
create table 表名 as select 语句；
将查询结果当做表创建出来。
9、将查询结果插入到一张表中？(表中的数据要对应)
insert into dept select* from dept;
select * from dept1;
10、修改数据：update
语法格式：
update 表名 set 字段名1=值1，字段名2=值2.。。where 条件；
注意：没有条件整张表数据全部更新。
案例：将部门10的loc修改为SHANGHAI,将部门名称修改为RENSHIBU
update dept1 set loc='SHANGHAI',dname='RENSHIBU' where deptno=10;
select * from dept1;
更新所有记录
update dept1 set loc='x',dname='y';
select * from dept1;
11、删除数据？
语法格式：
delete from 表名 where 条件；
注意：没有条件全部删除。
删除10部门数据？
delete from dept1 where deptno =10;
删除所有记录？
delete from dept1;
怎么删除大表中的数据？（重点）
truncate table emp1; //表被截断，不可回滚。永久丢失。
删除表？
drop table 表名；//这个通用
drop table if exists 表名；//oracle 不支持这种写法
12、DQL（select）DML(insert delete update)
DDL(create drop alter)
对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select (这些都是表中的数据操作。)
增删改查有一个术语：CRUD操作
Create（增） Retrieve（检索） Update（修改）
Delete（删除）
13、约束（Constraint）
13.1、什么是约束？常见的约束有哪些呢？
在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有哪些呢？
非空约束（not null）：约束的字段不能为 NULL。
主键约束（primary key）：约束的字段不能重复
外键约束（foreign key）：简称FK
检查约束（check） ：注意oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束
13.2、非空约束 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');
insert into t_user(id,username,password) values(1,'lisi','123');
MYSQL DAY03
14、约束
14.1、唯一性约束（unique）
*唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。
*案例：
drop table if exists t_user;
create table t_user(
id int ,
username varchar(255) unique
);
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhagnsan')
*案例：给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int ,
username varchar(255),
email varchar(255),
unique(usercode,username)//多个字段联合起来添加一个约束 [表级约束]
);
drop table if exists t_user;
create table t_user(
id int ,
username varchar(255) unique,//列级约束
email varchar(255) unique
);
*注意：not null 约束只有列级约束，没有表级约束
14.2、主键约束
*怎么给一张表添加主键约束呢？
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)
);
以下内容是演示以下复合主键，不需要掌握：
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id，username)
);
mysql 提供主键值 自增：
drop table if exists t_user;
create table t_user (
id int primary key auto-increment, //id字段自动维护一个自增的数字，从1开始，以1递增。
username varchar(255)
);
提示：oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。
14.3、外键约束
*关于外键约束的相关术语：
外键约束：foreign key
外键字段：添加有外键约束的字段
外键值： 外键字段中的每一个值。
*业务背景：
请设计数据库表，用来维护学生和班级的信息？
第一种方案：一张表存储所有数据
no（pk） name classno classname
缺点：冗余。【不推荐】
第二种方案：两张表（班级表和学生表）
t_class 班级表
Cno（pk） cname
-------------------------------------------
t_student 学生表
Sno（pk） sname classno（该字段添加外键约束fk）
-----------------------------------------
*将以上表的建表语句写出来：
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 varcahr(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,’xxxxxxxxxxxxxxxxxxxxxxx’);
Insert into t_class values(101,’yyyyyyyyyyyyyyyyyyyyy’);
Insert into t_student values(1,’zs1’,102);
*外键值可以为NULL
外键值可以为NULL。
*外键字段引用其他表的某个字段的时候，被引用的字段必须是外键吗？
注意 ：被引用的字段不一定是主键，但至少具有unique约束。
15.存储引擎？
15.1、完整的建表语句
CREATE TABLE `dept` (
`DEPTNO` int(2) NOT NULL,
`DNAME` varchar(14) DEFAULT NULL,
`LOC` varchar(13) DEFAULT NULL,
PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
注意：在mysql当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。
建表的时候可以指定存储引擎，也可以指定字符集。
Mysql默认使用的存储引擎是InnoDB方式。
默认采用的字符集是UTF8
16.2、什么是存储引擎呢？
存储引擎这名字只有在mysql中存在。（oracle中有相应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是”表的存储方式”）
Mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。
16.3、查看当前mysql支持的存储引擎？
Show engines G
Show engines
Mysql 5.5.36版本支持的存储引擎有9个：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> show engines G
*************************** 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
9 rows in set (0.00 sec)
2.4、常见的存储引擎？
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
MyISAM 这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种存储引擎不是默认的。
MyISAM采用三个文件组织一张表：
xxx.frm(存储格式的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。
MyISAM存储引擎
MyISAM存储引擎是mysql最常用的引擎。
它管理的表具有以下特征：
-使用三个文件表示每个表：
*格式文件-存储表结构的定义（mytable.frm）
*数据文件-存储表行的内容（mytable.MYD）
*索引文件-存储表上索引（mytable.MYI）
-灵活的AUTO_INCREMENT字段处理
-可被转换为压缩、只读表来节省空间
InnoDB存储引擎
*InnoDB存储引擎是mysql的缺省引擎。
*它管理的表具有下列主要特征：
-每个InnoDB表在数据库目录中以.frm格式文件表示
-InnoDB表空间tablespace被用于存储表的内容
-提供一组用来记录事务性活动的日志文件
-用commit（提交）、SQVAPOINT及ROLLBACK（回滚）支持事务处理
-提供全ACID兼容
-在mysql服务器崩溃后提供自动恢复
-多版本（MVCC）和行级锁定
-支持外键及引用的完整性，包括级联删除和更新
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引擎。
MEMORY存储引擎
*使用MEMORY存储引擎的表，其数据存储在内容中，且行的长度固定，这两个特点使得MEMORY
存储引擎非常快。
*MEMORY存储引擎管理的表具有下列特征：
-在数据库目录内，每个表均以.frm格式的文件表示。
-表数据及索引被存储在内存中。
-表级锁机制。
-不能包含TEXT或BLOB字段。
*MEMORY存储引擎以前被称为HEAP引擎
总结：
*MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适合情形是使用压缩的只读表。
*如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
*可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于键盘的表中重新生成的数据。
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。
以前叫做HEPA引擎。
事务（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要么全成功，要么全失败。事务具有四个特征ACID
- 原子性（Atomicity）
*整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
C）一致性（Consistency）
*在事务开始之前与结束之后，数据库都保持一致状态。
- 隔离性（isolation）
*一个事务不会影响其他事务的运行。
D）持久性（durability）
*持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
和事务相关的语句只有：DML语句。（insert delete update）
为什么？因为它们这三个语句都适合数据库表当中的“数据”相关的。
事务的存在是为了保证数据的完整性，安全性。
3.3、假设所有的业务的都能使用一条DML语句搞定，还需要事务机制吗？
不需要事务。
但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。
事务的原理
假设一个事儿，需要先执行一条insert，再执行一条update，最后执行一条delete。这个事儿才算完成。
开启事务机制：
执行insert语句-->insert...（这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。）
执行update语句--->update...（这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据）
执行delete语句--->delete...（这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据）
提交事务或者回滚事务（结束）
事务的特性？
事务包括四大特性：ACID
A：原子性：事务是最小的工作单元，不可再分。
C：一致性：事务必须保证多条DML语句同时成功或者同时失败。
I: 隔离性：事务A与事务B之间具有隔离。
D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。
关于事务之间的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括四个：
第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读（dirty read现象）：表示读到了脏的数据。
第二级别：读已提交（read committed）
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了：脏读现象没有了。
读已提交存在的问题是：不可重复读。
第三级别：可重复读（repeatable read）
这种隔离级别解决了：不可重复读问题。
第四级别：序列化读/串行化读（serializable
）
解决了所有问题。
效率低。需要事务排队。
Oracle数据库默认的隔离级别是二挡起步：读已提交。（read committed）
Mysql 数据库默认的隔离级别是三档起步：可重复读（repeatable read）。
3.6、演示事务
*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，则提交一次。
Insert into t_user(username) values(‘zs’);
- select * from t_user;
- Rollback;
- Select *from t_user;
*演示：使用start transaction；关闭自动提交机制。
- Start transaction；
- Insert into t_user(username ) values(‘lisi’);
- Insert into t_user(username ) values(‘wangwu’);
- select * from t_user;
- Rollback;
- select * from t_user;
*演示提交
- insert into t_user(username) values(‘rose’);
- Insert into t_user(username) values(‘jack’);
- Select * from t_user;
- Commit; //已经将数据持久到硬盘中了。
- Select * from t_user;
- Rollback;
- Select * from t_user;
4.7、使用两个事务演示以上的隔离级别
第一：演示read uncommitted(使用两个窗口)
- use bjpowernode;
Set global transaction isolation level read uncommitted;//设置事务的全局隔离级别
Select @@global.tx isolation;//查看事务的全局隔离级别
退出窗口，重新开启
- Use bjpowernode;
Start transaction;
Select * from t_user;(table1)
Insert into t_user(username) values(‘smith’);(table2)
Select * from t_user;(可查询到smith)
第二：演示read committed（读已提交）
- Use bjpowernode;
Set global transaction isolation level read committed;
Select @@global.tx_isolation;
Exit;
- use bjpowernode;(两个窗口同时使用)
Insert into t_user(name) values(‘test’);(窗口2)
Select * from t_user;(窗口1 未查询到记录)
Commit（窗口2）；
Select * from t_user;(窗口1，可查询到结果)
第三：演示repeatable read
- set global transaction isolation level repeatable;
Select @@global.tx_isolation;
Exit;
- Use bjpowernode;(两个窗口)
Start transaction；
Delete from t_user；（窗口二）
Commit;（窗口二）
Select *from t_user;(窗口二)
Insert into t_user(username) values(‘test’);(窗口二)
Commit;(窗口二)
Select * from t_user;（窗口二）
Select * from t_user；(窗口一)
第四：演示serializable
- set transaction isolation level serializable;
Select @@global.tx_isolation;
- Use bjpowernode;(both two)
Start transaction;(both two)
Select * from t_user;（窗口1）
Insert into t_user(username) values(‘hehe’);(窗口1)
Select * from t_user;(窗口二)
Commit；（窗口一）
Select * from t_user;(窗口二)
索引
什么是索引？有什么用？
索引就相当于一本书的目录，通过目录可以快速地找到对应的资源。在数据库方面，查询一张表的时候有两种检索方式：
第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）
索引为什么可以提高检索效率呢？
其实最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断地维护。是有维护成本的。比如，表中的数据也经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
添加索引是给某一个字段，或者说某些字段添加索引。
Select ename ,sal from emp where ename=’smith’;
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描enamel字段中的所有的值。
当enamel字段上添加索引的时候，以上sql语句辉根据索引扫描，快速定位。
怎么创建索引对象？怎么删除索引对象？
创建索引对象：
Create index 索引名称 on 表名（字段名）；
删除索引对象：
Drop index 索引名称 （on 表名）；
什么时候考虑给字段添加索引？（满足什么条件）
*数据量庞大。（根据客户的需求，根据线上的环境）
*该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
*该字段经常出现在where字句中。（经常根据哪个字段查询）
4.4、注意：主键和具有unique约束的字段自动会添加索引。
查看sql语句的执行计划：
Explain select ename,sal from emp where sal=5000;
给薪资sal字段添加索引：
Create index emp_sal_index on emp(sal);
Explain select ename,sal from emp where sal=5000;
索引底层采用的数据结构是：B+Tree
索引的实现原理？
通过B tree 缩小扫描范围，底层索引进行了排序，分析，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
Select ename from emp where ename=’smith’;
通过索引转换为：
Select ename from emp where 物理地址=0x33；
索引的分类？
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加一个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引
。。。
索引什么时候失效 ？
Select ename from emp where ename like ‘%A%’;
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。
视图
什么是视图
站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）
*视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。
*视图有时也被称为“虚拟表”。
*视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
*相对于从基表中直接获取数据，视图有以下好处：
-访问数据变得简单
-可被用来对不同用户显示不同的表的内容
用来协助适配表的结构以适应前端现有的应用程序
视图作用
1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。
2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）。
3、从而加强了安全性，使用户只能看到视图所显示的数据。
4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。
怎么创建视图？怎么删除视图？
Create view myview as select empno,ename from emp;
Drop view myview;
注意：只有DQL语句才能以视图对象的方式创建出来。
对视图进行怎删改查，会影响原表数据。（通过视图影响原表数据的，不是直接操作的原表）
面向视图操作？
Select * from myview;
Create table emp_bak as select * from emp;
Create view myview1 as select empno,ename,sal from emp_bak;
Update myview1 set ename=’hehe’,sal=1 where empno=7369;//通过视图修改原表数据。
Delete from myview1 where empno=7369;//通过视图删除原表数据。
视图示例：
Create view myview2 as select empno a,ename b,sal c from emp_bak;
Select * from myview2;
Insert into myview2(a,b,c) values(...);
视图的作用？
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。
DBA命令
将数据库当中的数据导出
在windows的dos命令窗口中执行：
Mysqldump bjpowernode>D:bjpowernode.sql -uroot -p123
在windows的dos命令窗口中执行：
Mysqldump bjpowernode emp>D:bjpowernode.sql -uroot -p123
导入数据
Create database bjpowernode;
Use bjpowernode;
Source D:bjpowernode.sql
数据库设计三范式（重点内容，面试经常问）
什么是设计范式？
设计表的依据。按照这个三范式设计的表不会出现数据冗余。
三范式都是哪些？
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
多对多？三张表，关系表两个外键。
t_student学生表
sno（pk） sname
t_teacher讲师表
Tno（pk） tname
t_student_teacher_relation 学生讲师关系表
Id（pk） sno（fk） tno（fk）
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
一对多？ 两张表，多的表加外键。
班级t_class
cno（pk） cname
学生 t_student
Sno（pk） sname classno（fk）
提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。
一对一怎么设计？
一对一设计有两种方案：主键共享
t_user_login 用户登录表
Id（pk） username password
t_user_detail 用户详细信息表
Id（pk+fk） realname tel userid（fk+unique）
一对一设计有两种方案：外键唯一。
t_user_login 用户登录表
Id（pk） username password
t_user_detail 用户详细信息表
Id（pk） realname tel userid（fk+unique）
到这里，老王把自己当年学习MySQL的经验全部码完，对于老王来说这也算是博客的处女作了，望大家多多支持，希望老王的笔记能帮助大家度过考试的难关！撒花