MySQL: SQL

SQL is a standard language for accessing and manipulating databases. To connect any and every database and to execute, insert, update or delete any records, we need SQL..
Brief description about SQL:

•     SQL stands for Structured Query Language
•     SQL lets you access and manipulate databases
•     SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

•     SQL can execute queries against a database
•     SQL can retrieve data from a database
•     SQL can insert records in a database
•     SQL can update records in a database
•     SQL can delete records from a database
•     SQL can create new databases
•     SQL can create new tables in a database
•     SQL can create stored procedures in a database
•     SQL can create views in a database
•     SQL can set permissions on tables, procedures, and views

SQL DML and DDL
SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data 
 Definition Language (DDL).

The query and update commands form the DML part of SQL:
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
The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:
CREATE DATABASE – creates a new database
ALTER DATABASE – modifies a database
CREATE TABLE – creates a new table
ALTER TABLE – modifies a table
DROP TABLE – deletes a table
CREATE INDEX – creates an index (search key)
DROP INDEX – deletes an index
So to start writing query, practice with sql editor.

• Open localhost/phpmyadmin
• Click on sql tab

The SQL SELECT Statement
The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT column_name(s) FROM table_name
and
SELECT * FROM table_name
Examples:
Now write following command.
And press go button.

SELECT * Example
Now we want to select all the columns from the “tbl_member” table.
We use the following SELECT statement:
SELECT * FROM tbl_member
Output:

SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s) FROM table_name
Example:
tbl_member table

 

SELECT DISTINCT ‘address’ FROM tbl_member
Output:

Here, redundant data of address column is removed.
 SQL WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE  Syntax
SELECT column_name(s)FROM table_name WHERE column_name operator value
Example:
SELECT * FROM `tbl_member` WHERE `name`=”Ram”;
Output:

Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:
=         Equal
<>     Not equal
>     Greater than
<     Less than
>=     Greater than or equal
<=     Less than or equal
BETWEEN     Between an inclusive range
LIKE     Search for a pattern
IN     To specify multiple possible values for a column
The ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.
SQL ORDER BY Syntax
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
Example:
SELECT * FROM `tbl_member` ORDER BY `name` DESC
Output:

The AND & OR Operators
The AND operator displays a record if both the first condition and the second condition is true.
The OR operator displays a record if either the first condition or the second condition is true.
Example:
SELECT * FROM `tbl_member` WHERE `name`=”Ram” AND `address`=”KTM”
Output:

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

SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.
The first form doesn’t specify the column names where the data will be inserted, only their values:
INSERT INTO table_name VALUES (value1, value2, value3,…)
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,…) VALUES (value1, value2, value3,…)
Example:
INSERT INTO tbl_member VALUES (’5′,’Binay’,'Chapagaun’,’9849111111′)
Output:

The UPDATE Statement
The UPDATE statement is used to update existing records in a table.
SQL UPDATE Syntax
UPDATE table_name SET column1=value, column2=value2,…     WHERE some_column=some_value
Example:
UPDATE `tbl_member` SET `address`=”Anamnagar” WHERE `id`=”5″

The DELETE Statement
The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax
DELETE FROM table_name WHERE some_column=some_value
Example:
DELETE FROM `tbl_member` WHERE id=”2″