MySQL查询相关语句，知识点总结全面，绝对有你想看的！

置顶农场主er

已于 2024-05-20 00:49:58 修改

阅读量393

点赞数 2

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

于 2020-05-04 11:46:45 首次发布

本文链接：https://blog.csdn.net/qq_42403042/article/details/105853925

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

使用MySQL

启动MySQL服务

命令	作用
`brew install mysql`	安装
`brew services start mysql`	启动3306端口服务
`mysql -u root -p`	登陆

了解数据库和表

命令	作用
`HELP SHOW;`	查询`SHOW`的用法
`SHOW DATABASES;`	查询所有的数据库
`USE db_name;`	打开数据库
`SHOW TABLES;`	返回当前选择的数据库内可用表的列表
`SHOW COLUMNS FROM tbl_name;`	返回每一列的数据定义

简单查询

语句	作用
`select 字段,... from 表名;`	查询一个或多个字段
`select * from 表名;`	查询全部字段
注释

SQL语句以；结尾；
SQL语句不区分大小写；
查询语句中字段可以参与数学运算，表达式中若有值为空，结果为空。例如emp员工表中有一个关键字sal表示月薪，查询年薪的SQL语句如下：select sal*12 from emp;。结果就会更改字段名为sal*12，如果想重命名就可以更改语句如下：select sal*12 as '年薪' from emp;，其中as可以省略，中文用单引号，保证在其它的数据库管理系统中仍可使用，因为MySQL相对之下还支持双引号；
实际开发中不建议用*，执行时会把*转化为字符串，效率较低。

示例
查找员工的年薪：select sal*12 ' 年薪 ' from emp;

+-----------+
| 年   薪   |
+-----------+
|   9600.00 |
|  19200.00 |
|  15000.00 |
|  35700.00 |
|  15000.00 |
|  34200.00 |
|  29400.00 |
|  36000.00 |
|  60000.00 |
|  18000.00 |
|  13200.00 |
|  11400.00 |
|  36000.00 |
|  15600.00 |
+-----------+

条件查询

假如有这么一个员工表emp：

根据指定条件查找相关数据的语句为： select 字段... from 表名 where 条件;

运算符	作用
`<>或!=`	不等于
`and`	并且，可以限制多个同时成立的条件
`or`	或者，可以增大查找的范围
`between ... and ...`	将查找范围限制在两个值之间，包括边界
`is (not) null`	是否为空，null代表相应的值不存在
`（not） in`	在给定的值‘数组’中查询，相当于`or` 。一般而言，数据库能为`in`条件生成的查询计划更有效地使用索引。
`like`	模糊查询，即根据值的部分信息检索信息
注释

between ... and ...用在字符串上左闭右开，即不包括and后的字符串；
and和or联合使用时，and的优先级大，会自动结合and前后的两个条件为一个整体，为避免出错，可使用（）；
like支持%（任意多个字符）、_（一个字符）匹配，转义字符为\,且like后的匹配表达式为字符串。在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

正则表达式

示例	作用
`REGEXP '1000'`	模式匹配，只要包含1000就可以
`REGEXP '1000 \| 2000'`	OR
`REGEXP '[1\|2\|3] Tom`	[1\|2\|3]定义一组字符，它的意思是匹配1或2或3
`REGEXP 'a-z`	定义一组范围，匹配a到z的字符
`REGEXP '\\.'`	匹配特殊字符.，sql 本身有一层转义，所以需要两个\

匹配字符类

在这里插入图片描述

匹配多个实例

在这里插入图片描述

定位符

在这里插入图片描述

示例

查找工资不小于3000的员工：select ENAME,SAL from emp where sal>=3000;

+-------+---------+
| ENAME | SAL     |
+-------+---------+
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

查找津贴不为空的员工： select ENAME,COMM from emp where COMM is not null;

+--------+---------+
| ENAME  | COMM    |
+--------+---------+
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| MARTIN | 1400.00 |
| TURNER |    0.00 |
+--------+---------+

查找工资大于2000且部门编号为10或20的员工： select ENAME,SAL,DEPTNO from emp where sal>2000 and (deptno=10 or deptno=20);

+-------+---------+--------+
| ENAME | SAL     | DEPTNO |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| CLARK | 2450.00 |     10 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

查询工作岗位为销售员和分析员的员工： select ENAME,JOB from emp where job in ('salesman','analyst');

+--------+----------+
| ENAME  | JOB      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| SCOTT  | ANALYST  |
| TURNER | SALESMAN |
| FORD   | ANALYST  |
+--------+----------+

查询姓名以S开头的员工： select ENAME from emp where ename like 'S%';

+-------+
| ENAME |
+-------+
| SMITH |
| SCOTT |
+-------+

排序数据

语句	作用
`select 字段,... from 表名 order by 字段 (asc/desc),...;`	根据指定字段的大小升序/降序排列数据
备注

若需要排序的字段后没有asc/desc，默认升序排列；
若有多个字段需要排序，除非根据第一个字段无法完成排序（相等），不然不会启动第二个字段进行排序，以此类推；
排序的字段也可使用列所在的数字，如...order by 1,但这样会降低程序的健壮性；
如果语句中同时出现了where，要把order by放在其后。

示例
选择出销售员并根据他们的工资进行降序排列，若工资一样则根据名字的升序进行排列：
select * from emp where job="salesman" order by sal desc,ename asc;

+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

创建计算字段

拼接字段

SELECT Concat(RTrim(col_name1), '(', RTrim(col_name2),')' ) AS new_col_name from tbl_name;
假如第一列的值为Paul，第二列的值为 USA，结果为Paul(USA)。

除此之外，列之间可以进行+ - * /运算。

函数

分组（多行处理）函数

函数名	作用
`count`	取得记录数
`sum`	求和
`avg`	求平均值
`max`	求最大值
`min`	求最小值
对字段对应的数据进行处理的语句为： `select 分组函数(字段),... from 表名；`。

注释

分组函数会自动忽略null；
分组函数不能直接在where之后；
分组函数必须在数组分组之后使用，默认整张表为一组;
如果想去除重复，可利用distinct,且只能放在所有字段的前面，表示同时去重，例如select count(distinct job) from emp;表示统计岗位数量。

示例
取得表格中的所有记录数以及津贴的记录数： select count(*),count(comm) from emp;

 +----------+-------------+
| count(*) | count(comm) |
+----------+-------------+
|       14 |           4 |
+----------+-------------+

如果要计算员工总薪水（包括补助），即select sum(sal+comm) from emp;,由于null参与运算时结果总为null，sum因此忽略掉补助为null员工的薪水，可用ifnull（可能为空的数据，为空时另外所赋的值）空处理函数：select sum(sal+ifnull(comm,0)) from emp;。
既然是分组函数，那么如何对数据进行分组呢？
如果想要按照某个字段进行分组处理，可用select 字段,... from 表名 group by 字段,...;，那么再利用分组函数时就会在每个组内分别调用。值得注意的是select后的字段必须是分组时使用的字段或者是分组函数！
跟group by一起使用的还有过滤子句having，用法与where一致，且放到group by之后，并可使用分组函数。
示例
计算出每个部门中相同工作岗位上职员的最低工资，且该工资需要大于1000,并按部门编号由小到大输出：
select deptno,job,min(sal) from emp group by deptno,job having min(sal)>1000 order by deptno;

+--------+-----------+----------+
| deptno | job       | min(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1250.00 |
+--------+-----------+----------+

总结

完整的查询语句如下：select 字段,... from 表名 where 条件 group by 字段,... having 过滤条件 order by 字段(asc/desc),...; ；
where在分组之前执行，分组函数在分组之后执行，这就是where语句中不能调用分组函数的原因；
先执行select选出数据之后再order by排序输出；
能利用where筛选尽量不用having过滤，以提高效率。

数据处理函数

在这里插入图片描述

连接查询

以上的学习都是操作一张表，如果需要同时操作多张表，就要用到连接查询。

1、内连接

两个表之间有字段可以进行匹配，表没有主次之分。

语句	作用
`select 表1的字段，表2的字段 from 表1 表1重命名 (inner) join 表2 表2重命名 on 连接条件`	根据多个表格之间的连接关系查询
注释

为避免混淆，表的字段常常这样表示：（重命名后的）表名.字段名；
有时候为了表示的方便（区分同一张表）往往重命名表，自连接必须要重命名。

1.1 等值连接

连接条件中两个表格中的字段是一对一的关系。例如查询每个员工的办公地址：
select emp.ename,dept.loc from emp join dept on emp.deptno=dept.deptno;

//部门表deptno结构如下：
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
//查询结果
+--------+----------+
| ename  | loc      |
+--------+----------+
| SMITH  | DALLAS   |
| ALLEN  | CHICAGO  |
| WARD   | CHICAGO  |
| JONES  | DALLAS   |
| MARTIN | CHICAGO  |
| BLAKE  | CHICAGO  |
| CLARK  | NEW YORK |
| SCOTT  | DALLAS   |
| KING   | NEW YORK |
| TURNER | CHICAGO  |
| ADAMS  | DALLAS   |
| JAMES  | CHICAGO  |
| FORD   | DALLAS   |
| MILLER | NEW YORK |
+--------+----------+

1.2 非等值连接

连接条件中两个表格中的字段不是一对一的关系。例如查询每个员工的薪资等级：
select emp.ename,emp.sal,salgrade.grade from emp join salgrade on emp.sal between salgrade.losal and salgrade.hisal order by emp.sal;

//薪资等级表salgrade结构如下：
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |
| LOSAL | int(11) | YES  |     | NULL    |       |
| HISAL | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
//查询结果为：
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| JAMES  |  950.00 |     1 |
| ADAMS  | 1100.00 |     1 |
| WARD   | 1250.00 |     2 |
| MARTIN | 1250.00 |     2 |
| MILLER | 1300.00 |     2 |
| TURNER | 1500.00 |     3 |
| ALLEN  | 1600.00 |     3 |
| CLARK  | 2450.00 |     4 |
| BLAKE  | 2850.00 |     4 |
| JONES  | 2975.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| FORD   | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
+--------+---------+-------+

1.3 自连接

查询中需要同时用到同一张表。例如查询每个员工的经理：
select e.ename as employee,m.ename as manager from emp e join emp m on e.mgr=m.empno;

+----------+---------+
| employee | 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   |
+----------+---------+

2、外连接

使用内连接有一个问题，比如查询每个员工的经理，其中有个员工没有经理，但是查询结果中并没有显示这条信息，这个时候就要用到外连接，将所有的员工都进行匹配，匹配不到结果用NULL表示。
外连接中两个表有主次之分，选定的主表所有字段都会显示，不会因为在另外一个表中没有与之匹配的信息就不显示。

语句	作用
`select 表1的字段，表2的字段 from 表1 (表1重命名) left/right (outer) join 表2 (表2重命名) on 连接条件`	left、right分别表示表1、2为主表
示例
查询没有员工的部门：`select dept.deptno from emp right outer join dept on emp.deptno=dept.deptno where emp.ename is null;`

+--------+
| deptno |
+--------+
|     40 |
+--------+

3、综合练习

查询出每个员工的办公地址、工资等级以及经理姓名：
select e.ename,d.loc,s.grade,m.ename from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between losal and hisal left join emp m on e.mgr=m.empno;

+--------+----------+-------+-------+
| ename  | loc      | grade | ename |
+--------+----------+-------+-------+
| SMITH  | DALLAS   |     1 | FORD  |
| ALLEN  | CHICAGO  |     3 | BLAKE |
| WARD   | CHICAGO  |     2 | BLAKE |
| JONES  | DALLAS   |     4 | KING  |
| MARTIN | CHICAGO  |     2 | BLAKE |
| BLAKE  | CHICAGO  |     4 | KING  |
| CLARK  | NEW YORK |     4 | KING  |
| SCOTT  | DALLAS   |     4 | JONES |
| KING   | NEW YORK |     5 | NULL  |
| TURNER | CHICAGO  |     3 | BLAKE |
| ADAMS  | DALLAS   |     1 | SCOTT |
| JAMES  | CHICAGO  |     1 | BLAKE |
| FORD   | DALLAS   |     4 | JONES |
| MILLER | NEW YORK |     2 | CLARK |
+--------+----------+-------+-------+

子查询

1、select嵌套查询

select后面跟的是字段名，如果想查到另外一张表中的字段，就可以用select嵌套语句。由于是多张表的select查询，如果不指定字段间的关系，就会陷入笛卡尔乘积的陷阱之中。
例如用select嵌套查询两列员工姓名：select ename,(select ename from emp) as ename from emp;就会报错ERROR 1242 (21000): Subquery returns more than 1 row，说明不能理解为拼接。
正确的写法应该是select e.ename,(select d.ename from emp d where d.ename=e.ename) as ename from emp e;，指定关系查询就会一一匹配出结果。

2、from嵌套查询

from嵌套查询着重点是把嵌套查询结果看作一张表，注意要给表重命名。例如用from嵌套查询查询平均工资：
select e.avgsal from (select avg(sal) as avgsal from emp) e;

+-------------+
| avgsal      |
+-------------+
| 2073.214286 |
+-------------+

3、where嵌套查询

where嵌套查询着重点是查询出的数据，例如查询高于平均薪资的员工：
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 |
+-------+---------+

union

union用来合并查询结果，前提是需要合并的数据对应的列数应该一致，总列名为union之前的数据列名。例如查询部门编号为10、20的员工： select ename,deptno from emp where deptno=10 union select ename,deptno from emp where deptno=20;

+--------+--------+
| ename  | deptno |
+--------+--------+
| CLARK  |     10 |
| KING   |     10 |
| MILLER |     10 |
| SMITH  |     20 |
| JONES  |     20 |
| SCOTT  |     20 |
| ADAMS  |     20 |
| FORD   |     20 |
+--------+--------+

limit

limit的作用在于显示查询结果的部分数据，limit index,length只显示索引（从1开始）在[index+1,index+length]区间内的数据，如果不写index，默认index=0。例如查询薪资排名前3的员工：select ename,sal from emp order by sal desc limit 3/0,3;:

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
+-------+---------+

查询语句到此结束，欢迎评论区交流~👍

农场主er

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
1
评论
MySQL查询相关语句，知识点总结全面，绝对有你想看的！

一、简单查询语句作用select 字段名1，字段名2... from 表名;查询一个或多个字段select * from 表名;查询全部字段注释SQL语句以；结尾；SQL语句不区分大小写；查询语句中字段可以参与数学运算，例如emp员工表中有一个关键字sal表示月薪，查询年薪的SQL语句如下：select sal*12 from emp;。结果就会更改字...
复制链接

扫一扫