Mysql 基础篇
简介
数据库（Database） 简称 **DB，**MySQL是一个开源、免费的数据库，由瑞典 MySQL AB 公司开发，后来Sun公司收购了MySQL，而Sun公司又被Oracle收购，被Oracle收购后，从Mysql 8+开始收费。
应用场景：数据库对大量的数据进行储存和管理的高效解决方案，例如电商网站
# 常见数据库
# MySQL目录结构
- bin目录：用于放置一些可执行文件，如mysql.exe、mysqld.exe、mysqlshow.exe等。
- data目录： 用于放置一些日志文件以及数据库。
- include目录：用于放置一些头文件，如：mysql.h、mysql_ername.h等。
- lib目录：用于放置一系列库文件。
- share目录： 用于存放字符集、语言等信息。
# MySQL相关概念
数据库、表、数据的关系
- MySQL服务器中可以创建多个数据库
- 每个数据库中可以包含多张表
- 每个表中可以存储多条数据记录
- 客户端通过数据库管理系统来操作MySQL数据库
DDL(Data Definition Language) 数据定义语言，用来定义数据库对象：数据库，表，列等
DML(Data Manipulation Language) 数据操作语言，用来对数据库中表的数据进行增删改
DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录(数据)
DCL(Data Control Language) 数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户
关系型数据库
关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的二维表组成的数据库
SQL的介绍
SQL(Structured Query Language)：结构化查询语言，一门操作关系型数据库的编程语言。
定义操作所有关系型数据库的统一标准。每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”。
通用语法规则
1.SQL 语句可以单行或多行书写，以分号结尾
2.MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
3.单行注释：-- 注释内容【推荐】 #注释内容(MySQL特有)
4.多行注释：/* 注释内容 */
# MySQL常见数据类型
char与varchar的区别
- varchar(10)：占用空间可变，当存入"aa"时占用2个字符空间，节省空间，效率略低
- char(10)：占用空间不可变，无论存入多少字符占用空间都是10，空间占用高，效率略高
date：使用java里sql包下的Date来接收
datetime：使用java中的Timestamp类来接收
tinyint：当只存放两个数时，如0、1，使用java中的Boolean类接收
# DOS命令行登录MySQL
登录格式1：登录本机的MySQL
mysql -u用户名-p密码
示例:mysql -uroot -proot
登录格式2：登录远程的MySQL
mysql -u用户名-p密码–h远程主机ip
示例:mysql -uroot -proot -h127.0.0.1
退出MySQL：exit或quit
保密登录MySQL
mysql -u用户名-p
示例：mysql -uroot -p
# DDL操作数据库
简介
数据库模式定义语言DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等
主要由create（添加）、alter（修改）、drop（删除表）和 truncate（删除表数据） 四个关键字完成。
DDL语句
| 语句 | 说明 |
|---|---|
| CREATE DATABASE **IF NOT EXISTS **数据库名 | 若数据库不存在则创建 |
| CREATE DATABASE 数据库名 CHARACTER SET utf8; | 建立一个数据库并制定编码格式 |
| **CREATE TABLE **表名 (字段名 数据类型, 字段名 数据类型); | 创建表 |
| **DROP TABLE **表名; | 删除表 |
| SHOW TABLES; | 查询所有的表 |
| **DESC **表名; | 查询表结构 |
| SHOW FULL COLUMNS FROM 表名; | 查询表全部结构 |
| **ALTER TABLE **表名 **RENAME TO **新表名; | 修改表名 |
| **ALTER TABLE **表名 **ADD **字段名 数据类型; | 单独添加一个字段 |
| **ALTER TABLE **表名 **MODIFY **字段名 新数据类型; | 修改某字段的数据类型 |
| **ALTER TABLE **表名 **CHANGE **字段名 新字段名 新数据类型; | 修改字段名和数据类型 |
| **ALTER TABLE **表名 **DROP **字段名; | 删除某一字段 |
# DML操作表数据
DML(Data Manipulation Language) 数据操作语言，用来对数据库中表的数据进行增删改
主要由Insert（添加）、Update（修改）、Delete（删除）和 Select（选择） 四个关键字完成。
DML语句
| 语句 | 说明 |
|---|---|
| **INSERT INTO **表名(字段名1, 字段名2, …) VALUES (值1, 值2, …); | 给指定列添加数据 |
| **INSERT INTO **表名 VALUES (值1, 值2, …); | 给全部列添加数据（一次一条数据） |
| **INSERT INTO **表名 VALUES (值1, 值2, …), (值1, 值2, …), (值1, 值2, …); | 批量添加数据(一次加入多条数据) |
| **UPDATE **表名 **SET **字段名=新的值,... **WHERE **条件; | 修改表中的数据 |
| **DELETE FROM **表名 **WHERE **条件; | 删除表中的数据 |
# DQL操作数据
DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录(数据)，关键字只有Select（查询）
DQL语句
| 语句 | 说明 |
|---|---|
| **SELECT **字段名1, 字段名2 **FROM **表名; | 查询指定字段的数据 |
| SELECT * **FROM **表名; | 查询所有字段的数据 |
| **SELECT DISTINCT **字段名1 **FROM **表名; | 去除重复查询 |
| **SELECT **字段名1 (+ - * /) 字段名2 **FROM **表名; | 计算列的值(四则运算) |
| **SELECT **字段名1 **AS **别名1, 字段名2 **AS **别名2 **FROM **表名; | 起别名查询 |
| **SELECT **字段名 **FROM **表名 **WHERE **条件; | 条件查询 |
| **SELECT **字段名 **FROM **表名 **ORDER BY **列名 排序方式; | 排序查询（升序ASC 降序DESC） |
| SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串'; | 模糊查询 |
通配符：
**%：表示多个字符
_：**表示一个字符
'米%'：米字开头
'%米%'：包含米字
'_米%'：第二个为米字
'_____'：四个字的 |
| **SELECT **聚合函数(字段名) **FROM **表名; | 聚合函数查询（纵向查询）
聚合函数：
COUNT： 统计指定列记录数
SUM： 计算指定列的数值和
MAX： 计算指定列的最大值
MIN： 计算指定列的最小值
AVG： 计算指定列的平均值 average
NOW：当前系统时间
控制小数位数
ROUND(AVG(字段),小数位数)
**注意：**非数值类型结果都是0 |
| SELECT * **FROM **表名 **GROUP BY **字段名 HAVING 条件; | 分组查询
**注意：**分组后的条件过滤要使用HAVING
where是分组前过滤，having是分组后过滤
where后不能用聚合函数，having后可以 |
| **SELECT *** **FROM **表名 **LIMIT **偏移量（跳过）, 总记录数; | 分页查询
分页查询 limit 是MySQL数据库的方言
Oracle 分页查询使用 rownumber
SQL Server分页查询使用 top
分页计算公式：
每页的偏移量 = (当前页码-1)*每页显示条数
**注意：**偏移量默认为0可以省略 |
条件运算符
| 符号 | 说明 |
|---|---|
| <>或!= | 不等于 |
| AND 或 && | 并且 |
| OR 或 | | | 或者 |
| NOT 或 ! | 非，不是 |
| BETWEEN...AND... | 在某个范围之内（都包括） |
| IN(...) | 是其中一个就行 |
| IS NULL | 是NULL |
| IS NOT NULL | 不是NULL |
**扩展：查询的七个关键字顺序 **
SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT
# 函数
| 函数名 | 效果 | 示例 |
|---|---|---|
| ROUND(column_name,decimals) - column_name: 要舍入的字段，必需 - decimals: 规定要返回的小数位数，可选 | 用于把数值字段舍入为指定的小数位数 | SELECT ROUND(column_name,decimals) FROM [表名]; |
| TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) - unit： - SECOND：秒 - MINUTE：分钟 - HOUR：小时 - DAY：天 - WEEK：星期 - MONTH：月 - QUARTER：季度 - YEAR：年 - datetime_expr1：要比较的日期1 - datetime_expr2:：要比较的日期2 | 用于计算两个日期的时间差 | -- 相差：25秒SELECT TIMESTAMPDIFF(SECOND,'2022-04-23 14:57:00','2022-04-23 14:57:25'); |
| DATE（date） | 提取日期或日期/时间表达式的日期部分 | SELECT id,date('2022-04-23 14:57:25'); => 1, 2022-04-23 |
| DATE_SUB(date,INTERVAL expr (opens new window) unit) - date：日期 - expr：数值 - unit： - SECOND：秒 - MINUTE：分钟 - HOUR：小时 - DAY：天 - WEEK：星期 - MONTH：月 - QUARTER：季度 - YEAR：年 | 从日期减去指定的时间间隔 | -- 减一小时select date_sub(@dt, interval 1 hour ); |
| OVER() | 窗口函数 | 详细可看： MySQL窗口函数OVER()_mysql over_奋斗的龙猫的博客-CSDN博客 (opens new window) |
# 多表查询
员工表有6条记录，部门表有4条记录，多表查询后查出来的记录数会多达24条，原因就是员工表的一条记录结合了部门表的四条记录，所以最终查询出来的记录数就是4*6=24。
这种多表数据的结合效果被称为笛卡尔乘积，多表连接查询后，并不是所有数据都是有用的，为了过滤掉没用的数据，我们需要添加表连接条件，例：员工表.id = 部门表.id
| 语句 | 说明 |
|---|---|
| **SELECT **字段名 **FROM **表1，表2 ... WHERE 条件; | 隐式内连接 |
没有**JOIN**关键字，条件使用**WHERE**指定 |
| SELECT 字段列表 **FROM **表1 JOIN 表2 ON 条件; | 显式内连接
使用INNER JOIN ... ON语句, 可以省略INNER |
| **SELECT **字段名 **FROM **表1 LEFT JOIN 表2 ON 条件; | 左外连接
**LEFT OUTER JOIN **... ON，
OUTER可以省略
显示左表全部数据，显示右表符合条件的数据 |
| **SELECT **字段名 **FROM **表1 RIGHT JOIN 表2 ON 条件; | 右外查询
**RIGHT OUTER JOIN **... ON，
OUTER可以省略
显示右表全部数据，显示左表符合条件的数据 |
| **SELECT **查询字段 **FROM **表 **WHERE **字段=(子查询); | 单行单列结果子查询 |
| **SELECT **查询字段 **FROM **表 **WHERE **字段 IN (子查询); | 多行单列结果子查询
父查询使用IN/ANY/ALL运算符
IN和ANY语义一致，都是任一符合返回true
ALL是完全一致返回true，如...AND...AND... |
| **SELECT **查询字段 FROM (子查询) 表别名 **WHERE **条件; | 多行多列结果子查询 |
**注意：**子查询建议把每一部分写出来，检查查询结果，再合并到一起
查询步骤
- 明确自己要查哪些表
- 明确表连接条件去掉笛卡尔积
- 后续的查询
# 约束
什么是约束？对表中的数据进行限定，保证数据的正确性、有效性、完整性
| 约束 | 说明 |
|---|---|
| PRIMARY KEY | 主键约束 |
唯一不重复、不能包含NULL
AUTO_INCREMENT 自增
AUTO_INCREMENT = 100 从100开始自增
复合主键（多用于主表定义外键）
PRIMARY KEY(外键1，外键2)
注意：主键的自增长会从曾经出现过的最大值开始+1 |
| UNIQUE | 唯一约束（唯一不重复） |
| NOT NULL | 非空约束（不能为NULL） |
| DEFAULT | 默认值约束
设置默认值：DEFAULT 值 |
| FOREIGN KEY | 外键约束
主键所在的表叫主表，外键所在的表是从表
规范（每个公司可能不同）：
外键约束名一般以fk开头，如：fk_dep_id |
| CHECK | 检查约束 |
**注意：**MySQL不支持检查索引
添加约束:**CREATE TABLE** 表名(字段名 字段类型 **约束**);**ALTER TABLE** 表名 **ADD 约束** (字段名);
添加外键约束：**CONSTRAINT **外键约束名 **FOREIGN KEY **(外键字段名) **REFERENCES **主表(主键字段名);
- CONSTRAINT: 表示约束外键约束名： 给外键约束取个名字,将来通过约束名可以删除这个约束
- FOREIGN KEY(外键字段名): 指定某个字段作为外键
- REFERENCES主表(主键字段名) : 引用主表的主键的值
删除约束：**ALTER TABLE** 表名 **DROP 约束 **约束名;
# 数据库设计
概念
数据库设计就是根据业务系统的具体需求，结合所选用的DBMS，为这个业务系统构造出最优的数据存储模型。建立数据库中的表结构以及表与表之间的关联关系的过程。
数据库设计步骤
- 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）
- 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
- 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
- 维护设计（1.对新的需求进行建表；2.表优化）
# 多表关系
一对多(多对一)：
- 如：部门表 和 员工表
- 一个部门对应多个员工，一个员工对应一个部门
**实现方式：**在多的一方建立外键，指向一的一方的主键
多对多：
- 如：订单 和 商品
- 一个商品对应多个订单，一个订单包含多个商品
**实现方式：**建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一：
- 如：用户 和 用户详情
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
实现方式：
- 在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)
- 从表的主键又是外键
# 事务
概念
数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令，事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败，事务是一个不可分割的工作逻辑单元。
事务的四大特性（ACID）
| 事务特性 | 含义 |
|---|---|
| 原子性（Atomicity） | 事务是不可分割的最小操作单位，要么同时成功，要么同时失败。 |
| 一致性（Consistency） | 事务前后数据的完整性必须保持一致。 |
| 隔离性（Isolation） | 是指多个事务并发访问数据库时，一个事务不能被其它的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。 |
| 持久性（Durability） | 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。 |
事务相关SQL语句
| SQL语句 | 描述 |
|---|---|
| START TRANSACTION; BEGIN; | 开启事务 |
| COMMIT; | 提交事务 |
| ROLLBACK; | 回滚事务 |
| SET AUTOCOMMIT = 1; | 自动提交事务(0为关闭) |
| select @@autocommit; | 查看是否开启自动提交 |
# 数据库的备份与还原
可以使用命令方式备份还原,也可以通过一些客户端进行备份还原
备份命令: 在cmd窗口中运行命令
mysqldump -u用户名 -p密码 需要备份的数据库名称>本地磁盘sql文件的路径
例如:mysqldump -uroot -p1234 reggie>e:/backup.sql
2
3
还原命令: 先登陆目标数据库再去执行命令
source 本地磁盘sql文件的路径
例如: source e:/backup.sql 创建reggie_166数据库,将数据还原这个数据库中
2
3
# docker 安装mysql
docker 安装 Mysql 5.7_疯狂的狮子Li的博客-CSDN博客_docker安装mysql5.7 (opens new window)
mkdir -p /usr/soft/docker-data/mysql/data /usr/soft/docker-data/mysql/logs /usr/soft/docker-data/mysql/conf
cd /usr/local/docker/mysql8.1/conf \
touch my.cnf
2
docker run \
-p 3306:3306 \
--name mysql5.7 \
--privileged=true \
--restart=always \
-v /usr/local/docker/mysql57/conf:/etc/mysql/conf.d \
-v /usr/local/docker/mysql57/logs:/var/log/mysql \
-v /usr/local/docker/mysql57/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7
2
3
4
5
6
7
8
9
10
-d: 后台运行容器 **-p **将容器的端口映射到本机的端口 **-v **将主机目录挂载到容器的目录 **-e **设置参数
docker run \
-p 3306:3306 \
--name mysql8 \
--privileged=true \
--restart=always \
-v /usr/local/docker/mysql/conf:/etc/mysql/conf.d \
-v /usr/local/docker/mysql/logs:/var/log/mysql \
-v /usr/local/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:8.0
2
3
4
5
6
7
8
9
10