create database mscmbw;\\ use mscmbw; ---- #### create table Trinity ####\\ create table Trinity(\\ contig_name varchar(15) primary key not null,\\ lenght int(10),\\ path varchar(10000),\\ sequence varchar(50000)\\ ); ---- #### show all tables in your database\\ show tables; #### Check description of your table to see if it is what you wanted\\ desc Trinity; ---- #### remove Trinity table ####\\ drop table Trinity; #### remove contents from Trinity table ####\\ Truncate table Trinity; #### remove contents from table with condition ####\\ delete from Trinity where length > 0; ---- #### import data. DO NOT IMPORT TWICE…. ####\\ load data local infile "/home/you/Trinity.txt" into table Trinity fields terminated by '\t'; ---- #### view contents of table ####\\ select * from Trinity; ### show the first 5 entries in Trinity table ###\\ select * from Trinity limit 5; #### Display contents of column "length" in Trinity table ####\\ select length from Trinity; ### select only contigs that have the lenght less than 500nt ###\\ select * from Trinity where length < 500; #### count the entries in Trinity table ####\\ select count(*) from Trinity; ---- #### create CDS table with foreign keys (link to Trinity table) ####\\ #### Alternatively, link the tables manually in alter table, foreign key tab ####\\ create table Transdecoder_CDS(\\ contig_ID_CDS varchar(50) primary key not null,\\ ORF_type varchar(100),\\ length int,\\ strand varchar(5),\\ original_position varchar(30),\\ sequence varchar(50000),\\ contig varchar(15) not null,\\ foreign key (contig) references Trinity(contig_name)\\ ); ---- #### Extract information from two connected tables ####\\ #### In this example, we want to join a table called tableABC, which has columnA, columnB, and columnC; to another called tableXYZ. ColumnA and columnX should share "identifiers" ####\\ \\ select tableABC.columnA, tableABC.columnC, tableXYZ.columnZ\\ from tableABC, tableXYZ\\ where tableABC.columnA = tableXYZ.columnX\\ and tableABC.columnB > 10; ---- ####**Remove foreign keys from table**####\\ #### Use when trying to remove foreign keys manually causes mySQL workbench to crash ####\\ #### Apply to the child table , first identify which column is causing foreign key constrains with SHOW CREATE TABLE, then DROP FOREIGN KEY of said column as it appears #### SHOW CREATE TABLE mytableXYZ;\\ ALTER TABLE mytableXYZ DROP FOREIGN KEY columnX;