31. 数据库基础

1. 数据库基础知识

1.1 关系型数据库与非关系型数据库

1.2 关系型数据库的结构

库 Database

库，也称数据库，用于组织、存储和管理数据

类比于文件夹

表 Table

表，是数据库中基本的数据存储单位，由行（Row）和列（Column）组成

类比于excel文件

记录 Record

记录，是表中的一行的所有数据

例如：在一个订单表中，每一条记录可能代表一个订单所有信息，包含订单号、订单日期、订单金额等。

表头 Header

表头，是表格中列的名称

可以看作是数据的结构化信息，帮助用户理解和操作数据

表单 Form

严格来说不直接属于数据库结构的一部分，但表单是一种常用的界面元素，用于收集并展示数据。

表单可以用于输入、修改或查询数据库中的数据。

表单通常包含多个字段，每个字段对应数据库表中的一个列。用户通过填写表单并提交，可以将数据填写到数据库中或更新现有数据。

2. SQL语句基础概念

2.1 SQL语句规范

SQL语句不区分大小写，为了提高代码的可读性，建议采用统一的书写规范。

关键字大写：SELECT, FROM, WHERE, JOIN。

表名、列名小写，用双引号括起来以确保大小写的敏感性。

字符串与日期：字符串与日期类型的数据应使用单引号' '括起来，单词之间用英文半角空格隔开。

SQL语句可单行或多行书写，默认以英文半角分号 ; 结尾，关键词不能跨多行

单行注释：--或#

多行注释：

注释内容

2.2 SQL语句类型

根据用途，SQL语句可以分为几种主要类型：

1. 数据查询语言 (DQL Data Query Language)
用于从一个或多个表中检索数据。
主要命令是 SELECT。

2. 数据定义语言 (DDL Data Definition Language)
用于定义或改变数据库结构。
常用命令包括：
CREATE：创建数据库、表、视图等。
ALTER：修改现有数据库对象的结构。
DROP：删除数据库对象。
TRUNCATE：清除表中的所有数据但保留表结构。
RENAME：重命名数据库对象（某些数据库系统可能使用不同的语法）。

3. 数据操纵语言 (DML Data Manipulation Language)
用于添加、更新和删除数据库中的数据。
常用命令包括：
INSERT：向表中插入新记录。
UPDATE：更新表中的现有记录。
DELETE：从表中删除记录。
MERGE：在某些数据库中，用于基于条件更新或插入数据。

4. 事务控制语言 (TCL Transaction Control Language)
用于管理事务，确保数据的一致性和完整性。
常用命令包括：
COMMIT：提交当前事务，使更改永久化。
ROLLBACK：撤销当前事务，恢复到事务开始前的状态。
SAVEPOINT：设置保存点，允许部分回滚事务。
SET TRANSACTION：设置事务的特性（如只读、隔离级别等）。

5. 数据控制语言 (DCL Data Control Language)
用于控制数据库访问权限。
常用命令包括：
GRANT：授予用户特定权限。
REVOKE：撤销用户的特定权限。

6. 其他命令
还有一些其他的命令不属于上述分类，例如：
EXPLAIN：显示查询计划，帮助优化查询性能。
SHOW：显示数据库信息，如表结构、索引等。
USE：选择当前工作的数据库。
BEGIN 或 START TRANSACTION：开始一个新的事务。

2.3 常用命令

3. SQL语句操作库、表、数据

3.1 操作数据库(文件夹级别)---对库的增删改查

[1]创建数据库

方法一：create database 数据库名称;

方法二：create database if not exists 数据库名称;　　　　

　　　　--数据库不存在则创建，存在则忽略

方法三：create database if not exists 数据库名称 charset 字符编码名称;

　　　　--latin1：西欧语言字符集

　　　　--utf8mb4（当前中文默认）：完整的utf-8编码，支持所有Unicode字符，包括表情符号，一个字符最多占4个字节

　　　 --gbk：简体中文字符集

[2]查看数据库

系统自带的数据库：

查看所有数据库：

show databases;

模糊查询，查看名称中包含指定字符的数据库：

show databases like "%obj%";

查看数据库的建库SQL语句：

show create database 数据库名称;

示例：

show create database test01;

show create database test02;

show create database test03;

[3]修改数据库

修改数据库默认的编码集：

alter database 数据库名称 charset "utf8mb4";

举例：将test03的字符集由gbk改为utf8mb4

[4]删除数据库

以下MySQL自带的数据库不能删除

drop database [if exists] 数据库名称;　　　　--中括号里的内容表示可选

3.2 操作数据库中的表(文件级别)---对表的增删改查

[1]操作表之前的操作：进入到指定库、查看所在的库

use 库名称;　　　　--进入到指定库

注意：进入到指定数据库后无法退回到之前刚登录的状态，但可以通过use进行切换

select database();　　　　--查看所在的库

[2] 创建表

语法：

create table 表名(
       列名1 数据类型[(最多字符个数)]  [约束条件],
       列名2 数据类型[(最多字符个数)]  [约束条件],
       ...
       列名n 数据类型[(最多字符个数)]  [约束条件]
);

列名和数据类型必填，中括号里的内容选填。

列名不可重复。

常见数据类型：

int：整数　　括号内的参数默认为11，是显示宽度，不影响存储大小，不推荐使用

　　　　有符号 INT
　　　　位数: 32 位　　4个字节
　　　　最小值: -2,147,483,648 (-2^31)
　　　　最大值: 2,147,483,647 (2^31 - 1)
　　　　无符号 INT
　　　　位数: 32 位　　4个字节
　　　　最小值: 0
　　　　最大值: 4,294,967,295 (2^32 - 1)

varchar(n)：变长字符串，n是字符串的最大长度　　1~255，默认宽度为1，只能存1个字符
text：长文本
date：日期
datetime：日期和时间
decimal(m, n)：小数，m是总位数，n是小数点后的位数

常见约束条件：

not null：不能为空值
primary key：主键，列（或列组合的）唯一标识，每个表可以有一个主键
unique：列的值必须唯一
default value：列的默认值
auto_increment：自增，通常用于主键，自动生成唯一的数字；一般与主键配合使用
check：检查约束，确保列中的值满足特定条件；MySQL 8.0.16之前的版本要使用触发器才能实现check的约束功能
foreign key：外键，用于在两个表之间建立连接，确保参照完整性

主键写法一：

create table product1(
      id int auto_increment primary key,
      name varchar(10) not null,
      unique(name),
      price decimal(10, 2),
      stock int default 20
);

主键写法二：

create table product2( 
       id int auto_increment,
       name varchar(10) not null,
       unique(name),
       price decimal(10, 2),
       stock int default 20,
       primary key(id)
);