Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the role of MySQL database SQL". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, the introduction of SQL, structured query language (Structured Query Language), referred to as SQL. It is a standard programming language specifically used to access databases. It can be used to store data, query data, update data and manage relational databases. At the same time, the database script file has the extension sql.

SQL is a high-level non-procedural programming language that allows users to work on high-level data structures. At the same time, SQL statements can be nested, so it has great flexibility and powerful functionality. However, database manufacturers have made some adaptations and extensions to the SQL standard language, so the SQL language, usage and format of different databases are not completely consistent.

The SQL language can be divided into five parts, namely DDL,DML,TCL,DCL,DQL.

1. Data definition language (Data Definition Language), referred to as DDL.

The language used to create, delete, and modify database objects. Contains the following keywords

Create: used to create the structure of tables or other objects

Drop: used to delete the structure of tables or other objects

Alter: used to modify the structure of tables or other objects

Truncate: used to clear table data and retain table structure

2. Data manipulation language (Data Manipulation Language), abbreviated as DML.

The data used to change the data table. Usually used with the transaction control language, it is only through the commit operation of the transaction control language that the data is really persisted to the database. Contains the following keywords:

Insert: inserts data into the data table.

Update: updates data that already exists in the data table.

Delete: deletes the data in the data table.

3. Data query language (Data Query Language), abbreviated as DQL.

Used to query data in a data table. Or for scientific calculation. The keywords are as follows:

Select: select the data to display.

4. Transaction Control language (Transaction Control Language), abbreviated as TCL.

Used to ensure the integrity and consistency of data. Transactions are involved only if the data table is in a DML operation. The keywords are as follows:

Commit: submit, confirm. Persist the data of the DML operations that have already taken place.

Rollback: roll back, roll back. Used to cancel DML operations that have already taken place.

Savepoint: save point. You can roll back the current DML operation to the specified SavePoint, making it easy to cancel some changes.

5. Data Control language (Data Control Language), abbreviated as DCL.

Used to perform permission grant and revocation operations. The keywords are as follows:

Grant: authorization. Used to grant permissions to a user or role.

Revoke: undo. Used to reclaim the permissions of a user or role.

Create user: create a user.

Drop user: delete the user.

Second, the commonly used SQL syntax of MySql

Although the databases in circulation in the market follow the standard specification of SQL language, their usage is not exactly the same. Below, I will sort out the syntax of each part of the MySQL database to facilitate your memory. PS: the parentheses in "[]" will be used below to indicate optional.

1. Syntax for database creation, deletion and other operations

In the actual development, the data storage and management of any project corresponds to a database (data storage space) to avoid mixing with the data of other projects in the same database, so it will involve the creation of the database and other operations.

(1) create a database: create database [if not exists] dbName [default character set utf8] [collate utf8_general_ci | utf8_general_cs]

This statement specifies that the character set of the database is utf8. Utf8_general_ci means case-insensitive; utf8_general_cs means case-sensitive

(2) View the database: show databases

(3) Select database: use dbName

(4) Delete database: drop database dbName

(5) View the database character set: show variables like "% character%"

(6) modify the database character set: alter database dbName character set cName

(7) modify the command prompt interface display character set (valid for the current window): set name cName

2. The operation syntax of DDL language

(1) create a table structure:

Create table tName (colName1 type1,...,colNameN typeN) [engine=innodb] [default character set cName] [collate utf8_general_ci]

(2) display table structure: desc tName or show columns from tName

(3) add table field: alter table tName add colName Type

(4) Delete table field: alter table tName drop colName

(5) modify table field type / table field name

Alter table tName modify colName newType

Alter table tName change colName newColName newType

(6) modify the table name: alter table oldName rename newName

(7) clear the table data and retain the table structure: truncate table tName

(8) Delete table structure: drop table tName

(9) copy table structure: create table newName like oldName

3. The operation syntax of DML language

(1) insert data into the table:

Method 1: insert into tName (colName1,....,colNameN) values (value1,.,valueN)

Method 2: insert into tName values (value1,.,valueN); / * assign values according to the order of table fields * /

(2) modify the data in the table:

Update tName set colName1=value1,.colNameN=valueN [where condition]

(3) Delete data in the table: delete from tName [where condition]

4. The operation syntax of DQL language

Select colName1,.,colNameN from tName [where clause] [group by clause] [having clause] [order by clause] select has the meaning of choice, that is, which fields are selected for query and display.

This is the end of the content of "what is the purpose of MySQL database SQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.