Home >Database >Mysql Tutorial >MySQL Essence One: DQL Data Query Statement
##Free learning recommendation: mysql video tutorial
Directory
##1. Basic queryBefore conducting the DQL query statement experiment, first prepare the corresponding data as a sample for query Use
to import the SQL script into SQLyog, and you can see the prepared sample table:
This sample is for employee management of a multinational enterprise There are 4 tables. The following figure introduces the fields of each table:
Syntax:
select query list from table name;Features:
The result of the query is a virtual table.
Execution order:
<pre class="brush:php;toolbar:false">注意:`是着重号,当某张表中的字段与关键字冲突时,可以在该字段两边加上着重号,以标明其是一个字段,而不是关键字(如`name`)。</pre>
<pre class="brush:php;toolbar:false">【基础查询】# 选中样本库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;</pre>
Instructions | |
---|---|
select field 1 from table ; |
|
select field 1, field 2,... Field n from table; |
|
select * from table; |
|
select 'Constant value;' |
|
select value 1 expression value 2; |
|
select f(); |
|
as |
|
distinct |
|
concat(last_name,first_name) |
|
2. Conditional query
Answer:
1.Correct2 .
Correct3.
Should use quotation marks in English4.
DESC departments;
;SELECT * FROM departments;
5.
SELECT CONCAT(first_name,',',last_name,',',IFNULL(email,0)) AS "out_put" FROM employees;
Syntax:
select query list from table name where filter conditions;Execution order:
Category:
(1) Filter by conditional operator
条件运算符有: > = )
(2) Filter by logical expression
支持&& || !,但推荐使用and or not 逻辑表达式作用:用于连接条件表达式 &&或and: 两个都为true,结果为true,反之为false ||或or : 只要有一个条件为true,结果即为true,反之为false !或not : 取反
(3) Fuzzy query
模糊查关键字: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;
Description | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
> = ) |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
##&& | | !Or and or not |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Keywords: like, between... and, in, is null |
|
排序查询总结 | 说明 |
---|---|
升序 | order by asc |
降序 | order by desc |
4. Common functions
Calling syntax: select function name (actual parameter list) from table;
Concept: similar to Java The method in encapsulates a set of logical statements in the method body and exposes the interface to the outside world.
Benefits:
1. Hides implementation details
2. Improves code reusability
Classification: Divided into single-line functions and grouped functions. Among them, Single-line functions
are divided into: character functions, mathematical functions, date functions, system functions, and process control functions. ;Group function
is used for statistical functions, also known as statistical functions, aggregate functions, and group functions.
Single-line function | Description |
---|---|
(1)Character function | The parameter type is character type |
Get the number of bytes of the parameter value | select length('string') |
Concatenation | concat(Field 1, Field 2) |
Case conversion | upper, lower |
substr(index,end )
|
|
instr(main string, substring)
|
|
trim(a from'aaaa string 1aa')
|
|
lpad( 'String 1', left padding number n, padding character 'a'), lpad('String 1', right padding number n, padding character 'a')
|
|
replace('String 1','Replaced string','New string')
|
|
(2) Math function | The parameter type is numeric value |
round(decimal, reserved digits)
|
|
truncate(decimal, reserved digits)
|
|
ceil (the value that is rounded up)
|
|
floor(the value that is rounded down)
|
|
mod( n,m); The sign of the result is the same as the remainder n
|
|
rand(); Returns 0-1 Decimal number
|
|
(3) Date function | The parameter is the date |
select now();
|
|
select curdate();
|
|
select curtime();
| |
select YEAR(now()) as year, MONTH(now()) as month, DAY(now()) as day;
|
|
STR_TO_DATE('2020-7-7','%Y-%m-%d')
|
|
DATE_FORMAT(NOW(),'%Y year %m month %d day')
| |
datediff(date1,date2)
|
|
(4 ) System function | The system comes with it |
select version();
|
|
select database();
|
|
select user();
|
|
password('character'); or md5( 'Character');
|
|
(5) Process control function | Analogy Java |
if(Bonus IS NULL,'No bonus','With bonus')
|
|
Description | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Sum |
|
||||||||||||
Average |
|
||||||||||||
Maximum value |
|
||||||||||||
Minimum value |
|
||||||||||||
Calculate the number |
|
分类 | 筛选源 | 位置 | 关键字 |
---|---|---|---|
分组前筛选 | 原始表 | 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
学完了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语句,称为子查询或内查询;外部的查询语句,称为主查询或外查询。
按子查询出现的位置
分类:
按结果集的行列数
分类:
【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.ag4、思路:①查询姓名中包含字母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。
注意事项:
# 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教程(视频)
The above is the detailed content of MySQL Essence One: DQL Data Query Statement. For more information, please follow other related articles on the PHP Chinese website!