一、数据库相关概念
1、SQL的分类
1、DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等； 常用语句：CREATE、 ALTER、DROP
2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别； 常用语句：GRANT、DENY
4、DQL(Data Query Language) 数据查询语言，用来查询数据 常用语句：SELECT
2、MySQL数据库
MySQL 是最流行的关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。
MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
- MySQL 是开源的，目前隶属于 Oracle 旗下产品。
- MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL 使用标准的 SQL 数据语言形式。
- MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
- MySQL 对 PHP 有很好的支持，PHP 是很适合用于 Web 程序开发。
- MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
- MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
3、RDBMS 术语
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
- **行：**一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
- 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- **外键：**外键用于关联两个表。
- 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
- **索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
二、MySQL字段
数值
| 数据类型 | 描述 | 大小 |
|---|---|---|
| tinyint | 十分小的数据 | 1个字节 |
| smallint | 较小的数据 | 2个字节 |
| mediumint | 中等大小的数据 | 3个字节 |
| int | 标准的整数 | 4个字节 |
| bigint | 较大的数据 | 8个字节 |
| float | 浮点数 | 4个字节 |
| double | 浮点数 | 8个字节 |
| decimal | 字符串形式的浮点数，一般用于金融计算 |
字符串
| 数据类型 | 描述 | 大小 |
|---|---|---|
| char | 字符串固定大小 | 0~255 |
| varchar | 可变字符串 | 0~65535 |
| tinytext | 微型文本 | 2^8-1 |
| text | 文本串 | 2^16-1 |
时间日期
| 数据类型 | 描述 | 格式 |
|---|---|---|
| date | 日期格式 | YYYY-MM-DD |
| time | 时间格式 | HH：mm：ss |
| datetime | 最常用的时间格式 | YYYY-MM-DD HH：mm：ss |
| timestamp | 时间戳，1970.1.1到现在的毫秒数 | |
| year | 年份表示 |
null
- 没有值，未知
- 不要使用NULL值进行计算
UnSigned
- 无符号的
- 声明了该列不能为负数
ZEROFILL
- 0填充的
- 不足位数的用0来填充 , 如int(3),5则为005
Auto_InCrement
- 通常理解为自增，自动在上一条记录的基础上默认+1
- 通常用来设计唯一的主键，必须是整数类型
- 可定义起始值和步长
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
NULL 和 NOT NULL
- 默认为NULL , 即没有插入该列的数值
- 如果设置为NOT NULL , 则该列必须有值
DEFAULT
- 默认的
- 用于设置默认值
- 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
拓展：每一个表，都必须存在以下五个字段：
| 名称 | 描述 |
|---|---|
| id | 主键 |
| version | 乐观锁 |
| is_delete | 伪删除 |
| gmt_create | 创建时间 |
| gmt_update | 修改时间 |
三、数据库存储引擎
INNODB
- 默认使用，安全性高，支持事务的处理，多表多用户操作
MYISAM
- 早些年使用，节约空间，速度较快
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约为2倍 |
四、MySQL命令操作
1、MySQL连接
[root@host]# mysql -u root -p
Enter password:******
在登录成功后会出现 mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句。
2、操作数据库
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
2、删除数据库
DROP DATABASE [if EXISTS] 数据库名;
3、使用数据库
--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;
4、查看数据库
SHOW DATABASES;
5、修改数据库
修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]
修改字段 :
- ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
删除字段 : ALTER TABLE 表名 DROP 字段名
**删除表：**DROP TABLE [IF EXISTS] 表名
- IF EXISTS为可选 , 判断是否存在该数据表
- 如删除不存在的数据表会抛出错误
所有的创建和删除尽量加上判断，以免报错
3、数据管理
1、添加insert
语法：
INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];
注意：
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的，但是值必须完整且一一对应
- 可以同时插入多条数据，VALUES后面的值需要使用逗号隔开
2、删除delete
语法：
DELETE FROM 表名 [WHERE 条件]
关于DELETE删除的问题，重启数据库现象：
- INNODB 自增列会从1开始（存在内存当中，断电即失）
- MYISAM 继续从上一个子增量开始（存在内存当中，不会丢失）
TRUNCATE
作用：完全清空一个数据库表，表的结构和索引约束不会变！
DELETE和TRUNCATE 的区别：
- DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表
- TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增
- DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');
-- 不会影响自增
DELETE FROM `test`;
-- 会影响自增
TRUNCATE TABLE `test`;
3、修改update
语法：
UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];
关于WHERE条件语句：
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| > | 大于 |
| < | 小于 |
| <= | 小于等于 |
| >= | 大于等于 |
| BETWEEN…AND… | 闭合区间 |
| AND | 和 |
| OR | 或 |
4、查询
1、基础查询
语法：
SELECT 查询列表 FROM 表名;
- 查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数
- 查询结果是一个虚拟的表格
2、条件查询
语法：
select 查询列表 from 表名 where 筛选条件;
3、分组查询
语法：
select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
区别：
| 使用关键字 | 筛选的表 | 位置 | |
|---|---|---|---|
| 分组前筛选 | where | 原始表 | group by的前面 |
| 分组后筛选 | having | 分组后的结果 | group by 的后面 |
4、连接查询
自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
5、排序和分页
排序
语法：
select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc
- order by的位置一般放在查询语句的最后（除limit语句之外）
| asc ： | 升序，如果不写默认升序 |
|---|---|
| desc： | 降序 |
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;
分页
语法：
select 查询列表
from 表
limit offset,pagesize;
- offset代表的是起始的条目索引，默认从0开始
- size代表的是显示的条目数
- offset=(n-1)*pagesize
-- 第一页 limit 0 5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页 limit (n-1)*pagesize,pagesize
-- pagesize:当前页面大小
-- (n-1)*pagesize:起始值
-- n:当前页面
-- 数据总数/页面大小=总页面数
-- limit n 表示从0到n的页面
6、子查询
本质：在 where子句中嵌套一个子查询语句
-- 使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
SELECT SubjectNo FROM `subject`
WHERE SubjectName='课程设计'
)
7、MySQL聚合函数
| 函数 | 描述 |
|---|---|
| max | 最大值 |
| min | 最小值 |
| sum | 和 |
| avg | 平均值 |
| count | 计算个数 |
SELECT COUNT(StudentName) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;
五、数据库加密
以下列举几个MySQL常用加密方式
1.MD5加密
语法：MD5(‘密码’)
向student中插入一条数据，语句如下：
insert into student values ('03163180', MD5('12345'));
成功之后查看表中的内容，可以看到刚才插入的数据密码已经变成了一串我们看不懂的字符串。
2.PASSWORD加密
语法：PASSWORD(‘密码’)
向student中插入一条数据，语句如下：
insert into student values ('03163181', PASSWORD('000000'));
成功之后查看表中的内容，可以看到刚才插入的数据密码已经变成了一串我们看不懂的字符串。
3.双向加密
双向加密就是明文可以变成密文存储在数据库中，同时密文也能通过相应的解密方法查看到明文
加密语法：ENCODE(‘密码’, ‘任意字符’)
向student中插入一条数据，语句如下：
insert into student values ('03163182', ENCODE('222222', 'abc'));
成功之后输入查看student表的命令，可以看到用ENCODE加密形成的密文比用MD5和PASSWORD加密形成的密文更复杂且更短。
解密语法：DECODE(‘密码’, ‘任意字符’)
注意：解密的任意字符必须和加密时所用的任意字符相同
查看明文的语句如下：
select sid, DECODE(spass, 'abc') as spass from student where sid = '03163182';
六、事务
要么都成功，要么都失败
SQL执行：A转账给B
SQL执行：B收到A的钱
12
将一组SQL放在一个批次中去执行
- 例如银行转账：只有A转账成功且B成功到账，该事件才算结束，如果一方不成功，则该事务不成功
6.1、事务原则：ACID
| 名称 | 描述 |
|---|---|
| 原子性（Atomicity） | 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 |
| 一致性（Consistency） | 事务前后数据的完整性必须保持一致。 |
| 隔离性（Isolation） | 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。 |
| 持久性（Durability） | 事务一旦被提交则不可逆，被持久化到数据库中，接下来即使数据库发生故障也不应该对其有任何影响 |
6.2、事务并发导致的问题
隔离所导致的一些问题：
| 名称 | 描述 |
|---|---|
| 脏读 | 指一个事务读取了另外一个事务未提交的数据。 |
| 不可重复读 | 在一个事务内读取表中的某一行数据，多次读取结果不同。 |
| 虚读(幻读) | 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。 |
6.3、隔离级别
在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别
-
读未提交：一个事务读取到其他事务未提交的数据；这种隔离级别下，查询不会加锁，一致性最差，会产生
脏读、不可重复读、幻读的问题 -
读已提交：一个事务只能读取到其他事务已经提交的数据；该隔离级别避免了
脏读问题的产生，但是不可重复读和幻读的问题仍然存在；读提交事务隔离级别是大多数流行数据库的默认事务隔离级别，比如 Oracle，但是不是 MySQL 的默认隔离界别
-
可重复读：事务在执行过程中可以读取到其他事务已提交的新插入的数据，但是不能读取其他事务对数据的修改，也就是说多次读取同一记录的结果相同；该个里级别避免了脏读、不可重复度的问题，但是仍然无法避免幻读的问题
可重复读是MySQL默认的隔离级别
-
串行化：事务串行化执行，事务只能一个接着一个地执行,、，并且在执行过程中完全看不到其他事务对数据所做的更新；缺点是并发能力差，最严格的事务隔离，完全符合ACID原则，但是对性能影响比较大
事务隔离级别 脏读 不可重复读 幻读 读未提交（read-uncommitted） 是 是 是 读已提交（read-committed） 否 是 是 可重复读（repeatable-read） 否 否 是 串行化（serializable） 否 否 否
6.4、执行事务的过程
1️⃣ 关闭自动提交
SET autocommit=0;
2️⃣ 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
3️⃣ 成功则提交，失败则回滚
-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
4️⃣ 事务结束
SET autocommit=1; -- 开启自动提交
5️⃣ 其他操作
5️⃣ 其他操作
SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 撤销保存点
七、索引
索引（
Index）是帮助MySQL高效获取数据的数据结构。
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化
主键索引（PRIMARY KEY）
唯一的标识，主键不可重复，只有一个列作为主键
- 最常见的索引类型，不允许为空值
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
普通索引（KEY / INDEX）
默认的，快速定位特定数据
- index 和 key 关键字都可以设置常规索引
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
唯一索引（UNIQUE KEY）
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值
与主键索引的区别：主键索引只能有一个、唯一索引可以有多个
全文索引（FULLText）
快速定位特定数据（百度搜索就是全文索引）
- 在特定的数据库引擎下才有：MyISAM
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
索引原则
- 索引不是越多越好，小数据量的表不需要加索引
- 不要对经常变动的数据增加索引
- 索引一般加在经常要查询的列上
八、SQL注入
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
拼接整条sql语句是select * from app_user where name=' ' or '1==1'，其中1==1永远是真的，所以该sql语句相当于查询表中所有的数据；这就是sql注入，主要是字符串拼接引起的问题，十分危险。
参考资料：
https://blog.csdn.net/qq_45173404/article/details/115712758
23万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
