釉色鸭的笔记

# 数据库应用（MySQL）

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。MySQL是一种关系型数据库管理系统（RDBMS），采用关系型数据结构，通过像表格一样的结构来表示和管理数据。

以下是关系型数据结构的主要特点和概念：

  • 表: 表是关系型数据库中最基本的数据存储单元。每个表由行和列组成，表中的每一行代表一条记录，每一列代表一个属性或字段
  • 列：列定义了表中每个字段的名称和数据类型。每一列代表实体的一个属性，例如在用户表中，可能有 id、name、email 等列。
  • 行：行是表中的一个具体实例，表示一组相关的数据。每一行对应一个实体的各个属性。
  • 字段：在关系型数据库中，字段指表中的一列（字段和列在数据库设计中是同一个概念的不同称呼。）
  • 主键：主键用于标识表中的每一个行，每一行的主键值必须是唯一的，并且不能为空。
  • 外键：外键用于建立表与表之间的关联关系，外键是一个表中的字段，其值引用另一个表的主键。
  • 复合键：是由多个字段组合而成的主键，用于唯一标识表中的每一行记录。当单个字段无法唯一标识表中的记录时，可以使用多个字段的组合来实现唯一性。
  • 索引：索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。在处理大量数据时可以提高查询的效率。

# 安装 MySQL

下载链接：MySQL Community Downloads (opens new window)

# 管理 MySQL

# 启动或关闭 MySQL 服务器

在 Windows 系统下启动 MySQL 服务器

net start mysql

关闭 MySQL 服务器

net stop mysql

在Linux 系统下启动 MySQL 服务器

sudo systemctl start mysql

如果不行可以尝试 sudo service mysql start

关闭 MySQL 服务器

sudo systemctl stop mysql

或者sudo service mysql stop

重启 MySQL 服务器

sudo systemctl restart mysql

或者使用 service 命令：sudo service mysql restart

检查 MySQL 服务器状态

sudo systemctl status mysql

或者使用 service 命令：sudo service mysql status

# 查看数据库

列出所有可查看的数据库

SHOW DATABASES;

# 查看用户名

查看所有用户

SELECT User FROM mysql.user;

查看用户及其主机信息

SELECT User, Host FROM mysql.user;

# 查看字符集

这个语句可以查询所有的字符集的名称以及与其对应的默认排序规则和最大字符长度

SHOW CHARACTER SET

# 注释

单行注释以 -- 或 # 开头，-- 后面必须跟一个空格，否则会被误认为是 SQL 命令的一部分

-- 创建一个名为 users 的表
# 创建一个名为 users 的表

多行注释以 /* 开头，以 */ 结尾

/*
创建一个名为 users 的表
包含 id、name 和 email 字段
*/

# 连接数据库

使用命令行连接 MySQL 服务器

mysql -u your_username -p

使用命令行连接远程 MySQL 服务器

mysql -u your_username -h remote_host -P port -p
  • -u 参数用于指定用户名，参数后的 your_username 表示用户名
  • -p 若该用户名设置了密码则应当加上这个参数
  • -h 指定远程 MySQL 服务器的地址（IP 地址或域名）remote_host 是服务器地址
  • -P 指定 MySQL 服务器的端口号（默认是 3306）port 表示端口号

示例：

-- 连接本地 MySQL服务器
mysql -u root -p

-- 连接 IP 地址为 192.168.1.100 的远程 MySQL 服务器
mysql -u root -h 192.168.1.100 -P 3306 -p

# 创建数据库

一般使用 CREATE 命令创建数据库，database_name 为要创建的数据库名称

CREATE DATABASE database_name;

提示

  1. 在使用 MySQL 时，每段代码后都要用;结尾
  2. 在创建和删除数据库时需要最高权限，一般使用 root 用户登录

数据库创建的基本语法：

CREATE DATABASE [IF NOT EXISTS] database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name];

用[]括起来的内容为非必要语句，如果不写则会按照默认情况创建数据库，MySQL 8.0+ 版本默认字符集为 utf8mb4，默认排序规则为 utf8mb4_0900_ai_ci

示例：

CREATE DATABASE mydatabase
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

如果所要创建的数据库名称已经存在，执行 CREATE DATABASE 创建数据库将会报错，为了避免报错，可以在语句中添加 IF NOT EXISTS

CREATE DATABASE IF NOT EXISTS mydatabase;

# 使用命令行创建数据库

mysqladmin 是 MySQL 提供的一个用于执行管理任务的命令行工具。通过 mysqladmin 可以执行各种数据库管理和 MySQL 服务器管理的操作

mysqladmin -u your_username -p create database_name
  • -u 参数用于指定 MySQL 用户名
  • your_username 表示用户名
  • -p 若该用户名设置了密码则需要添加这个参数
  • create 是执行的操作，表示创建数据库
  • database_name 是要创建的数据库的名称

查看 MySQL 服务器的状态

mysqladmin -u your_username -p status
  • your_username 表示用户名

# 删除数据库

在删除数据库时需要最高权限，一般使用 root 用户登录。

在 SQL 语句中使用 DROP 命令来删除数据库，格式如下：

DROP DATABASE [IF EXISTS] database_name
  • IF EXISTS 是可选语句，用于在创建数据库前先检查数据库是否存在，可以避免因为数据库不存在而引发错误
  • database_name 表示所要删除的数据库的名称

示例：

-- 删除指定数据库
DROP DATABASE mydatabase;

-- 删除指定数据库（如果数据库不存在则不会报错）
DROP DATABASE IF EXISTS mydatabase;

# 使用命令行删除数据库

mysqladmin -u your_username -p drop database_name
  • -u 参数用于指定 MySQL 用户名
  • your_username 表示用户名
  • -p 若该用户名设置了密码则需要添加这个参数
  • drop 是执行的操作，表示删除数据库
  • database_name 是要创建的数据库的名称

# 选择数据库

在 MySQL 中可以使用 USE 语句来选择要使用的数据库

USE database_name;
  • database_name 表示要选择的数据库的名称

选择数据库后，后续的所有操作都会在这个选择的数据库中执行

# 使用命令行选择数据库

mysql -u your_username -p -D your_database
  • -u 参数用于指定 MySQL 用户名
  • your_username 表示用户名
  • -p 若该用户名设置了密码则需要添加这个参数
  • -D 参数用于指定要选择的数据库, your_database 表示要选择的数据库的名称

# 修改数据库

使用 ALTER DATABASE 语句可以修改数据库的字符集和排序规则。

ALTER DATABASE database_name 
CHARACTER SET = charset_name 
COLLATE = collation_name;
  • database_name：要修改的数据库名称。
  • charset_name：新的字符集，例如 utf8mb4。
  • collation_name：新的排序规则，例如 utf8mb4_general_ci

# 数据类型

MySQL 支持多种数据类型，大致可以分为三类：数值类型、日期时间类型、字符串类型。

# 数值类型

数据类型 大小（bytes） 范围（有符号） 范围（无符号） 说明
TINYINT 1 (-128, 127) (0, 255) 较小的整数值
SMALLINT 2 (-32,768, 32,767) (0, 16,777,215) 整数值
MEDIUMINT 3 (-8,388,608, 8,388,607) (0, 16,777,215) 整数值
INT 4 (-2,147,483,648, 2,147,483,647) (0, 4,294,967,295) 整数值
BIGINT 8 (-9,223,372,036,854,775,808, 9,223,372,036,854,775,807) (0, 18,446,744,073,709,551,615) 较大的整数值
FLOAT 4 (-3.402 823 466 E+38, -1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 351 E+38) 0, (1.175 494 351 E-38, 3.402 823 466 E+38) 单精度浮点数
DOUBLE 8 (-1.797,693,134,862,315,7 E+308, -2.225,073,858,507,201,4 E-308), 0, (2.225,073,858,507,201,4 E-308, 1.797,693,134,862,315,7 E+308) 0, (2.225,073,858,507,201,4 E-308, 1.797,693,134,862,315,7 E+308) 双精度浮点数
  • FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示

定点浮点数类型：

  • 该类型用 DOUBLE 表示，没有固定的范围，范围由实际的精度决定，常用于表示高精度浮点数
  • DOUBLE(M,D) 参数 M 表示有效数字的位数（范围1~65），参数 D 指小数点后的位数（范围0~30且不超出M），不设置参数时默认为DECIMAL(10, 0)
  • 该类型的数据大小由精度决定，若M大于D，为M+2，否则为D+2

# 日期时间类型

数据类型 大小（bytes） 范围 格式 说明
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' YYYY-MM-DD hh:mm:ss 日期时间值
TIMESTAMP 4 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC YYYY-MM-DD hh:mm:ss 时间戳

# 字符串类型

数据类型 范围 说明
CHAR 0 ~ 255 定长字符串
VARCHAR 0 ~ 65,535 变长字符串
TINYBLOB 0 ~ 255 不超过 255 个字符的二进制字符串
TINYTEXT 0 ~ 255 短文本字符串
TEXT 0 ~ 65,535 长文本数据
MEDIUMTEXT 0 ~ 16,777,215 中等长度文本数据
LONGTEXT 0 ~ 4,294,967,295 极大文本数据
BLOB 0 ~ 65,535 二进制形式的长文本数据
MEDIUMBLOB 0 ~ 16,777,215 二进制形式的中等长度文本数据
LONGBLOB 0 ~ 4,294,967,295 二进制形式的极大文本数据

# CHAR

  • CHAR(n) 表示定长数据，占用空间是n个字符，n的取值在0~255之间
  • 存储的字符串长度不满 n 则右端补足空格，在读出时会自动将右端空格去除
  • 存储的字符串长度超过存储范围n，存储不会成功

# VARCHAR

  • VARCHAR(n) 表示变长数据，数据占用空间会根据实际字符串长度加1（自动添加一个字符串的结束标志）
  • 存储字符串在保存和检索时尾部的空格保留

CHAR(4)和VARCHAR(4)的实际存储对比:

值 CHAR(4) 占用空间 VARCHAR (4) 占用空间
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，但它们存储的是二进制字符串而不是非二进制字符串

# BINARY

  • BINARY(n) 指定一个固定长度的二进制字符串，长度为 n 字节。
  • 如果存储的值长度小于 n，则会用 \0（零字节）填充到指定长度，如果超过 n 则会在 n 处截断。 存储：无论实际数据长度如何，都会占用 n 字节的存储空间。

# VARBINARY

  • VARBINARY(n) 类型指定一个可变长度的二进制字符串，最大长度为 n 字节
  • 不会进行填充，存储的实际数据长度即为数据本身的长度
  • 只占用实际数据长度加 1 或 2 字节的存储空间（用于记录长度信息）

提示

数据检索效率：CHAR > VACHAR > TEXT，能不用TEXT就不用TEXT。

枚举类型：

  • ENUM 是一种字符串类型的对象，其值必须从预定义的列表中选择，且只能选其中一个。
  • 一个 ENUM 最多支持 65,535 个元素
  • ENUM中的每个值对应一个索引（从 1 开始）

# 语法

column_name ENUM('value1', 'value2', 'value3', ...)

# 集合类型

  • SET 是一种字符串对象，其值是预定义列表中的零个或多个值的组合。
  • 每个列的值可以包含多个选项，用逗号分隔。
  • 单个 SET 列最多支持 64 个元素

语法：

column_name SET('value1', 'value2', 'value3', ...)

# 创建数据表

创建一个数据表最重要的三个信息：

  • 表名
  • 表字段名（列的名称）
  • 定义每个表字段的数据类型（每一列的数据类型）

语法：

这是创建一个数据表最基本的数据格式

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);
  • table_name 是你要创建的表的名称
  • column1, column2, ... 是表中的列名
  • datatype 是每个列的数据类型

示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

注意

创建数据表时定义的每一个字段后都要加 , 最后一行不用加 ,

  1. 创建一个表名为 users 的数据表
  2. 创建名为 id 的字段，使用 AUTO_INCREMENT 属性，每次插入新记录时自动递增，将该字段设置为主键(PRIMARY KEY)
  3. 创建名为 name 的字段，设置该字段数据类型为 VARCHAR 并将最大长度设置为 100，添加 NOT NULL 约束，确保该字段不能为空.
  4. 创建名为 email 的字段，设置该字段数据类型为 VARCHAR 并将最大长度设置为 255，添加 NOT NULL 约束，确保该字段不能为空；添加 UNIQUE 约束，确保每个邮箱地址在表中是唯一的。
  5. 创建名为 created_at 的字段，设置该字段数据类型为 TIMESTAMP，用于存储日期和时间，设置默认值为 CURRENT_TIMESTAMP

通过以上操作创建出的数据表效果：

id name email created_at

在创建表时可以通过这种方式设置表的字符集和排序规则

CREATE TABLE mytable (
    ...
)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

也可以通过这种方式设置存储引擎（ENGINE）和编码方式（CHARSET）

CREATE TABLE mytable(
   ...
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

在创建数据表时也可以使用 IF NOT EXISTS 语句，避免重复创建或引发错误

CREATE TABLE IF NOT EXISTS (
  ...
);

# 复制数据表结构

这种方式会复制表结构（包括索引、约束等），但不会复制数据。

CREATE TABLE new_table LIKE original_table;

如果需要复制数据可以在新复制的数据表中插入数据

INSERT INTO new_table SELECT * FROM original_table;
  • original_table：原数据表
  • new_table：新复制的数据表

# 查看数据表

# 查看数据库中数据表的名称

方法一：

USE 数据库名; -- 先选择数据库
SHOW TABLES; -- 查看当前数据库中所有的数据表

方法二：

SHOW TABLES FROM 数据库名;

# 查看数据表的结构

方法一：

USE 数据库名; -- 选择数据库
DESC 数据表名 -- 查看数据表的结构

方法二：

SHOW COLUMNS FROM 数据表名 FROM 数据库名;

# 查看表的创建语句

USE 数据库名; -- 选择数据库
SHOW CREATE TABLE 数据表名; -- 查看数据表的创建语句

# 修改数据表

使用 ALTER TABLE 语句修改数据表原有的结构

添加字段：

ALTER TABLE table_name ADD COLUMN col data_type;
  • table_name：数据表名称
  • data_type：数据类型
  • col：要添加的字段

修改字段数据类型：

ALTER TABLE table_name MODIFY COLUMN col data_type;
  • table_name：数据表名称
  • data_type：数据类型
  • col：要修改的字段

修改字段名称：

ALTER TABLE table_name CHANGE COLUMN old_name new_name INT;
  • table_name：数据表名称
  • old_name：旧的字段名
  • new_name：新的字段名

删除字段：

ALTER TABLE table_name DROP COLUMN col;
  • table_name：数据表名称
  • col：要删除的字段

添加主键：

ALTER TABLE table_name ADD PRIMARY KEY (col);
  • table_name: 数据表名称
  • col：要设置的字段

删除主键：

ALTER TABLE table_name DROP PRIMARY KEY;
  • table_name: 数据表名称

修改数据表名称：

ALTER TABLE old_table RENAME TO new_table;
  • old_table：旧的数据表
  • new_table：新的数据表

# 删除数据表

语法：

DROP TABLE [IF EXISTS] table_name;
  • table_name：要删除的数据表名称。
  • IF EXISTS：该语句是可选的，用于检查数据表是否存在。如果表不存在，使用 IF EXISTS 可以避免报错。

示例：

删除名为 mytable 的数据表

DROP TABLE mytable;

也可以同时删除多个数据表

DROP TABLE table1, table2, table3;

# 存储引擎

常用的存储引擎对比

特性 InnoDB MyISAM Memory
事务 支持 不支持 不支持
锁 行级锁 表级锁 表级锁
外键 支持 不支持 不支持
崩溃恢复 支持 不支持 不支持
全文索引 MySQL 5.6+ 支持 支持 不支持

InnoDB：适合高并发场景，例如订单支付、实时交易等重要数据 MyISAM：适合存储大量需要快速查找的数据，读取数据效率高，但一次只能一人查找。 Memory：适合临时会话和存储，数据断电丢失

什么是锁？锁存在的意义是什么？

锁如同十字路口的红绿灯，协调“数据”有序存储，避免出现数据错误，在并发环境下，有序控制资源的访问。MyISAM 和 Memory 使用的是表级锁，在同一时间只能由一个事务操作表，所以不具有并发性，但是比较稳定不会出错；InnoDB 使用的是行级锁，仅锁定当前操作的这一行，可以实现高并发，但有可能会出现死锁（指两个或多个事务在执行过程中，因互相等待对方释放锁，而陷入无限等待的状态）的情况

# 查看支持的存储引擎

SHOW ENGINES;

# 指定表的存储引擎

创建一个名为 table_name 的表，该表的存储引擎为 InnoDB

CREATE TABLE table_name (
  ...
) ENGINE=InnoDB;

# 修改数据表的存储引擎

将数据表 table_name 的存储引擎修改为 MyISAM

ALTER TABLE table_name ENGINE=MyISAM;

# 约束

# 主键

将某字段设置为主键，主键起唯一标识表中每行数据的作用，主键的值不能为空

示例：

将字段 id 设置为主键，一个表中只要一个字段可以为主键，用 PRIMARY KEY 表示

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

# 外键

  • 外键是用于与其他表建立关系的字段
  • 外键引用的字段必须是唯一的，通常是另一个表的主键
  • 外键可以有多个
  • 外键用语句 FOREIGN KEY 表示

示例：

  • user_id 当前表的字段
  • users 要引用字段所在的表
  • id 引用的字段

将 user_id 设置为外键，引用数据表 users 中的字段 id

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE  -- 级联删除关联记录
);

外键的其他操作：

操作 说明
ON DELETE CASCADE 当主表中的某条记录被删除时，自动删除从表中所有与之关联的记录
ON DELETE RESTRICT 阻止删除主表记录（若存在关联从表记录），默认行为
ON DELETE SET NULL 主表记录删除时，从表关联字段设为 NULL（字段需允许 NULL 时）
ON UPDATE CASCADE 主表主键更新时，从表外键字段同步更新（如修改 users.id 的值时联动）

# 唯一约束

确保字段中的值是唯一的，用 UNIQUE 表示

示例：

CREATE TABLE employees (
    email VARCHAR(100) UNIQUE,
    ...
);

# 非空约束

要求该字段中的值不能为空，用 NOT NULL 语句表示

示例：

CREATE TABLE products (
    name VARCHAR(100) NOT NULL,
    price DECIMAL NOT NULL
);

# 默认值

插入数据时，若未指定值，自动填充默认值。用 DEFAULT 语句表示

示例：

表示如果插入数据时没有指定字段 created_at 的值，将自动使用当前的日期和时间作为默认值。 （CURRENT_TIMESTAMP 是一个 MySQL 中的一个函数，用于表示当前的日期和时间）

CREATE TABLE logs (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ...
);

# 检查约束

限制字段中的值的范围，用 CHECK () 语句表示 () 中的是限制范围

示例：

将字段 age 的限制在为小于等于 18 的整数

CREATE TABLE students (
    age INT CHECK (age >= 18),
    ...
);

# 自增约束

自动生成唯一递增值（通常与主键搭配），用 AUTO_INCREMENT 语句表示

示例：

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ...
);

# 查询数据

MySQL 查询数据的语句：

  • SELECT：指定要查询的列名称
  • FROM：指定要查询的表
  • WHERE：指定要查询的条件
  • GROUP BY：子句用于对查询结构进行分组
  • HAVING：指定分组的条件
  • ORDER BY：用于对查询结果的进行排序
  • LIMIT：限制查询的输出结果的行数

# 查询表中所有数据

  • SELECT 后面跟着的是要查询的字段
  • * 是通配符表示查询所有字段
  • FROM 后面跟着的是所查询的数据表
SELECT * FROM table_name;

# 查询指定字段

查询数据表 table_name 中的字段 a 和 b 这两列的数据

SELECT a, b FROM table_name;

# 条件查询

  • WHERE 后跟着的是筛选条件

查询数据表 table_name 中字段 a 的数据，并筛选出满足条件 c 的数据

SELECT a FROM table_name 
WHERE c;

# 排序查询

  • ORDER BY 是排序语句，ASC 表示升序排序，DESC 表示降序排序
  • column 是指定的字段，按该列的值进行排序

语法：

ORDER BY column [ASC | DESC]

示例：

查询数据表 table_name 中的所有数据，按照字段 column2 升序排序查询结果

SELECT * FROM table_name 
ORDER BY column2 ASC;

查询数据表 table_name 中的字段 column1 的数据，按照字段 column2 降序排序查询结果

SELECT column1 FROM table_name 
ORDER BY column2 DESC;

# 分组查询

分组查询常用于分类统计，例如计算每个类别的总数、平均值、最大值、最小值等

  • column1:：所要查询的字段
  • column2：查询是会根据 column2 进行分组
  • table_name：所要查询的数据表
  • COUNT(*) 表示每个分组中的对象数（行数）

查询数据表 table_name 中的字段 column1 和每个分组中的行数 COUNT(*)，按照字段 column2 进行分组

SELECT column1, COUNT(*) FROM table_name 
GROUP BY column2;