🐧运维笔记
首页  /  数据库  /  正文

MySQL 建库建表与单表查询

数据库 2026-10-02📖 25 分钟👁 —
🐬 数据库 · MySQL第 1 / 12 页123456789101112📖 完整导航 →

这部分我之前已经学过一遍，所以实操过程会省掉一些，省掉的不代表不重要。

这一页管的是 SQL 跟单表相关的操作。

一、概述和连接库

MySQL 是数据库管理系统，我们用 SQL 语句操作这个管理系统，再由它去管里面的库和表。

部署上推荐直接用 docker 起一个，安全组记得放行端口。

如果拿不准 mysql 的目录结构，可以先临时跑一个容器进去看看：

# 如果不确定 mysql 的目录结构可以临时跑一个容器查看
# 查看根目录下的结构
docker run --rm -it mysql ls -la /

# 查看数据目录
docker run --rm -it mysql ls -la /var/lib/mysql

# 查看配置目录
docker run --rm -it mysql ls -la /etc/mysql
docker run --rm -it mysql ls -la /etc/mysql/conf.d

# 查看日志目录
docker run --rm -it mysql ls -la /var/log/mysql


# 如果容器在运行可以直接进去看
docker exec -it mysql bash
# 或者
docker exec -it mysql sh

# 进去之后
ls -la /var/lib/mysql
ls -la /etc/mysql/conf.d

正式起容器就把三个目录都挂到宿主机上：

sudo mkdir -p /opt/mysql/data
sudo mkdir -p /opt/mysql/conf.d
sudo mkdir -p /opt/mysql/logs

docker run -d \
  --name mysql01 \
  -p 3366:3306 \
  -e MYSQL_ROOT_PASSWORD=123321 \
  -v /opt/mysql/data:/var/lib/mysql \
  -v /opt/mysql/conf.d:/etc/mysql/conf.d \
  -v /opt/mysql/logs:/var/log/mysql \
  mysql


root@VM-0-12-ubuntu:/# docker ps
CONTAINER ID   IMAGE     COMMAND                  CREATED          STATUS          PORTS                                                    NAMES
9b0e2a9916da   mysql     "docker-entrypoint.s…"   48 seconds ago   Up 48 seconds   33060/tcp, 0.0.0.0:3366->3306/tcp, [::]:3366->3306/tcp   mysql01

这几个目录各自是干嘛的：

目录作用挂载建议
/var/lib/mysql数据目录（最重要的！）一定要挂到宿主机固定目录
/etc/mysql/conf.d自定义配置文件目录挂不挂都行，挂空目录也没事
/var/log/mysql日志目录可挂可不挂
/var/run/mysqld运行时 socket 目录不要挂，没必要

这里有个坑要单独说一句：空目录能不能挂，取决于镜像里原本这个目录有没有东西。

容器内路径镜像里原本有什么挂空目录的结果
/etc/mysqlmy.cnf、mysql.conf.d/mysqld.cnf 等核心配置配置文件全没了，启动失败
/etc/mysql/conf.d空目录（专门留给用户放自定义配置）空挂没问题
/var/lib/mysql空（还没初始化）启动时自动初始化
/var/log/mysql空空挂没问题

连接还是那个老命令：

mysql [-h 127.0.0.1] [-P 3306] -u root -p

mysql [-h 127.0.0.1] [-P 3306] -u root -p [密码]，中括号括起来的是默认值，不写就用默认。

数据模型这块，MySQL 是关系型数据库（RDBMS），数据都放在一张张二维表里，表和表之间靠主外键连起来。比如员工表里存一个 dept_id 指向部门表的 id，一条员工记录就挂到了对应的部门上：

关系型数据库用二维表存数据，员工表的 dept_id 指到部门表的 id

二、SQL-DDL

DDL 动的是结构，建库建表、删库删表、改字段，都算它的活，跟表里的数据没关系。

SQL 按用途一共分四类，后面几页就是按这个顺序走的：

SQL 分四类：DDL 定义结构、DML 操作数据、DQL 查询记录、DCL 控制用户和权限

1. 操作库

# 中括号内可选
# 查询所有数据库
show databases;

# 查询当前数据库
select database();

# 创建数据库
# 如果不存在就创建、设置字符集（不建议是utf8，只能存3个字节，utf8mb4支持4个字节）
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

# 删除数据库
drop database [if exists] 数据库名;

# 使用数据库
use 数据库名;

2. 操作表

# 进入指定数据库，查询当前数据库所有表
show tables;

# 查询表结构
# describe 描述
desc 表名;

# 查询指定表的建表语句，是 desc 的详细版
show create table 表名;

# 创建表（comment 译为评论）
create table 表名(
  字段1 字段1类型 [comment 字段1注释],
  字段2 字段2类型 [comment 字段2注释],
  字段3 字段3类型 [comment 字段3注释],
  字段n 字段n类型 [comment 字段n注释]
) [comment 表注释];

# 例子展示（使用上面的目录查看）
create table `tb_user` (
  `id` int default null comment '编号',
  `name` varchar(50) default null,
  `age` int default null,
  `gender` varchar(1) default null comment '性别'
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci comment='测试用户表';

# 删除表
drop table [if exists] 表名;

# 修改表名
alter table 表名 rename to 新表名;

# 删除指定表，并重新创建该表（清空数据，保留结构）
# truncate 译为截断
truncate table 表名;

3. 数据类型

数值类型里，有符号就是有正负，无符号只有正。常用的就是 tinyint、int、bigint 和存金额的 decimal：

数值类型：tinyint 到 bigint 的大小与有符号、无符号范围，以及 float、double、decimal

字符串类型，定长用 char，变长用 varchar，大文本用 text：

字符串类型：char、varchar、blob、text 系列各占多少字节

日期时间类型，日期用 date，日期时间用 datetime 或 timestamp：

日期时间类型：date、time、year、datetime、timestamp 的范围和格式

按这套类型建一张合乎规范的表长这样：

# 创建一张合乎规范的表

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 '员工表';

4. 修改/删除表的字段-alter

表建完了还想改，就是 alter：

# 添加字段
# alter 译为改变
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

# 添加外键（建表后修改）
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);


# 修改字段
# 修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);

# 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

# 删除字段
alter table 表名 drop 字段名;



# 删除外键
alter table 表名 drop foreign key 外键名称;

这几条命令凑一张图就是这个样子：

DDL 小结：库操作的 show/create/use/drop，和表操作的 create/desc/alter/drop

三、SQL-DML

DML 操作的是表里的数据，就增、删、改三件事。

图形化工具用 DataGrip，建库建表都挺方便。

1. 插入

给指定字段添加一条/多条数据：

# 添加一条数据
# 指定字段
insert into 表名(字段名1, 字段名2, ...) values(值1, 值2, ...);

# 全部字段
# 值1 值2 对应表中第 1 2 字段的值
insert into 表名 values(值1, 值2, ...);


# 添加多条数据
# 指定字段
insert into 表名(字段名1, 字段名2, ...) values(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

# 全部字段
insert into 表名 values(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);


# 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
# 字符串和日期型数据应该包含在引号中
# 插入的数据大小，应该在字段的规定范围内

插入的时候工具会把字段类型提示出来，照着提示填就行，值必须符合类型：

DataGrip 里敲 insert 会自动列出字段和类型，值不符合类型插不进去

2. 修改和删除

update，改数据：

# 修改数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];

# 注意：修改语句的条件可以有，也可以没有
# 如果没有条件，则会修改整张表的所有数据，是毁数据的操作
# 正确的操作：先查后改
# 先确认范围
select * from 表名 where 条件;
# 确认无误再执行 update/delete


# 删除数据，99% 的情况都用 id 删
delete from 表名 [where 条件];

# delete 语句的条件可以有，也可以没有
# 如果没有条件，则会删除整张表的所有数据
# delete 语句不能删除某一个字段的值（可以使用 update）

这两条命令加 insert 凑一张图：

DML 小结：insert into、update set、delete from 三条命令的完整写法

四、SQL-DQL

DQL 就是查数据，一条 select 打天下。

# 编写顺序（不是执行顺序）
select
  字段列表
from
  表名列表
where
  条件列表
group by
  分组字段列表
having
  分组后条件列表
order by
  排序字段列表
limit
  分页参数

1. 基础语法&准备数据

# 查询多个字段
select 字段1, 字段2, 字段3 ... from 表名;
# 查询所有字段，相当于展示整个表
select * from 表名;

# 设置别名，as 可以省略
select 字段1 [as 别名1], 字段2 [as 别名2] ... from 表名;

# 去除重复记录
# distinct 译为不同的
select distinct 字段列表 from 表名;

先把后面要用的数据准备好：

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 '身份证号',
  workaddress varchar(50) comment '工作地址',
  entrydate date comment '入职时间'
) comment '员工表';


insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values
(1,  '1',  '柳岩',    '女', 20, '123456789012345678', '北京', '2000-01-01'),
(2,  '2',  '张无忌',  '男', 18, '123456789012345670', '北京', '2005-09-01'),
(3,  '3',  '韦一笑',  '男', 38, '123456789712345670', '上海', '2005-08-01'),
(4,  '4',  '赵敏',    '女', 18, '123456757123845670', '北京', '2009-12-01'),
(5,  '5',  '小昭',    '女', 16, '123456769012345678', '上海', '2007-07-01'),
(6,  '6',  '杨逍',    '男', 28, '12345678931234567X', '北京', '2006-01-01'),
(7,  '7',  '范瑶',    '男', 40, '123456789212345670', '北京', '2005-05-01'),
(8,  '8',  '黛绮丝',  '女', 38, '123456157123645670', '天津', '2015-05-01'),
(9,  '9',  '范凉凉',  '女', 45, '123156789012345678', '北京', '2010-04-01'),
(10, '10', '陈友谅',  '男', 53, '123456789012345670', '上海', '2011-01-01'),
(11, '11', '张士诚',  '男', 55, '123567897123456670', '江苏', '2015-05-01'),
(12, '12', '常遇春',  '男', 32, '123446757152345670', '北京', '2004-02-01'),
(13, '13', '张三丰',  '男', 88, '123656789012345678', '江苏', '2020-11-01'),
(14, '14', '灭绝',    '女', 65, '123456789012345670', '西安', '2019-05-01'),
(15, '15', '胡青牛',  '男', 70, '12345678971234567X', '西安', '2018-04-01'),
(16, '16', '周芷若',  '女', 18, null,                  '北京', '2012-06-01');

2. 条件查询

# 条件查询
select 字段列表 from 表名 where 条件列表;

# 比较运算符
# >           大于
# >=          大于等于
# <           小于
# <=          小于等于
# =           等于
# <> 或 !=    不等于
# between ... and ...  在某个范围之内（含最小、最大值）
# in(...)     在in之后的列表中的值，多选一
# like 占位符  模糊匹配（_匹配单个字符，%匹配任意个字符）
# is null     是null


# 逻辑运算符：组装多个条件
# and 或 &&    并且（多个条件同时成立）
# or 或 ||     或者（多个条件任意一个成立）
# not 或 !     非，不是

3. 聚合函数

聚合函数作用在一列数据上：

# 常见聚合函数
# count  统计数量
# max    最大值
# min    最小值
# avg    平均值
# sum    求和

# 语法（直接用在字段上）
select 聚合函数(字段列表) from 表名;

要注意 null 值不参与聚合函数运算。

4. 分组查询

分组这里要注意执行先后顺序：

# 分组查询
select 字段列表 from 表名 
[where 条件] 
[group by 分组字段名]
[having 分组后过滤条件];

# where 与 having 区别：
# 执行时机不同：where 是分组之前进行过滤，不满足 where 条件
# 不参与分组；而 having 是分组之后对结果进行过滤
# 判断条件不同：where 不能对聚合函数进行判断，而 having 可以

几个例子：

# 18 岁的人年龄之和
select sum(age) from emp where age = 18;
# 分别统计男女数量
select gender,count(id) from emp group by gender;
# 男女分别平均年龄
select gender,avg(age) from emp group by gender;
# 工作地址分组，年龄小于45，并获取员工数量大于3的地址(分组之后再过滤)
select count(id),workaddress from emp
                where age < 45
                group by workaddress
                having count(id)>3;


# 执行顺序：where > 聚合函数 > having
# 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义：
# 比如查询下面的 name 字段，每个人都分组了，只会返回其中一个代表的人名，不可能返回全部人名
select name,sum(age) from emp where age = 18;

# 比如这个查的就是分组字段
select gender,count(id) from emp group by gender;

5. 排序查询

# 排序查询
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

# 排序方式
# asc（ascending）: 升序（默认值）
# desc（descending）: 降序

# 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

6. 分页查询

limit 用的频率很高，实际项目里几乎每条列表查询都带它：

# 分页查询
select 字段列表 from 表名 limit 起始索引, 查询记录数;

# 注意
# 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数
# 分页查询是数据库的方言，不同的数据库有不同的实现，mysql中是limit
# 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

比如查第 2 页、每页 10 条：

select * from emp limit 10,10;

7. 执行顺序&小结

图里从上到下是编写顺序，红色序号 1~6 标的是执行顺序，两个顺序不是一回事：

执行顺序是 from、where、group by、select、order by、limit，和上面写的顺序对不上

写 SQL 的时候在脑子里模拟一遍执行过程，报错了也就知道大概是哪一步出的问题，不容易乱。

把这一节的知识点摞在一起看：

# 编写顺序
select
  字段列表                # 字段名 [as] 别名
from
  表名
where
  条件列表                # > >= < <= = <> like between...and in and or  分组之前过滤
group by
  分组字段列表
having
  分组后条件列表           # 分组之后过滤
order by
  排序字段列表             # 升序 asc，降序 desc
limit
  起始索引, 每页展示记录数   # 起始索引从0开始
#MySQL#SQL#数据库#DDL
← 返回全部文章下一页 →MySQL 权限、函数与约束