1, Data table advanced operations

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

Method 1:

create table test1 like kfc; #Using the like method, copy the test1 table structure to generate the test2 table
insert into test1 select * from kfc；

Method 2:

	create table test2 (select * from kfc);

2. Clear the table and delete all data in the table

Method 1:

delete from test2;
#After the table is cleared by delete, there are deleted record entries in the returned results. During delete, the record data is deleted row by row. If there is a self growing field in the table, after deleting all records with delete from, the newly added records will continue to be automatically added from the original maximum record ID

Method 2:

truncate table test1;
#After truncate empties the table, no deleted entries are returned. Truncate reestablishes the table structure as it is. Therefore, truncate is faster than delete. After truncate table empties the data in the table, the ID will be recorded from 1

3. Create temporary table

After the temporary table is created successfully, the temporary table cannot be seen by using the show tables command. 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, you can manually delete the temporary table by using the drop table statement

create temporary table Table name (field 1 data type, field 2 data type)[,.....][,primary ey (Primary key name)])；

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 tables and external tables

(1) Tables with public keywords as primary keys are primary key tables (parent tables and primary tables)

(2) Tables with public keywords as foreign keys are external tables (from table and appearance)

Note: the fields of the master table associated with the foreign key must be set as the primary key. It is required that the slave table cannot be a temporary table, and the fields of the master and slave tables have the same data type, character length and constraints

Create foreign key constraints

create table pro (pid int,pname char(20)); #Primary key table
create table stu (id int,name char(10),age int,cid int); #Foreign key table
#Set the pid of the primary key table as the primary key
alter table pro add constraint PK_pid primary key (pid);   #constraint PK_pid add primary key name
#Set the cid of the outsourcing table as the foreign key
alter table stu add constraint FK_cid foreign key (cid) references pro (pid);

matters needing attention:

5. View or delete foreign key constraints

show create table student；
desc student；
alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;

Common constraints in 6 MySQL:

Primary key constraint primary key
 Foreign key constraint foreign key
 Non NULL constraint not null
 Uniqueness constraint unique key
 Default value constraint default
 Self increasing constraint auto_increment

2, Database user management

1. New user

create user 'user name'@'Source address' [identified by [password] 'password'];
#User name: Specifies the user that will be created
#Source address: specify the hosts on which the newly created user can log in. The forms of IP address, network segment and host name can be used. Local users can use localhost. Any host is allowed to log in. The wildcard% can be used
#Password: if plaintext password is used, enter 'password' directly, and mysql will automatically encrypt it when it is inserted into the database;
      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)

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 'Old user name'@'localhost' to 'New user name'@'localhost';
select User,Host,authentication_string from user;

4. Delete user

DROP USER 'lisi'@'localhost';

5. Modify the password of the current login user

set password = password('New password')；
quit
mysql -uroot -p New password

6. Change other user passwords

set password for 'Other users'@'localhost' = password('password')；

7. Solution to forgetting the root password

Method 1: modify the / etc/my.cnf configuration file and log in to mysql directly without using a password

vim /etc/my.conf
[mysqld]
skip-grant-tables #Add to log in to mysql without using the authorization table
 
systemctl restart mysqld
 
mysql #Direct login

Method 2: use update to change the root password and refresh the database

update mysql.user set authentication_string = password('111111') where user='root';
 
flush privileges;
quit
mysql -uroot -p111111
 
#Note that finally, delete skip grant tables in the / etc/my.cnf configuration file and restart the service

3, Database user authorization

1. Grant permissions

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; when the specified user name exists, grant statement is used to modify user information

grant Permission list on Database name.Table name to 'user name'@'Source address' [identified by 'password']；
 
#Permission list: used to list various database operations authorized to use, separated by commas, such as' select,insert,update '. Use all to indicate all permissions and authorize any operation
#Database name. Table name: used to specify the name of the database and table for authorized operation, where the wildcard "*" can be used
#'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 and ip address. The wildcard "%" can also be used to represent all addresses in a region or network segment
#Identified by: used to set the password string used by the user when connecting data. If "identified by" is omitted when creating a new user, the user's password will be empty.

2. Permission classification

Authorized user permissions are all privilege
 
insert(insert data)
select(Query data)
update(Update table data)
delete(Delete data in table)
create(Creating libraries, tables)
drop(Delete library, table)
refernces
index((indexing)
alter(Change table properties)
create temp orary tables
lock tables(Lock table)
execute
create view(Create view
show view(Show attempts)
create routine(Create stored procedure)
alter routine(Modify stored procedure)
event(Events)
trigger on(Create trigger)

3. View permissions

show grants for 'user name'@'Source address'；

4. Delete permissions

revoke jurisdiction on Database name.Table name from 'user name'@'Source address'；