1. Get to know MySQL

preface

Data needs to be stored. Large companies will go to IOE for self research!

Database is the core existence in the software system -- DBA database administrator

Java EE: Enterprise Java Web Development

Front end (page: display, data)

Background (connection point: connect database JDBC, link the front end (control, control view jump, and transfer data to the front end))

Database (save data, Txt, Excel, word)

introduce

DataBase (DB, DataBase) - similar to excel

Concept: data warehouse, software, installed on operating system (SQL)

Function: store data and manage data

Database classification

Relational database: row and column (SQL)

• MySQL，Oracle，Sql Server，DB2，SQLlite
• Data is stored through the relationship between tables and between rows and columns, including student information table and attendance table

Non relational database: (NoSQL) not only

• Redis，MongDB
• Non relational database, object storage, is determined by the attributes of the object itself.

DBMS (database management system)

• Database management software, scientific and effective management of our data
• Database is used for storage, and MySQL is a database management system

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-73A2PXmZ-1633447488471)(img/image-20210823110719084.png)]

MySQL

Open source database software

Small size, fast speed and low total cost of ownership

Stable version 5.7 and 8.0 (different database drivers)

download

https://downloads.mysql.com/archives/community/

Try not to use exe. It will be injected into the registry and installed with compressed package as much as possible

Install MySQL

1. decompression

2. Configuration environment

path/add to bin catalogue

1. Create the configuration file my.ini (located in mysql directory)

[mysqld]
#Set 3306 port
port = 3306
#Set mysql installation directory
basedir=E:\Environment\mysql-5.7.19\
#Set the data storage directory of mysql database. MySQL 8 + does not need the following configuration. The system can generate it by itself, otherwise an error may be reported
datadir=E:\Environment\mysql-5.7.19\data\
#Skip password verification
skip-grant-tables

1. Start cmd in administrator mode and run all commands

cd /d E:\Environment\mysql-5.7.19\bin
//Install mysql service
mysqld -install
>>Service successfully installed.


//Initialize database file
mysqld --initialize-insecure --user=mysql
//One more data directory


//Start MySQL and enter the management interface with the command to modify the password
net start mysql
mysql -u root -p//No password
//Enter again, indicating that there is no password

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


>>Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

//Refresh permissions
flush privileges;

//Change the configuration in my.ini and comment out skip grant tables
#skip-grant-tables

//Restart normal use
exit
net stop mysql
net start mysql

get into
mysql -u root -p
 Then enter the password

mysql -u root -p123456

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-mzkgu93r-163344748473) (IMG / image-20210823113725391. PNG)]

success!

Empty service: sc delete mysql

Install SQLyog

https://blog.csdn.net/Sunshine_liang1/article/details/84400820

After normal installation, you can register and enter the following interface

Create and fill in relevant information to establish a connection

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-f3ulqc1j-163344748474) (IMG / image-20210823141852577. PNG)]

Note: if there is a small problem in the installation process and the IE browser on the desktop appears, delete the IE browser on the desktop

https://jingyan.baidu.com/article/fcb5aff77ebf18edaa4a7111.html

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-7rivqysa-163344488476) (IMG / image-202108231424417. PNG)]

These four files are actually the file database under the data directory

Interface use

operation

1. Create database

[the external link picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-PuZWsSlN-1633447488478)(img/image-20210823142711260.png)]

The execution of each sqlyog is essentially

1. Create table

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-vkalirrX-1633447488479)(img/image-20210823143207499.png)]

1. View tables and add

Right click to open the table, add and save

Connect to database

Command line connection

mysql -u root -p123456  --Connect to database

--Can find mysql Table below user
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';  --Change Password

flush privileges; --Refresh permissions
---------------------------------------------------------------------------------------
--All statements should use;ending
show databases;  --View all databases
use school    --Switch database
show tables; --View all tables in the database
describe student;  --Displays information about all tables in the database

creat database westos; --Create a database

exit; --Exit connection

/*multiline comment */

Database XXX language CRUD addition, deletion and modification query CV programmer API programmer CRUD programmer

DDL definition

DML operation

DQL query

DCL control

2. Database operation

Operating database > operating tables in Database > operating database

• View sql against visual history
• Fixed syntax or keywords must be forcibly remembered

Basic database operation

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

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

view the database : show databases;

Use database : use Database name;

--tab As shown above, the table name or field name is a special character band``
USE `school`

Create data table (DDL)

Create command

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

CREATE TABLE IF NOT EXISTS `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '',
`name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT'',
`pwd` VARCHAR(10) NOT NULL DEFAULT '123456' COMMENT'',
`email` VARCHAR(50) DEFAULT NULL COMMENT'mailbox',
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8


  -- Set strict check mode(No fault tolerance)SET sql_mode='STRICT_TRANS_TABLES';

Data values and column types

SQL divides data types into three categories: numeric type, string type and time date type

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-FPdhQO7n-1633447488479)(img/image-20210823151755457.png)]

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-4tDJFek6-1633447488480)(img/image-20210823151825156.png)]

NULL value

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

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

• You can define the starting value and step size

NULL and NOT NULL

• Set to NULL, no assignment, default to NULL
• If set to NOT NULL, the column must have a value

DEFAULT

• default
• Used to set default values
  • 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

standard

Each table must have the following five fields to indicate the significance of a record
id Primary key
`version` Optimistic lock
is_delete Pseudo deletion
gmt_create Creation time
gmt_updata  Modification time

Type of data table

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

Common MyISAM and InnoDB types:

Experience (where applicable):

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

Storage location of data table

• MySQL data tables are stored on disk as files

  • Including table files, data files, and database options files
  • Location: the Mysql installation directory \ data \ stores the data table. The directory name corresponds to the database name, and the file name under this directory corresponds to the data table

• be careful:

  • . frm - table structure definition file

  • . MYD - data file (data)

  • . MYI - index file (index)

  The InnoDB type data table has only one *. frm file and the ibdata1 file in the upper directory

  The MyISAM type data table corresponds to three files:

Set data table character set encoding

The default encoding does not support Chinese

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, it is set according to the parameters in the MySQL database configuration file my.ini

Modify and delete data table

Modify table (ALTER TABLE)

Modify table name :ALTER TABLE Old table name RENAME AS New table name

Add field : ALTER TABLE Table name ADD Field column properties[attribute]

Modify field :
--Modify constraints
ALTER TABLE Table name MODIFY Field column type[attribute]
--rename
ALTER TABLE Table name CHANGE Old field name new field column attribute[attribute]

Delete field :
ALTER TABLE Table name DROP Field name

Delete data table

DROP TABLE [IF EXISTS] Table name

IF EXISTS Is optional , Determine whether the data table exists
 If you delete a nonexistent data table, an error will be thrown

The deletion here is different from the following. It directly deletes the entire library

other

1. Available backquotes(`)Wrap identifiers (database name, table name, field name, index, alias) to avoid duplicate names with keywords! Chinese can also be used as identifiers!

2. There is an option file for saving the current database in each library directory db.opt. 

3. notes:
  Single-Line Comments  # Note Content 
  multiline comment  /* Note Content  */
  Single-Line Comments  -- Note Content        (standard SQL Note style, double dash followed by a space character (space TAB,Line feed, etc.))
   
4. Pattern wildcard:
  _   Any single character
  %   Any number of characters, even zero characters
  Single quotation marks need to be escaped \'
   
5. CMD The statement terminator on the command line can be ";", "\G", "\g"，It only affects the display results. It ends with a semicolon elsewhere. delimiter You can modify the statement terminator of the current conversation.

6. SQL Case insensitive (keyword), lowercase is recommended

7. Clear existing statements:\c

3.DML language

Foreign key

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-pgnvx6ou-16334474488480) (IMG / image-20210825093907484. PNG)]

concept

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

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

Foreign bond action

To maintain data consistency and integrity, the main purpose is to control the data and constraints stored in the foreign key table. To associate the two tables, the foreign key can only refer to the value of the column in the appearance or use null values.

establish

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 `student` (
`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

Modify after table creation

-- Create foreign key mode 2 : After creating the sub table,Modify child tables and add foreign keys
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

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;

instructions

Ali does not allow the use of foreign keys. Foreign key constraints must be considered every time you delete or update, which will cause pain during development and inconvenient test data

• A database is a simple table. It only stores data, only rows and columns

• If you want to use multiple table data, you can implement it by program

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 statements

DML: Data Manipulation Language

• Used to manipulate data contained in database objects
• include:
  • INSERT (add data statement)
  • UPDATE (UPDATE data statement)
  • DELETE (DELETE data statement)

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 the same
  • If you omit, you must write it all after it
• Multiple pieces of data can be inserted at the same time, separated by English commas after values
  • Note the position of parentheses

INSERT INTO Table name[(Field 1,Field 2,Field 3,...)] VALUES('Value 1','Value 2','Value 3')
INSERT INTO grade(gradename) VALUES ('Freshman');


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

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

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

Modify data

update command

Syntax:

UPDATE Table name SET column_name=value [,column_name2=value2,...] [WHERE condition];
//All that do not specify conditions will be changed

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

where conditional clause

It can be simply understood as: conditionally filter data from a table

Test:

-- Modify grade information
UPDATE grade SET gradename = 'high school' WHERE gradeid = 1;

Delete data

DELETE command

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. It is not used in this way

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

This command only deletes the data in it, not the table

TRUNCATE command

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

Syntax:

TRUNCATE TABLE table_name;
TRUNCATE table_name;


-- For example: clear grade table
TRUNCATE grade

This command only deletes the data in it, not the table

Note: it is different from the DELETE command

• Same: data can be deleted without deleting the table structure, but TRUNCATE is faster

• Different:

• • Use TRUNCATE TABLE to reset the AUTO_INCREMENT counter
  • Using TRUNCATE TABLE will not affect the transaction (the transaction will be described later)

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)

give an example

create database if not exists `school`;
-- Create a school database
use `school`;
-- Create student table
drop table if exists `student`;
create table `student`(
	`studentno` int(4) not null comment 'Student number',
    `loginpwd` varchar(20) default null,
    `studentname` varchar(20) default null comment 'Student name',
    `sex` tinyint(1) default null comment 'Gender, 0 or 1',
    `gradeid` int(11) default null comment 'Grade number',
    `phone` varchar(50) not null comment 'Contact number, can be blank',
    `address` varchar(255) not null comment 'Address, null allowed',
    `borndate` datetime default null comment 'time of birth',
    `email` varchar (50) not null comment '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 and add the rest by yourself. Only 2 rows are added here
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','Zhang Wei',0,2,'13800001234','Chaoyang, Beijing','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','Qiang Zhao',1,3,'13800002222','Shenzhen, Guangdong','1990-1-1','text111@qq.com','123456199001011233');

-- Insert score data. Only one group is inserted here, and the others are added by themselves
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- Insert grade data
insert into `grade` (`gradeid`,`gradename`) values(1,'Freshman'),(2,'Sophomore'),(3,'Junior'),(4,'Senior'),(5,'Preparatory class');

-- Insert account data
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'Advanced mathematics-1',110,1),
(2,'Advanced mathematics-2',110,2),
(3,'Advanced mathematics-3',100,3),
(4,'Advanced mathematics-4',130,4),
(5,'C language-1',110,1),
(6,'C language-2',110,2),
(7,'C language-3',100,3),
(8,'C language-4',130,4),
(9,'Java Programming-1',110,1),
(10,'Java Programming-2',110,2),
(11,'Java Programming-3',100,3),
(12,'Java Programming-4',130,4),
(13,'database structure -1',110,1),
(14,'database structure -2',110,2),
(15,'database structure -3',100,3),
(16,'database structure -4',130,4),
(17,'C#Foundation ', 130,1);

4. Use DQL to query data

DQL language

DQL (data query language)

• Query database data, such as SELECT statement
• Simple single table query or multi table complex query and nested query
• It is the core and most important statement in database language
• 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

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;

alias

Use of AS clause

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;

duplicate removal

Use of DISTINCT keyword

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

-- # Check which students took the exam (student number) to 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)

Columns using 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 return result column

• Used in 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 @@auto_increment_increment; -- Query auto increment step
  SELECT VERSION(); -- Query version number
  SELECT 100*3-1 AS Calculation results; -- expression
  
  -- Students' test scores are collectively raised by one point to view
  SELECT studentno,StudentResult+1 AS 'After scoring' FROM result;
  

• Avoid interfering with the development language program by including '.', '*' and parentheses in the SQL return result

where conditional statement

Function: used to retrieve qualified records in a data table

Search criteria can be composed of one or more logical expressions, and the results are generally true or false

Logical operator

test

-- Qualified query(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 && 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

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:

-- Fuzzy query between and \ like \ in \ null

-- =============================================
-- 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 Jiazi
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%Jia%';

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

Join table query

JOIN comparison

Seven kinds of Join:

test

/*
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 the 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 join
       (The right table is used as the benchmark, and the left table is matched one by one. If it fails to match, the records of the right table are returned, and the left table is 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 AS s
INNER JOIN result AS r
WHERE s.studentNO = r.studentNO

---------------------------------------------

--where/on It indicates the connection conditions. The two tables need to have cross parts, select The statement needs to have a definite look-up table description
--join on join query   where  Equivalent query

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

-- Check the 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

--

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-yu9nhpbc-163447488484) (IMG / image-20210928210406402. PNG)]

Multi table query should be split into two tables and two tables for query

Self connection

• The core idea: as like as two peas, the two tables are connected to the two tables.

• Requirement: query parent column name and other child column names from a table containing column ID, column name and parent column ID

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)
-- One table is queried as two tables. Pay attention to using more aliases
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'

Sorting and paging

For database level isolation, the order cannot be changed in select

Sorting 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

Pagination syntax

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

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

deduction:
   first page : limit 0,5
   Page 2 : limit 5,5
   Page 3 : limit 10,5
   ......
   The first N page : limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:Page number,pageSize:Number of single page displays]
   
*/

-- 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 subjectname='JAVA First academic year'
ORDER BY StudentResult DESC
LIMIT 0,10

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

5.MySQL function

Common functions

Data function

 SELECT ABS(-8);  /*absolute value*/
 SELECT CEILING(9.4); /*Round up*/
 SELECT FLOOR(9.4);   /*Round down*/
 SELECT RAND();  /*Random number, returns a random number between 0 and 1*/
 SELECT SIGN(0); /*Symbolic function: negative number returns - 1, positive number returns 1, 0 returns 0*/

String function

 SELECT CHAR_LENGTH('Crazy God says persistence can succeed'); /*Returns the number of characters contained in a string*/
 SELECT CONCAT('I','love','program');  /*Merge strings. There can be multiple parameters*/
 SELECT INSERT('I love programming helloworld',1,2,'Super love');  /*Replace string, replacing a length from a position*/
 SELECT LOWER('KuangShen'); /*a lowercase letter*/
 SELECT UPPER('KuangShen'); /*Capitalize*/
 SELECT LEFT('hello,world',5);   /*Intercept from left*/
 SELECT RIGHT('hello,world',5);  /*Intercept from the right*/
 SELECT REPLACE('Crazy God says persistence can succeed','insist','strive');  /*Replace string*/
 SELECT SUBSTR('Crazy God says persistence can succeed',4,6); /*Intercept string, start and length*/
 SELECT REVERSE('Crazy God says persistence can succeed'); /*reversal
 
 -- Query students surnamed Zhou,Changed to Zou
 SELECT REPLACE(studentname,'week','Zou') AS New name
 FROM student WHERE studentname LIKE 'week%';

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*/

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.

 -- Aggregate function
 /*COUNT:Non empty*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*recommend*/
 
 -- In a sense, count(1) And count(*) Represents the query of all data rows.
 -- count(field) The number of occurrences of this field in the table will be counted. The ignored field is null The situation. That is, the statistics field is not null Record of.
 -- count(*) It includes all columns, equivalent to the number of rows. In the statistical results, the included fields are null Records of;
 -- count(1) Use 1 to represent the code line. In the statistical results, the included fields are null Record of.
 /*
 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.
 
 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.
 */
 
 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;

Title:

 -- 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
 */

MD5 encryption

1, Introduction to MD5

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 translated abstract algorithm and hash algorithm). MD5 has been widely implemented in mainstream programming languages. Computing data (such as Chinese characters) into another fixed length value is the basic principle of hash algorithm. The predecessors of MD5 include MD2, MD3 and MD4.

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,'kuangshen','123456'),(2,'qinjiang','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 kuangshen) is encrypted separately:

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

Insert new data automatically encrypted

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

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

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

Summary

 -- ================ Built in function ================
 -- Numerical function
 abs(x)            -- absolute value abs(-10.9) = 10
 format(x, d)    -- Format the millennial value format(1234567.456, 2) = 1,234,567.46
 ceil(x)            -- Round up ceil(10.1) = 11
 floor(x)        -- Round down floor (10.1) = 10
 round(x)        -- Rounding off
 mod(m, n)        -- m%n m mod n Remainder 10%3=1
 pi()            -- Obtain pi
 pow(m, n)        -- m^n
 sqrt(x)            -- arithmetic square root 
 rand()            -- random number
 truncate(x, d)    -- intercept d Decimal place
 
 -- Time date function
 now(), current_timestamp();     -- Current date and time
 current_date();                    -- current date
 current_time();                    -- current time 
 date('yyyy-mm-dd hh:ii:ss');    -- Get date section
 time('yyyy-mm-dd hh:ii:ss');    -- Get time section
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- Format time
 unix_timestamp();                -- get unix time stamp
 from_unixtime();                -- Get time from timestamp
 
 -- String function
 length(string)            -- string Length, bytes
 char_length(string)        -- string Number of characters
 substring(str, position [,length])        -- from str of position start,take length Characters
 replace(str ,search_str ,replace_str)    -- stay str of use replace_str replace search_str
 instr(string ,substring)    -- return substring First in string Location in
 concat(string [,...])    -- connection string 
 charset(str)            -- Returns the string character set
 lcase(string)            -- Convert to lowercase
 left(string, length)    -- from string2 From left in length Characters
 load_file(file_name)    -- Read content from file
 locate(substring, string [,start_position])    -- with instr,However, the start position can be specified
 lpad(string, length, pad)    -- Reuse pad Add in string start,Until the string length is length
 ltrim(string)            -- Remove front-end spaces
 repeat(string, count)    -- repeat count second
 rpad(string, length, pad)    --stay str Later use pad supplement,Until the length is length
 rtrim(string)            -- Remove back-end spaces
 strcmp(string1 ,string2)    -- Compare two string sizes character by character
 
 -- Aggregate function
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- Other common functions
 md5();
 default();

6. Services

What is a transaction

• 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 only supports InnoDB and BDB data table types

ACID principles for transactions

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). Transactions 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.

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.

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.

Basic grammar

-- use set Statement to change the auto submit mode
SET autocommit = 0;   /*close*/
SET autocommit = 1;   /*open*/

-- be careful:
--- 1.MySQL The default is auto submit
--- 2.Auto commit should be turned off first when using transactions

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

-- Commit a transaction to the database
COMMIT

-- Rollback transaction,The data returns to the initial state of this transaction
ROLLBACK

-- reduction MySQL Automatic submission of database
SET autocommit =1;

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

/*
Classroom test questions

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,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

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

-- Transfer realization
SET autocommit = 0; -- Turn off auto submit
START TRANSACTION;  -- Start a transaction,Mark the starting point of the transaction
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- Commit transaction
# rollback;
SET autocommit = 1; -- Resume auto commit

7. Index

Function of index

• Improve query speed
• Ensure data uniqueness
• The connection between tables can be accelerated to realize the referential 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 field for search optimization

classification

• Primary key index
• Unique index
• General index (Index)
• Full text index (FullText)

primary key

Primary key: an attribute group can uniquely identify a record

characteristic:

• The most common index type
• Ensure the uniqueness of data records
• Determine the location of specific data records in the database

unique index

Function: avoid duplicate values in a data column in the same table

Difference from primary key index

• There can only be one primary key index
• There may be more than one unique index

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- or UNIQUE KEY `GradeID` (`GradeID`)
)

General index

Function: quickly locate specific data

be careful:

• Both index and key keywords can set the general index
• Fields that should be added to query criteria
• Too many general indexes should not be added, which will affect the operation of data insertion, deletion and modification

CREATE TABLE `result`(
   -- Omit some code
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- Add when creating table
)
-- Add after creation
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

Full text index

Baidu search: full text index

Function: quickly locate specific data

be careful:

• Can only be used for datasheets of type MyISAM
• Can only be used for char, varchar, text data column types
• Suitable for large data sets

/*
#Method 1: when creating a table
  　　CREATE TABLE Table name(
               Field name 1 data type [integrity constraint...],
               Field name 2 data type [integrity constraint...],
               [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;


#Display index information: SHOW INDEX FROM student;
*/

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

/*EXPLAIN : Analyze SQL statement execution performance*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*Use full-text indexing*/
-- Full text search passed MATCH() Function complete.
-- Search string as against() The parameters for are given. The search is performed ignoring the case of letters. For each record row in the table, MATCH() Returns a correlation value between the search string and the record line MATCH() The similarity scale between the text of the column specified in the list.
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
Before we start, let's talk about the full-text index version, storage engine and data type support

MySQL 5.6 In previous versions, only MyISAM storage engine supported 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.
*/

Extension: 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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app User table'

Batch insert data: 100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `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';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = 'User 9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

Create index

CREATE INDEX idx_app_user_name ON app_user(name);

Test general index

mysql> EXPLAIN SELECT * FROM app_user WHERE name = 'User 9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = 'User 9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = 'User 9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = 'User 9999';
1 row in set (0.00 sec)

Index criteria

• The more indexes, the better
• Do not index data that changes frequently
• 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

Indexed data structure

-- When creating the above index, we can specify the index type for it, which can be divided into two categories
hash Type index: single query is fast and range query is slow
btree Index of type: b+Tree, the more layers, the exponential growth of data volume (we use it because innodb (it is supported by default)

-- Different storage engines support different index types
InnoDB Supports transaction, row level locking, and B-tree,Full-text Index, not supported Hash Indexes;
MyISAM Transaction is not supported, table level locking is supported, and B-tree,Full-text Index, not supported Hash Indexes;
Memory Transaction is not supported, table level locking is supported, and B-tree,Hash Index, not supported Full-text Indexes;
NDB Supports transaction, row level locking, and Hash Index, not supported B-tree,Full-text Equal index;
Archive Transaction and table level locking are not supported B-tree,Hash,Full-text Equal index;

8. Authority and design database

user management

Create users using SQLyog and grant permissions to demonstrate

Basic command

/* User and rights management */ ------------------
User information table: mysql.user

-- Refresh permissions
FLUSH PRIVILEGES

-- Add user CREATE USER kuangshen 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 kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- Set password
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 kuangshen2
DROP USER user name

-- Assign permissions/Add user
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] ...

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 -p123456 school student >D:/a.sql
　　mysqldump -u user name -p Password library name table name > file name(D:/a.sql)
2. Export multiple tables -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u user name -p Password library name table 1 Table 2 Table 3 > file name(D:/a.sql)
3. Export all tables -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u user name -p Password library name > file name(D:/a.sql)
4. Export a library -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u user name -p password -B Library name > file name(D:/a.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

Standardized database design

Why database design

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

Poor database design:

• Data redundancy, waste of storage space
• Exceptions in data update and insertion
• 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 requirement information

To design a database

• 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 details that each entity needs to store [Attribute]

• Identify relationships between entities [Relationship]

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

First normal form (1st NF)

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 normal form (2nd NF)

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 (3rd NF)

If a relationship satisfies the second normal form and no other columns except the primary key are transitively dependent on the primary key column, it satisfies the third normal form

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 normalized database

At the same time of data standardization, we should comprehensively consider the performance of the 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

Command set

Help command

-- View the definition of creating a database
SHOW CREATE DATABASE school;
-- View the definition of creating a data table
SHOW CREATE TABLE student;
-- see mysql Supported engine types (Table type)
SHOW ENGINES;
-- Display table structure
--DESC student;

Database operation (create + delete + view + use)

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

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

view the database : show databases;

Use database : use Database name;

--tab As shown above, the table name or field name is a special character band``
USE `school`

Data table operation (create + modify + delete)

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

Modify table name :ALTER TABLE Old table name RENAME AS New table name

Add field : ALTER TABLE Table name ADD Field column properties[attribute]

Modify field :
--Modify constraints
ALTER TABLE Table name MODIFY Field column type[attribute]
--rename
ALTER TABLE Table name CHANGE Old field name new field column attribute[attribute]

Delete field :
ALTER TABLE Table name DROP Field name

Delete data table:
DROP TABLE [IF EXISTS] Table name

Data processing (add + modify + delete * 2)

INSERT INTO Table name[(Field 1,Field 2,Field 3,...)] VALUES('Value 1','Value 2','Value 3')
UPDATE Table name SET column_name=value [,column_name2=value2,...] [WHERE condition];
DELETE FROM Table name [WHERE condition];
TRUNCATE Table name;

Query usage

SELECT studentno AS Student number,studentname AS full name FROM student AS s;

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)

SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;

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='XXXXX'

Sorting and paging

ORDER BY StudentResult DESC
LIMIT 0,10

Shortcut key

tab completion 

Reference resources:

Crazy MySQL video bilibili bili