6. Services

Transaction is to execute a group of SQL statements in the same batch.

ACID principle:

• Atomicity

All operations in the whole transaction are either completed or not completed. It is impossible to stagnate in an intermediate link. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction, as if the transaction had never been executed.

• Consistency

A transaction can encapsulate state changes (unless it is read-only). Transactions must always keep the system in a consistent state, no matter how many concurrent transactions there are at any given time. In other words, if multiple transactions are concurrent, the system must also operate as a serial transaction. Its main feature is protection and invariance. Taking the transfer case as an example, assuming that there are five accounts, and the balance of each account is 100 yuan, the total amount of the five accounts is 500 yuan. If multiple transfers occur between the five accounts at the same time, no matter how many are concurrent, for example, 5 yuan is transferred between accounts a and B and 10 yuan is transferred between accounts C and D, If 15 yuan is transferred between B and E, the total amount of the five accounts should still be 500 yuan, which is protective and invariable.

• Isolated

Isolate state execution transactions so that they appear to be the only operation performed by the system at a given time. If two transactions run at the same time and perform the same functions, the isolation of transactions will ensure that each transaction is considered to be the only one using the system in the system. This attribute is sometimes called serialization. In order to prevent confusion between transaction operations, requests must be serialized or serialized so that only one request is used for the same data at the same time.

Dirty read: one transaction reads uncommitted data from another transaction.

Non repeatable read: read a row of data in a table within a transaction.

Phantom reading (virtual reading): data inserted by other transactions is read in a food, resulting in inconsistent reading.

• Persistent

After the transaction is completed, the changes made by the transaction to the database will be permanently saved in the database and will not be rolled back. The data after the transaction ends will not be lost due to external reasons.

-- use set Statement to change the auto submit mode
SET autocommit = 0;   /*close*/
SET autocommit = 1;   /*open*/
-- be careful:
---  1.MySQL The default is auto submit
---  2.Auto commit should be turned off first when using transactions

-- Start a transaction,Mark the starting point of the transaction
START TRANSACTION  
-- Commit a transaction to the database
COMMIT 
-- Rollback transaction,The data returns to the initial state of this transaction
ROLLBACK
 
-- reduction MySQL Automatic submission of database
SET autocommit =1;
 
-- Save point
SAVEPOINT Save point name -- Set a transaction savepoint
ROLLBACK TO SAVEPOINT Save point name -- Rollback to savepoint
RELEASE SAVEPOINT Save point name -- Delete savepoint

7. Index

• Improve query speed
• Ensure data uniqueness
• The connection between tables can be accelerated to realize the referential integrity between tables
• When using grouping and sorting clauses for data retrieval, the time of grouping and sorting can be significantly reduced
• Full text search fields for search optimization

1. Primary key index

Primary key: an attribute group can uniquely identify a record

characteristic:

• The most common index type
• Ensure the uniqueness of data records
• Determine the location of specific data records in the database

2. Unique index

Function: avoid duplicate values in a data column in the same table

Difference from primary key index

• There can only be one primary key index
• There may be more than one unique index

3. General index

Function: quickly locate specific data

be careful:

• Both index and key keywords can set the general index
• Fields that should be added to query criteria
• Too many general indexes should not be added, which will affect the operation of data insertion, deletion and modification

ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

4. Full text index

Function: quickly locate specific data

be careful:

• Can only be used for datasheets of type MyISAM
• Can only be used for char, varchar, text data column types
• Suitable for large data sets

/*
#Method 1: when creating a table
    　　CREATE TABLE Table name(
                Field name 1 data type [integrity constraint...],
                Field name 2 data type [integrity constraint...],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [Index name] (field name [(length)] [ASC |DESC])
                );
#Method 2: CREATE creates an index on an existing table
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  Index name
                     ON Table name (field name [(length)] [ASC |DESC]);
#Method 3: ALTER TABLE creates an index on an existing table
        ALTER TABLE Table name add [unique | Fulltext | spatial] index
                             Index name (field name [(length)] [ASC |DESC]);
                            
                            
#Delete index: DROP INDEX index name ON table name;
#Delete primary key index: ALTER TABLE table name DROP PRIMARY KEY;
#Display index information: SHOW INDEX FROM student;
*/
 
/*Add full text index*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
 
/*EXPLAIN : Analyze SQL statement execution performance*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
 
/*Use full-text indexing*/
-- Full text search passed MATCH() Function complete.
-- Search string as against() The parameters of are given. The search is performed ignoring the case of letters. For each record row in the table, MATCH() Returns a correlation value. That is, between the search string and the record line MATCH() The similarity scale between the text of the column specified in the list.
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
 
/*
Before we start, let's talk about the full-text index version, storage engine and data type support
MySQL 5.6 In previous versions, only MyISAM storage engine supported full-text indexing;
MySQL 5.6 And later versions, MyISAM and InnoDB storage engines support full-text indexing;
Full text indexes can be created only when the data types of fields are char, varchar, text and their series.
When testing or using full-text indexing, first check whether your MySQL version, storage engine and data type support full-text indexing.
*/

5. Precautions

• The more indexes, the better
• Do not index data that changes frequently
• It is recommended not to add indexes to tables with small amount of data
• The index should generally be added to the field of the search criteria

6. Data structure

When creating the above index, we can specify the index type for it, which can be divided into two categories

• hash type index: single query is fast and range query is slow
• btree type index: b + tree, with more layers, the data volume increases exponentially (innodb supports it by default)

Different storage engines support different index types:

• InnoDB supports transactions, row level locking, B-tree, full text and other indexes, but does not support Hash indexes;
• MyISAM does not support transactions, supports table level locking, supports B-tree, full text and other indexes, and does not support Hash indexes;
• Memory does not support transactions, supports table level locking, supports B-tree, Hash and other indexes, and does not support full text indexes;
• NDB supports transactions, row level locking, Hash indexes, but not B-tree, full text and other indexes;
  Archive does not support transactions, supports table level locking, and does not support B-tree, Hash, full text and other indexes;

8. Authority management

/* User and rights management */ ------------------
User information table: mysql.user
 
-- Refresh permissions
FLUSH PRIVILEGES
 
-- Add user  CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER user name IDENTIFIED BY [PASSWORD] password(character string)
    - Must have mysql Global of database CREATE USER Permission, or possession INSERT jurisdiction.
    - Only users can be created and cannot be granted permissions.
    - User name, note the quotation marks: for example: 'user_name'@'192.168.1.1'
    - Passwords also need quotation marks, and pure digital passwords also need quotation marks
    - To specify a password in plain text, ignore it PASSWORD key word. To specify the password as PASSWORD()The mixed value returned by the function must contain keywords PASSWORD
 
-- Rename User   RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
 
-- Set password
SET PASSWORD = PASSWORD('password')    -- Set password for current user
SET PASSWORD FOR user name = PASSWORD('password')    -- Sets the password for the specified user
 
-- delete user  DROP USER kuangshen2
DROP USER user name
 
-- Assign permissions/Add user
GRANT Permission list ON Table name TO user name [IDENTIFIED BY [PASSWORD] 'password']
    - all privileges Indicates all permissions
    - *.* All tables representing all libraries
    - Library name.The table name represents a table under a library
 
-- View permissions   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR user name
    -- View current user permissions
    SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();
 
-- revoking permission
REVOKE Permission list ON Table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user name    -- Revoke all permissions

-- Permission list
ALL [PRIVILEGES]    -- Set division GRANT OPTION All simple permissions except
ALTER    -- Allow use ALTER TABLE
ALTER ROUTINE    -- Change or cancel stored subroutines
CREATE    -- Allow use CREATE TABLE
CREATE ROUTINE    -- Create stored subroutines
CREATE TEMPORARY TABLES        -- Allow use CREATE TEMPORARY TABLE
CREATE USER        -- Allow use CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES. 
CREATE VIEW        -- Allow use CREATE VIEW
DELETE    -- Allow use DELETE
DROP    -- Allow use DROP TABLE
EXECUTE        -- Allows the user to run stored subroutines
FILE    -- Allow use SELECT...INTO OUTFILE and LOAD DATA INFILE
INDEX     -- Allow use CREATE INDEX and DROP INDEX
INSERT    -- Allow use INSERT
LOCK TABLES        -- Allow you to have SELECT Table usage of permissions LOCK TABLES
PROCESS     -- Allow use SHOW FULL PROCESSLIST
REFERENCES    -- Not implemented
RELOAD    -- Allow use FLUSH
REPLICATION CLIENT    -- Allows the user to ask for the address of the secondary or primary server
REPLICATION SLAVE    -- For replicated secondary servers (reading binary log events from the primary server)
SELECT    -- Allow use SELECT
SHOW DATABASES    -- Show all databases
SHOW VIEW    -- Allow use SHOW CREATE VIEW
SHUTDOWN    -- Allow use mysqladmin shutdown
SUPER    -- Allow use CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL sentence, mysqladmin debug Command; Allows you to connect (once), even if you have reached max_connections. 
UPDATE    -- Allow use UPDATE
USAGE    -- "Synonymous with "no permission"
GRANT OPTION    -- Permission granted
 
 
/* Table maintenance */
 
-- Analyze and store the keyword distribution of the table
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE Table name ...
-- Check one or more tables for errors
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- Defragment data files
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

9. Database backup

Database backup necessity:

• Ensure that important data is not lost
• Data transfer

MySQL database backup method:

• mysqldump backup tool
• Database management tools, manual export
• Directly copy database files and related configuration files

mysqldump client

Export from the command line.

effect:

• Dump database
• Collect database for backup
• Transfer data to another SQL server, not necessarily MySQL server

-- export
1. Export a table -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u user name -p Password library name table name > file name(D:/a.sql)
2. Export multiple tables -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u user name -p Password library name table 1 Table 2 Table 3 > file name(D:/a.sql)
3. Export all tables -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u user name -p Password library name > file name(D:/a.sql)
4. Export a library -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u user name -p password -B Library name > file name(D:/a.sql)
 
sure-w Carry backup conditions
 
-- Import
1. Logging in mysql In case of:-- source D:/a.sql
　　source  Backup file
2. Without logging in
　　mysql -u user name -p Password library name < Backup file

10. Standardized database design and three paradigms

Slightly. Then review the details.

Three paradigms:

• First normal form (1st NF)

  Ensure the atomicity of each column. If each column is the smallest non separable data unit, the first normal form is satisfied.

• Second normal form (2nd NF)

  Eliminate partial dependencies.

  Ensure that every column in the database table is related to the primary key.

  On the basis of meeting the first paradigm, each table is required to describe only one thing.

• Third paradigm (3rd NF)

  Eliminate delivery dependencies.

  If a relationship satisfies the second normal form, and all columns except the primary key do not depend on the primary key column, the third normal form is satisfied.

  The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly.

Relationship between normalization and performance

No more than three tables can be associated with the query.

• In order to meet some business goals, database performance is more important than normalized database

• At the same time of data standardization, we should comprehensively consider the performance of the database

• By adding additional fields to a given table, the time required to search for information is greatly reduced

• By inserting calculated columns into a given table, it is convenient to query

11.JDBC

1. Import jar package

2. Test database

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')

package com;
import java.sql.*;
public class jdbcFirstDemo {
    //Load mysql driver
    private static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
    //Database connection address
    private static final String URL = "jdbc:mysql://localhost:3306/jdbcstudy";//demo database
    //User name, replace it with your own user name. Here is the root user
    private static final String USER_NAME = "root";
    //Password, change to your own password, here is: 123456
    private static final String PASSWORD = "123456";
    public static void main(String[] args) throws Exception{
        //Load mysql driver class
        Class.forName(DRIVER_NAME);
        //Get database connection
        Connection connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
        //mysql query statement
        String sql = "SELECT * FROM users";
        PreparedStatement statement = connection.prepareStatement(sql);
        //The result set encapsulates all the returned results
        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
            //getInt() and so on can obtain specific types. If it is unclear, you can use getObject
            System.out.println("user name:" + rs.getObject("name"));
        }
        //Close connection
        rs.close();
        statement.close();
        connection.close();
    }
}

3. Database connection address

//mysql --3306
jdbc:mysql://Host address: port number / database name
//oralce --1521
jdbc:oracle:thin:@localhost:1521:sid

4.SQL execution object

//Object that executes SQL when Statement
statement.executeQuery(sql);//Query operation
statement.execute(sql);//Execute any SQL
statement.executeUpdate(sql);//Update insert delete operation

//When preparing statement
//? Placeholder instead of parameter
String sql="insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
//Precompiled SQL
PreparedStatement statement = connection.prepareStatement(sql);
//Assign parameters manually
statement.set(1,4);
satement.setString(2,"a");
satement.setString(3,"123");
satement.setString(4,"a");
satement.setDate(5,new java.sql.Date(new Date().getTime()));
//implement
state.excuteUpdate();

PrepareStatement: prevents SQL injection problems.

5.SQL injection

It means that the web application does not judge the legitimacy of the user input data or the filtering is not strict. The attacker can add additional SQL statements at the end of the query statements defined in advance in the web application and realize illegal operations without the knowledge of the administrator, so as to deceive the database server to execute unauthorized arbitrary queries, So as to further obtain the corresponding data information.

6. Database connection pool

Database connection - > release is a waste of system resources.

Pooling Technology: prepare some resources in advance and connect them when they come.

• Minimum number of connections
• maximum connection
• Wait timeout

Write a connection pool to implement an interface DataSource

• DBCP
• C3P0
• Druid

After using these database connection pools, we don't need to write code to connect to the database in the project development.

Guide package required. The specific use process can be found at will. It's not written here. It may be more convenient to use maven in the future.