MySql学习之基础查询(DQL语言)
1、mysql的进阶1 基础查询
语法： select 查询列表 from 表明 特点： 1、查询列表可以是: 表中的字段、长量值、表达式、函数 2、查询的结果是一个虚拟的表格 3、查询长量值、表达式、函数时显示的结果字段默认是我们查询的 长量值、表达式、函数,结果是运算的结果。 1、查询常量值 select 100; select 'join'; 2、查询表达式 select 100%98; 3、查询函数 select version(); 4、起别名 使用 as 或者直接起别名,如果别名中有特殊符号 如空格 # 需要我们加单引号 select salary as 'out put' from employees; 5、去重 查询员工表中涉及到的所有的部门编号 select DISTINCT `employees`.`department_id` from employees; 在我们查询的字段前加distinct 关键字 需求 查询员工名和姓连接成一个字段，并显示为姓名 6、算数运算符 算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算 + 号的作用 java 中 + 号的作用 1、运算符 2、连接符 mysql中 + 号仅仅只有一个功能 运算符的功能 select 100 + 90; 两个操作数都为数值型，则做加法运算 select '123' + 90; 其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算 select 'join' + 90; 如果转换失败，则将字符型数值转换成0,结果如下 'join'+90 90 select null + 10; 只要其中一方为null,则结果一定是null; 结果如下 null + 90 null - 号的作用 select 100 - 90; 两个操作数都为数值型，则做加法运算 select '123' - 90; 其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算 select 'join' - 90; 如果转换失败，则将字符型数值转换成0,结果如下 'join' - 90 -90 7、concat的使用 select concat(last_name,'_',first_name) as 姓名 from employees; 8、练习题 1、显示表departments的结构。 desc departments; 2、显示出表 departments 的全部部列，各个列之间用逗号连接，列头显示为 out_put 这里 简单的学习了下 ifnull 函数 select concat(IFNULL(`employees`.`commission_pct`,0),',',`employees`.`department_id`,',',`employees`.`email`) as 'out put' from `employees`; 如果第一个表达式的结果是null 就显示 为0否则就显示原结果。
2、mysql的进阶2 条件查询
1、条件查询的分类 1、按条件表达式筛选 < > = <> (不等于) <= >= 2、逻辑运算符 用于连接条件表达式 and or not not:如果连接的条件本身为false,结果是true,反之为false 3、模糊查询 like、between and、in、is null、is not null; 2、按条件表达式查询 select last_name,department_id FROM `employees` where `employees`.`department_id` <> 90; 3、 逻辑运算符 SELECT last_name, salary, IFNULL(commission_pct,0) as commission_pct FROM `employees` WHERE salary >= 10000 AND salary <= 20000; 4、模糊查询 select * from `employees` where `employees`.`last_name` like '%a%'; like 一般和 通配符配合使用,可以判断字符型和数值型 % 表示任意多个 _ 表示任意 任意单个字符 需求:查询员工名中第三个字符是e,第五个字符为a的员工名和工资 SELECT * FROM `employees` WHERE `employees`.`last_name` LIKE '__e_a%' 需求: 查询员工名中第二个字符为_的员工名 这里需要用到转译符了 SELECT * FROM `employees` WHERE `employees`.`last_name` LIKE '_\_%'; 推荐使用escape 关键字来指定我们自己定义的转义字符 SELECT * FROM `employees` WHERE `employees`.`last_name` LIKE '_$_%' ESCAPE '$'; in :判断某字段的值是否属于in列表中的某一项 is null / is not null 专门用来判断null 值的 = 或者 <> 不能用于判断null值 安全等于 <=> 判断的是：是否等于可以用来判断null值 和普通的字段。可读性较低，建议不要用 需求: 查询没有奖金的员工名和奖金和奖金率 SELECT last_name, commission_pct FROM `employees` WHERE commission_pct <=> NULL; 需求: 查询工资为12000的员工信息 需求: 1、 查询员工号是176 的员工的姓名和部门号和年薪 SELECT last_name, department_id, salary * 12 * (1 + IFNULL(commission_pct,0)) AS 年薪 FROM `employees` WHERE employee_id = 176; 这个SQL要好好的理解下 尤其是 salary * 12 * (1 + IFNULL(commission_pct,0)) 这个表达式。 2、查询employee表中 job_id 不为‘IT’ 或者 工资 为12000 的员工信息 select * from `employees` where salary = 12000 OR job_id <> 'IT' 这里要注意下 不 等于的用法<> 不能用not 没有这个语法 经典面试题 select * from `employees` 和 select * from `employees` where commission_pct like '%%' and last_name like '%%' 的结果是否一样 可能不一样 如果 commission_pct有null的话。like 主要针对的是字符串 而null可以理解成是一个对象。like 不会进行匹配的
3、mysql的进阶3排序查询
关键字是 order by 放在 查询的结果后面。 支持单个字段、多个字段、表达式、别名，函数 order by 子句 一般放在查询语句的最后面。 需求： 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)); 也支持使用别名 SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪 FROM `employees` ORDER BY 年薪 DESC; 4、按年薪的高低 显示员工的信息和年薪 按表达式排序 SELECT LENGTH(last_name) 字节长度, salary, email FROM `employees` ORDER BY LENGTH(last_name) DESC; 5、查询员工信息，要求先按工资排序，再按员工编号排序降序 SELECT * FROM `employees` ORDER BY salary ASC, employee_id DESC 理解:先按照工资升序，如果工资一样按照 编号进行降序显示 6、查询工资不在8000 到 17000 的员工姓名和工资，按工资降序 SELECT salary, last_name FROM `employees` WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC; 这里注意 学习 not between and 的学习。
4、mysql的进阶4 常见函数
概念: 类似于java的方法，将一组逻辑语句封装在方法中，对外暴露方法名。 好处: 1、隐藏了实现细节 2、提高代码的重用性 调用: select 函数名(参数列表) from 表; 这里要注意什么时候加 from 表。当函数中的参数用到了表中的字段是需要加。 分类: 1、单行函数 如 concat,length,ifnull 等，给一个值返回一个值,查询的字段结果是每一行都有结果 2、统计函数或者聚合函数 如 sum,avg,max,min,count 查询的字段结果就一行结果，展示的是统计结果 单行函数 1、字符函数 1、length 获取参数值的字节个数 select LENGTH('join'); 4 select LENGTH('张三丰hahaha'); 15 select length(last_name) from `employees` 2、concat 拼接字符串 select CONCAT(last_name,'_',first_name) from `employees` 这个sql 用到了 from 因为参数 来自于表。 4、upper,lower 用来转换大小写 需求: 将姓变大写，名变小写，然后拼接 SELECT CONCAT( UPPER(last_name), LOWER(first_name) ) AS 姓名 FROM `employees` 5、substr 截取字符 从1开始 java 从0开始 select SUBSTR('李莫愁爱上了陆展元',6) as out_put; #了陆展元从 第六个开始截取 select SUBSTR('李莫愁爱上了陆展元',1,3) as out_put; #李莫愁 从第一个开始截取3位 需求:姓名中首字母大写,其他字符小写然后用_拼接，显示出来 SELECT CONCAT( UPPER(SUBSTR(last_name, 1, 1)), '_', LOWER(SUBSTR(first_name, 2)) ) AS out_put FROM `employees` 这个需求要特别的注意下。 6、instr返回 字符串在大的字符串里面的第一次出现的位置 如果 没有就返回0 select instr('杨不悔爱上了殷六侠','殷六侠') 7; 查询employees 表中 first_name 字段中 ‘殷六侠’ 第一次出现的位置 select instr(first_name,'殷六侠') as out_put from `employees` 7、trim 默认去掉前后的空格 或者去特定的字符串 去前后空格 select TRIM( ' 11111 '); 去特定的字符 select TRIM('A' FROM 'AAAAAAA高河强AAAAAA'); 8、replace 替换,让指定的字符替换某个指定的字符 select REPLACE('张无忌爱上了周芷若','周芷若','赵敏') 把周芷若替换成赵敏 2、其他函数 select version(); 3、流程控制函数 1、if 函数 类似于 java 中的 if else. 结构: if(表达式1,表达式2,表达式2); 如果表达式 1 是返回true 就执行表达式2,如果返回false 就执行表达式3,类似于三元表达式 select if(10 > 5,'大','小'); 需求:查询有奖金的人的名字,奖金率,如果有奖金备注提示为‘有奖金 嘻嘻’ 否则 提示 ‘没奖金 呵呵’ SELECT last_name, commission_pct, IF ( commission_pct IS NULL, '没奖金 呵呵', '有奖金 嘻嘻' ) FROM `employees` 这个使用要特别的理解下哈 case 函数 1、case函数使用1 类似于 java 中的switch 函数 case 要判断的字段或表达式 when 常量1 then 要显示的值1或者语句1; #意思是 字段或者表达式的结果符合常量1 就执行。如果是语句一定要加分号 when 常量2 then 要显示的值1或者语句2; #意思是 字段或者表达式的结果符合常量2 就执行。如果是语句一定要加分号 ... else 默认的，如果都不符合就执行else end 表示case 函数到这里结尾了。 注意: case 可以搭配select来使用也可以单独的使用，如果搭配select 使用 then后面是值,单独使用跟的是表达式 需求 查询员工的工资，要求 如果部门号=30，显示的工资为1.1 倍 如果部门号=40，显示的工资为1.2 倍 如果部门号=50，显示的工资为1.3 倍 其他部门，显示的工资为原工资 SELECT salary 原始工资, department_id, CASE department_id WHEN 30 THEN salary * 1.1 WHEN 40 THEN salary * 1.2 WHEN 50 THEN salary * 1.3 ELSE salary END AS 新工资 FROM `employees`; 2、case函数使用2 类似于 java 中的多重if else if else if ...else。 case when 条件1 then 常量1或者表达式1; when 条件2 then 常量2或者表达式2; when 条件3 then 常量3或者表达式3; else 默认的常量n或者表达式n; end 需求 查询员工的工资情况 如果工资>20000,显示 A级别 如果工资>15000,显示 B级别 如果工资>10000,显示 C级别 否则 显示D级别 SELECT salary, CASE WHEN salary > 20000 THEN 'A级别' WHEN salary > 15000 THEN 'B级别' WHEN salary > 10000 THEN 'C级别' ELSE 'D级别' END '工资级别' FROM `employees` 3、体会这两种用法的区别 1、如果 case 结构不起别名 那么显示的列头是 case 或者 case 字段或表达式 2、case 时也会把表的每一行进行判断，这里要特别的注意啊 分组函数 分组函数主要是用来统计的 常见的有 sum,avg,max,min,count. sum(字段)求和 1、简单实用 select sum(salary) from `employees`; select avg(salary) from `employees`; select max(salary) from `employees`; select min(salary) from `employees`; select count(salary) from `employees`; select sum(salary) 平均,ROUND(avg(salary),2) 平均,max(salary) 最大 from `employees`; 2、统计函数的使用特点 sum和avg 一般 用来处理数值型的字段 max和min 一般 可以用来放字符型、数值型、日期型字段 count 可以放任何类型的字段，如果放的字段有null值，统计的结果是 非空字段的数量。 会把null值排除掉 3、null 是否参与运算 sum 或者 avg 函数，统计的结果 null不参与运算 max 和 min 函数 统计的结果 null不参与运算 count(字段) 也忽略null值 4、和distinct 搭配运算 select sum(DISTINCT salary),sum(salary) from `employees` select COUNT(DISTINCT salary), COUNT(salary) from `employees` #统计工资字段的对应的具体数量值 5、count函数的详细介绍 count(*,字段) *: 所有行所有列 用的比较多在统计的时候。如果某个字段的某一行是null,但是同行别的字段不是null就+1， 字段: 统计字段列非空的值的数量。 常量: 一般是1，相当于表中增加了一列，列名是1，所有行的值是1，所以count(1)也是统计所有行的数量。 效率的问题 INNODB 存储引擎 count(*) 和 count(1) 差不多，都比count(字段)稍微高点 MYISAM 存储引擎 count(*) 效率最高。 6、和分组函数一同查询的字段有限制要求的 和分组函数一同查询的字段要求是group by 后的字段 7、需求 查询员工表中最大入职时间和最小入职时间相差的天数 这里用到了一个日期函数 datediff SELECT max(hiredate) max, min(hiredate), DATEDIFF(max(hiredate), min(hiredate)) 天 FROM `employees` 常见的日期函数 curdate() 返回当天的日期 curtime() 返回现在的时间，时分秒 year() 获取当前的年份 month() 获取当前的月份 day() 获取当前是那一天 DATE_ADD() 指定时间向后推的日期 select DATE_ADD('2023-02-12',INTERVAL 50 year); select DATE_ADD(now(),INTERVAL 50 day); select DATE_ADD(now(),INTERVAL 50 month); DATEDIFF(时间1，时间2) 获取时间1和时间2之间差的天数,时间1 - 时间2 select first_name,DATEDIFF(now(),hiredate) as '天数' from employees order by DATEDIFF(now(),hiredate) DESC; TIMESTAMPDIFF(单位,时间1，时间2) 获取时间2和时间1 之间差的 单位(例如年), 时间2-时间1 select first_name,TIMESTAMPDIFF(year,hiredate,now()) as '年数' from employees order by DATEDIFF(now(),hiredate) DESC;
5、mysql的进阶5 分组查询
需求 查询每个部门的平均工资 关键字是 group by + 字段或者表达式、函数;意思把一个大表按照字段相同的值或者表达时的结果进行分成若干组。 group by 正常是配合统计函数来使用的，但不是必须的。如果要查询个字段，该字段必须根据字段来分组 需求1 查询每个工种的最高工资 SELECT max(salary), job_id FROM `employees` GROUP BY job_id 需求2 查询每个位置的部门个数 SELECT count(*), location_id FROM `departments` GROUP BY location_id; 需求3 查询邮箱中包含a的每个部门的平均工资 SELECT ROUND(AVG(salary), 2),department_id FROM `employees` WHERE email LIKE '%a%' GROUP BY department_id having 的使用 Having语句是对分组之后的虚拟表的字段进行过滤，因此使用having前通常会使用group by where 是对原始表有的字段进行过滤,放在group by 后面 统计函数做条件 肯定放在having 子句后面 需求4 复杂的筛选条件 查询那个部门的员工个数 > 2 1、查询每个部门的工员工个数 2、根据1的结果进行筛选，查询那个部门的员工个数>2 SELECT count(*), department_id FROM `employees` GROUP BY `employees`.`department_id` HAVING count(*) > 2; 查询每个工种有奖金的员工的最高工资大于12000的工种的工种编号和最高工资 SELECT max(salary), job_id FROM `employees` WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING max(salary) > 12000 查询领导编号>102的每个领导手下的最低工资>5000 的领导编号是那个，以及最低工资 SELECT MIN(salary), manager_id FROM `employees` WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000 按照多个字段进行分组 多个字段按照逗号进行分割，先按字段一进行分组，在1的基础上按照字段2 进行分组。 这里要特别的理解下，按字段1分组后如果需要继续按字段2分组，正常情况下按字段1分组后的结果有重复的字段，可以继续按照 字段2分组，但是特殊的情况下单纯的为了查字段，按字段1 分组和按字段2 分组的效果是一样的，也可以按多个字段分组。 需求 查询每个部门每个工种的员工的平均工资. 这句话的意思是先按部门进行分组，分成1---10 个组 每个组里面 根据工种来进行二次分组，比如说 1 组里面有 2个工种 20个人，那么就是 在1的基础上分成2组。 SELECT ROUND(AVG(salary), 2), department_id, job_id FROM `employees` GROUP BY department_id, job_id; 分组+排序 需求 查询每个部门每个工种的员工的平均工资>10000的,按照平均工资进行倒序 SELECT AVG(salary) a, department_id d, job_id FROM `employees` WHERE department_id IS NOT NULL GROUP BY department_id, job_id HAVING a > 10000 ORDER BY d DESC
6、mysql 进阶6 连接查询
笛卡尔积现象 假如表1 有m行，表2 有n行，select * from beauty,boys 的结果是 m*n行记录 发生的原因 没有有效的连接筛选条件 如何避免 添加有效的连接筛选条件 连接按功能分类 内连接 等值连接 非等值连接 自连接 外连接 左外连接 右外连接 全外连接 (mysql 不支持) 交叉连接 sql 99 语法 学习 内连接之等值连接 注意点： 1、inner join 中的 inner 可以省略，特别的注意 2、等值连接的意思连接条件用= 号来连接 需求: 查询女神和对应的男神名 SELECT beauty.`name`, boys.boyName FROM beauty INNER JOIN boys ON beauty.boyfriend_id = boys.id beauty表每一行 和 boys表每一行进行匹配，根据beauty.boyfriend_id 和boys.id 来匹配，如果匹配成功就显示可能会匹配很多条，不成功就不显示 需求:查询员工名,部门名，工种名，并按部门名进行降序 SELECT e.last_name, d.department_name, j.job_title FROM `employees` e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN jobs j ON e.job_id = j.job_id ORDER BY d.department_name 这里注意 多表连接的 sql 的书写 内连接之非等值连接 非等值连接的意思连接条件不用=号来连接 需求: 查询员工的工资和工资级别 SELECT salary, jg.grade_level, e.first_name FROM `employees` e JOIN `job_grades` jg ON e.salary BETWEEN jg.`lowest_sal` AND jg.`highest_sal` 这个sql要注意下，这个之前没有写过哈 内连接之自连接 自连接不是说每个表都适用的，有一些特殊的字段,连接条件是同一个表的一个字段和另一个字段是一样的 自连接就是 自己和自己连接 需求: 查询员工的名字、上级的名字 SELECT n.last_name, m.last_name FROM `employees` n JOIN `employees` m ON n.manager_id = m.employee_id 外连接 外连接一般用于查询一个表中有一个表中没有的记录. 左外连接:left 左边的是主表 右外连接:right 右边的是主表 主从表: 查询的主要信息来自于那个表，那个表就是主表 需求: 查询么有男朋友表的女神 SELECT b.`name` FROM `boys` bo RIGHT JOIN beauty b ON b.boyfriend_id = bo.id WHERE bo.id IS NULL; 判断条件一般是 id 需求: 查询那个部门没有员工 SELECT d.department_id, e.employee_id, e.last_name FROM `departments` d LEFT JOIN `employees` e ON d.department_id = e.department_id WHERE e.employee_id IS NULL; 判断条件一般是id 交叉连接 交叉连接就是为了实现笛卡尔积的，用的不多。 select b.*,bo.* from beauty b CROSS join boys bo 连接总结 无论是外连接还是内连接,select * 的时候都会把两个表的所有字段都会显示出来的。
7、 mysql 进阶7 子查询
含义: 出现在其他语句中的select语句,称为子查询，或内查询。子查询一般放在小括号内 外部的查询语句称为主查询或者外查询 特点: 1、子查询放在小括号呢 2、子查询一般放在条件的右侧(where/having) 3、标量子查询一般搭配着 条件运算符 > < = >= <= <> 使用 4、列子查询 一般搭配着多行操作符使用 如 in、any/some,all 5、子查询的执行顺序比主查询早 非法使用标量子查询的情况 1、只要是子查询的结果不是单行单列，都是非法的。 分类: 按子查询出现的位置 select 后面: 仅仅支持标量子查询 from 后面 支持表子查询 where 或 having 后面 标量子查询 列子查询 行子查询 exists 后面 表子查询 按结果集的行列数的不同 标量子查询(结果集只有一行一列) 列子查询(结果集只有一列多行) 行子查询(结果集一行多列) 表子查询(结果集多列多行) where 或者 having的后面 标量子查询(结果集只有一行一列) 列子查询(结果集只有一列多行) 一般搭配着多行操作符使用 如 in、any/some,all 标量子查询 需求:谁的工资比abel高 1、查询abel的工资 select salary from `employees` where last_name = 'Abel'; 2、查询员工的信息，满足 salary > 1的结果 SELECT * FROM `employees` WHERE salary > ( SELECT salary FROM `employees` WHERE last_name = 'Abel' ) 需求: 返回job_id 与141号员工相同，salary 比143号员工多的员工 姓名，job_id,和 工资 1、查询 141 号员工的job_id select job_id from `employees` where employee_id = 141 2、查询 143号员工的 salary SELECT salary FROM `employees` WHERE employee_id = 143 3、结果汇总 SELECT last_name, job_id, salary FROM `employees` WHERE job_id = ( SELECT job_id FROM `employees` WHERE employee_id = 141 ) AND salary > ( SELECT salary FROM `employees` WHERE employee_id = 143 ); 需求: 返回公司工资最少的员工的last_name,job_id和salary 1、查询最小的工资 select min(salary) from `employees` 2、汇总 select last_name,job_id,salary from `employees` where salary = (select min(salary) from `employees`); 需求: 查询最低工资大于50号部门最低工资的部门id和其最低工资 1、查询 50号部门的最低工资 select min(salary) from `employees` where department_id = 50 2、判断 部门id和其最低工资 select department_id,min(salary) from `employees` GROUP BY department_id HAVING MIN(salary) > ( select min(salary) from `employees` where department_id = 50); 列子查询 一般搭配着多行操作符使用 如 in、any/some,all all的意思是 和子查询返回值所有值比较 in / not in 等于强调等于，和列表中的任意一个比较 any /some 和子查询中的任意一个值比较 in 等价于 =any 需求: 返回location_id 是1400 或 1700 的部门中 的所有员工姓名 1、查询loaction_id 是1400 或者 1700 的部门id select department_id from departments where location_id in(1400,1700) 2、查询员工姓名 要求部门编号 是 1 列表中的一个 select last_name ,department_id from employees where department_id in( select department_id from departments where location_id in(1400,1700)) 需求: 返回其他部门中 比 job_id 为‘IT_PROG’ 部门任一工资低的员工的工号、姓名、job_id、以及salary select last_name,employee_id,salary,job_id,department_id from employees where salary <ANY( select DISTINCT salary from employees where job_id = 'IT_PROG') and department_id <> (select DISTINCT department_id from employees where job_id = 'IT_PROG') 这里注意 <ANY的用法 需求: 返回其他部门中 比 job_id 为‘IT_PROG’ 部门所有工资低的员工的工号、姓名、job_id、以及salary 1、查询job_id 是 ‘IT_PROG’ 的 部门id select DISTINCT department_id from employees where job_id = 'IT_PROG' 2、查询部门是 1 查询结果的 工资 select DISTINCT salary from employees where department_id in (select DISTINCT department_id from employees where job_id = 'IT_PROG') 3、查询工资比 2 查询的工资都低的 员工的信息，并且部门也是一个条件 select last_name,employee_id,salary,job_id,department_id from employees where salary <ALL( select DISTINCT salary from employees where department_id in (select DISTINCT department_id from employees where job_id = 'IT_PROG')) and department_id <> (select DISTINCT department_id from employees where job_id = 'IT_PROG') 这里注意 <all的用法 行子查询，用的很少 select 后面的查询之标量子查询 select 子查询仅仅支持标量子查询 标量子查询 需求: 查询每个部门的部门信息和员工个数 可能有的部门在员工表里面没有记录 SELECT d.*, ( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id ) 个数 FROM departments d; 这个sql要着重的理解下，它不违反 select 后面跟的结果是一个标量子查询 如果不这样写，别的sql 写起来比较的麻烦,查询employees 表每个部门员工的时候 条件是departments 表当前行的 department_id 其实我更习惯于下面的sql SELECT d.*, IF ( e.department_id IS NULL, 0, COUNT(*) ) 个数 FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id 需求: 查询员工号=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 后面跟子查询 from 后面 一般是 表子查询，相当于子查询的结果集当作主查询的数据源，并且一般结果集需要重新起一个别名 需求: 查询每个部门的平均工资的工资等级 SELECT ROUND(avg(salary),2) 平均工资, CASE WHEN ROUND(avg(salary),2) BETWEEN (select lowest_sal from job_grades where grade_level = 'A') and (select highest_sal from job_grades where grade_level = 'A') THEN 'A' WHEN ROUND(avg(salary),2) BETWEEN (select lowest_sal from job_grades where grade_level = 'B') and (select highest_sal from job_grades where grade_level = 'B') THEN 'B' WHEN ROUND(avg(salary),2) BETWEEN (select lowest_sal from job_grades where grade_level = 'C') and (select highest_sal from job_grades where grade_level = 'C') THEN 'C' WHEN ROUND(avg(salary),2) BETWEEN (select lowest_sal from job_grades where grade_level = 'D') and (select highest_sal from job_grades where grade_level = 'D') THEN 'D' WHEN ROUND(avg(salary),2) BETWEEN (select lowest_sal from job_grades where grade_level = 'E') and (select highest_sal from job_grades where grade_level = 'E') THEN 'E' ELSE 'F' END '工资级别' ,department_id FROM `employees` group by department_id; 另一种写法 1、查询每个部门的平均工资，查询工资等级表的信息 select ROUND(avg(salary),2),department_id from employees group by department_id select * from `job_grades` 2、结果1 和job_grades表进行非等值连接 select ag_dep.ag,j.grade_level, ag_dep.department_id FROM ( select ROUND(avg(salary),2) ag,department_id from employees group by department_id ) ag_dep INNER JOIN job_grades j on ag_dep.ag BETWEEN j.lowest_sal and j.highest_sal exists 后面跟子查询 语法 exits(完整的子查询，子查询无论是多行多列还是单行单列) exits的结果只有两种一个是 1 或者0 有结果 返回值就是1，没有结果 exists语法返回的结果是0 如下面的 SELECT exists(select * from employees where salary = 300000) result; 显示 是 0,mysql 中 结果是1 表示 为true ,0 表示 false 特点: 1、exits 的子查询和别的子查询是有区别的，别的子查询是先执行子查询，然后主查询用到了子查询的结果 而exits 是先执行主查询，然后，查询的结果根据exists里面的结果来过滤 2、exists 绝对可以使用in 或者 not in来代替 exists 又叫相关子查询的理解 exists 里面的查询 相关连到了主查询里面的字段 所以叫相关子查询。 一般是关联查询
exists 子查询的语法
select ... from 表名 where exists(子查询)
exists 的位置
一般在 where 后面
理解
如果 exists 子查询有结果，就是true,主查询当前行就显示
如果 exists 子查询没有结果，就是true,主查询当前行就不显示
主查询的 根据 exists 子查询的结果来过滤,exists 子查询的结果
无论是什么值都行，只要有结果
注意点:
exists 子查询里面不能 写 恒成立的 例如 1 = 1
必须是一个查询的语句 哪怕是 select 1 或者 select 0。
需求: 查询各部门中工资比本部门平均工资高的员工的员工号，姓名，和工资 sql 1 select employee_id,last_name,salary FROM employees e where salary > ( select avg(salary) from employees d where d.department_id = e.department_id ) sql 2 select e.department_id,employee_id,last_name,salary from employees e INNER join ( select avg(salary) ag,department_id from employees GROUP BY department_id) dep on e.department_id = dep.department_id where e.salary > dep.ag 要特别的理解 where 条件 需求: 查询没有女朋友的男神信息 select bo.* from boys where not exists( select boyfriend_id from beauty b where bo.id = b.boyfriend_id )
8、Mysql 进阶8 分页查询
关键词 limit 【起始索引，size】 从那里开始截取，截取的个数,起始索引从0 开始。 需求: 查询前5条员工信息 select * from employees LIMIT 0,5 需求: 查询第11条-第25条 数据 select * from employees LIMIT 10,15 需求: 查询平均工资最低的部门信息 1、查询平均工资 然后 按照平均工资正排序 select AVG(salary) as avg ,department_id from employees GROUP BY department_id ORDER BY avg ASC 2、获取 部门编号 select department_id from employees GROUP BY department_id ORDER BY AVG(salary ASC LIMIT 1 3、根据部门id获取部门信息 SELECT * FROM departments where department_id = ( select department_id from employees GROUP BY department_id ORDER BY AVG(salary) ASC LIMIT 1) 4、这里要体会 limit和order by 配合使用
9、Mysql 进阶9 联合查询
关键字: union,将多条查询语句的结果合并成一个结果 需求: 查询部门编号>90 或邮箱包含a的员工信息 方式1: select * from employees where department_id > 90 OR email like '%a%' 方式2: select * from employees where department_id > 90 UNION select * from employees where email like '%a%' 特点: union 操作符用于合并两个或多个SELECT语句的结果。 请注意使用UNION的注意点 1、内部的每个SELECT 的结果必须拥有相同数量的列。 2、列也必须拥有相似的数据类型。 3、同时每个SELECT语句中的列的顺序必须相同。 这个跟原始表的字段没有关系，只是查询后的结果每个SELECT 都可以加 条件的。 UNION 会把两个SELECT查询的结果进行去重，比如说学生表里面有学号，成绩表也有学号，使用union的时候 会去重，如果单独查成绩表的学号会有好多重复的。 应用场景: 要查询的结果来自与多个表，且多个表没有直接的连接关系，但查询的结果一致时可以用union 需求:查询 学生表和成绩表的学生成绩 select s_id from student UNION select s_id from score UNION ALL 不会进行去重，会把两个结果集进行合并 select s_id from student UNION ALL select s_id from score select s_id from student 查出来的是 8条数据 select s_id from score 查出来的是 18条数据 使用UNION ALL 查询出来的结果是 26条数据 UNION ALL 的效率比较好。
10、MySQL变量的学习
MySql 变量的学习 1、系统变量 1、全局变量 1、概述 全局变量在MySQL启动时由服务器自动将他们初始化为默认值，这些默认值可以哦天内故宫修改my.ini文件的设置来实现。 2、作用域 服务器每次启动都将为所有的全局变量赋初始值，针对于所有会话有效，但是不能跨越重启，这里的重启是指MySQL服务器的重新启动。如果要想每次启动也修改，则需要修改配置文件。 3、常用命令 查看所有的全局变量 show GLOBAL VARIABLES; ==> 5.7 505 个系统变量 查看满足条件的部分系统变量 show global variables like 'character_set%'; 查看指定的系统变量的值 select @@global.autocommit; 2、会话变量 1、概述 会话变量在每次建立一个新连接时，由MySQL初始化,MySQL会将当前所有全局变量的值复制一份作为会话变量。 2、作用域 仅对当前会话(连接)有效，当前会话中断则作用域失效。 3、常用命令 查看所有的会话变量 show session variables; 查看满足条件的部分会话变量 show session variables like '%char%'; 查看指定的会话变量的值 select @@autocommit; 2、自定义变量 1、用户变量 1、概述 用户变量与用户相关，当以某个用户账号登录MySQL服务器时便与服务器建立了一个会话，因此用户变量是基于会话变量来实现的。 2、作用域 仅对当前连接(会话)有效，作用域等同于会话变量。 3、用户变量的基本规范: 1、用户变量的赋值操作符: = 或者 := 2、用户变量的声明和赋值四种方式 set @变量名 = 值 set @变量名 := 值 select @变量 := 值; select 只能用:= select 字段 into @变量名 from 表; 把表中的某个字段的值放到变量里,字段的值必须是一个 3、查看变量的值 select @变量名 2、变量的练习 声明两个变量 m和 n 并均赋值为1,把m和n的值加和并赋值给新声明的变量 sum,显示 sum的值。 select @sum := @m + @n as sum from (select @m:=1) m,(select @n := 1) as n;
