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

This article is to share with you what the MySQL sentence has. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

SQL (Structured Query Language) statement, that is, structured query language, is a standard language for operating and retrieving relational databases. SQL statements are generally divided into the following categories:

DCL (Database Control Language, data control language) statement: mainly completed by two keywords: GRANT and REVOKE

DDL (Database Definition Language, data definition language) statement: mainly completed by four keywords: CREATE, ALTER, DROP and TRUNCATE

DML (Database Manipulation Language, data manipulation language) statement: mainly completed by three keywords: INSERT, UPDATE and DELETE

Query statement: mainly completed by SELECT statement

Transaction control statements: mainly completed by COMMIT, ROLLBACK and SAVEPOINT keywords

Note: SQL statements are not case-sensitive, so create and CREATE are the same

I. DCL statement

DCL statement is the operation of authorization and authorization recovery for users, which can control the permissions of different users, increase the security of the database, and maintain the database. Database administrators generally use superuser root to operate.

The permission command of MySQL is grant, and the command of permission revocation is revoke.

1.grant authorization format:

Grant permission list on library. Table to username @ 'ip' identified by "password

2.revoke reclaim permission format:

Revoke permission list on library. Table from user name @ 'ip'

II. DDL statement

Database objects:

Table (table), data dictionary, constraint (constraint), view (view), index (index), function (function), stored procedure (procedure), trigger (trigger)

The CREATE, ALTER, and DELETE keywords are used to create, modify, and delete database objects, respectively. Here we use the most frequently used table operations as an example

For the data types of the database, please refer to: / / www.jb51.net/article/55853.htm

For more information on database integrity constraints, please see: https://www.jb51.net/article/154000.htm

1.CREATE:

CREATE TABLE [schema name.] Table name (

Column definitions in this table

);

E.g.

CREATE TABLE test (StuId VARCHAR (8) PRIMARY KEY,StuName VARCHAR (30) NOT NULL,StuAge SMALLINT NOT NULL,StuBirth DATETIME)

Note: view table structure: DESCRIBE table name

2.ALTER:

1) add columns:

ALTER TABLE table name

ADD column columnName1 datatype [default expr] [FIRST | AFTER colName]

ColumnName1: newly added column name

Datatype: data typ

Default expr: integrity constraint

FIRST | AFTER colName: insert position. Default is insert in the last column, FIRST is in the first column, and AFTER colName is inserted after the specified column.

E.g.

ALTER TABLE testADD column StuMajor VARCHAR (20) NOT NULL AFTER StuName

2) modify the column

ALTER TABLE table name CHANGE oldName newName datatype

E.g.

ALTER TABLE test CHANGE StuBirth Birthday year

3) Delete columns

ALTER TABLE table name DROP column columnName

E.g.

ALTER TABLE test DROP column StuMajor

4) modify the table name

ALTER TABEL table name RENAME TO new table name

E.g.

ALTER TABLE test RENAME TO student

3.DROP

Delete tabl

DROP TABLE table name

E.g.

DROP TABLE student

4.TRUNCATE

Delete all data in the table but retain the structure of the table, called "truncation"

TRUNCATE TABLE table name

E.g.

TRUNCATE TABLE student

III. DML statement

1.INSERT

Standard SQL statements allow only one piece of data to be inserted at a time, but MySQL extends it to insert more than one piece of data at a time

Insert a piece of data:

INSERT INTO table name VALUES (value1, value2,...)

Insert multiple pieces of data:

INSERT INTO table name VALUES (value1,value2,...), (value1,value2,...), (value1,value2,...)

E.g.

INSERT INTO student VALUES ('001century ~ Lisa ~ 20 ~ 1997), (' 002 ~ Roseforth ~ 21 ~ 1996)

2.UPDATE

UPDATE table name SET COLUMN1 = VALUE1 [, COLUMN2 = VALUE2].

[WHERE CONDITION]

E.g. The age of all students over the age of 20 + 1

UPDATE student SET StuAge = StuAge+1 WHERE StuAge > 20

3.DELETE

DELETE FROM table name [WHERE CONDITION]

E.g. Delete all student information born in 1997

DELETE FROM student WHERE Birthday = 1997

four。 Query statement

1. Single table query:

SELECT COLUMN1, COLUMN2...FROM data source [WHERE CONDITION] [GROUP BY columnName] [ORDER BY columnName DESC | ASC]

E.g. Select the computer science majors and arrange them in descending order according to the student numbers, showing only the names of the students.

SELECT StuName FROM student WHERE StuMajor = 'CS'ORDER BY StuId DESC

two。 Multi-table query:

1) simple external connection

SELECT VALUE1 [, VALUE2]... FROM tableName1,tableName2WHERE tableName1.column1 = tableName2.column2 [AND...]; WHERE is followed by join conditions and query conditions

2) self-connection: sometimes you need to connect with yourself, which is called self-connection.

E.g.

There is the following table temp

CREATE TABLE emp (id INT AUTO_INCRETMENT PRIMARY KEY,name VARCAHR, mangerId INT,FOREIGN KEY (managerId) references temp (id))

There are four records.

Id name managerId

1 aaa null

2 bbb 1

3 ccc 1

4 ddd 1

Query the table:

SELECT employee.id, employee.name employee name, manager.name manager name FROM emp employee, emp managerWHERE employee.managerId = manager.id

The query statement uses self-join to display the relationship between the employee and the manager, where:

Employee.name employee name, manager.name manager name employee name and manager name are renamed, showing the employee name and manager name when the column is displayed

FROM emp employee and emp manager two identical tables need to be distinguished and given different names.

WHERE employee.managerId = manager.id is the connection condition

For more multi-table connections, please refer to:

Https://www.jb51.net/article/154006.htm

five。 Transaction processing

1. A transaction is a logical execution unit consisting of a sequence of one or more database operations.

This series of operations are either performed or abandoned. Program and transaction are two completely different concepts. In general, a program may contain multiple transactions. In MySQL, there are many engines, the two most commonly used: InnoDB and MyISAM, where InnoDB supports transactions, while MyISAM does not, which can be modified in the config configuration file.

two。 Four characteristics of a transaction:

Atomicity: a transaction is the smallest unit of execution in an application.

Consistency: the result of transaction execution must change the database from one consistent state to another. Consistency is guaranteed by atomicity.

Isolation: transaction execution does not interfere with each other.

Durability: also known as Persistence, which means that once a transaction commits, any changes made to the data are saved to the physical database.

These four features are also called ACID.

3. The transaction of the database consists of a set of DML statements, a DDL statement and a DCL statement

The DML statement operates on the data

DDL and DCL each have only one, because both DDL and DCL statements commit the transaction

4. Commit of transaction:

Show submission: commit

Auto-commit: DDL/DCL statement

MySQL turns off the transaction (autocommit) by default. By default, the user enters a DML statement to commit the operation. To start the transaction, you can set the autocommit by the following statement

SET AUTOCOMMIT = {0 | 1} 0: turn off autocommit (open transaction), 1: enable autocommit (close transaction)

5. Rollback of transactions (rollback)

Any one of the database operations contained in the transaction fails to execute and rolls back the transaction, invalidating all operations performed in the transaction. There are two ways:

Show rollback: rollback

Automatic rollback: system error or forced exit

6. Example:

If you only temporarily open a transaction, you can start a temporary transaction through: start transaction or begin, the DML statements that follow it will not be executed immediately, and the transaction will not end until the transaction is committed or rolled back.

E.g.1

BEGIN;INSERT INTO student VALUES (NULL,'001','aaa'); INSERT INTO student VALUES (NULL,'002','bbb'); INSERT INTO student VALUES (NULL,'003','ccc'); SELECT * FROM student; ① ROLLBACK;SELECT * FROM student; ②

The result of the ① query contains the inserted data, but if you execute the statement in another command line window at this time, you will not see the above three pieces of data, reflecting the isolation of the transaction. These three pieces of data are not actually written to the physical database.

After performing the rollback operation, the three pieces of data after begin are not seen in the results of the query statement of ②.

E.g.2

INSERT INTO student VALUES (NULL,'001','aaa'); INSERT INTO student VALUES (NULL,'002','bbb'); SAVEPOINT pten insert INTO student VALUES (NULL,'003','ccc'); SELECT * FROM student; ① ROLLBACK TO p * select * FROM student; ②

MySQL also provides the keyword SAVEPOINT to set the intermediate point, which can set the location of the rollback. The query statement result at ① contains three results of inserting data, but the query result at ② does not contain the data inserted after the intermediate point p. It is important to note that a rollback back to the midpoint does not end the transaction.

Thank you for reading! This is the end of this article on "what are the MySQL sentences?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.