数据库的DQL(1)

最新推荐文章于 2025-04-07 13:20:53 发布

传奇比特

最新推荐文章于 2025-04-07 13:20:53 发布

阅读量775

点赞数 19

分类专栏： MySQL专栏文章标签：数据库

本文链接：https://blog.youkuaiyun.com/2301_81548590/article/details/145241211

版权

MySQL专栏专栏收录该内容

17 篇文章

订阅专栏

数据库的DQL

DQL是sql中的一种重要语言类型，用于查询数据库中表的记录。DQL的主要关键字是select，用于从数据库中检索数据。常见的查询操作包括基本查询，条件查询，排序查询，分组查询，聚合函数等；

在MySQL中，可以使用select语句来查询数据。查询数据是指从数据库总根据需求，使用不同的查询方式来获取不同的数据。

SELECT <字段名> FROM 表名 WHERE 表达式

在练习之前，导入准备好的sql文件。


mysql> source D:\python and mysql-note\mysql笔记\emp.sql
Query OK, 6 rows affected (0.10 sec)

Query OK, 1 row affected (0.01 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected, 5 warnings (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)



mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept           |
| emp            |
| salgrade       |
+----------------+
3 rows in set (0.01 sec)


mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-07 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

1.基本查询

1.1查询指定字段

使用*通配符查询所有字段
列出表的所有字段

SELECT 可以使用*查找表中所有字段数据：

SELECT * FROM 表名;

使用*查询时，只能按照数据表中字段的顺序进行排序，不能改变字段的排序顺序。

案例1：


mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

使用* 查询，结果会返回表中所有列

一般情况下，除非要使用表格中所有数据，否则最好不要使用*查询。虽然可以节省输入查询语句的时间，但回去不需要的列数据通常会降低查询各所使用程序的效率。

select关键字后面的字段名为需要查找的字段，因此可以将表中所有字段的名称跟在select关键字后面。如果忘了字段名称，可以使用desc命令查看表结构。


mysql> select deptno,dname,loc from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

查询表中指定字段：

select 列名 from 表名

案列2：


mysql> select dname from dept;
+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)

mysql> select loc,dname from dept;
+----------+------------+
| loc      | dname      |
+----------+------------+
| NEW YORK | ACCOUNTING |
| DALLAS   | RESEARCH   |
| CHICAGO  | SALES      |
| BOSTON   | OPERATIONS |
+----------+------------+
4 rows in set (0.00 sec)

1.2.去重查询

在MySQL了中使用select语句执行简单的数据查询时，返回的是所有匹配的记录。若果表黄总的某些字段没有唯一性约束，那么这些字段就看存在重复值。为了实现查询不重复的数据，MySQL提供了distinct关键字。

select distinct 字段名 from 表名

如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重.

案例1：指定关键字去重


mysql> select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+
14 rows in set (0.00 sec)

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

案例2：多个字段去重


mysql> select job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| SALESMAN  |     30 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
| CLERK     |     20 |
| CLERK     |     30 |
| ANALYST   |     20 |
| CLERK     |     10 |
+-----------+--------+
14 rows in set (0.00 sec)

mysql> select distinct job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+
9 rows in set (0.00 sec)

distinct 只能返回他的目标字段，而无法返回其他字段，所以在实际情况中，我们经常使用distinct关键字来返回不重复字段的条数，

1.3设置别名

为了查询方便，MySQL提供了as关键字来为表和字段指定别名。

表名 AS 别名

案例1：指定表别名


mysql> select * from salgrade as sal;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

为字段指定别名

在使用select语句查询是MySQL会显示每个select后面指定输出的字段。为了显示结果更加直观，我们可以为字段指定一个别名。

字段名 AS 别名

AS 关键字也可以省略，省略后需要将字段名和别名用空格隔开。

案例2：


mysql> select grade as chengji,losal as di,hisal as gao from salgrade;
+---------+------+------+
| chengji | di   | gao  |
+---------+------+------+
|       1 |  700 | 1200 |
|       2 | 1201 | 1400 |
|       3 | 1401 | 2000 |
|       4 | 2001 | 3000 |
|       5 | 3001 | 9999 |
+---------+------+------+
5 rows in set (0.00 sec)

1.4指定查询数量

当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力，这时就可以使用limit关键字来限制查询结果返回的条数。

limit用于指定查询结果从哪一条记录开始显示，一共显示多少条记录。

1.指初始位置

limit 初始位置,记录数

数据表中第一条记录的位置是0，第二条位置是1…

案例1：


mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> select * from salgrade limit 2,3;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
3 rows in set (0.00 sec)

2.不指定初始位置

limit不指定初始位置是，记录从第一条记录开始显示。显示条数由limit关键字指定。

案例2：


mysql> select * from salgrade limit 2;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
+-------+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from salgrade limit 6;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

3.limit 和 offset组合使用

limit 记录数 offset 初始位置

初始位置”指定从哪条记录开始显示；“记录数”表示显示记录的条数

案例3：


mysql> select * from salgrade limit 2 offset 3;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
2 rows in set (0.00 sec)


mysql> select * from salgrade limit 2 offset 1;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
+-------+-------+-------+
2 rows in set (0.00 sec)

1.5查询结果排序

为了使查询结果的顺序满足用户的要求，MySQL提供了order by 关键字来对查询结果进行排序。

order by 字段名 [asc|desc]

ASC|DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值。
ORDER BY 关键字后可以跟子查询（关于子查询后面讲解，这里了解即可）。
当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。
ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。

案例1：


mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> select * from salgrade order by grade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

案例2：


mysql> select * from salgrade order by losal desc;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     5 |  3001 |  9999 |
|     4 |  2001 |  3000 |
|     3 |  1401 |  2000 |
|     2 |  1201 |  1400 |
|     1 |   700 |  1200 |
+-------+-------+-------+
5 rows in set (0.00 sec)

案例3：


mysql> select ename,mgr,sal from emp order by sal,hiredate;
+--------+------+---------+
| ename  | mgr  | sal     |
+--------+------+---------+
| SMITH  | 7902 |  800.00 |
| JAMES  | 7698 |  950.00 |
| ADAMS  | 7788 | 1100.00 |
| WARD   | 7698 | 1250.00 |
| MARTIN | 7698 | 1250.00 |
| MILLER | 7782 | 1300.00 |
| TURNER | 7698 | 1500.00 |
| ALLEN  | 7698 | 1600.00 |
| CLARK  | 7839 | 2450.00 |
| BLAKE  | 7839 | 2850.00 |
| JONES  | 7839 | 2975.00 |
| FORD   | 7566 | 3000.00 |
| SCOTT  | 7566 | 3000.00 |
| KING   | NULL | 5000.00 |
+--------+------+---------+
14 rows in set (0.00 sec)