SQL语句创建数据库以及一些查询练习

--创建 MyCompany数据库



use master

execute sp_configure ‘show advanced options‘,1  --开启权限
reconfigure
execute sp_configure ‘xp_cmdshell‘,1
reconfigure

execute xp_cmdshell ‘mkdir e:\作业数据库‘--自动创建文件夹
execute sp_configure ‘xp_cmdshell‘,0--关闭权限,先关
reconfigure
execute sp_configure ‘show advanced options‘,0 后关,一句话,先开后关
reconfigure

if exists(select * from sysdatabases where name=‘MyCompany‘)
drop database MyCompany
go
create database MyCompany
on primary
(
name=‘MyCompany_data‘,
size=3mb,
fileGrowth=10%,
Maxsize=100mb,
filename=‘e:\作业数据库\MyCompany_data.mdf‘
)
log on
(name=‘MyCompany_log‘,
size=3mb,
fileGrowth=10%,
Maxsize=100mb,
filename=‘e:\作业数据库\MyCompany_log.ldf‘
)
--部门表 department
--	dId
--	dName
use MyCompany
if exists(select * from sysobjects where name=‘department‘)
drop table department
go
create table department
(dId int not null primary key identity(1,1),
dName nvarchar(50) not null

)
--员工表 employee
--	eId
--	eName
--	eSex
--	eAge
--	eSalary
--	eDepId
--	eInTime 入职时间

if exists(select * from sysobjects where name=‘employee‘)
drop table employee
go
create table employee
(    eId int not null primary key identity(1,1),
     eName nvarchar(50),
     eSex char(2),
     eAge int,
     eSalary money,
     eDepId int,
     eInTime datetime

)
添加外键FK_employee_department_eDepId

alter table employee
add constraint FK_employee_department_eDepId foreign key(eDepId) references  department(dId)

insert into  department values (‘教学部‘)
insert into  department values (‘业务部‘)
insert into  department values (‘学工部‘)
  
   insert into  employee values (‘李定‘,‘男‘,28,5000,1,‘2014-8-8‘)
   insert into  employee values (‘张月月‘,‘女‘,28,5000,2,‘2013-8-8‘)
   insert into  employee values (‘李定山‘,‘男‘,18,3000,3,‘2014-8-1‘)
   insert into  employee values (‘张三‘,‘男‘,18,1800,3,‘2014-7-1‘)
   insert into  employee values (‘张三1‘,‘女‘,18,1800,3,‘2013-7-1‘)

   insert into  employee values (‘张三2‘,‘女‘,28,4800,2,‘2011-7-1‘)

--建库  建表  建约束  添加测试数据    更新 和删除
--1、查询所有员工
 select * from  employee

--2、查询工资超过2000快钱的员工
 select * from  employee where  eSalary>2000
--3、查询最新进来的5个员工  ---降序排序
 select top 5 * from  employee order by eInTime desc

--4、查询员工的平均工资 select avg() from 
  select avg(eSalary) as 平均工资 from  employee


--5、查询总共有多少员工 count
  select  count(*) as 员工数 from  employee



--6、查询每个部门有多少员工 ---按部门分组
  select eDepId,count(*) as  from  employee   group by eDepId


--7、查询每个部门的平均工资
  select eDepId,avg(eSalary)  from  employee   group by eDepId


--8、查询每个部门男员工的平均工资
  select eDepId,eSex,avg(eSalary)  from  employee where eSex=‘男‘   group by eDepId,eSex

--9、查询平均工资超过2000的那些部门  having 对分组之后的数据再进行筛选

  select eDepId,avg(eSalary)  from  employee   group by eDepId having avg(eSalary)>200

--10、查询员工工资（姓名，工资）的同时，同一结果集显示平均工资和最高工资
--select ‘ ‘+姓名，工资 from 表
--union
--select cast(平均工资 as varchar()),最高工资 from 表
select eName,eSalary from employee
union all

select ‘平均工资‘,avg(eSalary)    from  employee
union all
select ‘最高工资‘,max(eSalary)    from  employee
--这个方式也行
select eName,eSalary from employee
union all
select  convert(varchar(20),avg(eSalary)) ,max(eSalary)    from  employee
--11、查询名字里包含‘定,月‘的这些员工，并对结果按照年龄排序

select * from  employee where eName like‘%三%‘ or eName like ‘%定%‘order by eAge

----好吧没有做更新和删除

update 表名 set 字段=新赋值 where 字段=‘’
例如：把张三的名字改为逗碧

update employee set eName=‘逗碧‘ where  eName=‘张三‘

delete table 表名  ===等开除吧，后面加where 否则全删除 -

--删除数据时候 把自增长列的值还原成种子

   truncate table 表名 --   强迫症而已