实列数据库1.doc

实验4 数据库的查询和试图T4。1 数据库的查询（1） SELECT 语句的基本使用 1。对于实验2给出的数据库表结构,查询每个雇员的所有数据【思考与练习】用SELECT 语句查询Departments和Salary表中所有的数据信息 2.用SELECT语句查询Employees表中每个雇员的地址和电话【思考与练习】a.用SELECT语句查询Departments和salary表中的一列或若干列 b。查询Employees表中的部门号和性别,要求使用DISTINCT消除重复行3。查询EmployeeID为000001的雇员的地址和电话【思考与练习】a。查询月收入高于2000元的员工号码b.查询1970年以后出生的员工的姓名和地址c。查询所有财务部的员工的号码和姓名4。查询Employees表中的女雇员的地址和号码，使用AS句子将结果中各列的标题分别指定为地址和号码【思考与练习】查询Employees表中的男员工的姓名和出生日期，要求将各列标题用中文表示5。查询Employees表中的姓名和性别,要求sex值为1时显示为男,为0时显示为女【思考与练习】查询Employees员工的姓名,地址和收入水平，2000元以下显示为低收入,20003000元显示为中等收入,3000元以上显示为高收入6。计算每个雇员的实际收入【思考与练习】使用select语句进行简单的计算7.获得员工总数【思考与练习】a.计算salary表中员工月收入的平均数b。获得Employees表中最大的员工号码c。计算salary表中所有员工的总支出d。查询财务部雇员的最高和最低实际收入8。找出所有姓王的雇员的部门号【思考与练习】a.找出所有地址中含有“中山”的雇员的号码及部门号b.查找员工号码中倒数第2个为0的员工的姓名，地址和学历9.找出所有收入在20003000的员工号码【思考与练习】找出所有在部门“1”或“2"工作的雇员的号码10。使用INTO子句，由表salary创建“收入在1500以上的员工"表，包括编号和收入【思考与练习】使用INTO子句，由表Employees创建“男员工”表，包括编号和姓名（2） 子查询的使用1。查找在财务部工作的雇员的情况【思考与练习】用子查询的方法查找所有收入在2500元以下的雇员的情况2.查找财务部年龄不低于研发部雇员年龄的雇员的姓名【思考与练习】用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名（3） 连接查询的使用1.查询每个雇员的情况及其薪水情况【思考与练习】查询每个雇员的情况及其工作部门的情况2。使用内连接的方法查询名字为“王林”的员工所在的部门【思考与练习】a使用内连接方法查找出不在财务部的所有员工信息b.使用外连接方法查找出所有员工的月收入3。查找财务部收入在2000元以上的雇员姓名及其薪水情况【思考与练习】查询研发部在1976年以前出生的雇员姓名及其薪水情况（4） 聚合函数的使用 1。求财务部雇员的平均收入【思考与练习】查询财务部雇员的最高与最低收入2.求财务部雇员的平均实际收入【思考与练习】查询财务部雇员的最高和最低实际收入（5） GROUP BY,ORDER BY子句的使用1。查找Employees表中男性和女性的人数 【思考与练习】a。按部门列出在该部门工作的员工的人数b。按员工的学历分组,排列出本科，大专和硕士的人数2。查找员工人数超过2的部门名称和员工数量【思考与练习】按员工的工作年份分组，统计各个工作年份的人数,列如，工作1年的多少人，工作2年的多少人3。将各雇员的情况按收入由低到高排列【思考与练习】a。将员工信息按出生时间从小到大排列b。在order by子句中使用子查询，查询员工姓名,性别和工龄信息，要求按实际收入从小到大排列T4.2 视图的使用（1） 创建视图1。创建YGGL数据库上的视图DS_VIEW，视图包含Departments表的全部列2.创建YGGL数据库上的视图Employees_view，视图包括员工号码,姓名和实际收入3列。【思考与练习】a。在创建视图时SELECT语句有哪些限制？ b。在创建视图时有哪些注意点？c。创建视图，包含员工号码，姓名,所在部门名称和实际收入（2)查询视图1. 从视图DS_VIEW 中查询出部门号为3的部门名称2.从视图Employees_view中查询出姓名为“王林”的员工的实际收入【思考与练习】a.若视图关联了某表中的所有字段,而此时该表中添加了新的字段,视图能否查询到改字段b.自己创建一个视图,并查询视图中的字段 (3）更新视图在更新视图前需要了解可更新视图的概念，了解什么视图是不可以进行修改的。更新视图真正更新的是合视图关联的表1. 向视图DS_VIEW中插入一行数据“6，广告部,广告业务部".执行完该命令,使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化.尝试向视图Employees_view中插入一行数据，看看会发生什么情况2. 修改视图DS_VIEW，将部门号为5的部门名称改为“生产车间" 执行完命令，使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化3. 将试图Employees_view中员工号为“000001”的员工的姓名修改为“王浩"4. 删除视图DS_VIEW中部门号为“1"的一行数据【思考与练习】视图Employees_view中无法插入和删除数据，其中的RealIncome字段也无法修改,为什么?（4）删除视图 删除视图DS_VIEW【思考与练习】总结试图与基本表的差别实验6 索引和数据完整性（1） 建立索引1. 使用CREATE INDEX语句创建索引。对YGGL数据库的Employees表中的DepartmentID列建立索引在Employees表中的name列和address列上建立复合索引对Departments表上的DepartmentName列建立唯一非聚集索引【思考与练习】a.索引创建后在对象资源管理器中查看标准妈的索引b.了解索引的分类情况c.使用CREATE INDEX语句语句能创建主键吗？d。在什么情况下可以看到建立索引的好处（2）重建索引重建表Employees中的所有索引【思考与练习】重建表Employees中EmployeeID列的索引（3)删除索引使用DROP INDEX语句删除表Employees上的索引depart_ind【思考与练习】使用DROP INDEX一次删除Employees表上的多个索引使用界面方式删除表Departments上的索引（4)数据完整性1.创建一个表Employees5,只含EmployeeID,Name，Sex和Education列。将Name作为主键,作为列Name的约束。对EmployeeID列进行UNIQUE约束，并作为表的约束2. 删除上列中创建的UNIQUE约束.【思考与练习】使用T-SQL命令创建一个新表，使用一个复合列作为主键，作为表的约束，并为其命名使用ALTER TABLE 语句为表Employees5添加一个新列Address,并为该列定义UNIQUE约束使用界面方式为一个新表定义主键和UNIQUE约束,并了解如何使用图形向导方式删除主键和UNIQUE约束3。创建新表student，只考虑“号码”和“性别"两列，性别只能包含男或女【思考与练习】向该表插入数据,“性别”列插入“男”和“女以外的字符，查看会发生什么情况4。创建新表salary2，结构与salary相同,但salary2表不允许Outcome列大于Income列【思考与练习】a。向表中插入数据，查看outcome值大于income值会发生什么情况.b。创建一个新表Employees6，只考虑“学号”和“出生日期”两列，出生日期必须晚于1980年1月1日5.对YGGL数据库的Employees表进行修改,为其增加“DepartmentID"字段的CHECK约束【思考与练习】测试CHECK约束的有效性6。创建一个规则对象,用以限制输入到该规则所绑定的列中的值只能说该规则中列出的值【思考与练习】a.建立一个规则对象,限制值在020,然后把它绑定到Employees表的workyear字段上b.删除上述建立的规则对象7。创建一个表salary3,要求所有salary3表上的EmployeeID列的值都要出现在salary中,利用参照完整性约束实现，要求当删除或修改salary表上的EmployeeID列时,salary3表中的EmployeeID值也会随之变化【思考与练习】a。创建salary3表后，初始化该表的数据与salary表相同。删除salary表中一行数据,再查看salary3的内容，看看会发生什么情况。b。使用ALTER TABLE 语句向salary表中的EmployeeID列上添加一个外键，要求当Employees表中要删除或修改与EmployeeID值有关的行时，检查salary表有没有与该EmployeeID值相关的记录,如果存在，则拒绝更新Employees表c.在对象资源管理器中建立Departments，Employees和salary3表之间的参照关系实验7 存储过程和触发器(1)存储过程1.创建存储过程，使用Employees 表中的员工人数来初始化一个局部变量，并调用这个存储过程执行该存储过程，并查看结果2。创建存储过程，比较两个员工的实际收入,若前者比后者高就输出0，否则输出1执行该存储过程，并查看结果3.创建添加职员记录的存储过程EmployeeAdd执行该存储过程4.创建存储过程，使用游标确定一个员工的实际收入是否排在前3名.结果为1表示是，结果为0表示否。执行该存储过程,并查看结果【思考与练习】a。创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室。b.创建存储过程，根据每个员工的学历将收入提高500元.c.创建存储过程,使用游标计算本科及以上学历的员工在总员工数中所占的比例d。使用命令方式修改存储过程的定义.（2)触发器1。向employee表插入或修改一个记录时，通过触发器检查记录的departmentID 值在department表中是否存在,若不存在，则取消插入或修改操作向employee表插入或修改一行记录，查看效果2。修改department表departmentID字段值时，该字段在employee表中的对应值也做相应修改3.删除department表中记录的同时删除该记录departmentID字段值在employee表中对应的记录4.创建INSTEAD OF触发器，当向salary表中插入记录时，先检查employeeID列上的值在employee中是否存在，如果存在则执行插入操作，如果不存在则提示“员工号不存在"5.创建DDL触发器，当删除YGGL数据库的一个表时,提示“不能删除表”，并回滚删除表的操作【思考与练习】a。对于YGGL数据库，表Employee的EmployeeID列与表SAlary的EmployeeID列应满足参照完整性规则,请用触发器实现两个表间的参照完整性。b。.当修改表Employee时，若将Employee表中员工的工作时间增加1年,则将收入增加500，若增加2年则增加1000,一次增加。若工作时间减少则无变化。c。创建UPDATE触发器，当向salary表中Income值增加500时，outCome值则增加50。d.创建INSTEAD OF触发器，实现向不可更新视图插入数据e.创建DDL触发器，当删除数据库时,提示“无法删除”并回滚删除操作