DQL（Data Query Language）：数据查询语言 select
1.基础查询
语法：
SELECT 查询列表 【FROM 表名】;
类似于Java中 :System.out.println(要打印的东西);
特点：
1.通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
2.要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
3.使用前需打开指定的数据库
// 1. 查询表中单个字段SELECT last_name FROM employees;// 2. 查询表中多个字段，查询列表用逗号隔开，顺序无要求SELECT last_name,salary,email FROM employees;// 3. 查询表中所有字段SELECT * FROM employees;// 4.查询常量值SELECT 100;SELECT 'James';// 5. 查询表达式SELECT 6*23;// 6. 查询函数SELECT VERSION();// 7. 起别名//①便于理解 ②利用别名区分字段重名//方式一：SELECT last_name AS 姓;SELECT first_name AS 名;//方式二：SELECT last_name 姓;SELECT first_name 名;// 8. 去重(DISTINCT)SELECT DISTINCT department_id FROM emplyees;// 9. '+'的作用/*mysql中只有一个功能：运算符select 100+90;两个都为数值型，做加法运算select ’123‘+90; 一方为字符型，试图将字符型数值转换成数值型。如果转换成功，则继续做加法运算select ’John‘+90; 如果转换失败，则将字符型数值转换成0select null+10;只要其中一方为null，则结果肯定为null*///查询员工名和姓连接成一个字段，并显示为姓名，用空格隔开。（关键字CONCAT）SELECT CONCAT (last_name,' ',first_name) AS 姓名 FROM employees;
2.条件查询
条件查询：根据条件过滤原始表的数据，查询到想要的数据
语法：
SELECT 要查询的字段|表达式|常量值|函数FROM 表WHERE 条件 ;
分类：
一、条件表达式
示例：salary>10000
条件运算符：
大于，小于: > < >= <= 等于: = 不等于: != <>
二、逻辑表达式
示例：salary>10000 && salary<20000
逻辑运算符：
AND（&&）:两个条件如果同时成立，结果为true，否则为falseOR(||)：两个条件只要有一个成立，结果为true，否则为falseNOT(!)：如果条件成立，则not后为false，否则为true
三、模糊查询
示例：last_name like 'a%'关键词：like, between and, in, is null
1.like
特点：
① 一般和通配符搭配使用
%：任意多给字符
_：任意单个字符
例1：查询员工包含字符a的员工名
SELECT last_nameFROM employeesWHERE last_nameLIKE '%a%';
例2：查询员工名中第二个字符为_的员工名（ESCAPE）
SELECT last_nameFROM employeesWHERE last_nameLIKE '_\_%';或者指明转义符SELECT last_nameFROM employeesWHERE last_nameLIKE '_$_%' ESCAPE '$';
2.between and
① 使用between and 包含边界值
② 两个临界值不可颠倒顺序
例1：查询员工编号在100到120之间的员工名
SELECT last_nameFROM employeesWHERE employee_idBETWEEN 100 AND 120;
3.in
判断某字段的值是否属于in列表中的某一项
① 比使用OR提高语句简洁度
② IN列表里的值必须一致或兼容
③ 不支持%的使用
例1：查询员工的工种编号是IT_PROG, AD_VP, AD_PRES中的一个员工名和工种编号
SELECT last_nameFROM employeesWHERE job_idIN ('IT_PROG', 'AD_VP', 'AD_PRES');
4.is null / is not null
①=, <, >不能用于判断null
② is 只能判断null，= 只能判断数值， 安全等于 <=> 都可判断，但可读性较低
例1：查询没有奖金的员工名
SELECT last_name FROM employees WHERE commission_pct IS NULL;
3.排序查询
语法：
SELECT要查询的东西FROM表WHERE 条件ORDER BY 排序的字段|表达式|函数|别名 【asc/desc】
① asc/desc – 升序/降序，默认为asc升序
② order by子句一般是放在查询语句的最后面，limit子句除外
例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDERS BY salary DESC;
例2：查询部门编号>=90的员工信息，按入职时间的先后
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;
例3：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *, salary*12*(1+IFMULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary*12*(1+IFMULL(commission_pct,0)) DESC;
或
SELECT *, salary*12*(1+IFMULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;
例4：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) 字节长度, lastname, salaryFROM employees ORDER BY LENGTH(last_name) DESC;
例5：查询员工信息，先按工资升序，再按员工编号降序【按多个字段排序】
SELECT * FROM employees ORDER BY salary ASC, employee_id DESC;
4.常见函数
好处
1.隐藏了实现细节
2.提高代码的重用性
调用
SELECT 函数名(实参列表) 【from 表】
一、单行函数
1、字符函数
concat(str1,str2)拼接substr(str,pos)截取pos开始子串substr(str,pos,len)截取pos开始，长度为len子串instr(str,substr)返回子串第一次出现的索引upper(str)转换成大写lower(str)转换成小写trim(str)去前后指定的空格trim(char,str)去前后指定的字符ltrim去左边空格rtrim去右边空格replace(str,from_str,to_str)替换lpad(str,len,padstr)用指定字符左填充实现指定长度rpad用指定字符右填充实现指定长度length(str) 获取字节个数
2、数学函数
round 四舍五入rand 随机数floor向下取整ceil向上取整mod取余truncate截断truncate(1.699,2) 结果为1.69
3、日期函数
now当前系统日期+时间curdate当前系统日期curtime当前系统时间str_to_date 将字符转换成日期str_to_date('9-13-1999','%m-%d-%Y')结果为1999-9-13date_format将日期转换成字符date_format('/6/6','%Y年%m月%d日')结果为6月6日
4、流程控制函数
if 处理双分支if(5>10,'大','小');case使用一：处理等值判断case 要判断的字段或表达式when 常量1 then 要显示的值1或语句1when 常量2 then 要显示的值2或语句2...else 要显示的值n或语句nendcase使用二：处理条件判断，相当于多重ifcasewhen 条件1 then 要显示的值1或语句1when 条件2 then 要显示的值2或语句2...else 要显示的值n或语句nend
5、其他函数
version版本database当前库user当前连接用户
二、分组函数
功能：做统计使用
sum 求和max 最大值min 最小值avg 平均值count 计数SELECT SUM(salary) FROM employees;SELECT SUM(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees;
特点：
*
1、以上五个分组函数都忽略null值，除了count(*)2、sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型3、都可以搭配distinct使用，用于统计去重后的结果4、count的参数可以支持：(1) 字段 (2)*，表示全部行 (3) 常量值，一般放1，表示行数建议使用 count(*)，效率更高5、和分组函数一同查询的字段要求是group by后的字段
5.分组查询
语法：
select 查询的字段，分组函数from 表【where 条件】group by 分组的字段【order by 字段】
特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
4、可以按多个字段分组，字段之间用逗号隔开。多个字段均相同为一组
5、可以支持排序
6、having后可以支持别名
例1：查询每个部门的平均工资
SELECT AVG(salary) FROM employees;
例2：查询每个工种的最高工资
SELECT MAX(salary), job_id FROM employees GROUP BY job_id;
例3：查询每个位置上的部门个数
SELECT COUNT(*), location_id FROM departments GROUP BY location_id;
添加筛选条件
例3：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary), department_idFROM employeesWHERE email LIKE ‘%a%’BROUP BY department_id;
添加分组前的筛选条件
例4： 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary), manager_idFROM employeesWHERE commission_pct IS NOT NULLGROUP BY manager_id;
添加分组后的筛选条件
例5：查询哪个部门的员工个数>2
(1) 查询每个部门的员工个数SELECT COUNT(*), department_idFrom employeesGROUP BY department_id;(2) 根据(1)的结果进行筛选，查询哪个部门的员工个数>2SELECT COUNT(*), department_idFROM employeesGROUP BY department_idHAVING COUNT(*)>2;
例6：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
(1) 查询每个工种有奖金的员工的最高工资SELECT MAX(salary), job_idFROM employeesGROUP BY job_id
6.多表连接查询
含义：
又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 m行，表2 n行，结果m*n行
发生原因：没有有效连接条件
避免方法：添加有效连接条件
SELECT name, boyName FROM boys, beautyWHERE beauty.boyfriend_id=boys.id;
分类
按年代分
sql92标准：仅支持内连接sql99标准：支持内连接+外连接（全外连接除外）+交叉连接
按功能分：
内连接
等值连接非等值连接自连接
外连接
左外连接右外连接全外连接
交叉连接
一、sql92标准
1.等值连接
注：
字段重名有歧义时，添加表名限定。如：employees.department_id一般需要为表起别名。起别名后必须使用别名，不能使用原始表名表的顺序可以调换可以添加筛选、分组和排序可以实现多表连接。n表连接至少需要n-1个连接条件
例1：查询员工名和对应的部门名
SELECT last_name,department_nameFROM employees,departmentsWHERE employees.department_id=departments.department_id;
例2：查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_titleFROM employees e,jobs jWHERE e.job_id=j.job_id;
例3：查询有奖金的员工名、部门名（添加筛选）
SELECT last_name,department_nameFROM employees e,jobs jWHERE e.department_id=d.department_id;AND mission_pct IS NOT NULL;
例4：查询每个城市的部门个数（添加分组）
SELECT COUNT(*) 个数,cityFROM departments d,locations lWHERE d.location_id=l.location_idGROUP BY city;
例5：查询每个工种的工种名和员工的个数，并且按员工个数降序（添加排序）
SELECT job_title,COUNT(*)FROM jobs j,employees eWHERE j.job_id=e.job_idGROUP BY job_titleORDER BY COUNT(*) DESC;
例6：查询员工名、部门名和所在的城市（三表连接）
SELECT last_name,department_name,cityFROM employee e,departments d,locations lWHERE e.department_id=d.department_idAND d.location_id=l.location_id;
2.非等值连接
例1：查询员工和工资级别
SELECT salary,grade_levelFROM employees e,job_grades gWHERE salary BETWEEN g.lowest_sal AND g.highest_sal
3.自连接
一张表看作多张表连接
例1：查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name,FROM employees e,employees mWHERE e.employee_id=m.employee_id;
二、sql99标准
语法
SELECT 查询列表FROM 表1 别名1【连接类型：内连接、外连接、交叉连接】 JOIN 表2 别名2ON 连接条件【筛选、分组、排序】
分类
内连接(INNER)外连接(OUTER) 左外(LEFT 【OUTER】)右外(RIGHT 【OUTER】)全外(FULL 【OUTER】) 交叉连接(CROSS)
1.内连接
SELECT 查询列表FROM 表1 别名1INNER JOIN 表2 别名2ON 连接条件【筛选、分组、排序】
(1) 等值连接(可添加分组筛选排序)
例1：查询员工名、部门名
SELECT last_name,department_nameFROM employees eINNER JOIN departments dON e.department_id=d.department_id;
(2) 非等值连接(可添加分组筛选排序)
例1：查询员工工资级别个数>20的个数，并且按工资级别排序
SELECT COUNT(*),grade_levelFROM employees eINNER JOIN job_grades gON e.salary BETWEEN g.lowest_sal AND g.highest_salGROUP BY grade_levelHAVING COUNT(*)>20ORDER BY grade_level;
(3) 自连接
例：查询名字包含字符k的员工、上级的名字
SELECT e.last_name,m.last_nameFROM employees eINNER JOIN employees mON e.manager_id=m.employee_idWHERE e.last_name LIKE '%k%';
2.外连接
用于查询一个表有另一个表没有的记录
特点：
外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，显示匹配的值
如果从表中没有和它匹配的，显示NULL
外连接查询结果=内连接结果+主表中有二从表中没有的记录左外连接：left join左边的是主表
右外连接：right join右边的是主表
例1：查询男朋友不在男神表的女神名
//左外连接SELECT g.nameFROM girls gLEFT OUTER JOIN boys bON g.boyfriend = b.idWHERE g.id IS NULL;//右外连接SELECT g.nameFROM boys bRIGHT OUTER JOIN girls gON g.boyfriend = b.idWHERE g.id IS NULL;
例2：查询哪个部门没有员工
SELECT d.* FROM departments dLEFT OUTER JOIN employees eON d.departments_id = e.departments_idWHERE e.employee_id IS NULL;
3.交叉连接
笛卡尔乘积
总结
7.子查询
含义：出现在其他语句中的select语句，成为子查询或内查询
分类：
按子查询出现的位置：
select 后面 👉 仅支持标量子查询from后面 👉 支持表子查询where后面或having后面 ★
标量子查询 ✔
列子查询 ✔
行子查询（较少）exists后面（相关子查询） 👉
按结果集的行列数不同
标量子查询/单行子查询（结果集只有一行一列）列子查询/多行子查询（一列多行）行子查询（一般为一行多列，也可为多行多列）表子查询（一般为多行多列）
一、where/having后面
特点
子查询放在小括号内子查询一般放在条件的右侧标量子查询一般搭配单行操作符使用
列子查询一般搭配多行操作符使用 in, any/some, all子查询的执行优先主查询
1. 标量子查询/单行子查询
例1：谁的工资比Abel高
//(1)查询Able的工资SELECT salaryFROM employeesWHERE last_name = 'Abel';//2.查询员工信息，满足salary>(1)结果SELECT *FROM employeesWHERE salary>(SELECT salaryFROM employeesWHERE last_name = 'Abel');
例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资
SELECT last_name,job_id,salaryFROM employeesWHERE job_id=(SELECT job_idFROM employeesWHERE employee_id=141)AND salary>(SELECT salaryFROM employeesWHERE employee_id=143);
例3：查询公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salaryFROM employeesWHERE salary=(SELECT MIN(salary)FROM employees);
例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary)>(SELECT MIN(salary)FROM employeesWHERE department_id=50);
2. 列子查询/多行子查询
返回多行
使用多行比较操作符（IN/NOT IN, ANY|SOME, ALL）
例1：查询location_id是1400或1700的部门中的所有员工姓名
SELECT last_nameFROM employeesWHERE department_id IN(SELECT department_idFROM departmentsWHERE location_id=1400 OR 1700;)
例2：返回其他工种中比job_id为’IT_PROG’工种任意工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary< ANY(SELECT DISTINCT salaryFROM employeesWHERE job_id='IT_PROG')AND job_id<>'IT_PROG';//或SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary< (SELECT MAX(salary)FROM employeesWHERE job_id='IT_PROG')AND job_id<>'IT_PROG';
例3：返回其他工种中比job_id为’IT_PROG’工种所以工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary< ALL(SELECT DISTINCT salaryFROM employeesWHERE job_id='IT_PROG')AND job_id<>'IT_PROG';//或SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary< (SELECT MIN(salary)FROM employeesWHERE job_id='IT_PROG')AND job_id<>'IT_PROG';
3. 行子查询
需两个筛选条件用一样的操作符
例：查询员工编号最小并且工资最高的员工信息
SELECT *FROM employeesWHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)FROM employees)
二、select后面
例1：查询每个部门的员工个数
SELECT d.*,(SELECT COUNT(*)FROM employees eWHERE e.department_id=d.department_id) 个数FROM departments d;
例2：查询员工号为102的部门名
SELECT (SELECT department_nameFROM departmentsINNER JOIN employees eON d.department_id=e.department_idWHERE e.employee_id=102) 部门名;
三、from后面
查询结果充当一张表，必须起别名
例：查询每个部门的平均工资的工资等级
//(1)查询每个部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_id//(2)连接(1)的结果集和job_grades表，筛选条件平均工资between lowest and highestSELECT avg_dep.*g,grade_level,FROM (SELECT AVG(salary) avg_sal,department_idFROM employeesGROUP BY department_id) avg_depINNER JOIN job_grade gON avg_dep.avg_sal BETWEEN g.lowest_sal AND g.highest_sal;
四、exists后面（相关子查询）
语法
exists(完整的查询语句)
结果：1/0
例1：查询有员工的部门名
SELECT department_nameFROM departments dWHERE EXISTS(SELECT *FROM employees eWHERE e.department_id=d.department_id);//可以用IN的方式代替SELECT department_nameFROM departments dWHERE d.department_id IN(SELECT department_idFROM employees)
例2：查询没有女朋友的男神信息
SELECT boyNameFROM boys bWHERE NOT EXISTS(SELECT *FROM beauty gWHERE g.boyfriend_id=b.id);
8.分页查询
语法
SELECT 查询列表FROM 表【连接、筛选、分组、排序】limit offset,size;//offset: 要显示条目的起始索引(从0开始，若为0可省略)//size: 要显示的条目个数
特点
limit语句放在查询语句的最后公式
要显示的页数page，每页的条目数size
limit size*(page-1),size
例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
例2：查询有奖金的员工信息，并且工资较高的前十名
SELECT *FROM employeesWHERE commision_pct IS NOT NULLORDER BY salary DESCLIMIT 10;
9.联合查询(union)
将多条查询语句的结果合并为一个结果
语法
查询语句1union查询语句2union...
应用
要查询的结果来自于多个表且多个表没有直接的连接关系，但查询的信息一致
特点
要求多条查询语句的查询列数是一致的要求多条查询语句查询的每一列的类型和顺序最好一致union关键字默认去重。如果使用union all，可以包含重复项
例：查询中国用户中男性的信息以及外国用户中男性的信息
SELECT id,cName,cSex FROM t_ca WHERE cSex='男'UNIONSELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';
如果觉得《尚硅谷李玉婷老师MySQL课程--DQL语言》对你有帮助，请点赞、收藏，并留下你的观点哦！
