数据库操作
in Linux with 0 comment

数据库操作

in Linux with 0 comment
其实之前学过数据库相关操作,只不过许久不用就忘了。真是年纪大了呀。

create table tablename(columns);
create table demo(
     userid int unsigned not null auto_increment primary key,
     username char(50) not null,
     password char(20) not null,
     age int not null,
     city char(20) not null     
);
INSERT INTO `jobcategory` VALUES(11,"丸剂");
INSERT INTO wan (JobCategoryID,JobCategoryName) SELECT ID,MingCheng FROM `zysjyj` WHERE MingCheng LIKE  "%丸";
SELECT ID,MingCheng FROM `zysjyj` WHERE MingCheng LIKE  "%丸";
UPDATE `zysjyj` SET ID=(1100000+ID) WHERE MingCheng LIKE '%丸';
DELETE FROM `nzysys`.`jobintroduction`;
DROP TABLE table;
DROP DATABASE database;`

先创建新表,然后插入

CREATE TABLE wan(JobCategoryID INT(20) NOT NULL AUTO_INCREMENT,PRIMARY KEY (JobCategoryID),parentID INT(20) NOT NULL DEFAULT '10' ,JobCategoryName VARCHAR(128)  NOT NULL);  

INSERT INTO wan (JobCategoryID,JobCategoryName) SELECT ID,MingCheng FROM `zysjyj` WHERE MingCheng LIKE  "%丸";
INSERT `nzysys`.`subjobcategory` SELECT * FROM `chinese-drug`.`wan`;
INSERT INTO `nzysys`.`subjobcategory`(JobCategoryID,parentID,JobCategoryName)SELECT ID,10,MingCheng FROM `chinese-drug`.`zysjyj` WHERE MingCheng LIKE "%散";
SELECT ID,MingCheng FROM `chinese-drug`.`zysjyj` WHERE MingCheng LIKE "%散";
UPDATE `chinese-drug`.`zysjyj` SET ID=(1000000+ID) WHERE MingCheng LIKE "%散";
INSERT INTO `nzysys`.`subjobcategory`(JobCategoryID,parentID,JobCategoryName)SELECT ID,10,MingCheng FROM `chinese-drug`.`zysjyj` WHERE MingCheng LIKE "%散";
TRUNCATE TABLE `nzysys`.`jobintroduction`; 
INSERT INTO `nzysys`.`jobintroduction` (jobCategoryID,introductionValue,introductionkey,introductionID) SELECT b.jobCategoryID,a.ChuFang,'组成',1 FROM `nzysys`.`subjobcategory` AS b,`chinese-drug`.`zysjyj` AS a WHERE a.`ID`=b.`JobCategoryID`;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(o.NeiRong, '【', 2),'】',-1) FROM `nzysys`.`drug_history` AS o;

之前都是一个字符串一个字符串分割,这个操作可以把整个mysql某个列全部分割,洋气

INSERT INTO `nzysys`.`medical_history`(subMajorMLID,subMajorMLName,subMajorTitle,subMajorDetail) 
    SELECT ID,BiaoTi,
    SUBSTRING_INDEX(SUBSTRING_INDEX(o.NeiRong, '【', 2),'】',-1),
    SUBSTRING_INDEX(SUBSTRING_INDEX(o.NeiRong, '【', 5),'【病因】',-1)FROM `nzysys`.`drug_history` AS o;
Responses

From now on, bravely dream and run toward that dream.
陕ICP备17001447号