全部博文(130)
分类: LINUX
2015-11-28 19:48:39
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.