数据库操作

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

---

• 创建数据库：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;