MySql基础入门到进阶

最新推荐文章于 2025-07-04 09:00:41 发布
原创 最新推荐文章于 2025-07-04 09:00:41 发布 · 722 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库 #mysql关联查询

1. 基本操作

登录:

mysql -u root -p

输入密码，登陆数据库

创建数据库 study：

CREATE DATABASE study CHARACTER SET UTF8;

删除数据库：

DROP DATABASE 数据库名;

查看所有的数据库：

SHOW DATABASES;

使用数据库 study：

USE study;

创建数据表 news：

CREATE TABLE news (
	nid INT AUTO_INCREMENT,
	title VARCHAR(30) NOT NULL,
	content VARCHAR(300),
	CONSTRAINT pk_nid PRIMARY KEY(nid)
)ENGINE = INNODB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO news(title, content) VALUES('mysql study', 'this is test data');
INSERT INTO news(title, content) VALUES('mysql study', 'this is test data');
INSERT INTO news(title, content) VALUES('mysql study', 'this is test data');

主键设置了自增，所以插入数据时可以省略此字段

获得自增后的主键：

SELECT LAST_INSERT_ID();

删除数据：

DELETE FROM news WHERE nid = 2;

修改数据：

UPDATE news SET content='this is update test' WHERE nid=1;

2. 查询操作

数据表准备：

--
-- Table structure for table `dept`
--
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` INT(2) NOT NULL,
  `dname` VARCHAR(14) DEFAULT NULL,
  `loc` VARCHAR(13) DEFAULT NULL,
  PRIMARY KEY  (`deptno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `dept`
--
INSERT INTO `dept` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON'),(60,'HR','SY');

--
-- Table structure for table `emp`
--
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` INT(4) NOT NULL,
  `ename` VARCHAR(20) DEFAULT NULL,
  `job` VARCHAR(9) DEFAULT NULL,
  `mgr` INT(4) DEFAULT NULL,
  `hiredate` DATE DEFAULT NULL,
  `sal` DOUBLE(7,2) DEFAULT NULL,
  `comm` DOUBLE(7,2) DEFAULT NULL,
  `deptno` INT(2) DEFAULT NULL,
  PRIMARY KEY  (`empno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `emp`
--
INSERT INTO `emp` VALUES (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-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',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);

--
-- Table structure for table `salgrade`
--

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` INT(11) DEFAULT NULL,
  `losal` INT(11) DEFAULT NULL,
  `hisal` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `salgrade`
--
INSERT INTO `salgrade` VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

--
-- Table structure for table `bonus`
--
CREATE TABLE `bonus` (
  `ename` VARCHAR(20) DEFAULT NULL,
  `job` VARCHAR(9) DEFAULT NULL,
  `sal` DOUBLE(7,2) DEFAULT NULL,
  `comm` DOUBLE(7,2) DEFAULT NULL
) ENGINE = INNODB default charset utf8;

2.1 简单查询

最基本的查询，语法：

SELECT [DISTINCT] *|列[别名],列[别名],...
FROM 表名称 [别名];
  • 语句执行顺序：1. FROM 2. SELECT;
  • DISTINCT :去掉重复行数据；
  • * ：查询所有列，如果不想查询所有列，可以写上具体列名称；
  • 在拿到一个未知的数据库是,千万不要查询全部,不然...
  • 别名：与列名称以空格分开，将替代原列名称显示在结果的表头；
  • SELECT 子句中的“列”可以进行数学计算 + - * / %。

例一：

select empno 员工编号, ename 员工姓名, job 岗位, mgr 领导, hiredate 入职日期, sal*12 年薪, comm 奖金, deptno 部门编号 from emp;

练习

要求：查询所有雇员编号，雇员姓名， 每月总收入，所在部门。

select empno, ename, sal + ifnull(comm,0) from emp;
  • NULL 值和任何数据进行计算，结果都是 NULL；
  • IFNULL(expr1,expr2) 如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。

2.2 分页查询

数据库级别的分页查询，语法：

查询语句 LIMIT 开始行,长度;

 

  • 开始行索引从 0 开始。
  • LIMIT 子句是整个查询的最后一句.

2.3 限定查询

针对查询结果进行条件过滤，语法：

SELECT [DISTINCT] *|列[别名],列[别名],...
FROM 表名称 [别名]
[WHERE 条件];
  • 语句执行顺序：1. FROM 2. WHERE 3. SELECT
    WHERE子句中的条件可以是多条，该子句可以以下操作符：
    • 关系运算符：> >= < <= <> !=；
    • 范围运算符：BETWEEN ... AND ...；
      • 字段 BETWEEN 最小值 AND 最大值；
      • 注意：范围中包含最大值和最小值；
    • 逻辑运算符：AND（与）、 OR（或）、 NOT（非）；
    • 空判断运算符：IS NULL、IS NOT NULL；
      • 不能用逻辑运算符来进行空判断，比如“comm != null”，正确的是 “comm IS NOT NULL”；
      • 注意，对数据库来说，0 和 null 是不一样的概念；
    • 基数范围：IN、NOT IN；
      • 表示在或者不在某几个可选数值范围之中；
      • 字段 IN/NOT IN (数值, 数值,...)
      • 注意：在使用 NOT IN 时，如果基数中包含了 NULL ，则无论有没有满足要求的数据，都不会被显示，这是一种保护机制；
    • 模糊查询：LIKE
      • 字段 LIKE '匹配的关键字'；
      • _：表示匹配任意 一个 字符
      • %：表示匹配任意 0个，1个，或多个关键字；
      • 如果不使用任何通配符，则表示精确匹配。

相关练习

  1. 要求：查询出所有销售人员（SALESMAN）中工资高于 1200 的雇员信息。
SELECT * FROM emp WHERE job = 'salesman' AND sal > 1200;
  1. 要求：查询出工资在 1200 ~ 3000 之间的雇员信息。
SELECT * FROM emp WHERE sal BETWEEN 1200 AND 3000;
  1. 要求：查询出所有在 1981 年雇佣的雇员信息。
SELECT * FROM emp WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';
  1. 要求：查询所有领取佣金的雇员，即 comm 不为 null。
SELECT * FROM emp WHERE comm IS NOT NULL;
  1. 要求：查询出雇员编号为 7369,7566,7839,8899 的雇员。
SELECT * FROM emp WHERE empno IN (7369,7566,7839,8899);
  1. 要求：查询出姓名中第二个字母是'A'的雇员。
SELECT * FROM emp WHERE ename LIKE '_A%';

2.4 查询排序

对查询的结果进行排序，语法：

SELECT [DISTINCT] *|列[别名],列[别名],...
FROM 表名称 [别名]
[WHERE 条件]
[ORDER BY 字段[ASC|DESC],字段[ASC|DESC],...];
  • 语句执行顺序：1. FROM 2. WHERE 3. SELECT 4. ORDER BY；
  • 因为ORDER BY子句是整个查询的倒数第二句，所以可以使用在 SELECT 子句中定义的别名；
  • ASC 表示升序，是默认方式
  • DESC 表示降序。

相关练习

  1. 要求：查询出所有雇员的信息，结果按照工资由高到低排序，如果工资相同，则按照入职日期从早到晚排序。
SELECT * FROM emp ORDER BY sal DESC, hiredate;
  1. 要求：查询出所有雇员的信息，按照年薪排序。
SELECT * FROM emp ORDER BY sal*12 ;

2.5 思考题

  1. 要求：查询部门 30 中的所有员工。
select * from emp where deptno = 30;
  1. 要求：查询所有办事员（CLERK）的姓名，编号，部门编号。
select ename, empno,job, deptno from emp where job = 'CLERK';
  1. 要求：查询出佣金高于工资 60 % 的雇员。
select * from emp where comm > sal*0.6
  1. 要求：查询出部门 10 中所有的经理 和 部门 20 中所有的办事员。
select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'CLERK');
  1. 要求：查询出部门 10 中所有的经理 、 部门 20 中所有的办事员 、 既不是经理又不是办事员，但其工资大于等于 2000 的雇员信息。
select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'CLERK') or (job != 'manager' and job != 'clerk' and sal > 2000);
  1. 要求：查询出收入组成中有佣金的员工的不同工作。
select distinct job from emp where comm is not null;
  1. 要求：查询出收入组成中无佣金 或者佣金收入低于 100 的雇员信息。
select * from emp where comm is null or comm < 100;
  1. 要求：查询姓名中没有 “R” 的员工姓名。
select ename from emp where ename not like '%R%';
  1. 要求：查询姓名中有 “A” 的雇员信息，结果按照工资由高到低排序，如果工资相同，则按照入职早晚排序，如果入职日期相同，则按照岗位排序。
select * from emp where ename like '%A%' order by sal desc,hiredate,job;

2.6 多表查询

具有关联字段的表，以关联字段关系判断进行的查询，其中内连接查询语法：

SELECT [DISTINCT] *|列[别名],列[别名],...
FROM 数据表1 [别名], 数据表2 [别名],...
WHERE 数据表1.关联字段 = 数据表2.关联字段...
[ORDER BY 字段[ASC|DESC],字段[ASC|DESC],...];
或者
SELECT [DISTINCT] *|列[别名],列[别名],...
FROM 数据表1 [别名] [INNER] JOIN 数据表2 [别名] ON 关联条件
[ORDER BY 字段[ASC|DESC],字段[ASC|DESC],...];
  • 如果指定关联表，但是并不在 WHERE 子句中指定关联字段，则查询的结果将会是两个数据表的"笛卡儿积"，其实指定了关联字段，这个"笛卡儿积"依然存在，只是被过滤掉了。所以，在数据量比较大的情况下，多表关联查询的效率是比较差的。

相关练习

  1. 要求：查询每个雇员的编号，姓名，职位,工资，部门名称，部门位置。
SELECT empno, ename, job, sal, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno; 

-- 或者 

SELECT empno, ename, job, sal, dname, loc FROM emp INNER JOIN dept ON dept.deptno=emp.deptno;
  1. 要求：查询每个雇员的编号，姓名，职位，工资，入职日期，工资等级。
SELECT empno, ename, job, sal, hiredate, grade FROM emp, salgrade WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal; 

-- 或者

 SELECT empno, ename, job, sal, hiredate, grade FROM emp INNER JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

多表查询的连接方式

多表查询的连接方式有两种：

  • 内连接：默认为等值连接、上面的案例就是内连接；

在使用内连接进行多表查询的时候，如果其中一条数据，在关联表中没有符合条件的对应值，这时本条数据就不会被显示。

案例:查询每个雇员的编号,姓名,职位,领导姓名.
使用内连接:

SELECT e.empno, e.ename, e.job, m.ename FROM emp e, emp m WHERE e.mgr = m.empno; 

-- 或者

SELECT e.empno, e.ename, e.job, m.ename FROM emp e inner join emp m on e.mgr = m.empno;

可以看到,编号 7839 的雇员信息没有出现在结果中,因为他没有领导(老大).

  • 外连接：分为 左外连接、右外连接、全连接。
    外连接语法：
SELECT [DISTINCT] *|列[别名],列[别名],...
FROM 数据表1 [别名] LEFT|RIGHT|FULL [OUTER] JOIN 数据表2 [别名] ON 关联条件
[ORDER BY 字段[ASC|DESC],字段[ASC|DESC],...];

注意：全外连接的 FULL，MySQL 暂不支持，需要使用 查询结果连接符 UNION，UNION 操作会将两个 SELECT 语句的查询结果连接起来，并且会去掉重复数据。

用外连接改写上面的案例：

SELECT e.empno, e.ename, e.job, m.ename FROM emp e left outer join emp m on e.mgr = m.empno;

为了更清楚的说明问题，新建一张 myemp 的表，减少 emp 表中的字段：

CREATE TABLE myemp
( `empno` INT(4) NOT NULL AUTO_INCREMENT, 
`ename` VARCHAR(20) DEFAULT NULL, 
`deptno` INT(2) DEFAULT NULL,
 CONSTRAINT pk_empno PRIMARY KEY (`empno`) 
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入测试数据：

INSERT INTO myemp(ename, deptno) VALUES('SMITH', 10); 
INSERT INTO myemp(ename, deptno) VALUES('ALLEN', 10); 
INSERT INTO myemp(ename, deptno) VALUES('WARD', 10); 
INSERT INTO myemp(ename, deptno) VALUES('JONES', 20); 
INSERT INTO myemp(ename, deptno) VALUES('MARTIN', 20); 
INTO myemp(ename, deptno) VALUES('BLAKE', 30); 
INSERT INTO myemp(ename, deptno) VALUES('CLARK', 40); 
INSERT INTO myemp(ename, deptno) VALUES('SCOTT', 50);

先观察内连接（等值连接）：

SELECT * FROM myemp m INNER JOIN dept d ON m.`deptno`=d.`deptno`;

查询结果：

mysql> SELECT * FROM myemp e INNER JOIN dept d ON e.`deptno`=d.`deptno`;
+-------+--------+--------+--------+------------+----------+
| empno | ename  | deptno | deptno | dname      | loc      |
+-------+--------+--------+--------+------------+----------+
|     1 | SMITH  |     10 |     10 | ACCOUNTING | NEW YORK |
|     2 | ALLEN  |     10 |     10 | ACCOUNTING | NEW YORK |
|     3 | WARD   |     10 |     10 | ACCOUNTING | NEW YORK |
|     4 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
|     5 | MARTIN |     20 |     20 | RESEARCH   | DALLAS   |
|     6 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
|     7 | CLARK  |     40 |     40 | OPERATIONS | BOSTON   |
+-------+--------+--------+--------+------------+----------+
7 rows in set (0.00 sec)

可以发现，结果是两个表关联字段的交集：

左外连接：

SELECT * FROM myemp m LEFT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;

查询结果：

mysql> SELECT * FROM myemp m LEFT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;
+-------+--------+--------+--------+------------+----------+
| empno | ename  | deptno | deptno | dname      | loc      |
+-------+--------+--------+--------+------------+----------+
|     1 | SMITH  |     10 |     10 | ACCOUNTING | NEW YORK |
|     2 | ALLEN  |     10 |     10 | ACCOUNTING | NEW YORK |
|     3 | WARD   |     10 |     10 | ACCOUNTING | NEW YORK |
|     4 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
|     5 | MARTIN |     20 |     20 | RESEARCH   | DALLAS   |
|     6 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
|     7 | CLARK  |     40 |     40 | OPERATIONS | BOSTON   |
|     8 | SCOTT  |     50 |   NULL | NULL       | NULL     |
+-------+--------+--------+--------+------------+----------+
8 rows in set (0.00 sec)

可以发现，结果是左边的表是完整的，右边的表只保留交集部分：


右外连接：

SELECT * FROM myemp m RIGHT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;

查询结果：

mysql> SELECT * FROM myemp m RIGHT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;
+-------+--------+--------+--------+------------+----------+
| empno | ename  | deptno | deptno | dname      | loc      |
+-------+--------+--------+--------+------------+----------+
|     1 | SMITH  |     10 |     10 | ACCOUNTING | NEW YORK |
|     2 | ALLEN  |     10 |     10 | ACCOUNTING | NEW YORK |
|     3 | WARD   |     10 |     10 | ACCOUNTING | NEW YORK |
|     4 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
|     5 | MARTIN |     20 |     20 | RESEARCH   | DALLAS   |
|     6 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
|     7 | CLARK  |     40 |     40 | OPERATIONS | BOSTON   |
|  NULL | NULL   |   NULL |     60 | HR         | SY       |
+-------+--------+--------+--------+------------+----------+
8 rows in set (0.00 sec)

可以发现，结果是右边的表是完整的，左边的表只保留交集部分：

全外连接：

SELECT * FROM myemp m LEFT OUTER JOIN dept d ON m.`deptno`=d.`deptno` 
UNION 
SELECT * FROM myemp m RIGHT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;

查询结果：

mysql> SELECT * FROM myemp m LEFT OUTER JOIN dept d ON m.`deptno`=d.`deptno`
    -> UNION
    -> SELECT * FROM myemp m RIGHT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;
+-------+--------+--------+--------+------------+----------+
| empno | ename  | deptno | deptno | dname      | loc      |
+-------+--------+--------+--------+------------+----------+
|     1 | SMITH  |     10 |     10 | ACCOUNTING | NEW YORK |
|     2 | ALLEN  |     10 |     10 | ACCOUNTING | NEW YORK |
|     3 | WARD   |     10 |     10 | ACCOUNTING | NEW YORK |
|     4 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
|     5 | MARTIN |     20 |     20 | RESEARCH   | DALLAS   |
|     6 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
|     7 | CLARK  |     40 |     40 | OPERATIONS | BOSTON   |
|     8 | SCOTT  |     50 |   NULL | NULL       | NULL     |
|  NULL | NULL   |   NULL |     60 | HR         | SY       |
+-------+--------+--------+--------+------------+----------+
9 rows in set (0.00 sec)

可以发现，全外连接中，两个表都保证了完整性：

总结：
如果想要保证 JOIN 左边的表的完整性，使用 LEFT OUTER JOIN 连接；
如果想要保证 JOIN 右边的表的完整性，使用 RIGHT OUTER JOIN 连接；
如果想要保证 JOIN 两边的表的完整性，使用 UNION 连接左外连接查询和右外连接查询；
如果对表的完整性没有要求，或者两张表的关联字段取值相等，则使用 INNER JOIN。

三个表的关联查询

案例：要求：查询雇员编号，雇员姓名，雇员职位，所在部门名称，工资等级。

SELECT e.`empno`, e.`ename`, e.`job`, d.`dname`, s.grade
FROM (emp e LEFT OUTER JOIN dept d ON e.`deptno` = d.`deptno`) 
	LEFT OUTER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.hisal;

2.7 分组统计查询

常见的统计函数

  • COUNT() ： 计数，统计记录数
SELECT COUNT(*) FROM emp;
  • SUM() ： 求和
SELECT SUM(sal) 总工资 FROM emp;
  • AVG() ： 求平均值
SELECT AVG(sal) 平均工资 FROM emp;
  • MAX() ： 求最大值
SELECT MAX(sal) 最高工资 FROM emp;
  • MIN() ： 求最小值
SELECT MIN(sal) 最高工资 FROM emp;

注意，COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的区别？

  • COUNT(*)：统计除表中实际的数据量；
  • COUNT(字段)：不会统计 NULL 值字段；
  • COUNT(DISTINCT 字段)：消除重复数据后的统计结果。

分组统计

需要注意的是，有相同特征的事物才可以进行分组，对于数据库来说，有重复数据才可以进行分组。
分组统计查询的语法如下：

SELECT [DISTINCT] *|列[别名],列[别名],... | 统计函数
FROM 数据表 [别名], 数据表 [别名],...
[WHERE 条件]
[GROUP BY 分组字段,分组字段,...]
[HAVING 分组后过滤条件]
[ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]
[LIMIT 开始行, 显示行数]
  • 语句执行顺序：1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6.ORDER BY 7. LIMIT
  • 统计查询在Oracle数据库中的限制：
    • 统计函数单独使用时（没有GROUP BY 子句），查询语句中只能够出现统计函数，不能够出现其他字段；
    • 使用分组统计查询时（有GROUP BY 子句），SELECT 子句中只能够出现统计函数和分组字段，其他任何字段都不允许出现；

相关练习

  1. 要求：查询出每个部门的名称，部门人数，平均工资。
SELECT d.`dname`, COUNT(e.`empno`) 部门人数, AVG(e.`sal`) 平均工资
FROM emp e RIGHT OUTER JOIN dept d ON e.`deptno` = d.`deptno`
GROUP BY d.`dname`;
  1. 要求：查询出每个部门的编号，名称，位置，部门人数，平均工资。
SELECT d.`deptno`, d.`dname`, d.`loc`, COUNT(e.`empno`) 部门人数, AVG(e.`sal`) 平均工资
FROM emp e RIGHT OUTER JOIN dept d ON e.`deptno` = d.`deptno`
GROUP BY d.`deptno`, d.`dname`, d.`loc`;
  1. 要求：按照职位分组，统计每个职位的平均工资，要求显示的是平均工资高于2000的职位信息。
SELECT job, AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) > 2000;

注意 WHERE 和 HAVING 的区别：
WHERE 子句在分组之前执行，所以数据先经过 WHERE 子句筛选后才进行的分组，且 WHERE 子句不能使用统计函数。
HAVING 子句必须结合 GROUP BY 子句一起出现，是分组后的过滤，可以使用统计函数。
4. 要求：统计公司每个工资等级的人数，平均工资。

SELECT s.`grade`, COUNT(e.`empno`) 人数, AVG(e.`sal`) 平均工资
FROM emp e INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
GROUP BY s.`grade`;

2.8 子查询

子查询是复杂查询中最为重要的。所谓子查询，就是在一条查询语句中嵌套若干条查询。子查询可以改善关联查询的性能。

子查询可以嵌套在 SELECT、FROM、WHERE、HAVING 子句中，嵌套的语句用 () 包裹起来。

根据子查询语句返回的结果，其应该出现的位置及用法如下：

  • 子查询返回的结果是 单行单列，也就是说，返回的结果就是一个数据，那么它通常用于条件判断，所以经常出现在 WHERE、HAVING 子句中，这是直接使用它做条件判断即可；

  • 子查询返回的结果是 多行单列，也就是说，返回的结果是一组数据，那么它通常出现在 WHERE 子句中，需要配合如下符号使用：

    • IN ： 在指定的范围内，可以配合 NOT 使用，表示不在范围内。需要注意的是， NOT IN 的范围中不能包含 NULL 值；
    • ANY ： 指“任何”的意思，配合 >、=、< 使用；
      • =ANY : 功能和 IN 一样
       SELECT * FROM emp
 WHERE sal = ANY(SELECT sal FROM emp WHERE job = 'manager')
      • >ANY : 比子查询结果中的最小值大
       SELECT * FROM emp
 WHERE sal >ANY(SELECT sal FROM emp WHERE job = 'manager')
      • <ANY : 比子查询结果中的最大值小
       SELECT * FROM emp
 WHERE sal >ANY(SELECT sal FROM emp WHERE job = 'manager')
    • ALL ： 指“所有”的意思，同样配合 >、=、< 使用；
      • >ALL : 比子查询结果中的最大值大
      • <ALL : 比子查询结果中的最小值小

  • 子查询的结果返回多行多列，即，返回的结果是一个数据表，那么它通常出现在 FROM 子句中，作为一个虚拟表使用，最好配合别名使用。

相关练习

  1. 要求：查询最早入职的雇员信息。
SELECT * 
FROM emp 
WHERE hiredate = (
	SELECT MIN(hiredate) 
	FROM emp);
  1. 要求：查询与 Scott 从事同一工作，且工资相同的雇员信息。
SELECT * 
FROM emp
WHERE (job, sal) = (
	SELECT job, sal 
	FROM emp 
	WHERE ename = 'scott') AND ename <> 'scott';
  1. 要求：查询出平均工资高于公司平均工资的职位名称，职位人数，平均工资。
SELECT job, COUNT(empno), AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp);

2.9 复杂查询综合练习

  1. 要求：列出至少有一个员工的部门信息。
SELECT d.`deptno`, d.`dname`, d.`loc`, COUNT(e.`empno`)
FROM dept d LEFT OUTER JOIN emp e ON d.`deptno` = e.`deptno`
GROUP BY d.`deptno`
HAVING COUNT(e.`empno`) > 0;
  1. 要求：查询出入职日期早于直接上级的所有员工的编号，姓名，部门名称，上级姓名。
SELECT e.`empno`, e.`ename`,e.`hiredate`, d.`dname`, m.`ename`, m.`hiredate`
FROM (emp e LEFT OUTER JOIN dept d ON e.`deptno` = d.`deptno`)
	LEFT OUTER JOIN emp m ON e.`mgr` = m.`empno`
WHERE e.`hiredate` < m.`hiredate`;
  1. 要求：查询出工资比 SMITH 高的所有员工信息。
SELECT * 
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'smith');
  1. 要求：查询出所有办事员（CLERK）的姓名及其所在部门名称，部门人数。
SELECT e.ename, temp.tname, temp.count
FROM emp e LEFT OUTER JOIN (SELECT d.`deptno` tno, d.`dname` tname, COUNT(e.`empno`) COUNT
	     FROM dept d LEFT OUTER JOIN emp e ON d.`deptno` = e.`deptno`
	     GROUP BY d.`deptno`) temp ON e.`deptno` = temp.tno
WHERE e.`job` = 'CLERK';
  1. 要求：查询出工资大于 1500 的各种工作，以及从事此工作的雇员人数。
SELECT job, COUNT(empno)
FROM emp
GROUP BY job
HAVING MIN(sal) > 1500;
  1. 要求：查询出在 SALES 部门工作的员工姓名，工资。
SELECT e.ename, e.sal
FROM emp e LEFT OUTER JOIN dept d ON e.`deptno` = d.`deptno`
WHERE d.`dname` = 'SALES';
  1. 要求：查询出工资高于公司平均工资的所有雇员姓名，所在部门，上级领导，工资等级，与具备此工资等级的雇员人数。
    分析步骤，首先查询出工资高于公司平均工资的所有雇员姓名，所在部门，上级领导，工资等级
SELECT e.`ename`, d.`dname`, m.`ename`, s.`grade`
FROM (emp e LEFT OUTER JOIN dept d ON e.`deptno`=d.`deptno`) 
	INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
		LEFT OUTER JOIN emp m ON e.`mgr` = m.`empno`
WHERE e.`sal` > (SELECT AVG(sal) FROM emp);

然后查询出每个工资等级的人数：

SELECT s.`grade`, COUNT(e.`empno`)
FROM emp e INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
GROUP BY s.`grade`;

最后在第一个查询中再关联第二个查询的结果：

SELECT e.`ename`, d.`dname`, m.`ename`, s.`grade`, temp.count
FROM (emp e LEFT OUTER JOIN dept d ON e.`deptno`=d.`deptno`) 
	INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
		LEFT OUTER JOIN emp m ON e.`mgr` = m.`empno`
			INNER JOIN (SELECT s.`grade` tgrade, COUNT(e.`empno`) COUNT
				    FROM emp e INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
			            GROUP BY s.`grade`) temp ON s.`grade` = temp.tgrade
WHERE e.`sal` > (SELECT AVG(sal) FROM emp);
  1. 要求：查询出各种工作的最低工资及从事此工作的雇员姓名。
SELECT temp.min, e.`ename`
FROM emp e INNER JOIN (SELECT job, MIN(sal) MIN
		       FROM emp
		       GROUP BY job) temp
WHERE e.`job` = temp.job AND e.`sal` = temp.min;
  1. 要求：查询出各个部门办事员（CLERK）的最低工资。
SELECT d.`dname`, MIN(e.`sal`)
FROM emp e LEFT OUTER JOIN dept d ON e.`deptno`=d.`deptno`
WHERE e.`job` = 'clerk'
GROUP BY d.`deptno`;
  1. 要求：查询出部门名称中有 s 的部门的雇员工资合计，及部门人数。
SELECT d.`dname`,SUM(e.`sal`), COUNT(e.`empno`)
FROM dept d LEFT OUTER JOIN emp e ON d.`deptno`=e.`deptno`
WHERE d.`dname` LIKE '%s%'
GROUP BY d.`dname`;
（收藏）MySQL从入门到进阶这一篇就够了
weixin_42208959的博客
08-10 4819
https://blog.youkuaiyun.com/qq_38490457/article/details/107640904?utm_source=app
MYSQL从入门到精通---进阶篇
m0_56604311的博客
04-03 967
学习mysql笔记
MySQL从入门到进阶，看这一篇文章就够了
MySQL 安装教程及使用入门
01-11 462
现在Java开发人员一般都需要会主流数据库。开发人员需要对Mysql、Oracle、SqlServer这三个常用的熟悉了解。本次着重分享Mysql数据库的知识点，感兴趣的同学可以先收藏，再观看！ 如果需要Oracle、SqlServer数据库知识点的分享，可以在文章底部留言告诉我。 文章目录： 请简洁描述 MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别...
MySQL进阶篇(从0到1细到没话说)
最新发布
拧之的博客
07-04 1224
家可能没有听说过存储引擎，但是一定听过引擎这个词，引擎就是发动机，是一个机器的核心组件。比如，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎一样。而对于存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是。
mysql数据库入门、进阶和提升
weixin_34186931的博客
09-26 139
一、SQL概述什么是数据库？数据库就是以有组织的方式存储的数据集合，可以简单的理解为一个大储物柜。而我们常常使用的mysql，sql_server都是DBMS,就是管理数据库的软件。什么是表?用来存储数据的结构化文件，一个数据库中不能有重复的表名。什么是列？构成表的基本单位，每一个列均有自己的数据类型。什么是行？数据库中的数据是按照行存储的，一行称为一条记录。什么是主键？能...
MYSQL入门与进阶（完结）
你好，我是小新，很高兴认识你！
08-29 822
一文带你了解MYSQL的事务和事务处理机制
MySQL入门+进阶资源合集.pdf
07-12
### MySQL入门+进阶资源合集 #### 一、MySQL基础知识与入门 ##### 1.1 MySQL基础概念 - **数据库(Database)**：是用于存储数据的逻辑容器，它能够高效地管理和组织数据。 - **表(Table)**：是数据库中用来存储...
"MySql基础入门到进阶：基本操作、数据库和数据表操作详解
MySql基础入门到进阶 MySql是一种开源的关系型数据库管理系统，广泛应用于各种Web应用程序的开发和数据存储。本文将介绍MySql的基础入门知识以及一些进阶的操作。 1. 基本操作 首先，我们需要使用以下命令登录到...
黑马Mysql教程入门+进阶PDF （超详细，覆盖面全）
03-29
本教程旨在帮助初学者快速掌握 MySQL 的基础知识，并深入了解一些进阶主题如存储过程、索引优化等。 首先，我们将介绍 MySQL 的基础概念，包括数据库、表、列等，帮助读者了解如何创建数据库和表格，并学习基本的 ...
MySQL从入门到进阶运维篇学习笔记.zip
02-22
本学习笔记将带你从零基础开始，逐步深入到MySQL的高级运维层面。以下是你将会学到的关键知识点： 1. **MySQL安装与配置**：了解如何在不同操作系统上安装MySQL，包括Windows、Linux和macOS。设置用户权限，配置...
MYSQL基础入门pdf
10-25
MYSQL 基础入门知识，可以学习下 数据库概述 •MySQL基本操作 •MySQL索引基础 •MySQL高级特性
mysql数据库入门、进阶和提升（续一）
weixin_34175509的博客
09-26 127
DML数据操作语言三、检索数据select 单表查询 联结查询（join） 联合查询（union）检索表数据，关键点是至少要给出两条信息————选择什么，从哪里选择。1、检索多个列，需要使用逗号分隔，最后一个列名不用逗号。*表示所有列。mysql> select user_name,password,password_salt from user wh...
MySQL入门到进阶，学习MySQL，此篇足矣
weixin_50539646的博客
11-17 664
MySQL从入门到进阶，涵盖了事务，索引，SQL优化，锁的使用，MVCC等等，认真看完必定会有大收获。文章篇幅较长，知识涵盖量很广且陈述详细，建议多看几次，可以加深对MySQL的理解。
MySQL从基础到进阶
weixin_41622302的博客
04-02 208
零基础入门MySQL 一、安装MySQL后的配置 1、通过cmd操作MySQL 找到windows里的环境变量配置窗口，在PATH处添加mysql的安装路径，要一直到bin，如：D:\mysql-8.0.15-winx64\bin； 2、通过可视化界面操作MySQL 可以安装MySQLworkbench 二、MySQL基础使用方法 1、启用命令行 打开cmd输入: mysql -uroot -p ...
MySQL的基础操作及进阶（巨详细）
weixin_57176230的博客
02-25 3084
本实例使用的MySQL 8.0.23版本 目录 基础操作 进阶操作 本地数据的导入导出 查询进阶操作 触发器设置 事件操作 基础操作 create table if not exists db1.t8( id int unsigned , age int unsigned, address varchar(30), gender enum('男','女') default '女', primary key(id) )engine =inn.
【超级详细的Mysql入门到进阶】
烟溪彭于晏的博客
10-13 570
【MySQL】SQL的分类 DDL、DML、DQL、DCLDDL(Data Definition Language，数据定义语言)主要用于，这种结构包括数据库，表、视图、索引、同义词、聚簇等。createdropalterTRUNCATEDML(Data Manipulation Language，数据操纵语言)主要用于对数据库对象中包含的insertdeleteupdataDQL(Data Query Language，数据查询语言)主要用于数据库当中的数据seletefromwhere。
MySQL数据库入门到精通(进阶篇)
m0_63737415的博客
07-20 358
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在mysql5.5之后，InnoDB是默认的mysql存储引擎。聚集索引将数据存储与索引存放到了一块，索引结构的叶子节点保存了行数据。二级索引将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。Memory引擎的表数据时存储在内存中的，由于受到硬件问题，全文索引全文索引查找的是文本中的关键字，而不是比较索引中的值。通过索引列随数据进行排序，降低数据排序的成本，降低CPU的消耗。xxx.MYD存储数据。...
mysql进阶
从此踏上一条不归路
02-21 1939
mysql进阶1.mysql配置文件2. mysql数据库备份2.1 数据库常用备份方案2.2 mysql备份工具mysqldump2.3 mysql数据恢复2.4 mysql能用localhost连接，但无法使用127.0.0.1连接 1.mysql配置文件 mysql的配置文件为/etc/my.cnf 配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效 //不需要输入密码和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值