目录
UPDATE ... SET ... [WHERE] ...
一. 插入数据一条一条插入数据
1.1 方式一: 一条一条插入数据
insert into 表名[(字段1,字段2...)] values (值1,值2...), [(值1,值2)]
USE `atguigudb`; CREATE TABLE emp1( id INT, `name` VARCHAR(15), hire_date DATE, salary DOUBLE(10,2) ); DESC emp1; #1.没有指明添加的字段,按照声明字段的先后顺序添加 INSERT INTO emp1 VALUES (1,'Tom','2001-1-1',12000); #2.声明了添加的字段(推荐) INSERT INTO emp1(id,`name`,hire_date,salary) VALUES (2,'Jack','2002-1-1',13000); INSERT INTO emp1(id,hire_date,`name`,salary) VALUES (3,'2003-1-1','Lili',14000); INSERT INTO emp1(id,`name`) VALUES (4,'Sue'); #3. values插入多条 INSERT INTO emp1(id,NAME,salary) VALUES(7,'王五',17000), (8,'李四',16000); 一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句 在处理过程中效率更高。 SELECT * FROM emp1;
1.2 方式二: 将查询结果插入表中
INSERT INTO emp1(id,NAME,hire_date,salary)#+查询语句 SELECT employee_id,last_name,hire_date,salary FROM employees WHERE department_id IN(60,70); SELECT * FROM emp1; #注意表中数据类型精度损失,从大->小 不可以 #使用前注意表中字段范围
二.更新数据
UPDATE ... SET ... [WHERE] ...
- 可以一次更新多条数据。
- 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
UPDATE employees SET department_id = 70 WHERE employee_id = 113;
三. 删除数据
DELETE FROM table_name [ WHERE < condition >];
- table_name指定要执行删除操作的表；
- “[WHERE ]”为可选参数，指定删除条件
- 如果没有WHERE子句， DELETE语句将删除表中的所有记录。
四.MySQL8新特性：计算列
1. 创建表时表明
CREATE TABLE tb1( id INT, a INT, b INT, c INT GENERATED ALWAYS AS (a + b) VIRTUAL );generated always virtual
添加a,b的值,c的值自动改变
五.综合案例
# 1、创建数据库test01_library CREATE DATABASE IF NOT EXISTS test01_library ; USE test01_library; # 2、创建表 books，表结构如下： CREATE TABLE IF NOT EXISTS books( id INT, NAME VARCHAR(50), AUTHORS VARCHAR(100), price FLOAT , pubdate YEAR , note VARCHAR(100) , num INT); SELECT * FROM books; # 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 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 price=price+5 WHERE note='novel'; # 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。 UPDATE books SET price=40,note='drama' WHERE NAME='EmmaT'; # 6、删除库存为0的记录。 DELETE FROM books WHERE num=0; # 7、统计书名中包含a字母的书 SELECT NAME FROM books WHERE NAME LIKE '%a%'; # 8、统计书名中包含a字母的书的数量和库存总量 SELECT num,SUM(num) FROM books WHERE NAME LIKE '%a%'; # 9、找出“novel”类型的书，按照价格降序排列 SELECT NAME,note,price 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个字符的书，不包括里面的空格 LENGTH(NAME) 包含空格 SELECT NAME FROM books WHERE LENGTH(REPLACE(NAME,' ',''))>10; # 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通， joke显示笑话 SELECT NAME,note, CASE note WHEN 'novel' THEN '小说' WHEN 'law' THEN '法律' WHEN 'medicine' THEN '医药' WHEN 'cartoon' THEN '卡通' WHEN 'joke' THEN '笑话' ELSE '其他' END '类型' FROM books; # 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货 SELECT NAME,num '库存',CASE WHEN num>=30 THEN'滞销' WHEN num>0 AND num<10 THEN '畅销' WHEN num=0 THEN '无货' ELSE '正常' END '显示状态' FROM books; # 18、统计每一种note的库存量，并合计总量 SELECT IFNULL(note,'合计库存总量') '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 NAME,note,num FROM books ORDER BY num LIMIT 0,3; # 21、找出最早出版的一本书 SELECT * FROM books ORDER BY pubdate LIMIT 0,1; # 22、找出novel中价格最高的一本书 SELECT * FROM books WHERE note='novel' ORDER BY price DESC LIMIT 0,1; # 23、找出书名中字数最多的一本书，不含空格 #方式一 SELECT * FROM books ORDER BY LENGTH(REPLACE(NAME,' ','')) DESC LIMIT 0,1; #方式二 SELECT * FROM books WHERE LENGTH(REPLACE(NAME,' ','')) >= ALL( SELECT LENGTH(REPLACE(NAME,' ','')) FROM books)
六. 章节练习
#1. 创建数据库dbtest11
CREATE DATABASE IF NOT EXISTS dbtest11;
#2. 运行以下脚本创建表my_employees
USE dbtest11;
CREATE TABLE 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 );
#3. 显示表my_employees的结构
DESC my_employees;
#4. 向my_employees表中插入下列数据
方式一
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
方式二
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION ALL
SELECT 2,'Dancs','Betty','Bdancs',860 UNION ALL
SELECT 3,'Biri','Ben','Bbiri',1100 UNION ALL
SELECT 4,'Newman','Chad','Cnewman',750 UNION ALL
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
SELECT *
FROM my_employees;
#5. 向users表中插入数据
INSERT INTO users
VALUES (1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);
SELECT *
FROM users;
#6. 将3号员工的last_name修改为“drelxer”
UPDATE my_employees
SET last_name = "drelxer"
WHERE id=3;
#7. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
SET salary = 1000
WHERE salary <900;
#8. 将userid为Bbiri的user表和my_employees表的记录全部删除
方式一
DELETE m,u
FROM my_employees m
JOIN users u
ON u.id = m.id
WHERE m.userid='Bbiri';
方式二
DELETE FROM my_employees
WHERE userid='Bbiri';
DELETE FROM users
WHERE userid='Bbiri';
#9. 删除my_employees、users表所有数据
方式一
DELETE u,e
FROM users u
JOIN my_employees e
ON e.id = u.id;
方式二
DELETE FROM my_employees;
DELETE FROM users;
#10. 检查所作的修正
SELECT *
FROM users;
SELECT *
FROM my_employees;
#11. 清空表my_employees
TRUNCATE FROM my_employees;
# 1. 使用现有数据库dbtest11
USE dbtest11;
# 2. 创建表格pet
CREATE TABLE IF NOT EXISTS pet(
`name` VARCHAR(20),
`owner` VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth YEAR,
death YEAR);
# 3. 添加记录
INSERT INTO pet
VALUES ('Fluffy','harold','Cat','f',2003,2010),
INSERT INTO pet(NAME,OWNER,species,sex,birth) VALUES('Claws','gwen','Cat','m',2004)
INSERT INTO pet(NAME,species,sex,birth) VALUES('Buffy','Dog','f',2009);
INSERT INTO pet(NAME,OWNER,species,sex,birth) VALUES('Fang','benny','Dog','m',2000);
INSERT INTO pet
VALUES ('bowser','diane','Dog','m',2003,2009);
INSERT INTO pet(NAME,species,sex,birth) VALUES('Chirpy','Bird','f',2008);
SELECT *
FROM pet;
# 4. 添加字段:主人的生日owner_birth DATE类型。
ALTER TABLE pet ADD owner_birth DATE;
# 5. 将名称为Claws的猫的主人改为kevin
UPDATE pet
SET OWNER='kevin'
WHERE NAME = 'Claws';
# 6. 将没有死的狗的主人改为duck
UPDATE pet
SET OWNER = 'duck'
WHERE death IS NULL;
# 7. 查询没有主人的宠物的名字;
SELECT NAME
FROM pet
WHERE OWNER IS NULL;
# 8. 查询已经死了的cat的姓名，主人，以及去世时间；
SELECT NAME,OWNER,death
FROM pet
WHERE death IS NOT NULL
AND species = 'Cat';
# 9. 删除已经死亡的狗
SELECT *
FROM pet
WHERE species = 'Dog'
AND death IS NOT NULL;
# 10. 查询所有宠物信息
SELECT *
FROM pet;# 1. 使用已有的数据库dbtest11
USE dbtest11;
# 2. 创建表employee，并添加记录
CREATE TABLE IF NOT EXISTS employee(
id INT,
NAME VARCHAR(15),
sex CHAR(1),
tel VARCHAR(25),
addr VARCHAR(35),
salary DOUBLE(10,2));
INSERT INTO employee(id,`name`,sex,tel,addr,salary)
VALUES (10001,'张一一','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13454319000','河北保定',1201.21),
(10003,'李四','男','0751-1234567','广东佛山',1004.11),
(10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
(10005,'王艳','男','020-1232133','广东广州',1405.16);
SELECT *
FROM employee;
# 3. 查询出薪资在1200~1300之间的员工信息。
SELECT *
FROM employee
WHERE salary BETWEEN 1200 AND 1300;
# 4. 查询出姓“刘”的员工的工号，姓名，家庭住址。
SELECT id,NAME,addr
FROM employee
WHERE `name` LIKE '刘%';
# 5. 将“李四”的家庭住址改为“广东韶关”
UPDATE employee
SET addr = "广东韶关"
WHERE NAME = "李四" ;
# 6. 查询出名字中带“小”的员工
SELECT NAME
FROM employee
WHERE NAME LIKE '%小%';
952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
