MySQL

The pdf document will then be sent to the csdn library and Baidu Cloud Disk, or you can leave a mailbox for use in the comments.

1 New to MySQL

1.1 What is a database

Database (DB, DateBase)

Concepts: Data warehouse, software, installed on the operating system! SQL, can store a large amount of data. 5 million!

Role: Store data, manage data

1.2 Database Classification

Relational Database (SQL)

• MySQL， Oracle， Sql Sever， DB2， SQLite
• Storing data through relationships between rows and columns between tables

Non-relational database (NoSQL)Not Only

• Redis， MongDB
• Non-relational database, object storage, determined by the object's own properties

DBMS (Database Management System)

• Database management software, manage our data scientifically and effectively, maintain and obtain data
• MySQL, Data Management System

1.3 MySQL

MySQL is a relational database management system

Previous: Swedish MySQL AB Company

This life: belongs to Oracle products

MySQL is one of the best RDBMS (Relational Database Management System) applications.

Small, fast and low overall cost of ownership

Small and medium-sized websites, or large websites, clusters

2 Operation Database

Operations Database - > Operations Database Table - > Operations Database Table Data

2.1 Operation Database

1. Create a database

CREATE DATABASE IF NOT EXISTS westos

2. Delete database

DROP DATABASE IF EXISTS westos

3. Using databases

-- tab On top of the key, if your table or field name is a special symbol, you need to take``
USE `westos`

4. Query Database

SHOW DATABASE --View all databases

Column types for 2.2 databases

• numerical value

  • tinyint very small data 1 byte
  • Smllint smaller data 2 bytes
  • Meumint medium size data 3 bytes
  • int standard integer 4 bytes
  • bigint Big Data 8 Bytes
  • float floating point 4 bytes
  • double Floating Point 8 Bytes
  • Decimal string type Floating point financial calculations generally use decimal (double is not used because of precision type)

• Character string

  • char string fixed size 0-255
  • Vachar Variable String 0-65535 Common String
  • tinytext minitext 2^8 - 1
  • Text Text String 2^16 - 1 Save large text (ask questions during an interview, store blogs using data types, remember data)

• Time Date

  java.util.Date

  • date YYYY-MM-DD, date format
  • time HH:mm:ss, time format
  • datetime YYYY-MM-DD HH:mm:ss
  • Timstamp, milliseconds since 1970.1.1! More common too!
  • Year year representation

• null

  • No value, unknown
  • Note that do not use null for operations, the result is null

2.3 Field Properties for Databases (Key)

Unsigned:

• Unsigned integer
• Declares that the column cannot be declared negative

zerofill

• 0 Filled
• Insufficient digits, use 0 to fill in int(3), 5 ----> 005

Self-increasing

• Usually understood as self-increasing, automatically + 1 on the basis of the previous record (default)
• Usually used to design unique primary key~index, must be of integer type
• You can customize the start and step of the primary key auto-increment

Non-empty NULL not null

• Assume it is set to not null, if you do not assign it, you will get an error!
• NULL, if no value is filled in, the default is null!

default

• Set default value
• sex, the default value is male, if you do not specify a value for this column, there will be a default value!

2.4 Create database tables (focus)

-- Notes, in English()， Use table names and fields whenever possible `` Enclosed
-- AUTO_INCREMENT Self-increasing
-- Use single quotation marks to enclose strings
-- All statements followed by , (English Full Stop)，Last without adding
-- PRIMARY KEY Primary key, typically only one table has a single primary key
CREATE TABLE
IF
	NOT EXISTS `student` (
		`id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT 'School Number',
		`name` VARCHAR ( 30 ) NOT NULL DEFAULT 'anonymous' COMMENT 'Full name',
		`pwd` VARCHAR ( 20 ) NOT NULL DEFAULT '123456' COMMENT 'Password',
		`sex` VARCHAR ( 2 ) NOT NULL DEFAULT 'female' COMMENT 'Gender',
		`birthday` DATETIME DEFAULT NULL COMMENT 'Date of birth',
		`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT 'Home Address',
		`email` VARCHAR ( 50 ) DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY ( `id` ) 
	) ENGINE = INNODB DEFAULT CHARSET = utf8

format

CREATE TABLE IF NOT EXISTS `student`(
	`Field name` Column type [attribute] [Indexes] [Notes],
    `Field name` Column type [attribute] [Indexes] [Notes],
    ......
    `Field name` Column type [attribute] [Indexes] [Notes]
)[Table type][Character Set Settings][Notes]

Common Commands

SHOW CREATE DATABASE school;-- View statement to create database
SHOW CREATE TABLE student;-- See student Definition statements for data tables
DESC student;-- Show the structure of the table

Types of 2.5 Datasheets

-- About Database Engine
/*
	INNODB Default Use
	MYISAM Used in earlier years
*/

	MYISAM	INNODB
Transaction support	I won't support it	Support
Data row locking	I won't support it	Support
Foreign Key Constraints	I won't support it	I won't support it
Full-text Index	Support	I won't support it
Table space size	less	Larger, about twice as large

General usage operations:

• MYISAM saves space and is faster
• INNODB high security, transaction processing, multi-table, multi-user operation

Location in physical space

All database files are in the data directory

Essentially file storage

MySQL Engine Differences on Physical Files

• INNODB has only one *.fmr file in the database table and an ibdata1 file in the parent directory
• MYISAM corresponding file
  • *.fmr - Definition file for table structure
  • *.MYD Data File (data)
  • *.MYI Index File (index)

Set Character Set Encoding for Data

CHARSET = utf8

If not set, it will be mysql's default character set encoding ~ (Chinese is not supported)

The default encoding for MySQL is Latin1 and Chinese is not supported

Configure default encoding in my.ini

character-set-server = utf8

2.6 Modify or delete tables

modify

-- Modify Table Name  ALTER TABLE Old table name RENAME AS New table name
ALTER TABLE `teacher` RENAME AS `teacher1`;
-- Add Field to Table  ALTER TABLE Table Name ADD Field Name Column Properties
ALTER TABLE `teacher1` ADD `age` INT ( 11 );

-- Modify the fields of the table (rename, modify constraints!)
-- ALTER TABLE Table Name MODIFY Field Name Column Properties[]
ALTER TABLE `teacher1` MODIFY `age` VARCHAR ( 11 );  -- Modify Constraints
-- ALTER TABLE Table Name CHANGE Old Field Name New Field Name Column Properties[]
ALTER TABLE `teacher1` CHANGE age age1 INT ( 1 );  -- Field Rename

-- Delete field from table
ALTER TABLE `teacher1` DROP `age`;

delete

-- Delete tables (delete tables if they exist)
DROP TABLE
IF
	EXISTS `teacher`;

All creation and deletion operations should be judged as much as possible to avoid error~

Be careful

• ```Field name, use this package
• Comments -/**/
• sql critical case insensitive, we recommend lowercase
• All symbols are in English

3 MySQL Data Management

3.1 Foreign key (know it)

CREATE TABLE
IF
	NOT EXISTS `grade`(
	`grade` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade id',
	`gradename` VARCHAR(50) NOT NULL COMMENT 'Age Name',
	PRIMARY KEY(`gradeid`)
)ENGING = INNODB DEFALT CHARSET = utf8

Mode 1. Increase constraints when creating tables

-- Student Table gradeid The field is to refer to the grade table's gradeid
-- Define foreign keys key
-- Add a constraint to this foreign key (execute a reference) references Quote
CREATE TABLE
IF
	NOT EXISTS `student` (
		`id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT 'School Number',
		`name` VARCHAR ( 30 ) NOT NULL DEFAULT 'anonymous' COMMENT 'Full name',
		`pwd` VARCHAR ( 20 ) NOT NULL DEFAULT '123456' COMMENT 'Password',
		`sex` VARCHAR ( 2 ) NOT NULL DEFAULT 'female' COMMENT 'Gender',
		`birthday` DATETIME DEFAULT NULL COMMENT 'Date of birth',
		`gradeid` INT(10) NOT NULL COMMENT 'Grade of the student' ,
		`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT 'Home Address',
		`email` VARCHAR ( 50 ) DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY ( `id` ) ,
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
	) ENGINE = INNODB DEFAULT CHARSET = utf8

Mode 2. After creating a table, use alter to add foreign keys to the table

-- Student Table gradeid The field is to refer to the grade table's gradeid
CREATE TABLE
IF
	NOT EXISTS `student` (
		`id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT 'School Number',
		`name` VARCHAR ( 30 ) NOT NULL DEFAULT 'anonymous' COMMENT 'Full name',
		`pwd` VARCHAR ( 20 ) NOT NULL DEFAULT '123456' COMMENT 'Password',
		`sex` VARCHAR ( 2 ) NOT NULL DEFAULT 'female' COMMENT 'Gender',
		`birthday` DATETIME DEFAULT NULL COMMENT 'Date of birth',
		`gradeid` INT(10) NOT NULL COMMENT 'Grade of the student' ,
		`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT 'Home Address',
		`email` VARCHAR ( 50 ) DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY ( `id` ) 
	) ENGINE = INNODB DEFAULT CHARSET = utf8
	
-- There was no foreign key relationship when the table was created
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE surface ADD CONSTRAINT Constraint Name FOREIGN KEY(Columns as foreign keys) REFERENCES That table(That field);

Note: When deleting tables with foreign key relationships, you must delete tables that refer to others (from tables) before deleting referenced tables

The above operations are all physical foreign keys, database level foreign keys, not recommended! (Avoid the problem of too many databases, just know here)

Best Practices

• A database is simply a table that holds data, only rows (data) and columns (fields)
• We want to use data from multiple tables, we want to use foreign keys (program implementation)

3.2 DML (Database Management Language) (remember all)

**Database Meaning: ** Data Storage, Data Management

DML Language: Data Operation Language

• insert
• update
• delete

3.3 Add (insert)

-- Insert statement (add)
-- INSERT INTO Table Name([Field 1, Field 2, Field 3]) VALUES('Value 1'),('Value 2'),('Value 3');

INSERT INTO `grade`(`gradename`) VALUES('Senior')；

-- Since the primary key adds itself, we can omit it (if we don't write the fields in the table, they will match one another)
INSERT INTO `grade` VALUES('Junior')

-- Generally write insert statements, we must have one-to-one correspondence between data and fields!

-- Insert Multiple Fields
INSERT INTO `grade`(`gradename`) 
VALUES('Sophomore'),('Junior')

INSERT INTO `student`(`name`) VALUES ('Zhang San')

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('Zhang San','aaaaaa','male')；

INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('Zhang San','aaaaaa','male')，
('Li Si','aaaaaa','male')，
('King Five','aaaaaa','male')；

Matters needing attention

1. Fields are separated by English commas.
2. Fields can be omitted, but the following values must be one-to-one, not fewer;
3. Multiple data can be inserted at the same time. Values after VALUES need to be separated by English commas.

3.4 Update

-- UPDATE Who to Modify(condition) SET Original value = New Value

-- Change the name of the trainee with conditions
UPDATE `student` SET `name` = 'Crazy' WHERE `id` = 1;
-- All tables are altered without specifying a condition
UPDATE `student` SET `name` = 'Crazy';

-- Grammar:
-- UPDATE Table Name SET colnum_name = value where [condition]；

-- Modify multiple attributes
UPDATE `student` SET `name` = 'Crazy',`email` = '123456@qq.com' WHERE `id` = 1;
-- UPDATE Table Name SET colnum_name = value,[colnum_name = value,....] where [condition]；

Conditions: where clause operator id is equal to a value, greater than a value, modified within an interval...

Be careful:

• colum_name is a column in the database with ``as much as possible;
• Condition, filter condition, if not specified, all columns will be modified
• Value is either a specific value or a variable
• Separate multiple set attributes with English commas

Operator

The operator returns a Boolean value

Operator	Meaning	Range	Result
=	Be equal to	5 = 6	false
<>or!=	Not equal to	5 <> 6	true
>	greater than
<	less than
<=	Less than or equal to
>=	Greater than or equal to
BETWEEN ... AND ...	Within a range	[2, 5]
AND	And &&	5 > 1 and 1 > 2	false
OR	Or ||	5 > 1 or 1 > 2	true

3.5 Delete

delete command

Syntax: DELETE FROM table name [where condition]

-- Delete data (avoid this, delete all)
DELETE FROM `student`

-- Delete specified data
DELETE FROM `student` WHERE id = 1;

truncate command

What it does: Clear a database table completely, and its structure and index constraints will not change!

-- empty student surface
TRUNCATE `student`;

The difference between delete and truncate

• Same thing: you can delete data without deleting table structures
• Different:
  • TRUNCATE resets self-incrementing columns, counters return to zero
  • TRUNCATE does not affect transactions

-- test delete and truncate Differences between
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
  `coll` VARCHAR(20) NOT NULL,
  PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `test`(`coll`)  VALUES ('1'),('2'),('3');

DELETE FROM `test`; -- Will not affect self-growth
TRUNCATE TABLE `test`; -- Self-increasing returns to zero

Know it:

DELETE problem, restart database, phenomena

• INNODB self-growth starts at 1 (in memory, loss of power)
• MYISAM continues from the previous increment (there are files, no loss)

4 DQL Query Data

4.1 DQL

(DQL: Date Query Language Data Query Language)

• Use it to select all queries
• It can do simple queries, complex queries
• The Core Language, the Most Important Language in a Database
• The most frequently used statement

select complete syntax

SELECT[ALL|DISTINCT|DISTINCTROW|TOP] 
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,...]]} 
FROM tableexpression[,...][IN externaldatabase] 
[WHERE...] -- Specify the conditions for the results to be met
[GROUP BY...] -- Specify which groups the results will be grouped into
[HAVING...] -- Secondary conditions that must be met to filter grouped records
[ORDER BY...] -- Specify that query records are sorted by one or more criteria
[WITH OWNERACCESS OPTION] 

4.2 Specify Query Fields

-- Query all students. SELECT field FROME surface
SELECT * FROM `student`;

-- Query specified fields
SELECT `StudentNo`,`StudentName` FROM `student`;

-- Alias, give the result a name AS You can alias fields or tables
SELECT `StudentNo` AS School Number,`StudentName` AS Student Name FROM `student` AS s;

-- function Concat(a, b) Split String
SELECT CONCAT('Full name:'，`StudentName`) AS New name FROM `student`;

Grammar:

SELECT field,...FROM table

Sometimes, column names are not so knowledgeable. We alias AS field names AS aliases superstitious AS aliases

Removing DISTINCT

Role: Remove duplicate data from SELECT statement queries

-- Query a student who has taken the exam and got the results
SELECT * FROM ressult; -- Query all test results
SELECT `StudentNo` FROM result; -- Query which classmates are taking the exam
SELECT DISTINCT `StudentNo` FROM result; -- Find duplicate data, remove

Columns (expressions) in Databases

SELECT VERSION(); -- Query system version (function)
SELECT 100 * 3 - 1 AS Calculation results; -- To calculate (an expression)
SELECT @@auto_increment; -- Query incremental step (variable)

-- Student Test Results + 1 Subview
SELECT `StudentNo`, `StudentResult` + 1 AS 'After scoring' FROM result;

Expressions in the database: text values, columns, NULL, functions, calculation expressions, system variables...

4.3 where conditional clause

Role: Retrieve eligible values from data

Logical operators

operator	grammar	describe
and &&	a and b a&&b	Logical And, both true, result true
or ||	a or b a||b	Logical OR, one of which is true, results are true
Not !	not a !a	Logical NOT, TRUE FALSE, FALSE TRUE

Use English letters whenever possible

SELECT `StudentNo`,`StudentResult` FROM `result`;

-- Query exam results at 95～100 Intersection
SELECT `studentNo`,`StudentResult` FROM result
WHERE `StudentResult` >= 95 AND `StudentResult` <= 100;

-- and &&
SELECT `studentNo`,`StudentResult` FROM result
WHERE `StudentResult` >= 95 && `StudentResult` <= 100;

-- Fuzzy Query (Interval)
SELECT `studentNo`,`StudentResult` FROM result
WHERE `StudentResult` BETWEEN 95 AND 100;

-- Achievements of students other than Student 1000
SELECT `studentNo`,`StudentResult` FROM result
WHERE `StudentNo` != 100;

-- != not
SELECT `studentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo` = 1000;

Fuzzy Query: Comparison Operators

operator	grammar	describe
IS NULL	a is null	If the operator is NULL, the result is true
IS NOT NULL	a is not null	If the operator is not NULL, the result is true
BETWEEN	a between b and c	If a is between b and c, the result is true
LIKE	a like b	SQL matches, if a matches b, the result is true
IN	a in(a1,a2,a3...)	If A is between a1, a2, a3, etc., the result is true

-- Query a classmate named Liu
-- % Represents 0 to any character
-- _ Represents a character
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE 'Liu%';

-- Query a classmate named Liu, who has only one word after his name
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE 'Liu_';

-- Query a classmate named Liu, who has only two words after his name
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE 'Liu__';

-- Query students with the word "Jia" in the middle
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '%Jia%';



-- in Specific one or more values
-- Query students at 1001, 1002, 1003
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentNo` IN(1001,1002,1003);

-- Query students in Anhui, Henan
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('Anhui','Henan');



-- null    not null
-- Query for students with empty addresses
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `adress` = '' OR `adress` IS NULL;

-- Query students with birth date, that is, not empty
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;

-- Query for classmates without birth date is empty
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL;

4.4 Joint Table Query join

-- Joint Table Query join

-- Query the students who took the exam (number, name, subject number, score)
SELECT * FROM `student`;
SELECT * FROM `result`;

/*
thinking
1,Analysis requirements, analysis query fields from those tables
2,Determine which connection to use? Seven
 Determine the intersection (which data in the two tables is the same)
Criteria for judgment: StudentNo in the student table = StudentNo in the report table
*/


-- join (Joined table) on (Conditions to Judge) Join Query
-- where Equivalent Query

SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` as r
ON s.`StudentNo` = r.`StudentNo`;

-- Right Join
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` as r
ON s.`StudentNo` = r.`StudentNo`;

-- Left Join
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
LEFT JOIN `result` as r
ON s.`StudentNo` = r.`StudentNo`;

-- Query students who are absent
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
LEFT JOIN `result` as r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult` IS NULL;


-- Think Question (inquired about the students who took the exam: number, student name, subject name, score)
/*thinking
1,Analysis requirements, analysis query fields from those tables, student, result, subject (join query)
2,Which query do you want to use?
Determine the intersection (which data is the same in both tables)
Criteria for judgment: StudentNo in the student table = StudentNo in the report table
*/

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON r.`StudentNo` = s.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`;

-- What data do I want to query select
-- Which tables to check from FROME surface xxx JOIN Joined tables ON Intersection Conditions
-- Assuming there is a multiple table query, slowly, query two tables before slowly increasing

-- FROM a LEFT JOIN b
-- FROM a RIGHT JOIN b

The difference between join on and where

1. The on condition is the condition used when generating a temporary table and returns records from the left table regardless of whether the condition in on is true or not.
2. where condition is the condition that filters the temporary table after it has been generated. At this time, there is no meaning of left join (the record of the left table must be returned), and all conditions are filtered out if they are not true.

Self-connection

CREATE TABLE `category`(
	`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'theme id',
  `pid` INT(10) NOT NULL COMMENT 'father id',
  `categoryName` VARCHAR(50) NOT NULL COMMENT 'Theme Name',
  PRIMARY KEY(`categoryid`)
)ENGINE = INNODB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8;

INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES ('2','1','information technology'),
('3','1','software development'),
('4','3','data base'),
('5','1','Art Design'),
('6','3','web Development'),
('7','5','ps technology'),
('8','2','Office Information');

Your own table is joined to your own table, core: one table can be split into two identical tables

Parent Class

categoryid	categoryName
2	information technology
3	software development
5	Art Design

Subclass

pid	categoryid	categoryName
3	4	information technology
2	8	Office Information
3	6	web development
5	7	Art Design

Operation: Query the parent's subclass relationship

Parent Class	Subclass
information technology	Office Information
software development	information technology
software development	web development
Art Design	ps Technology

-- Query parent-child information: think of a table as two identical tables
SELECT a.`categoryName` AS 'Parent Column',b.`categoryName` AS 'Subcolumn'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`;

4.5 Paging and Sorting (limit and order by)

order by

-- Sort Ascending ASC    Descending order DESC
-- ORDER BY
-- Query results are sorted in descending order by results
-- Query Participation Data Structure-1 Examination information for students: number, student name, subject name, score 
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
From `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = 'database structure-1'
ORDER BY `StudentResult` ASC;

limit

-- 100 ten thousand
-- Why Paging
-- Relieving database pressure for a better experience, waterfall flow

-- Page breaks, showing only five pieces of data per page
-- Grammar: limit Current page, page size
-- Page application: current, total number of pages, page size
-- limit 0,5 1～5
-- limit 1,5 2~6
-- limit 6,5 6~11

--
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
From `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = 'database structure-1'
ORDER BY `StudentResult` ASC
LIMIT 1,5;

-- First page limit 0,5		(1-1)*5
-- Page 2 limit 5,5		(2-1)*5
-- Page 3 limit 10,5		(3-1)*5
-- No. N page	limit 0,5		(n-1)*pageSize,pageSize
-- [pageSize:Page Size]
-- [(n-1)*pageSize:Start Value]
-- [n:Current Page]
-- [Total Data/Page Size = PageCount]

-- Reflection
-- query JAVA Top 10 students with a score greater than 80 in the first school year (number, name, course name, score)
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`SubjectResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = 'JAVA - 1' AND `SubjectResult` >= 80
ORDER BY `SubjectResult` DESC
LIMIT 0,10;

4.6 Subqueries

-- 1,Query database structure-1 All test results (number, subject number, results), in descending order
-- Mode 1: Use join query
SELECT `StudentNo`,`SubjectNo`,`SubjectResult`
From `subject` AS sub
INNER JOIN `result` AS r
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE `SubjectName` = 'database structure-1'
ORDER BY StudentResult DESC;

-- Mode 2: Use subqueries (from inside to outside)
SELECT `StudentNo`,`SubjectNo`,`SubjectResult`
From `result`
WHERE `SubjectNo` = (
	SELECT `SubjectNo` From `subject`
  WHERE `SubjectName` = 'database structure-1'
)
ORDER BY StudentResult DESC;

-- 2,Number and name of a student with a score of not less than 80
-- Mode 1: Use join query
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student` AS s
INNER JOIN `result`
ON r.`StudentNo` = s.`StudentNo`
WHERE `SubjectResult` >= 80;

-- Mode 2: Use subqueries
SELECT s.`StudentNo`,`StudentName`
FROM `student`
WHERE s.`StudentNo` = (
	SELECT `StudentNo`
  FROM `result`
  WHERE `SubjectResult` >= 80
);

-- On this basis, add a subject, Higher Mathematics-2
-- Use join query
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student` AS s
INNER JOIN `result` AS r
ON r.`StudentNo` = s.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectResult` >= 80 AND `SubjectName` = 'Advanced mathematics-2';

-- Use subqueries
SELECT DISTINCT `StudentNo`,`StudentName`
FROM `student`
WHERE `StudentNo` = (
	SELECT `StudentNo`
  FROM `result`
  WHERE `SubjectResult` >= 80	AND `SubjectNo` = (
  	SELECT `SubjectNo`
  	FROM `subject`
    WHERE `SubjectName` = 'Advanced mathematics-2'
  )
);

Think Question: Query the result information of the top five students in c Language-1 (number, name, score)

-- query c language-1 Information on the results of the top five students (number, name, score)
-- 1,Use join query
SELECT DISTINCT s.`StudentNo`,`StudentName`,`SubejctResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = 'c language-1'
ORDER BY `SubjectResult` DESC
LIMIT 0,5;

-- 2,Use subqueries
SELECT DISTINCT s.`StudentNo`,`StudentName`,`SubjectResult`
FROM `student` AS s,`result` AS r
WHERE s.`StudentNo` = r.`StudentNo` AND `SubjectNo` = (
	SELECT `SubjectNo`
  FROM `subject`
  WHERE `SubjectName` = 'c language-1'
)
ORDER BY `SubjectResult` DESC
LIMIT 0,5;

4.7 Grouping and filtering

-- Query average, highest, lowest, average over 80 points for different courses
-- Core: Grouping according to different courses
SELECT `SubjectName`, AVG(`StudentResult`),MAX(`StudentResult`),MIN(`StudentResult`)
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.`SubjectNo`; -- By which field to group
HAVING AVG(`StudentResult`) > 80;

The difference between having and where

where filters fields that already exist in database tables, while having filters fields that were previously filtered.

5 MySQL function

5.1 Common Functions

-- Mathematical operations
SELECT ABS(-8); -- Absolute value 8
SELECT CEILING(9.4) -- Round up 10
SELECT FLOOR(9.4) -- Round down 9
SELECT RAND() -- Return a 0～1 Random number between 
SELECT SIGN(-10) -- Symbols for Judging a Number -1         0-0    Negative Return -1，Positive number returns 1

-- String function
SELECT CHAR_LENGTH('Even the smallest sail can go far') -- String Length
SELECT CONCAT('I','love','You') -- Split String
SELECT INSERT('I love programming helloworld',1,2,'Super love') -- Query, replacing a length from a location
SELECT LOWER('KUANGSHEN') -- Lowercase letters
SELECT UPPER('kuangshen') -- Larger letters
SELECT INSTR('kuangshen','h') -- Returns the index of the first occurrence of a substring
SELECT REPLACE('The madness says that perseverance will triumph','Insist','Strive') -- Replace the specified string that appears
SELECT SUBSTR('The madness says that perseverance will triumph',4,6) -- Returns the specified substring (source string, intercept location, intercept length)
SELECT REVERSE('I get on horse in the morning') -- Reversal

-- Query classmates with last name and replace with Zou
SELECT REPLACE(`StudentName`,'week','Zou')
FROM `student`
WHERE `StudentName` LIKE 'week%'；

-- Time and date functions (remember)
SELECT CURRENT_DATE() -- Get the current date
SELECT CURDATE() -- Get the current date
SELECT NOW() -- Get the current time
SELECT LOCALTIME() -- Local Time
SELECT SYSDATE() -- system time

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- system
SELECT SYSTEM_USER() -- SELECT USER()
SELECT VERSION

5.2 Aggregation Function

Function Name	describe
COUNT()	count
SUM()	Summation
AVG()	average value
MAX()	Maximum
MIN()	minimum value
...

-- Aggregate function
-- Query the number of students
SELECT COUNT(`StudentName`) FROM `student`; -- COUNT(field)  Will ignore all null value
SELECT COUNT(*) FROM `student`; -- COUNT(*)  Will Not Ignore null value
SELECT COUNT(1) FROM `student`; -- COUNT(1)  Will Not Ignore null value
/*
In terms of execution efficiency:
   There are slight differences between them, and MySQL optimizes count (*).
   (1)If listed as the primary key, count is more efficient than count(1)  
   (2)If the column is not the primary key, count(1) is more efficient than count (column name)  
   (3)Count (primary key column name) is most efficient if a primary key exists in the table  
   (4)If there is only one column in the table, count(*) is most efficient  
   (5)If the table has multiple columns and no primary key exists, count(1) is more efficient than count(*)
*/

SELECT SUM(`StudentRusult`) AS The sum FROM `result`;
SELECT AVG(`StudentRusult`) AS Average FROM `result`;
SELECT MAX(`StudentRusult`) AS Top Score FROM `result`;
SELECT MIN(`StudentRusult`) AS Minimum score FROM `result`;

-- Query average, highest, lowest, average over 80 points for different courses
-- Core: Grouping according to different courses
SELECT `SubjectName`, AVG(`StudentResult`),MAX(`StudentResult`),MIN(`StudentResult`)
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.`SubjectNo`; -- By which field to group
HAVING AVG(`StudentResult`) > 80;

5.3 Database Level MD5 Encryption (Extended)

What is MD5?

The MD5 information digest algorithm, a widely used cryptographic hash function, produces a 128-bit (16-byte) hash value that ensures complete and consistent transmission of information.

It mainly enhances algorithm complexity and irreversibility.

MD5 is not reversible, the specific value of MD5 is the same;

How MD5 cracks websites, with a dictionary behind it, encrypted values encrypted by MD5

-- test MD5 encryption

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

-- Insert Data Clear Password
INSERT INTO `testmd5`
VALUES(1,'zhangsan','123456'),
(2,'lisi','123456'),
(3,'wangwu','123456');

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

-- Encrypt when inserting
INSERT INTO `testmd5` VALUES(4,'xiaoming',MD5('123456'));

-- How to verify: Passwords passed in by users md5 Encrypt and then compare the encrypted values
SELECT * FROM `testmd5` WHERE `name` = 'xiaoming' AND `pwd` = MD5('123456');

6 Transactions

6.1 What is a transaction?

Either all succeeded or all failed

1. SQL Executes A to B Transfer A 1000 ->200 B 200

2. Money A 800 - > B 400 received by SQL Execution B

Place a set of SQL in a batch to execute

Transaction Principles

ACID Principle:

• Atomicity: These two steps succeed or fail together, and not just one action can occur; (Either both succeed or both fail)
• Consistency: Consistency: Consistency of the state before and after a transaction (the final total is equal); (Consistency of data integrity before and after a transaction)
• Isolation: Operate simultaneously for multiple users, mainly to exclude the impact of other transactions on this transaction; (Transaction isolation is when multiple users access the database concurrently, transactions opened by the database for each user cannot be interfered with by operational data of other transactions, transactions should be isolated from each other)
• Durability: Indicates that data is not lost after a transaction ends for external reasons; (The transaction is not committed and is restored to its original state; the transaction has been committed and the persistence channel database is persisted; that is, once a transaction is committed it cannot be reversed.)

Transaction isolation level

• Dirty read: A transaction that reads uncommitted data from another transaction
• Non-repeatable reading: Reading a row of data in a table within a transaction results in different results over time. (Not necessarily an error, but in some cases the error)
• False reading (magic reading): refers to reading data inserted by another transaction within one transaction, resulting in inconsistent reading.

Execute Transaction

-- affair
-- mysql Is the default open transaction autocommit
SET autocommit = 0 -- close
SET autocommit = 1 -- open

-- Manual transaction
-- Transaction Open
START TRANSACTION -- Marks the beginning of a transaction, after which sql All in the same transaction

INSERT xxx
INSERT xxx

-- Submit: Persistence (Success!)
COMMIT
-- Rollback: back to the original (failed)
ROLLBACK

-- End of Transaction
SET autocommit = 1 -- Turn on automatic submission

-- understand
SAVEPOINT Save Point Name -- Set a save point for a transaction
ROLLBACK TO SAVEPOINT -- Rollback to savepoint
RELEASE SAVEPOINT Save Point Name -- Undo Save Point

Simulated transfer

-- Transfer accounts
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

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

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

-- Simulated transfer
SET AUTOCOMMIT = 0; -- Turn off automatic submission
START TRANSACTION -- Open a transaction (set of transactions)

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

COMMIT; -- Commit the transaction and it is persisted
ROLLBACK; -- RollBACK

SET AUTOCOMMIT = 1; -- Restore Defaults

7 Index

MySQL officially defines an index as a data structure that helps MySQL obtain data efficiently.

By extracting the sentence backbone, you can get the essence of an index: an index is a data structure.

Classification of 7.1 Index

In a table, there can only be one primary key index and more than one unique index

• Primary Key Index PRIMARY KEY

  • Unique ID, non-repeatable, can only have one column as primary key

• Unique Index UNIQUE KEY

  • Avoid duplicate rows, unique indexes can be duplicated, and multiple columns can be identified as unique indexes

• General Index KEY/INDEX

  • By default, the index/key key key value is set

• FullText Index

  • Only under a specific database engine, MyISAM
  • Quick positioning data

Basic Grammar

-- Use of indexes
-- 1,Adding an index to a field when creating a table
-- 2,Add index after creation

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

-- Add a full-text index (Index Name)Column Name
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);

-- EXPLAIN Analysis sql Status of implementation

EXPLAIN SELECT * FROM `student`; -- Non-Full-Text Index

SELECT * FROM `student` WHERE MATCH(`StudentName`) AGAINST('Liu');

7.2 Test Index

-- Insert 1 million data.
DELIMITER $$
-- Function must be written before writing, flag
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('user',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;
SELECT mock_data() -- Execute this function to generate a million pieces of data

-- id_Table Name_Field name     Common naming conventions for Index Names
-- CREATE INDEX Index Name ON surface(field)；
CREATE INDEX id_app_user_name ON app_user(`name`);

-- Cost: More storage is used, but it's worth it

Indexes are less useful when you have a small amount of data

But when there's a lot of data, it's useful

7.3 Indexing Principles

• Not as many indexes as possible
• Do not index process change data
• Tables with small amounts of data do not need to be indexed
• Indexes are typically added to fields used for queries

Data structure of index

Hash type index

Default data structure for Btree INNODB

Read in detailhttp://blog.codinglabs.org/articles/theory-of-mysql-index.html

8 Rights Management and Backup

8.1 User Management

-- Create User CREATE USER User name IDENTIFIED BY 'Password'
CREATE USER kuangshen IDENTIFIED BY '12345';

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

-- Modify password (change specified user password)
SET PASSWORD FOR kuangshen = PASSWORD('12345');

-- rename RENAME USER Original name TO New name
RENAME USER kuangshen TO kuangshen2;

-- User Authorization ALL PRIVILEGES Full permissions, Libraries.surface
-- ALL PRIVILEGES Everything but authorization
GRANT ALL PRIVILEGES ON *.* TO kuangshen2;

-- Query permissions
SHOW GRANTS FOR kuangshen2; -- Query permissions for specified users
SHOW GRANTS FOR root@localhost; -- ROOT User-managed privileges GRANT ALL PRIVILEGES ON *.* WITH GRANT OPTION

-- Revoke Permission REVOKE Which permissions, which library to revoke, to whom to revoke
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2;

-- delete user
DROP USER kuangshen;

8.2 MySQL Backup

Why backup?

• Ensure important data is not lost
• Data Transfer

How do I back up my MySQL database?

• Direct Copy of Physical Files
• Export in Visualizer
• Export mysqldump using the command line

9 specification database design

9.1 Why design is required

When the database is complex, we need to design it

Bad database design

• Data redundancy, waste of space
• Inserting and deleting databases can be cumbersome and unusual [blocking the use of physical foreign keys]
• Poor program performance

Good database design

• Save memory space
• Ensuring database integrity
• Convenient for us to develop the system

In software development, about database design

• Analysis Requirements: Analyze business and database needs to be addressed
• Summary Design: Design Diagram E-R Diagram

Steps to design a database: (personal blog)

• Collect information and analyze requirements
  • User table (user logon logoff, user profile, blog, category creation)
  • Categories (article categories, who created them)
  • Article Table (article information)
  • Friends List (Friends Chain Information)
  • Custom tables (system information, a key child, or some primary field)
• Identifying entities (dropping requirements on each field)
• Identify relationships between entities
  • Blog: user --> blog
  • Create categories: user --> category
  • Attention: user --> user
  • Friend chain: links
  • Comment: user ->user ->blog

The Three Paradigms of 9.2

Why do I need data normalization?

• Repeated information
• Update Exception
• Insert Exception
  • Information cannot be displayed properly
• Delete Exception
  • Loss of valid information

Three Paradigms

First Norm (1NF)

Atomicity: guarantees that each column is indivisible

Second Norm (2NF)

Prerequisite: satisfy the first paradigm

Each table describes only one thing

Third Norm (3NF)

Prerequisite: satisfy the first and second paradigms

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

(Specification database design)

Specification and performance issues

Associated queries must have no more than three tables

• Consider the needs and objectives of commercialization (cost, user experience!) Database performance is even more important.
• When it comes to standardizing performance, you need to think about it appropriately!
• Intentionally add redundant fields to some tables. (From multi-table queries to table queries)
• Intentionally add some computed columns. (Queries from large data to small data: Indexes)

10 JDBC (Key)

10.1 Database Driver

Driver: Sound Card, Graphics Card, Database

Our programs will be database driven and work with databases!

10.2 JDBC

sun provides a specification, commonly referred to as JDBC, for simplifying developer (unified database) operations.

Implementation of these specifications is manufacturer operated ~

java.sql

Javax.sql

You also need to import a database driver package

mysql-connector-java-5.1.47.jar

10.3 First JDBC program

public class JdbcFirstDemo{
  public static void main(String[] args) throws ClassNotFoundException, SQLException{
    //1 Load Driver
    //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
    Class.forName("com.mysql.jdbc.Driver");//Fixed Writing, Load Driver
    
    //2 user information url
    //useUnicode=true&characterEncoding=utf8&useSSL=true
    String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
    //jdbcstudy is the specific database
    String name = "root";
    String password = "123456";
    
    //3 Connection successful, database object
    Connection connection = DriverManager.getConnection(url,username,password);
    
    //4 Object executing sql
    Statement statement = connection.createStatement();
    
    //5 Objects that execute SQL to execute sql, there may be results, see the returned results
    String sql = "SELECT * FROM users";
    
    ResultSet resultSet = statement.excuteQuery(sql);//Returns a result set that encapsulates the results of all our queries
    
    while(resultSet.next()){
      System.out.println("id=" + resultSet.getObject("id"));
      System.out.println("name=" + resultSet.getObject("NAME"));
      System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
      System.out.println("email=" + resultSet.getObject("email"));
      System.out.println("birth=" + resultSet.getObject("birthday"));
      
    }
    
    //6 Release the connection
    resultSet.close();
    statement.close();
    connection.close();
  }
  
}

Step Summary

1. Load Driver
2. Connect to Database DriverManager
3. Object statement executing sql
4. Get the returned result set
5. Release Connection

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//Fixed Writing, Load Driver
Connection connection = DriverManager.getConnection(url,username,password);

//connection represents the database
//Database Settings Autocommit
connection.setAutoCommit();
//Transaction Commit
connection.commit();
//rollback
connetcion.rollback();

url

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql --3306
//jdbc:mysql://host address: port number/database name? Parameter 1&parameter 2&parameter 3

//oracle --1521
//jdbc:oracle:thin:@localhost:1521:sid

Object that Statement executes sql PrepareStatement executes sql

String sql = "SELECT * FROM users";

statement.excuteQuery(sql); //Query operation returns ResultSet
statement.excute(sql);//Execute any SQL
statement.excuteUpdate(sql);//Update, insert, delete all use this to return an affected number of rows

ResultSet Query Result Set: Encapsulates all query results

Gets the specified data type

// Use without knowing the column type
resultSet.getObject();
// Use the specified type if you know the type of column
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
.....

Traversal, Pointer

resultSet.beforeFirst();//Move to the front
resultSet.afterLast();//Move to the end
resultSet.next();//Move to Next Data
resultSet.previous();//Move to the previous line
resultSet.absolute(row);//Move to specified line

Release Resources

//Release Connection
resultSet.close();
statement.close();
connection.close();//Consume resources, turn off when used up

10.4 statement object

The statement object in jdbc is used to send SQL statements to the database. To complete the database add-delete check, you only need to send add-delete check statements to the database through this object.

CRUD Operation-create

Use the executeUpdate (String sql) method to complete the data addition operation, sample operation:

Statement st = conn.createStatement();
String sql = "insert into user(...) values(....)";
int num = st.executeUpdate(sql);
if(num>0){
	System.out.println("Insertion succeeded!");
}

CRUD Operation - delete

Complete data deletion using executeUpdate(String sql) method

Statement st = conn.createStatement();
String sql = "delete from user where id = 1";
int num = st.executeUpdate(sql);
if(num>0){
	System.out.println("Delete successful!");
}

CRUD Operation - update

Complete data modification using executeUpdate(String sql) method

Statement st = conn.createStatement();
String sql = "update user set name = '' where name = '' ";
int num = st.executeUpdate(sql);
if(num>0){
	System.out.println("Successfully modified!");
}

CRUD Operation - read

Use executeQuery(String sql) method to complete database query operation

Statement st = conn.createStatement();
String sql = " select * from user where id = 1 ";
int num = st.executeUpdate(sql);
while(rs.next()){
	//Depending on the data type of the get list, the corresponding methods of rs are invoked to map to the JAVA object, respectively
}

Extract Profile

//db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
name=root
password=123456

Establish Tool Class Utils Class

public class JbdcUtils {
  private static String driver = null;
  private static String url = null;
  private static String username = null;
  private static String password = null;
  
  static{
    try{
      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 Driver only loads once
      Class.forName(driver);
    }catch(Exception e){
      e.printStackTrace();
    }
  }
  
  //Get Connections
  public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url,username,password); 
  }
  
  //Release Connection Resources
  public static void release(Connection conn, Statement st, Result rs){
    if(rs != null){
      try{
        rs.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
    if(st != null){
      try{
        st.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
    if(conn != null){
      try{
        conn.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
  }
}

Test Tool Class

insert data

public class TestInsert{
  public static void main(String[] args){
    Connection conn = null;
    Statement st = null;
    Result rs = null;
    
    try{
      conn = JdbcUtils.getConnection();//Get Database Objects
      st = conn.createStatement();//Get execution object of sql
      String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
        "VALUES(4,'kuangshen','123456','12345678@qq.com','2020-01-01')";
      int i = st.executeUpdate(sql);
      if(i > 0){
        System.out.println("success!");
      }
    }
    catch(SQLException e){
      e.printStackTrace();
    }finally{
        JdbcUtils.relese(conn, st, rs);
    }
  }
}

Delete data

public class TestDelete{
	public static void main(String[] args){
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    
    try{
      conn = JdbcUtils.getConnection();
      st = conn.getStatement();
      
      String sql = "DELETE FROM users WHERE id = 4";
      
      int i = st.executeUpdate(sql);
      if(i > 0){
        System.out.println("success!");
      }
    }catch(SQLException e){
      e.printStackTrace();
    }finally{
      JdbcUtils.relese(conn, st, rs);
    }
  } 
}

Update Data

public class TestUpdate{
  public static void main(String[] args){
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    
    try{
      conn = JdbcUtils.getConnection();
      st = conn.getStatement();
      
      Stirng sql = "UPDATE users SET `NAME`='kuangshen',`email`='12345678@qq.com' WHERE id = 1";
      
      int i = st.executeUpdate(sql);
      if(i > 0){
        System.out.println("success!");
      }
    }catch(SQLException e){
      e.printStackTrace();
    }finally{
      JdbcUtils.relese(conn, st, rs);
    }
  }
}

Query Data

public class TestSelect{
  public static void main(String[] args){
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    
    try{
      conn = JdbcUtils.getConnection();
      st = conn.getStatement();
      
      //SQL
      String sql = "SELECT * FROM users WHERE id = 1";
      rs = st.executeQuery(sql);//Return a result set when the query is complete
      
      while(rs.next()){
        System.out.println(rs.getString("NAME"));
      }
    }catch(SQLException e){
      e.printStackTrace();
    }finally{
      JbdcUtils.relese(conn, st, rs);
    }
  }
}

10.5 SQL Injection

sql has a vulnerability and can be attacked, resulting in data disclosure

SQL injection refers to a web application that does not judge or filter the validity of user input data. An attacker can add additional SQL statements to the end of pre-defined query statements in the web application to implement illegal operations without the knowledge of the administrator, in order to deceive the database server to execute any unauthorized queries and get further resultsCorresponding data information.

If a programmer is not aware of information security, uses a dynamic construction of SQL statements to access the database, and does not validate the user's input, there is a high possibility of a SQL injection vulnerability. Generally, the page's error information is used to determine whether there is a SQL injection vulnerability.

public class SQLInjection{
  public static void main(String[] args){
    login("kuangshen","123456");
    
    //login("'or' 1=1","'or' 1=1")
    //select * from users where NAME =''or' 1=1' AND `password`=''or' 1=1'
    //1=1 is equal SQL will be stitched
  }
  
  //Landing Business
  public static void login(String username, String password){
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    
    try{
      conn = JdbcUtils.getConnection();
      st = conn.getStatement();
      
      //SQL
      String sql= "select * from users where NAME ='"+username+"' AND `password`='"+password+"'";
      rs = st.executeQuery(sql);
      
      while(rs.next()){
        System.out.println(rs.getString("NAME"));
        System.out.println(rs.getString("password"));
      }
      
    }catch(SQLException e){
      e.printStackTrace();
    }finally{
      JdbcUtils.relese(conn, st, rs);
    }
  }
}

10.6 PreparedStatement object

Prevents SQL injection and is more efficient

insert data

public class TestInsert{
  Connection conn = null;
  PreparedStatement st = null;
  
  try{
    conn = JdbcUtils.getConnection();
    
    //Difference
    //Use?Placeholder instead of parameter
    //String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
    //    "VALUES(4,'kuangshen','123456','12345678@qq.com','2020-01-01')";
    String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
    
    
    st = conn.preparedStatement(sql);//Precompile sql, write SQL first, do not execute
    
    //Manually assign parameters
    st.setInt(1, 4);
    st.setString(2, "kuangshen");
    st.setString(3,"123456");
    st.setString(4,"12345678@qq.com");
    //Note: sql.Date database
    //				util.Date. Java new Date.getTime() Gets the timestamp
    st.setDate(5,new java.sql.Date(new Date().getTime()));
    int i = st.executeUpdate();
    if(i > 0){
      System.out.println("success!");
    }
  }catch(SQLException e){
    e.printStackTrace();
  }finally{
    JdbcUtils.relese(conn, st, rs);
  }
}

Delete data

public class TestDelete{
	public static void main(String[] args){
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    
    try{
      conn = JdbcUtils.getConnection();
      
      String sql = "DELETE FROM users WHERE id = ?";//Write sql first, then do not execute
      st = conn.perpareStatement(sql);
      
      //Manually assign parameters
      st.setInt(1, 4);
      
      int i = st.executeUpdate();
      if(i > 0){
        System.out.println("success!");
      }
    }catch(SQLException e){
      e.printStackTrace();
    }finally{
      JdbcUtils.relese(conn, st, rs);
    }
  } 
}

Update Data

public class TestUpdate{
  public static void main(String[] args){
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    
    try{
      conn = JdbcUtils.getConnection();
      
      String sql = "UPDATE `users` SET `NAME` = ? WHERE id = ?";
      st = conn.perpareStatement(sql);
      
      setString(1,"kuangshen");
      setInt(2,1);
      
      int i = st.executeUpdate();
      if(i > 0){
        System.out.println("success!");
      }
    }catch(SQLExpetion e){
      e.printStackTrace();
    }finally{
      JdbcUtils.relese(conn, st, rs);
    }
  }
}

Query Data

public class TestSelect{
  public static void main(String[] args){
    Connection conn = null;
    PreparedStatement st = null;
    RusultSet rs = null;
    
    try{
      conn = JdbcUtils.getConnection();
      
      String sql = "SELECT * FROM `users` WHERE id = ?";//Writing sql
      st = preparedStatement(sql);//Precompiled, not executed
      
      st.setInt(1, 1);//Setting Injection Parameters
      
      rs = st.executeQuery();//Execute sql statement
      while(rs.next()){//Traversal result table
        System.out.println(rs.getString("NAME"));
      }
      
      
    }catch(SQLException e){
      e.printStackTrace();
    }finally{
      JdbcUtils.relese(conn, st, rs);
    }
  }
}

How does PreparedStatement prevent sql injection?

public class SQLInjection{
  public static void main(String[] args){
    login("kuangshen","123456");
    //login("'or' 1=1","'or' 1=1")
  }
  
  //Landing Business
  public static void login(String username, String password){
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    
    try{
      conn = JdbcUtils.getConnection();
      
      //PreparedStatement prevents the nature of sql injection by treating parameters passed in as characters
      //Suppose there is escaping pride, for example,'will be translated directly'
      String sql= "select * from users where NAME =? AND `password`=?";//sql statement
      st = conn.preparedStatement(sql);//Precompiled, MyBatis not executed
      
      st.setString(1,username);//Set Injection Parameter Value
      st.setString(2,password);//Set Injection Parameter Value
      
      rs = st.executeQuery();
      
      while(rs.next()){
        System.out.println(rs.getString("NAME"));
        System.out.println(rs.getString("password"));
      }
      
    }catch(SQLException e){
      e.printStackTrace();
    }finally{
      JdbcUtils.relese(conn, st, rs);
    }
  }
}

10.7 Use IDEA to connect to databases

View in detail:https://www.cnblogs.com/Ran-Chen/p/9646187.html

10.8 JDBC Operational Transactions

Transaction: Either all succeeded or all failed

ACID Principle

• Atomicity: either all or none
• Consistency: Total data is unchanged
• Isolation: Multiple processes do not interfere with each other
• Persistence: Once submitted irreversibly, persist to database

Isolation issues

• Dirty Read: A transaction read a transaction that was not committed by another transaction
• Non-repeatable reading: within the same transaction, data in tables is read repeatedly and table data is changed
• False reading: reading other people's data within a transaction, resulting in inconsistent results

Simulate A to B transfer 100 process

public class TestTransaction1{
  public static void main(String[] args){
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet = null;
    
    try{
      conn = JdbcUtils.getConnection();
      
      // Turn off automatic commit of database, transaction will be opened automatically
      conn.setAutoCommit(false);//Close Transaction
      
      String sql1 = "update account set money=money-100 where name = 'A'";
      st = conn.preparedStatement(sql1);
      st.executeUpdate();
      
      String sql2 = "update account set money=money+100 where name = 'B'";
      st = conn.preparedStatement(sql2);
      st.executeUpdate();
      
      //Business completed, transaction committed
      conn.commit();
      System.out.println("success!");
      
    }catch(SQLException e){
      try{
        conn.rollback(); // Failure rolls back the transaction
      }catch(SQLException e1){
        e1.printStackTrace();
      }
      e.printStackTrace();
    }finally{
      JdbcUtils.relese(conn, st, rs);
    }
  }
}

1 Open Transaction

Two sets of businesses complete execution and commit transactions

3 Definition rollback statements that can be displayed in the catch statement, but will be rolled back if the default fails

JDBC operational transaction with rollback point

public void transaction() throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        Savepoint sp = null;   //Represents a rollback point object
        try{
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);  //Open a transaction, equivalent to the start transaction command

            String sql1 = "update account set money=money-100 where name='a'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            sp = conn.setSavepoint();   //Set rollback point here

            int x = 1/0;      //Error simulating transaction processing here

            String sql2 = "update account set money=money+100 where name='b'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            conn.commit();   //Submit Transaction
        }catch (Exception e) {
            conn.rollback(sp);    //Rollback to specified rollback point
            conn.commit();        //Submitting the transaction again after rollback ensures that the SQL before the rollback point can be executed

        }finally{
            JdbcUtils.release(conn, st, rs);
        }
    }

10.9 Database Connection Pool

Database Connection - Execution Complete - Release

Connection - Releasing system resources is a waste

Pooling technology: Prepare some resources in advance to connect them.

Example: Open door - Salesperson: Wait - Service - Close door

Number of common connections: 10

Minimum number of connections: 10

Maximum number of connections: 15 business maximum hosting limit, exceed enter waiting

Wait timeout: 100ms

Writing a connection pool requires only one interface, DataSource

Open Source Data Source Implementation

DBCP, C3P0, Druid: Alibaba

With these database connection pools in place, we no longer need to write code to connect to the database in our project development

DBCP

jar package needed: commons-dbcp-1.4 commons-pool-1.6

dbcpconfig.properties

#Connection settings This name is defined in the DBCP data source
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

#<!--Initialize Connection-->
initialSize=10

#Maximum number of connections
maxActive=50

#<!--Maximum idle connection-->
maxIdle=20

#<!--Minimum idle connection-->
minIdle=5

#<!--Timeout wait time in milliseconds 6000 milliseconds/1000 equals 60 seconds-->
maxWait=60000

#The JDBC driver must have connection property attributes attached to it in this format: [Property name = property;]
#Note that the attributes "user" and "password" are explicitly passed, so they do not need to be included here.
connectionProperties=useUnicode=true;characterEncoding=UTF8

#Specifies the auto-commit state of the connection created by the connection pool.
defaultAutoCommit=true

#driver default specifies the read-only state of the connection created by the connection pool.
#If this value is not set, the "setReadOnly" method will not be invoked. (Some drivers do not support read-only mode, such as Informix)
defaultReadOnly=

#driver default specifies the transaction level (TransactionIsolation) of the connection created by the connection pool.
#Available values are one of the following: (javadoc is available for details.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

JdbcUtils_DBCP

public class JbdcUtils {
  
  private static DataSource dataSource = null;
  
  static{
    try{
      InputStreanm in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
      Properties properties = new Properties();
      properties.load(in);
      
      //Create Data Source Factory Mode -->Create
      dataSource = BasicDataSourceFactory.createDataSource(properties);
      
    }catch(Exception e){
      e.printStackTrace();
    }
  }
  
  //Get Connections
  public static Connection getConnection() throws SQLException {
    return dataSource.getConnection(); //Get a connection from a data source
  }
  
  //Release Connection Resources
  public static void release(Connection conn, Statement st, Result rs){
    if(rs != null){
      try{
        rs.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
    if(st != null){
      try{
        st.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
    if(conn != null){
      try{
        conn.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
  }
}

TestDBCP

public class TestDBCP{
  Connection conn = null;
  PreparedStatement st = null;
  
  try{
    conn = JdbcUtils_DBCP.getConnection();
    
    //Difference
    //Use?Placeholder instead of parameter
    //String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
    //    "VALUES(4,'kuangshen','123456','12345678@qq.com','2020-01-01')";
    String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
    
    
    st = conn.preparedStatement(sql);//Precompile sql, write SQL first, do not execute
    
    //Manually assign parameters
    st.setInt(1, 4);
    st.setString(2, "kuangshen");
    st.setString(3,"123456");
    st.setString(4,"12345678@qq.com");
    st.setDate(5,new java.sql.Date(new Date().getTime()));
    int i = st.executeUpdate();
    if(i > 0){
      System.out.println("success!");
    }
  }catch(SQLException e){
    e.printStackTrace();
  }finally{
    JdbcUtils_DBCP.relese(conn, st, rs);
  }
}

C3P0

jar package needed: c3p0-0.9.5.5 change-commons-java-0.2.19

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- Default configuration, if not specified -->
    <default-config>
        <property name="user">zhanghanlun</property>
        <property name="password">123456</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/zhanghanlun</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="checkoutTimeout">30000</property>
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">3</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">2</property>
        <property name="maxStatements">200</property>
    </default-config>
    <!-- Named Configuration,Can be achieved through method calls -->
    <named-config name="test">
        <property name="user">zhanghanlun</property>
        <property name="password">123456</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- How many connections to grow at a time if there are not enough data connections in the pool -->
        <property name="acquireIncrement">5</property>
        <!-- Number of connections when initializing the database connection pool -->
        <property name="initialPoolSize">20</property>
        <!-- Maximum number of database connections in the database connection pool -->
        <property name="maxPoolSize">25</property>
        <!-- Minimum number of database connections in the database connection pool -->
        <property name="minPoolSize">5</property>
    </named-config>
</c3p0-config>

JdbcUtils_C3P0

public class JbdcUtils {
  
  private static ComboPooledDataSource dataSource = null;
  
  static{
    try{
      //Code version configuration
      //dataSource = new ComboPooledDataSource();
      //dataSource.setDriverClass();
      //dataSource.setUser();
      //dataSource.setPassword();
      //dataSource.setJdbcUrl();
      //dataSource.setMaxPoolSize();
      //dataSource.setMinPoolSize();
      
      //Create Data Source Factory Mode -->Create
      dataSource = BasicDataSourceFactory.createDataSource(properties);
      
    }catch(Exception e){
      e.printStackTrace();
    }
  }
  
  //Get Connections
  public static Connection getConnection() throws SQLException {
    return dataSource.getConnection(); //Get a connection from a data source
  }
  
  //Release Connection Resources
  public static void release(Connection conn, Statement st, Result rs){
    if(rs != null){
      try{
        rs.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
    if(st != null){
      try{
        st.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
    if(conn != null){
      try{
        conn.close();
      }catch(SQLException e){
        e.printStackTrace();
      }
    }
  }
}

TestC3P0

public class TestC3P0{
  Connection conn = null;
  PreparedStatement st = null;
  
  try{
    conn = JdbcUtils_C3P0.getConnection();//Originally achieved by oneself, but now achieved by others
    
    //Difference
    //Use?Placeholder instead of parameter
    //String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
    //    "VALUES(4,'kuangshen','123456','12345678@qq.com','2020-01-01')";
    String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
    
    
    st = conn.preparedStatement(sql);//Precompile sql, write SQL first, do not execute
    
    //Manually assign parameters
    st.setInt(1, 4);
    st.setString(2, "kuangshen");
    st.setString(3,"123456");
    st.setString(4,"12345678@qq.com");
    st.setDate(5,new java.sql.Date(new Date().getTime()));
    int i = st.executeUpdate();
    if(i > 0){
      System.out.println("success!");
    }
  }catch(SQLException e){
    e.printStackTrace();
  }finally{
    JdbcUtils_C3P0.relese(conn, st, rs);
  }
}

conclusion

No matter what data source you use, the DataSource interface will not change, and the method will not change