MySQL数据库基础教程：DDL、DML、DQL…
文章目录
1 概述
MySQL下载地址：https://dev.mysql.com/downloads/mysql/）
在终端连接MySQL服务器：mysql -u用户名 -p[密码] [-h数据库服务器IP地址 -P端口号]
或可在DataGrip或Idea（集成了DataGrip）的图形化界面中连接、操作MySQL数据库，如下图所示
数据库（DataBase，DB）：存储和管理数据的仓库。
数据库管理系统（DataBase Management System，DBMS）：操纵和管理数据库的大型软件。
关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。使用表存储数据，格式统一，便于维护；使用SQL语言操作，标准统一，使用方便，可用于复杂查询。
SQL（Structured Query Language，操作关系型数据库的编程语言）定义了一套操作关系型数据库的统一标准。
- SQL语句可以单行或多行书写，以分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写
- 注释：单行注释（
-- 注释内容或＃注释内容）、多行注释（/* 注释内容 */）
| SQL语句分类 | 英文全称 | 中文名 | 说明 |
|---|---|---|---|
| DDL | Data Definition Language | 数组定义语言 | 用来定义数据库对象（数据库库，表，字段） |
| DML | Data Manipulation Language | 数据操作语言 | 用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言 | 用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言 | 用来创建数据库用户、控制数据库的访问权限 |
2 数据库设计——DDL
2.1 数据库操作
- 查询数据库
/* 查询所有数据库 */
show databases;
/* 查询当前数据库 */
select database();
- 使用数据库
use 数据库名;
- 创建数据库
create database [if not exists] 数据库名;
- 删除数据库
drop database [if exists] 数据库名;
方括号[]中内容表示可选；所有的
database(s)可替换成schema(s)
2.2 表操作
设置
create_time/update_time：用now()函数获取当前时间
- 创建表结构
create table 表名 (
字段1 字段类型 [约束] [comment 字段1注释],
字段2 字段类型 [约束] [comment 字段2注释],
...
字段n 字段类型 [约束] [comment 字段n注释]
) [comment 表注释];
- 查询
/* 查询当前数据库所有表 */
show tables;
/* 查询表结构 */
desc 表名;
/* 查询建表语句 */
show create table 表名;
- 修改
/* 添加字段 */
alter table 表名 add 字段名 字段类型(长度) [comment 注释] [约束];
/* 修改字段类型 */
alter table 表名 modify 字段名 新字段类型(长度);
/* 修改字段名和字段类型 */
alter table 表名 change 旧字段名 新字段名 字段类型(长度) [comment 注释] [约束];
/* 删除字段 */
alter table 表名 drop column 字段名;
/* 修改表名 */
rename table 表名 to 新表名;
- 删除表结构
drop table [if exists] 表名;
2.2.1 约束
作用于表中字段上的规则，用于限制存储在表中的数据，保证数据库中数据的正确性、有效性和完整性。
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段值不能为null | not null |
| 唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key（auto_increment 自增） |
| 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 | default + 默认值 |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | foreign key |
2.2.2 数据类型
MySQL中的数据类型主要分为数值类型、字符串类型、日期时间类型，字符串和日期型数据应包含在引号中。
| 数值类型 | 大小 | 有符号范围signed | 无符号范围unsigned | 描述 |
|---|---|---|---|---|
| tinyint | 1 | ( − 128 , 127 ) (-128,\ 127) (−128, 127) | ( 10 , 255 ) (10,\ 255) (10, 255) | 小整数值 |
| smallint | 2 | ( − 32768 , 32767 ) (-32768,\ 32767) (−32768, 32767) | ( 0 , 65535 ) (0,\ 65535) (0, 65535) | 大整数值 |
| mediumint | 3 | ( − 8388608 , 8388607 ) (-8388608,\ 8388607) (−8388608, 8388607) | ( 0 , 16777215 ) (0,\ 16777215) (0, 16777215) | 大整数值 |
| int | 4 | ( − 2147483648 , 2147483647 ) (-2147483648,\ 2147483647) (−2147483648, 2147483647) | ( 0 , 4294967295 ) (0,\ 4294967295) (0, 4294967295) | 大整数值 |
| bigint | 8 | ( − 2 63 , 2 63 − 1 ) (-2^{63},\ 2^{63}-1) (−263, 263−1) | ( 0 , 2 64 − 1 ) (0,\ 2^{64}-1) (0, 264−1) | 极大整数值 |
| float | 4 | ( − 3.402823466 × 1 0 38 , 3.402823466351 × 1 0 38 ) (-3.402823466\times 10^{38},\ 3.402823466351\times 10^{38}) (−3.402823466×1038, 3.402823466351×1038) | 0 0 0 和 ( 1.175494351 × 1 0 − 38 , 3.402823466 × 1 0 38 ) (1.175494351\times 10^{-38},\ 3.402823466\times10^{38}) (1.175494351×10−38, 3.402823466×1038) | 单精度浮点数值，float(n, m)：整个数字长度为n，小数位为m |
| double | 8 | ( − 1.7976931348623157 × 1 0 308 , 1.7976931348623157 × 1 0 308 ) (-1.7976931348623157\times 10^{308},\ 1.7976931348623157\times 10^{308}) (−1.7976931348623157×10308, 1.7976931348623157×10308) | 0 0 0 和 ( 2.2250738585072014 × 1 0 − 308 , 1.7976931348623157 × 1 0 308 ) (2.2250738585072014\times 10^{-308},\ 1.7976931348623157\times 10^{308}) (2.2250738585072014×10−308, 1.7976931348623157×10308) | 双精度浮点数值，double(n, m)：整个数字长度为n，小数位为m |
| decimal | 小数值（精度更高），decimal(n, m)：整个数字长度为n，小数位为m |
| 字符串类型 | 大小 | 描述 |
|---|---|---|
| char | 0~255 bytes | 定长字符串，char(n)：最多只能存n个字符，始终占用n个字符的空间，空间换时间 |
| varchar | 0~65535 bytes | 变长字符串，varchar(n)：最多只能存n个字符，不足n个时按实际长度存储，时间换空间 |
| tinyblob | 0~255 bytes | 不超过255个字符的二进制数据 |
| tinytext | 0~255 bytes | 短文本字符串 |
| blob | 0~65535 bytes | 二进制形式的长文本数据 |
| text | 0~65535 bytes | 长文本数据 |
| mediumblob | 0~16 777 215 bytes | 二进制形式的中等长度文本数据 |
| mediumtext | 0~16 777 215 bytes | 中等长度文本数据 |
| longblob | 0~4 294 967 295 bytes | 二进制形式的极大文本数据 |
| longtext | 0~294 967 295 bytes | 极大文本数据 |
| 日期类型 | 大小(byte) | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| 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 至2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
3 数据库操作——DML
- 添加数据
/* 指定字段添加数据 */
insert into 表名 (字段名1, 字段名2) values (值1, 值2); -- 指定的字段与值一一对应
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2); -- 批量添加数据
/* 全部字段添加数据 */
insert into 表名 values (值1, 值2, ...);
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...); -- 批量添加数据
- 修改/更新数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];
- 删除操作（无法删除某一字段的值，可以使用
UPDATE将字段的值置为NULL）
delete from 表名 [where 条件];
4 数据库查询——DQL
4.1 基本查询
/* 查询字段 */
select 字段1, 字段2, 字段3 from 表名;
select * from 表名; -- 查询所有字段
/* 设置别名 */
select 字段1 [[as] 别名1], 字段2 [[as] 别名2] from 表名; -- 可以省略关键字as；别名有空格则必须带引号
/* 去重查询 */
select distinct 字段列表 from 表名;
通配符(*)不直观，影响效率，实际开发中尽量少用
4.2 条件查询
select 字段列表 from 表名 where 条件列表;
| 比较运算符 | 功能 |
|---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
!=或<> | 不等于 |
between ... and ... | 在某个范围之内（闭区间） |
in (...) | 属于in之后的列表中的值之一 |
like 占位符 | 模糊匹配：_匹配1个字符，%匹配任意个字符（可以0个）。例： name like '王__'表示匹配name为王某某（两个下划线） |
is null | 为null |
| 逻辑运算符 | 功能 |
|---|---|
and或&& | 并且（多个条件同时成立） |
or或|| | 或者（多个条件任意一个成立） |
not或! | 非，不是 |
流程控制：
- if函数：
if(条件表达式, true取值, false取值) - case：
(case 表达式 when 值1 then 结果1 [when 值2 then 结果2 ... ] [else result] end)
4.3 分组查询
聚合函数：将一列数据作为一个整体，进行纵向计算，如用select 聚合函数(字段列表) from 表名;可直接查询运算后的结果。null值不参与所有聚合函数运算。
| 常用的聚合函数 | 功能 | 语法说明 |
|---|---|---|
count | 统计数量 | count(字段)：统计该字段非null值数量count(*)：统计所有行的数量，包括含null值的行（推荐）count(常量)：统计所有行的数量，包括含null值的行，效果同上 |
max | 最大值 | max(字段) |
min | 最小值 | min(字段) |
avg | 平均值 | avg(字段)，返回浮点数 |
sum | 求和 | sum(字段) |
分组查询的字段列表一般为分组字段 聚合函数，执行顺序：where > 聚合函数 > having 。
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
-- 例1：根据性别分组，统计男女员工的数量
select gender, count(*) from tb_emp group by gender;
-- 例2：先查询入职时间在'2015-01-01'以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位
select job, count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;
where与having区别
- 执行时机不同：where是分组前进行过滤，不满足where条件，不参与分组；而having是分组后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
4.4 排序查询
ASC：升序（默认）DESC：降序
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件] order by 字段1 排序方式1, 字段2 排序方式2 ... ;
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
4.5 分页查询
起始索引从0开始，计算式：起始索引 = ( = ( =(页码 − 1 ) × -1) \times −1)×每页展示记录数
select 字段列表 from 表名 limit 起始索引, 查询记录数;
-- 例：从起始索引0开始查询员工数据，每页展示5条记录。
-- 1. 查询第1页员工数据（查询第1页时可省略起始索引0）
select * from tb_emp limit 5;
-- 2. 查询第2页员工数据
select * from tb_emp limit 5, 5;
-- 3. 查询第3页员工数据
select * from tb_emp limit 10, 5;
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
5 多表设计
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构。由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）
- 多对多
- 一对一
5.1 一对多
一对多关系中，"一"和"多"的双方常分别称为父表(主表)和子表（参考数据结构中的树）。
案例：部门与员工的关系。
-- 员工
create table tb_emp (
id int unsigned primary key auto_increment comment 'ID',
...
entrydate date comment '入职时间',
dept_id int unsigned comment '归属的部门ID', -- 关联的部门ID
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
-- 部门
create table tb_dept (
id int unsigned primary key auto_increment comment 'ID',
name varchar(10) not null unique comment '部门名称',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '部门表';
部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题。原因在于目前上述的两张表，在数据库层面并未建立关联，所以无法保证数据的一致性和完整性。
实现表与表之间关联的方法：外键
- 物理外键：使用外键约束
foreign key定义外键关联另外一张表。在多的一方添加外键，关联另外一方的主键。- 缺点
- 影响增、删、改的效率（需要检查外键关系）
- 仅用于单节点数据库，不适用与分布式、集群场景
- 容易引发数据库的死锁问题，消耗性能
- 缺点
-- 创建表时指定外键
create table 表明 (
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);
-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
- 逻辑外键：在业务层逻辑中，解决外键关联。可以避免物理外键的问题。
5.2 一对一
一对一关系多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。可以视为只有一个子表的特殊的一对多关系。
案例：用户与身份证信息的关系。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）。
5.3 多对多
案例：学生与课程的关系，一个学生可以选修多门课程，一门课程也可以供多个学生选择。
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
6 多表查询
6.1 概述
多表查询：从多张表中查询数据，分为连接查询（内连接、外连接）、子查询。
select * from tb_emp, tb_dept; -- 该种方式使得两张表的数据两两组合，产生了大量无效数据
笛卡尔积：在数学中，两个集合（
A
A
A 集合和
B
B
B 集合）的所有组合情况。
多表查询的目的就是消除笛卡尔积产生的无效数据，可以根据所设外键指定条件进行查询。指定字段时用表名.字段名格式。
select * from tb_emp, tb_dept where tb_emp.dept_id = tb.dept.id;
6.2 内连接
内连接：相当于查询上图中 A A A、 B B B 交集部分数据
/* 隐式内连接 */
select 字段列表 from 表1, 表2 where 条件 ... ;
/* 显式内连接 */
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ... ;
-- 例：查询员工的姓名及所属部门的名称（注：某些员工不属于任何部门，也某些部门无员工）
-- 1. 隐式内连接
select tb_emp.name, tb_dept.name from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;
select e.name, d.name from tb_emp e, tb_dept d where e.dept_id = d.id; -- 可先给表起别名，简化书写
-- 2. 显示内连接
select tb_emp.name, tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb.dept.id
6.3 外连接
外连接根据上图中集合位置可分为以下两类
- 左外连接：查询左表所有数据（包括两张表交集部分数据）
- 右外连接：查询右表所有数据（包括两张表交集部分数据）
/* 左外连接（更常用） */
select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ... ; -- 表1为左表
/* 右外连接（可改写为左外连接） */
select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ... ; -- 表2为右表
-- 例1：查询员工表所有员工的姓名及所属部门的名称
select e.name, d.name from tb_emp e left join tb_dept d on e.dept_id = d.id; -- 左外连接
-- 例2：查询部门表所有部门的名称及对应员工的名称
select e.name, d.name from tb_emp e right join tb_dept d on e.dept_id = d.id; -- 右外连接
6.4 子查询
子查询/嵌套查询：SQL语句中嵌套的select语句，外部语句可为insert、update、delete、select（最常见）。根据子查询返回的结果可分为如下几类
- 标量子查询：子查询返回的结果为单个值（数字、字符串、日期等），是最简单的子查询。常用的操作符有
=、!=、>、>=、<、<=。
-- 例1：查询部门表中教研部所有员工信息：1. 查询教研部的部门ID；2. 查询该ID的部门下的员工信息。
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- 例2：查询司马彰入职后的员工信息：1. 查询司马彰的入职时间；2. 查询在司马彰入职后的员工信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '司马彰');
- 列子查询：子查询返回的结果为一列（可以是多行）。常用的操作符有
in、not in等
-- 例：查询教研部和咨询部的所有员工信息：1. 查询教研部和咨询部的ID；2. 查询这些ID的部门下的员工信息
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
- 行子查询：子查询返回的结果为一行（可以是多列）。常用的操作符有
=、!=、in、not in等
-- 例：查询与yohane的入职日期与职位都相同的员工信息：1. 查询yohane的入职日期与职位；2. 查询入职日期和职位与她都相同的员工信息
select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = 'yohane');
-- 优化写法
- 表子查询：子查询返回的结果为多行多列，常作为临时表使用。常用的操作符有
in等
-- 例：查询入职日期是'2024-05-01'之后的员工信息，及其部门名称
select e.*, d.name from (select * from tb_emp where entrydate > '2024-05-01') e, tb_dept d where e.dept_id = d.id; -- e.*表示表e的所有信息
7 事务
事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
- 事务的控制
/* 开启事务 */ start transaction; begin; /* 提交事务 */ commit; /* 回滚事务 */ rollback; - 事务的四大特性（ACID）
- 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
8 数据库优化——索引
索引（index）是帮助数据库高效获取数据的数据结构。
- 优点
- 提高数据查询的效率，降低效据库的I0成本。
- 通过索引列对数据进行排序，降低致据排序的成本，降低CPU消耗。
- 缺点
- 索引会占用存储空间。
- 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。
MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree案引、Full-Text索引等。我们平常所说的案引，若无特别指明都是指默认的B+Tree结构组织的索引：
- 每一个结点可以存储多个key（有n个key，就有n个指针）
- 所有的数据都存储在叶子结点，非叶子结点仅用于索引数据
- 叶子结点形成了一条双向链表，便于数据的排序及区间范围查询
使用普通的二叉搜索树或红黑树作为索引结构的缺点：大数据量情况下，层级深，检索速度慢。
索引的常用操作如下
/* 为表的字段创建索引 */
create [unique] index 索引名 on 表名 (字段名, ...); -- 加上unique表示唯一索引
/* 查看索引 */
show index from 表名;
/* 删除索引 */
drop index 索引名 on 表名;
标签：--,数据库,表名,DML,查询,基础教程,MySQL,tb,select From： https://blog.csdn.net/Akira37/article/details/140425520
- 主键字段在建表时，会自动创建主键索引。
- 添加唯一约束时，数据库实际上会添加唯一索引。