1、
当数据库的某个表的某个属性声明为:aotu inrement时,说明该数据是自动增长的,不用初始化了,外部调用插入或者修改的应该默认该值为NULL。
![一周工作总结 一周工作总结](https://i-blog.csdnimg.cn/blog_migrate/a4c26d1e5885305701be709a3d33442f.gif)
当数据库的某个表的某个属性声明为:aotu inrement时,说明该数据是自动增长的,不用初始化了,外部调用插入或者修改的应该默认该值为NULL。
2、数据库存储过程终于知道怎么写了。分享一下代码:
DELIMITER //
DROP PROCEDURE IF EXISTS deleteModel//
CREATE PROCEDURE deleteModel(IN inMachineID VARCHAR(32),IN inEnergyValue FLOAT)
BEGIN
IF inMachineID = '' THEN
DELETE FROM evivo.physicalmodel WHERE EnergyValue = inEnergyValue;
ELSEIF inEnergyValue = -1 THEN
DELETE FROM evivo.physicalmodel WHERE MachineID = inMachineID;
ELSEIF inMachineID != '' AND inEnergyValue != -1 THEN
DELETE FROM evivo.physicalmodel WHERE MachineID=inMachineID AND EnergyValue = inEnergyValue;
END IF;
END
//
DELIMITER ;
DELIMITER //
DROP PROCEDURE IF EXISTS updateModelPrivate//
CREATE PROCEDURE updateModelPrivate(IN inType INT,IN inMachineID VARCHAR(32),IN inEnergyValue FLOAT,IN inLONGBLOB LONGBLOB)
BEGIN
CASE inType
WHEN 1 THEN UPDATE evivo.physicalmodel SET modelInfo = inLONGBLOB;
WHEN 2 THEN UPDATE evivo.physicalmodel SET SpectrumInfo = inLONGBLOB;
WHEN 3 THEN UPDATE evivo.physicalmodel SET ProfileInfo = inLONGBLOB;
WHEN 4 THEN UPDATE evivo.physicalmodel SET SCPInfo = inLONGBLOB;
WHEN 5 THEN UPDATE evivo.physicalmodel SET SCInfo = inLONGBLOB;
WHEN 6 THEN UPDATE evivo.physicalmodel SET PDDInfo = inLONGBLOB;
WHEN 7 THEN UPDATE evivo.physicalmodel SET K1Info = inLONGBLOB;
WHEN 8 THEN UPDATE evivo.physicalmodel SET Kernel = inLONGBLOB;
ELSE UPDATE evivo.physicalmodel SET EPID = inLONGBLOB;
END CASE;
END
//
DELIMITER ;
DELIMITER //
DROP PROCEDURE IF EXISTS updateModelAllData//
CREATE PROCEDURE updateModelAllData
(IN inMachineID VARCHAR(32),IN inEnergyValue FLOAT,IN inModelInfo LONGBLOB
,IN inSpectrumInfo LONGBLOB,IN inProfileInfo LONGBLOB,IN inSCPInfo LONGBLOB
,IN inSCInfo LONGBLOB,IN inPDDInfo LONGBLOB,IN inK1Info LONGBLOB
,IN inKernel LONGBLOB,IN inEPID LONGBLOB)
BEGIN
UPDATE evivo.physicalmodel
SET EnergyValue=inEnergyValue,ModelInfo=inModelInfo,SpectrumInfo=inSpectrumInfo
,ProfileInfo=inProfileInfo,SCPInfo=inSCPInfo,SCInfo=inSCInfo
,PDDInfo=inPDDInfo,K1Info=inK1Info,Kernel=inKernel
,EPID=inEPID
WHERE MachineID=inMachineID;
END
//
DELIMITER ;
然后用代码把大数据转换为二进制数据,即可在数据库中存储。不过个人觉得一些超大数据库应该用一个专门的容器,在数据库中存储其映射。这样子数据运行时不需要每次都全部载入这么大的数据,提高内存利用效率。
QString strSQL = QString("call insertPhysicalModel('%1',%2,?,?,?,?,?,?,?,?,?);")
//调用存储过程,增加数据库表physicModel的其他属性了_K1,测试通过
.arg(inModel.machineID).arg(inModel.phyEnergyAnalysis[i].fEnergyValue);
query.prepare(strSQL);
QByteArray byEpid;
EvivoStructParser::structToByteArray(inModel.phyEnergyAnalysis[i].modelInfo, byEpid);
query.addBindValue( byEpid);
3、SVN的初步使用,入门了,未来可以解决版本控制的问题;同时还有MySql的初步使用。多用一些代码,只要多点编写,一切都可以的。
4、嵌套查询
select * from evivo.physicalmodel
where MachineID = (select MachineID from evivo.physicalmodel where EnergyValue = 2);
select * from evivo.physicalmodel
where MachineID IN (select MachineID from evivo.physicalmodel where EnergyValue = 2);
要求内层select的数据是对应的项数
5、C++/Q中关于SQL语句的嵌入调用。
Qt中嵌入SQL语句在执行时被调用,只要改语句执行成功就返回true,此处说的执行成功是说在数据库中执行该SQL语句没有报错,并不包括实现SQL中的各种判定条件。故仍然需要根据SQL的返回信息进一步处理
6、数据库:
可以在表级别自定义约束属性,例如:
check (Ssex ='女' or Sname NOT LIKE 'Ms.%')
CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
取消限制:
ALTER TABLE Student
DROP CONSTRAINT C1
ADD C1
7、触发器
MySql的触发器比较不方便
DELIMITER //
DROP TRIGGER IF EXISTS tri_insert_physicalModel //
CREATE TRIGGER tri_insert_physicalModel
BEFORE UPDATE ON evivo.physicalModel
FOR EACH ROW
BEGIN
IF(NEW.EnergyValue <0) THEN
SET NEW.EnergyValue = 0;
END IF;
END
//
DELIMITER ;
DELIMITER //
DROP TRIGGER IF EXISTS tri_update_physicalModel//
CREATE TRIGGER tri_update_physicalModel
BEFORE UPDATE ON evivo.physicalModel
FOR EACH ROW
BEGIN
IF(NEW.EnergyValue<0) THEN
SET NEW.EnergyValue = 0;
END IF;
END
//
DELIMITER ;