집 > 기사 > 데이터 베이스 > MySQL Essence One: DQL 데이터 쿼리 문

MySQL Essence One: DQL 데이터 쿼리 문

coldplay.xixi앞으로: 2021-02-22 09:10:122548검색

추천 무료 학습: mysql 비디오 튜토리얼

1. 기본 쿼리
2. 조건부 쿼리
3. 정렬 쿼리
4 . 공통 함수
5. 그룹 쿼리
6. 조인 쿼리
7. 서브 쿼리
8. 페이지네이션 쿼리
9. 유니온 쿼리

샘플 데이터 준비

쿼리문 실험 , 먼저 해당 데이터를 쿼리용 샘플로 준비합니다.

SQLyog를 사용하여 SQL 스크립트를 가져오면 준비된 샘플 테이블을 볼 수 있습니다.
MySQL Essence One: DQL 데이터 쿼리 문

이 샘플은 아래 다국적 기업의 직원이 관리하는 4개의 테이블입니다. 그림은 각 테이블의 각 필드를 소개합니다:
MySQL Essence One: DQL 데이터 쿼리 문

1. 기본 쿼리

구문: 테이블 이름에서 쿼리 목록 선택 select 查询列表 from 表名;

特点：
查询列表可以是:表中的字段、常量值、表达式、函数。
查询的结果是一个虚拟的表格。

执行顺序：from > select (先找到表，再开始查询)

注意:`是着重号，当某张表中的字段与关键字冲突时，可以在该字段两边加上着重号，以标明其是一个字段，而不是关键字(如`name`)。

【基础查询】# 选中样本库USER myemployees;# 1.查询表中的单个字段SELECT last_name FROM employees;# 2.查询表中的多个字段SELECT last_name,salary,email FROM employees;# 3.查询表中所有的字段SELECT * FROM employees;# 4. 查询常量值SELECT 'Tom';# 5.查询表达式SELECT 7%6;# 6. 查询函数SELECT VERSION();# 7.起别名(mysql中建议将起别名使用双引号引起来"别名")/*
优点:便于理解;连接查询时,如果要查询的字段有重名情况,可以使用起别名来区分
*/# 方式一,使用asSELECT 7%6 AS 结果;SELECT last_name AS 姓,first_name AS 名 FROM employees;# 方式二,使用空格SELECT 7%6 结果;SELECT last_name 姓,first_name 名 FROM employees;# 查询员工号为176的员工的姓名、部门、nianxinSELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees; # 8.去重SELECT DISTINCT department_id FROM employees;# 9.+号的作用/*
select 13+21; 两个操作数都是数值型,自动做加法运算
其中一个为字符型,则将字符型转换为数值型
	select '13'+1; 	   转换成功,做加法运算
	select 'hello'+1;  转换失败,将字符型转换为0
select null+10; 只要其中一方为null,结果就为null
	补充ifnull函数:SELECT IFNULL(commission_pct,0) AS 奖金率,commission_pct FROM employees;
mysql中用来拼接的不是+号,而是concat函数
*/SELECT CONCAT(last_name,first_name) AS "姓名" FROM employees;

基础查询总结	说明
1.查询表中的单个字段	`select 字段1 from 表;`
2.查询表中的多个字段	`select 字段1,字段2,...字段n from 表;`
3.查询表中的所有字段	`select * from 表;`
4.查询常量值	`select '常量值;'`
5.查询表达式	`select 数值1 表达式数值2;`
6.查询函数	`select f();`
7.起别名	`as`
8.去重	`distinct`
9.拼接使用concat函数,而不是"+"	`concat(last_name,first_name)`

学完了基础查询，尝试完成下面的练习题

答案：
1.正确
2.正确
3.应在英文状态下使用引号
4.DESC departments;；SELECT * FROM departments;
5.SELECT CONCAT(first_name,',',last_name,',',IFNULL(email,0)) AS "out_put" FROM employees;

二、条件查询

语法：select 查询列表 from 表名 where 筛选条件;

执行顺序：from > where > select (先定位到表,然后开始筛选,最后走查询)

分类:

(1)按条件运算符筛选

条件运算符有： >  =   )

(2)按逻辑表达式筛选

支持&& || !,但推荐使用and or not 
逻辑表达式作用：用于连接条件表达式
&&或and: 两个都为true,结果为true,反之为false
||或or : 只要有一个条件为true,结果即为true,反之为false
!或not : 取反

(3)模糊查询

模糊查关键字：like、between and、in、is null
(1)like关键字
	可以判断字符型或数值型
	  like一般和通配符搭配使用,通配符有
		%:代表任意多个字符,包含0个
		_:代表任意单个字符
(2)between...and关键字
	  可以提高语句简洁度
	  包含临界值
	  两个临界值不能调换顺序
(3)in关键字
	  可以提高语句简洁度
	  in列表的值类型必须一致
(4)is null
	  取反是 is not null

【条件查询】(1)按条件运算符筛选# 1.查询工资>12000的员工SELECT * FROM employees WHERE salary > 12000 ;# 2.查询部门编号不等于90的员工名和部门编号SELECT department_name, department_id FROM departments WHERE department_id90;---------------------------------------------------------------------------------------------------------------------(2)按逻辑表达式筛选# 1.查询工资在10000到20000之间的员工名、工资以及奖金率SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary=90 AND department_id15000;---------------------------------------------------------------------------------------------------------------------(3)模糊查询# (1)like关键字# 1.查询员工名中包含字符a的员工的信息SELECT * FROM employees WHERE last_name LIKE '%a%';# 2.查询员工名中第三个字符为n,第五个字符为l的员工名和工资SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';# 3.查询员工名中第二个字符为_的员工名(转义)SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';# (2)between...and关键字# 1.查询员工编号在100到120的员工信息SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;# (3)in关键字# 1.查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的员工名和工种编号SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRES');# (4)is null# 1.查询没有奖金的员工名和奖金率SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;# is null仅仅可以用来判断null值;安全等于既可以用来判断null值,又可以用来判断普通值# is null的可读性高于,建议使用is nullSELECT last_name,commission_pct FROM employees WHERE commission_pct  NULL;

특징: 쿼리 목록은 다음과 같습니다. 테이블의 값, 표현식, 함수. 쿼리 결과는 가상 테이블입니다.

条件查询总结	说明
(1)按条件运算符筛选	`> = )`
(2)按逻辑表达式筛选	`&& \|\| !或and or not`
(3)模糊查询	`关键字:like、between...and、in、is null`

🎜실행 순서: from > select(먼저 테이블을 찾은 다음 쿼리 시작)🎜

【排序查询】# 1.查询员工信息,要求工资从高到低排序SELECT * FROM employees ORDER BY salary DESC;# 2.查询部门编号>=90的员工信息,按入职时间的先后进行排序【添加筛选条件】SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;# 3.按照年薪的高低显示员工的信息和年薪【添加表达式排序】SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC; # ORDER BY后支持别名# 4.按照姓名的长度,显示员工的姓名和工资【按函数排序】SELECT LENGTH(last_name) AS 字节长度, last_name,salary FROM employees ORDER BY 字节长度 DESC;# 5.查询员工信息,先按工资升序,再按员工编号降序SELECT * FROM employees ORDER BY salary ASC ,employee_id DESC;

【单行函数】# (1)字符函数-[参数类型为字符型]# 1.length 获取参数值的字节个数SELECT LENGTH('john');SELECT LENGTH('张三丰');SHOW VARIABLES LIKE '%char%' # 查看字符集# 2.concat拼接字符串SELECT CONCAT(last_name,'_',first_name) 姓名 from employees;# 3.upper、lower 大小写转换SELECT UPPER('tom');SELECT LOWER('TOM')# 将姓变大写,名变小写,然后拼接SELECT CONCAT(UPPER(last_name),LOWER(first_name))姓名 FROM employees;# 4.substr 拼接函数# mysql中的索引从1开始SELECT SUBSTR('若负平生意,何名作莫愁',7) AS out_put;SELECT SUBSTR('若负平生意,何名作莫愁',1,3) AS out_put;# 案例:姓名中首字符大写,其他字符小写,用_拼接并显示出来SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) oup_put FROM employees; # 5.instr 字符查找函数# 返回子串在主串中的起始索引,没有返回零SELECT INSTR('凡尘阿凉','阿凉') AS out_put;# 6.trim 清除空格函数# 将字符两边的空格移除SELECT LENGTH(TRIM('    凡尘    ')) AS out_put;SELECT TRIM('a' FROM  'aaaaaa凡aaa尘aaaa') AS out_put;# 7.lpad 左填充函数# 用指定的字符实现左填充指定长度SELECT LPAD('凡尘',10,'*') AS out_put;# 8.rpad 右填充函数# 用指定的字符实现右填充指定长度SELECT RPAD('凡尘',10,'*') AS out_put;# 9.replace 替换函数SELECT REPLACE('我的偶像是鲁迅','鲁迅','周冬雨') AS oup_put;---------------------------------------------------------------------------------------------------------# (2)数学函数-[参数类型为数值]# 1.round 四舍五入函数SELECT ROUND(1.65);SELECT ROUND(1.567,2);# 2.ceil 向上取整函数# 返回>=该参数的最小整数SELECT CEIL(1.00);# 3.floor 向下取整函数# 返回5,'大于','小于');SELECT last_name,commission_pct, IF(commission_pct IS NULL,'没奖金','有奖金') AS out_put FROM employees;# 2.MySQL Essence One: DQL 데이터 쿼리 문函数/*
方式一：类似于Java中的switch-MySQL Essence One: DQL 데이터 쿼리 문：
案例:查询员工工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
*/SELECT salary 原始工资,department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS 新工资FROM employees;/*
方式二：类似于Java中的多重if:
案例:查询员工的工资情况
工资>20000,显示A级别
工资>15000,显示B级别
工资>10000,显示C级别
否则,显示D级别
*/SELECT salary,CASEWHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别FROM employees;

🎜

기본 쿼리 요약 지침

🎜1. 테이블의 단일 필드를 쿼리합니다🎜🎜 테이블에서 필드 1을 선택합니다. 🎜🎜<tr> <td>🎜2. 테이블의 여러 필드를 쿼리합니다🎜🎜</td> <td> <code>테이블에서 필드 1, 필드 2,...필드 n을 선택합니다.🎜🎜 🎜3. 테이블의 모든 필드를 쿼리합니다🎜🎜 테이블에서 *를 선택합니다.🎜🎜

🎜4. 상수 값을 쿼리합니다. '상수 값 선택'🎜🎜

🎜5. 쿼리 표현식🎜🎜 값 1 표현식 값 2 선택;🎜🎜

🎜6. 쿼리 기능 🎜🎜 f() 선택;🎜🎜

🎜7. 별칭 🎜🎜 as code >🎜🎜<tr> <td>🎜8. 중복 제거🎜🎜</td> <td> <code>고유🎜🎜

🎜9. "+" 대신 연결 기능을 사용하세요🎜🎜 concat(last_name,first_name)🎜🎜🎜🎜🎜🎜기본 쿼리를 학습한 후 다음 연습을 완료해 보세요🎜 여기에 이미지 설명 삽입

🎜🎜답변: 🎜 1.올바름🎜 2.올바름 code >🎜 3.<code>따옴표는 영어로 사용해야 합니다.🎜 4.DESC Departments;;SELECT * FROM Departments;🎜 5. SELECT CONCAT(first_name,',',last_name,',',IFNULL(email,0)) AS "out_put" FROM 직원;🎜🎜🎜🎜🎜2. 조건부 쿼리🎜🎜🎜 구문: 필터 조건이 있는 테이블 이름에서 쿼리 목록 선택;🎜🎜실행 순서: from > 선택(먼저 테이블을 찾은 다음 필터링을 시작한 다음 마지막으로 쿼리)🎜🎜 :🎜🎜(1) 조건부 연산자로 필터링🎜

【分组函数】/*
      SUM 求和
      AVG 平均值
      MAX 最大值
      MIN 最小值
      COUNT 计算个数
*/# 综合使用SELECT SUM(salary) "和",AVG(salary) "平均数",MAX(salary) "最大值",MIN(salary) "最小值",COUNT(salary) "总个数" FROM employees;/*
分组函数的特点:
   1.sum、avg一般用于处理数值型；max、min、count可以处理任何类型
   2.分组函数都忽略null值,都可以和distinct搭配去重
   3.和分组函数一同查询的字段要求是group by后的字段
   4.count函数经常用来统计行数,使用count(*)或count(1)或count(常量)
效率问题:
	MYISAM存储引擎下,count(*)效率高
	INNODB存储引擎下,count(*)和count(1)效率差不多,但比count(字段)要高
*/

🎜(2) 논리식으로 필터링🎜

# 1.查询每个工种的最高工资SELECT MAX(salary) "最高工资",job_id "工种" FROM employees GROUP BY job_id;# 2.查询每个位置上的部门个数SELECT COUNT(*) "部门个数",location_id "位置id" FROM departments GROUP BY location_id;# 3.查询邮箱中包含a字符的,每个部门的平均工资SELECT AVG(salary) "平均工资",department_id "部门id" FROM employees WHERE email LIKE '%a%' GROUP BY department_id;# 4.查询每个领导手下的有奖金的员工的最高工资SELECT MAX(salary) "最高工资",manager_id "领导编号" FROM employees WHERE NOT ISNULL(commission_pct)  GROUP BY manager_id;# 5.查询哪个部门的员工个数>2# 思路:查询每个部门的个数,再根据结果哪个部门的员工个数>2SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;# 6.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资SELECT MAX(salary) "最高工资",job_id "工种" FROM employees WHERE NOT ISNULL(commission_pct) GROUP BY job_id HAVING MAX(salary)>12000;# 7.查询领导编号>102的每个领导手下员工的最低工资>5000的领导编号是哪个,以及其最低工资SELECT MIN(salary) "最低工资",manager_id "领导编号" FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;# 8.按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些SELECT COUNT(*) "员工个数",LENGTH(last_name) "姓名长度" FROM employees GROUP BY LENGTH (last_name) HAVING COUNT(*)>5;# 9.查询每个部门每个工种的员工的平均工资SELECT AVG(salary) "平均工资",department_id "部门",job_id "工种" FROM employees GROUP BY department_id,job_id; # 10.查询每个部门每个工种的员工的平均工资,并按平均工资的高低显示SELECT AVG(salary) "平均工资",department_id "部门",job_id "工种" FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) DESC;

🎜(3) 퍼지 쿼리🎜

【sql92标准】# 1.等值连接# 查询女神名和对应的男朋友名# SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id=boys.id;# 1.查询员工名和对应的部门名SELECT last_name "员工名",department_name "部门名" FROM employees,departments 
WHERE employees.department_id=departments.department_id;# 2.查询员工名、工种号、工种名SELECT last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.`job_id`=j.`job_id`;# 3.查询有奖金的员工名、部门名SELECT last_name,department_name,commission_pct FROM employees e,departments d 
WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL;# 等值连接+筛选# 4.查询城市中第二个字符为o的部门名和城市名SELECT department_name "部门名",city "城市名" FROM departments d,locations l 
WHERE d.`location_id`=l.`location_id` AND city LIKE '_o%'; # 等值连接+分组# 5.查询每个城市的部门个数SELECT COUNT(*) "部门个数",city "城市" FROM departments d,locations l 
WHERE d.`location_id`=l.`location_id` GROUP BY city;# 6.查询有奖金的每个部门的部门名、部门的领导编号、该部门最低工资SELECT department_name,d.manager_id,MIN(salary) FROM departments d,employees e 
WHERE d.`department_id`=e.`department_id` AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id;# 7.查询每个工种的工种名、员工的个数并按员工的个数降序SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` GOUP BY job_title ORDER BY COUNT(*) DESC;# 8.支持三表连接# 查询员工名、部门名、所在的城市SELECT last_name,department_name,city FROM employees e,departments d,locations l 
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;# (2)非等值连接/*
先执行下面的语句,在myemployees数据库中创建新的job_grades表。

CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  INT,
 highest_sal INT);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);
*/# 1.查询员工的工资和工资级别SELECT salary,grade_level FROM employees e,job_grades j WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;# (3)自连接# 1.查询员工名和其上级的名称.SELECT e.employee_id "员工id",e.last_name "员工姓名",m.employee_id "经理id",m.last_name "经理姓名" FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;

【sql99标准】# (1)等值连接# 1.查询员工名,部门名SELECT last_name,department_name 
FROM employees eINNER JOIN departments dON e.department_id=d.department_id;# 2.查询名字中包含e的员工名和工种名(添加筛选)SELECT last_name,job_titleFROM employees eINNER JOIN jobs jON e.job_id=j.job_idWHERE last_name LIKE '%e%' OR job_title LIKE '%e%';# 3.查询部门个数>3的城市名和部门个数(分组+筛选)SELECT city,COUNT(*) "部门个数"FROM departments dINNER JOIN locations lON d.location_id=l.location_idGROUP BY cityHAVING COUNT(*)>3;# 4.查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序(排序)SELECT department_name "部门名",COUNT(*) "员工个数"FROM departments dINNER JOIN employees eON d.department_id=e.department_idGROUP BY department_nameHAVING COUNT(*)>3ORDER BY COUNT(*) DESC;# 5.查询员工名、部门名、工种名、并按部门名排序SELECT last_name "员工名",department_name "部门名",job_title "工种名"FROM employees eINNER JOIN departments d ON d.department_id=e.department_idINNER JOIN jobs j ON e.job_id=j.job_idORDER BY department_name ;# (2)非等值连接# 查询员工工资级别SELECT salary,grade_levelFROM employees eJOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_sal;# 查询每个工资级别的个数>20的个数,并且按照工资级别降序排列SELECT COUNT(*),grade_levelFROM employees eJOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_salGROUP BY grade_levelHAVING COUNT(*)>20ORDER BY grade_level DESC;# (3)自连接# 查询员工的名字、上级的名字SELECT e1.last_name "员工名",e2.last_name "上级名"FROM employees e1JOIN employees e2 ON e1.manager_id=e2.employee_id;--------------------------------------------------------------------------------------------------------------# 二、外连接# 1.查询男朋友不在男神表的女神名# 左外连接SELECT NAME 
FROM beauty LEFT OUTER JOIN boys ON beauty.boyfriend_id=boys.idWHERE boys.id IS NULL;# 右外连接SELECT NAMEFROM boys RIGHT OUTER JOIN beauty ON beauty.boyfriend_id=boys.idWHERE boys.id IS NULL;# 2.查询没有员工的部门SELECT d.department_name,e.employee_idFROM departments d LEFT JOIN employees e ON d.department_id=e.department_idWHERE e.manager_id IS NULL;SELECT * FROM employees WHERE employee_id=100;# 3.全外连接(不支持)# 全外连接就是就并集USE girls;SELECT b.*,bo.*FROM beauty bFULL JOIN boys boON b.boyfriend_id=bo.id;# 三.交叉连接# 使用99标准实现的笛卡尔乘积,使用cross代替了92中的,SELECT b.*,bo.*FROM beauty bCROSS JOIN boys bo

🎜

조건부 쿼리 요약 설명

🎜(1) 조건부 연산자로 필터링🎜🎜 )🎜🎜

🎜(2) 논리 표현식으로 필터링🎜🎜 && || !or and not🎜🎜

🎜(3) 퍼지 쿼리🎜🎜

키워드: like, between...and, in은 null입니다.🎜🎜 🎜🎜

学完了条件查询，尝试完成下面的练习题
答案：
一、SELECT salary,last_name FROM employees WHERE commission_pct IS NULL AND salary <br> 二、<code>SELECT * FROM employees WHERE job_id 'IT' OR salary=12000;
三、DESC departments;
四、SELECT DISTINCT location_id FROM departments;
五、不一定,考虑字段有null的情况.

三、排序查询

语法：select 查询列表 from 表 where 筛选条件 order by 排序列表 asc|desc

特点：

1.asc代表升序，esc代表降序，不写默认是升序。
2.order by子句支持单个字段、多个字段、表达式、函数、别名

执行顺序：from > where > select > order by (order by一般放在查询语句的最后面，limit子句除外(后面会讲到))

【排序查询】# 1.查询员工信息,要求工资从高到低排序SELECT * FROM employees ORDER BY salary DESC;# 2.查询部门编号>=90的员工信息,按入职时间的先后进行排序【添加筛选条件】SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;# 3.按照年薪的高低显示员工的信息和年薪【添加表达式排序】SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;SELECT * ,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC; # ORDER BY后支持别名# 4.按照姓名的长度,显示员工的姓名和工资【按函数排序】SELECT LENGTH(last_name) AS 字节长度, last_name,salary FROM employees ORDER BY 字节长度 DESC;# 5.查询员工信息,先按工资升序,再按员工编号降序SELECT * FROM employees ORDER BY salary ASC ,employee_id DESC;

学完了排序查询，尝试完成下面的练习题
答案：
1、SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC;
2、SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;
3、SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC;

排序查询总结	说明
升序	`order by asc`
降序	`order by desc`

4. 공통 함수

호출 구문: 테이블에서 함수 이름(실제 매개변수 목록) 선택;select 函数名(实参列表) from 表;

概念：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露接口。

好处：
1.隐藏了实现细节
2.提高代码重用性

分类：分为单行函数和分组函数。其中单行函数又分为：字符函数、数学函数、日期函数、系统函数、流程控制函数。；分组函数用来做统计功能，又称为统计函数、聚合函数、组函数。

单行函数	说明
(1)字符函数	参数类型为字符型
获取参数值的字节个数	`select length('字符串')`
拼接	`concat(字段1,字段2)`
大小写转换	`upper、lower`
截取	`substr(index,end)`
查找	`instr(主串,子串)`
清除两边空格	`trim(a from'aaaa字符串1aa')`
左右填充	`lpad('字符串1',左填充个数n,填充字符'a')`、`lpad('字符串1',右填充个数n,填充字符'a')`
替换	`replace('字符串1','被更换的字符串','新的字符串')`
(2)数学函数	参数类型为数值
四舍五入	`round(小数,保留位数)`
截取	`truncate(小数,保留位数)`
向上取整	`ceil(被向上取整的数值)`
向下取整	`floor(被向下取整的数值)`
取余	`mod(n,m);结果的正负和被取余数n相同`
随机数	`rand();返回0-1之间的小数`
(3)日期函数	参数为日期
返回当前完整日期	`select now();`
返回当前年月日	`select curdate();`
返回当前时分秒	`select curtime();`
截取指定部分	`select YEAR(now()) as 年,MONTH(now()) as 月,DAY(now()) as 日;`
字符串→日期	`STR_TO_DATE('2020-7-7','%Y-%m-%d')`
日期→字符串	`DATE_FORMAT(NOW(),'%Y年%m月%d日')`
返回两个日期相差的天数	`datediff(日期1,日期2)`
(4)系统函数	系统自带
查看当前版本	`select version();`
查看当前数据库	`select database();`
查看当前用户	`select user();`
自动加密	`password('字符');或md5('字符');`
(5)流程控制函数	类比Java
if	`if(奖金 IS NULL,'没奖金','有奖金')`

개념: Java의 메소드와 유사하며 일련의 논리문이 메소드에 캡슐화됩니다. body 에서는 인터페이스가 외부 세계에 노출됩니다. 이점:
1. 숨겨진 구현 세부정보
2. 코드 재사용성 향상

分组函数	说明
sum	`求和`
avg	`平均值`
max	`最大值`
min	`最小值`
count	`计算个数`

🎜분류: 단일 행 함수와 그룹화된 함수로 구분됩니다. 그 중 한 줄 함수는 문자 함수, 수학 함수, 날짜 함수, 시스템 함수, 프로세스 제어 함수로 구분됩니다. ;그룹 함수는 통계 함수, 집계 함수, 그룹 함수라고도 하는 통계 함수에 사용됩니다. 🎜🎜왼쪽 및 오른쪽 패딩🎜 🎜

단일 행 함수 설명

🎜( 1) 문자 함수🎜🎜 🎜매개변수 유형은 문자 유형🎜🎜🎜

매개변수 값의 바이트 수를 가져옵니다🎜 길이('문자열') 선택 code>🎜 🎜<tr> <td>Splicing🎜</td> <td> <code>concat(Field 1, Field 2)🎜🎜

대소문자 변환🎜 상위, 하위 code>🎜🎜

가로채기 🎜 substr(index,end)🎜🎜

찾기 🎜 instr( 메인 문자열, 하위 문자열)🎜🎜

양쪽에 공백을 비우세요🎜 trim(a from'aaaa string 1aa')🎜🎜

lpad('문자열 1', 왼쪽 패딩 번호 n, 패딩 문자 'a'), lpad('문자열 1', 오른쪽 패딩 번호 n , 패딩 문자 'a')🎜🎜

Replace🎜 replace('문자열 1','대체된 문자열','새 문자열')🎜🎜

🎜(2) 수학 함수🎜🎜 🎜매개변수 유형은 숫자 값입니다🎜🎜🎜

반올림🎜 반올림(십진수, 예약된 숫자) 🎜🎜

Truncate🎜 truncate(십진수, 예약 숫자)🎜🎜

반올림🎜 ceil( 반올림됨)🎜🎜

내림됨🎜 floor(내림된 값)🎜🎜

나머지 🎜 mod(n,m); 결과의 부호는 나머지 n과 같습니다.🎜🎜

임의의 숫자 🎜 rand(); 0-1 사이의 소수점🎜🎜

🎜(3) 날짜 함수🎜🎜 🎜매개변수는 날짜입니다🎜🎜🎜

반환 현재 완전한 날짜🎜select now();🎜🎜

현재 연도, 월, 일을 반환합니다🎜 curdate();🎜🎜

현재 시, 분, 초를 반환합니다🎜 select curtime();🎜🎜

지정된 부분을 가로채기🎜 YEAR( now() )는 연도, MONTH(now())는 월, DAY(now())는 일;🎜🎜

String→Date🎜 STR_TO_DATE(' 2020- 7-7','%Y-%m-%d')🎜🎜

날짜→문자열🎜 DATE_FORMAT(NOW(),'%YYear% m 월 %d일')🎜🎜

두 날짜 사이의 일수 차이를 반환합니다🎜 dateiff(date1, date2)🎜🎜

🎜(4) 시스템 기능🎜🎜 🎜시스템과 함께 제공됩니다🎜🎜🎜

현재 버전 보기🎜 버전 선택();🎜 🎜

현재 데이터베이스 보기🎜 select data();🎜🎜

현재 사용자 보기🎜 select user() ; 🎜🎜

자동 암호화🎜 password('character') 또는 md5('character');🎜🎜

🎜 (5) 프로세스 제어 기능 🎜🎜 🎜유비 Java🎜🎜🎜

if🎜 if(Bonus IS NULL,'보너스 없음','보너스 포함')🎜🎜

🎜 🎜🎜🎜🎜

그룹 기능 설명

🎜sum🎜🎜 합계🎜🎜<tr> <td>🎜avg🎜🎜</td> <td> <code>평균🎜🎜

🎜max🎜🎜 최대값🎜🎜<tr> <td>🎜min🎜🎜</td> <td> <code>최소값🎜🎜

🎜count🎜🎜

카운트 수 🎜🎜🎜🎜

【单行函数】# (1)字符函数-[参数类型为字符型]# 1.length 获取参数值的字节个数SELECT LENGTH('john');SELECT LENGTH('张三丰');SHOW VARIABLES LIKE '%char%' # 查看字符集# 2.concat拼接字符串SELECT CONCAT(last_name,'_',first_name) 姓名 from employees;# 3.upper、lower 大小写转换SELECT UPPER('tom');SELECT LOWER('TOM')# 将姓变大写,名变小写,然后拼接SELECT CONCAT(UPPER(last_name),LOWER(first_name))姓名 FROM employees;# 4.substr 拼接函数# mysql中的索引从1开始SELECT SUBSTR('若负平生意,何名作莫愁',7) AS out_put;SELECT SUBSTR('若负平生意,何名作莫愁',1,3) AS out_put;# 案例:姓名中首字符大写,其他字符小写,用_拼接并显示出来SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) oup_put FROM employees; # 5.instr 字符查找函数# 返回子串在主串中的起始索引,没有返回零SELECT INSTR('凡尘阿凉','阿凉') AS out_put;# 6.trim 清除空格函数# 将字符两边的空格移除SELECT LENGTH(TRIM('    凡尘    ')) AS out_put;SELECT TRIM('a' FROM  'aaaaaa凡aaa尘aaaa') AS out_put;# 7.lpad 左填充函数# 用指定的字符实现左填充指定长度SELECT LPAD('凡尘',10,'*') AS out_put;# 8.rpad 右填充函数# 用指定的字符实现右填充指定长度SELECT RPAD('凡尘',10,'*') AS out_put;# 9.replace 替换函数SELECT REPLACE('我的偶像是鲁迅','鲁迅','周冬雨') AS oup_put;---------------------------------------------------------------------------------------------------------# (2)数学函数-[参数类型为数值]# 1.round 四舍五入函数SELECT ROUND(1.65);SELECT ROUND(1.567,2);# 2.ceil 向上取整函数# 返回>=该参数的最小整数SELECT CEIL(1.00);# 3.floor 向下取整函数# 返回5,'大于','小于');SELECT last_name,commission_pct, IF(commission_pct IS NULL,'没奖金','有奖金') AS out_put FROM employees;# 2.MySQL Essence One: DQL 데이터 쿼리 문函数/*
方式一：类似于Java中的switch-MySQL Essence One: DQL 데이터 쿼리 문：
案例:查询员工工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
*/SELECT salary 原始工资,department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS 新工资FROM employees;/*
方式二：类似于Java中的多重if:
案例:查询员工的工资情况
工资>20000,显示A级别
工资>15000,显示B级别
工资>10000,显示C级别
否则,显示D级别
*/SELECT salary,CASEWHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别FROM employees;

【分组函数】/*
      SUM 求和
      AVG 平均值
      MAX 最大值
      MIN 最小值
      COUNT 计算个数
*/# 综合使用SELECT SUM(salary) "和",AVG(salary) "平均数",MAX(salary) "最大值",MIN(salary) "最小值",COUNT(salary) "总个数" FROM employees;/*
分组函数的特点:
   1.sum、avg一般用于处理数值型；max、min、count可以处理任何类型
   2.分组函数都忽略null值,都可以和distinct搭配去重
   3.和分组函数一同查询的字段要求是group by后的字段
   4.count函数经常用来统计行数,使用count(*)或count(1)或count(常量)
效率问题:
	MYISAM存储引擎下,count(*)效率高
	INNODB存储引擎下,count(*)和count(1)效率差不多,但比count(字段)要高
*/

学完了单行函数，尝试完成下面的练习题
答案:
1、SELECT NOW();
2、SELECT employee_id,last_name,salary*1.2 "new salary" FROM employees;
3、SELECT LENGTH(last_name) "长度",SUBSTR(last_name,1) "首字符",last_name FROM employees ORDER BY 首字符;
4、SELECT CONCAT(last_name,' earns '),salary,' monthly but wants ',salary*3 AS "Dream Salary" FROM employees WHERE salary=24000;
5、
SELECT last_name,job_id AS job,
CASE job_id
WHEN ‘AD_PRES’ THEN ‘A’
WHEN ‘ST_MAN’ THEN ‘B’
WHEN ‘IT_PROG’ THEN ‘C’
WHEN ‘SA_PRE’ THEN ‘D’
WHEN ‘ST_CLERK’ THEN ‘E’
END AS “Grade”
FROM employees
WHERE job_id =“AD_PRES”;

学完了分组函数，尝试完成下面的练习题：

答案：
1.SELECT ROUND(MAX(salary),2) "最大值",ROUND(MIN(salary),2) "最小值",ROUND(AVG(salary),2) "平均值",ROUND(SUM(salary),2) "总和" FROM employees;
2.SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) "DIFFERNCE" FROM employees;
3.SELECT COUNT(*) AS "员工个数" FROM employees WHERE department_id=90;

五、分组查询

语法:
select 分组函数,查询列表(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【having 分组后的筛选】
【order by 子句】

执行顺序：from > where > group by > having > select > order by

分类	筛选源	位置	关键字
分组前筛选	原始表	group by	where
分组后筛选	分组后的结果集	group by	having

注意：
1.查询列表必须是分组函数和group by后出现的字段。
2.分组函数做条件一定放在having子句中。
3.能用分组前筛选的优先使用分组前筛选。
4.group by子句支持单个字段分组、多个字段分组(多个字段之间用逗号隔开，没有顺序要求)、表达式或函数。
5.可以添加排序(排序放在整个分组查询最后)
6.一般不在group by和having后使用别名。

# 1.查询每个工种的最高工资SELECT MAX(salary) "最高工资",job_id "工种" FROM employees GROUP BY job_id;# 2.查询每个位置上的部门个数SELECT COUNT(*) "部门个数",location_id "位置id" FROM departments GROUP BY location_id;# 3.查询邮箱中包含a字符的,每个部门的平均工资SELECT AVG(salary) "平均工资",department_id "部门id" FROM employees WHERE email LIKE '%a%' GROUP BY department_id;# 4.查询每个领导手下的有奖金的员工的最高工资SELECT MAX(salary) "最高工资",manager_id "领导编号" FROM employees WHERE NOT ISNULL(commission_pct)  GROUP BY manager_id;# 5.查询哪个部门的员工个数>2# 思路:查询每个部门的个数,再根据结果哪个部门的员工个数>2SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;# 6.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资SELECT MAX(salary) "最高工资",job_id "工种" FROM employees WHERE NOT ISNULL(commission_pct) GROUP BY job_id HAVING MAX(salary)>12000;# 7.查询领导编号>102的每个领导手下员工的最低工资>5000的领导编号是哪个,以及其最低工资SELECT MIN(salary) "最低工资",manager_id "领导编号" FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;# 8.按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些SELECT COUNT(*) "员工个数",LENGTH(last_name) "姓名长度" FROM employees GROUP BY LENGTH (last_name) HAVING COUNT(*)>5;# 9.查询每个部门每个工种的员工的平均工资SELECT AVG(salary) "平均工资",department_id "部门",job_id "工种" FROM employees GROUP BY department_id,job_id; # 10.查询每个部门每个工种的员工的平均工资,并按平均工资的高低显示SELECT AVG(salary) "平均工资",department_id "部门",job_id "工种" FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) DESC;

学完了分组查询，尝试完成下面的练习题
答案：
1、SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROM employees GROUP BY job_id ORDER BY job_id ASC;
2、SELECT MAX(salary)-MIN(salary) "DIFFERENCE" FROM employees;
3、SELECT MIN(salary) ,manager_id FROM employees WHERE NOT ISNULL(manager_id) GROUP BY manager_id HAVING MIN(salary)>=6000;
4、SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_name ORDER BY AVG(salary) DESC;
5、SELECT COUNT(*) "个数",job_id FROM employees GROUP BY job_id;

六、连接查询

概念：查询的字段来自多个表。

分类：安装年代可以分为sql92和sq99，按照功能分为内连接(交集)、外连接(一个表中有,另一个表中没有)、交叉连接；其中sql92仅支持内连接，sql99除全外连接其他全都支持。

注意：
1.如果为表起了别名,则查询的字段就不能使用原来的表名去限定。
2.当涉及到多表查询时,为表起别名可以有效提高语句简洁度,方便区分多个重名的字段。

连接查询分为下面三类：
(1)内连接inner

等值连接
非等值连接
自连接

(2)外连接

左外连接left outer
右外连接right outer
全外连接full outer

(3)交叉连接cross

【sql92标准】# 1.等值连接# 查询女神名和对应的男朋友名# SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id=boys.id;# 1.查询员工名和对应的部门名SELECT last_name "员工名",department_name "部门名" FROM employees,departments 
WHERE employees.department_id=departments.department_id;# 2.查询员工名、工种号、工种名SELECT last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.`job_id`=j.`job_id`;# 3.查询有奖金的员工名、部门名SELECT last_name,department_name,commission_pct FROM employees e,departments d 
WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL;# 等值连接+筛选# 4.查询城市中第二个字符为o的部门名和城市名SELECT department_name "部门名",city "城市名" FROM departments d,locations l 
WHERE d.`location_id`=l.`location_id` AND city LIKE '_o%'; # 等值连接+分组# 5.查询每个城市的部门个数SELECT COUNT(*) "部门个数",city "城市" FROM departments d,locations l 
WHERE d.`location_id`=l.`location_id` GROUP BY city;# 6.查询有奖金的每个部门的部门名、部门的领导编号、该部门最低工资SELECT department_name,d.manager_id,MIN(salary) FROM departments d,employees e 
WHERE d.`department_id`=e.`department_id` AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id;# 7.查询每个工种的工种名、员工的个数并按员工的个数降序SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` GOUP BY job_title ORDER BY COUNT(*) DESC;# 8.支持三表连接# 查询员工名、部门名、所在的城市SELECT last_name,department_name,city FROM employees e,departments d,locations l 
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;# (2)非等值连接/*
先执行下面的语句,在myemployees数据库中创建新的job_grades表。

CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  INT,
 highest_sal INT);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);
*/# 1.查询员工的工资和工资级别SELECT salary,grade_level FROM employees e,job_grades j WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;# (3)自连接# 1.查询员工名和其上级的名称.SELECT e.employee_id "员工id",e.last_name "员工姓名",m.employee_id "经理id",m.last_name "经理姓名" FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;

学完了sql92标准的连接查询，尝试完成下面的练习题
答案：
1、
SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;
2、
SELECT e.job_id,d.location_id FROM employees e,departments d
WHERE d.department_id=e.department_id
AND e.department_id=90;
3、
SELECT last_name , department_name , l.location_id , city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND e.commission_pct IS NOT NULL;
4、
SELECT last_name , job_id , d.department_id , department_name
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.city=‘Toronto’;
5、
SELECT department_name,job_title,MIN(salary)
FROM departments d,employees e,jobs j
WHERE d.department_id=e.department_id AND e.job_id = j.job_id
GROUP BY job_title,department_name;
6、
SELECT COUNT(),country_id FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY country_id
HAVING COUNT()>2;
7、
SELECT e1.last_name “employees”,e1.employee_id “Emp#”,e2.last_name “manager”,e2.employee_id “Mgr#”
FROM employees e1,employees e2
WHERE e1.manager_id=e2.employee_id AND e1.last_name=‘kochhar’;

【sql99标准】# (1)等值连接# 1.查询员工名,部门名SELECT last_name,department_name 
FROM employees eINNER JOIN departments dON e.department_id=d.department_id;# 2.查询名字中包含e的员工名和工种名(添加筛选)SELECT last_name,job_titleFROM employees eINNER JOIN jobs jON e.job_id=j.job_idWHERE last_name LIKE '%e%' OR job_title LIKE '%e%';# 3.查询部门个数>3的城市名和部门个数(分组+筛选)SELECT city,COUNT(*) "部门个数"FROM departments dINNER JOIN locations lON d.location_id=l.location_idGROUP BY cityHAVING COUNT(*)>3;# 4.查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序(排序)SELECT department_name "部门名",COUNT(*) "员工个数"FROM departments dINNER JOIN employees eON d.department_id=e.department_idGROUP BY department_nameHAVING COUNT(*)>3ORDER BY COUNT(*) DESC;# 5.查询员工名、部门名、工种名、并按部门名排序SELECT last_name "员工名",department_name "部门名",job_title "工种名"FROM employees eINNER JOIN departments d ON d.department_id=e.department_idINNER JOIN jobs j ON e.job_id=j.job_idORDER BY department_name ;# (2)非等值连接# 查询员工工资级别SELECT salary,grade_levelFROM employees eJOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_sal;# 查询每个工资级别的个数>20的个数,并且按照工资级别降序排列SELECT COUNT(*),grade_levelFROM employees eJOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_salGROUP BY grade_levelHAVING COUNT(*)>20ORDER BY grade_level DESC;# (3)自连接# 查询员工的名字、上级的名字SELECT e1.last_name "员工名",e2.last_name "上级名"FROM employees e1JOIN employees e2 ON e1.manager_id=e2.employee_id;--------------------------------------------------------------------------------------------------------------# 二、外连接# 1.查询男朋友不在男神表的女神名# 左外连接SELECT NAME 
FROM beauty LEFT OUTER JOIN boys ON beauty.boyfriend_id=boys.idWHERE boys.id IS NULL;# 右外连接SELECT NAMEFROM boys RIGHT OUTER JOIN beauty ON beauty.boyfriend_id=boys.idWHERE boys.id IS NULL;# 2.查询没有员工的部门SELECT d.department_name,e.employee_idFROM departments d LEFT JOIN employees e ON d.department_id=e.department_idWHERE e.manager_id IS NULL;SELECT * FROM employees WHERE employee_id=100;# 3.全外连接(不支持)# 全外连接就是就并集USE girls;SELECT b.*,bo.*FROM beauty bFULL JOIN boys boON b.boyfriend_id=bo.id;# 三.交叉连接# 使用99标准实现的笛卡尔乘积,使用cross代替了92中的,SELECT b.*,bo.*FROM beauty bCROSS JOIN boys bo

MySQL Essence One: DQL 데이터 쿼리 문

学完了sql99标准的连接查询，尝试完成下面的练习题
答案：
一、
SELECT b.name,bo.*
FROM beauty b LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.id>3;
二、
SELECT city “城市”,department_name “城市名”
FROM departments d RIGHT JOIN locations l
ON d.location_id=l.location_id
WHERE d.department_id IS NULL;
三、
SELECT d.department_name,e.*
FROM departments d LEFT JOIN employees e
ON d.department_id=e.department_id
WHERE d.department_name IN (‘SAL’,‘IT’);

七、子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询。

按子查询出现的位置分类：

select后面 (仅支持标量子查询)
from后面 (支持表子查询)
where或having后面 (支持标量、行、列子查询)☆☆☆
exists后面 (又叫相关子查询，支持表子查询)

按结果集的行列数分类：

标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)

【where和having后的子查询】(支持标量、行、列子查询)# 1.单个标量子查询# 查询工资比Abel工资高的员工名SELECT last_name,salary 
FROM employees 
WHERE salary>(SELECT salary FROM employees WHERE last_name='Abel');# 2。多个标量子查询# 返回job_id与141号相同,salary比143号员工多的员工的姓名、job_id、工资。SELECT last_name,job_id,salary 
FROM employeesWHERE job_id=(
	SELECT job_id FROM employees WHERE employee_id=141) 
	AND salary>(SELECT salary FROM employees WHERE employee_id=143);# 3。标量子查询+分组函数# 返回工资最少的员工的last_name、job_id和salarySELECT last_name,job_id,salary 
FROM employees 
WHERE salary=(SELECT MIN(salary) FROM employees);# 4。标量子查询+having子句# 查询最低工资 >50号部门最低工资的 部门id和其最低工资SELECT department_id,MIN(salary) FROM employees GROUP BY department_idHAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);

	# 5.列子查询(多行子查询)# 返回location_id是1400或1700的部门中的所有员工姓名.SELECT last_name FROM employees 
WHERE department_id IN (
	SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));# 返回其它工种中比job_id为'IT_PROG'工种中任一工资低的员工的工号、姓名、job_id、以及salarySELECT employee_id,last_name,job_id,salary FROM employeesWHERE salary<any>'IT_PROG';# 返回其它工种中比job_id为'IT_PROG'工种中所有工资低的员工的工号、姓名、job_id、以及salarySELECT employee_id,last_name,job_id,salary FROM employeesWHERE salary<all>'IT_PROG';# 6.行子查询(一行多列或多列多行子查询)# 查询出员工编号最小并且工资最高的员工信息# 方式一SELECT * FROM employees WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);# 方式二SELECT * FROM employees 
WHERE employee_id=(SELECT MIN(employee_id) FROM employees)AND salary=(SELECT MAX(salary) FROM employees)</all></any>

【select后的子查询】：(仅支持标量子查询)# 1.查询每个部门的员工个数SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id=d.department_id) "员工个数"FROM departments d;# 2.查询员工号=102的部门名SELECT (
	SELECT department_name	FROM departments d	INNER JOIN employees e	ON d.department_id=e.department_id	WHERE e.employee_id=102) 部门名;

【from后面的子查询】(支持表子查询)# 1.查询每个部门的平均工资的工资等级,即将子查询后的结果充当一张表,要求必须起别名SELECT a.*,g.grade_level "工资等级"FROM (
	SELECT AVG(salary) ag,department_id	FROM employees	GROUP BY department_id) aINNER JOIN job_grades gON a.ag BETWEEN lowest_sal AND highest_sal;

【existts后面的子查询】(又叫相关子查询,可以用in代替)# exists结果只会是1或0 ：SELECT EXISTS(SELECT * FROM employees);# 1.查询有员工名的部门名SELECT department_nameFROM departments dWHERE EXISTS(SELECT * FROM employees e WHERE d.department_id=e.department_id);

学完了子查询，尝试完成下面的习题

答案：
1、思路:①查询Zlotkey的部门②查询部门号=①的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name=‘Zlotkey’
)
2、思路:①查询平均工资②查询工资比①高的员工的工号、姓名、工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(SELECT AVG(salary) FROM employees);
3、思路:①查询各部门平均工资②查询各部门工资比①高的员工的员工号、姓名、工资
SELECT employee_id,last_name,salary,e.department_id
FROM (
SELECT AVG(salary) ag,department_id
FROM employees e
GROUP BY department_id
)a INNER JOIN employees e
ON a.department_id=e.department_id
WHERE e.salary>a.ag
4、思路:①查询姓名中包含字母u的员工的部门②查询部门号=①中任意一个的员工的工号和姓名
SELECT employee_id “员工号”,last_name “姓名”
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE ‘%u%’
);
5、思路:①查询location_id=1700的部门②查询在①部门中工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id=1700
);
6、思路:①查询姓名为K_ing的员工编号②查询manager_id=①的姓名和工资
SELECT last_name “员工姓名”,salary “工资”
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name=‘K_ing’
);
7、思路:①查询最高工资②查询工资=①的姓.名
SELECT CONCAT(first_name,last_name) “姓.名”
FROM employees
WHERE salary=(SELECT MAX(salary) FROM employees);

如果觉得做得不过瘾，可以继续挑战下面的子查询经典案例:

答案：
1、思路:①查询最低工资②查询工资=①的last_name, salary
SELECT last_name, salary
FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees)
2、思路一:①查询各部门的平均工资;②查询①结果上的最低平均工资③查询哪个部门的平均工资=②;④查询部门信息
SELECT d.*
FROM departments d
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) a
)
);
思路二:①使用排序求出最低平均工资的部门编号②查询部门信息
SELECT * FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
);
3、思路:①查询各部门平均工资;②查询最低平均工资的部门编号
SELECT d.* ,ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
) a
ON d.department_id=a.department_id;
4、思路:①查询job的平均工资最高的job_id;②查询job信息
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
5、思路:①查询公司平均工资;②查询每个部门的平均工资;最后筛选②中平均工资 > ①
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
);
6、思路:①查询有manager的员工编号;②查询编号在①中的详细信息
SELECT * FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id FROM employees);
7、思路:①查询各部门最高工资中最低的那个部门id;②查询①部门的最低工资啊
SELECT MIN(salary) FROM employees WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1
);
8、思路:①查询平均工资最高的部门编号;②将employees和departments连接查询,筛选条件是①
SELECT last_name, d.department_id, email,salary
FROM employees e
INNER JOIN departments d ON d.manager_id=e.employee_id
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
)

八、分页查询

应用场景:当要显示的数据一页显示不全,需要分页提交sql请求。

语法:
select 查询列表
from 表
[join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by]
limit offset,size;

offset:从0开始的起始索引,若省略默认从第一条开始
size:要显示的条目个数

执行顺序:from > join > on > where > group by > having > select > order by > limit (limit语法和执行都在最后)

limit分页公式：

select * from 表 limit (page-1)*size,size;(要显示的页数为page,每页的条目数为size)

# 1.查询前五条员工信息SELECT * FROM employees LIMIT 5;# 2.查询第11条到第25条SELECT * FROM employees LIMIT 10,15# 3.有奖金的员工信息,并且显示出工资较高的前10名SELECT * FROM employeesWHERE commission_pct IS NOT NULLORDER BY salaryLIMIT 10;

九、联合查询

定义：将多条查询语句的结果合并成一个结果。

语法：查询语句1 union 查询语句2 unin ... 查询语句n

应用场景：当要查询的结果来自多个没有连接关系的表,但查询的信息一致时，最适合使用union。

注意事项：

要求多条查询语句的查询列数是一致的
要求多条查询语句查询的每一列的类型和顺序最好一致.
union默认去重,如果使用union all可以关闭去重
大多数情况下，union的查询效率比or高。

# 1.查询部门编号>90或邮箱包含a的员工信息SELECT * FROM employees WHERE department_id>90 OR email LIKE '%a%';SELECT * FROM employees WHERE department_id>90 UNION  SELECT * FROM employees WHERE email LIKE '%a%';

更多相关免费学习推荐：mysql教程(视频)

위 내용은 MySQL Essence One: DQL 데이터 쿼리 문의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：

이 기사는 csdn.net에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제

이전 기사：MySQL에서 일반적으로 사용되는 날짜 관련 함수에 대해 이야기해 보겠습니다.다음 기사：MySQL에서 일반적으로 사용되는 날짜 관련 함수에 대해 이야기해 보겠습니다.