#连接查询(多表查询)要查询的字段涉及到多个表
/*
笛卡尔乘积现象:设表一有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();
MySQL连接查询——sql92标准(内连接)
最新推荐文章于 2023-09-23 20:59:34 发布