门户网站建设工作,长治房产网站建设,网页设计跳转链接怎么制作,成都网站建设开发价格2023.9.4 连接查询相关sql92语句笔记#xff1a;
#连接查询。 又称多表查询#xff0c;当查询的字段来自多个表时#xff0c;就会用到连接查询。
#等值连接
/*
①多表等值连接的结果为多表的交集部分
②n表连接#xff0c;至少需要n-1个连接条件
③多表的顺序没有要求
④一…2023.9.4 连接查询相关sql92语句笔记
#连接查询。 又称多表查询当查询的字段来自多个表时就会用到连接查询。
#等值连接
/*
①多表等值连接的结果为多表的交集部分
②n表连接至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面的子句使用如排序、分组、筛选
*/
#找女神对应的男神名
SELECT NAME,boyName FROM beauty,boys WHERE beauty.boyfriend_idboys.id;
#查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments WHERE employees.department_iddepartments.department_id;
#查询员工名工种号工种名
SELECT last_name,jobs.job_id,job_title FROM employees,jobs WHERE employees.job_idjobs.job_id;
#查询有奖金的员工名、部门名
SELECT last_name,department_name FROM employees,departments WHERE employees.department_iddepartments.department_id AND employees.commission_pct IS NOT NULL;
#查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city FROM departments,locations WHERE departments.location_idlocations.location_id AND locations.city LIKE _o%;
#查询每个城市的部门个数SELECT COUNT(*),city FROM departments,locations WHERE departments.location_idlocations.location_id GROUP BY city;
#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,departments.manager_id,MIN(salary) FROM departments,employees WHERE departments.department_idemployees.department_id AND commission_pct IS NOT NULL GROUP BY department_name,departments.manager_id;
#查询每个工种的工种名和员工的个数并且按员工个数降序
SELECT job_title,COUNT(*) FROM jobs,employees WHERE jobs.job_idemployees.job_id GROUP BY job_title ORDER BY COUNT(*) DESC;
#查询员工名、部门名和所在的城市
SELECT last_name,department_name,city FROM employees,departments,locations WHERE employees.department_iddepartments.department_id AND departments.location_idlocations.location_id
#内连接 之 非等值连接
#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees,job_grades
WHERE salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;
#内连接 之 自连接
#查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees AS e,employees AS m
WHERE e.employee_idm.manager_id;
连接查询sql92课后习题
#1.显示所有员工的姓名部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_idd.department_id;
#2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.department_idd.department_id
AND e.department_id90;
#3.选择所有有奖金的员工的last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,l.location_id,l.city
FROM employees e,departments d,locations l
WHERE e.department_idd.department_id
AND d.location_idl.location_id
AND e.commission_pct IS NOT NULL;
#4.选择city在Toronto工作的员工的last_name , job_id , department_id , department_name
SELECT e.last_name , e.job_id , d.department_id , d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_idd.department_id
AND d.location_idl.location_id
AND l.cityToronto;
#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_id,MIN(salary)
FROM employees e,departments d
WHERE e.department_idd.department_id
GROUP BY job_id,department_name;
#6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数
FROM locations l,departments d
WHERE d.location_idl.location_id
GROUP BY country_id
HAVING COUNT(*)2;
#7、选择指定员工的姓名员工号以及他的管理者的姓名和员工号结果类似于下面的格式
#employees Emp# manager Mgr#
#kochhar 101 king 100
SELECT e.last_name employees,e.employee_id Emp#,m.last_name manager,m.employee_id Mgr#
FROM employees e,employees m
WHERE e.manager_idm.employee_id
AND e.last_namekochhar; sql99语法的连接查询相关笔记
#sql92 vs sql99
/*
功能sql99支持的较多
可读性sql99实现连接条件和筛选条件的分离可读性较高
*/sql99语法
/*
语法select 查询列表from 表1 别名 【连接类型】join 表2 别名 on 连接条件【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】分类
内连接★inner
外连接左外(★):left 【outer】右外(★)right 【outer】全外full【outer】
交叉连接cross */#一、内连接
/*
语法select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;分类
等值
非等值
自连接特点
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面连接条件放在on后面提高分离性便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的都是查询多表的交集
*/#1、等值连接
#案例1.查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_idd.department_id
#案例2.查询名字中包含e的员工名和工种名添加筛选
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_idj.job_id
WHERE e.last_name LIKE %e%
#3. 查询部门个数3的城市名和部门个数添加分组筛选
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_idl.location_id
GROUP BY city
HAVING COUNT(*)3;
#案例4.查询哪个部门的员工个数3的部门名和员工个数并按个数降序添加排序
SELECT department_name,COUNT(*)
FROM employees e
INNER JOIN departments d
ON e.department_idd.department_id
GROUP BY department_name
HAVING COUNT(*)3
ORDER BY COUNT(*) DESC;
#5.查询员工名、部门名、工种名并按部门名降序添加三表连接
SELECT last_name,job_title,department_name
FROM employees e
INNER JOIN jobs j ON e.job_idj.job_id
INNER JOIN departments d ON d.department_ide.department_id
ORDER BY department_name DESC;#2、非等值连接
#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;
#查询工资级别的个数20的个数并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY grade_level
HAVING COUNT(*)20
ORDER BY grade_level DESC;#3、自连接
#查询员工的名字、上级的名字
SELECT e.last_name 员工,m.last_name 上级
FROM employees e
JOIN employees m
ON e.manager_idm.employee_id;
#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name 员工,m.last_name 上级
FROM employees e
JOIN employees m
ON e.manager_idm.employee_id
WHERE e.last_name LIKE %k%
AND m.last_name LIKE %k%;#二、外连接/*应用场景用于查询一个表中有另一个表没有的记录特点1、外连接的查询结果为主表中的所有记录如果从表中有和它匹配的则显示匹配的值如果从表中没有和它匹配的则显示null外连接查询结果内连接结果主表中有而从表没有的记录2、左外连接left join左边的是主表右外连接right join右边的是主表3、左外和右外交换两个表的顺序可以实现同样的效果 4、全外连接内连接的结果表1中有但表2没有的表2中有但表1没有的*/#引入查询男朋友 不在男神表的的女神名
# 左外
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_idbo.id
WHERE bo.id IS NOT NULL;
# 右外
SELECT b.name,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.boyfriend_idbo.id
WHERE bo.id IS NOT NULL;#案例1查询哪个部门没有员工
SELECT department_name,employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id e.department_id
WHERE e.employee_id IS NULL;#全外连接语法如下 MySQL不支持
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id bo.id;#交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
课后习题
#课后习题
#一、查询编号3的女神的男朋友信息如果有则列出详细如果没有用null填充
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id bo.id
WHERE b.id3;
#二、查询哪个城市没有部门
SELECT city
FROM locations l
LEFT OUTER JOIN departments d
ON l.location_id d.location_id
WHERE d.department_id IS NULL;
#三、查询部门名为SAL或IT的员工信息
# 这题我用的内连接尚硅谷老师用的外连接。
# 外连接会比内连接多两条数据但这两条数据全是null值。
# 原因是名为‘IT’的部门对应的id有两个没有员工存在。但个人认为内连接更符合题目要求。#内连接做法
SELECT e.*
FROM employees e
JOIN departments d
ON e.department_id d.department_id
WHERE d.department_nameSAL
OR d.department_nameIT;
#外连接做法
SELECT e.*,d.department_name,d.department_id
FROM departments d
LEFT JOIN employees e
ON d.department_id e.department_id
WHERE d.department_name IN(SAL,IT); 用尚硅谷的两张图总结一下本章图一左上是左外连接右上是右外连接中间是内连接
图二左上是左外连接条件右上是右外连接条件左下是全外连接右下是全外连接条件。
