Practical 11: Understanding MySQL Commands
We will go through a small series of exercises to create tables in a MySQL/MariaDB database. For the purpose of this exercise, we will carry out everything in phpMyAdmin, but the SQL scripts can be run on a typical MySQL console.
To access phpMyAdmin, type in
localhost/phpmyadmin in your web browser's address bar.
For those who installed each component of the LAMP stack individually including the MySQL database, ensure you remember your root user password.
Starting Out in phpMyAdmin
On the top menu bar, select "SQL". You should see an interface like as follows:
This will be where we will be typing in our SQL scripts for today's practical exercise.
Creating a New Database
The basic syntax to create a new database is as follows:
Let's create a database named
We will create the database by entering the following script:
Note that throughout my demo, I will be using backticks (`) to surround any names given to the database, tables and columns. This is completely optional.
Upon running this command, you can only run it once as you can only create a database with a stated name if it already does not exist.
IF NOT EXISTS clause will make it such that the statement can be run multiple times instead of spititng out an error each time, but it also does nothing as a result.
Select the "Go" button to execute the SQL statement, and a message from the database server should be displayed like as shown below:
By now, the database should now be available to use - the database name should appear on the left hand side of the window.
The opposite act of creating a database is referred as dropping a database. If you wish to drop the database, you have to use the following command script:
IF EXISTS clause works the same way as how the
IF NOT EXISTS clause works for the
CREATE TABLE statement - you can only drop a database if it actually exists in the database server.
The general syntax for dropping a database is as follows:
Create a Table
Right now, the database does not have any tables inside of it.
Let's create a new table called
Student that has the following columns:
- id: INT, AUTO_INCREMENT, NOT NULL, Primary Key
- first_name: VARCHAR(50), NOT NULL
- last_name: VARCHAR(50), NOT NULL
- age: INT(3), NOT NULL
- email: VARCHAR(255)
- active: BOOL, NOT NULL, DEFAULT is TRUE(1)
INT: integer value (default size 7);
VARCHAR(50): max 50 alphanumeric characters,
BOOLEAN: boolean value
- We set
PRIMARY KEYof the table - this is a unique column which distinguishes each record/row from each other.
AUTO_INCREMENTis often used with primary key columns so that they need not be defined when entering records.
NOT NULLconstraint makes a column unable to be empty in any record.
The script to create this table is as follows.
You can view the database table contents by selecting the table name from the left-hand side menu in the browser window.
Multiple Tables in Databases
Yes, you are allowed to have multiple tables in your database (unless it's a SQLite database). In fact, there are mechanisms to simplify tables so that they don't require so many columns (i.e., normalization) and/or so that tables are linked together with foreign keys. We will not cover them in this module, but feel free to look up on how they work if you are curious!
Just like dropping databases, you can also drop a table if you want to recreate the table from scratch.
Populating the Database Table
Let's populate the table with some data. The following command script adds in one record of data in the database.
After running the given command, you should see the table being populated like as follows:
Take note that with
id being set to
AUTO_INCREMENT, there's no need to specify the
id value when adding new records (it will automatically start from 1 by default).
However, you are still allowed to put in a custom
id value if you so desire.
Also, note that the
active value is set to
TRUE or 1 by default.
In this case, you can shorten your SQL command to be like as follows and it should give the same result:
Populate the table with more records:
Updating Records in a Table
Let's update Mark's record to say that he is now no longer inactive (i.e.,
active = 1).
Mark's record is indicated with
As mentioned prior, we can make utilize this column to uniquely identify one record from the others.
However, this is not restricted to just primary keys or unique columns like
When feasible, one may also utilize other columns that are not unique to easily modify multiple records at once.
Also, you can also have multiple conditions in your SQL script to further specify those columns to consider as well.
The table contents should now look like the following:
Run SQL commands to make the following changes:
Adam has recently changed his e-mail to firstname.lastname@example.org.
Tyson is no longer an active student.
Karen's age is to be updated to 20 instead of 19.
Selecting Records from a Table
By now, you should see the given SQL command script appear upon selecting the table in phpMyAdmin:
* is representative of "ALL".
The above syntax simply means to select all available records in
You can specify which records/rows to select by adding conditions.
The following example will select the row(s) where the
id value is equal to 2:
You can also make comparison conditions too when selecting columns.
The following example will select only the first and last names of the row(s) where the
age is greater than 21:
Student table with 4 more records.
- Select all students whose age is not greater than 20.
- Select all active students, displaying only the
Deleting Records from a Table
Deleting records works the same as updating and selecting records - you can utilize IDs as indicators of which record(s) to delete. For example, if we want to delete Tyson's record, we can use the following command script:
The same rules for selecting which type of records based on what column(s) to update also apply to what column(s) to delete.
Now that we've deleted Tyson's record, if you are to add another record after, the
id will not reassume as 4, but will continue after the number of the last record instead.
id values that were used and deleted will not be used under
AUTO_INCREMENT, but can be added manually if not occupied.