Shulou(Shulou.com)06/01 Report--

Editor to share with you the case study of mysql two-table query. I hope you will gain a lot after reading this article. Let's discuss it together.

Mysql two-table query method: 1, use "select field list from Table 1, Table 2 [where condition]" for query; 2, use "SELECT Field list FROM Table 1 keyword JOIN Table 2 ON Table 1. Field = Table 2. Field;" to query.

How does mysql query two tables? The following article introduces you to the method of multi-table query in mysql. There is a certain reference value, friends in need can refer to, hope to help you.

Multi-table joint query # create tables and data # create department CREATE TABLE IF NOT EXISTS dept (did int not null auto_increment PRIMARY KEY, dname VARCHAR (50) not null COMMENT 'department name') ENGINE=INNODB DEFAULT charset utf8;# add department data INSERT INTO `dept`VALUES ('1departments,' teaching department'); INSERT INTO `dept`VALUES ('2customers,' sales department'); INSERT INTO `dept`VALUES ('3customers,' marketing department') INSERT INTO `dept` VALUES ('4efficiency,' personnel Department'); INSERT INTO `dept` VALUES ('5efficiency,' encouragement Department');-- create personnel DROP TABLE IF EXISTS `person` CREATE TABLE `person` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (50) NOT NULL, `age`tinyint (4) DEFAULT '0persons, `sex` enum (' male', 'female', 'shemale') NOT NULL DEFAULT 'shemale', `salary`decimal (10L2) NOT NULL DEFAULT '250.00person`, `hire_ date` date NOT NULL, `dept_ id`int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8) -- add personnel data-- Teaching Department INSERT INTO `person`VALUES ('1million,' alex', '28skills,' shemales', '53000.00persons,' 2010-06-21skills,'1'); INSERT INTO `person`VALUES ('2customers,' wupeiqi', '23rd,' male', '8000.00persons,' 2011-02-21employees,'1') INSERT INTO `person` VALUES ('34th,' egon', '30th,' male', '6500.004th,' 2015-06-21th,'1'); INSERT INTO `person` VALUES ('44th,' jingnvshen', '18mm,' female', '6680.00miles,' 2014-06-21th,'1') -- sales department INSERT INTO `person` VALUES ('5million,' crooked', '20mm,' female', '3000.00million,' 2015-02-21mm,'2'); INSERT INTO `person`VALUES ('6mm,' Xing', '20bn,' female', '2000.00miles,' 2018-01-30mm,'2') INSERT INTO `person`VALUES ('7stories,' checkered', '20levels,' women', '2000.00stories,' 2018-02-27posts,'2'); INSERT INTO `person`VALUES ('8numbers,' weeks', '20persons,' women', '2000.00stories,' 2015-06-21posts,'2') -- Marketing Department INSERT INTO `person` VALUES ('9 months, 'month and month','21 girls, 'women', '400. 00 million,' 2014-07-21,'3'); INSERT INTO `person` VALUES ('10 years, 'Angel','22 girls, 'women', '4000.00seconds,' 2015-07-15 years,'3') -- INSERT INTO `person` VALUES of personnel Department ('11Qing,' Zhou Mingyue', '1700,' female', '5000.00million,' 2014-06-21,'4');-- encourage Department INSERT INTO `person` VALUES ('12', 'Miss Cang','33', 'female', '1000000.009,' 2018-02-21, null)

Multi-table query syntax

Select field 1, field 2... From Table 1, Table 2. [where condition]

Note: if you query directly without conditions, the following effect will occur, which we call the Cartesian product.

# query all information about personnel and departments select * from person,dept

Cartesian product formula: number of data in table A * number of data in table B = Cartesian product.

# Cartesian product example mysql > select * from person, dept +-+-+ | id | name | age | sex | salary | did | did | dname | + -+ | 1 | alex | 28 | female | 53000 | 1 | python | | 1 | alex | 28 | female | 53000 | 1 | 2 | linux | | 1 | alex | 28 | female | 53000 | 1 | 3 | Mingjiao | 2 | wupeiqi | 23 | female | 29000 | 1 | python | 2 | wupeiqi | 23 | female | 29000 | 1 | 2 | linux | | | 2 | wupeiqi | 23 | female | 29000 | 1 | 3 | Mingjiao | | 3 | egon | 30 | male | 27000 | 1 | python | 3 | egon | 30 | male | 27000 | 1 | linux | 3 | egon | 30 | male | 27000 | 1 | 3 | Mingjiao | | 4 | oldboy | 22 | male | 1 | 1 | python | 4 | oldboy | 22 | male | 22 | male | | 1 | 2 | 2 | linux | | 4 | oldboy | 22 | male | 1 | 2 | 3 | Mingjiao | | 5 | jinxin | 33 | female | 28888 | 1 | 1 | python | 5 | jinxin | 33 | female | 28888 | 1 | 2 | linux | 5 | jinxin | 33 | female | 28888 | 1 | 3 | Mingjiao | 6 | Zhang Wuji | 20 | male | 8000 | 3 | 1 | python | 6 | 6 | | | Zhang Wuji | 20 | male | 8000 | 3 | 2 | linux | 6 | Zhang Wuji | 20 | male | 8000 | 3 | 3 | Mingjiao | | 7 | Linghu Chong | 22 | male | 6500 | NULL | 1 | python | 7 | Linghu Chong | 22 | male | 6500 | NULL | 2 | linux | 7 | Linghu Chong | 22 | male | 6500 | NULL | 3 | Mingjiao | 8 | Oriental No | Defeat | 23 | female | 18000 | NULL | 1 | python | 8 | Oriental Invincible | 23 | female | 18000 | NULL | 2 | linux | 8 | Oriental Invincible | 23 | female | 18000 | NULL | 3 | Mingjiao | +-+-+ # Query all information about people and departments select * from person Dept where person.did = dept.did

# Note: when querying multiple tables, be sure to find the related fields in the two tables and use them as conditions

Example

Mysql > select * from person,dept where person.did = dept.did +-+-+ | id | name | age | sex | salary | did | did | dname | +-+ -- +-+ | 1 | alex | 28 | female | 53000 | 1 | 1 | python | 2 | wupeiqi | 23 | female | 29000 | 1 | python | | 3 | egon | 30 | male | 27000 | 1 | python | 4 | oldboy | 22 | male | 1 | 2 | 2 | linux | 5 | jinxin | 33 | female | 28888 | 1 | python | 6 | Zhang Wuji | 20 | Male | 8000 | 3 | 3 | Mingjiao | | 7 | Linghu Chong | 22 | male | 6500 | 2 | 2 | linux | +-+ 7 rows in set

Multi-table link query # Multi-table join query syntax (key) SELECT field list FROM Table 1 INNER | LEFT | RIGHT JOIN Table 2ON Table 1. Field = Table 2. Field

1 join query (only display data that meet the criteria)

# query all information about personnel and departments select * from person inner join dept on person.did = dept.did

Effect: you may find that inner join queries and multi-table federated queries have the same effect.

Mysql > select * from person inner join dept on person.did = dept.did +-+-+ | id | name | age | sex | salary | did | did | dname | +-+ -- +-+ | 1 | alex | 28 | female | 53000 | 1 | 1 | python | 2 | wupeiqi | 23 | female | 29000 | 1 | python | | 3 | egon | 30 | male | 27000 | 1 | python | 4 | oldboy | 22 | male | 1 | 2 | 2 | linux | 5 | jinxin | 33 | female | 28888 | 1 | python | 6 | Zhang Wuji | 20 | Male | 8000 | 3 | 3 | Mingjiao | | 7 | Linghu Chong | 22 | male | 6500 | 2 | 2 | linux | +-+ 7 rows in set

2 left outer join query (all data in the left table are displayed first)

# query all information about personnel and departments select * from person left join dept on person.did = dept.did

Effect: all the data in the personnel table will be displayed, while the data in the department table will be displayed only if they meet the conditions, and those that do not meet the conditions will be filled with null.

Mysql > select * from person left join dept on person.did = dept.did +-+-+ | id | name | age | sex | salary | did | did | dname | +-+ -- + | 1 | alex | 28 | female | 53000 | 1 | python | | 2 | wupeiqi | 23 | female | 29000 | 1 | python | | 3 | egon | 30 | male | 27000 | 1 | python | 5 | jinxin | 33 | female | 28888 | 1 | python | 4 | oldboy | 22 | male | 1 | 2 | 2 | | Linux | | 7 | Linghu Chong | 22 | male | 6500 | 2 | linux | | 6 | Zhang Wuji | 20 | male | 8000 | 3 | 3 | Mingjiao | | 8 | Oriental invincible | 23 | female | 18000 | NULL | +-+-- -+-+ 8 rows in set

3 right outer join query (all the data in the table on the right will be displayed first)

# query all information about personnel and departments select * from person right join dept on person.did = dept.did

Effect: just the opposite of [left outer connection]

Mysql > select * from person right join dept on person.did = dept.did +-+-+ | id | name | age | sex | salary | did | did | dname | +-+ -- +-+ | 1 | alex | 28 | female | 53000 | 1 | 1 | python | 2 | wupeiqi | 23 | female | 29000 | 1 | python | | 3 | egon | 30 | male | 27000 | 1 | python | 4 | oldboy | 22 | male | 1 | 2 | 2 | linux | 5 | jinxin | 33 | female | 28888 | 1 | python | 6 | Zhang Wuji | 20 | Male | 8000 | 3 | 3 | Mingjiao | | 7 | Linghu Chong | 22 | male | 6500 | 2 | 2 | linux | +-+ 7 rows in set

4 full join query (displays all data in the left and right tables)

Full join query: add data that is not displayed on the left and right sides on the basis of internal connection

Note: mysql does not support the fully connected full JOIN keyword

Note: however, mysql provides the UNION keyword. Full JOIN function can be realized indirectly by using UNION.

# query all data of personnel and departments SELECT * FROM person LEFT JOIN dept ON person.did = dept.didUNIONSELECT * FROM person RIGHT JOIN dept ON person.did = dept.did

Example

Mysql > SELECT * FROM person LEFT JOIN dept ON person.did = dept.did UNION SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did +-+ | id | name | age | sex | salary | did | did | dname | + -+ | 1 | alex | 28 | female | 53000 | 1 | 1 | python | 2 | wupeiqi | 23 | female | 29000 | 1 | python | 3 | egon | 30 | male | 27000 | 1 | python | 5 | jinxin | 33 | female | 28888 | 1 | 1 | python | 4 | Oldboy | 22 | male | 1 | 2 | 2 | linux | 7 | Linghu Chong | 22 | male | 6500 | 2 | 2 | linux | 6 | Zhang Wuji | 20 | male | 8000 | 3 | 3 | Mingjiao | 8 | Oriental Invincible | 23 | female | 18000 | NULL | 4 | Christianity | +- -+-+ 9 rows in set

Note: the difference between UNION and UNION ALL: UNION removes duplicate data, while UNION ALL displays the results directly

Replication condition multi-table query

1. Find out the employees of the teaching department who are more than 20 years old and whose salary is less than 40000, arranged in reverse order. (requirement: use multi-table federated query and inner join query respectively)

Example

# 1. Multi-table joint query method: select * from person p1 dept D2 where p1.did = d2.did and d2.dnameplate query python 'and age > 20 and salary 20 and salary, 21298.625; merge select name,salary from person where salary > (select avg (salary) from person) After reading this article, I believe you have a certain understanding of the case study of mysql two-table query. I would like to know more about it. Welcome to follow the industry information channel. Thank you for your reading!

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.