Basic MySql Tutorials

Introduction

MySQL is an open source database management software that helps users store, organize, and retrieve data. MySQL is one of the best RDBMS being used for developing web-based software applications.

It is a very powerful program with a lot of flexibility—this tutorial will provide the simplest introduction to MySQL.

Prerequisites

This tutorial assumes that you have basic knowledge of what a database is, the purpose and uses of a database and how it works.

In another guide[1], we showed you how to install a MySql server in CentOS 7 and we will execute some basic mysql commands to help you out.

Accessing the MySql shell

To access mysql shell we need to execute the command.

mysql -u root -p

Enter your password to continue login to the shell.
If you have a username other than administrator you can also use that with the command.

mysql -u myuser -p

Which will require the password of the user 'myuser'.

Creating & Deleting a Database

MySql organizes files into database and each database may have 0 or more tables which in turn have data stored in it. To delete a database, first we have to see which database we can delete. We execute the command.

SHOW DATABASES;

And it will show you a list of databases you have created.

 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample             |
| performance_schema |
| clients            |
+--------------------+
4 rows in set (0.02 sec)

To delete the 'clients' database we run the command.

DROP DATABASE clients;

To create a new database we execute:

CREATE DATABASE new_clients;

Using a Database

Suppose we have a list of available database and we need to modify one of them. We need to enable use of a database before we can edit or delete some files in it. To use a database we run.

USE new_clients;    

In the same way you are checking the list of database you can also see a list of tables.

SHOW TABLES;

Will result in:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_new_clients |
+-----------------------+
| credentials           |
+-----------------------+
1 row in set (0.01 sec)

Adding a Table

To create a mysql table we have to define the table name, the purpose of the table and its format. To create a table we execute.

CREATE TABLE visits (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(20),
branch VARCHAR(30),
confirmed CHAR(1), 
signup_date DATE);

Where the corresponding result of using 'SHOW TABLES' would be:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_new_clients |
+-----------------------+
| credentials           |
| visits                |
+-----------------------+
1 row in set (0.01 sec)

We can also describe what a table has.

 mysql>DESCRIBE visits;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | YES  |     | NULL    |                |
| branch      | varchar(30) | YES  |     | NULL    |                |
| confirmed   | char(1)     | YES  |     | NULL    |                |
| signup_date | date        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

Insert Values into MySql Table

Now that we have a working table called 'visits' let us add values using the 'INSERT' command.

INSERT INTO `visits` (`id`,`name`,`branch`,`confirmed`,`signup_date`) VALUES (NULL, "John", "SM Mall","Y", '2016-07-11');

To add multiple values in one sql command you will write.

INSERT INTO `visits` (`id`,`name`,`branch`,`confirmed`,`signup_date`) VALUES (1, "Daniel", "SM Mall","Y", '2016-07-11'), (2, "Mark", "Robinsons Mall","N", '2016-08-20');

To take a look at our table we execute.

mysql> SELECT * FROM visits;
+----+---------+----------------+-----------+-------------+
| id | name    | branch         | confirmed | signup_date |
+----+---------+----------------+-----------+-------------+
|  1 | Daniel  | SM Mall        | Y         | 2016-07-11  |
|  2 | Mark    | Robinsons Mall | N         | 2016-08-20  |
+----+---------+----------------+-----------+-------------+
2 rows in set (0.01 sec)

Update/Add Values into MySql Table

To update or change 1 or more values from a table we will run the 'UPDATE' command. From the existing 'visits' table that we have we will change the name in the two rows. To do that we run.

UPDATE visits set name = 'Divine' WHERE name = 'Daniel';

UPDATE visits set name = 'Jack' WHERE name = 'Mark';

Then we will take a look at the result.

mysql> SELECT * FROM visits;
+----+---------+----------------+-----------+-------------+
| id | name    | branch         | confirmed | signup_date |
+----+---------+----------------+-----------+-------------+
|  1 | Divine  | SM Mall        | Y         | 2016-07-11  |
|  2 | Jack    | Robinsons Mall | N         | 2016-08-20  |
+----+---------+----------------+-----------+-------------+
2 rows in set (0.01 sec)

Delete a row from the table

To delete a row we run the command 'DELETE' then set the condition that will run the delete command itself which can be a value on the table itself. To delete the second row we will run.

DELETE from visits  WHERE id = 2;

Then we will take a look at the result.

mysql> SELECT * FROM visits;
+----+---------+----------------+-----------+-------------+
| id | name    | branch         | confirmed | signup_date |
+----+---------+----------------+-----------+-------------+
|  1 | Divine  | SM Mall        | Y         | 2016-07-11  |
+----+---------+----------------+-----------+-------------+
1 row in set (0.01 sec)

Add/Delete a Column from the Table

To add or delete a column from the table we will use the 'ALTER' command. For the current table that we have we are missing one information which is the 'amount' to be paid.

We can easily add columns by the command.

ALTER TABLE visits ADD amount int(10);

The command above adds another column at the end with a name of 'amount' with a integer value of up to 10 only.

To place a column in a specific spot on the table we will have to run.

ALTER TABLE visits ADD amount int(10)AFTER confirmed;

To delete this column we will run.

ALTER TABLE visits DROP amount;

Adding user to MySql

By adding a user we add authentication credentials to another person or application, this credentials range from updating, deleting to overall control of the mysql database. To add a user we run the 'CREATE USER' command. To do that we run:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

At this point the new user has mo set permissions and cannot do anything unless we grant it access to something. To add permissions we do:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisk (*) refers to all the database and tables within the current mysql server system. To give permission to the userfor a single database we run.

GRANT ALL PRIVILEGES ON thid_database. * TO 'newuser'@'localhost';

the command above grants ALL permissions to newuser for all the tables in the database 'this_database'.

Then to finalize the permissions be sure to flush privileges.

FLUSH PRIVILEGES;

How to grant/remove specific permissions?

Here is a short list of other possible permissions that users can have.

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use the Select command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this template:

GRANT [type_of_permission] ON [database_name].[table_name] TO ‘[username]’@'[ip_address]’;

If you want to give access to any database or table just put an asterisk (*) in the database_name and table_name.

if you want to remove the permission you can always run.

REVOKE [type_of_permission] ON [database_name].[table_name] TO ‘[username]’@'[ip_address]’;

As always, make sure to 'FLUSH PRIVILEGES' for the new permission to take effect.