MySQL quick start

1. Get to know MySQL

Java EE: enterprise java development, Web

• Front end (page: display - data);
• Back end (connection point: connect to database JDBC, connect to the front end - control view jump and transfer data to the front end);
• Database (save data, Txt, Excel, world).

Programmer level:

• Can only write code, did not learn the database well, and basically make a living.
• Operating system, data structure, budget method! Be a good programmer!
• Discrete mathematics, digital circuit, architecture, compilation principle+ Practical experience = advanced program / excellent programmer.

1. Why learn MySQL

1. Job requirements;
2. In today's world, in the era of big data, those who get the database get the world.
3. Forced demand: save data;
4. Database is the core DBA in all software systems.

2. What is a database

1. DataBase (DB, DataBase)
2. Concept: data warehouse, software, installed on the operating system (windows, linux, mac,...)! SQL, can store a large amount of data. 5 million!
3. Function: store and manage data.

3. Database classification

Relational database: (SQL)

• MySQL，Oracle,Sql server ， DB2，SQLlite
• Data storage through the relationship between tables, rows and columns, student information table, attendance table

Non relational database (NoSQL) not only

• Redis,MongDB
• Non relational database, object storage, is determined by the object's own attributes.

DBMS (database management system)

• DataBase Management System
• Database management software can scientifically organize and store data, and efficiently obtain and maintain data

Why do you say this?

• Because MySQL we want to learn should be regarded as a database management system

4.MySQL introduction and installation

Concept: it is a popular open source, free relational database;
History: developed by Swedish MySQL AB company, it is currently a product of Oracle.
characteristic:

• Free, open source database;
• Compact and fully functional;
• Easy to use;
• It can run on Windows or Linux operating system;
• It is suitable for small and medium-sized or even large website applications.

install MySQL

• It is recommended to use compressed version, which is fast and convenient to install Not complicated. It is not recommended to use exe file, which is not easy to uninstall!!

• Software download, mysql5 7 64 bit download address: https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

• If the computer is 64 bit, download and use the 64 bit version!

Installation steps

1. Download the zip package.

2. Unzip it to the directory where you want to install it. I unzip it to "F:\java\MySQL"

3. Add environment variable:

   1. This computer - > properties - > Advanced - > environment variables;
   2. Select PATH and add: bin folder under your mysql installation file;
   3. Create a new my. In the F:\java\MySQL directory INI file;
   4. Edit my INI file, pay attention to the replacement path location;

[mysqld]
basedir=F:\java\MySQL
datadir=F:\java\MySQL\data\
port=3306
skip-grant-tables

1. Start CMD in administrator mode, switch the path to bin directory under mysql, and then enter mysqld – install;
2. Then enter mysqld -- initialize execute -- user = Mysql to initialize the data file;
3. Then enter the command net start mysql to start mysql again, and then use the command mysql – u root – p to enter the mysql management interface (the password can be empty);

1. Change the root password after entering the interface;

update mysql.user set authentication_string=password('root') where user='root' 
and Host = 'localhost';

1. Refresh permissions;

flush privileges;

1. Modify my INI file, delete the last skip grant tables;

1. Restart mysql for normal use (use exit first and exit mysql);

net stop MySQL
net start MySQL

1. Test on the connection and install it when the following results appear.

Note: error 29 (HY000): file 'appears/ mysql/user. MyD 'not found (errCode: 2 - no such file or directory) problem.

• MySQL files are not unloaded completely on drive C. Check drive C and delete MySQL related folders.

5.SQLyog installation

• It is a software tool that can be operated manually to manage MySQL database.

• Features: simple, easy to use, graphical.

• Download address: Address ①；Address ② (extraction code: 8fqx);

• Brainless installation, go to the next step directly; Registered software; Connect to the database, as shown in the following figure:

• Create a database; It is recommended to use utf8mb4 for character set and utf8mb4 for sorting rules_ bin.

The execution of each sqlyog is essentially corresponding to an sql, which can be viewed in the software history.

• Create a new table

• View table

• insert data

6. Connect to the database

Open MySQL command window

• Enter the installation directory \ mysql\bin in the DOS command line window
• If you set the environment variable before, you can open it in any directory!

Database connection statement: mysql -h server host address - u user name - p user password

Note: - p cannot be followed by a space, otherwise it will be regarded as the content of the password, resulting in login failure!

Note: click the "up arrow" on the keyboard to directly copy the dos command in the previous line.

• Basic database operation commands:

mysql -uroot -proot -- Connect to database
update user set password=password('123456')where user='root';  -- Change Password
flush privileges;  -- Refresh permissions
show databases; -- Show all databases
use school; -- choice school database
show tables; -- Displays all tables in the database
describe student; -- Display table mysql In database student Column information for table
create database book; -- Create database book
use book; -- choice book database

exit; -- sign out Mysql
? Command keywords : ask for help
-- Represents a single line comment
/*
SQL Multiline comment for
*/

2. Database operation

Classification of structured query statements

name	explain	command
Data definition language (DDL)	Define and manage data objects, such as databases, data tables, etc	CRATE,DROP,ALTER
Data manipulation language (DML)	Used to manipulate data contained in database objects	INSERT,VPDATE,DELETE
Data query language (DQL)	Used to query database data	SELECT
Data control language (DCL)	The language used to manage data, including permissions and data changes	GRANT,COMMIT,ROLLBACK

1. Operation database

Command line operation database

• Create database: create database [if not exists] database name;
• Delete database: drop database [if exists] database name;
• View databases: show databases;
• Use database: use database name;

If you see brackets in such statements, you can write them or not.

CREATE DATABASE westos;

DROP DATABASE westos;

SHOW DATABASES;
-- tab Key, if your table name or field name is a special character, you need to bring ``
USE book;

2. Data value and column type

Refer to: Jian Shu

• Column type: Specifies the data type stored in this column in the database.

value type

• The last one: decimal (13,2) means that the number has 13 digits and 2 digits after the decimal point.

String type

• The difference between the first two is: a fixed length and a variable length.

Date and time numeric type

NULL value

• Understood as "no value" or "unknown value";
• Do not use NULL for arithmetic operation, and the result is still NULL;

How to select the appropriate data type

1. Integer and floating point: if there are no decimals in the column you save, choose the integer type (currency, decimal).
2. Date type: DATETIME type is generally selected; The TIMESTAMP type may be used later. However, the storage range of the latter one is smaller than that of the previous one.
3. Char and varchar: char is of fixed length and varchar is of variable length. If the storage requires high speed and small space, use char; Otherwise, use varchar.

3. Data field properties

UnSigned

• Unsigned.
• Declare that the data column does not allow negative numbers.

ZEROFILL

• 0 filled;
• If the number of digits is less than 0, fill it with 0. For example, int(3),5 is 005;

Auto_InCrement

• For automatic growth, every time a piece of data is added, 1 will be automatically added to the number of previous records (default);

• It is usually used to set the primary key and is of integer type;

• Start value and step size can be defined;

• • Current table setting step (AUTO_INCREMENT=100): only the current table is affected;
  • SET @@auto_increment_increment=5 ; Affect all tables that use auto increment (global);

NULL and NOT NULL

• The default value is NULL, that is, the value of this column is not inserted;
• If NOT NULL is set, the column must have a value;

DEFAULT

• default;
• Used to set default values;

Each table must have the following five fields:

• id primary key;
• vorsion optimistic lock;
• is_delete pseudo delete;
• gmt_create creation time;
• gmt_update modification time;

4. Create data table

• It belongs to DDL. Syntax:

create table [if not exists] `Table name`(
   'Field name 1' Column type [attribute][Indexes][notes],
   'Field name 2' Column type [attribute][Indexes][notes],
  #...
   'Field name n' Column type [attribute][Indexes][notes]
)[Table type][Table character set][notes];

• explain:
  • Backquotes are used to distinguish MySQL reserved words from ordinary characters (the key under the keyboard esc).
  • Columns created between parentheses have commas between them, but the last one has no commas.
  • Indicates that you can take it at will; The field name is also optional (Chinese or English).
• Case code:

CREATE TABLE `user`( 
    `id` INT(10) NOT NULL COMMENT 'student ID', 
    `name` VARCHAR(100) NOT NULL COMMENT 'Student name', 
    `age` INT(3) NOT NULL COMMENT 'Student age', 
    PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

• For example, the gender field is male by default, otherwise it is female; If no value is specified for this column, the default value is male;

-- target : Create a school database
-- Create student table(column,field)
-- Student number int Login password varchar(20) full name,Gender varchar(2),date of birth(datatime),Home address,email

-- Before creating a table, Be sure to select the database first
USE school;

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(20) NOT NULL DEFAULT '123456' COMMENT 'password',
    `sex` varchar(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
    `birthday` datetime DEFAULT NULL COMMENT 'date of birth',
    `address` varchar(100) DEFAULT NULL COMMENT 'address',
    `email` varchar(50) DEFAULT NULL COMMENT 'mailbox',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- View the definition of the database
SHOW CREATE DATABASE school;

-- View the definition of the data table
SHOW CREATE TABLE student;

-- Display table structure
DESC student;  -- Set strict check mode(No fault tolerance)SET sql_mode='STRICT_TRANS_TABLES';

5. Type of data sheet

Sets the type of data table

CREATE TABLE Table name(
   -- Omitted code
   -- Mysql notes
   -- 1. # Single-Line Comments 
   -- 2. /*...*/ multiline comment 
)ENGINE = MyISAM (or InnoDB)

-- see mysql Supported engine types (Table type)
SHOW ENGINES;

• Types of MySQL data tables: MyISAM, InnoDB, heap, Bob, CSV, etc

Common MyISAM and InnoDB types:

name	MyISAM	InnoDB
transaction processing	I won't support it	support
Data row locking	I won't support it	support
Foreign key constraint	I won't support it	support
Full text index	support	I won't support it
Tablespace size	less	Larger, about 2 times

Applicable occasions:

• Applicable to MyISAM: save space and corresponding speed;
• Applicable to InnoDB: security, transaction processing, multi-user operation data table;

Where it exists in physical space

• MySQL data table is stored on disk in the form of file;

  • Including table file, data file and option file of database;
  • Location: the Mysql installation directory \ data \ stores the data table The directory name corresponds to the database name, and the file name under the directory corresponds to the data table;

• be careful:

  • * . frm – table structure definition file;
  • * . MYD – data file;
  • * . MYI – index file;
  • InnoDB type data table has only one * frm file and ibdata1 file in the upper directory;
  • The MyISAM type data table corresponds to three files:

Set data table character set

• We can set different character sets for database, data table and data column. Setting method:
  • When creating, it is set by command, such as: CREATE TABLE table name () CHARSET = utf8;
  • If there is no setting, according to the MySQL database configuration file my Ini.

6. Modify the database

Modify table (ALTER TABLE)

• Modify table name: ALTER TABLE old table name RENAME AS new table name;

• ADD field: ALTER TABLE table table name ADD field column attribute [attribute];

• Modify field:

  • ALTER TABLE table name MODIFY field column type [attribute];
  • ALTER TABLE table name CHANGE old field name new field column attribute [attribute];

• Delete field: ALTER TABLE table name DROP field name;

USE school;

CREATE TABLE `teacher`( 
    `id` INT(10) NOT NULL COMMENT 'teacher ID', 
    `name` VARCHAR(100) NOT NULL COMMENT 'Teacher name', 
    `age` INT(3) NOT NULL COMMENT 'Teacher age', 
    PRIMARY KEY (`id`)
) ENGINE=INNOBASE CHARSET=utf8 COLLATE=utf8_general_ci;

-- Modify table name :`ALTER TABLE Old table name RENAME AS New table name;`
ALTER TABLE teacher RENAME AS teacher1;

-- Add field : `ALTER TABLE Table name ADD Field column properties[attribute]；`
ALTER TABLE teacher1 ADD age INT(12);

-- Modify field : `ALTER TABLE Table name MODIFY Field column type[attribute]；`
ALTER TABLE teacher1 MODIFY age VARCHAR(12);	-- Modify constraints
ALTER TABLE teacher1 CHANGE age age1 INT(12);    -- Field rename

-- Delete field :  `ALTER TABLE Table name DROP Field name;`
ALTER TABLE teacher1 DROP age1;

Delete data table

• Syntax: DROP TABLE [IF EXISTS] table name;
  • IF EXISTS is optional to judge whether the data table exists;
  • If you delete a nonexistent data table, an error will be thrown;

Note:

1. You can wrap the identifier (database name, table name, field name, index, alias) in back quotation marks (`) to avoid duplicate names with keywords! Chinese can also be used as an identifier!
2. Each library directory has an option file DB to save the current database opt.
3. notes:
   1. Single line note: # note content
   2. Multiline comment: / * comment content*/
   3. Single line comment: – comment content (standard SQL comment style, requiring double dashes followed by a space character (space, TAB, line feed, etc.)
4. Pattern wildcard:
   1. _ : Any single character
   2. %: any number of characters, even zero characters
   3. Single quotation marks need to be escaped \ '
5. The statement terminator in CMD command line can be ", "\ g" and "\ g" only affect the display results. Other places still end with semicolons. delimiter to modify the statement terminator of the current conversation.
6. SQL is case insensitive (keyword)
7. Clear existing statements: \ c

3.MySQL data management

1. Foreign key

Foreign key concept

If a public keyword is the primary keyword in a relationship, the public keyword is called the foreign key of another relationship. Thus, the foreign key represents the correlation between the two relationships. A table with a foreign key of another relationship as the primary key is called the master table, and a table with this foreign key is called the slave table of the master table.

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

• Foreign bond action
  • The main purpose of maintaining data consistency and integrity is to control the data and constraints stored in the foreign key table. To associate two tables, foreign keys can only refer to the values of columns in the appearance or use null values.

Create foreign key

• Method 1: specify foreign key constraints when creating tables.

-- How to create a foreign key : Create child tables and foreign keys

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

-- Student information sheet (Student number,full name,Gender,grade,mobile phone,address,date of birth,mailbox,ID number)
CREATE TABLE `student2` (
`studentno` INT(4) NOT NULL COMMENT 'Student number',
`studentname` VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
`sex` TINYINT(1) DEFAULT '1' COMMENT 'Gender',
`gradeid` INT(10) DEFAULT NULL COMMENT 'grade',
`phoneNum` VARCHAR(50) NOT NULL COMMENT 'mobile phone',
`address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
`borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

• After creating the sub table, click the table you created. If you can see the foreign key name you created under the indexes of the table you created, the foreign key creation is successful. As follows:

• Method 2: modify after table creation.

-- Create foreign key mode 2 : After creating the sub table,Modify child tables and add foreign keys
CREATE TABLE `student` (
`id` INT(4) NOT NULL COMMENT 'Student number',
`name` VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
`sex` TINYINT(1) DEFAULT '1' COMMENT 'Gender',
`gradeid` INT(10) DEFAULT NULL COMMENT 'grade',
`phoneNum` VARCHAR(50) NOT NULL COMMENT 'mobile phone',
`address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
`borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

• Error code: 1022 Can't write; duplicate key in table ‘#sql-150c_3’
  • It is caused by duplicate foreign key names. Just change it to a different name.

Delete foreign key

• Operation: delete the grade table and report an error;

• Note: when deleting a table with primary foreign key relationship, delete the sub table first and then the primary table.

-- Delete foreign key
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- It is found that the above steps have been executed,The index is still there,So you have to delete the index
-- notes:This index is generated by default when creating foreign keys
ALTER TABLE student DROP INDEX FK_gradeid;

2.DML language

Significance of database: data storage and data management.

How to manage database data:

• Manage database data through management tools such as SQLyog;
• Manage database data through DML statement;

DML language: data operation language.

• Used to operate the data contained in the database object;

• include:

• • INSERT (add data statement);
  • UPDATE (UPDATE data statement);
  • DELETE (DELETE data statement);

3. Add data

INSERT command

• Syntax: INSERT INTO table name [(field 1, field 2, field 3,...)] VALUES('value 1 ',' value 2 ',' value 3 ');

• be careful:

  • Fields or values are separated by English commas;
  • ’Field 1, field 2... 'this part can be omitted, but the added values must correspond to the table structure, data column and order, and the quantity must be consistent;
  • Multiple pieces of data can be inserted at the same time, separated by English commas after values;

-- Insert statement (add)
-- grammar : INSERT INTO Table name[(Field 1,Field 2,Field 3,...)] VALUES('Value 1','Value 2','Value 3')
INSERT INTO grade(gradename) VALUES ('Freshman');

-- Primary key auto increment,Can you omit it?
INSERT INTO grade VALUES ('Sophomore');

-- query:INSERT INTO grade VALUE ('Sophomore')
-- Error code: 1136 Column count doesn`t match value count at row 1

-- Insert multiple pieces of data at a time
INSERT INTO grade(gradename) VALUES ('Junior'),('Senior');

-- conclusion:'Field 1,Field 2...'This part can be omitted , However, the added value must be consistent with the table structure,Data column,Sequence correspondence,And the quantity is consistent.

CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
  `name` VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
  `sex` VARCHAR(10) DEFAULT '1' COMMENT 'Gender',
  `gradeid` INT(10) DEFAULT NULL COMMENT 'grade',
  `phoneNum` VARCHAR(50) DEFAULT NULL COMMENT 'mobile phone',
  `address` VARCHAR(255) DEFAULT NULL COMMENT 'address',
  `borndate` DATETIME DEFAULT NULL COMMENT 'birthday',
  `email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
  `idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid2` (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

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

INSERT INTO `student`(`name`,`address`,`sex`) VALUES ('Zhang San','admin','male');

INSERT INTO `student`(`name`,`address`,`sex`) 
VALUES ('Li Si','pppppp','female'),('Wang Wu','tttttt','male');

4. Modify data

update command

• Syntax: UPDATE table name SET column_name=value [,column_name2=value2,...] [WHEREcondition];

• be careful:

  • column_name is the data column to be changed;
  • value is the modified data and can be a variable, specifically an expression or a nested SELECT result;
  • Condition is the filter condition. If it is not specified, all column data of the table will be modified;

  -- case
  UPDATE `student` SET `name`='subei',`birthday`=CURRENT_DATE WHERE id = 5;
  

• Test:

-- Revise the grade name and bring the introduction
UPDATE grade SET gradename = 'high school' WHERE gradeid = 1;

-- All tables will be changed without specifying conditions
UPDATE `grade` SET `gradename`='fruit juice';

-- Modify multiple properties
UPDATE `student` SET `name`='admin',`email`='2943357596@qq.com' WHERE id = 2;

The where condition clause can be simply understood as: conditionally filter data from a table.

operator	meaning	example	result
=	be equal to	5=6	false
< > or=	Not equal to	5!=6	true
>	greater than	5>6	false
<	less than	5<6	true
>=	Greater than or equal to	5>=6	false
<=	Less than or equal to	5<=6	true
BETWEEN	Between a range	BETWEEN 5 AND 10	-
AND	also	5>1 AND 1>2	false
OR	or	5>1 OR 1>2	true

• Test 2:

UPDATE `student` SET `name` = 'high school' WHERE `id` <= 3;

5. Delete data

DELETE command

• Syntax: DELETE FROM table name [WHERE condition];

• Note: condition is a filter condition. If it is not specified, all column data of the table will be deleted.

-- Delete last data
DELETE FROM grade WHERE gradeid = 5;

TRUNCATE command

• Function: used to completely empty table data, but the table structure, index, constraints, etc. remain unchanged;

• Syntax: TRUNCATE [TABLE] table name;

-- Empty grade table
TRUNCATE grade;

Note: it is different from the DELETE command

• Same: data can be deleted without deleting the table structure, but TRUNCATE is faster;
• Different:
  • Reset auto using TRUNCATE TABLE_ Increment counter;
  • Using TRUNCATE TABLE has no impact on transactions.
• Test:

-- Create a test table
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- Insert several test data
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

-- Delete table data(No where Conditional delete)
DELETE FROM test;
-- conclusion:If not specified Where Delete all column data of the table,The current value of self increment is still based on the original value,Will log.

-- Delete table data(truncate)
TRUNCATE TABLE test;
-- conclusion:truncate Delete data,The current value of auto increment will return to the initial value and start again;No logs will be logged.

-- Same use DELETE Clear database table data of different engines.After restarting the database service
-- InnoDB : The auto increment column starts again from the initial value (Because it is stored in memory,Loss of power)
-- MyISAM : The auto increment column still starts from the previous auto increment data (Exist in file,Not lost)

4.DQL query data

1.DQL language

DQL (data query language)

• Query database data, such as SELECT statement;
• Simple single table query or complex query and nested query of multiple tables;
• It is the core and most important statement in database language;
• The most frequently used statements;

SELECT syntax

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- Joint query
  [WHERE ...]  -- Specify the conditions to be met for the results
  [GROUP BY ...]  -- Specify which fields the results are grouped by
  [HAVING]  -- Secondary conditions that must be met to filter grouped records
  [ORDER BY ...]  -- Specifies that query records are sorted by one or more criteria
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- Specify which records to query from

• Note: [] brackets represent optional and {} brackets represent mandatory.

SQL preparation

-- Create a school database
CREATE DATABASE IF NOT EXISTS `school`;

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 'Mailbox 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 'Test date',
    `studentresult` INT (4) NOT NULL COMMENT 'Examination results',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- Insert student data
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'111111','Guo Jing',1,1,'13500000001','1 Zhongguancun Street, Haidian District, Beijing','1986-12-11 00:00:00','test1@bdqn.cn','450323198612111234'),
(1001,'123456','Wen Cai Li',1,2,'13500000002','Luoyang, Henan','1981-12-31 00:00:00','test1@bdqn.cn','450323198112311234'),
(1002,'111111','Li Siwen',1,1,'13500000003','Heping District, Tianjin','1986-11-30 00:00:00','test1@bdqn.cn','450323198611301234'),
(1003,'123456','Wu Song',1,3,'13500000004','Luwan District ','1986-12-31 00:00:00','test1@bdqn.cn','450323198612314234'),
(1004,'123456','Zhang San',1,4,'13500000005','Tongzhou, Beijing','1989-12-31 00:00:00','test1@bdqn.cn','450323198612311244'),
(1005,'123456','Zhang Qiuli ',2,1,'13500000006','Lingchuan, Guilin, Guangxi','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311214'),
(1006,'123456','Chaumet ',2,4,'13500000007','The address is unknown','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311134'),
(1007,'111111','Ouyang Junfeng',1,1,'13500000008','Dongcheng District, Beijing','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311133'),
(1008,'111111','Mei Chaofeng',1,1,'13500000009','Luoyang, Henan','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311221'),
(1009,'123456','Liu Yi',1,2,'13500000011','Anhui','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311231'),
(1010,'111111','Da Fan',1,1,'13500000012','Luoyang, Henan','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311044'),
(1011,'111111','Odens',1,1,'13500000013','Zhongguancun Street, Haidian District, Beijing*number','1984-12-31 00:00:00','test1@bdqn.cn','450323198412311234'),
(1012,'123456','Doren',2,3,'13500000014','Central street, Nanning, Guangxi','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311334'),
(1013,'123456','Li Mei',2,1,'13500000015','Luwan District ','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311534'),
(1014,'123456','Zhang De',2,4,'13500000016','Zhongguancun Street, Haidian District, Beijing*number','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311264'),
(1015,'123456','Li Dongfang',1,4,'13500000017','Lingchuan, Guilin, Guangxi','1976-12-31 00:00:00','test1@bdqn.cn','450323197612311234'),
(1016,'111111','Liu Fenfen',1,1,'13500000018','Luwan District ','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311251'),
(1017,'123456','cocoa',2,3,'13500000019','1 Chang'an Street, Beijing','1981-09-10 00:00:00','test1@bdqn.cn','450323198109108311'),
(10066,'','Tom',1,1,'13500000000','','0000-00-00 00:00:00','email@22.com','33123123123123123');

-- Insert grade data
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2012-11-10 10:00:00',75),
(1000,3,'2011-12-19 10:00:00',76),
(1000,4,'2010-11-18 11:00:00',93),
(1000,5,'2013-11-11 14:00:00',97),
(1000,6,'2012-09-13 15:00:00',87),
(1000,7,'2011-10-16 16:00:00',79),
(1000,8,'2010-11-11 16:00:00',74),
(1000,9,'2013-11-21 10:00:00',69),
(1000,10,'2012-11-11 12:00:00',78),
(1000,11,'2011-11-11 14:00:00',66),
(1000,12,'2010-11-11 15:00:00',82),
(1000,13,'2013-11-11 14:00:00',94),
(1000,14,'2012-11-11 15:00:00',98),
(1000,15,'2011-12-11 10:00:00',70),
(1000,16,'2010-09-11 10:00:00',74),
(1001,1,'2013-11-11 16:00:00',76),
(1001,2,'2012-11-10 10:00:00',93),
(1001,3,'2011-12-19 10:00:00',65),
(1001,4,'2010-11-18 11:00:00',71),
(1001,5,'2013-11-11 14:00:00',98),
(1001,6,'2012-09-13 15:00:00',74),
(1001,7,'2011-10-16 16:00:00',85),
(1001,8,'2010-11-11 16:00:00',69),
(1001,9,'2013-11-21 10:00:00',63),
(1001,10,'2012-11-11 12:00:00',70),
(1001,11,'2011-11-11 14:00:00',62),
(1001,12,'2010-11-11 15:00:00',90),
(1001,13,'2013-11-11 14:00:00',97),
(1001,14,'2012-11-11 15:00:00',89),
(1001,15,'2011-12-11 10:00:00',72),
(1001,16,'2010-09-11 10:00:00',90),
(1002,1,'2013-11-11 16:00:00',61),
(1002,2,'2012-11-10 10:00:00',80),
(1002,3,'2011-12-19 10:00:00',89),
(1002,4,'2010-11-18 11:00:00',88),
(1002,5,'2013-11-11 14:00:00',82),
(1002,6,'2012-09-13 15:00:00',91),
(1002,7,'2011-10-16 16:00:00',63),
(1002,8,'2010-11-11 16:00:00',84),
(1002,9,'2013-11-21 10:00:00',60),
(1002,10,'2012-11-11 12:00:00',71),
(1002,11,'2011-11-11 14:00:00',93),
(1002,12,'2010-11-11 15:00:00',96),
(1002,13,'2013-11-11 14:00:00',83),
(1002,14,'2012-11-11 15:00:00',69),
(1002,15,'2011-12-11 10:00:00',89),
(1002,16,'2010-09-11 10:00:00',83),
(1003,1,'2013-11-11 16:00:00',91),
(1003,2,'2012-11-10 10:00:00',75),
(1003,3,'2011-12-19 10:00:00',65),
(1003,4,'2010-11-18 11:00:00',63),
(1003,5,'2013-11-11 14:00:00',90),
(1003,6,'2012-09-13 15:00:00',96),
(1003,7,'2011-10-16 16:00:00',97),
(1003,8,'2010-11-11 16:00:00',77),
(1003,9,'2013-11-21 10:00:00',62),
(1003,10,'2012-11-11 12:00:00',81),
(1003,11,'2011-11-11 14:00:00',76),
(1003,12,'2010-11-11 15:00:00',61),
(1003,13,'2013-11-11 14:00:00',93),
(1003,14,'2012-11-11 15:00:00',79),
(1003,15,'2011-12-11 10:00:00',78),
(1003,16,'2010-09-11 10:00:00',96),
(1004,1,'2013-11-11 16:00:00',84),
(1004,2,'2012-11-10 10:00:00',79),
(1004,3,'2011-12-19 10:00:00',76),
(1004,4,'2010-11-18 11:00:00',78),
(1004,5,'2013-11-11 14:00:00',81),
(1004,6,'2012-09-13 15:00:00',90),
(1004,7,'2011-10-16 16:00:00',63),
(1004,8,'2010-11-11 16:00:00',89),
(1004,9,'2013-11-21 10:00:00',67),
(1004,10,'2012-11-11 12:00:00',100),
(1004,11,'2011-11-11 14:00:00',94),
(1004,12,'2010-11-11 15:00:00',65),
(1004,13,'2013-11-11 14:00:00',86),
(1004,14,'2012-11-11 15:00:00',77),
(1004,15,'2011-12-11 10:00:00',82),
(1004,16,'2010-09-11 10:00:00',87),
(1005,1,'2013-11-11 16:00:00',82),
(1005,2,'2012-11-10 10:00:00',92),
(1005,3,'2011-12-19 10:00:00',80),
(1005,4,'2010-11-18 11:00:00',92),
(1005,5,'2013-11-11 14:00:00',97),
(1005,6,'2012-09-13 15:00:00',72),
(1005,7,'2011-10-16 16:00:00',84),
(1005,8,'2010-11-11 16:00:00',79),
(1005,9,'2013-11-21 10:00:00',76),
(1005,10,'2012-11-11 12:00:00',87),
(1005,11,'2011-11-11 14:00:00',65),
(1005,12,'2010-11-11 15:00:00',67),
(1005,13,'2013-11-11 14:00:00',63),
(1005,14,'2012-11-11 15:00:00',64),
(1005,15,'2011-12-11 10:00:00',99),
(1005,16,'2010-09-11 10:00:00',97),
(1006,1,'2013-11-11 16:00:00',82),
(1006,2,'2012-11-10 10:00:00',73),
(1006,3,'2011-12-19 10:00:00',79),
(1006,4,'2010-11-18 11:00:00',63),
(1006,5,'2013-11-11 14:00:00',97),
(1006,6,'2012-09-13 15:00:00',83),
(1006,7,'2011-10-16 16:00:00',78),
(1006,8,'2010-11-11 16:00:00',88),
(1006,9,'2013-11-21 10:00:00',89),
(1006,10,'2012-11-11 12:00:00',82),
(1006,11,'2011-11-11 14:00:00',70),
(1006,12,'2010-11-11 15:00:00',69),
(1006,13,'2013-11-11 14:00:00',64),
(1006,14,'2012-11-11 15:00:00',80),
(1006,15,'2011-12-11 10:00:00',90),
(1006,16,'2010-09-11 10:00:00',85),
(1007,1,'2013-11-11 16:00:00',87),
(1007,2,'2012-11-10 10:00:00',63),
(1007,3,'2011-12-19 10:00:00',70),
(1007,4,'2010-11-18 11:00:00',74),
(1007,5,'2013-11-11 14:00:00',79),
(1007,6,'2012-09-13 15:00:00',83),
(1007,7,'2011-10-16 16:00:00',86),
(1007,8,'2010-11-11 16:00:00',76),
(1007,9,'2013-11-21 10:00:00',65),
(1007,10,'2012-11-11 12:00:00',87),
(1007,11,'2011-11-11 14:00:00',69),
(1007,12,'2010-11-11 15:00:00',69),
(1007,13,'2013-11-11 14:00:00',90),
(1007,14,'2012-11-11 15:00:00',84),
(1007,15,'2011-12-11 10:00:00',95),
(1007,16,'2010-09-11 10:00:00',92),
(1008,1,'2013-11-11 16:00:00',96),
(1008,2,'2012-11-10 10:00:00',62),
(1008,3,'2011-12-19 10:00:00',97),
(1008,4,'2010-11-18 11:00:00',84),
(1008,5,'2013-11-11 14:00:00',86),
(1008,6,'2012-09-13 15:00:00',72),
(1008,7,'2011-10-16 16:00:00',67),
(1008,8,'2010-11-11 16:00:00',83),
(1008,9,'2013-11-21 10:00:00',86),
(1008,10,'2012-11-11 12:00:00',60),
(1008,11,'2011-11-11 14:00:00',61),
(1008,12,'2010-11-11 15:00:00',68),
(1008,13,'2013-11-11 14:00:00',99),
(1008,14,'2012-11-11 15:00:00',77),
(1008,15,'2011-12-11 10:00:00',73),
(1008,16,'2010-09-11 10:00:00',78),
(1009,1,'2013-11-11 16:00:00',67),
(1009,2,'2012-11-10 10:00:00',70),
(1009,3,'2011-12-19 10:00:00',75),
(1009,4,'2010-11-18 11:00:00',92),
(1009,5,'2013-11-11 14:00:00',76),
(1009,6,'2012-09-13 15:00:00',90),
(1009,7,'2011-10-16 16:00:00',62),
(1009,8,'2010-11-11 16:00:00',68),
(1009,9,'2013-11-21 10:00:00',70),
(1009,10,'2012-11-11 12:00:00',83),
(1009,11,'2011-11-11 14:00:00',88),
(1009,12,'2010-11-11 15:00:00',65),
(1009,13,'2013-11-11 14:00:00',91),
(1009,14,'2012-11-11 15:00:00',99),
(1009,15,'2011-12-11 10:00:00',65),
(1009,16,'2010-09-11 10:00:00',83),
(1010,1,'2013-11-11 16:00:00',83),
(1010,2,'2012-11-10 10:00:00',87),
(1010,3,'2011-12-19 10:00:00',89),
(1010,4,'2010-11-18 11:00:00',99),
(1010,5,'2013-11-11 14:00:00',91),
(1010,6,'2012-09-13 15:00:00',96),
(1010,7,'2011-10-16 16:00:00',72),
(1010,8,'2010-11-11 16:00:00',72),
(1010,9,'2013-11-21 10:00:00',98),
(1010,10,'2012-11-11 12:00:00',73),
(1010,11,'2011-11-11 14:00:00',68),
(1010,12,'2010-11-11 15:00:00',62),
(1010,13,'2013-11-11 14:00:00',67),
(1010,14,'2012-11-11 15:00:00',69),
(1010,15,'2011-12-11 10:00:00',71),
(1010,16,'2010-09-11 10:00:00',66),
(1011,1,'2013-11-11 16:00:00',62),
(1011,2,'2012-11-10 10:00:00',72),
(1011,3,'2011-12-19 10:00:00',96),
(1011,4,'2010-11-18 11:00:00',64),
(1011,5,'2013-11-11 14:00:00',89),
(1011,6,'2012-09-13 15:00:00',91),
(1011,7,'2011-10-16 16:00:00',95),
(1011,8,'2010-11-11 16:00:00',96),
(1011,9,'2013-11-21 10:00:00',89),
(1011,10,'2012-11-11 12:00:00',73),
(1011,11,'2011-11-11 14:00:00',82),
(1011,12,'2010-11-11 15:00:00',98),
(1011,13,'2013-11-11 14:00:00',66),
(1011,14,'2012-11-11 15:00:00',69),
(1011,15,'2011-12-11 10:00:00',91),
(1011,16,'2010-09-11 10:00:00',69),
(1012,1,'2013-11-11 16:00:00',86),
(1012,2,'2012-11-10 10:00:00',66),
(1012,3,'2011-12-19 10:00:00',97),
(1012,4,'2010-11-18 11:00:00',69),
(1012,5,'2013-11-11 14:00:00',70),
(1012,6,'2012-09-13 15:00:00',74),
(1012,7,'2011-10-16 16:00:00',91),
(1012,8,'2010-11-11 16:00:00',97),
(1012,9,'2013-11-21 10:00:00',84),
(1012,10,'2012-11-11 12:00:00',82),
(1012,11,'2011-11-11 14:00:00',90),
(1012,12,'2010-11-11 15:00:00',91),
(1012,13,'2013-11-11 14:00:00',91),
(1012,14,'2012-11-11 15:00:00',97),
(1012,15,'2011-12-11 10:00:00',85),
(1012,16,'2010-09-11 10:00:00',90),
(1013,1,'2013-11-11 16:00:00',73),
(1013,2,'2012-11-10 10:00:00',69),
(1013,3,'2011-12-19 10:00:00',91),
(1013,4,'2010-11-18 11:00:00',72),
(1013,5,'2013-11-11 14:00:00',76),
(1013,6,'2012-09-13 15:00:00',87),
(1013,7,'2011-10-16 16:00:00',61),
(1013,8,'2010-11-11 16:00:00',77),
(1013,9,'2013-11-21 10:00:00',83),
(1013,10,'2012-11-11 12:00:00',99),
(1013,11,'2011-11-11 14:00:00',91),
(1013,12,'2010-11-11 15:00:00',84),
(1013,13,'2013-11-11 14:00:00',98),
(1013,14,'2012-11-11 15:00:00',74),
(1013,15,'2011-12-11 10:00:00',92),
(1013,16,'2010-09-11 10:00:00',90),
(1014,1,'2013-11-11 16:00:00',64),
(1014,2,'2012-11-10 10:00:00',81),
(1014,3,'2011-12-19 10:00:00',79),
(1014,4,'2010-11-18 11:00:00',74),
(1014,5,'2013-11-11 14:00:00',65),
(1014,6,'2012-09-13 15:00:00',88),
(1014,7,'2011-10-16 16:00:00',86),
(1014,8,'2010-11-11 16:00:00',77),
(1014,9,'2013-11-21 10:00:00',86),
(1014,10,'2012-11-11 12:00:00',85),
(1014,11,'2011-11-11 14:00:00',86),
(1014,12,'2010-11-11 15:00:00',75),
(1014,13,'2013-11-11 14:00:00',89),
(1014,14,'2012-11-11 15:00:00',79),
(1014,15,'2011-12-11 10:00:00',73),
(1014,16,'2010-09-11 10:00:00',68),
(1015,1,'2013-11-11 16:00:00',99),
(1015,2,'2012-11-10 10:00:00',60),
(1015,3,'2011-12-19 10:00:00',60),
(1015,4,'2010-11-18 11:00:00',75),
(1015,5,'2013-11-11 14:00:00',78),
(1015,6,'2012-09-13 15:00:00',78),
(1015,7,'2011-10-16 16:00:00',84),
(1015,8,'2010-11-11 16:00:00',95),
(1015,9,'2013-11-21 10:00:00',93),
(1015,10,'2012-11-11 12:00:00',79),
(1015,11,'2011-11-11 14:00:00',74),
(1015,12,'2010-11-11 15:00:00',65),
(1015,13,'2013-11-11 14:00:00',63),
(1015,14,'2012-11-11 15:00:00',74),
(1015,15,'2011-12-11 10:00:00',67),
(1015,16,'2010-09-11 10:00:00',65),
(1016,1,'2013-11-11 16:00:00',97),
(1016,2,'2012-11-10 10:00:00',90),
(1016,3,'2011-12-19 10:00:00',77),
(1016,4,'2010-11-18 11:00:00',75),
(1016,5,'2013-11-11 14:00:00',75),
(1016,6,'2012-09-13 15:00:00',97),
(1016,7,'2011-10-16 16:00:00',96),
(1016,8,'2010-11-11 16:00:00',92),
(1016,9,'2013-11-21 10:00:00',62),
(1016,10,'2012-11-11 12:00:00',83),
(1016,11,'2011-11-11 14:00:00',98),
(1016,12,'2010-11-11 15:00:00',94),
(1016,13,'2013-11-11 14:00:00',62),
(1016,14,'2012-11-11 15:00:00',97),
(1016,15,'2011-12-11 10:00:00',76),
(1016,16,'2010-09-11 10:00:00',82),
(1017,1,'2013-11-11 16:00:00',100),
(1017,2,'2012-11-10 10:00:00',88),
(1017,3,'2011-12-19 10:00:00',86),
(1017,4,'2010-11-18 11:00:00',73),
(1017,5,'2013-11-11 14:00:00',96),
(1017,6,'2012-09-13 15:00:00',64),
(1017,7,'2011-10-16 16:00:00',81),
(1017,8,'2010-11-11 16:00:00',66),
(1017,9,'2013-11-21 10:00:00',76),
(1017,10,'2012-11-11 12:00:00',95),
(1017,11,'2011-11-11 14:00:00',73),
(1017,12,'2010-11-11 15:00:00',82),
(1017,13,'2013-11-11 14:00:00',85),
(1017,14,'2012-11-11 15:00:00',68),
(1017,15,'2011-12-11 10:00:00',99),
(1017,16,'2010-09-11 10:00:00',76);

-- 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 First academic year',110,1),
(10,'JAVA Second academic year',110,2),
(11,'JAVA The third academic year',100,3),
(12,'JAVA The Fourth academic year',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#Foundation ', 130,1);

2. Specify query fields

-- Query the results of all data columns in the table , use **" \* "** Symbol; However, it is inefficient and not recommended .

-- Query all student information
SELECT * FROM student;

-- Query specified column(Student number , full name)
SELECT studentno,studentname FROM student;

AS clause AS alias

effect:

• You can give the data column a new alias;
• You can give the table a new alias;
• The calculated or summarized results can be replaced by another new name;

-- Here is the alias for the column(of course as Keywords can be omitted)
SELECT studentno AS Student number,studentname AS full name FROM student;

-- use as You can also alias the table
SELECT studentno AS Student number,studentname AS full name FROM student AS s;

-- use as,Give the query results a new name
-- CONCAT()Function concatenation string
SELECT CONCAT('full name:',studentname) AS New name FROM student;

Use of DISTINCT keyword

• Function: remove the duplicate data in the record results returned by the SELECT query (the returned values of all columns are the same), and return only one.

-- Check which students took the exam(Student number) Remove duplicates
SELECT * FROM result; -- View test results
SELECT studentno FROM result; -- Check which students took the exam
SELECT DISTINCT studentno FROM result; -- understand:DISTINCT Remove duplicates , (The default is ALL)

Database columns - (expressions)

• Expressions in the database: generally composed of text values, column values, nulls, functions and operators.

Application scenario:

• The SELECT statement is used in the returned result column;

• Order by, having and other sub clauses in the SELECT statement;

• Expressions are used in where conditional statements in DML statements.

  -- selcet Expressions can be used in queries
  SELECT VERSION(); -- Query version number
  SELECT 100*3-1 AS Calculation results; -- expression
  SELECT @@auto_increment_increment; -- Query auto increment step
  
  -- Students' test scores are collectively raised by one point to view
  SELECT studentno,StudentResult+1 AS 'After scoring' FROM result;
  

• Avoid including '.', '*' in the SQL return result And parentheses interfere with the development of language programs.

3.where conditional statement

• Function: used to retrieve qualified records in the data table.

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

Logical operators: try to use English symbols.

Operator name	grammar	describe
AND or&&	a AND b or a & & B	Logic and are true at the same time, and the result is true
OR or||	a OR b or a | B	Logical or, as long as one is true, the result is true
NOT or!	NOT a or! a	If the operand is false, the result is true

• test

-- =============where===============
SELECT studentno,studentResult FROM result;

-- The query test score is 95-100 Between
SELECT studentno,studentresult
FROM result
WHERE studentresult>=95 AND studentresult<=100;

-- AND It can also be written as &&
SELECT studentno,studentresult
FROM result 
WHERE studentresult>=95 AND studentresult<=100;

-- Fuzzy query(Corresponding word:Precise query)
SELECT studentno,studentresult
FROM result WHERE studentresult BETWEEN 95 AND 100;

-- Except classmate 1000,Ask other students for their grades
SELECT studentno,studentresult
FROM result WHERE studentno!=1000;

-- use NOT
SELECT studentno,studentresult
FROM result WHERE NOT studentno=1000;

Fuzzy queries: comparison operators

Operator name	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 ranges between b and c, the result is true
LIKE	a LIKE b	SQL pattern matching. If a matches b, the result is true
IN	a IN	If a equals one of a1, a2... The result is true

• be careful:

  • Arithmetic operation can only be performed between records of numerical data type;
  • Only data of the same data type can be compared;

• Test:

-- ===================LIKE==========================
-- Inquire the student number and name of students surnamed Liu
-- like Wildcards used in combination : % (Represents 0 to any character) _ (One character)
SELECT studentno,studentname FROM student
WHERE studentname LIKE 'Liu%';

-- Inquire about students surnamed Liu,There is only one word after it
SELECT studentno,studentname FROM student
WHERE studentname LIKE 'Liu_';

-- Inquire about students surnamed Liu,There are only two words behind it
SELECT studentno,studentname FROM student
WHERE studentname LIKE 'Liu__';

-- Query names containing plum characters
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%plum blossom%';

-- If the query name contains special characters, you need to use escape symbols '\'
-- Custom escape key: ESCAPE ':'

-- =====================IN========================
-- The inquiry student number is 1000,1001,1002 Name of student
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- The inquiry address is in Beijing,Nanjing,Students in Luoyang, Henan
SELECT studentno,studentname,address FROM student
WHERE address IN ('Beijing','Nanjing','Luoyang, Henan');

-- ====================NULL empty=========================
-- Query students whose birth date is not filled in
-- Can't write directly=NULL , This represents a mistake , use is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- Query students with birth date
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- Query students who do not write their home address(Empty string is not equal to null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4. Associated table query

JOIN comparison

Operator name	describe
INNER JOIN	If there is at least one match in the table, the row is returned
LEFT JOIN	Even if there is no match in the right table, all rows are returned from the left table
RIGHT JOIN	Even if there is no match in the left table, all rows are returned from the right table

• Seven kinds of Join:

• join query
  • If you need to query the data of multiple data tables, you can implement multiple queries through the join operator.
• inner join
  • Query the intersection of result sets in two tables.
• outer join
  • Left outer join - (the left table is used as the benchmark, and the right table is matched one by one. If it fails to match, the records of the left table are returned, and the right table is filled with NULL).
  • Right outer join - (take the right table as the benchmark, and the left table will match one by one. If it fails to match, the records of the right table will be returned, and the left table will be filled with NULL).
• Equivalent connection and non equivalent connection.
• Self connection

-- Query the information of students who took the exam(Student number,Student name,Account number,fraction)
SELECT * FROM student;
SELECT * FROM result;

/*Idea:
(1):Analyze the requirements and determine that the query columns come from two classes, student result and join query
(2):Determine which connection query to use? (internal connection)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno;

-- Right connection(Can also be achieved)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno;

-- Equivalent connection
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno;

-- Left connection (Inquired all the students,Those who don't take the exam will also be found out)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno;

-- Query absent students(Left connection application scenario)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL;

-- Thinking questions:Query the information of students who took the exam(Student number,Student name,Account name,fraction)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno;

Self connect (understand!!)

• Your own table is connected with your own table. Core: one table is split into two identical tables.

/*
Requirement: from a table containing column ID, column name and parent column ID
    Query parent column name and other sub column names
*/

-- Create a table
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 'Subject name',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

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

-- to write SQL sentence,Show the parent-child relationship of the column (Parent column name,Sub column name)
-- Core idea:Think of a table as like as two peas of two models.,Then join the two tables to query(Self connection)
SELECT a.`categoryName` AS 'Parent column',b.`categoryName` AS 'Sub column'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`;

-- Thinking questions:Query the information of students who took the exam(Student number,Student name,Account name,fraction)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno;

-- Query students and their grades(Student number,Student name,Grade name)
SELECT studentno AS Student number,studentname AS Student name,gradename AS Grade name
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`;

-- Query subject and grade(Account name,Grade name)
SELECT subjectname AS Account name,gradename AS Grade name
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid;

-- Query database structure-1 All test results(Student number student name subject name grade)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='database structure -1';

5. Paging and sorting

order by

• Syntax: ORDER BY
  • The ORDER BY statement sorts the result set by the specified column.
  • The ORDER BY statement sorts records in ascending ASC ORDER BY default.
  • If you want to sort records in descending order, you can use the DESC keyword.

-- Query database structure-1 All test results(Student number student name subject name grade)
-- Sort by grades in descending order
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='database structure -1'
ORDER BY StudentResult DESC;

-- If the grades are the same, arrange them according to other rules, such as ascending and descending student numbers.
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='database structure -1'
ORDER BY StudentResult DESC, studentno DESC;

be careful:

ORDER BY StudentResult, studentno DESC;

• The correct understanding here is: the grades are arranged in ascending order and the student numbers are arranged in descending order. Because DESC modifies studentno, and StudentResult is the default.

Paging - limit

• Syntax: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset;

• Benefits: (user experience, network transmission, query pressure).

  /*
  deduction:
     Page 1: limit 0,5 (1-1) * 5
     Page 2: limit 5,5 (2-1) * 5
     Page 3: limit 10,5 (3-1) * 5
     ......
     Page N: limit (n-1)*pageSzie,pageSzie
     [n:Current page number, pageSize: number of items displayed on a single page]
  */
  

-- Requirements: query database structure-1 All test results (student number, student subject name, grade)),achievement
-- Display 5 pieces of data per page
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='database structure -1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5;

-- query JAVA Information of the top 10 students with scores greater than 80 in the first academic year(Student number,full name,Course name,fraction)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE studentresult>80 AND subjectname='JAVA First academic year'
ORDER BY StudentResult DESC
LIMIT 0,10;

6. Sub query

What is a subquery?

• Another query statement is nested in the WHERE condition clause of the query statement;
• Nested query can be composed of multiple sub queries, and the solution method is from the inside to the outside;
• The results returned by subqueries are generally collections, so it is recommended to use the IN keyword;

-- Query database structure-1 All test results(Student number,Account number,achievement),And the grades are in descending order
-- Method 1:Use join 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 subquery(Execution sequence:From inside to outside)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   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
-- Method 1:Use join query
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = 'Advanced mathematics-2' AND StudentResult>=80;

-- Method 2:Use join query+Subquery
-- Student number and name of students with a score of no less than 80
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80;

-- on top SQL on the basis of,Add requirements:The course is advanced mathematics-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = 'Advanced mathematics-2'
);

-- Method 3:Use subquery
-- Step by step writing is simple sql sentence,Then nest them
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'
  )
);

• practice

/*
Title:
   Check the score information (student number, name, score) of the top 5 students in C Language-1
   Use sub query to query the grade name of Guo Jing
*/

-- Requirements: query C language-1 Score information of the top 5 students: student number, name and score
-- Method 1: connection query
SELECT s.studentno,studentname,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo=r.StudentNo
INNER JOIN `subject` AS su
ON r.SubjectNo=su.subjectno
WHERE subjectname='C language-1'
ORDER BY studentresult DESC
LIMIT 0,5;

-- Method 2: join query and sub query
SELECT s.studentno,studentname,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo=r.StudentNo
WHERE subjectno=
(SELECT subjectno FROM `subject` WHERE subjectname='C language-1')
ORDER BY studentresult DESC
LIMIT 0,5;

-- Use sub query: query the grade name of Guo Jing
SELECT gradename FROM grade 
WHERE gradeid=
(SELECT gradeid FROM student WHERE studentname='Guo Jing');

7. Grouping and filtering

 -- Query the average score of different courses,Highest score,Lowest score
 -- premise:Group according to different courses
 
 SELECT subjectname,AVG(studentresult) AS average,MAX(StudentResult) AS Highest score,MIN(StudentResult) AS Lowest score
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING average>80;
 
 /*
 where Write before group by
 If the filter is placed after the group
 To use HAVING
 Because having filters from the previously filtered fields, and where filters directly from the > field in the data table
 */

5.MySQL function

Official website: link

1. Common functions

• Data function

SELECT ABS(-8);	-- Absolute value function
SELECT CEILING(9.4); -- Round up/
SELECT FLOOR(9.4);   -- Round down
SELECT RAND();  -- random number,Returns a 0-1 Random number between
SELECT SIGN(0); -- Symbolic function: Negative return-1,A positive number returns 1,0 Return 0

• String function

SELECT CHAR_LENGTH('Java Persistence will succeed'); -- Returns the number of characters contained in a string
SELECT CONCAT('I','change','program');  -- Merge string,There can be more than one parameter
SELECT INSERT('I'm programming hello world',1,2,'For salted fish');  -- Replace string,Replace a length from a position
SELECT LOWER('subeiLY'); -- a lowercase letter
SELECT UPPER('unremittingly'); -- Capitalize
SELECT LEFT('hello,world',5);   -- Intercept from left
SELECT RIGHT('hello,world',5);  -- Intercept from the right
SELECT REPLACE('Java Persistence will succeed','Salted fish','strive');  -- Replace string
SELECT SUBSTR('Java Persistence will succeed',4,6); -- Intercept string,Start and length
SELECT REVERSE('Java Persistence will succeed'); -- reversal
 
-- Inquire about students surnamed Guo,Changed to Zou
SELECT REPLACE(studentname,'outer wall or surrounding area of a city','Zou') AS New name
FROM student WHERE studentname LIKE 'outer wall or surrounding area of a city%';

• Date and time functions

 SELECT CURRENT_DATE();   -- Get current date
 SELECT CURDATE();   -- Get current date
 SELECT NOW();   -- Get current date and time
 SELECT LOCALTIME();   -- Get current date and time
 SELECT SYSDATE();   -- Get current date and time
 
 -- Get date,Hour, minute and second
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

• System information function

 SELECT VERSION();  -- edition
 SELECT USER();     -- user 

2. Aggregate function

Function name	describe
COUNT()	Returns the total number of records that meet the Select criteria, such as select count(*) [not recommended, inefficient]
SUM()	Returns a numeric field or expression column for statistics, and returns the sum of a column.
AVG()	Statistics are usually made for numeric fields or expression columns, and the average value of a column is returned
MAX()	Statistics can be made for numeric fields, character fields or expression columns to return the maximum value.
MIN()	Statistics can be made for numeric fields, character fields or expression columns to return the smallest value.

• In terms of meaning, both count(1) and count(*) represent queries on all data rows.

  • Count (field) will count the number of times the field appears in the table, ignoring the case that the field is null. That is, records with null fields are not counted.
  • count(*) includes all columns, which is equivalent to the number of rows. In the statistical results, it includes records with null fields;
  • count(1) uses 1 to represent the code line. In the statistical result, it contains records with null fields.

Many people think that the execution efficiency of count(1) will be higher than that of count(*), because count(*) will have full table scanning, and count(1) can query for a field. In fact, count(1) and count(*) scan the whole table and count the number of all records, including those null records. Therefore, their efficiency is almost the same. The count (field) is different from the first two. It counts the number of records whose field is not null.

• Comparison between the two:
  • 1) When the table has no primary key, count(1) is faster than count(*);
  • 2) When there is a primary key, the primary key is used as the calculation condition, and the count (primary key) is the most efficient;
  • 3) If the table has only one field, count(*) is more efficient.

 -- Aggregate function
 -- COUNT:Non empty
 SELECT COUNT(studentname) FROM student; -- count(Specify column
 SELECT COUNT(*) FROM student;  -- count(*)
 SELECT COUNT(1) FROM student;  -- count(1) recommend
 
 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;

3. MD5 encryption at database level

1, What is MD5？

• It mainly enhances the complexity and irreversibility of the algorithm.
• MD5 is irreversible, and 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.

2, Realize data encryption

• Create a new table testmd5:

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 some data:

INSERT INTO testmd5 VALUES(1,'subei','123456'),(2,'wahaha','456789');

• If we want to encrypt pwd data, the syntax is:

update testmd5 set pwd = md5(pwd);

• If the password of a user (such as wahaha) is encrypted separately:

INSERT INTO testmd5 VALUES(3,'admin','123456');

UPDATE testmd5 SET pwd = MD5(pwd) WHERE NAME='admin';

• Insert new data automatically encrypted

INSERT INTO testmd5 VALUES(4,'party',md5('123456'));

• Query the login user information (md5 compare the user's encrypted password for comparison)

SELECT * FROM testmd5 WHERE `name`='subei' AND pwd=MD5('123456');

6. Services

1. What is a transaction?

• Either all succeed or all fail.

• Transaction is to execute a group of SQL statements in the same batch;

• If an SQL statement fails, all SQL statements in the batch will be cancelled;

• MySQL transaction processing only supports InnoDB and BDB data table types;

Transaction principles: ACID principle, atomicity, consistency, isolation and persistence.

Reference blog link: https://blog.csdn.net/dengjili/article/details/82468576

Atomicity

• All operations in the whole transaction are either completed or not completed. It is impossible to stagnate in an intermediate link. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction, as if the transaction had never been executed.

Consistency

• A transaction can encapsulate state changes (unless it is read-only). A transaction must always keep the system in a consistent state, no matter how many concurrent transactions there are at any given time.
• In other words, if multiple transactions are concurrent, the system must also operate as A serial transaction. Its main feature is protection and invariance. Taking the transfer case as an example, assuming that there are five accounts, and the balance of each account is 100 yuan, the total amount of the five accounts is 500 yuan. If multiple transfers occur between the five accounts at the same time, no matter how many are concurrent, for example, 5 yuan is transferred between accounts A and B and 10 yuan is transferred between accounts C and D, If 15 yuan is transferred between B and E, the total amount of the five accounts should still be 500 yuan, which is protective and invariable.

Persistent

• After the transaction is completed, the changes made by the transaction to the database will be permanently saved in the database and will not be rolled back.

Isolated

• Isolate state execution transactions so that they appear to be the only operation performed by the system at a given time. If two transactions run at the same time and perform the same functions, the isolation of transactions will ensure that each transaction is considered to be the only one using the system in the system. This attribute is sometimes called serialization. In order to prevent confusion between transaction operations, requests must be serialized or serialized so that only one request is used for the same data at the same time.

Some problems caused by isolation:

• Dirty read: refers to that one transaction reads uncommitted data from another transaction.
• Non repeatable reading: a row of data in a table is read 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 (phantom reading): refers to reading the data inserted by other transactions in one transaction, resulting in inconsistent total number of reads before and after.

2. Executive services

• be careful:
  1. Automatic submission is the default in MySQL;
  2. Auto commit should be turned off when using transactions.

-- ==========affair==========

-- MySQL By default, the transaction is enabled
SET autocommit = 0;   -- close
SET autocommit = 1;   -- open(default)

-- Manual transaction processing

-- Transaction on
START TRANSACTION;  -- Start a transaction,Mark the starting point of the transaction

INSERT XX
INSERT XX

-- Submit:Persistence
COMMIT;

-- RollBACK :The data returns to the initial state of this transaction
ROLLBACK;

-- End of transaction
SET autocommit =1;  -- Start auto submit

-- Save point
SAVEPOINT Save point name; -- Set a transaction savepoint
ROLLBACK TO SAVEPOINT Save point name; -- Rollback to savepoint
RELEASE SAVEPOINT Save point name; -- Delete savepoint

test

/*
Title:
	A Buy a commodity with a price of 500 yuan online and transfer it through online bank
	A Your bank card balance is 2000, and then pay 500 to merchant B
	Merchant B's bank card balance at the beginning is 10000
	
	Create a database shop and create a table account, and insert 2 pieces of data
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) 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 auto submit
START TRANSACTION;  -- Start a transaction

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

COMMIT; -- Commit transaction, persistent!!!
# rollback; --  RollBACK 
SET autocommit = 1; -- Resume auto commit

7. Index

1. General

MysQL's official definition of index is: index is a data structure that helps MysQL obtain data efficiently.

By extracting the main words of sentences, we can get the essence of index: index is a data structure.

Function of index:

• Improve query speed;
• Ensure the uniqueness of data;
• It can accelerate the connection between tables and realize the reference integrity between tables;
• When using grouping and sorting clauses for data retrieval, the time of grouping and sorting can be significantly reduced;
• Full text search fields for search optimization.

2. Classification of index

• Primary key index

  • Unique identifier. The primary key cannot be repeated. Only one column can be used as the primary key.

• Unique index

  • Avoid duplicate columns. Unique indexes can be repeated, and multiple columns can identify unique indexes.

• General index (Index)

  • By default, it is set by using the key keyword.

• Full text index (FullText)

  • It can only be implemented under a specific engine, MyISAM.
    • In versions before MySQL 5.6, only MyISAM storage engine supports full-text indexing;
    • MySQL 5.6 and later versions, MyISAM and InnoDB storage engines support full-text indexing;
    • Full text indexes can be created only when the data types of fields are char, varchar, text and their series.
    • When testing or using full-text indexing, first check whether your MySQL version, storage engine and data type support full-text indexing.
  • Quickly locate data.

• Syntax:

  #Method 1: when creating a table
    　　CREATE TABLE Table name (
                 Field name 1 data type [Integrity constraints],
                 Field name 2 data type [Integrity constraints],
                 [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                 [Index name] (Field name[(length)] [ASC |DESC])
                 );
  
  #Method 2: CREATE creates an index on an existing table
         CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX Index name
                      ON Table name (Field name[(length)] [ASC |DESC]) ;
  
  #Method 3: ALTER TABLE creates an index on an existing table
         ALTER TABLE Table name ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                              Index name (Field name[(length)] [ASC |DESC]) ;
                             
                             
  #Delete index: DROP INDEX index name ON table name;
  #Delete primary key index: ALTER TABLE table name DROP PRIMARY KEY;
  

-- Use of index
-- 1.Add indexes to fields when creating tables
-- 2.After creation, increase the index

-- Show all index information
SHOW INDEX FROM student;

-- Add full text index
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`studentname`);

-- EXPLAIN : analysis SQL Statement execution performance
EXPLAIN SELECT * FROM student;  -- Non full text index

EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('Zhang');

Error reporting: #1292 – Incorrect datetime value: '0000-00-00 00:00:00' cause and solution:

• reason:

  • The current MySQL mode does not support the case where datetime is 0.

• Solution: just modify "sql_mode" directly! With mysql5 Version 7 is an example. The specific solutions are as follows:

  1. In MySQL configuration file my Ini under [mysqld]:

     sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     

  2. Restart MySQL.

3. Test index

• Create table app_user:

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';

• Batch insert data: 100w

-- Insert millions of data
DROP FUNCTION IF EXISTS mock_data;

DELIMITER $$ -- The flag must be written before the function is written

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`, `email`, `phone`, `gender`, `password`, `age`)
	VALUES(CONCAT('user', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
	SET i = i + 1;
    END WHILE;
    RETURN i;
END;

SELECT mock_data();

Index efficiency test

• No index

SELECT * FROM app_user WHERE NAME = 'User 9999'; -- Viewing time
SELECT * FROM app_user WHERE NAME = 'User 9999';
SELECT * FROM app_user WHERE NAME = 'User 9999';

EXPLAIN SELECT * FROM app_user WHERE NAME = 'User 9999';

• Create index

-- CREATE INDEX Index name ON surface(field)
CREATE INDEX id_app_user_name ON app_user(`name`);

• Test general index

SELECT * FROM app_user WHERE NAME = 'User 9999';

4. Indexing principle

• The more indexes, the better;
• Do not add indexes to frequently changing data;
• It is recommended not to add indexes to tables with small amount of data;
• The index should generally be added to the field of the search criteria.

The data structure of the index, Reference blog

• When creating the above indexes, specify the index type, which can be divided into two categories:
  • hash type index: fast single query and slow range query;
  • btree type index: b + tree. The more layers, the exponential growth of data volume (we use it because innodb supports it by default).
• Different storage engines support different index types:
  • InnoDB supports transactions, row level locking, B-tree, full text and other indexes, but does not support Hash indexes;
  • MyISAM does not support transactions, supports table level locking, supports B-tree, full text and other indexes, and does not support Hash indexes;
  • Memory does not support transactions, supports table level locking, supports B-tree, Hash and other indexes, and does not support full text indexes;
  • NDB supports transactions, row level locking, Hash indexes, but not B-tree, full text and other indexes;
  • Archive does not support transactions, supports table level locking, and does not support B-tree, Hash, full text and other indexes;

8. Permission management and backup

1. User management

Use SQLyog to create users and grant permissions.

SQL command operation

• User table: MySQL user
• Essence: add, delete, modify and query this table.

-- Refresh permissions
FLUSH PRIVILEGES;

-- Add user 
CREATE USER subei IDENTIFIED BY '123456';

CREATE USER user name IDENTIFIED BY [PASSWORD] password(character string)
  - Must have mysql Global of database CREATE USER Permission, or possession INSERT jurisdiction.
  - Only users can be created and cannot be granted permissions.
  - User name, note the quotation marks: for example: 'user_name'@'192.168.1.1'
  - Passwords also need quotation marks, and pure digital passwords also need quotation marks
  - To specify a password in plain text, ignore it PASSWORD key word. To specify the password as PASSWORD()The mixed value returned by the function must contain keywords PASSWORD

-- Rename User  
RENAME USER subei TO subei2;
RENAME USER old_user TO new_user;

-- Set password
SET PASSWORD = PASSWORD('admin');
SET PASSWORD = PASSWORD('password');    -- Set password for current user
SET PASSWORD FOR user name = PASSWORD('password');    -- Sets the password for the specified user

-- delete user 
DROP USER subei2;
DROP USER user name;

-- Assign permissions/Add user
GRANT all privileges ON *.* TO subei2;
GRANT Permission list ON Table name TO user name [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges Indicates all permissions
  - *.* All tables representing all libraries
  - Library name.The table name represents a table under a library  

-- View permissions   
SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR user name
   -- View current user permissions
  SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();

-- revoking permission
REVOKE Permission list ON Table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user name    -- Revoke all permissions

Permission interpretation

-- Permission list
ALL [PRIVILEGES]    -- Set division GRANT OPTION All simple permissions except
ALTER    -- Allow use ALTER TABLE
ALTER ROUTINE    -- Change or cancel stored subroutines
CREATE    -- Allow use CREATE TABLE
CREATE ROUTINE    -- Create stored subroutines
CREATE TEMPORARY TABLES        -- Allow use CREATE TEMPORARY TABLE
CREATE USER        -- Allow use CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES. 
CREATE VIEW        -- Allow use CREATE VIEW
DELETE    -- Allow use DELETE
DROP    -- Allow use DROP TABLE
EXECUTE        -- Allows the user to run stored subroutines
FILE    -- Allow use SELECT...INTO OUTFILE and LOAD DATA INFILE
INDEX     -- Allow use CREATE INDEX and DROP INDEX
INSERT    -- Allow use INSERT
LOCK TABLES        -- Allow you to have SELECT Table usage of permissions LOCK TABLES
PROCESS     -- Allow use SHOW FULL PROCESSLIST
REFERENCES    -- Not implemented
RELOAD    -- Allow use FLUSH
REPLICATION CLIENT    -- Allows the user to ask for the address of the secondary or primary server
REPLICATION SLAVE    -- For replicated secondary servers (reading binary log events from the primary server)
SELECT    -- Allow use SELECT
SHOW DATABASES    -- Show all databases
SHOW VIEW    -- Allow use SHOW CREATE VIEW
SHUTDOWN    -- Allow use mysqladmin shutdown
SUPER    -- Allow use CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL sentence, mysqladmin debug Command; Allows you to connect (once), even if you have reached max_connections. 
UPDATE    -- Allow use UPDATE
USAGE    -- "Synonymous with "no permission"
GRANT OPTION    -- Permission granted


-- Table maintenance

-- Analyze and store the keyword distribution of the table
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE Table name ...
-- Check one or more tables for errors
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- Defragment data files
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

2.MySQL backup

Database backup necessity:

• Ensure that important data is not lost;
• Data transfer;

MySQL database backup method:

• mysqldump backup tool;
• Database management tools, such as SQLyog;

• Directly copy database files and related configuration files;

mysqldump client

effect:

• Dump database;
• Collect database for backup;
• Transfer data to another SQL server, not necessarily MySQL server.

-- export
1. Export a table 
-- mysqldump -uroot -proot school student >D:/a.sql
　　mysqldump -h host -u user name -p Password library name table name > file name(D:/a.sql)
2. Export multiple tables 
-- mysqldump -uroot -proot school student result >D:/b.sql
　　mysqldump -h host -u user name -p Password library name table 1 Table 2 Table 3 > file name(D:/b.sql)
3. Export all tables 
-- mysqldump -uroot -proot school >D:/c.sql
　　mysqldump -h host -u user name -p Password library name > file name(D:/c.sql)
4. Export a library 
-- mysqldump -uroot -proot -B school >D:/d.sql
　　mysqldump -h host -u user name -p password -B Library name > file name(D:/d.sql)

sure-w Carry backup conditions

-- Import
1. Logging in mysql In case of:
-- source D:/a.sql
　　source Backup file
2. Without logging in:
　　mysql -u user name -p Password library name < Backup file

9. Standardized database design

1. Why database design

When the database is complex, we need to design the database.

Poor database design:

• Data redundancy and waste of storage space;
• Exceptions in data update and insertion [shielding the use of physical foreign keys];
• Poor program performance;

Good database design:

• Save data storage space;
• Ensure data integrity;
• Facilitate the development of database application system;

Database design in software project development cycle:

• Demand analysis stage: analyze customers' business and data processing requirements;
• Outline design stage: design the E-R model diagram of the database to confirm the correctness and completeness of the demand information.

To design a database:

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

• Collect information
• Communicate and discuss with relevant personnel of the system to fully understand the needs of users and the tasks to be completed by the database
• Identify Entity [Entity]
  • Identify the key objects or entities to be managed in the database. Entities are generally nouns;
  • Identify the detailed information that each entity needs to store [Attribute];
  • Identify the Relationship between entities.

2. Three paradigms

Question: why do you need data normalization?

Problems caused by non-conforming table design:

• Duplicate information

• Update exception

• Insert exception

  • Information cannot be represented correctly

• Delete exception

  • Missing valid information

Three paradigms

First normal form (1NF)

• The goal of the first normal form is to ensure the atomicity of each column. If each column is the smallest non separable data unit, the first normal form is satisfied.

Second paradigm (2NF)

• The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to meet the second normal form (2NF) must first meet the first normal form (1NF).

• The second paradigm requires each table to describe only one thing.

Third paradigm (3NF)

• If a relationship satisfies the second normal form, and all columns except the primary key do not depend on the primary key column, the third normal form is satisfied.

• The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly.

Relationship between normalization and performance

• In order to meet some business goals, database performance is more important than standardized database;
• At the same time of data standardization, we should comprehensively consider the performance of database;
• By adding additional fields to a given table, the time required to search for information is greatly reduced;
• By inserting calculated columns into a given table, it is convenient to query.

10.JDBC

1. Database driver

The concept of the driver here is the same as that heard at ordinary times. For example, for the sound card purchased at ordinary times, the network card cannot be inserted directly into the computer. The sound card and network card can only be used after the corresponding driver is installed. Similarly, after we install the database, our application can not directly use the database, You must deal with the database through the corresponding database driver, as shown below:

2.JDBC introduction

In order to simplify and unify the operation of the database, SUN company defines a set of Java database operation specifications (interfaces), called JDBC. This set of interfaces is implemented by the database manufacturer. In this way, developers only need to learn the JDBC interface and load specific drivers through JDBC to operate the database.

• As shown in the figure below:

• The full name of JDBC is Java Data Base Connectivity, which is mainly composed of interfaces.
• Two packages that make up JDBC: Java sql,javax.sql
• In addition to the support of the above two packages, it is also necessary to import the corresponding JDBC database implementation (i.e. database driver package - mysql-connector-java-5.1.47.jar).

3. The first JDBC program

Build an experimental environment

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','1979-12-04');

Create a new Java project and import the data driver.

jar package download address: maven warehouse.

The program reads the data from the user table and prints it in the command line window.

package com.github.lesson01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * First JDBC program
 */
public class JdbcFirstDemo {
    public static void main(String[] args) throws Exception{
        // 1. Load drive
        Class.forName("com.mysql.jdbc.Driver"); // Fixed writing, load driven

        // 2. User information and url
        // uesUnicode=true supports Chinese encoding
        // characterEncoding=utf8 set character set
        // useSSL=true use a secure connection
        String url = "jdbc:mysql://localhost:3306/jdbcStudy?uesUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";

        // 3. The connection is successful. The database object connection represents the database
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4. Execute SQL object
        Statement statement = connection.createStatement();

        // 5. Execute SQL object, execute SQL
        String sql = "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);  // Returns the result set, which encapsulates all the queried objects

        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("both=" + resultSet.getObject("birthday"));
        }

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

    }
}

Step summary:

1. Load drive;
2. Connect to the database DriverManager;
3. Obtain the object Statement executing SQL;
4. Obtain the returned result set;
5. Release the connection.

4. Object description

DriverManager

• The DriverManager in the JDBC program is used to load the driver and create a link with the database. The common methods of this API are as follows:

// DriverManager.registerDriver(new Driver())
// DriverManager.getConnection(url, user, password)

Class.forName("com.mysql.jdbc.Driver"); // Fixed writing, load driven 
Connection connection = DriverManager.getConnection(url, username, password);

// connection represents the database
connection.createStatement();// Create a statement object that sends sql to the database
connection.prepareStatement(sql);// Create a PrepareSatement object that sends precompiled sql to the database
connection.rollback();  // Transaction rollback
connection.commit(); // Transaction commit
connection.setAutoCommit(); // Set up automatic database submission

Note: the registerDriver method is not recommended to register drivers in actual development. The reasons are as follows:

1. Looking at the Driver source code, you can see that if this method is adopted, the Driver will be registered twice, that is, there will be two Driver objects in memory.
2. The program depends on the mysql api. If it is separated from the mysql jar package, the program cannot be compiled. In the future, it will be very troublesome for the program to switch to the underlying database.

Recommended method: class forName("com.mysql.jdbc.Driver");

In this way, the driver object will not appear repeatedly in memory, and in this way, the program only needs a string and does not need to rely on the specific driver, which makes the program more flexible.

URL description

• The URL is used to identify the location of the database and tell the JDBC program which database to connect through the URL address. The URL address is written as follows:

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

// mysql port number 3306
// jdbc:mysql://localhost:3306/ Database name? Parameter1 & parameter2 & parameter3

// oracle port number 1521
// jdbc:oracle:thin:@localhost:1521:sid

• Common database URL address:
  • Oracle writing method: jdbc:oracle:thin:@localhost:1521:sid
  • SqlServer writing method: jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid
  • MySql writing method: jdbc:mysql://localhost:3306/sid
• If the local Mysql database is connected and the port used for the connection is 3306, the url address can be abbreviated as
  • jdbc:mysql: / / / database

Statement object to execute SQL

String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);  // Returns the result set, which encapsulates all the queried objects

// Common methods for Statement objects
statement.executeQuery(sql); // Query operation, return result set
statement.execute(sql); // Execute any SQL
statement.executeUpdate(sql); // Update, insert and delete are all this. The only affected is the number of rows
statement.addBatch(sql); // Put multiple sql statements into one batch
statement.executeBatch(sql); // Send a batch of sql statements to the database for execution

ResultSet query result set: encapsulates all query results.

• Gets the specified data type

resultSet.getString(); // Used when the data type is unknown
// If you know the type of the column, use the specified type
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDouble();
resultSet.getDate();
resultSet.getObject();
....

• ResultSet also provides a method to scroll the result set:

resultSet.next(); // Move to next line
resultSet.previous(); // Move to previous line
resultSet.absolute(int row); // Move to specified row
resultSet.beforeFirst(); // Move the top of the resultSet.
resultSet.afterLast(); // Move to the back of the resultSet.

Release resources

// 6. Release the connection
resultSet.close();
statement.close();
connection.close(); // Turn it off after use

5.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 addition, deletion and modification statements cause 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 succeeded!!!");
}

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.executeUpdate(sql);
while(rs.next()){
	//According to the data type of the obtained column, the corresponding methods of rs are called to map to the java object
}

Case: add, delete, modify and query the database using jdbc

1. Create a package for lesson02;
2. Create a DB in the src directory Properties file, write the following:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

1. Create a new Utils package under lesson02 and a new Utils class:

package com.github.lesson02.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class Utils {

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

    static{
        try{
            // Read dB Database connection information in the properties file
            InputStream in = Utils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            // Get database connection driver
            driver = properties.getProperty("driver");
            // Get database connection URL address
            url = properties.getProperty("url");
            // Get database connection user name
            username = properties.getProperty("username");
            // Get database connection password
            password = properties.getProperty("password");

            // Load the database driver only once!
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

    // Release resources
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

1. Add, delete, modify and query data

package com.github.lesson02;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * insert data
 */
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            // Get a database connection
            conn = Utils.getConnection();

            // Obtain the Statement object responsible for executing the SQL command through the conn object
            st = conn.createStatement();

            // SQL to execute
            String sql = "";

            // Perform operation
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("Insert data successfully!!!");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // close resource
            Utils.release(conn,st,rs);
        }
    }
}

package com.github.lesson02;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Delete data
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            String sql = "delete from users where id=4";
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("Delete data successfully!!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

package com.github.lesson02;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Modify data
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            String sql = "update users set name='Wang Wei',email='wangwei@163.com' where id=3";
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("Update data successfully!!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

package com.github.lesson02;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Query data
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
//            String sql = "select * from users";
            String sql = "select * from users where id=2";
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println("Query data successfully!!!");
//                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

SQL injection

• Through ingenious skills to splice strings, resulting in SQL short circuit, so as to obtain database data

• SQL has a vulnerability and will be attacked.

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQL injection {
    public static void main(String[] args) {
        login("lisi","123456");    // Normal login = = result = = > result output
//        login("'or '1=1","123456");  // SQL injection = = result = = > output database content
    }

    // Login service
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            // select * from users where name=''or '1-1' and password='123456'
            String sql = "select * from users where name='"+username
                    +"' and password='"+password+"'";
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println("Query data successfully!!!");
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("==========");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

6.PreparedStatement object

PreperedStatement is a subclass of Statement, and its instance object can be accessed by calling connection Preparedstatement () method. Compared with the Statement object, Preparedstatement can avoid the problem of SQL injection.

Statement will cause the database to compile SQL frequently, which may cause database buffer overflow.

PreparedStatement can precompile sql to improve the execution efficiency of the database. Moreover, PreperedStatement allows the parameters in sql to be replaced in the form of placeholders, which simplifies the writing of sql statements.

Use the PreparedStatement object to complete the CRUD operation on the database

• insert data

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            // Assign a value to the SQL parameter, and the index starts from 1
            st.setInt(1,5); // id is of type int
            st.setString(2,"subei"); // name is a string type
            st.setString(3,"123456"); // password is a string type
            st.setString(4,"24635862@qq.com"); // email is a string type
            st.setDate(5,new java.sql.Date(System.currentTimeMillis())); // birthday is of type date

            /*
            * Here's a small problem:
            *   When using new date() When gettime(), an error will be reported: please use system Currenttimemillis() replaces new date() getTime()
            * For this question, baidu:
            *   new Date()All you do is call system currentTimeMillis(). 
            *   If you only need or milliseconds, you can use system Currenttimemillis() instead of new Date(),
            *   A little more efficient. Moreover, many people like to use new Date() multiple times in the same method,
            *   Usually, the performance is consumed bit by bit. In fact, a reference can be declared here.
             * */

            // Perform insert data operation
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("Insert data successfully!!!");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL free resources
            Utils.release(conn,st,rs);
        }

    }
}

• Delete data

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Delete data
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "delete * from users where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1,4);
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("Delete data successfully!!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

• Update data

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Modify data
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "update users set name=?,email=? where id=?";
            st = conn.prepareStatement(sql);
            st.setString(1,"Ye Fan");
            st.setString(2,"632579682@163.com");
            st.setInt(3,1);
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("Update data successfully!!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

• Query data

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Query data
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "select * from users where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1,1);
            rs = st.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println("Query data successfully!!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

Avoid SQL injection

package com.github.lesson03;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class SQL injection {
    public static void main(String[] args) {
//        login("lisi","123456");    //  Normal login = = result = = > result output
        login("'or '1=1","123456");  // SQL injection = = result = = > no result output
    }

    // Login service
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "select * from users where name=? and password=?";
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            rs = st.executeQuery();
            while(rs.next()){
                System.out.println("Query data successfully!!!");
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("==========");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

• Principle: during execution, the parameters will be enclosed in quotation marks, and the quotation marks in the parameters will be used as escape characters, so as to avoid the parameters being part of the conditions.

7. Use IDEA to connect to the database

• Open idea2020 2 is shown below:

• Open the following interface to start relevant settings:

• After the connection is successful, enter the following operation:

• Then open the following figure:

• Be sure to click the green arrow, otherwise the update fails and the data is not saved! The update is successful, as shown in the figure below:

• Write the database, and then click the green button in the upper left corner of the figure below to execute, as shown in the figure below:

-- case
-- Create account table
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40),
    money FLOAT
);
-- Insert test data
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);

• If idea2020 2. Failed to connect to MySQL database. Please refer to the following figure for modification:

8.JDBC operation transaction

• Transaction: a logical group of operations. The units that make up this group of operations are either all successful or all unsuccessful.

• ACID principle:

  • Atomicity: either complete or not complete.
  • Consistency: the total number remains unchanged.
  • Isolated: multiple processes do not interfere with each other.
  • Durable: once the commit is irreversible, it is persisted to the database.

• Some problems caused by isolation:

  • Dirty read: one transaction reads uncommitted data from another transaction.
  • Non repeatable reading: a row of data in a table is read 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 (phantom reading): refers to reading the data inserted by other transactions in one transaction, resulting in inconsistent total number of reads before and after.

• When the JDBC program obtains a Connection object from the database, by default, the Connection object will automatically submit the SQL statements sent on it to the database. If you want to turn off this default commit method and let multiple SQL execute in one transaction, you can use the following JDBC control transaction statements.

Connection.setAutoCommit(false);//Start transaction
Connection.rollback();//Rollback transaction
Connection.commit();//Commit transaction

• Simulate the business when the transfer is successful

package com.github.lesson04;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Simulate the business scenario when the transfer is successful
 */
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            // Turn off the automatic submission of the database and start the transaction automatically
            conn = Utils.getConnection();
            // Open transaction
            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("Transfer succeeded!!!");
        } catch (Exception e) {
            try {
                conn.rollback(); // If it fails, the transaction is rolled back
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }

    }
}

• When an exception occurs during the simulated transfer process, which leads to the failure of some SQL execution, let the database automatically roll back the transaction

package com.github.lesson04;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Simulate the business scenario when the transfer fails
 */
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            // Turn off the automatic submission of the database and start the transaction automatically
            conn = Utils.getConnection();
            // Open transaction
            conn.setAutoCommit(false);

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

            int x=1/0;  // report errors

            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("Transfer succeeded!!!");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }

    }
}

• When an exception occurs in the process of simulating transfer, resulting in partial SQL execution failure, manually notify the database to roll back the transaction

package com.github.lesson04;

import com.github.lesson02.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Simulate the business scenario when the transfer fails
 */
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            // Turn off the automatic submission of the database and start the transaction automatically
            conn = Utils.getConnection();
            // Open transaction
            conn.setAutoCommit(false);

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

            int x=1/0;  // report errors

            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("Transfer succeeded!!!");
        } catch (Exception e) {
            try {
                conn.rollback(); // If it fails, the transaction is rolled back
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }

    }
}

8. Database connection pool

Users need to obtain a link from the database every request, and creating a connection to the database usually consumes relatively large resources and takes a long time. Assuming that the website has 100000 visits a day, the database server needs to create 100000 connections, which greatly wastes the resources of the database, and it is very easy to cause the memory overflow and expansion of the database server.

Basic concepts of database connection pool

Database connection is a key limited and expensive resource, which is particularly prominent in multi-user web applications. The management of database connection can significantly affect the scalability and robustness of the whole application and the performance index of the program. Database connection pool is formally proposed to solve this problem. Database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re establishing one.

During initialization, the database connection pool will create a certain number of database connections into the connection pool. The number of these database connections is set by the minimum number of database connections. No matter whether these database connections are used or not, the connection pool will always ensure that there are at least so many connections. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy. When the number of connections requested by the application from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

The following factors should be considered when setting the minimum and maximum connections of the database connection pool:

1. Minimum connections: the database connections maintained by the connection pool. Therefore, if the application does not use database connections, a large number of database connection resources will be wasted.
2. Maximum connections: refers to the maximum connections that can be applied for by the connection pool. If the number of database connection requests exceeds, subsequent database connection requests will be added to the waiting queue, which will affect future database operations.
3. If there is a big difference between the minimum number of connections and the maximum number of connections: the first connection request will benefit, and then the connection request exceeding the minimum number of connections is equivalent to establishing a new database connection. However, these database connections larger than the minimum number of connections will not be released immediately after use. They will be placed in the connection pool for reuse or released after space timeout.

To write a connection pool, you need to implement Java sql. Datasource interface.

Open source data source implementation

Now, many WEB servers (Weblogic, WebSphere, Tomcat) provide the implementation of datasoluce, that is, the implementation of connection pool. Usually, we call the implementation of DataSource as data source according to its English meaning. The data source includes the implementation of database connection pool.

Some open source organizations also provide independent implementations of data sources:

• DBCP database connection pool
• C3P0 database connection pool
• Druid database connection pool - Alibaba

After using the database connection pool, there is no need to write the code to connect to the database in the actual development of the project. The database connection is obtained directly from the data source.

DBCP

• To use the DBCP data source, you need to import the following two jar files:

  • Commons-dbcp-1.4.jar
  • Commons-pool-1.6.jar

• Add the dbcp configuration file in the src Directory: dbcpconfig properties

#connections setting up
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

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

#Maximum number of connections
maxActive=50

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

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

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

#The format of the connection property attached when the JDBC driver establishes a connection must be: [property name = property;]
#Note: the "user" and "password" attributes will be explicitly passed, so there is no need to include them here.
connectionProperties=useUnicode=true;characterEncoding=UTF8

#Specifies the auto commit status of connections created by the connection pool.
defaultAutoCommit=true

#driver default specifies the read-only status of connections created by the connection pool.
#If this value is not set, the "setReadOnly" method will not be called. (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: (see javadoc for details.) NONE,READ_UNCOMMITTED, READ_COMMITTED,REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

• Write tool class Utils_DBCP

package com.github.lesson05.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * Database connection tool class
 */
public class Utils_DBCP {
    private static DataSource ds = null;

    static{
        try {
            InputStream in = Utils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties prpo = new Properties();
            prpo.load(in);
            // create data source
            ds = BasicDataSourceFactory.createDataSource(prpo);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * Get database connection
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * Release resources
     * @param conn
     * @param st
     * @param rs
     */
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

• test

package com.github.lesson05;

import com.github.lesson05.utils.Utils_DBCP;
import java.sql.*;

/**
 * Database connection tool class test
 */
public class TestDbcp {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils_DBCP.getConnection();
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            st.setInt(1,6);
            st.setString(2,"apple");
            st.setString(3,"232323");
            st.setString(4,"327338203@qq.com");
            st.setDate(5,new java.sql.Date(System.currentTimeMillis()));
            // Perform insert data operation
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("Insert data successfully!!!");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL free resources
            Utils_DBCP.release(conn,st,rs);
        }
    }
}

C3P0

• C3P0 is an open source JDBC connection pool. It implements data source and JNDI binding, and supports JDBC 3 specification and JDBC 2 standard extension. At present, its open source projects include Hibernate, Spring and so on.

• Differences between c3p0 and dbcp:

  • dbcp does not automatically recycle idle connections;
  • c3p0 has the function of automatically reclaiming idle connections.

• To use C3P0 data source, you need to import the following two jar files:

  • c3p0-0.9.5.5.jar
  • mchange-commons-java-0.2.19.jar

• Add C3P0 configuration file in src Directory: C3P0 config xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    C3P0 Default of(default)to configure,
    If in code“ ComboPooledDataSource ds = new ComboPooledDataSource();"Write like this
    It means that C3P0 Default of(default)Configure information to create a data source
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy? useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    <!--
    C3P0 Named configuration for,
    If in code“ ComboPooledDataSource ds = new
    ComboPooledDataSource("MySQL");"This means that name yes MySQL Configuration of
    Information to create a data source
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

• Write tool class Utils_C3P0.java

package com.github.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * C3P0 Tool class
 */
public class Utils_C3P0 {
    private static ComboPooledDataSource ds = null;

    static{
        try {
            // Use the default configuration of C3P0 to create a data source
            ds = new ComboPooledDataSource();
            // Use the naming configuration of C3P0 to create a data source
//            ds = new ComboPooledDataSource("MySQL");
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * Get database connection
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * Release resources
     * @param conn
     * @param st
     * @param rs
     */
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if(rs != null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

• Test class

package com.github.lesson05;

import com.github.lesson05.utils.Utils_C3P0;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * C3P0 Test class
 */
public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils_C3P0.getConnection();
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            st.setInt(1,7);
            st.setString(2,"pink");
            st.setString(3,"263223");
            st.setString(4,"3276128203@qq.com");
            st.setDate(5,new java.sql.Date(System.currentTimeMillis()));
            // Perform insert data operation
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("Insert data successfully!!!");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL free resources
            Utils_C3P0.release(conn,st,rs);
        }
    }
}

• No matter what data source is used, the essence is the same!!!

MySQL quick start complete 🎉🎉🎉🎉