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 内连接（☆）：inner
 13 外连接
 14     左外（☆）left【outer】
 15     右外（☆）：right【outer】
 16     全外：full【outer】
 17 交叉连接：cross
 18 */
 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 SELECT last_name,department_name
 42 FROM employees e
 43 INNER JOIN departments d
 44 ON e.`department_id`=d.`department_id`
 45 
 46 #案例2.查询名字中包含e的员工名和工种名（筛选）
 47 SELECT last_name,job_title
 48 FROM employees e
 49 INNER JOIN jobs j
 50 ON e.`job_id` = j.job_id
 51 WHERE e.`last_name` LIKE ‘%e%‘;
 52 #案例3.查询部门个数>3的城市名和部门个数，（添加分组+筛选）
 53 #①查询每个城市的部门个数
 54 #②在①结果上筛选满足条件的
 55 SELECT city,COUNT(*)
 56 FROM departments d
 57 INNER JOIN locations l
 58 ON d.`location_id`=l.`location_id`
 59 GROUP BY city
 60 HAVING COUNT(*)>3;
 61 
 62 #案例4.查询哪个部门的部门员工数>3的部门名和员工个数，并按个数降序（排序）
 63 #①查询每个部门的员工个数
 64 SELECT COUNT(*),department_name
 65 FROM employees e
 66 INNER JOIN departments d
 67 ON e.`department_id`=d.`department_id`
 68 GROUP BY department_name
 69 #②在①的结果上筛选员工个数>3的记录，并筛选
 70 SELECT COUNT(*),department_name
 71 FROM employees e
 72 INNER JOIN departments d
 73 ON e.`department_id`=d.`department_id`
 74 GROUP BY department_name
 75 HAVING COUNT(*)>3
 76 ORDER BY COUNT(*) DESC;
 77 
 78 
 79 #5.查询员工名、部门名、工种名、并按部门名降序
 80 SELECT last_name,department_name,job_title
 81 FROM employees e
 82 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 SELECT salary,grade_level
 90 FROM employees e
 91 JOIN job_grades g
 92 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
 93 
 94 #查询每个级别的个数>20的个数，并且按工资级别降序
 95 SELECT salary,grade_level
 96 FROM employees e
 97 JOIN job_grades g
 98 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 99 GROUP BY grade_level
100 HAVING COUNT(*)>20
101 ORDER BY grade_level DESC;
102 
103 #三）自连接
104 #查询员工的名字，上级的名字
105 SELECT e.last_name,m.last_name
106 FROM employees e
107 INNER JOIN employees m
108 ON e.`manager_id`=m.`employee_id`
109 
110 #查询姓名中包含字符k的员工的名字，上级的名字
111 SELECT e.last_name,m.last_name
112 FROM employees e
113 INNER JOIN employees m
114 ON e.`manager_id`=m.`employee_id`
115 WHERE e.`last_name` LIKE ‘%k%‘
116 
117 #二、外连接
118 /*
119 应用场景：用于查询一个表中有，另一表中没有的记录
120 
121 特点：
122 1、外连接的查询结果为主表中的所有记录
123     如果从表中有和它匹配的，则显示匹配的值
124     如果从表中没有和它匹配的，则显示null
125     外连接的查询结果=内连接结果+主表中有而从表中没有的记录
126 2、左外链接，left左边的是主表
127    右外连接，right join右边的是主表
128 3、左外和右外交换两个表的顺序，可以实现同样的效果
129 4、全外连接=内连接的结果+表1中有但表2中没有的+表中有但表1没有的 
130 
131 */
132 #引入：查询男朋友不在男神表的女神名
133 USE girls;
134 SELECT * FROM beauty;
135 SELECT * FROM boys;
136 
137 #左外链接
138 SELECT b.name,bo.*
139 FROM beauty b
140 LEFT OUTER JOIN boys bo
141 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 FROM boys bo
149 RIGHT OUTER JOIN beauty b
150 ON b.`boyfriend_id`=bo.`id`
151 WHERE bo.id IS NULL;
152 
153 
154 #案例1：插叙哪个部门没有员工
155 #左外
156 USE myemployees;
157 SELECT d.*,employee_id
158 FROM departments d
159 LEFT OUTER JOIN employees e
160 ON d.`department_id`=e.`department_id`
161 WHERE e.`employee_id` IS NULL
162 
163 #右外
164 USE myemployees;
165 SELECT d.*,employee_id
166 FROM employees e
167 RIGHT OUTER JOIN departments d
168 ON d.`department_id`=e.`department_id`
169 WHERE e.`employee_id` IS NULL
170 
171 
172 
173 #全外
174 USE girls;
175 SELECT b.*,bo.*
176 FROM beauty b
177 FULL OUTER JOIN boys bo
178 ON b.‘boyfriend_id‘= bo.id;
179 ##效果：会将交集查出来加上从表中的所有记录，不分主从表
180 
181 
182 #交叉连接
183 SELECT b.*,bo.*
184 FROM beauty b
185 CROSS JOIN boys bo;
186 
187 #sql92和sql99pk
188 功能：sql99支持的较多
189 可读性：sql99实现连接条件和筛选条件的分离，可读性较高