MySQL的基本操作

1.MySQL基础

(1)MySQL是关系型数据库，开源并且免费，带有命令提示符的GUI(图形用户界面)

(2)关系型数据库：采用了关系模型来组织数据的数据库。

常见的：MySQL，Oracle，Microsoft SQL Server

(3)非关系型数据库：非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。

常见的：Redis，MongoDB，Elasticsearch

2.数据类型

int：整数
float：单精度浮点数
double：双精度浮点数
year：YYYY
time：HH:MM:SS
date：YYYY-MM-DD
datetime：YYYY-MM-DD HH:MM:SS
char：字符型，长度不可变，最多存放255个字符
varchar：字符型，长度可变，最多存放65535个字符

3.引擎

(1)三个引擎

InnoDB：提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它是用来处理大数据容量的数据库系统。

MyIASM(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高

(2)MyISAM和InnoDB的区别

MyISAM：每张表存放在三个文件，存储空间小，数据和索引是分别存储的，不支持外键和事务，支持表级锁，查询(select)用MyISAM更好，堆表

InnoDB：所有表都存在一个数据文件中，需要的内存和存储大，数据和索引是集中存储的，支持外键和事务，支持行级锁和表级锁，增(insert)删(delete)改(update)用InnoDB更好，是索引组织表

(3)存储引擎的选择

如果没有特别需求一般使用默认的innoDB

4.索引

(1)什么是索引

索引相当于目录，是一个文件，要占用物理空间

(2)索引的优缺点

索引的优点：可以加快数据的检索速度，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：创建索引和维护索引要耗费时间，索引需要占物理空间。

(3)索引的类型

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。
可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

5.事务

(1)什么是数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

(2)同时进行多个事务会造成

脏读：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新了原有的数据。

幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

6.锁

(1)什么是锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

行级锁：行级锁是Mysql中锁定粒度最细的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折中的页级锁，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

(2)死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

解决办法
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

7.SQL语句

(1)SQL语句主要分为

数据定义语言DDL：CREATE，DROP，ALTER
数据查询语言DQL：SELECT
数据操纵语言DML：INSERT，UPDATE，DELETE
数据控制语言DCL：GRANT，REVOKE，COMMIT，ROLLBACK

(2)主键，外键

主键：用来保证数据得完整性和唯一性，不能重复，不能为空，一个表中只能有一个主键
外键：其他表的主键，用来和其他表建立联系，可以为空，可以重复，可以有多个

(3)SQL约束

not null：非空约束，用于控制字段的内容一定不能为空
unique：唯一性约束，控制字段内容不能重复，一个表允许有多个unique约束
primary key：主键
foreign key：外键
auto_increment：自增

(4)连接查询

内连接：inner join

左外连接：left join，返回左表中的所有行

右外连接：right join，返回右表中的所有行

全外连接：full join，相当于左外和右外的并集，mysql不支持

全连接：union，union all
区别：union返回值不包括重复行，会对结果自动排序
union all返回所有行，不会对结果自动排序

(5)子查询

条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果

嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

(6)drop，delete和truncate的区别

drop：属于DDL，不可回滚，从数据库中删除整个表
delete：属于DML，可回滚，表结构还在，删除表中的所有行，可以用where删除指定行
truncate：属于DDL，不可回滚，表结构还在，删除表中的所有数据

(7)HAVING和WHERE的区别

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

(8)ORDER BY和GROUP BY的区别

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

(9)异常捕获try执行语句后，如果存在finally语句，不管有无异常finally语句下总是会被执行

8.SQL优化

1. 选取最适合的字段属性
2. 使用连接（JOIN）来代替子查询
3. 事务
4. 锁定表
5. 使用外键
6. 使用索引
7. 优化查询语句