MySQL 数据库学习笔记（二）——DQL数据查询语句

最新推荐文章于 2024-07-26 19:54:25 发布

胖嘟嘟62

最新推荐文章于 2024-07-26 19:54:25 发布

阅读量170

点赞数

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

本文链接：https://blog.csdn.net/qq_55069056/article/details/118254532

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

简单查询

查询一个字段

// 语句
select 字段 from 表名;

// 演示，查询部门列表
mysql> use plumpdudu;
Database changed
mysql> select dname from dept;
+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)

查询多个字段

// 语句
select 字段1,字段2,... from 表名;

// 演示，查询部门编号，部门名称
mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

查询全部字段

// 语句
select * from 表名;

// 演示，查询部门表中全部字段
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 字段(int型)*num from 表名;

// 演示，查询员工，年薪
mysql> select ename,sal*12 from emp;
+--------+----------+
| ename  | sal*12   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

查询结果去重

使用distinct 字段,字段语句，当查询字段全部重复时去除重复

// 语句
select distinct 字段,字段 from 表名;

// 演示，查询每个部门设置的职位
mysql> select distinct deptno,job from emp;
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+
9 rows in set (0.03 sec)

重命名表和字段

// 语句
select 字段 as 别名 from 表名;

// 演示
mysql> select ename as '员工',sal*12 as '年薪' from emp;
+--------+----------+
| 员工   | 年薪     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

注意：as可以省略
使用中文别名时，使用单引号''进行标识，由于其他数据库一般支持单引号而不支持双引号，为保证可移植性，建议不要使用双引号""

条件查询

条件查询使用where，放在from后面

条件	说明
`=`	等于
`<>`或`!=`	不等于
`<`	小于
`<=`	小于等于
`>`	大于
`>=`	大于等于
`between` … `and` ….	介于两个值之间(包含)，等同于 >= and <=
`is null`	为null (`is not null`：不为null)
`and`	并且
`or`	或者
`in` / `not in`	包含，相当于多个or / 不包含
`like`	模糊查询，支持`%`或`_`匹配，`%`匹配任意个字符，`_`匹配一个字符

`=`、`<>`/`!=`、`<`、`<=`、`>`、`>=`

// 语句
select 字段 from 表名 where 字段1 >= 数值(或字段);

// 演示，查询月薪大于等于2500的员工
mysql> select ename,sal from emp where sal >= 2500;
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
5 rows in set (0.00 sec)

// 其他几个同理，这里不做演示

`between` … `and` ….

// 语句
select 字段 from 表名 where between 数值(或字段) and 数值(或字段);

// 演示，查询月薪在2000到4000的员工
mysql> select ename,sal from emp where sal between 2000 and 4000;
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
+-------+---------+
5 rows in set (0.00 sec)

`is null`、`and`、`or`、`in`

注意：null类型比较特殊，只能使用is / is not来比较

// 语句
select 字段 from 表名 where and / or / in / is null;

//--------------------------------------------------------------------------

// 演示is null，查询津贴为空的员工
mysql> select ename,comm from emp where comm is null;
+--------+------+
| ename  | comm |
+--------+------+
| SMITH  | NULL |
| JONES  | NULL |
| BLAKE  | NULL |
| CLARK  | NULL |
| SCOTT  | NULL |
| KING   | NULL |
| ADAMS  | NULL |
| JAMES  | NULL |
| FORD   | NULL |
| MILLER | NULL |
+--------+------+
10 rows in set (0.00 sec)

//--------------------------------------------------------------------------

// 演示and / or，查询有津贴或工资大于等于3000的员工
mysql> select ename,sal,comm from emp where comm is not null and comm != 0 or sal >= 3000;
+--------+---------+---------+
| ename  | sal     | comm    |
+--------+---------+---------+
| ALLEN  | 1600.00 |  300.00 |
| WARD   | 1250.00 |  500.00 |
| MARTIN | 1250.00 | 1400.00 |
| SCOTT  | 3000.00 |    NULL |
| KING   | 5000.00 |    NULL |
| FORD   | 3000.00 |    NULL |
+--------+---------+---------+
6 rows in set (0.00 sec)

//--------------------------------------------------------------------------

// 演示in
mysql> select * from emp where job in ('manager','analyst');
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  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-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)

like(模糊查询)

使用like进行模糊查询，支持_单字符匹配和%多字符匹配

// 语句
select 字段 from 表名 where 字段 like '模糊条件';

//--------------------------------------------------------------------------

// 演示，查询姓名以m开头的员工
mysql> select * from emp where ename like 's%';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)

//--------------------------------------------------------------------------

// 演示，查询姓名以r结尾的员工
mysql> select * from emp where ename like '%r';
+-------+--------+----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+------+--------+
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+----------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)

//--------------------------------------------------------------------------

// 演示，查询入职时间为12月的员工
mysql> select * from emp where hiredate like '____-12%';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.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 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

数据排序

使用order by 字段语句进行排序，可以使用多个字段，字段间用,分隔，asc升序排列，desc降序排列，不写默认为升序

// 语句
select 字段 from 表名 order by 排序字段

//演示，查询无津贴员工并按工资降序显示
mysql> select * from emp where comm is null order by sal desc;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
+-------+--------+-----------+------+------------+---------+------+--------+
10 rows in set (0.00 sec)

//--------------------------------------------------------------------------

// 演示，根据列号进行排序，但不建议使用
mysql> select * from emp where comm is null order by 6;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
10 rows in set (0.09 sec)

分组查询

分组常用函数

count 总数，sum 总和，avg 平均值，max 最大值， min 最小值
注意：分组函数在计算时会自动忽略null值，不需要手动判断是否为空

// 演示
mysql> select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal)    | max(sal) | min(sal) |
+----------+----------+-------------+----------+----------+
|       14 | 29025.00 | 2073.214286 |  5000.00 |   800.00 |
+----------+----------+-------------+----------+----------+
1 row in set (0.01 sec)

`group by`

使用group by 字段将数据按字段内容进行分组

// 语句
select 字段 from 表名 group by 字段;

// 演示，查询员工表中的工作种类
mysql> select job from emp group by job;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

//--------------------------------------------------------------------------

// 演示，查询员工表中每个工作种类的平均工资
mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

`having`

使用having可对分组后的数据进行过滤

// 语句
select 字段 from 表名 group by 字段 having 过滤条件;

// 演示，查询员工表中平均工资低于3000的工作种类
mysql> select job,avg(sal) from emp group by job having avg(sal) < 3000;
+----------+-------------+
| job      | avg(sal)    |
+----------+-------------+
| CLERK    | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER  | 2758.333333 |
+----------+-------------+
3 rows in set (0.11 sec)

执行顺序分析：1.from 2.where 3.group by 4.having 5select 6.order by

连接查询

使用`where`进行连接

在进行连接查询时，通常会对表取别名表名 as 别名（as可省略），方便书写和识别

// 语句
select 表名.字段,表名.字段 from 表名,表名 where 连接条件;

// 演示，查询每个员工所属的部门名称
mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.14 sec)

单纯使用where的方式属于SQL92语法，而在SQL99语法中新增了join on方法，具有表连接和查询条件分离的优点，join on分为内连接和外连接，外连接又分为左连接和右连接

内连接

内连接使用inner join on语句从多张表中联合查询出满足条件的数据，inner一般会省略

- 等值连接

// 语法
select 表名.字段,表名.字段 from 表名 join 表名 on 字段1 = 字段2;

// 演示，显示薪资高于2000的员工岗位、工资、所属部门
mysql> select e.ename,e.job,d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+-----------+------------+
| ename  | job       | dname      |
+--------+-----------+------------+
| SMITH  | CLERK     | RESEARCH   |
| ALLEN  | SALESMAN  | SALES      |
| WARD   | SALESMAN  | SALES      |
| JONES  | MANAGER   | RESEARCH   |
| MARTIN | SALESMAN  | SALES      |
| BLAKE  | MANAGER   | SALES      |
| CLARK  | MANAGER   | ACCOUNTING |
| SCOTT  | ANALYST   | RESEARCH   |
| KING   | PRESIDENT | ACCOUNTING |
| TURNER | SALESMAN  | SALES      |
| ADAMS  | CLERK     | RESEARCH   |
| JAMES  | CLERK     | SALES      |
| FORD   | ANALYST   | RESEARCH   |
| MILLER | CLERK     | ACCOUNTING |
+--------+-----------+------------+
14 rows in set (0.00 sec)

- 非等值连接

// 语法
select 表名.字段,表名.字段 from 表名 join 表名 on 字段1 between 字段2 and 字段3;

// 演示，查询员工工资、工资等级
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.11 sec)

- 自连接

// 语法
select 表名1.字段,表名2.字段 from 表名 别名1 join 同表名 别名2 on 字段1 = 字段2;

// 演示，查询员工对应上级
mysql> select e.ename,m.ename manager from emp e join emp m on e.mgr = m.empno;
+--------+---------+
| ename  | manager |
+--------+---------+
| SMITH  | FORD    |
| ALLEN  | BLAKE   |
| WARD   | BLAKE   |
| JONES  | KING    |
| MARTIN | BLAKE   |
| BLAKE  | KING    |
| CLARK  | KING    |
| SCOTT  | JONES   |
| TURNER | BLAKE   |
| ADAMS  | SCOTT   |
| JAMES  | BLAKE   |
| FORD   | JONES   |
| MILLER | CLARK   |
+--------+---------+
13 rows in set (0.00 sec)

外连接

不同于内连接，外连接会以一张表为主表，无论其他表中有没有满足条件的数据与主表匹配，主表都会完整的显示出来，若无匹配数据则自动填充为null

- 左连接

注意：此处与上面的内连接中的自连接不同点在于，此处虽然KING没有上级，仍然会显示出来

// 语法
select 表名.字段,表名.字段 from 表名 别名 left join 表名 别名 on 字段1 = 字段2;

// 演示，查询员工对应上级
mysql> select e.ename,m.ename manager from emp e left join emp m on e.mgr = m.empno;
+--------+---------+
| ename  | manager |
+--------+---------+
| SMITH  | FORD    |
| ALLEN  | BLAKE   |
| WARD   | BLAKE   |
| JONES  | KING    |
| MARTIN | BLAKE   |
| BLAKE  | KING    |
| CLARK  | KING    |
| SCOTT  | JONES   |
| KING   | NULL    |
| TURNER | BLAKE   |
| ADAMS  | SCOTT   |
| JAMES  | BLAKE   |
| FORD   | JONES   |
| MILLER | CLARK   |
+--------+---------+
14 rows in set (0.00 sec)

- 右连接

// 语法
select 表名.字段,表名.字段 from 表名 别名 right join 表名 别名 on 字段1 = 字段2;

// 演示，查询每个部门含有的员工
mysql> select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)

练习

题目：查询每个员工的所属部门及工资等级，并按部门、工资排序

mysql> select e.ename,d.dname,e.sal,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal order by e.deptno,e.sal;
+--------+------------+---------+-------+
| ename  | dname      | sal     | grade |
+--------+------------+---------+-------+
| MILLER | ACCOUNTING | 1300.00 |     2 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
| SMITH  | RESEARCH   |  800.00 |     1 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| JAMES  | SALES      |  950.00 |     1 |
| WARD   | SALES      | 1250.00 |     2 |
| MARTIN | SALES      | 1250.00 |     2 |
| TURNER | SALES      | 1500.00 |     3 |
| ALLEN  | SALES      | 1600.00 |     3 |
| BLAKE  | SALES      | 2850.00 |     4 |
+--------+------------+---------+-------+
14 rows in set (0.00 sec)

子查询

在`where`后面进行子查询

查询工资高于平均工资的员工

mysql> select ename,sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.13 sec)

在`form`后面进行子查询

查询每个部门平均工资的工资等级

mysql> select ds.dname,ds.avgsal,s.grade from (select d.dname,avg(e.sal) avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno) ds join salgrade s on ds.avgsal between s.losal and s.hisal;
+------------+-------------+-------+
| dname      | avgsal      | grade |
+------------+-------------+-------+
| RESEARCH   | 2175.000000 |     4 |
| SALES      | 1566.666667 |     3 |
| ACCOUNTING | 2916.666667 |     4 |
+------------+-------------+-------+
3 rows in set (0.10 sec)

`union`(联合查询)

查询岗位为SALESMAN和CLERK的员工

mysql> select ename,job from emp where job = 'salesman' union select ename,job from emp where job = 'clerk';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| SMITH  | CLERK    |
| ADAMS  | CLERK    |
| JAMES  | CLERK    |
| MILLER | CLERK    |
+--------+----------+
8 rows in set (0.10 sec)

`limit`(分页查询)^*****

// 语句
select 字段 form 表名 limit 起始下标,长度;

// 演示，查询工资排名前五的员工
mysql> select * from emp order by sal desc limit 0,5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+
5 rows in set (0.11 sec)

查询语句执行顺序分析

select			5
	...
from			1
	...
where			2
	...
group by		3
	...
having			4
	...
order by		6
	...
limit			7
	...

胖嘟嘟62

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 数据库学习笔记（二）——DQL数据查询语句

MySQL 数据库学习笔记（二）——DQL数据查询语句简单查询条件查询数据排序分组查询链接查询子查询联合查询分页查询
复制链接

扫一扫

专栏目录

MySQL 数据库学习笔记（二）——DQL数据查询语句

MySQL 数据库学习笔记（二）——DQL数据查询语句

简单查询

查询一个字段

查询多个字段

查询全部字段

使用运算符

查询结果去重

重命名表和字段

条件查询

=、<>/!=、<、<=、>、>=

between … and ….

is null、and、or、in

like(模糊查询)

数据排序

分组查询

分组常用函数

group by

having

连接查询

使用where进行连接

内连接

- 等值连接

- 非等值连接

- 自连接

外连接

- 左连接

- 右连接

练习

子查询

在where后面进行子查询

在form后面进行子查询

union(联合查询)

limit(分页查询)*****

查询语句执行顺序分析

`=`、`<>`/`!=`、`<`、`<=`、`>`、`>=`

`between` … `and` ….

`is null`、`and`、`or`、`in`

`group by`

`having`

使用`where`进行连接

在`where`后面进行子查询

在`form`后面进行子查询

`union`(联合查询)

`limit`(分页查询)^*****