SQL Classification and Common statements

SQL statements can be divided into three categories: DDL, DML, DCL, subdivision can also be divided into six categories: DDL, DML, DQL, TPL, DCL, DCL, CCL. Different distributions are just different granularity of the classification.

Here's a three-class description.

DDL (data definition Language) in the definition of language: These languages define different data segments, databases, tables, columns, indexes and other data objects, mainly for the operation of the database itself or data structure. DDL includes create, DROP, alter, and more are used by database administrators.

Creating library: Create database name default character set UTF8;;

Delete Library: drop database name;

Creating Table: Create table table name (field list);

Delete tables: drop table name;

Add column: ALTER TABLE name add column name type;

Delete column: ALTER TABLE table name drop column name;

Add PRIMARY key: ALTER TABLE name add primary key (column name);

Add foreign key after building table: ALTER TABLE name ADD CONSTRAINT constraint name foreign key (column name) references table name (column name);

Self-increment after table is built: ALTER TABLE name change ID ID int. NOT NULL auto_increment;

Delete PRIMARY KEY constraint: ALTER TABLE name drop PRIMARY key;

Delete FOREIGN KEY constraint: ALTER TABLE name drop FOREIGN key foreign KEY constraint name;

DML (data manipulation Language) Data manipulation statement : Used to add, delete, modify, query the data recorded in the database, mainly for the operation of the data itself. DML includes INSERT, delete, update, select, which is primarily used by developers to manipulate data.

Add data: INSERT into table name (List of column names) values (corresponding column values);

Delete data: Delete from table name where column name = value;

Modify data: Update table name set column name = value where column name = value

Query data: SELECT * FROM table name

DCL Data Control Language : Statements that control the level of permission and access for different data segments directly. These statements define the database, table, field, user's access rights, and security level. The DCL includes Grant, REVOKE, which is used by the DBA to manage object permissions in the system, ensuring that the appropriate user has the appropriate permissions.

Add users to the grant Select,insert,update,delete on database. * To user name @localhost identified by "password"

By six class Description:

DDL data Definition Language: Create, drop

DML Data Manipulation language: INSERT, delete, update

DCL Data Control Language: Grant, REVOKE

DQL Data Query Language: Select, where, order by, group BY, having

TPL Data Processing Language: Ensures that all rows of tables affected by DML are updated in a timely manner, begin TRANSACTION, COMMIT, rollback

CCL Pointer Control Language: Declare cursor,fetch into and update where current is used to separate operations on one or more tables.

Reference: "MySQL database development, optimization and management maintenance"

Related blog: SQL query execution process: http://zuohao1990.blog.51cto.com/6057850/1720116

MySQL Optimizer: http://zuohao1990.blog.51cto.com/6057850/1710726

This article is from the "Stuffed Pig" blog, please be sure to keep this source http://zuohao1990.blog.51cto.com/6057850/1722563

SQL Classification and Common statements