Mysql database advanced operation

_ Rui_ ao 2022-02-13 07:07:11 阅读数:631

mysql database advanced operation

MySQL Database advanced operation

preparation : install MySQL database

MySQL Database first experience

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,'zhangsan','123123');
insert into TEST(id,name,cardid) values (2,'lisi','1231231');
insert into TEST(id,name,cardid) values (3,'wangwu','12312312');
select * from TEST;

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-A67woGGa-1639905188812)(https://gitee.com/rui_RCA/cloudimage/raw/master/img/image-20211219122936595.png)]

One 、 Clone table

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

Method 1

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

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-HQQ77TIs-1639905188813)(https://gitee.com/rui_RCA/cloudimage/raw/master/img/image-20211219140810473.png)]

Now you can see that , Data records have also been copied

Method 2

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

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-igxs6RTN-1639905188813)(https://gitee.com/rui_RCA/cloudimage/raw/master/img/image-20211219141052560.png)]

CREATE TABLE New table (SELECT * FROM Old table )
This method will oldtable All the contents of the table are copied, but one of the worst aspects of this method is that there are no old tables in the new table primary key、Extra(auto_increment) Equal attribute .

Two 、 Clear the table , Delete all data in the table

Method 1

delete from TEST02;
#DELETE After emptying the table , There are deleted record entries in the returned result ;DELETE When working, delete record data line by line ; If there is a self growing field in the table , Use DELETE FROM After deleting all records , The newly added record will be changed from the original largest record ID Continue to write the record automatically later

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-QcYqZf3p-1639905188814)(https://gitee.com/rui_RCA/cloudimage/raw/master/img/image-20211219141559995.png)]

 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;

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-6pvaMtya-1639905188814)(https://gitee.com/rui_RCA/cloudimage/raw/master/img/image-20211219142208914.png)]

You can see the method , Cannot be completely cleared , If there is a self growth field in the table , Use delete from The records added again after deletion will increase from the original maximum self growth id Continue self incrementing and writing records

Method 2

 example :select * from TEST03;
truncate table TEST03;
insert into TEST03 (name,cardid) values ('wangwu','33333');
select * from TEST03;
  • TRUNCATE After emptying the table , No deleted entries are returned ;
  • TRUNCATE When working, the table structure is rebuilt as it is , So in terms of speed TRUNCATE than DELETE Empty the watch, quick ;
  • Use TRUNCATE TABLE After clearing the data in the table ,ID From 1 Start re recording .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-1cK361Rk-1639905188814)(https://gitee.com/rui_RCA/cloudimage/raw/master/img/image-20211219142826523.png)]

You can see that the sub growth field is also cleared , from 1 restart

3、 ... and 、 Create a temporary table

 After the temporary table is created successfully , Use SHOW TABLES The command cannot see the created temporary table , The temporary table will be destroyed after the connection exits . If before exiting the connection , You can also perform operations such as adding, deleting, modifying, and querying , For example, use 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 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,'haha','12345');
select * from TEST04;

image-20211219144859601

image-20211219144958687

Four 、 Create foreign key constraints

Ensure data integrity and consistency

Definition of foreign key : If the same attribute field x In Table 1 is the primary key , In Table 2, it is not the primary key , Then the fields x The foreign key called table 2 .

Understanding of primary key table and foreign key table :

  1. Tables with public keywords as primary keys are primary key tables ( Parent table 、 Main table )
  2. Tables with public keywords as foreign keys are foreign key tables ( From the table 、 appearance )

Be careful : The fields of the primary 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 , The fields of the master-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);

image-20211219150743002

 example : Add data records
insert into TEST05 values (1,'zhangsan','20',1);
insert into TEST04 values (1,'sleep');
insert into TEST05 values (1,'zhangsan',20,1);

image-20211219151217563

 example :drop table TEST04;
drop table TEST05;
drop table TEST04;

deleted , You need to delete the line from the table , Delete the main table , Because the master table is not necessarily associated with only one slave table , Foreign key constraints ensure data integrity , Uniformity

image-20211219151620358

notes : If you want to delete the foreign key constraint field , Remove foreign key constraints from lines , Then delete the foreign key name

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

MySQL Common in 6 Species constraints

Primary key constraint primary key
Foreign key constraints foreign key
Non empty constraint not null
Unique constraint unique [key
Default constraint default
Self increasing constraint auto_increment

5、 ... and 、 Database user management

1. A new user

CREATE USER ' user name '@' Source address ' [IDENTIFIED BY [PASSWORD] ' password '];
  • ‘ user name ’: Specify the user name that will be created

  • ‘ Source address ’: Specify which hosts the newly created user can log on to , You can use IP Address 、 Network segment 、 The form of host name , Available to local users localhost, Allow any host to log in. Wildcards are available %

  • ‘ password ’: If plaintext password is used , Direct input ’ 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 ciphertext , Then add... To the statement PASSWORD ‘ Ciphertext ’;

    ------ If you omit “IDENTIFIED BY” part , Then the user's password will be empty ( Not recommended )

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

image-20211219152934911

2. View user information

The created user is saved in MySQL Database user table

USE mysql;
SELECT User,authentication_string,Host from user;

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-xB04HoF0-1639905188817)(…/…/…/AppData/Roaming/Typora/typora-user-images/image-20211219153220748.png)]

3. Rename user

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

image-20211219162914108

4. Delete user

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

image-20211219163308894

5. Change the password of the current login user

SET PASSWORD = PASSWORD('abc123');
# Modify the login password of the current user
quit
mysql -u root -p
# land

image-20211219164153493

6. Change other user passwords

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

7. forget root Password solution

1. modify /etc/my.cnf The configuration file , Log in directly to... Without a password mysql

vim /etc/my.cnf
[mysqld]
skip-grant-tables
# add to , Log in mysql Do not use authorization form 
systemctl restart mysqld
mysql # Direct login 

2. Use update modify root password , Refresh database

UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('112233') where user='root';
FLUSH PRIVILEGES;
quit
And then /etc/my.cnf In the configuration file skip-grant-tables Delete , And restart mysql service .
mysql -u root -p
112233

6、 ... and 、 Database user authorization

1. Grant authority

GRANT sentence : 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 for use , Separated by commas , Such as “select,insert,update”. Use “all” Indicates all permissions , You can authorize any operation .
# Database name . Table name : The name of the database and table used to specify the authorization operation , You can use wildcards “*”.* for example , Use “kgc.*” The object representing the authorization operation is kgc All the tables in the database .
#' user name @ Source address ': Used to specify the user name and the client address to which access is allowed , Who can connect 、 Where can I connect . The source address can be a domain name 、IP Address , You can also use “%” wildcard , Represents all addresses in an area or network segment , Such as “%.lic.com”、“192.168.184.%” etc. .
#IDENTIFIED BY: Used to set the password string used by users when connecting to the database . When creating a new user , If you omit “IDENTIFIED BY” part , Then the user's password will be empty .

# Allow users wangwu Query locally CLASS Data records of all tables in the database , But it is forbidden to query the records of tables in other databases .

 example :
GRANT select ON CLASS.* TO 'wangwu'@'localhost' IDENTIFIED BY '123456';
quit;
mysql -u wangwu -p
123456
show databases;
use information_schema;
show tables;
select * from INNODB_SYS_TABLESTATS;

# Allow users wangwu Connect locally and remotely mysql , And have all permissions .

quit;
mysql -u root -p112233
GRANT ALL PRIVILEGES ON *.* TO 'wangwu'@'localhost' IDENTIFIED BY '123456';
flush privileges;
quit
mysql -u wangwu -p123456
create database SCHOOL;

2. View permissions

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

3. Revoke authority

REVOKE Permission list ON Database name . Table name FROM user name @ Source address ;
example :quit;
mysql -u root -p112233
SHOW 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 , You can't do anything ;USAGE Permissions cannot be reclaimed , namely REVOKE Cannot delete user .

flush privileges;
copyright:author[_ Rui_ ao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130707090117.html