系统管理网站,app开发公司杭州,网站颜色搭配表,播放量网站推广免费视频链接#xff1a;【MySQL数据库入门到大牛#xff0c;mysql安装到优化#xff0c;百科全书级#xff0c;全网天花板】 文章目录 第11章_数据处理之增删改1. 插入数据1.1 实际问题1.2 方式1#xff1a;VALUES的方式添加1.3 方式2#xff1a;将查询结果插入到表中 2. 更… 视频链接【MySQL数据库入门到大牛mysql安装到优化百科全书级全网天花板】 文章目录 第11章_数据处理之增删改1. 插入数据1.1 实际问题1.2 方式1VALUES的方式添加1.3 方式2将查询结果插入到表中 2. 更新数据3. 删除数据4. MySQL8新特性计算列5. 综合案例 第11章_数据处理之增删改
讲师尚硅谷-宋红康江湖人称康师傅
官网http://www.atguigu.com 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. 综合案例
# 1、创建数据库test01_library# 2、创建表 books表结构如下字段名字段说明数据类型id书编号INTname书名VARCHAR(50)authors作者VARCHAR(100)price价格FLOATpubdate出版日期YEARnote说明VARCHAR(100)num库存INT
# 3、向books表中插入记录# 1不指定字段名称插入第一条记录
# 2指定所有字段名称插入第二记录
# 3同时插入多条记录剩下的所有记录idnameauthorspricepubdatenotenum1Tal of AAADickes231995novel112EmmaTJane lura351993joke223Story of JaneJane Tim402001novel04Lovey DayGeorge Byron202005novel305Old landHonore Blade302010law06The BattleUpton Sara301999medicine407Rose HoodRichard haggard282008cartoon28
# 4、将小说类型(novel)的书的价格都增加5。# 5、将名称为EmmaT的书的价格改为40并将说明改为drama。# 6、删除库存为0的记录。# 7、统计书名中包含a字母的书# 8、统计书名中包含a字母的书的数量和库存总量# 9、找出“novel”类型的书按照价格降序排列# 10、查询图书信息按照库存量降序排列如果库存量相同的按照note升序排列# 11、按照note分类统计书的数量# 12、按照note分类统计书的库存量显示库存量超过30本的# 13、查询所有图书每页显示5本显示第二页# 14、按照note分类统计书的库存量显示库存量最多的# 15、查询书名达到10个字符的书不包括里面的空格# 16、查询书名和类型其中note值为novel显示小说law显示法律medicine显示医药cartoon显示卡通joke显示笑话# 17、查询书名、库存其中num值超过30本的显示滞销大于0并低于10的显示畅销为0的显示需要无货# 18、统计每一种note的库存量并合计总量# 19、统计每一种note的数量并合计总量# 20、统计库存量前三名的图书# 21、找出最早出版的一本书# 22、找出novel中价格最高的一本书# 23、找出书名中字数最多的一本书不含空格答案
#1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET utf8;#指定使用哪个数据库
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;
