03 DQL(数据库查询语言)

03 DQL(数据库查询语言)

---

一、简单查询

1. 语法格式：select [字段1],[字段2],[字段3],··· from [表名] ;
2. 查询字段可以参与速学运算：select name,num*10 from emp;
3. 查询时还可以给字段别名：select name,num*10 as n from emp;as可以省略。
4. 查询所有字段：select * from emp;

二、条件查询

1. 语法格式：select [字段1],[字段2],[字段3],··· from [表名] where [条件];

2. 按条件表达式筛选
   简单条件运算符：> < = != <> >= <=

3. 按逻辑表达式筛选
   逻辑运算符：
   作用：用于连接条件表达式
   && || !
   and or not

   &&和and：两个条件都为true，结果为true，反之为false
   ||或or： 只要有一个条件为true，结果为true，反之为false
   !或not： 如果连接的条件本身为false，结果为true，反之为false

4. 模糊查询

   • like （必须掌握两个特殊字符，一个是%，一个是_。%代表任意多个字符，_代表任意1个字符）

   /*示例1：找出名字中有'O'的员工*/
   select ename from emp where ename like '%O%';
   
   /*找出名字中第二个字母是‘A’的员工*/
    select ename from emp where ename like '_A%';
   
   /*找出名字中有下划线的员工*/
   select ename from emp where ename like '%\_%';
   +-------+
   | ename |
   +-------+
   | JOH_N |
   +-------+
   
   /*找出名字中最后一个字母是‘T’的员工*/
   select ename from emp where ename like '%T';
   +-------+
   | ename |
   +-------+
   | SCOTT |
   +-------+
   

   • between and
   • in （in运算和 or 类似）

   /*如找出工作岗位是'MANAGER'和'SALESMAN'的员工*/
   or： select ename,job from emp where job='MANAGER' or job='SALESMAN';
   
   in：  select ename,job from emp where job in('MANAGER','SALESMAN');
   
   结果：
   +--------+----------+
   | ename  | job      |
   +--------+----------+
   | ALLEN  | SALESMAN |
   | WARD   | SALESMAN |
   | JONES  | MANAGER  |
   | MARTIN | SALESMAN |
   | BLAKE  | MANAGER  |
   | CLARK  | MANAGER  |
   | TURNER | SALESMAN |
   +--------+----------+
   

   • is null

三、排序

注意：asc升序排，desc降序排，不写默认升序。

/*按照工资升序排*/
select ename,sal from emp order by sal;

/*按照工资降序排*/
 select ename,sal from emp order by sal desc;

/*按照工资的降序排列，工资一样的按照名字的排列*/
select ename,sal from emp order by sal desc,ename asc;
+----------+---------+
| ename    | sal     |
+----------+---------+
| ADAMS    |   11000 |
| JAMES    |    9500 |
| KING     |    5000 |
| FORD     |    3000 |
| SCOTT    |    3000 |
| JONES    |    2975 |
| BLAKE    |    2850 |
+----------+---------+

/*找出工作岗位是‘SALESMAN’的员工，并按照工资降序排列*/
select ename,job,sal from emp where job='salesman' order by sal desc;
+--------+----------+------+
| ename  | job      | sal  |
+--------+----------+------+
| ALLEN  | SALESMAN | 1600 |
| TURNER | SALESMAN | 1500 |
| WARD   | SALESMAN | 1250 |
| MARTIN | SALESMAN | 1250 |
+--------+----------+------+

四、分组函数

1. count 计数

2. sum 求和

3. avg 平均值

4. max 最大值

5. min 最小值

6. 分组函数是对某一组数据进行操作

7. 示例

   /*找出最高工资*/
   select max(sal) from emp;
   
   /*找出最低工资*/
    select min(sal) from emp;
   
   /*找出工资总和*/
   select sum(sal) from emp;
   
   /*找出平均工资*/
   select avg(sal) from emp;
   
   /*找出总人数*/
    select count(ename) from emp;
   

8. 分组函数一共5个，分组函数又叫多行处理函数，其特点是输入多行输出一行，且忽略NULL。

9. SQL 语句有一条规则，分组函数不能直接使用在where子句当中。
   如:

/*找出工资高于平均工资的员工*/
select ename,sal from emp where sal>avg(sal); #这是一条错误语句
ERROR 1111 (HY000): Invalid use of group function

正确写法：
select ename,sal from emp where sal>(select avg(sal) from emp);

五、group by 和 having

1. group by :按照某个字段或者某些字段进行分组。

   1. 示例

      /*找出每个工作岗位的最高薪资*/
      select job,max(sal) from emp group by job;
      

   2. 注意：分组函数一般联合group by 使用，且在group by执行结束之后分组函数才会执行。

   3. 多字段联合分组

      /*找出每个部门不同岗位的最高薪资*/
      select max(sal),job,deptno from emp group by deptno,job;
      

2. having：对分组之后的数据再次进行过滤。
   1. 示例

      /*找出每个部门的最高薪资，要求显示薪资大于3000的部门*/
      #方法一
      select max(sal),deptno from emp group by deptno having max(sal)>3000;
      
      #方法二
      select max(sal),deptno from emp where sal>3000 group by deptno;
      
      

六、查询结果去重（distinct）

distinct：清除查询结果集的重复记录
使用命令：select distinct [字段名] from [表名] ；
示例：select distinct sal from emp;

七、表连接查询

1. 什么是连接查询
   在实际开发中，大部分情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终结果。在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，至少两种表组成。
2. 内连接和外连接有什么区别
   • 内连接：
     假设A表和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。
   • 外连接：
     假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副标，当副标中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
3. 内连查询
   1. 等值连接
      1. 特点：条件是等量关系
      2. 案例

      /*查询每个员工的部门名称，要求显示员工名和部门名*/
      select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
      
      

   2. 非等值连接
      1. 案例

      /*找出每个员工的工资等级，要求显示员工名、工资、工资等级*/
      select e.ename,e.sal,s.grade
      from emp e
      join salgrade s
      on e.sal between s.losal and s.hisal;
      

   3. 自连接
      1. 特点：自己连接自己，把自己看作2张表
      2. 案例

      /*找出每个员工的上级领导，要求显示员工名和对应的领导名*/
      select e.ename,m.ename mgr
      from emp e
      inner join emp m
      on e.mgr=m.empno;
      

4. 外联查询
   1. 左外连接（左连接）：表示左边的这张表是主表

   /*找出每个员工的上级领导，要求显示员工名和对应的领导名*/
   select a.ename '员工',b.mgr '领导' from emp a left outer join emp b on a.mgr=b.empno;
   

   2. 右外连接（右连接）：表示右边的这张表是主表
5. 全连接（不常使用）
6. 三张表怎么连接查询

   /*案例：找出 每个员工 的 部门名称 以及 工资等级 */
   select e.ename,d.dname,s.grade from emp e left join dept d on e.deptno=d.deptno left join salgrade s on e.sal between s.losal and s.hisal;
   

六、union 的使用

1. union可以将查询结果集相加
2. 案例

/*找出工作岗位是SALESMAN和MANAGER的员工*/
select e.ename,e.job from emp e where e.job='SSALESMANE'
union 
select e.ename,e.job from emp e where e.job='MANAGER';

七、limit查询（重点）

1. 应用：常用于分页查询
2. limit是mysql特有的，其他数据库没有，不通用。（Oracle中有一种相同的机制，叫做rownum）
3. limit取结果集中的部分数据，这是它的作用
4. 语法机制

   limit startIndex,length
   startIndex:表示起始位置
   length：表示取出几个
   
   /*案例：取出工资前五的员工信息*/
   select * from emp order by sal desc limit 0,5;
   

5. 分页技巧
   limit (pageNo-1)*pageSize,pageSize
   pageNo:表示页码数
   pageSize:表示每页显示多少条数据

八、完整DQL语句

5. select
···
1.from  （后面可以加 join on）
···
2. where
···
3. group by
···
4. having
···
6. order by
···
7. limit
···