Grid Guides

Explore How System On Grid Can Work For You

A Basic MySQL Tutorial


Table of Contents

Introduction

It is an open source relational database management software and it helps to store, organize and retrieve data. It is a handy and flexible programme. This guide provides information about how to create a database, tables and how to manage them and how to delete them.

Prerequisites

A running server

How to Install MySQL on Ubuntu

If you don't have MySQL installed on your orbit, you can download it by using the following command.

$ sudo apt-get install mysql-server

Now MySQL is downloaded on your orbit.

How to Access the MySQL shell

Once you have installed MySQL in your orbit, then you can access MySQL shell by using the following command.

mysql -u root -p

Then you have to enter the root MySQL password into the prompt. Then you can start building the MySQL Database.

Conditions

Every MySQL Command end with a semicolon. Otherwise, it will not execute. MySQL commands are usually written in UpperCase and databases, tables, usernames and text are written in lowercase. But it doesn’t mean MySQL Command line is case sensitive.

How to Create and Delete a MySQL Database

MySQL contains information into databases so that each database can hold data in Tables.

You can check your MySQL to know your databases by using the following command.

SHOW DATABASES;

Then you will get output like this.

showdatabases

To create a database using this following command.

CREATE DATABASE database name;

For example, we will create a database named “test”. By using the following command.

CREATE DATABASE test;

Now the database “test” is created. If you want to check databases, use this following command.

mysql> SHOW DATABASES;

Then you will get the following output.

adddatabasetestoutput

In MySQL, if you want to delete any command you have to use the object “drop”.

If you want to delete any database use this following command.

DROP DATABASE events;

Now the database is deleted.

How to Access a MySQL Database

In the new database, as we have created now, we can insert information. Now the first step is to create a table with an extensive database. Let’s we open the database by using the following command.

USE test;

If you want to see available databases and also see the overview of tables use the following command.

SHOW tables;

How to Create a MySQL Table

If you want to store the details in the database then you have to create a table. Create a new MySQL table using the following command.

CREATE TABLE student(sid int,name varchar(20),major varchar(10),gpa float,tutorid int,PRIMARY KEY (sid));

This command describes the following things. Now created a table called student within the directory, events. We have set up 5 columns in the table. Those are id, name, major, gpa and tutorid. The “sid” column has a command ( PRIMARY KEY). The “name” column has specified as VARCHAR command has a limit 20 characters long.

If you want to see how many tables are available in your database, use the following command.

mysql> SHOW TABLES;

You can see the organization of the table using the following command.

mysql>DESCRIBE student;

Then you will get output like this.

describestudent

How to Add Information to a MySQL Table

Insert the information into the table by using the following command.

INSERT INTO student values(101, ‘Bill’, ‘CIS’,3.45,102);

Once you enter the above command, you will get like this:

Query OK, 1 row affected (0.00 sec)

Now add people to the table that we have created.

INSERT INTO student values(102, ‘Mary’, ‘CIS’,3.1,null);
INSERT INTO student values(103, ‘Sue’, ‘Marketing’,2.95,102);
INSERT INTO student values(104, ‘Tom’, ‘Finance’,3.5,106);

Now we can see our table by using below command.

mysql> SELECT * FROM student;

Then the output looks like this.

studenttable

How to Update Information in the Table

Now you can update any information into the table. For example, a person “Mary” is changing her major stream by using below command.

UPDATE `student` 
SET 
`major` = 'Finance' 
WHERE `student`.`name` ='Mary';

Also, you can use this command to enter information into specific fields.

How to Add and Delete a Table

If you want to add any missing information use the below command. For example, our attendee's information then, use below command.

ALTER TABLE student ADD email VARCHAR(40);

This command will add a new column called “email” at the end of the table and also varchar command limits 40 characters. And if you want to add the column at the specific place then use below command.

ALTER TABLE student ADD email VARCHAR(40) AFTER name;

Now the new email column goes after column “name”.

You can delete the column by using below command.

ALTER TABLE student DROP email;

Now the column email is deleted.

How to delete a Row

If you want to delete rows from the table use this command.

DELETE from [table name] where [column name]=[field text];

For example, Mary is not suddenly gets detained due to some health issues then you can remove her details by using below command.

mysql> DELETE from student where name='Mary';

After that, you can check it once whether the row is deleted or not, by using the below command.

mysql> SELECT * FROM student;

Then the output must be like this.

updatedstudenttable

But the id numbers assigned to each person remains the same even after deletion of one person.

Conclusion

So far, we have discussed the basic MySQL tutorial briefly.