MySQL Database Tutorial for Beginners

MySQL is an open-source relational database management system for storing, managing, and accessing the data records.

Relational Database

A relational database is a type of database where data records with relationships are organized to store in tables made up of rows and columns. Each table represents a specific object in the database.

Following are the requirements which should be available in our system to work with MySQL:

• MySQL Server
• MySQL Workbench
• RAM 4 GB (6 GB recommended)

We can connect to MySQL and run queries mainly by using MySQL Command Line Client or MySQL Workbench. In this tutorial, we will be using MySQL Workbench. Workbench is a visual tool that makes working with MySQL database easy.

Creating Database in MySQL

A database is a collection of tables with rows and columns where data are stored in an organized form that can be easily retrieved, managed, and updated.

Syntax to create a new database in MySQL:

CREATE DATABASE database_name;

To create a new database in MySQL using MySQL Workbench, follow these steps:

1. Open the SQL editor in MySQL Workbench.
2. Type the following command:

CREATE DATABASE school_management_system;

Here in the above command, school_management_system is the database name.

3. Execute the query by clicking the third button at the top. You should see a green tick on the output section, when the database is created successfully. Example shown in the image below:

Creating Tables in MySQL

A database table is a collection of related data organized in a table format of rows and columns. Each column in a table must have a name, data type, size (size defines the maximum length of data) and constraints are optional rules that you can apply to each column, such as NOT NULL, UNIQUE, DEFAULT, etc.

Syntax to create a table in MySQL:

CREATE TABLE table_name(column1_name datatype size constraint, column2_name datatype size constraint);

USE school_management_system;

CREATE TABLE students(id int auto_increment primary key, first_name varchar(200), last_name varchar(200), email varchar(250), contact_number text, registration_code int, is_deleted bool, creation_datetime timestamp default current_timestamp, modification_datetime datetime on update current_timestamp);

Tables are created inside a database, so we should first select a database to create a table in it. In the above command, USE database_name query, tells the MySQL to use the specified database for executing query. The next CREATE TABLE command will create a new table called students with id, first_name, last_name, email, contact_number, creation_datetime, and modification_datetime columns in school_management_system database:

Insert Data in a MySQL Table

The INSERT intO statement can be used to add data into a table.

Syntax to insert data into a table:

INSERT intO table_name (column1, column2, ..) VALUES (value1, value2, ..); 

INSERT intO students(first_name, last_name, email, contact_number, registration_code, is_deleted) VALUES('Jake', 'Wilson', '[email protected]', '+55512345678910', 502201, false);

In the above command, id, creation_datetime, and modification_datetime columns of the table are populated automatically. Therefore, we do not need to include them in the query.

Query for Data in a MySQL Table

The SELECT statement can be used to select and return data from one of more tables in MySQL.

Syntax to insert data into a table:

SELECT * FROM table_name;

Here, * (asterisk) means to select and return values of all columns of a table. We can also specify column names in a query explicitly. For example, if we want the query to return only some columns from the table, then we can write query like this:

SELECT column1_name, column4_name FROM table_name;

For example, if we want a query to return first_name and email of students from the above students table, then the query should be something like this:

SELECT first_name, email FROM students;

The output of the above query will look like something shown in the table below:

Update Data in a MySQL Table

The UPDATE statement is used to modify data in a table:

Syntax to update data in a table:

UPDATE table_name SET column1_name = value, column2_name = value WHERE conditions;

Query to modify the value of first_name and last_name of a student whose id is 4:

UPDATE students SET first_name = 'John', last_name = 'Lee' WHERE id = 4;

Query to modify the value of first_name and last_name of a student whose id is 4 and email is [email protected]:

UPDATE students SET first_name = 'John', last_name = 'Lee' WHERE id = 4 AND email = '[email protected]';

Query to modify the value of first_name and last_name of a student whose id is 4 or email is [email protected]:

UPDATE students SET first_name = 'John', last_name = 'Lee' WHERE id = 4 OR email = '[email protected]';

Delete Data From a MySQL Table

The DELETE statement is used to delete data from a table:

Syntax to insert data into a table:

DELETE FROM table_name where conditions;

Query to delete a student data whose id is 2:

DELETE FROM students where id = 2;

Add New Column to an Existing Table in MySQL

Syntax to add a new column in a table:

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE students ADD middle_name varchar(200);

Modify Datatype of an Existing Column in MySQL

Syntax to modify datatype of an existing column in a table:

ALTER TABLE table_name COLUMN column_name datatype;

ALTER TABLE students COLUMN middle_name nvarchar(300);

Delete a Table in MySQL

The DROP statement is used to delete a table in MySQL:

Syntax to delete a table:

DROP TABLE table_name;

DROP TABLE students;

Delete a Database in MySQL

The DROP statement is used to delete a database in MySQL:

Syntax to delete a database:

DROP DATABASE database_name;

DROP DATABASE school_management_system;

Show Created Databases

To list all existing databases, we can execute the following query:

SHOW DATABASES;