An Brief Introduction to MySQL Database
MySQL is a free, efficient and widely used database system that implements structured query
language, i.e., SQL. It supports various platforms such as Linux, MAC OS X, Windows and more.
You will learn how to access MySQL database and how to write and run SQL on MySQL in this lab.
Access MySQL Database via Hercules
You can logging into MySQL database by the following commands:
a049510$ ssh hercules
hercules% mysql -p your_hercules_username
mysql> use database_name;
You must log into hercules before logging into MySQL database system. The first
ssh command requires your hercules account name and password. The second
mysql command requires the mysql account name and password, i.e., the one you just created.
You need to select a database before run your SQL commands. You can use the following MySQL command to display all existing databases:
mysql> show databases;
| Database |
| information_schema |
| temp1 |
In this lab, you use the one labeled with your MySQL-account name as your database. For example, if your account name is temp1, then you can enter
use temp1;. You are able to create a database for your class projects, for example
classprojects, and a database for your lab practices, for example,
labassignments. The following link provides details of how to create a new database under your account.
Create database: http://www.w3schools.com/sql/sql_create_db.asp
- Your SQL commands should end with a semi-colon (
- You do not need to create a database for the labs, since a database is already created for you
to use. Your database's name is your username.
Structured Query Language (SQL)
SQL, stands for structured query language. It is a standard query language for popular relational databases such MySQL, SQL Server and Oracle. Although diffrerent relational databases implement SQL with their own extentions and properties, they share the standard on commonly used operations, namely, data definition language (DDL) and data manipulation language (DML). DML commands can be used for querying and updating data. DDL commands create or delete a table in database.
Data Definition Language (DDL)
The following introduces SQL commands:
drop table and
describe (desc) table_name.
- creates a new table in your database
CREATE TABLE user (
user_id INT NOT NULL AUTO_INCREMENT,
user_email VARCHAR(255) NOT NULL,
user_password VARCHAR(255) NOT NULL,
PRIMARY KEY (user_id)
create table post (
post_id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
content varchar(255) NOT NULL,
time TIMESTAMP NOT NULL,
PRIMARY KEY (post_id),
FOREIGN KEY (user_id) REFERENCES user (user_id)
The DATE, DATETIME, and TIMESTAMP Types
- destroys a table from your database
- describes the sturture of a database table, including field names, default values and other properties
The following links provide detials of these SQL commands.
Create a table: http://www.w3schools.com/sql/sql_create_table.asp
Destroy a table: http://www.w3schools.com/sql/sql_drop.asp
Describe a table: http://dev.mysql.com/doc/refman/4.1/en/describe.html
This lab assignment contains 4 steps.
Keep all your sql commands in a log file, Yourusername.txt,
link it on your index.html and upload it to URcourses by 11:55 PM.
Step1: Load the students table by using the source command
Download this SQL script file, i.e.,
and upload the file into your public_html directory.
Make sure the file permission for students.sql is 644.
If you are in hercules account, you can use the following command:
cp -p /net/data/ftp/pub/class/215/basic_sql/students.sql ~/public_html/
Log into your MySQL and execute the following MySQL command:
mysql> source students.sql
source command creates the following students table as well as the data.
mysql> describe students;
| Field | Type | Null | Key | Default | Extra |
| student_ID | varchar(30) | NO | | NULL | |
| first_Name | varchar(30) | NO | | NULL | |
| middle_Name | varchar(30) | YES | | NULL | |
| last_Name | varchar(30) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| course_Name | varchar(30) | NO | | NULL | |
| course_Description | varchar(255) | NO | | NULL | |
| marks | real | NO | | NULL | |
Step2: Fix incorrect data
The table you created may contain errors or inconsistancy information due to various user inputs,
delete commands to fix these problems.
- A student, Ahmed Abbad, has a problem with his email address. Find the problem and use update command to fix it.
- Yupeng Xu doesn't have a middle name, but the system put his last name into the middle name column. Try to fix the problem for him.
- The course description of CS215 should be Web and Database Programming, but some records use a different description for CS215, find it and fix it.
- A student, Tao Ke, is taking a course "CS000" that does not exist. Use delete command to remove this record.
Note: keep the commands (e.g., update, delete, etc.) as part of the solution for the exercises.
Step3: Decompose the students table into three new tables
Decompose students table into three sub tables according to the following description:
- Create a table called Student_Info
contains five attributes, student ID, first_name, middle_name, last_name and email address.
- Create a table called Courses contains two attributes, course_Name and course_Description.
- Create a third table called
Marks table contains student_ID, course_Name and marks.
Step4: Extract records from students table and insert into new tables
Populate the three new tables. They are Student_Info, Courses and Marks.
Show all records of each new table.
The following SQL command is a combination of
commands. You can use this command to extract data from old students table and
insert into your new tables.
insert into table2 (column_name_1,column_name2,...column_name_m) select
distinct column_name_1, column_name_2,...column_name_m from table1;
insert into Student_Info (student_id,first_name,middle_name,last_name, email)
select distinct student_ID,first_name,middle_name, last_name,email from students;
The keyword distinct is optional and it helps to retrieve non-duplicated database records.