Skip to content

Commit 5d6d929

Browse files
linwtlinwt
authored
Add files via upload
1 parent 450dc23 commit 5d6d929

File tree

61 files changed

+629
-0
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

61 files changed

+629
-0
lines changed

‎数据库SQL实战/01.查找最晚入职员工的所有信息.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 入职日期等于最大入职日期
2+
select *
3+
from employees
4+
where hire_date = (select max(hire_date) from employees);
5+
6+
7+
-- 按照入职日期降序排序，取第一条
8+
select *
9+
from employees
10+
order by hire_date desc
11+
limit 1;

‎数据库SQL实战/02.查找入职员工时间排名倒数第三的员工所有信息.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- 入职日期降序排序取第三条
2+
select *
3+
from employees
4+
order by hire_date desc
5+
limit 2,1;

‎数据库SQL实战/03.查找当前薪水详情以及部门编号dept_no.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 内连接的条件可以转化为where条件查询
2+
-- 两表内连接关联获取信息
3+
select s.*, d.dept_no
4+
from salaries as s inner join dept_manager as d
5+
on d.emp_no=s.emp_no
6+
where d.to_date='9999-01-01' and s.to_date='9999-01-01';
7+
8+
9+
-- 按照查询条件关联两表信息，相当于内连接
10+
select s.*, d.dept_no
11+
from salaries as s, dept_manager as d
12+
where d.to_date='9999-01-01' and s.to_date='9999-01-01' and d.emp_no=s.emp_no;

‎数据库SQL实战/04.查找所有已经分配部门的员工的last_name和first_name以及dept_no.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 已分配表示部门员工表和员工表对应的员工信息共同存在，故使用内连接
2+
-- 查询的字段在两表中唯一故可省略表名
3+
select last_name, first_name, dept_no
4+
from dept_emp as d inner join employees as e
5+
on e.emp_no=d.emp_no;
6+
7+
8+
-- 按照查询条件关联两表信息，相当于内连接
9+
select e.last_name, e.first_name, d.dept_no
10+
from employees as e, dept_emp as d
11+
where e.emp_no=d.emp_no;

‎数据库SQL实战/05.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
-- 员工可能未分配部门，故使用左连接
2+
select e.last_name, e.first_name, d.dept_no
3+
from employees as e left join dept_emp as d
4+
on e.emp_no=d.emp_no;

‎数据库SQL实战/06.查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_no进行逆序.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 员工表和薪水表对应的员工信息需要共同存在，故使用内连接
2+
select e.emp_no, s.salary
3+
from employees as e inner join salaries as s
4+
on e.emp_no=s.emp_no and e.hire_date=s.from_date
5+
order by e.emp_no desc;
6+
7+
8+
select e.emp_no, s.salary
9+
from employees as e, salaries as s
10+
where e.emp_no=s.emp_no and e.hire_date=s.from_date
11+
order by e.emp_no desc;

‎数据库SQL实战/07.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
-- 按照员工号分组并统计次数
2+
select emp_no, count(*)
3+
from salaries
4+
group by emp_no
5+
having count(*)>15;
6+
7+
8+
-- having在产生虚表后执行
9+
-- 查询字段使用替代名
10+
select emp_no, count(emp_no) as times
11+
from salaries
12+
group by emp_no
13+
having times > 15;
14+
15+
16+
-- 此处对员工和薪水去重
17+
select emp_no, count(emp_no) as times
18+
from (select distinct emp_no, salary from salaries)
19+
group by emp_no
20+
having times > 15;

‎数据库SQL实战/08.找出所有员工当前薪水salary情况，对于相同的薪水只显示一次，并按照逆序显示.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 对某字段去重可用distinct或group by
2+
select distinct salary
3+
from salaries
4+
where to_date='9999-01-01'
5+
order by salary desc;
6+
7+
8+
select salary
9+
from salaries
10+
where to_date='9999-01-01'
11+
group by salary
12+
order by salary desc;

‎数据库SQL实战/09.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- 关联表信息，使用内连接
2+
select d.dept_no, d.emp_no, s.salary
3+
from dept_manager as d inner join salaries as s
4+
on d.emp_no=s.emp_no
5+
where d.to_date='9999-01-01' and s.to_date='9999-01-01';
6+
7+
8+
-- 直接在条件语句中关联表信息
9+
select d.dept_no, d.emp_no, s.salary
10+
from dept_manager as d, salaries as s
11+
where d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01';

‎数据库SQL实战/10.获取所有非manager的员工emp_no.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
-- 直接条件查询员工号不在manager中
2+
select emp_no
3+
from employees
4+
where emp_no not in
5+
(select emp_no from dept_manager);
6+
7+
8+
-- 使用左连接，非manager的员工关联不到其他信息，没有dept_no
9+
select e.emp_no
10+
from employees as e left join dept_manager as d
11+
on e.emp_no=d.emp_no
12+
where d.dept_no is null;

0 commit comments

Comments
 (0)