Java Learning Summary (16)--mysql database (middle) grouping, nesting, connection query and foreign key and relational table design

A Group queries
1. Syntax: GROUP by Group Field 1[, Group Field 2,.........] [Filter conditions after having a group]
2. Note: The grouping field should match the query field after select, otherwise the query result is meaningless
3. Group queries are often used with aggregate functions
Example: (1) first to build a commodity table (this table as an example for group queries)

(2) Inserting records

(3) Enquiry:
#1. Calculate the average price per unit of commodity

Query Result:

#2. Calculating the average price of commodity types

Query Result:

Two Connection Query
1. Equivalent connection
Syntax: Select field 1, Field 2,........ from table A, table B,....... where join condition [other filter conditions];
2. Internal connection
Syntax: Select field 1, Field 2,......... from table A INNER join table B on Join condition [other filter conditions];
3. External connection
Left Outer connection:
Syntax: Select field 1, Field 2,......... from table A LEFT JOIN Table B on join condition;
Note: The result of the left OUTER join query is that all remaining records in the left table are added in addition to the records in the table that match the criteria.
Right outer connection:
Syntax: Select field 1, Field 2,.......... from table A right join table B on join condition;
Note: The query result for the right outer join is to add all the remaining records in the right table, in addition to returning the eligible records in the table.
Example: (1) Create a table first
Create a school table (parent table)

Create Student tables (child tables)

(2) Add data:

(3) Check the student's name, score and the history of the school where
Equivalent connections:

Query Result:

Internal connection:

Query Result:

(4) Use the left Outer connection to query the Student table and school table related records, the school is the left table.

Query Result:

(5) Use the right outer connection to query the student's table about the school table records, the school table is the right table

Query Result:

Three subquery (nested query)
1. Sub-query refers to an internal query (subquery) nested inside an external query statement, the query result of the subquery is usually the condition of the external query, and the subquery is enclosed in parentheses.
2. Sub-query Common keywords:
In: Determines whether a certain field value is in the specified subquery result set
All: Determines whether a field value satisfies the filter of all records in the subquery result set for the specified operator
Any: Determines whether a field value satisfies any one of the values in the subquery result set of the specified operator
Example: (query case)
(1) Building a table

(2) Adding data

(3) Enquiry
#1. Find employees in the same department as Scott
If there is only one Scott:
Query statement:

Query Result:

If at least one SCOTT (add a scott:insert into EMP VALUES to the table (9527, ' SCOTT ', ' ANALYST ', 7566, ' 1987-05-19 ', 1000,null,10):
Query statement:

Query Result:

#2. Employees with a higher salary than Jones
If there is only one Jones:
Query statement:

Query Result:

If at least one JONES (add a jones:insert into EMP VALUES (9999, ' JONES ', ' MANAGER ', 7839, ' 1981-04-02 ', 2000,null,20):
Query statement:

Query Result:

#3. Employee information for all employees in the department with a salary higher than 30th
Way One:

Query Result:

Way two:

Query Result:

Way three:

Query Result:

#4. Query work and wages exactly the same employee information as ' Martin '
Only one employee is called ' MARTIN ':
Query statement:

Query Result:

At least one ' Martin ' when (manually add a ' Martin ': INSERT into EMP VALUES (8888, ' Martin ', ' clerk ', 7698, ' 1982-09-28 ', 1100,1400,30);):
Query statement (mode one):

Query Result:

#5. Query employee information for which commission is not null:
Query statement:

Query Result:

#6. In the EMP table, ask for the name of each employee and the name of the boss he corresponds to:
Query statement:

Query Result:

Four Foreign key (foreign key)
1. Definition of foreign keys:
[constraint constraint name] foreign key (field name as foreign key) references parent table name (parent table primary key)
2. Role of foreign keys: used to constrain records in a child table to correspond to records of the parent table
Example (supermarket table and employee table, where supermarket table is parent table, Employee table is child table):
Create a supermarket table and display the table structure (parent table):

Create an employee table and display the table structure (child table):

Set the foreign key part of the SQL statement:

Five Design of a one-to-many relational table and design of many-to-many relational tables
1. Associate the parent table's primary key by setting a foreign key on the table so that a record of the parent table can correlate multiple records of the child table.
% is no longer an example, the school table mentioned above is a one-to-many relationship with the student table and the supermarket table and the staff table
2. Design of many-to-many relational tables:
(1) Design of "Many-to-many" relational tables by setting up the intermediate table
(2) The intermediate table has at least two foreign key fields, respectively, associated with the primary key of the two "many" table.
(3) and the combined values of these two foreign-key fields cannot be duplicated (one way is to ensure that their combined values are not duplicated by setting the two fields to the Federated primary key)
(for example, to design many-to-many relational tables for students and courses):
To create a student table:

To create a curriculum:

To create an intermediate table:

Add Data:

Inquire:
#1. The student number 2 chose those courses.
Query statement:

Query Result:

"This summary is complete"

Java Learning Summary (16)--mysql database (middle) grouping, nesting, connection query and foreign key and relational table design