닥터노아바이오텍 하계 인턴쉽 - 2

ALTER TABLE `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729` DROP NOTEPID;
ALTER TABLE 2020_NoteP_WC.ALL_NOTE_P_WC_20200729 DROP NOTEPID;

# NULL to 0
UPDATE `ALL_NOTE_P_IS`
SET memoGPL_islee = 0, WGCNA_success = 0, memo_DEGs = 0, memo_Median = 0, memo_WGCNA = 0, memo_input = 0, memo_Ver2 = 0, memo_Ver3 = 0, memo_Ver4 = 0, memo_Ver5 = 0, memo_Ver22 = 0, memo_Ver44 = 0;

# NULL to 0
UPDATE `ALL_NOTE_P_SH`
SET memoGPL_islee = 0, WGCNA_success = 0, memo_DEGs = 0, memo_Median = 0,memo_WGCNA = 0,memo_input = 0,memo_Ver2 = 0, memo_Ver3 = 0, memo_Ver4 = 0, memo_Ver5 = 0, memo_Ver22 = 0, memo_Ver44 = 0;

# memo_islee -> delete
ALTER TABLE 2020_NoteP_WC.ALL_NOTE_P_WC_20200729 DROP memo_islee;
ALTER TABLE `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729` DROP memo_islee;

# memoGPL_islee -> add
ALTER TABLE `2020_NoteP_WC`.`ALL_NOTE_P_WC_20200729` ADD (`memoGPL_islee` TEXT DEFAULT NULL);
UPDATE `2020_NoteP_WC`.`ALL_NOTE_P_WC_20200729` SET memoGPL_islee = 0;
ALTER TABLE `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729` ADD (`memoGPL_islee` TEXT DEFAULT NULL);
UPDATE `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729` SET memoGPL_islee = 0;

# UNION WC and DH
CREATE TABLE `2020_NoteP`.ALL_NOTE_P_2020`ALL_NOTE_P_2020`
SELECT `Accession`,`Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,`Organism`,`Cell_Type`,`Platform`,`Color`,`GeneSymbol`,`EntrezGeneID`,`O_X`,`Groups`,`Total_Number_of_Groups`,`Samples_Control`,`Count_Sample_Control`,`Samples_Test`,`Count_Sample_Test`,`PMID`,`Comments`,`WGCNA_success`,`memo_DEGs`,`memo_Median`,`memo_input`,`memo_WGCNA`,`memo_Ver2`,`memo_Ver3`,`memo_Ver4`,`memo_Ver5`,`memo_Ver22`,`memo_Ver44`,`memoGPL_islee` FROM `2020_NoteP_WC`.`ALL_NOTE_P_WC_20200729`
UNION SELECT `Accession`,`Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,`Organism`,`Cell_Type`,`Platform`,`Color`,`GeneSymbol`,`EntrezGeneID`,`O_X`,`Groups`,`Total_Number_of_Groups`,`Samples_Control`,`Count_Sample_Control`,`Samples_Test`,`Count_Sample_Test`,`PMID`,`Comments`,`WGCNA_success`,`memo_DEGs`,`memo_Median`,`memo_input`,`memo_WGCNA`,`memo_Ver2`,`memo_Ver3`,`memo_Ver4`,`memo_Ver5`,`memo_Ver22`,`memo_Ver44`,`memoGPL_islee` FROM `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729`
UNION SELECT `Accession`,`Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,`Organism`,`Cell_Type`,`Platform`,`Color`,`GeneSymbol`,`EntrezGeneID`,`O_X`,`Groups`,`Total_Number_of_Groups`,`Samples_Control`,`Count_Sample_Control`,`Samples_Test`,`Count_Sample_Test`,`PMID`,`Comments`,`WGCNA_success`,`memo_DEGs`,`memo_Median`,`memo_input`,`memo_WGCNA`,`memo_Ver2`,`memo_Ver3`,`memo_Ver4`,`memo_Ver5`,`memo_Ver22`,`memo_Ver44`,`memoGPL_islee` FROM `2020_NoteP_IS`.`ALL_NOTE_P_SH`
UNION SELECT `Accession`,`Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,`Organism`,`Cell_Type`,`Platform`,`Color`,`GeneSymbol`,`EntrezGeneID`,`O_X`,`Groups`,`Total_Number_of_Groups`,`Samples_Control`,`Count_Sample_Control`,`Samples_Test`,`Count_Sample_Test`,`PMID`,`Comments`,`WGCNA_success`,`memo_DEGs`,`memo_Median`,`memo_input`,`memo_WGCNA`,`memo_Ver2`,`memo_Ver3`,`memo_Ver4`,`memo_Ver5`,`memo_Ver22`,`memo_Ver44`,`memoGPL_islee` FROM `2020_NoteP_IS`.`ALL_NOTE_P_IS`;

 

---

두개의 테이블에서 NOTEPID 컬럼 제거

ALTER TABLE `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729` DROP NOTEPID;
ALTER TABLE 2020_NoteP_WC.ALL_NOTE_P_WC_20200729 DROP NOTEPID;

 

---

NULL 값 0으로 만들어주기

# NULL to 0
UPDATE `ALL_NOTE_P_IS`
SET memoGPL_islee = 0, WGCNA_success = 0, memo_DEGs = 0, memo_Median = 0, memo_WGCNA = 0, memo_input = 0, memo_Ver2 = 0, memo_Ver3 = 0, memo_Ver4 = 0, memo_Ver5 = 0, memo_Ver22 = 0, memo_Ver44 = 0;

# NULL to 0
UPDATE `ALL_NOTE_P_SH`
SET memoGPL_islee = 0, WGCNA_success = 0, memo_DEGs = 0, memo_Median = 0,memo_WGCNA = 0,memo_input = 0,memo_Ver2 = 0, memo_Ver3 = 0, memo_Ver4 = 0, memo_Ver5 = 0, memo_Ver22 = 0, memo_Ver44 = 0;

 

---

두개의 테이블에서 memo_islee 컬럼 제거

# memo_islee -> delete
ALTER TABLE 2020_NoteP_WC.ALL_NOTE_P_WC_20200729 DROP memo_islee;
ALTER TABLE `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729` DROP memo_islee;

 

---

meoGPLE_islee 컬럼 추가 (데이터 타입 = TEXT)

그 후 NULL 값 = 0 으로 만들어주기

# memoGPL_islee -> add
ALTER TABLE `2020_NoteP_WC`.`ALL_NOTE_P_WC_20200729` ADD (`memoGPL_islee` TEXT DEFAULT NULL);
UPDATE `2020_NoteP_WC`.`ALL_NOTE_P_WC_20200729` SET memoGPL_islee = 0;
ALTER TABLE `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729` ADD (`memoGPL_islee` TEXT DEFAULT NULL);
UPDATE `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729` SET memoGPL_islee = 0;

 

---

새로운 테이블을 만든 후

그 테이블에 WC, DH, IS, SH 테이블 합치기

# UNION WC and DH
CREATE TABLE `2020_NoteP`.ALL_NOTE_P_2020`ALL_NOTE_P_2020`
SELECT `Accession`,`Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,`Organism`,`Cell_Type`,`Platform`,`Color`,`GeneSymbol`,`EntrezGeneID`,`O_X`,`Groups`,`Total_Number_of_Groups`,`Samples_Control`,`Count_Sample_Control`,`Samples_Test`,`Count_Sample_Test`,`PMID`,`Comments`,`WGCNA_success`,`memo_DEGs`,`memo_Median`,`memo_input`,`memo_WGCNA`,`memo_Ver2`,`memo_Ver3`,`memo_Ver4`,`memo_Ver5`,`memo_Ver22`,`memo_Ver44`,`memoGPL_islee` FROM `2020_NoteP_WC`.`ALL_NOTE_P_WC_20200729`
UNION SELECT `Accession`,`Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,`Organism`,`Cell_Type`,`Platform`,`Color`,`GeneSymbol`,`EntrezGeneID`,`O_X`,`Groups`,`Total_Number_of_Groups`,`Samples_Control`,`Count_Sample_Control`,`Samples_Test`,`Count_Sample_Test`,`PMID`,`Comments`,`WGCNA_success`,`memo_DEGs`,`memo_Median`,`memo_input`,`memo_WGCNA`,`memo_Ver2`,`memo_Ver3`,`memo_Ver4`,`memo_Ver5`,`memo_Ver22`,`memo_Ver44`,`memoGPL_islee` FROM `2020_NoteP_DH`.`ALL_NOTE_P_DH_20200729`
UNION SELECT `Accession`,`Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,`Organism`,`Cell_Type`,`Platform`,`Color`,`GeneSymbol`,`EntrezGeneID`,`O_X`,`Groups`,`Total_Number_of_Groups`,`Samples_Control`,`Count_Sample_Control`,`Samples_Test`,`Count_Sample_Test`,`PMID`,`Comments`,`WGCNA_success`,`memo_DEGs`,`memo_Median`,`memo_input`,`memo_WGCNA`,`memo_Ver2`,`memo_Ver3`,`memo_Ver4`,`memo_Ver5`,`memo_Ver22`,`memo_Ver44`,`memoGPL_islee` FROM `2020_NoteP_IS`.`ALL_NOTE_P_SH`
UNION SELECT `Accession`,`Disease`,`MeSH_Disease_Term`,`MeSH_Descriptor`,`Organism`,`Cell_Type`,`Platform`,`Color`,`GeneSymbol`,`EntrezGeneID`,`O_X`,`Groups`,`Total_Number_of_Groups`,`Samples_Control`,`Count_Sample_Control`,`Samples_Test`,`Count_Sample_Test`,`PMID`,`Comments`,`WGCNA_success`,`memo_DEGs`,`memo_Median`,`memo_input`,`memo_WGCNA`,`memo_Ver2`,`memo_Ver3`,`memo_Ver4`,`memo_Ver5`,`memo_Ver22`,`memo_Ver44`,`memoGPL_islee` FROM `2020_NoteP_IS`.`ALL_NOTE_P_IS`;

 

※ 테이블을 만든 후 따로 저장하는 명령어 없이 바로 저장이 된다.

CREATE TABLE 명령어의 아래에서 수행했던 것을 토대로 테이블을 만든다.

 

※ 원래 COLUMN의 순서가 달랐지만 UNION을 할 때, 맨 위의 기준이 되는 COLUMN의 순서대로 TABLE이 만들어진다.

---

MHS number 붙이기

# memeo_DEGs to 0
UPDATE `2020_NoteP`.`ALL_NOTE_P_2020` SET `memo_DEGs` = 0;

ALTER TABLE `2020_NoteP`.`ALL_NOTE_P_2020`
ADD COLUMN `no` INT(11) NULL AUTO_INCREMENT FIRST, ADD KEY(`no`); 

SELECT IF(`Organism` LIKE 'Homo sapiens', CONCAT('MHS',`no`),CONCAT('MMM',`no`)),
`Organism` 
FROM ALL_NOTE_P_2020

ALTER TABLE `2020_NoteP`.`ALL_NOTE_P_2020`
ADD COLUMN `NOTEPID` VARCHAR(20) NOT NULL FIRST, ADD KEY (`NOTEPID`); 

UPDATE ALL_NOTE_P_2020
SET `NOTEPID`= IF(`Organism` LIKE 'Homo sapiens', CONCAT('MHS',`no`),CONCAT('MMM',`no`));

ALTER TABLE `2020_NoteP`.`ALL_NOTE_P_2020` DROP COLUMN `no`, DROP INDEX `no`;

 

---

column 순서 변경하기

# change order
ALTER TABLE `Breast_Neoplasms`.`MHS10_GSE124646_DEGs_Extract` MODIFY `log2FC>1` DOUBLE AFTER `log2FC`;

ALTER TABLE `테이블명` MODIFY `순서 바꿀 컬럼명` 데이터타입 AFTER '앞에 오는 컬럼명'

 

---

컬럼 하나의 데이터 중

절대값이 1보다 큰 것을 추출한 후

절대값을 취한 것으로 다른 컬럼 하나에 추가한다.

# convert - to absolute
UPDATE `Breast_Neoplasms`.`MHS10_GSE124646_DEGs_copy`
SET `log2FC>1` = 
(SELECT ABS(a.`log2FC`) FROM `Breast_Neoplasms`.`MHS10_GSE124646_DEGs_copy` AS a
WHERE ABS(a.`log2FC`) > 1 AND a.`row_names` = `row_names`);

 

에러가 발생하기 쉽고 이렇게 절차적으로 할 필요가 없다.

SELECT `row_names`,`GeneSymbol`,`GeneID`,`log2FC`,ABS(`log2FC`) AS `modify_log2FC`,`AveExpr`,`t`,`P.Value`,`adj.P.Val`,`B` FROM `Breast_Neoplasms`.`MHS10_GSE124646_DEGs`
WHERE ABS(`log2FC`) > 1 AND `P.Value` < 0.05
ORDER BY ABS(`log2FC`) DESC;

그냥 SELECT로 내가 보고싶은 컬럼들을 선택하고

추가적으로 절대값을 취한 컬럼을 또 보고 (본다는 것 만으로 추가가 가능하다. 보고나서 또 추가할 필요가 없다.)

WHERE 문 안에서 절대값이 1보다 큰 것만 보고

ORDERT BY DESC로 내림차순으로 정리한다.