mysql数据库实验3
- 格式：pdf
- 大小：61.46 KB
- 文档页数：2
mysql数据库实验3
3． 假设当前数据库是xscj，要显⽰YGGL数据库的Employees表中的内容。
select * from yggl. Employees;
4. 从xs表中检索出所有学⽣的信息，并使⽤表别名studentinformation。
select * from xs as studentinformation;
5. 查找xscj数据库中所有学⽣选过的课程名和课程号。
select distinct kc.课程名,xs_kc.课程号
From kc,xs_kc
Where kc.课程号=xs_kc.课程号;
6. 查找选修了206号课程且成绩不少于80分的学⽣的姓名和成绩。
select xs.姓名,成绩
From xs join xs_kc on xs.学号=xs_kc.学号
Where 课程号 ='206' and 成绩>=80;
7. 查找选修了计算机基础课程且成绩不少于80分的学⽣的学号、姓名、课程号及成绩。
select xs.学号,姓名,课程名,成绩
From xs join xs_kc on xs.学号=xs_kc.学号
join kc on xs_kc.课程号 =kc.课程号
Where 课程名 ='计算机基础' and 成绩>=80;
8． 查找xscj数据库中课程不同、成绩相同的学⽣的学号。
select a.学号,a.课程号,b.课程号,a.成绩
from xs_kc as a join xs_kc as b
on a.成绩=b.成绩 and a.学号=b.学号 and a.课程号!=b.课程号;
9. 查找kc表中所有学⽣选过的课程名。
select 课程名
From kc inner join xs_kc
using(课程号);
10. 查找所有学⽣情况及他们选修的课程号，若学⽣未选修任何课程，也要包括其情况。
select xs.*,课程号,成绩
from xs left outer join xs_kc on xs.学号=xs_kc.学号;
11. 查找被选修了的课程的选修情况和所有开设的课程名。
select 课程名,xs_kc.*
from xs_kc right join kc on xs_kc.课程号=kc.课程号;
12. 列出学⽣所有可能的选课情况。
select 学号,姓名,课程号,课程名
from xs cross join kc;
13. 使⽤straight_join连接实现课程表和成绩表的连接。
select distinct 课程名,xs_kc.课程号
from kc straight_join xs_kc
on (kc.课程号=xs_kc.课程号);
14. 查询YGGL数据库中每个员⼯的情况及其薪⽔的情况。select employees .*,salary .* from employees ,salary
where employees.employeeid =salary .employeeID;
15. 查询YGGL数据库中每个员⼯的情况及其部门的情况。
select employees .*,departments .*
from employees ,departments
where employees .departmentID =departments .departmentID;
16. 使⽤内连的⽅法查询名字为“王林”的员⼯所在的部门。
select departmentName
from departments join employees on departments .departmentID =employees .departmentID
where employees .name ='王林';
17. 使⽤内连的⽅法查找不在财务部⼯作的所有员⼯的信息。
select employees .*
from employees join departments on departments .departmentID =employees .departmentID
where departmentName !='财务部';
18. 使⽤外连的⽅法查找所有员⼯的⽉收⼊。
select income
from salary left outer join employees on employees.employeeid=salary.employeeID;
19. 查找财务部收⼊在2000元以上的员⼯的姓名及其薪⽔详情。
select name ,income ,outcome from employees,salary,departments
where employees.employeeid =salary.employeeID
and employees.departmentID =departments.departmentID
and departmentname='财务部'
and income > 2000;
20. 查询研发部在1966年以前出⽣员⼯的姓名及其薪⽔详情。
select name,income ,outcome,birthday from employees,salary
where year(birthday) <1966
and departmentID =(select departmentID from departments where departmentName = '研发部')
and employees.employeeid=salary.employeeID;
