实例(工资).txt

经典的sql资料

-- 上课内容：设计数据库的方法

 -- 设计数据库要做哪些工作

  -- 需求分析：

  -- 可行性研究：

  -- 设计表：

    -- 定义表结构

    -- 为数据完整性设置约束、规则、默认等等
    
  -- 数据的操作：数据整理（插入、修改、删除、查询）

  -- 完成最基本的预定功能：

    -- 数据的维护

    -- 权限的设置

    -- 统计汇总功能


 -- 实例：员工工资数据库

  -- 表结构的定义：

   -- 部门表 deptment(dept_id int,dept varchar(20))

   -- 员工表 emp(emp_id int,emp_name varchar(10)，e_deptid int)

   -- 工资表 gongzi(emp_id int not null,g_jiben decimal(10,2) 基本工资,
             -- g_gangwei decimal(10,2) 岗位工资,g_jixiao decimal(10,2) 绩效工资,g_koufa char(1)，
             -- g_shifa decimal(10,2) 实发工资，g_date datetime not null)
             
 -- 数据完整性约束

   -- 对于deptment表，设置dept_id为主键

   -- 对于emp表，设置emp_id为主键,e_deptid 为参照deptment表dept_id字段的外键

   -- 对于gongzi表，我们要给(emp_id,g_date)一起来设主键;要设置 emp_id 为参照emp表emp_id字段的外键

 -- 插入、修改数据
create table deptment(
dept_id int primary key,
dept varchar(20)
)

create table emp(
emp_id int primary key,
emp_name varchar(10),
e_deptid int
)

create table gongzi(
	emp_id   int not null,         --员工号
        g_jiben  decimal(10,2),        --基本工资
        g_gangwei  decimal(10,2),      --岗位工资
        g_jixiao  decimal(10,2),       --绩效工资
	g_koufa  decimal(10,2),        --扣发工资
        g_shifa  decimal(10,2),        --实发工资
        g_date datetime not null
        )

    insert into deptment values(1,'销售部')
    insert into deptment values(2,'采购部')
    insert into deptment values(3,'财务部')


    insert into emp values(1,'王晓明',1)
    insert into emp values(2,'张小虎',1)
    insert into emp values(3,'黄佳佳',1)
    insert into emp values(4,'李莉莉',2)
    insert into emp values(5,'赵蕊',2)
    insert into emp values(6,'黄菲',2)
    insert into emp values(7,'李宇',3)
    insert into emp values(8,'赵新',3)

    insert into gongzi values(1,1100,1000,1000,500,0,'20060101')
    insert into gongzi values(2,1500,1500,1500,700,0,'20060101')
    insert into gongzi values(3,2000,2000,2000,1000,0,'20060101')
    insert into gongzi values(4,1200,1200,500,550,0,'20070601')
    insert into gongzi values(5,3000,3000,3000,1500,0,'20060101')
    insert into gongzi values(6,2000,2000,2000,1000,0,'20060101')
    insert into gongzi values(7,1200,1200,500,550,0,'20070601')
    insert into gongzi values(8,3000,3000,3000,1500,0,'20060101')
    insert into gongzi values(1,1100,1000,1000,500,0,'20060201')
    insert into gongzi values(2,1500,1500,1500,700,0,'20060201')
    insert into gongzi values(3,2000,2000,2000,1000,0,'20060201')
    insert into gongzi values(4,1200,1200,500,550,0,'20060201')
    insert into gongzi values(5,3000,3000,3000,1500,0,'20060201')
    insert into gongzi values(6,2000,2000,2000,1000,0,'20060201')
    insert into gongzi values(7,1200,1200,500,550,0,'20060201')
    insert into gongzi values(8,3000,3000,3000,1500,0,'20060201')

    insert into gongzi values(1,1100,1000,1000,500,0,'20070101')
    insert into gongzi values(2,1500,1500,1500,700,0,'20070101')
    insert into gongzi values(3,2000,2000,2000,1000,0,'20070101')
    insert into gongzi values(4,1200,1200,500,550,0,'20070101')
    insert into gongzi values(5,3000,3000,3000,1500,0,'20070101')
    insert into gongzi values(6,2000,2000,2000,1000,0,'20070101')
    insert into gongzi values(7,1200,1200,500,550,0,'20070101')
    insert into gongzi values(8,3000,3000,3000,1500,0,'20070101')
    insert into gongzi values(1,1100,1000,1000,500,0,'20070201')
    insert into gongzi values(2,1500,1500,1500,700,0,'20070201')
    insert into gongzi values(3,2000,2000,2000,1000,0,'20070201')
    insert into gongzi values(4,1200,1200,500,550,0,'20070201')
    insert into gongzi values(5,3000,3000,3000,1500,0,'20070201')
    insert into gongzi values(6,2000,2000,2000,1000,0,'20070201')
    insert into gongzi values(7,1200,1200,500,550,0,'20070201')
    insert into gongzi values(8,3000,3000,3000,1500,0,'20070201')


 -- 统计汇总

   -- 统计汇总每个月、每个部门的工资总额（财务部要各类报表）

   -- 统计汇总某类型的工资（比如销售部要统计绩效工资，来查看经营情况）

   -- 统计汇总各年、各月的工资



-- 练习：

-- 1. 创建一个数据库test


-- 2. 创建3个表：
   -- 部门表 deptment(dept_id int 主键,dept varchar(20))
   -- 员工表 emp(emp_id int 主键,emp_name varchar(10),e_deptid int)
   -- 工资表 gongzi(empid_id int not null,g_jiben decimal(10,2) 基本工资,
             -- g_gangwei decimal(10,2) 岗位工资,g_jixiao decimal(10,2) 绩效工资,g_koufa char(1)，
             -- g_shifa decimal(10,2) 实发工资,g_beizhu char(100)，g_date datetime not null)


-- 3. 修改工资表:删除g_beizhu字段;修改g_koufa字段为decimal(10,2)；加一个部门号字段g_deptid int


-- 4. 为gongzi表设置名为pk_g的主键，主键包含emp_id、g_date字段


-- 5. 为gongzi表设置名为con_deptid的外键约束，参照deptment表的dept_id字段；
    --为gongzi表设置名为con_empid的外键约束，参照emp表的emp_id字段


-- 6. 为gongzi表g_deptid字段设置名为df_deptid的默认约束，默认值为1


-- 7. 为gongzi表g_date字段设置名为df_date的默认约束，默认值为系统当前日期


-- 8. 为gongzi表g_jiben字段设置名为chk_jiben的检查约束，使基本工资不能小于1000


-- 9. 用系统存储过程查看表的信息、表的约束


-- 10. 插入记录,并查看

    insert into deptment values(1,'销售部')
    insert into deptment values(2,'采购部')
    insert into deptment values(3,'财务部')

    insert into emp values(1,'王晓明',1)
    insert into emp values(2,'张小虎',1)
    insert into emp values(3,'黄佳佳',1)
    insert into emp values(4,'李莉莉',2)
    insert into emp values(5,'赵蕊',2)
    insert into emp values(6,'黄菲',2)
    insert into emp values(7,'李宇',3)
    insert into emp values(8,'赵新',3)

    insert into gongzi values(1,1100,1000,1000,500,0,'20070101',1)
    insert into gongzi values(2,1500,1500,1500,700,0,'20070101',1)
    insert into gongzi values(3,2000,2000,2000,1000,0,'20070101',1)
    insert into gongzi values(4,1200,1200,500,550,0,'20070101',2)
    insert into gongzi values(5,3000,3000,3000,1500,0,'20070101',2)
    insert into gongzi values(6,2000,2000,2000,1000,0,'20070101',2)
    insert into gongzi values(7,1200,1200,500,550,0,'20070101',3)
    insert into gongzi values(8,3000,3000,3000,1500,0,'20070101',3)
    insert into gongzi values(1,1100,1000,1000,500,0,'20070201',1)
    insert into gongzi values(2,1500,1500,1500,700,0,'20070201',1)
    insert into gongzi values(3,2000,2000,2000,1000,0,'20070201',1)
    insert into gongzi values(4,1200,1200,500,550,0,'20070201',2)
    insert into gongzi values(5,3000,3000,3000,1500,0,'20070201',2)
    insert into gongzi values(6,2000,2000,2000,1000,0,'20070201',2)
    insert into gongzi values(7,1200,1200,500,550,0,'20070201',3)
    insert into gongzi values(8,3000,3000,3000,1500,0,'20070201',3)
    insert into gongzi values(1,1100,1000,1000,500,0,'20060101',1)
    insert into gongzi values(2,1500,1500,1500,700,0,'20060101',1)
    insert into gongzi values(3,2000,2000,2000,1000,0,'20060101',1)
    insert into gongzi values(4,1200,1200,500,550,0,'20060101',2)
    insert into gongzi values(5,3000,3000,3000,1500,0,'20060101',2)
    insert into gongzi values(6,2000,2000,2000,1000,0,'20060101',2)
    insert into gongzi values(7,1200,1200,500,550,0,'20060101',3)
    insert into gongzi values(8,3000,3000,3000,1500,0,'20060101',3)
    insert into gongzi values(1,1100,1000,1000,500,0,'20060201',1)
    insert into gongzi values(2,1500,1500,1500,700,0,'20060201',1)
    insert into gongzi values(3,2000,2000,2000,1000,0,'20060201',1)
    insert into gongzi values(4,1200,1200,500,550,0,'20060201',2)
    insert into gongzi values(5,3000,3000,3000,1500,0,'20060201',2)
    insert into gongzi values(6,2000,2000,2000,1000,0,'20060201',2)
    insert into gongzi values(7,1200,1200,500,550,0,'20060201',3)
    insert into gongzi values(8,3000,3000,3000,1500,0,'20060201',3)


select * from deptment
select * from emp
select * from gongzi

-- 11. 计算员工的实发工资，等于（基本工资+绩效工资+岗位工资-扣发工资）


-- 12. 查询2007年2月所有职工工资 按gongzi_jiben 降序排序


-- 13. 用COMPUTE计算2007年2月所有职工实发工资总额
 

-- 14. 查询 工号为2记录
 

-- 15. 查询 工号为2的员工的平均实发工资


-- 16. 查询部门为1的所有员工的平均实发工资


-- 17. 查询部门为1的所有员工的平均实发工资大于3000的记录


-- 18. 查询 gongzi_jiben小于2000 而且 gongzi_jixiao 小于1500 的记录


-- 19. 工号为5的员工2007年2月工资的扣款增加100元，重新算实发工资,并查看


-- 20. 删除工号为2的员工的工资记录


-- 21. 查询每个部门的实发工资总额、平均实发工资额，按总额升序排序


-- 22. 查询每个部门的实发工资额、平均实发工资额，部门最高工资，部门最低工资,按部门排序


-- 23. 查询2007年2月实发工资在前三名的职工工资记录


-- 24. 查询实发工资总额在前三名的职工工资记录