Common syntax for SQL basics

First, the basic

1. Description: Create DATABASE Database-name

2. Description: Delete database drop DB dbname

3. Description: Create a new table

CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)

To create a new table from an existing table:

4. Description: Delete new table drop table TabName

5. Description: Add a column Alter table tabname add column col type

6, Description: A few simple basic SQL statements

Select: SELECT * FROM table1 where range

Insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)

Delete: Delete from table1 where range

Updated: Update table1 set field1=value1 where range

Find: SELECT * FROM table1 where field1 like '%value1% '

Sort: SELECT * FROM table1 ORDER by FIELD1,FIELD2 [DESC]

Total: Select count as TotalCount from table1

Sum: Select SUM (field1) as Sumvalue from table1

Average: Select AVG (field1) as Avgvalue from table1

Maximum: Select Max (field1) as MaxValue from table1

Min: select min (field1) as MinValue from table1

Second, promote

1, Description: Sub-query (table name 1:a table name 2:b)

Select A,b,c from a where a in (select D from B)

Or: Select A,b,c from a where a in (all-in-a-

2, Description: Between usage, between limits the query data range includes the boundary value, not between does not include

SELECT * FROM table1 where time between time1 and time2

Select A,b,c, from table1 where a is not between value 1 and value 2

3. Description: How to use

SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

4, Description: Two related tables, delete the main table is already in the secondary table does not have the information delete from table1 where is NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1)

5, Description: Four table linked to check the problem:

SELECT * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ...

Third, skills

1, the use of 1=1,1=2, in the combination of SQL statements used more

"Where 1=1" is the choice of all "where 1=2" is not selected

2, LEFT join (external connection), very important

SELECT column_name (s) from table1 left JOIN table2 on table1.column_name= Table2.column_name;

I work for a long time, the above are the more commonly used statements I encountered, as well as foreign keys and data processing logic, of course, SQL syntax is profound, and later encountered other common and useful will be added.

Common syntax for SQL basics