mysql打卡学习7连接查询

水深无声 2023-03-14 10:40 23阅读 0赞

## 进阶6：连接查询 ##

/*
    含义：多表查询，当查询的字段来自于多个表时，就会用到连接查询
    笛卡尔乘积现象：表1有m行,表2有n行，结果m*n
    	发生原因：没有有效的连接条件
    	如果避免：添加有效的连接条件
    分类
    	sql92 内连接
    	sql99 全外不支持 
    按功能分类
    	内连接
    		等值连接
    		非等值连接
    		自连接
    	外连接
    		左外连接
    		右外连接
    		全外连接
    	交叉连接
    */

SELECT * FROM beauty;
    SELECT * FROM boys;
    
    #两个表联合查询
    SELECT NAME,boyName FROM boys,beauty
    WHERE beauty.boyfriend_id=boys.id;
    
    /*
    1.多表等值连接的结果为多表的交集部分
    2.n表连接至少需要n-1各连接条件
    3.表连接没有顺序要求
    4.可以为表起别名
    5.可以搭配前面的所有查询
    */

### 一.等值连接 ###

#男女名对应
    SELECT NAME,boyName 
    FROM beauty,boys
    WHERE beauty.boyfriend_id=boys.id;
    
    #查询员工名和对应的部门名
    SELECT last_name,department_name
    FROM employees,departments
    WHERE employees.`department_id`=departments.`department_id`;

#2.为表起别名
    /*
    简洁度
    区分重名
    注意：起了别名之后，查询的字段不能用原来的
    */
    #查询员工名，工种号，工种名
    SELECT last_name,e.`job_id`,job_title
    FROM employees AS e,jobs AS j
    WHERE e.`job_id`=j.`job_id`;

#3.表的顺序可以调换
    SELECT last_name,e.`job_id`,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 AS e,departments AS d
    WHERE e.`department_id`=d.`department_id`
    AND e.`commission_pct` IS NOT NULL;
    
    #（2）查询城市名中第二个字符为o的部门名和城市名
    SELECT department_name,city
    FROM locations AS l,departments AS d
    WHERE l.`location_id`=d.`location_id`
    AND 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.可以加排序
    #（1）查询每个工种的工种名和员工的个数，并且按员工个数降序
    SELECT j.job_id,job_title,COUNT(*)
    FROM employees e,jobs j
    WHERE e.`job_id`=j.`job_id`
    GROUP BY job_title
    ORDER BY COUNT(*) DESC;

#7.三表连接
    #（1）查询员工名，部门名 所在城市
    SELECT last_name,department_name,city
    FROM employees e,locations l,departments d
    WHERE e.`department_id`=d.`department_id`
    AND d.`location_id`=l.`location_id`
    AND city LIKE 's%'
    ORDER BY department_name DESC;

### 二.非等值连接 ###

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',16000,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 g
    WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
    AND g.`grade_level`='A';

### 三.自连接 ###

#案例：员工名和上级名称 都在一张表里
    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`;

### 练习 ###

#1.员工表的最大工资，工资平均值
    SELECT MAX(salary),AVG(salary)
    FROM employees;
    
    #2.员工的id,工作id，last_name department_id降序，salary升序
    SELECT employee_id,job_id,last_name
    FROM employees
    ORDER BY department_id DESC,salary DESC;
    
    #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(学号)
    姓名，年级名，成绩
    */
    SELECT s.name,g.name,r.score
    FROM student s,grade g,result r
    WHERE s.id=r.studentNo
    AND g.id=s.gradeId;
    
    #5.显示当前日期，去前后空格，截取字符串的函数
    SELECT NOW()
    SELECT TRIM(字符,FROM ' ');
    SELECT SUBSTR(str,startindex,LENGTH);