How to create updates and deletions of multiple tables by MySQL 03/28 Update SLTechnology News&Howtos

How to create updates and deletions of multiple tables by MySQL

2025-03-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article mainly explains "how to create updates and deletions of multiple tables by MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to create updates and deletions of multiple tables by MySQL".

1. Updates and deletions involving multiple tables

Create a test table:

Mysql > create table users1

-> (

-> uid tinyint unsigned

-> uname varchar (255)

-> gid tinyint unsigned

->)

Query OK, 0 rows affected (0.06 sec)

Mysql > create table groups1

-> (

-> gid tinyint unsigned

-> gname varchar (255)

->)

Query OK, 0 rows affected (0.02 sec)

[@ more@] mysql > insert into users1 values (0, 'root', 0)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into users1 values (201,' ggyy', 101)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into users1 values (202, 'ssff', 101)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into groups1 values (0, 'root')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into groups1 values (101, 'guest')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from users1

+-+

| | uid | uname | gid | |

+-+

| | 0 | root | 0 | |

| | 201 | ggyy | 101 | |

| | 202 | ssff | 101 | |

+-+

3 rows in set (0.00 sec)

Mysql > select * from groups1

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 101 | guest |

+-+ +

2 rows in set (0.00 sec)

The following statement adds 10 to the uid of users who belong to the guest group in the users1 table:

Mysql > update users1, groups1 set users1.uid = users1.uid + 10 where users1.gid = groups1.gid and gr

Oups1.gname = 'guest'

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

Mysql > select * from users1

+-+

| | uid | uname | gid | |

+-+

| | 0 | root | 0 | |

| | 211 | ggyy | 101 | |

| | 212 | ssff | 101 | |

+-+

3 rows in set (0.00 sec)

The following statement changes the gid of the guest group in the two tables to 102:

Mysql > update users1, groups1 set users1.gid = 102, groups1.gid = 102 where users1.gid = groups1.gid

And groups1.gid = 101

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

Mysql > select * from users1

+-+

| | uid | uname | gid | |

+-+

| | 0 | root | 0 | |

| | 211 | ggyy | 102 | |

| | 212 | ssff | 102 | |

+-+

3 rows in set (0.00 sec)

Mysql > select * from groups1

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 102 | guest |

+-+ +

2 rows in set (0.00 sec)

However, such a statement will produce the wrong result:

Mysql > update users1, groups1 set users1.gid = 102, groups1.gid = 102 where users1.gid = groups1.gid

And groups1.gname = 'guest'

Query OK, 2 rows affected (0.01sec)

Rows matched: 2 Changed: 2 Warnings: 0

Mysql > select * from users1

+-+

| | uid | uname | gid | |

+-+

| | 0 | root | 0 | |

| | 211 | ggyy | 102 | |

| | 212 | ssff | 101 | |

+-+

3 rows in set (0.00 sec)

Mysql > select * from groups1

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 102 | guest |

+-+ +

2 rows in set (0.00 sec)

Ssff users' gid has not been updated, so I don't quite understand why.

The following statement deletes the records of users who belong to the root group in the users1 table:

Mysql > delete from users1 using users1, groups1 where users1.gid = groups1.gid and groups1.gname ='

Root'

Query OK, 1 row affected (0.00 sec)

Mysql > select * from users1

+-+

| | uid | uname | gid | |

+-+

| | 211 | ggyy | 102 | |

| | 212 | ssff | 102 | |

+-+

2 rows in set (0.02 sec)

Mysql > select * from groups1

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 102 | guest |

+-+ +

2 rows in set (0.00 sec)

The delete statement can be written in the form "delete users1 from users1, groups1 where users1.gid = groups1.gid and groups1.gname = 'root';". Note that the from is preceded by the table to delete the record, followed by several tables involved in the delete operation (in this case, internal joins, which can be other join types).

The following statement deletes the records of users who belong to the guest group in the users1 table and the records of the guest group in the groups1 table.

Mysql > delete from users1, groups1 using users1, groups1 where users1.gid = groups1.gid and groups1.

Gname = 'guest'

Query OK, 3 rows affected (0.00 sec)

Mysql > select * from users1

Empty set (0.02 sec)

Mysql > select * from groups1

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

+-+ +

1 row in set (0.00 sec)

Similarly, the delete statement can be written in the form "delete users1, groups1 from users1, groups1 where users1.gid = groups1.gid and groups1.gname = 'guest';".

two。 Randomly select records

Use the ORDER BY clause in conjunction with the RAND () function to achieve the effect of randomly selecting records in the table:

Mysql > select * from oraleng

+-+

| | ask | answer |

+-+

| | How do you do? | How do you do? |

| How are you? | Fine.Thank you. | |

| What's your name? | My name is Jack Sparrow. | |

| Where are you from? | I'm from maldives. | |

| What's the weather like? | It's fine. | |

| What time is it now? | It's seven o'clock. | |

| What day is it today? | It's Wednesday. | |

+-+

7 rows in set (0.00 sec)

Mysql > select * from oraleng order by rand () limit 1

+-+ +

| | ask | answer |

+-+ +

| How are you? | Fine.Thank you. | |

+-+ +

1 row in set (0.02 sec)

Mysql > select * from oraleng order by rand () limit 1

+-+ +

| | ask | answer |

+-+ +

| What day is it today? | It's Wednesday. | |

+-+ +

1 row in set (0.02 sec)

Mysql > select * from oraleng order by rand () limit 1

+-- +

| | ask | answer |

+-- +

| What's your name? | My name is Jack Sparrow. | |

+-- +

1 row in set (0.02 sec)

Mysql > select * from oraleng order by rand () limit 2

+-+ +

| | ask | answer |

+-+ +

| What time is it now? | It's seven o'clock. | |

| Where are you from? | I'm from maldives. | |

+-+ +

2 rows in set (0.02 sec)

3. Control SELECT behavior

Here are some keywords that can change the behavior of SELECT statements:

DISTINCT: delete the record containing duplicate values in the result set.

SQL_CALC_FOUND_ROWS: calculates the total number of rows that match the query. Not affected by LIMIT, the result can be obtained by calling the FOUND_ROWS function.

SQL_CACHE and SQL_NO_CACHE: specify whether the query results need to be cached.

SQL_BUFFER_RESULT: forces the query results to be stored in a temporary table. This buffering eliminates locking on the queried table.

SQL_BIG_RESULT and SQL_SMALL_RESULT: specify the expected size of the result set. This helps to find the best way to sort and store returned records (based on disk or temporary tables in memory).

SQL_HIGH_PRIORITY: raises the priority of queries that compete with UPDATE, INSERT, and DELETE statements. Queries can be executed quickly on busy database servers.

4. Import and export to and from files

You can use the LOAD DATA INFILE statement to import data from a file into a table, or you can use the SELECT...INTO OUTFILE statement to export records from a table to a file.

1) Delimiter

In the above statement, some clauses and keywords are used to specify the data format in the file.

LINES TERMINATED BY clause: specifies the Terminator of the record. (by default, n represents a new line. )

FIELDS clause: specifies the delimiter of the field. FIELDS is followed by one or more of the keywords TERMINATED BY, ESCAPED BY, ENCLOSED BY, and so on.

TERMINATED BY specifies the Terminator of the field (t by default), ESCAPED BY is used to skip special characters (default is backslash), and ENCLOSED BY specifies the symbol that surrounds the field (none by default).

2) Import data from a file

E:downloadcontact.txt is a text file that contains a set of contact information as follows:

Hebei Unicom Shijiazhuang Branch, Zhang Shaolan, 0311-87052200

Hebei Unicom Cangzhou Branch, Wang Jianrong, 0317-3520079

Baoding Branch of Hebei Unicom, Sun Fengrui, 0312-3075574

Hebei Unicom Langfang Branch, Pang Haijing, 0316-2684535

Hebei Unicom Qinhuangdao Branch, Dai Yanli, 0335-3050172

Now create a table to store these contact information:

Mysql > create table contact

-> (

-> name varchar (20)

-> sex enum ('male', 'female')

-> tel bigint

> email varchar (50)

> company varchar (50)

->)

Query OK, 0 rows affected (0.13 sec)

Use the Load DATA INFILE statement to import data into it:

Mysql > load data infile'Evirtual downloadcontact.txt' into table contact

-> fields terminated by', 'escaped by'-'lines terminated by' rn'

-> (company, name, tel)

Query OK, 46 rows affected (0.02 sec)

Records: 46 Deleted: 0 Skipped: 0 Warnings: 0

Mysql > select * from contact limit 7

+-+

+-+

+-+

7 rows in set (0.00 sec)

The following points are explained:

a. The user who makes the import must have FILE privileges.

b. Replace the "" symbol in the file path with "".

c. When the contents of each part of the file do not match the number or order of the fields in the table, you can specify a list of field names at the end of the LOAD DATA INFILE statement to map each part of the file to the correct field.

Introduces some keywords in the LOAD DATA INFILE statement:

LOCAL: specifies that INFILE is on the client's file system. By default, it is thought to be on the server.

LOW_PRIORITY: delays the execution of the LOAD DATA statement until no other client reads from the table.

IGNORE, REPLACE: when a key of an inserted new record duplicates an existing record, skip the new record or replace the existing record with the new record.

3) Export records to a file

Use the SELECT INTO...OUTFILE statement to export records to the text file contact2.txt:

Mysql > select name, tel, company from contact where name like 'Zhang%'

-> into outfile'Evirtual downloadcontact2.txt'

-> fields enclosed by'"'lines terminated by' rn'

Query OK, 4 rows affected (0.06 sec)

Check the contents of the file:

"Zhang Shaolan" 31187052200 "Hebei Unicom Shijiazhuang Branch"

"Zhang Lei"3125902030"Hebei Telecom Baoding Branch"

"Zhang Dongwang"3155960019"Qian'an Xingyu Trading Co., Ltd"

"Zhang Lei"3123100913"Baoding Enterprise League Information Network Co., Ltd."

The following points are explained:

a. The user who makes the export must have FILE privileges.

b. The export file cannot exist in advance. Otherwise, an error will occur:

ERROR 1086 (HY000): File'Epurl downloadcontact2.txt' already exists

c. For binary data, such as the BLOB type, you can use the INTO DUMPFILE clause instead of the INTO OUTFILE clause. In this way, MySQL will write data to the file in a separate line format (no fields or record Terminator), thus avoiding corruption of binary data.

At this point, I believe you have a deeper understanding of "how MySQL creates updates and deletions of multiple tables". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.