MySQL multi-Table query-an example basically involves MySQL statements

MySQL Database Advanced query multi-Table query and join Table query

 

Query all emp_name information in the EMP table.

Mysql> select * from EMP where emp_name = 'xiaotian ';

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100005 | Xiaotian | 27 | 4000 | 1979-07-10 | male |

+ -------- + ---------- + --------- + ------------ + --------- +

1 row in SET (0.00 Sec)

 

Query all the information of emp_sal In the EMP table with a salary of more than 5000.

Mysql> select * from EMP where emp_sal> 5000;

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100001 | Hongfeng | 29 | 9000 | male |

| 100002 | Lili | 27 | 8000 | 1979-12-31 | fmale |

+ -------- + ---------- + --------- + ------------ + --------- +

2 rows in SET (0.00 Sec)

 

Queries the EMP table that was born after January 1, January 1, 1978

Mysql> select * from EMP where emp_bir> '2017-01-01 ';

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100005 | Xiaotian | 27 | 4000 | 1979-07-10 | male |

| 100002 | Lili | 27 | 8000 | 1979-12-31 | fmale |

+ -------- + ---------- + --------- + ------------ + --------- +

2 rows in SET (0.00 Sec)

 

Query the EMP table that was born before January 1, December 1, 1979 and has a salary of more than 5000

Mysql> select * from EMP where emp_bir <'2017-12-01 'and emp_sal> 1979;

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100001 | Hongfeng | 29 | 9000 | male |

+ -------- + ---------- + --------- + ------------ + --------- +

1 row in SET (0.00 Sec)

 

2.6.2 field Query

 

CEO: view employee salary

Mysql> select emp_name, emp_sal from EMP;

 

The query result is as follows:

+ ---------- + --------- +

| Emp_name | emp_sal |

+ ---------- + --------- +

| Xiaotian | 4000 |

| Red maple | 9000 |

| Lihu | 8000 |

+ ---------- + --------- +

3 rows in SET (0.00 Sec)

 

View the name, salary, and gender of the person born after January 1, 1978

Mysql> select emp_name, emp_sal, emp_sex from EMP where emp_bir> "1977-12-31 ";

 

The query result is as follows:

+ ---------- + --------- +

| Emp_name | emp_sal | emp_sex |

+ ---------- + --------- +

| Xiaotian | 4000 | male |

| Lihu | 8000 | fmale |

+ ---------- + --------- +

2 rows in SET (0.00 Sec)

 

2.6.3 sort query results

 

Use the order by statement to query the salary order of all employees in the EMP table (from low to high by default-ascending)

Mysql> select * from EMP order by emp_sal;

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100005 | Xiaotian | 27 | 4000 | 1979-07-10 | male |

| 100002 | Lili | 27 | 8000 | 1979-12-31 | fmale |

| 100001 | Hongfeng | 29 | 9000 | male |

+ -------- + ---------- + --------- + ------------ + --------- +

3 rows in SET (0.00 Sec)

 

Use the DESC keyword for sorting from high to low-descending order

Mysql> select * from EMP order by emp_sal DESC;

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100001 | Hongfeng | 29 | 9000 | male |

| 100002 | Lili | 27 | 8000 | 1979-12-31 | fmale |

| 100005 | Xiaotian | 27 | 4000 | 1979-07-10 | male |

+ -------- + ---------- + --------- + ------------ + --------- +

3 rows in SET (0.00 Sec)

 

2.6.4 limit on the number of query results

 

Use Limit to view information about the first two employees in the EMP table:

Mysql> select * from EMP order by emp_sal DESC limit 2;

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100001 | Hongfeng | 29 | 9000 | male |

| 100002 | Lili | 27 | 8000 | 1979-12-31 | fmale |

+ -------- + ---------- + --------- + ------------ + --------- +

2 rows in SET (0.00 Sec)

 

View the information about employees with a salary ranking of 2nd to 3rd:

Mysql> select * from EMP order by emp_sal DESC limit 1, 2;

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100002 | Lili | 27 | 8000 | 1979-12-31 | fmale |

| 100005 | Xiaotian | 27 | 4000 | 1979-07-10 | male |

+ -------- + ---------- + --------- + ------------ + --------- +

2 rows in SET (0.01 Sec)

 

Using the rand () sampling survey, two employees are randomly selected to view their data.

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

 

The result is as follows: (random)

 

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100005 | Xiaotian | 27 | 4000 | 1979-07-10 | male |

| 100001 | Hongfeng | 29 | 9000 | male |

+ -------- + ---------- + --------- + ------------ + --------- +

2 rows in SET (0.01 Sec)

 

2.6.5 combine and rename fields in the query results

 

Mysql> select Concat (emp_id, "", emp_name) from EMP;

 

Query results:

+ ------------------------------ +

| Concat (emp_id, "", emp_name) |

+ ------------------------------ +

| 100005 Xiaotian |

| 100001 Hongfeng |

| 100002 lihu |

+ ------------------------------ +

3 rows in SET (0.00 Sec)

 

Rename the title of the output result with the as keyword

Mysql> select Concat (emp_id, "", emp_name) as info from EMP;

 

The query results are as follows:

+ ---------------- +

| Info |

+ ---------------- +

| 100005 Xiaotian |

| 100001 Hongfeng |

| 100002 lihu |

+ ---------------- +

3 rows in SET (0.00 Sec)

 

2.6.6 Calculation of date Query

 

The year (), month (), and dayofmonth () functions can be used to extract the composition elements of a date.

 

Query Information about employees born in January:

Mysql> select * from EMP where month (emp_bir) = 7;

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100005 | Xiaotian | 27 | 4000 | 1979-07-10 | male |

+ -------- + ---------- + --------- + ------------ + --------- +

1 row in SET (0.00 Sec)

 

You can use the English month to query:

Mysql> select * from EMP where monthname (emp_bir) = "January ";

 

The query result is as follows:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100001 | Hongfeng | 29 | 9000 | male |

+ -------- + ---------- + --------- + ------------ + --------- +

1 row in SET (0.00 Sec)

 

The to_days () function can be used to query the Time of the employees from birth to the current manager, in days

Mysql> select to_days (current_date)-to_days (emp_bir) as livingdays from EMP;

 

The query result is as follows:

+ ------------ +

| Livingdays |

+ ------------ +

| 1, 9425 |

| 1, 10345 |

| 1, 9251 |

+ ------------ +

3 rows in SET (0.00 Sec)

 

Calculate the date after 100 days from now on

Mysql> select date_add (now (), interval 100 day );

 

The query result is as follows:

+ ---------------------------------- +

| Date_add (now (), interval 100 day) |

+ ---------------------------------- +

| 13:56:58 |

+ ---------------------------------- +

1 row in SET (0.00 Sec)

 

Calculate the date after 100 days from now on

Mysql> select date_sub (now (), interval 100 day );

 

The query result is as follows:

+ ---------------------------------- +

| Date_sub (now (), interval 100 day) |

+ ---------------------------------- +

| 14:00:20 |

+ ---------------------------------- +

1 row in SET (0.00 Sec)

 

2.6.7 data statistics

 

Use the count () function to calculate the number of table data (for example, the number of employees in the EMP table)

Mysql> select count (*) from EMP;

 

The query result is as follows:

+ ---------- +

| Count (*) |

+ ---------- +

| 3 |

+ ---------- +

1 row in SET (0.01 Sec)

 

Count the number of 5000 of the total wage

Mysql> select count (*) from EMP where emp_sal> 5000;

 

The query result is as follows:

+ ---------- +

| Count (*) |

+ ---------- +

| 2 |

+ ---------- +

1 row in SET (0.00 Sec)

 

Count the number of male and female employees: (group by statement classification)

Mysql> select emp_sex, count (*) from EMP group by emp_sex;

 

The query result is as follows:

+ --------- + ---------- +

| Emp_sex | count (*) |

+ --------- + ---------- +

| Fmale | 1 |

| Male | 2 |

+ --------- + ---------- +

2 rows in SET (0.01 Sec)

 

Use statistical functions (min (), max (), sum (), AVG ())

Mysql> select

-> Min (emp_sal) as min_salary,

-> MAX (emp_sal) as max_salary,

-> Sum (emp_sal) as sum_salary,

-> AVG (emp_sal) as avg_salary,

-> Count (*) as employee_num

-> From EMP;

 

The query result is as follows:

+ ------------ + -------------- +

| Min_salary | max_salary | sum_salary | avg_salary | employee_num |

+ ------------ + -------------- +

| 4000 | 9000 | 21000 | 7000.0000 | 3 |

+ ------------ + -------------- +

1 row in SET (0.00 Sec)

 

2.6.8 retrieve information from multiple data tables

 

Follow these steps:

1) create a new table dept in the ASB database. The table has two elements:

Dept_id --> varchar (6)

Dept_name --> varchar (10)

2) Insert the following new record in the table EMP:

+ -------- + ---------- + --------- + ------------ + --------- +

| Emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |

+ -------- + ---------- + --------- + ------------ + --------- +

| 100003 | small red | 30 | 8000 | 1976-11-11 | fmale |

+ -------- + ---------- + --------- + ------------ + --------- +

3) In the new table Dept, enter the following record

+ --------- + ----------- +

| Dept_id | dept_name |

+ --------- + ----------- +

| 100005 | MTD |

| 100001 | MTD |

| 100002 | MTD |

| 100003 | HR |

+ --------- + ----------- +

 

Query the names and departments of employees in the EMP and dept tables.

Mysql> select EMP. emp_name, Dept. dept_name from EMP, Dept

-> Where EMP. emp_id = Dept. dept_id;

 

The query result is as follows:

+ ---------- + ----------- +

| Emp_name | dept_name |

+ ---------- + ----------- +

| Xiaotian | MTD |

| Hongfeng | MTD |

| Lili | MTD |

| Xiaohong | HR |

+ ---------- + ----------- +

4 rows in SET (0.00 Sec)

 

Note the following when querying multiple tables:

1). the from clause must provide all the names of the queried tables.

2). specify the name of the table to which a field belongs (for example, emp_id In the EMP table must be expressed as EMP. emp_id)

3). The WHERE clause must specify the query conditions (for example, EMP. emp_id and dept. dept_id are elements of the same meaning)