MYAQL SELECT 语句-CSDN博客

本文链接：https://blog.csdn.net/qq_57197323/article/details/125972989

MYAQL SELECT 语句

1 结构化查询语言

数据查询语言 DQL （Data Query Language）语句主要包括SELECT，用于从表中检错数据
数据操作语言 DML （Data Manipulation Language）语句主要包括 INSERT，UPDATE,DELETE 用于添加、修改、删除表中的行数据
事务处理语言 TPL( Transaction Process Language) 语句主要包括 COMMIT 和 ROLLBACK，用于提交和回滚
数据控制语言 DCL（Data Control Lauguage）语句主要包括GRANT 和REMOVE 用于进行授权和收回权限。
数据定义语言 DDL （Data Definition Lauguage）语句主要包括CREATE、DROP、ALTER，用于定义、销毁、修改数据库对象

2 SELECT语句的结构

2.1 select 语句由多个子句组成

简单语句结构为：

SELECT 列名 
	FROM 表名

完整语句结构为：（只是最完整的查询语句会由以下七个子句构成，一般做查询时只需要根据需要选择子句，不一定要全部都写上）

SELECT 列名
	FROM 表名
	where 过滤条件
	GROUP BY 分组条件
	HAVING 过滤条件
	ORDER BY 别名 [ASC,DESC]
	LIMIT 开始记录数，每页的记录数

2.2 SELECT 简单语句学习

首先运行一下代码，建立dept (部门表) ,emp(员工表)，salgrade(等级表)

create table dept
(
deptno integer(4) not null,
dname varchar(14),
loc varchar(13)
);
alter table dept add constraint pk_dept primary key (deptno);
create table emp
(
empno integer(4) not null,
ename varchar(10),
job varchar(9),
mgr integer(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno integer(2)
);
alter table emp add constraint pk_emp primary key (empno);
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);
create table salgrade
(
grade integer(1),
losal decimal(7,2),
hisal decimal(7,2)
);
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');
insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);
-- 下面要特别注意，%d-%m-%Y 里的 d 和 m 一定要小写
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600.00, 300.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250.00, 500.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250.00, 1400.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('19-04-1987', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500.00, 0.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('23-05-1987', '%d-%m-%Y'), 1100.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300.00, null, 10);

开始操作

一简单查询列

1 查询emp表中的所有列

SELECT * FROM emp

2 查询emp 表中的指定列（empno,ename,sal）

SELECT empno,ename,sal FROM emp

在数据足够的情况下选择指定列的查询效率比查询所有列更高

二为查询的列取别名

3 假设员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入，
要求显示列标题为员工姓名，工资收入，奖金收入，总收入。（别名）

添加列别名有两种方法

方式一：列名列别名
方式二：列名 AS 列别名

在一下三种情况下，列别名需要添加双引号

列别名中包含空格
列别名要求区分大小写（因为 MYSQL 是不区分大小写的如果需要分别大小写，需要在别名上添加双引号）
类别名中有特殊字符

方式一

SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal*6+sal*1.2*6+comm 总收入
	FROM emp

方式二

SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal*6+sal*1.2*6+comm 总收入
	FROM emp

三使用where子句过滤条件

这里需要先介绍 MYSQL 提供的运算符作为过滤的条件

优先级·	运算分类	符号
1	算术运算符	* / + -
2	比较运算符	= <> < > <= >=
3	特殊运算符	DETWEEN AND、IN LIKE、IS NULL
4	逻辑非	NOT
5	逻辑与	AND
6	逻辑或	OR

**（）括号的优先级是最高的，可以使用括号强制改变优先级 **

**NOT 可以配合特殊运算符使用如 NOT LIKE IS NOT NULL NOT BETWEEN AND **

4 查询入职日期在82年至85年的员工姓名，入职日期。 特殊运算符 BETWEEN AND 的使用

SELECT ename,hiredate 
	FROM emp 
	WHERE hiredate BETWEEN '1982-01-01' AND '1985-12-31'

5 查询部门编号为10或者20的员工姓名，部门编号。特殊运算符 IN 的使用

SELECT ename,deptno 
	FROM emp 
	WHERE deptno IN (10,20)

6 查询 JOB 以“MAN”开头的雇员信息 特殊运算符 LIKE 的使用

LIKE 需要搭配 % (百分号) _(下划线)一起使用，百分号代表任意多个字符，下划线代表一个任意字符。当需要比对的字段中有%或__符号出现时使用 ESCAPE 函数

SELECT * 
	FROM emp 
	WHERE job LIKE 'MAN%'

SELECT a
	FROM t_a
	WHERE a LIKE 'MAN@_%' ESCAPE '@'

7 查询奖金为空的员工姓名，奖金。特殊运算符 IS NULL 使用

在MYSQL中空值不等于 ‘ ’，并且空值参与的表达式也被认为是空值，如果需要计算空值时，可以使用 IFMULL 函数

SELECT ename,comm 
	FROM emp 
	WHERE comm IS NULL

8 查询所有员工的姓名和一年的总收入

SELECT ename,sal*12+IFNULL(comm,0) AS 总收入 
	FROM emp

四 ORDER BY 子句排序

9 查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。(升序 ASC 为从小到大降序 DESC 从大到小 NULL值可以认为时最小)

SELECT ename,deptno,sal 
	FROM emp 
	WHERE sal BETWEEN 2000 AND 3000 
	ORDER BY deptno,sal DESC

五 LIMIT 子句限制记录行数

LIMITI 有两个参数 LIMITI [START],LENGTH 第一个参数可选，表示从第几行记录开始输出数据，第二个参数表示输出记录行数 第一行记录的值为0 而不是1

10 查询20号部门下入职日期最早的前2名员工姓名，入职日期。

SELECT ename,hiredate 
	FROM emp  
	WHERE deptno=20 
	ORDER BY hiredate 
	LIMIT 0,2

2.3 SELECT 多表查询

2.3.1 多表连接

多表连接按连接条件分可以分为 :：等值连接和非等值连接

按其他连接分可以分为：内连接和外连接

并且有两种写法：基本写法和 ANNSI SQL 1999

基本写法

1 笛卡尔积

第一个表中的所有行与第二个表中的所有行进行连接，会产生非常庞大的数据，出现这种情况是因为两个表连接时没有写连接条件，或者连接连接条件无效

SELECT * 
	FROM emp,dept

2 等值连接

当两个表中有同一个字段属性或者存在相同类型且存在联系的字段时，可以通过该字段将两个表连接起来

1 写一个查询，显示所有工作在CHICAGO并且奖金为空的员工姓名，工作地点，奖金 ,部门编号

SELECT e.ename,d.loc,e.comm,e.deptno 
	FROM emp e,dept d 
	where e.deptno=d.deptno 
	AND d.loc='CHICAGO' 
	AND e.comm IS  NOT NULL

当两个表中有相同字段的数据时，可以为器提供表别名，以免显示时出现歧义

3 非等值连接

2 查询每个员工的姓名，工资，工资等级

SELECT     e.ename, e.sal, s.grade
	FROM    emp e,   salgrade s
	WHERE     e.sal
	BETWEEN     s.losal AND s.hisal;

4 自连接

3 查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号

SELECT e1.ename 员工姓名,e1.empno 员工编号,e2.ename 经理姓名 ,e2.empno 经理编号
	FROM emp e1,emp e2
	WHERE e1.deptno=e2.deptno AND e1.mgr=e2.empno

ANSI SQL 1999 标准连接语法

交叉连接等同于笛卡尔积

自然连接以多个表的所有同名同类型的列作为连接条件

USING连接指定多个表中的某个同名同类型的列作为连接

ON 连接正常的等值连接 (常用)

外连接

左外连接

4 查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来

SELECT ename,emp.deptno,dname 
	FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno

右外连接

SELECT ename,emp.deptno,dname 
	FROM dept RIGHT JOIN emp ON emp.deptno=dept.deptno

count (*) 不会忽略空值但是count其他的条件会忽略空值

在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中。

2.4 高级查询（分组查询子查询）

2.4.1 分组查询

分组查询的关键词 GROUP BY MIN MAX SUM AVG COUNT

1 查询入职日期最早和最晚的日期

SELECT MAX(hiredate),MIN(hiredate) 
	FROM emp

2 查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和

SELECT AVG(sal),MIN(sal),MAX(sal),SUM(sal) 
	FROM emp 
	WHERE job LIKE 'SALES%'

3 查询部门30有多少个员工领取奖金

SELECT COUNT(deptno) 
	FROM emp 
	WHERE deptno=30

除了COUNT（*）之外，其它所有分组函数都会忽略列中的空值，然后再进行计算。

4 查询每个部门的编号，平均工资

SELECT deptno,AVG(sal) 
	FROM emp 
	GROUP BY deptno

存在GROUP BY 语句时，SELECT查询的列除了分组函数的项（avg,sum…）其他列都必须包含在GROUP BY 语句中，但是不是所有GROUP BY 所指定的列不是必须要在SELECT中

5 查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

SELECT COUNT(*),e2.empno,e2.ename
	FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno
	GROUP BY e2.mgr

HAVING 子句

当需要使用 GROUP BY 子句的结果作为过滤条件时，将过滤条件写在 HAVING子句中，如果卸载where子句中，将会无法执行

6 查询每个部门最高工资大于2900的部门编号，最高工资

SELECT deptno,MAX(sal) 
	FROM emp
	GROUP BY deptno
	HAVING MAX(sal)>2900

SELECT语句执行过程：

通过FROM子句中找到需要查询的表；
通过WHERE子句进行非分组函数筛选判断；
通过GROUP BY子句完成分组操作；
通过HAVING子句完成组函数筛选判断；
通过SELECT子句选择显示的列或表达式及组函数；
通过ORDER BY子句进行排序操作。

2.4.1 子查询

子查询也被称为内部查询，会先于主查询执行，且子查询的结果会被主查询应用

7 查询工资比Jones工资高的员工信息

SELECT * 
	FROM emp 
	WHERE sal>(
        SELECT sal 
        FROM emp 
        WHERE ename='jones'
    )

根据子查询返回的行和列的数值，可以分为

单行子查询返回一行一列
多行子查询返回多行一列
多列子查询返回一条或多条记录

单行子查询

8 显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作

SELECT ename,job 
	FROM emp 
	WHERE job=
	(SELECT job FROM emp WHERE empno=7369) 
    AND sal>
     (SELECT sal FROM emp WHERE empno=7876)

多行子查询

和多行紫川进行比较时，使用多行操作符 IN ANY ALL

9 查询是经理的员工姓名，工资

SELECT ename, sal 
	FROM emp 
	WHERE empno IN
    (SELECT mgr FROM emp )

10 查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资

SELECT empno,ename,job,sal 
	FROM emp 
	WHERE deptno<>20 AND 
	sal>ALL(SELECT sal FROM emp WHERE deptno=20)

在 FROM子句中使用子查询

11 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

SELECT e.ename,e.sal,e.deptno,AVG(sal) 
	FROM emp e,(SELECT deptno,AVG(sal) 
                FROM emp 
                GROUP BY deptno) d 
    WHERE e.deptno=d.deptno