Database Backup Introduction:
Once the database has deleted the data, it will disappear forever. Therefore, DELETE
you should back up the database before executing the statement, just in case you want to retrieve the deleted data.
MySQL provides a very useful tool for backing up or dumping the MySQL database locally on the server.
MySQLDump is a program provided by MySQL that can be used to dump a database for a database or to transfer a database to another database server.
The dump file contains a set of SQL statements that are used to create database objects. Additionally, mysqldump
you can use it to generate a CSV, delimiter, or XML file.
Database backup operations:
To back up the MySQL database, the database must first exist in the database server, and you can also access the server. If you do not have a remote Desktop, you can log on to the remote server by using SSH or Telnet .
-U [Username]–p[password] [database_name] > [DUMP_FILE.SQL]
[username]
: A valid MySQL user name.
[password]
: A valid password for the user. Note that -p
there are no spaces between the passwords.
[database_name]
: The name of the database to be backed up
[dump_file.sql]
: The dump file to generate.
execute the preceding command, and all database structures and data will be exported to a [dump_file.sql]
dump file.
Database Backup instance:
-u root –p123456 yiibaidb > D:\worksp\bakup\yiibaidb001.sql
Back up Database structure operations only:
If you only want to back up the database structure and don't need to back up your data,you only need to add an option-no--u [username]–p[password]–no-data [database_name] > [dump_ File.sql] Instances: C:\Users\Administrator> Mysqldump-u root–p123456 -no-data yiibaidb > D:\worksp\bakup\ Backup002.sql
To back up database data only:
There is a situation where you want to refresh data in a staging and development system, so the data in these systems is the same as the production system.
In this case, you only need to export the data from the production system and import it into a temporary or development system. To implement backup-only data.
Options that you can use mysqldump
-no-create-info:
mysqldump-u [username]–p[password]–no-create-info [database_name] > [dump_file.sql] Example: Mysqldump–u root– P123456–no-create-info yiibaidb > D:\worksp\bakup\backup003.sql
Multiple databases are backed up to a folder:
-U [Username]–p[password] [dbname1,dbname2,...] > [all_dbs_dump_file.sql] If you want to back up all databases in the database server, use the option - all--u [username]–p[password]–all-database > [All_dbs_dump_file.sql]
Mysql-delect statements
Feature Description: Delete data from a table.
mysql-delect Syntax: DELETE from table_namewhere condition;
First, specify the table () that deletes the data table_name
.
Second, use conditions to specify WHERE
the row records to be deleted in the clause. If the row matches the criteria, the row records are deleted. if the WHERE
clause is omitted, the DELETE
statement deletes all rows in the table .
Mysql-delect and limit
If you want to limit the number of rows to be deleted, use the limit clause with order BY. DELETE from Table_nameorder by C1, C2, ... LIMIT Row_count;
mysql-on DELETE Cascade Statement
Description: ON DELETE CASCADE
for a foreign key reference operation, you can automatically delete data from the child table when data is deleted from the parent table.
FOREIGN key:
If the public keyword is the primary key in a relationship, then this common keyword is called the foreign key of the other relationship.
Students (student number, name, gender, professional number, age, monitor) in the relationship, "school Number" is the primary key, "monitor" attribute indicates the student's class monitor's number, it refers to this relationship "student number" attribute, so "monitor" is a foreign key.
Suppose there are two tables: buildings ( buildings
) and rooms ( rooms
).
In the database model, each building has one or more rooms. However, each room belongs to only one building. The room does not exist without a building.
The first step is to create buildings
the table, creating the statement as follows:
Use TestDB; CREATE TABLE Buildings ( building_no INT PRIMARY KEY auto_increment, building_name VARCHAR (255) not NULL, address VARCHAR (255) not NULL) ENGINE=innodb DEFAULT Charset=utf8;
The second step is to create rooms
the table with the following creation statement:
Use TestDB; CREATE TABLE Rooms ( room_no INT PRIMARY KEY auto_increment, room_name VARCHAR (255) not NULL, Building_no INT not NULL, FOREIGN KEY (building_no) REFERENCES Buildings (building_no) on DELETE CASCADE) ENGINE=innodb DEFAULT Charset=utf8;
NOTE: Add a clause at the end of the foreign key constraint definition ON DELETE CASCADE
.
The third step , insert some data into the buildings
table, insert the following statement:
INSERT into buildings (building_name,address) VALUES (' Hainan mansion ',' Haikou, No. 1234th, Guo Xing Avenue , (' Wanda Shuicheng ',' Haikou Datong Road, No. 1200th ');
The fourth step is to insert some data into the rooms
table, following the INSERT statement:
INSERT into Rooms (room_name,building_no) VALUES ('Amazon', 1), ('WarGuest ', 1), ('Office of the CEO', 1 ), ('Marketing', 2), (' Showroom', 2);
Fifth Step , delete the buildings numbered 2 :
DELETE from buildings WHERE building_no = 2;
Tips for finding tables affected by the MySQL on DELETE cascade operation
Use the testdb
delete rule in the sample database (because the above two tables are built on top of the testdb
database) to CASCADE
find the table associated with the building table.
Use information_schema; SELECT table_namefrom referential_constraintswhere 'testdb' ' buildings' CASCADE '
Mysql-delect Join Statement
Feature Description: To delete data from multiple tables.
The MySQL allow DELETE
clause in the statement is used INNER JOIN
to delete the matching row records from the table and the other table.
T1
T2
Delete Row records from and tables that meet the specified criteria
= T2.key # T1.key = T2.key
Specifies the criteria that will be deleted T1
and the T2
matching row records between the tables. WHERE
the conditions in the WHERE condition # Clause determine T1
and the T2
Row records in the table to be deleted.
MySQL Delete with INNER JOIN clause
To delete an associated other table
Use delete ... The INNER join statement deletes Row records for ref= 1 in the T2 table: Delete T1, t2 from T1 INNER JOIN= t1.id WHERE= 1;
MySQL Delete with LEFT JOIN clause
DELETE
Use clause in LEFT JOIN
a statement to delete a row record in a table (left table) that does not match the other table (right table).
First step: Use the DELETE statement with the LEFT JOIN clause to clean up the customer data. DELETE customers from customers left JOIN = orders.customernumber WHERE ordernumber is NULL; Second step: by querying customers without any orders, use the following query to verify the deletion: SELECT c.customernumber, c.customername, ordernumberfrom Customers C left JOIN = o.customernumberwhere ordernumber is NULL;
mysql-Delete data (delect)