Mysql
一. DQL
1.1 简单查询语句
语法：
select
字段名1,字段名2,字段名3,...
from
表名;
*1. 任何一条sql语句以";"结尾 *2. sql语句不区分大小写
1.2 条件查询
语法：
select
字段,字段,...
from
表名
where
条件;
执行顺序：先from，然后where，最后select
1.3 NULL
在数据库中NULL不是一个值，它代表什么也没有，为空。空不是值，不能用等号衡量，必须使用is null 和is not null
1.4 and和or优先级
and 的优先级大于or
注意：当运算符的优先级不确定的时候加小括号
1.5 in
in后面的值不是区间，是具体的值
select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
select ename,job from emp where job in ('SALESMAN','MANAGER');
1.6 模糊查询
模糊查询like（两个特殊的符号，一个是'%'，一个是'_'）
% 代表任意多个字符
_ 代表任意一个字符
1.7 排序（升序、降序）
order by
默认升序，asc升序，desc降序
注意：越靠前的字段越能起主导作用。只有当前面的字段无法完成排序的时候，才会启动后面的字段。
select
字段
from
表名
where
条件
order by
...
// order by是最后执行的
1.8 分组函数（多行处理函数）
count（计数）
sum（求和）
avg（平均值）
max（最大值）
min（最小值）
所有的分组函数都是对“某一组”数据进行操作的
*分组函数自动忽略null
重点：
-
只要有NULL参与的运算，结果一定是NULL；
-
SQL语句当中有一个语法规则：分组函数不可直接使用在where子句当中（因为where子句是在group by子句执行之前执行，还未进行分组时不能使用分组函数；无group by时，表中数据自成一组。）
group by和having
group by：按照某个字段或者某些字段进行分组
having：对分组之后的数据进行再次过滤
*分组函数通常都会和group by联合使用，并且任何一个分组函数都是在group by语句执行结束之后才会执行。
当一条sql语句没有group by的话，整张表的数据会自成一组
当一条sql语句中有group by的话，select后面只能跟分组函数和参与分组的字段
1.9 单行处理函数
ifnull() 空处理函数
语法：
ifnull(可能为null的数据，被当做什么处理)
完整的DQL语句：
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
补充：
关于查询结果集的去重：distinct 关键字
distinct 只能出现在所有字段的最前面，后面所有的字段联合起来去重
1.10 连接查询
根据表的连接方式来划分：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（很少用）
在表连接查询时有一种现象被称为：笛卡尔积现象。当两张表进行连接查询时，没有任何条件限制，最终的查询结果条数是两张表记录条数的乘积
如何避免笛卡尔积现象？加条件进行过滤
思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
不会，次数还是原来的匹配次数，只不过显示的是有效记录。
内连接
内连接之等值连接：最大特点是条件为等值关系
select
e.name,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
语法：
....Ajoin Bon 连接条件Where ...
*SQL99的写法：语法更清晰，表连接的条件和后来的where条件分离了
内连接之非等值连接：连接条件中的关系是非等量关系
select e.ename,e.sal,s.gradefrom emp ejoin salgrade son e.sal between s.losal and s.hisal;
自连接：一张表看做两张表（自己连接自己）
select a.ename as '员工',b.ename as '领导名'from emp ainner join //inner可以省略 emp bon a.mgr = b.empno;
外连接
假设A、B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
select a.ename '员工',b.ename '领导名'from emp aleft outer join //outer可以省略 emp bon a.mgr = b.empno;
三张以上表连接查询
.....Ajoin B on ...join C on ...
A表和B表先进行连接，连接之后A表继续和C表连接
select e.ename '员工',d.dname,s.grade,e1.ename '领导'from emp ejoin //outer可以省略 dept don e.deptno = d.deptnojoin salgrade son e.sal between s.losal and s.hisalleft join emp e1on e.mgr = e1.empno
1.11 子查询
select语句当中嵌套select语句，被嵌套的select语句是子查询
可以出现的位置：
select ..(select)from ..(select)where ..(select)
（1）where后面嵌套子查询
select * from emp where sal>(select avg(sal) from emp);
（2）from后面嵌套子查询
select t.*,s.gradefrom (select deptno,avg(sal) as avgsal from emp group by deptno) tjoin salgrade son t.avgsal between s.losal and s.hisal;
（3）select后面嵌套子查询
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dnamefrom emp e;
1.12 union
可以将结果集相加
两张不相干的表中的数据拼接在一起显示
1.13 limit
分页查询全靠它，是mysql特有的，不通用
取结果集中的部分数据
语法：
limit startIndex,length // startIndex 表示起始位置，从0开始，0表示第一条数据 // length 表示取几个
例子：每页显示pageSize条记录
第pageNo页：(pageNo-1)*pageSize,pageSize
limit (pageNo-1)*pageSize,pageSize
二. 创建表
建表语句的语法格式：
create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型, ...);
mysql中字段的数据类型（常见）：
int 整数型（对应java中的int）
bigint 长整型（对应java中的long）
float 浮点型（float、double）
char 定长字符串（String）
varchar 可变长字符串（StringBuffer、StringBuilder）
date 日期类型（对应java中的java.sql.Date类型）
BLOB 二进制大对象（存储图片，视频等）（Object）
CLOB 字符大对象（Object）
三. insert语句插入数据
语法格式：
insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
注意：当一条insert语句执行成功后，表格当中必然会多一行记录
四. 表的复制
语法：
creat table 表名 as select 语句;
将查询结果当做表创建出来
五. 修改数据
update
语法格式：
update 表名 set 字段名1 = 值1,字段名2 = 值2, ... where 条件;
注意：没有条件整张表数据全部更新；字段之间用 ',' 隔开，不是and
六. 删除数据
语法格式：
delete from 表名 where 条件;注意：没有条件会删除表中所有数据
怎么删除大表中的数据
truncate table 表名; //表被截断，不可回滚，永久丢失
对于表结构的修改（DDL：create、drop、alter）可以通过工具进行
增删改查有一个术语：CRUD操作
Create(增)、Retrieve(查)、Update(修改)、Delete(删除)
七. 约束
在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束：
非空约束（not null）：约束的字段不能为null
唯一性约束（unique)：约束的字段不能重复
主键约束（primary key）：约束的字段既不能为null，也不能重复
外键约束（foreign key）：见详细描述
检查约束（check）：注意Oracle数据库有check约束，mysql没有，不支持
详细介绍
非空约束
非空约束的字段不能为空
唯一性约束
*唯一性约束修饰的字段具有唯一性，不能重复，但可以为null。
例：给两个列或多个列添加unique
drop table if exists t_user;create table t_user( id int, username varchar(255) unique //列级约束);
create table t_user( id int, usercode varchar(255), username varchar(255), unique(usercode,username) //多个字段联合起来添加1个约束unique（表级约束）);
主键约束
例：
create table t_user( id int primary key, username varchar(255), email varchar(255));
术语：
主键约束：primary key
主键字段：id字段值添加primary key之后，id叫做主键字段
主键值：id字段中的每一个值都是主键值
*主键作用：是这行记录在这张表中的唯一标识，一张表的主键约束只能有1个
表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
主键分类：
1.根据主键字段的数量来划分
单一主键：推荐、常用
复合主键：多个字段联合起来添加一个主键约束；不建议使用，违背三范式
2.根据主键性质来划分
自然主键：和业务无关的自然数
业务主键：主键值和系统的业务挂钩
*mysql提供主键值自增
create table t_user( id int primary key auto_increment, username varchar(255));
auto_increment：id字段自动维护一个自增的数字，从1开始，以1递增
外键约束
术语：
外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值
两张表（班级和学生表）
t_class（班级表）：
cno(pk) cname101 上海小学一年级1班102 上海小学一年级2班
t_student（学生表）：
sno(pk) sname classno（该字段添加外键约束fk） 1 zs 101 2 lisi 101 3 wangwu 101 4 yuanrui 101 5 bigdude 101
*t_student中的classno字段引用t_class表中的字段cno，此时t_student表叫做子表，t_student表叫做父表
顺序要求：
删除数据的时候，先删除子表，再删除父表；
添加数据的时候，先添加父表，再添加子表；
创建表的时候，先创建父表，在创建子表；
删除表的时候，先删除子表，再删除父表；
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) //重点);
*外键值可以为null
外键字段引用其他表的某个字段时，被引用的字段必须是主键吗？
回答：被引用的字段不一定是主键，但至少具有unique约束（唯一性约束）
八. 事务
是一个完整的业务逻辑单元，不可再分。
和事务相关的语句只有：DML（insert、delete、update）
因为这三个语句都是和数据库当中的“数据”相关的，事务的存在是为了保证数据的完整性，安全性。
事务的四大特性
ACID
A:原子性：事务是最小的工作单元，不可再分
C:一致性：事务必须保证多条DML语句同时成功或失败
I:隔离性：事务A、B之间具有隔离
D:持久性：最终数据必须持久化到硬盘文件中，事务才算成功结束
事务之间的隔离性
隔离级别（四个）：
第一级别：读未提交（read uncommitted）
对方事务还未提交，我们当前事务可以读取对方未提交的数据；存在脏读现象（dirty read）：读到了脏数据
第二级别：读已提交（read committed）
对方事务提交之后的数据我方可以读取到
存在的问题：不可重复读；解决脏读问题
第三级别：可重复读（repeatable read）
解决问题：不可重复读
存在问题：幻读
第四级别：序列化/串行化（serializable）
解决所有问题，但效率低，需要事务排队
*oracle 数据库默认隔离级别是：读已提交；mysql是可重复读
*mysql事务默认情况下是自动提交的
自动提交：只要执行一条DML语句则提交一次；怎么关闭自动提交：
start transaction
九. 索引
1、 相当于一本书的目录，通过目录可以快速的找到对应的资源
2、在数据库方面，查询一张表的时候有两种检索方式：
第一种方式：全表扫描
第二种方式：根据索引检索（效率高）
3、索引提高检索效率的根本原因是：缩小了扫描的范围
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库中的对象，需要不断维护。
比如：表中的数据经常被修改就不适合添加索引
4、创建/删除索引
create index 索引名称 on 表名(字段名)
drop index 索引名称 on 表名
5、考虑用索引的情况
-
数据量庞大（根据需求，线上环境）
-
该字段很少的DML操作
-
该字段经常出现在where子句中 注意：主键和具有unique约束的字段自动会添加索引，根据主键查询效率较高，尽量根据主键查询。
6、实现原理
通过 B Tree 缩小扫描范围，底层索引进行排序，分区，索引会携带数据在表中的“物理地址”，通过索引检索到数据之后，获取到关联的物理地址，通过物理定位表中的数据
索引底层采用的数据结构：B+Tree
7、分类
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段会自动添加索引
8、索引失效的情况
(1)模糊查询时，以%开头
(2)where中索引列使用了函数，或是索引列有运算
十. 数据库设计三范式
设计范式：按照三范式设计的表可以有效避免数据冗余
第一范式：任何一张表都应该具有主键，并且每一个字段原子性不可再分
第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖
*多对多，三张表，关系表俩外键
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
第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖
*一对多，两张表，多的表加外键
班级 t_class
cno(pk) cname 1 班级a 2 班级b
学生 t_student
sno(pk) sname classno(fk) 101 张1 1 102 张2 1 103 张3 2 104 张4 2 105 张5 2
提醒：在实际开发中，以满足客户的需求为主，有时候会拿冗余换执行速度
一对一：有两种设计方案
(1)主键共享
t_user_login 用户登录表
id(pk) username password 1 zs 123 2 ls 456
t_user_detail 用户详细信息表
id(pk+fk) realname tel 1 张三 111111111 2 李四 222222222
(2)外键唯一
id(pk) username password 1 zs 123 2 ls 456
id(pk+fk) realname tel userid(fk+unique) 1 张三 111111111 1 2 李四 222222222 2
十一. 存储引擎
1、完整的建表语句
create table 't_x'( 'id' int(11) default null)engine=InnoDB default charset=utf8;
建表的时候可以指定存储引擎，也可以指定字符集；mysql默认为InnoDB、UTF8
2、查看mysql支持的存储引擎
show engines \G
3、常见的存储引擎
MyISAM：
(1)不支持事务
(2)是mysql常用的，但不是默认的
(3)使用三个文件表示每个表
- 格式文件：存储表结构的定义（mytable.frm）
- 数据文件：存储表行的内容（mytable.MYD）
- 索引文件：存储表上索引（mytable.MYI）
(4)可被转化为压缩、只读表来节省空间
InnoDB：
(1)支持事务，行级锁，外键等，安全得到保障
(2)表结构存储在xxx.frm文件中
(3)数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转为只读
(4)数据库崩溃之后提供自动恢复机制
(5)InnoDB支持级联删除和级联更新
MEMORY：
(1)不支持事务，数据容易丢失，因为所有数据和索引都是存储在内存当中的
(2)查询速度快
京公网安备 11010502036488号