MySQL basic syntax

MySQL database three basic operations:

ddl--Data Definition language (create,alter,drop,declare)

dml--Data Manipulation Language (Select,delete,update,insert)

dcl--Data Control Language (Grant,revoke,commit,rollback)

MySQL Base statement

1. Create a database

CREATE DATABASE database_name

2. Delete Database

DROP DATABASE database_name

3. Backing Up the database

4. Create a new table

CREATE TABLE tabname (col1 type [not null][primary key],col2 type [ not null],...)

5. Delete a table

drop table tabname

6. Add columns

ALTER TABLE tabname Add column col type

7. Add PRIMARY Key

ALTER TABLE tabname Add primary key(COL)

8. Delete primary key

ALTER TABLE tabname drop primary key(COL)

9. Add Index

Create [uniqe] index idxname on tabname (COL)

10. Delete Index

Drop index idxname

Note: The index cannot be changed, and the need to change must be removed for re-build .

11. Create a View

Create View viewname as select statement

12. Delete View

Drop View viewname

A few simple basic SQL statements

Find: SELECT * from tabname where condition

Insert: INSERT INTO TabName (col1,col2) VALUES (value1,value2)

Delete: Delete from tabname where condition

Updated: Update tabname set col1=value1 where condition

Sort: SELECT * from TabName ORDER by COL1[DESC/INSC]

Total: Select count as TotalCount from TabName

Sum: Select SUM (col1) as Sumvalue from TabName

Average: Select AVG (col1) as Avgvaule from TabName

Maximum: Select Max (col1) as MaxValue from TabName

Min: select min (col1) as MinValue from TabName

Several advanced query operators

Union operator

Combine two result tables and eliminate any one of the repeating rows to derive a result table when

Except operator

Intersect operator