数据查询报告.docx

1、数据查询报告实验1 SQL Server 2005 环境(1) 对象资源管理器的使用 进入“SQL Server Management Studio”。 图1.1了解系统数据库和数据库的对象。 图1.2试试不同数据库对象的操作功能 图1.3(3) 查询分析器的使用use studentsysselect * from tblstudentGo 图1.4（4）了解SQL Server Management Studio 中其他窗口的使用方法。图1.5实验2 创建数据库和表（1）在“对象资源管理器”中创建数据库YGGL。图2.1设定数据库的增长方式 如图2.2图2.2图2.3图2.4图2.5（2）

2、删除YGGL数据库 USE masterGODROP DATABASE YGGL（3）使用T-SQL语句创建数据库YGGLcreate database YGGLon( name=YGGL_data,filename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataYGGL.mdf,size=10MB,maxsize=50MB,filegrowth=5%)log on( name=YGGL_log,filename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataYGGL_log.ldf

3、,size=2MB,maxsize=5MB,filegrowth=1MB)Go（4）使用T-SQL语句创建表use YGGLgo create table employees( employeeID char(6)not null primary key,Name char(10)not null,Education char(4)not null,Birthday datetime not null,Sex bit not null default 1,WorkYear tinyint null,Address varchar(40) null,PhoneNumber char(12) nu

4、ll,DepartmentID char(3) not null)Gouse YGGLgo create table Departments( DepartmentID char(3)not null primary key, DepartmentName char(20)not null, Note varchar(100) null)gouse YGGLcreate table Salary( EmployeeID char(6)not null primary key, InCome float not null, OutCome float not null)go实验3 表数据插入、修

5、改和删除（1）在“对象资源管理器”中初始化数据库YGGL中所有表的数据。图3.1图3.2图3.3（2）使用T-SQL命令插入表数据。删除use YGGLgoinsert into employees values(000001,王林,1956-01-23,True,中山路-1-508,210003,335568,null,2,)插入use YGGLgoinsert into Salary(employeeID,Income,Outcome)values(000001,2100.8,123.09)(4)使用SQL语句修改表数据。使用SQL命令修改表Salary中的某个记录的字段值：update

6、salaryset income=2890where employeeid=000001执行上述语句，将编号为000001的职工收入改为2890将所有职工收入增加100：update salaryset income=income+100;执行完，打开Salary表查看数据的变化。使用SQL命令删除表Employmees中编号为000001的职工信息：delete from employees where employeeid=000001删除所有女性员工信息：delete from employees where sex=0使用TRANCATE TABLE语句删除表中所有行：truncate

7、 table salary执行上述语句，将删除Salary表中的所有行。实验4 数据库的查询和视图（3）SELECT语句的基本使用。 对于实验2给出的数据库表结构，查询每个雇员的所有数据。 新建一个查询，在“查询分析器”窗口中输入如下语句并执行：use YGGLselect *from employeesgo用SELECT语句查询Employees表中每个雇员的地址和电话。新建一个查询，在“查询分析器”窗口中输入如下语句并执行：use YGGLgoselect address,phonenumberfrom employees查询EmployeeID为000001的雇员的地址和电话。use Y

8、GGLgoselect address,phonenumberfrom employeeswhere employeeid=000001go查询Employees表中男雇员的地址和电话，使用as子句将结果中各列的标题分别指定为地址、电话。use YGGLgoselect address as 地址,phonenumber as 电话from employeeswhere sex=1go 查询Employees表中员工姓名和性别，要求Sex值为1时显示“男”，为0时显示为“女”。select name as 姓名,casewhen sex=1 then 男end as 性别from employ

9、ees 计算每个员工的实际收入。use YGGL goselect employeeID,实际收入=income-outcomefrom salary获得员工总数。select count(*)from employees找出所有姓王的雇员的部门号。use YGGL goselect departmentidfrom employeeswhere name like 王_找出所有收入在20003000之间的员工的号码。use YGGL goselect employeeidfrom salarywhere income between 2000 and 3000使用INTO子句，由表Salar

10、y创建“收入在1500以上的员工”表，包括编号和收入。use YGGL goselect employeeID as 编号,income as 收入into 收入在以上的员工from salarywhere income1500（4）子查询的使用。1查找在财务部工作的雇员的情况。use YGGL Goselect * from employees,departments where dbo.employees.departmentID=dbo.departments.departmentID and departmentName=财务部2查找财务部年龄不低于研发部雇员年龄的雇员的姓名。use

11、YGGL goselect namefrom employeeswhere departmentid in( select departmentid from departments where departmentname=财务部)andbirthday !all( select birthday from employees where departmentid in( select departmentid from departments where departmentname=研发部) 3查找比所有财务部的雇员收入都高的雇员的姓名。use YGGL goselect namefro

12、m employeeswhere departmentid in( select departmentid from salary where incomeall( select income from salary where departmentid in( select employeeid from employees where departmentid=( select departmentid from departments where departmentname=财务部)（3）连接查询的使用。1查询每个雇员的情况及其薪水的情况。use YGGL goselect emplo

13、yees.*,salary.*from employees,salarywhere employees.employeeid=salary.employeeid2使用内连接的方法查询名字为“王林”的员工所在的部门。use YGGL goselect departmentnamefrom departments join employeeson departments.departmentid=employees.departmentidwhere employees.name=王林3查找财务部收入在2000以上的雇员姓名及其薪水详情。use YGGL goselect name,income,

14、outcomefrom departments ,salary, employeeswhere employees.employeeid=salary.employeeidand employees.departmentid=departments.departmentidand departmentname=财务部and income2000（5）聚合函数的使用1求财务部雇员的平均收入。use YGGL goselect avg(income)as财务部平均收入from salarywhere employeeid in( select employeeid from employees w

15、here departmentid=( select departmentid from departments where departmentname=财务部 )2求财务部雇员的平均实际收入。use YGGL goselect avg(income-outcome)as财务部雇员平均实际收入from salarywhere employeeid in( select employeeid from employees where departmentid=( select departmentid from departments where departmentname=财务部 )3求财

16、务部雇员的总人数。use YGGL goselect count(employeeid)from employeeswhere departmentid=( select departmentid from departments where departmentname=财务部) （6）GROUP BY 、ORDER BY 子句的使用。1查找Employees表中男性和女性的人数。use YGGL goselect sex,count(sex)from employeesgroup by sex;2查找员工数超过2人的部门名称和员工数量。use YGGL goselect employees

17、.departmentid,count(*)as 人数from employees,departmentswhere employees.departmentid=departments.departmentidgroup by employees.departmentidhaving count(*)23将雇员的情况按收入由高到低排列。use YGGL goselect employees.*,salary.*from employees,salarywhere employees.employeeid=salary.employeeidOrder by income 实验4.2 视图的使用

18、实验内容（1）创建视图1创建YGGL数据库上的视图DS_VIEW，视图包含Departments表的全部列。create view DS_VIEWas select* from departments2创建YGGL数据库上的视图Employees_view，视图包含员工号码、姓名和实际收入三列。create view Employees_view(Employeeid,name,realincome)as select employees.employeeid,name,income-outcomefrom employees,salarywhere employees.employeeid=

19、salary.employeeid（2）查询视图。1从视图DS_VIEW中查询出部门号为3的部门名称。select departmentnamefrom ds_viewwhere departmentid=32从视图Employees_view查询出姓名为“王林”的员工的实际收入。select realincomefrom employees_viewwhere name=王林（3）更新视图。1向视图DS_VIEW中插入一行数据：“6，广告部，广告业务”。insert into ds_view values(6,广告部,广告业务) 2修改视图DSVIEW，将部门号为5的部门名称修改为“生产车间”。update ds_view set departmentname=生产车间where departmentid=53修改视图Employees_view中员工号为“000001”的员工的姓名为“王浩”update employees_view set name=王浩where employeeid=0000014删除视图DS_VIEW中部门号为“1”的一行数据。delete from ds_viewwhere departmentid=1（4）删除视图DS_VIEW。drop view ds_view