数据库MySQL学习笔记（三）

这篇博客主要讲一下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*/