6.MySQL基本查询

表的增删查改

表的增删查改也可简写为CRUD：Create（创建），Retrieve（读取），Update（更新），Delete（删除）

Insert（插入）

语法：

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

示例：


也可以多列插入

插入替换

如果插入时，已经存在了，希望能更改原来的内容，而不是直接报错拦截。

语法：

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

示例：

插入成功后会返回 2 rows affected，这是什么意思呢？
0 rows affected：表中有冲突数据，但冲突数据的值和update的值相等
1 rows affected：表中没有冲突数据，数据被插入
2 rows affected：表中有冲突数据，并且数据已经被更新

插入替换2

语法：

REPLACE INTO table_name [(column [, column] ...)] VALUES (value_list) [, value_list]...

replace的特点
没有冲突，则直接插入；
如果有冲突，则删除后再插入。注意，这里与替换不同。

示例：
没有冲突，则直接插入

有冲突，则删除后再插入。

可以看到他的id变了，说明不是直接替换，而是删除后再插入。

Retrieve（查找）

语法：

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

先创建一个表

SELECT 列

全列查找

通常情况下不建议使用 * 进行全列查询。因为
1。查询的列越多，意味着需要传输的数据量越大；
2。可能会影响到索引的使用。

示例：

指定列查询

按照你的需求，从表中拿出数据

示例：

查询字段为表达式

select后面不仅能跟列属性，也能跟表达式。

示例：

为查询结果指定别名

有些时候我们查询到结果的时候，想给列名字重命名。

示例：

结果去重

示例：

WHERE 条件

有些时候我们想根据我们的需求对结果进行筛选，比如有需求筛选出成绩及格和成绩不及格的。就需要用where条件了，where可以看做我们学C/C++时的if语句。

比较运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，操作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 返回TRUE(1)
IS NOT NULL	不是NULL 返回TRUE(1)
LIKE	模糊匹配。%表示任意多个(包括0个)任意字符；_表示任意一个字符

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

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

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

使用 BETWEEN … AND …条件

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

使用IN条件

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

孙某同学

5.语文成绩好于英语成绩的同学

where后面：可以跟表达式，一个列可以和另一个列比较，也可以和常数比较，也可以和字符串比较，也可以模糊匹配。

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

之前不是说可以对列进行重命名吗？那来试一下。

为什么报错了？报错显示total未找到，未知列。
这是因为select有执行顺序。select的执行顺序到底是怎么样的。

所以重命名并不能在where中被识别

7.语文成绩 大于 80并且不姓孙的同学

8.孙某同学，否则要求总成绩 大于 200 并且 语文成绩 小于 数学成绩 并且 英语成绩 大于 80

order by子句

语法：

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

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

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

2.查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示
解析：以数学降序来排序，如果数学成绩相等则按照英语升序来排序；如果英语成绩相等则按照语文升序来排序

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

之前讲过where后面不能使用别名，为什么这里又能使用别名了？
所以我们还需要搞清楚语句执行的顺序是怎么样的。
1.from 表名
2.where子句筛选条件
3.select筛选出需要哪些属性
4.order by排序
5.最后显示到屏幕

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

筛选分页结果

语法

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

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死

示例：
按 id 进行分页，每页 3 条记录， 分别显示第 1、2、3 页

第 1 页

第 2 页

第 3 页

我们在平时在网页上，鼠标点击页数，在数据库层面就会被转换为以上操作。

Update（更新）

update的时候，本质是先查询出数据，再修改。也就是先把select替换update操作，再进行修改。言外之意就是执行update的顺序是放在最后的。
语法：

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

示例：
1.将孙悟空同学的数学成绩变更为 80 分
查看原数据

数据更新

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

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


注意：update搭配limit使用时，只能如下操作，因为UPDATE 语句本身不支持使用 ORDER BY 和 LIMIT 来指定更新的行，因为数据库引擎不知道如何基于这些排序和限制条件来正确地应用更新。

4.将所有同学的语文成绩更新为原来的 2 倍
注意：如果没有where子句的筛选一定要慎用update

delete（删除）

语法：

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

删除数据

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

2.删除整张表的数据
注意：该操作要谨慎操作，一定是确定了这些数据不要了才能删除。

可以看到删除了表的内容，但表的结构还是，auto_increment没有变，仍然是4。

删除表中的数据还有一种做法：

截断表

语法：

TRUNCATE [TABLE] table_name

示例：

发现truncate使auto_increment重置了。

truncate的特点与delete的区别
1.只能对整表操作，不能像DELETE一样正对部分数据操作。
2.比DELETE执行执行速度更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚。
3.会重置AUTO_INCREMENT项。

插入查询结果

语法：

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

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

创建原数据表


这不就去重了吗？这只是查找时的去重，并没有影响原始表。

那要如何做呢？
创建一个新表，将查询到去重的结果再插入到新表中；将要去重的表备份（即重命名），再将新表重命名为要去重表的名字。
1.创建和原表结构一样的表

2.把select的去重数据筛出来，插入到新表

3.备份+重命名

我们在上传10G文件到A目录的时候，有两个方案：
1。直接将10G文件传到A目录，并且要保证原子性，要让整个上传文件的过程保证他是原子态的传输状态，这是非常慢的。
2。将10G文件预处理传输到临时目录下，最后一步直接mv到A目录。只需保证最后一步mv是原子态即可。
第二种方案的操作是更推荐的，因为第二种的方法更比第一种更轻量。
这也就是为什么我们数据去重的时候，使用rename的方式，而不是直接对原表进行操作，因为采用原子性的时候，rename的方式能使效率更高。

聚合函数

为什么叫聚合函数？因为每一行函数执行出来的值相同，所以能被聚合压缩。
比如：

如果把 1 + 1 看做一个函数，每一行执行出来的结果都为2，因为很多行的结果都是一样的，所以能被聚合压缩成一行。这就是聚合压缩。

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

示例：

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

使用 * 做统计

使用 表达式 做统计

2.统计数学成绩的个数

执行的顺序是先统计，再去重
这样才能先去重，再统计

3.统计数学成绩的总分

4.统计数学成绩平均分

5.统计数学平均分

我也想显示每个人的名字和数学成绩，和平均数学成绩对比。

为什么报错了？
对于每个人来说平均成绩是一样的，所以平均成绩可以聚合。但是name对于每一个来说不是一样的，所以name的记录不能够被压成一个记录，所以没办法做聚合。那具体要如何做呢？后面会讲。

分组聚合

分组是将某个属性记录相同的分为同一组。聚合函数针对的是组，不是表，组是表的划分。
分组的目的是为了进行分组之后，方便进行聚合统计

group by子句的使用（包含oracle 9i的经典测试表）

语法：

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

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）

• EMP员工表
• DEPT部门表
• SALGRADE工资和最高工资

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;USE `scott`;DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (`grade` int(11) DEFAULT NULL COMMENT '等级',`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);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');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);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.显示每个部门的平均工资和最高工资

group by deptno：将相同的deptno分为了一组，组内的一定deptno一定是相同的，所以可以被聚合压缩

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

group by deptno, job：首先根据group by分为几个组，再根据job将这几个组分为更多的组。

显示平均工资低于2000的部门和它的平均工资

having是对聚合后的统计数据，再条件筛选。

having 和 where 的区别如何理解？只是执行的顺序不同。
示例：显示平均工资低于2000的部门和它的平均工资，但SMITH员工不参与统计

执行顺序：
1.from子句
2.where子句
3.group by子句
4.执行聚合函数，重命名
5.having子句
6.select子句，选择显示指定列

所以having和where的区别就是执行的顺序不同

总结：
不要单纯的认为，只有存入磁盘的表才叫表，中间筛选出来的，中途显示出来的表，也叫表，这些全部都是逻辑上的表。所以也可以总结出一句话“MySQL一切皆表”。