Running away from stores? Not rollback? MySQL creates and manages tables, modifies empty tables, MySQL8 features DDL atomization, complete and detailed collection

1. Basic knowledge

1.1 A procedure for data storage
Storing data is the first step in processing data. Data can only be stored correctly for efficient processing and analysis. Otherwise, it can only be a mess, unable to start. So how can we store all kinds of business-related and complex data in an orderly and efficient way? In MySQL, a complete data stored procedure consists of four steps: creating a database, validating fields, creating a data table, and inserting data.

Do we need to create a database first, not a table directly?
Because at the system architecture level, MySQL database systems are database servers, databases, data tables, rows and columns of data tables from large to small.

1.2 Identifier Naming Rules
1. Database and table names must not exceed 30 characters, and variable names are limited to 29.
(2) Must contain only A-Z, a-z, 0-9, _ There are 63 characters in total.
(3) Do not include spaces between object names such as database name, table name, field name, etc.
(4) In the same MySQL software, the database cannot have the same name; Tables cannot have duplicate names in the same library; Fields cannot have duplicate names in the same table.
You must ensure that your fields do not conflict with reserved words, database systems, or common methods. If you insist, use `(emphasis) in your SQL statement.

1.3 Data types in MySQL

Among them, several commonly used types are described as follows:

2. Create and manage databases

2.1 Create a database
Create a database

CREATE DATABASE Database Name;

Create a database and specify a character set

CREATE DATABASE Database Name CHARACTER SET character set;

Determine if the database already exists and create it if it does not (recommended)

CREATE DATABASE IF NOT EXISTS Database Name;

Note: DATABASE cannot be renamed. Some visualizers can be renamed to create new libraries, copy all tables to new libraries, and delete old libraries.

2.2 Using databases
View all current databases

SHOW DATABASES; #There is an S that represents multiple databases

View the database currently in use

SELECT DATABASE(); #Using a global function in mysql

View all tables under the specified library

SHOW TABLES FROM Database Name;

View database creation information

SHOW CREATE DATABASE Database Name;

Use/Switch Database

USE Database Name;

Note: To manipulate tables and data, you must first state which database you are operating on, or you must add a database name to all objects.

2.3 Modify the database
Change database character set

ALTER DATABASE Database Name CHARACTER SET character set; #For example: gbk, utf8, etc.

2.4 Delete database

DROP DATABASE IF EXISTS Database Name;

3. Create tables

3.1 Creation Method 1

CREATE TABLE [IF NOT EXISTS] Table Name(
Field 1, data type [constraint condition] [Default value],
Field 2, data type [constraint condition] [Default value],
Field 3, data type [constraint condition] [Default value],
......
[Table Constraints]
);
-- Create Table
CREATE TABLE IF NOT EXISTS emp (
-- int type
emp_id INT,
-- Save up to 20 Chinese and English characters
emp_name VARCHAR(20),
-- No more than 15 digits in total
salary DOUBLE,
-- Date type
birthday DATE
);
DESC emp;


3.2 Creation Mode 2
Combine creating tables with inserting data using the AS subquery option

Specified columns correspond to columns in a subquery
Define columns by column name and default

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- Created emp2 Is an empty table
CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

3.3 View data table structure
Once you have created a data table in MySQL, you can view the structure of the data table. MySQL supports the use of DESCRIBE/DESC statements to view the data table structure, and SHOW CREATE TABLE statements to view the data table structure.
The grammar format is as follows:

SHOW CREATE TABLE Table Name;

4. Modify tables

Modifying tables refers to modifying the structure of data tables that already exist in the database. Using the ALTER TABLE statement, you can:
1. Add columns to existing tables
(2) Modify columns in existing tables
(3) Delete columns from existing tables
(4) Rename columns in existing tables

4.1 Append a column
The grammar format is as follows:

ALTER TABLE Table Name ADD [COLUMN] Field name field type [FIRST|AFTER Field name];

Give an example:

ALTER TABLE dept80
ADD job_id varchar(15);


4.2 Modify a column
You can modify the column's data type, length, default value, and location.
Modify the syntax format of the field data type, length, default value, and location as follows:

ALTER TABLE Table Name 
MODIFY [COLUMN] Field Name 1 Field Type [DEFAULT Default value][FIRST|AFTER Field Name 2];

Give an example:

ALTER TABLE dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

4.3 Rename a column
The grammar format is as follows:

ALTER TABLE Table Name 
CHANGE [column] Column Name New Column Name New Data Type;

Give an example:

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

4.4 Delete a column
The syntax format for deleting a field in a table is as follows:

ALTER TABLE Table Name 
DROP [COLUMN]Field name;

Give an example:

ALTER TABLE dept80
DROP COLUMN job_id;

5. Rename Table

Mode 1: Use RENAME

RENAME TABLE emp
TO myemp;

Mode 2:

ALTER table dept
RENAME [TO] detail_dept; -- [TO]Can be omitted

6. Delete Table

In MySQL, when a data table is not associated with any other data table, you can delete the current data table directly. The data and structure are deleted, all related running transactions are committed, and all related indexes are deleted. Grammar Format:

DROP TABLE [IF EXISTS] Data Table 1 [, Data Table 2, ..., Data sheet n];

Give an example:

DROP TABLE dept80;

DROP TABLE statements cannot be rolled back.

7. Empty table

TRUNCATE TABLE statement: Deletes all data in a table, frees up storage space for the table.
Give an example:

TRUNCATE TABLE detail_dept;

TRUNCATE statements cannot be rolled back, whereas DELETE statements can be used to delete data.

SET autocommit = FALSE;
DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

[Reference] TRUNCATE TABLE is faster than DELETE, uses fewer system and transaction log resources, but TRUNCATE is non-transactional and does not trigger TRIGGER, which can cause accidents, so it is not recommended to use this statement in development code. Description: TRUNCATE TABLE is functionally identical to a DELETE statement without a WHERE clause.

8. New MySQL8 Feature-Atomicization of DDL

In MySQL version 8.0, the DDL of InnoDB tables supports transactional integrity, where DDL operations either succeed or roll back. DDL operation rollback log written to data dictionary data dictionary table mysql.innodb_ddl_log, which is hidden and not visible through show tables, for rollback operations. By setting parameters, the DDL operation log can be printed out to the MySQL error log.

Create databases and data tables in MySQL version 5.7 and MySQL version 8.0 respectively. The results are as follows:

CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);
SHOW TABLES;

(1) In MySQL version 5.7, the test steps are as follows: Delete data tables Book 1 and book2, and the results are as follows:

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

Query the name of the table in the database again, and the results are as follows:

mysql> SHOW TABLES;
Empty set (0.00 sec)

As you can see from the results, although the deletion operation was wrong, the data table book1 was deleted.

(2) In MySQL version 8.0, the test steps are as follows: Delete data tables book1 and book2, and the results are as follows:

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

Query the name of the table in the database again, and the results are as follows:

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1 |
+------------------+
1 row in set (0.00 sec)

As you can see from the results, the data table book1 was not deleted.

Tags: Database MySQL SQL

Posted on Tue, 30 Nov 2021 22:35:36 -0500 by rsnell