3. MySQL DQL 数据库查询语句

我们先准备一张基础表来验证查询语句的相关操作

show databases;
use mydata;
show tables;
CREATE TABLE `dept`(
    `deptno` INT(2) NOT NULL,
    `dname` VARCHAR(14),
    `loc` VARCHAR(13),
    CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE `emp` (
    `empno` int(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),  
    `job` VARCHAR(9),  
    `mgr` int(4),  
    `hiredate` DATE,  
    `sal` float(7,2),  
    `comm` float(7,2),  
    `deptno` int(2),
    CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE TABLE `bonus`(
    `ename` VARCHAR(10),
    `job` VARCHAR(9),
    `sal` INT,
    `comm` INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `salgrade` (  
    `grade` int,
    `losal` int,  
    `hisal` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

表结构说明：
emp表员工信息表

empno:员工编号
ename:员工姓名
job:员工职位
mgr:其上司的员工编号
hiredate:入职日期
sal:工资
comm:佣金
deptno:所在部门的部门编号

dept表部门信息表

deptno:部门编号
dname:部门名称
loc:工作地点

salgrade表工资登记表

grade:工资等级
losal:等级最低工资
hisal:等级最高工资

DQL基本格式

查询不会对数据库中的数据进行修改，只是一种显示数据的方式。

查询语句基本格式：

SELECT 列名 FROM 表名 [WHERE 条件表达式]

注意：
1、SELECT 命令可以读取一行或者多行记录。
2、你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
3、你可以使用 WHERE 语句来包含任何条件。
4、表的列也可以叫做：属性,字段,列。
5、表的也可以叫做：记录,元组,行。
6、关键字可以不区分大小写
7、表名,列名也不区分大小写

一、简单查询

查询表所有行和列的数据

1、使用*表示所有列

格式：

select * from 表名;

查询所有的员工

select * from emp;
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

image.png

注意：使用第2种方式,比第1种效率略高。

2、查询指定列

查询指定列的数据,多个列之间以逗号分隔
格式：

select 列名1,列名2,...列名n from 表明；

查询员工编号,员工姓名,部门编号

select empno, ename, deptno from emp;

image.png

注意：select关键字必须与from同时使用,两者缺一不可。

3、指定列的别名进行查询

使用别名的好处：显示的时候使用新的名字，并不修改表的结构，同时防止语句书写可能出现的错误。
1）使用空格（不推荐使用）

SELECT 列名1   别名, 列名2  别名... FROM 表名;

2）使用as关键字（推荐使用）

SELECT 列名1 AS 别名, 列名2 AS 别名... FROM 表名;

注意：
1、推荐使用as声明别名,容易区分表达式与别名的界限
2、as和空格也可以为表设置别名
如：

select * from emp as e；
select * from emp e;

表使用别名的原因：用于多表查询操作

4、清除重复值（distinct）

查询指定列并且结果不出现重复数据
格式：

SELECT DISTINCT 列名1，列名2...列名n  FROM 表名;

查询所有的部门编号,去掉重复的数据

select distinct deptno from emp;

image.png

注意：distinct只能放在select 与列名之间的位置

练习:查询所有的职位信息,去掉重复的数据

select distinct job from emp;

image.png

5、查询结果参与运算

1）某列数据和固定值运算
格式

SELECT 列名1 + 固定值 FROM 表名;

2）某列数据和其他列数据参与运算

SELECT 列名1 + 列名2 FROM 表名;

注意: 参与运算的必须是数值类型

练习：
查询员工姓名和员工的年薪

select ename, sal*12 as 年薪 from emp;

image.png

练习:查询员工姓名，月薪， “员工的奖金是XXX” 别名是奖金，员工的年收入别名是年收入 (月薪+奖金)*12

select ename, sal, concat('员工的奖金是',comm) as 奖金,
(sal + comm) * 12 as 年收入
from emp;

image.png

这里我们可以看到两个问题：
(1)null值参与的是数值运算,结果一定是null
(2)null参与字符串拼接,没有任何效果

修改上面的语句为：

select ename, sal, concat('员工的奖金是',ifnull(comm,0)) as 奖金,
(sal + ifnull(comm,0)) * 12 as 年收入
from emp;

image.png

二、条件查询

1）为什么要条件查询
如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对记录进行过滤。
2）条件查询的语法
格式：

SELECT 字段名 FROM 表名 WHERE 条件;

流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回
语句执行流程

select...from...where...

1、基本比较运算符

image.png

查询在10号部门工作的员工

select * from emp where deptno = 10;

image.png

查询月薪不高于1600的员工信息

select * from emp where sal <= 1600;

image.png

查询不在20号部门工作的员工信息

select * from emp where deptno != 20;
select * from emp where deptno <> 20;

image.png

2、查询条件中与关系（and）或关系（or）

image.png

1）AND
查询在10号部门工作,并且月薪高于2000，并且职位是MANAGER的员工

select * from emp
where deptno = 10 and sal > 2000 and job = 'MANAGER';

image.png

2）OR
查询在10号部门工作或者工资高于2000的员工

select * from emp
where deptno = 10 or sal > 2000

image.png

3）AND与OR一起使用
AND的优先级比OR要高如果想改变优先级,加括号

查询在10号部门工作或工资高于2500并且职位是MANAGER的员工

select * from emp
where (deptno = 10 or sal > 2500) and job = 'MANAGER';

image.png

三、特殊的比较运算符

image.png

1、between…and...

格式:

列 between 下限 and 上限

包含两个端点的值

查询emp表中,20号部门月薪在1000-2000之间的员工信息
方法1：

select *from emp
where (sal >= 1000 and sal <= 2000) and deptno = 20;

方法2：

select * from emp
where (sal between 1000 and 2000) and deptno = 20;

image.png

数据库中常见的数据类型
(1)文字
(2)数值
(3)日期/时间
注意：between...and...通常用于数值或日期的比较

查询emp表中,在1982年入职的员工信息

select * from emp
where hiredate between '1982-1-1' and '1982-12-31';

image.png

练习:查询emp表中,在1981年入职,月薪在1200-1800之间的员工

select * from emp
where hiredate between '1981-1-1' and '1981-12-31'
and sal between 1200 and 1800;

image.png

between…and…也可以比较字符串
按照英文字母顺序进行比较

select * from emp
where ename between 'A' and 'C';

image.png

上下限反着写
语句不报错,但是逻辑上没有结果

select * from emp
where sal between 2000 and 1000;

相当于

select * from emp
where sal >= 2000 and sal <= 1000;

2、IN

格式：

列 in (值1,值2..值n)

列的取值是值1或值2…或值n

查询所有30号部门,其上司的员工编号是7902,7698,7788的员工信息

select * from emp
where (mgr = 7902 or mgr = 7698 or mgr = 7788)
and deptno = 30;

select * from emp
where mgr in(7902,7698,7788) and deptno = 30;

image.png

练习:用IN来查询emp表中职位是’SALESMAN’,’MANAGER’的员工

select * from emp
where job in('SALESMAN','MANAGER');

image.png

3、like… 像…[重要]

通配字符

image.png

查询所有名字首字母是B的员工信息

select * from emp where ename like 'S%';

image.png

练习:查询倒数第二个字符是T的员工

select * from emp where ename like '%T_';

image.png

关键字定义转义符
在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。例如:
查询名字中开头字母是MAN_的员工名字

select ename from emp where ename like 'MAN/_%' escape '/';

实际应用
模糊查询，例如：输入一个”张”,将所有的姓张的人都显示出来

注意like:只能用于文字类型的模糊匹配

4、is null

用于筛选空值
因为=null的逻辑关系是null,不能筛选出空值
所以利用is null将空值筛选出来

查询没有奖金的人

select * from emp
where comm = 0 or comm is null;

image.png

练习:查询出公司中boss是谁,用is null来实现

select * from emp where mgr is null;

image.png

5、NOT

逻辑非关系
与上述特殊比较运算符联合使用，表示取反

查询月薪不在1000-2000之间的员工信息
not between…and…

select * from emp
where sal not between 1000 and 2000;

image.png

查询其上司的员工编号不是7902,7698,7788的员工信息
not in(…)

select * from emp where mgr not in(7902,7698,7788);

image.png

查询所有不姓张的员工
not like …

select * from emp where ename not like '张%';

查询有奖金的人(设无奖金的人comm值是null)
is not null

select * from emp where comm is not null;

image.png

四、排序

1、单列排序

order by…
升序由小到大
数值:数值由小到大
日期/时间:由早到晚
文字:英文字母顺序，根据每个位上文字进行比较–如果文字相同,比较下一位文字

查询所有10号部门的员工信息,按照月薪升序进行排序
(1)默认

select * from emp where deptno = 10 order by sal;

image.png

(2)ASC关键字

select * from emp where deptno =10 order by sal asc;

image.png

降序由大到小
查询所有10号部门的员工信息,按照月薪降序进行排序
(1)DESC关键字

select * from emp where deptno =10 order by sal desc;

image.png

查询员工姓名,年收入,按照年收入升序排序（可以利用别名进行排序）

select ename, (sal + ifnull(comm, 0))*12 as 年收入
from emp
order by 年收入 asc

image.png

查询年收入高于15000的所有员工信息

select ename, (sal + ifnull(comm, 0))*12 as 年收入
from emp
where 年收入 > 15000;

注意：错误写法，where中不能存在别名

2、多列排序

查询员工信息,按照部门编号升序，月薪降序进行排序

select * from emp
order by deptno asc,sal desc;

image.png

第2个排序在第1个排序的基础上,继续进行

部门升序排序,在每个部门中,月薪降序排序

select * from emp
order by deptno asc,sal desc, hiredate asc;

image.png

五、SQL语句的执行顺序

书写顺序

select...from...where...order by...

执行顺序

from...where...select...order by...

注意：
1、因为执行顺序导致where中不能使用列别名，原因:select语句尚未执行,还没有创建出别名
2、order中可以使用列别名，原因:order by在select之后执行,列别名已经被创建

六、限制记录的行数

select 字段列表 from 数据源 limit [start,]length;

1、limit接受一个或两个整数参数。start表示从第几行记录开始输出，length表示输出的记录行数。
2、表中第一行记录的start值为0(不是 1)。

初始记录行的偏移量是 0(而不是 1)：

SELECT * FROM emp LIMIT 5,10; # 检索记录行6-15

image.png

如果只给定一个参数，它表示返回最大的记录行数目。换句话说，LIMIT n 等价于 LIMIT 0,n：

SELECT * FROM emp LIMIT 5;    # 检索前 5 个记录行

image.png

最后编辑于：2019.08.16 10:00:21

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 219,753评论 6赞 508
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 93,668评论 3赞 396
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 166,090评论 0赞 356
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 59,010评论 1赞 295
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 68,054评论 6赞 395
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,806评论 1赞 308
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,484评论 3赞 420
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,380评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,873评论 1赞 319
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 38,021评论 3赞 338
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 40,158评论 1赞 352
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,838评论 5赞 346
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,499评论 3赞 331
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 32,044评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 33,159评论 1赞 272
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,449评论 3赞 374
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 45,136评论 2赞 356

3. MySQL DQL 数据库查询语句

DQL基本格式

一、简单查询

1、使用*表示所有列

2、查询指定列

3、指定列的别名进行查询

4、清除重复值（distinct）

5、查询结果参与运算

二、条件查询

1、基本比较运算符

2、查询条件中 与关系（and） 或关系（or）

三、特殊的比较运算符

1、between…and...

2、IN

3、like… 像…[重要]

4、is null

5、NOT

四、排序

1、单列排序

2、多列排序

五、SQL语句的执行顺序

六、限制记录的行数

推荐阅读更多精彩内容

2、查询条件中与关系（and）或关系（or）