MySQL

MySQL特征：

• MySQL是开源的
• 支持大型数据库。可以处理拥有上千万条记录的大型数据库
• MySQL可以用于多个系统，并且支持多种语言
• MySQL支持定制，可以自己修改源码来开发自己的MySQL

MySQL逻辑架构

数据库逻辑结构共分为4层：连接层、业务逻辑处理层、存储引擎层、数据存储层

• 外部程序
• 连接层
• • 提供客户端和连接服务，管理缓冲用户连接、线程处理等需要缓存的需求
• 业务逻辑处理层
• • SQL接口：接受用户的SQL命令，并返回用户需要查询的结果
• • 解析器：SQL命令被解析器解析验证，解析器是一个很长的脚本，将SQL命令分解成数据结构，并将这个结构传递到后续步骤，以后的SQL语句的传递和处理就是基于这个结构，如果在分解过程中不合理就说明这个SQL语句是错误的。
• • 查询优化器：对SQL语句进行查询优化，它使用“选取–投影–连接”策略进行查询。
• • 缓存和缓冲池：查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个而过缓存机制由一系列小缓存组成。比如表缓存、记录缓存、Key缓存、权限缓存等
• 存储引擎层：数据的存储和提取，默认是InnoDB
• 数据存储层：将数据存储到磁盘上，并协同存储引擎对数据进行读写操作。

插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离，这种架构可以根据业务的需求来选择合适的存储引擎。

一条SQL语句的执行过程

• 应用程序与数据库服务器建立一个连接，权限校验和身份验证
• SQL接口接收用户的SQL语句
• 解析器解析验证SQL语句
• 查询优化 器对SQL语句进行优化
• 执行器执行语句，从存储引擎返回数据

1. 数据库优势，为啥需要数据库？

数据存储在内存，读取方便，但是不能永久存储。
数据存储在文件，可以永久保存，但是有频繁的IO操作，数据读取不方便。
数据存储在数据库，可以永久保存，SQL语句查询方便效率高，管理数据方便。

2. 数据库的三大范式

1. 第一范式：每一列都不可再拆分
2. 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能依赖主键列的一部分
3. 第三范式：在第二范式的基础上，非主键列只依赖于主键，而不能依赖其他非主键列

2.1 范式的优点和缺点

优点

1. 重复数据很少或者没有，修改的数据也更少
2. 表通常更小，可以更好地放在内存里，执行操作会比较快
3. 很少有多余的数据意味着检索列表时更少的使用DISTINCT或者GROUP

缺点：范式的缺点通常是需要关联

2.2 反范式的优点和缺点

优点：所有的数据都在一张表里，不需要关联
缺点：表比较大，更新数据比较麻烦，会有很多重复的数据，占用的内存会比较多

2.3 混用范式化和反范式化

在不同的表中存储相同的特定列

3. 数据库中有权限的表

user表：记录可以连接这个服务器的用户信息
db表：记录各个用户对于各个数据库的操作权限信息
table_priv表：记录数据表级的操作权限
columns_priv表：记录数据列级的操作权限
host表：和db包配合，对给定服务器上的数据库操作权限做更细致的控制

4. SQL语言

4.1 SQL语句分类

数据定义语句(DDL)：drop，create，alter，truncate
数据操作语言(DML)：insert，delete，update
数据查询语言(DQL)：select
数据控制语言(DCL)：grant，remoke，commit，rollback

4.2 超键，候选键，主键 外键

• 超键：在关系中能唯一标识一个元组的属性集称为超键，超键可以是一个属性，也可以是多个属性组合在一起。超键包含候选键和主键。
• 候选键：最小超键，没有冗余元素的超键
• 主键：唯一且不为空的属性，一个关系中只能有一个主键
• 外键：一个关系中存在另一个表的主键

4.3 关联查询

• 交叉连接（CROSS JOIN）
  SELECT * FROM A,B,C;或者SELECT * FROM A CROSS JOIN B CROSS JOIN C#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义。返回的结果是所有表的行数的乘积。
• 内连接（INNER JOIN）
  很少使用内连接（INNER JOIN）SELECT * FROM A,B WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN
  内连接又分为等值连接，不等值连接和内连接，内连接就是两边的表都是自己
• 外连接(LEFT/RIGHT JOIN)
  外连接分为左连接和右连接
• • 左连接又称为左外连接（LEFT OUT JOIN）。返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。
• • 右连接又称为右外连接（RIGHT OUT JOIN）。恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。
• 联合查询（UNION与UNION ALL）

SELECT * FROM A UNION SELECT * FROM B
就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并，UNION ALL不会合并重复的记录，UNION ALL效率更高

• 全连接（FULL JOIN）返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值。

4.4 子查询

• 子查询是单行单列，父查询用=，<，>等运算符
• 子查询是多行单列，父查询用in
• 子查询是多行多列，不能用于where条件，用于select子句中作为子表

-- 1) 查询出2011年以后入职的员工信息
-- 2) 查询所有的部门信息，与上面的虚拟表中的信息比对，找出所有部门ID相等的员工。
select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    

-- 使用表连接：
select d.*, e.* from  dept d inner join employee e on d.id = e.dept_id where e.join_date >  '2011-1-1'  

4.4 表的约束

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY：用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，必须是它指向的那个表中那一列的值之一
• CHECK：用于控制字段的范围

4.5 drop，delete，truncate的区别

	delete	truncate	delete
类型	DML	DDL	DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表中的全部或者部分数据行	表结构还在，删除表中的所有数据	表结构已不在，删除表中的全部数据
删除速度	慢，逐行删除	快	最快

4.6 常见SQL语句

show processlist

可以用来查看有哪些用户连接了数据库，如果有异常用户可能代表着你的数据库被入侵了

查看表结构

desc table_name;
创建表
create table users (
id int,
name varchar(20) comment ‘用户名’,
password char(32) comment ‘密码是32位的md5值’,
birthday date comment ‘生日’
) character set utf8 engine MyISAM;

在表中插入记录

insert into users values(1,‘a’,‘b’,‘1982-01-04’),(2,‘b’,‘c’,‘1984-01-04’);
假设主键冲突，可以使用更新操作：

insert into 表名 values(值列表) on duplicate key update 字段1=值, 字段2=值

mysql中常用的三种插入数据的语句:

insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，
或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

添加字段

alter table users add assets varchar(100) comment ‘图片路径’ after birthday;

修改表中是name，将其长度改为60

alter table users modify name varchar(60);

删除表中某个字段

alter table users drop password;

修改表名

alter table users rename to emploee;

表中属性改名

alter table emploee change name xingming varchar(60);
新字段需要完整定义

删除表

drop table emploee;

修改