MySQL连接查询——sql92标准（内连接）

#连接查询（多表查询）要查询的字段涉及到多个表
/*
笛卡尔乘积现象：设表一有m行，表二有n行，结果有mn行
产生原因：没有有效的连接条件
解决办法：添加有效的连接条件
*/
SELECT NAME,boyname FROM beauty,boys WHERE beauty.`boyfriend_id` = `boys`.`id`;

#分类
/*
按年代分类
sql92标准  仅支持内连接
sql98标准，推荐 支持除全外连接的所有连接

按功能分类
内连接： 等值连接 非等值连接 自连接
外连接： 左外连接，右外连接，全外连接
交叉连接
*/

#sql92标准
#1、等值连接
/*
m表连接，至少需要m-1个连接条件
*/
SELECT NAME,boyname 
FROM beauty,boys 
WHERE beauty.`boyfriend_id` = `boys`.`id`;
#过程：用表一的每一行匹配表二的每个行
#案例：查询员工名和对应的部门名
USE myemployees;
SELECT CONCAT(last_name,' ' ,first_name),department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;

#为表起别名:提高代码简洁度
#起了别名之后就不认识原始的表名
/*
SELECT CONCAT(last_name,' ' ,first_name),employees.job_id,jobs.job_title 会报错
FROM employees e,jobs j
WHERE e.job_id=j.job_id;
*/
#查询员工名，工种号，工种名
SELECT CONCAT(last_name,' ' ,first_name),e.job_id,job_title
FROM employees e,jobs j  #两个表的顺序可以变
WHERE e.job_id=j.job_id;

#加筛选
#案例：查询有奖金的员工名，部门名
SELECT CONCAT(last_name,' ' ,first_name),department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
#先等值连接再筛选（and）
/*
SELECT CONCAT(last_name,' ' ,first_name),department_name
FROM employees e,departments d
where e.employees is not null 
and e.department_id = d.department_id;  报错
*/
#案例：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';

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

#案例：查询有奖金的每个部门名和部门领导编号和该部门的最低工资
SELECT department_name,e.manager_id,MIN(salary)
FROM departments d,employees e
WHERE e.department_id = d.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name ,d.manager_id;

#加排序
#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#三表连接
#查询员工名部门名和所在城市
SELECT CONCAT(last_name,' ',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;

#2、非等值连接

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 salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN lowest_sal AND highest_sal;
#实质就是拿工资和等级表中的最低最高工资依次比较

#3、自连接  同一个表中
#查询员工名和对应上级名称
SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e,employees m#起不同的别名，相当于两个相同的表
WHERE e.manager_id = m.employee_id;


#测试
#显示员工表中的最大工资，工资平均值
SELECT MAX(salary),ROUND(AVG(salary),2) FROM employees;

#查询员工表的employee_id job_id last_name 按department_id降序，salary升序
SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_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 gradeId = g.id AND studentNo = s.id;

#显示当前时间，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT TRIM(要去的字符 FROM '');
SELECT SUBSTR();