1 Introduction

1. Database classification

• Relational database (SQL)
  • MySQL,Oracle,sql Server,SQLite.
  • Data is stored through the relationship between tables and between rows and columns.
• Non relational database (NoSQL)
  • Redis,MongDB
  • Object storage is determined by the properties of the object itself.

1. DBMS database management system

• Database management software, scientific and effective management of our data, maintenance and access to data.

1.1 environmental installation

1.1.1 Mysql installation

1. Unzip the package to my installation directory. D:\03Enviroment\05mysql\01install
2. Modify the environment variable path.

1. Create a new core configuration file my. Under D: \ 03environment \ 05mysql \ 01install \ mysql-8.0.19 ini.

# I missed a "d" and reported an error when I entered the password option.
[mysqld]
# The catalogue should be changed to its own; Add "\" to the following address
basedir=D:\03Enviroment\05mysql\01install\mysql-8.0.19\
# Do not create a new "data" in this directory. The file will be generated automatically during initialization.
datadir=D:\03Enviroment\05mysql\01install\mysql-8.0.19\data\
port=3306
# Skip password verification
skip-grant-tables

1. Start cmd in administrator mode (we need to register the service in the registry at this time), switch the directory to the bin directory of mysql, and then enter the following command to install.

However, when executing this installation statement, the missing component error message pops up:

We can solve this problem by running the "Microsoft common runtime collection" in the cloud disk.

# install is preceded by a space.
mysqld -install

1. Initialization file

myaqld --initialize-insecure --user=mysql

1. Start mysql,

net start mysql

Use the following command to enter the mysql management interface to modify the password (the password can be blank)

# -Do not add a space after p, otherwise an error will be reported
mysql -u root -p

1. Enter the command statement and update the password

# mysql5.0 version
update mysql.user set authentication_string=password('123') where user='root' and Host='localhost';
# mysql8.0.19 (modified successfully). When our password is entered incorrectly, we can use it to change it.
# alter user 'user name' @ 'Login Host' identified by 'password (custom)';
alter user 'root'@'localhost' IDENTIFIED BY '123';

1. Refresh permissions

flush privileges;

1. Modify my INI file, delete (or add # comments) the following code

skip-grant-tables

1. Restart mysql for connection test.

net stop mysql
net start mysql

Operate according to the following figure:

Finally, use the exit command to exit.

1.1.2 Navicat installation

install

1. First, install navicat in the cloud disk compression package.

1. After installation, click the second file in the figure above, select the exe file under the navicat installation directory, and open it.

connect

1. When using navicat to connect to the database (mysql8.0.19), a 1251 error will appear:

Client does not support authentication protocol. 

The main reason is that the encryption rule in versions before MySQL 8 is mysql_native_password, and after mysql8, the encryption rule is caching_sha2_password.

We need to restore the user login password encryption rules to mysql_native_password. Just.

• After using the administrator mode, enter the bin directory of mysql and enter the password

mysql -u root -p

• Change policy

# Don't change 'password' to your own password. After this sentence, our password will be changed to 'password'. We also need to change our password.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

• Refresh permissions

flush privileges;

• The original password will be changed

alter user 'root'@'localhost' IDENTIFIED BY '123';

Then, after the connection test, it can be connected.

Note:

1. I loaded the drivers in my maven project on the IDEA. This problem will be encountered in the process of testing the connection:

Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' property manually. 

Time zone error. MySQL's default time zone is UTC, which is 8 hours later than Beijing time. Therefore, we need to enter the administrator command interface. After logging in to MySQL with a password, we need to enter the following command to run it:

set global time_zone='+8:00'; 

In this way, you can connect successfully.

1.2 basic command line operations

mysql -uroot -p123 --Linked database.
 
update user set password=password('123') where user='root' and host='localhost';  --Change the user password.
flush privileges; --Refresh permissions.

-----------------------------------
show database; --View all databases.
mysql> use db1; --Switch databases, use Database name.
show tables; -- View all tables in the database.
describe t_user; --Displays all the corresponding information in the table.

-------------------------------------
-- Single-Line Comments 
/*(Multiline comment)
XXX
*/

1. Database language

• DDL definition
• DML operation
• DQL query
• DCL control

2 operation database statement

1. MySQL keywords are not case sensitive.

2.1 operation database

1. Create database

CREATE DATABASE IF NOT EXISTS test01;

1. Delete database

DROP DATABASE IF EXISTS test01;

1. Use database

use test01;

• If your table name or field name is a special character (it will be highlighted), you need to bring the floating ` 'above the tab key.

1. Query database creation operation

SHOW CREATE DATABASE DATABASE school; --View the statement that created the database.
SHOW CREATE TABLE student; --View the statement that created the database.
DESC student --Displays the structure of the table.

• Enter the above command line statement in navicat.

2.2 explanation of data types of columns

1. numerical value

• tinyint: very small data, 1 byte.
• smallint: 2 bytes.
• mediumint: 3 bytes.
• int: 4 bytes (standard integer).
• big: 8 bytes (larger data)
• float: floating point number 4 bytes.
• double: floating point number 8 bytes.
• decimal: floating point number in string form, which is used in financial calculation.

1. character string

• char: fixed string size 0 ~ 255.
• varchar: variable String 0 - 65535, corresponding variable: String.
• tinytext: Micro text, 2 ^ 8 - 1 (blog);
• Text: text string, 2 ^ 16 - 1, save large text;

1. Time and date

• Date: YYYY-MM-DD date (java.util.Date).
• time: HH:mm:ss time format.
• datetime: YYYY-MM-DD HH:mm:ss, the most commonly used time format.
• Timestamp: timestamp, the number of milliseconds from 1970.1.1 to now.
• Year: year.

1. null

• No value, unknown.

Database properties

• Unsigned: an unsigned integer that declares that the column cannot be declared as a negative number.
• zerofill: insufficient number of digits, filled with 0.

2.4 creating database tables

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• In English (), the names and fields of the table shall be enclosed with '' as far as possible.

• format

CREATE TABLE [IF NOT EXISTS] `Table name` (
  `Field name` Column type [attribute] [Indexes] [notes]
) [Table type][Character set settings][notes];

2.5 differences between MyISAM and InnoDB

1. Database engine: MyISAM and InnoDB.

	MyISAM	InnoDB
Transaction support	Not supported	support
Data row locking	Not supported	support
Foreign key constraint	Not supported	support
Full text index	support	Not supported
Tablespace size	less	more

1. Comparison of advantages and disadvantages

• MyISAM: save space and speed.
• InnoDB: high security, transaction processing, multi table and multi-user operation.

1. Where it exists in physical space

• All database files are stored in the data directory.
• InnoDB has only one *. In the database table frm file and ibdata1 file in the parent directory.
• MyISAM corresponding file
  • *. frm: definition file of table structure.
  • *.MYD: Data file (Data).
  • *. MYI: Index file (Index).

1. Set the character set encoding of the database

CHARSET=utf8mb4 

• When not set, the system will use the default code and does not support Chinese.

2.6 modification and deletion table

ALTER TABLE `student` RENAME AS `student1`; --Modify table name

ALTER TABLE `student1` ADD `age` int; --Add table fields

ALTER TABLE `student1` MODIFY `age` VARCHAR(11); -- Modify table fields (modify constraints)

ALTER TABLE `student1` CHANGE `age` `age1` VARCHAR(11); -- Field rename

ALTER TABLE `student1` DROP `age1`; --Delete table fields

DROP TABLE IF EXISTS `student1`; -- Delete table

• CHANGE is used for field renaming. Types and constraints cannot be modified.
• MODIFY cannot be used to rename fields. Only types and constraints can be modified.
• All creation and deletion operations should add fields as much as possible to avoid errors.

2.7 foreign keys

1. Method 1: add constraints when creating tables (more complex)

CREATE TABLE IF NOT EXISTS `grade` (
    `gradeid1` int NOT NULL AUTO_INCREMENT COMMENT 'grade id',
    `gradename` VARCHAR(30) NOT NULL COMMENT 'Grade name',
    PRIMARY KEY (`gradeid1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Student table gradeid Field to reference the grade table gradeid
-- Define foreign keys key
-- Add constraints to foreign keys (execute references)
DROP TABLE IF EXISTS `student`;
CREATE TABLE IF NOT EXISTS `student` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
    `sex`  varchar(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
    `gradeid` int NOT NULL  COMMENT 'Student's grade',
    PRIMARY KEY (`id`),
    KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid1`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• Navicat interface display

• The foreign key of the sub table (student table) must be the primary key of the main table (grade table).

• (assuming the student table (student number, name, gender, major number) and major table (major number, major name), the major number in the student table is the foreign key of the student table, which is associated with the "major number" attribute in the major table. Therefore, the major table is the master table and the student table is the slave table.)

• To delete the main table, you must delete the sub table first. To delete the records of the main table, you must delete the records associated with the sub table first. You cannot update the value of the primary key field of the main table.

  That is, when deleting a table with a foreign key relationship, you must first delete the table referencing others (from the table "student") and then delete the referenced table (the main table "grade").

• Problem encountered: incompatible with sql_mode=only_full_group_by

--resolvent
select @@sql_mode;
-- Console output: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

-- Execute the following two commands to remove sql_mode of ONLY_FULL_GROUP_BY. 
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';


CREATE TABLE IF NOT EXISTS `student` (
  `id` int NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL DEFAULT `anonymous` COMMENT `full name`,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1. Method 2: after the table is created successfully, add constraints

DROP TABLE IF EXISTS `grade`;
CREATE TABLE IF NOT EXISTS `grade` (
	`gradeid1` int NOT NULL AUTO_INCREMENT COMMENT 'grade id',
  `gradename` VARCHAR(30) NOT NULL COMMENT 'Grade name',
  PRIMARY KEY (`gradeid1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Student table gradeid Field to reference the grade table gradeid
-- Add constraints to foreign keys (execute references)
DROP TABLE IF EXISTS `student`;
CREATE TABLE IF NOT EXISTS `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
	`sex`  varchar(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
	`gradeid` int NOT NULL  COMMENT 'Student's grade',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- There is no foreign key relationship when creating a table
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid1`);

--ALTER TABLE `Table name`
--ADD CONSTRAINT `Constraint name` FOREIGN KEY (`Columns as foreign keys`) REFERENCES `That watch` (`That field of the table`);

• The above operations are physical foreign keys, database level foreign keys, which are not recommended! (avoid trouble caused by too many databases)
• Best practices
  • A database is a simple table. It can only store data, only rows (records) and columns (fields).
  • We want to use the data of multiple tables and foreign keys (programs).

2.8 DML language

DML: Data Manipulation Language.

• Insert

• update

• delete

  TRUNCATE `student`; 
  

• Completely empty a data table, and the table structure and index constraints will not change.

2.9 DQL language

• DQL: data query language.

• The core language and the most important statement in the database.

• Complete syntax of SELECT:

select [ALL | DISTINCT]
from table_name [as table_alias]
	[left | right | inner join table_name2]  --Joint query
	[where...] --Specify the conditions to be met for the results
	[group by ...] --Specify which fields the results are grouped by
	[having ...] --Secondary conditions that must be met to filter grouped records
	[order by Listing[asc | desc]] --Specifies that query records are sorted by one or more criteria
	[limit [offset,] rows | rows OFFSET offset]
	
	--Note:[]Represents optional,{}The representative is required.

1. Data De duplication

SELECT *FROM `student`;
SELECT `name` FROM `student`; -- Query the names in the student table
SELECT DISTINCT `name` FROM `student`; -- Duplicate data found, de duplication.

• DISTINCT: remove the duplicate data from the query results, and only one duplicate data is displayed.

1. Database columns

SELECT VERSION(); --Query System Version (function)
SELECT 100*3 AS `Calculation results`; -- Used to evaluate (an expression)
SELECT @@auto_increment_increment; --Query self increasing step size (variable)

SELECT `gradeid`+ 1 AS 'id After adding 1' FROM `student`; --Add 1 to the query result

2.9.1 where conditional clause

• Retrieves the qualified values in the data. The search condition consists of one or more expressions, and the result is Boolean.

  operator	grammar	describe
  and &&	a and b	Logic and, both of which are true, are true.
  or ||	a or b	Logic and, one of which is true, is true.
  not !	not a	Logical non, true is false, false is true.

• Try to use English letters.

2.9.2 fuzzy query operation

operator	grammar	describe
IS NULL	a IS NULL	If the operator is NULL, the result is true.
IS NOT NULL
BETWEEN	a BETWEEN b AND c	If a is between the results of B and C, the result is true.
Like	a LIKE b	If a matches b, the result is true.
IN	a IN(a1,a2,...)	Suppose a is one of a1, or a2... And the result is true.

-- Inquire about students surnamed Liu
-- like combination %(Represents 0 to any character) _((one character)
SELECT `name` FROM `student`
WHERE `name` LIKE 'Liu%';

-- Query students with "Zhi" in their names
SELECT `name` FROM `student`
WHERE `name` LIKE '%Ambition%';

-- query gradeid Students of (1, 3, 4).
SELECT `name`,`gradeid` FROM `student`
WHERE `gradeid Students of (1, 3, 4)` in(1,3,4);
-- query gradeid Students with empty data in.
SELECT `name`,`gradeid` FROM `student`
WHERE `gradeid`= '' OR `gradeid`IS NULL ;

2.9.3 associated table query

1. Join table query diagram

1. thinking

• Analyze the requirements and the fields to be queried from which tables (join query).
• Determine which connection query to use (7 ways).
• Determine the intersection (those data in the two tables are the same).
  • Judgment condition: studentNo in student table = studentNo in grade table.

2.9.3.1 create test items

1. Left join query.

• It is based ON the left table and connects the two tables according to the conditions of the two tables given after ON. As a result, all the query information in the left table will be listed, while the right table only lists the part that meets the conditions after ON and the left table.
• The full name of left connection is left external connection, which is a kind of external connection.

SELECT  *FROM `student`AS s
LEFT JOIN `result` AS r
ON s.studentNo = r.studentNo;

• All the records in the studnt table are queried. If there is no record with s.studentNo = r.studentNo in grade, the display on the right is empty, and only the records with s.studentNo = r.studentNo in grade table are queried.

1. Right join query

• It is based ON the right table and connects the two tables according to the conditions of the two tables given after ON. As a result, all the query information in the right table will be listed, while the left table only lists the part that meets the conditions after ON and the right table.
• The full name of right connection is right external connection, which is a kind of external connection.

SELECT  *FROM `student`AS s
RIGHT JOIN `result` AS r
ON s.studentNo = r.studentNo;

1. Internal connection query

• Use comparison operators to match rows in two tables based on the values of columns common to each table.

SELECT  *FROM `student`AS s
INNER JOIN `result` AS r
ON s.studentNo = r.studentNo;

• Only records with s.studentNo = r.studentNo are displayed.

operation	describe
LEFT JOIN	Even if there is no match in the table, the record row is returned from the left table.
RIGHT JOIN	Even if there is no match in the table, the record row is returned from the right table.
INNER JOIN	If there is at least one match in the table, the record row is returned.

2.9.4 self connection query

• Your own table is connected with your own table. The core is to split one table into two tables.

CREATE TABLE `category`(
	`categoryid` int NOT NULL AUTO_INCREMENT COMMENT 'theme id',
	`pid` int NOT NULL COMMENT 'Parent class id',
	`categoryName` VARCHAR(30)	NOT NULL COMMENT 'Subject name',
	PRIMARY KEY (`categoryid`)
)ENGINE = INNODB  DEFAULT CHARSET = UTF8

INSERT INTO `category` (`categoryid`,`pid`,`categoryName`)
VALUES ('2','1','information technology'),
('3','1','software development'),
('4','3','database'),
('5','1','Art design'),
('6','3','Web development'),
('7','5','PS technology'),
('8','2','Office information');

• Database display

• Parent class table

categoryid	categoryName
2	information technology
3	software development
5	Art design

• • The main body id(categoryid) of the parent class is determined by "1" of pid.

• Subclass table

pid	categoryid	categoryName
3	4	database
2	8	Office information
3	6	Web development
5	7	PS Technology

• • The subclass table is associated with the parent table through pid.

• Operation: query the correspondence between parent and child classes.

  Parent class	Subclass
  information technology	Office information
  software development	database
  software development	Web development
  Art design	PS Technology

SELECT a.`categoryName` AS 'Parent column',b.`categoryName` AS 'Sub column'
FROM `category` AS a ,`category` AS b
WHERE a.`categoryid` = b.`pid`;

• Console output:

2.9.5 paging and sorting

• Paging can relieve the pressure on the database and increase a good user experience.

SELECT *FROM `student`
ORDER BY `id` ASC
LIMIT 1,5;

• Syntax: limit the offset of record lines and the maximum number of record lines. Note: the offset of the initial record line is 0 (not 1).

• Page N (n - 1) * pageSize,pageSize;

  • (n - 1) * pageSize: start page (offset of record line)

• pageSize: page size (maximum number of record rows)

  • n: The current page.
  • Total pages = total number of arrays / page size.

2.9.6 sub query

• The essence of where (calculated value) is to nest a sub query statement in the where statement.

1. take

SELECT `name`,`sex`
FROM `student`
WHERE `studentNo` = (
-- query result Student number of student achievement in
	SELECT `studentNo` FROM `result` where `studentResult` = '20'
)

• Query the names and genders of the students whose test scores are 20. The examination results shall be inquired in the result, and the name and gender shall be inquired in the student.
• studentNo cannot return two values, otherwise an error will be reported.

SELECT `name`,`sex`
FROM `student`
WHERE `studentNo` = (
-- query
	SELECT `studentNo` FROM `result` where `subjectNo` = '2'
)

• 
• Solution: add keyword ANY

SELECT `name`,`sex`
FROM `student`
WHERE `studentNo` = ANY(
-- query
	SELECT `studentNo` FROM `result` where `subjectNo` = '2'
)

2.10 MySQL functions

2.10.1 common functions

• Official website address:

  https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html

1. give an example:

SELECT ABS(-8);

• Returns the absolute value of x.

2.10.2 aggregate function

Function name	describe
COUNT()	count
SUM()	Sum
AVG()	average value
MAX()	Maximum
MIN()	minimum value

1. COUNT function

-- count(`field`)
SELECT COUNT(`gradeid`) FROM `student`;
SELECT COUNT(*) FROM `student`;
SELECT COUNT(1) FROM `student`;

• Can count the data in the table. Count (field) ignores all null values in the table. The latter will not ignore the number of accounting lines.

1. SUM function

SELECT SUM(`gradeid`) AS 'the sum' FROM `student`;

2.11 MD5 encryption at database level

• MD5 has enhanced algorithm complexity and irreversibility.

CREATE TABLE `testmd5`(
    `id` INT NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- enable password 
-- INSERT INTO `testmd5` VALUES(1,'zhangsan','123'),(2,'zhangsan','123'),(3,'zhangsan','123');

-- encryption
UPDATE `testmd5` SET `pwd` = MD5(`pwd`);

-- Encryption during insertion (common)
INSERT INTO `testmd5` VALUES (4,'wangwu',MD5('123'));

-- Verification method, written in the background. The password passed by the user is md5 Encrypt and then compare the encrypted values.
SELECT *FROM `testmd5` WHERE `name` = 'wangwu' AND `pwd`= MD5('123');

• Output after encryption:

• When designing the table structure of password, if the length is small, the problems are as follows:

• When inserting encrypted records:

3. Design a database according to the specification

3.1 introduction

1. Bad database design:

• Data redundancy, waste of space.
• Database insertion and deletion will be troublesome and abnormal (shielding the use of physical foreign keys).

1. In software development, the design of database

• Analysis requirements: analyze the requirements of the business and the database to be processed.
• Outline design: design relation diagram and E-R diagram.

1. Design database steps (personal blog)

• Collect information and analyze requirements
  • User table (user login and logout, user's personal information).
  • Article table (information of articles).
  • Friendship link table.
• Identify the entity and implement the above broad requirements to each field.

3.2 three paradigms

1. Reasons for Standardization

• Avoid duplication of information.
• Avoid update exceptions.
• Avoid inserting exceptions.
  • Unable to display information normally. (incomplete data insertion)
• Avoid deleting exceptions.

1. Three paradigms

• First normal form (1NF)
  • Each column of the database table is required to be an indivisible atomic data item.

• Second paradigm (2NF)
  • Satisfy the first paradigm.
  • Each table only does one thing (you need to ensure that every column in the database table is related to the primary key, not only a part of the primary key (mainly for the joint primary key).

• Third paradigm (3NF)
  • Satisfy the second normal form.
  • Ensure that each column of data in the data table is directly related to the primary key, not indirectly.

1. Balance between standardization and performance

• Alibaba stipulates that there should be no more than three associated query tables.
• Consider the needs and objectives of commercialization, (cost, user experience!) The performance of database is more important.
• When standardizing performance issues, the following specifications need to be properly considered.
• Deliberately add some honor fields to some tables (change multi table query into single table query).

4 transaction

• Transaction is to put a group of SQL into a batch for execution.

1. Transaction principle - ACID principle

• Atomicity

  It means that a transaction is an inseparable work unit, and the operations in the transaction either occur or do not occur.

• Consistency

  The integrity of data before and after the transaction must be consistent.

• Isolation

  When multiple users access the database concurrently, the transaction opened by the database for each user cannot be disturbed by the operation data of other transactions. Multiple concurrent transactions should be isolated from each other.

  • Dirty read: one transaction reads uncommitted data from another transaction.
  • Non repeatable reading: in a transaction, a row of data in the table is read, and the reading results are different for multiple times. (this is not necessarily a mistake, it's just wrong on some occasions)
  • Virtual read: refers to reading the data inserted by other transactions in one transaction, resulting in inconsistent reading. (usually affected by one line, one more line)

• Durability

  Once a transaction is committed, its changes to the data in the database are permanent. Next, even if the database fails, it should not have any impact on it.

1. General process of Opening Transaction

SET autocommit = 0; -- Turn off auto submit( MySQL The default is to enable automatic transaction submission, i.e SET autocommit = 1;)

-- Manual transaction processing
SET autocommit = 0;

-- Transaction on
START TRANSACTION; -- Mark the beginning of a transaction, starting from the beginning sql All in the same transaction

INSERT XX
INSERT XX

-- Submit,Persistence (successful!)
COMMIT;

-- Rollback, rollback to the original (failed!)
ROLLBACK

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

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

1. Transfer example

SET autocommit = 0;
START TRANSACTION; 

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;

ROLLBACK;
SET autocommit = 1; 

5 index

5.1 introduction

• Index is a data structure that helps MySQL get data efficiently.

1. classification

• PRIMARY KEY (PRIMARY KEY)
  • Unique identification. The primary key cannot be repeated. There can only be one column as the primary key.
• UNIQUE KEY
  • The purpose of creating a unique index is not to improve access speed, but only to avoid data duplication. There can be multiple unique indexes, but the value of the index column must be unique, and the value of the index column can have null value.
• General index (KEY/INDEX)
  • default.
• Full text index
  • Only under a specific database engine, MyISAM.
  • Quickly locate data.

1. Use of index

• Add indexes to fields when creating tables.
• After creating the table, increase the index.

-- Display all index information
SHOW INDEX FROM `student`;

-- Add a full-text index column name (index name)
ALTER TABLE `student` ADD FULLTEXT INDEX `sex1`(`sex`);

-- analysis sql Implementation status of
EXPLAIN SELECT * FROM `student`;

1. Indexing principle

• The more indexes, the better.
• Do not index the data changed by the process.
• Small data tables do not need to be indexed.
• Indexes are usually sandwiched between fields commonly used for query.

1. Indexed data structure

• Index of type Hash.
• Btree: InnoDB's default data structure.

Recommended reading:

http://blog.codinglabs.org/articles/theory-of-mysql-index.html6

6 JDBC

Database driven

The program will interact with the database through the database driver.

In order to simplify the operation of developers on the database, sun company provides a unified (Java operation database) standard JDBC.

For developers, we only need to master the operation of JDBC.

6.1 first JDBC program

• Create data table

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• Test class

  @Test
    void contextLoads() throws Exception {
        //1. Load drive
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2. User information
        /**
         * 1.userUnicode=true,characterEncoding=utf8 Define database UTF-8 encoding format
         */
        String url = "jdbc:mysql://localhost:3306/demo?userUnicode=true & characterEncoding=utf8 & useSSL=false & serverTimezone=UTC ";
        String username = "root";
        String password = "123";
        //3. The connection is successful. The database object connection represents the database
        Connection connection = DriverManager.getConnection(url, username, password);
        //4. Execute SQL object. statement represents the object executing SQL.
        Statement statement = connection.createStatement();
        //5. The object executing SQL executes SQL
        String sql = "select * from users";

        /**
         * 1. resultSet The returned result set encapsulates all the data we query.
         *   statement.execute()  //Execute any sql
         *   statement.executeQuery() //Query operation returns ResultSet
         *   statement.executeUpdate() //Update, insert and delete all use this to return the number of affected rows.
         */

        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            /**
             * Used without knowing the column type.
             * If you know the column type, use the specified type
             */
            System.out.println("id = "+resultSet.getObject("id"));
            System.out.println("name = "+resultSet.getObject("name"));
            System.out.println("password = "+resultSet.getObject("password"));
            System.out.println("email = "+resultSet.getObject("email"));
            System.out.println("birthday = "+resultSet.getObject("birthday"));
            System.out.println("----------------------------------------------");
        }

        //6. Release the connection
        resultSet.close();
        statement.close();
        connection.close();
    }

• Of course, the driver should be loaded in the pom file before using mysql.

• The statement object is mainly used to send SQL statements to the database. You can use this object to send SQL statements to the database to complete the addition, deletion, modification and query of the database.

6.1.1 extraction tools

1. Extract the database connection configuration

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?userUnicode=true & characterEncoding=utf8 & useSSL=false & serverTimezone=UTC
username=root
password=123

Encapsulate it into a DB The main purpose of the properties file is to understand the coupling.

1. Extract connection configuration, etc. into tool classes

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1. The drive is loaded only once
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Get connection
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //Release connection resources
    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws Exception {
        if (resultSet != null){
            resultSet.close();
        }
        if (statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
        }
    }
}

• test

@Test
    void contextLoads() throws Exception {
        Connection connection = JdbcUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select * from users";

        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println("id = "+resultSet.getObject("id"));
            System.out.println("name = "+resultSet.getObject("name"));
            System.out.println("password = "+resultSet.getObject("password"));
            System.out.println("email = "+resultSet.getObject("email"));
            System.out.println("birthday = "+resultSet.getObject("birthday"));
            System.out.println("----------------------------------------------");
        }

        //6. release the connection
        JdbcUtils.release(connection, statement, resultSet);
    }

6.1.2 PreparedStatement object

@Test
    void contextLoads() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            connection = JdbcUtils.getConnection();
            //Turn off the automatic submission of the database and start the transaction automatically.
            connection.setAutoCommit(false);
            //Use placeholders instead of parameters
            String sql = "insert into users(name,password,email,birthday) values (?,?,?,?)";
            //Precompiled SQL is not executed directly.
            preparedStatement = connection.prepareStatement(sql);

            //Assign values to parameters manually
            preparedStatement.setString(1, "xiaolun");
            preparedStatement.setString(2, "123");
            preparedStatement.setString(3, "123456@qq.com");
            preparedStatement.setDate(4, (new Date(new java.util.Date().getTime())));

            //implement
            preparedStatement.executeUpdate();
            //Commit transaction
            connection.commit();
        }catch (Exception e){
            //If there is a problem with the transaction, perform the rollback operation.
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(connection, preparedStatement, null);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

• There are also transaction operations and data rollback operations in the above program.

6.2 database connection pool

Database operation: database connection, execution completed, release. The latter two steps are a waste of resources. We have developed a pooling technology, that is, prepare some pre prepared resources, and connect the pre prepared resources when SQL comes.

At present, the more popular connection pools are DBCP and C3P0. We need to write a connection pool and implement an interface DataSource, so we don't need to write code to connect to the database in the project. Because it realizes the functions of the following code:

 connection = JdbcUtils.getConnection();

7 access of time type output

 private Date birthday; //Entity class, synchronization time field.

--sql Field in.
`birthday` datetime DEFAULT NULL,

• Test class.

 @Autowired
    private UserService userService;

    @Test
    void contextLoads() {

        /**
         *  insert data
         *  Insert Date() type data in Java into Mysql
         */
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String nowTime = sdf.format(date);
        // nowTime-------->2020-06-18 10:13:40
        System.out.println("nowTime-------->"+nowTime);
        Date time = null;
        try {
            //Convert String type to Date()
            time = sdf.parse(nowTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }

        User user = new User();
        user.setName("xiaohei");
        user.setBirthday(time);
        //insert data
        userService.insert(user);

        /**
         *  Query data
         *  Output the data of time type (datetime) in Mysql as String
         */
        User user1 = userService.selectById(1);
        System.out.println(user1.getId());
        System.out.println(user1.getName());
        //Sat Feb 15 21:51:37 CST 2020
        System.out.println( user1.getBirthday());

        //Convert the queried Date() data into String format data and output it
        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        //Simulate a date format from the front end or yourself and convert it to String
        String nowTime1 = sdf.format(user1.getBirthday());
        //2020-02-15 21:51
        System.out.println("nowTime----->"+nowTime1);
    }

   */
    Date date = new Date();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String nowTime = sdf.format(date);
    // nowTime-------->2020-06-18 10:13:40
    System.out.println("nowTime-------->"+nowTime);
    Date time = null;
    try {
        //Convert String type to Date()
        time = sdf.parse(nowTime);
    } catch (ParseException e) {
        e.printStackTrace();
    }

    User user = new User();
    user.setName("xiaohei");
    user.setBirthday(time);
    //insert data
    userService.insert(user);

    /**
     *  Query data
     *  Output the data of time type (datetime) in Mysql as String
     */
    User user1 = userService.selectById(1);
    System.out.println(user1.getId());
    System.out.println(user1.getName());
    //Sat Feb 15 21:51:37 CST 2020
    System.out.println( user1.getBirthday());

    //Convert the queried Date() data into String format data and output it
    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm");
    //Simulate a date format from the front end or yourself and convert it to String
    String nowTime1 = sdf.format(user1.getBirthday());
    //2020-02-15 21:51
    System.out.println("nowTime----->"+nowTime1);
}