MySQL Database Intro

Some useful links

• The MySQL Web Site
• The MySQL Manual (including examples etc)
• SQL Tutorial 1
• SQL Tutorial 2
• SQL FAQ (frequently Asked Questions)

How to use mysql/mysqlshow/mysqldump

This is not a tutorial (see links above, and esp the manual), just some examples to get started and to get the flavor.

Show the databases accessible under user evptest

c:> mysqlshow --host=Vega --user=evptest

Shows the tables in database Kees_Test (adding -i gives more details)

c:> mysqlshow --host=Vega --user=evptest Kees_Test *

Shows details on table Operations (field types, etc)

C:> mysqlshow --host=Vega --user=evptest Kees_Test Operations *

Start the mysql command-line/prompt program using database Kees_Test; list content of the table called Operations; add new entry (note: TimeStamp is automatically updated, format yyyymmddhhmmss).

c:> mysql --host=Vega --user=evptest Kees_Test

mysql> select * from Operations;

+---------------+-----------+--------------+----------------+
| OperationName | Encoding  | Syntax       | TimeStamp      |
+---------------+-----------+--------------+----------------+
| add           | 10101010  | add r1,r2,r3 | 20040611125343 |
| sub           | 10101011  | sub r1,r2,r3 | 20040611125402 |
+---------------+-----------+--------------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO Operations
    -> VALUES ('mul', '11110000', 'mul r1,r2,r3', NULL);
mysql> quit  
  
  

An example of a more complex insert statement (also note the computation) is
    INSERT INTO tbl_name (col1,col2) VALUES (15,col1*2);
Other interesting statements include the update statement:
    UPDATE persondata SET age=age*2, age=age+1 WHERE Name = "Moerman";  
or the multi-table update example
    UPDATE items,month SET items.price=month.price WHERE items.id=month.id; and the select statement to retrieve specific values only as in
    select OperationName, Encoding from Operations where OperationName = "add"; For more examples, see the syntax section in the manual.

Note: if you want XML or HTML output, use the --xml or --html command line option. For example, to get a HTML list of all mnemonics use:

Dump the extire database (ready to reload) on stdout, e.g. for backup purposes

C:> mysqldump --host=Vega --user=evptest -c Kees_Test

Dumps tables (not create statements) as xml to file

C:> mysqldump --host=Vega --user=evptest -c --xml Kees_Test > dump.xml

How to use MySQL from Perl

Using MySQL from Perl is very easy (if you know SQL). You will need to have a few Perl modules installed: the DBI module (generic SQL database interface) and the DBD-mysql module (mySQL driver). For Windows both are available from the ActiveState repository (use the ppm command to install). Documentation (limited) is included in the DBD-mysql module.

A sample code fragment:

use DBI(); # note: the mysql driver will be loaded automatically when opening

# Connect to the database.
my $database = "DBI:mysql:database=Kees_Test;host=Vega;port=3306";
my $dbh = DBI->connect( $database, "mydatabase", "", {'RaiseError' => 1});

# Open table called 'Operations' to select all records
# More specific questions are possible, e.g. as in
# SELECT * FROM Operations where Mnemonic = "xor";
my $sth = $dbh->prepare("SELECT * FROM Operations");
$sth->execute();

# print returned records (fields OperationName and Encoding)
while (my $ref = $sth->fetchrow_hashref()) {
       print "Found a row: name = $ref->{'OperationName'}, encoding = $ref->{'Encoding'}\n";
}

# Insert new element
$dbh->do('INSERT INTO Operations VALUES ("mul", "10101001", "mul r1, r2", NULL)');

# Disconnect from the database.
$dbh->disconnect();

Note: NULL values can in Perl be represented by undef.
Note: when changing fields, TimeStamp fields are automatically updated (pass NULL as argument) However, if field value is same as previous, the TimeStamp field is unaltered.

Very useful: Change entry in database if exists (not changing TimeStamp field if no actual change !!!), insert if not yet exists. Very useful, as the timestamp does not alter if the new content is the same as the old content; so for users it is easy to see what has changed when:

if( !$dbh->do( qq[UPDATE Mnemonics SET ShortDesc="$shortdesc", PseudoCode="$pseudocode" WHERE mnemonic="$mnemonic";]))
{
   $dbh->do(qq[INSERT INTO Mnemonics VALUES ("$mnemonic", "$shortdesc", "$pseudocode", NULL);]);
}

For more details and examples, refer to the manuals and the Perl DBI documentation.

SQLite

(2009) For using mySQL, you have to have a mySQL server running somewhere. In case you make scripts for a web server, this is likely already the case, but to run it on your own computer can be quite complex. A fast replacement (and usable without administrator rights) is SQLite. This implements a large set of SQL, and works without server (it operates directly on SQLite database files). It has a command line interface for inspections and quick experiments, and has also for example Perl/DBI bindings. Some command line examples (open the program with an empty database using the command sqlite3 c:\temp\testbase.sql ):

CREATE TABLE test(index1 INTEGER PRIMARY KEY, name TEXT, text TEXT);
insert into test values(NULL, 'name1', 'text1');
select * from test;
select name, text from test where name = 'name1';
UPDATE test SET text = 'updated text' WHERE name = 'name1';
.dump test
.quit

The perl example above works as as, with the only exception of the database connection to DBI: replace the 'my $database' statement with:

my $database = 'dbi:SQLite:dbname=c:\temp\testbase.sql';

Nice for locally testing scripts without a local mySQL server!

Some more hints

• Use single quotes as string delimiters, not double quotes!
• Use back-ticks to use restricted words for e.g. table names (e.g. use `index` if you want to call a column 'index' ,as it is a reserved word)
• In SQLite, the command select * from sqlite_master; shows you the table structures in your database