数据库基础详解

1、MySQL简介

1.1 什么是数据库？

​ 数据库（DB）是存放数据的仓库，只不过这些数据存在一定的关联，并按照一定的格式存放在计算机上，MySQL是最流行的开放源码SQL数据库管理系统，它是由MySQL AB公司开发，发布并支持。

1.2 什么是数据库管理系统？

​ 数据库管理系统是用来管理数据库，它按一定的数据模型组织数据。

​ DBMS功能：

• 数据定义功能可以定义数据库的数据对象
• 数据操作功能可以对数据库进行基本操作，如插入、删除、修改、查询。
• 数据的完整性检查功能保证用户输入的数据应满足相应的约束条件
• 数据库的安全保护功能保证只有赋予权限的用户才能访问数据库中的数据
• 数据库的并发控制能使多个应用程序可以在同一时刻并发的访问数据库的数据
• 数据库系统的故障挥发功能使数据库运行出现故障时进行数据库恢复，以保证数据库的可靠运行

1.3 什么是数据库系统？

​ 数据，数据库，数据库管理系统与操作数据库的应用程序，加上支撑他们的硬件平台，软件平台和与数据库有关的人员一起构成了一个完整的数据库系统。

1.3.1 数据模型

​ 数据库管理系统根据数据模型对数据进行存储和管理，数据库管理系统采用的数据模型主要有层次模型（以树形层次结构组织数据）、网状模型和关系模型。

​ 现在流行的数据库管理系统大多采用关系模型进行数据的组织和管理，因为按关系模型组织的数据表达方式，简洁，直观，插入，删除，修改操作方便，而且层次，网状模型组织的数据表达关系型数据库得以广泛的应用。

​ 关系数据库的标准语言是SQL

1.3.2 SQL语言功能的四个部分

• 数据查询（select）
• 数据操作（delete/update/insert）
• 数据定义（create/alter/drop）
• 数据控制（grant/revoke）

1.4 SQL数据库与nosql数据库

• SQL数据库通过SQL语句来操作数据的–存储数据
• NOSQL不适用SQL语句来操作–缓存数据库
• NOSQL不适用SQL语句，所有效率比SQL更快

2、MySQL基础指令

2.1数据库进入和退出

1. 进入MySQL
   • 从命令行进入： cmd
   • 进入指令：mysql -u root -p
   • 输入密码
2. 退出MySQL
   • 退出指令：exit；

2.2 对数据库的基础指令

1. 查看MySQL里面的全部数据库
   • show databases;
2. 创建数据库
   • create databases 要创建的库名 ；
   • 无法创建已有数据库
3. 选用数据库
   • use 数据库名；
4. 删除数据库
   • drop database 数据库名；
5. 显示数据库里面的所有表
   • show tables；
6. 退出数据库
   • exit

3、Mysql命名规则与数据类型

3.1 MySQL命名规则

​ MySQL命名可用数据类型为：

• 名称可以有服务器所采用的字符集中的任意字母、数字、"_“和”$"组成
• 名称可以按照上述任意字符包括数字起头，但是名称不能单独由数字组成，因为那样会使其与数值相混
• 数据库、表、列和索引名称最多可由64个字符组成。别命最多可以长达256个字符

3.2 MySQL数据类型

MySQL常用数据类型包括：

• 数值型：
  • MySQL可以处理普通十进制的数据，亦可以处理十六进制的数据
• 字符型
  • MySQL中的字符型数据是用来保存字符串的
• 日期和时间类型
  • 日期和时间是一些如"2020-1-1"或者"15:14:51"这样的值
• NULL值
  • null是一种无类型的值

数据类型	描述	字节	推荐使用
tinyint	十分小的数据	1	不推荐使用
smallint	整数，从-32000到+32000	2	存储相对比较小的整数
mediumint	中等大小数据	3
int	整数，从负20亿到正20亿	4	存储中等整数。如：距离
bigint	不能使用smallint或int描述的超大整数	8	存储超大的整数。如：科学/数学数据
float	单精度浮点型数据	4	存储较小的数据。如：测量，温度
double	双精度浮点型	8	需要上精度存储的小数数据。如：科学数据
decimal	用户自定义精度的浮点型数据；字符串形式的浮点数	变量；取决精度于长度	存储特别高的精度小数数据。如货币额数，科学数据，金融计算
char	固定长度的字符串	特定字符串（高达255字符）	存储通常包含预定义字符串的变量
varchar	固定长度的字符串	变量：1+实际字符串长度	存储不同长度的字符串值
tinytext		2的8次方-1	存储微型文本
blob	二进制字符串	变量：2+实际字符串长度	存储二进制数据。如图片，附件，二进制文档
text	没有最大长度限制的可变长度字符串		存储二进制数据。如新闻故事，产品描述
date	以yyyy-mm-dd格式的日期	3	存储日期。如生日，产品满期
time	以hh：mm：ss格式的时间	3	存储时间或者时间间隔。如警报声，两时间之间的间隔
datetime	以yyyy-mm-dd hh：mm：ss格式结合日期和时间		存储包含日期和时间的数据
timestamp	时间戳		1970.1.1到现在的毫秒数

4、 表的基本操作

4.1 创建表

create table `表名`（
字段名1 数据类型 约束，
字段名2 数据类型 约束，
字段名n 数据类型 约束
）；

4.2 查看表

show tables;

4.3 查看表中的所有信息

describe `表名`

4.4 向表里添加内容

4.4.1 添加所有列的内容

insert into `表名` values (值);  -- 值和字段名一一对应

4.4.2 往指定某些列中添加内容

insert into `表名`(字段名1，字段名2，字段名n) values (值)
-- 值于 ()里的字段名一一对应

4.5 查看某张表的所有内容

select * from `表名`;

4.6 修改表里的数据

update `表名` set 字段名 = 值,字段名 = 值, 字段名 = 值,  where 【条件】

4…7 删除某张表的数据

delete from `表名` where 【条件】

4.8 修改表

-- 修改表名 ALTER TABLE  旧表名 RENAME AS 新表名
alter table `旧表名` rename as `新表名`;
-- 增加表的字段 ALTER TABLE 表明 ADD 字段名 属性
alter table `表名` add `字段名` 数据类型;
-- 修改表的字段
alter table `表名` modify `字段名` 数据类型  -- 修改约束
alter table `表名` change `旧字段名` `新字段名` 数据类型  -- 修改字段名
-- 删除表的字段
alter table `表名` drop `字段名`;
-- 删除表
drop table if exists `表名`;

5、MySQL数据完整性

​ 确保数据库中包含的数据的准确和一致的数据性质

​ 存储在数据库中的所有数据值均正确的状态。如果数据库中存储有不正确的数据值，则该数据库成为已丧失数据完整性。

5.1 数据库完整性规则

1. 数据完整性的分类：

   ​ 数据的值正确无误

   ​ 数据的存在必须确保同一表格数据之间的和谐统一

   ​ 数据的存在必须维护不同表格之间的数据的和谐统一

2. 域的完整性的实现：

   ​ 域的完整性又称为列完整性，指定一个数据集对某一个列是否有效和确定是否允许为空值

3. 实体完整性的实现：

   ​ 实体完整性也可以成为行完整性。要求表中的每一行有一个唯一的标识符，这个标识符就是主关键字。

4. 参照完整性的实现：

   ​ 参照完整性又可以称为引用完整性。参照完整性保证主表中的数据与从表中数据的一致性。

   ​ 主键：再表中能唯一标识表的每个数据行的一个或多个列表

   ​ 外键：如果一个表中的一个字段或若干个字段的组合是一个表的主键则称该字段或者字段祝贺为该表的外6*键

   ​ 外键所在的表叫做子表或者从表

   ​ 被参照的表叫附表或者主表

5.2 如何保证数据的完整性——约束

约束：是在表上强制执行的数据效验规则

• not null 非空
• check 检查保证域完整性
• unique key 唯一键
• primary key 主键 实体完整性
• foreign key 外键 参照完整性
• unsigned 不能为负数 无符号整型
• zerofill 0填充 不足的位数用0来提案冲
• auto_increment 自增 自动在上一条记录的基础上+1 必须是整型 可以自定义初始值
• default 设置默认值

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwdd` VARCHAR(20) NOT NULL COMMENT '密码',
`sex` VARCHAR(2) NOT NULL COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`emali` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', 
PRIMARY KEY (`id`)`student`
)ENGINE=INNODB DEFAULT CHARSET=utf8

5.3 阿里巴巴规范

每一个表都必须存在以下五个字段！

1. id 主键
2. ‘version’ 乐观锁
3. is_delete 伪删除
4. gmt_create 创建时间
5. gmt_upddate 修改时间

6、数据库查询语言——DQL

6.1查询语句、

• 所有的查询操作都用它select

• 简单的查询，复杂的查询它都可以做

• 数据库中最核心的语言，最重要的语句

• 使用频率最高

  -- 查询表中的所有字段
  select * from `表名`;
  select * from `student`;-- 查询一个指定的字段
  select `字段名1`,`字段名2` from `表名`;
  select `name`,`id` from `student`;-- 给字段起别名
  select `字段名1` as 别名,`字段名2` 别名 from `表名`;
  select `name` as '姓名',id '学号' from `gread`;-- 函数Cancat(a,b) 字符串拼接
  select concat("要拼接的字符",`字段名`) from `表名`
  select concat('年级：',gradename) as '所在年级' from `gread`;

  -- 查询时去掉重复数值--distinct
  select distinct `字段名` from `表名`;
  select distinct `gradename` from `gread`;

  -- 把查询出来的数据都增加或其他改变
  select `字段名`+1 from `grade`;
  select `字段名`+1 as '增加后',`gradename` from `grade`;
  

6.2 where 条件查询

根据条件查询 where:
where 语句里可以使用 = 、> 、< 、<= 、>= 、!=

-- and 条件连接
select * from `表名` where `字段名` 条件 and `字段名` 条件;
select * from `library` where `s_setm` > 20 and `s_setm` < 30;

-- or 或
select * from `表名` where `字段名` 条件 or `字段名` 条件
select * from `library` where `s_setm` = 30 or `s_setm` = 31;

-- in 
select * from `表名` where `字段名` in(条件);
select * from `library` where `s_stem` in(30, 31, 32, 33)

-- 两者之间
select * from `表名` where `字段名` between 条件 and 条件;
select * from `libraray` where `s_price` between 50 and 90;

6.3 模糊查询

比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null，结果为真
is not null	a is not null	如果操作符不为null，结果为真
between	a between b and c	若a在b和c之间，结果为真
like	a like b	SQL匹配，如果a匹配b，结果为真
in	a in （a1,a2,a3）	假设a在a1，或者a2，a3其中的某一个结果为真

-- 查询以指定内容开头或者结尾或者包含的数据
select * from `表名` where `字段名` like '查询内容%';
select * from `grade` where `gradename` like '大%';-- 查询指定内容后只有一个字或前面只有一个字的
select * from `grade` where `gradename` like '大_';
select * from `grade` where `gradename` like '_大'1;

6.4 分组查询和分页查询

6.4.1 分组查询

-- 例：查询每个部门有多少人
select `字段名`,count(要统计的字段名) from `表名` group by `字段名`;
select id,count(id) from `school` group by `id`;

6.4.2 分页查询

-- 查询出表里面第X到第Y个信息：分页查询
select * from `表名` limit x,y;
select * from `person` limit 7,10;

6.5 where和having的区别

​ where和having语句都是条件语句，where后面的条件不能使用函数，having后面只能使用函数，一般情况下having和group by 一起使用

6.6 多表查询

操作	描述
inner join	如果表中至少又一个匹配就返回行
left join	会从左表中返回所有值，即使右表中没有匹配
right join	会从右表中返回所有值

-- 查询两个表中id 相同的数据
select * from `表名1`,`表名2` where `表名1`.id = `表名2`.id;
select * from `school` as s, `person` p where s.id = p.id;

-- 查询字段名所属其他字段名
select `表名`.`字段名`,`表名`.`字段名` from `表名`,`表名` where `表名`.`字段名` = `表名`.`字段名`;

-- 查询所有部门内的所有员工
-- 左连接：left join
select * from `表名` left join `表名` on `表名`.`字段名` = `表名`,`字段名`;
select * from `school` left join `person` on `school`.id = `person`.id; 

-- 右连接：right join
-- 右连接是查询右表中的所有信息和左表里面符合要求的信息
select * from `表名` right join `表名` on `表名`.`字段名` = `表名`.`字段名`;

-- 查询指定目标的所有数据
select * from `school` right join `person` on `school`.id = `person`.id where `person`.name = '小明';

-- 查询指定目标的指定数据
select `person`.name,`pserson`.age,`school`.section 
from `person` join `school` 
on `school`.id = `person`.id 
where `person`.name = '小明';

6.7 分页和排序

6.7.1 分页查询

-- 查询出表里面X到第Y个的信息
-- limit x,y
-- X 表示开始 Y要查询的个数
select * from `表名` limit x,y;
select * from `person` limit x,y;

6.7.2 排序

-- 排序：order by 
-- order by 字段名【asc】  --升序
-- order by 字段名【desc】 --降序
select * from `person` oder by age desc;

7、常用函数

7.1 数学函数

-- 数学运算函数
SELECT ABS(-1)  -- 绝对值
SELECT CEILING(9.4)  -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND()  -- 返回0-1之间的随机数
SELECT SIGN(10)  -- 判断一个数的符号 0-0 负数返回-1， 正数返回1

7.2 字符串函数

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航')  -- 字符串长度
SELECT CONCAT('一giao','我里giao','giao ')  -- 拼接字符串
SELECT INSERT('帆船要远航' , 1 ,2,"巨轮")  -- 查询替换
SELECT LOWER('HeiZi')  -- 全部转为小写字母
SELECT UPPER('HuiZi')  -- 全部转为大写字母
SELECT INSTR('heizi','e')  -- 返回第一次出现的子串的索引
SELECT REPLACE('好运藏在努力里' ,'好运' ,'努力') -- 替换指定出现的字符串
SELECT SUBSTR('好运藏在努力里' ,4，6)  -- 返回指定的子串字符串
SELECT REVERSE('我马上成')  -- 字符串反转

7.3 时间和日期函数

-- 时间和日期函数
SELECT CURRENT_DATE()  -- 获取当前时间
SELECT CURDATE()  -- 获取当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME()  -- 本地时间
SELECT SYSDATE()  -- 系统时间

7.4 聚合函数

函数名称	描述
count()	计数
sum()	求和
avg（）	平均值
max（）	最大值
min（）	最小值

-- 聚合函数SELECT COUNT(NAME) FROM people;   -- Count(字段)，会忽略所有的null值SELECT COUNT(*) FROM people;	-- Count 不会忽略null值，本质 计算行数SELECT COUNT(1) FROM people;	-- Count 不会忽略null值。本质 计算行数

8、数据库级别的MD5加密

什么是MD5：

​ MD5是一种被广泛使用的密码散列函数，用于确保信息传输完整一致，可以产生出一个128位的散列值。

​ 主要增强算法复杂度和不可逆性

​ MD5 不可逆，具体的值的md5 是一样的

​ MD5破解原理 背后一个字典，MD5加密后的值 和加密钱的值

  -- 测试md5 加密CREATE TABLE  `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `testmd5` VALUES(1,'heizi','123456'),
(2,'huizi','456789'),
(3,'yazi','147258');-- 加密
UPDATE testmd5 SET pwd = MD5(pwd);-- 插入的时候就加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('147110'));
-- 如何效验：将用户传递来的密码，进行md5加密，然后对比加密后的值

9、事务

9.1 什么是事务：

​ 数据库事务是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

​ 要么都成功，要么都失败

事务原则：

​ ACID 原则 原子性 一致性 隔离性 持久性

持久性：

​ 事务没有提交，恢复到原状 ，事务已经提交，持久化到数据库事务一旦提交不可逆

9.2数据库隔离性存在的问题

1.脏读

脏读指一个事务读取了另外一个事务未提交的数据。

​ 这是非常危险的，假设Ａ向Ｂ转帐100元，对应sql语句如下所示：
1.update account set money=money+100 where name=‘B’;
2.update account set money=money-100 where name=‘A’;
当第1条sql执行完，第2条还没执行(A未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了100元钱。如果A等B走后再回滚，B就会损失100元。

2、不可重复读

​ 不可重复读指在一个事务内读取表中的某一行数据，多次读取结果不同。
例如银行想查询A帐户余额，第一次查询A帐户为200元，此时A向帐户内存了100元并提交了，银行接着又进行了一次查询，此时A帐户为300元了。银行两次查询不一致，可能就会很困惑，不知道哪次查询是准的。
不可重复读和脏读的区别是，脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。
很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和写到文件中，结果在一个事务中针对输出的目的地，进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

3、虚读(幻读)

虚读(幻读)是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
如丙存款100元未提交，这时银行做报表统计account表中所有用户的总额为500元，然后丙提交了，这时银行再统计发现帐户为600元了，造成虚读同样会使银行不知所措，到底以哪个为准。

9.3 模拟事务

-- 事务 
-- mysql是默认开启事务自动提交的SET autocommit = 0 /* 关闭 */SET autocommit = 1 /* 开启（默认的） */-- 手动处理事务-- 事务开启 START TRANSACTION  -- 标记一个事务的开始，从这个之后的sql都在同一个事务中-- 提交：持久化（成功）COMMIT-- 回滚：回到原来的样子（失败）ROLLBACK-- 事务结束：
SET autocommit = 1 SAVEPOINT 保存点 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE-- ========================= 模拟场景============================CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ciUSE shopCREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8INSERT INTO account(`name`,`money`) VALUES('A','2000.00'),('B','5000.00');-- 模拟事务转载
SET autocommit = 0;  -- 关闭自动提交START TRANSACTION  -- 开启一个事务UPDATE account SET money=money - 500 WHERE `name` = 'A' -- A减去500
UPDATE account SET money=money + 500 WHERE `name` = 'B' -- B加上500COMMIT;  -- 提交事务   如果没有执行提交 数据就可以返回原始状态 
ROLLBACK;  -- 回滚   
SET autocommit = 1; -- 恢复默认状态

10、索引

10.索引的分类

• 主键索引 （PRIMARY KEY）

• • 唯一标识，主键不可重复，只能由一个列作为主键

• 唯一索引 （UNIQUE KEY）

• • 避免重复的列出现，唯一索引的列可以由多个

• 常规索引 （KEY/INDEX）

• • 默认的，index，key 关键字来设定

• 全文索引 （FULLTEXT）

• • 在特性的数据库引擎下才有，myisam
  • 快速定位数据

增加一个全文索引 （索引名）列名
ALTER TABLE school.student ADD FULLTEXT INDEX studentname(stdentname);
分析sql的执行状况
EXPLAIN SELECT * FROM student;CREATE INDEX id_app_user_name ON app_user(`name`);

10.2索引原则

• 索引不是越多越好

• 不好对经常变动的数据加索引

• 小数据量的标不需要加索引

• 索引一般加载常用来查询的字段上

  索引的数据据结构

  hash 类型的索引

  btree：innoDB的默认数据结构

11、数据库的备份与设计

11.1 数据库备份

为什么要备份：

• 保证重要的数据补丢失
• 数据转移

数据库的备份方式

• 直接拷贝物理文件
• 在sqlyog等可视化工具中手动导出
• 使用命令行导出 mysqldump

11.2 数据库设计

为什么要设计数据库：

​ 当数据库比较赋值的时候我们就要设计了

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据插入和删除都会麻烦，可能会产生异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图 E-R图

设计数据库的步骤：（个人博客）

• 收集信息，分析需求

• • 用户表（用户登陆注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键的字，或者一些主字段） key ：value

• 标识实体（把需求落地到每个字段）

• 标识实体之间的关系

11.3 数据库的三大范式

为什么需要数据规范化

• 信息重复

• 更新异常

• 插入异常

• • 无法正常显示信息

• 删除异常

• • 丢失有效信息

第一范式：

要求数据库表的每一列都是不可分割的原子数据项

第二范式：

前提：满足第一范式

一张表只能描述一件事情

第三范式：

前提：满足第一范式和第二范式

每一列数据都要和主键直接相关，不能间接相关

规范性和性能问题：

关联查询的表不得超过三张表

• 考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
• 在规范性能的问题的时候，需要适当考虑一下 规范性
• 故意给某些表增加一些冗余大的字段
• 故意增加一个计算列（从大数据量降低为小数据量）