mysql-Delete data (delect)

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. WHEREthe 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

DELETEUse 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)