MySQL复习笔记

一、SQL分类

DQL

数据查询语言(凡是带有select关键字的)

select…

一、简单查询

1、查询一个字段

select 字段名 from 表名;

2、查询多个字段

select 字段名,字段名 from 表名;

3、查询所有字段

每个字段都写上，或使用*

使用*的缺点：效率低、可读性差

4、查询的列起别名

select 字段名 as 别名 from 表名;

P：只是将显示的结果列名修改，原表列名不变。

​ as关键字可以用空格省略

​ 若别名中有空格或别名为中文用’'或""括起来(在所有的数据库当中，字符串统一使用单引号，单引号是标准。双引号在oracle中用不了。)

5、字段可以使用数学表达式

二、条件查询

1、条件查询语法格式

select 字段1,字段2,字段3... from 表名 where 条件

2、条件

<>或!=   不等于

=、<、<=、>、>= 

between...and... 两个值之间，等价于 >= and <=

is null 为 null (is not null 不为空)
P：在数据库中null不能使用等号进行衡量，它不是一个值。

and 并且

or 或者
P:and优先级比or高。可用()提升优先级。

in 包含，相当于多个or(not in 不在这个范围中)
P:in('','',...)

not 可以取反，主要用在is 或in中

like 称为模糊查询，支持%或_匹配

%匹配任意个字符，一个下划线只匹配一个字符
P:若查询包含'%'或'_'使用'\'进行转义

3、排序

order by -- 默认升序
order by desc -- 指定降序
order by asc -- 指定升序
/*
若要求薪资升序，薪资一样则名字升序。order by sal asc,ename asc;		sal在前起主导。
*/
/*
了解：根据字段位置也可排序。
select ename,sal from emp order by 2;
表示第二列排序。不建议使用。
*/

4、综合

关键字顺序不能变：
			select...from...where...order by...
执行顺序1、from 2、where 3、select 4、order by

三、分组查询

1、分组查询语法格式

select...from...group by...

2、组合使用

select...from...where...group by...order by...
-- 执行顺序：1、from 2、where 3、group by 4、select 5、order by
-- select语句中，如果有group by，select后面只能跟参加分组的字段以及分组函数。

select...from...where...group by...having...order by...
-- 执行顺序：1、from 2、where 3、group by 4、select 5、having 6、order by
-- P:having可以对分完组的数据进一步过滤，不能代替where，不能单独使用，必须和group by 联合使用。优先选择where，where无法实现的再选having。

四、查询结果去重

-- 关键字distinct
select ename,distict job from emp;-- 错误，distinct只能出现在所有字段的最前方。
select distinct job,deptno from emp;-- distinct出现在两个字段前，表示联合去重。

五、连接查询

笛卡尔积现象：当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积。

1、内连接查询之等值连接

-- 表起别名提升效率。案例
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;-- SQL92语法(缺点，结构不清晰，表的连接条件和后期进一步筛选的条件都放在了where后。)
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;-- SQL99语法(优点，表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where。)
-- join前有一个inner可省略，表示内连接。

2、内连接查询之非等值连接

-- 案例
select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

3、内连接之自连接

-- 技巧：一张表看成两张表。案例。
select a.ename as'员工名',b.ename as '领导名' from emp a join emp b on a.mgr=b.empno;

P:内连接特点，数据完全匹配。

4、外连接

-- 案例(右外连接又称右连接)
select e.ename,d.dname from emp right join dept d on e.deptno=d.deptno;
-- right表示将join关键字右边的表看成主表，为了将这张表的数据全部查询出来，捎带关联查询左边的表。主次关系。
-- left为左连接
-- join前有一个outer可省略，表示外连接。

5、多表连接

-- 案例：找出每个员工的部门名称以及工资等级，要求现实员工名、部门名、薪资、薪资等级。
select
	e.ename,e.sal,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语句中嵌套select语句，被嵌套的select语句称为子查询。

-- 子查询可以出现的位置
select
	..(select).
from
	..(select).
where
	..(select).

where子语句中的子查询

-- 案例：找出比最低工资高的员工姓名和工资。
-- 实现思路。第一步查询最低工资是多少
select min(sal) from emp;
-- 第二步找出大于最低的
select ename,sal from emp where sal>最低;
-- 第三步合并
select ename,sal from emp where sal>(select min(sal) from emp;);

from子句中的子查询

-- 技巧：from后面的子查询可以将子查询的查询结果当做一张临时表。-- 案例：找出每个岗位的平均工资的薪资等级。-- 第一步找出每个岗位的平均工资select job,avg(sal) from emp group by job;-- 第二步把以上的查询结果当做一张真实存在的表t。select * from salgrade;-- s表-- t表和s表进行表连接，条件：t表avg(sal) between s.losal and s.hisal;select	t.*,s.gradefrom	(select job,avg(sal) as avgsal from emp group by job) tjoin	salgrade son	t.avgsal between s.losal and s.hisal;

七、union

union的效率会高一些，对于表连接来说，每连接一次新表匹配满足笛卡尔积。

union可以减少匹配次数。

例：表a 10条记录 表b 10条记录 表c 10条记录

a连接b连接c 匹配次数10 * 10 * 10

使用union的话是10 * 10 + 10 * 10

P:union在进行结果集合并的时候，要求两个结果集的列数相同。

八、limit

-- 作用：将查询结果集的一部分取出来。通常使用在分页查询中。
-- 完整用法
limit startIndex,length
-- startIndex是起始下标起始为0，length是长度
-- 缺省用法
limit 5;-- 取前五。startIndex默认为0.

分页

//每页显示pageSize条记录
//第pageNo页：limit(pageNo-1)*pageSize,pageSize
public static void main(String[] args){
    //用户提交过来一个页码，以及每页显示的记录条数
    int pageNo=5;
    int pageSize=10;
    int startIndex=(pageNo-1)*pageSize;
    String sql="select...limit"+startIndex+","+pageSize;
}
//公式：limit (pageNo-1)*pageSize , pageSize

DML

数据库操作语言（操作表中数据的）

insert 增

delete 删

update 改

一、insert

-- 语法格式
insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);
-- 字段名要和值一一对应，数量对应，类型对应。
-- 字段名可都省略，省略则所有字段都在。

-- 一次插入多条记录insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3),(),(),()...;

二、update

-- 语法格式update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3...where 条件;-- 没有条件限制会导致所有数据全部更新。

三、delete

-- 语法格式
delete from 表明 where 条件;
-- 没有条件，整张表的数据会全部删除。

DDL

数据定义语言（操作表的结构的）

create 新建

drop 删除

alter 修改

一、表的创建

-- 语法格式
create table 表名(
	字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型
);
-- 可以在数据类型后加default关键字设置默认值。
-- 表名建议以t_或tbl_开始，可读性强。
-- 命名规范：所有的标识符都是小写，单词和单词之间使用下划线衔接。
-- 如果有日期字段，且格式为%Y-%m-%d，则可省略str_to_date函数。

-- 常见数据类型
varchar -- 可变长字符串，会根据实际长度动态分布空间。最长255
char -- 定长字符串。最长255
int -- 整数型。最长11
bigint -- 长整型，相当于java中的long。
float -- 单精度浮点型。
double -- 双精度浮点型。
date -- 短日期类型
datetime -- 长日期类型
clob -- 字符大对象，最多可以存储4G的字符串。
blob -- 二进制大对象，专门用来存储图片、声音、视频等流媒体数据。插入数据需使用IO流。

-- 快速复制表
create table 新表名 as select * form 被复制的表名;
-- 就是将select查询结果当作一张表新建

二、删除表

drop table 表名;-- 当删除表不存在时会报错。
drop table if exists 表名;-- 如果这张表存在，删除。

三、快速删除表中数据

delete语句删除数据原理，(属于DML语句)
	表中的数据删除了，但是数据在硬盘上的真实存储空间不会被释放。
	缺点：删除效率比较低。
	有点：支持回滚，后悔了可以再恢复数据。
truncate语句删除数据原理，
	表被一次截断，物理删除
	缺点：不支持回滚
	优点：快速
用法：truncate table 表名;

四、约束

非空约束：not null
-- 只有列级约束

唯一性约束：unique
-- unique(字段1，字段2...)表示联合唯一

主键约束：primary key(简称PK)

外键约束：foreign key(简称FK)

检查约束：check(mysql不支持，oracle支持)

-- 添加在列后面的叫列级约束，没有在列后的是表级约束
-- 在mysql中，如果一个字段同时被not null和unqiue约束的话，该字段自动变成主键字段(Oracle中不一样。)

-- 主键约束
主键值是每一行记录的唯一标识。任何一张表都应该有主键，没有主键表无效。主键只能有一个。且最好没有意义。
主键特征：not null + unique
主键可以采用自增,auto_increment，从1开始以1递增。

-- 外键约束使用
foreign key(外键字段) references 引用表(引用字段)
-- 外键引用的字段需要具有唯一性

TCL

事物控制语言

commit 事物提交

rollback 事物回滚

DCL

数据控制语言

grant 授权

revoke 撤销权限

二、常用命令

desc(全称:describe) 查看表结构

select Version(); 查看mysql版本号

select database(); 查看当前使用的是哪个数据库

三、数据处理函数

now()函数获取系统当前时间，datetime类型的。

1、常见的单行处理函数

单行处理函数特点：一个输入对应一个输出。

Lower 转换小写
upper 转换大写
substr 取子串，三个参数，(被截取的字符串,起始下标,截取的长度) P:起始下标是1没有0
length 取长度
trim 去前面的空格
str_to_date('字符串日期','日期格式') 将varchar类型转换成date类型
mysql的日期格式：
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
date_format(日期类型数据,'日期格式') 将date类型转换成具有一定格式的varchar类型
format 设置千分位
round(数值,保留几位小数) 四舍五入
rand() 生成随机数
case..when..then..when..then..else..end。匹配多条件
Ifnull(数据,被当做哪个值) 可以将null转换成一个具体值
P:NULL只要参与运算，最终结果一定是NULL。为避免此现象，使用ifnull。

P:字符串拼接用concat

2、分组函数(多行处理函数)

多行处理函数特点：输入多行，最终输出一行。

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值

P:分组函数在使用的时候须进行分组。如果没有分组，默认整张表为一组。
分组函数自动忽略NULL，不需要对NULL进行处理。
所有的分组函数可以组合使用。(select sum(),max(),avg()...)
不能直接用在where后。

四、存储引擎

-- 查看mysql支持哪些存储引擎
show engines \G

-- mysql常用的存储引擎
-- MyISAM存储引擎
使用三个文件表示每个表：
	格式文件-存储表结构的定义(mytable.frm)
	数据文件-存储表行的内容(mytable.MYD)
	索引文件-存储表上索引(mytable.MYI)
	优势：可被转换为压缩、只读来节省空间
	缺点：不支持事务，安全性低。
-- InnoDB存储引擎
mysql默认存储引擎，同时也是一个重量级的存储引擎。
支持事务，支持数据库崩溃后的自动恢复机制。
特征：
	-每个InnoDB表在数据库中以.frm格式文件表示
	-InnoDB表空间tablespace被用于存储表的内容
	-用COMMIT(提交)、SAVEPOINT以及ROLLBACK(回滚)支持事务处理
	-提供全ACID兼容
	-在mysql服务器崩溃后提供自动修复
	-多版本(MVCC)和行级锁定
	-支持外键及引用的完整性，包括级联删除和更新
	优势：非常安全
	缺点：效率不是很高，不能压缩，只能转换为只读，不能很好的节省储存空间
-- MEMORY存储引擎
使用该存储引擎的表数据存储在内存中，且行的长度固定。
特征：
	-在数据库目录内，每个表以.frm格式的文件表示
	-表数据及索引被存储在内存中。
	-表级锁机制。
	-不能包含TEXT或BLOB字段。
	优势：查询效率是最高的
	缺点：不安全，关机后数据消失，因为都在内存中
以前被称为HEAP引擎

五、事务

一个事务就是一个完整的业务逻辑。一个最小的工作单元，不可再分。只有DML语句才和事务有关。事务就是批量的DML语句同时成功，或同时失败。

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。

在事务的执行过程中，可以提交事务，也可以回滚事务。

-- 提交事务
	清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
	提交事务标志着事务的结束。并且是一种全部成功的结束。
-- 回滚事务
	将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
	回滚事务标志着事务的结束。并且是一种全部失败的结束。

-- 提交事务
commit;
-- 回滚事务
rollback;-- 回到上一次的提交点。
-- 开始事务
start transaction;
/*mysql默认情况下是自动提交事务的，每执行一次DML语句就提交一次，当手动开启事务后mysql将关闭自动提交机制。*/

-- 事务的4个特性
A(Atomicity)：原子性
	说明事务是最小的工作单元，不可再分。
C(Consistency)：一致性
	在同一个事务中，所有操作必须同时成功或同时失败。
I(Isolation)：隔离性
	A事务和B事务之间存在一道墙。
D(Durability)：持久性
	事务最终结束的保障。事务提交。

-- 事务的隔离性
4个级别：
	读未提交：read uncommitted(最低的隔离级别)
	-- 事务A可以读取到事务B未提交的数据，这种隔离级别存在的问题，脏读现象(Dirty)。
	
	读已提交：read committed
	-- 事务A只能读取到事务B提交之后的数据，解决了脏读现象。每一次读到的数据绝对真实。存在的问题是不可重复读取数据。是oracle的默认隔离级别。
	
	可重复读：repeatable read
	-- 事务A开启之后，不管过多久，每次在事务A中读取到的数据都是一致的，即使事务B将数据已经修改并提交，事务A读到的数据还是没有发生改变。存在的问题是可能出现幻读，不够真实。mysql默认的隔离级别。
	
	序列化/串行化：seriallizable(最高的隔离级别)
	-- 效率最低，解决了所有的问题。事务排队，不能并发。

六、索引

索引(index)是在数据库表的字段上添加的，为了提高查询效率存在的一种机制。

mysql数据库中索引是要排序的，排序和TreeSet数据结构相同。TreeSet(TreeMap)底层是一个自平衡的二叉树，在mysql中索引是一个B-Tree数据结构。

遵循左小又大原则存放，采用中序遍历方式遍历取数据。

在任何数据库中，主键上都会自动添加索引对象，一个字段上如果有unique也会自动创建索引对象。

不要随意添加索引，因为索引也是需要维护的，太多会降低系统性能。

-- 创建索引
create index 索引名 on 表(字段);
-- 删除索引
drop index 索引名 on 表;
-- 查看SQL语句是否使用了索引检索
explain SQL语句;

-- 索引失效例子一
select * from emp where ename like '%T';
/*ename上即使添加了索引也不会走索引，因为模糊匹配中以'%'开头了，不知道第一个字母无法比对
所以应尽量避免模糊查询的时候以'%'开头。这是一种优化策略*/
-- 索引失效例子二
如果使用or那么要求or两边都条件字段都要有索引，否则失效。
-- 索引失效例子三
使用复合索引的时候，没有使用左侧的列查找，索引失效。
-- 索引失效例子四
在where当中索引列参加了运算，索引失效。
-- 索引失效例子五
在where当中索引列使用了函数。

七、视图

-- 创建视图
create view 视图名 as DQL语句;
-- 删除视图
drop view 视图名;
-- 可以面向视图对象进行增删改查，对视图对象的增删改查会导致原表被操作。
-- 视图可以简化SQL语句，并且利于后期维护
-- 视图是存储在硬盘上的，不会消失

八、DBA命令

只需掌握导入导出。

-- 导出数据库(在windows的DOS命令窗口中)
mysqldump 数据库名>导出位置\导出sql文件名.sql -uroot -p密码
-- 导出数据库某张表(在windows的DOS命令窗口中)
mysqldump 数据库名 表名>导出位置\导出sql文件名.sql -uroot -p密码
-- 导入数据库
登入mysql
创建数据库
使用数据库
source 导入sql文件位置

九、数据库设计三范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖。(多对多，三张表，关系表，两个外键。)

第三范式：在第二范式的基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖。(一对多，两张表，多的表加外键。)

一对一，外键唯一。

设计数据库表时按照以上范式进行，可以避免表中数据的冗余，空间的浪费。

数据库设计三范式是理论上的，实践和理论有时候有偏差，最终的目的都是为了满足客户的需求，有时候会拿冗余换执行速度。因为在sql中，表和表之间的连接次数越多，效率越低。有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的。