MYSQL
🌕 : 必看
🌓 : 可忽略
🌒 :了解
基本配置 🌓
安装 Mysql
可参考：mysql安装
不同的版本可能有不同。这里仅做参考
镜像地址 ： https://dev.mysql.com/downloads/mysql/5.7.html
找到 mysql-5.7.35-winx64.msi 下载即可
⚠️ 注意事项:
- 端口号:
端口号 port 是任何一个软件/应用都会有的，端口号是应用的唯一代表。
端口号通常和 IP 地址在一块，IP 地址用来定位计算机的，端口号 port
在同一台计算机上，端口号不能重复。具有唯一性。
mysql 数据库启动的时候，这个服务占有的默认端口号是 3306 - 字符编码方式:
设置mysq1数据库的字符编码方式为 UTF8 一定要注意:先选中第3个单选按钮，然后再选择 utf8 字符集。 - 选择自动配置换变量
如果没有选择怎么办?你可以手动配置 path-其它路径;C: \Program Files (x86) \MySQL\MySQL Server 5. 5\bin - 设置超级管理员密码: root 用户密码。
完美卸载Mysql
- 点击 mysql 安装包,选择 remove
- 删除目录
- C:\Program Files (x86)\Mysql
- C:\ProgramData\MySQL
查看Mysql服务
- 计算机->右键->管理->服务和应用程序->服务->mysql （win 11 在搜索中搜索 ‘服务’ 即可）
- 点击 mysql 服务-> 右键设置启动方式(默认自动)
命令控制 mysql 服务
- net stop 服务名称
- net start 服务名称
其他服务也可以使用。
登录Mysql
提示：可以使用下面的 命令行窗口，也可以使用 数据库可视化管理工具(mysql 8 自带)。
⚠️ 注意：如果没有配置全局变量，那么需要到安装目录的 bin 文件下执行文件
- mysql -uroot -p密码
- mysql -uroot -p 第二行填写密码
- exit 退出
常用命令
- show databases; ：显示数据库
- use database; ：使用数据库
- create database dbname; : 创建数据库
- show tables; : 显示当前数据库下的表
⚠️ 注意 ：sql 以英文分号结尾(😉,才能执行。
基础知识
使用的数据库，在cmd 的mysql 中执行 source sql文件，即可导入。出现权限不足将文件copy 到安装的bin目录下即可。
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
`id` int(11) NOT NULL,
`sname` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sage` int(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`ssex` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES (1, '老痞', 24, '男');
INSERT INTO `stu` VALUES (2, '卡卡西', 40, '男');
INSERT INTO `stu` VALUES (3, '佐助', 24, '男');
INSERT INTO `stu` VALUES (4, '小樱', 24, '女');
INSERT INTO `stu` VALUES (5, '迈特凯', 40, '男');
INSERT INTO `stu` VALUES (6, '纲手', 60, '女');
INSERT INTO `stu` VALUES (7, '自来也', 60, '男');
SET FOREIGN_KEY_CHECKS = 1
基本操作 🌒
- desc table; 查看表结构
- show create dabase database; 查看数据库编码。
- show variables like ‘%char%’; 查看数据库详细编码。
- set character=编码; 设置指定编码。
- select * from table; 查看表内容。
- select version(); 查看 mysql 版本号。
- select database(); 查看当前使用数据库。
1. SQL 分类 🌕
sql 语句是通用的 ，并且不区分大小写。
TCL：事务控制语言（Transaction Control Language）
DDL：数据定义语言 (Data Definition Language, DDL)
DML：数据操纵语言（Data Manipulation Language, DML）
DQL：数据查询语言（Data Query Language, DQL）
| DQL | 数据查询语言(凡是带有select关键字的都是查询语句) select. . . |
|---|---|
| DDL | 数据定义语言 凡是带有create、drop、 alter的都是DDL。 create:新建，等同于增 drop:删除 alter:修改 这个主要是对表结构进行操作。 |
| DML | 数据操作语言(凡是对表当中的数据进行增删改的都是DML) insert delete update insert增 delete删 update改 这个主要是操作表中的数据data. |
| TCL | 事务控制语言 事务提交: commit; 事务回滚: rollback;… |
| DCL | 数据控制语言 例如:授权grant、 撤销权限revoke. . . |
2. DQL 🌕
简单查询
-- 1.简单查询 查询多个字段用逗号,隔开
select sname from stu;
-- 2.查询所有
-- 缺点:
-- 1.效率低
-- 2.可读性差
select * from 表名;
-- 3.字段别名:可以不用 as,使用空格隔开，别名不推荐空格。如果有需要用 '' 包裹特殊名。不推荐中文
select newname as sname from stu;
-- 4.字段可以使用数学表达式，注意数据类型
select sage * 10 from stu;
-- 5.条件查询 :
select * from stu where sage = 24;
select * from stu where sage is null;
select * from stu where sage between 18 and 20;
select * from stu where sage in 18 and 20;
select * from stu where sname like "%L";
select * from stu where sname like "_L";
-- 6. 排序查询
-- 单个字段排序
select * from stu order by ssage desc;
-- 多个字段排序，ssage 主导作用，ssage 相同时会根据 sname 排序;
select * from stu order by ssage desc,sname asc;
-- 7.分组查询 : 使用了 group by 后 select 后的字段只能是 分组的字段
select sage from stu group by sage;
-- 8.去重 distinct :只能出现在字段前方，但是可以使用函数，所有字段联合起来去重
select distinct sage,sname from stu;
补充
–5.条件查询
- = , > ,< ,>= ,<=
- and , or , not
- is null ,is not null
- between … and … (表示区间值等价于 >= and <= ,左小右大)
- in (相当于多个 or,但是不代表区间，需要具体的值)
- like (模糊查询 :某个字段包含。% 代表多个字符,_ 代表一个字符，查询特殊字符需要转义)。
- order by ( 升序 asc(默认),降序 desc)
- gruopy by 分组
- having 对分组后的数据过滤
⚠️ 注意： and 和 or 同时出现的时候,存在优先级问题。and > or
-- 查询语句执行顺序
where
gruop by
having
order by
limit
数据处理函数
- 单行处理函数
- 多行处理函数
常见的单行处理函数:
- lower 转换小写
- upper 转换大写
- substr 取子串
- length 取长度
- trim 去空格
- str_to_data 字符串转为日期
- date_format 格式化日期
- format 设置千分位
- round 四舍五入
- rand() 生成随机数
- ifnull 将null 转为一个具体值
- concat 拼接
- case when then when then else end;类似于if else
- date_to_str 日期转换指定格式字符串
- str_to_date 字符串转为日期
- date_format 日期转为指定格式字符串,常用于显示。
- now() 获取当前系统时间
常见的多行处理函数(分组函数):
- count 计数
- sum 求和
- avg 平均值
- max 最大值
- min 最小值
⚠️ 注意：分组函数使用前必须分组，如果没有分组那么整张表为一组。会自动忽略 null ，一些如果需要统计的一定注意判断空值问题。
连表查询
顾名思义: 表与表之间根据一定的条件连接查询。
表连接分类
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左连接
- 右连接
- 全连接
内连接
-
等值连接
-- sql 92 select * from table1,table2 where table1.id = table2.id; -- sql 99 :将where 条件分开，结构更加清晰 select * from table1 t1 join table2 t2 on t1.id = t2.id; -
非等值连接
-- 条件不是一个等量关系 select t1.age,t1.name from table1 t1 join table2 t2 on t1.age between t2.losal and t2.hisal; -
自连接
-- 自己连接自己 ，比如员工表中的某个人是某个人的领导，阶梯制 -- id 标识符 ，name 姓名，mgr 领导id select t1.id;t1.name ,t2.name from table1 t1 join table1 t2 on t1.mgr = t2.id;
特点：完全匹配才能显示，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。
外连接
表与表组件有主次关系，主表的所有数据都会显示。
-
左连接
-- 左边的表为主表 select * from table1 t1 left join table2 t2 on t1.id = t2.id -
右连接
-- 右边的表为主表 select * from table1 t1 right join table2 t2 on t1.id = t2.id
子查询
什么是子查询？就是 select 语句中嵌套 select 语句。
可以出现在哪些地方？select 后 from 后 where 后。
注意：子查询需要用() 包裹。子查询的查询结果可以当做一张临时表,这个临时表需要起别名。
-- where 后的子查询
select * from stu
where
sage > (select min(sage) from stu) order by sage limit 0, 1
-- from 后面的子查询
select * from (select * from stu where sage between 18 and 40) as newstu
where sname = "卡卡西";
-- select 后子查询:该子查询只允许返回一个结果
select (select max(sage) from stu) from stu;
合并查询
-- 查询24岁和60岁
-- or
select * from stu where sage = 24 or sage = 60;
-- in
select * from stu where sage in (24,60);
-- union 更加高效
--- union 对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
--- union all 对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。
---- 使用的条件：结果集列相同，数据类型相同
select * from stu where sage = 24
union
select * from stu where sage = 60;
select * from stu where sage = 24
union all
select * from stu where sage = 60;
分页查询
将数据库的部分数据取出。
-- limit 使用
--- 查询一行
select * from stu order by sage limit 1 ;
--- limit 区间查找 limit (起始位置，长度)
select * from stu order by sage limit 3,5;
通用分页:
找规律：
每页10条数据，有 n 页。
第 1 页: limit 0,10
第 2 页: limit 10,10
第 3 页: limit 20,10
第 4 页: limit 30,10
…
得出如下:
-- 通用分页
--- 数据条数 pageSize
--- 页数 pageNo
select * from stu
limit (pageNo - 1 ) * pageSize;
总结
执行顺序：
- from
- where
- group by
- having
- select
- order by
- limit
3.DDL
1. 表的创建 (create)🌕
语法:
-- 语法 ：在同一个数据库下，表名系统创建会报错
create table [if not exists] 表名 (
字段1 数据类型 [约束],
字段2 数据类型 [约束],
字段3 数据类型 [约束],
字段4 数据类型 [约束]
);
-- 实例
create table `stu` (
id int(11) PRIMARY KEY,
sname varchar(16),
sage int(11),
ssex varchar(16)
)
-- 将查询结果插入一张表
create table usertable as select * from stu [where sage > 30];
常见数据类型
| 数据类型 | 描述 |
|---|---|
| varchar | 可变长度字符串，根据实际数据长度分配内存。效率低，节省空间。最长255 |
| char | 固定长度。效率高，占用内存。最长255 |
| int | 整数类型，最长11 |
| bigint | 长整数类型 |
| float | 单精度浮点数 |
| double | 双精度浮点数 |
| date | 短日期类型，只包括年月日 |
| datetime | 长日期类型，包括年月日时分秒 |
| clob | 字符串大对象。超过 255 字符需要使用 clob |
| blob | 二进制大对象。专门存储图片，音频，视屏。不能直接插入数据，需要使用 IO 流。 |
约束 🌕
什么是约束？
- 对字段进行规范，确保表中的数据有效。
什么时候使用？
- 创建表的时候。
约束包括哪些?
- 非空约束: not null
- 唯一性约束: unique,可以多个字段联合唯一
- 主键约束: primary key ( 简称pk)
- 外键约束: foreign key(filed) references table(filed)( 简称FK)
- 检查约束: check ( mysq1不支持，oracle支持 )
索引🌕
什么是索引?
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
什么时候去添加索引?
- 数据量庞大。
- 该字段经常出现在 where 后面。
- 该字段很少 DML 操作，因为DML 后，索引需要重新排列。
注意: 不要随意去添加索引，因为索引需要维护，太多反而降低系统性能。建议通过主键查询，通过 nuique 约束的字段进行查询，效率是比较高的。
索引如何创建/删除?
-- 创建
--- 表已经创好的情况下
create index 索引名 on 表名(字段名);
--- 创建表时添加索引
create table stu_user(
字段名 varchar(12),
index 索引名(字段名)
);
-- 删除
drop index 索引名 on 表名;
-- 如何检查一条语句是否使用了索引，列中的 type 类型为 ref 即为使用，为 all 即为未使用
explain select....
-- 查看一个表的索引
show keys from 表名;
索引百分百生效么？
当然不是，索引有失效的情况下。比如：
select * from stu where sname like '%西';
索引只有一种么?
索引有：
- 单一索引:单个字段。
- 复合索引:多个字段。
- 主键索引: 主键上。
- 唯一索引:具有unique 约束的字段添加索引。
- …
注意: 唯一性比较弱的字段上添加索引用处不大。
视图
什么是视图?
view: 站在不同的角度去看待同一份数据。
视图的创建/删除
-- 创建
create view 视图名 as DQL语句;
-- 删除
drop view 视图名
视图能干嘛?
增删改查(操作和对表的操作一致),对视图的操作会直接影响原表数据。可以简化 SQL ,将一些复制的 SQL 作为视图对象进行操作。
2. 表的删除 (drop)
emm不太常用，但是得会。
-- 如果表不存在，就会报错
drop table 表名;
-- 加入 if exists ,判断表是否存在
drop table if exists 表名
-- 删除表内容，不删除表结构，不可回滚，效率高
truncate table 表名;
3.alter 修改表结构
开发很少使用，成本比较高。
(https://www.likecs.com/show-803506.html)
4. DML 🌕
1. 数据的添加 (insert)
insert into 表名(字段名1，字段名2....)
values(值1，值2....);
⚠️注意:
- 字段名的顺序可以打乱也可以省略。省略的时候，添加数据顺序按照建表的字段顺序。
- 如果字段不写全，只添加部分字段，那么其他的值会为 null 。
思考? 有些字段是否需要默认值?需要如何添加？
-- 如果需要默认值那么需要在建表的时候去使用 default 添加默认值
create table `stu` (
id int(11) PRIMARY KEY,
sname varchar(16),
sage int(11),
ssex varchar(16) [default "男"]
)
日期类型的插入
-- 涉及函数
--- str_to_date : 字符串转为日期
--- date_to_str :日期转字符串
insert into stu(now_time) -- now_time 字段为 date 数据类型
values(str_to_date('2002-03-11','%Y-%m-%d'));
好消息，好消息:
-- 如果插入的日期格式为 %Y-%m-%d 那么不需要使用 str_to_date 函数
insert into stu(now_time)
values('2002-03-11');
date 与 datetime的区别？
date: 短日期，包括年月日，默认格式 %Y-%m-%d
datetime:长日期，包括年月日时分秒，默认格式 %Y-%m-%d %h:%i:%s
批量插入数据
insert into stu
values
(1, '老痞', 24, '男'),
(2, '卡卡西', 40, '男'),
(3, '佐助', 24, '男'),
(4, '小樱', 24, '女'),
(5, '迈特凯', 40, '男'),
(6, '纲手', 60, '女'),
(7, '自来也', 60, '男');
2. 数据的修改(update)
update 表名 set 字段1 = 值 ,字段2 = 值 ....
where 条件;
⚠️注意: 如果没有条件则会全部修改。
3.数据的删除
-- 删除内容，可回滚，效率低.如果有外键约束，那么需要先删除引用的数据，再删除当前数据
delete from 表名 [where 条件]
5. TCL 🌕🌕
什么是事务？
- 事务就是一个完整的业务逻辑。来确保数据安全。
什么又是业务逻辑？
比如:假设转账，从 a 账号转到 b 账号 1000 ，其业务逻辑如下
1.A 账号减少 1000
2.B 账号加上 1000
这段业务逻辑要么同时成功，要么同时失败，不然数据会混乱。
🤔 思考? 哪些和事务有关?为什么呢？
- insert
- update
- delete
因为就以上三个 DML 语句与数据操作相关。
🤔思考?是否所有的事务只需要一套 DML 语句即可完成？
当然不是上面举例的业务逻辑，也是需要两条 DML 语句来完成的。
🤔思考? 什么情况下使用事务?
使用场景: 当业务需要使用到多条 DML 语句时，事务就起到了关键的作用。来保证多条 DML 同时成功或同时失败。
事务的使用 🌕
- 开启事务 start transaction;
- 执行 DML 语句
- 提交事务/回滚事务 commit/rollback
⚠️注意: mysql 默认自动提交事务， start transaction 也可以理解为关闭自动提交。提交事务后数据就完成了持久化，无法回滚到 start transaction 前。
事务的特性 🌕
A:原子性:说明事务是最小的工作单元。不可再分。
c:一致性:所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败,以保证数据的一致性。
I:隔离性
A事务和B事务之间具有一定的隔离.
教室A和教室B之间有一道墙，这道墙就是隔离性。
A事务在操作一张表的时候，另一个事务B也操作这张表会那样???
D:持久性:事务最终结束的一个保障。事务提交，就相当于将数据保存到硬盘上!
隔离级别
-- 4个级别
1. 读未提交 read uncommitted (最低的隔离级别，不会使用)
什么是读未提交?
事务A可以读取到事务B未提交的数据。
存在的问题就是: 脏读现象! (Dirty Read)
2. 读以提交 read committed
什么是读已提交?
事务A只能读取到事务B提交之后的数据。
这种隔离级别解决了什么问题?
解决了脏读。
这种隔离级别存在什么问题?
不可重复读取数据。
什么是不可重复读取数据呢?
在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条， 3不等于4，称为不可重复读取。
这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
oracle数据库默认的隔离级别是: read committed
3. 可重复读: repeatable read
什么可重复读?
事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交 了，事务 A 读取到的数据还是没有发生改变，这就是可重复读。
可重复读解决了什么问题?
解决了不可重复读取数据.
可重复读存在的问题是什么?
可以会出现幻影读。
每一次读取到的数据都是幻象。不够真实!
4.序列化/串行化: serializable (最高的隔离级别)
这是最高隔离级别，效率最低。解决了所有的问题。
这种隔离级别表示事务排队，不能并发!
6. DCL
就是使用 DBA 命令
扩展知识
存储引擎
什么是存储引擎，有什么用呢?
- 存储引擎是MySQL中特有的-一个术语，其它数据库中没有。(oracle中有， 但是不叫这个名字)
实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。
如何为表添加/指定存储引擎呢?
- 在创建表时指定
-- ENGINE = InnoDB : 指定存储引擎为innoDB，默认就是innoDB
-- CHARACTER SET = utf8 :指定字符集编码 utf8
CREATE TABLE stu (
.....
) 【ENGINE = InnoDB】 【CHARACTER SET = utf8】;
查看 mysql 支持哪些存储引擎？
【show engines \G;】
-- 我的mysql 存储引擎情况，不同版本有不同存储引擎支持的情况。
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
mysql 中常见存储引擎 🌒
- MYISAM
优点:
可被转换为压缩、只读表来节省空间。
缺点:
不支持事务,安全级别低。
管理表具有的特征:
使用三个文件表示每个表:
格式文件 - 存储表结构的定义(mytable.frm)
数据文件 - 存储表行的内容(mytable.MYD)
索引文件 - 存储表上索引(mytable.MYI):索引是一本书的目录
注意:对于一张表来说，只要是主键,或者加有 unique 约束的字段上会自动创建索引。
- InnoDB
mysql默认的存储引擎，属于重量级。
优点：
1.支持事务，支持数据库崩溃后自动恢复机制。
2.非常安全。
缺点:
1.不能压缩
2.效率不高
管理表具有的特征:
-每个 InnoDB 表在数据库目录中以 .frm 格式文件表示。
-InnoDB 表空间 tablespace 被用于存储表的内容。
-提供一组用来记录事务性活动的日志文件。
-用COMMIT (提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理。
-提供全 ACID 兼容。
-在 MySQL 服务器崩溃后提供自动恢复。
-多版本 (MVCC) 和行级锁定。
-支持外键及引用的完整性，包括级联删除和更新。
- MEMORY
特点：
1. 其数据存储在内存中
2. 行的长度固定
优点:
1.查询效率高
缺点:
1.不安全。
2.关机后数据丢失。
管理表具有的特征:
-在数据库目录内，每个表均以 .frm 格式的文件表示。
-表数据及索引被存储在内存中。
-表级锁机制。
-不能包含 TEXT 或 BLOB 字段。
DBA 命令
数据库设计规范(三范式)
- 第一范式(确保每列保持原子性)
- 第二范式(确保表中的每列都和主键相关)
- 第三范式(确保每列都和主键列直接相关,而不是间接相关)
注意:最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在sq1当中，表和表之间连接次数越多，效率越低。有的时候可能会存在几余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。
数据备份与恢复
mysql 中是通过 二进制日志的形式来保存数据的 。8.0 版本自动开启了备份日志 ， 5.0 的需要配置一下 。当然备份的方式有很多种，我使用日志的方式。
1. 查看是否开启日志
ON 开启
OFF 关闭
2. 修改 my.inf (linux 环境)
如果你不知道找个文件在哪可以使用 ： whereis my.inf
log-bin=bin
binlog_format=mixed
3.重启 Mysql
service mysql restart // 重启
service mysql stop // 停止
service mysql start // 启动
4. 查看日志列表
show master logs;
数据函数处理示例
待补充…
3793
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
