MySQL： 对表数据的增删改的相关练习题

MySQL： 对表数据的增删改的相关练习题

---

每博一文案

举世欲知而不加劝，举世非之而不加沮，即便世上所有人都称赞他，
他并不因此，就特别奋勉，世上所有的人都诽谤他，他也并不因此
而感到沮丧，只要清楚自己寻求什么，能够守住自己的底线，就
能在这个充满闲言碎语的世界清净的活着。
余生，不强求，所有人的理解，只想问心无愧地活着，走好自己的每一步。
                           ——————   一禅心灵庙语

对表数据的增删改的相关练习题

对于数据库来说，使用频率最多的，除了对数据的查询以外，就是对表中的数据的增删改了。

对于表的数据的处理的详细操作，大家可以移步到 🔜🔜🔜

下面是为了巩固对表数据处理的增删改操作的一些练习题：

---

练习:1

向把表books创建出来，基于该表做相应的增删改

CREATE TABLE IF NOT EXISTS books 
(
    id INT,
    `name` VARCHAR(50),      /* name, authors 与关键字冲突了,需要使用上着重号区分*/
    `authors` VARCHAR(100),
    price FLOAT,
    pubdate YEAR,
    note VARCHAR(100),
    num INT
);

---

• 向表books 中插入数据记录,使用为指明的字段的方式插入第一条记录

INSERT INTO books
VALUES (1, 'Tal of AA', 'Dickes', 23, '1995','novel',11);

---

• 指名所有字段名称的方式，插入第二组数据

                       /* name ,authors 与关键字冲突需要使用着重号区分*/
INSERT INTO books (id, `name`, `authors`, price, pubdate, note, num)   
VALUES (2,'Emmal', 'Jane lula', 35, '1993', 'joke', 22);

---

• 同时插入多条数据，把剩下所有的记录都插入进表中，使用逗号分隔开

INSERT INTO books(id, `name`, `authors`, price, pubdate, note, num )
VALUES (4,'lover day', 'george byron', 20, 2005, 'novel', 30),
       (5, 'old land', 'honore blade', 30, 2020, 'low',0),
       (6, 'the battle', 'upton sara', 30, 1999, 'medicine', 40),
       (7, 'rose hood', 'richard hoggcrd', 28, 2008, 'cartoon',28);

---

• 将小说类型 novel 的书的价格都增加50

UPDATE books
SET price = price + 50
WHERE note = 'novel';

---

• 将名称为 Emmal 的书的价格改为 40 ，并将说明改为 drama

UPDATE books
SET price = 40, note = 'drama'
WHERE `name` = 'Emmal';
/* name 与关键字冲突,使用着重号区分*/

---

• 删除库存为 0 的记录

DELETE FROM books
WHERE num = 0;

---

• 查找书名中字数 最多的一本书，不含空格

SELECT * 
FROM books
ORDER BY CHAR_LENGTH (REPLACE(`name`,' ','')) DESC /* REPLACE将name字段内容为中的空格替换成了 无,再通过*/
LIMIT 0,1;                                     /* char_length 计算去除空格之后的字符串字符个数,最后排序*/
/* 分页，显示一个记录*/

---

• 统计每一种 note 的库存量，并合计总量

SELECT note, SUM(num)
FROM books
GROUP BY note WITH ROLLUP;
/* with rollup 会将分组中所有的总和计算出来
   但是却是以 NULL的方式显示 */

优化 可使用 ifnull 将 null 替换成我们想要的

SELECT IFNULL(note,'合计库存总量'), SUM(num)
FROM books
GROUP BY note WITH ROLLUP;
/* with rollup 会将分组中所有的总和计算出来
   但是却是以 NULL的方式显示 */

---

• 统计每一种 note 的数量，并合计总量

/* ifnull 会将 NULL 替换为 '合计总量'*/
SELECT IFNULL(note, '合计总量') AS note, COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;
/* with rollup 会将分组后的总值计算出来，以NULL显示 */

---

练习2

首先创建需要使用到的表 my_employees

CREATE TABLE IF NOT EXISTS my_employees
(
    id INT(10),
    first_name VARCHAR(10),
    last_name VARCHAR(10),
    userid VARCHAR(10),
    salary DOUBLE(10,2)
);

CREATE TABLE users
(
    id INT,
    userid VARCHAR(10),
    department_id INT
);

SHOW TABLES
FROM test01;  /* 查看指定数据库下的所有表*/

---

• 向表my_employees插入数据

INSERT INTO my_employees ( id, first_name, last_name, userid,salary)
VALUES (1,'patel', 'ralph', 'rpatel', 8950),
       (2,'Dancs', 'Betty', 'Bdancs', 8600),
       (3,'Biri', 'Ben', 'Bbiri', 21000),
       (4,'Newman', 'Chad', 'Chewman', 7500),
       (5,'Ropeburn', 'Audrey', 'Aropebur', 8950);

---

• 向 users 表中插入数据

INSERT INTO users ( id, userid, department_id)
VALUES (1,'Rpatel',10),
       (2,'Bdancs',10),
       (3,'Bbir',20),
       (4,'Cnewman',30),
       (5,'Aropebur',40);

或者,使用查询表连接

/* 通过查询结果插入数据，union all 将查询结果拼接再一起，不去重复*/
INSERT INTO users (id, userid, department_id)
SELECT 1,'patel', 'ralph', 'rpatel', 8950 UNION ALL
SELECT 2,'Dancs', 'Betty', 'Bdancs', 8600 UNION ALL
SELECT 3,'Biri', 'Ben', 'Bbiri', 21000 UNION ALL
SELECT 4,'Newman', 'Chad', 'Chewman', 7500 UNION ALL
SELECT 5,'Ropeburn', 'Audrey', 'Aropebur', 8950;    
   

---

• 将所有工资少于 9000的修改为 10000

UPDATE my_employees
SET salary = 10000
WHERE salary < 9000;

---

• 将 userid 为 Bbiri 的 users 表 和 my_employees 表的记录全部删除

方式1，通过表连接的方式 ,建立练习统一删除

DELETE m, u
FROM my_employees m
JOIN users u
ON m.`userid` = u.`userid`  /* 表连接条件 */
WHERE m.`userid` = 'Bbiri';

方式2 ： 一个一个的删除

DELETE FROM users
WHERE userid = 'Bbiri';

DELETE FROM my_employees
WHERE userid = 'Bbiri';

• 删除 my_employees , users 表中的数据

方式1 使用 DELETE 可回滚，速度慢

DELETE FROM  my_employees;
DELETE FROM  users;

方式2 使用 TRUNCATE 不可回滚，速度快，不可筛选

TRUNCATE TABLE my_employees;
TRUNCATE TABLE users;

---

最后:

限于自身水平，其中存在的错误，希望大家给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见 ！！！