SQL Syntax collation

SQL is the abbreviation of Structured Query language, the Chinese full name is a structured query language, is a relational database system for data storage, querying, updating and management.

SQL syntax

Create a table

CREATE TABLE TableName (filedname1 filedtype1 (length), filedname2 filedtype2 (length),...)

Create a View

CREATE VIEW viewname AS SELECT statement

Adding data records

INSERT INTO TableName (filed1,filed2,filed3,...) VALUES (Val1,val2,val3,...)

INSERT INTO Newtablename select * from Oldtablename (add data from the old table to the target table)

Add primary Key

ALTER TABLE TableName Add primary key (COL)

Delete primary key

ALTER TABLE TableName drop PRIMARY key (COL)

Create an index

Create [Unique]index IndexName on TableName

Delete Index

Drop INDEX IndexName on tablename

Create a View

CREATE VIEW viewname AS SELECT statement

Delete a view

Drop View ViewName

Add columns

ALTER TABLE TableName Add ColumnName Columntype[default] Adds a column to the table, and the contents of [] are optional

Delete Column

ALTER TABLE tablename DROP column ColumnName

Delete a table

DROP TABLE TableName

Delete information from a table

Delete from TableName (delete all information in the table)

Delete from table where ...

Delete Column

ALTER TABLE tablename DROP column ColumnName

Update data records

Update tablename Set filedname = Filedvalue where ...

Update tablename Set filedname1 = Filedvalue1,filedname2 = Filedvalue2,... where ...

Data statistics functions

AVG (Filedname) counts the average of a column

Count (*,filedname) statistics on the number of rows in a table or on the number of rows of data in a column

Max (Filedname) Statistics The maximum value of a column

Min (filedname) Statistics The minimum value of a column

SUM (filedname) Statistics The sum of a column of values

External connection

Left OUTER JOIN outer JOIN result set includes all rows of the primary table's matching row and left join table.

Right outer join the outer join result set includes all rows in the matching row and right join table of the join table.

The full outer join result set includes the matching rows for the join table and all the records in the two join tables.

Union operator

The Union operator derives a new result table by combining two result tables, and union all does not eliminate duplicate rows.

Data record filtering

Select top from TableName ORDER by Filedname[desc]

SELECT * FROM tablename where filedname like '%filedvalue% ' ORDER by Filedname[desc]

SELECT * FROM TableName where filedname in (Val1,val2,val3,...)

SELECT * FROM tablename where filedname between Val1 and Val2

Use of 1=1,1=2

Mostly used in SQL statement combinations

Where 1=1 means select all

Where 1=2 means all is not selected

SQL Syntax collation