mysql连接查询（一）

连接查询

目录

• 连接查询
  • 一、sql92标准
    • 1. 等值连接
    • 2.为表起别名
    • 3.两个表的顺序是否可以调换
    • 4. 可以加筛选
    • 5. 可以加分组
    • 6. 可以加排序
    • 7. 可以实现三表连接
    • 8. 非等值连接
    • 9. 自连接
  • 作业

含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
     按年代分类：
        sql92标准：仅仅支持内连接
                  (1) 多表等值连接的结果为多表的交集部分
                  (2) n表连接，至少需要n-1个连接条件
                  (3) 多表的顺序没有要求
                  (4) 一般需要为表起别名
                  (5) 可以搭配前面介绍的所有子句使用，比如：排序、分组、筛选
                          
                  
        sql99标准：推荐：支持内连接+外连接（不支持全外连接）+交叉连接
        
     按功能分配
       内连接：
             等值连接
             非等值连接
             自连接
       外连接：
             左外连接
             右外连接
             全外连接
       交叉连接

SELECT * FROM beauty;
SELECT * FROM boys;

一、sql92标准

1. 等值连接

案例1：查询女神名和对应的男神名

SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.`boyfriend_id` = boys.`id`;

案例2：查询员工名和对应的部门名

SELECT `last_name`, `department_name`
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

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

SELECT last_name,employees.job_id,job_title
FROM employees,jobs
WHERE employees.`job_id` = jobs.`job_id`;

2.为表起别名

提高语句的简洁度
区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

SELECT e.last_name,e.job_id,j.job_title
FROM employees AS  e,jobs AS j
WHERE e.`job_id` = j.`job_id`;

3.两个表的顺序是否可以调换

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

SELECT e.last_name,e.job_id,j.job_title
FROM jobs AS j,employees AS  e
WHERE e.`job_id` = j.`job_id`;

4. 可以加筛选

案例1: 查询有奖金的员工名、部门名

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：查询城市名中第二个字符为o的部门名，城市名

SELECT `department_name`,`city`
FROM `departments` d, locations l
WHERE d.`location_id` = l.`location_id`
AND l.`city` LIKE '_o%';

5. 可以加分组

案例1：查询每个城市的部门个数

SELECT COUNT(*), 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 `department_name`,d.`manager_id`;

6. 可以加排序

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT `job_title`, COUNT(*)
FROM jobs j, employees e
WHERE j.`job_id` = e.`job_id`
GROUP BY e.`job_id`
ORDER BY COUNT(*) DESC;

7. 可以实现三表连接

案例：查询员工名、部门名和所在城市

SELECT `first_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`;

8. 非等值连接

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 * FROM `job_grades`;

案例1： 查询员工的工资和工资级别

SELECT  salary, grade_level
FROM `employees` e, `job_grades` j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

9. 自连接

案例： 查询员工名和上级的名称

SELECT e.`last_name`,e.`employee_id`,m.`employee_id`,m.`last_name`
FROM `employees` e, employees m
WHERE e.`manager_id` = m.`employee_id`;

作业

1. 显示员工表的最大工资、工资平均值

SELECT MAX(salary) AS 最大工资, AVG(salary) 平均工资
FROM employees;

2. 显示员工表的employee_id,job_id,last_name,按department_id降序，salary升序

SELECT `employee_id`,`job_id`,`last_name`,`department_id`,`salary`
FROM employees
ORDER BY `department_id` DESC, `salary` ASC;

3. 查询员工表的job_id中包含a和e的，并且a在e前面

SELECT `job_id`
FROM employees
WHERE job_id LIKE '%a%e%';

4. 已知表student，里面有id(学号），name,gradeid(年级号）
   已知表grade，里面有id（年纪编号），name（年纪名）
   已知表result，里面有id，score，studentNo（学号）
   要求查询姓名、年纪名、name

SELECT s.name,g.name,score
FROM student s, grade g, result r
WHERE s.gradeid = g.id
AND s.id = r.studentNo
ORDER BY r.score DESC;

5. 显示当前日期，以及去前后空格，截取子字符串的函数
   SELECT TRIM(NOW());