Assume that the MySQL server is already set up and running on either a Ubuntu or Slackware Linux machine. The MySQL server may be set up to support different users. For simplicity, we shall only assume the root user. In order to use MySQL, the user must run a MySQL client to connect to the server. MySQL supports client connections from remote IP hosts. For simplicity, we shall run both the server and client on the same machine, i.e. the default localhost. The following sections show to use MySQL to manage databases.
1. Connect to MySQL Server
The first step of using MySQL is to run the MySQL client program. From an X-Window terminal, enter the MySQL client command, mysql, which connects to the MySQL server on the default localhost on the same computer.
mysql -u root -p # specify the root user with password
Enter password: # enter the MySQL root user password
mysql> # mysql prompt
After connecting to the MySQL server, you will be accessing the MySQL shell, as indicated by a mysql> prompt. The MySQL shell behaves like a regular shell. It shows a mysql promt >, asking the user to enter SQL commands for the MySQL server to execute. Like regular sh, it also maintains a command history, which allows the user to recall and modify previous commands by arrow keys. However, it only accepts mysql commands or mysql scripts, but not regular sh commands. When entering MySQL commands, the reader should note the following.
.All MySQL command lines must end with a semicolon. For long commands, you may enter the command phrases on separate lines (by pressing the ENTER key). mysql will continue to prompt for more inputs by a -> symbol until it sees an ending semicolon.
.MySQL command line is not case sensitive. Although not required, it is a common practice to write MySQL commands in uppercase and databases, tables, usernames, or text in lowercase for clarity and easier identification.
2. Show Databases
The command SHOW DATABASES shows the current databases in MySQL.
Example:
3. Create New database
The command CREATE DATABASE dbname creates a new database named dbname, if it does not yet exist. The command can be qualified by an optional IF NOT EXISTS clause to avoid an error message if the database already exists.
Example:
mysql> CREATE DATABASE testdb
Query OK; 1 row affected (0.02 sec) # mysql response
After creating a new database, enter SHOW DATABASES to see the result.
4. Drop Database
The command DROP DATABASE dbname drops or deletes a named database if it exists. The command may be qualified with an optional IF EXIST clause. Beware that the DROP operation is irreversible. Once a database is dropped or deleted, it can not be undone or restored. So be careful about its usage.
Example:
mysql> DROP DATABASE testdb;
Query OK; one row affected (0.04 sec)
5. Choose Database
Assume that MySQL already has several databases. In order to operate on a specific database, the user must choose a database by the USE dbname command.
Example:
mysql> CREATE DATABASE cs360;
Query OK, 1 row affected (0.00 sec)
mysql> use cs360;
Database changed
6. Create Tables
This section shows how to create tables in MySQL databases. Assume that a cs360 database contains the following student records.
struct students)
int student_id; # an integer ID number must exist
char name[20]; # name string of 20 chars
int score; # an integer exam score, which may not exist
}
The command CREATE TABLE table_name creates a table in the current database. The command syntax is
CREATE TABLE [IF NOT EXISTS] tableName (
columnName columnType columnAttribute, …
PRIMARY KEY(columnName),
FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)
)
For a single table case, the FOREIGN KEY clause is not needed.
Example:
mysql> CREATE TABLE students (
student_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name CHAR(20),
score INT );
Query OK, 0 rows affected (0.00 sec)
The DESCRIBE or DESC command shows the table format and column attributes.
It shows that the students table has 3 fields or columns, where
“student_id” is an integer, which can not be NULL, used as the primary key and its value will be incremented automatically when new rows are added.
“name” is a fixed length string of 20 chars long, which may be NULL.
“score” is an integer.
In a table, a primary key is a column or a set of columns which can be used to uniquely identify a row. A primary key is unique by default. As shown, the name column may be NULL, which could result in a row with a valid student_id but without a name. In order to avoid this, it should be declared with the not null attribute.
7. Drop Tables
The DROP TABLE table_name command drops or deletes a table.
Example:
mysql> CREATE TABLE dummy (
id INT PRIMARY KEY, name CHAR(20));
Query OK, 0 rows affected (0.00 sec)
8. Data Types in MySQL
Before continuing, it is necessary to know the basic data types used in MySQL, which include mainly 3 categories; numeric, strings, date and time. We only show some of the commonly used data types in each category.
Numeric Types:
INT: integer (4 bytes), TINYINT: (1 byte), SMALLINT: (2 bytes), etc.
FLOAT: floating pointers number.
String Types:
CHAR(size): fixed length string of 1-255 chars.
VARCHAR(size): variable length strings, but may not use all the space.
TEXT: variable length strings
Date and Time Types:
DATE – Date in YYYY-MM-DD format
TIME Stores the time in HH:MM:SS format.
9. Insert Rows
To add rows to a table, use the INSERT command, which has the syntax form
INSERT INTO table_name VLAUES (columnValue1, columnValue2, … .);
Example:
mysql> INSERT INTO students VALUES (1001, ‘Baker’, ’50’);
Query OK, 1 row affected (0.01 sec)
Similarly, we may insert more student records into the table. Since it is tedious to insert many entries by individual command lines manually, we may create a MySQL script file containing many INSERT command lines and use it as an input source file to mysql. Assume that we have edited a .sql script file as shown below.
/********* insert.sql script file **********/
INSERT INTO students VALUES (NULL, “Miller”, 65);
INSERT INTO students VALUES (2001, “Smith”, 76);
INSERT INTO students VALUES (NULL, “Walton”, 85);
Enter the SOURCE command to let mysql use the sql script as input file, as in
mysql> SOURCE insert.sql; # use insert.sql as input source file (Query OK, rows affected, etc)
Note that, when inserting a new row, if an ID number is NULL, it will be assigned the next value (from the last entered ID value) by auto_increment.
10. Delete Rows
Use the DELETE command to delete row(s) from a table, which has the syntax form
DELETE FROM table_name; # delete all rows of a table
DELETE FROM table_name WHERE condition; # delete row by condition
Example To demonstrate the delete operation, we first add a new row and then delete it from the table.
mysql> INSERT INTO students VALUES (NULL, ‘Zach, ’45’);
11. Update Table
The UPDATE command is used to modify existing records (columns) in a table. It has the syntax form
UPDATE table_name SET col1 = value1, col2 = value2, … WHERE condition;
Example Assume that we want to change Walton’s score to 92.
mysql> UPDATE students SET score = 92 WHERE name = ‘Walton’;
Similarly, we may update the values of other columns by changing the WHERE condition.
12. Alter Table
The ALTER TABLE command is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.
- Change table name:
ALTER TABLE table_name RENAME TO new_name;
- Add Column: To add a column in a table, use the following command:
ALTER TABLE table_name ADD column_name datatype;
- Drop Column: To drop a column, use the following command:
ALTER TABLE table_name DROP column_name datatype;
- Alter/Modify Column: To change the data type of a column in a table, use the following command:
ALTER TABLE table_name ALTER COLUMN column_name datatype;
Examples Assume that we want to add a column named grade to the students table, and then assign letter grades of A to D to the students by their exam scores.
mysql> ALTER TABLE students ADD grade CHAR(2); // for ‘A’, ‘B+’, etc.
(Quert OK, 4 rows affected (0,01 sec)
After adding the grade column, all the entry values are initially NULL. We may assign a letter grade to students by the UPDATE table_name command.
Example:
mysql> UPDATE students SET grade = ‘A’ WHERE name = ‘Walton’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Alternatively, we can also assign letter grades by using ranges of scores in the WHERE condition clause.
Examples:
mysql> UPDATE students SET grade = ‘A’ WHERE score > 80;
Assume that we have executed the following commands:
mysql> UPDATE students SET grade = ‘B’ WHERE score >= 70 AND score < 80;
mysql> UPDATE students SET grade = ‘C’ WHERE score >= 60 and score < 70;
mysql> UPDATE students SET grade = ‘D’ WHERE score < 60;
13. Related Tables
So far, we have shown databases with only a single table. A real database may consist of many tables, which are related to one another. In MySQL, table relationships are defined by using Primary Key-Foreign Key constraints. A link is created between two tables where the primary key of one table is associated with the foreign key of another table. In MySQL, tables may be related in several ways, which include
(1). One-to-One (1-1) Relation
One-to-One (1-1) relation is defined as the relationship between two tables where the tables are associated with each other based on only one matching row. This kind of relationship can be created using the Primary key-Unique foreign key constraints. Assume that in the cs360 database, each student has a unique email address. We may include the email address of each student in the students table, but that would require an additional column in the students table. Instead, we can create a separate email table containing only student email addresses, and define a 1-1 relation between the two tables by a unique foreign key in the email table, which references the primary key in the students table.
Example:
mysql> CREATE TABLE email (id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT UNIQUE NOT NULL, email CHAR (40),
FOREIGN KEY (student_id) REFERENCES students(student_id));
Query OK, 0 rows affected (0.00 sec)
Next, we insert student email addresses into the email table. Then we query the email table and show the table contents.
mysql> INSERT INTO email VALUES (NULL, 2002, ’walton@wsu.edu’);
mysql> INSERT INTO email VALUES (NULL, 2001, ’smith@gmail.com’);
mysql> INSERT INTO email VALUES (NULL, 1002, ’miller@hotmail.com’);
mysql> INSERT INTO email VALUES (NULL, 1001, ’baker@gmail.com’);
Note that in the email table the student_id can be in any order as long as they are all unique. Each student_id is a foreign key which references a primary key in the students table.
mysql> SELECT a.name, b.email FROM students a, email b WHERE
a.student_id = b.student_id;
(2). One-to-Many (1-M) Relations
In a database, One-to-Many or 1-M relations are more common and useful than 1-1 relations. The One- to-Many relation is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relation can be created using Primary key-Foreign key relationship. Suppose that the course cs360 has a required textbook and also a reference book. Students may order the books through the book store, which uses a book_order table to keep track of student book orders. Each book order belongs to a unique student, but each student may order one or several books, including none as most of them do. So the relation between the students table and the book_order table is one-to-many. As in the one-to-one relation case, we may use a foreign key in the book_order table to reference the primary key in the students table. With the two tables related to each other, we can run queries on the tables to check which student has not ordered the required textbook, etc.
Example In this example, we first create a book_order table, add a foreign key to it and populate the table with book orders from students.
mysql> CREATE TABLE book_order
(order_no INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
book_name char(20),
date DATE
);
Query OK, 0 rows affected (0.01 sec)
mysql> DESC book_order;
mysql> ALTER TABLE book_order ADD FOREIGN KEY (student_id)
REFERENCES students(student_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC book_order;
mysql> SELECT * FROM book_order;
(3). Many-to-Many (M-M) Relations
Two tables have a M-M relation if multiple records in one table are related to multiple records in another table. For instance, each student may take several classes and each class usually has many students. So the relation between a students table and a class enrollment table is M-M. A standard way of handling M-M relations is to create a join table between the two tables. The join table uses foreign keys to reference the primary keys in both tables, thus creating a bridge between the two tables.
(4). Self-Referencing Relation
A table can relate to itself by some columns. We shall not discuss this type of self-referencing relations.
14. Join Operations
In MySQL, JOIN operations are used to retrieve data from multiple tables. There are 4 different types of JOIN operations.
(INNER) JOIN table1, table2: retrieve items that are common in both tables.
LEFT JOIN table1, table2: retrieve items in table1 and items common in both tables.
RIGHT JOIN table1, table2: retrieve items in table2 and items common in both tables.
OUTER JOIN tabel1, table2: retrieve items in both tables; uncommon and not useful.
In terms of regular set operations, JOIN operations in MySQL can be interpreted as follows. Denote + as union of two sets and ^ as intersection of two sets. Then
(INNER) JOIN t1, t2 = t1 ^ t2
LEFT JOIN t1, t2 = t1 + (t1 ^ t2)
RIGHT JOIN t1, t2 = t2 + (t1 ^ t2)
OUTER JOIN t1, t2 = t1 + t2;
We illustrate JOIN operations on the tables in the cs360 database by examples.
mysql> select * from students JOIN email ON
students.student_id = email.student id;
mysql> SELECT * FROM students JOIN book_order ON
students.student_id = book_order.student_id;
mysql> SELECT * FROM students RIGHT JOIN book_order ON
students.student_id = book_order.student_id;
mysql> SELECT * FROM students LEFT JOIN book_order ON
students.student_id = book_order.student_id;
mysql> SELECT * FROM book_order JOIN students ON
students.student_id = book_order.student_id;
15. MySQL Database Diagram
In MySQL as well as in all relational data base systems, it is very useful to depict the relationships among the tables by a database diagram. Such diagrams are usually called ERD (Entity Relationship Diagram) or EERD (Enhanced/Extended ERD). They provide a visual representation of the various components in a database and their relationships. For the simple cs360 database used in this chapter, it can be represented by the database diagram shown below.
In the database diagram, arrow lines depict the relations between tables by connecting the foreign key in one table to its referenced primary key in another table. Lines with a single arrow mark at both ends denote 1-1 relations and lines with multiple arrow marks at one end denote 1-M relations.
16. MySQL Scripts
Like regular Unix/Linux sh, the MySQL shell can also accepts and executes scripts files. MySQL script files have the suffix .sql. They contain MySQL commands for the MySQL server to execute. Instead of entering command lines by hand, we may use MySQL scripts to create database, create tables in the database, insert table entries and alter the table contents, etc. This section shows how to use MySQL scripts.
First, we create a do.sql file as shown below. For convenience, all MySQL commands are shown in lowercase but highlighted for clarity.
— Comments: do.sql script file
drop database if exists cs360;
— 1. create database cs360
create database if not exists cs360;
show databases;
use cs360;
show tables;
— 2. create table students
create table students (id INT not null primary key auto_increment,
name char (20) NOT NULL, score INT);
desc students;
— 3. insert student records into students table
insert into students values (1001, ’Baker’, 92);
insert into students values (NULL, ’Miller’, 65);
insert into students values (2001, ’Smith’, 76);
insert into students values (NULL, ’Walton’, 87);
insert into students values (NULL, ’Zach’, 50);
— 4. show students table contents
select * from students;
— 5 add grade column to students table alter
table students add grade char(2);
select * from students;
— 6. Assign student grades by score ranges
update students set grade = ’A’ where score >= 90;
update students set grade = ’B’ where score >= 80 and score < 90;
update students set grade = ’C’ where score >= 70 and score < 80;
update students set grade = ’D’ where score >= 60 and score < 70;
update students set grade = ’F’ where score < 60;
— 7. Show students table contents
select * from students;
— end of script file
There are two ways to run the mysql client using sql scripts. The first method is to use the SOURCE command to let mysql take inputs from a script file. The following shows the MySQL outputs with added comments to identify the results of the commands in the script file.
mysql> source do.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
// create database cs360 and show it
Query OK, 1 row affected (0.00 sec)
// use cs360
Database changed
Empty set (0.00 sec)
// create students table
Query OK, 0 rows affected (0.01 sec)
// add grade column to table
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
// assign grades by scores
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
// show table with grades
The second method is to run mysql in batch mode using sql script as inputs.
root@wang:~/SQL# mysql -u root -p < do.sql
Enter password:
Database
information_schema
cs3 60
mysql
As can be seen, the outputs in batch mode are much terser than those in interactive or command mode.
Source: Wang K.C. (2018), Systems Programming in Unix/Linux, Springer; 1st ed. 2018 edition.