MySQL Database

Your mysql database

You have been given a MySQL database on the roadrunner server. Access to the database requires three pieces of information:
database:  LOGIN 
username:  LOGIN 
password:  LOGIN 
This database account is completely separate from your roadrunner user account even though the information has been made similar so as to avoid account information overload.

The mysql command line client

There are many software tools which can be used to access your mysql database. The simplest, most direct way to do so is through the mysql command-line client tool. Here is how:
  1. Log in to roadrunner.cs.wcupa.edu account via a secure shell client.
  2. Invoke the client. You get no feedback for the characters typed as your password!
    $ mysql -p -u LOGIN LOGIN 
    Enter password: LOGIN
    
  3. At this point, you have an SQL command-line interpreter with this prompt:
    mysql>
    
    The first thing to know is how to get back out. Do so by doing:
    mysql> quit
    

Database Access Methods

So far we have this way to access your database:
$ mysql -p -u LOGIN LOGIN 
It turns out that the "-u" parameter is unnecessary. By default, mysql will use your shell account user name as the database account user name. This means you can access your account simply by doing:
$ mysql -p LOGIN
Password: LOGIN
mysql> 
Although considered somewhat insecure in general, the password can be provided on the command line like this:
$ mysql -pLOGIN  LOGIN
mysql> 
Note that there can be no space between the "-p" and the password.

One more way to access your account: access the MySQL system without specifying the database, and then specify the database through the use command:
$ mysql -pLOGIN
mysql> use LOGIN

Password Change

I do not recommend doing so initially, but here is how to change your database account password:
mysql> set password = password('some-new-password');

SQL command entry

The mysql client accepts SQL (Structured Query Language) commands terminated by a semicolon like this:
mysql> some sql command;  
The command can be continued across multiple lines. Whenever you type the terminating semicolon, the command is presumed to be complete:
mysql> some sql command
    -> across multiple lines;
If you get stuck after mistyping something, just type the semicolon, generating an syntax error and try again.

MySQL overview

MySQL is a relational database. To be more technically correct, MySQL is a relational database management system (DBMS). This means that it can maintain multiple databases and provides an interface and control features whereby multiple users can store and retrieve data.

Being a relational database means that the information is grouped into relations, or more simply tables. Each table row is an ordered collection of data values as you might think. The access of the data values in a single row is determined by the field name. For example, this might be considered a very simple table keeping track of thing, where each thing is some item and the quantity thereof:
thing
item qty
book 10
pencil 4
chair 12
The suggestion of the diagram is that the table name is thing. The data in each row are identified by one of two fields: item and qty. The main point missing in this diagram is the information about the types of the fields. In a database table, it is very important to specify the type of each field. Like Php, the type specifies what you can do with the data in that field, but, unlike Php, it specifies the all important size allocation for the data of that field.

Please refer to the table below for information about the available types. For our two fields we can use these types:
item:  varchar(20)       (a variable number of characters, up to 20)
qty:   int               (an integer, representing the quantity of the thing)
We can be far more efficient about the size of the qty field, if we know that quantities are going to be small; nevertheless, we'll ignore that concern.

SQL Data Types

SQL Type#bytesDescription
CHAR(N)Nfixed length string up to 255 bytes
VARCHAR(N)string length + (1 or 2) variable length string up to ≈ 65,000 bytes
TINYTEXTstring length + 1string up to ≈ 255 bytes
TEXTstring length + 2string up to 65,000 types
MEDIUMTEXTstring length + 3string up to ≈ 16 million bytes
LONGTEXTstring length + 4string up to ≈ 4.2 billion bytes
TINYINT10 to 255 or -127 to 128
SMALLINT20 to ≈ 65,000 (or neg/pos split)
MEDIUMINT30 to ≈ 16 million (or neg/pos split)
INT40 to ≈ 4 billion (or neg/pos split)
BIGINT80 to ≈ 18*1018 (or neg/pos split)
FLOAT46-digit precision
REAL (DOUBLE)816-digit precision
DECIMAL(N,D)length + (1 or 2)arbitrary precision
DATE3year-month-day
TIME3hour-min-sec
DATETIME8DATE and TIME combined
TIMESTAMP4# seconds since 1970
BINARY, VARBINARY like "*CHAR" types, but binary
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB like "*TEXT" types, but binary
ENUM('value1','value2',...)1 or 2column holds one of the specific string values
SET('value1','value2',...)1, 2, 3, 4 or 8column holds one or more of the specific string values

MySQL operations

The operations we want to be able to do fall into two general categories:
  1. SQL operations that create/modify our tables in some way
  2. SQL operations that retrieve information from our tables without changing them
  3. MySQL-specific operations that manipulate information about the DBMS itself, rather than information within the databases.
Regarding the operations of group I, there are many. Here are some common ones:
create table ...           create a table
drop table ...             remove a table

insert into table ...      add a new row

replace into table ...     replace a row by another
update table ...           change the contents of one or more rows
delete from table ...      remove one or more rows
The last three of these are separated from insert by virtue of the fact that they modify existing rows within a table. When these are used, one typically qualifies the affected rows by adding a where clause:
replace/update/delete ...   where ....
On the right-hand side of the where keyword is a specification that identifies one or more rows which are to be affected by the operation.

Regarding the operations of group II, there is effectively only one: the select operation. The syntax is like this:
select field-specification  from  table  where  where-specification
The select operation retrieves information from the rows identified by the where-specification. The field-specification indicates which fields from those rows to select and/or how to modify them.

MySQL learning examples

It is best just to get your feet wet and try out creating a table and performing various operations on it. Use the mysql command-line client as invoked above and enter these commands. You can simplify the command entry in many cases by using the command-repeat feature. Simply press the up-arrow key to access the history of previous commands and then use those directly or make modifications on them.
mysql> create table thing (item varchar(20), qty int);
mysql> show tables;
mysql> describe thing;
mysql> insert into thing values ( 'book', 10 );
mysql> insert into thing values ( 'pencil', 4 ), ( 'book', 5 );
mysql> select * from thing;
mysql> select count(*) from thing;
mysql> select max(qty) from thing;
mysql> delete from thing where item='book';
mysql> select * from thing;
mysql> insert into thing values  ('table', 2), ('chair',12);
mysql> select * from thing;
mysql> update thing set qty=qty-1 where qty>10;
mysql> select * from thing;
mysql> update thing set qty=qty+1 where item='table';
mysql> select * from thing;
mysql> drop table thing;
mysql> quit


© Robert M. Kline