当前位置：首页 > news >正文

MySQL技巧

news 2025/6/30 13:37:39

SQL语句分类（理解）

☆ DDL 定义

数据定义语言：简称DDL(Data Definition Language) 用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等

搭建数据存储的框架，如管理数据库、管理数据表、管理字段

☆ DML 操作

数据操作语言：简称DML(Data Manipulation Language) 用来对数据库中表的记录进行更新（增加、修改、删除）。关键字：insert，delete，update等

☆ DQL 查询

数据查询语言：简称DQL(Data Query Language) 用来查询数据库中表的记录（查询）。关键字：select，from，where等

☆ DCL 控制

数据控制语言：简称DCL(Data Control Language) 用来定义数据库的访问权限和安全级别及创建用户（账号管理、权限管理）。

SQL基本语法（重点）

① SQL语句可以单行或多行书写，但是最终需要以分号结尾。

② 可使用空格和缩进来增强语句的可读性

③ MySQL数据库的SQL语句默认不区分大小写

④ 可以使用单行与多行注释

数据库的基本操作

查看数据库

show databases; -- 显示所有的数据库

show create database 库名; -- 查看指定数据库

创建数据库

create database 库名 [编码格式];

-- 方式一：创建库
mysql> create database 库名;

-- 方式二：创建库并指定默认字符集
mysql> create database 库名 default charset gbk;

-- 方式三：如果存在不报错(if not exists)
mysql> create database if not exists 库名 default character set utf8;

删除数据库

drop database 库名;

选择数据库

use 库名;

编码格式

编码格式，常见的gbk（中国的编码格式）与utf8（国际通用编码格式）

DDL表操作

DDL要学4个关键字：create（创建）、drop（删除）、alter（修改）、show（查询）

查询表

show tables;

创建表

create table 表名(
字段字段类型 [字段约束]
)

删除表

drop table 表名;

修改表名

rename table 旧名 to 新名;

修改表字段

alter table 表名 add 新字段名字段类型 first;
alter table 数据表名称 add 新字段名称字段类型 after 其他字段名称;

first：把新添加字段放在第一位
after 字段名称：把新添加字段放在指定字段的后面

删除字段

alter table 表名 drop 字段名;

修改字段名称、类型

alter table 表名 change 字段名 name varchar(20);

只修改字段类型

alter table 表名modify name varchar(20);

auto_increment

作用：自动增长，往往用于设置主键，可以让其值自动增长，默认从1开始，依次递增。

DML数据操作语言

DML包括哪些SQL语句

insert插入、update更新、delete删除

数据的增删改

增加

insert into 表名 values (
字段
);

查询命令

select * from 表名; -- 查看表内容

修改

update 表名 set 字段=更新后的值;

删除

delete from 表名 [where 删除条件];

清空数据表

delete from 数据表;

truncate 数据表;

delete：删除数据记录
- 数据操作语言（DML）
- 删除大量记录速度慢，只删除数据，主键自增序列不清零，1-100 => 新插入 => 101
- 可以带条件删除
truncate：删除所有数据记录
- 数据定义语言（DDL）
- 清理大量数据速度快，主键自增序列清零, 1-100 => 新插入 => 1
- 不能带条件删除

字段约束

五种约束：①非空约束 ②默认值约束 ③唯一约束 ④主键约束 ⑤外键约束

主键约束（重点）

强调：非空、唯一、一个表中最多有且仅有1个主键，在MySQL中，往往id字段都是主键（primary key）

主键约束与唯一约束区别？

和主键约束相同点：都是唯一的

和主键约束不同点：主键非空、有且仅有1个；唯一可以为null，在一个表中也可以同时拥有多个

DQL数据查询语言

select查询

# 根据某些条件从某个表中查询指定字段的内容
格式：select [distinct]*| 列名,列名 from 表 [where 条件]

select 查询哪些列多个列用逗号隔开 from 数据表 where 查询条件（满足条件的就显示，不满足的就忽略）

简单查询

select * from 表名 -- 查询所有
select 列名,列名 from 表名 -- 查询某一列

五子句

SQL除了简单查询以外，还支持五子句查询（SQL查询五子句）

select */字段 from 数据表 ① where子句 ② group by子句 ③ having子句 ④ order by子句 ⑤ limit子句

① where：条件查询
② group by：分组查询
③ having：条件查询，只不过发生在分组之后，可以对分组后结果进行筛选
④ order by：排序子句，用于排序操作
⑤ limit：限制查询，用于限制查询数量
五子句可以单独出现，也可以多个关键词一起出现。但是不管多少个关键字都必须严格按照五子句顺序进行书写，否则报错！！！

条件查询(where字句)

五子句的一部分

简单查询的常见用法

case when

-- 比如，把gender的'0', '1'转换成男女
create table tb_employee (
   id int,
   name varchar(32),
   gender tinyint    -- 男0，女1
);

insert into tb_employee
   values (1, '张三', 0)
, (2, '晓红', 1)
, (3, '李四', 0)
;

select
   name,
   case gender when 0 then '男' else '女' end as '性别'
   from tb_employee;

left() 和 right()

LEFT(string, length)：从字符串左侧开始，截取指定长度的字符

RIGHT(string, length)：从字符串右侧开始，截取指定长度的字符

count()

CONCAT() 是用于字符串拼接的核心函数，支持将多个字符串连接为一个。

聚合函数

-- 字段定义别名
select name, (chinese+english+math) [as] 别名 from 数据表;

聚合函数	作用
count()	统计指定列不为NULL的记录行数；
sum()	计算指定列的数值和，如果指定列类型不是数值类型，则计算结果为0
max()	计算指定列的最大值，如果指定列是字符串类型，使用字符串排序运算；
min()	计算指定列的最小值，如果指定列是字符串类型，使用字符串排序运算；
avg()	计算指定列的平均值，如果指定列类型不是数值类型，则计算结果为0

分组查询（group by 子句）

作用：分组就是为了更好的进行数据的统计，分组 + 聚合。

分组查询基本的语法格式如下：GROUP BY 列名 [HAVING 条件表达式]

group by

① group by可以实现去重操作

② group by的作用是为了实现分组统计（group by + 聚合函数）

group by使用注意事项：

select 分组字段, 聚合函数, 聚合函数 from students group by 分组字段;

SQL官方文档：在有group by出现的情况下，select后面的字段要么只能出现在分组中，要么只能出现在聚合函数中

过滤（having 子句）

having作用和where类似都是过滤数据的，但是两者之间的执行顺序不同

① where子句（发生在分组之前）

② group by子句 (分组)

③ having子句（发生在分组之后）

☆ 第一种情况：如果只是简单的查询操作（没有group by的情况），大部分时间having是可以直接替代where子句

排序查询（order by 子句）

-- 通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。
-- 格式：SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
ASC（Ascend /əˈsend/）升序 (默认)，默认情况下，ASC关键词可以省略不写
DESC (Descend /dɪˈsend/) 降序

-- 1.使用价格排序(降序)
select * from tb_product order by price desc;

-- 2.在价格排序(降序)的基础上，以品类排序(降序)
select * from tb_product order by price desc, category_id desc;
-- 首先按照第一个字段排序，如果第一个字段能比较出大小，则不需要进行字段2排序；
-- 如果第一个字段值相同，则系统会继续按照第二个字段进行排序