目录

1.insert 增添数据

1.1单行数据 + 全列插入

1.2多行数据 + 指定列插入

1.3插入否则更新

1.4.插入否则替换

2.select查找

2.1 全列查找

2.2指定列查找

2.3查询字段为表达式

2.4为查询结果指定别名

2.5 结果去重

2.6 where条件查询

2.7结果排序

2.8.筛选分页结果

 3.update修改数据

 4.delete删除数据

4.1delete

4.2截断表

 5.插入查询结果

6.聚合函数

 7.group by子句的使用

准备工作

使用案例

 where和having在group by中的使用

---

1.insert 增添数据

语法：

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

使用案例：

        创建一个学生表，以下是字段详细信息。id为自增主键，学号为唯一键

---

1.1单行数据 + 全列插入

        value_list 数量必须和定义表的列的数量及顺序一致。
        当然你也可以一次插入多组的全列数据，如下所示：

---

1.2多行数据 + 指定列插入

        value_list 数量必须和指定列数量及顺序一致。在进行指定列插入时，自增长和空字段可以不进行指定插入

---

1.3插入否则更新

        由于 主键 或者 唯一键 对应的值已经存在而导致插入失败：

语法：

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...

        如果主键发生了冲突，就更新update后指定的字段。如果你不指定更新的字段，那么就是用之前的值，在这里并没有指定sn，那么sn就使用原来的值

判断数据的插入情况：

-- 0 row affected: -- 1 row affected: -- 2 row affected:

表中有冲突数据，但冲突数据的值和 update 的值相等 表中没有冲突数据，数据被插入 表中有冲突数据，并且数据已经被更新

---

1.4.插入否则替换

        主键 或者 唯一键 没有冲突，则直接插入；主键 或者 唯一键 如果冲突，则删除后再插入

语法：

REPLACE INTO 表名 VALUES ...

没有冲突，直接插入：      

主键和唯一键任何一个有冲突那就替换：

主键

唯一键

2.select查找

语法：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

使用案例：

        创建一个学生成绩表，并插入数据，方便演示查找操作

        插入数据

---

2.1 全列查找

        将表中所有数据全部读出来，但在数据庞大的场景下不会这么使用，如公司的数据库中

SELECT * FROM table_name;

---

2.2指定列查找

        查询指定列的信息

SELECT column1, column2 FROM table_name;

---

2.3查询字段为表达式

        你可以对表中的数值字段进行加、减、乘、除等算术运算。

求总分

---

2.4为查询结果指定别名

        查询时可对每一列重命名，下面是使用样例:

SELECT column [AS] alias_name [...] FROM table_name;

           不加as也是可以的

---

2.5 结果去重

        查询结果有重复则去重。

SELECT DISTINCT 字段列表 from 表名;

---

2.6 where条件查询

        在数据库查询中，WHERE 子句用于指定筛选条件，以从表中获取满足特定条件的记录。
比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, ...)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

使用案例：

       1. 英语不及格的同学及英语成绩 ( < 60 )

       2. 语文成绩在 [80, 90] 分的同学及语文成绩。使用 AND 进行条件连接

       3. 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩。使用 OR 进行条件连接

        或者用 '||'运算符

        也可以使用 in ，表示从四个值中去选一个

        4.姓孙的同学 及 孙某同学。

        % 匹配任意多个（包括 0 个）任意字符

        _ 匹配严格的一个任意字符

        5.语文成绩好于英语成绩的同学
        -- WHERE 条件中比较运算符两侧都是字段

        6.总分在 200 分以下的同学

        -- WHERE 条件中使用表达式
        -- 别名不能用在 WHERE 条件中

        7.孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
        要么姓孙，要么总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80，满足其一即可

        8.NULL 的查询
        这里使用学生表

        指定qq不为空

        qq为空

---

2.7结果排序

语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
案例：

        1.同学及数学成绩，按数学成绩升序显示

        2.查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
        -- 多字段排序，排序优先级随书写顺序

        3.查询同学及总分，由高到低

        4.查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

---

2.8.筛选分页结果

语法：

-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

        建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死
        按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页

第一页：从第0行开始向后筛选3行数据

第二页：重第三行开始向后筛三行

第三页：从第六行开始向后筛3行，不足三个，不会有影响

 3.update修改数据

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

对查询到的结果进行列值更新

案例：
       1.将孙悟空同学的数学成绩变更为 80 分

        2.将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

        3.将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

        4.将所有同学的语文成绩更新为原来的 2 倍

 4.delete删除数据

4.1delete

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

        1.删除孙悟空同学的考试成绩

        2.删除整张表数据

准备一个测试表

现在删除这张表

再插入一条数据，自增 id 在原值上增长

---

4.2截断表

语法：

TRUNCATE [TABLE] table_name

与delete的区别：

• （条件删除）因为delete是可以带WHERE的，所以支持条件删除;而truncate只能删除整个表。
• （事务回滚）由于delete是数据操作语言，操作时原数据会被放到 rollback segment中，可以被回滚;而truncate是数据定义语言，操作时不会进行存储，不能进行回滚。
• （清理速度）truncate由于是直接删除整个表的数据并重新创建表，因此在处理大型数据表（如千万级的数据记录）时，其执行效率通常比delete更高。delete：每次删除一行，并在事务日志中为所删除的每行记录一项，因此在处理大型数据表时可能会比较慢。
• （高水位重置）TRUNCATE：会重置表的高水位线，使得之后的DML操作速度有所提升。DELETE：虽然删除了表中的数据，但并不会降低表的高水位线，因此查询速度可能不会得到提升。

测试案例：

        准备表并插入数据

        截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作

        查看删除结果

        truncate会重置自增长，id重新从 1开始自增长

 5.插入查询结果

        该语句允许从一个或多个表中选择数据，并将这些数据插入到另一个表中。这种语句非常有用，特别是当你需要将一个表中的数据复制到另一个表中，或者需要将多个表的数据合并到一个表中时。

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

测试案例：删除表中的的重复复记录，重复的数据只能有一份

        创建表，如下：

        创建一张空表 no_duplicate_table，结构和 duplicate_table 一样

        将 duplicate_table 的去重数据插入到 no_duplicate_table

        分别对两张表进行重命名操作，实现原子的去重操作。当然，如果你觉duplicate_table

原数据不需要了，可以直接删除。

6.聚合函数

MySQL中的聚合函数（Aggregate Functions）用于对一组值执行计算，并返回单个值。

常见形式：

select 函数名(参数) from 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...];

以下是常见的聚合函数：

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

使用案例：

        1.统计班级共有多少同学

        将*作为参数传给count，统计表中行数

        使用表达式做统计,临时增加列，用于统计

        2.统计班级收集的 qq 号有多少

        count自动忽略空值

        3.统计本次考试的数学成绩分数个数
        count会做去重

        4.统计数学成绩总分

        不及格 < 60 的总分，没有结果，返回 NULL

        5.统计平均总分

        6.返回 > 70 分以上的数学最低分

 7.group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询

select column1, column2, .. from table group by column;

准备工作

        创建一个雇员信息表

• EMP员工表

• DEPT部门表

• SALGRADE工资等级表

---

        向表中插入数据

1.向员工表中插入数据

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

2.向DEPT部门表插入数据

insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON');

3.向SALGRADE工资等级表插入数据

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

---

使用案例

        1.如何显示每个部门的平均工资和最高工资

        按组来显示，分别是10，20，30组，他们各组的平均工资和最高工资情况。

        像这样，不进行分组，查的就是全公司的情况了

        2.显示每个部门的每种岗位的平均工资和最低工资

        从EMP表中选择每个部门（deptno）内每种职位（job）的平均工资（avg(sal)）、最低工资（min(sal)）、职位名称（job）以及部门编号（deptno），并按照部门编号和职位进行分组。

---

 where和having在group by中的使用

        3.显示平均工资低于2000的部门和它的平均工资
        统计各个部门的平均工资

        having和group by配合使用，对group by结果进行过滤

        4.显示除10组外每个部门的每种岗位的平均工资和最低工资

   HAVING子句是用于对GROUP BY子句生成的分组结果进行过滤的。与WHERE子句不同，WHERE子句是在分组之前对记录进行过滤，而HAVING子句是在分组之后对分组结果进行过滤。