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
- Fields are separated by English commas.
- Fields can be omitted, but the following values must be one-to-one, not fewer;
- 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
- 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.
- 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
- Load Driver
- Connect to Database DriverManager
- Object statement executing sql
- Get the returned result set
- 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¶meter 2¶meter 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