← 返回首页
MySQL数据库基础篇详解

MySQL数据库基础篇详解

发布于 2026年11月16日 17:17⏱ 8 分钟阅读👁 15

MySQL 基础篇

MySQL 基础学习笔记(纯文字版)。原文中的函数表、约束表、示意图、操作符表、隔离级别表均已转为文字与表格,内容与原文一致。

目录

一、SQL 概述

数据库:存储数据的仓库,数据是有组织地进行存储(DB)。

数据库管理系统(DBMS):管理和操纵数据库的大型软件,例如 MySQL。

SQL:操作关系型数据库的编程语言,定义了一套操作关系型数据库的统一标准。

主流的关系型数据库管理系统:Oracle、MySQL、Microsoft SQL Server、PostgreSQL、SQLite。

注意:SQL 为关系型数据库提供了一套统一标准。

MySQL 启动

net start mysql80
net stop mysql80

MySQL 数据库客户端连接

方式一:系统自带的命令行执行指令(注意:使用此方法需要设置环境变量)—— 推荐。

mysql [-h 127.0.0.1] [-P 3306] -u root -p
  • -h:指定主机连接地址
  • -P:指定端口(大写 P;小写 -p 表示输入密码)

方式二:MySQL 自带的客户端命令行。

数据模型

客户端 → DBMS → 多个数据库 → 表结构(多张)→ 二维表

关系型数据库(RDBMS)

  1. 建立在关系模型基础上,由多张互相连接的二维表组成的数据库。
  2. 特点:使用表存储数据,格式统一;使用 SQL 语言,有统一标准。

二、SQL

通用语法

  • 可以单行或者多行书写,以分号结尾。
  • 注释:
    • 单行注释:-- 注释内容 或 # 注释内容
    • 多行注释:/* 注释内容 */

SQL 语句分类(基础也是重点)

分类 名称 说明
DDL 数据定义语言 定义/修改数据库对象结构(数据库、表、字段等)
DML 数据操作语言 对数据库表中的数据进行增删改
DQL 数据查询语言 查询数据库表中的记录
DCL 数据控制语言 创建数据库用户、控制数据库的访问权限

DDL

数据库操作

show databases;   -- 查询所有数据库
select database(); -- 查询当前数据库
 
-- 创建数据库
create database [if not exists] 数据库名 [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注释],
    字段4 字段4类型 [comment 字段4注释]
    -- ……
) [comment 表注释];

表操作 —— 数据类型

  1. 数值类型
  2. 字符串类型
    • char:定长字符串,性能高
    • varchar:变长字符串,性能稍差,需要根据内容计算所占用的空间
  3. 日期时间类型:date、time、year、datetime、timestamp

表操作 —— 修改

alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]; -- 添加字段
 
alter table 表名 modify 字段名 新数据类型(长度); -- 修改数据类型
 
-- 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
 
alter table 表名 drop 字段名; -- 删除字段
 
alter table 表名 rename to 新表名; -- 修改表名

表操作 —— 删除

drop table [if exists] 表名; -- 删除表
 
-- 删除指定表,并重新创建该表(空表)—— 此地空余黄鹤楼
truncate table 表名;

MySQL 的图形化界面工具推荐:DataGrip。

DML

对数据库表中的数据记录进行增删改操作:

  1. INSERT
  2. UPDATE
  3. DELETE

添加、修改、删除数据的具体语法见博客文章。

DQL

数据查询语言(使用频率最高)—— important。用来查询数据库中表的记录。

select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数;

注意点:where 与 having 的区别

  1. 执行时机不同:where 在分组之前进行过滤,不满足 where 条件的不参与分组;而 having 是分组之后对结果进行过滤。
  2. 判断条件不同:where 不能对聚合函数进行判断,而 having 可以。

排序查询

order by —— 支持多字段排序。

分页查询

limit 起始索引, 查询记录数;

分页查询是数据库的方言,不同的数据库有不同的实现。

编写顺序与执行顺序

  • 编写顺序:如上。
  • 执行顺序:from → where → group by → having → select → order by → limit

DCL

  • 管理用户(注意:主机名可以使用 % 通配)
  • 权限控制

三、函数

函数是一段可以直接被另一段程序调用的程序或者代码。

字符串函数

MySQL 中内置了很多字符串函数,常用的几个如下:

函数 功能
CONCAT(S1,S2,…Sn) 字符串拼接,将 S1, S2, … Sn 拼接成一个字符串
LOWER(str) 将字符串 str 全部转为小写
UPPER(str) 将字符串 str 全部转为大写
LPAD(str,n,pad) 左填充,用字符串 pad 对 str 的左边进行填充,达到 n 个字符串长度
RPAD(str,n,pad) 右填充,用字符串 pad 对 str 的右边进行填充,达到 n 个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串 str 从 start 位置起的 len 个长度的字符串

数值函数

常见的数值函数如下:

函数 功能
CEIL(x) 向上取整
FLOOR(x) 向下取整
MOD(x,y) 返回 x/y 的模
RAND() 返回 0~1 内的随机数
ROUND(x,y) 求参数 x 的四舍五入的值,保留 y 位小数

日期函数

常见的日期函数如下:

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定 date 的年份
MONTH(date) 获取指定 date 的月份
DAY(date) 获取指定 date 的日期
DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔 expr 后的时间值
DATEDIFF(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数

流程控制函数

函数 功能
IF(value, t, f) 如果 value 为 true,则返回 t,否则返回 f
IFNULL(value1, value2) 如果 value1 不为空,返回 value1,否则返回 value2
CASE WHEN [val1] THEN [res1] ... ELSE [default] END 如果 val1 为 true,返回 res1,... 否则返回 default 默认值
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END 如果 expr 的值等于 val1,返回 res1,... 否则返回 default 默认值

四、约束

约束是作用于表中字段上的规则,用于限制存储在表中的数据。

目的:保证数据库中数据的正确性、有效性、完整性。

约束 描述 关键字
非空约束 限制该字段的数据不能为 null NOT NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY
默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT
检查约束(8.0.16 版本之后) 保证字段值满足某一个条件 CHECK
外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 FOREIGN KEY

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

外键约束:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。

五、多表查询

多表关系

实际项目开发中,会根据业务需求以及业务模块之间的关系,分析设计表结构。由于业务之间相互关联,所以各个表之间存在关系:

一对多:一个部门对应多个员工,一个员工对应一个部门。

多对多:学生与课程之间的关系,一个学生可以选择多门课程,一门课程也可以供多个学生选择。需要建立第三张中间表。

一对一:用户与用户详情的关系。一对一关系用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率。实现方式是在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的 UNIQUE。

多表查询概述

概述:从多张表中查询数据。

笛卡尔积:在数学中,两个集合 A 集合和 B 集合的所有组合情况。

举例说明:表 A 只有一列两行(A、B),表 B 只有一列四行(1、2、3、4),两表做笛卡尔积后得到 2 × 4 = 8 行结果:

表A 表B
A 1
A 2
A 3
A 4
B 1
B 2
B 3
B 4

提示:多表查询如果不加连接条件,得到的就是两张表的完整笛卡尔积,其中大部分组合是无意义的,因此需要用连接条件进行筛选。

多表查询分类

  1. 连接查询:内连接、外连接、自连接。
  2. 子查询(一张表会与自身进行查询)

内连接

查询的是两张表交集的部分。

  1. 隐式内连接
  2. 显式内连接

外连接

  1. 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;

左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。

  1. 右外连接
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 ...;
  • 联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
  • union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。

子查询

SQL 语句中嵌套 select 语句,称为嵌套查询,又称子查询。

select * from t1 where column1 = (select column1 from t2);

根据子查询结果不同,分为:

  • A. 标量子查询(子查询结果为单个值)
  • B. 列子查询(子查询结果为一列)
  • C. 行子查询(子查询结果为一行)
  • D. 表子查询(子查询结果为多行多列)

1) 标量子查询

返回的结果是单个值,最简单的形式。

常用的操作符:=、<>、>=、>、<、<=

2) 列子查询

返回的是一列,可以是多行。

常用的操作符:

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

3) 行子查询

返回的结果是一行(可以是多列)。

常见的操作符:=、<>、IN、NOT IN

4) 表子查询

子查询返回的结果是多行多列,相当于一张表 —— 表子查询。

常用操作符:IN

六、事务

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

事务操作

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

select @@autocommit;
set @@autocommit = 0;

提交事务:

commit;

回滚事务:

rollback;

开启事务:

start transaction;
-- 或
begin;

注意:start transaction 是开启一个显式事务,在事务期间暂停 autocommit 的自动提交行为(不是把 autocommit 变量改成 0,而是当前事务内不再逐条自动提交)。

事务四大特性(面试题)—— ACID

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

并发事务问题(面试题)

  1. 脏读:一个事务读到另外一个事务还没有提交的数据。
  2. 不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同。
  3. 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。

事务的隔离级别(面试题)

解决并发事务所引发的问题。既要权衡数据的安全性,又要考虑性能。

隔离级别 脏读 不可重复读 幻读
Read uncommitted √ √ √
Read committed × √ √
Repeatable Read(默认) × × √
Serializable × × ×

表中 √ 表示在该隔离级别下仍会出现对应问题,× 表示该问题已被解决。

查看事务隔离级别:

select @@transaction_isolation;

设置事务隔离级别:

set [session | global] transaction isolation level
    { read uncommitted | read committed | repeatable read | serializable };

注意:事务隔离级别越高,数据越安全,但是性能越低。 越低。

评论 (0)

还没有评论,来写第一条吧~

写评论