SQL COMMAND

SQL Query syntax

1.       Create database

CREATE DATABASE DatabaseName;

2.       drop database

DROP DATABASE DatabaseName;

3.       select database

USE DatabaseName;

4.       create table

CREATE TABLE table_name(

   column1 datatype,

  column2 datatype,

   column3 datatype,

  .....

  columnN datatype,

  PRIMARY KEY( one or more columns )

);

5.       Drop table

DROP TABLE table_name;

6.       Inset to table

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]  

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

7.       select table

SELECT column1, column2, columnN FROM table_name;

8.       WHERE clause

        The SQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. If the given condition is satisfied then only it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records.

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition]

9.       AND OR Clause

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition1] AND [condition2]...AND [conditionN];

10.   Update

UPDATE table_name

SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];

11.   Delete

DELETE FROM table_name

WHERE [condition];

12.   LIKE clause

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.

SELECT FROM table_name

WHERE column LIKE 'XXXX%'

or 

SELECT FROM table_name

WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name

WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name

WHERE column LIKE '_XXXX'

or

SELECT FROM table_name

WHERE column LIKE '_XXXX_'

13.   Top clause

SELECT TOP number|percent column_name(s)

FROM table_name

WHERE [condition]

14.   Oder by

SELECT column-list 

FROM table_name 

[WHERE condition] 

[ORDER BY column1, column2, .. columnN] [ASC | DESC];

15.   Group by

SELECT column1, column2

FROM table_name

WHERE [ conditions ]

GROUP BY column1, column2

ORDER BY column1, column2

16.   DISTINCT ( 唯一)

SELECT DISTINCT column1, column2,.....columnN 

FROM table_name

WHERE [condition]

17.   Inner join

SELECT table1.column1, table2.column2...

FROM table1

INNER JOIN table2

ON table1.common_field = table2.common_field;

18.   Alter

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

ALTER TABLE table_name DROP INDEX MyUniqueConstraint;

ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;

19.