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

Today, I will talk to you about how to achieve multi-table query function in MySQL, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1. View the contents of the first table mytable: mysql > select * from mytable +-+ | name | sex | birth | birthaddr | +-+ | abccs | f | 1977-07-07 | china | | | mary | f | 1978-12-12 | usa | | tom | m | 1970-09-02 | usa | +-2. | Create a second table title (including author, article title, publication date): mysql > create table title (writer varchar (20) not null -> title varchar (40) not null,-> senddate date) Add a record to the table, and the final table reads as follows: mysql > select * from title +-+ | writer | senddate | +-+ | abccs | A1 | 2000-01-23 | | mary | b1 | 1998-03-21 | | abccs | A2 | 2000-12-04 | | tom | C1 | 1992-05-16 | | tom | C2 | 1999-12-12 | +-+ 5 rows in set (0.00sec) 3. Multi-table query now we have two tables: mytable and title. We can use these two tables to make a combined query: in the above example, because the author's name, gender, and article are recorded in two different tables, a combination must be used for the query. You must specify how records in one table match records in other tables. Note: if the writer column in the second table title is also named name (the same as the name column in the mytable table) instead of writer, it must be represented as mytable.name and title.name to show the difference. To give another example, query the author, place of birth, and date of birth of article a2: mysql > select title,writer,birthaddr,birth from mytable,title-> where mytable.name=title.writer and title='a2' +-+ | title | writer | birthaddr | birth | +-+ | a2 | abccs | china | 1977-07-07 | + -+ modify and backup, Batch processing sometimes we have to modify and delete database tables and databases You can use the following methods: 1. Add a column: for example, add a column to the mytable table in the previous example to indicate whether you are single single: mysql > alter table mytable add column single char (1) 2. Change the single record of abccs to "y": mysql > update mytable set single='y' where name='abccs';. Now let's see what happens: mysql > select * from mytable. +-+ | name | sex | birth | birthaddr | single | +- -+ | abccs | f | 1977-07-07 | china | y | | mary | f | 1978-12-12 | usa | NULL | | tom | m | 1970-09-02 | usa | NULL | +-+ 3. Adding a record has already talked about how to add a record. For ease of viewing, repeat this: mysql > insert into mytable-> values ('abc','f','1966-08-17) Query OK, 1 row affected (0.05sec) check it out: mysql > select * from mytable +-+ | name | sex | birth | birthaddr | single | +- -+ | abccs | f | 1977-07-07 | china | y | | mary | f | 1978-12-12 | usa | NULL | | tom | m | 1970-09-02 | usa | NULL | | abc | f | 1966-08-17 | china | n | +-+ 4, Delete a record use the following command to delete a record in the table: mysql > delete from mytable where name='abc' DELETE deletes a record from the table that meets the conditions given by where. Show the result again: mysql > select * from mytable +-+ | name | sex | birth | birthaddr | single | +- -+ | abccs | f | 1977-07-07 | china | y | | mary | f | 1978-12-12 | usa | NULL | | tom | m | 1970-09-02 | usa | NULL | +-+ 5. Delete table: mysql > drop table * (name of Table 1) * the name of Table 2 You can delete one or more tables and use them carefully. 6. Delete the database: mysql > drop database database name; use with care. 7. Database backup: return to DOS: mysql > quit d:\ mysqlbin use the following command to back up the database abccs: mysqldump-- opt abccs > abccs.dbb abccs.dbb is the backup file of your database abccs. 8. Use MySQL in batch mode: first create a batch file mytest.sql, which is as follows: use abccs; select * from mytable; select name,sex from mytable where name='abccs'; runs the following command under DOS: d:mysqlbin mysql

Mytest.out

After reading the above, do you have any further understanding of how to implement the multi-table query function in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.