Niugu news publishing system (2) query tables using Stored Procedures

Many people are familiar with the database. The earliest way to query records from the database table is to use the SQL statement string. This is also the SQL language we used to operate the database table. At that time, we also read the stored procedure, I just don't understand what the Stored Procedure means. Now it's just a simple application, just a little bit.

The stored procedure is something that completes a specific function. Similar to a function, it is a collection of program code.

The following describes how to learn, master, and understand stored procedures through specific applications in a system. At the same time, learning is also a step-by-step and step-by-step learning. From the process of understanding, and application, the learning knowledge lies in the application and can only be flexibly applied.

Concept of stored procedure:

Stored Procedure is a set of SQL statements for specific functions. It is compiled and stored in the database. You can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. Create statement:

1. For example, create a stored procedure and query several latest news items from "News table" category and "News table" News (the descriptions of these two tables are already in the previous blog)

The above Code uses a foreign key Association to query the latest news from the category and news tables. N and C are the aliases of the table (alias ).

The red box indicates the inner join. In addition, there are also cross join, Outer Join (left Outer Join, right Outer Join, complete Outer Join) and Other types. Internal join is commonly used,

The internal join format is:

Data Table 1
Inner join data table 2 on join expression

Returns all matched rows in two tables.

2. Create a stored procedure to query hot news based on the number of comments.

To apply stored procedures, you must be familiar with SQL query statements. The syntax is as follows:

The box contains keywords that are not commonly used but are very important. The meanings of these keywords are as follows:

ALL: indicates that columns with the same values can be displayed in the query results. All is the default distinct of the system. It indicates that only one of the columns is displayed if the values in the query results are the same. For the distinct option, null
The value is considered to be the same. Top N [percent]: Specifies the first n rows of data in the query result. If the percent keyword is specified, the first n rows of data in the query result are returned.

Group_by_expression: Specifies the grouping condition group_by_expression. It is usually a column name, but cannot be the column alias.

The orderby clause specifies the sorting method of the query results.

ASC (ascending): indicates that the query results are listed in ascending order. This is the default value of DESC (descending): indicates that the query results are listed in descending order.

The stored procedure plays an important role in the database systems of big points. Essentially, it is a collection of SQL statements and flow control statements. The storage process generates Execution Methods During computation, so it will be executed quickly when it is re-run later.

We can use it more quickly, especially when executed between web-servers.

When is it appropriate to use stored procedures?