您现在的位置是:首页 >技术杂谈 >MySQL基本查询网站首页技术杂谈
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 号部门和它的平均工资。
各子句的执行顺序如下图: