MySQL statement arrangement and summary introduction

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

1. DCL (Database Control Language) statements: mainly completed by the two keywords GRANT and REVOKE
2. DDL (Database Definition Language) statements: mainly completed by four keywords: CREATE, ALTER, DROP and TRUNCATE
3. DML (Database Manipulation Language) statements: mainly completed by three keywords: INSERT, UPDATE and DELETE
4. Query statement: mainly completed by SELECT statement
5. Transaction control statements: mainly completed by the three keywords COMMIT, ROLLBACK and SAVEPOINT

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

1. DCL Statement

DCL statements are used to authorize and revoke user permissions. They can control permissions for different users, increase database security, and perform database maintenance. Generally, database administrators use the super user root to perform operations.

The MySQL permission command is grant, and the permission revocation command is revoke;

1. Grant authorization format:

grant permission list on library.table to username@'ip' identified by "password";

2. Revoke permission format:

revoke permission list on library.table from username@'ip';

2. DDL statements

Database objects:

Table, data dictionary, constraint, view, index, function, stored procedure, trigger

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

• The data types of the database can be found at: //www.jb51.net/article/55853.htm
• For database integrity constraints, please refer to: https://www.jb51.net/article/154000.htm

1.CREATE:

CREATE TABLE [schema name.] table name (
The column definitions in this table
);

eg

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

Note: View the table structure: DESCRIBE table name;

2.ALTER:

1) Add columns:

ALTER TABLE table name
ADD column columnName1 datatype [default expr] [FIRST|AFTER colName];
columnName1: the name of the newly added column;
datatype: data type;
default expr: integrity constraint;
FIRST|AFTER colName: Insertion position. By default, it is inserted in the last column. FIRST is in the first column. AFTER colName is inserted after the specified column.

eg

ALTER TABLE test
ADD column StuMajor VARCHAR(20) NOT NULL AFTER StuName;

2) Modify columns

ALTER TABLE table name CHANGE oldName newName datatype;

eg

ALTER TABLE test CHANGE StuBirth Birthday year;

3) Delete columns

ALTER TABLE table name DROP column columnName;

eg

ALTER TABLE test DROP column StuMajor;

4) Modify the table name

ALTER TABEL table name RENAME TO new table name;

eg

ALTER TABLE test RENAME TO student;

3.DROP

Delete a table
DROP TABLE table name;

eg

DROP TABLE student;

4.TRUNCATE

Deleting all data in a table but retaining its structure is called "truncation"
TRUNCATE TABLE table name;

eg

TRUNCATE TABLE student;

3. DML statements

1. INSERT

The standard SQL statement only allows one row of data to be inserted at a time, but MySQL extends it to allow multiple rows of data to be inserted at a time.

Insert a piece of data:

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

Insert multiple records:

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

eg

INSERT INTO student VALUES('001','Lisa',20,1997),('002','Rose',21,1996);

2.UPDATE

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

For example, add 1 to the age of all students older than 20.

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;

4. Query Statement

1. Single table query:

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

For example, select the students majoring in computer science and sort them in descending order by student ID, and only display the student names.

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

2. Multi-table query:

1) Simple outer join method

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

2) Self-connection: Sometimes you need to connect to yourself, which is called self-connection

eg

There is the following table temp

CREATE TABLE emp(
id INT AUTO_INCRETMENT PRIMARY KEY,
name VARCAHR(255),
managerId 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 manager
WHERE employee.managerId = manager.id;

This query uses a self-join to show the relationship between employees and managers:

• employee.name employee name, manager.name manager employee name and manager name are renamed, and employee name and manager name are displayed when displaying columns;
• FROM emp employee, emp manager Two identical tables need to be distinguished and given different names;
• WHERE employee.managerId = manager.id is the join condition

For more multi-table connections, please refer to:
https://www.jb51.net/article/154006.htm

5. Transaction Processing

1. A transaction is a logical execution unit consisting of one or several database operation sequences.

This series of operations must either be performed in full or abandoned in full. Programs and transactions are two completely different concepts. Generally speaking, a program may contain multiple transactions. In MySQL, there are multiple engines, the two most commonly used engines are: InnoDB and MyISAM. InnoDB supports transactions, while MyISAM does not. You can modify them in the config configuration file.

2. Four characteristics of transactions:

• Atomicity: A transaction is the smallest execution unit in an application.
• Consistency: The result of transaction execution must change the database from one consistent state to another consistent state. Consistency is ensured by atomicity
• Isolation: The execution of each transaction does not interfere with each other.
• Durability: Also known as persistence, it means that once a transaction is committed, any changes made to the data are saved in the physical database.

These four characteristics are also called ACID properties

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

• DML statements operate on data
• There is only one DDL and one DCL statement each, because both DDL and DCL statements commit the transaction.

4. Transaction submission:

• Show commit: commit
• Automatic commit: DDL/DCL statements

MySQL turns off transactions (auto commit) by default. By default, when a user enters a DML statement, the operation will be committed. To turn on transactions, you can set auto commit with the following statement:

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

5. Rollback of transactions

If any database operation contained in the transaction fails, the transaction will be rolled back, and all operations in the transaction will become invalid. Two ways:

• Display rollback: rollback
• Automatic rollback: system error or forced exit

6. Example:

If you just want to temporarily start a transaction, you can use: start transaction or begin to start a temporary transaction. DML statements after it will not be executed immediately until the transaction is committed or rolled back.

eg1

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 statement contains the inserted data. However, if you execute the statement in another command line window at this time, you will not see the above three data. This reflects the isolation of transactions. These three data are not actually written to the physical database.

After the rollback operation is executed, the three data after begin cannot be seen in the result of the query statement ②

eg2

INSERT INTO student VALUES(NULL,'001','aaa');
INSERT INTO student VALUES(NULL,'002','bbb');
SAVEPOINT p;
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 midpoint, which can be used to set the rollback position. The query statement result at ① contains the results of three inserted data, but the query result at ② does not contain the data inserted after the midpoint p. It is important to note that a rollback back to the midpoint does not end the transaction.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links