SQL Quick Check

/*2014.11.02

* Based on several documents, it is difficult to be exhaustive and will be expanded as soon as possible.

* Any suggestions please feel free to ask in the comment area, or if you find the mistake please point out in time, grateful.

*/

Introduction to SQL

SQL full name Structured query Language (Structured Query language).

SQL is an ANSI standard computer language.

SQL can be used to access and manipulate data in Oracle,sybase,microsoft SQL Server,db2,access,mysql,postgresql and other database systems.

SQL can add, delete, update, and query operations on the database.

SQL is easy to learn.

SQL Basic Syntax table

Grammatical structure	Description
	Database operations
CREATE DATABASE database_name	Create a database
DROP DATABASE database_name	Delete a database
	Data Table Operations
CREATE TABLE "table_name" ("column_1" "data_type_for_column_1", "Column_2" "Data_type_for_column_2", ...)	Create a table in the database
ALTER TABLE table_name ADD column_name datatype	Add a new column to a table that already exists
ALTER TABLE table_name DROP column_name datatype	Delete a column in a table that already exists
DROP TABLE table_name	Delete a table
	Index operations
CREATE INDEX index_name on table_name (column_name_1,column_name_2,...)	Create a simple index on a table
CREATE UNIQUE INDEX index_name on table_name (column_name_1,column_name_2,...)	Create a unique index on a table
DROP INDEX Table_name.index_name	Delete Index
	Data manipulation
INSERT into table_name VALUES (value_1,value_2,...)	Add a row of data to a table
INSERT into table_name (column_1,column_2,...) VALUES (value_1,value_2,...)	Add a row of data to a table
UPDATE table_name SET Column_name_1=new_value_1, Column_name_2=new_value_2 WHERE Column_name=some_value	Update one or more columns of data
DELETE from table_name Wehre Column_name=some_value	Delete a row or rows in a table (DML language, can have the option to delete)
TRUNCATE TABLE table_name	Delete all data in this table (DDL language, all data in the default table is deleted)
	SELECT
SELECT column_name (s) from table_name	Querying data in a table (one or more columns)
SELECT * FROM table_name	Querying all data in a table
SELECT DISTINCT column_name (s) From table_name	Querying data that is not duplicated in a table
SELECT column_name (s) from table_name Wehre column operator value and column operator value OR column operator value and (..... OR ...) ...	Querying data in a table based on criteria (operator means equals =, not equal to <> Greater than, less than <, not less than >=, not greater than <=, Range between, approximate like operator) (Operators and usages are listed separately in the table)
SELECT column_name (s) from table_name WHERE column_name in (value1,value2,...)	Querying data for a field value as any of the values in parentheses (column_name in (value1,value2,...) Equivalent to Column_name=value1 OR column_name=value2 ... ）
SELECT column_name (s) from table_name ORDER by row_1,row_2 desc,row_2 ASC,...	Sort the query results for a table (Asc,ascend, which are arranged alphabetically and numerically (by default, not written), Desc,descend, in reverse alphabetical and numeric order)
SELECT column_1,..., SUM (group_column_name) From table_name GROUP by Group_column_name	Group by can group result sets based on one or more columns
SELECT column_1,..., SUM (group_column_name) From table_name GROUP by Group_column_name Having SUM (group_column_name) condition value	Is the same as the usage of the where statement. Because the WHERE keyword cannot be used with an aggregate function, in SQL Added HAVING clause Condition = equals =, not equal to <>, greater than;, less than < operator
	alias ( aliases )
SELECT column_name as Column_alias From table_name	Column name aliases
SELECT Table_alias.column_name From table_name As Table_alias	Table name aliases
	Join
SELECT Column_1_name,column_2_name,... From First_table_name INNER JOIN Second_table_name On First_table_name.keyfield =second_table_name.foreign_keyfield	In both the first and second tables INNER JOIN keyword Returns a matching row when at least one match exists
SELECT Column_1_name,column_2_name,... From First_table_name Left JOIN Second_table_name On First_table_name.keyfield =second.table_name.foreign_keyfield	The left JOIN keyword is from the first table (First_table_name) Where all the rows are returned Even if there are no matching rows in the second table (Second_table_name)
SELECT Column_1_name,column_2_name,... From First_table_name Right JOIN Second_table_name On First_table_name.keyfield =second.table_name.foreign_keyfield	The right JOIN keyword will be from the second table (Second_table_name)) Where all the rows are returned Even if there are no matching rows in the first table (First_table_name) (opposite to left join)
	UNION
SELECT column_name (s) from Table_name_1 UNION SELECT column_name (s) from table_name_2	Union is used to combine result sets of two or more SELECT statements These SELECT statements must have the same number of columns, and each column must have the same data type Default selection of values that are not duplicates
SELECT column_name (s) from Table_name_1 UNION All SELECT column_name (s) from table_name_2	Ditto, select a value that is not duplicated
	SELECT into/in
SELECT column_name (s) Into New_table_name From Source_table_name WHERE Query	Pick the data from one table and insert the data into another table (often used to create a backup copy of a table or to archive records)
SELECT column_name (s) In External_database_name From Source_table_name WHERE Query	Select data from one table, and then insert the data into a table in another database
	Create view (Creating views)
CREATE VIEW View_name as SELECT column_name (s) From table_name WHERE condition	Create a data view based on the results of the query

Data type

Data type	Data type description
INTEGER (SIZE) INT (SIZE) SMALLINT (SIZE) TINYINT (SIZE)	The integer data is stored and the maximum value is marked within parentheses
DECIMAL (SIZE,D) NUMERIC (SIZE,D)	Stores data with decimals, size represents its maximum value, and D represents the number of decimal places
CHAR (SIZE)	A string with a fixed length of size that can include letters, numbers, and special characters
VARCHAR (SIZE)	A variable-length string with a maximum length of size that can include letters, numbers, special characters, and so on
DATE (YYYYMMDD)	Data that holds the date type

Operator

Operator	Operator description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
Between	Between a range
Like	Used to search for a specified pattern in a column in a WHERE clause Wildcard characters: "%" can be used to replace one or more characters "_" replaces only one character “[...]” Single character for alternate word columns "[^ ...]" or "[!...]" Replace any single character that is not a character columns

Some aggregate functions

Function	Function description
AVG (column)	Returns the average of the field
COUNT (column)	Returns the total number of non-null values for a column
MAX (column)	Returns the maximum value of a column
MIN (column)	Returns the minimum value of a column
SUM (column)	Returns the and of all values of a column

SQL Quick Check