One transaction

Core: success or failure
Atomicity

Both succeed or fail

Consistency

The data integrity before and after the transaction should be consistent, 1000
Principle: ACID
1. Atomicity
Either they succeed or they fail;

2. Consistency
Data integrity before and after transactions should be consistent;

3. Durability
- transaction commit
Once a transaction is committed, it is irreversible and persistent in the database!

4. Isolation
The isolation of transactions is that when multiple users access the database concurrently, the transactions opened by the database for each user cannot be interfered by the operation data of other transactions, and multiple concurrent transactions should be isolated from each other.

5. Some problems caused by isolation

1. Dirty reading:
   A transaction reads uncommitted data from another transaction.

2. Non repeatable reading:
   Reading a row of data in a table in a transaction results in different results. (it's not necessarily a mistake, but it's wrong in some situations.)

3. Virtual reading (unreal reading)
   It refers to reading data inserted by other transactions in one transaction, resulting in inconsistent reading.

Execute transaction:

-- mysql Transaction auto commit is enabled by default
SET autocommit = 0 /* Close */
SET autocommit = 1 /* On (default) */

-- Handle transactions manually
SET autocommit = 0 -- Turn off auto submit

-- Transaction opening
START TRANSACTION  -- Mark the beginning of a transaction, and sql All in the same transaction

INSERT xx
INSERT xx

-- Commit: persistent (successful!)
COMMIT
-- Rollback: back to the original (failed!)
ROLLBACK

-- End of transaction
SET autocommit = 1 -- Turn on auto submit

-- understand
SAVEPOINT Save roll call -- Set a savepoint for a transaction
ROLLBACK TO SAVEPOINT Save roll call -- Rollback to savepoint
RELEASE SAVEPOINT Save roll call -- Undo savepoint

Simulated transfer transaction

-- Transfer accounts
-- 1,SQL implement    A to B  Transfer accounts     A 1000 ---> B 500  
-- 2,SQL implement    B Received A Money    A 500  ---> B 500
-- 3.The total amount is 1000 yuan
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- Simulated transfer: transaction
SET autocommit = 0; -- Turn off auto submit
START TRANSACTION -- Open a transaction (a group of transactions)

UPDATE account SET money=money-500 WHERE `name` = 'A' -- A Minus 500
UPDATE account SET money=money+500 WHERE `name` = 'B' -- A Plus 500

COMMIT; -- Commit the transaction and it will be persisted!
ROLLBACK;  -- RollBACK

SET autocommit = 1; -- Restore defaults

II. Authority and backup

1. visualization

2. Command operation

-- Create user CREATE USER User name IDENTIFIED BY 'Password'
CREATE USER panghu IDENTIFIED BY '123456'

-- Change password (change current user password)
SET PASSWORD = PASSWORD('123456')

-- Change password (change specified user password)
SET PASSWORD FOR panghu = PASSWORD('123456')

-- rename RENAME USER Original name TO New name
RENAME USER panghu TO erdan

-- User authorization ALL PRIVILEGES All permissions, library.surface
-- ALL PRIVILEGES I can do anything but authorize others   
GRANT ALL PRIVILEGES ON *.* TO erdan

-- Query authority
SHOW GRANTS FOR erdan -- View permissions for the specified user
SHOW GRANTS FOR root@localhost  

-- ROOT User rights: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- Revocation of authority REVOKE Which permissions, which library to revoke and to whom to revoke
REVOKE  ALL PRIVILEGES ON *.* FROM erdan

-- delete user
DROP USER erdan

3. backup
1. Ensure that important data is not lost or transferred;
2.MySQL database backup mode

• Direct copy of physical files
• Export manually in Sqlyog, a visualizer
  
  Using the command line to export mysqldump

# mysqldump -h host - u user name - p password database table name > physical disk location / file name
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysqldump -h host - u user name - p password database table 1 Table 2 Table 3 > physical disk location / file name
mysqldump -hlocalhost -uroot -p123456 school student >D:/b.sql

# mysqldump -h host - u user name - p password database > physical disk location / file name
mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql

# Import
# Switch to the specified database when logging in
# source backup file
source d:/a.sql

mysql -u User name -p Password library name< Backup file

Three index

MySQL's official definition of Index is: Index is a data structure that helps MySQL obtain data efficiently. Extract the sentence trunk, you can get the essence of Index: Index is data structure.
**1. Classification of index**

In a table, there can only be one primary key index and multiple unique indexes

• PRIMARY KEY index
  • Unique identification. The primary key cannot be duplicate. Only one column can be used as the primary key
• UNIQUE KEY
  • Avoid duplicate columns. Unique indexes can be duplicated. Multiple columns can identify bit unique indexes
• General index (KEY/INDEX)
  • By default, index. Key key to set
• Full text
  • Only under a specific database engine, MyISAM
  • Quickly locate data
    Basic grammar

-- Use of index
-- 1,Index fields when creating tables
-- 2,Add index after creation
-- Show all index information
SHOW INDEX FROM student

-- Add a full-text index (index name) column name
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);

-- EXPLAIN Analysis sql Status of implementation 
EXPLAIN SELECT * FROM student; -- Non full text index

EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('Liu');

2. Test index

CREATE TABLE `app_user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT '' COMMENT 'User nickname',
  `email` VARCHAR(50) NOT NULL COMMENT 'User mail box',
  `phone` VARCHAR(20) DEFAULT '' COMMENT 'Cell-phone number',
  `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT 'Sex (0:Male; 1: female)',
  `password` VARCHAR(100) NOT NULL COMMENT 'Password',
  `age` TINYINT(4) DEFAULT '0' COMMENT 'Age',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app User table'

-- Insert 1 million data55.176 sec
DELIMITER $$  -- Must write before writing function, flag
CREATE FUNCTION mock_data() 
RETURNS INT
BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO
     INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('user',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i = i+1;
   END WHILE;
   RETURN i;
END;
SELECT mock_data();

SELECT * FROM app_user WHERE `name` = 'User 9999'; --  0.993 sec
SELECT * FROM app_user WHERE `name` = 'User 9999'; --  1.098 sec
SELECT * FROM app_user WHERE `name` = 'User 9999'; --  0.788 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = 'User 9999';

SELECT * FROM student

-- id _ Table name _ Field name
-- CREATE INDEX Index name on surface(field)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name` = 'User 9999'; -- 0.001 sec
SELECT * FROM app_user WHERE `name` = 'User 9999'; 
EXPLAIN SELECT * FROM app_user WHERE `name` = 'User 9999';

When the index is small, the users are not large, but when the index is big, the difference is very obvious~
3. Index principle

• The more indexes, the better
• Do not index process change data
• Small data tables do not need to be indexed
• The index is generally added to the fields commonly used for query!

Data structure of index:
Index of Hash type~
Btree: default data structure of InnoDB~

Four specification

First normal form (1NF)
Atomicity: ensure that each column is indivisible

Second normal form (2NF)
Premise: meet the first paradigm
Each table only describes one thing

Third normal form (3NF)
Premise: satisfying the first and second paradigms
The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly.
(standardize the design of database)

Specification and performance issues
The associated query cannot have more than three tables

• Consider the needs and goals of commercialization, (cost, user experience!) Database performance is more important
• When it comes to specification performance, we need to consider the specification properly!
• Deliberately add some redundant fields to some tables. (from multi table query to single table query)
• Intentionally add some calculation columns (query from large data to small data: index)