Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the design idea of relational database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

There are many great design ideas and ideas in the computer field, such as:

In Unix, everything is a file.

In object-oriented programming languages, everything is an object.

Relational databases also have their own design ideas: in SQL, everything is related.

Relation model

The Relational Model (Relational model), proposed by Dr. E.F.Codd in 1970, is based on the concept of relations in set theory; both physical objects in the real world and the relationships between them are represented by relationships. The relationship we see in the database system is a two-dimensional table (Table), which consists of rows (Row) and columns (Column). Therefore, it can also be said that a relational table is a collection of data rows.

The relational model consists of three parts: data structure, relational operation and integrity constraints.

The data structure in the relational model is relational tables, including basic tables, derived tables (query results), and virtual tables (views).

Common relational operations include add, delete, modify, and query (CRUD), using the SQL language. The query operation is the most complex, including selection (Selection), projection (Projection), union (Union), intersection (Intersection), difference (Exception), Cartesian product (Cartesian product) and so on.

Integrity constraints are used to maintain data integrity or to meet the requirements of business constraints, including entity integrity (primary key constraints), referential integrity (foreign key constraints), and user-defined integrity (non-null constraints, unique constraints, check constraints, and default values).

Our topic today is the relational manipulation language, or SQL. The sample data used in this article comes from https://blog.csdn.net/horses/article/details/86518676.

Set oriented

SQL (structured query language) is a standard language for operating relational databases. SQL is very close to English and is very easy to use. At the beginning of its design, it takes into account the needs of non-technical personnel, we usually only need to explain the desired results (What), and the data processing process (How) to the database management system. So SQL is the real programming language for people to use!

Next, we analyze the various operation statements of the relationship in detail; the purpose is to let everyone understand that SQL is a set-oriented programming language, its operation object is a collection, and the result of the operation is a collection.

In relational databases, relationships, tables, and collections usually represent the same concept.

SELECT

Here is a simple query statement:

SELECT employee_id, first_name, last_name, hire_date FROM employees

Its purpose is to query employee information from the employees table. Obviously, we all know that FROM is followed by a table (relationship, collection). Not only that, the result of the entire query statement is also a table. So, we can use the above query as a table:

SELECT * FROM (SELECT employee_id, first_name, last_name, hire_date FROM employees) t

The query statement in parentheses is called a derived table, and we give it an alias t. Similarly, the entire query result is a table; this means that we can continue to nest, although it is boring to do so.

Let's look at another example in PostgreSQL:

-- PostgreSQLSELECT * FROM upper ('sql'); | upper | |-| | SQL |

Upper () is an uppercase conversion function. It appears in the FROM clause, meaning that its result is also a table, but a special table with one row and one column.

The SELECT clause is used to specify the fields to be queried, which can contain expressions, function values, and so on. SELECT is called Projection in relational operations, and it should be easier to understand by looking at the diagram below.

In addition to SELECT, there are some commonly used SQL clauses.

WHERE is used to specify the conditions for data filtering, which is called Selection in relational operations. The schematic diagram is as follows:

ORDER BY is used to sort the results of the query, as shown in the following diagram:

In short, SQL can perform a variety of data operations, such as filtering, grouping, sorting, limiting numbers, and so on; the objects of all these operations are relational tables, and the result is also relational tables.

Among these relational operations, there is a special one, which is grouping.

GROUP BY

The GROUP BY operation is different from other relational operations because it changes the structure of the relationship. Take a look at the following example:

SELECT department_id, count (*), first_name FROM employees GROUP BY department_id

The purpose of this statement is to count the number of employees by department, but there is a syntax error that first_name cannot appear in the query list. The reason is that when grouped by department, each department contains multiple employees; it is a logical error to determine which employee's name needs to be displayed.

So, GROUP BY changes the structure of collection elements (data rows) to create a whole new relationship. The schematic diagram of the grouping operation is as follows:

Nevertheless, the result of GROUP BY is still a collection.

UNION

The most obvious embodiment of the set-oriented feature of SQL is UNION (union operation), INTERSECT (intersection operation) and EXCEPT/MINUS (difference operation).

The purpose of these set operators is to merge two sets into one set, so the following conditions need to be met:

The number and order of fields in the collection on both sides must be the same

The types of the corresponding fields in the collections on both sides must match or be compatible.

Specifically, UNION and UNION ALL are used to calculate the union of two sets and return the data that appears in the first query result or the second query result. The difference between them is that UNION excludes duplicate data from the results, and UNION ALL retains duplicate data. Here is a schematic diagram of the UNION operation:

The INTERSECT operator is used to return a common part of two sets, that is, data that appears in both the first query result and the second query result, and excludes duplicate data in the results. The schematic diagram of the INTERSECT operation is as follows:

The EXCEPT or MINUS operator is used to return the difference between two sets, that is, records that appear in the first query result but not in the second query result, and exclude duplicate data in the result. The schematic diagram of the EXCEPT operator is as follows:

In addition, the DISTINCT operator is used to deduplicate data, that is, to exclude duplicate elements from the collection.

The concept of relation in SQL comes from set theory in mathematics, so UNION, INTERSECT and EXCEPT come from ∪\ cup ∪, ∩\ cap ∩ and ∖\ setminus ∖ in set theory, respectively. It should be noted that sets in set theory do not allow duplicate data, but SQL does. Therefore, sets in SQL are also called multiple sets (multiset); multiple sets and sets in set theory are unordered, but SQL can sort query results through the ORDER BY clause.

JOIN

In SQL, not only entity objects are stored in relational tables, but also relationships between objects are stored in relational tables. Therefore, when we want to get these related data, we need to use another operation: join query (JOIN).

The common types of SQL connection check include inner connection, outer connection, cross connection and so on. Among them, the external connection can be divided into left outer connection, right outer connection and full external connection.

Inner join (Inner Join) returns the data in two tables that meet the join conditions. The principle of the inner join is shown in the following figure:

The left outer join (Left Outer Join) returns all the data in the left table; for the right table, returns data that meets the join condition; if not, returns a null value. The principle of the left outer connection is shown in the following figure:

The right outer join (Right Outer Join) returns all the data in the right table; for the left table, returns data that meets the join conditions, or null if not. The right outer connection and the left outer connection are interchangeable, and the following two are equivalent:

T1 RIGHT JOIN t2t2 LEFT JOIN t1

Full outer join (Full Outer Join) is equivalent to a left outer join plus a right outer join, returning all the data in both the left and right tables, and returning null values for data in both tables that do not meet the join conditions. The principle of the full external connection is shown in the following figure:

Cross connections are also known as Cartesian products (Cartesian Product). A cross join between two tables is equivalent to a pairwise combination of all rows of one table and all rows of another table, and the resulting number is multiplied by the number of rows of the two tables. The principle of the cross connection is shown in the following figure:

Other types of connections include semi-join (SEMI JOIN) and reverse connection (ANTI JOIN).

The collection operation merges two collections into a larger or smaller collection; the join query converts the two collections into a larger or smaller collection while obtaining a larger element (more columns). In many cases, collection operations can be achieved through join queries, such as:

SELECT department_id FROM departments UNIONSELECT department_id FROM employees

Equivalent to:

SELECT COALESCE (d.department_id, e.department_id) FROM departments d FULL JOIN employees e ON (e.department_id = d.department_id)

We have introduced many examples of queries, and let's take a look at other data operations.

DML

DML represents the data manipulation language, that is, insert, update, and delete. The following is an example of an insert statement:

CREATE TABLE test (id int);-- INSERT INTO test (id) VALUES (1), (2), (3);-- OracleINSERT INTO test (id) (SELECT 1 AS id FROM DUALUNION ALLSELECT 2 FROM DUALUNION ALLSELECT 3 FROM DUAL)

We inserted three records through an INSERT statement, or a relational table containing three records. Because UNION ALL returns a relational table. VALUES also specifies a relational table, which supports the following statements in SQL Server and PostgreSQL:

SELECT * FROM (VALUES (1), (2), (3) test (id)

As we said earlier, FROM is followed by a relational table, so it's the same with VALUES here. Because we often insert a single record, we don't realize that we are actually operating on a table basis.

Similarly, both UPDATE and DELETE statements operate on a relational table basis; it's just that we're used to updating a row of data or deleting several records.

This is the end of the content of "what is the design idea of relational database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.