Chinaunix首页 | 论坛 | 博客
  • 博客访问: 6636220
  • 博文数量: 915
  • 博客积分: 17977
  • 博客等级: 上将
  • 技术积分: 8846
  • 用 户 组: 普通用户
  • 注册时间: 2005-08-26 09:59
个人简介

一个好老好老的老程序员了。

文章分类

全部博文(915)

文章存档

2022年(9)

2021年(13)

2020年(10)

2019年(40)

2018年(88)

2017年(130)

2015年(5)

2014年(12)

2013年(41)

2012年(36)

2011年(272)

2010年(1)

2009年(53)

2008年(65)

2007年(47)

2006年(81)

2005年(12)

分类: Mysql/postgreSQL

2022-04-29 16:24:56

1.1


1.1题目

1.显示所有员工的姓名,部门号和部门名称。 2.查询90号部门员工的job_id和90号部门的location_id 3.选择所有有奖金的员工的 last_name , department_name , location_id , city 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 5.查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为’Executive’ 6.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式 employees Emp # manager Mgr# kochhar 101 king 100 7.查询哪些部门没有员工 8. 查询哪个城市没有部门 9. 查询部门名为 Sales 或 IT 的员工信息 

1.2答案


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


SELECT  last_name,  e.department_id,  d.department_name FROM  employees e  LEFT OUTER JOIN departments d  ON e.department_id = d.department_id ;

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

SELECT  job_id,  location_id FROM  employees e  INNER JOIN departments d  ON e.department_id = 90 ;


3.选择所有有奖金的员工的 last_name , department_name , location_id , city

SELECT last_name, department_name, d.location_id, city FROM employees e LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id` LEFT OUTER JOIN locations l ON l.`location_id` = d.`location_id` WHERE e.`commission_pct` IS NOT NULL;


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

SELECT e.last_name, e.job_id,e.department_id,department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id` JOIN locations l ON d.`location_id` = l.`location_id` WHERE city = 'Toronto'; 

5 查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为’Executive’

SELECT department_name, street_address, last_name, job_id, salary FROM employees e JOIN departments d ON e.`department_id` = d.`department_id` JOIN locations l ON d.`location_id` = l.`location_id` WHERE department_name ='Executive'; 


6.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式

employees Emp # manager Mgr#

kochhar 101 king 100


SELECT e.last_name, e.employee_id 'emp#' , m.last_name manager , m.employee_id 'mgr#' FROM employees e JOIN employees m ON e.`manager_id` = m.`employee_id` WHERE e.last_name = 'kochhar'; 


7.查询哪些部门没有员工。

思路:用外连接得到departments表与employees的连接关系,再筛选employee_id为null的departments表

SELECT d.department_id FROM departments d LEFT OUTER JOIN employees e ON e.`department_id` = d.`department_id` WHERE employee_id IS NULL; 

8.查询哪个城市没有部门 查询哪个城市没有部门,同样的思路,先查询location表,外连接departments表

SELECT city FROM locations l LEFT OUTER JOIN departments d ON l.`location_id` = d.`location_id` WHERE department_id IS NULL;

9. 查询部门名为 Sales 或 IT 的员工信息

查询部门名为sales或id的员工信息 SELECT * FROM employees e JOIN departments d ON e.`department_id` = d.`department_id` WHERE department_name IN ('Sales','IT');

2.多表查询2


2.1题目

#1.所有有门派的人员信息  #2.列出所有用户,并显示其机构信息  #3.列出所有门派  #4.所有不入门派的人员  #5.所有没人入的门派  #6.列出所有人员和机构的对照关系 MySQL Full Join的实现 。因为MySQL不支持FULL JOIN,下面是替代方法 #left join + union(可去除重复数据)+ right join  #7.列出所有没入派的人员和没人入的门派  

首先创造数据集


CREATE DATABASE ex4; CREATE TABLE `t_dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `t_emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, empno INT NOT NULL, PRIMARY KEY (`id`), KEY `idx_dept_id` (`deptId`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO t_dept(deptName,address) VALUES('华山','华山'); INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳'); INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山'); INSERT INTO t_dept(deptName,address) VALUES('武当','武当山'); INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶'); INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺'); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,NULL,100010); 


2.2 答案


1.所有有门派的人员信息

SELECT * FROM t_emp e INNER  JOIN t_dept d ON e.`deptId` = d.id; 

2.列出所有用户,并显示其机构信息

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id`; 


3.列出所有门派

SELECT * FROM t_dept; 

4.所有不入门派的人员

SELECT * FROM t_emp e LEFT OUTER JOIN t_dept d ON e.`deptId` = d.`id` WHERE deptId IS NULL; 


5.所有没人入的门派

SELECT * FROM t_dept d LEFT OUTER JOIN t_emp e ON e.`deptId` = d.`id` WHERE e.`deptId` IS NULL;


6.列出所有人员和机构的对照关系

MySQL Full Join的实现 。因为MySQL不支持FULL JOIN,下面是替代方法

#left join + union(可去除重复数据)+ right join

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id` UNION SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId = d.id; 


7.列出所有没入派的人员和没人入的门派

SELECT * FROM t_emp e LEFT OUTER JOIN t_dept d ON e.deptId = d.id WHERE e.`deptId` IS NULL UNION SELECT * FROM t_emp e RIGHT OUTER JOIN t_dept d ON e.deptId = d.id WHERE e.`deptId` IS NULL; 


阅读(562) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~