脚本文件

hrm

-- ´´½¨ÈËʹÜÀíÊý¾Ý¿âHRM
drop database if exists HRM;
create database HRM;
USE HRM;

/* ÒªÏÈɾ³ýemp±í£¬²»ÄÜÏÈɾ³ýdept±í£¬ÒòΪdeptÓÐÒ»¸öÍâ¼ü¹ØÁªemp±í*/
drop TABLE if exists emp;
drop TABLE if exists dept;
drop TABLE if exists salgrade;
drop TABLE if exists bonus;
-- ´´½¨¹¤×ʱí
create table bonus
(
ename varchar(10),
job varchar(9),
sal decimal(7,2),
comm decimal(7,2)
);


-- ´´½¨²¿Ãűí
create table dept
(
deptno integer(4) not null,
dname varchar(14),
loc varchar(13)
);
-- Ϊ²¿ÃűíÌí¼ÓÖ÷¼üÔ¼Êø
alter table dept add constraint pk_dept primary key (deptno);

-- ´´½¨Ô±¹¤±í
create table emp
(
empno integer(4) not null,
ename varchar(10),
job varchar(9),
mgr integer(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno integer(2)
);
-- ΪԱ¹¤±íÌí¼ÓÖ÷¼üÔ¼ÊøºÍÍâ¼üÔ¼Êø
alter table emp add constraint pk_emp primary key (empno);
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);


-- ´´½¨¹¤×ʵȼ¶±í 
create table salgrade
(
grade integer(1),
losal decimal(7,2),
hisal decimal(7,2)
);

-- Ïò²¿ÃűíÌí¼ÓÊý¾Ý
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');
-- Ïò¹¤×ʵȼ¶±íÌí¼ÓÊý¾Ý
insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);
-- ÏòÔ±¹¤±íÌí¼ÓÊý¾Ý
-- ÏÂÃæÒªÌرð×¢Ò⣬%d-%m-%Y ÀïµÄ d ºÍ m Ò»¶¨ÒªÐ¡Ð´
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600.00, 300.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250.00, 500.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250.00, 1400.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('19-04-1987', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500.00, 0.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('23-05-1987', '%d-%m-%Y'), 1100.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300.00, null, 10);

studentmanger

drop database if exists studentmananger;
create database studentmanager DEFAULT CHAR SET UTF8;
use studentmanager;
-- 创建系部表
drop table if exists department;
create table department
(
    dep_id char(10) primary key comment '系部编号',
    dep_name varchar(20) not null unique comment '系部名称',
    dep_head varchar(10) comment '系主任'
);
-- 向系部表department中添加数据
insert into department values('01','电气与信息工程系','刘明');
insert into department values('02','机械工程系','于明');
insert into department values('03','建筑工程系','王天');
insert into department values('04','材料工程系','李大可');
-- 创建教师表
drop table if exists teacher;
create table teacher
(
    t_id char(10) primary key,
    t_name varchar(10) not null,
    t_sex ENUM('男','女'),
    t_entrydate TIMESTAMP  DEFAULT CURRENT_TIMESTAMP(),                        -- 入职日期
    t_professor varchar(10) default '助教',
    t_salary decimal(10,2) unsigned,                                -- 基本工资
    dep_id char(10)  references department(dep_id)
) ;
-- 向教师表teacher中添加数据
insert into teacher values('0101','陈平','女','2003-07-15','讲师',5000,'01');
insert into teacher values('0102','陈扬','女','2002-07-15','讲师',5000,'01');
insert into teacher values('0103','杨欣','男','1995-07-15','副教授',6000,'01');
insert into teacher values('0104','蒋固安','男','1990-07-15','教授',8000,'01');
insert into teacher values('0105','张振','女','1995-07-15','讲师',5000,'01');
insert into teacher values('0201','王丽娜','女','1998-07-15','副教授',6000,'02');
insert into teacher values('0202','于林','男','1989-07-15','教授',8000,'02');
-- 创建班级表
drop table if exists class;
create table class
(
    c_id char(10) primary key,
    c_name varchar(20) not null,
    c_mentor varchar(10),            -- 班导师
    dep_id char(10) references department(dep_id)
);
-- 向班级表class中添加数据
insert into class values('20160101','计算机16-1','张振','01');
insert into class values('20160102','计算机16-2','王丽娜','01');
insert into class values('20160103','自动化16-1','于林','01');
insert into class values('20160104','自动化16-2','王伟','01');
insert into class values('20160201','机制16-1','张静','02');
insert into class values('20160202','机制16-2','李超','02');
insert into class values('20160203','机制16-3','赛飞','02');
insert into class values('20160204','机制16-4','户康','02');
insert into class values('20160301','土木16-1','朱明','03');
-- 创建学生表
drop table if exists student;
create table student
(
    s_id char(10) primary key,
    s_name varchar(10) not null unique,
    s_sex ENUM('男','女'),
    s_borndate datetime,-- 出生日期
    s_enrolldate TIMESTAMP default CURRENT_TIMESTAMP(),-- 入学日期
    s_telephone char(11),
    s_address varchar(30),-- 家庭住址
    c_id char(10)  references class(c_id)
);
-- 向学生表student中添加数据
insert into student values('2016010101','白沧明','男','1995-4-16','2016-9-1','13478098447','山东省济南市','20160101');
insert into student values('2016010102','孔亚薇','女','1996-4-3','2016-9-1','13804961254','山东省青岛市','20160101');
insert into student values('2016010201','王丽','女','1989-7-8','2016-9-1','13079452444','山东省烟台市','20160102');
insert into student values('2016010202','田园','女','1985-6-9','2016-9-1','13897256641','山东省潍坊市','20160102');
insert into student values('2016010203','刘晓','男','1993-12-4','2016-9-1','15874526365','山东省济宁市','20160102');
insert into student values('2016020101','张孝文','男','1996-9-7','2016-9-1','18945623120','山东省枣庄市','20160201');
insert into student values('2016020201','孙天方','男','1992-5-6','2016-9-1','13940931222','山东省德州市','20160202');
insert into student values('201020201','李鹏飞','男','1992-10-6','2016-9-1',null,null,'20160202');
-- 创建课程表
drop table if exists course;
create table course
(
    course_id char(10) primary key,
    course_name varchar(20) unique,
    course_credit tinyint unsigned ,-- 学分1-10
    course_type char(10)                -- 课程类型
);
-- 向课程表course中添加数据
insert into course values('0001','计算机应用基础',4,'公共基础课');
insert into course values('0002','大学英语',6,'公共基础课');
insert into course values('0003','数据库原理与应用',4,'专业基础课');
insert into course values('0004','C语言程序设计',4,'专业基础课');
insert into course values('0005','机械设计原理',4,'专业基础课');
insert into course values('0006','web程序设计',6,'专业课');
insert into course values('0007','计算机网络',5,'专业课');
insert into course values('0008','数控编程及应用',3,'专业课');
-- 创建授课表
drop table if exists t_c;
create table t_c
(
    t_id char(10)  references teacher(t_id),
    course_id char(10)  references course(course_id),
    term tinyint unsigned,-- 开课学期
    primary key(t_id,course_id)
);
-- 向授课表t_c中添加数据
insert into t_c values('0101','0001',1);
insert into t_c values('0102','0001',1);
insert into t_c values('0103','0002',1);
insert into t_c values('0104','0002',1);
insert into t_c values('0101','0003',3);
insert into t_c values('0102','0004',2);
insert into t_c values('0105','0007',4);
insert into t_c values('0105','0006',5);
insert into t_c values('0201','0005',2);
insert into t_c values('0202','0008',3);
-- 创建选课表
drop table if exists s_c;
create table s_c
(
    s_id char(10),
    course_id char(10),
    grade decimal(4,1) unsigned ,
    primary key(s_id,course_id),
    foreign key(s_id) references student(s_id),
    foreign key(course_id) references course(course_id)
);
-- 向选课表s_c中添加数据
insert into s_c values('2016010101','0001',90);
insert into s_c values('2016010201','0001',92);
insert into s_c values('2016020101','0001',88);
insert into s_c values('2016020201','0001',56);
insert into s_c values('2016010101','0002',85);
insert into s_c values('2016010201','0002',88);
insert into s_c values('2016010202','0002',59);
insert into s_c values('2016020201','0002',78);
insert into s_c values('2016010101','0003',89);
insert into s_c values('2016010102','0003',89);
insert into s_c values('2016010201','0003',78);
insert into s_c values('2016010202','0004',66);
insert into s_c values('2016020101','0004',99);