SQL-DML操作

#插入数据：
#1.添加数据
#0.储备工资
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
VALUES(1,'Tom','2000-12-21',3400);
#错误的：
INSERT INTO emp1
VALUES(2,34000,'Tom','2001-1-1');
#2 指明要添加的字段的顺序（推荐的方法）;
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'2001-1-1',50000,'Jerry');
#3
INSERT INTO emp1(id,salary,hire_date)
VALUES(3,10000,'2003-1-1');
SELECT * FROM emp1; #没有标明的字段会默认值null
#4
INSERT INTO emp1(id,NAME,salary)
VALUES(4,'Jim',50000),
(4,'张俊杰',50000);
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) ;

# ait +shift + i 快捷键
INSERT INTO `atguigudb`.`emp1` (`id`, `name`, `hire_date`, `salary`)
VALUES
('id', 'name', 'hire_date', 'salary') ;
#更新数据：修改
# update ---- set ---- where
UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 4;
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 employees
WHERE department_id = 50;
#综合案例：
#1
CREATE DATABASE IF NOT EXISTS test01_library;
USE test01_library;
#2
CREATE TABLE books(
id INT,
NAME VARCHAR(50),
AUTHORS VARCHAR(100),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT
);
#3
INSERT INTO books (
id,
NAME,
AUTHORS,
price,
pubdate,
note,
num
)
VALUES
(
1,
'Tal of AAA',
'Dickes',
23,
1995,
'novel',
11
),
(
2,
'EmmaT',
'Jane lura',
35,
1993,
'joke',
22
),
(
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
UPDATE books
SET price = price +5
WHERE note = 'novel';
#5
UPDATE books
SET price = 40,note = 'drama'
WHERE NAME = 'EmmaT';
#6
DELETE FROM books
WHERE num = 0;
#7
SELECT COUNT(*)
FROM books
WHERE NAME LIKE '%a%';
#8
SELECT COUNT(*),num
FROM books
WHERE NAME LIKE '%a%';
#9
SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC;
#10
SELECT *
FROM books
ORDER BY num DESC,note ASC;
#11
SELECT note,COUNT(*)
FROM books
GROUP BY note;
#12
SELECT note,COUNT(*)
FROM books
GROUP BY note
HAVING SUM(num) > 30;
#13
SELECT *
FROM books
LIMIT 5;
#14
SELECT note,COUNT(*)
FROM books
GROUP BY note
ORDER BY SUM(num) DESC
LIMIT 1;
#15
SELECT *
FROM books
WHERE CHAR_LENGTH(REPLACE(NAME,'','')) >= 10;
#16
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
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
IFNULL(note, '合计总库存量') AS note,
SUM(num)
FROM
books
GROUP BY note WITH ROLLUP ;