Mysql操作DQL

阳光穿透心脏的1/2处 2023-10-14 01:26 31阅读 0赞

DQL是数据查询语言（Data Query Language）的缩写，是一种用于从数据库中检索数据的编程语言。DQL是SQL（结构化查询语言）的子集，用于查询关系型数据库，例如MySQL、Oracle和SQL Server等。

DQL提供了多种查询操作，如SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY等。使用这些操作，可以根据特定的条件检索所需的数据，并按照特定的顺序进行排序和分组。

DQL还支持多表查询和子查询，可以从多个表中联合检索数据，并在子查询中使用嵌套查询语句进行检索。

### 基础的查询语法 ###

select ... from ...
    
    select [distinct] ... from ... [where ...] [group by ...] [having ...] [order by ...] [limit ...]

### 查询语句的执行顺序 ###

1.  先执行from子句: 基于表进行查询操作
2.  再执行where子句: 进行条件筛选或者条件过滤
3.  再执行group by子句: 对剩下的数据进行分组查询。
4.  再执行having子句: 分组后，再次条件筛选或过滤
5.  然后执行select子句: 目的是选择业务需求的字段进行显示
6.  再执行order by子句: 对选择后的字段进行排序
7.  最后执行limit子句: 进行分页查询，或者是查询前n条记录

#### 准备数据 ####

在学习接下来的查询的语法之前，我们提前准备几张表，并向这张表中插入一些数据，方便我们之后的查询操作。

##### student表 #####

<table> 
 <thead> 
  <tr> 
   <th><strong>字段名称</strong></th> 
   <th><strong>字段类型</strong></th> 
   <th><strong>说明</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>sid</strong></td> 
   <td>char(6)</td> 
   <td>学生学号</td> 
  </tr> 
  <tr> 
   <td><strong>sname</strong></td> 
   <td>varchar(50)</td> 
   <td>学生姓名</td> 
  </tr> 
  <tr> 
   <td><strong>age</strong></td> 
   <td>int</td> 
   <td>学生年龄</td> 
  </tr> 
  <tr> 
   <td><strong>gender</strong></td> 
   <td>varchar(50)</td> 
   <td>学生性别</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE stu (
        sid	CHAR(6),
        sname		VARCHAR(50),
        age		INT,
        gender	VARCHAR(50)
    );
    INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
    INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
    INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
    INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
    INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
    INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
    INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
    INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
    INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
    INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
    INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

##### emp表 #####

<table> 
 <thead> 
  <tr> 
   <th><strong>字段名称</strong></th> 
   <th><strong>字段类型</strong></th> 
   <th><strong>说明</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>empno</strong></td> 
   <td>int</td> 
   <td>员工编号</td> 
  </tr> 
  <tr> 
   <td><strong>ename</strong></td> 
   <td>varchar(50)</td> 
   <td>员工姓名</td> 
  </tr> 
  <tr> 
   <td><strong>job</strong></td> 
   <td>varchar(50)</td> 
   <td>员工工作</td> 
  </tr> 
  <tr> 
   <td><strong>mgr</strong></td> 
   <td>int</td> 
   <td>领导编号</td> 
  </tr> 
  <tr> 
   <td><strong>hiredate</strong></td> 
   <td>date</td> 
   <td>入职日期</td> 
  </tr> 
  <tr> 
   <td><strong>sal</strong></td> 
   <td>decimal(7,2)</td> 
   <td>月薪</td> 
  </tr> 
  <tr> 
   <td><strong>comm</strong></td> 
   <td>decimal(7,2)</td> 
   <td>奖金</td> 
  </tr> 
  <tr> 
   <td><strong>deptno</strong></td> 
   <td>int</td> 
   <td>部分编号</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE emp(
        empno	INT,
        ename	VARCHAR(50),
        job		VARCHAR(50),
        mgr		INT,
        hiredate	DATE,
        sal		DECIMAL(7,2),
        comm	decimal(7,2),
        deptno	INT
    );
    INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
    INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
    INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
    INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
    INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
    INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
    INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
    INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
    INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
    INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
    INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

##### dept表 #####

<table> 
 <thead> 
  <tr> 
   <th><strong>字段名称</strong></th> 
   <th><strong>字段类型</strong></th> 
   <th><strong>说明</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>deptno</strong></td> 
   <td>int</td> 
   <td>部分编码</td> 
  </tr> 
  <tr> 
   <td><strong>dname</strong></td> 
   <td>varchar(50)</td> 
   <td>部分名称</td> 
  </tr> 
  <tr> 
   <td><strong>loc</strong></td> 
   <td>varchar(50)</td> 
   <td>部分所在地点</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE dept(
        deptno		INT,
        dname		varchar(14),
        loc			varchar(13)
    );
    INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
    INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
    INSERT INTO dept values(30, 'SALES', 'CHICAGO');
    INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

#### 基础查询 ####

1.  查询所有列
    
        SELECT * FROM stu;
2.  查询指定列
    
        SELECT sid, sname, age FROM stu;

#### 条件查询 ####

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

=、!=、<>、<、<=、>、>=、BETWEEN…AND、IN(set)、IS NULL、AND、OR、NOT、XOR (逻辑异或)

1.  查询性别为女，并且年龄小于50的记录
    
        SELECT * FROM stu
        WHERE gender='female' AND  age<50;
2.  查询学号为S\_1001，或者姓名为liSi的记录
    
        SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';
3.  查询学号为S\_1001，S\_1002，S\_1003的记录
    
        SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');

#### 模糊查询 ####

按照模糊的条件进行查询，可以使用LIKE条件，或者REGEXP。

##### like #####

like用于where子句之后，表示部分的匹配。在like后，通常会有两种通配符:

\_ => 表示匹配任意的一位字符。

% => 表示匹配任意位的字符。

# 查询所有的姓名以s开头的学生
    select * from student where sname like 's%'
    # 查询所有的姓名以s开头的，且长度为5的学生
    select * from student where sname like 's____'

##### regexp #####

使用正则表达式进行字符串的匹配。

<table> 
 <thead> 
  <tr> 
   <th>模式</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>^</td> 
   <td>匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。</td> 
  </tr> 
  <tr> 
   <td>$</td> 
   <td>匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。</td> 
  </tr> 
  <tr> 
   <td>.</td> 
   <td>匹配除 "\n" 之外的任何单个字符。</td> 
  </tr> 
  <tr> 
   <td>[...]</td> 
   <td>字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。</td> 
  </tr> 
  <tr> 
   <td>[^...]</td> 
   <td>负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。</td> 
  </tr> 
  <tr> 
   <td>\d</td> 
   <td>[0-9]，匹配所有的数字。</td> 
  </tr> 
  <tr> 
   <td>p1|p2|p3</td> 
   <td>匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。</td> 
  </tr> 
  <tr> 
   <td>{n}</td> 
   <td>是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。</td> 
  </tr> 
  <tr> 
   <td>{n,m}</td> 
   <td>m 和 n 均为非负整数，其中n &lt;= m。最少匹配 n 次且最多匹配 m 次。</td> 
  </tr> 
 </tbody> 
</table>

# 查询名字以l开头,以i结尾的
    select * from stu  where name regexp '^l|i$'
    
    SELECT 'hello' REGEXP '^he'      结果:1  表示匹配
    SELECT 'hello' REGEXP '^hh'      结果:0  表示不匹配

#### 字段控制 ####

##### 去除重复记录 #####

去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

SELECT DISTINCT sal FROM emp;

##### 列之间的计算 #####

查看雇员的月薪与佣金之和，因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL

SELECT *,sal+IFNULL(comm,0) FROM emp;

##### 给列名添加别名 #####

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(comm,0)  total FROM emp;

#### 结果排序 ####

1.  查询所有学生记录，按年龄升序排序
    
        SELECT *  FROM stu ORDER BY sage ASC;
        # 或者
        SELECT *  FROM stu ORDER BY sage;
2.  查询所有学生记录，按年龄降序排序
    
        SELECT *  FROM stu  ORDER BY age DESC;
3.  查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
    
        SELECT * FROM emp  ORDER BY sal DESC,empno ASC;

#### 聚合函数 ####

聚合函数，是作用在一列数据上的，对一列的数据进行运算的函数。包含有: max、min、sum、count、avg等常见的函数。

*  max(): 计算指定列数据的最大值
 *  min(): 计算指定列数据的最小值
 *  count(): 计算指定列不为NULL的数据的数量
 *  sum(): 计算指定列的数值的和，如果计算的列的类型不是数值类型，计算结果为0
 *  avg(): 计算指定列的数值的平均值，如果计算的列的类型不是数值类型，计算的结果为0

使用方法如下:

##### max #####

-- 用来计算指定列的最大值
    -- 计算最高的工资
    select max(sal) from emp;

##### min #####

-- 用来计算指定列的最小值
    -- 计算最低的工资
    select min(sal) from emp;

##### count #####

-- 用来统计指定列的数据的数量，注意，NULL不会被统计
    -- 1. 计算emp表中有多少人有工资sal
    select count(sal) from emp;
    -- 2. 计算emp表中有多少行数据
    select count(*) from emp;
    
    -- count(*) : 用来统计行记录，只要有这一行就会统计，即便这一行的所有的字段值都是NULL，依然算是一个有效的行

##### sum #####

-- 用来统计指定列的数据的和，注意，NULL不会被统计
    -- 计算emp表中的工资的和
    select sum(sal) from emp;

##### avg #####

-- 用来统计指定列的数据的平均值，注意，NULL不会被统计
    -- 计算emp表中的平均工资
    select avg(sal) from emp;

> 注意:
> 
> 在上述的需求中，我们需要统计员工的平均工资。但是，有些行的数据中，工资(sal)对应的值是NULL。
> 
> 例如: 表中一共有20行数据，有2行数据是NULL。那么平均值在计算的时候，会将每一个人的工资加到一起，用这个和除18，而并不是20。因为聚合函数不会统计NULL值的。
> 
> 如果需求需要将这个和均摊到每一个人的身上，包括NULL的行，那就需要对这条SQL语句进行修改了:
> 
> select avg(ifnull(sal, 0)) from emp;

#### 分组查询 ####

在进行查询的时候，可以按照某一个或多个字段进行分组。分组字段值相同的行会被视为一个分组。一般情况下，分组的意义是对每一个分组的数据进行聚合的统计，例如统计每一个分组的数量、最大值等操作。

**注意事项: 查询的字段中只能包含分组字段和聚合函数**

##### **group by** #####

-- 查询每一个部门的编号以及这个部门的最高工资(sal)
    select deptno, max(sal) from emp group by deptno;
    
    -- 查询每一个工作的名字以及这个工作的人数
    select job, count(*) from emp group by job;
    
    -- 查询每一个部门、每一个工作的人数
    select deptno, job, count(*) from emp group by deptno, job;

##### having #####

having是一个数据过滤的控制条件，类似于where，但是又和where有不同的地方:

1.  having是作用在分组之后的数据的，where是作用在分组之前的数据的。被where过滤掉的数据不参与分组。
    
    写法体现: having需要写在group by之后，where需要写在group by之前。
2.  having之后可以使用聚合函数，where不可以使用聚合函数。

-- 查询平均工资高于3000的部门编号及平均工资
    select deptno, avg(sal) from emp group by deptno having avg(sal) > 3000;

#### imit ####

select查询语句会查询出来一张表中所有的满足条件的数据。limit关键字可以限制查询结果的行数。

-- 查询emp表中的第0行开始，5行的数据。
    select * from emp limit 0, 5;
    
    -- 查询emp表中从第10行开始，7行的数据。
    select * from emp limit 10, 7;

灵活的使用limit，可以实现分页查询的效果。

-- 例如: 我需要在一个页面上显示数据库中的数据，但是页面的大小有限，每一页我需要显示20条数据。
    -- 第一页的数据:
    select * from news limit 0, 20;
    -- 第二页的数据:
    select * from news limit 20, 20;
    -- 第三页的数据:
    select * from news limit 40, 20;
    
    -- 往后的每一个分页的内容，只需要控制好每一次limit的起点即可。

#### 查询总结 ####

##### 查询语句书写顺序 #####

select – from - where - group by - having - order by - limit

##### 查询语句执行顺序 #####

from - where -group by - having - select - order by-limit

【千锋教育】大数据开发全套教程，史上最全面的大数据学习视频