0. General

1. MySQL basic notes, review and use
2. Environment construction: detailed installation steps of MySQL database

name	explain	command
DDL (data definition language)	Define and manage data objects, such as databases, data tables, etc	create,drop,alter
DML (data operation language)	Used to manipulate data contained in database objects	insert,update,delete
DQL (data query language)	Used to query database data	select
DCL (data control language)	The language used to manage the database, including administrative permissions and data changes	grant,commit,rollback

1. Operation database

• Operate database > operate tables in Database > operate data of tables in database
• mysql keyword is not case sensitive

1.1 operation database

• Create database: create database [if not exists] database name;

• Delete database: drop database [if exists] database name;

• use database: use Database

-- Use database `school`
use `school`;

• View databases: show databases

When using SQLyog to create a database, you need to select

• Base character set: utf8
• Database collation: utf8_unicode_ci

1.2 column type of database

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

value type

type	explain	Storage requirements
tinyint	Very small data	1 byte
smallint	Smaller data	2 bytes
mediumint	Medium size data	3 bytes
int	Standard integer	4 bytes
bigint	Larger integer	8 bytes
float	Single-precision floating-point	4 bytes
double	Double precision floating point number	8 bytes
decimal	Floating point number in string form	In financial calculation, decimal is generally used

character string

type	explain	Maximum length
char[(M)]	Fixed long string, fast but time-consuming retrieval	0 ~ 255 bytes
varchar[(M)]	Variable string	0 ~ 65535 bytes
tinytext	Micro text string	2 ^ 8 - 1 byte
text	Text string	2 ^ 16-1 bytes

Time and date

java.util.Date

type	explain
date	YYYY-MM-DD, date format
time	HH:mm:ss, time format
datetime	Yyyy MM DD HH: mm: SS, the most commonly used time format
timestamp	The number of milliseconds from yymss.1 to yymss.1
year	Year value in YYYY format

null

• No value, unknown
• Note that do not use NULL for the operation, because the result is NULL

1.3 field attributes of database (key points)

Unsigned:

• Unsigned integer
• The data column declared is not allowed to be declared as a negative number

zerofill:

• 0 filled
• If the number of digits is insufficient, use 0 to fill in [for example, int(3), 5 is 005]

Auto_InCrement:

• For automatic growth, every time a piece of data is added, it will be automatically + 1 on the basis of the previous record (default)
• It is usually used to design a unique primary key and must be of integer type
• You can customize the starting value and step size of the self increment of the design primary key (generally not used)

NULL and NOT NULL:

• The default value is NULL, that is, the value of this column is not inserted

• If set to NOT NULL, the column must have a value

default:

• Set the default value!
• sex, the default value is male. If the value of this column is not specified, there will be a value with the default value of male

extend

/* Each table must have the following five fields, which will be used in future projects to indicate the significance of a record!

id  Primary key
`version`	Optimistic lock
is_delete   Pseudo deletion
gmt_create  Creation time
gmt_update  Modification time
*/

1.4 creating database tables (key)

• Create a school database
• Create student table
  • Student id int
  • Name varchar
  • Password varchar
  • Gender varchar
  • Date of birth datetime
  • Home address varchar
  • Mailbox varchar

-- Create database school
CREATE DATABASE IF NOT EXISTS `school`;
-- Delete database student
DROP DATABASE IF  EXISTS `student`;
-- Attention:Table names and fields should be used as much as possible `` Enclose
-- Create student table
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',  		-- id Not null, auto increment
  `name` VARCHAR(30) NOT NULL DEFAULT 'Xiao Lin' COMMENT 'full name',	-- name It is not empty. It defaults to Lin Xiao
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'password',	-- pwd It is not empty. The default value is 123456
  `sex` VARCHAR(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',	-- sex Not null, default to male
  `birthday` DATETIME DEFAULT NULL COMMENT 'date of birth',		-- birthdat The default is empty
  `address` VARCHAR(100) DEFAULT NULL COMMENT 'Home address',	-- address The default is empty
  `email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',		-- email The default is empty
    
  PRIMARY KEY (`id`)  									 -- Primary key. Generally, a table has only one unique primary key
)ENGINE=INNODB DEFAULT CHARSET=utf8;

format

CREATE TABLE [IF NOT EXISTS] `Table name`(
	`Field name` Column type [attribute] [Indexes] [notes],
    `Field name` Column type [attribute] [Indexes] [notes],
    .......
    `Field name` Column type [attribute] [Indexes] [notes]
)[Table type][Character set settings for table][notes]

Common commands

SHOW CREATE DATABASE school  -- View the statement that created the database
SHOW CREATE TABLE student    -- see student Definition statement of data table
DESC student                 -- Displays the structure of the table

1.5 type of data sheet

There are two common types:

• INNODB is used by default
• MYISAM was used in earlier years

	MYISAM	INNODB
Transaction support	I won't support it	support
Data row locking	I won't support it	support
Foreign key constraint	I won't support it	support
Full text index	support	I won't support it
Table space size	less	Larger, about 2 times

General operation:

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

Storage location of data table

MySQL data tables are stored on disk as files

• Location: Mysql installation directory \ data directory

1.6 modification and deletion table

Modify table (ALTER TABLE)

• Modify table name: alter table old table name rename as new table name

-- 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 attribute [attribute]

-- Add table fields alter table Table name ADD Field column properties
ALTER TABLE teacher1 ADD age INT(11)

• Modify field:
  • alter table table name modify field column attribute [attribute]
  • alter table table name change old field column attribute [attribute]

-- Modify the fields of the table (1.Rename 2.Modify constraints)
-- ALTER TABLE Table name MODIFY Field column properties[]
ALTER TABLE teacher1 MODIFY age VARCAHAR(11)  --  Modify constraints
-- ALTER TABLE Table name CHANGE Old name new name column properties[]
ALTER TABLE teacher1 CHANGE age age1 INT(1)  -- Rename field

• Delete field: alter table name drop field name

-- Delete table fields ALTER TABLE Table name drop Field name
alter table teacher1 drop age1

Final conclusion

• change is used to rename fields. Field types and constraints cannot be modified
• Modify does not need to rename fields, but can only modify field types and constraints

Delete table

• Delete table: DROP TABLE [IF EXISTS] table name

-- Delete table
DROP TABLE [IF EXISTS] teacher1

2.MySQL data management

2.1 foreign keys (understand)

• Create foreign key

Method 1: create a sub table and create a foreign key at the same time

• The gradeid field of the student table refers to the gradeid field of the grade table
  • 1. Define foreign key
  • 1. Add constraints (execute references) to this foreign key

-- Grade table
create table `grade`(
    `gradeid` int(10) not null auto_increment comment 'Age id',
    `gradename` varchar(50) noy null comment 'Grade name',
    primary key(`gradeid`)
    
)engine=innodb default charset = utf8;

-- Student list
-- Student table gradeid Field to reference the grade table gradeid
-- 1. Define foreign keys key
-- 2. Add constraints to this foreign key(Execute reference)
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 'female' COMMENT 'Gender',
    `birthday` DATETIME DEFAULT NULL COMMENT 'date of birth',
    `address` varchar(100) DEFAULT NULL COMMENT 'Home address',
    `gradeid` int(10) noy null comment 'Student's grade',
    `email` varchar(50) DEFAULT NULL COMMENT 'mailbox',
     PRIMARY KEY(`id`), 

     -- 1. Define foreign keys FK_gradeid
     KEY `FK_gradeid`(`gradeid`),
     -- 2. Add constraints to this foreign key
     CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) references `grade`(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

Method 2: after the table is created successfully, add a foreign key constraint

-- There is no foreign key relationship when creating a table
alter table `studnet` add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`),

The above operations are physical foreign keys, database level foreign keys, which are not recommended!

Best practices

• A database is a simple table. It only stores data, only rows (data) and columns (fields)
• We want to use the data of multiple tables, and we want to use foreign keys

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

2.2 DML language (remember)

Meaning of database: data storage, data management

DML language: data operation language

• Insert
• update
• delete

2.3 adding

insert

Syntax:

-- insert data(add to)
insert into Table name([Field name 1,Field 2,Field 3]) values('Value 1'),('Value 2'),('Value 3');

be careful:

• Fields or values are separated by English commas
• Multiple pieces of data can be inserted at the same time, separated by English commas after values
• Since the primary key increases automatically, we can omit it (if we don't write the fields of the table, it will match one by one)
• Generally, when writing insert statements, we must correspond the data and fields one by one!
• Fields can be omitted, but the following values must correspond one by one

-- towards`grade`In table`gradename`Field insertion
insert into `grade`(`gradename`) values('Senior');

-- Insert multiple fields
insert into `grade`(`gradename`) 
values('Sophomore'),('Freshman');

-- Data and fields correspond one by one!
insert into `student`(`name`,`pwd`,`sex`) values('Zhang San','aaaa','male');

2.4 modification

update

Syntax:

update Table name set colnum_name = value,[.....] where [condition]

matters needing attention:

1. colnum_name is the column of the database. Try to bring it with you``
2. Condition, filter condition. If not specified, all columns will be modified
3. Value is a specific value or a variable
4. The attributes of multiple settings are separated by English commas

update `student` set `name`='Mad God' where id = 1;

-- All tables will be changed without specifying conditions!
update `student` set `name`='Changjiang 7'; 

-- Modify multiple attributes
update `student` set `name`='Mad God',`email`='12123@qq.com' where id between 2 and 5;

-- Locate data through multiple conditions 
update `student` set `name`='Changjiang 7' where `name`='Mad God' and `sex`='male';

Operator	meaning	Range	result
=	be equal to	5=6	false
< > or=	Not equal to	5<>6	true
>	greater than
<	less than
<=	Less than or equal to
>=	Greater than or equal to
between...and...	Within a certain range	[2,5]
and	&&	5>1 and 1>2	false
or	||	5>1or 1>2	true

2.5 deletion

delete command

Syntax: delete from table name [where condition]

-- Delete data,If no condition is specified, all column data of the table will be deleted
delete from `student`;

-- Delete specified data
delete from `student` where id = 1;

truncate command

Function: completely empty a database table, and the table structure and index constraints will not change

-- empty student surface
truncate `student`; 

The difference between delete and truncate

• Same point: data can be deleted without deleting table structure, but truncate is faster
• difference:
  • truncate resets the auto increment column and the counter will return to zero
  • truncate does not affect transactions

3. DQL query data (most important)

3.1DQL

Date query language: Data Query Language

• All query operations use it, select
• Simple and complex queries can be done
• The core language and the most important statement in the database

3.2Select statement

Basic syntax: select field from table

-- Query all students
select * from student;

-- Query specified fields
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
• You can also give the query results a new alias

-- Alias, give the result a name  AS You can alias fields or tables(AS Keywords can be omitted)
select `StudentNo` As Student number,`StudentName` AS Student name from student AS S

-- Function concatenation string Concat(a,b)
select concat('full name',StudentName) AS New name from student

Syntax: SELECT field 1, field 2 From table

3.3 weight removal distinct

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

-- Find out which students took the exam and got good grades
select * from  result;   -- Query all test scores
select `StudentNo` from result;   -- Check which students took the exam
select distinct `StudentNo` from result;   -- Duplicate data found, de duplication

Database columns (expressions)

• select expression from table
• Expressions in database: text value, column, Null, function, calculation expression, system variable

select version() -- Query system version(function)
select 100*3-1 AS Calculation results   -- Used to calculate(expression)
select @@auto_increment_increment -- Query self increasing step size(variable)

-- Student examination results + 1  see
select `StudentNo`,`StudentResult` + 1 AS 'After raising points' from result;

3.4 where conditional clause

Function: retrieve qualified values in data

The search condition consists of one or more expressions, and the result is Boolean!

Logical operator

operator	grammar	describe
and &&	a and b a&&b	Logic and, both are true, and the result is true
or ||	a or b a || b	Logical or, one of which is true, the result is true
Not !	not a !a	Logical non, true is false, false is true

Try to use English letters

-- The query test score is 95 ~ 100 Between points
select studentNo,`StudentResult` 
from result 
where StudentResult>=95 and StudentResult<=100

3.5 fuzzy query

Fuzzy queries: comparison operators

operator	grammar	describe
is null	a is null	If the operator is null, the result is true
is not null	a is not null	If the operator is not null, the result is true
between	a between and c	If a is between b and c, the result is true
like	a like b	SQL matches. If a matches b, the result is true
in	a in(a1,a2,a3...)	If a is in a1 or a2..., the result is true

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

-- Inquire about student 100110021003
select `Sno`,`Sname` 
from `student`
where `Sno` in (1001,1002,1003);


-- Query students in Beijing
select `Sno`,`Sname` 
from `student`
where `Address` in ('Anhui','Luoyang, Henan');



-- 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 are only two words after it
SELECT studentno,studentname FROM student
WHERE studentname LIKE 'Liu__';

-- Query names containing Jiazi
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%Jia%';

-- 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 the students whose birth date is filled in
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;

• Note:%_ Can only be used in like
• in is the exact set

3.6 connection query

• inner join: query the intersection of result sets in two tables
• left join: Based on the left table, the right table will match one by one. If it fails to match, the records of the left table will be returned, and the right table will be filled with NULL
• right join: Based on the right table, 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

The construction of the database is as follows:

• Create a school database
• Create student table student
• Create grade table
• Create chart of accounts subject
• Create grade sheet result

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

-- Create student table
CREATE TABLE IF NOT EXISTS `student`(
    `studentno` INT(4) NOT NULL COMMENT 'Student number',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT 'Student name',
    `sex` TINYINT(1) DEFAULT NULL COMMENT 'Gender, 0 or 1',
    `gradeid` INT(11) DEFAULT NULL COMMENT 'Grade number',
    `phone` VARCHAR(50) NOT NULL COMMENT 'Contact number, can be blank',
    `address` VARCHAR(255) NOT NULL COMMENT 'Address, null allowed',
    `borndate` DATETIME DEFAULT NULL COMMENT 'time of birth',
    `email` VARCHAR (50) NOT NULL COMMENT 'Email account can be empty',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- Create grade table
CREATE TABLE IF NOT EXISTS `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
CREATE TABLE IF NOT EXISTS `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
CREATE TABLE IF NOT EXISTS `result`(
	`studentno` INT(4) NOT NULL COMMENT 'Student number',
    `subjectno` INT(4) NOT NULL COMMENT 'Course number',
    `examdate` DATETIME NOT NULL COMMENT 'Examination date',
    `studentresult` INT (4) NOT NULL COMMENT 'Examination results',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

JOIN comparison

inner join

• Analyze which tables the fields of the query come from (join query is used if they come from two tables)
• Determine the intersection (which data is the same in the two tables)
• Judgment condition: studentNo in student table = studentNo in grade table

Query the student number, name, subject number and score of the students who took the exam

1. Student number, name, studentNo,studentName are in the student table
2. Account number, score, SubjectNo,StudentResult are in the result table
3. The studentNo in the two tables is the same

select t1.studentNo,studentName,SubjectNo,StudentResult
from student as t1
inner join result as t2
-- Both in student Look inside and outside,student Table connection result,
-- Connect the two watches,All have studentNo,So point to a table studentNo check
on t1.studentNo = t2.studentNo;

right join

select t1.studentNo,studentName,SubjectNo,StudentResult
from student  t1
right join result t2
on t1.studentNo = t2.studentNo;

left join

select t1.studentNo,studentName,SubjectNo,StudentResult
from student  t1
left join result t2
on t1.studentNo = t2.studentNo;

• The left table is the student. First check the student number and student name from the student
• Even if there is no subject number and score matching student number and student name in the result table on the right table
• All values will also be returned, except that the account number and score are null

studentNo	studentName	SubjectNo	StudentResult
1001	Sun Dasheng	null	null

3.7 summary and difference

operation	describe
inner join	If there is at least one match in the table, the row is returned
left join	All values will be returned from the left table, even if there is no match in the right table
right join	All values will be returned from the right table, even if there is no match in the left table

-- Query the absent students
select t1.studentNo,studentName,SubjectNo,StudentResult
from student  t1
left join result t2
on t1.studentNo = t2.studentNo
where StudentResult = null;


-- Inquired the information of the students who took the exam: Student number,Student name,Account name,Examination results
-- 1.Student number,Student name  student surface
-- 2.Account name        subject surface
-- 3.fraction			 result surface




-- Concrete implementation
select t1.studentNo,studentName,SubjectName,StudentResult
from student t1
right join result t2
on t1.studentNo = t2.studentNo
-- Query exam information,Use the exam form to connect,Zuo Lian is based on the table on the left
-- Then connect the third table in the query results above
inner join subject t3
on t2.SubjectNo = t3.SubjectNo;

Syntax:

• Join on join query
• where equivalent query

3.8 self connection (understanding)

Database establishment:

• Create classification table
• Insert data in classification table

-- Create classification table
CREATE TABLE IF NOT EXISTS `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 in classification table
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');

• Your own table is connected with your own table. One table can be split into two identical tables

Parent class

categoryid	categoryName
2	information technology
3	software development
5	Art design

Subclass

pid	categoryid	categoryName
3	4	database
2	8	Office information
3	6	web development
5	7	ps Technology

Explanation: the pid of the database is 3, which indicates that under the software development, the classification idcategoryid of the database is 4

Operation: query the subclass relationship corresponding to the parent class

Parent class	Subclass
information technology	Office information
software development	database
software development	web development
Art design	ps Technology

Query the subclass relationship corresponding to the parent class

select a.`categoryName` AS 'Parent column',b.`categoryName` AS 'Sub column'
from `category` AS a,`category` AS b
where a.`categoryid` = b.`pid`;

3.9 paging and sorting

• Paging limit
• Sort order by
  • Ascending: ASC
  • Descending: DESC

Sorting syntax: order by which field to sort and how to sort

-- In ascending order by subject grade
order by StudentResult ASC;

Pagination syntax:

• Complete syntax: limit starting subscript, page size

• Default syntax: limit 5. This is the top 5

• Note: in mysql, limit is executed after order by!!

• The first page displays 5 pieces of data

  • limit 0,5;
    

• On the second page, five pieces of data are displayed

  • limit 5,5;
    

• Page 3

  • limit 10,5;
    

• Page n

  • limit(n-1) * pageSize,pageSize
    

  • pageSize: page size

  • (n-1) * pageSize: starting value

  • Total data / page size = total pages

Example:

Query the information of the top ten students in the first year of JAVA with a score greater than 80 (student number, name, course name, examination score)

• Student number, name ➡ In the student table
• Course name ➡ In the subject table
• Examination results ➡ In the result table

select t1.studentNo,StudentName,SubjectName,StudentResult
from student t1
inner join result t2
on t1.StudentNo = t2.StudentNo
inner join subject t3
on t3.SubjectNo = t2.SubjectNo
where SubjectName = 'JAVA First academic year' and StudentResult >= 80;
order by studentResult DESC
limit 0,10;

4.MySQL function

4.1 common functions (understand)

1. Mathematical operation

-- Mathematical operation
select ABS(-8);        -- Absolute value 8
select celiling(9.4)   -- Round up 10
select floor(9.4)      -- Round down 9
select rand()          -- Return a 0~1 Random number between
select sing(10)        -- The sign of judging a number returns 0, and the negative number returns 0-1,A positive number returns 1

1. String function

-- String function
select char_length('Even the smallest sail can sail far')   -- String length 10
select concat('I','want','come on.')            -- Splice string
select lower('KUANG')    -- Turn lowercase
select upper('kuang')    -- Capitalize
select instr('kuangshen','h') -- Returns the index of the first occurrence of a substring
select replace('Persistence will lead to success','insist','strive')  -- Replace the specified string that appears
select substr('Persistence will lead to success',2,3) -- Returns the specified string(Source string, intercepted position, intercepted length) 
select reverse('Hello')    -- Reverse string

1. Time and date functions (remember)

-- Time and date functions(remember)
select current_data()   -- Get current date
select curdate()        -- Get current date
select now()            -- Get current time
select localtime()      -- Local time
select sysdate()        -- system time
select year(now())      -- year
select month(now())     -- month
select day(nowue())     -- day

4.2 aggregate function

Function name	describe
COUNT()	Returns the total number of records that meet the Select condition
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

-- count(Specify column/field) 
select count(Studentname) from student;
-- count(*) 
select count(*) from  student;
-- count(1) recommend
select count(1) from student;

The results returned by the above three sql statements are the same. How many student names are there? What is the difference between them?

• Count (specify column / 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 contains records with null fields
• count(1) uses 1 to represent the code line. In the statistical result, the record with null field is included

Here are some comparisons between them:

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

Other aggregate function usage

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;

4.3MD5 encryption (extension)

MD5, message digest algorithm 5, is used to ensure complete and consistent information transmission. It is one of the hash algorithms widely used in computers (also known as abstract algorithm and hash algorithm). MD5 has been widely implemented in mainstream programming languages.

Realize data encryption

1. 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

1. Insert some data

INSERT INTO testmd5 VALUES
(1,'kuangshen','123456'),
(2,'1 qinjiang','456789')

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

update testmd5 set pwd = md5(pwd);

1. If the password of a user (such as kuangshen) is encrypted separately

INSERT INTO testmd5 VALUES
(3,'kuangshen2','123456')
update testmd5 set pwd = md5(pwd) 
where name = 'kuangshen2';

1. Insert new data automatically encrypted

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

1. Query the login user information (md5 compare the use, check the encrypted password entered by the user for comparison)

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