A description of SQL Structured Query Language classification

A description of SQL Structured Query Language classification

SQL: Structured Query Language, which is a language method for defining and manipulating relational data.

The SQL Structured Query language consists of 6 parts:

First, data Query Language (DQL)

DQL Full name Data Query Language, whose statement is also called a "database retrieval statement", is to obtain data from the table and determine how the data is given in the application. The keyword Select is the most common verb used by DQL (and all SQL), and the other dql commonly used reserved words Where,order by,group by and having. These dql reserved words are often used in conjunction with other types of SQL statements.

2. Select (keyword) Use example with ORDER by (reserved word)

   Mysql> Select User,host from Mysql.user order by user;

   +-----------+-----------+

   | user | Host |

   +-----------+-----------+

   | Mysql.sys | localhost |

   | Root | localhost |

   | Zabbix | localhost |

   +-----------+-----------+

   3 Rows in Set (0.00 sec)

4. Select and from
   

   Mysql> select user,host,authentication_string from Mysql.user;

   +-----------+-----------+-------------------------------------------+

   | user | Host | authentication_string |

   +-----------+-----------+-------------------------------------------+

   | Root | localhost | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |

   | Mysql.sys | localhost | *thisisnotavalidpasswordthatcanbeusedhere |

   | Zabbix | localhost | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |

   +-----------+-----------+-------------------------------------------+

   3 Rows in Set (0.00 sec)

   Description: There is no password this field under the mysql5.7 database, the password field is changed to Authentication_string, which is used to view the user's account password.

Ii. Data Manipulation Language (DML)

DML full Name: Data manipulation Language, whose statements include verb insert,update and delete. They are used to add, modify, and delete rows (data) in a table, respectively. Also known as the action query language.

1. Delete data from a table

   First look at the data in MySQL (library), User (table)

   Mysql> select User,host from Mysql.user;

   +-----------+---------------+

   | user | Host |

   +-----------+---------------+

   | Nginx | 192.169.5.144 |

   | Mysql.sys | localhost |

   | Root | localhost |

   | Zabbix | localhost |

   +-----------+---------------+

   4 rows in Set (0.00 sec)

   Then delete the nginx in the MySQL Library user table:

   Mysql> Delete from Mysql.user where user= ' nginx ' and host= ' 192.169.5.144 ';

   Query OK, 1 row affected (0.03 sec)

   Description: And host= ' 192.169.5.144 ' can be added, i.e.: delete from mysql.user where user= ' nginx ';

   Finally, review the user information in MySQL (library), Users (table)

   Mysql> select User,host from Mysql.user;

   +-----------+-----------+

   | user | Host |

   +-----------+-----------+

   | Mysql.sys | localhost |

   | Root | localhost |

   | Zabbix | localhost |

   +-----------+-----------+

   3 Rows in Set (0.00 sec)

   Description: We can see that nginx this user has been deleted!

3. Adding and modifying data in a table

   View: http://wutengfei.blog.51cto.com/10942117/1905963

Third, transaction processing language (TPL)

Its statement ensures that all rows of the table affected by the DML statement are updated in a timely manner. The TPL language includes begin transaction,commit and rollback.

Description: MySQL performs commit by default, and Oracle needs to manually execute the TPL language such as commit.

Iv. Data Control Language (DCL)

The DCL full name (Data Control Language) whose statements are licensed through grant or revoke. Determine access to database objects for individual users and groups of users. Some RDBMS can use grant or revoke to control access to individual columns of a table.

V. Data definition language (DDL)

The DDL full name (Data Definition Language) whose statements include verb create and drop. Create a new table or delete a table in a database, add an index to a table, and so on. The DDL includes many reserved words related to the data obtained in the Human database directory. It is also part of the action query.

Six, pointer Control language (CCL)

Its statements, like declare cursor,fetch into and update WHERE current, are used to operate on separate rows for one or more tables.

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/9D/FB/wKioL1mJggzzY1p-AAFHAEx_3-Q728.png "title=" SQL classification -4-2017-8-7.png "width=" "height=" "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:700PX;HEIGHT:116PX; " alt= "Wkiol1mjggzzy1p-aafhaex_3-q728.png"/>

This article is from the "Hand of the Paladin Control" blog, please make sure to keep this source http://wutengfei.blog.51cto.com/10942117/1954569

A description of SQL Structured Query Language classification