調剤薬局のためのデータベース講座 #5 医薬品テーブルの作成

元動画はこちら　https://youtu.be/IfY8BZ0rXUY

テーブルyの作成

CREATE TABLE `y` (
  `c1` decimal(1,0) unsigned NOT NULL,
  `c2` char(1) NOT NULL,
  `c3` decimal(9,0) unsigned zerofill NOT NULL,
  `c4` decimal(2,0) unsigned NOT NULL,
  `c5` varchar(96) NOT NULL,
  `c6` decimal(2,0) unsigned NOT NULL,
  `c7` varchar(60) NOT NULL,
  `c8` decimal(3,0) unsigned NOT NULL,
  `c9` decimal(1,0) unsigned NOT NULL,
  `c10` varchar(18) NOT NULL,
  `c11` decimal(1,0) unsigned NOT NULL,
  `c12` float unsigned NOT NULL,
  `c13` decimal(1,0) unsigned NOT NULL,
  `c14` decimal(1,0) unsigned NOT NULL,
  `c15` decimal(1,0) unsigned NOT NULL,
  `c16` decimal(1,0) unsigned NOT NULL,
  `c17` decimal(1,0) unsigned NOT NULL,
  `c18` decimal(1,0) unsigned NOT NULL,
  `c19` decimal(1,0) unsigned NOT NULL,
  `c20` decimal(1,0) unsigned NOT NULL,
  `c21` decimal(5,0) unsigned NOT NULL,
  `c22` decimal(1,0) unsigned NOT NULL,
  `c23` decimal(9,0) unsigned NOT NULL,
  `c24` decimal(1,0) unsigned NOT NULL,
  `c25` float unsigned NOT NULL,
  `c26` decimal(1,0) unsigned NOT NULL,
  `c27` decimal(1,0) unsigned NOT NULL,
  `c28` decimal(1,0) unsigned NOT NULL,
  `c29` CHAR(1) DEFAULT NULL,
  `c30` char(8) NOT NULL,
  `c31` char(8) NOT NULL,
  `c32` char(12) NOT NULL,
  `c33` decimal(9,0) unsigned NOT NULL,
  `c34` char(8) NOT NULL,
  `c35` varchar(300) NOT NULL,
  PRIMARY KEY (`c3`)
) ;

y.csvの読み込み

set character_set_database = sjis;

load data 
  local infile 'C:/Users/{ユーザー名}/Desktop/y.csv'
  replace into table y
  fields terminated by ',' enclosed by '"'
  lines terminated by '\r\n';
     
set character_set_database = utf8;

テーブルmedisの作成

CREATE TABLE `medis` (
  `c1` char(13) NOT NULL,
  `c2` char(7) DEFAULT NULL,
  `c3` decimal(2,0) unsigned DEFAULT NULL,
  `c4` decimal(2,0) unsigned DEFAULT NULL,
  `c5` decimal(2,0) unsigned DEFAULT NULL,
  `c6` char(13) DEFAULT NULL,
  `c7` char(12) DEFAULT NULL,
  `c8` char(12) DEFAULT NULL,
  `c9` decimal(9,0) unsigned DEFAULT NULL,
  `c10` char(9) DEFAULT NULL,
  `c11` varchar(180) DEFAULT NULL,
  `c12` varchar(180) DEFAULT NULL,
  `c13` varchar(135) DEFAULT NULL,
  `c14` varchar(120) DEFAULT NULL,
  `c15` varchar(24) DEFAULT NULL,
  `c16` float unsigned DEFAULT NULL,
  `c17` varchar(24) DEFAULT NULL,
  `c18` float unsigned DEFAULT NULL,
  `c19` varchar(24) DEFAULT NULL,
  `c20` varchar(3) DEFAULT NULL,
  `c21` varchar(30) DEFAULT NULL,
  `c22` varchar(30) DEFAULT NULL,
  `c23` decimal(1,0) unsigned DEFAULT NULL,
  `c24` char(8) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ;

MEDISXXXXXXXX.TXTの読み込み

set character_set_database = sjis;

load data 
  local infile 'C:/Users/{ユーザー名}/Desktop/MEDISXXXXXXXX.TXT'
  replace into table medis
  fields terminated by ',' enclosed by '"'
  lines terminated by '\r\n'
  ignore 1 lines;
     
set character_set_database = utf8;