1, hierarchical model:

①There is only one node without a parent node (this node is called the root node).

②Nodes other than the root node have and only one biparent node.

Records in a hierarchical model can only be organized as a collection of trees and not as a collection of arbitrary graphs. In the hierarchical model, the organization of records is no longer a jumbled diagram, but a "long" tree.

2, mesh model:

①Allow more than one node has no parents.

2) A node can have more than one parent node.

Data in a mesh model is represented as a collection of records, and connections between data are represented by links (which can be thought of as pointers). Records in a database can be organized into arbitrary sets.

3. Relational model: ?

The relational model uses a collection of tables to represent data and links between data.

Each table has multiple columns, each with a unique column name.

In the relational model, a single structure type is used for both the entities abstracted from the objective and the links between entities

1. Unconditional query

Example: Find out all the students' course selections

SELECT st_no, su_no

FROM score

Example: Find out all the students' course selections

From score

From score

For example: find out all the students' course selections. p>

Example: Find all students

SELECT*

FROM student

"*" is a wildcard character that indicates that you are looking for the values of all attributes of the relationship indicated in FROM.

2, conditional query

Conditional query that is, with the WHERE clause query, the object to be queried must meet the conditions given in the WHERE clause.

Example: to find out the student status, class number and score of any class with a score of 70 or more

SELECT UNIQUE student.st_class, student.st_no, student.st_name, student.st_sex, student.st_age, student. score.su_no, score.score

FROM student, score

WHERE score.score>=70 AND score.stno=student,st_no

The use of UNIQUE here is not to remove duplicate rows from the query result set. Duplicate rows are not removed from the query result set, but are removed if DISTINCT is used. In addition, the order of precedence of logical operators is NOT→AND→OR.

Example: Find out the students who failed the exam with course number c02

SELECT st_no

FROM score

WHERE su_no='c02 'AND score<60

3. Sort query

Sort query is a query that arranges the results of a query in ascending (ASC) or descending (DESC) order by a specified attribute, as indicated by the ORDER BY clause.

Example: Find courses that failed and arrange the results in ascending order by course number

SELECT UNIQUE su_no

FROM score

WHERE score<60

ORDER BY su_no DESC

4. Nested query

Nested query is the WHERE clause contains a SELECT clause, it is used for more complex queries across multiple basic tables.

Example: To find the course number c03 and the course score of 80 points or more of the student's number, name

SELECT st_no, st_name

FROM student

WHERE stno IN (SELECT st_no

FROM score< /p>

WHERE stno IN (SELECT st_no

FROM score< /p>

WHERE su_no='c03' AND score>80 )

What needs to be clear here is: when the query involves more than one basic table with a nested query to solve hierarchically, with the structure of the program design features. In the nested query, IN is commonly used predicate. If the user knows exactly that the inner query returns a single value, then the user's request can also be expressed using the arithmetic comparison operator.

5, calculated query

Calculated query refers to the system through the provision of specific functions (aggregation function) in the statement of the direct use of some of the results can only be obtained after calculation. Commonly used functions are:

COUNT (*) calculate the number of tuples

COUNT (column name) for the value of a column to calculate the number of

SUM (column name) for the sum of the value of a column (the value of this column is a numerical value)

AVG (column name) for the value of the average of the value of a column (the value of the value of this column is a numerical value)

MAX () for a column name (column name) for the value of a column (the value of this column is a numerical value)

MAX (column name) for a column (column name) for a column name (column name) for a column value of an average (this column value)

MAX ( column name) for the maximum value in a column

MIN(column name) for the minimum value in a column

Example: find the total number of male students and the average age

SELECT COUNT(*), AVG(st_age)

FROM student

WHERE st_sex= 'male'

Example: count the number of students who have taken a course

SELECT COUNT(DISTINCT st_no)

FROM score

Note: Be sure to include the DISTINCT here, because some students may have taken more than one course, but the statistics can only be counted by 1 person, so use DISTINCT to filter.

Reference: