MySQL Tutorials

Posted on by nhv91

MySQL is the world’s most popular open-source database management system and it is very popular with developers in the application development process. Learning MySQL by yourself is not difficult, so in this tutorial we will provide you with the basic knowledge you need to know when learning MySQL.

What is MySQL

MySQL is known as a type of high-speed open-source database management system, operated under the client-server model. MySQL is also known as RDBMS (Relational Database Management System). This software is used in creating and managing databases based on managing all the relationships between them.

How MySQL works

In the MySQL environment, the client and server interact with each other according to the following principles:

  • MySQL creates tables to store data and defines the relationships between those tables.
  • The client sends SQL requests with special commands to MySQL.
  • The application on the server receives and responds to information, returning the results to the client.

Create Database in MySQL

To create a database, you use the CREATE command in MySQL. Here is the syntax of the CREATE command:

CREATE DATABASE databasename;

The following simple example will create the database:

CREATE DATABASE learnshareit;

This command will create a database named learnshareit in MySQL Database.

Dropping Databases in MySQL

To drop any databases that you don’t need, you use the DROP DATABASE command in MySQL. Be careful while removing any database because you will lose all the data contained in that database.

The syntax of the DROP DATABASE command in MySQL is:

DROP DATABASE databasename;

Here is an example to drop a database created in the previous content:

DROP DATABASE learnshareit;

Select Database in MySQL

Selecting a specific database in MySQL is very simple. You can use the use command in SQL to select a database. The syntax of the use command is:

USE databasename;

The following example selects a database named learnshareit:

USE learnshareit;

You have now selected the learnshareit database and all subsequent operations will be performed on this database.

NOTE: All database names, table names, and table field names are case sensitive. Therefore, you must use the correct name while providing any SQL commands.

Create table in MySQL

The command to create a data table in MySQL needs to include:

  • Table name
  • Names of field
  • Definition for each field

Syntax 

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Here is an example to create a table named Staff with the fields StaffID, Name, Age, Address in the learnshareit database:

Note: If you have multiple databases, then to create the Staff table included in the learnshareit database you must first select that database with the USE command.

CREATE TABLE Staff (
StaffID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
Age INT NOT NULL,
Address VARCHAR(255)
);

Once created, the staff table will look like this:

StaffID NameAgeAddress

Some concepts need explanation:

  • The NOT NULL property of the field is being used because we don’t want the field to be NULL. Therefore, if the user tries to create a record with a NULL value, MySQL will generate an error.
  • The AUTO_INCREMENT attribute tells MySQL to automatically increment the primary key and add the next available value to the StaffID field.

Drop Table in MySQL

To delete an existing table in MySQL is quite simple, but you need to be very careful while deleting any table, because you will lose all the data stored in that table and cannot be recovered. Okay.

Syntax

DROP TABLE table_name;

For example:

To delete the Staff table created in the previous chapter, you use:

DROP TABLE Staff;

INSERT INTO in MySQL

To insert data into a MySQL table, you will need to use the SQL command INSERT INTO.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)

VALUES(value1, value2, value3, ...);

To insert the string data type, it is imperative that you keep all values in single or double quotes, e.g. “John”.

Below is an example illustrating the use of the INSERT INTO command to insert data into a table. In the previous content, we created the Staff table, now we insert the records as follows:

INSERT INTO Staff (StaffID, Name, Age, Address)
VALUES (12, "John", 22, "HaNoi");
INSERT INTO Staff (StaffID, Name, Age, Address)
VALUES (13, "Jack", 21, "HaNoi");

SELECT in MySQL

To get data from MySQL Database we use SQL SELECT.

Syntax

SELECT column1, column2, ...
FROM table_name;
[ WHERE clause]
[OFFSET M ][LIMIT N]

One or more fields can be obtained in a single SELECT statement.

An asterisk (*) can be specified in place of fields. In this case, the SELECT statement will return all fields.

SELECT * FROM table_name;

Any condition can be specified using the WHERE clause.

An offset can be specified using OFFSET from which this SELECT statement will begin returning records. By default, the offset is 0.

The number of results returned can be limited by using the LIMIT attribute.

WHERE clause in MySQL

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The WHERE clause works the same way as the If clause. Use the WHERE clause when you want to compare two values: given and available in a MySQL table. In case the two values are the same, the result returned is the row of that value.

The WHERE clause can specify any condition. In addition it is also possible to multiple conditions using AND and OR. You can also combine the DELETE command. UPDATE in the WHERE clause.

NULL value in MySQL

In MySQL, the common NULL operator is:

  • IS NULL: if the column value is NULL, this operator will return true.
  • IS NOT NULL: if the column value is not NULL, this operator will return true
  • <=>: Use this operator when comparing values.

Use the IS NULL operator to find columns that are NULL, IS NOT NULL when you want to find columns that are not NULL.

For example:

CREATE TABLE Staff (
     StaffID int NOT NULL,
     Name varchar(255) NOT NULL,
     Age int
);

Error situations occur when working with MySQL:

Summary

In this tutorial, we have given you an overview of MySQL. Hope the article will be useful to you. To learn more about programming languages you can visit our LearnShareIT. Thanks for reading!

Related Posts

MySQL Tutorials

MySQL is the world’s most popular open-source database management system and it is very popular […]

Leave a Reply

Your email address will not be published. Required fields are marked *