【MySQL】基础知识 上
一、MySQL基础入门
1. 什么是数据库？
数据库是按照数据结构来组织、存储和管理数据的仓库。简单说，它就是存放数据的“电子文件柜”。而MySQL是目前最流行的开源关系型数据库管理系统（RDBMS），广泛用于Web应用、数据分析等领域。
数据库管理系统（DBMS）的核心作用
DBMS（Database Management System）是位于用户与操作系统之间的一层数据管理软件。它的主要功能包括：
· 数据定义：创建、修改、删除数据库对象（表、索引、视图等）
· 数据操纵：对数据进行增、删、改、查
· 数据安全：用户认证、权限控制
· 数据完整性：保证数据符合约束规则
· 并发控制：处理多用户同时访问
· 故障恢复：保证数据不丢失
常见的DBMS有：MySQL、Oracle、SQL Server、PostgreSQL等。
MySQL简介
MySQL 是最流行的开源关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。
MySQL的逻辑结构从上到下为：
数据库层级结构
├── 服务器（Server）
│ └── 数据库（Database）
│ └── 数据表（Table）
│ ├── 列（Column）
│ └── 行（Row）
使用MySQL的一般步骤：
1. 安装MySQL服务端和客户端
2. 启动MySQL服务
3. 使用客户端（命令行或图形工具）连接服务器
4. 执行SQL语句操作数据库
2. 什么是SQL？
SQL (Structured Query Language) 是结构化查询语言，专门用来操作关系型数据库。它就像数据库的“通用普通话”——无论MySQL、Oracle还是SQL Server，都使用SQL作为标准语言。不过不同数据库在细节上有差异，称为“方言”。
3. SQL通用语法规则
语句可以单行或多行书写，以分号结尾
SELECT * FROM student;
SELECT *
FROM student;
可使用空格和缩进增强可读性（MySQL会忽略多余空白）
SELECT
name,
age
FROM
student;
不区分大小写，但关键字建议使用大写，便于区分
-- 以下三种写法效果相同
SELECT * FROM student;
select * from student;
SeLeCt * FrOm student;
注释方式
· 单行注释：-- 注释内容（注意--后面有一个空格）
· 单行注释（MySQL特有）：# 注释内容
· 多行注释：/* 注释内容 */
-- 查询学生表全部数据
SELECT * FROM student; -- 单行注释示例
/*
多行注释示例
支持换行显示
*/
4. SQL的四大分类
| 分类 | 全称 | 作用 | 常用关键字 |
|---|---|---|---|
| DDL | 数据定义语言 (Data Definition Language) | 定义数据库、表和列的结构 | CREATE, DROP, ALTER |
| DML | 数据操作语言 (Data Manipulation Language) | 对表中的数据进行增删改操作 | INSERT, UPDATE, DELETE |
| DQL | 数据查询语言 (Data Query Language) | 查询表中的数据 | SELECT, WHERE, ORDER BY |
| DCL | 数据控制语言 (Data Control Language) | 管理权限和用户控制 | GRANT, REVOKE |
二、数据库与表的核心操作（DDL）
1. 数据类型详解
MySQL中的数据类型决定了列可以存储什么类型的数据，以及占用的存储空间。选择合适的数据类型可以提高存储效率和查询性能。
数值类型
| 类型 | 大小 | 说明 |
|---|---|---|
| INT | 4字节 | 整数类型，取值范围约±21亿 |
| DOUBLE(m,d) | 8字节 | 双精度浮点数，m表示总位数，d表示小数位数 |
| DECIMAL(m,d) | 可变 | 高精度小数类型，适用于金融计算场景 |
示例：
CREATE TABLE example_int (
age TINYINT, -- 存储年龄信息
score SMALLINT, -- 记录分数值
population INT, -- 保存人口数量
big_id BIGINT -- 存储大数据量标识符
);
-- 使用 FLOAT/DOUBLE 类型可能导致精度损失
CREATE TABLE example_float (
price FLOAT(8,2), -- 8位总长度，保留2位小数
rate DOUBLE(6,4) -- 6位总长度，保留4位小数
);
-- DECIMAL 类型适用于需要精确计算的场景（如金额）
CREATE TABLE account (
balance DECIMAL(10,2) -- 10位总长度（8位整数+2位小数）
);
日期与时间类型
| 类型 | 格式 | 范围 | 说明 |
|---|---|---|---|
| DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-31 | 仅存储日期 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 同上 | 存储日期和时间 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 ~ 2038-01-19 | 时间戳格式，受时区影响 |
DATE、DATETIME、TIMESTAMP的区别与使用
CREATE TABLE example_date (
birth DATE, -- 存储生日日期，格式：YYYY-MM-DD（如1990-05-20）
created_at DATETIME, -- 记录创建时间，包含日期和时间（如2024-01-15 14:30:00）
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 自动记录最后更新时间
);
字符串类型
| 类型 | 说明 |
|---|---|
| CHAR(n) | 定长字符串，固定长度为n，存取效率较高 |
| VARCHAR(n) | 变长字符串，最大长度为n，存储空间利用率高（最常用的字符串类型） |
| TEXT | 长文本类型，适用于存储文章、评论等大段文字内容 |
CHAR vs VARCHAR：定长与变长的选择
· CHAR(n)：固定长度，存储时右侧补空格，检索时去掉。适合长度固定的数据，如性别（CHAR(1)）、MD5密码（CHAR(32)）。性能略高。
· VARCHAR(n)：变长，实际内容占多少就存多少，额外用1~2字节记录长度。适合长度变化大的数据，如姓名、地址。
CREATE TABLE student (
name VARCHAR(32) -- 存储学生姓名，最大长度为32个字符
);
其他数据类型
二进制类型
| 类型 | 说明 |
|---|---|
| BINARY(n) | 固定长度的二进制数据，占用n个字节 |
| VARBINARY(n) | 可变长度的二进制数据 |
| BLOB | 二进制大对象，适用于存储图片、文件等大型二进制数据 |
JSON类型（MySQL 5.7+）
MySQL原生支持JSON数据类型，可以高效存储和查询JSON文档。
2. DDL：数据库的创建与管理
DDL（Data Definition Language）用于定义数据库的结构。以下操作都是对数据库本身的增删改查。
创建数据库
-- 基础创建语句
CREATE DATABASE school;
-- 安全创建（避免重复创建报错）
CREATE DATABASE IF NOT EXISTS school;
-- 创建时指定字符编码
CREATE DATABASE school CHARACTER SET utf8mb4;
综合练习
-- 创建db4数据库（如果不存在），并设置字符集为GBK
CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;
字符集建议：现代开发统一使用 utf8mb4，它是 utf8 的超集，支持4字节Unicode字符。
查看数据库
查看所有数据库
SHOW DATABASES;
输出示例：
+--------------------+
| 数据库名称 |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
查看数据库创建语句（含字符集）
-- 查看school数据库的创建语句
SHOW CREATE DATABASE school;
输出示例：
+----------+--------------------------------------------------------------+
| Database | Create Database Statement |
+----------+--------------------------------------------------------------+
| school | CREATE DATABASE `school` DEFAULT CHARSET=utf8mb4 |
+----------+--------------------------------------------------------------+
修改数据库字符集
-- 修改school数据库的字符集为GBK
ALTER DATABASE school CHARACTER SET gbk;
删除数据库
-- 删除数据库（安全方式）
DROP DATABASE IF EXISTS school;
-- 强制删除（不检查是否存在）
DROP DATABASE school;
警告：删除数据库是不可逆操作，所有表和数据都会丢失，生产环境请谨慎。
使用数据库
USE school; -- 切换到school数据库
SELECT DATABASE(); -- 查询当前使用的数据库名称
-- 或
SELECT SCHEMA();如果没有选择任何数据库，返回 NULL。
3.DDL：表的创建与管理
表是数据库中存储数据的核心单位。
语法：
CREATE TABLE table_name (
column1 data_type1 [constraints],
column2 data_type2 [constraints],
...
);
注意：最后一列后面不需要加逗号。
示例：
CREATE TABLE student (
id INT,
name VARCHAR(32),
age INT,
score DECIMAL(4,1),
birthday DATE,
insert_time TIMESTAMP
);
使用IF NOT EXISTS避免重复创建
CREATE TABLE IF NOT EXISTS student (
student_id INT,
student_name VARCHAR(32)
);
复制表：
-- 创建空表（仅复制表结构）
CREATE TABLE student_copy LIKE student;
-- 创建表并复制全部数据
CREATE TABLE student_backup AS SELECT * FROM student;
AS SELECT 方式只会复制数据和列定义，不会复制索引、主键、外键等约束。
查看表信息
-- 查询当前数据库中的所有表
SHOW TABLES;
-- 查看student表的结构信息
DESC student;
-- 或
DESCRIBE student;
-- 获取student表的完整创建语句（包含字符集、存储引擎等详细信息）
SHOW CREATE TABLE student;
输出演示：
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(32)| YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
修改表结构
-- 重命名表
ALTER TABLE student RENAME TO stu;
-- 另一种重命名语法
RENAME TABLE stu TO student;
-- 新增列
ALTER TABLE student ADD COLUMN address VARCHAR(100);
-- 修改列数据类型
ALTER TABLE student MODIFY COLUMN address VARCHAR(200);
-- 修改列名及数据类型
ALTER TABLE student CHANGE COLUMN address addr VARCHAR(150);
-- 删除列
ALTER TABLE student DROP COLUMN addr;
-- 修改表字符集
ALTER TABLE student CHARACTER SET utf8mb4;
删除表
-- 删除 student 表（如果存在）
DROP TABLE IF EXISTS student;
-- 强制删除 student 表（无论是否存在）
DROP TABLE student;
三、表的约束
约束是对表中数据的限制，保证数据的正确性、有效性和完整性。就像表格的“规则”。
1. 非空约束 NOT NULL
确保列不允许为NULL值。
-- 创建表时设置非空约束
CREATE TABLE user (
id INT,
name VARCHAR(20) NOT NULL
);
-- 表创建后添加非空约束（注意：若列存在NULL值将执行失败）
ALTER TABLE user MODIFY name VARCHAR(20) NOT NULL;
-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);
2. 唯一约束 UNIQUE
确保列中的所有值都不重复（允许多个NULL值，因为NULL不等于任何值）。
-- 创建表时添加唯一约束
CREATE TABLE user (
id INT,
phone VARCHAR(20) UNIQUE
);
-- 通过修改表结构添加唯一约束
ALTER TABLE user MODIFY phone VARCHAR(20) UNIQUE;
-- 删除唯一约束（需指定约束名称）
ALTER TABLE user DROP INDEX phone;
3. 主键约束 PRIMARY KEY
非空 + 唯一
一张表只能有一个主键
主键是表中记录的唯一标识
-- 创建表时设置主键
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 表创建后添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;
-- 移除主键约束（注意：不会自动取消NOT NULL属性）
ALTER TABLE student DROP PRIMARY KEY;
自增长列 AUTO_INCREMENT
通常结合主键使用，自动为插入的每条新记录生成唯一数字
-- 创建学生表，包含自增主键ID和姓名字段
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- 移除ID字段的自增属性
ALTER TABLE student MODIFY id INT;
-- 为ID字段重新添加自增属性
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
4. 外键约束 FOREIGN KEY
外键用来建立两张表之间的联系。例如：员工表(dept_id) 关联 部门表(id)。
准备工作：
-- 部门表（主表）
CREATE TABLE dept (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 员工表（从表），设置外键关联部门表
CREATE TABLE emp (
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id)
);
添加和删除外键：
-- 移除现有外键约束
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;
-- 添加新的外键约束
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES dept(id);
级联操作（慎用，会联动删除或更新）：
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id)
REFERENCES dept(id)
ON UPDATE CASCADE
ON DELETE CASCADE;
四、DML数据操纵语言
DML负责对表中数据进行增、删、改操作。
1. 插入数据 INSERT
基本语法：
-- 指定列插入
INSERT INTO student (name, age, gender, english, math, birthday)
VALUES ('张三', 20, '男', 85, 92, '2004-05-10');
-- 全列插入
INSERT INTO student VALUES (2, '李四', 22, '女', 78, 88, '2002-03-15');
-- 批量插入
INSERT INTO student (name, age, gender, english, math, birthday) VALUES
('王五', 21, '男', 90, 95, '2003-07-20'),
('赵六', 19, '女', 88, 91, '2005-11-02');
注意事项：
- 字符串和日期要用引号（单双引号均可）。
- 自增列（如id）可写NULL或0，MySQL会自动生成新值。
- 如果插入NULL到NOT NULL列会报错。
INSERT ... SELECT（从其他表导入数据）：
-- 将年龄大于20岁的学生数据备份到student_backup表
INSERT INTO student_backup
SELECT * FROM student
WHERE age > 20;
2. 修改数据 UPDATE
-- 批量更新所有学生年龄（慎用，会修改全部记录）
UPDATE student SET age = 18;
-- 条件更新：仅修改特定学生的年龄
UPDATE student SET age = 19 WHERE name = '张三';
-- 多字段更新：同时修改英语和数学成绩
UPDATE student SET english = 100, math = 100 WHERE id = 1;
忘记写WHERE会更新整张表，务必小心！
3. 删除数据 DELETE 与 TRUNCATE
-- 删除指定姓名的学生记录
DELETE FROM student WHERE name = '李四';
-- 删除表中所有记录（逐行删除操作，支持事务回滚但效率较低）
DELETE FROM student;
-- 快速清空整张表（通过删除重建实现，效率高但不支持回滚）
TRUNCATE TABLE student;
区别对比：
| 操作 | 原理 | 自增列是否重置 | 能否回滚 | 效率 |
|---|---|---|---|---|
| DELETE | 逐行删除记录 | 不重置 | 可以 | 慢 |
| TRUNCATE | 删除表后重建表结构 | 重置 | 不可以 | 快 |
生产环境删除全部数据一般用TRUNCATE，但请三思。
五、DQL数据查询语言
查询是SQL中最常用、最灵活的部分。先看一个完整的SELECT语句结构：
SELECT [DISTINCT] 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段
HAVING 分组后条件
ORDER BY 排序字段 [ASC|DESC]
LIMIT 起始索引, 每页条数;
执行顺序并非书写顺序，而是：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
1. 基础查询
-- 查询所有学生信息
SELECT * FROM student;
-- 查询特定字段：姓名、年龄和性别
SELECT name, age, gender FROM student;
-- 查询不重复的年龄数据
SELECT DISTINCT age FROM student;
-- 计算英语和数学总分
SELECT name, english, math, english + math AS total FROM student;
-- 使用字段别名（AS关键字可选）
SELECT name AS 姓名, age 年龄 FROM student;
NULL问题：任何数与NULL运算结果都为NULL。使用IFNULL(列, 默认值)处理：
SELECT name, english + COALESCE(math, 0) AS total_score FROM student;
2. 条件查询 WHERE
比较运算符
| 运算符 | 说明 |
|---|---|
| =, <, >, <=, >=, <>（或!=） | 比较运算符：等于、小于、大于等 |
| BETWEEN ... AND ... | 范围查询（包含边界值） |
| IN(集合) | 匹配集合中的任一值 |
| LIKE | 字符串模糊匹配 |
| IS NULL / IS NOT NULL | 空值判断 |
| AND / OR / NOT | 逻辑运算符：与、或、非 |
示例：
-- 筛选年龄超过20岁的学生记录
SELECT * FROM student WHERE age > 20;
-- 查询年龄在20至25岁之间的学生（两种等效写法）
SELECT * FROM student WHERE age BETWEEN 20 AND 25;
SELECT * FROM student WHERE age >= 20 AND age <= 25;
-- 查找姓名为张三或李四的学生（两种查询方式）
SELECT * FROM student WHERE name = '张三' OR name = '李四';
SELECT * FROM student WHERE name IN ('张三', '李四');
-- 查询英语成绩为空的记录（注意：必须使用IS NULL而非= NULL）
SELECT * FROM student WHERE english IS NULL;
-- 查询英语成绩非空且数学成绩高于80分的学生
SELECT * FROM student WHERE english IS NOT NULL AND math > 80;
模糊查询 LIKE
%：代表任意多个字符（包括0个）
_：代表任意一个字符
-- 查询所有姓"张"的学生记录
SELECT * FROM student WHERE name LIKE '张%';
-- 查找姓名中第二个字为"三"的学生
SELECT * FROM student WHERE name LIKE '_三%';
-- 检索姓名中包含"花"字的学生信息
SELECT * FROM student WHERE name LIKE '%花%';
-- 查询姓名恰好为三个汉字的学生
SELECT * FROM student WHERE name LIKE '___';
3. 排序查询 ORDER BY
-- 按年龄升序排列（ASC可省略）
SELECT * FROM student ORDER BY age;
-- 按英语成绩降序排列
SELECT * FROM student ORDER BY english DESC;
-- 复合排序：先按年龄升序，年龄相同时按数学成绩降序
SELECT * FROM student ORDER BY age, math DESC;
4. 聚合函数
聚合函数对一组值进行计算并返回单个值，常用有：
| 函数 | 功能描述 | 使用示例 |
|---|---|---|
| COUNT(列) | 统计非空行数 | COUNT(*) 统计所有行数 |
| MAX(列) | 最大值 | MAX(math) 数学最高分 |
| MIN(列) | 最小值 | MIN(english) 英语最低分 |
| SUM(列) | 计算总和 | SUM(english + math) 总分 |
| AVG(列) | 计算平均值 | AVG(age) 平均年龄 |
注意：聚合函数自动忽略NULL值。
-- 统计学生总人数
SELECT COUNT(*) FROM student;
-- 获取英语科目最高分
SELECT MAX(english) FROM student;
-- 计算学生平均年龄
SELECT AVG(age) FROM student;
-- 汇总所有学生英语和数学成绩总和
SELECT SUM(english + math) FROM student;
5. 分组查询 GROUP BY
分组查询通常和聚合函数配合使用，对每组数据进行统计。
基本语法：GROUP BY 分组字段
-- 按性别分组统计学生人数及平均年龄
SELECT
gender,
COUNT(*) AS student_count,
AVG(age) AS avg_age
FROM
student
GROUP BY
gender;
核心规则：
出现在SELECT后的非聚合列，必须出现在GROUP BY中。
分组前过滤用WHERE，分组后过滤用HAVING。
-- 按性别分组计算平均年龄，筛选出平均年龄超过20岁的组
SELECT gender, AVG(age) AS avg_age
FROM student
GROUP BY gender
HAVING avg_age > 20;
-- 先筛选18岁以上学生，再按性别分组统计人数，最后保留人数超过2的组
SELECT gender, COUNT(*) AS cnt
FROM student
WHERE age > 18
GROUP BY gender
HAVING cnt > 2;
6. 分页查询 LIMIT
当数据很多时，分页显示可以提升体验。
语法：LIMIT 起始索引，每页条数，起始索引从0开始。
分页公式：起始索引 = (当前页码 - 1) × 每页条数
-- 查询第1页数据，每页显示3条记录
SELECT * FROM student LIMIT 0, 3;
-- 查询第2页数据，每页显示3条记录
SELECT * FROM student LIMIT 3, 3;
-- 查询第3页数据，每页显示3条记录
SELECT * FROM student LIMIT 6, 3;
实战案例
查询所有女生，按数学成绩降序排列，只显示前3名，并列出她们的姓名、数学成绩、英语成绩。
-- 查询女生中数学成绩前三名的学生姓名及成绩
SELECT name, math, english
FROM student
WHERE gender = '女'
ORDER BY math DESC
LIMIT 3;
openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构
更多推荐
11
0- 0
已为社区贡献1条内容
所有评论(0)