Tag Archives: mysql

Jun 07 2012
Leave a comment
laboratory

Last laboratory: queries on a database

mysql logoWelcome back guys!

Please, start reviewing what databases are so that we all have a common background when talking about tables and fields. Then we prepared a very short introduction to the SQL language:

These tasks are quite common, but not so useful to understand how databases works so we’ll just have a look to them.

Today you’ll play with the Linux machine with a good level of independence. Tomorrow we’ll use the CIV computer room where there is the possibility to use a projector for a more “guided” and commented experience. Consider this as tomorrow we will practice more with queries… while only today you’ll have the possibility to play with Perl if you wish to do so 🙂

Continue reading →

Tagged ,
Jun 06 2012
Leave a comment
snippet

MySQL: add a new record

We can add a new record with a statement like this one:

INSERT INTO reagents (name, supplier, amount) VALUES ('Trizma Base', 12, 100);

Note that we list the fields we want to populate and then the values in the same order. See more examples.

Tagged , ,
Jun 06 2012
Leave a comment
laboratory

MySQL: creating a new table

The very nice thing about MySQL is that… it’s very similar to fluent English. So if we want to create a table we must decide in advance the list of fields we need.

For each field we need to specify the type (will we store a number? a date? a name?). A list of fields types is available here.

Remember that MySQL is case-insensitive, while table names and field names are case-sensitive. It’s common to type SQL commands in upper case though, and to choose lowercase names. The “create table” command requires a name and a list of fields.

CREATE TABLE reagents (
  id  INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  supplier INT,
  amount INT,
  arrived DATE);

A single command ends with “;” in MySQL shell (this is not always true for SQL languages), so that we can split a single command across multiple lines. We choosed a list of fields and some of them store a number, another a date, the name is a string (varchar).

Each table has to have a “Primary Key“, a unique identifier for the record. A common practice is to use an integer with the “auto_increment” keyword that means: every time you create a record, the “id” field will be incremented.

If we later want to add a new field there’s a simple command to do this:

UPDATE reagents ADD (casnumber VARCHAR(20));

 

Tagged ,
Jun 06 2012
Leave a comment
laboratory, tutorial

MySQL: first commands into the shell

MySQL has its own shell. We can enter using the following command:

mysql -h servername -u username -p

Where “servername” can be omitted if we installed MySQL in our computer. The prompt switches from “$” to “mysql>” and we can type SQL commands.

We want to:

  • list the databases
  • select a particular database
  • list the tables in that database
  • list the fields of a table
Tagged
Jun 05 2012
Leave a comment
laboratory, perl

Databases: the very basic introduction

Databases… a whole ‘big thing’ in the bioinformatics field. Databases are the “backbone” of all the public data banks of course, but can be very useful for many other aspects. Even our scaffolding program uses a database for fast data retrieval.

Databases stores data, as a very simple “text file”. But they have a powerful engine to quickly retrieve data using selected criteria. Consider our “pick primer” program: it takes a while to take the sequence of both contigs because it has to parse the file containing all of them. If we decided to store all sequences into a database we could perform a simple query to get them.

This blog is powered by a quite complex database. Continue reading →

Tagged ,