MySQL Tutorial

[Link]
Introduction :
● My is the daughter’s name of the MySQL’s co-founder, Monty Widenius.
● MySQL is a database management system that allows you to manage
relational databases.
● It is open source software backed by Oracle.
● Even though MySQL is open source software, you can buy a commercial
license version from Oracle to get a premium support services.
● MySQL can run on various platforms UNIX, Linux, Windows, etc. You can
install it in a server or even in a desktop.

[Link]
Installing MySQL
Step1 :
$sudo apt-get update
$sudo apt-get install mysql-server
During the installation process, you will be prompted to set a password for the MySQL root user as shown
below. Choose a strong password and keep it in a safe place for future reference.

[Link]
Interacting with mysql
● The standard tool for interacting with MySQL is the mysql client, which installs with the mysql-server
package.
● The MySQL client is accessed through a terminal.
● Step 1: To log into MySQL as the root user:

[Link]
 MySQL Database
Management System

[Link]
MySQL Create Database :
You can create a MySQL database by using MySQL Command Line Client.

Syntax:
CREATE DATABASE database_name;
Output:

[Link]
You can check the created database by the following query:

SHOW DATABASES;
Output:

[Link]
MySQL SELECT Database :
SELECT Database is used in MySQL to select a particular database to work with.
This query is used when multiple databases are available with MySQL Server.

Syntax:
USE database_name;
Output:

[Link]
MySQL Drop Database :
you can drop/delete/remove a MySQL database easily with the MySQL command.
You should be careful while deleting any database because you will lose your all
the data available in your database.

Syntax:
DROP DATABASE database_name;

Output:

[Link]
Now you can check that either your database is removed by executing the
following query:

SHOW DATABASES;
Output:

[Link]
 Tables

[Link]
MySQL CREATE TABLE
The MySQL CREATE TABLE command is used to create a new table into the
database. A table creation command requires three things:

• Name of the table

• Names of fields
• Definitions for each field

Syntax:

Following is a generic syntax for creating a MySQL table in the database.

CREATE TABLE table_name (column_name column_type...);

[Link]
Example: OUTPUT :

CREATE TABLE emp(

empid INT NOT NULL AUTO_INCREMENT,
empname VARCHAR(100) NOT NULL, salary
city VARCHAR(100) NOT NULL,
salary INT NOT NULL,
PRIMARY KEY ( empid )
);

Note:
• Here, NOT NULL is a field attribute and it is used because we don't want this field to be NULL. If
you will try to create a record with NULL value, then MySQL will raise an error.
• The field attribute AUTO_INCREMENT specifies MySQL to go ahead and add the next available
number to the id field.
• PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated
by comma to define a primary key.

[Link]
See the table structure:

Use the following command to see the table already created:

desc emp;
Output :

[Link]
MySQL INSERT Statement :
MySQL INSERT statement is used to insert data in MySQL table within the
database. We can insert single or multiple records using a single query in MySQL.

Syntax:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );

MySQL INSERT Example : inserting multiple records :

Output :
INSERT INTO emp
(empid,empname,city, salary) salary
VALUES (1,’ JHON', ‘hyd‘,2000),
(2, ‘albert', ‘usa‘,30000),
(3, 'Vinay', ‘hyd‘,28000),
(4, 'Vinay', ‘delhi‘,30000);

[Link]
See the created table:
Use the following command to see the table already created:

SHOW tables;
Output:

[Link]
MySQL UPDATE Query :
MySQL UPDATE statement is used to update data of the MySQL table within the
database. It is used when you need to modify the table.
Syntax:

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

Note:

• One or more field can be updated altogether.

• Any condition can be specified by using WHERE clause.
• You can update values in a single table at a time.
• WHERE clause is used to update selected rows in a table.

[Link]
[Link]
Example:

UPDATE emp
SET empname= 'Ambani'
WHERE empid = 4;

Output :

salary

[Link]
MySQL DELETE Statement :
MySQL DELETE statement is used to delete data from the MySQL table within
the database. By using delete statement, we can delete records on the basis of
conditions.

Syntax:

DELETE FROM table_name

WHERE
(Condition specified);

[Link]
Example:

DELETE FROM emp

WHERE empid = 4;

Output :

salary

[Link]
MySQL SELECT Statement :
The MySQL SELECT statement is used to fetch data from the one or more
tables in MySQL. We can retrieve records of all fields or specified fields.

Syntax for specified fields:

SELECT expressions
FROM tables
[WHERE conditions];

Syntax for all fields:

SELECT * FROM tables [WHERE conditions];

[Link]
Example for specified fields :
SELECT empname, city
FROM emp ;

Output :

[Link]
Example for all fields :

SELECT * FROM emp;

Output :

salary

[Link]
MySQL WHERE Clause :
MySQL WHERE Clause is used with SELECT, INSERT, UPDATE and DELETE clause
to filter the results. It specifies a specific position where you have to do the
operation.

Syntax:

WHERE conditions;

[Link]
MySQL Distinct Clause :
MySQL DISTINCT clause is used to fetch only the unique records from the
table. The DISTINCT clause is only used with the SELECT statement.

Syntax:

SELECT DISTINCT expressions

FROM tables
[WHERE conditions];

[Link]
Parameters
expressions: specify the columns or calculations that you want to retrieve.
tables: specify the name of the tables from where you retrieve records. There
must be at least one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be met for
the records to be selected.

Note:
• If you put only one expression in the DISTINCT clause, the query will return the
unique values for that expression.
• If you put more than one expression in the DISTINCT clause, the query will
retrieve unique combinations for the expressions listed.
• In MySQL, the DISTINCT clause doesn't ignore NULL values. So if you are using
the DISTINCT clause in your SQL statement, your result set will include NULL as
a distinct value.

[Link]
MySQL DISTINCT Clause with single expression :

If you use a single expression then the MySQL DISTINCT clause will return a
single field with unique records (no duplicate record).

SELECT DISTINCT city

FROM emp;

Output :
salary

[Link]
MySQL DISTINCT Clause with multiple expressions :

If you use multiple expressions with DISTINCT Clause then MySQL DISTINCT
clause will remove duplicates from more than one field in your SELECT statement.

SELECT DISTINCT empname, city

FROM emp;

Output :

[Link]
MySQL FROM Clause :
The MySQL FROM Clause is used to select some records from a table.

Note:

• If you are using the FROM clause in a MySQL statement then at least one table
must have been selected.
• If you are using two or more tables in the MySQL FROM clause, these tables are
generally joined using INNER or OUTER joins.

[Link]
MySQL ORDER BY Clause :
The MYSQL ORDER BY Clause is used to sort the records in ascending or
descending order.

Syntax:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

[Link]
MySQL ORDER BY: without using ASC/DESC attribute :
If you use MySQL ORDER BY clause without specifying the ASC and DESC
modifier then by default you will get the result in ascending order.

Example :
SELECT * FROM emp WHERE city = ‘hyd' ORDER BY empname;

Output :

salary

[Link]
MySQL ORDER BY: with DESC attribute

Example :
SELECT * FROM emp WHERE city = ‘hyd‘
ORDER BY empname DESC;

Output :

salary

[Link]
MySQL ORDER BY: using both ASC and DESC attributes :

Example :
SELECT empname, salary
FROM emp
WHERE empid < 5
ORDER BY empname DESC, salary ASC;

Output :

salary

[Link]
MySQL GROUP BY Clause :
The MYSQL GROUP BY Clause is used to collect data from multiple records and
group the result by one or more column. It is generally used in a SELECT
statement.
You can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG
etc. on the grouped column.
Syntax:

SELECT expression1, expression2, ... expression_n,

aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

[Link]
By using the following query we can count number of cities in the above table.
Example :
SELECT city, COUNT(*)
FROM emp
GROUP BY city;
Output :

[Link]
Example :
SELECT city, SUM(salary)
FROM emp
GROUP BY city;
Output :

[Link]
MySQL MIN function :

The following example specifies the minimum salary of the employees form the
table "emp".

Example :
SELECT empname, MIN(salary)
FROM emp;

Output :

[Link]
MySQL MAX function :
The following example specifies the maximum salary of the employees form
the table "emp".
Example :

SELECT empname, MAX (salary)

FROM emp;

Output :

[Link]
MySQL AVG function :
The following example specifies the average salary of the employees form the
table "emp".
Example :

SELECT empname, AVG(salary)

FROM emp ;

Output :

[Link]
MySQL HAVING Clause :
MySQL HAVING Clause is used with GROUP BY clause. It always returns the
rows where condition is TRUE.
Syntax:

SELECT expression1, expression2, ... expression_n,

aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

[Link]
HAVING Clause with SUM function :
Consider a table "emp“.

[Link]
Example :
SELECT empname, SUM(workinghours)
FROM emp
GROUP BY empname
HAVING SUM(workinghours) > 5;

Output :

[Link]
MySQL AND Condition :
The MySQL AND condition is used with SELECT, INSERT, UPDATE or DELETE
statements to test two or more conditions in an individual query.

Syntax:
WHERE condition1
AND condition2
...
AND condition_n;

[Link]
Example :
SELECT *
FROM emp
WHERE empname= ‘JHON'
AND empid > 2;
Output :

[Link]
MySQL OR Condition
The MySQL OR condition specifies that if you take two or more conditions then
one of the conditions must be fulfilled to get the records as result.

Syntax:
WHERE condition1
OR condition2
...
OR condition_n;

[Link]
Example :
SELECT *
FROM emp
WHERE empname= 'JHON'
OR empid > 7;
Output :

[Link]
MySQL AND & OR condition :
In MySQL, you can use AND & OR condition both together with the SELECT,
INSERT, UPDATE and DELETE statement. While you combine these conditions, you
must be aware of where to use parenthesis so that the database know the order
to evaluate each condition.

Syntax:
WHERE condition1
AND condition2
...
OR condition_n;

[Link]
Example :
SELECT *
FROM emp
WHERE (empname = 'harry' AND city = 'mumbai')
OR (empid < 2);

Output :

[Link]
MySQL LIKE condition :
In MySQL, LIKE condition is used to perform pattern matching to find the
correct result. It is used in SELECT, INSERT, UPDATE and DELETE statement with
the combination of WHERE clause.

Syntax:

expression LIKE pattern [ ESCAPE 'escape_character' ]

[Link]
Example Using % (percent):
SELECT *
FROM emp
WHERE city LIKE ‘h%’ ;
Output :

[Link]
Example Using _ (Underscore):
SELECT *
FROM emp
WHERE city LIKE ‘u_a%’ ;
Output :

[Link]
Example Using NOT Operator:
SELECT *
FROM emp
WHERE city NOT LIKE ‘us%’ ;
Output :

[Link]
MySQL IN Condition :
The MySQL IN condition is used to reduce the use of multiple OR conditions in
a SELECT, INSERT, UPDATE and DELETE statement.
Syntax:

expression IN (value1, value2, .... value_n);

[Link]
Example :
SELECT *
FROM emp
WHERE empname IN (‘JHON', 'Vimal', ‘harry');

Output :

[Link]
MySQL IS NULL Condition
MySQL IS NULL condition is used to check if there is a NULL value in the
expression. It is used with SELECT, INSERT, UPDATE and DELETE statement.
Syntax:

expression IS NULL

[Link]
Example Using NULL:
SELECT *
FROM emp
WHERE salary IS NULL;

Output :

[Link]
MySQL IS NOT NULL Condition :
MySQL IS NOT NULL condition is used to check the NOT NULL value in the
expression. It is used with SELECT, INSERT, UPDATE and DELETE statements.

Syntax:

expression IS NOT NULL

[Link]
Example Using NOT Operator:
SELECT *
FROM emp
WHERE workinghours IS NOT NULL;

Output :

[Link]
MySQL BETWEEN Condition :
The MYSQL BETWEEN condition specifies how to retrieve values from an
expression within a specific range. It is used with SELECT, INSERT, UPDATE and
DELETE statement.

Syntax:
expression BETWEEN value1 AND value2;

[Link]
Example :
SELECT *
FROM emp
WHERE empid BETWEEN 1 AND 3;

Output :

[Link]
MySQL ALTER Table :
MySQL ALTER statement is used when you want to change the name of your
table or any table field. It is also used to add or delete an existing column in a
table.

The ALTER statement is always used with "ADD", "DROP" and "MODIFY"
commands according to the situation.

1) ADD a column in the table :

Syntax:

ALTER TABLE table_name

ADD new_column_name column_definition
[ FIRST | AFTER column_name ];

[Link]
Parameters :

Table_name : It specifies the name of the table that you want to modify.

New_column_name : It specifies the name of the new column that you want to add
to the table.

Column_definition : It specifies the data type and definition of the column (NULL or
NOT NULL, etc).

FIRST | AFTER column_name : It is optional. It tells MySQL where in the table to

create the column. If this parameter is not specified, the new column will be added
to the end of the table.

[Link]
Example:

In this example, we add a new column “empage" in the existing table “emp".

ALTER TABLE emp

ADD empage varchar(40) NOT NULL;

Output :

[Link]
2) Add multiple columns in the table :

Syntax:

ALTER TABLE table_name

ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
...
;

[Link]
Example:
ALTER TABLE emp
ADD emp_address varchar(100) NOT NULL
AFTER empname,
ADD emp_bonus int(100) NOT NULL
AFTER empage ;
Output :

[Link]
3) MODIFY column in the table

The MODIFY command is used to change the column definition of the

table.

Syntax:

ALTER TABLE table_name

MODIFY column_name column_definition
[ FIRST | AFTER column_name ];

[Link]
Example:

In this example, we modify the column cus_surname to be a data type of

varchar(50) and force the column to allow NULL values.

ALTER TABLE emp;

MODIFY city varchar(50) NULL;

Output:

[Link]
4) DROP column in table :

Syntax:

ALTER TABLE table_name

DROP COLUMN column_name;

Example:
ALTER TABLE emp
DROP COLUMN emp_address;

[Link]
Output :

salary

[Link]
5) RENAME column in table :
Syntax:

ALTER TABLE table_name

CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ]

Example:
Here ,we will change the column name "city" to "emp_city".

ALTER TABLE emp

CHANGE COLUMN city emp_city
varchar(20) NOT NULL;

[Link]
5) RENAME column in table :
Syntax:

ALTER TABLE table_name

CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ]

Example:
Here ,we will change the column name “city" to “emp_city".

ALTER TABLE emp

CHANGE COLUMN city emp_city
varchar(20) NOT NULL;

[Link]
Output :

salary

[Link]
6) RENAME table
Syntax:

ALTER TABLE table_name

RENAME TO new_table_name;
Example:

Here, the table name emp is renamed as emp_table.

ALTER TABLE emp

RENAME TO emp_table;

[Link]
Output :

[Link]
MySQL TRUNCATE Table :
MYSQL TRUNCATE statement removes the complete data without removing its
structure.
The TRUNCATE TABLE statement is used when you want to delete the complete
data from a table without removing the table structure.

Syntax:
TRUNCATE TABLE table_name;
Example:

In this example, we truncate the table "emp_table".

TRUNCATE TABLE emp_table;

[Link]
Output :

[Link]
MySQL DROP Table :
MYSQL DROP table statement removes the complete data with structure.

Syntax:
DROP TABLE table_name;
Example:

This example specifies how to drop a table. In this example, we are dropping the
table "emp_table".

DROP TABLE emp_table;

[Link]
[Link]
MySQL JOINS

[Link]
MySQL JOINS :
MySQL JOINS are used with SELECT statement. It is used to retrieve data from
multiple tables. It is performed whenever you need to fetch records from two or
more tables.

There are three types of MySQL joins:

• MySQL INNER JOIN (or sometimes called simple join)
• MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

[Link]
MySQL Inner JOIN (Simple Join) :
The MySQL INNER JOIN is used to return all rows from multiple tables where
the join condition is satisfied. It is the most common type of join.
Syntax :
SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];
Image representation:

[Link]
Consider two tables “emp" and "std_tbl",

[Link]
Example :

SELECT [Link], [Link], std_tbl.std_sub

FROM emp
INNER JOIN std_tbl
ON [Link] = std_tbl.std_id;
Output :

[Link]
MySQL Left Outer Join :
The LEFT OUTER JOIN returns all rows from the left hand table specified in the
ON condition and only those rows from the other table where the join condition is
fulfilled.
Syntax :
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON [Link] = [Link];
Image representation:

[Link]
Example :

SELECT [Link], [Link], std_tbl.std_sub,std_tbl.std_name

FROM emp
LEFT JOIN std_tbl
ON [Link] = std_tbl.std_id;
Output :

[Link]
MySQL Right Outer Join :
The MySQL Right Outer Join returns all rows from the RIGHT-hand table
specified in the ON condition and only those rows from the other table where he
join condition is fulfilled.
Syntax :
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON [Link] = [Link];
Image representation:

[Link]
Example :

SELECT [Link], [Link], std_tbl.std_sub,std_tbl.std_name

FROM emp
RIGHT JOIN std_tbl
ON [Link] = std_tbl.std_id;
Output :

[Link]