Live Chat 
(+972) 079-5599-110
About MySQL
MySQL is an open source database management software which is used for helping users store, retrieve, and organize data. This program is powerful and has a lot of flexibility.
In this tutorial we will teach you the simplest introduction to MySQL, including how you can install it on both Ubuntu and CentOS.
How to Install MySQL on Ubuntu and CentOS
In case you have yet to install MySQL on your VPS, here is how you can download it quickly.
Centos:
sudo yum install mysql-server /etc/init.d/mysqld start
Ubuntu:
sudo apt-get install mysql-server
How to Access the MySQL shell
After you have the MySQL installed on your VPS, you may access the MySQL shell by executing the command below into the terminal.
mysql -u root -p
Once you have entered the MySQL root password into the prompt, don’t be confused with the root VPS password, you’ll be able to begin building your MySQL database.
There are two things to keep in mind:
- Every MySQL command has to end with a semicolon; if it doesn’t have a semicolon at the end, the command will not execute.
- Even though it isn’t needed, generally MySQL commands will be typed in uppercase and databases, usernames, or text are typed in lowercase, so it is simpler to distinguish. However, the MySQL command line is not case sensitive.
How to Create and Delete a MySQL Database
MySQL manages its information inside databases; each one can hold tables with specific data.
You can quickly check which database is available by using the following:
SHOW DATABASES;
A similar output should appear like shown below.
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec)
Creating a database is simple.
CREATE DATABASE database name;
For this tutorial we’ll be calling our database ‘event’.
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | event | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
In MySQL, generally the phrase used to delete objects is ‘Drop’. You would delete a MySQL database using the command given here.
DROP DATABASE database name;
How to Access a MySQL Database
After you have a new database, start filling it with information.
The first thing to do is to create a new table inside the larger database.
Open up the database you would like to use.
USE events;
In the same way that available databases can be checked, you can also see an overview of the tables which the database has inside.
SHOW tables;
As this is a new database, MySQL will have nothing to present to you and you will get a message which says, ‘Empty set’.
How to Create a MySQL Table
Let’s pretend that we’re going to get together a couple friends, we could use MySQL to track the details of the event.
Let’s make a new MySQL table.
CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), food VARCHAR(30), confirmed CHAR(1), signup_date DATE);
This command accomplishes a few things:
- We’ve created a table named ‘potluck’ inside the directory, events.
- We’ve set up 5 columns in the table—id, name, food, confirmed, and signup date.
- The ‘id’ column has a command ‘INT NOT NULL PRIMARY KEY AUTO_INCREMENT’ which automatically numbers each row.
- The ‘name’ column has been limited by the ‘VARCHAR’ command to be under 20 characters long.
- The ‘food’ column designates the food each person will bring. The ‘VARCHAR’ limits text to be under 30 characters.
- The ‘confirmed’ column records whether the person has RSVP’d with one letter: Y or N.
- The ‘date’ column will show once the people are signed up for the event. MySQL writes it as ‘yyyy-mm-dd’.
Let’s see how the table shows up from inside the database with the ‘SHOW TABLES;’ command.
mysql> SHOW TABLES; +------------------+ | Tables_in_events | +------------------+ | potluck | +------------------+ 1 row in set (0.01 sec)
We can remind ourselves about the table’s organization using this command.
DESCRIBE potluck;
Note that, throughout this, even if the MySQL command line won’t pay attention to cases, the table and database names are case sensitive: potluck is not POTLUCK or Potluck.
mysql>DESCRIBE potluck; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | food | varchar(30) | YES | | NULL | | | confirmed | char(1) | YES | | NULL | | | signup_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
How to Add Information to a MySQL Table
Now we’ve got ourselves a working table for the party. We’ll begin filling in the details.
Use the following format to put information inside each row.
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Mike", "Casserole","Y", '2013-05-4');
After that is in, you’ll see the following words.
Query OK, 1 row affected (0.00 sec)
Let’s append a few more people into the group:
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tony", "Key Lime Tarts","N", '2013-05-7'); INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Sam", "BBQ","Y", '2013-05-8'); INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Parker", "Salad","Y", '2013-05-6');
Now let’s take a peek at our table.
mysql> SELECT * FROM potluck; +----+-------+----------------+-----------+-------------+ | id | name | food | confirmed | signup_date | +----+-------+----------------+-----------+-------------+ | 1 | Mike | Casserole | Y | 2013-05-4 | | 2 | Tony | Key Lime Tarts | N | 2013-05-7 | | 3 | Sam | BBQ | Y | 2013-05-8 | | 4 | Parker | Salad | Y | 2013-05-6 | +----+-------+----------------+-----------+-------------+ 4 rows in set (0.00 sec)
How to Update Information in the Table
Since we’ve got our potluck list started, we can address any possible changes.
As an example: Sam has verified that he’ll be attending, let’s update that in the table.
UPDATE `potluck` SET `confirmed` = 'Y' WHERE `potluck`.`name` ='Sam';
You may use this command to append information into particular cells, even if they are empty.
How to Add and Delete a Column
We are making a useful chart; however, it is still missing some important information: our attendees’ emails.
We could simply add the following.
ALTER TABLE potluck ADD email VARCHAR(40);
This command inserts a new column named ‘email’ at the end of the table by default, and the ‘VARCHAR’ command limits it to 40 characters.
If you have to place that column in a particular spot in the table, we could add one more phrase to the command.
ALTER TABLE potluck ADD email VARCHAR(40) AFTER name;
Then the new ‘email’ column will go after the column ‘name’.
In the same way that you could add a column, you can remove one as well.
ALTER TABLE potluck DROP email;
How to Delete a Row
If required, you can also remove rows from the table using the following command.
DELETE from [table name] where [column name]=[field text];
As an example, in the case where Sandy isn’t able to arrive and take part in the potluck after all, we can just eliminate her from the list.
mysql> DELETE from potluck where name='Sandy'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM potluck; +----+------+-----------+-----------+-------------+ | id | name | food | confirmed | signup_date | +----+------+-----------+-----------+-------------+ | 1 | John | Casserole | Y | 2012-04-11 | | 3 | Tom | BBQ | Y | 2012-04-18 | | 4 | Tina | Salad | Y | 2012-04-10 | +----+------+-----------+-----------+-------------+ 3 rows in set (0.00 sec)
Notice how the id numbers associated with every person remain unchanged.
Conclusion
This concludes the basic MySQL tutorial, we’ve helped you create yourself a new database, create a new user, and a table etc.