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″
Posts
