MySQL数据库——连接查询
#进阶6：连接查询 /* 含义：又称多表查询，当查询字段来自多个表，就会用到连接查询 笛卡尔乘积：表1 m行 表2 n行 结果为m*n行 发生原因：没有有效的连接条件 解决方法：添加有效的连接条件 分类： 1.按年代分类 sql92标准：仅仅支持内连接 sql99标准：【推荐】支持所有内连接+外连接（左外和右外）+全外连接 2.按功能分类 内连接 等值连接 非等值连接 自连接 外连接 左外连接 右外连接 全外连接 交叉连接 */ SELECT * FROM beauty; SELECT * FROM boys; SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id = boys.id; #一、sql92标准 #1.等值连接 #案例1：查询女神名字和对应的男神名字 SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id = boys.id; #拿着第一张表的每一行去匹配第二张表的每一行，进行筛选 #案例2.查询员工名和对应的部门名字 SELECT last_name,department_name FROM employees,departments WHERE employees.`department_id` = departments.`department_id`; #2.为表起别名 #好处：提高语句简洁度，区分多个重名的字段 #案例3：查询工种号、员工号、工种名 #注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定 SELECT last_name,e.`job_id`,job_title#如果还用表名限定，就不好使了，因为执行顺序的问题！ FROM employees AS e,jobs AS j#位置换了也没关系 WHERE e.`job_id` = j.`job_id`; #3.两个表的顺序是否可以调换 #可以交换！ #4.可以加筛选！ #案例4：查询有奖金的员工名和部门名 SELECT last_name,department_name FROM employees e,departments d WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL; #案例5：查询城市名中第二个字符为o的部门名和城市名 SELECT department_name,city FROM departments d,locations l WHERE d.`location_id` = l.`location_id` AND city LIKE '_o%'; #5.加分组 #案例6：查询每个城市的部门个数 SELECT COUNT(*) 个数,city FROM departments d,locations l WHERE d.`location_id` = l.`location_id` GROUP BY city; #案例7：查询有奖金的每个部门的部门名和部门的领导编号以及该部门的最低编号 SELECT department_name,d.`manager_id`,MIN(salary) FROM employees e,departments d WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL GROUP BY department_name; #6.加排序 #案例8：查询每个工种的工种名和员工的个数并且按员工个数降序 SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.`job_id` = j.`job_id` GROUP BY job_title ORDER BY COUNT(*) DESC; #7.实现三表连接 #案例9：查询员工名部门名和所在的城市 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`; #总结： /* 1.多表等值连接的结果为多表的交集部门 2.n表连接至少需要n-1个链接条件 3.多表的顺序没有要求 4.一般需要为表起别名 5.可以搭配前面介绍的所有查找子句，比如排序筛序分组等 */
#2.非等值连接 #案例1：查询员工工资和工资级别 SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`; #也可以加筛选和排序 #将上面的查询只显示A等级的，并且按照工资降序排序 SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` AND g.`grade_level` = 'A' ORDER BY salary ASC; #3.自连接 #相当于等值连接，涉及到的表只有自己，也不是每个都可以做 #案例2：查询 员工名和上级的名称 SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e,employees m#相当于一个员工表一个领导表 WHERE e.`manager_id` = m.`employee_id`; 总结： 排序查询 一、语法 SELECT 查询列表 FROM 表 WHERE 筛选条件 ORDER BY 排序列表 二、特点 ASC：升序，默认 DESC：降序 排序列表支持单个字段 排序列表支持多个字段 也支持函数表达式别名 ORDER BY一般放在查询语句最后，但是LIMIT除外 常见函数 一、概述 功能：类似于Java方法 好处：提高代码重用性和隐藏实现细节 调用：SELECT 函数名（实参列表）； 二、单行函数 1.字符函数 CONCAT连接 SUBSTR截取子串 UPPER、LOWER REPLACE LENGTH TRIM去前后空格 LPAD左填充 RPAD右填充 INSTR获取子串第一次出现的索引 三、数学函数 CEIL:向上取整 ROUND：四舍五入 MOD：取模 FLOOR：向下取整 TRUNCATE：截断 RAND：获取随机数，默认返回0-1之间小数，到不了1 四、日期函数 NOW返回当前日期+时间 YEAR返回年 MONTH返回月 DAY：返回日 DATE_FORMAT：将日期转换成字符 CURDATE:返回当前日期 CURTIME:返回当前时间 STR_TO_DATE:将字符按照格式解析成日期 HOUR：返回当前时间 MINUTE SECOND DATEDIFF:返回日期相差的天数 MONTHNAME：以英文形式返回月 五、其他函数 VERSION：当前版本号 DATABASE:当前打开数据库 USER PASSWORD(‘字符’)自动加密 MD5('字符')自动加密 六、流程控制函数 IF(条件表达式，表达式1，表达式2）:如果条件表达式成立返回表达式1否则返回2 CASE的使用！ 七、分组函数 1.分类 MAX MIN AVG SUM COUNT 2.特点 语法 支持的类型：SUM和AVG一般处理数值型；其他支持任何类型 3.以上都忽略NULL值 4.都可以搭配DISTINCT去重统计 5.count函数 COUNT(字段)：统计该字段非空值的个数 COUNT(*)：统计结果集的行数 COUNT(1):相当于添加了一列值，都是1，也是统计行数 从效率上来讲： 和分组函数一起查询的字段，要求是GROUP BY之后出现的字段 八、分组查询 SELECT 分组函数,分组后的字段 FROM 表 [WHERE 筛选] GROUP BY 分组字段 [HAVING 分组后筛选] [ORDER BY 排序] #不管从书写还是执行都是最后 Oracle不支持HAVING和GROUP BY别名，所以我们也别用 分组前筛选和分组后筛选！ 使用关键字 筛选的表 位置 分组前筛选 WHERE 原始表 GROUP BY 前面 分组后筛选 HAVING 分组后的结果 GROUP BY 后面 九、连接查询 当查询中涉及到了多个表的字段 SELECT 字段1，字段2 FROM 表1，表2 笛卡尔乘积：当查询多个表时没有添加有效的连接条件，导致多个表实现完全连接 如果解决：添加有效的链接条件！ 1.分类： 按年代 sql92:等值，非等值，自连接，也支持一部门外连接（用于Oracle，sqlsever，MySQL不支持） sql99:推荐使用 内连接：等值、非等值、自连接 外连接：左外、右外、全外（MySQL不支持） 交叉连接 2.语法 等值连接： 非等值连接： 自连接： 3.特点： 一般为表起别名 多表顺序可以调换 n表连接至少N-1个条件 等值连接的结果是多表的交集
连接查询总结：
内连接
左外，A当主表
右外,B当主表
全外连接（Oracle支持）
#二、sql99语法 /* 语法： select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 【连接条件】 【where 筛选条件】 【group by 分组条件】 【having 筛选条件】 【order by 排序列表】 内连接（※）：inner 外连接 左外（※）：left 【outer】 右外（※）：right 【outer】 全外：full 【outer】 交叉连接：cross */ #一、内连接 /* select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件； 分类： 等值 非等值 自连接 等值连接——特点：添加排序、分组、筛选 inner可以省略 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读 inner join连接和sql92里头的连接实现的效果是一样的 */ #案例1：查询员工名、部门名 SELECT last_name,department_name FROM employees e INNER JOIN departments d ON e.`department_id` = d.`department_id`; #案例2：查询名字中包含a的员工名、工种名 SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e.`job_id` = j.`job_id` WHERE last_name LIKE '%a%'; #案例3：查询部门个数大于3的城市名和部门个数 SELECT city,COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY city#查询每个城市的部门个数 HAVING COUNT(*) > 3; #案例4：查询哪个部门的部门员工个数大于3的部门名和员工个数，并按个数降序 #①查询每个部门的部门员工个数 #②筛选和排序 SELECT department_name,COUNT(*) FROM departments d INNER JOIN employees e ON d.`department_id` = e.`department_id` GROUP BY department_name HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC; #案例5：查询员工名，部门名，工种名，并按部门名降序 SELECT last_name,department_name,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 department_name DESC; #2、非等值连接 #查询员工的工资级别 SELECT salary,grade_level FROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`; #查询每个工资级别的个数大于20并降序排序 SELECT COUNT(*),grade_level FROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` GROUP BY grade_level HAVING COUNT(*) > 20 ORDER BY grade_level DESC; #3、自连接 #查询员工的名字和上司名字 SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id` = m.`employee_id`; #外连接 #用于查询一个表中有另一个表中没有的记录 #特点：外连接的查询结果为主表中的全部记录 # 如果从表中有跟主表匹配的，显示匹配的值 # 如果从表中没有，显示null # 外连接查询结果相当于内连接结果+主表中有而从表中没有的记录 #左外连接：left join左边的是主表 #右外连接：right join右边的是主表 #左外和右外交换两个表的顺序可以实现同样的效果 #引入：查询男朋友不在男神表的女神名 SELECT b.name FROM beauty b#主表 LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id; SELECT b.name FROM boys bo RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bo.id; #案例：查询哪个部门没有员工 USE myemployees; #左外 SELECT d.`department_name` FROM departments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL; #右外 SELECT d.`department_name` FROM employees e RIGHT OUTER JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`employee_id` IS NULL; #三、全外连接 #不支持 #会将交集查出来，除此之外，还会将主表在从表没有的查出来填充null，除此之外，还能将从表没有，主表有的查出来填充，也就是没有主从表区分了 #全外=内连接结果+表1有表2没有+表2有表1没有 #四、交叉连接 #使用99语法标准实现笛卡尔乘积 USE girls; SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo; /* 总结： sql92 sql99 功能：99支持更多 可读性：99实现了连接和筛选的分离，可读性更高 */ #测试 #1.查询编号大于3的女神的男朋友信息，如果有则列出详细，没有用null填充 #左外 #2.查询哪个城市没有部门 SELECT l.`city` FROM locations l LEFT OUTER JOIN departments d ON l.`location_id` = d.`location_id` WHERE d.`department_id` IS NULL; #3.查询部门名为SAL或者IT的员工信息 #有可能没员工，所以用外连接 SELECT e.*,d.`department_name` FROM departments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE d.`department_name` IN( 'SAL','IT');
