general:computerenvironment:basic_sql [Teaching]

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;

Tools

menus and quick search

quick search

site status

Page Tools

meta data for this page