Java基础第19天-MySQL数据库

数据库三层结构

所谓安装Mysql数据库，就是在主机安装一个数据库管理系统（DBMS：database manage system），这个管理程序可以管理多个数据库。

一个数据库可以创建多个表，以保存数据（信息）。

数据库管理系统（DBMS）、数据库和表的关系如图所示：其中，数据库-表的本质依旧是文件

表的一行称之为一条记录->在java程序中，一行记录往往使用对象表示

SQL语句分类：

• DDL:数据定义语句（create表、库....）
• DML:数据操作语句（增加，修改，删除）
• DQL:数据查询语句（select）
• DCL:数据控制语句（管理数据库比如用户权限 ）

创建数据库:

CREATE DATABASE[IF NOT EXISTS] db_name

CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，默认utf8

COLLATE：指定数据库字符集的校对规则（常用的utf8_bin（区分大小写）、utf8_general_ci（不区分大小写）;其中默认是utf8_general_ci）

其中在创建数据库，表的时候，为了规避关键字，可以使用反引号``来解决

查看、删除数据库:

显示数据库语句：SHOW DATABASES

显示数据库创建语句：SHOW CREATE DATABASE db_name

数据库删除语句：DROP DATABASE[IF EXISTS] db_name;这个语句一定要慎用

备份和恢复数据库：

备份数据库（注意：在DOS执行）命令行

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql

恢复数据库，要进入Mysql命令行再执行：Source 文件名.sql

update语句：

delete语句：

使用delete语句删除表中数据：delete from db_name  WHERE .....

select语句：

字符串相关函数：

 合计/统计函数-count：

 其中count（*）和count（列）的区别是：count（*）会返回满足条件的记录的行数，count（列）统计满足条件的某列有多少个，但是会排除为null的情况

合计函数-sum：

sum函数返回满足where条件的行的和，一般使用在数值列

注意：sum仅对数值起作用，对非数值的列求和没有意义；对多列求和时，逗号“，”不能少

合计函数-avg：

AVG函数返回满足where条件的一列的平均值

 合计函数-Max/min：

Max/min函数返回满足where条件的一列的最大/最小值

数学相关函数：

给定seed后返回的随机数会不变

时间日期相关函数：

流程控制函数：

判断是否为null要使用 is null，判断不为空 要使用 is not

分页查询公式：

如果select语句同时包含有group by，having，limit，order by，那么他们的顺序时 group by，having，order by，limit

多表查询时，在默认情况下。两个表一起查询时，规则是：从第一张表中取出一行和第二张表的每一行进行组合，返回结构【含有两张表的所有列】；一共返回的记录数是第一张表的行数*第二张表的行数，这样多表查询默认处理返回的结构称为笛卡尔集，解决这个多表的关键就是要用where写出正确的过滤条件

自连接：

自连接是指在同一张表的连接查询，也就是说将同一张表看做两张表 ，查询时需要给表取别名，列明不明确，可以指定列的别名

子查询：

子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询

• 单行子查询：只返回一行数据的子查询语句
• 多行子查询：返回多行数据的子查询 使用关键字in

合并查询：

union all：取两个结果集的并集并且不会取消重复行；union：与union all的区别就是他会取消掉重复行

mysql表外连接

常用的有左外连接和右外连接，分别是显示左侧表所有信息和显示右侧表所有信息 

select..from 表1 left/right join 表2 on 条件

在实际的开发过程中，绝大情况下使用的是自连接。

mysql约束

约束用于确保数据库的数据满足特定的商业规则，包括：not null,unique,primary key,foreign key,check这五种

• primary key（主键）：用于唯一的标示表行的数据，当定义主键约束后，该主键列不能重复且不能为null，一张表最多只能有一个主键，但可以是复合主键比如：在实际开发中，每个表往往都有主键，使用desc表明可以看到主键的情况
• not null（非空）：如果在列上定义了not null，那么当插入数据时，必须为列提供数据
• unique（唯一）：当定义了唯一约束后，该列的值是不能重复的；如果没有指定not null，则unique字段可以有多个null，一张表可以有多个unique字段；如果一个列是unique not null，那么它的使用效果类似primary key
•  foreign key（外键）：用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null，如下图所示：（其中表的类型必须是innodb才支持外键，一旦建立主外键关系，数据就不能随意删除了）

  

• check：用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在1000~2000之间就会提示出错；在mysql中实现check的功能，一般是在程序中控制或者通过触发器完成（8.0之前的版本都不支持check，但是可以做语法校验，不会生效）

自增长

比如在某张表中，存在一个id列（整数），我们希望在添加记录的时候。该列从1开始，自动的增长，就可以这样设置，在创建表的时候在某一列后添加“AUTO INCREMENT”，

• 一般来说自增长是和primary key配合使用的
• 自增长也可以单独使用但是需要配合一个unique
• 自增长修饰的字段为整数型的（存在小数的情况但是非常非常少这样用）
• 自增长默认从1开始，也可以通过如下命令修改：alter table 表名 auto_increment = 新的开始值
• 如果你添加数据时，给自增长字段列指定的有值，那么就以指定的值为准，即下次再插入null时会在指定的值的前提上加一；如果指定了自增长一般就按照自增长的规则来添加数据，不要给指定值

索引

没有索引的时候，mysql查询时会因为全表扫描非常慢，使用索引后，会形成一个索引的数据结构比如二叉树..从而大大缩短查询的时间，索引的代价就是磁盘的占用以及对dml（update delete insert）语句的效率影响。

• 索引的类型

1. 主键索引，主键自动的为主索引（类型为primary key）
2. 唯一索引（UNIQUE）
3. 普通索引（INDEX）
4. 全文索引（FULLTEXT） 适用于MyISAM，开发中考虑使用Solr 和ElasticSearch（ES），一般开发不使用mysql自带的全文索引

   

如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引

• 哪些列上适合使用索引

1. 较频繁的作为查询条件字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件 比如性别
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在WHERE子句中字段不该创建索引

事务

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账事件：转出和转入必须同时成功，否则转账事件就是失败的，为了保证数据的一致性，必须要把两条语句当作一个整体来执行，要么全部成功，要么全部失败，这时就需要使用事务来解决。

当执行事务操作时（dml语句），mysql会在表上加锁，防止其他用户改表的数据，这对用户来讲是非常重要的

• mysql数据库控制台事务的几个重要操作

1. start transaction --开始一个事务/set autocommit = off
2. savepoint 保存点名 --设置保存点
3. rollback to 保存点名 -- 回退事务
4. rollback -- 回退全部事务
5. commit --提交事务，所有的操作生效，不能回退

当结束事务时（commit），会自动的删除该事务所定义的所有保存点，当执行回退事务时，通过指定保存点可以回退到指定的点

当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务后，其他会话（其他连接)将可以查看到事务变化后的新数据

 如果开始一个事务start transaction，没有创建保存点，可以执行rollback，默认就是回退到事务开始的状态

InnoDB存储引擎支持事务，MyISAM不支持

• 事务隔离级别

多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性

不考虑隔离性，可能会引发：脏读，不可重复读，幻读

• 脏读:当一个事务读取另一个事务尚未提交的修改时，产生脏读
• 不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读
• 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读

事务隔离级别定义了事务与事务之间的隔离程度，下图中v表示可能出现，x表示不会出现

语句：SET SESSION TRANSACTION ISOLATION LEVEL XXXX（隔离级别）

查看当前事务(会话)隔离级别 SELECT @@transaction_isolation；

查看当前系统隔离级别 SELECT @@global.transaction_isolation；

mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改，该级别已经可以满足绝大部分项目需求

• 事务ACID

mysql表类型和存储引擎

1. mysql的表类型由存储引擎（storage engines）决定，主要包括MyISAM、innoDB、Memory等
2. mysql数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoDB
3. 这六种又分为两类，一类是事务安全型（transaction-safe），比如：InnoDB；其余都属于第二类，称为非事务安全型（non-transaction-safe）如myisam和memory

查看所有的存储引擎：SHOW ENGINES

• 如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MySIAM是不二选择，速度快
2. 如果需要支持事务，选择InnoDB
3. Memory存储引擎就是将数据直接存储在内存中，由于没有磁盘和I/O的等待，速度极快，但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失，一些业务可能会需要用到比如用户的zai'xian'zhuang'd

视图

视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含列，其数据来自对应的真实表（基表）且可以是多个基表，通过视图可以修改基表的数据，基表的改变也会影响到视图的数据

• 视图的基本使用

1. create view 视图名 as select语句；创建一个视图
2. alter view 视图名 as select语句；修改视图
3. SHOW CREATE VIEW 视图名；展示最开始创建视图的指令
4. drop view 视图名1，视图名2；删除多个视图

创建视图之后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm），是没有数据结构的；视图中可以再使用视图 

• 视图的最佳实践

mysql管理

不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）都不一样