The latest MySQL tutorial is easy to understand
javaEE: Enterprise java Web Development
Front end (page: display, data!)
Background (connection point: connect database JDBC, link the front end (control, control view jump, and transfer data to the front end))
Database (save data, Txt, Excel, word)
1.1 why learn database?
1. Job demand
2. In today's world, in the era of big data, those who get the database get the world.
3. Forced demand: save data to IOE
4. Database is the core DBA in all software systems
1.2. What is a database
Database (DB,DataBase)
Concept: data warehouse, software, installed on the operating system (window, linux, mac,)! SQL, which can store a large amount of data
Function: store data and manage data
1.3 database classification
Relational database: (SQL)
- MySQL，Oracle,SqlServer,DB2,SQLite
- Store data through the relationship between tables and between rows and columns
Non relational database: (NoSQL) Not Only
- Redis，MongDB
- Non relational database, object storage, is determined by the attributes of the object itself.
DBMS (database management system)
- Database management software, scientific and effective management of our data, maintenance and acquisition of data;
- MySQL, database management system
1.4 introduction to MySQL
MySQL is a relational database management system
Previous life: Swedish MySQL AB Company
Bensheng: a product of Oracle
MySQL is one of the best RDBMS (relational database management system) application software
Open source database software!
Small size, fast speed, low overall cost of ownership and low recruitment cost. Everyone must be able to
Small and medium-sized websites, or large websites, clusters!
Official website: mysql.com com
1.5 installing MySQL
Slightly!!!!! (my MySQL has been installed)
1. Decompress
2. Put this bag in your computer environment directory
3. Configure environment variables
4. Create a new mysql configuration file ini
5. Start CMD in administrator mode and run all commands
6. Install mysql service
7. Initialize database file
8. Start mysql and change the password
9. Enter mysql and modify the password through the command line (- p without adding an empty shell) (the semicolon must be added after the sql statement!)
10. Note the skip password in ini
11. Restart mysql. Connection test. If the connection is successful, it's OK!
1. Missing component dll
2. Command output
sc delete mysql, Empty service
1.6 installing SQLyog
1. Brainless installation
2. Register
3. Open connection database
4. Create a new database school
The execution of each sqlyog is essentially corresponding to an sql, which can be viewed in the history of the software
5. Create a new table student
6. View table
7. You can add multiple records yourself
Field: id, name,age,
1.7. Connect to database
Command line connection!!
mysql -uroot -p123456 ---Connect database
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; ---Modify user password
flush pricileges; --Refresh permissions
------------------------------------------------------------
All statements use;ending
show databases; -----View all databases
mysql> use school ----Switch database use Database name
Database changed
show tables; ---View all tables in the database
describe student; -----Displays information about all tables in the database
create database westos; --Create a database
exit; --- Exit connection
-- Single line note( SQL (original note)
/* (SQL (multiline comment for)
helloi
*/
Addition, deletion, modification and query of database xxx language CRUD
DDL definition
DML operation
DQL query
DCL control
2. Operation database
Operate database > operate tables in Database > operate data of tables in database
mysql keyword is not case sensitive
2.1. Operation database (understand)
1. Create database
CREATE DATABASE [IF NOT EXISTS] westos
2. Delete database
DROP DATABASE IF EXISTS westos
3. Use database
-- tab Above the key ``,If your table name or field name is a special character, you need to bring it USE school;
4. View database
SHOW DATABASES --- View all databases
Learning ideas:
- View sql against sqlyog visual history
- Fixed syntax or keywords must be forcibly remembered!!
2.2. Column type of database
numerical value
- tinyint very small data 1 byte
- smallint smaller data 2 bytes
- mediumint medium size data 3 bytes
-int standard integer 4 bytes - big larger data is 8 bytes
- float floating point number 4 bytes
- double floating point number 8 bytes
- Decimal is generally used in financial calculation of floating-point numbers in the form of decimal string
character string
- char string fixed size 0-255
-varchar variable string 0-65535 common string - Tiny text 2 ^ 8-1
- Texttext string 2 ^ 16-1 save large text
Time and date
java.util.Date
- date YYYY-MM-DD date format
- time HH:mm:ss time format
-datetime YYYY-MM-DD HH:mm:ss the most commonly used time format
-The number of milliseconds from timestamp 1970.1.1 to now!! It is also commonly used - Year means year
null
- No value, unknown
-= = note: do not use NULL for operation. The result is NULL
2.3. Field attributes of database (key points)
Unsigned:
- Unsigned integer
- The column declared cannot be declared negative
zerofill
- 0 filled
- Insufficient digits, filled with 0, int (3), 5 ------ > 005
Self increment:
- Normally, Sister Li is self incremented and automatically + 1 (default) on the basis of the previous record
- It is usually used to design a unique primary key ~ index, which must be of integer type
- You can customize the starting value and step size of the self increment of the design primary key
Non null NOLL not null
- If it is set to not null, an error will be reported if it is not assigned a value
- Null. If the value is not filled in, it is null by default!!
default
- Set default values
- sex, the default value is male. If the value of this column is not specified, there will be a default value!!
Supplement:
/Each table must have the following five fields
For future projects, it indicates the significance of a record
id primary key
'version' optimistic lock
is_delete pseudo delete
gmt_create creation time
gmt_update modification time
/
2.4. Create database table (key)
- Objective: to create a school database
- create student tables (columns, fields) using sql
- student id int login password varchar(20) name, gender varchar(2), date of birth (datatime), home address, email
- note: use English (), and try to enclose the names and fields of the table
— AUTO_INCREMENT self increment
- string, enclosed in single quotes
- all statements are followed by (English), and the last field does not need to be added
- PRIMARY KEY. Generally, a table has only one unique PRIMARY KEY
CREATE TABLE IF NOT EXISTS `student` ( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number', `name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name', `pwd` VARCHAR(30) 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 ] `Table name`( `Field name` Type column [attribute][Indexes][notes], `Field name` Type column [attribute][Indexes][notes], ...... `Field name` Type column [attribute][Indexes][notes] )[Table type][Character set settings][notes]
Common commands
SHOW CREATE DATABASE school --View the statement that created the database SHOW CREATE TABLE student --see student Definition statement of data table DESC student -- Displays the structure of the table
2.5 type of data sheet
- About database engine /* INNODB Default use MYISAM Used in earlier years */
General operation:
- MYISAM saves space and is fast
- INNODB has high security, transaction processing, multi table and multi-user operation
Where it exists in physical space
All database files are stored in the data directory, one folder for one database
The essence is the storage of files!!!
Differences of MySQL engine in physical files
- INNODB has only one *. In the database table frm file and ibdata1 file in the parent directory
- MYISAM corresponding file
-
- *Definition file of. frm table structure
-
- *. MYD data file (data)
-
- *. MYI index file (index)
Set the character set encoding of the database table
CHARSET=utf8
If it is not set, it will be the default character set code of mysql ~ (Chinese is not supported)
MySQL's default code is Latin1, which does not support Chinese
In my Ini to configure the default encoding
character-set-server=utf8
2.6. Modify and delete table
modify
-- Modify table name ALTER TABLE Old table name RENAME AS New table name ALTER TABLE teacher RENAME AS teacher1 --Add table fields ALTER TABLE Table name ADD Field column properties ALTER TABLE teacher1 ADD age INT(11) -- Modify the fields of the table (rename, modify constraints!) -- ALTER TABLE Table name MODIFY Field column properties[] ALTER TABLE teacher1 MODIFY age VARCHAR(11) --Modify constraints -- ALTER TABLE Table name change Old name new name column properties[] ALTER TABLE teacher1 CHANGE age age1 INT(1) --Field rename -- Delete table fields ALTER TABLE teacher DROP age1
delete
-- Delete table (delete if table exists) DROP TABLE IF EXISTS teacher1
All creation and deletion operations should be judged as much as possible to avoid errors
Note:
- Field name, use this package
- Notes –/**/
- sql keyword is not case sensitive. We recommend that you write lowercase
- All symbols are in English
3. MySQL data management
3.1. Foreign keys (just understand)
Method 1: add constraints when creating tables (troublesome and complex)
CREATE TABLE `grade` ( `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade id', `gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name', PRIMARY KEY(`gradeid`) )ENGINE=INNODB DEFAULT CHARSET=utf8 --- Student table gradeid Field to reference the grade table gradeid --- Define foreign keys key --- Add constraints (execute references) to this foreign key) CREATE TABLE IF NOT EXISTS `student` ( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number', `name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name', `pwd` VARCHAR(30) 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 'Student's grade', `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
When deleting a table with a foreign key relationship, you must first delete the table that references others, and then delete the referenced table (primary table)
Method 2: after the table is created successfully, add a foreign key constraint
CREATE TABLE IF NOT EXISTS `student` ( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number', `name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name', `pwd` VARCHAR(30) 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 'Student's grade', `address` VARCHAR(100) DEFAULT NULL COMMENT 'Home address', `email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox', PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 --- There is no foreign key relationship when creating a table ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
The above operations are physical foreign keys, database level foreign keys, which are not recommended!! (avoid trouble caused by too many databases)
Best practices
- A database is a simple table. It only stores data, only rows (data) and columns (fields)
- We want to use the data of multiple tables, and we want to use foreign keys
3.2. DML language (remember all)
Meaning of database: data storage, data management
DML language: data operation language
- Insert
- update
- delete
3.3. Add
**INSERT**
-- Insert statement (add)
-- insert into Table name([Field name 1, field name 2, field 3]) values('Value 1'),('Value 2','Value 3'...)
INSERT INTO `grade`(`gradename`) VALUES ('Senior')
-- Since the primary key increases automatically, we can omit it (if we don't write the fields of the table, they will match one by one)
INSERT INTO `grade` VALUES('Junior')
-- Generally, when writing insert statements, we must make sure that the data and fields correspond one by one
-- Insert multiple fields
INSERT INTO `grade` (`gradename`)
VALUE ('Junior'),('Senior')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('Zhang San',;'aaaa','male')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('Li Si',;'aaaaa','male'),('Wang Wu',;'aaaaaa','male')
Syntax: - insert into table name ([field name 1, field name 2, field 3]) values ('value 1 '), ('value 2'), ('value 3 '...)
matters needing attention:
- 1. Fields are separated by English commas
- 2. Fields can be omitted, but the following values must correspond to each other one by one
- 3. Multiple pieces of data can be inserted at the same time. The VALUES after VALUES need to be used to separate VALUES()
3.4 modification
update modify who (condition) set original value = new value
-- Modify the name of the student and bring the introduction UPDATE `student` SET `name` ='Mad God' WHERE id =1; -- All tables will be changed without specifying conditions UPDATE `student` SET `name`='Changjiang 7' -- Modify multiple attributes,Comma separated UPDATE `student` SET `name` = 'Mad God',email='1122555@qq.com' WHERE id=1
Syntax:
UPDATE table name set colnum_name = value,[colnum_name = value] where [condition]
Condition: where clause operator id is equal to a certain value, greater than a certain value, modified in a certain interval
-- Locate data through multiple conditions,No upper limit UPDATE `student` SET `name` = 'Changjiang 7' WHERE `name`='Madness 44' AND sex='female'
Syntax: UPDATE table name set colnum_name = value,[colnum_name = value...] where [condition]
be careful:
- colnum_name is the column of the database. Try to bring
- Criteria, filter criteria. If not specified, all columns will be modified
- Value is a specific value or a variable
- The attributes of multiple settings are separated by English commas
3.5 deletion
delete command
-- Delete data(Avoid writing like this, and all will be deleted) DELETE FROM `student` -- Delete specified data DELETE FROM `student` WHERE id= 1;
TRUNCATE command
– function: completely empty a database, and the table structure and index constraints will not change
--empty student surface TRUNCATE `student`
The difference between delete and truncate
- Same point: data can be deleted without deleting table structure
- Different:
-
- When TRUNCATE is reset, the auto increment counter will return to zero
-
- TRUNCATE does not affect transactions
-- test delete and truncate difference
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 increment
TRUNCATE TABLE `test` --Auto increment will return to zero
**Understand the problem of DELETE deletion and restart the database. The phenomenon is as follows:
- InnoDB auto increment will restart with 1 (if it exists in memory, it will lose power immediately)
- MyISAM continues from the previous increment (if it exists in the file, it will not be lost)**
4. DQL query data (most important)
4.1,DQL
- All query operations use it, SELECT
- It can do simple query and complex query~
-The core language and the most important statement in the database - Most frequently used statements
CREATE DATABASE IF NOT EXISTS `school`;
-- Create a school database
USE `school`;-- Create student table
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT 'Student number',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT 'Student name',
`sex` TINYINT(1) DEFAULT NULL COMMENT 'Gender, 0 or 1',
`gradeid` INT(11) DEFAULT NULL COMMENT 'Grade number',
`phone` VARCHAR(50) NOT NULL COMMENT 'Contact number, can be blank',
`address` VARCHAR(255) NOT NULL COMMENT 'Address, null allowed',
`borndate` DATETIME DEFAULT NULL COMMENT 'time of birth',
`email` VARCHAR (50) NOT NULL COMMENT 'Email account can be empty',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- Create grade table
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Grade number',
`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- Create chart of accounts
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Course number',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT 'Course name',
`classhour` INT(4) DEFAULT NULL COMMENT 'Class hours',
`gradeid` INT(4) DEFAULT NULL COMMENT 'Grade number',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- Create grade sheet
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT 'Student number',
`subjectno` INT(4) NOT NULL COMMENT 'Course number',
`examdate` DATETIME NOT NULL COMMENT 'Examination date',
`studentresult` INT (4) NOT NULL COMMENT 'Examination results',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','Zhang Wei',0,2,'13800001234','Chaoyang, Beijing','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','Qiang Zhao',1,3,'13800002222','Shenzhen, Guangdong','1990-1-1','text111@qq.com','123456199001011233');
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- Insert grade data
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'Freshman'),(2,'Sophomore'),(3,'Junior'),(4,'Senior'),(5,'Preparatory class');
-- Insert account data
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'Advanced mathematics-1',110,1),
(2,'Advanced mathematics-2',110,2),
(3,'Advanced mathematics-3',100,3),
(4,'Advanced mathematics-4',130,4),
(5,'C language-1',110,1),
(6,'C language-2',110,2),
(7,'C language-3',100,3),
(8,'C language-4',130,4),
(9,'Java Programming-1',110,1),
(10,'Java Programming-2',110,2),
(11,'Java Programming-3',100,3),
(12,'Java Programming-4',130,4),
(13,'database structure -1',110,1),
(14,'database structure -2',110,2),
(15,'database structure -3',100,3),
(16,'database structure -4',130,4),
(17,'C#Basic ', 130,1);
4.2. Specify query fields
-- Query all students SELECT field FROM surface
SELECT * FROM student
SELECT * FROM result
-- Query specified fields
SELECT `studentNo`,`StudentName` FROM student
-- Alias, give the result a name AS You can alias fields or tables
SELECT `StudentNo` AS Student number, `StudentName` AS Student name FROM student AS s
-- function Concat(a,b)
SELECT CONCAT('full name: ',StudentName) AS New name FROM student
Syntax: select field... from table
Sometimes, the list of names is not so obvious. We use alias as field name as alias table name as alias
De duplication distinct
Function: remove the duplicate data in the result of SELECT query, and only one duplicate data is displayed
-- Find out which students took the exam and got results SELECT * FROM result --Query all test scores -- Check which students took the exam SELECT `studentNo` FROM result -- Duplicate data found, de duplication SELECT DISTINCT `StudentNo` FROM result
Database columns (expressions)
SELECT VERSION() -- Query System Version (function) SELECT 100*3-1 AS Calculation results -- Used to evaluate (an expression) SELECT @@auto_increment_increment -- Query self increasing step size (variable) -- Student examination results +1 see SELECT `StudentNo`,`StudentResult`+1 AS 'After raising points' FROM result
Expressions in database: text value, column, Null, function, calculation expression, system variable
select expression from table
4.3 where conditional clause
Function: retrieve qualified values in data
The search criteria consist of one or more expressions and the result is a Boolean value
Try to use English letters
SELECT studentNo,`StudentResult` FROM result -- The query test score is 95~100 Between points 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 -- Grades of students other than student 1000 SELECT studentNo,`StudentResult` FROM result WHERE NOT studentNo =1000
Fuzzy queries: comparison operators
-- Inquire about students surnamed Liu
-- like combination %(Represents 0 to any character) _((one character)
SELECT `StudentNo`,`studentName` FROM `student`
WHERE StudentName LIKE 'Liu%'
-- Query the students surnamed Liu. There is only one word after their name
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'Liu_'
-- For students surnamed Liu, there are only two words after their name
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'Liu__'
-- Query the students with Jiazi in the middle of their names %Jia%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%Jia%'
-- Inquire about student 100110021003
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- Query students in Beijing
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `address` IN ('Beijing')
-- Query students whose address is empty
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address='' OR address IS NULL
-- Query students with date of birth
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL
-- Query students without date of birth
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL
4.4. Associated table query
join comparison
-- Query the students who took the exam (student number, name, subject number, score) SELECT * FROM student SELECT * FROM result /* thinking 1,Analyze the requirements and the tables from which the fields of the analysis query come (connection query) 2,Determine which connection query to use? 7 kinds Determine the intersection (which data of the two tables is the same) Conditions for judgment: studentNo in student table = studentNo in grade table */ SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student AS s INNER JOIN result AS r WHERE s.studentNo = r.studentNo -- Right Join SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student s RIGHT JOIN result r ON s.studentNo = r.studentNo -- Left Join SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student s RIGHT JOIN result r ON s.studentNo = r.studentNo
-- Query the students who took the exam (student number, name, subject number, score) SELECT * FROM student SELECT * FROM result /* thinking 1,Analyze the requirements and the tables from which the fields of the analysis query come (connection query) 2,Determine which connection query to use? 7 kinds Determine the intersection (which data of the two tables is the same) Conditions for judgment: studentNo in student table = studentNo in grade table */ -- join on(Connected table) (judged condition) connection query -- where Equivalent query SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student AS s INNER JOIN result AS r WHERE s.studentNo = r.studentNo -- Right Join SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student s RIGHT JOIN result r ON s.studentNo = r.studentNo -- Left Join SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student s LEFT JOIN result r ON s.studentNo = r.studentNo -- Query the absent students SELECT s.studentNo,studentName,SubjectNo,StudentResult FROM student s LEFT JOIN result r ON s.studentNo = r.studentNo WHERE StudentResult IS NULL -- Thinking questions (inquired the information of students participating in the exam: student number, student name, subject name and score) /* thinking 1,Analyze the requirements, and analyze which tables student, result and subject the fields of the query come from (connection query) 2,Determine which connection query to use? 7 kinds Determine the intersection (which data of the two tables is the same) Conditions for judgment: studentNo in student table = studentNo in grade table */ SELECT s.studentNo,studentName,subjectName,`StudentResult` FROM student s RIGHT JOIN result r ON r.studentNo = s.studentNo INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo -- What data do I want to query select... -- Look it up from those tables FROM surface XXX Join Connected tables On Cross condition -- Suppose there is a multi table query. Take your time. First query two tables and then increase them slowly -- From a left join b -- from a right join b
Self connection
Connect your own table with your own table. Core: one table can be split into two identical tables
Operation: query the subclass relationship corresponding to the parent class
-- Query parent-child information :Look at a table as like as two peas of two models. SELECT a.`categoryName` AS 'Parent column',b.`categoryName` AS 'Sub column' FROM `category` AS a,`category` AS b WHERE a.`categoryid`=b.`pig`
-- Query the grade of the student (student number, student name, grade name) SELECT studentNo,studentName,`GradeName` FROM student s INNER JOIN `grade` g ON s.`GradeId` = g.`GradeID` -- Query the grade of the subject (subject name, grade name) SELECT `SubjectName`,`GradeName` FROM `subject` sub INNER JOIN `grade` g ON sub.`GradeID` = g.`GradeID` -- Query the database structure-1 Information of students in the exam: student number, student name, subject name and score SELECT s.`studentNo`,`studentName`,`subjectName`,`studentResult` FROM student s INNER JOIN `result` r ON s.studentno = r.studentNo INNER JOIN `subject` sub ON r.`subjectno` = sub.`subjectno` WHERE subjectname = 'database structure -1'
4.5. limit and order by
sort
-- Sorting: ascending ASC Descending order DESC -- order by Which field is used to sort and how -- The query results are sorted in descending order according to their grades SELECT s.`studentNo`,`studentName`,`subjectName`,`studentResult` FROM student s INNER JOIN `result` r ON s.studentno = r.studentNo INNER JOIN `subject` sub ON r.`subjectno` = sub.`subjectno` WHERE subjectname = 'database structure -1' ORDER BY studentResult ASC
paging
-- Why pagination? -- Ease the pressure of database and give people a better experience -- Pagination, only five pieces of data are displayed on each page -- Syntax: limit Starting value page size -- Web application: current, total pages, page size -- LIMIT 0,5 1~5 -- LIMIT 1,5 2~6 SELECT s.`studentNo`,`studentName`,`subjectName`,`studentResult` FROM student s INNER JOIN `result` r ON s.studentno = r.studentNo INNER JOIN `subject` 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 -- The first N page limit .,5 (n-1)*pageSize,pageSize -- [pageSize: [page size] -- [(n-1) * pageSize: Starting value] -- [n: [current page] -- [Total data/Page size = Total pages]
Syntax: limit (query start subscript, page size)
-- query JAVA Students with the top ten course scores in the first academic year and a score greater than 80 -- (Student number, name, course name, score) SELECT s.studentno,studentname,`subjectName`,`studentResult` FROM student s INNER JOIN `result` r ON s.studentno = r.studentNo INNER JOIN `subject` sub ON r.`subjectno` = sub.`subjectno` WHERE subjectname = 'JAVA First academic year' AND studentResult>80 ORDER BY studentResult DESC LIMIT 0,10
4.7 sub query
where (the value is fixed and calculated)
Essence: nest a subquery statement in the where statement
where (select * from)
-- 1,Query database structure-1 All the test results (student number, subject number and score) are arranged in descending order -- Method 1: use connection query SELECT `studentno`,r.`subjectno`,`studentresult` FROM `result` r INNER JOIN `subject` sub ON r.subjectno = sub.subjectno WHERE subjectname = 'database structure -1' ORDER BY studentresult DESC -- Method 2: use sub query SELECT `studentno`,`subjectno`,`studentresult` FROM `result` WHERE subjectno = ( -- Query all database structures-1 Student ID SELECT subjectno FROM `subject` WHERE subjectname = 'database structure -1' ) ORDER BY studentresult DESC -- The inquiry course is advanced mathematics-2 Student number and name of students with a score of no less than 80 SELECT s.studentno,studentname FROM student s INNER JOIN result r ON s.studentno = r.studentno INNER JOIN `subject` sub ON r.`subjectno` = sub.`subjectno` WHERE `subjectname` = 'Advanced mathematics-2' AND studentresult >= 80 -- Student number and name of students with a score of no less than 80 SELECT DISTINCT s.`studentno`,`studentname` FROM student s INNER JOIN result r ON r.studentno = s.studentno WHERE studentresult >=80 -- On this basis, add a subject, advanced mathematics-2 -- Query Advanced Mathematics-2 Number of SELECT DISTINCT s.`studentno`,`studentname` FROM student s INNER JOIN result r ON r.studentno = s.studentno WHERE studentresult >=80 AND `subjectno`=( SELECT subjectno FROM `subject` WHERE `subjectname`= 'Advanced mathematics-2' ) -- Remoulding SELECT studentno,studentname FROM student WHERE studentno IN( SELECT studentno FROM result WHERE studentresult > 80 AND subjectno = ( SELECT subjectno FROM `subject` WHERE subjectname = 'Advanced mathematics-2' ) )
-- Exercise: querying C language-1 Information on the scores of the top 5 students (student number, name, score) -- Use subquery SELECT s.studentno,studentname,`studentresult` FROM student s INNER JOIN result r ON s.studentno = r.studentno INNER JOIN `subject` sub ON r.studenno = sub.studentno WHERE subjectname = 'C language-1' ORDER BY studentresult DESC LIMIT 0,5 SELECT s.studentno,studentname,`studentresult` FROM student s INNER JOIN result r ON s.studentno = r.studentno WHERE subjectname = ( SELECT subjectname FROM `subject` WHERE subjectname = 'C language-1' ) ORDER BY studentresult DESC LIMIT 0,5
4.8 grouping and filtering
-- Query the average score of different courses, the highest score, the lowest score, and the average score is greater than 80 -- Core: (grouped according to different courses) SELECT subjectname,AVG(studentresult) AS average,MAX(studentresult) AS Highest score,MIN(studentresult) AS Lowest score FROM result r INNER JOIN `subject` sub ON r.subjectno = sub.subjectno -- where AVG(studentresult) >= 80 GROUP BY r.subjectno -- By what field HAVING average > 80
4.9. SELECT summary
5. MySQL function
Official website: https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
5.1. Common functions
-- Mathematical operation
SELECT ABS(-8) -- absolute value
SELECT CEILING(9.4) -- Round up
SELECT FLOOR(9.4) -- Round down
SELECT RAND() -- Return a 0~1 Random number between
SELECT SIGN(10) -- Sign 0 for judging a number--0 Negative return-1 A positive number returns 1
-- String function
SELECT CHAR_LENGTH('Even the smallest sail can sail far') -- String length
SELECT CONCAT('I','love','You') -- Splice string
SELECT INSERT('I love programming helloworld',1,2,'Super love') -- query,replace,Replace a length from a position
SELECT LOWER('Kuangshen') -- Lowercase letters
SELECT UPPER('kuangshen') -- capital
SELECT INSTR('kuangshen','h') -- Returns the index of the first occurrence string
SELECT REPLACE('Crazy God says persistence can succeed','insist','strive') -- Replace the specified string that appears
SELECT SUBSTR('Crazy God says persistence can succeed',4,6) -- Returns the specified substring (original string, intercepted position, intercepted length)
SELECT REVERSE('I got on my horse in the morning') -- reversal
-- Query the classmate surnamed Zhou, whose name is Zhou Zhou
SELECT REPLACE(studentname,'week','Zhou Zhou') FROM student
WHERE studentname LIKE 'week%'
-- Time and date functions (remember)
SELECT CURRENT_DATE() -- Get current date
SELECT CURDATE() -- Get current date
SELECT NOW() -- Get 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 aggregate function (common)
-- Can count the data in a table (use this if you want to query how many records there are in a table) count()) SELECT COUNT(studentname) FROM student; -- count(field)，Will ignore all null value SELECT COUNT(*) FROM student; -- Count(*)，Will not ignore null Value, count rows SELECT COUNT(1) FROM result; -- count(1)，Will not ignore all null Number of rows for value calculation SELECT SUM(`studentresult`) AS the sum FROM result SELECT AVG(`studentresult`) AS average FROM result SELECT MAX(`studentresult`) AS Highest score FROM result SELECT MIN(`studentresult`) AS Lowest score FROM result
Suggest Baidu: the difference between count(1) and count(*)
5.3 MD5 encryption at database level (extension)
What is MD5?
It mainly increases the complexity and irreversibility of the algorithm.
MD5 is irreversible. The specific value of MD5 is the same
The principle of MD5 cracking the website is that there is a dictionary behind it. The value after MD5 encryption and the value before encryption
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
-- enable password
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),
(2,'lisi','12345'),(3,'wangwu','1234')
-- encryption
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) -- Encrypt all passwords
-- Encrypt when inserting
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
-- How to verify: check the password passed in by the user md5 Encryption, how to compare the encrypted values
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')
6. Business
6.1. What is a transaction?
1. SQL execute A to B account transfer A 1000 - > 200 B 200
2. SQL executive B receives A's money b 800 - > b 400
Put a group of SQL into a batch for execution~
Transaction principle: ACID principle, atomicity, consistency, isolation, persistence (dirty reading, phantom reading)
Reference blog link: https://blog.csdn.net/dengjili/article/details/82468576
Atomicity
Either all succeed or all fail
Consistency
The data integrity before and after the transaction should be consistent, 1000
Durability – transaction commit
Once the transaction is committed, it is irreversible and is persisted to the database
Isolation
Transaction isolation is that when multiple users access the database concurrently, the transactions opened by the database for each user cannot be disturbed by the operation data of other transactions. Multiple concurrent transactions should be isolated from each other
Some problems caused by isolation
Dirty read:
Refers to a transaction reading uncommitted data from another transaction.
Non repeatable:
Read a row of data in a table in a transaction, and the results are different for multiple times. (this is not necessarily a mistake, but it is wrong on some occasions)
Virtual reading (unreal reading)
It refers to that the data inserted by other transactions is read in one transaction, resulting in the inconsistency of the total number of reads before and after.
-- mysql Automatic transaction submission is enabled by default SET autocommit =0 /*close*/ SET autocommit =1 /*On (default)*/ -- Manual transaction processing SET autocommit =0 -- Turn off automatic condition -- Transaction on START TRANSACTION -- Mark the beginning of a transaction from the beginning sql All in the same transaction INSERT xx INSERT xx -- Commit: persistent (successful!) COMMIT -- Rollback: return to the original state (failed!) ROLLBACK -- End of transaction SET autocommit =1 -- Turn on automatic condition SAVEPOINT Save roll call -- Set a transaction savepoint ROLLBACK TO SAVEPOINT Save roll call -- Rollback to savepoint RELEASE SAVEPOINT Save roll call -- Undo savepoint
Simulation scenario:
-- transfer 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 -- Simulated transfer: Transactions SET autocommit = 0 -- Turn off auto submit START TRANSACTION -- Start a transaction (a group of transactions) UPDATE account SET money=money-500 WHERE `name` = 'A' -- A Minus 500 UPDATE account SET money=money+500 WHERE `name` = 'B' -- B Plus 500 COMMIT; -- When the transaction is committed, it is persisted ROLLBACK;-- RollBACK SET autocommit=1; -- Reply to default
7. Index
MySQL's official definition of Index is: Index is a data structure that helps MySQL obtain data efficiently.
By extracting the sentence trunk, we can get the essence of index: index is a data structure
7.1 classification of index
In a table, there can only be one primary key index and multiple unique indexes
- Primary key (PRIMARY KEY)
Unique identifier. It cannot be repeated. There can only be one column as the primary key
- Unique key
Avoid duplicate columns. Unique indexes can be repeated, and multiple columns can identify unique indexes
- General index (KEY/INDEX)
By default, index and key keywords are used to set
- Full text index (FULLTEXT)
Only under a specific database engine, MyISAM
Fast positioning data
Basic syntax:
-- Use of index
-- 1,Add indexes to fields when creating tables
-- 2,After creation, increase the index
-- Display 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
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('Liu');
7.2 test index
CREATE TABLE `app_user`(
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT 'User nickname',
`email` VARCHAR(50) NOT NULL COMMENT 'User mailbox',
`phone` VARCHAR(20) DEFAULT '' COMMENT 'cell-phone number',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT 'Gender (0):male;1:(female)',
`password` VARCHAR(100) NOT NULL COMMENT 'password',
`age` TINYINT(4) DEFAULT '0' COMMENT 'Age',
`create_time` DATETIME DEFAULT NULL ,
`update_time` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app User table'
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'User nickname',
`email` VARCHAR(50) NOT NULL COMMENT'User mailbox',
`phone` VARCHAR(20) DEFAULT'' COMMENT'cell-phone number',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT 'Gender (0): Male;1:(female)',
`password` VARCHAR(100) NOT NULL COMMENT 'password',
`age` TINYINT(4) DEFAULT'0' COMMENT 'Age',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app User table'
-- Insert 1 million pieces of data
DELIMITER $$ -- Flag that must be written before writing a function
SET GLOBAL log_bin_trust_function_creators=TRUE;
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- Insert statement
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 one million pieces of data
SELECT * FROM app_user WHERE `name`='User 9999';
-- id _Table name _field
-- create INDEX Index name on surface(field)
CREATE INDEX id_app_user_name ON app_user(`name`);
Index users are not large when there is a small amount of data, but the difference is very obvious when there is big data
7.3 indexing principle
- The more indexes, the better
- Do not index process change data
- Tables with small amounts of data do not need to be indexed
- The index is usually added to the fields commonly used for query
Indexed data structure
Hash type index
Btree: default data structure of innodb
8. Rights management and backup
8.1 user management
SQL yog visual management
SQL command operation
User table: MySQL user
Essence: read this table for addition, deletion, modification and query
-- Create user CREATE USER user name IDENTIFIED BY password
CREATE USER kuangshen IDENTIFIED BY '123456'
-- Modify password (modify current user password)
SET PASSWORD = PASSWORD('123456')
-- Modify password (modify the password of the specified user)
SET PASSWORD FOR kuangshen = PASSWORD('123456')
-- rename RENAME USER Original name TO New name
RENAME USER kuangshen TO kuangshen2;
-- User authorization all privileges All permissions all libraries and tables
-- all privileges Except for authorizing others, others are capable
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
-- Query authority
SHOW GRANTS FOR kuangshen2 -- View the permissions of the specified user
SHOW GRANTS FOR root@localhost
-- ROOT User rights:
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
-- Revoke authority, REVOKE Which permissions, in which library, and to whom
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
-- delete user
DROP USER kuangshen
8.2 MySQL backup
Why backup?:
- Ensure that important data is not lost
- Data transfer
MySQL database backup method:
-
Copy physical files directly
-
Export manually in a visualizer like Sqlyog
-
Right click the table or library you want to export and select backup or export
-
Export mysqldump from the command line
# mysqldump -h host - u user name - p password database table name > physical disk location / file name mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # mysqldump -h host - u user name - p password database table name 1 table name 2 > physical disk location / file name mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # mysqldump -h host - u user name - p password database 2 > physical disk location / file name mysqldump -hlocalhost -uroot -p123456 school>D:/a.sql # Import Switch to the specified database when logging in source Backup files mysql -u user name -p Password library name< Backup files
Suppose you want to back up the database to prevent data loss
9. Standardize database design
9.1 why design is needed
When the database is complex, we need to design it
Poor database design:
- Data redundancy, waste of space
- Database insertion and deletion will be troublesome and abnormal [shielding the use of physical foreign keys]
- Poor program performance
Good database design:
- Save memory space
- Ensure the integrity of the database
- It is convenient for us to develop the system
Steps of designing database: (personal blog)
Collect information and analyze requirements
- User table (user login and logout, personal information of user table, blogging, creating classification)
- Classification table (article classification, who created it)
- Comment form
- Article table (information of articles)
- Friend chain list (friend chain information)
- User defined table (system information, a key word, or some main fields) key: value
- Talk about the table (express your mood...)
Identify the entity (implement the requirements to each field)
Identify relationships between entities - Blog user - > blog
- Create category: user - > blog
- Attention: user - > User
- Links: links
- Comment: user - > User blog
9.2 three paradigms
Why do we need data normalization?
- Duplicate information
- Update exception
- Insert exception
Unable to display information normally - Delete exception
Missing valid information
Three paradigms
First normal form (1NF): each column of the data table is required to be an indivisible atomic data item
Atomicity: ensure that each column cannot be further divided
Second normal form (2NF) (based on 1NF, eliminate the partial functional dependence of non main attributes on main codes)
Premise: on the premise of meeting the first paradigm
Each table describes only one thing
The third paradigm (3NF) (eliminating transmission dependence on the basis of 2NF)
Premise: meet the first paradigm and the second paradigm
The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly
(standardize the design of database)
Normative and performance issues
There are no more than three tables associated with the query
- Considering the needs and objectives of commercialization, the performance of database (cost, user experience) is more important
- When it comes to standardizing performance, we need to properly consider the standardization!
- Deliberately add some redundant fields to some tables (from multi table query to single table query)
- Deliberately add some calculated columns (query with large data volume reduced to small data volume: index)
10,JDBC
10.1. Database driver
Driver: sound card, graphics card, database
Our program will deal with the database through database driver
10.2,JDBC
In order to simplify the (unified database) of developers, SUN company provides a (Java operation database) plan, commonly known as JDBC. The implementation of these specifications is done by specific manufacturers
For developers, we only need to master the operation of JDBC interface
java.sql
javax.sql
You also need to import a database driver package
10.3. First off JDBC program
Create test database
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci; USE jdbcStudy; CREATE TABLE users( id INT PRIMARY KEY, NAME VARCHAR(40), PASSWORD VARCHAR(40), email VARCHAR(60), birthday DATE ); INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'), (2,'lisi','123456','lisi@sina.com','1981-12-04'), (3,'wangwu','123456','wangwu@sina.com','1982-12-04'),
1. Create a normal project
2. Import database driver
3. Write
package JDBC;
import java.sql.*;
// My first JDBC program
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException,SQLException{
//1. Load driver
Class.forName("com.mysql.jdbc.Driver"); //Fixed writing, load driven
//2. User and url information
//Support Chinese coding, determine the Chinese coding number as utf8, and use a secure connection
//useUnicode=true&characterEncoding=utf8&useSSL=true
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "1234";
//3. The Connection is successful. The database object Connection represents the database
Connection connection = DriverManager.getConnection(url, username, password);
//4. Object executing SQL Statement object executing SQL
Statement statement = connection.createStatement();
//5. For the object executing SQL, execute SQL. There may be results. Check the returned results
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql); // The returned result set 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 connection
resultSet.close();
statement.close();
connection.close();
}
}
Step summary:
1. Load driver
2. Connect to database DriverManager
3. Get the Statement object that executes 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 driven
//connection represents the database
//Database settings auto submit
//Transaction commit
//Transaction rollback
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy? useUnicode=true&characterEncoding=utf8&useSSL=false"; //mysql--3306 //jdbc:mysql: / / host address: port number / database name? Parameter 1 & parameter 2 & parameter 3 //Protocol: / / host address: port number / database name? Parameter 1 & parameter 2 & parameter 3 //oralce --1521 //jdbc:oracle:thin:@localhost:1521:sid
Statement object executing SQL PrepareStatement object executing SQL
String sql = "SELECT * FROM users"; //Write SQL statement.executeQuery(); //Query operation returns ResultSet statement.execute(); //Execute any SQL statement.executeUpdate(); //Update, insert, delete. This is used to return the number of affected rows
ResultSet query result set: encapsulates all query results
Gets the specified data type
resultSet.getObject(); //Use without knowing the column type //If you know the column type, use the specified type resultSet.getString(); resultSet.getInt(); resultSet.getFloat(); resultSet.getDate(); resultSet.getObject();
Traversal pointer
resultSet.beforeFirst(); //Move to the front resultSet.afterLast(); //Move to the back resultSet.next(); //Move to next data resultSet.previous(); //Move to previous line resultSet.absolute(row); //Move to specified row
Release resources
//6. Release connection
resultSet.close();
statement.close();
connection.close();
10.4 statement object
The statement object in jdbc is used to send SQL statements to the database. To complete the addition, deletion, modification and query of the database, you only need to send the addition, deletion, modification and query statements to the database through this object.
The executeUpdate method of the Statement object is used to send sql statements of addition, deletion and modification to the database. After executeUpdate is executed, an integer will be returned (that is, the Statement of addition, deletion and modification causes several rows of data in the database to change).
Statement. The executeQuery method is used to send query statements to the database, and the executeQuery method returns the ResultSet object representing the query results
CRUD operation - create
Use the executeUpdate(String sql) method to add data. Example operations:
Statement st=conn.createStatement();
String sql="insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("Insert successful!!!");
}
CRUD operation - delete
Use the executeUpdate(String sql) method to delete data. Examples:
Statement st=conn.createStatement();
String sql="delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("Deleted successfully!!!");
}
CRUD operation - update
Use the executeUpdate(String sql) method to modify the data. Examples:
Statement st=conn.createStatement();
String sql="update user set name='' where name='' ";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("Modified successfully!!!");
}
CRUD operation - read
Use the executeQuery(String sql) method to complete the data query operation. Example operations:
Statement st=conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(re.next()){
//According to the data type of the acquired column, respectively call the corresponding methods of rs and map them into java objects
}
code implementation
1. Extraction tool class
package JDBC.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static{
try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1. The driver is loaded only once
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//Get connection
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//Release connection resources
public static void release(Connection conn, Statement st, ResultSet 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();
}
}
}
}
2. Write the method of addition, deletion and modification executeUpdate
increase
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn=null;
Statement st =null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//Get database connection
st = conn.createStatement(); //Get the execution object of SQL
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(4,'kuangshen','123456','242323232@qq.com','2020-01-01')";
int i=st.executeUpdate(sql);
if(i>0){
System.out.println("Insert successful!!!");
}
}catch (SQLException e){
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
Delete:
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//Get database connection
st = conn.createStatement(); //Get the execution object of SQL
String sql = "DELETE FROM users WHERE id = 4;";
int i = st.executeUpdate(sql);
if (i > 0) {
System.out.println("Deleted successfully!!!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}
Change:
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn=null;
Statement st =null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//Get database connection
st = conn.createStatement(); //Get the execution object of SQL
String sql = "UPDATE users SET `Name`='kuangshen',`email`='1234445@qq.com' WHERE id=1;";
int i=st.executeUpdate(sql);
if(i>0){
System.out.println("Update successful!!!");
}
}catch (SQLException e){
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
Query:
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect
{
public static void main(String[] args) {
Connection conn = null;
Statement st=null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//SQL
String sql="select * from users where id = 1";
rs=st.executeQuery(sql); //After the query, a result set is returned
while(rs.next()){
System.out.println(rs.getString("NAME"));
}
}catch (SQLException e){
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
Problems with SQL injection
There is a vulnerability in SQL, which will be attacked, resulting in data leakage, and SQL will be spliced
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Sqlzhu {
public static void main(String[] args) {
//login("kuangshen","1234");
login(" 'or ' 1=1"," ' or '1=1");
}
//Login service
public static void login(String username,String password){
Connection conn = null;
Statement st=null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//SELECT * FROM users WHERE `Name` = 'kuangshen' AND `password` = '1234';
String sql="select * from users where `NAME`='"+username+"' AND `password` = ' "+password+"'";
rs=st.executeQuery(sql); //After the query, a result set is returned
while(rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
}
}catch (SQLException e){
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
10.5 PreparedStatement object
PreparedStatement can prevent SQL injection and is more efficient!!
//PreparedStatement prevents the essence of SQL injection from treating the parameters passed in as characters
//Suppose there are escape characters, such as' will be directly escaped
1. Add
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestInsert02 {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st =null;
try{
conn = JdbcUtils.getConnection();
//use? Placeholder instead of parameter
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
//difference
st = conn.prepareStatement(sql); //Precompiled SQL, write SQL first, and then do not execute
//Assign values to parameters manually
st.setInt(1,4);
st.setString(2,"qinjiang");
st.setString(3,"12322323");
st.setString(4,"12323414@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()));
//implement
int i=st.executeUpdate();
if(i>0){
System.out.println("Insert successful");
}
}catch (SQLException e){
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,null);
}
}
}
2. Delete
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestDelete02 {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st =null;
try{
conn = JdbcUtils.getConnection();
//use? Placeholder instead of parameter
String sql = "delete from users where id = ?";
//difference
st = conn.prepareStatement(sql); //Precompiled SQL, write SQL first, and then do not execute
//Assign values to parameters manually
st.setInt(1,4);
//Note: SQL Date database
// util Date Java new Date().getTime() gets the timestamp
//implement
int i=st.executeUpdate();
if(i>0){
System.out.println("Deleted successfully");
}
}catch (SQLException e){
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,null);
}
}
}
3. Renew
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate02 {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st =null;
try{
conn = JdbcUtils.getConnection();
//use? Placeholder instead of parameter
String sql = "Update users set `NAME`=? where id=? ";
//difference
st = conn.prepareStatement(sql); //Precompiled SQL, write SQL first, and then do not execute
//Assign values to parameters manually
st.setString(1,"Mad God");
st.setInt(2,1);
//Note: SQL Date database
// util Date Java new Date().getTime() gets the timestamp
//implement
int i=st.executeUpdate();
if(i>0){
System.out.println("Update successful");
}
}catch (SQLException e){
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,null);
}
}
}
4. Inquiry
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect02 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select * from users where id = ? "; //Write SQL
st=conn.prepareStatement(sql); // precompile
st.setInt(1,1); //Transfer parameters
//implement
rs = st.executeQuery();
if(rs.next()){
System.out.println(rs.getString("NAME"));
}
}catch (SQLException e){
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
sql injection:
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.*;
public class Sqlzhu02 {
public static void main(String[] args) {
//login("lisi","1234");
login(" ''or 1=1"," 1234");
}
//Login service
public static void login(String username,String password) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//PreparedStatement prevents the essence of SQL injection from treating the parameters passed in as characters
//Suppose there are escape characters, such as' will be directly escaped
//SELECT * FROM users WHERE `Name` = 'kuangshen' AND `password` = '1234';
String sql = "select * from users where `NAME`=? and `PASSWORD`=?";
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery(); //After the query, a result set is returned
while (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}
10.6. Use IDEA to connect to the database
After the connection is successful, you can select a database
Double click the database
Update data
Connection failed, check the reason
10.8 affairs
Either all succeed or all fail
ACID principle
Atomicity: either complete or not complete
Consistency: the total number remains unchanged
Isolation: multiple processes do not interfere with each other
Persistence: once the commit is irreversible, it is persisted to the database
Isolation issues:
Dirty read: one transaction reads another uncommitted transaction
Non repeatable reading: in the same transaction, the data in the table is repeatedly read, and the table data has changed
Virtual reading (unreal reading): in a transaction, the data inserted by others is read, resulting in inconsistent reading results
code implementation
1. Start transaction conn.setAutoCommit(false)
2. After the execution of a group of business, submit the transaction
3. You can define the rollback statement displayed in the catch statement, but the default failure will roll back
package JDBC;
import JDBC.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection conn= null;
PreparedStatement st=null;
ResultSet rs = null;
try {
conn=JdbcUtils.getConnection();
// Turning off auto commit of the database automatically turns on transactions
conn.setAutoCommit(false);
String sql1 = "update account set money = money-100 where name = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update account set money = money+100 where name = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//After the business is completed, submit the transaction
conn.commit();
System.out.println("success!!");
}catch (SQLException e){
//If it fails, rollback is the default
try {
conn.rollback(); //Roll back the transaction if it fails
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
10.8. Database connection pool
Database connection - execution complete - release
Connect - free up system resources
Pooling Technology: prepare some pre prepared resources and connect the pre prepared resources
... open the door... Salesman: wait - Service -
10 / 100 common connections
Minimum number of connections: 5 / 10
Maximum number of connections: 100 service maximum bearing limit
Queue up
Waiting timeout: 100ms
Write a connection pool and implement an interface DataSource
Implementation of open source data source
DBCP
C3P0
Druid: Alibaba
After using these database connection pools, we don't need to write code to connect to the database in the project development
