数据库实验2

实验二：交互式SQL一、创建数据库及表1、创建数据库create database HRMGo2、创建表(1)创建Departments表create table Departments(DepartmentID char(3)not null primary key,DepartmentName char(20)not null,Note Text)(2)创建Employee表create table Employee(EmployeeID char(6)not null primary key,Name char(10)not null,Birthday Datetime not null,Sex bit not null,Address char(20),Zip char(6),PhoneNumber char(12),EmailAddress char(30),DepartmentID char(3)not null foreign key references Departments)(3)创建Salary表create table Salary(EmployeeID char(6)not null foreign key references Employee, Income float(8)not null,OutCome float(8)not null)3、举例说明如何操作会违背完整性（实体完整性、参照完整性）(1)实体完整性如上面的Departments表，DepartmentID是主键，不能为空，当插入数据时，如果DepartmentID为空，则会报错，也就违背了实体完整性。

DepartmentID不为空时，则能成功插入：(2)参照完整性如上面的Salary表，EmployeeID是Employee的外键，当向Salary 表插入数据时，如果插入记录的EmployeeID是Emploree表中没有的数据，则会报错，即违背了参照完整性。

二、SQL数据查询语句1、数据插入（1）插入记录到Departmentsinsert into Departmentsselect'1','财务部','财务部'union allselect'2','研发部','研发部'union allselect'3','人力资源部','人力资源部';（2）插入记录到Employeeinsert into Employeeselect'1001','李勇','78-3-12','0','河南','475001','3880378','ly@','1'union all select'1002','王敏','80-11-2','1','河南','475002','0378311','wm@','1'union all select'1003','刘晨','78-6-22','0','河南','475003','0378322','lc@','1'union all select'2001','张立','78-8-1','0','河南','475004','0378333','zl@','2'union allselect'2002','刘毅','82-1-23','0','河南','475005','0378344','ly@','2'union all select'2003','张玫','81-3-15','1','河南','475006','0378355','zm@','2'union all select'3001','徐静','76-8-12','1','河南','475007','0378366','xj@','3'union all select'3002','赵军','79-2-19','0','河南','475008','0378377','zj@','3';（3）插入记录到Salaryinsert into Salaryselect'1001','3600','1500'union allselect'1002','3300','1000'union allselect'1003','3700','1200'union allselect'2001','4000','1600'union allselect'2002','3800','1800'union allselect'2003','3800','1500'union allselect'3001','4200','2000'union allselect'3002','4100','1800'2、数据查询（1）简单的数据查询2-d：select Address as"地址",PhoneNumber as"电话"from Employee where Sex='1'; 2-e：select EmployeeID,Income-OutCome as"工资"from Salary;2-f：select DepartmentID from Employee where Name like'王%';思考：select DepartmentID,PhoneNumber from Employee where Address like'%中山%'; （2）多表连接查询和嵌套查询3-a:Select Employee.EmployeeID,Name,Birthday,Sex,Address,Zip,PhoneNumber,EmailAddress,DepartmentID,Income-OutCome as"工资"from Employee,Salary;3-b：select Name,Income-OutCome as"工资"from Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID and Income-OutCome>=2000and DepartmentID in(select DepartmentID from Departmentswhere DepartmentName='财务部');3-c：select Name,Income,OutCome from Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID and Birthday<'1966'and DepartmentID in (select DepartmentID from Departmentswhere DepartmentName='研发部');3-d：select MAX(Income-OutCome)as"最高工资",Min(Income-OutCome)as"最低工资"from Salary,Employeewhere Employee.EmployeeID=Salary.EmployeeID and DepartmentID in(select DepartmentID from Departments where DepartmentName='人力资源部');3-e：select Employee.EmployeeID,Name,Sex,Address,Zip,PhoneNumber,EmailAddress,DepartmentID,Income-OutCome'工资'from Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID order by Income-OutCome ASC;3-f：select DepartmentID,COUNT(EmployeeID)from Employee group by DepartmentID;3-g：select EmployeeID from Departments,Employeewhere Departments.DepartmentID=Employee.DepartmentID and DepartmentName='人力资源部'or DepartmentName='财务部';3-h：select COUNT(EmployeeID)'人数' from Salary,Departmentswhere DepartmentName='人力资源部'and Income-Outcome>='2500';3-i：select COUNT(EmployeeID)'人数' from Employee,Departmentswhere DepartmentName='财务部'and Departments.DepartmentID=Employee.DepartmentID; 3-j：select A VG(Income-OutCome)'平均工资' from Salary,Employeewhere Employee.EmployeeID=Salary.EmployeeID and DepartmentID in(select DepartmentID from Departments where DepartmentName='财务部');3-k：select Name姓名from Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID and Income-OutCome>(select MAX(Income-OutCome) from Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID and DepartmentId in(select DepartmentID from DepartmentS where DepartmentName='财务部'));3-l：select Name姓名from Employee,Departmentswhere Employee.DepartmentID=Departments.DepartmentID and DepartmentName='财务部'and Birthday<=(select Min(Birthday) from Employee,Departmentswhere Employee.DepartmentID=Departments.DepartmentID and DepartmentName='研发部'); 3-m：select* from Employee where DepartmentID in(select DepartmentID from Departments where DepartmentName='财务部');三、视图的定义和操作1、视图定义语句1-4：create view Employee_Na_Dep_Sal asselect Name,DepartmentName,Income-OutCome'工资'from Employee,Salary,Departmentswhere Employee.EmployeeID=Salary.EmployeeIDand Departments.DepartmentID=Employee.DepartmentID;1-5：create view Employee_Info asSelectEmployee.EmployeeID,Name,Birthday,Sex,Address,Zip,PhoneNumber,EmailAddress,Departmen tID from Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID and Income-OutCome>(select MAX(Income-OutCome)from Employee,Salarywhere Employee.EmployeeID=Salary.EmployeeID and DepartmentId in(select DepartmentID from DepartmentS where DepartmentName='财务部'));1-6：create view Employee_Info_6 asselect EmployeeID,Name,datediff(year,Birthday,getdate())as年龄from Employee;2、查询视图与更新视图（1）查询视图Employee_Info_6中年龄大于35的员工select*from Employee_Info_6where年龄>35;或者：select * from Employee_Info_6 where CONVERT(int,年龄)>35;（2）更新不成功例子视图中如果使用了AVG、max等函数，即有对数据的二次加工性质的操作时，不允许更新；而简单的SQL形成的视图的灵活性则没有限制!四、SQL数据更新语句1、SQL更新语句1-3：create table TotalQTY (SNO nchar(2)foreign key references S,totalQTY int) insert into TotalQTY select SNO,SUM(QTY) from SPJ group by SNO;1-5：set rowcount 4update SPJ set QTY=300;或者update top(4)SPJ set QTY=300;1-10：alter table SPJ add SDATE datetime;update SPJ set SDATE=GETDATE();。