I met the MySQL

MySQL is a relational database management system developed by MySQL AB in Sweden and a product of Oracle.

MySQL is one of the most popular Relational Database Management systems and one of the best RDBMS (Relational Database Management System) applications for WEB applications.

Relational Database (SQL)

1. MySQL,Oracle,DB2
2. It is stored by the relationship between tables and rows

Non-relational database (NoSQL)

1. Redis,MongDB
2. Non-relational database, object storage

---

Basic Database Commands

Connect to database
mysql -u root -P [password]-- query all databases
show databases;

-- Switch database
user[Database name];-- View all tables
show tables;

-- Displays table information
describe[table name].-- Create database
createDatabase [name of database];-- View the statement that created the database
show create database school;

-- View the definition statement for creating the table
show create table student;

-- View the table structure
desc student;

-- View the health EXPLAIN of SQL execution
EXPLAIN select * from user

-- Operating database
-- Operation database > Operation database table > Operation table dataIn the MySQLSQLStatements are case insensitive-- Create database
create database [if not exists] westos;

-- Delete database
drop database [if exists] student;

-- Using a database
If the table or library name is a special character, the ' 'character is required
user `westos`; 

Copy the code

---

Create database table

Integer: Tinyint Very small data 1 byte SmallInt Small data 2 bytes Mediumint Medium size data 2 bytes int Standard integer 4 bytes Bigint Large data 8 bytes

Floating-point number: float floating-point number 4 bytes double floating-point number 8 bytes

Financial calculations often use decimal: a floating point number in the form of a decimal string

String char String fixed size 0 255 varchar variable String 0 65535 Common String TinyText Micro text 2^8 1 text text String 2^16 1 Keep large text

Time format date YYYY-MM-DD Date format time HH: MM :ss Datetime YYYY-MM-DDHH: MM :ss The most common time format TIMESTAMP Timestamp 1970.1.1 Number of milliseconds since year Year said

Null has no unknown value. Do not use null to perform operations

Create database table complete statement

CREATE 	TABLE IF NOT EXISTS `t_te_user`(
      `id` INT(4) NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(30) NOT NULL ,   
      `pwd` VARCHAR(20) NOT NULL ,   
      `sex` VARCHAR(30) NOT NULL ,     
     `address` VARCHAR(100) DEFAULT NULL.PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

Format -
CREATE 	TABLE [IF NOT EXISTS] ` table name ` (` field name ` column type [properties] [index] [note], ` field name ` column type [properties] [index] [note], ` field name ` column type [properties] [index] [note], ` field name ` column type [properties] [index] [note], [table type][character set]Set the character set encoding of the database table

CHARSET=utf8
Copy the code

Mysql default character set encoding

Pay attention to the point

• Field names should be wrapped in ‘ ‘symbols whenever possible
• Comments use the — symbol
• SQL is case insensitive, but lowercase is recommended
• All punctuation marks are in English

---

Database engine

Database engine

	InnoDB	MYISAM
Transaction support	support	Does not support
Data row locking	support	Does not support
Foreign key constraints	support	Does not support
The full text indexing	Does not support	support
The size of the tablespace	Larger, about twice as large	smaller

​

MYISAM: Save space, the fastest

InnoDB: high security, transaction processing, multi-table multi-user operation

Location in physical space

All database files are stored in the data directory, a folder represents a database

The default Windows installation directory is C:\Program Files\

The essence is file storage

MySQL > select * from ‘MySQL’ where ‘MySQL’ = ‘MySQL’;

1. InnoDB has only one *. FRM file in the database table and an idbdata1 file in the parent directory
2. *.frm- table structure definition file,.myd – data file,.myi – index file

---

Modify and delete table fields

Change table name

ALTER TABLE[Old table name] RENAMEAS[New table name]-- New fields

ALTER TABLE[table name]ADD[Field name][Type]Change field constraint (e.g., int to varchar)

ALTER TABLE[table name] MODIFY [field name][Field constraint]-- Rename the field

ALTER TABLE[table name] CHANGE [old field name][new field name]-- Final conclusion: change is used for renaming fields, and cannot change field types and constraints;

--modify Modify the type and constraint of a field instead of renaming it;



- delete

-- Delete field

ALTER TABLE[table name]DROP[Field name]- delete table

DROP TABLE IF EXISTS[table name]-- All deletions and modifications should be determined (' if exists') to avoid errors
Copy the code

---

MySQL Data Management

A foreign key

concept

If the public key is the primary key in one relationship, then the public key is called the foreign key of the other relationship.

As you can see, a foreign key represents a correlation between two relationships. A table with a foreign key of another relationship as its primary key is called the master table, and a table with another key is called a slave of the master table.

In practice, the values of one table are put into the second table to represent the association, using the primary key values of the first table (including compound primary keys if necessary). At this point, the attribute in the second table that holds these values is called a foreign key.

Specify foreign key constraints when creating a table

Create a child table and create a foreign key

-- Grade table (ID \ Grade name)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade ID',
`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name'.PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- Student Information sheet (Student NUMBER, Name, gender, Grade, Mobile phone, Address, Date of birth, Email, ID number)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT 'student id',
`studentname` VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT 'name',
`sex` TINYINT(1) DEFAULT '1' COMMENT 'gender',
`gradeid` INT(10) DEFAULT NULL COMMENT 'grade',
`phoneNum` VARCHAR(50) NOT NULL COMMENT 'mobile phone',
`address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
`borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'email',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT 'Id Number'.PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


Method 2: After creating a child table, modify the child table to add a foreign key
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);


When deleting a table with a primary/foreign key relationship, delete the child table first and then the primary table
Delete the foreign key
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
Delete index (index); delete index (index
Note: this index is generated by default when creating a foreign key
ALTER TABLE student DROP INDEX FK_gradeid;

Copy the code

These are all physical foreign keys

Database-level foreign keys are not recommended to avoid excessive database problems

All future use of foreign keys is implemented at the application layer (code implementation)

---

DML language

--INSERT new detail

-- Insert command format:

insert into[table name]([table name],[Table name])values(value], value])-- Notes:

-- Generally write insert statement, field and data must be one to one

-- Separate fields or values with commas (,)

Field 1, Field 2... This section can be omitted, but the added values must correspond to the table structure, data column, order, and the same number.

- You can insert multiple values at the same time. Separate values with commas (,)



--UPDATE details

-- Modify the command format:UPDATE [table name]SET[Field name]=[Modified value]WHERE `id`='4';

- note:
The -- WHERE statement is followed by a filter condition. If not specified, all column data of the table is modified



--DELETE

-- Delete command format:

DELETE FROMThe table name [WHERECondition matching];- note:

-- Where is the filter condition. If not specified, all column data of the table will be deleted



--TRUNCAT Details

-- Delete command format:

TRUNCATE TABLE[table name].-- clear a table completely without changing table structure, indexes, constraints, etc.

-- Note: This is different from the DELETE command

- the same:

TRUNCATE deletes data without deleting table structures, but TRUNCATE is faster
- different:

Set AUTO_INCREMENT counter to zero using TRUNCATE TABLE

Using TRUNCATE TABLE does not affect transactions
Copy the code

●InnoDB will start from the first increment (if it is in memory, it will be lost when power is off) ●MyISAM will continue from the last increment (if it is in file, it will not be lost)

---

Query data in DQL

Data Query Language (DQL)

1. Queries database data, such as a SELECT statement
2. Simple single-table queries or complex and nested queries with multiple tables
3. Database language is the most core, the most important statement
4. The most frequently used statement

grammar

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[asalias2]][,...] ]}FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- Federated query
  [WHERE. ]-- Specifies the conditions that the result must meet
  [GROUPBY... ]Specifies which fields to group the results by
  [HAVING]  - Secondary conditions that the records in the filter group must meet
  [ORDER BY. ]-- Specifies that query records are sorted by one or more criteria
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- Specifies which records to query from
Copy the code

Single table query

-- Query all information about the table

select * from[table name]-- Queries the information about the specified field

select[Field] [field]from[table name]-- Query the specified field and take the alias

select[field]as[alias],[field]as[alias]from[table name]Concat(a,b)

select concat('s name,[field]),from[table name]- Delete duplicate data. The keyword is DISTINCT

select DISTINCT[Field name]from[table name]-- Other queries

select version() -- Query the system version
select 100*3- 1 as -- Calculation result
select @@auto_increment_increment -- Incrementing the query step

-- Students' test scores are collectively raised one point for viewing
SELECT studentno,StudentResult+1 AS 'After the raise' FROM result;

Expressions in a database: usually consist of text values, column values, nulls, functions, operators, etc
Copy the code

WHERE condition clause

The simple idea is to filter data conditionally from a table

Search criteria can consist of one or more logical expressions, and the results are usually true or false

-- Query the user whose ID is 1

select * from user where id=1;

Select * from user where id = 1 and name = King of golden Horn

select * from user where id=1 and name='King of the Golden Horn';

Select * from user where id=1 or id=2

select * from user where id=1 or id=2;;

Copy the code

Fuzzy query: The comparison operator

Note:

1. Arithmetic operations can only be performed between records of numeric data types;
2. Only data of the same data type can be compared.

IS NULL
-- Query a user whose email is empty

select * from user where email is null;

IS NOT NULL
-- Query users whose email is not empty

select * from user where email is not null;

BETWEEN
-- Interval fuzzy query between and Queries the users whose ids are greater than 4 but less than 50

select * from user where id between 4 and 50

LIKE
-- Usually used with the wildcard %

Select * from user whose name starts with gold

select * from user where name like 'king';

Select * from user whose name has a good character in the middle

select * from user where name like '% % better';

IN
Match multiple results to one or more specific values

Select * from user where id=1,2,3

select * from user where id in (1.2.3);

Copy the code

League table query

JOIN

Join queries

If you need to query the data of more than one data table, you can achieve multiple queries through the join operator

In the connectioninner joinQuery the intersection format of the result sets in two tables:select a.*,b.* from user a 
inner join student b on a.id=b.id; The left outer joinleft joinThe left table is used as the benchmark, and the right table is matched one by one. If no match is found, the left table is returned, and the right table is returnedNULLFill format:select a.*,b.* from user a 
left join student b on a.id=b.id; Right connectionright joinThe right table is used as the benchmark, and the left table is matched one by one. If no match is found, the right table is returned, and the left table is returnedNULLFill format:select a.*,b.* from user a 
right join student b on a.id=b.id;

Copy the code

Contour connection

SELECT s.*,r.*
FROM `user` s , `student` r
WHERE r.studentno = s.studentno
Copy the code

Since the connection

The data table is joined to itself

Split a table into two tables

Self-join Join a data table to itself to split a table into two tables

Create a table
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'theme id',
`pid` INT(10) NOT NULL COMMENT 'parent id',
`categoryName` VARCHAR(50) NOT NULL COMMENT 'Subject name'.PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- Insert data
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');

-- Query method 1
select a.categoryName as 'father',b.categoryName as'child' 
from category as a,category as b
where a.categoryid=b.pid;
-- Query method 2
select a.categoryName as 'father',b.categoryName as'child' 
from category as a
inner join category as b on a.categoryid=b.pid;
Copy the code

1. The equivalent join does not require that the attribute values are identical, while the natural join requires that the two relationships to be compared must be the same attribute group (attribute names can be different), that is, they must have the same range of values.

2. An equivalent join does not remove the duplicate property, while a natural join removes the duplicate property. In other words, a natural join is an equivalent join that removes the duplicate column (as shown)

Sort query ORDER BY

Keywords in sorted and paging queries are placed at the end of the entire SQL statement and cannot be placed before where or having

Ascending order:ASCGrammar:select * from[table name]order BY id ascBESC syntax:select * from[table name]order BY id desc
Copy the code

Query GROUP by

Grammar:select * from user
GROUP by[Grouped fields] Used after group queryHAVINGTo filter the grouped dataselect * from user
GROUP  by sex
HAVING age>20
Copy the code

Paging query Limit

Grammar:select * from[table name] limit [start value],[size per page]Copy the code

The formula for calculating each page can be derived by searching the law: page 1: limit 0,5 formula :(1-1)*5 page 2: limit 5 formula :(2-1)*5 page 3: limit 10,5 formula :(3-1)*5... Page N: (n-1)* pageSize limit (pageno-1)*pageSzie,pageSzieCopy the code

Nested query (subquery)

1. In the WHERE condition clause of the query statement, another query statement is nested
2. Nested query can be composed of multiple subqueries, and the solution is from inside to outside.
3. The results returned by subqueries are usually collections, so it is recommended to use the IN keyword.

Grammar:

select * from[table name]where id in ( select id from[Table name])Copy the code

---

MySQL > Select * from ‘MySQL’;

Commonly used functions

Mathematical function

select abs(- 8 -) - the absolute value
select CEILING(9.4); -- Round up
select RAND(); Return a random number
select SIGN(0); -- Sign function: returns -1 for negative numbers, returns 1 for positive numbers, returns 0 for 0

Copy the code

String function

select CHAR_LENGTH('You are the foundation.'); -- string length
select CONCAT("Zheng".'in'.'draft');-- Merge strings
select INSERT('You are the foundation.'.4.2.'Written by Zheng Jae'); -- Replace string
select LOWER("SKSSSS");- lower case
select UPPER('ssssss'); - the capital
select LEFT('hello,word'.5); -- Cut from the left
select RIGHT('hello,word'.5); -- Cut from the right
select REPLACE('Mad God says persevere and you will succeed.'.'stick to'.'success');-- Replace string
select SUBSTR('Mad God says persevere and you will succeed.'.4.6);-- Intercept a string
select REVERSE('Mad God says persevere and you will succeed.'); - reverse
Copy the code

Time date function

Date and time functions
select CURRENT_DATE(a);-- Gets the current date
select CURDATE();-- Gets the current date
select NOW();Get the current date and time
select LOCALTIME(a);Get the current date and time
select SYSDATE();Get the current date and time

-- Obtain year, month, day, hour, minute, second
select YEAR(NOW());
select MONTH(NOW());
select DAY(NOW());
select HOUR(NOW());
select MINUTE(NOW());
select SECOND(NOW());
Copy the code

Aggregation function

Function name description:~
COUNT() returns satisfiesSelectThe total number of records for a condition, such asselect count(*Not recommended*, low efficiency 】~
SUM() Returns a numeric field or expression column for statistics.~
AVG() usually performs statistics for numeric fields or expression columns and returns the average value of a column~
MAX() can count numeric fields, character fields, or expression columns and return the maximum value.~
MIN() can count numeric fields, character fields, or expression columns and return the minimum value.Copy the code

COUNT()

Format:select count([field])from[table name]-- ignores all nulls
select count(*) from[table name]-- does not ignore null
select count(1) from[table name]-- does not ignore null
Copy the code

Generally, count(1) is faster than count(*)

Other aggregate functions

Format:

select sum(id) from user - the sum
select AVG(id) from user - the average
select MIN(id) from user - minimum points
select MAX(id) from user - the highest
Copy the code

---

Database transaction

1. A transaction is the execution of a set of SQL statements in the same batch
2. If one SQL statement fails, all SQL in that batch will be cancelled
3. MySQL transactions only support InnoDB and BDB data table types

ACID

Atomic either all succeed or all fail Consistency Data integrity before and after transaction execution Isolation Transactions Each user is required to start a separate transaction with concurrent access that is Durable between transactions Once committed, a transaction is irreversibleCopy the code

Problems caused by isolation

Dirty read

One transaction read another transaction that was not committed

Transaction A reads the data updated by transaction B, and then B rolls back the operation, so the data read by TRANSACTION A is dirty

Phantom read

In the same transaction, data inserted by another person is read, resulting in inconsistent results

Unrepeatable read

The data in a table is read repeatedly within the same transaction, and the table data changes

Transaction A reads the same data for multiple times, and transaction B updates and commits the data during the multiple reads of transaction A. As A result, when transaction A reads the same data for multiple times, the results are inconsistent.

The basic grammar

Use the set statement to change the autocommit mode
SET autocommit = 0;   / * closed * /
SET autocommit = 1;   / * * /

- note:
-- 1. The default is auto-commit
-- 2. Automatic commit should be turned off before using transactions

Start a transaction, marking the starting point of the transaction
START TRANSACTION  

Commit a transaction to the database
COMMIT

The transaction is rolled back to the original state of the transaction
ROLLBACK

Restore the automatic commit of MySQL database
SET autocommit =1;

- the savepoint
SAVEPOINTSavepoint nameSet a transaction savepoint
ROLLBACK TO SAVEPOINTSavepoint name-- Roll back to savepoint
RELEASE SAVEPOINTSavepoint nameDelete savepoints

Copy the code

---

The index

MySQL defines an index as follows: An index is a data structure that helps MySQL obtain data efficiently

Display the index information of the table

show index from[table name]1
Copy the code

Create indexes

createIndex [index name]on[table]([field])Copy the code

The index classification

Primary Key index

Primary key: An attribute group that uniquely identifies a record

Features:

1. The most common index type
2. Ensure the uniqueness of data records
3. Determines the location of specific data records in the database

ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
//This statement adds a primary key, which means that the index value must be unique and cannot beNULL.Copy the code

Unique index (Unique Key)

Function: Avoid duplicate values in a column of the same table

Primary key index

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

ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
The value that this statement creates the index must be unique.
Copy the code

General Index (Index)

Function: Quickly locate specific data

Note:

• The index and key keywords can be used to set the general index
• The field that should be added to the search criteria in the query
• Do not add too many regular indexes to affect data insertion, deletion, and modification operations

CREATE TABLE `result` (-- Omit some code
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- Added when creating the table
)
Copy the code

ALTER TABLE tbl_name ADD INDEX index_name (col_list);
-- Add normal index, index value can appear multiple times.
Copy the code

FullText index

Function: Quickly locate specific data

1 2 The FullText index is used to quickly locate specific data

Note:

• Only for MyISAM type tables
• Can only be used for CHAR, VARCHAR, TEXT data column types
• The full-text index does not take effect when the data volume is small
• Suitable for large data sets

Add full-text index after creation
ALTER TABLE[table name]ADDFULLTEXT INDEX [INDEX]([column name]);Copy the code

-- Use of full-text indexes:

select * from student where MATCH([full-text index column name]) AGAINST ([search content])Copy the code

Force index

If the query optimizer ignores the INDEX, you can use the FORCE INDEX prompt to instruct it to use the INDEX.

1 FORCE indexes If the query optimizer ignores indexes, you can use the FORCE INDEX prompt to instruct it to use indexes.

The FORCE INDEX prompt syntax is described as follows:

SELECT * 
FROM table_name 
FORCE INDEX (index_list)
WHERE condition;

Copy the code

The index principles

1. More indexes is not always better
2. Do not index data that changes frequently
3. Do not add indexes to tables with small data volumes
4. The index should generally be added to the field of the search criteria

Index data structure

For hash indexes, single query is fast, but range query is slow

Index of type Btree: b+ tree, the amount of data grows exponentially with more layers (we’ll use this because InnoDB supports it by default)

Index deletion method

DROP INDEX index_name ON tbl_name;
//orALTER TABLE tbl_name DROPThe INDEX index_name;ALTER TABLE tbl_name DROP PRIMARY KEY;

Copy the code

---

Rights management

Create user command format:CREATE USER[User name] IDENTIFIEDBY '[code]'Modify the current user:set password=password('[code]'Change the password of the specified user:set password for[User name]=password('[code]') renameuser[Old user name]to[New User name] Grants full permissions to the user-- PRIVILEGES ALL PRIVILEGES. Table ON to User name
-- Can do nothing but give other users permission
GRANT ALL PRIVILEGES ON *.* TO[User name] Queries user rightsshow GRANTS for[users]-- Ordinary users
show GRANTS for root@localhost -- Check the permissions of the root userRevoking user RightsREVOKE ALL PRIVILEGES ON *.* from[User name] Deletes a userDROP[User name]Copy the code

---

Database backup

1. Ensure that important data is not lost
2. Data transfer

MySQL Database backup

Mysqldump command line export

mysqldump -U a username-P Password database name Table name>The file name (D:/a.sql)
Copy the code

Export --
1.Export a table-- mysqldump -uroot -p123456 school student >D:/a.sqlmysqldump-U a username-P Password database name Table name>The file name (D:/a.sql)
2.Export multiple tables-- mysqldump -uroot -p123456 school student result >D:/a.sqlmysqldump-U a username-P Password database name table1 表2 表3 >The file name (D:/a.sql)
3.Export all tables-- mysqldump -uroot -p123456 school >D:/a.sqlmysqldump-U a username-P Password database name>The file name (D:/a.sql)
4.Exporting a library-- mysqldump -uroot -p123456 -B school >D:/a.sqlmysqldump-U a username-P password-The library B>The file name (D:/a.sql)

Copy the code

Data import:

1. Command line import

2.In the case of login to mysql:-- source D:/a.sqlSource Backup file3.Mysql without logging in-U a username-P Password database name<Backup fileCopy the code

---

Three major database design paradigms

1st NF

The goal of the first normal form is to ensure the atomicity of each column, which satisfies the first normal form if each column is the smallest non-divisible unit of data

2nd NF

The second normal form (2NF) is based on the first normal form (1NF), i.e. to satisfy the second normal form (2NF) must first satisfy the first normal form (1NF).

The second normal form requires that each table only describe one thing

3rd Normal Form (3rd NF)

If a relationship satisfies the second normal form and no columns other than the primary key are passed dependent on the primary key column, then the third normal form is satisfied.

The third normal form ensures that each column of data in the data table is directly related to the primary key, not indirectly.

Normalization and performance relationships

A maximum of three tables can be used for associated query

• To meet certain business goals, database performance is more important than a standardized database (cost, user experience)
• While data normalization, the performance of the database should be considered comprehensively
• Add additional fields to a given table to significantly reduce the time required to search for information from it (increasing redundancy)

---

JDBC

Create project import dependencies and write code

/** * Test JDBC program */
@SpringBootTest
public class JdbcFirstDemoTest {

    @Test
    public void TestJDBC(a) throws Exception {
        
        // Load the driver
        Class.forName("com.mysql.jdbc.Driver");// Fixed writing
        // User information and URL
        String password="123456";
        String username="root";
        String url="jdbc:mysql://localhost:3306/jtdb? serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
        // Connect to the database object successfully
        Connection connection = DriverManager.getConnection(url, username, password);
        // Execute the SQL object
        Statement statement = connection.createStatement();

        // Use the object to run SQL to return the object
        ResultSet resultSet = statement.executeQuery("select * from user");

        // Iterate over the result
        while (resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
            System.out.println(resultSet.getObject("age"));
            System.out.println(resultSet.getObject("sex"));
        }
        // Release the connectionresultSet.close(); statement.close(); connection.close(); }}Copy the code

Description of the Connection object

Connection represents the database, and database-related operations are in there.

For example, set automatic transaction commit, transaction start, rollback and so on

connection.setAutoCommit(false);// Turn off autocommit
connection.commit();/ / submit
connection.rollback();/ / rollback
Copy the code

Statement Specifies the details of the object

statement.execute("");// Execute all types of SQL
statement.executeQuery("");// Execute the query SQL
statement.executeUpdate("");// Add modify delete

Copy the code

ResultSet provides detailed information about the resultSet

Gets data of the specified type

resultSet.getObject();// Used when the field type is not known
resultSet.getDate();// The field is a time type
resultSet.getString();// The field is varchar
resultSet.getInt();
resultSet.getBoolean();
Copy the code

Traversal, pointer

resultSet.next();// Move the pointer down one bit
resultSet.beforeFirst();// Move the pointer to the front
resultSet.afterLast();// Move the pointer to the end
resultSet.previous();// Move the pointer up one bit
resultSet.absolute(1);// Move the pointer to the specified position

Copy the code

Write JDBC utility classes

/** * JDBC tool class */
public class JdbcUtil {

    private static String url;
    private static String username;
    private static  String password;

    static {
        // Reflect read configuration file SRC directory
        InputStream inp = JdbcUtil.class.getClassLoader().getResourceAsStream("JDBC.properties");
        try {
            Properties properties = new Properties();
            properties.load(inp);// Read configuration information from the input stream
           url = properties.getProperty("url");
           username = properties.getProperty("username");
           password = properties.getProperty("password");
            Class.forName(properties.getProperty("Driver"));// Load the database driver
        } catch(IOException | ClassNotFoundException e) { e.printStackTrace(); }}// Get the connection
    public static Connection getConnection(a) throws SQLException {
            return DriverManager.getConnection(url, username, password);
    }

    // Release the connection
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(connection ! =null) {try {
                connection.close();
            } catch(SQLException throwables) { throwables.printStackTrace(); }}if(statement ! =null) {try {
                statement.close();
            } catch(SQLException throwables) { throwables.printStackTrace(); }}if(resultSet ! =null) {try {
                resultSet.close();
            } catch(SQLException throwables) { throwables.printStackTrace(); }}}}Copy the code

SQL injection

SQL injection is refers to the legitimacy of the web application to user input data without judgment or filtering is lax, the attacker can be defined in advance in the web application at the end of the query statement to add extra SQL statements, the administrator unwittingly illegal operation, in order to realize deceived any query of the database server to perform unauthorized, Thus further obtain the corresponding data information;

Insert into user(id,name,age,sex) values (? ,? ,? ,?)
PreparedStatement preparedStatement = connection.prepareStatement("insert into user(id,name,age,sex) values (? ,? ,? ,?) ");

 // Assign parameters manually
preparedStatement.setInt(1.100);
preparedStatement.setString(2."Golden horn and silver Horn.");
preparedStatement.setInt(3.12);
preparedStatement.setString(4."Male");

/ / execution
int i = preparedStatement.executeUpdate();
System.out.println("Result of execution :"+i);

Copy the code

JDBC operation transactions

  @Test
    public void Test2(a)  {

        // Load the driver
        try {
            Class.forName("com.mysql.jdbc.Driver");// Fixed writing
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        // User information and URL
        String password="123456";
        String username="root";
        String url="jdbc:mysql://localhost:3306/jtdb? serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
        // Connect to the database object successfully
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            // Closing the database auto-commit automatically opens the transaction
            connection.setAutoCommit(false);
            Insert into user(id,name,age,sex) values (? ,? ,? ,?)
            preparedStatement = connection.prepareStatement("insert into user(id,name,age,sex) values (? ,? ,? ,?) ");

            // Assign parameters manually
            preparedStatement.setInt(1.100);
            preparedStatement.setString(2."Golden horn and silver Horn.");
            preparedStatement.setInt(3.12);
            preparedStatement.setString(4."Male");

            / / execution
            int i = preparedStatement.executeUpdate();
            System.out.println("Result of execution :"+i);
            connection.commit();// Commit the transaction
        } catch (SQLException throwables) {
            try {
                connection.rollback();/ / rollback
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch(SQLException throwables) { throwables.printStackTrace(); }}Copy the code

---

Database connection Pool

Database connection > Execute > Release is wasteful

Pooling technology: Prepare some pre-selected resources and connect them directly when needed

To write a connection pool, all you need to do is implement a DataSource interface. Each vendor needs to make its own connection pool implement this interface

The common connection pools on the market are DBCP connection pool, C3P0 connection pool, Druid connection pool

No matter what DataSource you use, it’s essentially the same thing, the DataSource interface doesn’t change, okay

Use the C3P0 connection pool

Import dependence

 <! --C3P0 connection pool -->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
Copy the code

Write the C3P0 configuration file

XML configuration file must be named c3P0-config.xml

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jtdb? serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config>
    
    <! -- This app is massive! -->
    <named-config name="intergalactoApp">
        <property name="acquireIncrement">50</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
    
        <! -- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>
    
        <! -- he's important, but there's only one of him -->
        <user-overrides user="master-of-the-universe">
            <property name="acquireIncrement">1</property>
            <property name="initialPoolSize">1</property>
            <property name="minPoolSize">1</property>
            <property name="maxPoolSize">5</property>
            <property name="maxStatementsPerConnection">50</property>
        </user-overrides>
    </named-config>

</c3p0-config>
Copy the code

Writing tool class

/** * C3P0 connection pool */
   public class JdbcUtilsC3P0 {

   private static DataSource dataSource=null;

   static {
       dataSource = new ComboPooledDataSource();
   }
   // Get the connection
   public static Connection getConnection(a) throws SQLException {
       returndataSource.getConnection(); }}Copy the code

test

  // Test the C3P0 connection pool
    @Test
    public void TEst4(a) throws SQLException {
        Connection connection = JdbcUtilsC3P0.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from user");
        // Iterate over the result
      while (resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
           System.out.println(resultSet.getObject("age"));
            System.out.println(resultSet.getObject("sex")); }}Copy the code

---

Database level MD5

MD5 is message-Digest Algorithm 5. It is used to ensure the integrity and consistency of information transmission. It is one of the hashing algorithms widely used in computer, and the mainstream programming language has generally implemented MD5. Computing data, such as Chinese characters, into another fixed-length value is the basic principle of hash algorithms. MD5’s predecessors are MD2, MD3, and MD4.

Implement data encryption

1. Encrypt all passwords

update testmd5 set pwd = md5(pwd);

Copy the code

2. Encrypt the single fight records

INSERT INTO testmd5 VALUES(3.'kuangshen2'.'123456')
update testmd5 set pwd = md5(pwd) where name = 'zwt';
Copy the code

3. Add automatic data encryption

3. Add automatic data encryption

INSERT INTO testmd5 VALUES(4.'kuangshen3',md5('123456'));
Copy the code

4. Query the login user information (MD5 encrypted password).

SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');
Copy the code