1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
#一、等值连接
/*
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
#案例2：查询员工名和对应的部门名
USE myemployees;
SELECT first_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名
SELECT e.first_name, e.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id;
#3、两个表的顺序可以调换
#查询员工名、工种号、工种名
SELECT e.first_name, e.job_id, j.job_title
FROM jobs j, employees e
WHERE e.job_id = j.job_id;
#4、可以加筛选
#案例1：查询有奖金的员工名、部门名
SELECT e.first_name, d.department_name, commission_pct
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND commission_pct IS NOT NULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT d.department_name, l.city
FROM departments d, locations l
WHERE d.location_id = l.location_id
AND l.city LIKE '_o%';
#5、可以加分组
#案例1：查询每个城市的部门个数
SELECT COUNT(*) AS '个数', city
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY city;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name, d.manager_id, MIN(salary)
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND commission_pct IS NOT NULL
GROUP BY d.department_name;
#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title, COUNT(*) AS '个数'
FROM employees e, jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY 个数 DESC;
#7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
SELECT first_name, department_name, city
FROM departments d, employees e, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city LIKE 's%'
ORDER BY department_name DESC;
#二、非等值连接
#案例1：查询员工的工资和工资级别
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES
('A', 1000, 2999),
('B', 3000, 5999),
('C', 6000, 9999),
('D', 10000, 14999),
('E', 15000, 24999),
('F', 25000, 40000);
SELECT first_name, salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='B';
#三、自连接
#案例：查询 员工名和上级的名称
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`;
|