MySQL笔记

MySQL学习笔记

记录一些日常经常用到的MySQL知识

表操作

建表语句

1
2
3
4
5
6
7
CREATE TABLE users (
    -- 用户ID，主键，自动递增
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户的唯一标识符',
  
    -- 用户名，唯一，不允许为空
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户的登录名，必须唯一'
);

批量新增语句

1
2
3
4
5
INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
    (value1_1, value1_2, value1_3, ...),
    (value2_1, value2_2, value2_3, ...),
    (value3_1, value3_2, value3_3, ...);

添加列

1
2
3
4
-- 语法
alter table table_name add column 列名 字段类型
-- 示例
alter table 表名 add column 列名 varchar(30);

删除列

1
2
3
4
-- 语法
alter table table_name drop column 列名
-- 示例
alter table 表名 drop column 列名;

语法

排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 基本排序
order by 字段名 asc/desc
-- 多字段排序
order by 字段名1 asc/desc, 字段名2 asc/desc
-- 表达式排序 
ORDER BY 表达式 [ASC|DESC];
-- 随机排序
ORDER BY RAND();
-- 结合 GROUP BY 和 ORDER BY
group by 字段名 order by 字段名 asc/desc
-- 条件排序
order by case when 表达式1 then 1
when 表达式2 then 2
else 3 end

函数

常用的文本处理函数

函数名 说明
length(str) 返回字符串的长度
concat(str1, str2, …) 连接多个字符串
substr(str, start, length) 从字符串中提取子串
replace(str, old, new) 替换字符串中的子串
GROUP_CONCAT(str, separator) 将多个字符串连接成一个字符串，并使用分隔符分隔
upper(str) 将字符串转换为大写
lower(str) 将字符串转换为小写
ltrim(str) 去除字符串左侧的空白字符
rtrim(str) 去除字符串右侧的空白字符
trim(str) 去除字符串两侧的空白字符
left(str, n) 返回字符串左侧的n个字符
right(str, n) 返回字符串右侧的n个字符
concat_ws(‘,’,str1, str2, …) 连接多个字符串。第一个参数是分隔符，后续参数是要连接的字符串。

分组函数

函数名 说明
count(*) 返回行数
sum(col) 返回指定列的总和
avg(col) 返回指定列的平均值
max(col) 返回指定列的最大值
min(col) 返回指定列的最小值

数值处理函数

函数名 说明
abs(x) 返回x的绝对值
round(x, d) 返回x四舍五入到d位小数
ceil(x/100) 返回大于或等于x的最小整数
floor(x/100) 返回小于或等于x的最大整数
round(x,y) 返回x四舍五入到整数,y指定保留的小数位数
rand() 返回0到1之间的随机数
mod(x,y) 返回x除以y的余数

日期处理函数

函数名 说明
curdate() 返回当前日期
curtime() 返回当前时间
current_timestamp() 返回当前日期和时间
now() 返回当前日期和时间
date(date) 返回日期的日期部分
time(date) 返回日期的时间部分
TIMESTAMPDIFF(date1,date2) 返回两个日期之间的天数差
year(date) 返回日期的年份
month(date) 返回日期的月份
day(date) 返回日期的天数
weekday(date) 返回日期是星期几，星期一为0，星期六为5
date_format(date,format) 格式化日期，format为格式化字符串，如%Y-%m-%d %H:%i:%s
date_add(date, interval n day) 将日期增加n天，interval后面可以指定年(YEAR)、月(MONTH)、日(DAY)、时(HOUR)、分(MINUTE)、秒(SECOND)等单位
date_sub(date, interval n day) 将日期减少n天，interval后面可以指定年(YEAR)、月(MONTH)、日(DAY)、时(HOUR)、分(MINUTE)、秒(SECOND)等单位

条件判断

IF

1
2
-- 条件为真，返回结果1，否则返回结果2
if(条件,结果1,结果2)

CASE

1
2
3
4
-- 符合条件1，返回结果1，符合条件2，返回结果2，否则返回结果3
case when 条件1 then 结果1
when 条件2 then 结果2
else 结果3 end

NULLIF

1
2
-- expr1 和 expr2 相等，返回 NULL
NULLIF(expr1,expr2)

IFNULL

1
2
-- expr1 为 NULL，返回 expr2
IFNULL(expr1,expr2)

索引

索引是一种数据结构，用于快速查找数据库表中的数据行。索引可以大大提高数据库查询效率，但同时会降低插入和删除数据的效率。

创建索引(常用索引)

1
2
3
4
5
6
7
8
-- 创建普通索引 用于提高查询速度，特别是在WHERE子句中经常使用的列上。普通索引允许列中存在重复值
CREATE INDEX index_name ON table_name (column_name);
-- 创建唯一索引 用于确保列中的所有值都是唯一的，不允许重复值。这在需要保证数据完整性和唯一性的情况下非常有用，例如用户表中的电子邮件地址
CREATE UNIQUE INDEX index_name ON table_name (column_name);
-- 创建全文索引 用于对文本数据进行高效的全文搜索。适用于需要对大量文本数据进行搜索的场景，如文章、评论等
CREATE FULLTEXT INDEX index_name ON table_name (column_name);
-- 创建组合索引 用于在多个列上创建索引，可以提高涉及多个列的查询性能。适用于经常需要同时根据多个列进行查询的场景
CREATE INDEX index_name ON table_name (column1, column2);

删除索引

1
DROP INDEX index_name ON table_name;