Usage and parsing of computational fields for SQL learning

First, the calculated field

1. Data stored in database tables is generally not the format required by the application. In most cases, data in a data table needs to be processed two times. Here are a few examples.

(1), we need a field that displays both the company name and the company address, but the two information is stored in columns in different tables.

(2), provinces, cities, zip codes are stored in different columns (in most cases), but the mailing label printing program needs to print them as a field in the appropriate order and format.

(3), the column data is mixed in case, but the report program needs to put all the programs in uppercase.

(4), the Item Order table stores the price and quantity of the item, does not store the total price of each item, but the total price of the item is required to print the invoice. In the above example, the data stored in the table is not required by the application, and we need to retrieve it from the data, and convert, calculate, or format the data. Rather than retrieving it, and initializing it to the client.

At this point, the calculated field can be useful, and the calculated field does not actually exist with the data table, and the calculated field is run within the SELECT statement created.

Field: A field is basically the same as a column, often used interchangeably, but a database column is generally called a column, whereas a term field is usually used with a calculated field.

It is important to note that only the database knows which columns in the SELECT statement are actual table columns, and those columns are calculated fields. From a client (such as a Web application), the data for the computed field is returned in the same way as the data from the other columns.

Many of the data transformations and formatting that can be done within a SQL statement can be done directly in the client application. However, it is generally much faster to complete these operations on the database server than in the client!

2. Stitching Fields

The values of two or more fields are stitched together to form a field. The code is as follows

Select *  from Dbo.student

Now there is a requirement that a report requires a field package Student_deatils contains the student table name, age, gender, as the basic information of the student, the following is the resolution code:

Select + ' ('+ssex+','+CONVERT(  varchar, Sage)+' as from dbo.student

OK, complete the requirements

Note: Different DBMS (database management systems) use the string concatenation operator differently, ACESSS and SQL Server use ' + ', DB2, Oracle, PostgreSQL, SQLite, and open Office base using ' | | '. Special functions must be used in MySQL and mariadb.

3. Calculating fields for arithmetic operations

Another common use of calculated fields is to perform arithmetic calculations on the retrieved data. The following code:

Select *  from Tb_order

Now a reporting program requires the order number of each order, the actual price (unit price * discount), and the total price, the settlement code is as follows:

Select  as order number, price* as the actual value, prices*Quantity* as from DBO.TB _order

OK, complete the demand!

Usage and parsing of computational fields for SQL learning