MySQL之数据处理之增删改

#数据处理之增删改
#储备工作
USE atguigudb;

CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);



DESC emp1;

SELECT *
FROM emp1;

#添加数据
#方式一：一条一条添加数据

#① 没有指明添加的字段

#正确的
INSERT INTO emp1
VALUE(1,'tom','2000-12-21',3400);#注意：一定要按照字段的声明顺序添加

#错误的
INSERT INTO emp1
VALUE(2,3400,'2000-12-25','Jerry');

#② 指明要添加的字段（推荐）
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-9-09',4000,'Jerry');
#说明：没有进行赋值的hire_date的值为NULL
INSERT INTO emp1(id,salary,`name`)
VALUES(3,4500,'shk');

#③ 同时插入多条记录(推荐)
INSERT INTO emp1(id,`name`,salary)
VALUES
(4,'Jim',5000),
(5,'火星包',5500);

#方式二：将查询结果插入到表中

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);

DESC emp1;
DESC employees;

#说明：emp1表中要添加数据的字段的长度不能低于employees表中查询字段的长度。

#如果emp1表中要添加数据的字段的长度低于employees表中查询字段的长度的话，就有添加不成功的风险。

#更新数据（或修改数据）
#UPDATE.....SET.....WHERE....
#可以实现批量修改数据的

UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5;

SELECT * FROM emp1;

#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date = CURDATE(),salary = 6000
WHERE id=4;
 
#题目： 将表中姓名中包含字符a的提薪20%
UPDATE emp1 
SET salary = salary*1.2
WHERE NAME LIKE '%a%';

#修改数据时，是可能存在不成功的情况的。原因：可能是由于约束的影响造成的
UPDATE employees
SET department_id=10000
WHERE employee_id=102

#删除数据 DELETE FROM.....WHERE

DELETE FROM emp1
WHERE id=1;

#在删除数据时也有可能因为约束的影响，导致删除失败
DELETE FROM departments
WHERE department_id=50;

#小结：DML操作默认情况下，执行完以后都会自动提交数据。
#如果希望执行完以后不自动提交数据，则需要使用 SET autocommit = FALSE.


#MYSQL8的新特性：计算列
USE atguigudb;

CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS(a + b) VIRTUAL #字段c即为计算列
);

INSERT INTO test1(a,b)
VALUES(10,20);

SELECT *
FROM test1;

UPDATE test1
SET a=100;

#综合案例
# 1、创建数据库test01_library
CREATE DATABASE IF  NOT EXISTS  test01_library;
SHOW DATABASES;

# 2、创建表 books，表结构如下：
/*字段名 字段说明 数据类型
id 	书编号 	    INT
name 	书名     VARCHAR(50)
authors 作者     VARCHAR(100)
price 	价格       FLOAT
pubdate 出版日期   YEAR
note    说明     VARCHAR(100)
num     库存        INT
*/
USE test01_library;

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）不指定字段名称，插入第一条记录
# 2）指定所有字段名称，插入第二记录
# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books
VALUES (1,'Tal of AAA','Dickes',23,1995,'novel',11);

INSERT INTO books(NAME,id,price,AUTHORS,note,pubdate,num)
VALUES('EmmaT',2,35,'Jane lura','joke',1993,22);

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);

SELECT * FROM books;
# 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 COUNT(*)
FROM books
WHERE NAME LIKE '%a%';

SELECT NAME
FROM books
WHERE NAME LIKE '%a%';

# 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num)
FROM books
WHERE NAME LIKE'%a%';

# 9、找出“novel”类型的书，按照价格降序排列
SELECT NAME
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;

SELECT *
FROM books

# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT *
FROM books
WHERE num>30
GROUP BY note;

# 13、查询所有图书，每页显示5本，显示第二页
SELECT NAME
FROM books
LIMIT 0,5;

# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT *
FROM books
WHERE num >=ALL(

	SELECT num
	FROM books
	GROUP BY note
	HAVING MAX(num)

);

SELECT *
FROM books b1
WHERE num =(
	     SELECT MAX(num)
	     FROM books b2    
);


# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT *
FROM books
WHERE 10<=CHAR_LENGTH(REPLACE(NAME,' ',''));

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 'jock' 	THEN '笑话'
		       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 AS "存货"
FROM books;

# 18、统计每一种note的库存量，并合计总量
SELECT note,SUM(num)
FROM books
GROUP BY note;

# 19、统计每一种note的数量，并合计总量
SELECT COUNT(*)
FROM books
GROUP BY 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;

SELECT *
FROM books
WHERE pubdate=(
		SELECT MIN(pubdate)
		FROM books
		ORDER BY pubdate
		);

# 22、找出novel中价格最高的一本书
SELECT *
FROM books
WHERE note='novel'
ORDER BY price DESC
LIMIT 0,1;


SELECT *
FROM books
WHERE note='novel'
HAVING MAX(price); 

# 23、找出书名中字数最多的一本书，不含空格
SELECT NAME
FROM books
ORDER BY LENGTH(REPLACE(NAME,' ','')) DESC;



#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


#5. 向users表中插入数据
/*1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
*/
DESC users

INSERT INTO users(id,userid,department_id)
VALUES
(1,'Rpate1',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 u,e
FROM users u JOIN my_employees e
ON u.userid=e.userid
WHERE u.userid='Bbiri';

#9. 删除my_employees、users表所有数据
DELETE e,u
FROM my_employees e JOIN users u
ON e.userid=e.userid;

#10. 检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;

#11. 清空表my_employees
TRUNCATE TABLE my_employees;

# 1. 使用现有数据库dbtest11
USE dbtest11;
# 2. 创建表格pet
/*
字段名 字段说明 数据类型
name 	宠物名称 VARCHAR(20)
owner 	宠物主人 VARCHAR(20)
species 种类 	VARCHAR(20)
sex 	性别 	CHAR(1)
birth 出生日期 	YEAR
death 死亡日期 	YEAR
*/
CREATE TABLE IF NOT EXISTS pet(
`name` VARCHAR(20),
`owner` VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth YEAR,
death YEAR
);

DESC pet;
# 3. 添加记录
INSERT INTO pet(`name`,`owner`,species,sex,birth,death)
VALUES
('Fluffy','harold','Cat','f',2003,2010),
('Claws','gwen','Cat','m',2004,NULL),
('Buffy',NULL,'Dog','f',2009,NULL),
('Fang','benny','Dog','m',2000,NULL),
('bowser','diane','Dog','m',2003,2009),
('Chirpy',NULL,'Bird','f',2008,NULL)

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 species = 'Dog' AND death IS NULL; 
# 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 species='Dog'AND death IS NOT NULL;
# 10. 查询所有宠物信息
SELECT *
FROM pet;
# 1. 使用已有的数据库dbtest11
USE dbtest11;
# 2. 创建表employee，并添加记录
CREATE TABLE employee
(
id INT,
`name` VARCHAR(20),
sex VARCHAR(2),
tel VARCHAR(20),
addr VARCHAR(50),
salary DOUBLE	
);
DESC employee;

SELECT *
FROM employee;

INSERT INTO employee
VALUES
('10001','张一一','男','13456789000','山东青岛',1001.58),
('10002','刘小红','女','13454319000','河北保定', 1201.21),
('10003','李四','男','0751-1234567','广东佛山',1004.11),
('10004','刘小强','男','0755-5555555','广东深圳',1501.23),
('10005','王艳','女','020-1232133','广东广州',1405.16)

# 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 '%小%'；