MySQL

MySQL is the most popular Open Source Relatonal SQL Database Management System. MySQL is one of
the best RDBMS being used for developing various web-based sofware applicatons. MySQL is
developed, marketed and supported by MySQL AB, a company founded in 1995 in Sweden.

In 2008, MySQL AB announced that it had agreed to be acquired by Sun Microsystems.

MySQL is becoming so popular because of many good reasons −

 MySQL is released under an open-source license. So you have nothing to pay to use it.
 MySQL uses a standard form of the well-known SQL data language.
 MySQL works on many operatng systems and with many languages including PHP, PERL, C, C++,
JAVA, etc.
 MySQL works very quickly and works well even with large data sets.
 MySQL is very friendly to PHP, the most appreciated language for web development.
 MySQL supports large databases, up to 50 million rows or more in a table. The default fle size
limit for a table is 4GB, but you can increase this (if your operatng system can handle it) to a
theoretcal limit of 8 million terabytes (TB).
 MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL
sofware to ft their own specifc environments.

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

Syntax

CREATE DATABASE databasename;

EX:

create database school;

Note:

1) Once a database is created, you can check it in the list of databases with the SQL command:
SHOW DATABASES;
2) SQL keywords are NOT case sensitve: show is the same as SHOW

3) Some database systems require a semicolon at the end of each SQL statement. Semicolon is the
standard way to separate each SQL statement in database systems that allow more than one
SQL statement to be executed in the same call to the server.

4) \q to quit from Mysql.

The SQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop an existng SQL database.

Syntax

DROP DATABASE databasename;

EX:

drop database school;

Note: Be careful before dropping a database. Deletng a database will result in loss of complete
informaton stored in the database!

The SQL CREATE TABLE Statement

To create table, frst select the database by following command

use databasename;

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
);

Example:

create table Student (roll int, name varchar(20),per foat);

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways.

The frst way specifes both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names in
the SQL query. However, make sure the order of the values is in the same order as the columns in the
table. The INSERT INTO syntax would be as follows:

INSERT INTO table_name

VALUES (value1, value2, value3, ...), (value4,vale5,value6);

EX:

insert into Student values(1,’aman’,87.5),(2,’riya’,98.3);

The SQL WHERE Clause

The WHERE clause is used to flter records.

The WHERE clause is used to extract only those records that fulfll a specifed conditon . It is also used in
UPDATE, DELETE statement, etc.!

WHERE Syntax

SELECT column1, column2, ...

FROM table_name
WHERE conditon;

The following SQL statement selects all the customers from the country "INDIA", in the "Customers"
table:
Example

SELECT * FROM Customers

WHERE Country='INDIA';

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric felds should not be enclosed in quotes:

MySQL AND Conditon

The MySQL AND conditon is used with SELECT, INSERT, UPDATE or DELETE statements to test two or
more conditons in an individual query.

Ex:

SELECT *
FROM student
WHERE stu_frstname = 'Ajeet'
AND roll > 3;

MySQL OR Conditon

The MySQL OR conditon specifes that if you take two or more conditons then one of the conditons
must be fulflled to get the records as result.

Ex:

SELECT *
FROM student
WHERE stu_frstname = 'Ajeet'
OR roll > 5;

MySQL AND & OR conditon

Ex:

SELECT *
FROM students
WHERE (course_name = 'Java' AND student_name = 'Aryan')
OR (student_id < 2);

MySQL LIKE conditon

In MySQL, LIKE conditon is used to perform patern matching to fnd the correct result. It is used in
SELECT, INSERT, UPDATE and DELETE statement with the combinaton of WHERE clause.

Ex:
SELECT student_name
FROM student
WHERE address LIKE 'Jaip%';

MySQL NOT Operator:

Ex:

SELECT student_name
FROM student
WHERE address NOT LIKE 'Jaip%';

MySQL IN Conditon

The MySQL IN conditon is used to reduce the use of multple OR conditons in a SELECT, INSERT,
UPDATE and DELETE statement.

EX:

SELECT *
FROM student
WHERE student_name IN ('Anil', 'Rahim', 'Deepika');

MySQL NOT Conditon

The MySQL NOT conditon is opposite of MySQL IN conditon. It is used to negate a conditon in a
SELECT, INSERT, UPDATE or DELETE statement.

EX:

SELECT *
FROM student
WHERE student_name NOT IN ('Anil', 'Rahim', 'Deepika');

MySQL NOT Operator with IS NULL conditon:

EX:

SELECT *
FROM student
WHERE student_name IS NOT NULL;

MySQL NOT Operator with LIKE conditon:

EX:

SELECT *
FROM student
WHERE student_name NOT LIKE 'A%';

MySQL BETWEEN conditon

Ex:

SELECT *
FROM student
WHERE student_id NOT BETWEEN 3 AND 5;

MySQL IS NULL Conditon

MySQL IS NULL conditon is used to check if there is a NULL value in the expression. It is used with
SELECT, INSERT, UPDATE and DELETE statement.

Ex:

SELECT *
FROM student
WHERE student_name IS NULL;
The SQL UPDATE Statement

The UPDATE statement is used to modify the existng records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE conditon;

Note: Be careful when updatng records in a table! Notce the WHERE clause in the UPDATE statement.
The WHERE clause specifes which record(s) that should be updated. If you omit the WHERE clause, all
records in the table will be updated!

Example

UPDATE Student
SET name = 'Aman', City= 'Jaipur'
WHERE roll = 1;

The SQL DELETE Statement

The DELETE statement is used to delete existng records in a table.

DELETE Syntax

DELETE FROM table_name

WHERE conditon;

Note: Be careful when deletng records in a table! Notce the WHERE clause in the DELETE statement.
The WHERE clause specifes which record(s) that should be deleted. If you omit the WHERE clause, all
records in the table will be deleted!

DELETE FROM Student

WHERE name='Aman';

The SQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existng table in a database.

Syntax

DROP TABLE table_name;

Note: Be careful before dropping a table. Deletng a table will result in loss of complete informaton
stored in the table!
Ex:

Drop table Student;

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existng table.

ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype;

EX:

ALTER TABLE Student add column address varchar(30);

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notce that some database systems don't allow
dram

eletng a column):

ALTER TABLE table_name

DROP COLUMN column_name;

EX:

ALTER TABLE Student drop column address;

ALTER TABLE - ALTER/MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name

ALTER COLUMN column_name datatype;

My SQL / Oracle (prior version 10G):

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

The SQL MIN() and MAX(), COUNT(), AVG() and SUM() Functons

The MIN() functon returns the smallest value of the selected column.

The MAX() functon returns the largest value of the selected column.

The COUNT() functon returns the number of rows that matches a specifed criteria.

The AVG() functon returns the average value of a numeric column.

The SUM() functon returns the total sum of a numeric column.

MIN() Syntax

SELECT MIN(column_name)
FROM table_name
WHERE conditon;

MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE conditon;

COUNT() Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE conditon;

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE conditon;

SUM() Syntax

SELECT SUM(column_name)
FROM table_name
WHERE conditon;
Some of The Most Important SQL Commands

 SELECT - extracts data from a database

 UPDATE - updates data in a database
 DELETE - deletes data from a database
 INSERT INTO - inserts new data into a database
 CREATE DATABASE - creates a new database
 ALTER DATABASE - modifes a database
 CREATE TABLE - creates a new table
 ALTER TABLE - modifes a table
 DROP TABLE - deletes a table
 SHOW DATABASES; shows all databases
 SHOW TABLES; shows all tables
 DESCRIBE TABLENAME; describe table
 USE DATABASENAME; use database

MySQL Distnct Clause

MySQL DISTINCT clause is used to remove duplicate records from the table and fetch only the unique
records. The DISTINCT clause is only used with the SELECT statement.

Syntax:

SELECT DISTINCT expressions

FROM tables
[WHERE conditons];

Example:
SELECT DISTINCT officer_name, address FROM officers;

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 conditons]
ORDER BY expression [ ASC | DESC ];
Example1:

SELECT *
FROM officers
WHERE address = 'Lucknow'
ORDER BY officer_name;

OR

SELECT *
FROM officers
WHERE address = 'Lucknow'
ORDER BY officer_name ASC;

Note: If you use MySQL ORDER BY clause without specifying the ASC and DESC modifer then by default
you will get the result in ascending order.

Example2:

SELECT *
FROM officers
WHERE address = 'Lucknow'
ORDER BY officer_name DESC;

MySQL ORDER BY: using both ASC and DESC atributes

SELECT officer_name, address

FROM officers
WHERE officer_id < 5
ORDER BY officer_name DESC, address ASC;

MySQL GROUP BY Clause

The MYSQL GROUP BY Clause is used to collect data from multple records and group the result by one
or more column. It is generally used in a SELECT statement.

Example1:
SELECT address, COUNT(*)
FROM officers
GROUP BY address;
Example2:
SELECT emp_name, SUM(working_hours) AS "Total working hours"
FROM employees
GROUP BY emp_name;

MySQL HAVING Clause

MySQL HAVING Clause is used with GROUP BY clause. It always returns the rows where conditon is
TRUE.

Example:
SELECT emp_name, SUM(working_hours) AS "Total working hours"
FROM employees
GROUP BY emp_name
HAVING SUM(working_hours) > 5;

MySQL frst functon

The MySQL frst functon is used to return the frst value of the selected column. Here, we use limit
clause to select frst record or more.

Syntax:

SELECT column_name
FROM table_name
LIMIT 1;

Example1: To select frst record

SELECT officer_name
FROM officers
LIMIT 1;

Example2: To SELECT FIRST two records

SELECT officer_name
FROM officers
LIMIT 2
MySQL last functon

MySQL last functon is used to return the last value of the selected column.

Syntax:

SELECT column_name
FROM table_name
ORDER BY column_name DESC
LIMIT 1;

Example: To return the last officer_name ordering by officer_id.

SELECT officer_name
FROM officers
ORDER BY officer_id DESC
LIMIT 1;

DDL, DML, DCL, and TCL.

SQL commands are divided into four subgroups, DDL, DML, DCL, and TCL.

1) DDL is short name of Data Defniton Language, which deals with database schemas and
descriptons, of how the data should reside in the database.

Example: create, alter, drop etc.

2) DML is short name of Data Manipulaton Language which deals with data manipulaton and
includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is
used to store, modify, retrieve, delete and update data in a database.

3) DCL is short name of Data Control Language which includes commands such as GRANT and
mostly concerned with rights, permissions and other controls of the database system.

 GRANT - allow users access privileges to the database

 REVOKE - withdraw users access privileges given by using the GRANT command
4) TCL is short name of Transacton Control Language which deals with a transacton within a
database.

 COMMIT - commits a Transacton

 ROLLBACK - rollback a transacton in case of any error occurs
 SAVEPOINT - to rollback the transacton making points within groups