mysql 99语法_Mysql学习笔记（009）-sql99语法及相关连接查询

1 #二、sql99语法2 /*

3 语法：4 select 查询列表5 from 表1 别名【连接类型】6 join 表2 别名7 on 连接条件8 【where 筛选条件】9 【group by 分组】10 【having 筛选条件】11 【order by 排序列表】12 内连接(☆)：inner13 外连接14 左外(☆)left【outer】15 右外(☆)：right【outer】16 全外：full【outer】17 交叉连接：cross18 */

19 #一)内连接20 /*

21 语法：22 select 查询列表23 from 表1 别名24 inner join 表2 别名25 on 连接条件；26 分类：27 等值28 非等值29 自连接30

31 特点:32 ①添加排序、分组、筛选33 ②inner可以省略34 ③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读35 ④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集36

37 */

38

39 #1等值连接40 #案例1.查询员工名、部门名(调换位置)41 SELECTlast_name,department_name42 FROMemployees e43 INNER JOINdepartments d44 ON e.`department_id`=d.`department_id`45

46 #案例2.查询名字中包含e的员工名和工种名(筛选)47 SELECTlast_name,job_title48 FROMemployees e49 INNER JOINjobs j50 ON e.`job_id` =j.job_id51 WHERE e.`last_name` LIKE '%e%';52 #案例3.查询部门个数>3的城市名和部门个数，(添加分组+筛选)53 #①查询每个城市的部门个数54 #②在①结果上筛选满足条件的55 SELECT city,COUNT(*)56 FROMdepartments d57 INNER JOINlocations l58 ON d.`location_id`=l.`location_id`59 GROUP BYcity60 HAVING COUNT(*)>3;61

62 #案例4.查询哪个部门的部门员工数>3的部门名和员工个数，并按个数降序(排序)63 #①查询每个部门的员工个数64 SELECT COUNT(*),department_name65 FROMemployees e66 INNER JOINdepartments d67 ON e.`department_id`=d.`department_id`68 GROUP BYdepartment_name69 #②在①的结果上筛选员工个数>3的记录，并筛选70 SELECT COUNT(*),department_name71 FROMemployees e72 INNER JOINdepartments d73 ON e.`department_id`=d.`department_id`74 GROUP BYdepartment_name75 HAVING COUNT(*)>3

76 ORDER BY COUNT(*) DESC;77

78

79 #5.查询员工名、部门名、工种名、并按部门名降序80 SELECTlast_name,department_name,job_title81 FROMemployees e82 INNER JOIN departments d ON e.`department_id`=d.`department_id`83 INNER JOIN jobs j ON e.`job_id`=j.`job_id`84 ORDER BY department_name DESC;85

86

87 #二)非等值连接88 #查询员工的工资级别、89 SELECTsalary,grade_level90 FROMemployees e91 JOINjob_grades g92 ON e.`salary` BETWEEN g.`lowest_sal` ANDg.`highest_sal`;93

94 #查询每个级别的个数>20的个数，并且按工资级别降序95 SELECTsalary,grade_level96 FROMemployees e97 JOINjob_grades g98 ON e.`salary` BETWEEN g.`lowest_sal` ANDg.`highest_sal`99 GROUP BYgrade_level100 HAVING COUNT(*)>20

101 ORDER BY grade_level DESC;102

103 #三)自连接104 #查询员工的名字，上级的名字105 SELECTe.last_name,m.last_name106 FROMemployees e107 INNER JOINemployees m108 ON e.`manager_id`=m.`employee_id`109

110 #查询姓名中包含字符k的员工的名字，上级的名字111 SELECTe.last_name,m.last_name112 FROMemployees e113 INNER JOINemployees m114 ON e.`manager_id`=m.`employee_id`115 WHERE e.`last_name` LIKE '%k%'

116

117 #二、外连接118 /*

119 应用场景：用于查询一个表中有，另一表中没有的记录120

121 特点：122 1、外连接的查询结果为主表中的所有记录123 如果从表中有和它匹配的，则显示匹配的值124 如果从表中没有和它匹配的，则显示null125 外连接的查询结果=内连接结果+主表中有而从表中没有的记录126 2、左外链接，left左边的是主表127 右外连接，right join右边的是主表128 3、左外和右外交换两个表的顺序，可以实现同样的效果129 4、全外连接=内连接的结果+表1中有但表2中没有的+表中有但表1没有的130

131 */

132 #引入：查询男朋友不在男神表的女神名133 USEgirls;134 SELECT * FROMbeauty;135 SELECT * FROMboys;136

137 #左外链接138 SELECT b.name,bo.*

139 FROMbeauty b140 LEFT OUTER JOINboys bo141 ON b.`boyfriend_id`=bo.`id`142 WHERE bo.id IS NULL;143

144 UPDATE boys SET usercp=NULL WHERE id=3;#最好选择从表中的主键列145

146 #右外连接147 SELECT b.name,bo.*

148 FROMboys bo149 RIGHT OUTER JOINbeauty b150 ON b.`boyfriend_id`=bo.`id`151 WHERE bo.id IS NULL;152

153

154 #案例1：插叙哪个部门没有员工155 #左外156 USEmyemployees;157 SELECT d.*,employee_id158 FROMdepartments d159 LEFT OUTER JOINemployees e160 ON d.`department_id`=e.`department_id`161 WHERE e.`employee_id` IS NULL

162

163 #右外164 USEmyemployees;165 SELECT d.*,employee_id166 FROMemployees e167 RIGHT OUTER JOINdepartments d168 ON d.`department_id`=e.`department_id`169 WHERE e.`employee_id` IS NULL

170

171

172

173 #全外174 USEgirls;175 SELECT b.*,bo.*

176 FROMbeauty b177 FULL OUTER JOINboys bo178 ON b.'boyfriend_id'=bo.id;179 ##效果：会将交集查出来加上从表中的所有记录，不分主从表180

181

182 #交叉连接183 SELECT b.*,bo.*

184 FROMbeauty b185 CROSS JOINboys bo;186

187 #sql92和sql99pk188 功能：sql99支持的较多189 可读性：sql99实现连接条件和筛选条件的分离，可读性较高