MySQL学习笔记
参加拉勾教育大数据训练营课程笔记
1. 安装启动与配置
1.1 安装与启动
Win10下直接运行安装程序，按照提示安装即可。安装完成后可以运行MySQL 5.7 Command Line Client测试是否安装成功
输入密码，登录成功：
增加MySQL到系统PATH环境变量，然后运行在CMD运行mysql -uroot -pdont4get登录MySQL。
Win10下启动与关闭（也可手动在服务中启动、关闭、设置自启动）：
net start mysql57
net stop mysql57
1.2 目录结构
安装目录：
bin/ # executable binary files
docs/ # doc
include/ # header files
lib/ # lib
share/ # encodings and language
LICENSE
README
数据和配置文件C:\ProgramData\MySQL\MySQL Server 5.7：
Data/ # data folder
Uploads/
installer_config.xml
my.ini # configuration file
DBMS
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MeeEG5Mk-1624890267454)(dbms-1622556832988.jpg)]
2. SQL(Structured Query Language)
MySQL特点：
不区分大小写，一般关键字大写，数据对象名小写。#特有的单行注释。
分类：
DDL- Data Definition Language，创建、更改、删除数据库、数据表等定义类型的数据结构DML- Data Manipulation Language，对数据增删查改DQL- Data Query Language，查询DCL- Data Control Language，访问权限
DDL
数据库CRUD(create, retrieve, update, delete)
创建：
默认字符集为latin1：
create database db1; # 默认latin1字符集
指定字符集创建：
create database db1 character set utf8;
切换数据库：
use db1;
select database(); # 查询当前正在使用的数据库
查询数据库列表：
show databases;
MySQL自带数据库介绍：
| Database | Desc |
|---|---|
| information_schema | 信息数据库，保存其他数据库的信息(meta data) |
| mysql | MySQL数据库的核心数据库，保存用户和权限相关的信息 |
| performance_schema | 保存性能相关数据，用于监控MySQL的性能 |
| sys | 记录了DBA所需要的信息，比如哪个表访问最多，更加方便DBA快速了解数据库运行状况 |
修改：
修改字符集：
alter database db1 character set utf8;
show create database db1;
删除：
drop database db1; # 永久删除数据库
表CRUD
create table (
name type,
name type,
name type
);
数据类型：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SVTmHfVH-1624890267459)(mysql_data_types.jpg)]
表结构复制：
create table tname2 like tname1;
查看表结构：
desc tname;
查看所有数据表：
show tables;
查看DDL:
show create table tname;
删除：
drop table tname;
drop table if exists tname;
修改：
rename table tname_old to tname_new;
alter table tname character set gkb;
alter table tname add fname type;
alter table tname add (
fname type,
fname type
);
alter table tname modify fname new_type;
alter table tname change fname_old fname_new type;
alter table tname drop fname;
约束(constraints)
-
primary key- 主键，唯一 + 非空-
create table tname ( fname1 type primary key, fname2 type ) -
create table tname ( fname1 type, fname2 type, primary key(fname1) ) -
create table tname ( fname1 type, fname2 type ); alter table tname add primary key (fname1); -
删除主键
alter table tname drop primary key; -
自增主键（必须是整数）-
auto_increment，create table tname ( fname1 int primary key auto_increment, fname2 type )auto_increment=100;
Notes:
delete- 逐条删除，自增主键不变，继续前面的值自增truncate- 删除表，再创建，自增主键重置，从1开始，不管是否创建表时是否指定起始值 -
-
unique- 唯一，NULL不做非空判断 -
not null- 非空 -
foreign key- 外键create table tname ( fname1 type, fname2 type， constraint fk_name foreign key(fname[, ...]) references tname_master(fname[, ...]) ); alter table tname add constraint fk_name foreign key (fname[, ...]) references tname_master(fname[, ...]);
视图
由查询结果创建的虚拟表。针对使用频繁的查询，尤其是复杂的查询，创建视图，节省查询时间，降低查询复杂度。
create view vname as select statement;
DML
插入：
insert into tname(fname1, fname2[,...])
values(val1, val2[,...])
[,(val1, val2[,...])]
;
更新：
update tname
set fname1 = val1
[,fname2 = val2]
[where condition]
;
删除：
delete from tname
[where condition]
;
truncate table tname; // 删除整张表，然后创建一张相同的表
事务(transaction)
- 原子性(atomicity)
- 一致性(Consistency)
- 隔离性(Isolation)
- 持久性(Durability)
the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.
-
脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
-
不可重复读(Non-repeatable read)：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
-
幻读(Phantom Read)：在一个事务的两次查询中数据条数不一致，例如有一个事务查询了几行(Row)数据，而另一个事务却在此时插入了新的几行数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
| 隔离级别 | 脏读(Drity Read) | 不可重复读(Non-repeatable read) | 幻读(Phantom Read) |
|---|---|---|---|
| READ UNCOMMITTED | Yes | Yes | Yes |
| READ COMMITTED | No | Yes | Yes |
| REPEATABLE READ | No | No | Yes |
| SERIALIZABLE | No | No | No |
-
READ UNCOMMITTED- 读取未提交内容在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
-
READ COMMITTED- 读取提交内容这是大多数数据库系统的默认隔离级别（但不是MySQL默认的，Oracle默认）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
-
REPEATABLE READ- 可重读这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
-
SERIALIZABLE- 可串行化这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
查看和设置隔离级别：
select @@tx_isolation;
set global transaction isolation level [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]; # 新会话生效
DQL
查询：
select *
select fname1, fname2[, ...]
from tname
inner|left [outer]|right [outer] join
where condition # MySQL支持&&, ||
group by # 没有聚合函数时，返回的每组的第一条数据，select字段必须是group by的字段+聚合函数
having
order by
;
聚合函数（aggregation function)
COUNT- 忽略NULL，所以尽量不选择包含NULL值字段进行COUNTSUM- 求和MAX- 求最大值MIN- 求最小值AVG- 求平均值
限制返回条数：limit n，limit offset, n。分页公式：begin = (page_number - 1) * page_size
Notes:
where和having，where在分组前过滤，不能使用聚合函数，having在分组后，可以使用聚合函数
3. 数据库规整化（Database Normalization）
创建冗余少，结构合理的数据表结构，原始数据到表的设计过程
3.1 1NF - 第一范式(First Normal Form)
3.2 2NF - 第二范式(Second Normal Form)
- 满足第一范式(The table should be in the First Normal Form)
- 消除数据部分依赖(There should be no Partial Dependency)
3.3 3NF - 第三范式(Third Normal Form)
- 满足第一范式(The table should be in the First Normal Form)
- 满足第二范式(The table should be in the First Normal Form)
3.4 反三范式
冗余存储，用空间换时间，提升查询速度。尽量遵循三范式，合理增加冗余字段来提升查询性能。
5. 索引
表对应的索引被保存在索引文件中，进行DML操作时，需要更新索引，会消耗计算机资源，所以需要按需合理增加索引，否则会导致DML操作性能过低。
5.1 索引的创建、删除
- 主键索引(primary key index)
- 唯一索引(unique index)
- 普通索引(index)
# 创建索引
create [unique] index idx_name on tname(fname[(length)]);
alter table tname add [unique] index idx_name (fname[(length)]);
# 删除索引
alter table tname drop index idx_name;
5.2 索引优缺点
优点：
- 大幅提高查询速度
- 减少查询中分组和排序时间
- 通过唯一索引保证数据唯一性
缺点：
- 创建、维护索引需要时间，需要消耗计算资源，数据量越大，时间越长
- 表中的数据进行
DML时，也需要更新、维护索引 - 索引文件会占用存储空间
所以不能滥用索引，随意给自动添加索引，甚至给所有字段添加索引，优先选择经常出现在查询条件，排序，分组的字段创建索引。
6. 存储过程(Stored Procedure)
优点：
- 调试完成，就可以稳定运行，业务需求稳定的场景
- 减少业务系统与数据库的交互，减轻数据库服务器压力
缺点：
-
互联网项目中，很少使用存储过程，因为业务需求变化快
-
存储过程移植非常困难
-
阿里巴巴代码规范里也禁止使用存储过程，存储过程维护起来麻烦
7.【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性
6.1 存储过程的创建
# 1
delimiter $$
create procedure pname(in a varchar, out b int)
begin
-- SQL statements
end $$
7. 触发器
8. JDBC
Java定义的访问数据库的接口，各大厂商实现具体的接口，提供JDBC Driver。
- 加载
JDBC Driver - 获取
Connection - 获取
Statement,PreparedStatement（Statement的子接口） - 执行获取结果，
DML返回受影响行数，DQL返回ResultSet
直接写一个访问MySQL的工具类作为案例：
/** 定义访问接口
IDatabase.java
*/
public interface IDatabase {
int insert(String sql, Object ...parameters);
int update(String sql, Object ...parameters);
int delete(String sql, Object ...parameters);
List<HashMap<String, Object>> query(String sql, Object ...parameters)
}
/** 实现IDatabase，MySQL版本
*/
public class MySQLDB implements IDatabase {
public MySQLDB(String url, String user, String password) {
Connection connection = DriverManager.getConnection(url, user, password);
}
@Override
public int insert(String sql, Object ...parameters);
@Override
public int update(String sql, Object ...parameters);
@Override
public int delete(String sql, Object ...parameters);
@Override
public List<HashMap<String, Object>> query(String sql, Object ...parameters) {}
}
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
