一篇上手MySQL常用知识点

注：本文使用的MySQL的版本为8.0用Navicat使用

一、MySQL的基础知识CRUD

1、数据库的基本操作

1.1、创建数据库

数据库可以理解为一个仓库，里面存放你想要的物品（数据），因此我们需要创建一个数据库对我们想要的数据进行管理。
创建数据库可以通过软件的功能进行创建，也可以使用DDL语言来创建。
语法：

创建数据库 : CREATE DATABASE IF NOT EXISTS 数据库名;
删除数据库 : DROP DATABASE IF EXISTS 数据库名;
查看数据库 : SHOW DATABASES;
使用数据库 : USE 数据库名;

1.2、创建表

仓库里面有很多地方，如果物品随意摆放使用不方便，造成不必要的麻烦，因此需要对里面的物品进行分类汇总，而数据库表，相当于一个分类的标准，规定了有那些物品可以放在这里。

语法：

CREATE TABLE IF NOT EXISTS `表的名称` (
  `字段名1` 字段类型(长度) NOT NULL AUTO_INCREMENT COMMENT '字段的备注内容，可以不写',
  `字段名2` 字段类型(长度) NOT NULL DEFAULT 'DEFAULT默认的一个值，可以不写' COMMENT '字段的备注内容，可以不写',
  `字段名3` 字段类型(长度)  COMMENT '字段的备注内容，可以不写',
  `字段名4` 字段类型(长度)  COMMENT '字段的备注内容，可以不写',
  PRIMARY KEY (`主键的字段名，自己设计`)
) ENGINE=引擎类型 DEFAULT CHARSET=utf8

例子：
创建一个学生表

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `studentName` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

运行结果

解析：

IF NOT EXISTS：判断该表是否存在，如果不存在则进行创建表。
NOT NULL ：表示该数据不能为空。
AUTO_INCREMENT：表示创建数据时，该数据自动加1进行计算，常用于修饰主键。
DEFAULT  ：英文的意思是默认，也就是说给该数据一个默认的值，每新建一行数据时，默认的值都会添加上。COMMENT  ：就是注释，给你创建的字段一个说明。
PRIMARY KEY ：设置该表的主键是那个字段。
ENGINE  设置表使用哪种引擎方式。
DEFAULT CHARSET  设置表使用哪种字符串编码格式。

1.3、数据库引擎

MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等…

常见的数据库引擎有两种MyISAM和InnoDB

2、Create增加数据

Create增加数据顾名思义就是往数据库中插入数据，例如用户的注册、商品的补货。
语法：
INSERT INTO 表名[(字段1,字段2,字段3,…)] VALUES(‘值1’,‘值2’,‘值3’)
例：

INSERT INTO student (studentName,password,gender,birthday,address,email)VALUES('张三','123456','男','2000-1-1','广东','111@qq.com')

运行结果

3、Update修改数据

更新数据，就是将数据库中某个数据修改，对数据进行更新。比如商品库存的变化、账号密码的修改。
语法格式：

UPDATE 表名 SET 字段名1=更新的值1,字段名2=更新的值2 WHERE 筛选条件;

例:

UPDATE student SET studentName="李四",password="root" WHERE id=1

运行结果

4、Delete删除数据

删除数据，就是把该数据给删除掉，例如用户的注销、商品的下架。
语法格式

DELETE FROM 数据库表名 WHERE 筛选条件

注意：如果筛选条件不写，将会把整个表的数据给删除掉。
例：

DELETE FROM student WHERE id=1

运行结果

5、Retrieve查询数据

查询语句是使用频率最高的一个语句

语法格式

1、 SELECT  查询内容  FROM  数据库名称
2、 SELECT  查询内容  FROM  数据库名称  WHERE  满足条件的查询

满足条件的查询有精准查询和模糊查询

• 精准查询：相当于Java中的==，只有满足条件才能进行查询。
• 模糊查询：模糊查询有between and \ like \ in \ null，模糊查询顾名思义就是根据某一个条件，但该条件很模糊的查询，这里使用LIKE来演示。
  1、LIKE结合通配符%（代表0到任意个字符）
  2、 _（一个字符）来使用。

例子1：查询表中所有的信息

SELECT * FROM student

注：查询语句中的 * 相当于通配符所有的意思。
运行结果

例子2：查询表中某个数据的信息

SELECT password,email FROM student

运行结果

例子3：查询符合条件的信息

SELECT password,email FROM student WHERE studentName="李四"

运行结果

例子4：模糊查询LIKE 搭配通配符%使用

SELECT studentName,password FROM student WHERE email LIKE '111%'

运行结果

例子4：模糊查询LIKE 搭配通配符_使用

SELECT password,email FROM student WHERE studentName LIKE '李_'

运行结果

例子5：自链接、内连接、外连接、左外链接、右外链接、等值连接和非等值连接都省略不写了

二、MySQL的事务和索引

事务

1、什么是事务？

• 事务就是将一组SQL语句放在同一批次内去执行
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型

2、事务四大特性ACID

原子性(Atomic)
整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)
一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

隔离性(Isolated)
隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)
在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

例子：
A在线买一款价格为500元商品,网上银行转账，A的银行卡余额为2000,然后给商家B支付500，商家B一开始的银行卡余额为10000，创建表account并插入2条数据

创建表account

CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

插入数据

INSERT INTO account (`name`,`cash`)VALUES('A',2000.00),('B',10000.00)

事务实现过程

SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务,事务一旦提交成功，数据将会持久化
ROLLBACK; -- 回滚事务
SET autocommit = 1; -- 恢复自动提交

运行结果

索引

1、什么是索引？

索引能帮助MySQL高效获取数据的数据结构，可以理解为一本书的目录，根据该目录就可以快速找到自己想要看的章节。

2、索引的作用

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

3、索引的分类

主键索引 (Primary Key)

主键 : 某一个属性组能唯一标识一条记录
特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

唯一索引 (Unique)

作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别

• 主键索引只能有一个 唯一索引可能有多个

常规索引 (Index)

作用 : 快速定位特定数据
注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

全文索引 (FullText)

作用 : 快速定位特定数据
注意 :

• 只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

三、MySQL的约束

1、什么是约束？

约束，顾名思义就是给表中数据添加限制条件，其作用是为了保证表中的记录完整和有效。比如usename字段中添加约束，那么其用户名就不重复了。

2、约束的种类

2.1、非空约束(not null)

字段中加入NOT NULL ，让该字段在使用的时候不能为空。

2.2、唯一性约束(unique)

unique约束的字段，具有唯一性，不可重复，但可以为null

2.3、主键约束(primary key) PK

每张表设置一个主键，相当于编号，查找起来也比较方便。

2.4、外键约束(foreign key) FK

A为基本表，B为信息表
若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。可以理解为两个表的交集部分就是主键，表中的内容可以不一样，主键可以控制外键的内容。

2.5、检查约束(目前MySQL不支持、Oracle支持)

2.6、约束演示小案例

创建一个学生信息表Student和一张学生成绩表achievement。

1、创建学生信息表Student

CREATE TABLE IF NOT EXISTS `student`(
`sid` INT(5) NOT NULL UNIQUE COMMENT '学号',
`username` VARCHAR(20) NOT NULL COMMENT '姓名',
`classNo` VARCHAR(20) NOT NULL COMMENT'班级',
PRIMARY KEY(`sid`)
)ENGINE = INNODB DEFAULT charset=utf8
-- NOT NULL 为不为空约束
-- UNIQUE 为唯一性约束

2、创建学生成绩表achievement

CREATE TABLE achievement(
`id` INT(5) UNIQUE COMMENT '学号',
`username` VARCHAR(20) NOT NULL COMMENT '姓名',
`chinese` FLOAT(3) NOT NULL COMMENT '语文成绩',
`mathe` FLOAT(3) NOT NULL COMMENT '数学成绩',
`english` FLOAT(3) NOT NULL COMMENT '英语成绩',
FOREIGN KEY(`id`) REFERENCES `student`(sid)
ON DELETE CASCADE
)ENGINE = INNODB DEFAULT charset=utf8
-- FOREIGN KEY(`id`)声明表中的id为外键，REFERENCES `student`(sid) 说明了是student表sid的外键
-- ON DELETE CASCADE 解释了是在删除的时候触发事件

3、往表中插入数据
错误1：添加了唯一性约束，数据不唯一
因为在学生信息表中的电话字段添加了唯一性约束(unique)，如果我们的添加了重复的字段会报不唯一的异常。

错误2：主键数据与外键数据的不一致

正常录入

-- 添加学生信息
INSERT INTO student(sid,username,classNo) VALUES(1,"张三","文德班"),(2,"李四","B站班")
INSERT INTO achievement(id,username,chinese,mathe,english) VALUES(1,"张三",88,99,80),(2,"李四",90,90,90)

-- 查找学生信息
select * FROM student
SELECT * FROM achievement

-- 删除学生
DELETE FROM student WHERE sid =2

主键与外键的关系

四、MySQL的性能优化

1、性能优化与作用

性能优化可以理解为提高对数据的访问速度，让其效率更高，一般从SQL语句以及MySQL引擎选择和表设计上进行优化。

2、常见的性能优化的方法

（1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。
（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。
（3） 避免在索引列上使用计算
（4）避免在索引列上使用IS NULL和IS NOT NULL
（5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
（7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

五、MySQL的连接池

在没有使用链接池之前，数据的传输路径为：数据库链接–》执行完毕–》释放
由上面可见数据多次进行：链接–》释放 十分浪费系统资源，因此连接池的技术应运而生，
池化技术：准备一些预先的资源，过来就连接预先准备好的

常见连接池有三种：
1、DBCP
2、C3P0
3、Druid