INTRODUCTION

In this article, we are going to learn the basics of MySQL.SQL stands for Structured Query Language. It is used for storing and managing data in a relational database management system (RDMS). SQL is a standard language for Relational Database System. This enables a user to create, read, update, and delete relational databases and tables. All the RDBMS like MySQL, Informix, Oracle, MS Access, and SQL Server use SQL as their standard database language.

What Can SQL do?

SQL can execute queries against a database, retrieve data from a database, insert records in a database, update records in a database, delete records from a database.SQL is also used to create new databases, create new tables in a database, and can set permissions on tables, procedures, and views.

CONTENTS

1. Install MYSQL on windows
2. Install MYSQL on LINUX
3. INSERT INTO, SQL SELECT and SQL SELECT DISTINCT
4. SQL WHERE Clause, AND, OR and NOT, BETWEEN Operators
5. ORDER BY KEYBOARD and LIKE operators
6. CROSS JOIN and INNER JOIN
7. GROUP BY and SQL HAVING CLAUSE
8. UNION OPERATOR UPDATE and DELETE Statement

1.INSTALL MYSQL ON WINDOWS

1. Download the MySQL Installer from dev.mysql.com. The two download options are a web-community version and a full version. The web-community version will only download the server. By default, but you can select other applications (like Workbench) as desired. The full installer will download the server and all the recommended additional applications. (You’ll also be asked to create a user account, but you skip this part by scrolling down to the bottom and clicking “No thanks, just start my download”.)

2. Run the installer that you downloaded from its location on your server, generally by double-clicking.
 
NoteYou can use this same MSI Installer to upgrade currently installed versions of MySQL as well! As is typical, the first step is accepting the license agreement, then click Next.

3. Determine which setup type you would like to use for the installation:

1. Developer Default: this is the full installation of MySQL Server and the other tools needed for development. If you are building your database from the ground up or will be managing the data directly in the database, you’ll want to use this setup type.
2. Server Only: if you only need MySQL Server installed for use with a CMS or other application and will not be managing the database directly. You can install just the server (you can always install additional tools later).
3. Custom: this setup type will allow you to customize every part of the installation from the server version to whichever additional tools you select.

4. Install the server instance and whichever additional products you selected. Then begin the configuration process by selecting the availability level (most users will use the default, standalone version).

5. Complete the configuration process by following the on-screen instructions. You’ll want to make sure to install MySQL as a Service so that Windows can automatically start the service after a reboot or can restart the service if it fails.

2.INSTALL MYSQL ON LINUX

1. Download the latest stable relase of MySQL

Download mySQL from mysql.com .  Please download the community edition of MySQL for your appropriate Linux platform. I downloaded the “Red Hat Enterprise Linux 5 RPM (x86)”. Make sure to download MySQL Server, Client and “Headers and libraries” from the download page.

• MySQL-client-community-5.1.25-0.rhel5.i386.rpm.
• MySQL-server-community-5.1.25-0.rhel5.i386.rpm.
• MySQL-devel-community-5.1.25-0.rhel5.i386.rpm .

2. Remove the existing default MySQL that came with the Linux distro

Do not perform this on an system where the MySQL database is getting used by some application.

3. Install the downloaded MySQL package

Install the MySQL Server and Client packages as shown below.

This will also display the following output and start the MySQL daemon automatically.

Install the “Header and Libraries” that are part of the MySQL-devel packages.

Note: When I was compiling PHP with MySQL option from source on the Linux system, it failed with the following error. Installing the MySQL-devel-community package fixed this problem in installing PHP from source.

4.  Perform post-install security activities on MySQL.

At a bare minimum you should set a password for the root user as shown below:

The best option is to run the mysql_secure_installation script that will take care of all the typical security related items on the MySQL as shown below. On a high level this does the following items:

• Change the root password.
• Remove the anonymous user.
• Disallow root login from remote machines.
• Remove the default sample test database.

5.  Verify the MySQL installation:

You can check the MySQL installed version by performing mysql -V as shown below:

Connect to the MySQL database using the root user and make sure the connection is successfull.

Follows the steps below to stop and start MySQL

3.INSERT INTO,SQL SELECT AND SQL SELECT DISTINCT

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. “Faculty” or “student”). Tables contain records (rows) with data. In this tutorial we will use the faculty, student, and department tables.

The following SQL statement selects all the columns from the ” Faculty” table:

 select * from Faculty;

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways. The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

We are creating 3 tables called Faculty, student and department.

create database college;
use college
create table Faculty(Fid int,Fname Varchar(100),Qualification varchar(50),Deptid int);
insert  into Faculty values(1,'Aman','Btech',1);
 insert  into Faculty values(2,'Mohan','Btech',1);
insert  into Faculty values(3,'ramnath','Mtech',1);
insert  into Faculty values(4,'pri','phd',1);
insert  into Faculty values(5,'suja','phd',2);
select * from Faculty;

 create table student(Sid int,Sname varchar(100),branch varchar(50),mark int);
insert into student values(1,'ram','IT','75');
insert into student values(2,'Mahesh','IT','90');
insert into student values(3,'sagar','CSE',85);
insert into student values(4,'Arun','ECE',85);
insert into student values(5,'Anand','ECE',35);
 select * from student ;

create table Department(deptid int,dname varchar(100));
insert into Department values(1,'IT');
insert into Department values(2,'CSE');
insert into Department values(3,'ECE');
select * from Department;

The SQL SELECT Statement

The SELECT statement is used to select data from a database and the data returned is stored in a result table, called the result-set. The select syntax is as shown below.

SELECT column1, column2, …
FROM table_name;

select fname from faculty;

select fid,fname from faculty;

The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. The SELECT DISTINCT Syntax is as shown below.

SELECT DISTINCT column1, column2, …
FROM table_name;

select distinct branch from student;

4.The SQL WHERE Clause,AND, OR AND NOT,BETWEEN Operators

The WHERE clause is used to filter records and the WHERE clause is used to extract only those records that fulfill a specified condition. WHERE Syntax is as given below.

SELECT column1, column2, …
FROM table_name
WHERE condition;

select fid,fname,fname from faculty where qualification="Btech";

The SQL AND, OR and NOT Operators

WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition:

• AND operator displays a record if all the conditions separated by AND are TRUE.
• OR operator displays a record if any of the conditions separated by OR is TRUE.
• NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;

OR Syntax

SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;

NOT Syntax

SELECT column1, column2, …
FROM table_name
WHERE NOT condition;

The SQL BETWEEN Operator

The BETWEEN operator selects values within a given range and the values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. BETWEEN Syntax is shown below.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

 select sname from student where branch='IT' or branch="ECE";

select * from student where branch NOT IN('IT','CSE');

select * from student where mark BETWEEN 60 and 90;

LIMIT Clause

In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count.The value of both the parameters can be zero or positive integers. The syntax is given below.

SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;

Offset: It is used to specify the offset of the first row to be returned.
Count: It is used to specify the maximum number of rows to be returned.

 select * from faculty LIMIT 4;

select * from faculty LIMIT 1,4;

5.ORDER BY KEYBOARD AND LIKE OPERATOR

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order . ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order. Use the DESC keyword. ORDER BY Syntax is given below.

SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

select * from student order by mark desc;

select * from student order by field (branch,'IT','CSE','ECE');

SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.There are two wildcards often used in conjunction with the LIKE operator:

• % – The percent sign represents zero, one, or multiple characters.
• _ – The underscore represents a single character.

The percent sign and the underscore can also be used in combinations.The SELECT Syntax is given below.

SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;

select * from student where Sname like 'A%';

select * from student where Sname like '%d';

select Sname,mark from student where sname like '_a%';

6.CROSS JOIN AND INNER JOIN

CROSS JOIN

MySQL, the CROSS JOIN produced a result set which is the product of rows of two associated tables when no WHERE clause is used with CROSS JOIN. In this join, the result set appeared by multiplying each row of the first table with all rows in the second table if no condition introduced with CROSS JOIN.

select * from faculty cross join department;

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables. The INNER JOIN Syntax is shown below.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

select f.fname,d.dname from Faculty1 f INNER JOIN Department d ON f.deptid=d.deptid;

SQL COUNT(), AVG() and SUM() Functions.COUNT() function returns the number of rows that match a specified criteria. AVG() function returns the average value of a numeric column. SUM() function returns the total sum of a numeric column.

COUNT() Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM() Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

7.GROUP BY and SQL HAVING CLAUSE

The SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows. Like “find the number of customers in each country” .The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. The GROUP BY Syntax is shown below

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

select branch,count(*) from student GROUP BY branch;

select branch,sum(mark) from student GROUP BY branch;

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. The HAVING clause Syntax is shown below.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

 select branch,sum(mark) from student GROUP BY branch having branch IN('CSE','IT');

select fname from faculty1 where deptid IN(select deptid from department where dname=’IT’);

8..UNION OPERATOR UPDATE AND DELETE STATEMENT

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within UNION must have the same number of columns.
• The columns must also have similar data types.
• The columns in each SELECT statement must also be in the same order.

UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

select fname from faculty1 UNION select sname from student ;

UPDATE STATEMENT

The UPDATE statement is used to modify the existing records in a table.The syntax is given below.

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

 insert into department values
     (6,'A'),(7.’b’)’;

create table dept_temp(depid int,deptname varchar(20));
insert into dept_temp
    -> select deptid,dname from department;

 update dept_temp
    -> set deptname='Applied sc.'
    -> where depid=7;
select * from dept_temp;

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.The DELETE Syntax is given below.

DELETE FROM table_name WHERE condition;

delete from dept_temp
    -> where depid=2;
select * from dept_temp;

delete from dept_temp
    -> limit 2;
select * from dept_temp;

delete from dept_temp;
select * from dept_temp;

After reading this tutorial on “MYSQL”. I hope you understood the basics of MYSQL and I am pretty sure you want to know more about MYSQL and IoT. To know more about IoT you can refer to the following blogs.

• https://iot4beginners.com/iot-security-factors/
• https://iot4beginners.com/iot-gateway-a-beginners-guide/
• https://iot4beginners.com/iot-applications-in-transportation/