MySQL中DQL数据查询语句怎么写
这篇文章将为大家详细讲解有关MySQL中DQL数据查询语句怎么写，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
目录
一、基础查询
二、条件查询
三、排序查询
四、常见函数
五、分组查询
六、连接查询
七、子查询
八、分页查询
九、联合查询
样本数据准备
进行DQL查询语句实验前，首先准备对应的数据，作为样本以供查询使用
使用SQLyog中导入该SQL脚本，可以看到准备好的样本表：
该样本是某跨国企业员工管理的4张表，下图对每张表的各个字段做了介绍：
一、基础查询
语法：select 查询列表 from 表名;
特点：
查询列表可以是:表中的字段、常量值、表达式、函数。
查询的结果是一个虚拟的表格。
执行顺序：from > select (先找到表，再开始查询)
注意:`是着重号，当某张表中的字段与关键字冲突时，可以在该字段两边加上着重号，以标明其是一个字段，而不是关键字(如`name`)。
【基础查询】#选中样本库USERmyemployees;#1.查询表中的单个字段SELECTlast_nameFROMemployees;#2.查询表中的多个字段SELECTlast_name,salary,emailFROMemployees;#3.查询表中所有的字段SELECT*FROMemployees;#4.查询常量值SELECT'Tom';#5.查询表达式SELECT7%6;#6.查询函数SELECTVERSION();#7.起别名(mysql中建议将起别名使用双引号引起来"别名")/*优点:便于理解;连接查询时,如果要查询的字段有重名情况,可以使用起别名来区分*/#方式一,使用asSELECT7%6AS结果;SELECTlast_nameAS姓,first_nameAS名FROMemployees;#方式二,使用空格SELECT7%6结果;SELECTlast_name姓,first_name名FROMemployees;#查询员工号为176的员工的姓名、部门、nianxinSELECTlast_name,department_id,salary*12*(1+IFNULL(commission_pct,0))AS年薪FROMemployees;#8.去重SELECTDISTINCTdepartment_idFROMemployees;#9.+号的作用/*select13+21;两个操作数都是数值型,自动做加法运算其中一个为字符型,则将字符型转换为数值型select'13'+1;转换成功,做加法运算select'hello'+1;转换失败,将字符型转换为0selectnull+10;只要其中一方为null,结果就为null补充ifnull函数:SELECTIFNULL(commission_pct,0)AS奖金率,commission_pctFROMemployees;mysql中用来拼接的不是+号,而是concat函数*/SELECTCONCAT(last_name,first_name)AS"姓名"FROMemployees;基础查询总结 说明 1.查询表中的单个字段select 字段1 from 表;2.查询表中的多个字段select 字段1,字段2,...字段n from 表;3.查询表中的所有字段select * from 表;4.查询常量值select '常量值;'5.查询表达式select 数值1 表达式 数值2;6.查询函数select f();7.起别名as8.去重distinct9.拼接使用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)按逻辑表达式筛选
支持&&||!,但推荐使用andornot逻辑表达式作用：用于连接条件表达式&&或and:两个都为true,结果为true,反之为false||或or:只要有一个条件为true,结果即为true,反之为false!或not:取反
(3)模糊查询
模糊查关键字：like、betweenand、in、isnull(1)like关键字可以判断字符型或数值型like一般和通配符搭配使用,通配符有%:代表任意多个字符,包含0个_:代表任意单个字符(2)between...and关键字可以提高语句简洁度包含临界值两个临界值不能调换顺序(3)in关键字可以提高语句简洁度in列表的值类型必须一致(4)isnull取反是isnotnull
【条件查询】(1)按条件运算符筛选#1.查询工资>12000的员工SELECT*FROMemployeesWHEREsalary>12000;#2.查询部门编号不等于90的员工名和部门编号SELECTdepartment_name,department_idFROMdepartmentsWHEREdepartment_id<>90;---------------------------------------------------------------------------------------------------------------------(2)按逻辑表达式筛选#1.查询工资在10000到20000之间的员工名、工资以及奖金率SELECTlast_name,salary,commission_pctFROMemployeesWHEREsalary>=10000ANDsalary<=20000;#2.查询部门编号不是在90到110之间的,或工资高于15000的员工信息SELECT*FROMemployeesWHERENOT(department_id>=90ANDdepartment_id<=110)ORsalary>15000;---------------------------------------------------------------------------------------------------------------------(3)模糊查询#(1)like关键字#1.查询员工名中包含字符a的员工的信息SELECT*FROMemployeesWHERElast_nameLIKE'%a%';#2.查询员工名中第三个字符为n,第五个字符为l的员工名和工资SELECTlast_name,salaryFROMemployeesWHERElast_nameLIKE'__n_l%';#3.查询员工名中第二个字符为_的员工名(转义)SELECTlast_nameFROMemployeesWHERElast_nameLIKE'_$_%'ESCAPE'$';#(2)between...and关键字#1.查询员工编号在100到120的员工信息SELECT*FROMemployeesWHEREemployee_idBETWEEN100AND120;#(3)in关键字#1.查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的员工名和工种编号SELECTlast_name,job_idFROMemployeesWHEREjob_idIN('IT_PROG','AD_VP','AD_PRES');#(4)isnull#1.查询没有奖金的员工名和奖金率SELECTlast_name,commission_pctFROMemployeesWHEREcommission_pctISNULL;#isnull仅仅可以用来判断null值;安全等于<=>既可以用来判断null值,又可以用来判断普通值#isnull的可读性高于<=>,建议使用isnullSELECTlast_name,commission_pctFROMemployeesWHEREcommission_pct<=>NULL;条件查询总结 说明 (1)按条件运算符筛选> < = >= <= !=(或<>)(2)按逻辑表达式筛选&& || !或and or not(3)模糊查询关键字:like、between...and、in、is null
学完了条件查询，尝试完成下面的练习题
答案：
一、SELECT salary,last_name FROM employees WHERE commission_pct IS NULL AND salary < 18000;
二、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*FROMemployeesORDERBYsalaryDESC;#2.查询部门编号>=90的员工信息,按入职时间的先后进行排序【添加筛选条件】SELECT*FROMemployeesWHEREdepartment_id>=90ORDERBYhiredateASC;#3.按照年薪的高低显示员工的信息和年薪【添加表达式排序】SELECT*,salary*12*(1+IFNULL(commission_pct,0))AS年薪FROMemployeesORDERBYsalary*12*(1+IFNULL(commission_pct,0))DESC;SELECT*,salary*12*(1+IFNULL(commission_pct,0))AS年薪FROMemployeesORDERBY年薪DESC;#ORDERBY后支持别名#4.按照姓名的长度,显示员工的姓名和工资【按函数排序】SELECTLENGTH(last_name)AS字节长度,last_name,salaryFROMemployeesORDERBY字节长度DESC;#5.查询员工信息,先按工资升序,再按员工编号降序SELECT*FROMemployeesORDERBYsalaryASC,employee_idDESC;
学完了排序查询，尝试完成下面的练习题
答案：
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四、常见函数
调用语法：select 函数名(实参列表) from 表;
概念：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露接口。
好处：
1.隐藏了实现细节
2.提高代码重用性
分类：分为单行函数和分组函数。其中单行函数又分为：字符函数、数学函数、日期函数、系统函数、流程控制函数。；分组函数用来做统计功能，又称为统计函数、聚合函数、组函数。
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)流程控制函数类比Javaifif(奖金 IS NULL,'没奖金','有奖金')求和avg平均值max最大值min最小值count计算个数【单行函数】#(1)字符函数-[参数类型为字符型]#1.length获取参数值的字节个数SELECTLENGTH('john');SELECTLENGTH('张三丰');SHOWVARIABLESLIKE'%char%'#查看字符集#2.concat拼接字符串SELECTCONCAT(last_name,'_',first_name)姓名fromemployees;#3.upper、lower大小写转换SELECTUPPER('tom');SELECTLOWER('TOM')#将姓变大写,名变小写,然后拼接SELECTCONCAT(UPPER(last_name),LOWER(first_name))姓名FROMemployees;#4.substr拼接函数#mysql中的索引从1开始SELECTSUBSTR('若负平生意,何名作莫愁',7)ASout_put;SELECTSUBSTR('若负平生意,何名作莫愁',1,3)ASout_put;#案例:姓名中首字符大写,其他字符小写,用_拼接并显示出来SELECTCONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))oup_putFROMemployees;#5.instr字符查找函数#返回子串在主串中的起始索引,没有返回零SELECTINSTR('凡尘阿凉','阿凉')ASout_put;#6.trim清除空格函数#将字符两边的空格移除SELECTLENGTH(TRIM('凡尘'))ASout_put;SELECTTRIM('a'FROM'aaaaaa凡aaa尘aaaa')ASout_put;#7.lpad左填充函数#用指定的字符实现左填充指定长度SELECTLPAD('凡尘',10,'*')ASout_put;#8.rpad右填充函数#用指定的字符实现右填充指定长度SELECTRPAD('凡尘',10,'*')ASout_put;#9.replace替换函数SELECTREPLACE('我的偶像是鲁迅','鲁迅','周冬雨')ASoup_put;---------------------------------------------------------------------------------------------------------#(2)数学函数-[参数类型为数值]#1.round四舍五入函数SELECTROUND(1.65);SELECTROUND(1.567,2);#2.ceil向上取整函数#返回>=该参数的最小整数SELECTCEIL(1.00);#3.floor向下取整函数#返回<=该参数的最大整数SELECTFLOOR(-9.99);#4.truncate截取函数#保留小数点后几位SELECTTRUNCATE(1.65,1);#5,mod取余函数#结果的正负和被除数相同:a-a/b*bSELECTMOD(10,-3);---------------------------------------------------------------------------------------------------------#(3)日期函数-[参数为日期]#1.now返回当前完整日期SELECTNOW();#2.curdate返回当前年月日SELECTCURDATE();#3.curtime返回当前时分秒SELECTCURTIME();#4.获取指定的部分SELECTYEAR(NOW())AS年;SELECTYEAR('1998-12-12')AS年;SELECTYEAR(hiredate)年FROMemployees;SELECTMONTH(NOW())月;SELECTMONTHNAME(NOW())月;#5.str_to_date将日期格式的字符转换为指定格式的日期SELECTSTR_TO_DATE('2020-7-7','%Y-%m-%d')ASout_put;#查询入职日期为1992-4-3的员工信息SELECT*FROMemployeesWHEREhiredate=STR_TO_DATE('4-31992','%m-%d%Y');#6.date_format将日期转换为字符SELECTDATE_FORMAT(NOW(),'%Y年%m月%d日');#查询有奖金的员工和入职日期(xx月/xx日xx年)SELECTlast_name,DATE_FORMAT(hiredate,'%m月/%d日%Y年')入职日期FROMemployeesWHEREcommission_pctISNOTNULL;---------------------------------------------------------------------------------------------------------#(4)系统函数#1.查看当前版本SELECTVERSION()#2.查看当前数据库SELECTDATABASE();#3.查看当前用户SELECTUSER();---------------------------------------------------------------------------------------------------------#(5)流程控制函数#1.if函数SELECTIF(10>5,'大于','小于');SELECTlast_name,commission_pct,IF(commission_pctISNULL,'没奖金','有奖金')ASout_putFROMemployees;#2.case函数/*方式一：类似于Java中的switch-case：案例:查询员工工资,要求部门号=30,显示的工资为1.1倍部门号=40,显示的工资为1.2倍部门号=50,显示的工资为1.3倍其他部门,显示的工资为原工资*/SELECTsalary原始工资,department_id,CASEdepartment_idWHEN30THENsalary*1.1WHEN40THENsalary*1.2WHEN50THENsalary*1.3ELSEsalaryENDAS新工资FROMemployees;/*方式二：类似于Java中的多重if:案例:查询员工的工资情况工资>20000,显示A级别工资>15000,显示B级别工资>10000,显示C级别否则,显示D级别*/SELECTsalary,CASEWHENsalary>20000THEN'A'WHENsalary>15000THEN'B'WHENsalary>10000THEN'C'ELSE'D'ENDAS工资级别FROMemployees;
【分组函数】/*SUM求和AVG平均值MAX最大值MIN最小值COUNT计算个数*/#综合使用SELECTSUM(salary)"和",AVG(salary)"平均数",MAX(salary)"最大值",MIN(salary)"最小值",COUNT(salary)"总个数"FROMemployees;/*分组函数的特点:1.sum、avg一般用于处理数值型；max、min、count可以处理任何类型2.分组函数都忽略null值,都可以和distinct搭配去重3.和分组函数一同查询的字段要求是groupby后的字段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
注意：
1.查询列表必须是分组函数和group by后出现的字段。
2.分组函数做条件一定放在having子句中。
3.能用分组前筛选的优先使用分组前筛选。
4.group by子句支持单个字段分组、多个字段分组(多个字段之间用逗号隔开，没有顺序要求)、表达式或函数。
5.可以添加排序(排序放在整个分组查询最后)
6.一般不在group by和having后使用别名。
#1.查询每个工种的最高工资SELECTMAX(salary)"最高工资",job_id"工种"FROMemployeesGROUPBYjob_id;#2.查询每个位置上的部门个数SELECTCOUNT(*)"部门个数",location_id"位置id"FROMdepartmentsGROUPBYlocation_id;#3.查询邮箱中包含a字符的,每个部门的平均工资SELECTAVG(salary)"平均工资",department_id"部门id"FROMemployeesWHEREemailLIKE'%a%'GROUPBYdepartment_id;#4.查询每个领导手下的有奖金的员工的最高工资SELECTMAX(salary)"最高工资",manager_id"领导编号"FROMemployeesWHERENOTISNULL(commission_pct)GROUPBYmanager_id;#5.查询哪个部门的员工个数>2#思路:查询每个部门的个数,再根据结果哪个部门的员工个数>2SELECTCOUNT(*),department_idFROMemployeesGROUPBYdepartment_idHAVINGCOUNT(*)>2;#6.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资SELECTMAX(salary)"最高工资",job_id"工种"FROMemployeesWHERENOTISNULL(commission_pct)GROUPBYjob_idHAVINGMAX(salary)>12000;#7.查询领导编号>102的每个领导手下员工的最低工资>5000的领导编号是哪个,以及其最低工资SELECTMIN(salary)"最低工资",manager_id"领导编号"FROMemployeesWHEREmanager_id>102GROUPBYmanager_idHAVINGMIN(salary)>5000;#8.按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些SELECTCOUNT(*)"员工个数",LENGTH(last_name)"姓名长度"FROMemployeesGROUPBYLENGTH(last_name)HAVINGCOUNT(*)>5;#9.查询每个部门每个工种的员工的平均工资SELECTAVG(salary)"平均工资",department_id"部门",job_id"工种"FROMemployeesGROUPBYdepartment_id,job_id;#10.查询每个部门每个工种的员工的平均工资,并按平均工资的高低显示SELECTAVG(salary)"平均工资",department_id"部门",job_id"工种"FROMemployeesGROUPBYdepartment_id,job_idORDERBYAVG(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.等值连接#查询女神名和对应的男朋友名#SELECTNAME,boyNameFROMboys,beautyWHEREbeauty.boyfriend_id=boys.id;#1.查询员工名和对应的部门名SELECTlast_name"员工名",department_name"部门名"FROMemployees,departmentsWHEREemployees.department_id=departments.department_id;#2.查询员工名、工种号、工种名SELECTlast_name,e.job_id,job_titleFROMemployeese,jobsjWHEREe.`job_id`=j.`job_id`;#3.查询有奖金的员工名、部门名SELECTlast_name,department_name,commission_pctFROMemployeese,departmentsdWHEREe.`department_id`=d.`department_id`ANDe.`commission_pct`ISNOTNULL;#等值连接+筛选#4.查询城市中第二个字符为o的部门名和城市名SELECTdepartment_name"部门名",city"城市名"FROMdepartmentsd,locationslWHEREd.`location_id`=l.`location_id`ANDcityLIKE'_o%';#等值连接+分组#5.查询每个城市的部门个数SELECTCOUNT(*)"部门个数",city"城市"FROMdepartmentsd,locationslWHEREd.`location_id`=l.`location_id`GROUPBYcity;#6.查询有奖金的每个部门的部门名、部门的领导编号、该部门最低工资SELECTdepartment_name,d.manager_id,MIN(salary)FROMdepartmentsd,employeeseWHEREd.`department_id`=e.`department_id`ANDcommission_pctISNOTNULLGROUPBYdepartment_name,d.manager_id;#7.查询每个工种的工种名、员工的个数并按员工的个数降序SELECTjob_title,COUNT(*)FROMemployeese,jobsjWHEREe.`job_id`=j.`job_id`GOUPBYjob_titleORDERBYCOUNT(*)DESC;#8.支持三表连接#查询员工名、部门名、所在的城市SELECTlast_name,department_name,cityFROMemployeese,departmentsd,locationslWHEREe.`department_id`=d.`department_id`ANDd.`location_id`=l.`location_id`;#(2)非等值连接/*先执行下面的语句,在myemployees数据库中创建新的job_grades表。CREATETABLEjob_grades(grade_levelVARCHAR(3),lowest_salINT,highest_salINT);INSERTINTOjob_gradesVALUES('A',1000,2999);INSERTINTOjob_gradesVALUES('B',3000,5999);INSERTINTOjob_gradesVALUES('C',6000,9999);INSERTINTOjob_gradesVALUES('D',10000,14999);INSERTINTOjob_gradesVALUES('E',15000,24999);INSERTINTOjob_gradesVALUES('F',25000,40000);*/#1.查询员工的工资和工资级别SELECTsalary,grade_levelFROMemployeese,job_gradesjWHEREsalaryBETWEENj.`lowest_sal`ANDj.`highest_sal`;#(3)自连接#1.查询员工名和其上级的名称.SELECTe.employee_id"员工id",e.last_name"员工姓名",m.employee_id"经理id",m.last_name"经理姓名"FROMemployeese,employeesmWHEREe.`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.查询员工名,部门名SELECTlast_name,department_nameFROMemployeeseINNERJOINdepartmentsdONe.department_id=d.department_id;#2.查询名字中包含e的员工名和工种名(添加筛选)SELECTlast_name,job_titleFROMemployeeseINNERJOINjobsjONe.job_id=j.job_idWHERElast_nameLIKE'%e%'ORjob_titleLIKE'%e%';#3.查询部门个数>3的城市名和部门个数(分组+筛选)SELECTcity,COUNT(*)"部门个数"FROMdepartmentsdINNERJOINlocationslONd.location_id=l.location_idGROUPBYcityHAVINGCOUNT(*)>3;#4.查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序(排序)SELECTdepartment_name"部门名",COUNT(*)"员工个数"FROMdepartmentsdINNERJOINemployeeseONd.department_id=e.department_idGROUPBYdepartment_nameHAVINGCOUNT(*)>3ORDERBYCOUNT(*)DESC;#5.查询员工名、部门名、工种名、并按部门名排序SELECTlast_name"员工名",department_name"部门名",job_title"工种名"FROMemployeeseINNERJOINdepartmentsdONd.department_id=e.department_idINNERJOINjobsjONe.job_id=j.job_idORDERBYdepartment_name;#(2)非等值连接#查询员工工资级别SELECTsalary,grade_levelFROMemployeeseJOINjob_gradesjONe.`salary`BETWEENj.lowest_salANDj.highest_sal;#查询每个工资级别的个数>20的个数,并且按照工资级别降序排列SELECTCOUNT(*),grade_levelFROMemployeeseJOINjob_gradesjONe.`salary`BETWEENj.lowest_salANDj.highest_salGROUPBYgrade_levelHAVINGCOUNT(*)>20ORDERBYgrade_levelDESC;#(3)自连接#查询员工的名字、上级的名字SELECTe1.last_name"员工名",e2.last_name"上级名"FROMemployeese1JOINemployeese2ONe1.manager_id=e2.employee_id;--------------------------------------------------------------------------------------------------------------#二、外连接#1.查询男朋友不在男神表的女神名#左外连接SELECTNAMEFROMbeautyLEFTOUTERJOINboysONbeauty.boyfriend_id=boys.idWHEREboys.idISNULL;#右外连接SELECTNAMEFROMboysRIGHTOUTERJOINbeautyONbeauty.boyfriend_id=boys.idWHEREboys.idISNULL;#2.查询没有员工的部门SELECTd.department_name,e.employee_idFROMdepartmentsdLEFTJOINemployeeseONd.department_id=e.department_idWHEREe.manager_idISNULL;SELECT*FROMemployeesWHEREemployee_id=100;#3.全外连接(不支持)#全外连接就是就并集USEgirls;SELECTb.*,bo.*FROMbeautybFULLJOINboysboONb.boyfriend_id=bo.id;#三.交叉连接#使用99标准实现的笛卡尔乘积,使用cross代替了92中的,SELECTb.*,bo.*FROMbeautybCROSSJOINboysbo
学完了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工资高的员工名SELECTlast_name,salaryFROMemployeesWHEREsalary>(SELECTsalaryFROMemployeesWHERElast_name='Abel');#2。多个标量子查询#返回job_id与141号相同,salary比143号员工多的员工的姓名、job_id、工资。SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=141)ANDsalary>(SELECTsalaryFROMemployeesWHEREemployee_id=143);#3。标量子查询+分组函数#返回工资最少的员工的last_name、job_id和salarySELECTlast_name,job_id,salaryFROMemployeesWHEREsalary=(SELECTMIN(salary)FROMemployees);#4。标量子查询+having子句#查询最低工资>50号部门最低工资的部门id和其最低工资SELECTdepartment_id,MIN(salary)FROMemployeesGROUPBYdepartment_idHAVINGMIN(salary)>(SELECTMIN(salary)FROMemployeesWHEREdepartment_id=50);#5.列子查询(多行子查询)#返回location_id是1400或1700的部门中的所有员工姓名.SELECTlast_nameFROMemployeesWHEREdepartment_idIN(SELECTDISTINCTdepartment_idFROMdepartmentsWHERElocation_idIN(1400,1700));#返回其它工种中比job_id为'IT_PROG'工种中任一工资低的员工的工号、姓名、job_id、以及salarySELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary<ANY(SELECTDISTINCTsalaryFROMemployeesWHEREjob_id='IT_PROG')ANDjob_id<>'IT_PROG';#返回其它工种中比job_id为'IT_PROG'工种中所有工资低的员工的工号、姓名、job_id、以及salarySELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary<ALL(SELECTDISTINCTsalaryFROMemployeesWHEREjob_id='IT_PROG')ANDjob_id<>'IT_PROG';#6.行子查询(一行多列或多列多行子查询)#查询出员工编号最小并且工资最高的员工信息#方式一SELECT*FROMemployeesWHERE(employee_id,salary)=(SELECTMIN(employee_id),MAX(salary)FROMemployees);#方式二SELECT*FROMemployeesWHEREemployee_id=(SELECTMIN(employee_id)FROMemployees)ANDsalary=(SELECTMAX(salary)FROMemployees)
【select后的子查询】：(仅支持标量子查询)#1.查询每个部门的员工个数SELECTd.*,(SELECTCOUNT(*)FROMemployeeseWHEREe.department_id=d.department_id)"员工个数"FROMdepartmentsd;#2.查询员工号=102的部门名SELECT(SELECTdepartment_nameFROMdepartmentsdINNERJOINemployeeseONd.department_id=e.department_idWHEREe.employee_id=102)部门名;
【from后面的子查询】(支持表子查询)#1.查询每个部门的平均工资的工资等级,即将子查询后的结果充当一张表,要求必须起别名SELECTa.*,g.grade_level"工资等级"FROM(SELECTAVG(salary)ag,department_idFROMemployeesGROUPBYdepartment_id)aINNERJOINjob_gradesgONa.agBETWEENlowest_salANDhighest_sal;
【existts后面的子查询】(又叫相关子查询,可以用in代替)#exists结果只会是1或0：SELECTEXISTS(SELECT*FROMemployees);#1.查询有员工名的部门名SELECTdepartment_nameFROMdepartmentsdWHEREEXISTS(SELECT*FROMemployeeseWHEREd.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 表2on 连接条件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*FROMemployeesLIMIT5;#2.查询第11条到第25条SELECT*FROMemployeesLIMIT10,15#3.有奖金的员工信息,并且显示出工资较高的前10名SELECT*FROMemployeesWHEREcommission_pctISNOTNULLORDERBYsalaryLIMIT10;
九、联合查询
定义：将多条查询语句的结果合并成一个结果。
语法：查询语句1 union 查询语句2 unin ... 查询语句n
应用场景：当要查询的结果来自多个没有连接关系的表,但查询的信息一致时，最适合使用union。
注意事项：
要求多条查询语句的查询列数是一致的
要求多条查询语句查询的每一列的类型和顺序最好一致.
union默认去重,如果使用union all可以关闭去重
大多数情况下，union的查询效率比or高。
#1.查询部门编号>90或邮箱包含a的员工信息SELECT*FROMemployeesWHEREdepartment_id>90ORemailLIKE'%a%';SELECT*FROMemployeesWHEREdepartment_id>90UNIONSELECT*FROMemployeesWHEREemailLIKE'%a%';
关于“MySQL中DQL数据查询语句怎么写”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
