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