Steps To Install Mysql on Ubuntu and Create Delete Update Database using mysql shell

MySQL is a relational database management system which runs as a server and allows multiple users to manage and create numerous databases.It is used for a wide range of purposes, including web applications, data warehousing, e-commerce, and logging applications. The most common use for mySQL is web database.

Steps to install Mysql on Ubuntu

sudo su
--[enter your root password]
sudo apt-get update
sudo apt-get install mysql-server

You will be prompted for root password during the installation. Choose a secure password & complete in installation.

Type following command to check installation

mysqladmin -p -u root version

You will prompted for password, Enter root password to continue, then you will get following output. It means your installation is successful.

mysqladmin  Ver 8.42 Distrib 5.7.19, for Linux on i686
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.7.19-0ubuntu0.17.04.1
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			40 min 36 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 108  Flush tables: 1  Open tables: 27  Queries per second avg: 0.004

To Access the MySQL shell

mysql -u root -p

Enter root MySQL password into the prompt. Now you will be able to start building your MySQL database. All MySQL commands end with a semicolon.

Now we can perform all DDL (Data Definition Language) & DML (Data Manipulation Language) operations through Terminal window. Here i assume that you are familiar with basic SQL statements, so I demonstrated basic SQL commands.

To Create Database

Syntax
CREATE DATABASE database name;

Example
CREATE DATABASE COLLEGE;

You can show databases by following command:

Syntax 
SHOW DATABASES;

Example
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| COLLEGE |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

To Access a MySQL Database

Syntax
USE Database;

Example
USE COLLEGE;

Here COLLEGE Database is selected. so we can create use this database to create tables.

To Create a MySQL Table
Here as example create table student having fields ROLL_NUMBER, STUDENT_NAME, COLLEGE_NAME, BRANCH, ADMISSION DATE with proper data type.

Syntax
CREATE TABLE [TABLE NAME] (

FIELD NAME DATA TYPE,
...,
...

);

Example

CREATE TABLE STUDENT (
ROLL_NUMBER INT NOT NULL PRIMARY KEY, 
STUDENT_NAME VARCHAR(20),
COLLEGE_NAME VARCHAR(30),
BRANCH VARCHAR(10), 
ADM_DATE DATE
);

To Verify tables in Database

Syntax
SHOW TABLES;

Example
mysql> SHOW TABLES;
+-------------------+
| Tables_in_COLLEGE |
+-------------------+
| STUDENT           |
+-------------------+
1 row in set (0.00 sec)

To check properties of tables columns

Syntax
DESC STUDENT;

Example
mysql> DESC STUDENT;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ROLL_NUMBER  | int(11)     | NO   | PRI | NULL    |       |
| STUDENT_NAME | varchar(20) | YES  |     | NULL    |       |
| COLLEGE_NAME | varchar(30) | YES  |     | NULL    |       |
| BRANCH       | varchar(10) | YES  |     | NULL    |       |
| ADM_DATE     | date        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

To Insert Records in the Table

INSERT INTO `STUDENT` (`ROLL_NUMBER`,`STUDENT_NAME`,`COLLEGE_NAME`,
`BRANCH`,`ADM_DATE`) VALUES(111,"ARVIND","GLOBAL","COMPUTER",'2017-09-23');

Please write special character correctly before execution.Here columns name is encoded in “ and values of varchar type is encoded in “”(double quotes).

Once Query get executed correctly, you get message as

Query OK, 1 row affected (0.04 sec)

Similarly add more records as

INSERT INTO `STUDENT` (`ROLL_NUMBER`,`STUDENT_NAME`,`COLLEGE_NAME`,
`BRANCH`,`ADM_DATE`) VALUES(222,"TOM","GLOBAL","IT",'2017-09-23');

INSERT INTO `STUDENT` (`ROLL_NUMBER`,`STUDENT_NAME`,`COLLEGE_NAME`,
`BRANCH`,`ADM_DATE`) VALUES(333,"KESHAV","GLOBAL","CHEM",'2017-09-23');

INSERT INTO `STUDENT` (`ROLL_NUMBER`,`STUDENT_NAME`,`COLLEGE_NAME`,
`BRANCH`,`ADM_DATE`) VALUES(444,"RAM","GLOBAL","COMPUTER",'2017-09-23');

INSERT INTO `STUDENT` (`ROLL_NUMBER`,`STUDENT_NAME`,`COLLEGE_NAME`,
`BRANCH`,`ADM_DATE`) VALUES(555,"NARAYAN","GLOBAL","COMPUTER",'2017-09-23');

To check inserted records or records from table

SELECT * FROM `STUDENT`;
mysql> SELECT * FROM STUDENT;
+-------------+--------------+--------------+----------+------------+
| ROLL_NUMBER | STUDENT_NAME | COLLEGE_NAME | BRANCH   | ADM_DATE   |
+-------------+--------------+--------------+----------+------------+
|         111 | ARVIND       | GLOBAL       | COMPUTER | 2017-09-23 |
|         222 | TOM          | GLOBAL       | IT       | 2017-09-23 |
|         333 | KESHAV       | GLOBAL       | CHEM     | 2017-09-23 |
|         444 | RAM          | GLOBAL       | COMPUTER | 2017-09-23 |
|         555 | NARAYAN      | GLOBAL       | COMPUTER | 2017-09-23 |
+-------------+--------------+--------------+----------+------------+
5 rows in set (0.00 sec)

To Delete a Records

Here we are deleting a records whose name is ‘TOM’. As we know the syntax of delete SQL statement as follows.

Syntax
DELETE from [Table Name] where Criteria or Condition;

Example of deleting a records is given below.

mysql> DELETE from STUDENT where STUDENT_NAME='TOM';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM STUDENT;
+-------------+--------------+--------------+----------+------------+
| ROLL_NUMBER | STUDENT_NAME | COLLEGE_NAME | BRANCH   | ADM_DATE   |
+-------------+--------------+--------------+----------+------------+
|         111 | ARVIND       | GLOBAL       | COMPUTER | 2017-09-23 |
|         333 | KESHAV       | GLOBAL       | CHEM     | 2017-09-23 |
|         444 | RAM          | GLOBAL       | COMPUTER | 2017-09-23 |
|         555 | NARAYAN      | GLOBAL       | COMPUTER | 2017-09-23 |
+-------------+--------------+--------------+----------+------------+
4 rows in set (0.00 sec)

To Update a Record

Here we are modifying the student name whose roll number is 444. so execute sql statement update. Syntax of update SQL statement as follows.

Syntax

UPDATE [`TABLE NAME`]
SET
FIELD NAME1 = VALUE1 ,
FIELD NAME2 = VALUE2 ,
FIELD NAMEn = VALUEn ,
WHERE
CRITERIA OR CONDITION;

Example of updating record in STUDENT table as follows.

mysql> UPDATE `STUDENT`
    -> SET
    -> `STUDENT_NAME`='SACHIN'
    -> WHERE `ROLL_NUMBER`=444;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM STUDENT;
+-------------+--------------+--------------+----------+------------+
| ROLL_NUMBER | STUDENT_NAME | COLLEGE_NAME | BRANCH   | ADM_DATE   |
+-------------+--------------+--------------+----------+------------+
|         111 | ARVIND       | GLOBAL       | COMPUTER | 2017-09-23 |
|         333 | KESHAV       | GLOBAL       | CHEM     | 2017-09-23 |
|         444 | SACHIN       | GLOBAL       | COMPUTER | 2017-09-23 |
|         555 | NARAYAN      | GLOBAL       | COMPUTER | 2017-09-23 |
+-------------+--------------+--------------+----------+------------+
4 rows in set (0.00 sec)

Thanks !
In Next tutorial we will see how to use mysql database to develop web application using Java Servlet.

One Comment

Leave a Reply