STPMS项目记录——涉及的mySQL8知识点

原创 于 2018-08-28 17:12:59 发布 · 297 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#Web

本文详细记录了mySQL8的安装过程，包括环境变量配置、初始化、服务管理等。接着介绍了如何创建用户、授权、查看权限，以及数据库的建立和SQL文件的载入。同时，分享了在STPMS项目中应用mySQL8的经验。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

mySQL8知识点

mySQL8的安装

  1. 首先，将RAR文件解压到预设的目录中，在目录中创建my.ini文件，内容如下
[mysqld]
basedir = D:\ProgramFiles\mysql-8.0.11-winx64
datadir = D:\ProgramFiles\mysql-8.0.11-winx64\data
port = 3306
lower_case_table_names = 2
default_authentication_plugin=mysql_native_password
character-set-server = utf8mb4
[mysql]
default-character-set = utf8mb4
[client]
default-character-set = utf8mb4

  1. 右键点击我的电脑，配置环境变量，添加PATH设置，将解压文件夹下的bin路径添加到变量值中。

  2. 以管理员的身份运行cmd,运行命令

mysqld --initialize --console

在执行的过程信息中会显示ROOT的临时密码，需要记录一下。没记住，删掉初始化的 data目录，再执行一遍初始化命令，又会重新生成的。

  1. 安装服务，需要执行
mysqld --install
  1. 启动服务，执行
net start mysql

如果需要停止服务或卸载服务，可以通过命令

net stop mysql

停止服务。通过命令

sc delete MySQL/mysqld -remove

卸载 MySQL 服务。

  1. 更改密码

在MySQL安装目录的 bin 目录下执行命令：

mysql -u root -p

这时候会提示输入密码，记住了上面安装时的密码，填入即可登录成功，进入MySQL命令模式。
然后修改密码，运行

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

在SQL的shell里，可以用命令行检查一下SQL的运行情况

show databases;
use mysql;
show tables;

会显示databases->mysql中数据表的清单。

mysql使用的知识点

建立用户

在SHELL模式下，输入

create user 'norwin'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

则会建立本地帐户norwin,密码是123456。
mySQL中建立的用户都在mySQL库的user表中保存，可以通过下面的命令查看用户是否建立成功

use mysql;
select user,host from user\G;

服务断开

quit;

授权所有权限

GRANT ALL PRIVILEGES ON *.* TO 'norwin'@'%'；

授权基本的查询修改权限，按需求设置

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON *.* TO 'norwin'@'%';

查看用户权限

show grants for 'norwin'@'%';

uuid的生成和使用

  1. mysql中直接使用uuid()函数，可以生成一个随机的uuid。
  2. 正常的uuid是36位长度的。

数据库的建立

如果存在数据库，则删除该数据库并建立新的

DROP DATABASE IF EXISTS STPMS;
CREATE DATABASE STPMS;
USE STPMS;

载入SQL文件

source xxx.sql

STPMS工程的源代码

STPMS工程由杨MM所写，记录在这里，以备后续的查阅需要，在此对杨MM表示感谢。

#数据库
DROP DATABASE IF EXISTS STPMS;
CREATE DATABASE STPMS;
USE STPMS;

#单位表
DROP TABLE IF EXISTS department;
CREATE TABLE department
(
    id VARCHAR(36),
    pID VARCHAR(36),
    name VARCHAR(64) NOT NULL,
    address VARCHAR(255),
    remark VARCHAR(255),
    PRIMARY KEY(id),
    FOREIGN KEY(pID) REFERENCES department(id),
    UNIQUE INDEX(pID,name)
);
INSERT INTO department(id,pID,name,address) VALUES(uuid(),NULL,'中国电子科技集团公司第二十七研究所','河南省郑州市郑东新区博学路36号');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='中国电子科技集团公司第二十七研究所') a),'基础技术部');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='基础技术部') a),'计算机软件室');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='中国电子科技集团公司第二十七研究所') a),'测控与雷达系统事业部');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='测控与雷达系统事业部') a),'测控技术室');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='测控与雷达系统事业部') a),'遥感技术室');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='测控与雷达系统事业部') a),'功放技术室');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='测控与雷达系统事业部') a),'卫星终端室');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='中国电子科技集团公司第二十七研究所') a),'光电系统事业部');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='光电系统事业部') a),'激光技术室');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='光电系统事业部') a),'侦察告警室');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='光电系统事业部') a),'指挥控制室');
INSERT INTO department(id,pID,name) VALUES(uuid(),(SELECT a.id FROM(SELECT id FROM department WHERE name='光电系统事业部') a),'光电导航室');


#人员表
DROP TABLE IF EXISTS person;
CREATE TABLE person
(
    id VARCHAR(36),
    departmentID VARCHAR(36) NOT NULL,
    name VARCHAR(64) NOT NULL,
    cardNo VARCHAR(4),
    telephone VARCHAR(32),
    remark VARCHAR(255),
    PRIMARY KEY(id),
    FOREIGN KEY(departmentID) REFERENCES department(id),
    UNIQUE INDEX(cardNo)
);

INSERT INTO person(id,departmentID,name,cardNo,telephone) VALUES
            (uuid(),(SELECT id FROM department WHERE name='中国电子科技集团公司第二十七研究所'),'admin','0000','0371-61270921');


#角色表
DROP TABLE IF EXISTS role;
CREATE TABLE role
(
    id VARCHAR(36),
    name VARCHAR(64) NOT NULL,
    level VARCHAR(1) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE INDEX(name)
);
INSERT INTO role(id,name,level) VALUES(uuid(),'系统管理员','0');
INSERT INTO role(id,name,level) VALUES(uuid(),'主任','1');
INSERT INTO role(id,name,level) VALUES(uuid(),'组长','2');
INSERT INTO role(id,name,level) VALUES(uuid(),'测试人员','3');
INSERT INTO role(id,name,level) VALUES(uuid(),'课题负责人','4');
INSERT INTO role(id,name,level) VALUES(uuid(),'委托人','4');
INSERT INTO role(id,name,level) VALUES(uuid(),'软件负责人','4');
INSERT INTO role(id,name,level) VALUES(uuid(),'项目QA','4');
INSERT INTO role(id,name,level) VALUES(uuid(),'测试负责人','3');
INSERT INTO role(id,name,level) VALUES(uuid(),'设计师','4');




#人员_角色关系表
DROP TABLE IF EXISTS rPersonRole;
CREATE TABLE rPersonRole
(
    id VARCHAR(36),
    personID VARCHAR(36) NOT NULL,
    roleID VARCHAR(36) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(personID) REFERENCES person(id),
    FOREIGN KEY(roleID) REFERENCES role(id),
    UNIQUE INDEX(personID,roleID)
);
INSERT INTO rPersonRole(id,personID,roleID) VALUES(uuid(),(SELECT id FROM person WHERE name='admin'),(SELECT id FROM role WHERE name='系统管理员'));

#用户表
DROP TABLE IF EXISTS user;
CREATE TABLE user
(
    id VARCHAR(36),
    personID VARCHAR(36) NOT NULL,
    password VARCHAR(32) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(personID) REFERENCES person(id),
    UNIQUE INDEX(personID)
);
INSERT INTO user(id,personID,password) VALUES(uuid(),(SELECT id FROM person WHERE name='admin'),'0000');

#关键进展类型
DROP TABLE IF EXISTS keyProgressType;
CREATE TABLE keyProgressType
(
    id VARCHAR(36),
    keyProgressType VARCHAR(128) NOT NULL,
    importLevel VARCHAR(1) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE INDEX(keyProgressType)
);
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'接收委托单','2');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'接收样品','3');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'测试依据出库','3');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'编制测试需求','2');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'编制测试计划','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'修改测试计划','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'测试计划评审','2');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'编制测试说明','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'修改测试说明','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'测试说明评审','2');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'测试产品出库','3');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'测试就绪评审','2');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'执行测试用例','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'编制测试记录','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'修改测试记录','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'编制测试问题报告','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'修改测试问题报告','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'编制测试报告','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'修改测试报告','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'测试工具开发','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'确认测试工具','2');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'测试数据准备','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'测试环境准备','3');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'增加测试协调单','2');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'存档','1');
INSERT INTO keyProgressType(id,keyProgressType,importLevel) VALUES(uuid(),'入受控库','1');


#执行状态
DROP TABLE IF EXISTS executeStatus;
CREATE TABLE executeStatus
(
    id VARCHAR(36),
    executeStatus VARCHAR(32) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE INDEX(executeStatus)
);
INSERT INTO executeStatus(id,executeStatus) VALUES(uuid(),'运行中');
INSERT INTO executeStatus(id,executeStatus) VALUES(uuid(),'暂停');
INSERT INTO executeStatus(id,executeStatus) VALUES(uuid(),'停滞');
INSERT INTO executeStatus(id,executeStatus) VALUES(uuid(),'完成');


#关键进展类型_执行状态关系表
DROP TABLE IF EXISTS rKeyProgressTypeExecuteStatus;
CREATE TABLE rKeyProgressTypeExecuteStatus
(
    id VARCHAR(36),
    executeStatusID VARCHAR(36) NOT NULL,
    keyProgressTypeID VARCHAR(36) NOT NULL,
    isRecNumber VARCHAR(1) NOT NULL,
    isRecWorkload VARCHAR(1) NOT NULL,
    workloadUnit VARCHAR(8),
    isRecWorkingProduct VARCHAR(1) NOT NULL,
    isRecBug VARCHAR(1) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(executeStatusID) REFERENCES executeStatus(id),
    FOREIGN KEY(keyProgressTypeID) REFERENCES keyProgressType(id),
    UNIQUE INDEX(executeStatusID,keyProgressTypeID)
);
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='接收委托单'),'1','0','','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='测试依据出库'),'1','0','','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='编制测试需求'),'0','1','项','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='编制测试需求'),'0','1','项','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='编制测试计划'),'0','1','项','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='编制测试计划'),'0','1','项','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='修改测试计划'),'0','1','项','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='修改测试计划'),'0','1','项','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='编制测试说明'),'0','1','个','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='编制测试说明'),'0','1','个','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='修改测试说明'),'0','1','个','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='修改测试说明'),'0','1','个','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='测试产品出库'),'1','0','','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='执行测试用例'),'0','1','个','0','1');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='执行测试用例'),'0','1','个','0','1');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='编制测试记录'),'0','1','个','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='编制测试记录'),'0','1','个','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='修改测试记录'),'0','1','个','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='修改测试记录'),'0','1','个','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='编制测试问题报告'),'0','1','个','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='编制测试问题报告'),'0','1','个','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='修改测试问题报告'),'0','1','个','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='修改测试问题报告'),'0','1','个','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='编制测试报告'),'0','1','份','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='修改测试报告'),'0','1','份','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='测试工具开发'),'0','1','行','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='测试工具开发'),'0','1','行','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='确认测试工具'),'1','0','','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='测试数据准备'),'0','1','行','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='测试数据准备'),'0','1','行','1','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='运行中'),(SELECT id FROM keyProgressType where keyProgressType='测试环境准备'),'0','1','天','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='测试环境准备'),'0','1','天','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='存档'),'1','0','','0','0');
INSERT INTO rKeyProgressTypeExecuteStatus(id,executeStatusID,keyProgressTypeID,isRecNumber,isRecWorkload,workloadUnit,isRecWorkingProduct,isRecBug)
    VALUES(uuid(),(SELECT id FROM executeStatus where executeStatus='完成'),(SELECT id FROM keyProgressType where keyProgressType='入受控库'),'1','0','','0','0');



#项目表
DROP TABLE IF EXISTS project;
CREATE TABLE project
(
    id VARCHAR(36),
    clientDepartmentID VARCHAR(36) NOT NULL,
    manufacturerDepartmentID VARCHAR(36) NOT NULL,
    name VARCHAR(128),
    workOrderNo VARCHAR(32),
    startTime DATETIME,
    remark VARCHAR(255),
    PRIMARY KEY(id),
    FOREIGN KEY(clientDepartmentID) REFERENCES department(id),
    FOREIGN KEY(manufacturerDepartmentID) REFERENCES department(id),
    UNIQUE INDEX(name),
    UNIQUE INDEX(workOrderNo)
);

#项目_人员_角色关系表
DROP TABLE IF EXISTS rProjectPersonRole;
CREATE TABLE rProjectPersonRole
(
    id VARCHAR(36),
    userID VARCHAR(36) NOT NULL,
    projectID VARCHAR(36) NOT NULL,
    roleID VARCHAR(36) NOT NULL,
    ondutyStartTime DATETIME,
    ondutyEndTime DATETIME,
    PRIMARY KEY(id),
    FOREIGN KEY(userID) REFERENCES user(id),
    FOREIGN KEY(projectID) REFERENCES project(id),
    FOREIGN KEY(roleID) REFERENCES role(id),
    UNIQUE INDEX(userID,projectID,roleID)
);

#项目结构表
DROP TABLE IF EXISTS projectStructure;
CREATE TABLE projectStructure
(
    id VARCHAR(36),
    projectID VARCHAR(36) NOT NULL,
    pID VARCHAR(36) NOT NULL,
    name VARCHAR(128) NOT NULL,
    identifier VARCHAR(64) NOT NULL,
    scale VARCHAR(1),
    softwareLevel VARCHAR(1),
    softwareType VARCHAR(1),
    developmentLanuage VARCHAR(64),
    remark VARCHAR(255),
    PRIMARY KEY(id),
    FOREIGN KEY(projectID) REFERENCES project(id),
    FOREIGN KEY(pID) REFERENCES projectStructure(id),
    UNIQUE INDEX(identifier)
);

#项目结构_人员_角色关系表
DROP TABLE IF EXISTS rProjectStructurePersonRole;
CREATE TABLE rProjectStructurePersonRole
(
    id VARCHAR(36),
    userID VARCHAR(36) NOT NULL,
    projectStructureID VARCHAR(36) NOT NULL,
    roleID VARCHAR(36) NOT NULL,
    ondutyStartTime DATETIME,
    ondutyEndTime DATETIME,
    PRIMARY KEY(id),
    FOREIGN KEY(projectStructureID) REFERENCES projectStructure(id),
    FOREIGN KEY(roleID) REFERENCES role(id),
    UNIQUE INDEX(userID,projectStructureID,roleID)
);

#任务分工基本信息表
DROP TABLE IF EXISTS task;
CREATE TABLE task
(
    id VARCHAR(36),
    projectID VARCHAR(36) NOT NULL,
    testLevel VARCHAR(1),
    testStage VARCHAR(32),
    PRIMARY KEY(id),
    FOREIGN KEY(projectID) REFERENCES project(id),
    UNIQUE INDEX(projectID,testLevel,testStage)
);

#任务分工列表
DROP TABLE IF EXISTS taskAssign;
CREATE TABLE taskAssign
(
    id VARCHAR(36),
    taskID VARCHAR(36) NOT NULL,
    projectStructureID VARCHAR(36) NOT NULL,
    testerID VARCHAR(36) NOT NULL,
    ondutyStartTime DATETIME,
    ondutyEndTime DATETIME,
    testPlannedFinishTime DATETIME,
    serialNumber VARCHAR(12),
    PRIMARY KEY(id),
    FOREIGN KEY(taskID) REFERENCES task(id),
    FOREIGN KEY(projectStructureID) REFERENCES projectStructure(id),
    FOREIGN KEY(testerID) REFERENCES person(id),
    UNIQUE INDEX(taskID,projectStructureID,testerID)
);

#工作内容列表
DROP TABLE IF EXISTS workContent;
CREATE TABLE workContent
(
    id VARCHAR(36),
    taskAssignID VARCHAR(36) NOT NULL,
    rKeyProgressTypeExecuteStatusID VARCHAR(36) NOT NULL,
    remark VARCHAR(128),
    recordTime DATETIME,
    PRIMARY KEY(id),
    FOREIGN KEY(taskAssignID) REFERENCES taskAssign(id),
    FOREIGN KEY(rKeyProgressTypeExecuteStatusID) REFERENCES rKeyProgressTypeExecuteStatus(id)
);

#编号表
DROP TABLE IF EXISTS number;
CREATE TABLE number
(
    id VARCHAR(36),
    workContentID VARCHAR(36) NOT NULL,
    number VARCHAR(128) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(workContentID) REFERENCES workContent(id)   
);

#工作量表
DROP TABLE IF EXISTS workload;
CREATE TABLE workload
(
    id VARCHAR(36),
    workContentID VARCHAR(36) NOT NULL,
    workload DECIMAL(12,2) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(workContentID) REFERENCES workContent(id)   
);

#工作产品表
DROP TABLE IF EXISTS workingProduct;
CREATE TABLE workingProduct
(
    id VARCHAR(36),
    workContentID VARCHAR(36) NOT NULL,
    numberID VARCHAR(36) NOT NULL,
    name VARCHAR(64) NOT NULL,
    identifier VARCHAR(64) NOT NULL,
    version VARCHAR(8) NOT NULL,
    savePath VARCHAR(128),
    PRIMARY KEY(id),
    FOREIGN KEY(workContentID) REFERENCES workContent(id),
    FOREIGN KEY(numberID) REFERENCES number(id),
    UNIQUE INDEX(workContentID,name,identifier,version)
);


#缺陷
DROP TABLE IF EXISTS problem;
CREATE TABLE problem
(
    id VARCHAR(36),
    workContentID VARCHAR(36) NOT NULL,
    bugLevel VARCHAR(1),
    bugLevelNum INT,
    bugType VARCHAR(1),
    bugTypeNum INT,
    reproducibleDegree VARCHAR(1),
    reproducibleDegreeNum INT,
    PRIMARY KEY(id),
    FOREIGN KEY(workContentID) REFERENCES workContent(id)
);

#系统日志表
DROP TABLE IF EXISTS systemLog;
CREATE TABLE systemLog
(
    id VARCHAR(36),
    cardNo VARCHAR(4) NOT NULL,
    operationTime DATETIME,
    operationType VARCHAR(64),
    operationContent VARCHAR(128),
    PRIMARY KEY(id)
);

COMMIT;
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值