40900602邱敏.docVIP

数据定义 1.创建数据库 create database YGKQ on (name=YGKQ_data, filename=C:\Program Files\Microsoft SQL Server\MSSQL\data\YGKQ_Data.MDF, size=10MB, MAXSIZE=50MB, FILEGROWTH=5% ) LOG ON ( NAME=YGKQ_log, filename=C:\Program Files\Microsoft SQL Server\MSSQL\data\YGKQ_Data.MDF, size=2MB, maxsize=5MB, FILEGROWTH=1MB ) 2.建表 create table JBQK ( 员工号 char (4)primary key, 姓名 char(8), 缺勤时间段 datetime, 缺勤天数 int , 缺勤类型 char (4), 缺勤理由 char (20) ) create table QQLX ( 缺勤类型 char (4) primary key, 缺勤名称 char (8), 缺勤描述 char (60) ) 3.插入数据 insert into JBQK values (001,张三,2010-08-03,3,1,事假) insert into JBQK values (002,李四,20010-08-12,2,2,病假) insert into JBQK values (003,王景,2010-08-13,5,3,旷工) insert into JBQK values (004,项林,20010-08-15,2,1,事假) insert into JBQK values (005,钟凯,2010-08-16,1,2,病假) insert into QQLX values (1,事假,本人必须提前1天申请，1-2天由部门批准，2天以上由经理批准) insert into QQLX values (2,病假,1－2天由部门批准，2天以上由经理批准) insert into QQLX values (3,旷工,无故不到者按旷工处理) insert into QQLX values (4,迟到,在规定上班时间1小时后到岗) 4.创建索引 create clustered index myindex on JBQK(缺勤天数) 数据更新（插入、修改和删除） 插入数据 insert into JBQK values (001,张三,2010-08-03,3,1,事假) insert into JBQK values (002,李四,20010-08-12,2,2,病假) insert into JBQK values (003,王景,2010-08-13,5,3,旷工) insert into JBQK values (004,项林,20010-08-15,2,1,事假) insert into JBQK values (005,钟凯,2010-08-16,1,2,病假) 修改数据 update JBQK set 姓名=’张三’ where ‘缺勤天数’=4 update JBQK set 姓名=’李四’ where ‘缺勤类型’=’旷工’ update JBQK set 员工号=’005’ where ‘缺勤天数’=4 update JBQK set 员工号=’009’ where ‘缺勤类型’=’事假’ update QQLX set ‘缺勤名称’=’ 事假’ where ‘缺勤类型’=’4’ 删除数据 delete JBQK where ’员工号’=’008’ delete JBQK where ‘姓名’=’项林’ delete JBQK where ‘员工号’=’010’ delete QQLX where ‘缺勤名称’=’旷工’ delete QQLX where ’缺勤类型’=’1’ 数据查询 select 员工号,姓名,缺勤天数 from JBQK select 姓名,缺勤天数 from JBQK where ‘员工号’=001 select 员工号,缺勤类型 from JBQK where ‘姓名’=’李%’ select 缺勤天数 from JBQK where ‘缺勤天数’ between 2 and 3 select 员工号，姓名 from JBQK where ‘缺勤类型’=’病假’ select员工号,缺勤类型 from JBQK where ‘缺勤天数’ =2 select count(*) from JBQK where 缺勤类型=事假 select count(*) from JBQK