您现在的位置是：首页 >技术杂谈 >MySQL基本查询网站首页技术杂谈

MySQL基本查询

Hoshino373 2023-05-23 08:00:02
简介MySQL基本查询

文章目录

对表中数据的增删查改：
CRUD：Create（创建）、Retrieve（查找）、Update（更新）、Delete（删除）。

一、Create

insert [into] table_name
[(column [, column] ...)] 
VALUES 
(value_list) [, (value_list)] ...;

注：value_list: value, [, value] ...

示例：

创建一张学生表。在这里插入图片描述

1.单行数据 + 全列插入

插入记录时，可以不指定 column 列表（按表中默认的列顺序），value_list 中的列值必须跟 column 列表中的字段按顺序一一对应。在这里插入图片描述注：只有全列插入才可以省略 column 列表。

2.单行数据 + 指定列插入

指定要插入的列，且 value_list 中的列值必须跟 column 列表中的字段按顺序一一对应。在这里插入图片描述

3.多行数据 + 指定列插入

多个 value_list 之间用 ‘,’ 进行分隔。在这里插入图片描述

4.插入否则更新

insert ... on duplicate key UPDATE column=value [, column=value] ...;

注：on duplicate key 指的是，当出现重复 Key 的时候。

  • 若主键或者唯一键没有冲突，则直接插入数据。
  • 若主键或者唯一键冲突，则根据 update 后的列值进行数据更新。

执行该条 SQL 语句后，可以通过受影响的数据行数来判断本次数据的插入情况：
 ① 0 rows affected：表中有冲突数据，但冲突数据的值和 update 的值相等。
 ② 1 row affected：表中没有冲突数据，数据直接被插入。
 ③ 2 rows affected：表中有冲突数据，并且数据已经被更新。

可以选择性地进行同步更新操作。
在这里插入图片描述

5.替换

将 insert 语句中的 insert 换成 replace
  • 若主键或者唯一键没有冲突，则直接插入。
  • 若主键或者唯一键冲突，则删除后再插入。

执行该条 SQL 语句后，可以通过受影响的数据行数来判断本次数据的插入情况：
 ① 1 row affected：表中没有冲突数据，数据直接被插入。
 ② 2 rows affected：表中有冲突数据，删除后再插入。

在这里插入图片描述
在这里插入图片描述

二、Retrieve

select
[distinct] {* | {column [, column] ...}}
FROM table_name
[where ...]
[order by column [ASC | DESC], ...]
[limit ...];

注：{ } 中的 | 表示或，即可以选择左侧的语句或右侧的语句。

各子句的执行顺序为：where、select、order by、limit 。

示例：

在这里插入图片描述

1. SELECT 列

全列查询：

通常情况下不建议使用 * 进行全列查询，理由如下：
 ① 查询的列越多，意味着需要传输的数据量越大。
 ② 可能会影响到索引的使用。

在这里插入图片描述

指定列查询：
指定列的顺序不需要按定义表的顺序来。

在这里插入图片描述

查询字段为表达式：

在这里插入图片描述
在这里插入图片描述在这里插入图片描述

为查询结果指定别名：
as 可以省略。

在这里插入图片描述

结果去重：
在对应列前面加上 distinct 可以对结果进行去重，前提条件是指定列的数据都一样。

在这里插入图片描述

2. 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)，否则为 FALSE(0)
OR只要有任意一个条件为 TRUE(1)，结果就为 TRUE(1)，否则为 FALSE(0)
NOT若条件为 TRUE(1)，则结果为 FALSE(0)；若条件为 FALSE(0)，则结果为 TRUE(1)

示例：

数学成绩不低于 80 分的同学及其数学成绩。
在这里插入图片描述
数学成绩等于 98 分的同学及其数学成绩。
在这里插入图片描述
在这里插入图片描述

语文成绩在 [70, 87] 分的同学及其语文成绩。
在这里插入图片描述在这里插入图片描述

语文成绩是 70 或 87 分的同学及其语文成绩。在这里插入图片描述

数学成绩是 65 或者 84 或者 98 或者 99 分的同学及其数学成绩。在这里插入图片描述

姓孙的同学 及 孙某同学。

%：匹配任意多个（包括 0 个）任意字符。
_：匹配严格的一个任意字符。在这里插入图片描述

语文成绩好于英语成绩的同学及其语文成绩和英语成绩。在这里插入图片描述

在 where 子句中无法使用 select 子句中指定的别名：

  • 按照逻辑顺序去理解：先根据 where 子句筛选出符合条件的记录，再对这些记录执行 select 子句。
  • 换言之，where 子句的执行是先于 select 子句的，所以在执行 where 子句时并不认识 select 中指定的别名。

在 order by 子句中可以使用 select 子句中指定的别名：

  • 按照逻辑顺序去理解：前面的子句全部执行完了，order by 子句才能对 select 子句的执行结果进行排序。
  • 换言之，order by 子句的执行是晚于 select 子句的，所以在执行 order by 子句时认识 select 中指定的别名。

总分在 200 分以下的同学及其总分。在这里插入图片描述
查询同学及其总分，按总分升序显示。在这里插入图片描述

语文成绩高于 80 分并且不姓孙的同学。在这里插入图片描述

查询孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80 的同学。在这里插入图片描述

NULL 代表这个字段的数据没有填，不参与任何运算，不能用 = 去比较。
在这里插入图片描述

判断是否为 NULL 可用 <=> 。在这里插入图片描述


=：等于（NULL 不安全）。
在这里插入图片描述

<=>：等于（NULL 安全）。
在这里插入图片描述
推荐使用 IS NULL 和 IS NOT NULL 来判断 NULL 。在这里插入图片描述
在这里插入图片描述

3.结果排序

排序指的是对整条记录进行排序。

order by 子句：
asc：升序（从小到大）
desc：降序（从大到小）

默认为 asc，不过建议最好还是指明。

注意：没有 order by 子句的查询结果，返回的顺序是未定义的，永远不要依赖这个顺序。

同学及其数学成绩，按数学成绩升序显示。在这里插入图片描述

同学及其 qq 号，按 qq 号排序显示。
NULL 视为比任何值都小，升序出现在最上面，降序出现在最下面。在这里插入图片描述

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

在此示例的意思是：
按数学成绩降序显示；若这两条记录的数学成绩相同，则按英语成绩升序显示；若这两条记录的英语成绩也相同，则按语文成绩进行升序显示。在这里插入图片描述

查询同学及其总分，按总分降序显示。在这里插入图片描述

查询总分大于 200 分的同学及其总分，按总分降序显示。在这里插入图片描述

查询姓孙的同学或者姓曹的同学及其数学成绩，按数学成绩由高到低显示。在这里插入图片描述

4.筛选分页结果

limit 子句：
/* 起始下标从 0 开始 */
limit n：         从 0 开始，筛选 n 条结果
limit s,n：       从 s 开始，筛选 n 条结果
limit n offset s：从 s 开始，筛选 n 条结果

注：如果筛选出来的结果不足 n 条，则全部显示。

建议：对未知表进行查询时，最好加上 limit 子句，避免因为表中数据过大，导致查询全表数据时数据库卡死。

在这里插入图片描述

查询总分前三名的同学及其总分。在这里插入图片描述

按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页。
在这里插入图片描述

三、Update

update table_name 
set column1=expr1 [, column2=expr2 ...]
[where ...] [order by ...] [limit ...];

先执行 update 子句后的所有子句，再执行 update 子句。

where、order by 和 limit 子句是用来从表中筛选出需要 update 的记录的。

将孙悟空同学的数学成绩变更为 80 分。
在这里插入图片描述

将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分。
在这里插入图片描述

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分。
注：MySQL 不支持 math += 30 这种语法。在这里插入图片描述

将所有同学的语文成绩更新为原来的 2 倍。
注：若没有 where 子句，则更新全表。
建议：即使是更新全表，也最好把 where 子句带上。在这里插入图片描述

四、Delete

1.删除数据

delete from table_name 
[where ...] [order by ...] [limit ...];

先执行 delete 子句后的所有子句，再执行 delete 子句。

where、order by 和 limit 子句是用来从表中筛选出需要 delete 的记录的。

删除孙悟空同学的考试成绩。
在这里插入图片描述

删除整张表数据。
注意：该操作需慎用！
说明：执行 delete 操作之后，auto_increment 并不会被重置，而是保持自增。
在这里插入图片描述

2.截断表

truncate [table] table_name;

注意：该操作需慎用！

truncate 子句和 delete 子句的区别：

  • truncate 只能对整表操作，不能像 delete 一样针对部分数据操作。
  • truncate 实际上不对数据操作，所以比 delete 更快。但是 truncate 在删除数据时并不经过真正的事务，所以无法回滚。
  • truncate 会重置 auto_increment 项。

在这里插入图片描述

五、插入查询结果

insert into table_name [(column [, column ...])] select ...;

① 全列插入：

比如，删除表中的的重复记录，重复的数据只能有一份。
在这里插入图片描述在这里插入图片描述

② 指定列插入：

在这里插入图片描述在这里插入图片描述

六、聚合函数

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

聚合函数用于多条记录的纵向统计。

聚合函数在最后执行。

统计班级共有多少名同学。
注：不受 NULL 的影响。在这里插入图片描述

统计班级收集的 qq 号有多少个。
注：NULL 不会计入结果。
在这里插入图片描述

统计本次考试的数学成绩分数个数（全部个数 和 去重后的个数）。
在这里插入图片描述

统计数学成绩总分（全部总分 和 小于 60 分的总分）。
注：若没有结果，则返回 NULL 。在这里插入图片描述

统计数学平均分和平均总分。
在这里插入图片描述在这里插入图片描述

返回英语最高分。在这里插入图片描述

返回 > 70 分的数学最低分。
在这里插入图片描述

七、分组查询

select ... group by columnA [, columnB, ...] [having ...] [order by ...] [limit ...];

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

having 子句和 group by 子句配合使用，作用是对分组进行筛选。

各子句的执行顺序为：where、group by、select、having、order by、limit 。

示例：有一个雇员信息表，共包含三张表：emp（员工表）、dept（部门表）、salgrade（工资等级表）。

执行下面文件的所有 SQL 语句，会创建一个名为 scott 的数据库，并在该数据库中创建上述三张表，并分别向这三张表插入数据。

scott_data.sql 文件：

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

在 MySQL 中使用source scott_data.sql所在路径命令，就会依次执行文件中的 SQL 语句。在这里插入图片描述

三张表的结构：在这里插入图片描述

三张表的内容：在这里插入图片描述

显示每个部门的平均工资和最高工资。
在这里插入图片描述

显示每个部门的每种岗位的平均工资和最低工资。
在这里插入图片描述

显示平均工资低于 2000 的部门和它的平均工资。
在这里插入图片描述

where VS having：

  • where 是过滤表中数据的，放在表名后面。
  • having 是过滤分组数据的，放在 group by 后面。

两者的执行顺序是不同的，它们两个不会冲突。

显示平均工资低于 2000 的非 10 号部门和它的平均工资。

各子句的执行顺序如下图：
在这里插入图片描述

风语者！平时喜欢研究各种技术，目前在从事后端开发工作，热爱生活、热爱工作。

站长推荐