MySQL Database Intro

DutchSorry, deze pagina heb ik voor mijn werk gemaakt, en is dus in het Engels. Ook is de pagina mogelijk wat verouderd...

Note: most text on this page date back to 2004, and might be a bit outdated!

MySQL logoWhat is MySQL: an open-source SQL relational database system. According to the web site: The MySQL (R) software delivers a very fast, multi-threaded, multi-user, and robust SQL database server. MySQL Server is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed softwaremySQL is often installed by default on e.g. web servers, and as such is more or less a standard database program for web-based applications.

What is SQL: Structured Query Language, a standardised way to query/update relational databases.

What is a relational database: very simply said it is a table-based database, containing multiple tables with relations between those tables, plus a query/update mechanism to exploit these relations.

What is MySQL used for? All kind of database aplications, and especially by web providers: most providers allow you to create a database on their server to store data. Could be anything: feedback, articles, web pages, pictures, just think of something.

What can I store in MySQL: nearly all kinds of data, including binary data, long strings, etc. However, in most cases it is more appropriate to select the right data type (see section 12 in the MySQL manual) instead of just dumping binary data, in order to be able to use operations like date/time comparison etc.

What do I need to access MySQL: MySQL is server-based. You can run a local server, and/or use the server on the Vega, which is the global DSP-IC server recently installed by Mircea (Thanks!). If you use an existing server, there are several possible clients, for example:

  • SQL database interfacingmysqlcc: MySQL Control Centre, a nice GUI-based interface to MySQL. You can input data, query the database, and (if you have sufficient rights) alter the database structures.
  • mysql: a command-line based interface also very usable for batch mode (e.g. generating xml or HTML output). Has a few partner programs, most important mysqlshow (to show meta-information on databases) and mysqldump (capable of dumping a complete database as ASCII, HTML or XML). See further down the page for some examples.
  • Perl: using the DBI module a powerful but simple to use interface to MySQL can be used inside Perl scripts. See further down the page for some example code.
  • Access (Microsoft) is also able to connect to MySQL (through ODBC), however I have no real experience with this route (nor with Access). Two steps (optional hints in the manual):
    1. You need to download and install the MySQL ODBC driver (version 3.51) for windows. To configure the driver: start the Control Panel, select Administrative Tools, select Data Sources (ODBC), press 'add', select the 'MySQL ODBC 3.51 driver', fill in the data for the database you want to access (for example the name or IP number of the Server=, port=3306), press 'Test Data Source' to verify settings, press OK (twice).
    2. Now you can start Access: create a new blanko database and store it somewhere, then with File/Get External Data/Link Tables you get into the Link window, at the entry 'Files of Type:' select the 'ODBC Databases()'. In the now visible 'Select Data Source' window select the 'Machine Data Source' tab, and select your database, click OK. You can now see a list of tables in your database, select the interesting ones. After OK, they will appear in your database. Clicking on them will open them.
  • How are databases backed up? This is probably still to be arranged; however making a (reloadable) dump of the database to file is easy, see below examples of mysqldump command. To be continued...

Can I make new tables and/or databases? Only if you have the corresponding rights. Contact the system administrator if you want to create new databases, and the database owner if you want to e.g. add tables to an existing database.

Some useful links

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

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)

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; 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:

H:\>mysql --host=Vega --user=evptest Kees_Test --html -e"select Mnemonic from Mnemonics;"

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");

# 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.

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.


Updated! (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 ):

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

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