create database testuse test --部門表create table department( dept_id int not null identity primary key,--主鍵 dept_no char(4) not null unique, --編號 dept_n ...
create database test
use test
--部門表
create table department(
dept_id int not null identity primary key,--主鍵
dept_no char(4) not null unique, --編號
dept_name varchar(20) not null --名稱
)
insert into department values('D001','財務部')
insert into department values('D002','研發部')
insert into department values('D003','業務部')
--職位表
create table job(
job_id int not null identity primary key,--主鍵
job_no char(4) not null unique, --編號
job_name varchar(20) not null, --名稱
job_salary float not null --工資
)
insert into job values('J001','web前端',5000)
insert into job values('J002','java開發',8000)
insert into job values('J003','c#開發',6000)
insert into job values('J004','財務管理',7000)
insert into job values('J005','業務管理',7000)
--員工表
create table employee(
emp_id int not null identity primary key,--主鍵
emp_no char(4) not null unique, --編號
emp_name varchar(20) not null, --姓名
emp_sex nchar(1) not null, --性別
emp_phone char(11) not null, --手機號
emp_date date not null, --入職日期
job_no char(4) foreign key references job(job_no), --職位編號，外鍵管理職位表的職位編號
dept_no char(4) foreign key references department(dept_no)--部門編號，外鍵管理部門表的部門編號
)
insert into employee values('E001','張三','男','11111111111','2017-01-01','J001','D002')
insert into employee values('E002','莉莉','女','45341321324','2017-01-01','J001','D002')
insert into employee values('E003','李四','男','22222222222','2017-01-01','J002','D002')
insert into employee values('E004','張凱','男','11212121212','2017-01-01','J002','D002')
insert into employee values('E005','王丹','女','35453155125','2017-01-01','J002','D002')
insert into employee values('E006','王五','男','33333333333','2017-01-01','J003','D002')
insert into employee values('E007','孫迪','男','47651215451','2017-01-01','J003','D002')
insert into employee values('E008','趙六','男','44444444444','2017-01-01','J004','D001')
insert into employee values('E009','唐舞','女','83212133200','2017-01-01','J005','D003')
--內連接
--1.查詢所有職員的:職員編號,姓名,職位名稱,工資,部門名稱
--select 欄位 from 表1 inner join 表2 on 條件匹配
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.dept_no=c.dept_no
--2.查詢性別是女的職員的:職員編號,姓名,職位名稱,工資,部門名稱,性別
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no
inner join department c on a.dept_no=c.dept_no
where a.emp_sex='女'
--3.查詢每個部門的:部門名稱,人數,平均工資,最大工資,最小工資,工資總和
select c.dept_name,
COUNT(*) as 人數,
avg(b.job_salary) as 平均工資,
max(b.job_salary) as 最大工資,
min(b.job_salary) as 最小工資,
sum(b.job_salary) as 工資總和
from employee a inner join job b on a.job_no=b.job_no
inner join department c on a.dept_no=c.dept_no
group by c.dept_name
--子查詢
--1.查詢部門是 研發部 的所有職員的:職員編號,姓名,職位名稱,工資,部門名稱
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.dept_no=(select dept_no from department where dept_name='研發部')
--2.查詢職位是 web前段 的所有職員的:職員編號,姓名,職位名稱,工資,部門名稱
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.job_no=(select job_no from job where job_name='web前端')
--3.查詢工資大於等於7000的所有職員的:職員編號,姓名,職位名稱,工資,部門名稱
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.job_no in(select job_no from job where job_salary>=7000)
--外連接
--1.將三張表做一個全鏈接關聯查詢全部信息
select * from employee a full join department b on a.dept_no=b.dept_no full join job c on a.job_no=c.job_no
--集合操作
--1.部門 財務部和業務部 並集查詢:職員編號,姓名,職位名稱,工資,部門名稱
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.dept_no=(select dept_no from department where dept_name='財務部')
union all
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.dept_no=(select dept_no from department where dept_name='業務部')
--2.職位 java開發和c#開發 差集查詢：職員編號,姓名,職位名稱,工資,部門名稱
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.job_no=(select job_no from job where job_name='java開發')
except
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.job_no=(select job_no from job where job_name='c#開發')
--3.職員表 id<=3和id<=7 交集查詢：職員編號,姓名,職位名稱,工資,部門名稱
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.emp_id<=3
intersect
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
from employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.emp_id<=7
