MySQL database base SQL statement/syntax

SQL is now into the Internet to work people must be one of the skills, below to share their feel very nice SQL basic statement, from the Internet to find, feel very good, share to everyone!
Brief introduction to the underlying statement:
1. Description: Create DATABASE

Create DATABASE Database-name
2. Description: Delete Database

Drop Database dbname
3. Description: Back up SQL Server

---to create a device that backs up data
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
---start Backup
BACKUP DATABASE pubs to Testback
4. 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:
A:create table tab_new like Tab_old (create new table with old table)
B:create table tab_new as Select Col1,col2 ... from tab_old definition only
5. Description: Delete new table

drop table TabName
6. Description: Add a column

Alter table tabname Add column col type
Note: Columns cannot be deleted after they are added. DB2 column plus after data type can not be changed, the only change is to increase the varchar type
The length.
7. Description: Add primary key: Alter table TabName Add primary key (COL)

Description: Delete primary key: Alter table tabname drop primary key (COL)
8. Description: Create INDEX: [unique] index idxname on tabname (col ...)

Drop INDEX: Idxname
Note: The index is immutable and you must remove the rebuild if you want to change it.
9. Description: Creating view: Create VIEW viewname AS SELECT statement

Delete view: Drop View ViewName
10, 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% '---the syntax of like is very subtle, check the information!
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
11. Description: Several advanced query operation words

A:union operator
The UNION operator is derived by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in a table
Out a result table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, the pie
Each row of the raw table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives one by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows
The result table. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows
Table. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operation word must be consistent.
12. Description: Use external connection

A, LEFT OUTER join:
Left OUTER join (left JOIN): The result set includes a matching row for the join table and all rows of the left join table.
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C
B:right outer join:
Right outer join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table.
C:full outer join:
Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.
13. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available)

Law one: SELECT * into B from a where 1<>1
Law II: SELECT top 0 * into B from a
14. Description: Copy table (copy data, source table name: A target table name: B) (Access available)

Insert into B (A, B, c) select d,e,f from B;
15. Description: Copy of table across databases (use absolute path for specific data) (Access available)

Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition
Example:.. From B in ' "&server.mappath (". ") & "\data.mdb" & "' Where."
16, 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-

MySQL database base SQL statement/syntax