day3：记录的增删改查

最新推荐文章于 2021-12-25 16:06:42 发布

weixin_44457930

最新推荐文章于 2021-12-25 16:06:42 发布

阅读量243

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44457930/article/details/115266116

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文详述了MySQL中的数据操作，包括插入单条与多条记录、更新与删除数据，以及如何清空数据表。同时，深入探讨了查询语句，如查询所有和特定字段、使用别名、条件查找、模糊匹配以及分组聚合等高级用法，为数据库操作提供了全面的指导。

摘要由CSDN通过智能技术生成

1 数据的操作

（1）数据的查询

a 插入一条记录

-- 不指定字段名
INSERT [INTO] tbl_name VALUES|VALUE(值...)

-- 指定字段名，字段的顺序必须和值的顺序一一对应
INSERT [INTO] tbl_name(字段名称1,...) VALUES|VALUE(值1,...)

先建立一张表

-- 测试插入记录INSERT
CREATE TABLE IF NOT EXISTS user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
age TINYINT UNSIGNED DEFAULT 18
);

测试以下操作

INSERT INTO user VALUES(1,'KING','KING','KING@QQ.COM',20);

INSERT user VALUE(2,'QUEEN','QUEEN','QUEEN@QQ.COM',30);

INSERT user(username,password) VALUES('A','AAA');

-- 只要保持字段能和值对应上即可，哪个字段在前无所谓
INSERT user(password,username) VALUES('BBB','B');

INSERT user(username,id,password,email,age) VALUES('C',55,'CCC','CCC@QQ.COM',DEFAULT);

b 插入多条记录

-- 只能用VALUES，不能用VALUE
INSERT [INTO] tbl_name[(字段名称...)] VALUES(值...),(值...)...

-- 一次插入多条记录
INSERT user VALUES(6,'D','DDD','D@QQ.COM',35),
(8,'E','EEE','E@QQ.COM',9),
(18,'F','FFF','F@QQ.COM',32);

c 通过INSERT SET形式插入

INSERT [INTO] tbl_name SET 字段名称=值,...

INSERT INTO user SET id=98,username='test',password='this is a test',email='123@qq.com',
age=48;
-- INTO可以省略
INSERT user SET username='maizi',password='maizixueyuan' ;

d 插入查询结果

INSERT [INTO] tbl_name[(字段名称,...)] SELECT 字段名称 FROM tbl_name [WHERE 条件]

新建一张表

CREATE TABLE IF NOT EXISTS testUser(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE
);

将查询结果插入到表中

INSERT testUser SELECT id,username FROM user;

INSERT testUser(username) SELECT username FROM user;

-- 字段数目不匹配，下面这条命令会报错
INSERT testUser SELECT * FROM user;

（2）更新数据

UPDATE tbl_name SET 字段名称=值,... [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

现在user的内容是这样的
在这里插入图片描述
执行下面的命令之后

-- 将用户表中所有的用户年龄改成5（更新）
UPDATE user SET age=5;

在这里插入图片描述
可以看到，age那一列全部变成5。

也可以同时更新两列

UPDATE user SET age=20,email='test@qq.com';

在这里插入图片描述
也可以添加条件

-- 将第一个记录的password，email，age
UPDATE user SET password='king123',email='123@qq.com',age=99
WHERE id=1;

-- 将编号大于等于3的记录，年龄减5
UPDATE user SET age=age-5 WHERE id>=3;

-- 对符合条件的记录，把年龄改成默认值，用户名改为A
UPDATE user SET age=DEFAULT WHERE username='A';

（3）删除表中的数据

删除testUser表中的记录，用法和UPDATE完全一样

DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

表testUser在删除前
在这里插入图片描述
执行以下命令

DELETE FROM testUser ;

在这里插入图片描述
数据全被删除

也可以按照条件删除

DELETE FROM user WHERE id=1;

在这里插入图片描述
可以看到，id为1的记录被删除

（4）彻底清空数据表

用DELETE只能删除记录，不会重置AUTO_INCREMENT
当前user表的内容为
在这里插入图片描述
删除所有记录

DELETE FROM user

再重新插入

INSERT user VALUE(NULL,'QUEEN','QUEEN','QUEEN@QQ.COM',30);

在这里插入图片描述
可以看到，插入的数据从DELETE语句执行前的AUTO_INCREMENT开始计算，删除所有记录前，最大的编号是99，重新插入后，便后继续，从100开始

-- 删除所有用户
DELETE FROM testUser ;
-- 这样删除并不会重置AUTO_INCREMENT，原来编号是100，再重新插入，编号会是101
-- 可以使用 SHOW CREATE TABLE testUser查看表的详细定义，找到自增长的值看验证

-- 删除user表中id为1的用户
DELETE FROM user WHERE id=1;

要想清空数据表的同时，也重置AUTO_INCREMENT为0，可以使用TRUNCATE语句

TRUNCATE [TABLE] tbl_name

查看表的详细定义

SHOW CREATE TABLE user;

在这里插入图片描述
可以看到，当前AUTO_INCREMENT为101
执行彻底清空的命令

TRUNCATE TABLE user;

在这里插入图片描述
再重新插入

INSERT user VALUE(NULL,'QUEEN','QUEEN','QUEEN@QQ.COM',30);

在这里插入图片描述
可以看到，新的记录编号又变成了1。
（之所以要重新插入，而不是查看表的定义来确定AUTO_INCREMENT的值，是因为彻底清空之后，再查看表的定义，AUTO_INCREMENT是多少显示不出来）

2 查询语句

现有一张cms_admin表，其定义如下：
在这里插入图片描述
内容如下

（1）查询所有字段

SELECT * FROM cms_admin;

也可带上表名

SELECT cms_admin.* FROM cms_admin;

（2）查询特定字段

SELECT id,username FROM cms_admin;

在这里插入图片描述
字段顺序未必要按照定义时的顺序

-- 查询管理员编号和名称，只查询需要的字段
SELECT username,id,role FROM cms_admin;

在这里插入图片描述

（3）查询其他数据库下的表

先切换到maizi数据库，然后执行下面的语句

-- 查询其他数据库下的表，db_name.tbl_name（数据库名.表名）
SELECT id,username,role FROM cms.cms_admin;

在这里插入图片描述
也可以在字段前面加上表名

SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

（4）给表和字段起别名

a 给表起别名

SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;

这样的话，在字段前面加表名时，就无需写全程，只需要写别名就行

b 给字段起别名

显示的时候只会显示别名，不会显示原来的字段名

SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;

在这里插入图片描述
也可以同时给字段和表起别名

SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a;

在这里插入图片描述

（5）按条件查找

WHERE 条件相关的关键字和符号：
在这里插入图片描述
现有一张表cms_user，其详细定义如下：

其内容如下

练习以下命令

-- WHERE条件
-- 查询编号为1的用户
SELECT id,username,email FROM cms_user WHERE id=1;

SELECT id,username,email FROM cms_user WHERE username='king';

-- 查询编号不为1的用户
SELECT  * FROM cms_user WHERE id!=1;

SELECT  * FROM cms_user WHERE id<>1;

（6）查询空与非空

a 使用<=>

添加age字段并插入值

ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;

INSERT cms_user(username,password,regTime,proId,age)

VALUES('test1','test1',1419811708,1,NULL);

插入之后内容
在这里插入图片描述
现要查询age为NULL的字段，可以使用下面的命令

-- 查询表中记录age值为NULL
SELECT * FROM cms_user WHERE age=NULL; -- 这个不行

SELECT * FROM cms_user WHERE age<=>NULL; -- 这个可以

在这里插入图片描述
从结果中可以看到，要查询NULL，不能用等号，而应使用“<=>”，等号有的功能，它都有，它还能查空值。

b 使用 IS NULL（是否为空）和 NOT NULL（非空）

NOT的作用是取反

-- IS NULL 是否为空	IS NOT NULL 是否非空
SELECT * FROM cms_user WHERE age IS NULL;

（7）IN 与 NOT IN

-- 查询编号为1，3，5，7，9，11，13，100
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);

-- 查询proId为1 和3的用户

SELECT * FROM cms_user WHERE proId IN(1,3);

-- 也可以用于字符串，查询用户名为king,queen,张三，章子怡的记录
-- 以下两条命令等效，忽略大小写
SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');

SELECT * FROM cms_user WHERE username IN('KinG','QUEEN','张三','章子怡');

（8） BETWEEN AND 和 NOT BETWEEN AND

-- 查询编号在3~10之间的用户，即能取到3，也能取到10
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;

（9）模糊查找

-- 模糊查询
-- %:代表0个一个或者多个任意字符
-- _:代表1个任意字符
-- 查询姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';

-- 查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';

-- 跟没有写条件一样
SELECT * FROM cms_user WHERE username LIKE '%';

-- 查询用户名为3位的用户
SELECT * FROM cms_user WHERE username LIKE '___';
-- 三个下滑线

--用户名_i%
SELECT * FROM cms_user WHERE username LIKE '_I%';

SELECT * FROM cms_user WHERE username LIKE 'king';
-- LIKE这里和写等号是一样的，代表精准查询

SELECT * FROM cms_user WHERE username NOT LIKE '_I%';

（10）多个条件

-- 查询用户名为king并且密码为king的用户
SELECT * FROM cms_user WHERE username='king' AND password='king';

-- 查询编号大于等于3的变量年龄不为NULL的用户

SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;

-- 查询编号大于等于3的变量年龄不为NULL的用户 并且proId为的3
SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proId=3;

-- 查询编号在5~10的用户并且用户名为4位的用户

SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';

-- 查询用户名以张开始或者用户所在身份为2,4的记录
SELECT * FROM cms_user WHERE username LIKE '张%' OR proId IN(2,4);

3 分组聚合

（1）分组

当前cms_user内容如下：
在这里插入图片描述

（1）简单分组

执行以下命令

-- 按照用户所属省份编号分组proId，只会显示每组的第一条记录
SELECT * FROM cms_user GROUP BY proId;

在这里插入图片描述
分组查询，只会显示每组的第一条数据

向用户表中添加性别字段

-- 添加字段
ALTER TABLE cms_user ADD sex ENUM('男','女','保密');

-- 插入数据
UPDATE cms_user SET sex='男' WHERE id IN(1,3,5,7,9);

UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);

UPDATE cms_user SET sex='保密' WHERE id IN(12,11);

按照用户性别分组
在这里插入图片描述
也可以用字段的位置来代替字段，如按照proId分组时，可以这么写：

--按照字段位置分组，创建数据表时，第七个字段是proId
SELECT * FROM cms_user GROUP BY 7;

在这里插入图片描述

（2）条件分组

-- 查询编号大于等于5的用户，并按照sex分组
SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;

在这里插入图片描述

（3）按照多个字段分组

-- 先按性别分组，再按proId
SELECT * FROM cms_user GROUP BY sex,proId;

在这里插入图片描述
先按性别分，得到男性组，崽崽男性组的内部，按照proId分组，显示每个小组的第一条数据

（4）显示用户详情

GROUP_CONCAT(字段名)显示分组详情

-- 查询id,sex,用户名详情按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

在这里插入图片描述
id显示的是每个分组的第一条记录的编号，sex是组名，GROUP_CONCAT(username)是显示每组中username有哪些，即每组有哪些用户

--查询proId，用户名详情，性别详情，注册时间详情 安照proId
SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) 
FROM cms_user GROUP BY proId;
-- 也就是显示有哪些人，这些人的性别，注册时间等

在这里插入图片描述
修改年龄

UPDATE cms_user SET age=11 WHERE id=1;
UPDATE cms_user SET age=21 WHERE id=2;
UPDATE cms_user SET age=33 WHERE id=3;
UPDATE cms_user SET age=44 WHERE id=4;
UPDATE cms_user SET age=25 WHERE id=5;
UPDATE cms_user SET age=77 WHERE id=6;
UPDATE cms_user SET age=56 WHERE id=7;
UPDATE cms_user SET age=88 WHERE id=8;
UPDATE cms_user SET age=12 WHERE id=9;
UPDATE cms_user SET age=32 WHERE id=10;
UPDATE cms_user SET age=65 WHERE id=11;

在这里插入图片描述
如果觉得GROUP_CONCAT(username)太长，可以对其起别名

SELECT id,sex,GROUP_CONCAT(username)AS users,
COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;

在这里插入图片描述
COUNT(*)是聚合函数，统计个数。

（5）聚合

聚合函数是用来统计的
在这里插入图片描述

执行以下命令，体会聚合的含义

-- 统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM cms_user;

SELECT COUNT(id) AS totalUsers FROM cms_user;

-- COUNT(字段)不统计NULL值，age字段有一个NULL值，所以这里最后显示11条记录
SELECT COUNT(age) AS totalUsers FROM cms_user;

在这里插入图片描述

（6）分类聚合

a 基本形式

对每个分组进行统计

SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex;

在这里插入图片描述

b WITH ROLLUP

还可以添加WITH ROLLUP，表示对整体进行统计

SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex WITH ROLLUP;

在这里插入图片描述
可以看到，添加WITH ROLLUP后，结果中会比没加的时候多出一行，这是对cms_user中的所有记录进行统计

4 HAVING子句

通过HAVING子句对分组结果进行二次筛选，说白了就是对组进行筛选。

先看看不加HAVING子句的情况

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex;

在这里插入图片描述
有三个分组

再看看有HAVING子句的情况

-- 查询编号大于等于2的用户，
-- 即先筛选记录，再分组，然后再对分组进行筛选
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;

在这里插入图片描述
只显示一个分组了。

5 排序

（1）对记录进行排序

通过ORDER BY对查询结果排序，升序ASC，降序DESC，默认的是升序

SELECT * FROM cms_user ORDER BY age ASC;

SELECT * FROM cms_user ORDER BY age DESC;

SELECT * FROM cms_user ORDER BY age;

升序
在这里插入图片描述
降序

默认升序

也可以按照字段的位置确定排序

-- 1是字段id的位置，这里是按照字段的位置确定字段
SELECT * FROM cms_user ORDER BY 1 DESC;

在这里插入图片描述

（2）多字段排序

也可以用两个字段作为排序依据
先修改编号为5的记录

UPDATE cms_user SET age=12 WHERE id=5;

这样就有两个年龄为12的记录
在这里插入图片描述
这样如果按照年龄排序的话，会自动按照记录的先后，即id来确定顺序，现在如果希望当年龄一样时，按照id降序，那么可以使用两个字段，命令如下：

-- 先按照年龄升序，再按id降序排列
-- 两个字段排序
SELECT * FROM cms_user ORDER BY age ASC,id DESC;

（3）对组进行排序

若仅仅进行分组

SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age 
FROM cms_user 
WHERE id>=2 
GROUP BY sex
HAVING COUNT(*)>=2；

则
在这里插入图片描述
添加 ORDER BY age

SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age 
FROM cms_user 
WHERE id>=2 
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC;

在这里插入图片描述
可以看到，排序的关键字是age，是根据每个分组的第一条记录的age进行的

（4）记录的随机排序

使用RAND()函数

-- 实现记录随机排序
SELECT * FROM cms_user ORDER BY RAND();

在这里插入图片描述

6 LIMIT限制查询结果显示条数

显示查询结果的前三条记录

SELECT * FROM cms_user LIMIT 3;

在这里插入图片描述
这和编号id无关，可以先逆序，然后再显示

SELECT * FROM cms_user ORDER BY id DESC LIMIT 3;

在这里插入图片描述
可以设置偏移量

-- 偏移6，即从第7条记录开始，显示5条
SELECT * FROM cms_user LIMIT 6,5;

在这里插入图片描述

7 更新、删除记录

（1）更新记录

-- 更新用户名为4位的用户，让其已有年龄-3
UPDATE cms_user SET age=age-3 WHERE username LIKE '____';

在这里插入图片描述
也可以使用LIMIT，但不能设置偏移量

-- 更新前3条记录，让已有年龄+10，LIMIT只能有一个参数
UPDATE cms_user SET age=age+10 LIMIT 3;

-- 无法设置偏移量
UPDATE cms_user SET age=age+10 LIMIT 0,3; -- 这句有错

在这里插入图片描述
也可以加上ORDER BY

-- 按照id降序排列，更新前3条
-- ORDER BY id DESC LIMIT 3 相当于筛选条件，
-- 先按照id降序，然后取前三条，接着对这三条记录进行修改
UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3;

在这里插入图片描述
NULL加10等于没加，因此只有两条记录被影响

（2）删除记录

-- 删除用户性别为男的用户，按照年龄降序排列，删除1前一条记录
-- LIMIT同样不能设置偏移量
DELETE FROM cms_user WHERE sex='男' ORDER BY age DESC LIMIT 1;

在这里插入图片描述
可以看到，编号为7的记录被删除。

weixin_44457930

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

day3：记录的增删改查

数据的操作与查询

1 数据的操作

（1）数据的查询

a 插入一条记录

b 插入多条记录

c 通过INSERT SET形式插入

d 插入查询结果

（2）更新数据

（3）删除表中的数据

（4）彻底清空数据表

2 查询语句

（1）查询所有字段

（2）查询特定字段

（3）查询其他数据库下的表

（4）给表和字段起别名

a 给表起别名

b 给字段起别名

（5）按条件查找

（6）查询空与非空

a 使用<=>

b 使用 IS NULL（是否为空）和 NOT NULL（非空）

（7）IN 与 NOT IN

（8） BETWEEN AND 和 NOT BETWEEN AND

（9）模糊查找

（10）多个条件

3 分组聚合

（1）分组

（1）简单分组

（2） 条件分组

（3）按照多个字段分组

（4）显示用户详情

（5）聚合

（6）分类聚合

a 基本形式

b WITH ROLLUP

4 HAVING子句

5 排序

（1）对记录进行排序

（2）多字段排序

（3）对组进行排序

（4）记录的随机排序

6 LIMIT限制查询结果显示条数

7 更新、删除记录

（1）更新记录

（2）删除记录

（2）条件分组