I will use MySQL to demonstrate the concepts in this section. However, the material is equally applicable to most other relational databases. MySQL can be freely downloaded and installed from the MySQL web site 40 in several minutes. It comes with a PHP web user interface that you will get access to via an ISP or you can use the command line if you download and install it yourself.
The type of SQL used to create a schema is called data definition language (DDL). DDL may differ slightly from database to database. However, the example I give here should work reasonably uniformly. The SQL that I show to insert, update, and query data should be completely portable.
The first step is to create a database. I will call this database 'genes'. Create it using the web tool or if you have installed MySQL yourself using the command line. Start the MySQL command line interpreter and create the database by typing
The USE command tells the SQL client that to connect to and start using the newly created database.
Continuing the gene information example from previous sections I will create a table called gene_info. It will have three fields: id, symbol, and name. The id field will be an integer (int) and also be the primary key. That will mean that the id will have to be unique and must always be provided. The symbol field will be a varchar of length 10, be not nullable, and be indexed. The index will help us look up genes by the gene symbol. The name field will be a varchar of length 255, be not nullable, and be indexed. The SQL to create this table is:
If you are using the command line then just cut and paste this in. Now let's enter some sample data. We will enter the sample data id = 3064, symbol = 'HD', and name = 'huntingtin (Huntington disease)'. If you are using the command line type
You can check that the data has been inserted by typing the query
This will return the data just entered.
There are no user comments.
Please send ideas and opinions by email at alexamies@gmail.com.