Basic MySQL Commands

This page assumes you know how to access MySQL in your preferred operating system. In MATLAB, MySQL is accessed through the mym.m function, so all the commands described below will be in the form

mym('COMMAND')

Otherwise, all commands are identical no matter which OS you use.

Summary of Database Terms

The main terms you need to acquaint yourself with for now are:

Database

• The overall container for a collection of MySQL data

Table

• A subcontainer within a database that stores the actual data

Row

• A single record within a table, which may contain several fields

Column

• The name of a field within a row.

Common commands

Before we start, note that typically, MySQL commands end with a semicolon. In the mYm MATLAB interface, you don't have to do this, but you can issue multiple commands separated with semicolons to execute them as a batch.

SQL commands and keywords are not case-sensitive, therefore CREATE, create, and cReAtE all mean the same thing. The recommended style is to use UPPERCASE.

Table names are case-sensitive on Linux and Mac OS X, but not so for Windows. Therefore, it is recommended to use lowercase for tables.

Creating a database

Let's create a database called books.

CREATE DATABASE books;

Note that if you are working inside the WSC_DB database in the SEV or in any situation where you are a single user with access to a single database that was created for you, you cannot create a database or users. In that case, skip to the section "Creating a table."

After creating the database, we want to work with it:

USE books;

We should see the message "Database changed".

Creating users

Creating a user gives the database administrator power to restrict permissions on what can be done to which database. It is done by issuing the GRANT command, which takes the following form:

GRANT PRIVILEGES ON database.object TO 'username@hostname' IDENTIFIED BY 'password';

Examples:

mym(['GRANT ALL ON ',dbStruct.name,'.* TO ''',dbStruct.user,'''@''localhost'' IDENTIFIED BY ''',dbStruct.password,'''']);

GRANT ALL ON books.* TO bookworm@localhost IDENTIFIED BY bookpass;

So what this does is create a user "bookworm" who can access the books database and all its objects using the password bookpass. If we wanted to restrict bookworm's access to just one object in books, then we would put books.object instead of an asterisk. We can also omit "localhost" because it is the default hostname.

Note that you can only grant privileges that you yourself have, and you must also have the privilege to issue GRANT commands.

Creating a table

Now let us create a table. First USE the database you want to use.

USE books;

Then issue a command like this:

mym('CREATE TABLE classics(author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE MyISAM');

mym(['CREATE TABLE IF NOT EXISTS ',TableName,'('...
'PatStudyKey SMALLINT UNSIGNED NOT NULL',...
', DetectorID TINYINT (3) UNSIGNED NOT NULL'...
', Start_sample INTEGER UNSIGNED NOT NULL',...
', Stop_sample INTEGER UNSIGNED NOT NULL',...
', Duration_seconds REAL UNSIGNED NOT NULL',...
', Epoch SMALLINT UNSIGNED NOT NULL',...
', Stage ENUM(''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'')',...
', Cycle TINYINT UNSIGNED DEFAULT 1',...
', Params BLOB DEFAULT NULL',...
', KEY (DetectorID)',...
', PRIMARY KEY (PatStudyKey,DetectorID,Start_sample))']);

To check if your new table has been created, type:

DESCRIBE classics;

The following should pop up for the first example:

Field Type Null Key Default Extra
+------+ +------------+ +----+ +---+ +-------+ +-----+
author varchar(128) YES
title varchar(128) YES
type varchar(16) YES
year char(4) YES

Here is what each column in that output is describing:

Field The name of each field or column within a table

Type The type of data being stored in the field

Null Whether a field is allowed to contain a value of NULL

Key MySQL supports keys, which are quick ways to look up and search for data. The Key heading shows what type of key (if any).

Default The default value that will be assigned to the field if no value is specified when a new row is created

Extra Additional information, such as whether a field is set to auto-increment.

We will not get into various data types, but they are pretty self-explanatory, except for BLOB, which stands for Binary Large OBject. The number (n) in parentheses after a data type signifies the number of bytes used.

In the second example, there are some characteristics and data types that are useful.

INT UNSIGNED Unsigned integer, i.e. an integer from 0 to over 4 billion. A signed integer has a range of about -2 billion to 2 billion.

NOT NULL Ensures that every column has a value.

AUTO_INCREMENT Causes MySQL to set a unique value for this column in every row. We don't have control over the value that this column will take, but this does make every row unique, and thus you can use this column as a KEY to search for indices.

Here is another table created by the create_diagnostics_T.m function.

Field Type Null Key Default Extra +-------------------+ +--------------------+ +----+ +---+ +-------+ +-----+ PatStudyKey smallint(5) unsigned NO PRI
Stage varchar(2) NO PRI 7
Cycle tinyint(3) unsigned NO PRI 0
Duration_sec smallint(6) YES 0
Count smallint(6) YES 0
Pct_study decimal(4,2) YES 0.00
Pct_sleep decimal(4,2) YES 0.00
Fragmentation_count smallint(6) YES 0

Here you can see examples of the NULL, KEY, and DEFAULT fields.

Adding data to a table

To add data to a table, use the INSERT command. Let's populate our books table.

INSERT INTO books (author, title, type, year) VALUES('Mark Twain', 'The Adventures of Tom Sawyer', 'Fiction', '1876') INSERT INTO books (author, title, type, year) VALUES ('Charles Darwin', 'Origin of Species', 'Non-Fiction', '1856')

and so on.

The first part of the command, INSERT INTO books, tells MySQL where to insert the following data. Then, within parentheses, the four column names are listed, all separated by commas. This tells MySQL that these are the fields into which the data is to be inserted.

The second line of each INSERT command contains the keyword VALUES followed by four values (strings in this case), separated by commas. The number of columns must match the number of data items. If you accidentally listed the columns in a different order from the data, the data would go in the wrong columns.

Deleting a table

You can delete a column or table by using the DROP command.

Querying a MySQL database

SELECT

The SELECT command extracts data from a table.

Syntax:

SELECT something FROM tablename

If "something" is a * (asterisk), it will select all columns. You can also select only certain columns by specifying them and separating them by commas.

SELECT author, title FROM books;

SELECT COUNT will return the number of rows in a table that have a value for the queried field. For example,

SELECT COUNT(*) FROM table1;

will return the number of rows in table1.

SELECT COUNT(COMMENT1) FROM table1;

will return the number of rows that contain a value in the field COMMENT1.

SELECT DISTINCT will return entries that are distinct. That is, it will weed out multiple entries when they contain the same data.

SELECT DISTINCT author FROM books;

WHERE is probably the most important query keyword. It will return queries where a certain expression is true.

SELECT author,title FROM books WHERE author="Mark Twain";
mym('SELECT * FROM diagnostics_t WHERE bmi>25')

You can also use partial matches for searches using LIKE and %.

SELECT author, title FROM books WHERE author LIKE "Charles%";
SELECT author,title FROM books WHERE title LIKE "%Species";
SELECT author,title FROM books WHERE title LIKE "%and%";

LIMIT allows you to choose how many rows to return in a query and where in the table to start returning them.

SELECT author,title FROM books LIMIT 3;
SELECT author,title FROM books LIMIT 1,2;

The first command will return the first three entries from the top of the table. The second will skip 1 entry, and then return two rows (i.e., it will return the 2nd and 3rd entries).

ORDER BY sorts returned results by one or more fields in ascending or descending order.

SELECT * FROM books ORDER BY author;
SELECT * FROM diagnostics_t ORDER BY date DESC;

Some of the material in this page comes from Robin Nixon, "Learning PHP, MySQL & JavaScript", O'Reilly, 2009.