Wednesday 7 December 2016

IMPORTANT SQL COMMANDS PART-1......

SQL:
       ·         SQL stands for Structured Query Language.
       ·         SQL is used to communicate with a database.
       ·         According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.
       ·         SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
       ·         Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.
       ·         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.

SOME IMPORTANT SQL COMMANDS:
COMMANDS
SYNTAX
FUNCTION
SELECT
SELECT column_name,column_name
FROM 
table_name;
OR
SELECT * FROM table_name;
Asterisk(*) means select all columns in the table.

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

CREATE TABLE
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
Used to create tables to store data. 

Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level.
CREATE DB
CREATE DATABASE dbname;
Used to create a database.
INSERT
INSERT INTO table_name
VALUES (value1,value2,value3,...);

OR

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Used to add new rows of data to a table.
UPDATE
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Used to modify the existing rows in a table.

In the Update statement, WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected.
DELETE
DELETE FROM table_name
WHERE some_column=some_value;

Used to delete rows from a table.

The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.
ALTER
ALTER TABLE table_name
ADD column_name datatype
Used to change characteristics of a database.
After creating a database, we can change its properties by executing ALTER DATABASE statement. The user should have admin privileges for modifying a database.

ORDER BY
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
Used to sort the result-set by one or more columns.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.

WHERE
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
Used to extract only those records that fulfill a specified criterion.

No comments:

Post a Comment