A little bit of database-----Day 7 field related and federated result set

A little bit of database-----Day 7 field related and federated result set

----Reprint Please specify the Source: Coder-pig

I. Field-related

1. Constant fields:

What is a constant field?

A : It is a field that does not exist in the table, but the result of the query looks like it actually exists ~

Use the following sql:

SELECT ' Xiao Ming Supermarket ', ' Xiangzhou shop ', Pname,pprice from t_product

Run sql:

Here the ' Xiao Ming supermarket ' and ' Xiangzhou shop ' are constant fields!!

2. Calculation between fields

Calculations can also be made between fields, such as the total price of a product:

SELECT Pname,fnum,pprice,pprice * Fnum as ' merchandise price ' from t_product

Run sql:

You can use constant fields as normal fields, or you can write more complex points.

For example, filter out product information with a total price greater than 5000:

SELECT *,pprice * Fnum as ' Total price ' from t_product WHERE pprice * fnum > 5000

Run sql:

3. A common example:

One of the items in the table all +1

For example, the number of items in the table +1

UPDATE t_product SET fnum = fnum + 1; SELECT * from T_product

Run sql:

two. Federated result set1. What is a federated result set?

A: That is, we want to put multiple query result sets in a result set to display, for example:

Here's the SQL:

SELECT PName, ' 1. Goods ' from t_product; SELECT FName, ' 2. Personnel ' from T_worker;

Run SQL:

Now we want to put the result set of these two queries into a result set!

This is the federated result set!

2. How to implement result set syndication:

Union can be used with Union operator !, such as the above SQL only need to add Union:

SELECT PName, ' 1. Goods ' from T_productunionselect FName, ' 2. Personnel ' from T_worker;

Run SQL:

This will be displayed on a result set, and more than the Union keyword, it is very simple!

3. Precautions for using union:

1) Each result set must have the same number of columns

2) The data type of each corresponding column of each result set needs to be the same or can be converted to the same data type

3) If the contents of a column with a record in multiple result sets are the same, then repeat , only one

If you don't want to do this, you can use the all operator after the UNION operator!

that UNION All !

4. Examples of the use of federated result sets:

Federated result sets more often when you make a report , when we want to show two data that is not directly related to the same

A report, the use of Union can bring us convenience! Here are some simple examples to use!

Example: Check the highest and lowest price of the product, and the most highly paid and lowest salary of the employee:

SELECT ' Maximum price per item ', Max (pprice) from T_productunionselect ' lowest unit price ', MIN (pprice) from T_productunionselect ' employee's highest salary ', Max ( fsalary) from T_workerunionselect  ' employee minimum wage ', MIN (fsalary) from T_worker;

Run SQL:

A little bit of database-----Day 7 field related and federated result set