Set of common SQL statements and SQL statements

Set of common SQL statements and SQL statements

I. Data Definition 
1. CREATE a new DATABASE: create database database_name
2. CREATE a new TABLE: create table table_name (column_name datatype, column_name datatype ,...)
3. modify a data table:
ADD column: alter table table_name ADD column_name datatype
Delete COLUMN: alter table table_name drop column column_name
Alter column: alter table table_name alter column column_name datatype
4. delete a TABLE: drop table table_name

 

Ii. Data Operations
SELECT data: SELECT column name FROM Table Name
SELECT * FROM Table Name
Duplicate Filtering: select distinct column name FROM Table Name
Specify the condition: SELECT column name FROM table name WHERE column operator Value
Multiple conditions: SELECT column name FROM table name WHERE column operator value AND (OR) column operator Value
Sort ORDER: SELECT column name FROM table name order by column 1, column 2 DESC (column 1 in ascending ORDER and column 2 in reverse ORDER)
INSERT data: insert into table name VALUES (value 1, value 2 ,...)
Insert into table_name (column 1, column 2,...) VALUES (value 1, value 2 ,....)
UPDATE Data: UPDATE table name SET column name = new value WHERE column name = A Value
DELETE data: delete from table name WHERE column name = Value

 

Iii. Examples of common SQL statements

SELECT: SELECT LastName, FirstName FROM Persons
DISTINCT: select distinct Company FROM Orders
Condition: SELECT * FROM Persons WHERE City = 'beijing'
AND: SELECT * FROM Persons WHERE FirstName = 'Thomas 'AND LastName = 'cart'
OR: SELECT * FROM Persons WHERE firstname = 'Thomas 'OR lastname = 'cart'
Ascending: SELECT Company, OrderNumber FROM Orders order by Company
Descending ORDER: SELECT Company, OrderNumber FROM Orders order by Company DESC
INSERT 1: insert into Persons VALUES ('gates', 'bill ', 'xuanwumen 10', 'beijing ')
INSERT 2: insert into Persons (LastName, Address) VALUES ('wilson ', 'champs-elysees ')
UPDATE: UPDATE Person SET FirstName = 'fred 'WHERE LastName = 'wilson'
DELETE: delete from Person WHERE LastName = 'wilson'

LIMIT: SELECT * FROM Persons LIMIT 5
Character search: SELECT * FROM Persons WHERE City LIKE '% BJ %'
Wildcard: %: replace one or more characters
_: Replace only one character;
[ABC]: One of ABC;
[^ ABC]: Except ABC
Multiple selection conditions: SELECT * FROM Persons WHERE LastName IN ('adams', 'cart ')
BETWEEN them: SELECT * FROM Persons WHERE LastName BETWEEN 'adams' AND 'cart'
Alias: SELECT po. OrderID, p. * FROM Persons AS p, Product AS po WHERE p. LastName = 'adams' AND p. FirstName = 'john'
Joint query: SELECT Persons. *, Orders. OrderNo FROM Persons, Orders WHERE Persons. Id_P = Orders. Id_P
JOIN Union: SELECT Persons. *, Orders. OrderNo FROM Persons inner join Orders ON Persons. Id_P = Orders. Id_P
Merge result: SELECT E_Name FROM Employees_China union select E_Name FROM Employees_USA