--创建 MyCompany数据库use masterexecute sp_configure ‘show advanced options‘,1  --开启权限reconfigureexecute sp_configure ‘xp_cmdshell‘,1reconfigureexecute xp_cmdshell ‘mkdir e:\作业数据库‘--自动创建文件夹execute sp_configure ‘xp_cmdshell‘,0--关闭权限,先关reconfigureexecute sp_configure ‘show advanced options‘,0 后关,一句话,先开后关reconfigureif exists(select * from sysdatabases where name=‘MyCompany‘)drop database MyCompanygocreate database MyCompanyon 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--	dNameuse MyCompanyif exists(select * from sysobjects where name=‘department‘)drop table departmentgocreate 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 employeegocreate 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 employeeadd 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 employeeunion allselect ‘平均工资‘,avg(eSalary)    from  employeeunion allselect ‘最高工资‘,max(eSalary)    from  employee--这个方式也行select eName,eSalary from employeeunion allselect  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 表名 --   强迫症而已