恬静荒芜的时光
mysql基础
song
  2025-07-07 11:04:03   2025-12-29 11:07:03

ddl（Data Definition Language 数据定义语言）

用来创建数据库中的库、表、索引、视图、存储过程、触发器等。

数据库操作

  • show databases;查看所有数据库
  • create databse [if not exists] 数据库名; 创建数据库
  • drop database [if exists] 数据库名; 删除数据库
  • select database(); 看当时使用的数据库
  • use 数据库名; 选中一个数据

表操作

  • C Create

  • R Retrieve/Read

  • U Update

  • D Delete

  • show tables; 查看当前数据库下的所有表

  • desc 数据表名;

  • ceate table 表名 (字段名1 数据类型,字段名2 数据类型2…); 创建表

  • drop table 表名; 删除表

修改表
  1. 修改表名
    • ALTER TABLE 旧表名 RENAME TO 新表名
  2. 添加一列
    • ALTER TABLE 表名 ADD 字段名 类型
  3. 修改数据类型
    • ALTER TABLE 表名 MODIFY 列名 新数据类型
  4. 修改列名和数据类型
    • ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型
  5. 删除列
    • ALTER TABLE 表名 DROP 列名

mysql数据类型

  • 数值
  • 日期
  • 字符串

dml （Data Manipulation Language 数据操作语言）

用来对表内数据的添加、更新、删除等。

  • insert
    • insert into 表名 (列名1，列名2….) values (值1,值2….)
    • insert into 表名 (列名1，列名2….) values (值1,值2….),(值1,值2….)…..
  • update
    • update 表名 set 列名1=值1,列名2=值2,…..[where 条件]
  • delete
    • delete from 表名 [where 条件]
    • truncate table 表名

dql（Data Query Language 数据查询语言）

用于对表内数据的关键字逻辑查询等。

1
2
3
4
5
6
7
SELECT 字段列表
FROM  表名
WHERE 条件
GROUP BY 分组字段
HAVING 分组后条件
ORDER BY  排序字段
LIMIT 分页限定
  • as
  • distinct
  • ifnull

where

运算符符号 运算符名称 运算符类型 描述 示例
= 等于 比较运算符 判断两个值是否相等 WHERE id = 10
<> 或 != 不等于 比较运算符 判断两个值是否不相等 WHERE status <> 0
> 大于 比较运算符 判断左值是否大于右值 WHERE price > 100
< 小于 比较运算符 判断左值是否小于右值 WHERE age < 18
>= 大于等于 比较运算符 判断左值是否大于或等于右值 WHERE score >= 60
<= 小于等于 比较运算符 判断左值是否小于或等于右值 WHERE quantity <= 50
BETWEEN ... AND ... 范围匹配 范围运算符 判断值是否在指定闭区间内 WHERE salary BETWEEN 5000 AND 10000
NOT BETWEEN ... AND 范围排除 范围运算符 判断值是否不在指定区间内 WHERE id NOT BETWEEN 1 AND 100
IN (...) 包含于 集合运算符 判断值是否在指定列表中 WHERE status IN (1, 3, 5)
NOT IN (...) 不包含于 集合运算符 判断值是否不在指定列表中 WHERE country NOT IN ('US', 'CN')
LIKE 模式匹配 模糊匹配运算符 用通配符匹配字符串（%：任意字符，_：单个字符） WHERE name LIKE 'Tom%'
NOT LIKE 模式排除 模糊匹配运算符 排除匹配模式的字符串 WHERE email NOT LIKE '%@test.com'
IS NULL 是空值 空值判断运算符 判断字段是否为 NULL WHERE phone IS NULL
IS NOT NULL 非空值 空值判断运算符 判断字段是否不为 NULL WHERE address IS NOT NULL
AND 逻辑与 逻辑运算符 同时满足所有条件 WHERE age > 18 AND score >= 60
OR 逻辑或 逻辑运算符 满足任意一个条件 WHERE status = 1 OR status = 3
NOT 逻辑非 逻辑运算符 取反操作 WHERE NOT deleted
REGEXP 或 RLIKE 正则匹配 其他运算符 用正则表达式匹配字符串 WHERE name REGEXP '^A.*z$'
<=> 空值安全等于 其他运算符 可安全比较 NULL（包括 NULL = NULL） WHERE column <=> NULL
  1. 优先级规则：
    • 括号 () > NOT > AND > OR
    • 示例：WHERE (a=1 OR b=2) AND c=3
  2. 特殊注意：
    • NULL 判断必须用 IS NULL/IS NOT NULL（不可用 = NULL）
    • 通配符 % 和 _ 需配合 LIKE 使用
    • BETWEEN 包含边界值（闭区间）
  3. 性能提示：
    • IN 适用于离散值，BETWEEN 适用于连续范围
    • 避免在 LIKE 中使用前置 %（如 %abc）导致全表扫描

order by

1
2
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式],排序字段名2 [排序方式]
--多个条件时，当前一个条件值一样时，才会根据第二条件进行排序
  • asc 升序 默认
  • desc 降序

group by

聚合函数

和分组查询密切相关的技术：聚合函数
常用的聚合函数：

  • sum
  • avg
  • min
  • max
  • count
    1
    SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]
分组查询
1
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 字段名1 [HAVING 分组后条件过滤]
  • 分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
  • group by 后边的字段值相同才会划分为一组

where 和 having 区别

  • 执行时机不同，where 先筛选数据，不满足条件不参与分组，having是对分组之后的结果进行过滤
  • 可判断条件不一样，where不能对聚合函数进行判断 having 可以

limit

1
SELECT 字段列表 FROM 表名 LIMIT [起始索引,]查询条目数
  • 起始索引 从0开始 ，索引0表示数据的表的第一行数据
  • 起始索引 缺少是默认 0
  • 起始索引 计算公式 ： 起始索引 = (当前页面-1) * 每页显示的条数
  • limit 是mysql方言

sql执行顺序

  1. from
  2. where
  3. group by
  4. having
  5. select
  6. order by
  7. limit

dcl（Data Control Language 数据控制语言）

表关系

约束

约束是一种限制，用于修饰表中的列数据，通过这种限制来保证表数据的正确性、完整性、有效性

约束类型 关键字 作用描述 示例
主键约束 PRIMARY KEY 唯一标识每行数据，不允许重复和 NULL 值，每表仅一个主键。 id INT PRIMARY KEY
外键约束 FOREIGN KEY 确保两表间数据关联（引用其他表的主键），防止非法数据插入。 user_id INT, FOREIGN KEY (user_id) REFERENCES Users(id)
唯一约束 UNIQUE 保证列的值唯一（允许 NULL 值），可用于非主键字段。 email VARCHAR(100) UNIQUE
非空约束 NOT NULL 强制列的值不允许为 NULL。 name VARCHAR(50) NOT NULL
默认约束 DEFAULT 插入数据时，若未指定值，则自动填充默认值。 status INT DEFAULT 1
检查约束 CHECK 验证列的值是否符合指定条件（MySQL 8.0.16+ 原生支持）。 age INT CHECK (age >= 18)
自增约束 AUTO_INCREMENT 自动生成唯一递增值（仅用于整数类型主键）。 id INT PRIMARY KEY AUTO_INCREMENT

一对多

  • 在从表（多方）创建一个字段作为外键，从表外键值指向主表（一方）的主键。

多对多

  • 创建第三张表作为中间表，中间表至少2个字段，这两个字段分别作为外键指向各自一方的主键

一对一

  • 两张表合并成一张表
  • 任选一方为从表建议唯一约束的外键

多表查询

笛卡尔积

多张表查询时，每张表的每条数据组合成的数据结果集，叫做笛卡尔积。这样组合后的数据不是所有的都有用，通过主外键相等条件过滤掉没用的数据是消除笛卡尔积

1
2
3
4
5
6
7
8
create table department(id int primary key auto_Increment,  name varchar(10) not null);
create table employee(id int primary key  auto_increment,name varchar(10) not null ,departmentId int not null default 0);

insert  into department (name) values ('研发部'),('市场部'),('生产部');
insert into employee (name, departmentId) values ('孙悟空',1),('猪八戒',2),('唐僧',3),('沙和尚',3);

select * from employee,department ;  --笛卡尔积
select * from employee,department  where employee.departmentId= department.id;  -- 消除笛卡尔积

多表查询

内连接 inner join ： 把多个表中相互关联的数据查询出来
1
2
3
4
--隐式内连接
select 列名 from 左表,右表 where 从表.外键=主表.主键
--显示内链接
select 列名 from 左表 [inner] join 右表 on 从表.外键=主表.主键
外连接 left/right/full join

外连接包括三种类型：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）

连接类型 是否属于外连接 保留数据范围
INNER JOIN ❌ 否 仅匹配记录
LEFT JOIN ✅ 是 左表全部 + 右表匹配部分
RIGHT JOIN ✅ 是 右表全部 + 左表匹配部分
FULL JOIN ✅ 是 左右表全部记录
[子查询] 在一个查询语句中嵌套了另一个查询语句
  • 单行单列
  • 多行单列

事务

事务特性

ACID

事务的隔离级别

  1. 1. ddl（Data Definition Language 数据定义语言）
    1. 1.1. 数据库操作
    2. 1.2. 表操作
      1. 1.2.1. 修改表
    3. 1.3. mysql数据类型
  2. 2. dml （Data Manipulation Language 数据操作语言）
  3. 3. dql（Data Query Language 数据查询语言）
    1. 3.1. where
    2. 3.2. order by
    3. 3.3. group by
      1. 3.3.1. 聚合函数
      2. 3.3.2. 分组查询
    4. 3.4. limit
    5. 3.5. sql执行顺序
  4. 4. dcl（Data Control Language 数据控制语言）
  5. 5. 表关系
    1. 5.1. 约束
    2. 5.2. 一对多
    3. 5.3. 多对多
    4. 5.4. 一对一
  6. 6. 多表查询
    1. 6.1. 笛卡尔积
    2. 6.2. 多表查询
      1. 6.2.1. 内连接 inner join ： 把多个表中相互关联的数据查询出来
      2. 6.2.2. 外连接 left/right/full join
      3. 6.2.3. [子查询] 在一个查询语句中嵌套了另一个查询语句
  7. 7. 事务
    1. 7.1. 事务特性
    2. 7.2. 事务的隔离级别
© 2024 - 2025    song
由 Hexo 驱动 & 主题 Keep
  1. 1. ddl（Data Definition Language 数据定义语言）
    1. 1.1. 数据库操作
    2. 1.2. 表操作
      1. 1.2.1. 修改表
    3. 1.3. mysql数据类型
  2. 2. dml （Data Manipulation Language 数据操作语言）
  3. 3. dql（Data Query Language 数据查询语言）
    1. 3.1. where
    2. 3.2. order by
    3. 3.3. group by
      1. 3.3.1. 聚合函数
      2. 3.3.2. 分组查询
    4. 3.4. limit
    5. 3.5. sql执行顺序
  4. 4. dcl（Data Control Language 数据控制语言）
  5. 5. 表关系
    1. 5.1. 约束
    2. 5.2. 一对多
    3. 5.3. 多对多
    4. 5.4. 一对一
  6. 6. 多表查询
    1. 6.1. 笛卡尔积
    2. 6.2. 多表查询
      1. 6.2.1. 内连接 inner join ： 把多个表中相互关联的数据查询出来
      2. 6.2.2. 外连接 left/right/full join
      3. 6.2.3. [子查询] 在一个查询语句中嵌套了另一个查询语句
  7. 7. 事务
    1. 7.1. 事务特性
    2. 7.2. 事务的隔离级别