DQL查询语句之单表查询-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_52331401/article/details/146451255

简单查询

查询是SQL语言的核心，用于表达SQL查询的select查询命令是功能最强也是最为复杂的SQL语句，它的作用就是从数据库中检索数据，并将查询结果返回给用户

查一个字段

查询一个字段说的是：一个表有多列，查询其中的一列

语法格式：select 字段名 from 表名；

select和from是关键字，不能随便写
一条SQL语句必须以“；”结尾
对于SQL语句来说，大小写都可以
字段名和表名属于标识符，按照表的实际情况填写，不知道表名的，可以使用desc命令查看表结构

#查询公司中所有员工编号
select empn from emp;
#查询公司中所有员工姓名
select ename from emp;

查询多个字段

查询多个字段时，在字段名和字段名之间添加“,”即可

语法格式：select 字段名1，字段名2，字段名3 from 表名；

字段的前后顺序无所谓，只是显示结果列的时候顺序变了

#查询员工编号以及员工姓名
select empnc, ename from emp;

查询所有字段

查询所有字段的可以将每个字段都列出来查询，也可以采用“*“来代表所有字段

#查询员工的所有信息
select * from emp

查询时字段可参与数学运算

在进行查询操作的时候，字段是可以参与数学运算的，例如加减乘除等。

#查询每个员工的月薪
select ename, sal from emp;
#查询每个员工的年薪（月薪*12）
select ename, sal * 12 from emp;

查询时字段可起别名

#我们借用一下之前的SQL语句
select ename, sal * 12 from emp;
#使用as关键字
select ename, sal * 12 as yearsal from emp;

条件查询

通常在进行查询操作的时候，都是查询符合某些条件的数据，很少将表中所有数据都取出来。所以需要在查询语句中添加条件进行数据的过滤，常见的过滤条件如下：

条件查询语法格式

select
    ...
from
    ...
where
    过滤条件;

过滤条件放在where字句当中，以上语句的执行顺序是：

先执行from；
再通过where条件过滤；
最后执行select，查询并将结果展示到控制台

等于、不等于

等于=

判断等量关系，支持多种数据类型，比如：数字、字符串、日期等。

#eg.1：查询月薪3000的员工编号及姓名
select
    empno, name
from
    emp
where
    sal = 3000;
#eg.2：查询员工ford的岗位及月薪
select
    job, sal
from
    emp
where
    ename = 'ford';
#eg.3：查询岗位是manager的员工编号及姓名
select
    empno, ename
from
    emp
where
    job = 'manager';

不等于<>或!=

判断非等量关系，支持字符串、数字、日期类型等。不等号有两种写法，第一种<>，第二种!=，第二种写法和Java程序中的不等号相同，第一种写法比较诡异，不过也很好理解，比如<>3，表示小于3，大于3，旧就是不等于3.你get到了吗？

#eg.1：查询工资不是3000的员工编号，姓名，薪资
select
    empno, ename, sal
from
    emp
where
    sal <> 3000;
#eg.2：查询工作岗位不是manager的员工姓名和岗位
select
    ename, job
from
    emp
where
    job <> 'manager';

大于、大于等于、小于、小于等于

大于>

#eg.1：找出薪资大雨3000的员工姓名、薪资
select
    ename, sal
from
    emp
where
    sal > 3000;

大于等于>=

#eg.1：
select
    ename, sal
from
    emp
where
    sal >= 3000;

and

and表示并且，还有另一种写法：&&

#eg.1：找出薪资大于等于3000并且小于等于5000的员工姓名、薪资。
select
    ename, sal
from
    emp
where
    sal >= 3000 and sal <=5000;

or

or表示或者，还有另一种写法：||

eg.1：找出工作岗位是manager和salesman的员工姓名，工作岗位
select
    ename, job
from
    emp
where
    job = 'manager' and job ='salesman';

and和or的优先级问题

这两位同时出现时注意通过添加小括号区分优先级

between...and...

        between...and...等同于>=and<=
        做区间判断的，包含左右两个边界值
        它支持数字、日期、字符串等数据类型
        between...and...在使用时一定是左小右大。反之无法查询到数据
        between...and...和>=and<=只是在写法结构上有区别，执行原理和效率方面完全相同

eg.1：找出薪资在1600到3000的员工姓名、薪资
select
    ename, sal
from
    emp
where
    sal between 3000 and 5000;

is null、is not null

        判断某个数据是否为null，不能使用等号，只能使用is null
        判断某个数据是否不为null，不能使用不等号，只能使用is not null
        在数据库中null不是一个值，不能是用等号和不等号衡量，null代表什么也没有，没有数据，没有值

is null

#eg.1：找出津贴为空的员工姓名、薪资、津贴。
select
    ename, sal, comm
from
    emp
where
    comm is null;

安全等于（了解一下）

<=>安全等于，用的很少，因为他的缺点是可读性差

#eg.1：找出薪资3000的员工姓名，岗位
select
    ename, job
from
    emp
where
    sal <=> 3000;

in、not in

in

#eg.1：找出工作岗位是manager和salesman的员工姓名、薪资、工作岗位
#第一种：使用or
select
    ename, sal, job
from
    emp
where
    job = 'manager' or job = 'salesman';
#第二种：使用in
select
    ename, sal, job
from
    emp
where
    job in('manager','salesman');

in、not in与NULL

in（NULL，300）：忽略NULL，输出300
not in（NULL，300）：不忽略空，输出空值

模糊查询like

模糊查询又被称为模糊匹配，在实际开发中使用较多，比如：查询公司中所有姓张的，查询岗位中带有经理两个字的职位等等。语法格式如下：

select .. from .. where 字段 like '通配符表达式';
#在模糊查询中，通配符主要包括两个：一个是%，一个是下划线，其中%代表任意多个字符。下滑想代表任意一个字符。
eg.1：查询员工名字以“S”开始的员工姓名
select ename from emp where ename like 'S%';
eg.2：查询员工名字以“T”结尾的员工姓名
select ename from emp where ename like '%T';
eg.3：查询员工名字中含有“O”的员工姓名
select ename from emp where ename like '%O%';
eg.4：查询员工名字中第二个字母是“A”的员工姓名
select ename from emp where ename like '_A%';

排序操作

排序操作很常用，比如查询学员成绩，按照成绩降序排列。排序的SQL语法：

#单一字段升序
#查询员工编号、姓名、薪资，按照薪资升序排列
select empno,ename,sal from emp order by sal asc;
#单一字段降序
#查询员工编号、姓名、薪资，按照薪资升序排列
select empno,ename,sal from emp order by sal asc;
#多个字段排序
#查询员工的编号、姓名、薪资，按照薪资升序排列，如果薪资相同的，在按照姓名升序排列
select empno,ename,sal from emp order by sal asc,ename asc;
#where和order by的位置
#找出岗位是manager的员工姓名和薪资，按照薪资升序排列。
select ename,sal from emp where job = 'manager' order by sal asc;
#distinct去重
#查询工作岗位
select job from emp;
#可以想到工作岗位中有重复记录，如何在显示的时候去除重复记录呢？在字段前添加distinct关键字。
select distinct job from emp;

数据处理函数

关于select语句，我们之前都是这样写：select 字段名 from 表名;其实，这里的字段名可以看做“变量”，select后面既然可以跟变量，那么可以跟常量吗？
通过测试得知，select后面既可以跟变量，又可以跟常量

字符串相关

转大写upper和ucase

#查询所有员工名字，以大写形式展现
select upper(ename) as ename from emp;

截取字符串substr

#语法：substr(‘被截取的字符串’，起始下标，截取长度)
#有两种写法：
#第一种：substr(‘被截取的字符串’，起始下标，截取长度)
#第二种：substr(‘被截取的字符串’，起始下标)，当第三个参数“截取长度缺失时，截取到字符串末尾”
#注意：起始下标从1开始，不是从0开始。（1表示从左侧开始的第一个位置，-1表示从右侧开始的第一个位置。）
#eg.1：找出员工名字中第二个字母是A 的
select ename from emp where substr(ename, 2, 1) = 'A';
#获取字符串长度length
select length('你好123');
#获取字符的个数char_length
select char_length('你好123');

字符串拼接

#语法：concat('字符串1','字符串2','字符串3');
#拼接的字符串数量没有限制
select concat('zhangsan','lisi','wanwgwu');
#去除字符串前后空白trim
select concat(trim('   abc   '),'def');
#默认是去除前后空白，也可以去除指定的前缀后缀，例如：
#去除前置0：
select trim(leading '0' from '001110000');
#去除后置0：
select trim(trailing '0' from '001110000');

数字相关

rand()和rand(x)

#rand()生成0到1的随机浮点数
select rand();
#rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值
select rand(7);
#round(x)和round(x,y)四舍五入
#round（x）四舍五入，保留整数位，舍去所有小数
select round(9.231);
#round(x,y)四舍五入，保留y位小数
select round(9.3123,1);
#ceil与floor
#ceil函数：向上取整
#floor函数：向下取整
select ceil(5.2);
select floor(5.2);

#空处理
#ifnull(x,y)，空处理函数，当x为NULL时，jiangx当做y处理
#ifnull(comm,0)，表示如果员工的津贴是NULL时当做0处理
#在SQL语句中，凡是有NULL参与的数学运算，最终的计算结果都是NULL
select null + 1;
#看这样一个需求：查询每个员工的年薪。（年薪=（月薪+津贴）*12个月。注意：有的员工津贴comm是NULL）
select ename,(sal + comm) * 12 as yearsal from emp;

日期和时间相关函数

获取当前日期和时间

#now是获取执行select语句的时刻
select now();   
#sysdate是获取执行sysdate()函数的时刻
select sysdate();
select now(), sleep(2), sysdate();
#获取当前日期
select curdate();
select current_date();
select current_date;
#获取当前时间
select curtime();
select current_time();
select current_time;
select year/month/day/hour/minute/second(now());

date_add函数

#作用：给指定的日期添加间隔的时间，从而得到一个新的日期
#语法格式：date_add(日期,interval expr 单位);
#eg.1：
select date_add('2023-01-03',interval 3 day);
#以'2023-01-03'为基准，间隔三天之后的日期：'2023-01-06'
select date_add('2023-01-03 10:10:10',interval '3,2' day_hour);
#复合写法

date_format日期格式化函数

#作用：将日期转换成具有某种格式的日期字符串，通常用在查询操作中（date类型转换为char类型）
#语法格式：date_format(日期,'日期格式');
#该函数有两个参数：
#1.第一个：日期。这个参数就是即将要被格式化的日期。类型是date类型。
#2.第二个：指定要格式化的格式字符串
#a.%Y：四位年份
#b.%y：两位年份
#c.%m：月份（1..12）
#d.%d：日（1..30）
#e.%H：小时（0..23）
#f.%i：分（0..59）
#g.%s：秒（0..59）
eg.1：获取当前系统时间，让其以这个格式展示：200-10-11 20:15:30
select date_format(now(),'%Y-%m-%d %H:%i:%s');

str_to_date函数

#我们要给这个表插入一条数据：姓名zhangbing，生日02年7月25日，执行以下insert语句：
insert into t_student(name,birth) values('zhangbing','07/25/2002');
#会报错，错误原因：日期值不正确。意思是：birth字段需要一个日期，你给的这个字符串'07/25/2002'我识别不了。这种情况下，我们就可以使用str_to_date函数进行类型转换：
insert into t_student(name,birth) values('zhangbing',str_to_date('07/25/2002','%m%d%Y'));

dayofweek、dayofmonth、dayofyear函数

#一周中的第3、几天（周日是第一天）
select dayofweek(now());
#一个月中的第几天（1~31）
select dayofmonth(now()); 
#一年中的第几天（1~366）
select dayofyear(now());

last_day函数

select last_day(now());
#获取给定日期所在月的最后一天的日期

datediff函数

select datediff('1970-02-01 20:10:30','1970-01-01');
#计算两个日期之间所差天数，时分秒不算，只计算日期部分相差的天数

timediff函数

select timediff('1970-02-01 20:10:30','1970-01-01 20:09:30');
#计算两个日期所差时间，例如日期1和日期2所差10:20:30，表示差10小时20分钟30秒

if函数

#如果条件为true则返回“yes”，如果条件为false则返回“no”
select if(500<1000,"yes","no");
#如果名字是smith的，工资上调10%，其他员工工资正常显示
select ename,if(ename='smith',sal*1.1,sal) as sal from emp;
#工作岗位是manager的工资上调10%，是salesman的工资上调20%，其他岗位工资正常
select ename,job,if(job='manager',sal*1.1,if(job='salesman',sal*1.2,sal)) as sal from emp;
上面这个需求也可以使用：case..when..then..when..then..else.end来完成
select ename,job,
case,job
when 'manager' then sal*1.1
when 'salesman' then sal*1.2
else sal
end
as sal
from emp;

cast函数

cast函数用于将值从一种数据类型转换为指定的另一种数据类型
语法：cast(值as数据类型)
例如：cast('2020-10-11' as date),表示将字符串’2020-10-11‘转换成日期date类型。
在使用cast函数时，可用的数据类型包括：
- date：日期类型
- time：时间类型
- datetime：日期时间类型
- signed：有符号的int类型（有符号指的是正数负数）
- char：定长字符串类型
- decimal：浮点型
select cast('2020-10-11 20:15:30' as date);
select cast('2020-10-11 20:15:30' as time);
select cast('1234.567' as decomal(5,1));

加密函数

#md5函数，可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的
select md5('powernode');

分组函数（多行处理函数）

        分组函数（多行处理函数）
        分组函数的执行原则：先分组，然后对每一组数据执行分组函数。如果没有分组语句group by的话，整张表的数据自成一组。
        分组函数包括五个：
                - max：最大值
                - min：最小值
                - avg：平均值
                - sum：求和
                - count：计数

#max：找出员工的最高薪资
select max(sal) from emp;
#min：找出员工的最低薪资
select min(sal) from emp;
#avg：计算员工的平均薪资
select avg(sal) from emp;
#sum：计算员工的工资和
select sum(sal) from emp;
#count：统计员工人数
select count(ename) from emp;
select count(*) from emp;
select count(1) from emp;
#count(*)和count(1)的效果一样，统计该组中总记录行数
#count(ename)统计的是这个ename字段中不为NULL个数总和
#例如：count(comm)结果是4，而不是14
select count(comm) from emp;
统计岗位数量
select count(distinct job) from emp;

分组函数组合使用

select count(*),max(sal),min(sal),avg(sal),sum(sall),from emp;

分组函数注意事项

分组函数自动忽略NULL，不能用在where后面，因为分组函数必须先分组，而分组语句group by是在where语句后执行

分组查询

group by

        按照某个字段分组，或者按照某些字段联合分组。注意：group by的执行是在where之后。
        语法：
                group by 字段
                group by 字段1，字段2，字段3...

#找出每个岗位的平均薪资
select job,avg(sal) from emp group by job;
#找出每个部门的最高工资
select deptno,max(sal) from emp group by deptno;
#找出每个部门不同岗位的平均薪资
select deptno,job avg(sal) from emp group by deptno,job;
#当select语句中有group by的话，select后面只能跟分组函数或参加分组的字段
select ename,deptno,avg(sal) from emp group by deptno; //这个SQL执行后会报错，因为当语句中有group by的时候select后面只能跟参加分组的函数

having

        having写在group by的后面，当你对分组之后的数据不满意，可以继续通过having对分组之后的数据进行过滤。
        where的过滤实在分组前进行过滤。
        使用原则：尽量在where中过滤，实在不行，再使用having。越早过滤效率越高。

#找出除20部分之外，其他部门的平均薪资
select deptno,avg(Sal) from emp where deptno<>20 group by deptno; //建议
select deptno,avg(Sal) from emp group by deptno having deptno<>20; //不建议
#查询每个部门平均薪资，找出平均薪资高于2000的
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

组内排序

#案例：找出每个工作岗位的工资排名在前两名的
#substring_index函数的使用：
select substring_index('http://www.baidu.com','.'1);
#group_concat函数的使用
select group_concat(empno order by sal desc) from emp group by job;

总结单表的DQL语句

        总结单表的DQL语句
                - select...5
                - from...1
                - where...2
                - group by...3
                - having...4
                - order by...6
        重点掌握一个完整的DQL语句执行顺序（1~6）