Shulou(Shulou.com)06/01 Report--

Introduction to the Classification of SQL structured query language

SQL: structured query language, which is a language method for defining and manipulating relational data.

The SQL structured query language consists of six parts:

1. Data query language (DQL)

The full name of DQL is Data Query Language, and its statement, also known as "data retrieval statement", is used to obtain data from a table and determine how the data is given in the application. The keyword SELECT is the most frequently used verb in DQL (and all SQL), and other reserved words WHERE,ORDER BY,GROUP BY and HAVING are commonly used in DQL. These DQL reserved words are often used with other types of SQL statements.

Examples of using select (keyword) 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)

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)

Note: the password field is no longer available in the mysql5.7 database. The password field has been changed to authentication_string to check the user's account password.

2. Data manipulation language (DML)

The full name of DML: Data Manipulation Language, and its sentences include verbs INSERT,UPDATE and DELETE. They are used to add, modify, and delete rows (data) in the table, respectively. Also known as Action query language.

Delete data from a table

Check the data in mysql (library) and user (table) first.

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 from the user table of the mysql library:

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 not be added, that is, delete from mysql.user where user='nginx'

Finally, check the user information in mysql (library) and user (table) again.

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 the user nginx has been deleted!

Add and modify data in a table

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

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.

Note: MySQL executes commit by default, and Oracle needs to manually execute TPL languages such as commit.

4. Data Control language (DCL)

The full name of DCL (Data Control Language), and its statements are licensed through GRANT or REVOKE. Determine the access of individual users and user groups to database objects. Some RDBMS can use GRANT or REVOKE to control access to individual columns of the form.

Data definition language (DDL)

The full name of DDL (Data Definition Language), whose sentences include verbs CREATE and DROP. Create or delete a new table in the database; add an index to the table, and so on. DDL includes a number of reserved words related to obtaining data in the human database catalog. It is also part of the action query.

Pointer Control language (CCL)

Its statements, such as DECLARE CURSOR,FETCH INTO and UPDATE WHERE CURRENT, are used for separate operations on one or more forms.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.