SQL language is a simple but powerful language that allows you to quickly get started and write more complex query statements. But for most developers, using SQL to query a database doesn't have an abstract process and a reasonable step, this is likely to lead to a "card" when writing specific SQL query statements to solve specific problems, and this series focuses on some basic theories of SQL queries, as well as abstract ideas for writing query statements.
Introduction to SQL queries
The SQL language originated in the relational database theory published by E.j.codd in 1970, so SQL is serviced for relational databases. For SQL queries, refers to the data from the database to obtain a subset, this sentence seems to listen to some obscure is it, the following through a few pictures to explain briefly:
If there is only one table in a database, then if all the data is as follows (taken from the Adventurework sample database):
And for the concept of subsets, look at the following figure:
Finally, the subsets are as follows:
In fact, SQL, no matter how complex the query, can be abstracted into the process as above.
Preconditions for exact queries
For the correct subset of the data needed. In addition to the need for a correct thinking and the correct way of thinking into the corresponding SQL query statements. It is also important to have a good design of the database. Good design Here I mean the database design conforms to the business logic and at least the third paradigm, for the realization of the third paradigm, this is just my personal point of view, for the simple introduction of the paradigm, please see my blog: Database paradigm those things. If the database design is bad, there are a lot of redundancy, the information in the database has a large number of exceptions, even if the SQL is written correctly, can not achieve accurate results.
two ways, same kind of result
In SQL, getting the same subset of data can be different ideas or different SQL statements, because SQL originates from relational database theory, and relational database theory derives from mathematics, and when you think about how to build a query, you can abstract it into two ways:
1. Relational Algebra method
The idea of relational algebra method is to make a step-by-step operation of the database and finally get the desired result.
For example, the following statement:
Copy Code code as follows:
Select Name,department,age
From Employee
where age>20
A description of the concept of relational algebra The above statement is: projection (select column) operations on the Table employee table, and then filter the results to obtain results that are older than 20.
2. Relational algorithm
Compared with relational algebra, relational algorithms are more concerned with the conditions for obtaining data. The above SQL can be described by a relational algorithm: I want the name, department and age of all employees older than 20.
Why two methods are needed
For simple query statements, neither of these methods is required. You can think of it with your feet. The problem is that many query statements can be very complex. For relational algorithms, it is more concerned with the condition that the information is extracted, and for relational algebra, it's more about how to get specific information. In short, relational algorithms represent "what", whereas relational algebra expresses "how". The ideas that are revealed in SQL statements are sometimes relational algebra, sometimes relational algorithms, and some are mixed in two ways.
Relational algebra may be simpler for some query situations, and for other cases, the relational algorithm will appear more straightforward. There are some cases. We need to mix two ideas. So these two ways of thinking are necessary to write SQL queries.
single-table query
A single table query is the middle state of all queries, and complex queries that are multiple tables can be abstracted into a single table query after the connection is finally made. So let's start with a single table query.
Select a subset of columns
Depending on the subset of the data above, the Select column is implemented by adding the column name that you want to select after the SELECT statement:
For example, the following database implements a subset of the selected columns by selecting the appropriate column names after the select.
The corresponding SQL statement is as follows:
Copy Code code as follows:
SELECT [Name]
, [GroupName]
from [AdventureWorks]. [HumanResources]. [Department]
Select a subset of rows
A subset of the Select row is appended to the WHERE clause of the SQL statement, and a subset of the corresponding row is returned when the expression following the WHERE clause is true, that is, when the so-called condition is met.
The operators following the WHERE clause are divided into two classes, namely comparison operators and logical operators.
The comparison operator is a comparison of two data of the same type. To return the Boolean type (BOOL) operator, in SQL, there are six different comparison operators, equal to (=), less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>= And not equal to (<>), where less than or equal to and greater than or equal to the combination of comparison operators and logical operators.
The logical operator is to concatenate two Boolean types and return a new Boolean-type operator, in which logical operators are typically connected to a Boolean type returned by a comparison operator to eventually determine whether the condition is true or false after the WHERE clause. A logical operator is one of three kinds, with (and), or (or), non (not).
For example, I would like to select the second and sixth, in order to illustrate the comparison operator and the logical operator, you can use the following SQL statement:
Copy Code code as follows:
SELECT [Name]
, [GroupName]
from [AdventureWorks]. [HumanResources]. [Department]
WHERE departmentid>1 and Departmentid<3 or departmentid>5 and departmentid<7
From this we can see that these operators are prioritized, and the precedence is from large to small, comparing operators > (and) > Non (OR)
Of course, operators can also use parentheses to change precedence, for the table above
For non-parenthesis:
Copy Code code as follows:
SELECT *
from [AdventureWorks]. [HumanResources]. [Department]
WHERE departmentid>=1 and departmentid<=3 and departmentid>=5 or departmentid<=7
After adding parentheses to change the order of operations:
Copy Code code as follows:
SELECT *
from [AdventureWorks]. [HumanResources]. [Department]
WHERE Departmentid>=1 and Departmentid<=3 and (departmentid>=5 or departmentid<=7)
Very special null.
If in a user-registered table, some of the optional information is not required to be filled in by the user, and is saved in the database as NULL, which, when taking advantage of the operator after the WHERE clause above, may cause data loss, such as a Gender, assuming that the following two conditional clauses :
Copy Code code as follows:
where gender= "M"
Where NOT (gender= "M")
Because of the existence of null values, the data rows returned by these two statements add up to not all the data in the entire table. Therefore, when the null value is taken into account, the conditional clause at the end of the where has the possible values from true and false, the increment is true, false, and unknown (null). These are the possible answers when we think about some problems in the real world--really, fake, I don't know.
So how do we not lose data in this case, for the above example, how to keep the data of the entire table from being lost, this must include the option "Unknown" except "true", "false", and SQL provides is null to indicate the unknown option:
Where Gender is NULL adds the above statement, no more data is lost.
Sort results
The above methods are all about fetching data, and the following is about sorting the subset of the fetched. SQL is sorted by the ORDER BY clause, which is the last clause in the SQL query statement, which means that no clauses can be added after an ORDER BY clause.
The ORDER BY clause is divided into ascending (ASC) and Descending (DESC), and if you do not specify ascending or descending order, the default is ascending (from small to large), and order by is determined by the data type by which the sort is sorted: 3 data types are available for sorting.
Character
Digital
Time and date
Characters are sorted alphabetically, numbers are sorted according to number size, and time dates are sorted by time.
Some of the other related
View
A view can be viewed as a saved virtual table or as a saved query statement. The benefit of a view is that the view can change depending on the contents of the table that the view is querying for, and for example:
The advantage of using views is that you can encrypt and manage queries, and it is said to optimize performance (which I do not recognize).
Prevent duplication
Sometimes we don't want to repeat the subset of data we take out, like you want to know how many specific employees belong to several departments.
Copy Code code as follows:
SELECT [EmployeeID]
, [DepartmentID]
from [AdventureWorks]. [HumanResources]. [Employeedepartmenthistory]
This result is meaningless, and SQL provides the DISTINCT keyword to achieve this:
Copy Code code as follows:
SELECT distinct DepartmentID
from [AdventureWorks]. [HumanResources]. [Employeedepartmenthistory]
Aggregate functions
The so-called aggregate function, is to aggregate multiple values of the same column into one for a specific purpose, for example, I want to know what the maximum age of a group of people can take advantage of Max, for example, I want to know how much of a class average test score can be done with Avg.
Summarize
This article briefly outlines the principles of SQL query and simple single table query, which is the basic concept of database query, for complex queries, it is necessary to understand these concepts.
