preface
Many programmers regard SQL as a monster. SQL is one of the few declarative languages that operates in a completely different way than the command line languages, object-oriented programming languages, or even functional languages we are familiar with (although some people think that SQL is a functional language as well).
The relational model
The Relational Model was proposed by Dr. E.f. Codd in 1970 and is based on the concept of relationships in set theory. Both real-world entity objects and the relationships between them are represented by relationships. The relationship we see in a database system is a two-dimensional Table, consisting of rows and columns. Therefore, a relational table can also be said to be a collection of data rows.
The relational model consists of data structure, relational operation and integrity constraint. 1. The data structure in relational model is relational table, including base table, derived table (query result) and virtual table (view).
2. Common relational operations include add, Delete, Modify, and Query (CRUD), using SQL language. Query operations are the most complex, including Selection, Projection, Union, Intersection, Exception, and Cartesian product.
3. Integrity constraints are used to maintain data integrity or meet the requirements of business constraints, including entity integrity (primary key constraints), referential integrity (foreign key constraints), and user-defined integrity (non-empty constraints, unique constraints, check constraints, and default values).
Our topic today is relational manipulation language, which is SQL.
For collection
SQL (Structured Query Language) is the standard language for operating relational databases. SQL is very close to English and very simple to use. It is designed with non-technical requirements in mind. We usually only need to explain the desired results (What) and leave the process of data processing (How) to the database management system. So SQL is a real programming language for people! Let’s look at the various operation statements of the relationship; The goal is to show you that SQL is a set-oriented programming language that operates on sets and results in sets. 📝 In relational databases, relationships, tables, and collections often represent the same concepts.
SELECT
Here is a simple query:
SELECT employee_id, first_name, last_name, hire_date
FROM employees;
Copy the codeIt is used to query information about employees from the employees table. Obviously, we all know that FROM is followed by a table (relationship, set). Not only that, the result of the entire query statement is also a table. So, we can use the query above as a table:
SELECT *
FROM (SELECT employee_id, first_name, last_name, hire_date
FROM employees) t;
Copy the codeThe query in parentheses is called the derived table, and we have given it an alias called T. Again, the entire query result is a table; That means we can continue to nest, which is boring. Let’s look at another example from PostgreSQL:
-- PostgreSQL
SELECT *
FROM upper('sql');
| upper |
|-------|
| SQL |
Copy the codeUpper () is an uppercase conversion function. Its presence in the FROM clause means that its result is also a table, just a special table with 1 row and 1 column. The SELECT clause is used to specify the fields to be queried. It can include expressions, function values, and so on. SELECT is called Projection in relational operations. This should make sense to you by looking at the diagram below.
In addition to SELECT, there are some common SQL clauses.
Search the public number program Yuan Xiaowan, to obtain the relevant interview questions and answers.
WHERE is used to specify the conditions for data filtering, which is called Selection in relational operations, as shown below:
ORDER BY is used to sort the query results, as shown below:
In short, SQL can perform a variety of data operations, such as filtering, grouping, sorting, limiting the number of; The objects and results of all these operations are relational tables.
One of these relational operations is special: grouping.
GROUP BY
The GROUP BY operation is different from other relational operations because it changes the structure of the relationship. Look at the following example:
SELECT department_id, count(*), first_name
FROM employees
GROUP BY department_id;
Copy the codeThe purpose of this statement is to count the number of employees by department, but there is a syntax error: first_name cannot appear in the query list. The reason is that when grouped by departments, each department contains multiple employees; Unable to determine which employee’s name needs to be displayed, this is a logical error. So, GROUP BY changes the structure of the set element (the data row), creating an entirely new relationship. The diagram of group operation is as follows:
Nonetheless, the result of GROUP BY is still a set.
UNION
The most obvious manifestation of SQL’s set-oriented feature is UNION, INTERSECT, and EXCEPT/MINUS.
These set operators combine two sets into one, so they must satisfy the following conditions:
1. The number and order of fields in both sets must be the same.
2. The types of the corresponding fields in both sets must match or be compatible.
Specifically, UNION and UNION ALL are used to calculate the UNION of two sets, returning the data that appears in either the first query result or the second query result. The difference is that UNION excludes duplicate data from the results, while UNION ALL retains duplicate data. Here is a schematic of the UNION operation:
The INTERSECT operator is used to return the common part of both sets, that is, the data that appears in both the first query result and the second query result, and to exclude duplicate data from the result. INTERSECT operation diagram is as follows:
The EXCEPT or MINUS operators are used to return the difference set between two sets, that is, records that appear in the first query result but are not in the second, and to exclude duplicate data from the result. A schematic diagram of the EXCEPT operator is as follows:
In addition, the DISTINCT operator is used to eliminate duplicate data, that is, to exclude duplicate elements from a set. The concept of relationships in 📝SQL comes from set theory in mathematics, so UNION, INTERSECT, and EXCEPT come from the UNION (UNION \cup∪), intersection (∩\cap∩), and difference set (∖\setminus∖) operations in set theory, respectively. Note that sets in set theory do not allow duplicate data, but SQL does. Therefore, a set in SQL is also called a multiset; Both multiple sets and sets in set theory are unordered, but SQL can sort the query results with the ORDER BY clause.
JOIN
In SQL, not only are entity objects stored in relational tables, but the relationships between objects are also stored in relational tables. Therefore, when we want to retrieve this related data, we need to use another operation: a JOIN query. Search the public number program Yuan Xiaowan, to obtain the Java interview questions and answers. Common SQL connection search types include inner join, outer join, cross join and so on. Among them, the outer connection can be divided into the left outer connection, the right outer connection and the whole outer connection. Inner Join returns data from two tables that meet Join conditions. The principle of Inner Join is shown in the figure below:
Left Outer Join = Left Outer Join = Left Outer Join For the right table, return the data that satisfies the join condition; Returns null if not. The principle of the left outer connection is shown in the figure below:
Right Outer Join = Right Outer Join = Right Outer Join For the left table, return the data that satisfies the join condition, or null if none. The right outer join and the left outer join are interchangeable, and they are equivalent:
t1 RIGHT JOIN t2
t2 LEFT JOIN t1
Copy the codeFull Outer Join = left Outer Join + right Outer Join; Returns a null value for data in both tables that does not meet join conditions. The principle of full external connection is shown in the figure below:
Cross connections are also called Cartesian products. A cross join of two tables is equivalent to a pairwise combination of all the rows of one table and all the rows of the other, with the number of rows multiplied by the number of rows of the two tables. The principle of cross connection is shown in the figure below:
📝 Other types of connections include SEMI JOIN and ANTI JOIN.
Set operations combine two sets into a larger or smaller set; Join queries transform two collections into a larger or smaller collection, while obtaining a larger element (more columns). Many times collection operations can be performed by joining queries, such as:
SELECT department_id
FROM departments
UNION
SELECT department_id
FROM employees;
Copy the codeIs equivalent to:
SELECT COALESCE(d.department_id, e.department_id)
FROM departments d
FULL JOIN employees e ON (e.department_id = d.department_id);
Copy the codeNow that we’ve covered many examples of queries, let’s look at other data operations.
DML
DML stands for data manipulation language, that is, insert, update, and Delete. Here is an example insert statement:
CREATE TABLE test(id int); INSERT INTO test(id) VALUES (1),(2),(3); -- Oracle INSERT INTO test(id) (SELECT 1 AS id FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL);Copy the codeWe INSERT three records with an INSERT statement, or we INSERT a relational table with three records. Because UNION ALL returns a relational table. VALUES also specifies a relational table that supports the following statements in SQL Server and PostgreSQL:
SELECT *
FROM (
VALUES(1),(2),(3)
) test(id);
Copy the codeAs we said earlier, FROM is followed by a relational table, so VALUES are the same here. Because we often insert single records, we don’t realize that we’re actually working in tables.
Similarly, UPDATE and DELETE statements operate on relational tables; It’s just that we’re used to saying update a row or delete a few records.
The last
I have compiled a copy here: SQL related materials documents, Mybatis related materials, Mysql documents, brain maps, Spring family bucket series, Java systematic materials, (including Java core knowledge, interview topics and 20 years of the latest Internet real questions, e-books, etc.) friends in need can pay attention to the public number [Cheng Xuyuan Xiaowan] can be obtained.