Java MySQL database programming Chapter 5: transactions, views, indexes, backup and recovery, mysql Database Programming

Java MySQL database programming Chapter 5: transactions, views, indexes, backup and recovery, mysql Database Programming
Chapter 5: transactions, views, indexes, backup and recovery

1. Transactions refer to bundling a series of data operations into a whole for unified management.

 

 

I. Transaction Syntax:

 

1. Start the transaction

Syntax: BEGIN or start trainsaction;

 

2. Commit a transaction: All data updates executed will be permanently stored in the database data file.

Syntax: COMMIT;

 

3. rollback transaction: roll back the data status to the beginning of the transaction

Syntax: ROLLBACK;

 

 

4. Set automatic submission on or off

The automatic submission mode is started by default in MySQL.

Off: Value 0: Disable Automatic submission

Syntax: SET autocommit = 0;

 

On: value 1: Enable automatic submission

Syntax: SET autocommit = 1;

 

 

2. Follow the following principles when writing transactions:

 

• The transaction should be as short as possible

 

• Minimize the amount of data accessed in transactions

 

• Do not use transactions when querying data

 

• Do not wait for user input during Transaction Processing

 

What is a view?

View is the select query stored in the database

 

3. Create and use a view

 

1. Use SQL statements to create a view

Syntax: create view name

AS

<SELECT statement>;

Note: In SQL statement naming conventions, views are generally named in view_xxx or v_xxx.

Use SQL statements to delete views

Syntax: drop view [if exists] VIEW name;

 

2. View data using SQL statements

Syntax: SELECT Field 1, Field 2 ,..... FROM view_name;

 

3. When using the view, pay attention

Multiple tables can be used in each view. Similar to queries, one view can be nested with another view. However, it is better not to exceed three layers.

Adding, updating, and deleting View data directly references the data in a table. When View data comes from multiple tables, adding and deleting data is not allowed.

 

 

What is an index?

According to the index storage type, indexes can be divided into B-tree indexes (BTREE) and HASH indexes by using indexes.

 

Iv. Index

Index category:

General Index, unique index, primary key index, full-text index, spatial index

 

Common Index

The basic index type in MySQL during normal indexing. duplicate values and null values can be inserted in the columns that define the index. Its only task is to speed up data access.

 

Unique Index

The unique index does not allow two rows to have the same index value. A null value is allowed for columns that create a unique index.

 

Primary Key Index

The primary key index requires that each value in the primary key be unique and not empty.

Composite Index

You can combine multiple columns as an index, which is called a composite index.

 

Full-text index

Full-text index supports full-text search of values on the columns that define the index. duplicate values and null values can be inserted in these index columns.

 

V. Index syntax

1. Create an index

 

Syntax: CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

 

ON table_name (column_name [length] ......);

 

(1) UNIQUE | FULLTEXT | SPATIAL: UNIQUE index, full-text index, and SPATIAL index, which are optional parameters.

(2) Index_name: Specify the index name.

(3) Table_name: Specifies the name of the table for which the index is created.

(5) column_name: Specifies the column for which an index is to be created.

(6) Length: Specify the index Length

 

2. delete an index

Syntax: drop index index_name ON table_name;

 

(1) When a table is deleted, all indexes of the table will be deleted at the same time.

(2) When deleting a column in a table, if the column to be deleted is an index component, the column will also be deleted from the index.

 

3. View Indexes

 

Syntax: show index from table_name;

 

(1) Add "\ G" after the query statement to represent the result set in columns.

 

 

Vi. backup and recovery

 

Mysqldump command backup database

1. mysqldump Command Format

Syntax: mysqldump-u username-h host-ppassword dbname [tbname1 [, tbname2. ......]

> Filename. SQL

 

Resolution:

Username: indicates the user name.

Host indicates the Host Name of the login user. If the Host is a Host, it can be omitted.

Password indicates the logon Password.

Dbname is the database to be backed up

Tbname is the data table to be backed up. You can specify multiple tables. This option is optional. If you back up the entire database, this option is omitted.

Filename. SQL indicates the name of the backup file.

 

2. mysql command to restore the database

 

Syntax: mysql-u username-p [dbname] <filename. SQL

 

Resolution:

Username indicates the user

Dbname Database Name

Filename. SQL is the file backed up by the database.

Note: Before executing this statement, you must create a new database on the MySQL server. If no new database exists, an error will occur when restoring the database.

 

 

3. Mysql Command recovery database command in DOS environment:

 

Syntax: source filename;

Data Recovery through copying files