MySQL Tutorial

Connecting to MySQL

In order to connect to the MySQL database server you must change directory to the z server.

> cd /u/z/users/cs327e/userName

Execute the following command at the shell prompt:

> mysql -h z.cs.utexas.edu -u userName - p

Replace the word userName in the command with your own user name. The system will respond by asking for your password:

Enter password:

Enter your MySQL password and press the Enter key. Your MySQL password has been given to you and it is different from your CS account password. You will not see any entry as you type your password. If your password is accepted you will see the MySQL prompt.

mysql>

Change your password the very first time you login.

mysql> set password=password('new_password');

The quotation marks are necessary within the parentheses. You can use this to change your password any time later on if you need to.

Basic MySQL Queries

In order to start working, you need to have a database. A database has already been created for you: cs327e_s12_userName. Check by running the command:

mysql> show databases;

You must select the database each time you want to use the database. The command to issue is:

mysql> use cs327e_s12_userName;

To quit or exit the system, use the command:

mysql> quit;

To select version and date:

mysql> SELECT version(), current_date;

You can use MySQL as a simple calculator:

mysql> SELECT sin(pi()/4), (3 + 2) * 4;

Commands need not be given on a single line. But be sure to end the command with a semi-colon. To cancel a command use \c;

mysql> SELECT
    -> USER()
    -> \c;

Creating Tables

To see the tables in your database do:

mysql> show tables;

To create a table for students in this class we can do:

mysql> CREATE TABLE student (lastName VARCHAR(20), firstName VARCHAR(20),
    -> major VARCHAR(20), bDay DATE);

The format for the DATE is YYYY-MM-DD.

To see if the table has been correctly set up do:

mysql> DESCRIBE student;

To enter data into the table use the command:

mysql> INSERT INTO student
    -> VALUES ('Duck', 'Donald', 'Dance', '1934-06-09');

Retrieving Data from Tables

To retrieve data from a table use the SELECT command:

mysql> SELECT *
    -> FROM student;

This will return all the data in the table.

Changing Values in a Table

To change a value in a table use the UPDATE command:

mysql> UPDATE student SET major = 'Song'
    -> WHERE lastName = 'Duck';

Deleting a Table

Use the DROP TABLE command to delete a table:

mysql> DROP TABLE student;

Differences in MySQL SQL and Oracle SQL

For a complete discussion on the differences between the SQL syntax on the MySQL database and Oracle database refer to the article Oracle and MySQL Compared. But here is a short table that gives some of the differences that you need to be aware of:

MySQL Data Type	Oracle Data Type
BIGINT	NUMBER(19,0)
CHAR	CHAR
DATE	DATE
DATETIME	DATE
DECIMAL	FLOAT(24)
DOUBLE	FLOAT(24)
FLOAT	FLOAT
INT	NUMBER(10, 0)
INTEGER	NUMBER(10, 0)
NUMERIC	NUMBER
REAL	FLOAT(24)
SMALLINT	NUMBER(5,0)
TIME	DATE
TIMESTAMP	DATE
VARCHAR	VARCHAR2
YEAR	NUMBER

Running a SQL Script

As a test, copy the file Name.sql into your directory on the z server. Make sure that the file has .sql extension. To run this script run the command:

mysql> source Name.sql;

The script should run and create a table called Name and populate that with data.