连接查询-笛卡尔乘积

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

连接查询的分类

一、sql92标准

1.等值连接

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

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

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

#案例2：查询员工名和对应的部门名
SELECT `last_name`,`department_name`
FROM `employees`,`departments`
WHERE `employees`.`department_id`=`departments`.`department_id`;

2.为表起别名

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

#查询员工名、工种号、工种名
SELECT e.last_name,e.`job_id`,j.`job_id`
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;

但是如果还使用原始的表名 则会报错

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

#查询员工名、工种号、工种名
SELECT e.last_name,e.`job_id`,j.`job_id`
FROM jobs AS j,employees AS e
WHERE e.`job_id`=j.`job_id`;

答案是可以替换的

4.可以加筛选吗？

#4.可以加筛选吗？

#查询有奖金的员工名、部门名
SELECT last_name,`department_name`,`commission_pct`
FROM `departments`,`employees`
WHERE `employees`.`department_id`=`departments`.`department_id`
AND `employees`.`commission_pct` IS NOT NULL;

案例2：查询城市名中第二个字符为o的部门名和城市名

#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`,`city`
FROM `departments`,`locations`
WHERE `departments`.`location_id`=`locations`.`location_id`
AND `city` LIKE '_o%';

5.可以加分组吗？

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

#案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,`city`
FROM `departments`,`locations`
WHERE `departments`.`location_id`=`locations`.`location_id`
GROUP BY city

案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT `departments`.`department_name`,`departments`.`manager_id`,MIN(`employees`.`salary`)
FROM `departments`,`employees`
WHERE `employees`.`department_id`=`departments`.`department_id`
AND `employees`.`commission_pct` IS NOT NULL
GROUP BY `departments`.`department_name`,`departments`.`manager_id`;
#最后一行 因为不确定department_name和manager_id是不是一一对应的关系，所以都进行分组

6.可以加排序吗？

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

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

6.可以实现三表连接吗？

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

#案例：查询员工名、部门名和所在的城市
SELECT `last_name`,`department_name`,`city`
FROM `employees`,`departments`,`locations`
WHERE `departments`.`department_id`=`employees`.`department_id`
AND `departments`.`location_id`=`locations`.`location_id`

可以在上面的基础上继续添加筛选条件和排序

#案例：查询员工名、部门名和所在的城市
SELECT `last_name`,`department_name`,`city`
FROM `employees`,`departments`,`locations`
WHERE `departments`.`department_id`=`employees`.`department_id`
AND `departments`.`location_id`=`locations`.`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', 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`,`job_grades`
WHERE `salary` BETWEEN `job_grades`.`lowest_sal` AND `job_grades`.`highest_sal`

自连接

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

kochhar的领导是K_ing

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

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

习题1：显示员工表的最大工资和工资平均值

#习题1：显示员工表的最大工资和工资平均值

SELECT MAX(`salary`),AVG(`salary`)
FROM `employees`;

习题2：查询员工表的employee_id，job_id，last_name按department_id降序，salary升序

#习题2：查询员工表的`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;

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

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

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

习题4：

习题5：显示当前日期，以及去前后空格，截取子字符串的函数

SELECT NOW();

SELECT TRIM(' ')
#要是取指定的字符 则
SELECT TRIM(字符 FROM '')

SELECT SUBSTR(str,startIndex);
SELECT SUBSTR(str,startIndex,LENGTH);

复习

日期函数

作业讲解

涉及到的表如下：

1.显示所有员工的姓名，部门号和部门名称

#1.显示所有员工的姓名，部门号和部门名称
SELECT `last_name`,`employees`.`department_id`,`department_name`
FROM `departments`,`employees`
WHERE `employees`.`department_id`=`departments`.`department_id`;

2.查询90号部门员工的job_id和90号部门的location_id

#2.查询90号部门员工的job_id和90号部门的location_id
SELECT `employees`.`job_id`,`departments`.`department_id`
FROM `departments`,`employees`
WHERE `employees`.`department_id`=`departments`.`department_id`
AND `employees`.`department_id`=90;

3.查询所有有奖金的员工的last_name,department_name,location_id,city

#3.查询所有有奖金的员工的last_name,department_name,location_id,city
SELECT `employees`.last_name,`departments`.department_name,`departments`.location_id,`locations`.city
FROM `departments`,`employees`,`locations`
WHERE `employees`.`department_id`=`departments`.`department_id`
AND `departments`.`location_id`=`locations`.`location_id`
AND `employees`.`commission_pct` IS NOT NULL;

4.选择city在Toronto工作的员工的last_name，job_id，department_id，department_name

#4.选择city在Toronto工作的员工的last_name，job_id，department_id，department_name
SELECT `employees`.`last_name`,`employees`.`job_id`,`employees`.`department_id`,`departments`.`department_name`
FROM `departments`,`employees`,`locations`
WHERE `employees`.`department_id`=`departments`.`department_id`
AND `departments`.`location_id`=`locations`.`location_id`
AND `locations`.`city` = 'Toronto';

5.查询每个工种、每个部门的部门名、工种名和最低工资

#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT `departments`.`department_name`,`jobs`.`job_title`,MIN(`salary`)
FROM `departments`,`employees`,`jobs`
WHERE `employees`.`department_id`=`departments`.`department_id`
AND `employees`.`job_id`=`jobs`.`job_id`
GROUP BY `jobs`.`job_title`,`employees`.`department_id`;

6.查询每个国家下的部门个数大于2的国家编号

#6.查询每个国家下的部门个数大于2的国家编号
SELECT `country_id`,COUNT(*)
FROM `departments`,`locations`
WHERE `departments`.`location_id`=`locations`.`location_id`
GROUP BY `country_id`
HAVING COUNT(*)>2;

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_id=m.employee_id
AND e.last_name='kochhar';