龙溪网站制作,wordpress设计网站,在线优化网站建设,网页的后台管理系统数据处理之增删改 1.插入数据1.1实际问题1.2方式1#xff1a;VALUES的方式添加1.3方式2#xff1a;将查询结果插入到表中 2.更新数据3.删除数据4.MySQL8新特性#xff1a;计算列5.综合案例 1.插入数据
1.1实际问题 解决方式#xff1a;使用INSERT语句向表中插入数据
1.2方… 数据处理之增删改 1.插入数据1.1实际问题1.2方式1VALUES的方式添加1.3方式2将查询结果插入到表中 2.更新数据3.删除数据4.MySQL8新特性计算列5.综合案例 1.插入数据
1.1实际问题 解决方式使用INSERT语句向表中插入数据
1.2方式1VALUES的方式添加
使用这种语法一次只能向表中插入一条数据。 情况1为表的所有字段按默认顺序插入数据
INSERT INTO 表名
VALUES (value1,value2,....);值列表中需要为表得到每一个字段指定值并且值的顺序必须和数据表中字段定义时的顺序相同 举例
INSERT INTO departments
VALUES (70, Pub, 100, 1700);INSERT INTO departments
VALUES (100, Finance, NULL, NULL);情况2为表的指定字段插入数据
INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);为表的指定字段插入数据就是在INSERT语句中只向部分字段中插入值而其他字段的值为表定义时的默认值。 在 INSERT 子句中随意列出列名但是一旦列出VALUES中要插入的value1,…valuen需要与column1,…columnn列一一对应。如果类型不同将无法插入并且MySQL会产生错误。 举例
INSERT INTO departments(department_id, department_name)
VALUES (80, IT);情况3同时插入多条记录 INSERT语句可以同时向数据表中插入多条记录插入时指定多个值列表每个值列表之间用逗号分隔开基本语法格式如下
INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);或者
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);举例
mysql INSERT INTO emp(emp_id,emp_name)
- VALUES (1001,shkstart),
- (1002,atguigu),
- (1003,Tom);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0使用INSERT同时插入多条记录时MySQL会返回一些在执行单行插入时没有的额外信息这些信息的含义如下 ● Records表明插入的记录条数。 ● Duplicates表明插入时被忽略的记录原因可能是这 些记录包含了重复的主键值。 ● Warnings表明有问题的数据值例如发生数据类型转换。 一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句但是多行的INSERT语句 在处理过程中 效率更高 。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句 快所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。 小结 VALUES也可以写成VALUE但是VALUES是标准写法。 字符和日期型数据应包含在单引号中。
1.3方式2将查询结果插入到表中
INSERT还可以将SELECT语句查询的结果插入到表中此时不需要把每一条记录的值一个一个输入只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速从一个或多个表中向一个表中插入多行。 基本语法格式如下
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]在INSERT语句中加入子查询不必书写VALUES子句子查询中的值列表应与INSERT子句中的列名对应 举例
INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id 90;INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE %REP%;2.更新数据 使用UPDATE语句更新数据。语法如下
UPDATE table_name
SET column1value1, column2value2, … , columnvaluen
[WHERE condition]可以一次更新多条数据如果需要回滚数据需要保证在DML前进行设置SET AUTOCOMMIT FALSE;使用WHERE子句指定更新的数据
UPDATE employees
SET department_id 70
WHERE employee_id 113;如果省略WHERE子句则表中的所有的数据都将被更新
UPDATE copy_emp
SET department_id 110;更新中的数据完整性错误
UPDATE employees
SET department_id 55
WHERE department_id 110;说明不存在55号部门 3.删除数据 使用DELETE语句从表中删除数据
DELETE FROM table_name [WHERE condition];table_name指定要执行删除操作的表“[WHERE ]”为可选参数指定删除条件如果没有WHERE子句DELETE语句将删除表中的所有记录。
使用WHERE子句删除指定的记录
DELETE FROM departments
WHERE department_name Finance;如果省略WHERE子句则表中的全部数据将被删除
DELETE FROM copy_emp;删除表中的数据完整性错误
DELETE FROM departments
WHERE department_id 60;说明You cannot delete a row that contains a primary key that is used as a foreign key in another table. 4.MySQL8新特性计算列
什么叫计算列呢简单来说就是某一列的值是通过别的列计算得来的。例如a列值为1、b列值为2c列不需要手动插入定义ab的结果为c的值那么c就是计算列是通过别的列计算得来的。
在MySQL 8.0中CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。
举例定义数据表tb1然后定义字段id、字段a、字段b和字段c其中字段c为计算列用于计算ab的值。 首先创建测试表tb1语句如下
CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a b) VIRTUAL
);插入演示数据语句如下
INSERT INTO tb1(a,b) VALUES (100,200);查询表tb1中的数据结果如下
mysql SELECT * FROM tb1;
------------------------
| id | a | b | c |
------------------------
| NULL | 100 | 200 | 300 |
------------------------
1 row in set (0.00 sec)更新数据中的语句语句如下:
mysql UPDATE tb1 SET a 500;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 05.综合案例
创建数据库test01_library创建表 books表结构如下
字段名字段说明数据类型id书编号INTname书名VARCHAR(50)authors作者VARCHAR(100)price价格FLOATpubdate出版日期YEARnote说明VARCHAR(100)num库存INT
向books表中插入记录 1不指定字段名称插入第一条记录 2指定所有字段名称插入第二记录 3同时插入多条记录剩下的所有记录
idnameauthorepricepubdatenotenum1Tal of AAADickes231995novel112EmmaTJane lura351993joke223Story of Jane JaneTim402001novel04Lovey DayGeorge Byron202005novel305Old landHonore Blade302010law06The BattleUpton Sara301999medicine407Rose HoodRichard haggard282008cartoon28
将小说类型(novel)的书的价格都增加5。将名称为EmmaT的书的价格改为40并将说明改为drama。删除库存为0的记录。统计书名中包含a字母的书统计书名中包含a字母的书的数量和库存总量找出“novel”类型的书按照价格降序排列查询图书信息按照库存量降序排列如果库存量相同的按照note升序排列按照note分类统计书的数量按照note分类统计书的库存量显示库存量超过30本的查询所有图书每页显示5本显示第二页按照note分类统计书的库存量显示库存量最多的查询书名达到10个字符的书不包括里面的空格查询书名和类型其中note值为novel显示小说law显示法律medicine显示医药cartoon显示卡通joke显示笑话查询书名、库存其中num值超过30本的显示滞销大于0并低于10的显示畅销为0的显示需要无货统计每一种note的库存量并合计总量统计每一种note的数量并合计总量统计库存量前三名的图书找出最早出版的一本书找出novel中价格最高的一本书找出书名中字数最多的一本书不含空格
1.创建数据库test01_library
CREATE DATABASE test01_library CHARACTER SET uft8;
#指定使用哪个数据库
USE test01_library;2.创建表 books
CREATE TABLE books(
id INT,
name VARCHAR(50),
authors VARCHAR(100) ,
price FLOAT,
pubdate YEAR ,
note VARCHAR(100),
num INT
);3.向books表中插入记录
# 1不指定字段名称插入第一条记录
INSERT INTO books
VALUES(1,Tal of AAA,Dickes,23,1995,novel,11);
# 2指定所有字段名称插入第二记录
INSERT INTO books (id,name,authors,price,pubdate,note,num)
VALUES(2,EmmaT,Jane lura,35,1993,Joke,22);
# 3同时插入多条记录剩下的所有记录
INSERT INTO books (id,name,authors,price,pubdate,note,num) VALUES
(3,Story of Jane,Jane Tim,40,2001,novel,0),
(4,Lovey Day,George Byron,20,2005,novel,30),
(5,Old land,Honore Blade,30,2010,Law,0),
(6,The Battle,Upton Sara,30,1999,medicine,40),
(7,Rose Hood,Richard haggard,28,2008,cartoon,28);4.将小说类型(novel)的书的价格都增加5。
UPDATE books SET priceprice5 WHERE note novel;5.将名称为EmmaT的书的价格改为40并将说明改为drama。
UPDATE books SET price40,notedrama WHERE nameEmmaT;6.删除库存为0的记录。
DELETE FROM books WHERE num0;7.统计书名中包含a字母的书
SELECT * FROM books WHERE name LIKE %a%;8.统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num) FROM books WHERE name LIKE %a%;9.找出“novel”类型的书按照价格降序排列
SELECT * FROM books WHERE note novel ORDER BY price DESC;10.查询图书信息按照库存量降序排列如果库存量相同的按照note升序排列
SELECT * FROM books ORDER BY num DESC,note ASC;11.按照note分类统计书的数量
SELECT note,COUNT(*) FROM books GROUP BY note;12.按照note分类统计书的库存量显示库存量超过30本的
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)30;13.查询所有图书每页显示5本显示第二页
SELECT * FROM books LIMIT 5,5;14.按照note分类统计书的库存量显示库存量最多的
SELECT note,SUM(num) sum_num FROM books GROUP BY note ORDER BY sum_num DESC LIMIT 0,1;15.查询书名达到10个字符的书不包括里面的空格
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name, ,))10;/*
16. 查询书名和类型其中note值为 novel显示小说law显示法律medicine显示医药cartoon显示卡通joke显示笑话
*/
SELECT name AS 书名 ,note, CASE note WHEN novel THEN 小说WHEN law THEN 法律WHEN medicine THEN 医药WHEN cartoon THEN 卡通WHEN joke THEN 笑话END AS 类型
FROM books;17.查询书名、库存其中num值超过30本的显示滞销大于0并低于10的显示畅销为0的显示需要无货
SELECT name,num,CASE WHEN num30 THEN 滞销WHEN num0 AND num10 THEN 畅销WHEN num0 THEN 无货ELSE 正常END AS 库存状态
FROM books;18.统计每一种note的库存量并合计总量
SELECT IFNULL(note,合计总库存量) AS note,SUM(num) FROM books GROUP BY note WITH ROLLUP;19.统计每一种note的数量并合计总量
SELECT IFNULL(note,合计总数) AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP;20.统计库存量前三名的图书
SELECT * FROM books ORDER BY num DESC LIMIT 0,3;21.找出最早出版的一本书
SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;22.找出novel中价格最高的一本书
SELECT * FROM books WHERE note novel ORDER BY price DESC LIMIT 0,1;23.找出书名中字数最多的一本书不含空格
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name, ,)) DESC LIMIT 0,1;
