How does mysql query two tables?

Mysql two-table query method: 1. Use “select field list from table 1, table 2 [where condition]” to query; 2. Use “SELECT field list FROM table 1 keyword JOIN table 2 ON Table1.Field = Table2.Field;” to query.

How does mysql query two tables? The following article will introduce to you the method of multi-table query in mysql. There is a certain reference value, and friends in need can refer to it, and I hope it will be helpful to everyone.

Multi-table joint query

#Create table 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 ('1', 'teaching department');
 INSERT INTO `dept` VALUES ('2', 'Sales Department');
 INSERT INTO `dept` VALUES ('3', 'Marketing Department');
 INSERT INTO `dept` VALUES ('4', 'Personnel Department');
 INSERT INTO `dept` VALUES ('5', 'Dept of Encouragement');

 -- Creator
 DROP TABLE IF EXISTS `person`;
 CREATE TABLE `person` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(50) NOT NULL,
   `age` tinyint(4) DEFAULT '0',
   `sex` enum('Male','Female','Shemale') NOT NULL DEFAULT 'Shemale',
   `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
   `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

 -- Education Department
 INSERT INTO `person` VALUES ('1', 'alex', '28', 'shemale', '53000.00&  #39;, & #39;2010-06-21& #39;, & #39;1& #39;);
 INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', 'Male', '8000.00&  #39;, & #39;2011-02-21& #39;, & #39;1& #39;);
 INSERT INTO `person` VALUES ('3', 'egon', '30', 'Male', '6500.00&  #39;, & #39;2015-06-21& #39;, & #39;1& #39;);
 INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', 'female', '6680.00&  #39;, & #39;2014-06-21& #39;, & #39;1& #39;);

 -- Sales
 INSERT INTO `person` VALUES (& #39; 5 & # 39;, & # 39; crooked & # 39;, & # 39; 20 & # 39;, & # 39; female & # 39;, '  3000.00', '2015-02-21', '2');
 INSERT INTO `person` VALUES ('6', 'star', '20', 'female', '2000.00  ', '2018-01-30', '2');
 INSERT INTO `person` VALUES ('7', 'Gegg', '20', 'Female', '2000.00  ', '2018-02-27', '2');
 INSERT INTO `person` VALUES (& #39; 8 & # 39;, & # 39; Zhou Zhou & # 39;, & # 39; 20 & # 39;, & # 39; Female & # 39;, & # 39;  2000.00', '2015-06-21', '2');

 -- Marketing Department
 INSERT INTO `person` VALUES ('9', 'monthmonth', '21', 'female', '  4000.00', '2014-07-21', '3');
 INSERT INTO `person` VALUES ('10', 'Angel', '22', 'female', '  4000.00', '2015-07-15', '3');

 -- Personnel Department
 INSERT INTO `person` VALUES ('11', ' Zhou Mingyue', '17', 'female', '  5000.00', '2014-06-21', '4');

 -- Department of Encouragement
 INSERT INTO `person` VALUES ('12', 'Cang teacher', '33', 'female', '  1000000.00', '2018-02-21', null);

multi-table query syntax

select field 1, field 2... from table 1, table 2... [where condition]

Note: If not Adding conditions to query directly will result in the following effects, which we call Cartesian product

#Query personnel  and all departmental information
 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 | 1 | python |
 | 1 | alex | 28 | female | 53000 | 1 | 2 | linux |
 | 1 | alex | 28 | Female | 53000 | 1 | 3 |
 | 2 | wupeiqi | 23 | Female | 29000 | 1 | 1 | python |
 | 2 | wupeiqi | 23 | Female | 29000 | 1 | 2 | linux |
 | 2 | wupeiqi | 23 | Female | 29000 | 1 | 3 |
 |3|e� 
 select * from person right join dept on person.did =dept.did;

Effect: exactly the opposite of [left outer join]

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 | 1 | python |
 | 3 | egon | 30 | male | 27000 | 1 | 1 | python |
 | 4 | oldboy | 22 | male | 1 | 2 | 2 | linux |
 | 5 | jinxin | 33 | female | 28888 | 1 | 1 | python |
 | 6 | Zhang Wuji | 20 | Male | 8000 | 3 | 3 |
 | 7 | Linghu Chong | 22 | Male | 6500 | 2 | 2 | linux |
 +----+---------+-----+-----+--------+-----+-----+-  -------+
 7 rows in set

4 Full join query (display all data in the left and right tables)

Full join query: add left and right on the basis of inner join Data not displayed on both sides
Note: mysql does not support the full JOIN keyword
Note: But mysql provides the UNION keyword. Using UNION can indirectly realize the full JOIN function

#Query all data of personnel and departments
 
 SELECT * FROM person LEFT JOIN dept ON person.did = dept.did
 UNION
 SELECT * 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 | 1 | python |
 | 3 | egon | 30 | male | 27000 | 1 | 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 |
 | 8 | Undefeated East | 23 | Female | 18000 | NULL | NULL | NULL |
 | NULL | NULL | NULL | NULL | NULL | NULL | 4 | Christianity |
 +------+----------+------+------+--------+------+-  -----+--------+
 9 rows in set

Note: The difference between UNION and UNION ALL: UNION will remove duplicate data, while UNION ALL will directly display the results

Multi-table query with copy conditions

1. Query the employees who are older than 20 years old and whose salary is less than 40,000 in the teaching department, and arrange them in reverse order of salary. (Requirement: use multi-table joint query and inner join query respectively)

Example

Example

#1. Multi-table joint query method:
 select * from person p1,dept d2 where p1.did = d2.did
     and d2.dname='python'
     and age>20
     and salary 20
     and salary <40000
 ORDER BY salary DESC;

2. Query the maximum salary and minimum salary in each department, and display the department name

select MAX  (salary),MIN(salary),dept.dname from
         person LEFT JOIN dept
             ON person.did = dept.did
  GROUP BY person.did;

Sub-statement query

Sub-query (nested query): check multiple times, multiple select

Note: the first time The query result can be used as the condition or table name of the second query.

The subquery can contain keywords such as IN, NOT IN, ANY, ALL, EXISTS and NOT EXISTS. It can also contain Comparison operators: =, !=, >, <, etc.

1. Use as a table name

select * from (select * from person) as table name;

ps: Everyone needs to pay attention to: there can be multiple such subqueries in a statement. When executing, the innermost brackets (sql statement) has the priority to execute.
Note: the table name after as cannot be quoted ('')

2. Find the maximum The name and salary of the person who pays the salary

1. Find the maximum salary
 select max(salary) from person;
 2. What is the name of the person asking for the maximum salary?
 select name, salary from person where salary=53000;

 merge
 select name,salary from person where salary=(select max(salary) from person);

3. Find the person whose salary is higher than the average salary of all personnel

1. Find the average salary
 select avg(salary) from person;

 2. The name and salary of the person whose salary is greater than the average salary
 select name, salary from person where salary > 21298.625;

 merge
 select name, salary from person where salary >(select avg(salary) from person);

Recommended tutorial: mysql video tutorial

The above is how to query two tables in mysql? For more details, please pay attention to other related articles on 1024programmer.com!

Cheng

The above is how to query two tables in mysql? For more details, please pay attention to other related articles on 1024programmer.com!