数据库

数据库

1.数据库：DataBase（DB），是存储和管理数据的仓库。

2.数据库管理系统：DataBase Management System（DBMS），操纵和管理数据库的大型软件。

3.SQL：Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

数据库产品

数据库	介绍
Oracle	收费的大型数据库，Oracle公司的产品
MySQL	开源免费的中小型数据库。Sun公司收购了MySQL，Oracle收购了Sun公司
SQL Server	MicroSoft公司收费的中型的数据库。C#、.net等语言常使用
PostgreSQL	开源免费中小型的数据库
DB2	IBM公司的大型收费数据库产品
SQLite	嵌入式的微型数据库，如：作为Android内置数据库
MariaDB	开源免费的中小型的数据库

数据库设计

MySQL的安装：

下载解压zip压缩包，配置环境变量，

• MYSQL_HOME（系统变量新建一个）
  D:\ （安装路径）
• PATH变量下新建一个%MYSQL_HOME%\bin

MySQL数据模型

1.关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：

• 使用表存储数据，格式统一，便于维护
• 使用SQL语言操作，标准统一，使用方便，可用于复杂查询

SQL简介

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

通用语法：

• SQL语句可以单行或多行书写，以分号结尾
• SQL语句可以使用空格/缩进来增强语句的可读性
• MySQL数据库的SQL语句不区分大小写
• 注释：
  1.单行注释：-- 注释内容 或 # 注释内容(MySQL特有)（注意--和#后面有空格）
  2.多行注释：/* 注释内容 */

SQL分类

分类	全程	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

数据库设计-DDL

1.查询所有数据库：show databases;

2.查询当前数据库：select database();

3.创建数据库：create database [ if not exists ] 数据库名;

4.使用数据库：use 数据库名;

5.删除数据库：drpp database [ if exists ] 数据库名;

6.注意事项：上述语法中的database，也可以替换成 schema。如：create schema db01;

DDL（表操作）

注意：[ ]里的内容是可选项

创建：

create table 表名(

​ 字段1 字段类型 [约束] [comment 字段1注释],

​ ......

​ 字段n 字段类型 [约束] [comment 字段2注释]

)[comment 表注释];

约束

• 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
• 目的：保证数据库中的数据的正确性、有效性和完整性。

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key (auto_increment自增)
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

数据类型

主要分为三类：

1.数值类型

tinyint，int，bigint，float，double，decimal（无符号就在后面加上unsigned）

2.字符串类型

char（定长），varchar（变长），text（长文本）

3.日期时间类型

data（YYYY-MM-DD），time（HH:MM:SS），datatime（YYYY-MM-DD HH:MM:SS）

创建表时一般都会加上这两个字段：create_time：记录的是当前这条数据插入的时间，update_time：记录当前这条数据最后更新的时间。

查询：

• 查询当前数据库所有表：show tables;
• 查询表结构：desc表名;
• 查询建表语句：show create table表名;

修改：

• 添加字段：alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
• 修改字段类型：alter table 表名 modify 字段名 新数据类型(长度);
• 修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
• 删除字段：alter table 表名 drop column 字段名;
• 修改表名：rename table 表名 to 新表名;

删除：

• 删除表：drop table [if exists] 表名;

注意事项：在删除表时，表中的全部数据也会被删除。

DML

DML英文全程是Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增、删、改操作。

• 添加数据（INSERT)
• 修改数据（UPDATE）
• 删除数据（DELETE）

insert语法：

• 指定字段添加数据：insert into 表名（字段名1，字段名2）value（值1，值2）;
• 全部字段添加数据：insert into 表名（值1，值2，...）;
• 批量添加数据（指定字段）：insert into 表名（字段名1，字段名2）values（值1，值2），（值1，值2）;
• 批量添加数据（全部字段）：insert into 表名 values（值1，值2，...），（值1，值2，...）;

注意事项：

1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
2. 字符串和日期型数据应该包含在引号中。
3. 插入的数据大小，应该在字段的规定范围内。

update语法：

修改数据：update 表名 set 字段名1 = 值1，字段名2 = 值2，...[where 条件];

注意事项：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

delete语法

删除数据：delete from 表名 [where 条件];

注意事项：

1. DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
2. DELETE语句不能删除某一个字段的值（如果要操作，可以使用UPDATE，将该字段的值置为NULL）。

DQL

DQL英文全程是Data Query Language（数据查询语言），用来查询数据库表中的记录。

关键字：SELECT

语法

select
	字段列表
from
	表名列表		#基本查询
where
	条件列表		#条件查询（where）
group by
	分组字段列表
having
	分组后条件列表	  #分组查询（group by）
order by
	排序字段列表	   #排序查询（order by）
limit
	分页参数		#分页查询（limit）

单表查询：

• 查询多个字段：select 字段1，字段2，字段3 from 表名；
• 查询所有字段（通配符）：select * from 表名；
• 设置别名：select 字段1 [as 别名1]，字段2 [as 别名2] from 表名；（as可以省略）
• 去除重复记录：select distinct 字段列表 from 表名；

注意事项：*号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。

条件查询

• 条件查询：select 字段列表 from 表名 where 条件列表；

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between ... and ...	在某个范围之内（含最小、最大值）
in(...)	在in之后的列表中的值，多选一
like	模糊匹配（_匹配单个字符，%匹配任意个字符）
is null	是null

逻辑运算符	功能
and 或 &&	并且（多个条件同时成立）
or 或 ||	或者（多个条件任意一个成立）
not 或 ！	非，不是

分组查询

聚合函数：

• 介绍：将一列数据作为一个整体，进行纵向计算。
• 语法：select 聚合函数（字段列表）from 表名；

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

注意事项：

• null值不参与所有聚合函数运算。
• 统计数量可以试用：count(*) count(字段) count(常量)，推荐试用 count(*)。

语法：

分组查询：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where 与 having 区别：

1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

2.判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项：

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。（即不能select *，select后的字段一般包含分组字段名和聚合函数，中间用逗号隔开）
• 执行顺序：where>聚合函数>having。（先执行where条件判断，再分组执行聚合函数，最后再判断having后面的条件过滤）

排序查询

语法：

• 排序查询：select 字段列表 from 表名 [][][][][where 条件列表] [group by 分组字段] order by 字段1 排序方式1，字段2 排序方式2 ... ;

排序方式：

• ASC：升序（默认值，通常省略不写）
• DESC：降序

注意事项：

• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。以此类推。

分页查询

语法：

• 分页查询：select 字段列表 from 表名 limit 起始索引，查询记录数；

注意事项：

1. 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
3. 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

函数：

• if(表达式，truevalue，falsevalue)：当表达式为true时，取值truevalue；当表达式为false时，取值falsetrue
• case 表达式 when value1 then result1 [when value2 then result2 ... ] [else result] end

多表设计

概述：项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系 ，基本上分为三种：

• 一对多（多对一）
• 多对多
• 一对一

一对多

一的一方叫父表，多的一方叫子表

现象：部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整性、不一致问题。

问题分析：目前上述两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。-->外键

外键语法：

--创建表时指定
create table 表名(
	字段名 数据类型,
	...
	[constraint] [外键名称] foreign key(外键名称) references 主表(字段名)
);
--键完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references(字段名);

外键约束

物理外键：

概念：使用foreign key定义外键关联另外一张表。

缺点：

1.影响增、删、改的效率（需要检查外键关系）。

2.仅用于单节点数据库，不适用于分布式、集群场景。

3.容易引发数据库的死锁问题，消耗性能。

逻辑外键：（推荐）

概念：在业务层逻辑中，解决外键关联。

通过逻辑外键，就可以很方便的解决上述问题。

一对一

案例：用户 与 身份证信息的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

多对多

案例：学生 与 课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两张表的主键

多表查询

分类：

连接查询：

• 内连接：相当于查询A、B交集部分数据
• 外连接：
  1.左外连接：查询左表所有数据（包括两张表交集部分数据）
  2.右外连接：查询右表所有数据（包括两张表交集部分数据）

子查询

内连接

语法：

• 隐式内连接：select 字段列表 from 表1，表2 where 条件 ... ;
• 显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;

注意：可以给表起别名，起了别名后在字段名前就必须用别名点而不是表名点

外连接

语法：

• 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...;
• 右外连接：select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ...;

注意：左外连接可以和右外连接相互转换，表1和表2互换位置即可，左外连接是查询包含左表即表1的全部部分包括交集，右外连接是查询包含右表即表2的全部部分包括交集。通常使用左外连接。

子查询

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

形式：select * from t1 where column1 = (select column1 from t2 ...);

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是select。

分类：

• 标量子查询：子查询返回的结果为单个值
• 列子查询：子查询返回的结果为一列
• 行子查询：子查询返回的结果为一行
• 表子查询：子查询返回的结果为多行多列

标量子查询

• 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

• 常用的操作符：= <> > >= < <=

列子查询

• 子查询返回的结果是一列（可以是多行）
• 常用的操作符：in、not in等

行子查询

• 子查询返回的结果是一行（可以是多列）。
• 常用的操作符：=、<>、in、not in

表子查询

• 子查询返回的结果是多行多列，常作为临时表
• 常用的操作符：in

事务

概念：事务 是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

（默认情况下：每一条SQL语句都是一件事务）

事务控制：

• 开启事务：start transaction; / begin;
• 提交事务：commit;
• 回滚事务：rollback;

四大特性（ACID）

原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败

一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态

隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

索引

概念：索引（index）是帮助数据库高效获取数据的数据结构。

优点：

• 提高数据查询的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

缺点：

• 索引会占用存储空间。
• 索引大大提高了查询的效率，同时也降低了insert、update、delete的效率。

结构：

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是默认的B+Tree结构组织的索引。

B+Tree（多路平衡搜索树）：

• 每一个节点，可以存储多个key（有n个key，就有n个指针）。
• 所有的数据都存储在叶子节点，非叶子节点仅用于索引数据。
• 叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询。

通常B+Tree树只有几层（四五层）

语法：

• 创建索引：create [unique] index 索引名 on 表名(字段名, ... );
• 查看索引：show index from 表名;
• 删除索引：drop index 索引名 on 表名;

注意事项：

• 主键字段，在建表时，会自动创建主键索引。（性能最高）
• 添加唯一约束时，数据库实际上会添加唯一索引。