Sql server join on group by order by having用法

Sql server join on group by order by having用法

所有的命令全在SQL SERVER 2008进行运行调试 保证命令正常

此次 主要的重点就是SQL 的查询 多以join on为主

group by order by having用法

解题方式不止一种方法

注意在新建表的时候

有些表包含两个主键 注意学习方法 不止一种写法

还有在新建表的时候 同一个字段为主键也为外键

路是多条的 就看你怎么走了

方法不局限一种

create database compy--创建数据库名为：compy
on
(
name=compy_Data,--主逻辑名称
Filename='G:\数据库\查询技术应用\compy_Data.mdf',--指定文件路径
Size=3,--文件初始化大小：3MB
MaxSize=20,--文件最大容量限制：20M
Filegrowth=10%--文件增量：10%
)
log on
(
name=compy_log,--日志文件名称
Filename='G:\数据库\查询技术应用\compy_Data.log',--指定文件路径
Size=2,--初始化文件大小：2MB
MaxSize=10,--文件最大容量限制：10M
Filegrowth=1--文件增量1MB
)
--使用数据库
use compy
--创建部门信息表
create table Departments
(
DepID char(3) not null primary key,
DepName char(20) not null,
DepState text
)
--增加数据
insert Departments values(1,'财务部','null')
insert Departments values(2,'人力资源部','null')
insert Departments values(3,'生产部','null')
insert Departments values(4,'采购部','null')
insert Departments values(5,'销售部','null')

select * from Departments
--创建员工信息表
create table Employees
(
EmpID char(3) not null primary key,
EmpName varchar(10) not null,
EmAdd text,
Postal_Code char(6),
Tel char(11),
DepID char(3) not null foreign key references Departments,
Birthdate datetime not null,
Sex char(2) not null
)
--增加数据


insert Employees values('000001','张晓强','上海',230027,3333556,2,'1956-1-1',1)
insert Employees values('010008','柳树','合肥',230027,3131359,2,'1966-1-1',1)
insert Employees values('002001','许静','北京','230026','3132359','1','1972-1-1','1')
insert Employees values('020018','李可','武汉','123456','3635346','1','1950-1-1','0')
insert Employees values(102201,'张三丰','西安','123456','3458965','5','1962-1-1','0')
insert Employees values(102208,'白眉','南京','123456','3635346','5','1955-1-1','1')
insert Employees values(108991,'胡适','昆明','123456','3568921','3','1969-1-1','1')
insert Employees values(111006,'成吉思汗','海口','123456','3458145','5','1964-1-1','1')
insert Employees values(210678,'萧然','成都','123456','3454565','3','1967-1-1','1')

select * from Employees
--删除主键
alter table Employees drop PK__Employee__AF2DBA7903317E3D
--修改员工信息表EmpID 类型为char(6)
alter table Employees alter column EmpID char(6)
alter table Employees alter column EmpName varchar(10)
--新建主键
alter table Employees add constraint Employees_Primary primary key(EmpID)
--创建员工薪

水信息表
create table Salary
(
SaID char(6) not null primary key CONSTRAINT SaID_FK Foreign KEY(SaID)references Salary(SaID),
Revenue money default '0.01',--收入
Expense money default '0.01'--消耗
)
--增加信息
insert salary values('000001','2100','123')
insert salary values('010008','1582','88')
insert salary values('002001','2569','185')
insert salary values('020018','1987','79')
insert salary values('102201','2066','108')
insert salary values('102208','2980','210')
insert salary values('108991','3259','281')
insert salary values('111006','2860','198')
insert salary values('210678','2347','180')

select * from Salary
--alter table salary add Constraint Salary_FK foreign key(SaID)
--创建员工购物信息表
create table Purchase
(
PurID char(6) not null,
Goods char(4) not null constraint pk_tb_colname primary key(PurID,Goods),--联合主键
Num char(4) not null
)
--增加信息
insert purchase values('000001','01','3')
insert purchase values('000001','03','4')
insert purchase values('002001','01','5')
insert purchase values('020018','03','6')
insert purchase values('108991','11','4')
insert purchase values('000001','11','3')
insert purchase values('020018','01','5')
insert purchase values('201678','02','1')
insert purchase values('020018','11','2')

select * from Purchase
select * from Departments
select * from Employees
select * from Salary
--查询1956,1962,1964年出生的员工记录
select * from Employees where Birthdate LIKE '%1956%'or Birthdate like'%1962%' or Birthdate like'%1964%'
--查询性别为1的员工记录
select * from Employees where Sex='1'
--查询名字中包含‘白’字的雇员信息
select * from Employees where EmpName like'白%'
--查询不姓‘李’或者‘胡’，并且名字为三个字的雇员的记录
select * from Employees where EmpName not like'白%' and EmpName not like'胡%' and len(EmpName)=3
--查询每个雇员情况及薪水情况
select a.*,b.Revenue from Employees as a inner join Salary as b on a.EmpID=b.SaID
--查找财务部收入在1000元以上员工姓名以及其薪水
select a.EmpName,b.Revenue from Employees as a join Salary as b on a.EmpID=b.SaID where Revenue>1000
--查询收入高于编号为102201的员工收入的职工信息
select a.*,b.revenue from Employees as a join salary as b on a.EmpID=b.SaID where Revenue>(select Revenue from Salary where SaID='102201')
--查找比所有财务部的员工的收入都高的员工的姓名
select a.EmpName from Employees as a inner join Salary as b on a.EmpID=b.SaID where Revenue>(select max(b.Revenue) from Salary as b join Employees as a on a.EmpID=b.SaID where DepID='1')
--求财务部员工的平均净收入
select avg(b.revenue) as 财务部员工的平均净收入 from Employees as a inner join Salary as b on a.EmpID=b.SaID where DepID='1'
--求财务部门总人数
select COUNT(DepID)as 财务部门总人数 from Employees where DepID='1'
--查询购买

了商品的雇员的总数
select COUNT(distinct purid)as 购买商品雇员总人数 from Purchase
--Group by,Order by子句的使用
--（求各部门的雇员数）
select count (a.depid) as 部门人数 ,b.DepName from Employees as a join Departments as b on a.DepID=b.DepID group by (DepName)
--（将各雇员的情况按薪水由低到高排列）
select a.*,b.Revenue from Employees as a inner join Salary as b on a.EmpID=b.SaID order by Revenue
--将总收入大于6000的部门中的雇员收入减少5%
select a.EmpName,DepID,b.revenue,(revenue*0.95) as 减少后的工资 from Employees as a join Salary as b on a.EmpID=b.SaID and DepID
in (select a.DepID as 部门 from Employees as a inner join Salary as b on a.EmpID=b.SaID group by DepID having SUM(Revenue)>6000)
--查询收入与支出差值最大的雇员信息
select a.*,b.Revenue-Expense as 差值 from Employees as a join Salary as b on a.EmpID=b.SaID where Revenue-Expense
in
(select MAX(revenue-expense) from Salary)
--查询所属员工平均净收入低于2000元的部门及其薪水情况
select * from Employees,Salary where EmpID=SaID and DepID
in (select a.DepID from Employees as a join Salary as b on a.EmpID=b.SaID group by DepID having AVG(Revenue-Expense)<2000)
--第二种方法
select * from Employees,Salary where EmpID=SaID
and DepID
in
( select DepID from Employees,Salary where EmpID=SaID group by DepID having avg(Revenue-Expense)<2000)