连接查询
1 #进阶6：连接查询
2 /*
3 含义：又称为多表查询，当查询的字段来自多个表或者涉及到多个表时，就会用到连接查询
4
5 笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
6 发生原因：没有有效的连接条件
7 如何避免：添加有效的连接条件
8
9 分类：
10 按年代分类：
11 sql92标准：仅仅支持内连接
12 sql99标准【推荐】：支持内连接+外连接+（左外和右外）+交叉连接
13
14 按功能分类：
15 内连接：
16 等值连接
17 非等值连接
18 自连接
19 外连接：
20 左外链接
21 右外连接
22 全外连接
23 交叉连接：
24
25
26 */
27
28 SELECT *FROM beauty;
29
30 SELECT *FROM boys;
31
32 SELECT NAME,boyName FROM boys,beauty;#产生笛卡尔乘积现象
33
34
35 SELECT NAME,boyName FROM boys,beauty
36 WHERE beauty.`boyfriend_id`=boys.id;
37
38 #一、sql92标准
39 #1、等值连接
40
41 /*
42 ①多表等值连接的结果为多表的交际部分
43 ②n表连接，至少需要n-1个连接条件
44 ③多表顺序没有要求
45 ④一般要为多表起别名
46 ⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
47
48 */
49
50
51
52 #案例1：查询女神名和对应的男神名
53 SELECT
54 NAME,
55 boyName
56 FROM
57 boys,
58 beauty
59 WHERE beauty.`boyfriend_id` = boys.id;
60 #案例2：查询部门名和对应的部门名
61 USE myemployees;
62 SELECT last_name,department_name
63 FROM employees,departments
64 WHERE employees.`department_id`=departments.`department_id`
65
66 #2、为表起表名
67 /*
68 ①提高语句的简洁度
69 ②区分重名的字段
70
71 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
72
73 */
74
75 #要查询员工名、工种号、工种名
76 SELECT last_name,e.job_id,job_title
77 FROM employees AS e,jobs j #先从表查起，生成表别名视图，select语句就不认识原有的表名了
78 WHERE e.`job_id`=j.`job_id`
79
80 #3、两个表的顺序是否可以调换
81 #查询员工名、工种号、工种名
82 SELECT last_name,e.job_id,job_title
83 FROM jobs j,employees AS e
84 WHERE e.`job_id`=j.`job_id`
85
86
87 #4、可以加筛选？
88 #案例：查询有奖金的员工名、部门名`myemployees`
89 SELECT last_name,department_name,commission_pet
90 FROM employees e,departments d
91 WHERE e.department_id = d.department_id
92 AND e.commission_pet IS NOT NULL;
93
94 #案例2：查询城市名称第二个字符为o的部门名和城市名
95 SELECT department_name,city
96 FROM departments d,locations l
97 WHERE d.`location_id`= l.`location_id`
98 AND city LIKE '_o%';
99
100 #5、可以加分组？
101
102 #案例1：查询每个城市的部门的个数
103 SELECT COUNT(*) 个数,city
104 FROM departments d,locations l
105 WHERE d.`location_id`=l.`location_id`
106 GROUP BY city;
107
108 #案例2：查询出有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
109 SELECT department_name,d.`department_id`,MIN(salary)
110 FROM employees e,departments d
111 WHERE e.`department_id`=d.`department_id`
112 AND commission_pet IS NOT NULL
113 GROUP BY department_name,d.`department_id`
114
115 #6.可以加排序
116 #案例：查询每个工种名和员工的个数，并且按员工个数降序
117 SELECT job_title,COUNT(*)
118 FROM employees e,jobs j
119 WHERE e.`job_id`=j.job_id
120 GROUP BY job_title
121 ORDER BY COUNT(*) DESC;
122
123 #7、可以实现三表连接？
124 #案例：查询员工名、部门名和所在城市
125 SELECT last_name,department_name,city
126 FROM employees e,departments d,locations l
127 WHERE e.`department_id`=d.`department_id`
128 AND d.`location_id`=l.`location_id`
129 AND city LIKE 's%'
130
131 ORDER BY department_id
132
133
134
135 #2、非等值连接
136
137 #案例1：查询员工的工资和工资级别
138
139 SELECT salary,grade_level
140 FROM employees e,job_grades g
141 WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
142 AND grade_level='A';
143
144 #3、自连接
145
146 #案例：查询员工和上级名称
147 SELECT e.employee_id,e.last_name,m.`manager_id`
148 FROM employees e,employees m
149 WHERE e.employee_id=m.`manager_id`
150
151 SELECT MD5('李白');
练习及准备
/*
create database girls charset=utf8;
use girls;
create table beauty(
id int(6) PRIMARY KEY not null AUTO_INCREMENT,
name varchar(12),
sex varchar(3),
borndate datetime,
phone varchar(11),
photo longtext,
boyfriend_id int(6)
)
drop table beauty
alter table beauty convert to character set utf8mb4 collate utf8mb4_bin;
insert into beauty value(0,'柳岩','女','1987-12-30','18209876577',NULL,8);
INSERT INTO beauty VALUE(0,'苍老师','女','1987-12-30','18219876577',NULL,9);
INSERT INTO beauty VALUE(0,'Angelababy','女','1989-02-03','18209876567',NULL,3);
INSERT INTO beauty VALUE(0,'热巴','女','1993-02-03','18209876579',NULL,2);
INSERT INTO beauty VALUE(0,'周冬雨','女','1992-02-03','18209179577',NULL,9);
INSERT INTO beauty VALUE(0,'周芷若','女','1988-02-03','18209876577',NULL,1);
INSERT INTO beauty VALUE(0,'岳灵珊','女','1987-12-30','18219876577',NULL,9);
INSERT INTO beauty VALUE(0,'小昭','女','1988-02-03','18209876577',NULL,1);
INSERT INTO beauty VALUE(0,'双儿','女','1993-02-03','18209876579',NULL,9);
INSERT INTO beauty VALUE(0,'王语嫣','女','1992-02-03','18209179577',NULL,4);
INSERT INTO beauty VALUE(0,'夏雪','女','1993-02-03','18209876579',NULL,9);
INSERT INTO beauty VALUE(0,'赵敏','女','1992-02-03','18209179577',NULL,1);
SELECT * FROM beauty
create table boys(
id INT(6) PRIMARY KEY NOT NULL AUTO_INCREMENT,
boyName varchar(12),
userCP int(6)
)
ALTER TABLE boys CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
insert into boys value(0,'张无忌',100);
insert into boys value(0,'鹿晗',800);
INSERT INTO boys VALUE(0,'黄晓明',50);
INSERT INTO boys VALUE(0,'段誉',300);
select *from boys;
select name,boyName from boys,beauty;
create table job_grades(
grade_level varchar(3),
lowest_sal int,
highest_sal int
);
insert into job_grades
values('A',1000,2999);
insert into job_grades
values('B',3000,5999);
insert into job_grades
values('C',6000,9999);
insert into job_grades
values('D',10000,14999);
insert into job_grades
values('E',15000,24999);
insert into job_grades
values('F',25000,40000);
*/
一、显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary) FROM employees;
二、查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序
SELECT employee_id,job_id,last_name FROM employees ORDER BY deparment_id DESC,salary ASC;
三、查询员工表的job_id中包含a和e的，并且a在e的前面
SELECT job_id FROM employees WHERE job_id LIKE '%a%e%'
四、已知表student，里面有id（学号），name，gradeId（年级编号）
已知表grade，里面有id（年级编号），name（年级名）
已知result，里面有id,score,studentNo（学号）
要求查询姓名，年级名，成绩
SELECT s.name,g.name,score FROM student s,grade g,result r
WHERE s.id=r.studentNo
AND g.id=s.gradeId
五、显示当前日期，以及去前后空格，截取字符串的函数
SELECT NOW()
SELECT TRIM(字符 FROM '');
SELECT SUBSTR(str,startIndex)
SELECT SUBSTR(str,startIndex,LENGTH)
小结