概述

非关系型数据库

非关系型数据库并没有统一的存储结构标准，现常见结构有键值、文档、JSON类型等，对高性能需求设计

1. 灵活的数据类型：支持多种数据模型，例如键值对、文档型、列族型和图形数据库
2. 无固定的表结构：不需要预定义固定的表结构，适应非结构化或者半结构化数据
3. 高性能读写：少关系型，注重高性能读写能力，适用于大规模数据和高并发访问模型

关系型数据库

数据按照类别进行存储，每个类别存储到一个容器中，，表和表之间可以建立关系，可以进行关联操作，性能相对一般

1. 结构化数据模型：数据以表格形式存储，具有固定的结构
2. ACID事务：通过强大的事务支持，保证数据的原子性、一致性、隔离性和持久性
3. 丰富查询语句：支持SQL语句，能够进行复杂的关联数据查询
4. 数据一致性：数据的关系和约束确保数据的一致性和完整性

特点总结

关系型数据库适用于需要强一致性和复杂查询的场景

非关系型数据库适用于需要高性能、灵活性和分布式处理场景

数据库的选择

1. 绝大部分应用采用混合模式，二者结合使用
2. 程序主体数据一般存储在关系型数据库（主）
3. 程序缓存数据和高并发数据存储到非关系型数据库（辅）

关系型数据库存储设计规则（模型）

• 遵循ER模型

  • E（Entity）代表实体类别，关系型数据库中一类数据，对应数据库中的一张“表”存储

  • R（Relationship）“表”和“表”可以维护某种关系，可以通过“关系”进行多表操作

• 模型解释

  • 数据中最大的存储单位是库

  • 每类数据存储一张“表”中，“表”存储到“库”中

  • “表”具有一些特性“列”，这些特性“列”定义数据在表中如何存储

  • “表”中的数据时按照“行”存储的，一“行”即为一条记录

数据库管理系统概述

概述

数据库管理系统（DataBase Management System，DBMS）

指一种操作和管理数据库的大型软件，用户通过数据库管理系统操作数据库中的数据

常见关系型数据库管理系统

• Oracle：行业标杆、大型项目、企业许可收费昂贵
• MySQL：中小项目、企业开源免费
• DB2：IBM产品生态、金融项目
• SQLite：小型嵌入式、移动设备、企业开源免费

安装官网

MySQL_Downloads (https://www.mysql.com/downloads/)

MySQL启动

• net start mysql80
• net stop mysql80

客户端连接

• 直接使用MySQL提供的工具
• 以管理员身份运行命令行窗口，这种方法需要配置path环境变量（粘贴MySQL bin路径下的地址）

结构化查询SQL

数据库操作方式

• 图形化界面（GUI）应用程序：拥有图形化界面直接进行操作
• 命令行界面（CLI）/后台服务应用程序：用户通过在命令行或终端输入文本命令进行交互

结构化查询语句(SQL)

SQL：Structure Query Language （结构化查询语言）

SQL被国际化标准组织（ISO）采纳为关系型数据库语言的国际标准，可以使用SQL命令进行关系型数据库操作

SQL概述

SQL包括了所有对数据库的操作，主要是由数据定义、数据操纵、数据查询、数据控制、事务控制等SQL语言的使用规定组成

• 基础和重点

  创建和修改盛放数据的容器（数据定义DDL）

  表中添加、修改、删除数据（数据控制DML）

  表中数据多条件查询（数据查询DQL）

• 扩展和提高

  事务启动、提交和回滚（事务控制TCL）

  账号创建、权限控制（数据控制DCL）

DDL——>DML——>DQL——>TCL——>DCL

基础指令

SQL语句以分号结尾，可以逐行书写

• 先打开cmd命令窗口（以管理员身份运行）

• 连接mysql服务命令

  登录用户名>mysql-u <username> -p<password> -h <hostname> <databasename>

  参数含义：

  • -u <username>:用于指定你要连接的MYSQL数据库的用户名

  • -p<password>:表示密码，后边紧跟着密码，中间没有空格

    ​ 如果你不希望在命令行中显示密码，可以不指定密码，直接-p，然后在提示下手动输入密码

  • -h <hostname>:用于指定MySQL服务器的主机名或者IP地址。如果MySQL在本地运行，可以使用localhost

  • -p <port>:用于指定连接MySQL服务器的端口号，默认情况下是3306

  • <databasename>:你要连接的数据库名称，连接后会默认使用这个数据库

• 其它基础指令

  • SQL注释

    单行注释：#注释内容

    单行注释：-- 注释内容 其中–后面必须要有空格

    多行注释：/ * 注释内容 * /

  • 版本和退出连接

    –查看版本，命令符号之间用空格隔开，每条命令后使用;结束否则不执行

    mysql>select version();

    –退出连接

    mysql>exit;

数据定义DDL

数据库操作

• 查询

  • 查询所有数据库

    SHOW DATABASES( );

  • 查询当前数据库

    SELECT DATABASE( );

• 创建

  CREATE DATABASE [ IF NOT EXITS ] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]; 必须大写

• 删除

  DROP DATABASE[ IF EXISTS ] 数据库名;

• 使用

  USE 数据库名;

• 不区分大小写

表操作-创建&查询

查询

• 查询当前数据库所有表

  SHOW TABLES；

• 查询表结构

  DESC 表明；

• 查询指定表的建表语句

  SHOW CREATE TABLE 表名；

创建

CREATE TABLE 表名（

​ 字段1 字段1类型 COMMENT 字段1注释，

​ 字段2 字段2类型 COMMENT 字段2注释，

​ 字段3 字段3类型 COMMENT 字段3注释，

​ ……

​ 字段n 字段n类型 COMMENT 字段n注释

） COMMENT 表注释;

注意：[…]为可选参数，最后一个字段后面没有逗号

表操作-修改

• 添加字段

  ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT 注释] [约束];

• 修改数据类型

  ALTER TABLE 表名 MODIFY 字段名 新数据类型 （长度）;

• 修改字段名和字段类型

  ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT 注释] [约束]；

• 删除字段

  ALTER TABLE 表名 DROP 字段名;

• 修改表名

  ALTER TABLE 表名 RENAME TO 新表名;

表操作-删除

• 删除表

  DROP TABLE[IF EXISTS] 表名;

• 删除指定表，并重新创建该表

  TRUNCATE TABLE 表名;

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

数据操作DML

概述

Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作

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

DML-添加数据

• 给指定字段添加数据

  INSERT INTO 表名（字段名1，字段名2，…）VALUES（值1，值2，…）;

• 给全部字段添加数据

  INSERT INTO 表名 VALUES（值1，值2）;

• 批量添加数据

  • INSERT INTO 表名（字段名1，字段名2，…）VALUES（值1，值2，…）（值1，值2，…）（值1，值2，…）；

  • INSERT INTO 表名 VALUES（值1，值2，…）（值1，值2，…）（值1，值2，…）；

注意：

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

DML-修改数据

• 更新数据

  • UPDATE 表名 SET 字段名1=值1，字段名2=值2，…WHERE 条件；

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

• 删除数据

  • DELETE FROM 表名 WHERE 条件

  • 注意：

    1. DELETE语句的条件可以有也可以没有，没有条件会删除整张表的数据
    2. DELETE语句不能删除某一个字段的值（使用UPDATE代替）

数据查询DQL

概述

Data Query Language ，数据查询语言，用来查询数据库中表的记录

DQL-基本查询

• 查询多个字段

  • SELECT 字段1，字段2，字段3…FROM 表名；

  • SELECT * FROM 表名；

• 设置别名

  • SELECT 字段1 [AS 别名1]，字段2[AS 别名2]…FROM 表名；

• 去除重复记录

  • SELECT DISTINCT 字段列表 表名；

DQL-条件查询

• 基础语法

  SELECT 字段列表 FROM WHERE 条件列表；

• 条件

  比较运算符	功能
  BETWEEN…AND…	在某个范围之内（含最大值，最小值）
  IN（…）	在in之后的列表中的值，多选一
  LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
  IS NULL	为空

DQL-聚合函数

将数据作为一个整体，进行纵向运算

• 常见的聚合函数

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

• 语法

  SELECT 聚合函数（字段列表） FROM 表名；

注意：null值不参与所有聚合函数的运算

DQL-分组查询

• 语法

  SELECT 字段列表 FROM 表名 WHERE 条件 GROUP BY 分组字段名 HAVING 分组过滤后的条件

• WHERE和HAVING的区别

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

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

DQL-排序查询

• 语法

  SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2；

• 排序方式

  • ASC：升序（默认值）

  • DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行升序

DQL-分页查询

• 语法

  SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数；

• 注意

  • 起始索引从0开始，起始索引=（查询页码-1）*每页显示的记录数

  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

  • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit+查询记录数

数据控制DCL

Data Control Language 数据控制语言，用来管理数据库用户、控制数据库访问权限

DCL-管理用户

• 查询用户

  • USE mysql；

  • SELECT * FROM user；

• 创建用户

  • CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’；

• 修改用户密码

  • ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’；

• 删除用户

  • DROP USER ‘用户名’@‘主机名’；

DCL-权限控制

MySQL中定义了很多种权限，常用的有以下几种

• 查询权限
  • SHOW GRANTS FOR ‘用户名@‘主机名’;
• 授予权限
  • GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’；
• 撤销权限
  • REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’；

函数

SELECT 聚合函数（字段列表） FROM 表名；

字符串函数

数值函数

日期函数

流程函数

在SQL语句中实现条件筛选，从而提高语句的效率

约束

概述

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

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

外键约束

外键用来让两张表的数据之间建立链接，从而保证数据的一致性和完整性

• 语法

  • 添加外键

  ​ CREATE TABLE 表名（

  ​ 字段名 数据类型，

  ​ …

  ​ [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 （主表列名）

  ​ );

  ​ ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERNCES 主表(列表名);

  • 删除外键

  ​ ALTER TABLE 表名 DROP FOGREIGN KEY 外键名称；

• 删除/更新行为

  行为	说明
  NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
  RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
  CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录
  SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null
  SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值

  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY （外键字段）REFERENCES 主表名（主表名字段）ON UPDATE 行为 DELETE 行为

多表查询

多表关系

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

• 一对多
• 多对多
• 一对一

多表查询概述

• 概述：指从多张表中查询数据

• 笛卡尔积：笛卡尔积是指两个集合A和B的所有组合情况

  • 在多表查询中，需要消除无效的笛卡尔积（WHERE 关键字）

• 分类

  • 连接查询

  ​ 内连接：相当于查询A、B交集的数据

  ​ 外连接：

  ​ 左外连接：查询左表所有数据，以及两张表交集部分数据

  ​ 右外连接：查询右表所有数据，以及两张表交集部分数据

  ​ 自连接：当前表与自身的连接查询，自连接必须使用别名

  • 子查询

内连接

• 隐式内连接

  • SELECT 字段列表 FROM 表1，表2 WHERE 条件…；

• 显示内连接

  • SELECT 字段列表 FROM 表1 [INNER] JOIN表2 ON 连接条件…;

内连接查询的是两张表交集的部分

外连接

• 左外连接

  • SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN表2 ON 条件…；

  ​ 相当于查询表1[左表]的所有数据，包含表1和表2交集部分的数据

• 右外连接

  • SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件…；

  ​ 相当于查询表2[右表]的所有数据，包含表1和表2交集部分的数据

自连接

• 查询语法

  SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件…;

自连接查询时，可以是内连接查询也可以是外连接查询

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

SELECT 字段列表 FROM 表A

UNION[ALL]

SELECT 字段列表 FROM 表B…;

子查询

• 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称为子查询

  • SELECT * FROM t1 WHERE column1 =(SELECT column1 FROM t2);

  ​ 子查询外部语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

• 根据子查询结果的不同，分为：

  • 标量子查询（子查询结果为单个值）

  • 列子查询（子查询结果为一列）

  • 行子查询（子查询结果为一行）

  • 表子查询（子查询结果为多行多列）

• 根据子查询位置，分为WHERE之后、FROM之后、SELECT之后

标量子查询

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

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

列子查询

• 子查询返回的结果是一列（可以是多行）

• 常用操作符

  操作符	描述
  IN	在指定的集合范围之内，多选一
  NOT IN	不在指定的集合范围之内
  ANY	子查询返回的列表中，有任意一个满足即可
  SOME	与ANY等同，使用SOME的地方都可以使用ANY
  ALL	子查询返回列表的所有值都必须满足

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

常用的操作符：=、<>、IN、NOT IN

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

常用的操作符：IN

事务

概述

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

• 默认MySQL的事务时自动提交的，也就是说，当执行一条DML语句时，MySQL会立即隐式的提交事务

相关操作

• 查看/设置事务提交方式

  SELECT @@autocommit； 查看事务的自动提交方式，一为自动提交，零为手动提交

  SET @@autocommit=0；

• 提交事务

  COMMIT；

• 回滚事务

  ROLLBACK；

四大特性

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

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

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

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

并发事务问题

事务隔离级别