Introduction to MySql:

1. Why use MySQL

Data is easily lost in memory
The data is in the file, which is not easy to operate

2. Whenever

Use a complete management system for unified management and easy query

3. When will it be used

Where persistent data storage is required

Terminology:
DB: the "warehouse" in which a database stores data. It holds a series of organized data.
DBMS: database management system (DBMS) database is a container created and operated by DBMS.
SQL: structured query language is a language specially used to communicate with database.

1, MySQL installation (see the homepage for detailed steps)

  2, Permission management (whole code)

-- Query all users
select * from user;

-- Follow user name and password
select host,user,authentication_string from user;

-- Create a new user (no password)
create user lgs;

-- Modify new user password
ALTER USER lgs IDENTIFIED WITH mysql_native_password BY'123456'

-- Set permissions
#Syntax: grant privileges on databasename.tablename to username@'host ';

-- take mybatis_ssm Database t_mvc_book Table permissions to lgs
grant privileges on mybatis_ssm.t_mvc_book to lgs@'%';

-- Add operation permission
grant select on mybatis_ssm.t_mvc_book to lgs@'%';
grant update on mybatis_ssm.t_mvc_book to lgs@'%';
grant delete on mybatis_ssm.t_mvc_book to lgs@'%';
grant insert on mybatis_ssm.t_mvc_book to lgs@'%';
-- Add all permissions
grant all on mybatis_ssm.t_jsoup_article to lgs@'%';

-- Grant permissions to all tables in a library
grant all on mysql.* to lgs@'%';

-- Revoke permissions
#Syntax: revoke privileges on databasename.tablename from username@'host ';
-- Revoke table permissions in the database
revoke all on mybatis_ssm.t_jsoup_article from lgs@'%';

-- Cancel library permissions
revoke all on mysql.* from lgs@'%';

-- Cancel library permissions
revoke all on mybatis_ssm.* from lgs@'%';

-- Revoke table permissions in the database
revoke all on mybatis_ssm.t_mvc_book from lgs@'%';

-- Cancel individual permissions
revoke delete on mybatis_ssm.t_mvc_book from lgs@'%';

-- Refresh
flush privileges;

-- View to list User granted permissions SQL
show grants for 'lgs'@'%';

  Step by step:

Query all users (the user table is the original table in the local connection)

select * from user;

Y: Allow   N: Not allowed  

The meaning of the value of the host column in the user table

%                 Match all hosts

::1                :: 1 is compatible with ipv6, which means 127.0.0.1 of ipv4

127.0.0.1     It will connect through TCP/IP protocol and can only be accessed locally

localhost     Localhost will not be resolved to an IP address, but will be connected directly through UNIX socket

To create a user, you can query all users first

select host,user,authentication_string from user;

1. Create user: (lgs)

Create a new user (no password)

create user lgs;

Set password:

Modify the new user password (red part: user name and password)

ALTER USER lgs IDENTIFIED WITH mysql_native_password BY'123456'

Delete user:

drop user lgs; 

Connect after user creation:  

  2. Set permissions

#Syntax: grant privileges on databasename.tablename to username@'host ';

Parameter description of setting permission (Grant) and revoke permission (Revoke):

privileges: user's operation permissions, such as SELECT,INSERT,UPDATE,DELETE, etc. all permissions are granted;

databasename: database name;

tablename:   Table name. If you want to grant users permission to operate all databases and tables, you can use it directly: *. *;  

Example: setting permissions for users

  Set permissions:

-- Set permissions
#Syntax: grant privileges on databasename.tablename to username@'host ';

-- take mybatis_ssm Database t_mvc_book Table permissions to lgs
grant privileges on mybatis_ssm.t_mvc_book to lgs@'%';

-- Add operation permission
grant select on mybatis_ssm.t_mvc_book to lgs@'%';
grant update on mybatis_ssm.t_mvc_book to lgs@'%';
grant delete on mybatis_ssm.t_mvc_book to lgs@'%';
grant insert on mybatis_ssm.t_mvc_book to lgs@'%';
-- Add all permissions
grant all on mybatis_ssm.t_jsoup_article to lgs@'%';

-- Grant permissions to all tables in a library
grant all on mysql.* to lgs@'%';

Operation results: (corresponding operations can be performed only after corresponding permissions are given)

Revoke permissions:

#Syntax: revoke privileges on databasename.tablename from username@'host ';

-- Revoke permissions
#Syntax: revoke privileges on databasename.tablename from username@'host ';
-- Revoke table permissions in the database
revoke all on mybatis_ssm.t_jsoup_article from lgs@'%';

-- Cancel library permissions
revoke all on mysql.* from lgs@'%';

-- Cancel library permissions
revoke all on mybatis_ssm.* from lgs@'%';

-- Revoke table permissions in the database
revoke all on mybatis_ssm.t_mvc_book from lgs@'%';

-- Cancel individual permissions
revoke delete on mybatis_ssm.t_mvc_book from lgs@'%';

-- Refresh
flush privileges;

-- View to list User granted permissions SQL
show grants for 'lgs'@'%';

Code to refresh permissions

flush privileges;

View the SQL that gives permission to the list user

show grants for 'lgs'@'%';

You need to cancel as many permissions as you set for the user. For example, we set all permissions for a table, but we also give you an add permission before setting all permissions. However, after we cancel all permissions for the table, the user will still have an add permission. This is why we can use the above line of code to view all permissions of the user.

3, Database creation (manual)

Remember the same character set and sorting rules, otherwise different characters may be garbled

Code creation

Syntax 1: create database database name;

Syntax 2: create database if not exists database name default charset utf8 collate utf8_general_ci;

notes   Meaning: the default database code set is utf8 (i.e. UTF-8). collate indicates that the sorting rule is utf8_general_ci；

View all databases
Syntax: show databases;

Delete database (use with caution)
Syntax: drop database database name;

4, Four engines (InooDB, MyISAM, MEMORY, SHOW ENGINES)

Common: InnoDB storage engine, MyISAM storage engine

1. InnoDB storage engine: InnoDB is the preferred engine for transactional databases. It supports transaction security tables (ACID), row locking and foreign keys. InnoDB is the default MySQL engine.

2. MyISAM storage engine: MyISAM is based on and extends the ISAM storage engine. It is one of the most commonly used storage engines in Web, data warehousing and other application environments. MyISAM has high insertion and query speed, but it does not support things.

  OK!   It's over now. I hope I can help you!!!