1. 插入数据
1.1 实际问题
1.2 方式1：VALUES的方式添加
#方式1：一条一条添加数据
#1.没有指明添加字段，
INSERT INTO emp1
VALUES (1,'tom','2000-12-21',3400);#一定按照声明的先后顺序添加
#2.指明添加字段（推荐）
INSERT INTO emp1(id,hire_date,`name`,salary)
VALUES (2,'1999-09-09','jeru',3000);
INSERT INTO emp1(id,`name`,salary)
VALUES (3,'lou',4000);
#3.同时插入多条数据（推荐）
INSERT INTO emp1(id,`name`,salary)
VALUES (4,'jim',4000),(5,'shu',4000);1.3 方式2：将查询结果插入到表中
#方式2：将查询结果插入表
SELECT * FROM emp1;
INSERT INTO emp1(id,NAME,salary,hire_date)
SELECT employee_id,last_name,salary,hire_date #查询的字段要一一对应
FROM employees
WHERE department_id IN (70,60);#说明：emp1表中要添加数据的字段长度不能低于emplyees中字段的长度
# ，否者，就不能将emplyees表的数据进行添加
2. 更新数据
#2.更新数据（修改数据）
#UPDATE....SET...WHERE
#可以实现批量数据的修改
UPDATE emp1
SET hire_date=NOW()
WHERE id=5;
SELECT *FROM emp1;
#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date=NOW(),salary=6000
WHERE id=4;
#题目：将表中姓名中包含a的提薪20%
UPDATE emp1
SET salary=(1+0.2)*salary
WHERE `name` LIKE '%a%';
#修改数据时，可能不成功的情况（可能是由于约束的情况）
UPDATE employees
SET department_id=10000000;
WHERE employee_id=102;
3. 删除数据
#3.删除数据
#DELETE...FROM....WHERE
DELETE FROM emp1
WHERE id=1;
#在删除数据时，也会有约束的影响，导致删除失败
DELETE FROM departments
WHERE department_id=50;
#小结：DML操作下，执行完后会自动提交数据
# 如果需要执行完后数据不自动提交，需要使用 SET autocommit = FALSE;4. MySQL8新特性：计算列
简单来说就是某一列的值是通过别的列计算得来的。
#4.MYSQL的新特性：计算列
CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a+b) VIRTUAL
);
DESC test1;
INSERT INTO test1(a,b)
VALUES (10,20);
SELECT *FROM test1;
UPDATE test1
SET a=100;5. 综合案例
5. 综合案例
DROP DATABASE test01_library;
DROP TABLE books;
# 1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';
# 2、创建表 books，表结构如下：
CREATE TABLE books(
id INT,
`name` VARCHAR(50),
`authors` VARCHAR(100),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT
);
DESC books;
# 3、向books表中插入记录
# 1）不指定字段名称，插入第一条记录
INSERT INTO books
VALUES(1,' Tal of AAA','Dickes',23,1995,'novel',11);
SELECT * FROM books;
# 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 price=price+5
WHERE note='novel';
SELECT *FROM books;
# 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 COUNT(*),SUM(num)
FROM books
WHERE `name` LIKE '%a%';
SELECT *FROM books;
# 9、找出“novel”类型的书，按照价格降序排列
SELECT `name`,note
FROM books
WHERE note='novel'
ORDER BY price DESC;
# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT *
FROM books
ORDER BY num DESC,note;
# 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)
FROM books
GROUP BY note
HAVING SUM(num)=(
SELECT MAX(m)
FROM(
SELECT SUM(num) m
FROM books
GROUP BY note
) al
);
# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT NAME
FROM books
WHERE CHAR_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 AS '类型'
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,'合计') AS note,SUM(num)
FROM books
GROUP BY note WITH ROLLUP;
# 19、统计每一种note的数量，并合计总量
SELECT IFNULL(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
LIMIT 0,1;
# 22、找出novel中价格最高的一本书
SELECT *
FROM books
ORDER BY price DESC
LIMIT 0,1;
# 23、找出书名中字数最多的一本书，不含空格
SELECT *
FROM books
ORDER BY CHAR_LENGTH(REPLACE(NAME,' ','')) DESC
LIMIT 0,1;课后练习
#第11章_数据处理之增删改练习题
#1. 创建数据库dbtest11
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';
#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表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
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(id,first_name,last_name,userid,salary)
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);
SELECT *FROM my_employees;
#方式二：
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;
#5. 向users表中插入数据
DESC users;
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
INSERT INTO users(id,userid,department_id)
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;
SELECT *FROM my_employees;
#7. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
SET salary=1000
WHERE salary <900;
#8. 将userid为Bbiri的user表和my_employees表的记录全部删除
#方式1；
DELETE FROM users
WHERE userid='Bbiri' ;
DELETE FROM my_employees
WHERE userid='Bbiri' ;
#方式2：
DELETE m,u
FROM my_employees m
JOIN users u
ON m.userid=u.userid
WHERE m.userid='Bbiri';
#9. 删除my_employees、users表所有数据
DELETE FROM my_employees;
DELETE FROM users;
#10. 检查所作的修正
SELECT *FROM users;
SELECT *FROM my_employees;
#11. 清空表my_employees
TRUNCATE TABLE my_employees;
#练习2
# 1. 使用现有数据库dbtest11
# 2. 创建表格pet
`name` 宠物名称 VARCHAR(20)
`owner` 宠物主人 VARCHAR(20)
species 种类 VARCHAR(20)
sex 性别 CHAR(1)
birth 出生日期 YEAR
death 死亡日期 YEAR
CREATE TABLE pet(
`name` VARCHAR(20) ,
`owner` VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth YEAR,
death YEAR
);
DESC pet;
# 3. 添加记录
Fluffy harold Cat f 2003 2010
Claws gwen Cat m 2004
Buffy Dog f 2009
Fang benny Dog m 2000
bowser diane Dog m 2003 2009
Chirpy Bird f 2008
INSERT INTO pet(NAME,OWNER,species,sex,birth,death)
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(NAME,OWNER,species,sex,birth,death)
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 AND species='Dog';
# 7. 查询没有主人的宠物的名字；
SELECT NAME
FROM pet
WHERE OWNER IS NULL;
# 8. 查询已经死了的cat的姓名，主人，以及去世时间；
SELECT NAME,OWNER,death
FROM pet
WHERE species='Cat' AND death IS NOT NULL;
# 9. 删除已经死亡的狗
DELETE FROM pet
WHERE death IS NOT NULL AND species ='Dog';
# 10. 查询所有宠物信息
SELECT *
FROM pet;
练习3
# 1. 使用已有的数据库dbtest11
USE dbtest11;
# 2. 创建表employee，并添加记录
id NAME sex tel addr salary
10001 张一一 男13456789000 山东青岛 1001.58
10002刘小红 女 13454319000 河北保定1201.21
10003李四 男 0751-1234567广东佛山 1004.11
10004刘小强男 0755-5555555广东深圳1501.23
10005王艳女020-1232133广东广州 1405.16
CREATE TABLE employee(
id INT(5),
`name` VARCHAR(13),
sex CHAR(1),
tel VARCHAR(20),
addr VARCHAR(10),
salary DOUBLE (10,2)
)
DESC employee;
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 *
FROM employee
WHERE NAME LIKE '%小%';
扫一扫
1401
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
