(vii) MySQL data operation DQL: Multi-table Query 2

Source: Internet
Author: User

(1) Preparation environment 1) Create an employee table
mysql> create table company.employee6(    -> emp_id int auto_increment primary key not null,    -> emp_name varchar(10),    -> age int,    -> dept_id int);mysql> insert into employee6(emp_name,age,dept_id) values    -> (‘tom‘,19,200),    -> (‘jack‘,30,201),    -> (‘alice‘,24,202),    -> (‘robin‘,40,200),    -> (‘natasha‘,28,204);
2) Create a departmental table
mysql> create table company.department(    -> dept_id int,    -> dept_name varchar(100));mysql> insert into department values (200,‘hr‘), (201,‘it‘), (202,‘sale‘), (203,‘fd‘);
(2) Cross connection

Generate Cartesian product with no matching criteria
Syntax: Select table 1. field 1, table 1. Field 2, table 2. Field 1 from Table 1, table 2;

Mysql> Select Employee6.emp_name,employee6.age,employee6.dept_id,department.dept_name from Employee6,department ;+----------+------+---------+-----------+| Emp_name | Age | dept_id | Dept_name |+----------+------+---------+-----------+|   Tom |     19 | 200 | HR | |   Tom |     19 | 200 | it | |   Tom |     19 | 200 | Sale | |   Tom |     19 | 200 | FD | |   Jack |     30 | 201 | HR | |   Jack |     30 | 201 | it | |   Jack |     30 | 201 | Sale | |   Jack |     30 | 201 | FD | |   Alice |     24 | 202 | HR | |   Alice |     24 | 202 | it | |   Alice |     24 | 202 | Sale | |   Alice |     24 | 202 | FD | |   Robin |     40 | 200 | HR | |   Robin |     40 | 200 | it | |   Robin |     40 | 200 | Sale | |   Robin |     40 | 200 | FD | |   Natasha |     28 | 204 | HR | |   Natasha |     28 | 204 | it | |   Natasha |     28 |204 | Sale | |   Natasha |     28 | 204 | FD |+----------+------+---------+-----------+
(3) Inner connection: Only matching rows are connected according to the same field in the two tables

Syntax: Select table 1. field N, table 2. field n from table 1, table 2 table 1. field = table 2. Field
Connect based on the dept_id of the employee table and the dept_id of the departmental table, matching only the same rows dept_id

mysql> select employee6.dept_id,employee6.emp_name,employee6.age,department.dept_name from employee6,department where employee6.dept_id = department.dept_id;+---------+----------+------+-----------+| dept_id | emp_name | age  | dept_name |+---------+----------+------+-----------+|     200 | tom      |   19 | hr        ||     201 | jack     |   30 | it        ||     202 | alice    |   24 | sale      ||     200 | robin    |   40 | hr        |+---------+----------+------+-----------+
(4) External connection

Syntax: Select field List from table 1 left|right join table 2 on table 1. field = table 2. Field

1) left side connection: Displays all values in the left table, regardless of the match on the right table
mysql> select emp_id,emp_name,age,dept_name from employee6 left join department on employee6.dept_id = department.dept_id;+--------+----------+------+-----------+| emp_id | emp_name | age  | dept_name |+--------+----------+------+-----------+|      1 | tom      |   19 | hr        ||      4 | robin    |   40 | hr        ||      2 | jack     |   30 | it        ||      3 | alice    |   24 | sale      ||      5 | natasha  |   28 | NULL      |+--------+----------+------+-----------+
2) Right connection of outer connection: All values in the right table will be displayed, regardless of the table in the left
mysql> select emp_id,emp_name,age,dept_name from employee6 right join department on employee6.dept_id = department.dept_id;+--------+----------+------+-----------+| emp_id | emp_name | age  | dept_name |+--------+----------+------+-----------+|      1 | tom      |   19 | hr        ||      2 | jack     |   30 | it        ||      3 | alice    |   24 | sale      ||      4 | robin    |   40 | hr        ||   NULL | NULL     | NULL | fd        |+--------+----------+------+-----------+
(5) Compound Condition connection Query

The EMPLOYEE6 and department tables are queried in a connected way, and the age field value in the Employee6 table must be greater than 25, sort

Mysql> Select Emp_id,emp_name,age,dept_name from employee6,department where employee6.dept_id = department.dept_id and age >25;+--------+----------+------+-----------+| emp_id | Emp_name | Age |      Dept_name |+--------+----------+------+-----------+| 4 |   Robin | 40 |      HR | | 2 |   Jack | 30 | It |+--------+----------+------+-----------+2 rows in Set (0.00 sec) mysql> Select Emp_id,emp_name,age,dept_name From employee6,department where employee6.dept_id = department.dept_id and age >25 ORDER by age;+--------+----------+- -----+-----------+| emp_id | Emp_name | Age |      Dept_name |+--------+----------+------+-----------+| 2 |   Jack | 30 |      it | | 4 |   Robin | 40 | HR |+--------+----------+------+-----------+2 rows in Set (0.00 sec) mysql> Select Emp_id,emp_name,age,dept_name From employee6,department where employee6.dept_id = department.dept_id and age >25 order by age desc;+--------+-------- --+------+-----------+|emp_id | Emp_name | Age |      Dept_name |+--------+----------+------+-----------+| 4 |   Robin | 40 |      HR | | 2 |   Jack | 30 | It |+--------+----------+------+-----------+
(6) Sub-query

A subquery is a query statement that is nested within another query statement. The query result of the inner query statement can provide the query condition for the outer query statement.

1) Sub-query with in
mysql> select * from employee6 where dept_id in (select dept_id from department);+--------+----------+------+---------+| emp_id | emp_name | age  | dept_id |+--------+----------+------+---------+|      1 | tom      |   19 |     200 ||      2 | jack     |   30 |     201 ||      3 | alice    |   24 |     202 ||      4 | robin    |   40 |     200 |+--------+----------+------+---------+4 rows in set (0.00 sec)
2) Sub-query with comparison operator
mysql> select dept_name from department where dept_id in (select dept_id from employee6 where age >25); +-----------+| dept_name |+-----------+| it        || hr        |+-----------+2 rows in set (0.00 sec)

(vii) MySQL data operation DQL: Multi-table Query 2

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.