Getting Started with SQL queries (previous)

Introduction

The SQL language is an easy-to-learn yet 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 does not have an abstract process and a reasonable step, which is likely to be "stuck" when writing specific SQL query statements to solve a particular problem, 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 from the relational database theory published by E.j.codd in 1970, so SQL is for relational database service. And for SQL query, refers to get the subset of data from the database , this sentence seems to listen to some obscure is it, the following through a few pictures briefly explain:

If there is only one table in a database, and if all the data is (taken from the Adventurework sample database):

For the concept of subsets, look:

Finally, the subset is as follows:

In fact, no matter how complex the query in SQL, can be abstracted into the process as above.

pre-conditions for exact queries

For proper access to the subset of data that is needed. In addition to the need to think correctly and turn ideas into corresponding SQL query statements. It is also important to have a good design of the database . Good design Here I mean the design of the database conforms to the business logic and at least the third paradigm , for the implementation of the third paradigm, this is my personal view, for a brief introduction to the paradigm, please see my blog: Database paradigm those things. If the database is poorly designed, there is a lot of redundancy, and there are a lot of exceptions to the information in the database, even if the SQL is written correctly, you cannot get accurate results.

two different ways, one result

In SQL, getting the same subset of data can be a different idea or a different SQL statement, because SQL originates from relational database theory, and relational database theory derives from mathematics, and when you think about how to construct query statements, you can abstract two ways:

1. Relational Algebra method

The idea of the relational algebra method is to step through the database and obtain the desired result finally.

such as the following statement:

Select Name,department,agefrom Employeewhere age>20

The idea of relational algebra describes the following statement: projecting (selecting columns) The table employee table, and then filtering the results to get only the results of age greater than 20.

2. Algorithms for relational Modeling

Compared with relational algebra, relational algorithms are more concerned with the conditions of obtaining data. The above SQL can be described as a relational algorithm: I want to get the names, departments and ages of all employees older than 20.

Why two methods are needed

Neither of the above methods is required for simple query statements. You can think of it with your feet. The problem is that many query statements can be very complex. For the relational algorithm, more attention is paid to the conditions of the information being extracted, but for the relational algebra method, more attention is given to how to extract the specific information. In short, the relational algorithm represents "what", while the relational algebra method expresses "how". Some of the ideas in SQL statements are relational algebra, some of which are relational algorithms, and some are a mixture of two ideas.

For some query cases, relational algebra may be simpler, and in other cases, the relational algorithm will appear more straightforward. There are some situations. We need to mix two ideas. So these two ways of thinking are necessary when writing SQL queries.

single-table query

A single-table query is the intermediate state of all queries, and complex queries that are multiple tables can be abstracted into a single-table query after the final connection. So let's start with a single-table query.

Select a subset of columns

Depending on the subset of data above, the selection column is implemented by adding the column name that you want to select after the SELECT statement:

For example, the following database selects a subset of columns by selecting the appropriate column name after the SELECT.

The corresponding SQL statements are as follows:

SELECT [Name]      , [GroupName] from  [adventureworks].[ HumanResources]. [Department]

Select a subset of rows

Selecting a subset of rows is followed by a corresponding constraint after the WHERE clause of the SQL statement, and when the expression following the WHERE clause is true, that is, when the so-called "condition" is met, a subset of the corresponding row is returned.

The operator following the WHERE clause is divided into two categories, the comparison operator and the logical operator , respectively.

The comparison operator compares two data of the same type to return an operator of the Boolean type (bool), in SQL, a total of six 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 can be seen as a combination of comparison operators and logical operators.

The logical operator is to concatenate two Boolean types and return a new operator of the Boolean type, in SQL, where the logical operator is usually connected to the Boolean type returned by the comparison operator to ultimately determine whether the condition is true after the WHERE clause. There are three types of logical operators, with (and), or (or), non-(not).

For example, I would like to choose the second and sixth, in order to illustrate comparison operators and logical operators, you can use the following SQL statement:

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 have precedence, the precedence is from large to small arrangement is the comparison operator > (and) > Non (OR)

Of course, operators can also change precedence by parentheses, for the table above

For without parentheses:

SELECT * from  [adventureworks].[ HumanResources]. [Department]  WHERE departmentid>=1 and departmentid<=3 and departmentid>=5 or departmentid<=7

After the parentheses change the order of operations:

SELECT * from  [adventureworks].[ HumanResources]. [Department]  WHERE Departmentid>=1 and Departmentid<=3 and (departmentid>=5 or departmentid<=7)

very special. Null

If in a user register table, some optional information does not need the user must fill in, then saves in the database is NULL, these null values may cause the data loss when using the operator of the above WHERE clause, for example, an optional information is gender (Gender), assuming the following two conditional clauses :

where gender= "M"

Where NOT (gender= "M")

Because of the existence of null values, the rows returned by these two statements add up to not all the data in the entire table. Therefore, when a null value is taken into account, the conditional clause that follows the where has the possible values from true and false, increased to true, false, and unknown (null). These are the possible answers to our questions 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 make the entire table of data is not lost, here must be in addition to "true", "false" outside the "unknown" This option is included, SQL provides is null to indicate the unknown option:

where Gender is NULL

Adding the above statement will no longer lose data.

Sort Results

The above methods are all about fetching data, and the following is about sorting the subset that is taken out. SQL is ordered by the ORDER BY clause, which is the last clause in the SQL query statement, meaning that no more clauses can be added after the 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 type of data to sort by, and 3 data types can be sorted, respectively:

1. Character
2. Digital
3. Time and date

Characters are sorted alphabetically, numbers are sorted by number size, and time dates are sorted by time.

some of the other relevant View

Views can be thought of as a saved virtual table, or simply as a saved query statement. The benefit of the view is that the view can change depending on the contents of the table being queried by the view, for example, to understand this sentence:

The advantage of using views is that queries can be encrypted and manageable, and performance is said to be optimized (I don't recognize this).

Prevent duplication

Sometimes we don't want to repeat the subset of data that we've taken out, for example, you want to know that some specific employees belong to several departments

SELECT [EmployeeID]      , [DepartmentID] from  [adventureworks].[ HumanResources]. [Employeedepartmenthistory]

The result is meaningless, and SQL provides the DISTINCT keyword to achieve this:

SELECT distinct departmentid from  [adventureworks].[ HumanResources]. [Employeedepartmenthistory]

Aggregation Functions

The so-called aggregation function, for some specific purpose, the same column multiple values are aggregated into one, such as I want to know the maximum age of a group of people can take advantage of Max, for example, I want to know how much a class average test results can be used avg (Result) ...

Summary

This article briefly outlines the principles of SQL queries and simple single-table queries, which are the basic concepts of database queries, and it is essential to understand these concepts for complex queries.

Getting Started with SQL queries (previous)