Introduction To MySQL

Starting MySQL Command-Line Interface

MySQL is preinstalled on our provided SEASNet machines. Once you log in to the VM, you can start the MySQL command-line interface by typing mysql -u USERNAME -p: 
cs143@cs143:~$ mysql -u cs143ta -p
where USERNAME is your userid with which you logged in onto the system. Once you press enter, you should be prompted for password, which is once again the same that yo uused for logging onto the system. Then you should receive the following prompt 
mysql>
and be inside the MySQL command-line interface. All commands in this tutorial should be issued inside the MySQL command-line unless noted otherwise.

Choosing a Databases in MySQL

MySQL allows users to create multiple databases, so that a single MySQL server can host databases for many independent applications. Before you start issuing SQL commands to mysql, you first have to select the database that you will be using. In order to see what databases currently exist, run 
    SHOW DATABASES;
You will see an output like 
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| cs143ta            | 
+--------------------+
information_schema is a database that MySQL creates automatically and uses to maintain some internal statistics on datbases and tables. The other database, cs143ta, is what we created for the project. Your database name will be different depending on your username (note database names are case-sensitive in MySQL). The cs143ta database is your "production" database, meant for use in the final versions of your code. Select the cs1434ta database (or in your case it database name will be the same as your userid) for the rest of this tutorial by issuing the command 
    USE cs143ta;
It is also possible to specify a database as a command line parameter to the mysql command: 
cs143@cs143:~$ mysql -u cs143ta -p cs143ta

Creating a Table

Once you select a database, you can execute any SQL command. For example, you can create a table using the CREATE TABLE command: 
     CREATE TABLE <tableName> (
         <list of attributes and their types>
     );
Note that all reserved keywords (like CREATE and TABLE) are case-insensitive and identifiers (like table names and attribute names) are case-sensitive in MySQL by default. That is, a table named STUDENT is different from the student table.

You may enter a command on one line or on several lines. If your command runs over several lines, you will be prompted with " -> " until you type the semicolon that ends any command. An example table-creation command is: 

     CREATE TABLE tbl(a int, b char(20));
This command creates a table named tbl with two attributes. The first, named a, is an integer, and the second, named b, is a character string of length (up to) 20.

When you create a table, you can declare a (set of) attribute(s) to be the primary key like: 

     CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);
or 
     CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY(a,b,c));
IMPORTANT NOTE: In this project we will be using FOREIGN KEY integrity constraints. In MySQL in order to use FOREIGN KEYs we need to create tables using INNODB engine. In other words, both the referenced table and the referee table need to be created using this engine. For more information with regards to syntax please have a look at MySQL Foreign Key

Inserting and Retrieving Tuples

Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT command: 
     INSERT INTO <tableName>
         VALUES( <list of values for attributes, in order> );
For instance, we can insert the tuple (10, 'foobar') into relation tbl by 
     INSERT INTO tbl VALUES(10, 'foobar');

Once tuples are inserted, we can see the tuples in a relation with the command: 

     SELECT *
     FROM <tableName>;
For instance, after the above create and insert statements, the command 
     SELECT * FROM tbl;
produces the result 
+------+--------+
| a    | b      |
+------+--------+
|   10 | foobar | 
+------+--------+

Creating Index

Having created a table, we can create an index on some attributes of the table. The command for creating an index is: 
     CREATE INDEX <indexName> ON <tableName>(<list of attributes>);
For instance, we can create an index on b attribute of table tbl by 
     CREATE INDEX IdxOnAttrB ON tbl(b);

Later, if you want to drop an index, you use the following command 

     DROP INDEX <indexName> ON <tableName>;
like 
     DROP INDEX IdxOnAttrB ON tbl;
Note that in MySQL, an index is automatically created on primary keys and unique attributes.

Bulk Loading Data

Instead of inserting tuples one at a time, it is possible to create a file that contains all tuples that you want to load in batch. The command for bulking loading tuples from a file is the following: 
     LOAD DATA LOCAL INFILE <dataFile> INTO TABLE <tableName>;
where <dataFile> is the name of the file that contains the tuples. Each line in the data file corresponds to one tuple and columns are separated by a tab character (\t). You can specify a NULL value in the data file using \N.

For example, the following data file 

1	first
2	second
3	\N
will insert three tuples, (1, 'first'), (2, 'second'), and (3, NULL) to a table. If you want to use, say, commas to separate columns, not tabs, add FIELDS TERMINATED BY ',' to the LOAD command as follows: 
     LOAD DATA LOCAL INFILE <dataFile> INTO TABLE <tableName> 
     FIELDS TERMINATED BY ',';

If some columns in the data file is enclosed with, say, double quotes, you need to add OPTIONALLY ENCLOED BY '"' as well: 
     LOAD DATA LOCAL INFILE <dataFile> INTO TABLE <tableName>
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

Getting Rid of Your Tables

To remove a table from your database, execute 
     DROP TABLE <tableName>;
We suggest you execute 
     DROP TABLE tbl;
after trying out the sequence of commands in this tutorial to avoid leaving a lot of garbage tables around.

Getting Information About Your TABLES

You can get the set of all tables within the current database by the following command: 
     SHOW TABLES;
Once you know the list of tables, it is also possible to learn more about the table by issuing the command: 
     DESCRIBE <tableName>;

Executing SQL From a File

Instead of typing and running SQL commands at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed.

To run the commands in foo.sql (in the current working directory), type: 

     SOURCE foo.sql;
in mysql. Files like foo.sql that have SQL commands to be executed are often referred to as a (batch) script file. You can also execute the script file directly from the Unix shell by redirecting the input to mysql like the following: 
cs143ta@cs143:~$ mysql -u cs143ta -p < foo.sql

Recording Your MySQL Session In a File

mysql provides the command TEE to save the queries that you executed and their results to a file. At the mysql> prompt, you say: 
     TEE foo.txt;
and a file called foo.txt will appear in your current directory and will record all user input and system output, until you exit mysql or type: 
     NOTEE;
Note that if the file foo.txt existed previously, new output will be appended to the file.

Quitting mysql

To leave mysql, type 
     QUIT;

Help Facilities

mysql provides internal help facilities for MySQL commands. To see a list of commands for which help is available, type help or help contents in mysql. To look up help for a particular topic (listed in the contents), type help followed by the topic.

This document was adapted from Getting Started With Oracle