MySQL数据处理之增删改_insert overwrite table是否支持直插入几个字段,不插入全部

一、插入数据

1、方式一：VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。
（1）为表的所有字段按默认顺序插入数据，语法格式：

INSERT INTO 表名 VALUES (value1,value2,....);
1

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

举例：

# 建表语句
USE atguigudb;

CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);
1
2
3
4
5
6
7
8
9

INSERT INTO emp1
VALUES (1,'Tom','2000-05-28',5000); #一定要按照声明的字段的先后顺序添加
1
2

（2）为表的指定字段插入数据，语法格式：

INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);
1

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,…valuen需要与column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

举例：

INSERT INTO emp1(id,hire_date,salary,`name`) # 可以不按建表顺序，只要对应即可
VALUES(2,'1996-12-05',6600,'Mercy');

INSERT INTO emp1(id,salary,`name`)# 没有进行赋值的hire_date 的值为 null
VALUES (3,4400,'Jerry');
1
2
3
4
5

（3）同时插入多条数据，语法格式：

INSERT INTO table_name VALUES (
value1 [,value2, …, valuen]), 
(value1 [,value2, …, valuen]), 
……
(value1 [,value2, …, valuen]);
1
2
3
4
5

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开。

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中 效率更高 。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

举例：

INSERT INTO emp1(id,`name`,salary)
VALUES
(4,'Godv',9900),
(5,'Crazy',5000),
(6,'Forever',8800);
1
2
3
4
5

注意：

1、VALUES 也可以写成 VALUE ，但是VALUES是标准写法。
2、字符和日期型数据应包含在单引号中。

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

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

基本语法格式：

INSERT INTO 目标表名 
(tar_column1 [, tar_column2, …, tar_columnn]) 
SELECT (src_column1 [, src_column2, …, src_columnn]) FROM 源表名 
[WHERE condition]
1
2
3
4

注意：

1、 在 INSERT 语句中加入子查询。
2、不必书写 VALUES 子句。
3、子查询中的值列表应与 INSERT 子句中的列名对应。

举例：

INSERT INTO emp1(id,`name`,salary,hire_date)
# 查询语句
SELECT employee_id,last_name,salary,hire_date # 查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN (60,70);


# 注意
# emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
# 如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险。
1
2
3
4
5
6
7
8
9
10

二、更新数据

使用 UPDATE 语句更新数据。可以一次更新多条数据。如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE。同时可以使用 WHERE 子句指定需要更新的数据。如果省略 WHERE 子句，则表中的所有数据都将被更新。

基本语法如下：

UPDATE table_name 
SET column1=value1, column2=value2, … , column=valuen [WHERE condition]
1
2

举例：

# UPDATE...SET...WHERE...

# 将emp1表中id=5的员工的hire_date改为当前日期
UPDATE emp1
SET hire_date=CURDATE()
WHERE id=5;

# 同时修改一条数据的多个字段
# 将emp1表中id=4的员工的hire_date改为当前日期，salary改为7000
UPDATE emp1
SET hire_date=CURDATE(),salary=7000,
WHERE id=4;

# 将emp1表中姓名含有字符a的员工提薪20%
UPDATE emp1
SET salary=salary*1.2
WHERE `name` LIKE '%a%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

（可自行与上图对比）

三、删除数据

使用 DELETE 语句从表中删除数据。基本语法格式：

DELETE FROM table_name [WHERE <condition>];
1

table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

举例：

# DELETE FROM ... WHERE...

DELETE FROM emp1
WHERE id=1;

# 在删除数据时，有可能因为约束的影响，导致删除失败
# 报错：Cannot delete or update a parent row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))
DELETE FROM departments
WHERE department_id = 50;
1
2
3
4
5
6
7
8
9

（可自行与上图对比）

四、MySQL8的新特性：计算列

计算列是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。

举例：

USE atguigudb;

CREATE TABLE IF NOT EXISTS test1(
a INT,
b INT,
c INT generated always AS(a+b) virtual);# c即为计算列


INSERT INTO test1(a,b)
VALUES(10,10);
1
2
3
4
5
6
7
8
9
10

五、综合案例

# 1、创建数据库test01_library 
CREATE DATABASE IF NOT EXISTS test01_library  CHARACTER SET 'utf8';

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

# 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 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 COUNT(*),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升序排列 # 11、按照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 `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 '类型'
FROM books;


# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货 
SELECT `name` AS "书名",num AS "库存",CASE WHEN num>30 THEN '滞销'
                                           WHEN num>0  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,'合计总量') 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150

books表：

插入记录：

所用数据库请看文章末尾：SQL语言和基本的select语句