其实之前学过数据库相关操作，只不过许久不用就忘了。真是年纪大了呀。更多见：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;
