【MySQL】表的增删查改操作详解

---

MySQL 表的增删查改操作详解

CRUD：Create（创建）、Retrieve（读取）、Update（更新）、Delete（删除）

1. 创建表

在MySQL中创建表的语法如下：

CREATE TABLE `table_name` (
    `column1` datatype constraints,
    `column2` datatype constraints,
    ...
    PRIMARY KEY (`column1`)
);

示例：创建一个名为 users 的表

CREATE TABLE `users` (
    `user_id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1.1 插入数据

1.1.1 单行插入

INSERT INTO `table_name` (`column1`, `column2`, ...) VALUES (value1, value2, ...);

示例：向 users 表插入一条记录

INSERT INTO `users` (`username`, `email`, `password`) VALUES ('john_doe', 'john@example.com', 'password123');

1.1.2 多行插入

INSERT INTO `table_name` (`column1`, `column2`, ...)
VALUES
    (value1, value2, ...),
    (value1, value2, ...),
    ...;

示例：向 users 表插入多条记录

INSERT INTO `users` (`username`, `email`, `password`) 
VALUES 
    ('jane_smith', 'jane@example.com', 'securepassword'),
    ('mike_jones', 'mike@example.com', 'mikepassword');

1.1.3 插入后更新（INSERT … ON DUPLICATE KEY UPDATE）

当插入数据时，如果存在主键或唯一索引冲突，可以选择更新现有行的值。

INSERT INTO `table_name` (`column1`, `column2`, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE `column1` = value1, `column2` = value2, ...;

1.1.4 替换（REPLACE INTO）

替换数据时，如果插入的新数据与现有数据冲突，会先删除冲突数据，再插入新数据。

REPLACE INTO `table_name` (`column1`, `column2`, ...) VALUES (`data1`, `data2`, ...);

1.2 查询数据

1.2.1 查询语法

SELECT
    [DISTINCT]
    column1, column2, ...
FROM `table_name`
[WHERE condition]
[ORDER BY column [ASC | DESC], ...]
[LIMIT offset, count];

1.2.2 查询所有数据

SELECT * FROM `table_name`;

示例：查询所有用户信息

SELECT * FROM `users`;

1.2.3 查询指定列数据

SELECT `column1`, `column2`, ... FROM `table_name`;

示例：查询用户的用户名和电子邮件

SELECT `username`, `email` FROM `users`;

1.2.4 添加表达式查询

SELECT `column1`, `column2`, `expression` AS `alias_name` FROM `table_name`;

示例：查询用户名，电子邮件以及创建时间格式化为年-月-日

SELECT `username`, `email`, DATE_FORMAT(`created_at`, '%Y-%m-%d') AS `created_date` FROM `users`;

1.2.5 为字段起别名

SELECT `column` AS `alias_name` FROM `table_name`;

示例：查询用户名并为其起别名

SELECT `username` AS `user_name` FROM `users`;

1.2.6 结果去重

SELECT DISTINCT `column1`, `column2`, ... FROM `table_name`;

示例：查询所有不重复的电子邮件地址

SELECT DISTINCT `email` FROM `users`;

1.2.7 结果排序

SELECT `column1`, `column2` [, ...] FROM `table_name` ORDER BY `column` ASC;  -- 升序
SELECT `column1`, `column2` [, ...] FROM `table_name` ORDER BY `column` DESC; -- 降序

示例：按创建时间降序查询用户信息

SELECT * FROM `users` ORDER BY `created_at` DESC;

1.2.8 WHERE子句

SELECT `column` FROM `table_name` WHERE `condition`;

常见的比较和逻辑运算符

比较运算符	解释
> , >= , < , <=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如 NULL = NULL 的结果是 false(0)
<=>	等于，NULL安全，例如 NULL <=> NULL 的结果是 true(1)
!= , <>	不等于
BETWEEN a0 AND a1	范围匹配，满足 [a0, a1] 区间，返回 TRUE(1)
IN (option …)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配。% 表示任意(>=0)个字符， _ 表示任意一个字符
逻辑运算符	解释
AND	与，多个条件必须都为 true(1)，结果才是 true(1)
OR	或，任意一个条件为 true(1)，结果为 true(1)
NOT	非，条件为 true(1)，结果为 false(0)

示例：查询创建时间在2022年后的用户

SELECT `username`, `email` FROM `users` WHERE `created_at` > '2022-01-01';

1.2.9 分页查询

SELECT `column` [, ...] FROM `table_name` LIMIT `offset`, `count`;  -- 从 offset 开始筛选 count 条记录
SELECT `column` [, ...] FROM `table_name` LIMIT `count` OFFSET `offset`;  -- 从 offset 开始筛选 count 条记录

示例：从第 5 条记录开始，查询 10 条用户信息

SELECT `username`, `email` FROM `users` LIMIT 4, 10;

1.3 更新数据

UPDATE `table_name` SET `column1` = value1, `column2` = value2, ... [WHERE condition];

示例：将用户名为 ‘john_doe’ 的用户密码更新为新密码

UPDATE `users` SET `password` = 'newpassword' WHERE `username` = 'john_doe';

1.4 删除数据

1.4.1 删除行

DELETE FROM `table_name` WHERE `condition`;

示例：删除用户名为 ‘mike_jones’ 的用户

DELETE FROM `users` WHERE `username` = 'mike_jones';

1.4.2 截断表

截断表是删除表中所有行的操作，但保留表结构和定义。

TRUNCATE TABLE `table_name`;

示例：截断 users 表

TRUNCATE TABLE `users`;

1.5 插入查询结果

可以将一个查询的结果插入到另一个表中，这通常用于数据备份或数据转移。

INSERT INTO `target_table` SELECT * FROM `source_table`;

示例：将 users 表中的所有记录插入到 users_backup 表中

INSERT INTO `users_backup` SELECT * FROM `users`;

1.5.1 表去重

-- 1. 建立备份表
CREATE TABLE `duplicate_table_bak` LIKE `duplicate_table`;

-- 2. 将去重结果导入备份表
INSERT INTO `duplicate_table_bak` SELECT DISTINCT * FROM `duplicate_table`;

-- 3. 删除原表
RENAME TABLE `duplicate_table` TO `old_duplicate_table`;

-- 4. 将备份表更名为原表
RENAME TABLE `duplicate_table_bak` TO `duplicate_table`;

1.6 聚合函数

聚合函数的使用更多时关注数据纵向之间的关系。

1.6 聚合函数

聚合函数的使用更多时关注数据纵向之间的关系。

函数	说明
COUNT ( [DISTINCT] expr )	返回查询到的数据的数量
SUM ( [DISTINCT] expr )	返回查询到的数据的总和，不是数字没有意义
AVG ( [DISTINCT] expr )	返回查询到的数据的平均值，不是数字没有意义
MAX ( [DISTINCT] expr )	返回查询到的数据的最大值，不是数字没有意义
MIN ( [DISTINCT] expr )	返回查询到的数据的最小值，不是数字没有意义

示例：查询用户总数

SELECT COUNT(*) FROM `users`;

示例：查询电子邮件域名为 example.com 的用户总数

SELECT COUNT(*) FROM `users` WHERE `email` LIKE '%@example.com';

示例：查询用户创建时间的最早日期

SELECT MIN(`created_at`) FROM `users`;

1.7 GROUP BY

GROUP BY 用于将结果集按一个或多个列进行分组，通常与聚合函数一起使用。

SELECT `column1`, `column2`, ... FROM `table_name` GROUP BY `column1`, `column2`, ...;

示例：按电子邮件域名分组，查询每个域名的用户数量

SELECT SUBSTRING_INDEX(`email`, '@', -1) AS `domain`, COUNT(*) AS `user_count`
FROM `users`
GROUP BY `domain`;

1.8 HAVING

HAVING 用来对分组后的数据进行筛选，通常与 GROUP BY 一起使用。

SELECT `column1`, `column2`, ... FROM `table_name` GROUP BY `column1`, `column2` HAVING `condition`;

示例：查询用户数量超过 1 的电子邮件域名

SELECT SUBSTRING_INDEX(`email`, '@', -1) AS `domain`, COUNT(*) AS `user_count`
FROM `users`
GROUP BY `domain`
HAVING `user_count` > 1;

1.9 OJ练习

• https://www.nowcoder.com/practice/51c12cea6a97468da149c04b7ecf362e

• https://www.nowcoder.com/practice/ae51e6d057c94f6d891735a48d1c2397

• https://www.nowcoder.com/practice/218ae58dfdcd4af195fff264e062138f

• https://www.nowcoder.com/practice/ec1ca44c62c14ceb990c3c40def1ec6c

• https://www.nowcoder.com/practice/6d4a4cff1d58495182f536c548fee1ae

• https://www.nowcoder.com/practice/4c8b4a10ca5b44189e411107e1d8bec1

• https://www.nowcoder.com/practice/72ca694734294dc78f513e147da7821e

• https://leetcode.com/problems/duplicate-emails/description/

• https://leetcode-cn.com/problems/big-countries/description/

• https://leetcode.com/problems/nth-highest-salary/description/

 

2. 内置函数

内置函数一般放在SQL语句里帮助我们执行一些逻辑。

2.1 日期函数

函数名称	描述
current_date()	获取当前日期
current_time()	获取当前时间
current_timestamp()	获取当前时间戳
date(datetime)	返回datetime的日期部分
time(datetime)	返回datetime的时间部分
date_add (date, interval x day)	在date中添加日期或时间，单位可为年月日时分秒
date_sub (date, interval x day)	在date中减去日期或时间，单位可为年月日时分秒
datediff (date2, date1)	两个日期的差，单位是天
now()	当前日期时间

2.2 字符串函数

字符串函数	说明
charset ( str )	获取字符串字符集
concat ( string1, …)	拼接字符串
length ( string )	返回字符串的字节数
replace ( str, search_str, replace_str )	将字符串中的 replace_str 替换 search_str
substring ( str, position [, length] )	从字符串的postion位置开始截取length个字符
ucase ( string )	转换成大写
lcase ( string )	转换成小写
instr ( string, substr )	返回substr在string中出现的位置，没有返回0
left ( string, length )	从string中的左边起截取length个字符
strcmp ( string1.string2 )	逐字符比较两字符串大小
ltrim ( string ) rtrim ( string ) trim ( string )	去除前或后或前后空格

2.3 数学函数

数学函数	描述
abs ( number )	绝对值函数
bin ( decimal number )	转换二进制
hex ( decimalNumber )	十六进制
Iconv ( number, from_base, to_base )	指定进制转换
ceiling ( number )	向上去整
floor ( number )	向下去整
format ( number, decimal_places )	格式化，保留小数位数
rand ()	返回随机浮点数，范围 [ 0.0, 1.0 )
mod ( number, denominator )	取模

2.4 其他函数

其他函数	描述
user ()	返回当前用户
md5 ( string )	对字符串进行md5摘要
database ()	返回当前数据库
password ( string )	对用户加密
ifnull ( string1, string2 )	如果string1不为null则返回string1，否则返回string2