mysql复习

Kinger2026/7/23大约 14 分钟

用户

创建一个用户

create USER'test'@localhost IDENTIFIED by 'TestPass123!'

修改密码

set password for '用户名'@'主机' = password('新密码')

基础命令

查看数据库 show database；

创建数据库 create database <数据库名>

选择数据库 use <数据库名>

查看创建数据库命令 show create database test\G

删除数据库 drop databse test3

表

查看创建表语句 show create table <表名>

创建表

create table <表名>(

column1 datatye column_constrains,

)

列名：大小写下划线，小写+下划线

插入数据 insert into test_int (age,num1,num2)values(1,2,3),(4,5,6);

查询表中的数据 select *from test_int；

数据类型

整数类型

TINYINT 1字节 -128-127 0-255

SMALLINT 2字节

MEDIUMINT 3字节

INT 4字节

BIGINT 8字节

TYPE(n) =>显示宽度 n=2

ZEROFILL =>零填充 00127

UNSIGNED =>无符号

注释：

单行注释：SELECT * FROM user;-- 查询数据 # 正确：--后有空格

多行注释：/* */

浮点型（小数）

float 4字节默认长度7 单精度浮点型

double 8字节默认长度17 双精度浮点型

decimal 精确的浮点数

float/double 指定显示宽度和小数位数，如果没有指定按实际精度来处理

decimal 不指定显示宽度和小数位数，默认为（10，0）10位整数位，0位小数位

字符串类型

char(n) 固定长度字符串，手机号，身份证。。

varchar(n) 可变长字符串，标题，昵称（灵活，省空间，效率稍微差一点）

text 用于储存大块文本 0-65535

tinytext 小文本 0-255

mediumntext 中等文本

longtext 超大文本

blog 储存二进制数据（图片，音频，视频）

json 轻量级数据 ‘交换’文本

日期时间类型

date 3字节 yyyy-MM-dd 日期

time 3字节 HH:mm:ss 时间

year 1字节 yyyy 年份

datetime 8字节 yyyy-MM-dd HH:mm:ss

timestamp 4字节 yyyy-MM-dd HH:mm:ss（显示数据依赖当前时区）

常见的约束

默认值约束（DEFAULT）：该列元素如果没有给值，使用默认值

主键约束（PRIMARY KEY）：不能为空加唯一约束，一个表只能有一个主键

主键：单个字段，多个字段

作用：标识唯一的一条记录，一般来说建议每个表都创建一个主键

创建了主键之后 =>自动创建索引 =>提升查询效率

外键约束（FOREIGN KEY）：用于建立表之间的关系

分表的意义？

节省存储空间（减少数据冗余）

提升可维护性

数据分表 ->外键

优点：数据之间有关联，数据一致性检查，数据完整性

缺点：

外键有一定的性能开销（产生临时表，消耗内存和cpu）

复杂性：进行2数据插入和删除时，由于有约束更麻烦

企业：会分表

外键 =》数据库层面解决一些约束问题

通常从代码层面解决约束问题

查询SQL

select [distinct] <列名|*> from <表名> [where <条件>] [order by <列名>] [group by <列名>]

select 查询

distinct 去重(对结果去重)

from 从哪个表查询

where 条件

order by 排序

group by 分组

limit M,N 限制返回数据

比较查询 <,>,<=,>=,!=或<>,=

名字不等于花花公子的数据

select *from product where pname <> "花花公子";

单价大于1000的商品

select *from product where price >1000;

逻辑查询 and,or,not

a and b =>a和b同时位真，结果为真

单价大于200，且小于等于1000

select *from product where price>200 and price <=1000

范围查询 in,between and

单价大于200，且小于等于1000

select *from product where price between 200 and 1000

select *from product where not (cid=c004)

`%`	匹配任意长度的字符（0 个 / 1 个 / 多个）	模糊匹配开头 / 结尾 / 包含内容
`_`	匹配单个任意字符	精准控制字符长度的模糊匹配
`[]`	匹配括号内单个字符（需搭配 `REGEXP`）	匹配指定范围内的单个字符

正则匹配regexp 或 rlike

SELECT 字段 FROM 表名 WHERE 字段 REGEXP '正则模式';

SELECT 字段 FROM 表名 WHERE 字段 RLIKE '正则模式'; -- 与REGEXP等价

-- 示例1：匹配test_enum_set中name以“刘”开头的记录（^）

SELECT * FROM test_enum_set WHERE name REGEXP '^刘';

-- 输出：刘志强

-- 示例2：匹配articles中title以“log”结尾的记录（$）

SELECT * FROM articles WHERE title REGEXP 'log$';

-- 输出：test123.log

-- 示例3：匹配name包含“张”或“李”的记录（|）

SELECT * FROM test_enum_set WHERE name REGEXP '张|李';

-- 输出：张伟、张张强、李强

限制返回数量 limit

limit n =>返回n条数据

select *from product limit 1；

limit n,m =>跳过前面n条数据，输出m条数据

select *from product limit 3,3;

查询空值和非空值 NULL

select *from articles where id is not null;

正则匹配

如果基本的能满足需求，尽量用基本的查询方法

[0-9]：匹配在这个范围内任意一个数据

[a-z,A-Z]

重复前面指定的字符出现的次数

？：前面的字符重复0-1次

+：前面的字符出现1次及以上

*：匹配一个字符0次或者多次

^：匹配字符串开头

$：匹配字符串结尾

排序查询

默认升序

select *from product order by price;

select *from product order by price desc;（desc倒序排序）

默认ASC升序

多字段排序

select *from product where pid between 1 and 10 order by cid desc,price desc;

聚合查询：将多条记录聚合到一起计算出一条

计数，求和，求平均

count（字段名）：统计值不为NULL的数量

select count(*) from product;

select count(pid) from product;

max字段，max：求最大值，min：最小值

select max(price),min(price) from product;

avg字段：求平均值

select avg(price) from product;

分组查询

...where 条件 group by 列名 [having 条件表达式（having对分组的结果在操作)]

查看每个不同类别的平均值

select avg(price),cid from product group by cid;

查找商品类别数量大于等于3的分类

select cid,count(pid) cid_count from product group by cid having cid_count >=3;

更新SQL

update <表名> set 列名=值，列名=值...where 条件;

update product set price=price*10 where price<=100;

删除SQL

delete from <表名> where 条件;

delete from product where pid=13;

清空表

delete from product;

truncate table product;

用delete和truncate清空表有什么区别

delete :删除数据是一行一行的删除 ->删除会产生二进制日志 -》可以恢复

删除慢

truncate：删除表，重新创建表 ->数据无法恢复

速度快

联接查询（多个表）

多表查询：查询操作中涉及两个或多个表数据查询

多表查询需要表与表之间有紧密联系

表：学生姓名，id ，年龄，年纪，班级id，班主任，教室

分表 = >classes，student（classid）,menus

交叉联结

select 列名 from 表1，表2 where

select 列名 from 表1 cross join 表2

两个表相乘

内连接（inner join）

核心逻辑：只保留两个表中「关联字段完全匹配」的记录

返回两个表中满足条件的行的组合

select 列名 from 表1 inner join 表2 on 连接条件;

select *from student inner join class on students.class_id=classes.id;

select * from classes, students where students.class_id=classes.id;

+----+--------+------+----------+----+--------+-----------+
| id | name | age | class_id | id | name | teacher |
+----+--------+------+----------+----+--------+-----------+
| 1 | 张三 | 18 | 1 | 1 | 一班 | 张老师 |
| 2 | 李四 | 19 | 2 | 2 | 二班 | 李老师 |
| 3 | 王五 | 20 | 1 | 1 | 一班 | 张老师 |
| 4 | 赵六 | 18 | 2 | 2 | 二班 | 李老师 |
+----+--------+------+----------+----+--------+-----------+
4 rows in set (0.00 sec)

左连接（left join）

核心逻辑：保留「左表全部数据」，右表只保留匹配的记录；右表无匹配时，显示 NULL

select *from student left join class on students.class_id=classes.id;

右连接（right join）

核心逻辑：保留「右表全部数据」，左表只保留匹配的记录；左表无匹配时，显示 NULL

select *from student right join class on students.class_id=classes.id;

自连接

select *from menus as a,menus as b where a.id =b.parent;

索引

简单来说，索引（Index） 就是数据库中的“目录”。

它是数据库管理系统（DBMS）中一个排序的数据结构，通过它，数据库可以像查字典一样，不需要扫描全表就能快速找到你需要的数据。

核心原理：为什么要索引？

想象一本书有 500 页，如果你想找“异步编程”在哪一页：

没有索引（全表扫描）：你必须从第 1 页翻到第 500 页。
有索引（使用目录）：你先翻到书后的索引表，找到“异步编程”，看到它在第 213 页，直接翻过去。

在 MySQL 中，如果不加索引，查询数据的时间复杂度是 O(n)；有了索引（通常是 B+ 树结构），时间复杂度可以降低到 O(\log n)。

索引的分类

索引根据不同的维度有多种叫法：

逻辑功能角度

主键索引 (Primary Key)：唯一且不为空。
唯一索引 (Unique Index)：字段值必须唯一，但允许为空。
普通索引 (Normal Index)：最基本的索引，没有任何限制。
全文索引 (Full-text Index)：用于在大文本里搜索关键词。

字段个数角度

单列索引：只针对一个字段建索引。
联合索引 (Composite Index)：多个字段组合成一个索引。注意： 必须符合“最左前缀法则”才能生效。

索引的优缺点

索引虽好，但不能乱加：

优点	缺点
查询速度极快，减少磁盘 I/O。	占用物理空间。索引本身也是要存磁盘的。
提高分组 (`GROUP BY`<br>) 和排序 (`ORDER BY`<br>) 的效率。	降低写操作性能。每次 `INSERT`<br>, `UPDATE`<br>, `DELETE`<br>时，数据库都要同步维护索引树。