(1) Clone the table and generate the data records of the data table into a new table

1. Method 1: use like

create table test1 like info;  #Using the LIKE method, copy the KY08 table structure to generate the test01 table
insert into test1 select * from info;

2. Method 2: direct cloning

CREATE TABLE test2 (SELECT * from info);

show create table test2\G;					#Get the table structure, index and other information of the data table
SELECT * from test2;

*Defect of direct cloning: some special attributes cannot be cloned

(2) Clear the table and delete all data in the table

1. Method 1: record ID is not deleted

delete from test1;
#DELETE after clearing the table, the returned result contains deleted record entries; DELETE deletes record data line by line during operation; If there is a self increment field in the table, after deleting all records with DELETE FROM, the newly added record will continue to be self increment written to the record from the original maximum record ID.

2. Method 2: delete record ID

truncate table test2;
#TRUNCATE does not return deleted entries after clearing the table; TRUNCATE rebuilds the table structure as it is, so TRUNCATE is faster than DELETE to clear the table; After using TRUNCATE TABLE to clear the data in the table, the ID will be re recorded from 1.

(3) Create temporary table

• After the temporary table is created successfully, the temporary table created cannot be seen by using the SHOW TABLES command, and the temporary table will be destroyed after the connection exits. Before exiting the connection, you can also perform operations such as adding, deleting, modifying, and querying. For example, use the DROP TABLE statement to manually and directly delete the temporary table.

CREATE TEMPORARY TABLE Table name (Field 1 data type,Field 2 data type[,...][,PRIMARY KEY (Primary key name)]);

Example:
create temporary table test3 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
sex char(2) not null);

insert into test3 values(1,'zhangsan','male');

select * from test03;
show tables;

(4) Create foreign key constraints to ensure data integrity and consistency

• Definition of foreign key: if the same attribute field X is the primary key in Table 1 but not in Table 2, then field X is called the foreign key in Table 2

• Understanding of primary key table and foreign key table:
  *Tables with public keywords as primary keys are primary key tables (parent and primary)
  *Tables with public keywords as foreign keys are foreign key tables (from table and appearance)

• Note: the fields of the sub table associated with the foreign key must be set as the primary key. The foreign key fields of the main table and the fields of the sub table are required to have the same data type, character length and constraints.

#Create main table test4
create table test4 (hobid int(4),hobname varchar(50));
#Create from table test5
create table test5 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int(4));

#Add a primary key constraint to the main table test4. It is recommended that the primary key name be "PK_" start
alter table test4 add constraint PK_hobid primary key (hobid);
#To add a foreign key from the test5 table and establish a foreign key association between the hobbid field of the test5 table and the hobbid field of the test4 table, it is recommended that the foreign key name be "FK_" start
alter table test5 add constraint FK_hob foreign key (hobid) references test4 (hobid);

desc test5;

• When inserting a new data record, you must first the master table and then the slave table

insert into test4 values(1,'read');
insert into test5 values(1,'lisi',10,1);

• When deleting data records, you must first delete the primary table from the primary table, that is, when deleting the primary key table, you must first delete other tables associated with it

drop tables test5;
drop tables test4;

• Viewing and deleting foreign key constraints

show create table test5\G;
alter table test5 drop foreign key FK_hob;
alter table test5 drop key FK_hob;
desc test5;

• Six common constraints in MySQL

Primary key constraint( primary key)
Foreign key constraint( foreign key)
Non NULL constraint( not null)
Uniqueness constraint( unique [key|index])
Default value constraint( default)
Self increasing constraint( auto_increment)

6, Database user management

(1) New user

CREATE USER 'user name'@'Source address' [IDENTIFIED BY [PASSWORD] 'password'];
#----------------------Interpretation part-----------------------------------------
'user name': Specifies the user name that will be created
'Source address': Specify the hosts on which the newly created user can log in, and use the IP The form of address, network segment and host name,
          Available to local users localhost，Allow any host to log in. Wildcards are available%
'password': If plaintext password is used, enter it directly'password'，Inserted into the database by Mysql Automatic encryption;
       If you use an encrypted password, you need to use it first SELECT PASSWORD('password'); Get the ciphertext and add it to the statement PASSWORD 'ciphertext';
       If omitted“ IDENTIFIED BY"Part, the user's password will be empty (not recommended)
#----------------------------------------------------------------------
For example:
create user 'test1'@'localhost' IDENTIFIED BY '123456';

select password('123456');
create user 'test2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

(2) View user information

#The created user is saved in the user table of mysql database
use mysql;
select user,authentication_string,Host from user;

(3) Rename user

rename user 'test1'@'localhost' to 'zhangsan'@'localhost';

(4) Delete user

drop user 'test2'@'localhost';

(5) Modify the password of the current login user

set PASSWORD = PASSWORD('abc123');

(6) Change other user passwords

set PASSWORD for 'zhangsan'@'localhost' = PASSWORD('abc123');

(7) Solution of forgetting root password

1. Modify / etc / my CNF configuration file, login to mysql directly without password

vim /etc/my.cnf
[mysqld]
skip-grant-tables					#Add to log in to mysql without using the authorization table

systemctl restart mysqld.service

mysql								#Direct login

2. Use update to change the root password and refresh the database

update mysql.user set AUTHENTICATION_STRING = PASSWORD('123456') where user='root';

FLUSH PRIVILEGES;
quit

mysql -u root -pabc123

Note: finally /etc/my.cnf In the configuration file skip-grant-tables Delete or comment and restart mysql Service.

• Note: finally, put / etc / my Delete or comment skip grant tables in CNF configuration file and restart MySQL service. Otherwise, everyone can log in directly using mysql, which will cause serious consequences.

7, Database user authorization

• GRANT statement: it is specially used to set the access rights of database users.
  *When the specified user name does not exist, GRANT statement will create a new user;
  *GRANT statement is used to modify user information when the specified user name exists.

GRANT Permission list ON Database name.Table name TO 'user name'@'Source address' [IDENTIFIED BY 'password'];
#-------------------------------Parameter interpretation---------------------------------------------------------------------------
Permission list:  Used to list various database operations authorized to use, separated by commas, such as“ select,insert,update". Use“ all"Indicates all permissions and can authorize any operation.
          
Database name.Table name:  Use to specify the names of databases and tables that authorize operations, where wildcards can be used“*". For example, use“ test.*"The object representing the authorization operation is test All tables in the database.
              
'user name'@'Source address':  Used to specify the user name and the client address allowed to access, that is, who can connect and where to connect. The source address can be domain name IP Address, you can also use“%"Wildcard, indicating all addresses in a region or network segment, such as“%.test.com","192.168.163.%"Wait.

IDENTIFIED BY: Used to set the password string used by users when connecting to the database. When creating a new user, if omitted“ IDENTIFIED BY"Part, the user's password will be empty.

For example:
#The user zhangsan is allowed to query the data records of all tables in the school database locally, but it is prohibited to query the records of tables in other databases.
GRANT select ON school.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'abc123';

flush privileges;
quit

mysql -u zhangsan -pabc123
use school;
show tables;
select * from info;

Other authorization examples:
#Allow user zhangsan to remotely connect to mysql at all terminals and have all permissions.
GRANT ALL [PRIVILEGES] ON *.* TO 'zhangsan'@'%' IDENTIFIED BY 'abc123';

(2) View permissions

SHOW GRANTS FOR user name@Source address;

For example:
SHOW GRANTS FOR 'zhangsan'@'localhost';

(3) Revoke permission

REVOKE Permission list ON Database name.Table name FROM user name@Source address;
For example:
REVOKE SELECT ON "school".* FROM 'zhangsan'@'localhost';
SHOW GRANTS FOR 'zhangsan'@'localhost';

#Use permission can only be used for database login and cannot perform any operation; The USAGE permission cannot be recycled, that is, REVOKE cannot delete a user.
flush privileges;
REVOKE ALL ON *.* FROM 'zhangsan'@'localhost';