MySQL Learning--sql query statement (connection Query & subquery) (iii)

One: Connection query:

Connection query is to connect two or more tables according to a certain condition, select the required data, the connection query query two or more tables at the same time, when there are fields representing the same meaning in different tables, this field can be used to connect the tables, for example, the student table has Course_ The ID field represents the course number of the course, and the NUM field in the curriculum indicates the course number, so you can make a connection query from the COURSE_ID fields in the student table to the NUM fields in the curriculum, including the inner join query and the outer join query.

1.1 Internal Connection Query

Internal connection query is a common connection query, internal connection query can query two or more tables.

Note: The fields representing the same meaning in both tables can refer to the parent table's primary key and the foreign key of the Word table;

Example: Creating two Sample tables employee and Department

 
mysql>
mysql> show create table department\G
*************************** 1. row ***************************
       Table: department
Create Table: CREATE TABLE `department` (
  `d_id` int(11) DEFAULT NULL,
  `d_name` varchar(20) DEFAULT NULL,
  `function` varchar(20) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `num` int(11) DEFAULT NULL,
  `d_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  `homeaddr` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| num      | int(11)     | YES  |     | NULL    |       |
| d_id     | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
| sex      | char(4)     | YES  |     | NULL    |       |
| homeaddr | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc department;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id     | int(11)     | YES  |     | NULL    |       |
| d_name   | varchar(20) | YES  |     | NULL    |       |
| function | varchar(20) | YES  |     | NULL    |       |
| address  | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

Insert the data required for the experiment:

mysql> select * from employee, department;
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- + ------ + ----------- + ---------- + ------- -------------- +
num | d_id | name | age | sex | homeaddr | d_id | d_name | function | address |
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- + ------ + ----------- + ---------- + ------- -------------- +
1 | 1001 | Zhang San | 26 | Male | Haiding District, Beijing | 1001 | Scientific Research Department |
1 | 1001 | Zhang San | 26 | Male | Haiding District, Beijing | 1002 | Health | Production Products |
1 | 1001 | Zhang San | 26 | Male | Haiding District, Beijing | 1003 | Sales Department | Planning Sales |
2 | 1002 | Li Si | 24 | Female | Changping District, Beijing | 1001 | Scientific Research Department |
2 | 1002 | Li Si | 24 | Female | Changping District, Beijing | 1002 | Health | Production Products |
2 | 1002 | Li Si | 24 | Female | Changping District, Beijing | 1003 | Sales Department | Planning Sales |
3 | 1001 | Wang Wu | 24 | Male | Changsha City, Hunan Province | 1001 | Scientific Research Department |
3 | 1001 | Wang Wu | 24 | Male | Changsha City, Hunan Province | 1002 | Health | Production Products |
3 | 1001 | Wang Wu | 24 | Male | Changsha, Hunan | 1003 | Sales Department | Planning Sales |
4 | 1004 | Aric | 15 | Male | England | 1001 | Scientific Research Department |
4 | 1004 | Aric | 15 | Male | England | 1002 | Health | Production Products |
4 | 1004 | Aric | 15 | Male | England | 1003 | Sales Department |
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- + ------ + ----------- + ---------- + ------- -------------- +
12 rows in set (0.00 sec)

mysql> 

The query results show that the employee table and the d_id field of the Department table represent the department number, and the Employee table and Department table can be queried for inner joins through the d_id field, and a num,name,d_id is queried from the employee table. Several fields of age and sex.

mysql>
mysql> select num, name, employee.d_id, age, sex, d_name, function from employee, department where employee.d_id = department.d_id;
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| num | name | d_id | age | sex | d_name | function |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| 1 | Zhang San | 1001 | 26 | Male | Scientific Research Department |
| 2 | Li Si | 1002 | 24 | Female | Health |
| 3 | Wang Wu | 1001 | 24 | Male | Scientific Research Department |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
3 rows in set (0.00 sec)

mysql> 

  

1.2 External connection Query

Outside the connection query can query two or more tables, the outer join query also need to make a field to connect, when the value of the field is equal, you can query the record, and the field value is not equal to the record can be queried, the outer join query includes the left connection query and the right connection query.

Format: SELECT attribute list from table name 1 left | Right JOIN table name 2 on table Name 1 property = Table Name 2 property;

1.2.1 Left connection query

Use the left connection to query both the employee and Department tables and connect through the d_id field;

mysql>
mysql> select num, name, employee.d_id, age, sex, d_name, function from employee LEFT JOIN department on employee.d_id = department.d_id;
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| num | name | d_id | age | sex | d_name | function |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| 1 | Zhang San | 1001 | 26 | Male | Scientific Research Department |
| 3 | Wang Wu | 1001 | 24 | Male | Scientific Research Department |
| 2 | Li Si | 1002 | 24 | Female | Health |
| 4 | Aric | 1004 | 15 | Male | NULL | NULL |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
4 rows in set (0.00 sec)

mysql> 

1.2.2 Right Connection query

Use the right Connection query employee and department two tables, through the d_id field to connect;

mysql> select num, name, age, sex, department.d_id, d_name, function from employee RIGHT JOIN department on employee.d_id = department.d_id;
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| num | name | age | sex | d_id | d_name | function |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
1 | Zhang San | 26 | Male | 1001 | Scientific Research Department |
| 2 | Li Si | 24 | Female | 1002 | Health |
| 3 | Wang Wu | 24 | Male | 1001 | Scientific Research Department |
| NULL | NULL | NULL | NULL | 1003 | Sales Department |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
4 rows in set (0.00 sec)

mysql> 

1.2.3 Compound Condition Query

In connection with the query, you can also add additional restrictions, through multiple criteria compound query, you can make the query results more accurate.

For example: Query the Employee table and Department table using an inner join, and the age field in the employee table must have a value greater than.

mysql>
mysql> select num, name, employee.d_id, age, sex, d_name, function
    -> from employee, department
    -> where employee.d_id = department.d_id
    -> and age> 24;
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| num | name | d_id | age | sex | d_name | function |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| 1 | Zhang San | 1001 | 26 | Male | Scientific Research Department |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select num, name, employee.d_id, age, sex, d_name, function from employee, department where employee.d_id = department.d_id having age> 24;
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| num | name | d_id | age | sex | d_name | function |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| 1 | Zhang San | 1001 | 26 | Male | Scientific Research Department |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
1 row in set (0.00 sec)

mysql> 

Example: Querying employee and Department tables using an inner join and displaying query results in ascending order of the age field

mysql>
mysql> select num, name, employee.d_id, age, sex, d_name, function
     -> from employee, department
     -> where employee.d_id = department.d_id
     -> order by age ASC;
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| num | name | d_id | age | sex | d_name | function |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
| 3 | Wang Wu | 1001 | 24 | Male | Scientific Research Department |
| 2 | Li Si | 1002 | 24 | Female | Health |
| 1 | Zhang San | 1001 | 26 | Male | Scientific Research Department |
+ ------ + -------- + ------ + ------ + ------ + ----------- + -------------- +
3 rows in set (0.00 sec)

mysql> 

Two: Sub-query

When a subquery is nested in another query statement, the query result of the inner query statement can provide the query condition for the outer query statement, because in a particular case, the condition of a query statement needs to be obtained by another query statement, for example, it is necessary to query the students ' scores from the Student score table. , you must first know which courses are computer students elective, so you must query the computer students elective courses, and then according to these courses to query the computer department students of various subjects, through the subquery, can realize the query between multiple tables, sub-query may include In,not In,any,all, exists and not exsits keywords, subqueries may also contain comparison operators, such as ' = ', '! = ', ' > ', etc.;

2.1 Sub-query with in keyword

mysql> select * from employee
     -> where d_id IN
     -> (select d_id from department);
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
| num | d_id | name | age | sex | homeaddr |
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
| 1 | 1001 | Zhang San | 26 | Male | Haiding District, Beijing |
| 2 | 1002 | Li Si | 24 | Female | Changping District, Beijing |
| 3 | 1001 | Wang Wu | 24 | Male | Changsha City, Hunan Province |
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
3 rows in set (0.00 sec)

mysql> 

Query the records in the employee table, the d_id fields of these records must not appear in the Department table.

mysql>
mysql> select * from employee
     -> where d_id NOT IN
     -> (select d_id from department);
+ ------ + ------ + ------ + ------ + ------ + ---------- +
| num | d_id | name | age | sex | homeaddr |
+ ------ + ------ + ------ + ------ + ------ + ---------- +
| 4 | 1004 | Aric | 15 | Male | England |
+ ------ + ------ + ------ + ------ + ------ + ---------- +
1 row in set (0.00 sec)

mysql> 

2.2 Sub-query with comparison operator

A subquery can use the comparison operator (=,! =,>,<,>=,<=,<>) etc.

Sample Data table

 
mysql> select * from computer_stu,scholarship;
+------+------+-------+-------+-------+
| id   | name | score | level | score |
+------+------+-------+-------+-------+
| 1001 | Lily |    85 |     1 |    90 |
| 1001 | Lily |    85 |     2 |    80 |
| 1001 | Lily |    85 |     3 |    70 |
| 1002 | Tom  |    91 |     1 |    90 |
| 1002 | Tom  |    91 |     2 |    80 |
| 1002 | Tom  |    91 |     3 |    70 |
| 1003 | Jim  |    87 |     1 |    90 |
| 1003 | Jim  |    87 |     2 |    80 |
| 1003 | Jim  |    87 |     3 |    70 |
| 1004 | Aric |    77 |     1 |    90 |
| 1004 | Aric |    77 |     2 |    80 |
| 1004 | Aric |    77 |     3 |    70 |
| 1005 | Lucy |    65 |     1 |    90 |
| 1005 | Lucy |    65 |     2 |    80 |
| 1005 | Lucy |    65 |     3 |    70 |
| 1006 | Andy |    99 |     1 |    90 |
| 1006 | Andy |    99 |     2 |    80 |
| 1006 | Andy |    99 |     3 |    70 |
| 1007 | Ada  |    85 |     1 |    90 |
| 1007 | Ada  |    85 |     2 |    80 |
| 1007 | Ada  |    85 |     3 |    70 |
| 1008 | Jeck |    70 |     1 |    90 |
| 1008 | Jeck |    70 |     2 |    80 |
| 1008 | Jeck |    70 |     3 |    70 |
+------+------+-------+-------+-------+
24 rows in set (0.00 sec)

mysql>

Find the student's number, name and score from the Computer_stu table for the first prize scholarship

 
mysql>
mysql> select id,name,score from computer_stu where score >= (select score from scholarship where level=1);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql>

Check the department table for employees who do not have age 24 years;

 
mysql>  select d_id,d_name from department
    -> where d_id NOT IN
    -> (select d_id from employee where age=24);
+------+-----------+
| d_id | d_name    |
+------+-----------+
| 1003 | 销售部    |
+------+-----------+
1 row in set (0.00 sec)

mysql>

2.3 Sub-query with exists keyword

exists keyword indicates existence, when using the EXISTS keyword, the inner query will not go back to the query record, but return a true and false value, when the return is really the outer query statement query, when the return of false, the outer statement does not query or query no results.

Example: If a record with d_id value of 1003 exists in the department table, the records for the employee table are queried:

mysql>
mysql> select * from employee
     -> where EXISTS
     -> (select d_name from department where d_id = 1003)
     ->;
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
| num | d_id | name | age | sex | homeaddr |
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
| 1 | 1001 | Zhang San | 26 | Male | Haiding District, Beijing |
| 2 | 1002 | Li Si | 24 | Female | Changping District, Beijing |
| 3 | 1001 | Wang Wu | 24 | Male | Changsha City, Hunan Province |
| 4 | 1004 | Aric | 15 | Male | England |
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
4 rows in set (0.00 sec)

mysql> 

  

Example: If there is a record of d_id value 1004 in the Department table, the records of the Emloyee table are queried;

 
mysql>
mysql> select * from employee
    -> where EXISTS
    -> (select d_name from department where d_id=0);
Empty set (0.00 sec)

mysql>

Example: If there is a record of d_id value 1003 in the department table, the record of age greater than 24 in the employee table is queried;

 
mysql>
mysql> select * from employee where age> 24 and EXISTS
     -> (select d_name from department where d_id = 1003);
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
| num | d_id | name | age | sex | homeaddr |
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
| 1 | 1001 | Zhang San | 26 | Male | Haiding District, Beijing |
+ ------ + ------ + -------- + ------ + ------ + ------------ -------- +
1 row in set (0.00 sec)

mysql> 

2.4 Sub-query with any keyword

The Any keyword means that any of these conditions are met, and when the Any keyword is used, the outer query statement can be executed by satisfying any one of the results returned by the inner query statement.

Example: Find out which students can get scholarships:

Mysql>mysql>  SELECT * from C
mysql>
mysql>  select * from computer_stu where score >= ANY
    -> (select score from scholarship);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
7 rows in set (0.00 sec) 

2.5 subqueries with the ALL keyword

The all keyword means that all conditions are met, and when the ALL keyword is used, the outer query statement can be executed only if all the results returned by the Inner query statement are satisfied.

Example: from the Computer_stu table to find out those students can get the first prize scholarship;

 
mysql>
mysql> select * from computer_stu
    -> where score >= ALL
    -> (select score from scholarship);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql>

  

Note: The Any keyword and the ALL keyword are used the same way, but there is a big difference between the use of the Any keyword, as long as it satisfies any of the results returned by the inner query statement, you can execute the outer query statement by this condition, and the ALL keyword is just the opposite, The outer query statement can be executed only if all the results returned by the Inner query statement are satisfied.

Attached: Merge query results (with redo function)

The result of the merge query is to merge the query results of multiple SELECT statements together, in which case it is necessary to combine the results of several SELECT statements, for example, to query all the employee information of company A and Company B, and then merge the results of the two queries together. The Union and UNION ALL keywords are used for the merge operation.

Format: SELECT statement 1

UNION | UNION All

SELECT statement 2

UNION | UNION All

............

Example: Query the value of the d_id field from the Department table and the employee table, and then merge the results together with the Union keyword.

 
mysql>
mysql>
mysql> select d_id from department
    -> UNION
    -> select d_id from employee;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
+------+
4 rows in set (0.00 sec)

mysql>

  

MySQL Learning--sql query statement (connection Query & subquery) (iii)