1. 插入数据

1) VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。

情况1：为表的所有字段按默认顺序插入数据

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

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

举例：

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

情况2: 指定字段名插入数据

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

举例：

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

情况3：同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或者

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

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

• Records：表明插入的记录条数。
• Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
• Warnings：表明有问题的数据值，例如发生数据类型转换。

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

2) 将查询结果插入到表中

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

INSET INTO 目标表名
(tar_column1 [, tar_column2, ..., tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

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

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

2. 更新数据

• 使用 UPDATE 语句更新数据。语法如下：

UPDATE table_name
SET column1=value1, column2=value2, ..., column=valuen
[WHERE condition]

• 可以一次更新多条数据。

• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

• 使用 WHERE 子句指定需要更新的数据。

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

• 如果省略 WHERE 子句，则表中的所有数据都将被更新。

3. 删除数据

DELETE FROM table_name [WHERE <condition>];

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

4. 查询数据

1) SELECT ... FROM

• 语法

SELECT 标识选择哪些列
FROM 标识从哪个表中选择

• 选择全部列

SELECT *
FROM departments;

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。

在生产环境下，不推荐你直接使用SELECT *进行查询。

• 选择特定的列：

SELECT department_id, location_id
FROM departments;

MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，读者也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护。

2) 列的别名

• 重命名一个列
• 便于计算
• 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
• AS 可以省略
• 建议别名简短，见名知意
• 举例：

 SELECT last_name AS name, commission_pct comm
 FROM   employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM   employees;

3) 去除重复行

• 默认情况下，查询会返回全部行，包括重复行。

  SELECT department_id
  FROM  employees;
  

  

  

  在SELECT语句中使用关键字DISTINCT去除重复行

  SELECT DISTINCT department_id
  FROM  employees;
  

  

  

  针对于：

SELECT DISTINCT department_id,salary FROM employees;

这里有两点需要注意：

1. DISTINCT 需要放到所有列名的前面，如果写成SELECT salary, DISTINCT department_id FROM employees会报错。

2. DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需要写DISTINCT department_id即可，后面不需要再加其他的列名了。

4) 空值参与运算

空值：null ( 不等同于0, ' ', 'null')

实际问题的解决方案：引入IFNULL

​```sql SELECT employee_id, salary "月工资", salary * (1 + IFNULL(commission_pct, 0)) * 12 "年工资" FROM employees;

这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

### 5) 着重号 ``

必须保证你的字段没有和保留字、数据库系统或常见方法冲突。

```sql
mysql> SELECT * FROM ORDER; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1

如果坚持使用，在SQL语句中使用``引起来。

SELECT * FROM `order`;

6) 查询常数

SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。

你可能会问为什么我们还要对常数进行查询呢？

SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个字段固定值为“尚硅谷”，可以这样写：

SELECT '尚硅谷' as corporation, last_name FROM employees;

7) 过滤数据

- 语法：

 SELECT 字段1,字段2
 FROM 表名
 WHERE 过滤条件

• 使用WHERE 子句，将不满足条件的行过滤掉

• WHERE子句紧随 FROM子句

• 举例

SELECT employee_id, last_name, job_id, department_id
FROM  employees
WHERE  department_id = 90 ;

5. 排序规则

• 使用 ORDER BY 子句排序

  • ASC（ascend）: 升序
  • DESC（descend）:降序

• ORDER BY 子句在SELECT语句的结尾。

1) 单列排序

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date;

2) 多列排序

• 可以使用不在SELECT列表中的列排序。
• 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

6. 分页

• 格式：

LIMIT [位置偏移量,] 行数

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。

• 举例：

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。

• 分页显式公式：（当前页数-1）* 每页条数，每页条数

SELECT * FROM table
LIMIT(PageNo - 1) * PageSize, PageSize;

• 注意：LIMIT 子句必须放在整个SELECT语句的最后！

• 使用LIMIT的好处

约束返回结果的数量可以减少数据表的网络传输量 ，也可以提升查询效率 。如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

7. MySQL8新特性：计算列

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

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的 值。 首先创建测试表tb1，语句如下：

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);