在数据库当中 NULL 不是一个值，代表什么也没有，为空。 空不是一个值，不能用等号衡量。 必须使用 is null 或者 is not null

and 和 or 联合起来用：找出薪资大于 1000 的并且部门编号是 20 或 30 部门的员工。select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno = 30; // 错误的

select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30); // 正确的。

注意：当运算符的优先级不确定的时候 加小括号 。

in 等同于 or： select ename,job from emp where job in('SALESMAN', 'MANAGER');

模糊查询 like 找出名字当中含有 O 的？（在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是*）   %代表任意多个字符，*代表任意 1 个字符。 select ename from emp where ename like '%O%';

找出名字中第二个字母是 A 的？ select ename from emp where ename like '_A%';

找出名字中最后一个字母是 T 的？ select ename from emp where ename like '%T';

查询结果去重 distinct  distinct 只能出现在所有字段的最前面。 select distinct job from emp;

排序 ORDER BY

默认是升序. asc 表示升序，desc 表示降序。

select ename , sal from emp order by sal; // 升序
select ename , sal from emp order by sal asc; // 升序
select ename , sal from emp order by sal desc; // 降序。

按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

select ename,sal from emp order by sal desc;
select ename,sal from emp order by sal desc , ename asc;

注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。 order by 是最后执行的

分组/聚合函数

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

select sum(sal) from emp where JOB='CLERK';

所有的分组函数都是对“某一组”数据进行操作的。

找出工资总和？ 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 个。 分组函数还有另一个名字：多行处理函数。 多行处理函数的特点：输入多行，最终输出的结果是 1 行。 分组函数自动忽略 NULL。

所有数据库都是这样规定的， 只要有 NULL 参与的运算结果一定是 NULL。 使用 ifnull 函数：ifnull(可能为 NULL 的数据,被当做什么处理) ： 属于单行处理函数。 select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;  sum 函数自动忽略 null

分组函数也能组合起来用： select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

count(*)和 count(具体的某个字段)，他们有什么区别？

• count(*):不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
• count(comm): 表示统计 comm 字段中不为 NULL 的数据总数量。

select		5
		..
	from			1
		..
	where			2
		..
	group by		3
		..
	having		4
		..
	order by		6
		..

记住一个规则：当一条语句中有 group by 的话，select 后面只能跟分组函数和参与分组的字段。

分组查询

分组查询主要涉及到两个子句，分别是：group by 和 having 一个完整的 select 语句格式如下

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

以上语句的执行顺序

1. 首先执行 where 语句过滤原始数据
2. 执行 group by 进行分组
3. 执行 having 对分组数据进行操作
4. 执行 select 选出数据
5. 执行 order by 排序 原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤的。

连接查询 JOIN

在表的连接查询方面有一种现象被称为：笛卡尔积现象。 笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

关于表的别名： select e.ename,d.dname from emp e,dept d;  表的别名有什么好处？

• 第一：执行效率高。
• 第二：可读性好。

内连接中的等值连接

select
		e.ename,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;

内连接的非等值连接 工资->工资等级

mysql> select e.ename , e.sal,s.grade
    -> from emp e
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal;

自连接: 一张表看出两张不同的表 什么是外连接，和内连接有什么区别？

1. 内连接： 假设 A 和 B 表进行连接，使用内连接的话，凡是 A 表和 B 表能够匹配上的记录查询出来，这就是内连接。 AB 两张表没有主副之分，两张表是平等的。
2. 外连接： 假设 A 和 B 表进行连接，使用外连接的话，AB 两张表中有一张表是主表，一张表是副表，主要查询主表中 的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出 NULL 与之匹配。

外连接的分类？

• 左外连接（左连接）：表示左边的这张表是主表。
• 右外连接（右连接）：表示右边的这张表是主表。

左连接有右连接的写法，右连接也会有对应的左连接的写法。

外连接：（右外连接/右连接）
select
	a.ename '员工', b.ename '领导'
from
	emp b
right join
	emp a
on
	a.mgr = b.empno;

UNION 并集

select * from emp where job='MANAGER'
union
select * from emp where job='SALESMAN'

合并结果集的时候，需要查询字段对应个数相同。在 Oracle 中更严格，不但要求个数相同，而且还要求类型对应相同。

LIMIT 的使用

select * from table limit m,n  其中 m 是指记录开始的 index，从 0 开始，表示第一条记录 n 是指从第 m+1 条开始，取 n 条。

select * from tablename limit 2,4  即取出第 3 条至第 6 条，4 条记录

limit是sql语句最后执行的一个环节：
	select		5
		...
	from			1
		...
	where			2
		...
	group by		3
		...
	having		4
		...
	order by		6
		...
	limit			7
		...;

分页的 sql 语句 limit (PageNo-1)*PageSize, PageSize;

表

创建表

create table tableName(
   columnName dataType(length),
   ………………..
   columnName dataType(length)
);
set character_set_results='gbk';

show variables like '%char%';

创建表的时候，表中有字段，每一个字段有：
     * 字段名
     * 字段数据类型
     * 字段长度限制
     * 字段约束

数据类型

说明: 如果需使用Java中的 Boolean 类型可以使用 bit(1)

增/删/改 DDL

//删除表
drop table if exists t_student;

//添加字段
alter table t_student add  contact_tel varchar(40);
//修改字段
alter table t_student modify student_name varchar(100) ;
alter table t_student change sex gender char(2) not null;

//删除字段
alter table t_student drop contact_tel;

增/删/改 DML

//添加记录
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
insert into emp values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

如何插入日期： 第一种方法，插入的日期格式和显示的日期格式一致 第二种方法，采用 str_to_date 第三种方法，添加系统日期（now()）

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,'zhangsan','MANAGER',null,now() ,3000, 500, 10);

表复制

create table emp_bak as select empno,ename,sal from emp;

以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中 如何将查询的数据直接放到已经存在的表中，可以使用条件

insert into emp_bak select * from emp where sal=3000;

数据更新 可以修改数据，可以根据条件修改数据

update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where ……

数据删除

delete from表名 where 。。。。。

创建表加入约束

常见的约束 a) 非空约束，not null b) 唯一约束，unique   c) 主键约束，primary key d) 外键约束，foreign key e) 自定义检查约束，check（不建议使用）(在 mysql 中现在还不支持)

非空约束， 针对某个字段设置其值不为空，如：学生的姓名不能为空

drop table if exists t_student;
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date,
	email		varchar(30),
	classes_id	int(3)
)
//加入不符合约束条件的内容会报错

唯一约束 unique 唯一性约束，它可以使某个字段的值不能重复，如：email 不能重复：[每个字段一个约束-列级约束]

drop table if exists t_student;
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date,
	email		varchar(30)  unique,
	classes_id	int(3)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)

给两个列或者多个列添加 unique [多个字段联合添加一个约束-表级约束]

drop table if exists t_user;
create table t_user(
  	id int,
  	usercode varchar(255),
  	username varchar(255),
  	unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
);

注意：not null 约束只有列级约束。没有表级约束。 主键约束: 不为空且不能重复

drop table if exists t_user;
create table t_user(
  id int primary key,  // 列级约束
  username varchar(255),
  email varchar(255)
);

主键约束, 主键值, 主键字段 主键有什么作用？

• 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
• 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）

主键的分类:   根据 主键字段的字段数量 来划分：

1. 单一主键（推荐的，常用的。）
2. 复合主键 (多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）

根据 主键性质 来划分：

1. 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
2. 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

一张表的主键约束只能有 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
• 外键字段：添加有外键约束的字段
• 外键值：外键字段中的每一个值。

删除先删子, 再删除父表 创建的时候先创建父表, 后创建子表 外键可以为 NULL。  外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？ 注意：被引用的字段不一定是主键，但至少具有 unique 约束。

drop table if exists t_student;
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20),
	sex		char(2),
	birthday	date,
	email		varchar(30),
	classes_id	int(3),
	constraint student_id_pk primary key(student_id),
  // 外键约束
	constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
)

MySQL 函数

数字函数

日期函数

字符函数

条件函数

事务 TCL

SET AUTOCOMMIT=false;
START TRANSACTION ;
SELECT * FROM dept;
UPDATE dept set LOC='BEIJIN' WHERE LOC='NEW YORK';
SELECT * FROM  dept;
ROLLBACK ;
# COMMIT
SELECT * FROM  dept;

索引

什么是索引？有什么用？ 索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。 在数据库方面，查询一张表的时候有两种检索方式：   第一种方式：全表扫描   第二种方式：根据索引检索（效率很高） 索引为什么可以提高检索效率呢？ 其实最根本的原理是缩小了扫描的范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引， 因为数据一旦修改，索引需要重新排序，进行维护。 添加索引是给某一个字段，或者说某些字段添加索引。

select ename,sal from emp where ename = ‘SMITH’;   当 ename 字段上没有添加索引的时候，以上 sql 语句会进行全表扫描，扫描 ename 字段中所有的值。   当 ename 字段上添加索引的时候，以上 sql 语句会根据索引扫描，快速定位。

什么时候考虑 给字段添加索引 ？（满足什么条件）

1. 数据量庞大。（根据客户的需求，根据线上的环境）
2. 该字段很少的 DML(插入,删除)操作。（因为字段进行修改操作，索引也需要维护）
3. 该字段经常出现在 where 子句中。（经常根据哪个字段查询）
4. 该字段经常出现在连接操作的连接条件中.

注意：主键和具有 unique 约束的字段自动会添加索引。根据主键查询效率较高。尽量根据主键检索。

给薪资sal字段添加索引：
create index emp_sal_index on emp(sal);

索引底层采用的数据结构是：B+ Tree 索引的分类

1. 单一索引：给单个字段添加索引
2. 复合索引: 给多个字段联合起来添加 1 个索引
3. 主键索引：主键上会自动添加索引
4. 唯一索引：有 unique 约束的字段上会自动添加索引

索引的实现原理？ 通过 B Tree 缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = 'SMITH';
通过索引转换为：
select ename from emp where 物理地址 = 0x3;

索引什么时候失效？ select ename from emp where ename like '%A%';  模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

视图

什么是视图？ 站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

怎么创建视图？怎么删除视图？

create view myview as select empno,ename from emp;drop view myview;

注意：只有 DQL 语句才能以视图对象的方式创建出来。 对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表） 可以对视图进行 CRUD 操作。

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; // 通过视图删除原表数据。

视图的作用 视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java 程序员只对视图对象进行 CRUD。 通过多表连接产生的视图, 每次修改只能修改来自其中一张表的数据.

数据库设计三范式

参考：数据库逻辑设计之三大范式通俗理解，一看就懂，书上说的太晦涩 - SegmentFault 思否

一、第一范式

1NF是对属性的原子性，要求属性具有原子性，不可再分解；

表：字段1、 字段2(字段2.1、字段2.2)、字段3 ……

如学生（学号，姓名，性别，出生年月日），如果认为最后一列还可以再分成（出生年，出生月，出生日），它就不是一范式了，否则就是；

二、第二范式

2NF是对记录的唯一性 ，要求记录有唯一标识，即实体的唯一性，即 不存在部分依赖**；

表：学号、课程号、姓名、学分;

这个表明显说明了两个事务:学生信息, 课程信息;由于非主键字段必须依赖主键，这里 学分依赖课程号 ， 姓名依赖与学号 ，所以不符合二范式。

可能会存在问题：

• 数据冗余:，每条记录都含有相同信息；
• 删除异常：删除所有学生成绩，就把课程信息全删除了；
• 插入异常：学生未选课，无法记录进数据库；
• 更新异常：调整课程学分，所有行都调整。

正确做法: 学生：Student(学号, 姓名)； 课程：Course(课程号, 学分)； 选课关系：StudentCourse(学号, 课程号, 成绩)。

三、第三范式

3NF是对字段的冗余性 ，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即 不存在传递依赖；

表: 学号, 姓名, 年龄, 学院名称, 学院电话

因为存在 依赖传递 : (学号) → (学生)→(所在学院) → (学院电话) 。

可能会存在问题：

• 数据冗余:有重复值；
• 更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现 数据不一致的情况 。

正确做法：

学生：(学号, 姓名, 年龄, 所在学院)；

学院：(学院, 电话)。

四、反范式化

一般说来，数据库只需满足第三范式（3NF）就行了。

没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余， 达到以空间换时间的目的 。

〖例〗：如订单表，“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

在Rose 2002中，规定列有两种类型： 数据列 和 计算列 。“金额”这样的列被称为“计算列”，而“单价”和“数量”这样的列被称为“数据列”。

参考文档

• 数据库模型设计，第一范式、第二范式、第三范式简单例子理解_屋檐下的鞋-CSDN博客_数据库第一二三范式例题
• SQLZOO
• MySQL 教程
• SQL 教程
• 学习MySQL这一篇就够了_轻松的小希-CSDN博客_学习mysql
• MySQL是怎样运行的
• 数据库逻辑设计之三大范式通俗理解，一看就懂，书上说的太晦涩 - SegmentFault 思否