13-基础SQL-DQL(数据查询语言)-执行顺序（编写顺序和执行顺序）

DQL-介绍（常用）

DQL英文全称是Data Query Language（数据查询语言），数据查询语言用来查询数据库中表的记录

查询关键字：SELECT

DQL-语法

DQL-语法（编写顺序和执行顺序）

案例：创建一个 emp 员工表，添加一些员工数据

CREATE TABLE emp(
    id int comment "编号",
    workno varchar(10) comment "工号",
    name varchar(10) comment "姓名",
    gender char(1) comment "性别",
    age tinyint unsigned comment "年龄",
    idcard char(18) comment "身份证号",
    workaddress varchar(50) comment "工作地址",
    entrydate date comment "入职时间"
) comment "员工表";

INSERT INTO emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
VALUES    
(1,"1","员工1","男","23","123456789000000000","江西","2023-8-31"),
(2,"2","员工2","男","26","123456789000000001","河北","2023-12-31"),
(3,"3","员工3","女","23","123456789000000002","河北","2023-5-31"),
(4,"4","员工4","女","24","123456789000000003","山西","2023-3-31"),
(5,"5","员工5","男","23","123456789000000000","江西","2023-8-31"),
(6,"6","员工6","男","26","123456789000000001","河北","2023-12-31"),
(7,"7","员工7","女","23","123456789000000002","河北","2023-5-31"),
(8,"8","员工8","女","24","123456789000000003","山西","2023-3-31"),
(9,"9","员工9","男","23","123456789000000000","江西","2023-8-31"),
(10,"10","员工10","男","26","123456789000000001","河北","2023-12-31"),
(11,"11","员工11","女","23","123456789000000002","河北","2023-5-31"),
(12,"12","员工12","女","24","123456789000000003","山西","2023-3-31")

思考问题：

以下两个SQL语句，哪个能执行成功

# 第一个

SELECT age,count(*) from emp where age < 25  GROUP BY age ORDER BY age DESC;

# 第二个

SELECT age,count(*) from emp where age < 25  GROUP BY age ORDER BY id DESC;

第一个执行成功，第二个报错，这是因为 SELECT age,count(*) from emp where age < 25  GROUP BY age 执行得到下面的内容，第二个无法匹配到id字段。

重点：那再分析下面两个SQL语句，哪个能执行成功

# 第一个

SELECT age,name from emp where age < 25 ORDER BY age DESC;

# 第二个

SELECT id,name from emp where age < 25 ORDER BY age DESC;

两个都能执行成功，为什么第二个也能执行成功？

按理来说，SELECT id,name from emp where age < 25 执行得到下面的内容，应该无法匹配到age字段。

这是因为emp表内置的字段中存在age字段，才可以对age字段进行排序。我们换一个写法，就执行不成功了

SELECT * FROM
(SELECT id,name from emp where age < 25) c 
ORDER BY c.age DESC;