Data table advanced operations

Clone the table and generate the data records of the data table into a new table

Method 1: use like

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

Method 2: direct cloning

However, direct cloning cannot replicate some special attributes

CREATE TABLE test1 (SELECT * from info);

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

Clear the table and delete all data in the table

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.

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.

Add: the deletion speed is drop > truncate > delete, but delete has the best security

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.
PS: unable to create foreign key

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 test3;
show tables;

Six common constraints in MySQL

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

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;
desc test4;
#You can use the query table statement structure command to view foreign key associations
show create table 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

If you want to delete the foreign key constraint field, delete the foreign key constraint first, and then delete the foreign key name

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

Database user management

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

View user information

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

Rename User

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

delete user

drop user 'test2'@'localhost';

Modify the password of the current login user

set PASSWORD = PASSWORD('abc123');

Change other user passwords

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

Solution of forgetting root password

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

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.

Database user authorization

Grant permission

• grant right
• 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:  Use commas to separate various operations, such as authorizing databases“ 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.249.%"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.

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

flush privileges;
quit

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

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

View permissions

SHOW GRANTS FOR user name@Source address;

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

Revoke permission

You need to return the root user to revoke. dizu user has only query permission

REVOKE Permission list ON Database name.Table name FROM user name@Source address;
For example:
REVOKE SELECT ON "school".* FROM 'dizu'@'localhost';
SHOW GRANTS FOR 'dizu'@'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 'dizu'@'localhost';