Shulou(Shulou.com)05/31 Report--

Editor to share with you an example of advanced operation of MySQL database, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Article catalogue

Advanced operation of data table

Preparation: install the MySQL database

I. Clone table

Method one

Method two

Clear the table and delete all the data in the table

Method one

Method two

Third, create temporary tables

Fourth, create foreign key constraints

Six common constraints in MySQL

5. Database user management

1. Create a new user

2. View user information

3. Rename the user

4. Delete a user

5. Modify the password of the currently logged-in user

6. Modify the passwords of other users

7. The solution to forgetting the root password

VI. Database user authorization

1. Grant permissions

2. View permissions

3. Revoke the authority

Advanced operation of data table

Preparation: install the MySQL database

One-click deployment of Shell script-source code compilation and installation of MySQL

Create database CLASS;use CLASS;create table TEST (id int not null,name char (20) not null,cardid varchar (18) not null unique key,primary key (id)); insert into TEST (id,name,cardid) values (1 recordzhangsanqie 123123'); insert into TEST (id,name,cardid) values (2meme Lisiqian pamphlet 1231231'); insert into TEST (id,name,cardid) values (3memewangwujia 12312312'); select * from TEST

I. Clone table

Generate the data records of the data table into a new table

Method one

Example: create table TEST01 like TEST;select * from TEST01;desc TEST01;insert into TEST01 select * from TEST;select * from TEST01

Method two

Example: create table TEST02 (select * from TEST); select * from TEST02

Clear the table and delete all the data in the table

Method one

Delete from TEST02

# after DELETE empties the table, the returned result contains deleted record entries; when DELETE works, it deletes record data row by row; if there is a self-growing field in the table, after deleting all records using DELETE FROM, the newly added record will continue to add to the record after the original largest record ID

Example: create table if not exists TEST03 (id int primary key auto_increment,name varchar (20) not null,cardid varchar (18) not null unique key); show tables;insert into TEST03 (name,cardid) values ('zhangsan','11111'); select * from TEST03;delete from TEST03;insert into TEST03 (name,cardid) values (' lisi','22222'); select * from TEST03

Method two

Example: select * from TEST03;truncate table TEST03;insert into TEST03 (name,cardid) values ('wangwu','33333'); select * from TEST03

After # TRUNCATE cleans the table, no deleted entries are returned. When TRUNCATE works, the table structure is re-established as is, so TRUNCATE is faster than DELETE in emptying the table. After TRUNCATE TABLE is used to empty the table, ID will re-record it from 1.

Third, create temporary tables

After the temporary table is created successfully, the temporary table created cannot be seen using the SHOW TABLES command, and the temporary table is destroyed after the connection exits. You can also perform operations such as additions, deletions, modifications and queries before exiting the connection, such as manually deleting temporary tables using the DROP TABLE statement.

CREATE TEMPORARY TABLE table name (field 1 data type, field 2 data type [,...] [, PRIMARY KEY (primary key name)]); for example: create temporary table TEST04 (id int not null,name varchar (20) not null,cardid varchar (18) not null unique key,primary key (id)); show tables;insert into TEST04 values (1 minute hawks, 12345'); select * from TEST04

Fourth, create foreign key constraints

Ensure the integrity and consistency of the data

Definition of foreign key: if the same property field x is the primary key in table 1, but not in table 2, field x is called the foreign key in table 2.

Understanding of primary key tables and foreign key tables:

1. A table with a common key as its primary key (parent table, primary table)

2. The table with the public key as the foreign key is the foreign key table (from table, appearance)

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

Example: create table TEST04 (hobid int (4), hobname varchar (50)); create table TEST05 (id int (4) primary key auto_increment,name varchar (50), age int (4), hobid int (4)); alter table TEST04 add constraint PK_hobid primary key (hobid); alter table TEST05 add constraint FK_hobid foreign key (hobid) references TEST04 (hobid)

Example: add data records insert into TEST05 values (1); insert into TEST04 values (1); insert into TEST05 values (1).

Example: drop table TEST04;drop table TEST05;drop table TEST04

Note: if you want to delete a foreign key constraint field

Delete the foreign key constraint first, and then delete the foreign key name. It is not demonstrated here.

Show create table TEST05;alter table TEST05 drop foreign key FK_hobid;alter table TEST05 drop key FK_hobid;desc TEST05

Six common constraints in MySQL

Primary key constraint primary key Foreign key constraint foreign key non-null constraint not null unique constraint unique [key default value constraint default self-increasing constraint auto_increment

5. Database user management

1. Create a new user

CREATE USER 'user name' @ 'Source address' [IDENTIFIED BY [PASSWORD] 'password']

'user name': specify the user name to be created

'Source address': specify the hosts on which newly created users can log in, in the form of IP address, network segment and hostname, local users can use localhost, and any host is allowed to log in with the available wildcard%

Password: if you use a plaintext password, enter the password directly and it will be automatically encrypted by Mysql when it is inserted into the database

-if you use an encrypted password, you need to use SELECT PASSWORD ('password') first; get the ciphertext, and then add PASSWORD 'ciphertext' to the statement

-if the "IDENTIFIED BY" section is omitted, the user's password will be empty (not recommended)

Example: create user 'zhangsan'@'localhost' identified by' 123123 selection password ('123123'); create user 'lisi'@'localhost' identified by password' * E56A114692FE0DE073F9A1DD68A00EEB9703F3F1'

2. View user information

The created users are saved in the user table of the mysql database

USE mysql;SELECT User,authentication_string,Host from user

3. Rename the user

RENAME USER 'zhangsan'@'localhost' TO' wangwu'@'localhost';SELECT User,authentication_string,Host from user

4. Delete a user

DROP USER 'lisi'@'localhost';SELECT User,authentication_string,Host from user

5. Modify the password of the currently logged-in user

SET PASSWORD = PASSWORD ('abc123'); quitmysql-u root-p

6. Modify the passwords of other users

SET PASSWORD FOR 'wangwu'@'localhost' = PASSWORD (' abc123'); use mysql;SELECT User,authentication_string,Host from user

7. The solution to forgetting the root password

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

Vim / etc/ my.cnf [mysqld] skip-grant-tables # added to login mysql without using authorization table systemctl restart mysqldmysql # to log in directly

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

UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD ('112233') where user='root';FLUSH PRIVILEGES;quit then delete the skip-grant-tables in the / etc/my.cnf configuration file and restart the mysql service. Mysql-u root-p112233

VI. Database user authorization

1. Grant permissions

GRANT statement: specifically used to set the access rights of database users. The GRANT statement creates a new user when the specified user name does not exist, and the GRANT statement is used to modify the user information when the specified user name exists. GRANT permission list ON database name. Table name TO 'username' @ 'source address' [IDENTIFIED BY 'password']; # permission list: used to list the various database operations authorized to use, separated by commas, such as "select,insert,update". Use "all" to indicate all permissions and can authorize any operation. # Database name. Table name: the name of the database and table used to specify the authorized operation, where the wildcard "*" can be used. * for example, use "kgc.*" to indicate that the object authorizing the operation is all tables in the kgc database. # 'user name @ source address': used to specify the user name and the client address that is allowed to access, that is, who can connect and where to connect. The source address can be a domain name, an IP address, or you can use the "%" wildcard to indicate all addresses in an area or network segment, such as "% .lic.com", "192.168.184.%", and so on. # IDENTIFIED BY: used to set the password string that the user uses to connect to the database. When you create a new user, if you omit the "IDENTIFIED BY" section, the user's password will be empty.

# allows the user wangwu to query the data records of all tables in the CLASS database locally, but forbids querying the records of tables in other databases.

Example: GRANT select ON CLASS.* TO 'wangwu'@'localhost' IDENTIFIED BY' 123456 wangwu quitters MySQL-u MySQL-p123456show databases;use information_schema;show tables;select * from INNODB_SYS_TABLESTATS

# allow user wangwu to connect to mysql remotely locally and have all permissions.

Quit;mysql-u root-p112233GRANT ALL PRIVILEGES ON *. * TO 'wangwu'@'localhost' IDENTIFIED BY' 123456 Flo privileges;quitmysql-u wangwu-p123456create database SCHOOL

2. View permissions

SHOW GRANTS FOR user name @ source address; for example: SHOW GRANTS FOR 'wangwu'@'localhost'

3. Revoke the authority

REVOKE permission list ON database name. Table name FROM user name @ source address; for example: quit;mysql-u root-p112233SHOW GRANTS FOR 'wangwu'@'localhost';REVOKE SELECT ON "CLASS". * FROM' wangwu'@'localhost';SHOW GRANTS FOR 'wangwu'@'localhost'

# USAGE permission can only be used for database login and cannot perform any operation; USAGE permission cannot be reclaimed, that is, REVOKE cannot delete users.

Flush privileges; above is all the contents of this article entitled "examples of Advanced Operation of MySQL Database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.