数据库操作

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


  • 创建数据库:create database books;

  • 使用数据库:use dbname;

  • 新建数据库表

    1
    2
    3
    4
    5
    6
    7
    8
    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;

  • 在数据库中插入数据

    1
    2
    INSERT INTO `jobcategory` VALUES(11,"丸剂");
    INSERT INTO wan (JobCategoryID,JobCategoryName) SELECT ID,MingCheng FROM `zysjyj` WHERE MingCheng LIKE "%丸";
  • 查询数据
    1
    SELECT ID,MingCheng FROM `zysjyj` WHERE MingCheng LIKE  "%丸";
  • 更新数据:
    1
    UPDATE `zysjyj` SET ID=(1100000+ID) WHERE MingCheng LIKE '%丸';
  • 删除数据:

    1
    DELETE FROM `nzysys`.`jobintroduction`;
  • 表的删除:

    1
    DROP TABLE table;
  • 数据删除:

    1
    DROP DATABASE database;`
  • 通过SELECTINSERT
    先创建新表,然后插入
1
2
3
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 "%丸";
  • 跨数据库插入

    1
    INSERT `nzysys`.`subjobcategory` SELECT * FROM `chinese-drug`.`wan`;
  • selectcreate,并指定默认值

    1
    INSERT INTO `nzysys`.`subjobcategory`(JobCategoryID,parentID,JobCategoryName)SELECT ID,10,MingCheng FROM `chinese-drug`.`zysjyj` WHERE MingCheng LIKE "%散";
  • 叠加使用

    1
    2
    3
    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归零
    1
    TRUNCATE TABLE `nzysys`.`jobintroduction`; 
  • 交叉

    1
    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`;
  • 字符串分割

    1
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(o.NeiRong, '【', 2),'】',-1) FROM `nzysys`.`drug_history` AS o;

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

  • 分割后插入
    1
    2
    3
    4
    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;
作者

mmmwhy

发布于

2017-05-16

更新于

2021-05-30

许可协议

评论