数据库操作

其实之前学过数据库相关操作，只不过许久不用就忘了。真是年纪大了呀。更多见：iii.run

---

• 创建数据库：create database books;

• 使用数据库：use dbname;

• 新建数据库表

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     
);

• 显示数据库表：show tables;

• 在数据库中插入数据

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;`

• 通过SELECT来INSERT：
  先创建新表，然后插入

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`;

• 用select来create，并指定默认值

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 "%散";

• 自增ID归零

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;