这篇博客主要讲一下DQL(数据查询语言)
查询用到的就是select关键字,是数据库一个非常重要的部分。
一、查询的基本格式
1、查询的过程:先是定位哪张表,再按照我们的要求寻找,最后使用选择出来(即是先走from判断哪张表,再走where条件,最后用select选择出来)。
2、查询内容:
(1)当查询的内容和表无关时,select 查询内容 即可。
例如 select 1+2 可以输出3
(2)当查询的内容和表有关时,select 查询内容 from 表名
查询内容:查询所有字段时用 *
查询指定的字段 select 字段名,字段名 from 表名
(3)给查询的列起别名: select 字段名 [as] 别名,字段名 from 表名
注意:别名实际可以不加双引号或单引号,但是如果别名中有空格,则必须加上二者之一。
二、约束条件之where子句
1、作用:起到条件限制约束等操作。有过滤的作用。
2、位置:放在 from子句后。
3、格式:select colName from tableName where 条件
4、关系运算符:>,>=,<,<=,=,!=,<>(<>和!=是相同的意思)
5、具体使用格式:
(1)null在条件中,不能使用=或!=来判断,应该使用is 或者is not
(2)范围查询:针对某个字段来说:
1⃣️ 使用>,>=,<,<=来限制。
2⃣️ 使用between and 来限制(此处val1和val2为闭区间的)。
格式:字段名 [not] between val1 and val2
(3)集合作为条件写法:同一个字段有多个值的情况下使用。
1⃣️ 使用:in|not in
2⃣️ 格式:字段名 [not] in (元素1,元素2....);
in 相当于 or ,not in 相当于 and
3⃣️ 集合的其他用法:
all和any格式: >all(元素1,元素2,元素3......) //大于集合中最大的元素。
<all(元素1,元素2,元素3......) //小于集合中最小的元素。
>any(元素1,元素2,元素3......) //大于集合中最小的元素。
<any(元素1,元素2,元素3......) //小于集合中最大的元素。
注意:在mysql中,集合函数(>all,>any,<all,<any)不适用于直接加数字。
>any(10,20,30) -- 不允许但是我们可以将(10,20,30)转换为子查询即可。
(4)模糊查询:like关键字
格式:字段名 like value
通常与通配符%连用: %:匹配0或0个以上的字符 _:匹配一个字符
三、约束条件之排序
1、当在查询表中数据时,记录比较多,有可能需要进行排序,此时我们可以使用order by子句。
2、位置语法:select 字段 from 表名 where 条件 order by 字段 排序规则
3、排序规则有两个:升序:asc(默认) 降序:desc
注意: 当order by后面是一个字段时,就按照该字段的排序规则进行排序。
如果order by后面是多个排序规则,那么会先按照第一个排序规则进行排序,在第一个排序结果相同时,会按照第二个排序规则进行排序。
可以发现desc字段有两个用处:(1)查询表结构:desc 表名 (2)排序的降序规则:order by 字段 desc
如果设计表时,字段名叫desc,会怎么样?在使用该字段时,会出现错误,解决办法,将该字段进行转义`字段名`。
四、约束条件之去重
1、去重:有的时候我们可以需要对重复的记录进行去重操作。比如,查询表中有哪些职位,此时,一种职位只需要显示一条记录就够。
2、关键字:distinct
3、位置: 必须写在select关键字后。
注:distinct可以对一个字段进行去重,也可以对多个字段进行去重(联合去重,就是多个字段组合起来唯一即可)。
五、约束条件之分组
1、group by 子句:有的时候,我们可能需要查询表中的记录总数,或者查询表中每个部门的总工资,平均工资,总人数。这种情况需要对表中的数据进行分组统计。
2、位置:select..from tName [where 条件] [group by子句] [order by子句]
3、用法: group by Field1[,Field2]。
4、注意:在分组查询时,select子句中的值如果含有组标志或组函数,就尽量不要含有非组标志或组函数,如果在select后边既有组函数,又有非组函数(普通字段),那么在oracle数据库中,该语句出现错误,而在mysql数据库中,该语句没有错误,但是查询结果不正确,所以我们要避免在select后面既有组函数,又有普通字段(通过起别名的方式避免前述操作)。
5、一些聚合函数:
(1)count(Filed):统计指定字段的记录数。(filed为*时:统计指定表中有多少条记录,为字段名时:统计指定表中该字段不为空的个数。
(2)sum(Filed):统计指定字段的和。
(3)avg(Filed):统计指定字段的平均值。
(4)max(Filed):返回指定字段中的最大值。min(Filed):返回指定字段中的最小值。
注:聚合函数会忽略null值。因此有时候需要使用函数:ifnull(),函数ifnull(field,value)逻辑:如果field字段对应的值不是null,就使用field的值,如果是null,就使用value。
6、多字段分组时:
field1 field2
10 500
20 600
30 700
注意:多字段分组时,最多分组的数目为Filed1的记录条数*Field2的[*Filed3的....]
六、几道利用上述知识的题:
/*1.查询emp表中工资大于1000的员工姓名以及工资
select ename,sal from emp where sal > 1000*/
/*2.查询emp表中职位为'MANAGER'的员工姓名和工资以及职位
select ename,sal,job from emp where job='MANAGER'*/
/*3.查询emp表中名字长度为5的员工姓名和工资以及职位
select ename '姓 名',sal "工 资",job "职 位"
from emp where length(ename)=5*/
/*4.查询emp表中没有奖金的员工信息
SELECT * FROM emp WHERE comm IS NULL */
/*5.查询emp表中有奖金并且工资大于1000的员工姓名以及工资和奖金
select ename,sal,comm from emp where comm is not null and sal > 1000*/
/*6.查询工资在1000到2000之间的员工信息
select * from emp where sal > 1000 and sal < 2000
select * from emp where sal BETWEEN 1000 AND 2000*/
/*7.查询10部门和20部门的员工信息
select * from emp where deptno = 10 or deptno = 20
select * from emp where deptno in (10,20)*/
/*8.查询emp表中职位为'CLERK','MANAGER','ANALYST'的员工信息
select * from emp where job='CLERK' or job='MANAGER'
or job='ANALYST'
select * from emp where job in ('CLERK','MANAGER','ANALYST')*/
/*9.查询emp表中既不是10部门,又不是20部门的员工
select * from emp where deptno != 10 and deptno != 20
select * from emp where deptno not in (10,20)*/
/*10.查询emp表中职位不为'CLERK','MANAGER','ANALYST'的员工信息
select * from emp where job not in ('CLERK','MANAGER','ANALYST')*/
/*11.查询emp表中工资大于smith,allen,ward中最大工资的员工
select * from emp where sal >all( select sal from emp where ename in ('SMITH','ALLEN','WARD'))*/
mysql数据库不支持括号里直接为数字
/*12.查询emp表中名字带有'S'的
select * from emp where ename like '%S%'*/
/*13.查询名字中第五个字符为'S'的员工
select * from emp where ename like '____S%'*/
/*14.查询emp表中职位的第二个字符为'a'的
select * from emp where LOWER(job) like '_a%'*/
/*15.查询emp表中部门号为20号部门的工资,工资降序排列,工资相同时,按照姓名升序排列
select sal,ename from emp where deptno = 20 order by sal desc,ename asc*/
/*16.查询emp表中所有数据,要求先按照部门降序排,同部门的按照工资升序排
select * from emp order by deptno desc,sal*/
/*17.查询product表中所有的字段
select pid,pname,`desc` from product*/
/*18.查询emp表中有哪些职位
select DISTINCT job from emp*/
/*19.查询emp表中的员工都来自于哪个部门
select DISTINCT deptno from emp*/
/*20.查询每个部门中都有哪些职位
select DISTINCT deptno,job from emp*/
/*21.统计emp表中有多少条记录
select count(*) from emp*/
/*22.最大值,最小值,平均值
select max(sal),min(sal),avg(sal) from emp*/
/*23.查询20部门中的最高工资,最低工资,平均工资
select max(sal),min(sal),avg(sal) from emp where deptno = 20*/
/*24.查询每个部门中的最高工资,最低工资,平均工资
select deptno,max(sal),min(sal),avg(sal) from emp group by deptno*/
/*25.查询emp表中的总工资和总奖金 (ifnull函数表示如果该字段为null则用0代替)
select SUM(sal),SUM(IFNULL(comm,0)) from emp*/
/*26.查询emp表中每个人的工资年薪,奖金年薪
select sal*12,IFNULL(comm,100)*12 from emp*/
/*27.查询不同部门不同职位的最高工资,最低工资,平均工资
select deptno,job,max(sal),min(sal),avg(sal) from emp group by deptno,job*/