【数据库原理】基础篇：MySQL基础入门与实战指南

前言

在当今数字化时代，数据已成为企业运营的核心资产之一。而MySQL作为一款广泛使用的开源关系型数据库管理系统，凭借其高性能、可靠性和易用性，成为众多开发者和企业的首选。本文将从MySQL的基础概念出发，逐步深入到实际操作，涵盖数据库的创建、表的定义、数据的增删改查、多表查询以及事务管理等核心内容。无论是初学者还是有一定基础的开发者，都能从本文中获得实用的知识和技能。

一、数据库基础概念

在深入学习MySQL之前，我们先来了解一下数据库的一些基本概念。

1. 数据库（Database）

数据库是一个存储数据的仓库，数据以有组织的方式进行存储。它类似于一个电子表格，但功能更强大，能够处理海量的数据。数据库的主要作用是高效地存储和检索数据。

2. 数据库管理系统（DBMS）

数据库管理系统是操纵和管理数据库的大型软件。它提供了对数据库进行操作的各种功能，如数据的增删改查等。MySQL就是一种常见的数据库管理系统，它通过SQL语言与用户进行交互。

3. SQL语言

SQL（Structured Query Language）是操作关系型数据库的编程语言。它定义了一套操作关系型数据库的统一标准，无论是MySQL、Oracle还是其他关系型数据库，都使用SQL语言进行操作。SQL语言分为以下几个部分：
DDL（Data Definition Language）：数据定义语言，用于定义数据库对象，如数据库、表、字段等。
DML（Data Manipulation Language）：数据操作语言，用于对数据库表中的数据进行增删改操作。
DQL（Data Query Language）：数据查询语言，用于查询数据库中表的记录。
DCL（Data Control Language）：数据控制语言，用于管理数据库用户和控制访问权限。

二、MySQL简介

MySQL是一种开源免费的中小型数据库管理系统，具有高性能、可靠性和易用性。它被广泛应用于各种应用程序中，从简单的网站到复杂的企业级应用。

1. MySQL版本

MySQL官方提供了两种版本：
社区版（MySQL Community Server）：免费，但MySQL不提供技术支持。
商业版（MySQL Enterprise Edition）：收费，官方提供技术支持。
本教程采用的是MySQL最新的社区版——MySQL Community Server 8.0.26。

2. MySQL安装

安装MySQL非常简单，只需要从官网下载安装包，按照提示进行安装即可。以下是详细的安装步骤：
下载MySQL
访问MySQL官网下载页面：MySQL Downloads，选择适合你操作系统的安装包进行下载。

安装MySQL
双击下载的安装包文件，启动安装程序。
按照安装提示进行操作，安装MySQL的相关组件。这个过程可能需要耗时几分钟，请耐心等待。
在安装过程中，输入MySQL中root用户的密码，并记住该密码，后续连接数据库时会用到。

配置环境变量
安装完成后，还需要配置环境变量，以便在任何目录下都能连接MySQL。
在“此电脑”上，右键选择“属性”。
点击左侧的“高级系统设置”，选择“环境变量”。
找到“Path”系统变量，点击“编辑”。
选择“新建”，将MySQL Server的安装目录下的bin目录添加到环境变量。

MySQL启动与连接
MySQL安装完成后，会自动启动服务。我们可以通过命令行工具连接MySQL，也可以使用图形化界面工具（如DataGrip）进行操作。
启动和停止MySQL服务
以管理员身份运行cmd，进入命令行执行如下指令：

net start mysql80  # 启动MySQL服务
net stop mysql80   # 停止MySQL服务

注意：mysql80是我们在安装MySQL时默认指定的系统服务名，如果你未改动，默认就是mysql80。
使用命令行工具连接MySQL

mysql -u root -p

输入root用户的密码后，即可连接到MySQL服务器。
使用图形化界面工具连接MySQL
推荐使用DataGrip，它是一款轻量级，功能强大的MySQL图形化界面工具，支持多种数据库操作，能够显著提高开发效率。

三、MySQL数据模型

MySQL是一种关系型数据库，基于二维表存储数据。一个数据库服务器可以创建多个数据库，一个数据库中可以包含多张表，而一张表中可以包含多行记录。

1. 关系型数据库特点

使用表存储数据：格式统一，便于维护。
使用SQL语言操作：标准统一，使用方便。

2. 数据模型结构

通过MySQL客户端连接数据库管理系统DBMS（Data Base Management System），然后通过DBMS操作数据库。具体的结构如下图所示：

四、SQL语言基础

SQL语言是操作关系型数据库的核心工具，它分为以下几个部分：

1. DDL（Data Definition Language）

DDL用于定义数据库对象，如数据库、表、字段等。常见的DDL语句包括CREATE、DROP、ALTER等。

创建数据库

CREATE DATABASE datatext;

查询数据库

SHOW DATABASES;

创建表（要先在指定的数据库下才能创建，使用命令：use 数据库;）

CREATE TABLE jd_user (id INT COMMENT '编号',name VARCHAR(50) COMMENT '姓名',age INT COMMENT '年龄',gender VARCHAR(1) COMMENT '性别'
) COMMENT '用户表';

查询表结构

DESC 表名;

查询指定表的建表语句

SHOW CREATE TABLE 表名;

删除数据库

DROP DATABASE itcast;

删除表

DROP TABLE (if exists) jd_user; 删除表
TRUNCATE TABLE jd_user;删除指定表，并重新创建该表

表操作-案例 设计一张员工信息表，要求如下：(不懂mysql数据类型可以先看：五、MySQL数据类型）

1. 编号（纯数字）
2. 员工工号 (字符串类型，长度不超过10位)
3. 员工姓名（字符串类型，长度不超过10位）
4. 性别（男/女，存储一个汉字）
5. 年龄（正常人年龄，不可能存储负数）
6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
7. 入职时间（取值年月日即可）

create table emp(
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
) comment '员工表';

修改表结构

ALTER TABLE emp ADD nickname VARCHAR(20) COMMENT '昵称'; 添加字段
ALTER TABLE emp MODIFY age TINYINT UNSIGNED COMMENT '年龄'; 修改数据类型
ALTER TABLE emp CHANGE nickname username varchar(30) comment '用户名'; 修改字段名和字段类型
ALTER TABLE emp DROP username; 删除字段
ALTER TABLE emp RENAME TO employee; 修改表名

案例:

为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)

五、MySQL数据类型

MySQL支持多种数据类型，包括数值类型、字符串类型和日期时间类型。

1. 数值类型

2. 字符串类型

注意：char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和
字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性
能会更高些

如：
1). 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)

3. 日期时间类型

如:
1). 生日字段 birthday
birthday date
2). 创建时间 createtime
createtime datetime

表操作-案例 设计一张员工信息表，要求如下：

1. 编号（纯数字）
2. 员工工号 (字符串类型，长度不超过10位)
3. 员工姓名（字符串类型，长度不超过10位）
4. 性别（男/女，存储一个汉字）
5. 年龄（正常人年龄，不可能存储负数）
6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
7. 入职时间（取值年月日即可）

create table emp(
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
) comment '员工表';

六、MySQL图形化界面工具

虽然可以通过命令行工具操作MySQL，但图形化界面工具（如DataGrip）更加直观和方便。它可以帮助我们快速创建数据库、表，执行SQL语句，提高开发效率。

1. DataGrip安装

安装可以通过网盘分享的文件：DataGrip 链接: https://pan.baidu.com/s/1065T7XlWcQFWi8-Mp6J3Og
提取码: s4w5

下载DataGrip安装包。
双击安装包，按照提示完成安装。
启动DataGrip，配置数据库连接。
3. DataGrip使用
添加数据源：配置MySQL服务器的连接信息，包括主机名、端口、用户名和密码。
创建数据库：在DataGrip中，右键点击数据库节点，选择“New Database”。
创建表：在指定的数据库上右键，选择“New Table”。

修改表结构：在需要修改的表上右键选择“Modify Table”，可以添加、删除字段，修改字段类型等。

执行SQL语句：在指定的数据库上右键，选择“New Query Console”，在控制台中编写和执行SQL语句。

以下是DataGrip的一些配置

2. DML（Data Manipulation Language）

DML用于对数据库表中的数据进行增删改操作。常见的DML语句包括**INSERT（增）、UPDATE（改）、DELETE（删）**等。
插入数据

INSERT INTO tb_user (id, name, age, gender) VALUES (1, '张三', 18, '男');

更新数据

UPDATE tb_user SET age = 20 WHERE id = 1;

删除数据

DELETE FROM tb_user WHERE id = 1;

3. DQL（Data Query Language）
   DQL用于查询数据库中表的记录。SELECT是DQL的核心语句。
   查询所有记录

SELECT * FROM tb_user;

条件查询

SELECT * FROM tb_user WHERE age > 18;

聚合函数

SELECT COUNT(*) FROM tb_user;  -- 统计记录数
SELECT AVG(age) FROM tb_user;  -- 计算平均年龄
SELECT MAX(age) FROM tb_user;  -- 查询最大年龄
SELECT MIN(age) FROM tb_user;  -- 查询最小年龄
SELECT SUM(age) FROM tb_user;  -- 求年龄总和

分组查询

SELECT gender, COUNT(*) FROM tb_user GROUP BY gender;

排序查询

SELECT * FROM tb_user ORDER BY age ASC, name DESC;

分页查询

SELECT * FROM tb_user LIMIT 0, 10;  -- 查询第1页，每页10条
SELECT * FROM tb_user LIMIT 10, 10; -- 查询第2页，每页10条

4. DCL（Data Control Language）
   DCL用于管理数据库用户和控制访问权限。常见的DCL语句包括GRANT、REVOKE等。
   创建用户

CREATE USER 'itcast'@'localhost' IDENTIFIED BY '123456';

授予权限

GRANT ALL PRIVILEGES ON itcast.* TO 'itcast'@'localhost';

撤销权限

REVOKE ALL PRIVILEGES ON itcast.* FROM 'itcast'@'localhost';

七、多表查询
在实际应用中，我们经常需要从多张表中查询数据。多表查询包括内连接、外连接、自连接和子查询等。
4. 内连接
内连接查询的是两张表交集部分的数据。
隐式内连接

SELECT e.name, d.name FROM emp e, dept d WHERE e.dept_id = d.id;

显式内连接

SELECT e.name, d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

2. 外连接
   外连接分为左外连接和右外连接。
   左外连接

SELECT e.*, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;

右外连接

SELECT e.*, d.name FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

3. 自连接
   自连接是当前表与自身的连接查询。

SELECT a.name, b.name FROM emp a, emp b WHERE a.managerid = b.id;

4. 子查询
   子查询是SQL语句中嵌套的SELECT语句。
   标量子查询

SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部');

列子查询

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name IN ('研发部', '市场部'));

表子查询

SELECT e.*, d.* FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT JOIN dept d ON e.dept_id = d.id;

八、事务管理
事务是一组操作的集合，它是一个不可分割的工作单位。事务的四大特性（ACID）保证了数据的一致性和完整性。

1. 事务四大特性
   原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
   一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
   隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
   持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
2. 事务操作
   查看事务提交方式

SELECT @@autocommit;

设置事务提交方式

SET @@autocommit = 0;  -- 设置为手动提交

开启事务

START TRANSACTION;

提交事务

COMMIT;

回滚事务

ROLLBACK;

3. 并发事务问题
   并发事务可能导致以下问题：
   脏读：一个事务读到另一个事务未提交的数据。
   不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。
   幻读：一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，发现这行数据已经存在。
4. 事务隔离级别
   为了解决并发事务所引发的问题，MySQL提供了以下几种事务隔离级别：
   Read Uncommitted：允许脏读、不可重复读和幻读。
   Read Committed：不允许脏读，但允许不可重复读和幻读。
   Repeatable Read（默认级别）：不允许脏读和不可重复读，但允许幻读。
   Serializable：不允许脏读、不可重复读和幻读。
   查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

九、实战案例
为了更好地理解MySQL的使用，我们通过一个实际案例来展示如何使用MySQL进行数据库设计和操作。假设我们正在开发一个简单的员工管理系统，需要存储员工信息、部门信息以及员工的薪资等级。

1. 数据库设计
   我们设计以下几张表：
   dept表：存储部门信息
   emp表：存储员工信息
   salgrade表：存储薪资等级信息
   创建dept表

CREATE TABLE dept (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '部门ID',name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';

创建emp表

CREATE TABLE emp (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',name VARCHAR(50) NOT NULL COMMENT '员工姓名',age INT COMMENT '员工年龄',job VARCHAR(20) COMMENT '职位',salary INT COMMENT '薪资',entrydate DATE COMMENT '入职时间',managerid INT COMMENT '直属领导ID',dept_id INT COMMENT '部门ID',FOREIGN KEY (dept_id) REFERENCES dept(id)
) COMMENT '员工表';

创建salgrade表

CREATE TABLE salgrade (grade INT PRIMARY KEY COMMENT '薪资等级',losal INT COMMENT '最低薪资',hisal INT COMMENT '最高薪资'
) COMMENT '薪资等级表';

2. 插入测试数据
   插入部门数据

INSERT INTO dept (name) VALUES ('研发部'), ('市场部'), ('财务部'), ('销售部'), ('总经办');

插入员工数据

INSERT INTO emp (name, age, job, salary, entrydate, managerid, dept_id) VALUES
('金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5),
('张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
('杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
('韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
('常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
('小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
('灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
('周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
('丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
('赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
('鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
('鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
('方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
('张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
('俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
('宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
('陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);

插入薪资等级数据

INSERT INTO salgrade (grade, losal, hisal) VALUES
(1, 0, 3000),
(2, 3001, 5000),
(3, 5001, 8000),
(4, 8001, 10000),
(5, 10001, 15000),
(6, 15001, 20000),
(7, 20001, 25000),
(8, 25001, 30000);

3. 查询操作
   查询员工的姓名、年龄、职位、部门信息（隐式内连接）

SELECT e.name, e.age, e.job, d.name AS department_name
FROM emp e, dept d
WHERE e.dept_id = d.id;

查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

SELECT e.name, e.age, e.job, d.name AS department_name
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id
WHERE e.age < 30;

查询拥有员工的部门ID、部门名称

SELECT DISTINCT d.id, d.name
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id;

查询所有年龄大于40岁的员工及其归属的部门名称；如果员工没有分配部门，也需要展示出来（外连接）

SELECT e.*, d.name AS department_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.id
WHERE e.age > 40;

查询所有员工的工资等级

SELECT e.*, s.grade
FROM emp e
INNER JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal;

查询“研发部”所有员工的信息及工资等级

SELECT e.*, s.grade
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id
INNER JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal
WHERE d.name = '研发部';

查询“研发部”员工的平均工资

SELECT AVG(e.salary) AS average_salary
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id
WHERE d.name = '研发部';

查询“研发部”员工的平均工资

SELECT AVG(e.salary) AS average_salary
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id
WHERE d.name = '研发部';

查询工资比“灭绝”高的员工信息

SELECT *
FROM emp
WHERE salary > (SELECT salary FROM emp WHERE name = '灭绝');

查询比平均薪资高的员工信息

SELECT *
FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp);

查询低于本部门平均工资的员工信息

SELECT e1.*
FROM emp e1
WHERE e1.salary < (SELECT AVG(e2.salary) FROM emp e2 WHERE e2.dept_id = e1.dept_id);

查询所有的部门信息，并统计部门的员工人数

SELECT d.id, d.name, COUNT(e.id) AS employee_count
FROM dept d
LEFT JOIN emp e ON d.id = e.dept_id
GROUP BY d.id, d.name;

4. 事务管理
   假设我们需要实现一个员工薪资调整的功能，要求在调整薪资时，同时更新员工的薪资等级。如果在更新过程中出现任何错误，需要回滚事务，确保数据的一致性。
   开启事务

START TRANSACTION;

更新员工薪资

UPDATE emp SET salary = 15000 WHERE name = '张无忌';

更新员工薪资等级

UPDATE emp e
INNER JOIN salgrade s ON e.salary BETWEEN s.losal AND s.hisal
SET e.grade = s.grade
WHERE e.name = '张无忌';

提交事务

COMMIT;

如果在更新过程中出现错误，可以回滚事务：

ROLLBACK;

十、总结
通过本文的详细介绍，我们从MySQL的基础概念出发，逐步深入到实际操作，涵盖了数据库的创建、表的定义、数据的增删改查、多表查询以及事务管理等核心内容。希望本文能够帮助读者快速掌握MySQL的使用方法，为后续的开发工作打下坚实的基础。

如果你对MySQL还有其他疑问，或者在实际操作中遇到了问题，欢迎在评论区留言！