yggl建库记录

yggl建库记录

本文用于个人记录,对网友价值微乎其微
需要数据库文件可以直接下载 yggl.sql ,不过这个文件是写作业的时候写的,记录了代码的全过程,运行文件会有一些查询,不过不会影响数据库的生成就是。

建库

  1. 创建编码格式为gb2312的数据库yggl。
create database yggl character set gb2312;
  1. 选择要使用的数据库yggl。
use yggl;

建表

  1. 创建员工信息表employees。
create table employees(
employeeid char(6),
name char(6) not null,
degree char(4) not null,
birthday date not null,
sex char(1) not null,
workyear tinyint(1),
address varchar(20),
phone char(12),
departmentid char(3),
primary key(employeeid));
  1. 创建部门信息表departments。
create table departments(
departmentid char(3),
deptname char(10) not null,
mark text,
primary key(departmentid));
  1. 创建员工薪水情况表。
create table salary(
employeeid char(6),
income decimal(8,2) not null,
expenses decimal(8,2) not null,
primary key(employeeid));

文章重点1:
为什么使用decimal取代书本的float?:
float和double的存储类型都是非精确存储,使用float和double类型存放金额有可能会产生较小的误差,具体原因可以参考我的另一篇博文:float和double存储为什么是非精确存储?

  1. 添加外键约束,保证当departments表中的departmentid要进行更新或删除时,若employees表中的departmentid列还有该值的记录时,拒绝对departments表的操作。
alter table employees
add foreign key(departmentid) references departments(departmentid)
on delete cascade
on update cascade;
  1. 添加外键约束,保证当employees表中的employeeid要进行更新或删除时,若salary表中的employeeid列还有该值的记录时,拒绝对employees表的操作。
alter table salary
add foreign key (employeeid) references employees(employeeid)
on delete cascade
on update cascade;

插入数据

  1. 插入部门信息数据。
INSERT INTO departments VALUES ('1', '财务部', NULL),
('2', '人力资源部', NULL),
('3', '经理办公室', NULL),
('4', '研发部', NULL),
('5', '市场部', NULL);
  1. 插入员工信息数据。
INSERT INTO employees VALUES ('000001', '王林', '大专', '1966-01-23', '1', 8, '中山路32-1-508', '83355668', '2'),
('010008', '伍容华', '本科', '1976-03-28', '1', 3, '北京东路100-2', '83321321', '1'),
('020010', '王向荣', '硕士', '1982-12-09', '1', 2, '四牌楼', '83792361', '1'),
('020018', '李丽', '大专', '1960-07-30', '0', 6, '中山东路102-2', '83413301', '1'),
('102201', '刘明', '本科', '1972-10-18', '1', 3, '虎距路100-2', '83606608', '5'),
('102208', '朱俊', '硕士', '1965-09-28', '1', 2, '牌楼巷5-3-106', '84708817', '5'),
('108991', '钟敏', '硕士', '1979-08-10', '0', 4, '中山路10-3-105', '83346722', '3'),
('111006', '张石兵', '本科', '1974-10-01', '1', 1, '解放路34-1-203', '84563418', '5'),
('210678', '林涛', '大专', '1977-04-02', '1', 2, '中山北路24-35', '83467336', '3'),
('302566', '李玉珉', '本科', '1968-09-20', '1', 3, '热和路209-3', '58765992', '4'),
('308759', '叶凡', '本科', '1978-11-18', '1', 2, '北京西路3-7-52', '83308901', '4'),
('504209', '陈林琳', '大专', '1969-09-03', '0', 5, '汉中路120-4-12', '84468158', '4');
  1. 插入员工薪资情况数据。
INSERT INTO salary VALUES ('000001', 2100.80, 123.09),
('010008', 1582.62, 88.03),
('020010', 2860.00, 198.00),
('020018', 2347.68, 180.00),
('102201', 2569.88, 185.65),
('102208', 1980.00, 100.00),
('108991', 3259.98, 281.52),
('111006', 1987.01, 79.58),
('210678', 2240.00, 121.00),
('302566', 2980.70, 210.20),
('308759', 2531.98, 199.08),
('504209', 2066.15, 108.00);

文章重点2:
为什么插入的顺序是:部门信息>员工信息>员工薪资情况?
这里应该算是老师设计的一个坑,把给我们的表信息顺序设为 员工信息>部门信息>员工薪资情况 ,但是由于我们建表时添加了外键约束“当departments表中的departmentid要进行更新或删除时,若employees表中的departmentid列还有该值的记录时,拒绝对departments表的操作”,所以我们如果先插入员工信息的话,因为部门信息表中还不存在部门编号,所以员工信息表中的部门编号也没有对应值,插入信息失败

视图

  1. 对员工管理数据库yggl创建视图emp_view1,包含所有男员工的员工号码、姓名、工作年限和学历
    create view emp_view1 as
    (select phone,name,workyear,degree from employees where sex=1);

  2. 从emp_view1视图中查询工作年限再两年以上的员工信息
    select * from emp_view1 where workyear>2;

  3. 创建视图emp_view2,包含员工号码、姓名、所在部门名称和收入。

create view emp_view2 as
(select phone,name,deptname,income from employees
join departments on (departments.departmentid=employees.departmentid)
join salary on (salary.employeeid=employees.employeeid));
  1. 从emp_view2视图中查询研发部门的员工号码、姓名和收入。
select phone,name,income from emp_view2 where deptname="研发部";
  1. 创建视图emp_view3,包含所有工作年限2年以上的员工的员工号码、姓名、学历、出生日期、性别、工作年限及所在部门编号。在创建视图是加上with check option字句。
create view emp_view3 as
(select employeeid,name,degree,birthday,sex,workyear,departmentid from employees
where workyear>2) with check option;
  1. 从emp_view3视图中插入一条记录:(041110,钟晓玲,博士,1973-12-01,男,3,4)。
insert into emp_view3 values ('041110','钟晓玲','博士','1973-12-01','1',3,'4');
  1. 修改emp_view2,将”李丽”的收入加200元。
update emp_view2 set income=income+1 where name='李丽';
  1. 删除视图emp_view3中“本科”学历的员工。
mysql> delete from emp_view3 where degree="本科";
Query OK, 3 rows affected (0.31 sec)
  1. 修改视图emp_view1的定义,包含员工号码、姓名和实际收入。
create or replace view emp_view1 as
(select employees.employeeid,name,income from employees
join salary on (salary.employeeid=employees.employeeid));
  1. 删除视图emp_view2和emp_view3。
drop view emp_view2,emp_view3;

  转载请注明: F-同学 yggl建库记录

  目录