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.
