MySQL连接查询——sql92语法

1、连接查询

          含义：当查询结果涉及到多个表的字段时，需要使用连接查询

          分类：

                    按年代分：sql92标准/sql99标准（通常使用sql99标准）

                    按功能分：内连接（等值连接、非等值连接、自连接）；外连接（左外连接、右外连接、全外连接）；交叉连接

          等值连接特点：

                    1）多表等值连接的结果为多表的交集部分

                    2）n表连接，至少需要n-1个连接条件

                    3）查询结果集与表的先后顺序无关

                    4）可以为表起别名

                    5）可以搭配所有子句的使用，例如：排序、分组、筛选等

2、分类（sql92标准）

          2.1  等值连接

                    2.1.1 查询女神名和对应的男神名

-- 案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

                    2.1.2  查询员工名和对应的部门名

-- 案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.employee_id = departments.department_id;

                    2.1.3  查询员工名、工种号、工种名

-- 案例3：查询员工名、工种号、工种名
/*为表起别名，可以提高语句的简洁度，也可以区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
SELECT employees.last_name,employees.job_id,jobs.job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id;

                    2.1.4  查询有奖金的员工名、部门名（添加筛选条件）

-- 案例4：查询有奖金的员工名、部门名（添加筛选条件）
-- 注意：两个表的顺序可以交换，查询结果与表的先后顺序无关
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

                    2.1.5  查询城市表中城市名中第二个字符为o的部门名和城市名

-- 案例5：查询城市表中城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';

                    2.1.6  查询每个城市的个数（添加分组）

-- 案例6：查询每个城市的个数（添加分组）
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city;

                    2.1.7  查询有奖金的每个部门得部门名和部门的领导编号和该部门得最低工资

-- 案例7：查询有奖金的每个部门得部门名和部门的领导编号和该部门得最低工资
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 department_name,d.manager_id;

                    2.1.8  查询每个工种的工种名和员工的个数，并且按员工个数降序（添加排序）

-- 案例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;

                    2.1.9  查询员工名、部门名和所在的城市（多表连接查询）

-- 案例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;

          2.2  非等值连接

                    2.2.1  查询员工的工资和工资级别,此处引入了一个工资等级表

-- 案例：查询员工的工资和工资级别,此处引入了一个工资等级表
SELECT salary,grade_level
FROM employees e,jobs_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

          2.3  自连接

                    2.3.1  查询员工名和上级的名称

-- 案例：查询员工名和上级的名称
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;

MySQL连接查询——sql92语法

1、连接查询

          含义：当查询结果涉及到多个表的字段时，需要使用连接查询

          分类：

                    按年代分：sql92标准/sql99标准（通常使用sql99标准）

                    按功能分：内连接（等值连接、非等值连接、自连接）；外连接（左外连接、右外连接、全外连接）；交叉连接

          等值连接特点：

                    1）多表等值连接的结果为多表的交集部分

                    2）n表连接，至少需要n-1个连接条件

                    3）查询结果集与表的先后顺序无关

                    4）可以为表起别名

                    5）可以搭配所有子句的使用，例如：排序、分组、筛选等

2、分类（sql92标准）

          2.1  等值连接

                    2.1.1 查询女神名和对应的男神名

-- 案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

                    2.1.2  查询员工名和对应的部门名

-- 案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.employee_id = departments.department_id;

                    2.1.3  查询员工名、工种号、工种名

-- 案例3：查询员工名、工种号、工种名
/*为表起别名，可以提高语句的简洁度，也可以区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
SELECT employees.last_name,employees.job_id,jobs.job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id;

                    2.1.4  查询有奖金的员工名、部门名（添加筛选条件）

-- 案例4：查询有奖金的员工名、部门名（添加筛选条件）
-- 注意：两个表的顺序可以交换，查询结果与表的先后顺序无关
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

                    2.1.5  查询城市表中城市名中第二个字符为o的部门名和城市名

-- 案例5：查询城市表中城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';

                    2.1.6  查询每个城市的个数（添加分组）

-- 案例6：查询每个城市的个数（添加分组）
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city;

                    2.1.7  查询有奖金的每个部门得部门名和部门的领导编号和该部门得最低工资

-- 案例7：查询有奖金的每个部门得部门名和部门的领导编号和该部门得最低工资
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 department_name,d.manager_id;

                    2.1.8  查询每个工种的工种名和员工的个数，并且按员工个数降序（添加排序）

-- 案例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;

                    2.1.9  查询员工名、部门名和所在的城市（多表连接查询）

-- 案例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;

          2.2  非等值连接

                    2.2.1  查询员工的工资和工资级别,此处引入了一个工资等级表

-- 案例：查询员工的工资和工资级别,此处引入了一个工资等级表
SELECT salary,grade_level
FROM employees e,jobs_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

          2.3  自连接

                    2.3.1  查询员工名和上级的名称

-- 案例：查询员工名和上级的名称
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;