SELECT Statement basics

The process of using a SELECT statement to query and extract the necessary data is called a matching query or query.

SELECT queries need to specify what to SELECT and from which (table) to SELECT.

Basic SELECT statement

SELECT <The column name>.FROM <The name of the table>;
Copy the code

The SELECT statement contains two clauses: SELECT and FROM.

A clause is an element of an SQL statement. A clause usually consists of a keyword plus provided data, such as a phrase starting with SELECT or FROM.

The order of columns in the query result is the same as in the SELECT clause

The SELECT clause lists the names of the columns you want to query from the table,

The FROM clause specifies the name of the table FROM which the data is selected.

The SELECT clause can be column names, constants, and computations.

SELECT product_id, product_name, purchase_price
 FROM Product;
Copy the code

Query all columns of the table

The asterisk (*) wildcard character can represent all columns

For example, query all columns:

SELECT * FROM Product;


- the equivalent to
SELECT product_id, product_name, product_type, sale_price,
       purchase_price, regist_date
  FROM Product;
Copy the code

When asterisks are used, columns are usually displayed in the order defined by the TABLE in CREATE TABLE.

Do not use * wildcards in general, retrieving unwanted columns will degrade the performance of the query.

In SQL, you can add line breaks at any word or clause position.

Alias the column

Columns can be aliased using the AS keyword

SELECT product_id AS id, product_name ASCommodity name, purchase_priceASReplenish onr's stock priceFROM Product;

Aliases can also be enclosed in double quotation marks ("). You cannot use single quotes
SELECT product_id AS "id", product_name AS"Commodity Name ", purchase_priceASReplenish onr's stock priceFROM Product;

Alias names can also be written directly without specifying AS
SELECTProduct_id ID, product_name Commodity name, purchase_Price Purchase priceFROM Product;
Copy the code

Results:

Id name | goods | replenish onr's stock price -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- 500 0002 | | 0001 | T-shirt punch | 320 0003 2800 0005 | | | sporting t-shirts pressure cooker | 5000, 0006 Forks | | 0007 | just scrape 0008 | | 790 ball-point pen | 0004 | 8000 | meleeCopy the code

Constant query

You can also write constants in the SELECT clause. Constants are displayed in all rows of the query.

As follows, the first column ‘commodity’ is a string constant, the second column 38 is a numeric constant, and the third column ‘2009-02-24’ is a date constant.

SELECT 'goods' AS string, 38 AS number, '2009-02-24' AS date,
       product_id, product_name
  FROM Product;
Copy the code

The result is:

String | number | date | product_id | product_name -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - | goods | 38 The 2009-02-24 | 0001 | goods | T-shirt 38 0002 | | 2009-02-24 | punch goods | | | 0003 | 2009-02-24 38 sports T-shirt goods | | 0005 | 2009-02-24 38 38 0006 | | 2009-02-24 | | | pressure cooker goods forks goods | | | 0007 | 2009-02-24 38 box grater commodities | | | 0008 | 2009-02-24 38 ball-point pen | | 38 The 0004 | 2009-02-24 | chopper (8 rows)Copy the code

Duplicate entries were removed from the query result

Use DISTINCT in the SELECT clause to remove duplicates.

Remove duplicate data from product_TYPE column:

SELECT DISTINCT product_type
 FROM Product;
Copy the code

The result is:

Product_type -------------- clothing office supplies kitchen appliancesCopy the code

If there are multiple columns followed by DISTINCT, the data in multiple columns is combined to remove duplicates.

SELECT DISTINCT product_type, regist_date
  FROM Product;
Copy the code

The result is:

Product_type | regist_date -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- clothes | kitchenware clothing | | 2008-04-28 | 2009-09-20 office supplies | 2009-09-20 kitchen utensils The office supplies 2009-01-15 | 2009-09-20 | 2009-09-11 kitchen utensilsCopy the code

The DISTINCT keyword can only be used before the first column name.

Select records (filter data) using the WHERE statement

The SELECT statement uses the WHERE clause to specify the conditions for querying data.

SELECT <The column name>.FROM <The name of the table>
 WHERE <Conditional expression>; -- An expression that represents a query condition
Copy the code

Select * from product_type where product_type = “clothes”;

SELECT product_name,product_type
  FROM Product
  WHERE product_type='clothes';
Copy the code

The result is:

Product_name | product_type -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- a T-shirt | | sports T-shirtCopy the code

The query process is to query the records that meet the specified conditions through the WHERE clause, and then SELECT the columns specified by the SELECT statement.

The WHERE clause must follow the FROM clause.

WHERE is used to filter data.

Filtering can be divided into SQL filtering and application filtering (that is, returning too much data and worrying too much about it at the application layer).

Filtering data on the client is not recommended, because the database can filter data more quickly and effectively, especially after optimization. However, processing the database at the application layer not only affects the performance of applications, but also limits the scalability of applications. In addition, the database server will send redundant data across the network, wasting network bandwidth.

annotation

Comments are the part of an SQL statement that identifies instructions or notes.

There are two ways to comment:

  • One-line comment: Write in-- After. Only one line
  • Multi-line comment: Write in/ *and* /Between, can span multiple lines

Limit the results

The default SELECT returns all matching rows. How do you implement returning the first row or a certain number of rows?

  1. PostgreSQL, MySQL, MariaDB, or SQLiteLIMIT n1 OFFSET n2

These databases use the LIMIT clause to LIMIT the number of rows returned

Returns no more than 3 rows of data
SELECT product_id 
FROM Product
LIMIT 3;
Copy the code

OFFSET specifies the number of rows to start the query. The first three rows and the next three rows can be queried.

SELECT product_id 
FROM Product
LIMIT 3 OFFSET 3;
Copy the code

This is the most common “paging” function.

Limit the number of rows returned, usually in conjunction with the Order By sort clause. Because if you don’t specify a sort, the results may not be returned in a fixed order, and specifying the number of rows doesn’t make much sense.

The LIMIT clause comes after WHERE/ORDER BY

Line 0: the first query is line 0, for example, LIMIT 1, OFFSET 1 is the second query. It is safer to think of OFFSET as “OFFSET”, that is, query n rows after OFFSET 0 and query n rows after OFFSET 1, which is easier to understand and more semantic.

MySQL/MariaDB/SQLite

LIMIT n1 OFFSET n2: LIMIT n2,n1

LIMIT 4, OFFSET 3 can be written as LIMIT 3,4

As for the performance of OFFSET, the larger the OFFSET, the slower the query time.

MySQL pagination query is not a pagination query, but a pagination query.

  1. SQL ServeruseTOPorOFFSET

SQL Server commonly uses TOP to specify the maximum number of rows to return. And Access.

Query only the first 3 rows
SELECT TOP 3 product_id FROM Product;
Copy the code

SQL Server 2012 ORDER BY… OFFSET … The FETCH statement, OFFSET and FETCH are used as optional ORDER BY clauses and cannot be used alone.

The syntax is as follows, where FIRST and NEXT are synonyms without distinction and can be used interchangeably. There is no difference between ROW and ROWS. The two are also interchangeable:

ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY
Copy the code

Fetch_row_count can be an integer or an expression that results in an integer

Select data after the first 3 rows (skip the first 3 rows)

SELECT product_id
FROM Product
ORDER BY product_id
OFFSET 3 ROWS; OFFSET 3 ROW is equivalent
Copy the code

Select the data in the first three rows

SELECT product_id
FROM Product
ORDER BY product_id
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY; -- Equivalent to FETCH FIRST 3 ROWS ONLY;

-- equivalent to TOP 3
Copy the code

Select the four rows of data after the first three

SELECT product_id
FROM Product
ORDER BY product_id
OFFSET 3 ROWS
FETCH NEXT 4 ROWS ONLY; 
Copy the code

Paging around SQL Server can be implemented in several ways, but from a performance perspective, the OFFSET and FETCH clauses are recommended (and the syntax is concise).

The Microsoft official documentation also recommends using OFFSET and FETCH to limit the number of rows returned and as a paging solution

Other SQL Server paging methods, such as ROW_NUMBER(), NTILE() window functions, or implementations of TOP and TOP clauses.

For the comparison and performance comparison of OFFSET and other pages, you can refer to the two Sql Server2012 Paging Method analysis (OFFSET and FETCH) and Sql Server2012 T-SQL’s attempt to enhance paging

  1. Oracle uses row countersROWNUM

Oracle does this by calculating rows with ROWNUM

SELECT product_id FROM Product
WHERE ROWNUM< =3;
Copy the code
  1. DB2 uses the FETCH statement
SELECT product_id FROM Product
FETCH FIRST 5 ROWS ONLY;
Copy the code

Arithmetic operators and comparison operators

Arithmetic operator

You can use computed expressions (operational expressions) in SQL statements. Operations are performed in units of action.

As follows, query the unit price of the goods by 2 times

SELECT product_name, sale_price, sale_price*2 AS sale_price_x2
 FROM Product;
Copy the code

The result is:

 product_name | sale_price | sale_price_x2
--------------+------------+---------------T-shirt|       1000 |          2000punch|        500 |          1000Sports T-shirt|       4000 |          8000The pressure cooker|       6800 |         13600The fork|        500 |          1000Just scrape|        880 |          1760Ballpoint pen|        100 |           200A kitchen knife|       3000 |          6000
Copy the code

The operators used in the four operations (+, -, *, /) are called arithmetic operators.

An operator is a symbol that returns the result of four operations or string concatenation, numeric size comparison, etc. using the values on both sides.

Parentheses () can be used in operational expressions. Parentheses can be used in any expression in SQL.

An expression containing NULL

All calculations that contain NULL result in NULL

As follows, the result is NULL

5+NULL, 10-NULL, 1 * NULL, 4 / NULL, NULL / 9, NULL / 0

The same principle applies when dividing NULL by 0, where no error is reported and the result is NULL.

About the FROM clause in SELECT:

SELECT statements usually contain a FROM clause. However, it is sometimes possible to evaluate using only the SELECT clause. Usually used to get a temporary row of data.

-- SQL Server | PostgreSQL | MySQL
SELECT (100 + 200)*3 AS calculation;
Copy the code

However, OracIe does not allow the omission of the FROM clause in the SELECT statement. DUAL temporary tables can be used instead.

Comparison operator

Select the record whose sale_price column is 500:

 SELECT product_name,product_type
  FROM Product
  WHERE sale_price=500;
Copy the code

The symbol used to compare two columns or values is called a comparison operator.

The symbol = is the comparison operator, <> means “not equal “,>= greater than or equal to,> greater than, <= less than or equal to, < less than.

Some DBMS also support! = = =! < not less than,! > is not greater than the equal operator

You can also compare the results using the comparison operator,

Below, in the condition expression for WHERE, specify the condition that the sale_price is 500 higher than the purchase price

SELECT product_name,sale_price,purchase_price
 FROM Product
 WHERE sale_price - purchase_price > = 500;
Copy the code

The results are as follows:

Product_name | sale_price | purchase_price -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - T-shirt | 1000 | | 4000 | 500 sporting t-shirts 2800 the pressure cooker | 6800 | 5000Copy the code

Use an inequality sign for strings

When strings are not equal to or less than or equal to unequal, they are compared in alphabetical order, not in character order.

When comparing, items are sorted by the order in which they appear in the dictionary.

As follows, create table Chars and insert data

-- DDL: create table
CREATE TABLE Chars
(chr CHAR(3) NOT NULL.PRIMARY KEY (chr));

--SQL Server PostgreSQL
-- DML: inserts data
BEGIN TRANSACTION;
INSERT INTO Chars VALUES ('1'),
                  ('2'),
                  ('3'),
                  ('10'),
                  ('11'),
                  ('222');
COMMIT;
Copy the code

Query for data greater than ‘2’

SELECT chr
 FROM Chars
 WHERE chr>'2';
Copy the code

The result of the query is only ‘3’ and ‘222’

 chr
-----
 3
 222
Copy the code

Cannot use comparison operators for NULL (NULL value filtering)

When using the comparison operator, if the query condition column contains NULL, note that the comparison result is always false. The comparison operator cannot select a record that is listed as NULL.

For example, the purchase_Price column of the Product table has two records with a value of NULL, and if you query for a record with a purchase unit price other than 2800, the result is as follows.

SELECT product_name,purchase_price
  FROM Product
  WHERE purchase_price <> 2800;
Copy the code

Records that are not 2800 NULL will not appear in the result

Product_name | purchase_price -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - T-shirt | punch 500 | 320 pressure cooker | just scrape 5000 | 790Copy the code

Records that are NULL are not selected even with purchase_price=NULL

SQL does not recognize =NULL, <>NULL

To determine whether it is NULL, use theIS NULLThe operator; Check whether the value is not NULLIS NOT NULL

SELECT product_name,purchase_price
  FROM Product
  WHERE purchase_price IS NULL;
Copy the code

The result is:

Product_name | purchase_price -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - a fork | | ballpoint pensCopy the code

If you select all rows that do not contain the specified value through filtering, you also want to return rows with NULL values. Be sure to pay attention. Add NULL filtering alone.

Logical operator

The NOT operator.

The NOT operator denotes negation and is more widely used than <>. But it cannot be used alone. It must be combined with other conditions.

Most DBMSS allow the use of NOT to negate any condition.

For example, select records whose sale_price is greater than or equal to 1000

SELECT product_name, product_type, sale_price
 FROM Product
 WHERE sale_price > = 1000;
Copy the code

The result is:

Product_name | product_type | sale_price -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- a T-shirt | | | | 1000 sporting t-shirts clothes clothes 4000 pressure cooker | kitchen appliances | 3000 | | 6800 melee kitchen utensilsCopy the code

Add NOT to the query condition to display all records less than 1000:

SELECT product_name, product_type, sale_price
 FROM Product
 WHERE NOT sale_price > = 1000;
Copy the code

AND AND OR operators

Combine multiple query conditions.

  • The AND operator is valid only if both sides of the query are valid, equivalent to “AND”.

  • The OR operator holds the entire query if one of the conditions on either side is true, equivalent to “OR”

Query as follows, AND.

SELECT product_name,purchase_price
 FROM Product
 WHERE product_type='Kitchen appliances'
      AND sale_price> =3000;
Copy the code

The Venn diagram for this query is shown below, where the overlap of the two circles is the record selected by the AND operator.

Venn diagram, or Venn diagram, Venn diagram, Venn diagram, or Van diagram, is a sketch used in a less strict sense to represent sets (or classes) in the mathematical branch called set theory (or the theory of classes).

The OR query:

SELECT product_name,purchase_price
 FROM Product
 WHERE product_type='Kitchen appliances'
      OR sale_price> =3000;
Copy the code

The Venn diagram of OR query is as follows, and the item satisfying either of the two query conditions is the record that the OR operator can select.

Use of parentheses (evaluation order)

If the item is office supplies and the date of registration is September 11, 2009 or September 20, 2009, what should the query condition be?

SELECT product_name,product_type,regist_date
 FROM Product
 WHERE product_type='Office supplies'
 AND regist_date='2009-09-11'
 OR regist_date='2009-09-20';
Copy the code

Because the AND operator executes first, the final query condition will be:

 product_type='Office supplies' AND regist_date='2009-09-11'
 OR
 regist_date='2009-09-20'
Copy the code

That is, “The item is office supplies and the registration date is September 11, 2009” or “the registration date is September 20, 2009”.

The correct way is to use () and have the OR operation executed as a whole

SELECT product_name,product_type,regist_date
 FROM Product
 WHERE product_type='Office supplies'
 AND (regist_date='2009-09-11'
 OR regist_date='2009-09-20');
Copy the code

The results are as follows:

Product_name | product_type | regist_date -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- punch | office supplies | 2009-09-11Copy the code

Parentheses have a higher order of evaluation than the AND OR OR operators. In this way, the conditions in parentheses are filtered when the SQL statement is executed

The AND operator takes precedence over the OR operator. Most languages are the same. That is, the AND operator is processed first before the OR operator.

Therefore, the query cannot be modified by simply adjusting the order of AND AND OR.

Logical operators and truth values

NOT, AND, AND OR are called logical operators, where the logic is operating on truth values, which operate on truth values returned by comparison operators, etc.

A truth value is one of TRUE or FALSE.

Truth tables (truth tables, or Logic tables) are tables of logic operations on truth values and their results.

  • A logical operation using the AND operator is called a logical product.

  • A logical operation using the OR operator is called a logical sum.

True if NULL is present

Note the NULL value for logical operations.

From the Product table, the purchase unit price of Fork 0006 and biro pen 0008 is NULL, but NULL records cannot be selected from purchase_Price =2800 and NOT purchase_Price =2800. The reason is that NULL represents UNKNOWN. A third value other than true or false.

The usual logical operations are binary logic. But only logical operations in SQL are called three-valued logic.

The complete truth table with “indeterminate” is as follows:

The original truth table has only 4 rows, but when NULL is added, it becomes 3*3=9 rows.

In databases, “try not to use NULL” is the general consensus.

Complex filtering (condition)

BETWEEN operator (range checking)

The BETWEEN operator is used to check the values of a range. The BETWEEN keyword is followed by two values of the AND connection, the start AND end values.

==BETWEEN Matches all values in the range, including the specified start and end values. = =

Query products whose prices are between 500 and 1000.

select * from product where sale_price between 500 and 1000;

Select * from product where sale_price >= 500 and sale_price<=1000;
Copy the code

Results:

product_id | product_name | product_type | sale_price | purchase_price | regist_date -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - | 0001 | T-shirt clothing | | 500 | 1000 The 2009-09-20 0002 | punch | office supplies | 500 | 320 | 0006 | 2009-09-11 fork | 500 | | kitchen utensils | 0007 | 2009-09-20 box grater | 880 | | kitchen utensils 790 | | 0009 | 2008-04-28 T-shirt clothing | 1000 | 500 | 2009-09-20 (5 rows)Copy the code

IN operator (conditional range)

IN specifies the range of conditions within which to match. The IN range consists of a comma-separated set of legal values enclosed IN parentheses.

As follows, take the products whose sales value is within the range of (500,1000,2000,3000) :

select * from product where sale_price in (500.1000.2000.3000);
Copy the code

Results:

product_id | product_name | product_type | sale_price | purchase_price | regist_date -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - | 0001 | T-shirt clothing | | 500 | 1000 The 2009-09-20 0002 | punch | office supplies | 500 | 320 | 0006 | 2009-09-11 fork | 500 | | kitchen utensils | 2009-09-20 | 3000 | | 0004 | melee kitchen utensils 2800 | | 0009 | 2009-09-20 T-shirt clothing | 1000 | 500 | 2009-09-20 (5 rows)Copy the code

The IN operator performs the same function as OR. The IN query above equals:

select * from product where sale_price=500 OR sale_price=1000 OR sale_price=2000 OR sale_price=3000;
Copy the code

Advantages of using the IN operator:

  • The syntax of IN is clearer when matching multiple legal values.
  • When IN is combined with the AND AND OR operators, the order of evaluation is more manageable.
  • The IN operator generally executes faster than a set of OR operators.
  • The biggest advantage of IN is that it can include other SELECT statements, making the WHERE clause more dynamic.

Wildcard filtering

The LIKE operator

Previous WHERE filtering operators matched known values. For example, one value or more values, greater or less than a certain value, a certain range of values, etc., all filter known values.

Sometimes, however, you need to search for data that contains some text but does not know the exact value. That is, the filtered values are indeterminate. Simple comparison operators do not work, so wildcards are required.

Wildcards are characters that have special meaning in the WHERE clause of SQL.

Using wildcards, you can create search patterns that match specific data.

  • Wildcards (wildcard) : special character used to match part of a value.

Search pattern: Search criteria consisting of literals, wildcards, or a combination of both.

Wildcards are used in the LIKE operator. The following search pattern makes use of wildcard matching rather than simple equality matching for comparison.

Technically, LIKE is a predicate, not an operator. But it has the same result as the operator.

Wildcard searches can only be used for text fields (strings). Wildcard searches cannot be used for fields of non-text data types.

Percent sign (%) wildcard

The percent sign % indicates any number of occurrences of any character.

For example, find products that begin with T:

select * from product where product_name like 'T%';
Copy the code
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
 0001       |T-shirt|clothes|       1000 |            500 | 2009- 09- 20
 0009       |T-shirt|clothes|       1000 |            500 | 2009- 09- 20
(2Rows)Copy the code

The search mode ‘T%’ retrieves any content beginning with T.

Microsoft Access arbitrary string wildcards use * instead of %.

Wildcards can be used anywhere in the search mode, and multiple wildcards can be used.

The following uses two wildcards, at both ends of the search pattern.

 select * from product where product_type like '%用%';
Copy the code
product_id | product_name | product_type | sale_price | purchase_price | regist_date -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- 0002 | punch | office supplies | | 500 | 320 The 2009-09-11 0005 | pressure cooker | kitchenware 5000 | 6800 | | 0006 | 2009-01-15 fork | 500 | | kitchen utensils | 0007 | 2009-09-20 box grater | 880 | | kitchen utensils 790 | | 0008 | 2008-04-28 ballpoint pens stationery | 100 | | 2009-11-11 | 0004 | melee kitchen appliances | 3000 | 2800 | 2009-09-20 (six rows)Copy the code

% can also match 0 characters.

The wildcard % cannot match NULL. Where product_name like ‘%’ will not match rows with product name NULL.

Underscore (_) Wildcard

Underscore (_) matches only a single character.

DB2 does not support the wildcard _; Need to use in Microsoft Access? Instead of _.

select * from product where product_name like 'T_';
Copy the code

Square brackets ([]) wildcard. Used to specify a character set that matches a character (a character in the set) at the specified position (the position of the wildcard).

Only Access and SQL Server support square bracket collections

Suggestions on how to use wildcards

SQL wildcards are useful in fuzzy matching. But wildcard searches generally take longer to process than other searches.

  • Don’t overuse wildcards.
  • Try not to use wildcards at the beginning of a search pattern. Wildcards are the slowest to search for at the beginning.
  • Note the wildcard position. Otherwise, it is easy to search for errors.

Computed field

The calculated field is not the actual field value in the database table; it is created during the calculation operation when the SELECT statement is executed.

Concatenate field

Concatenate is simply joining multiple values together to form a single value (appending one value to another).

A concatenation can be multiple columns or strings.

As follows, concatenate the product type with the product name “type-name”.

- DB2, Oracle, PostgreSQL, SQLite use | | string concatenation
select product_type||The '-'||product_name from product;

Access and SQL Server use + concatenation
select product_type+The '-'+product_name from product;

MySQL and MariaDB can only use concat functions
select concat(product_type,The '-',product_name) from product;
Copy the code

Results:

? column? ----------------- Clothes - T-shirt Office Supplies - Punch Clothes - Sports T-shirt Kitchen Utensils - Pressure cooker Kitchen Utensils - Fork Kitchen Utensils - Eraser Board Office Supplies - ballpoint pen Kitchen Utensils - Kitchen knives clothes - T-shirt (9 lines of record)Copy the code

Concat string concatenation function, supported by most DBMSS such as SQL Server, PostgreSQL, MySQL, MariaDB, Oracle, etc.

Arithmetic calculation

Arithmetic operators (+, -, *, /) are used to perform arithmetic calculations on fields.

When concatenating fields or performing arithmetic calculations on fields, the AS alias is usually used to indicate a new concatenated field.