Common functions of SQL language

My mood: Today suddenly rain and sunny, a weekend and so to pass, always feel time is fast, whether it is work hours or after work time, total feeling time is not enough. Recently the blog is also less, some knowledge with simple words to describe is very abstract, so sometimes there is nothing to write, the following is my time this week to learn the SQL language commonly used functions. We hope to help you.

common functions of SQL language very much. Usually we don't use them often, but we don't know how to use them. Here, I give examples of several commonly used SQL functions;

character functions

Case one: Retrieve the commodity list and add a "meta" to the commodity price (for example, 20)

Analysis: You can use the character function to stitch concat or "| |" This stitching to complete the above requirements,

Select Concat (Price, "Yuan") from ES; or select Price| | " Yuan "from ES;

Case TWO: Retrieve the user table, if the user name is empty, then use "passers-by" to replace

Analysis: This example shows that if the user name in the user table is empty, only empty in the table, and the requirement is to replace the empty user name with a "passer-by", then we can use the NVL function to determine the conversion

Select NVL (Name, "passer-by") from emp;//when name has a value, displays the first content in (), and if it is empty, displays the contents of the second "passer-by"

Numeric Functions TRUNC function:TRUNC (column| EXPRESSION,N);Description: The trunc function is an intercept function that determines how much of the first parameter is intercepted from N. 1. This function can have only one parameter, at this point the second function n will default to 0;2.N can be positive or negative, a positive integer representing the number of digits after the decimal point, and a negative integer that indicates the number of digits before the decimal point is truncated.Example: we create the following statement in the dual table (the dual table is a virtual table, the dual table in Oracle contains only one record and can be read by any user, and this table is often used to construct our syntax structure)

1.SELECT TRUNC (45.926) from dual; The result is: 45 (the second function n is not, the default is 0,) 2. SELECT TRUNC (45.926,2) from dual: The result is: 45.92 (n is a positive integer 2, so the first parameter bits to the right of the decimal point to intercept 2, leaving only 2 bits before the data) 3.SELECT TRUNC (45.926,-1) from dual; The result is: (n is a negative integer-1, so the first parameter digit is truncated to the left of the decimal point 1 bits, therefore 40)

ROUND function : ROUND (column| EXPRESSION,N);Description: The round function is the same as the previous trunc function, except that the algorithm is rounded when he intercepts it, and we also call the round function a rounding function .

1.SELECT TRUNC (45.926) from dual; The result is: 46 (the second function n is not, the default is 0, the decimal point to the right is 9, then rounding plus 1) 2. SELECT TRUNC (45.926,2) from dual: The result is: 45.93 (n is a positive integer 2, so the first parameter bit to the right of the decimal point to intercept 2, leaving only 2 bits before the data, the third digit is 6, you want to round 2 change 3) 3.SELECT TRUNC ( 45.926,-1) from dual; The result is: (n is negative integer-1, so the first parameter digit is truncated to the left of the decimal point 1 bits, 45 the first digit is 5, then rounding, therefore 50,)

Example: Retrieving a commodity table, rounding the commodity price in the search results (accurate to two digits after the decimal point)

Select ROUND (p.price,2) as "price after rounding" from Es p;//as to add an alias to the previous field

In addition, the trunc and round functions do not only manipulate the number type, but also manipulate data of the date type. Because of the use of less, so in this is not the description, from the above description and case, you can understand their role, and the difference
Date function sysdate function:SELECT sysdate from dual;Description: The Sysdate function is to get the current system time,example: Inserting a data into a commodity table and adding the current time

Insert INTO ES VALUES (100,2, ' Apple ', sysdate,700);//Where sysdate is the time field and the current system time is inserted

EXTRACT function:SELECT EXTRACT (year from sysdate) from dual;Description: Extract function is to get some time, such as year, month, day, etc.

Select EXTRACT (year from sysdate) from Dual;//<span style= "font-family:arial, Helvetica, Sans-serif;" > Get the current system time of the year </span>

Select EXTRACT (MONTH from sysdate) from Dual;//<span style= "font-family:arial, Helvetica, Sans-serif;" > Get the month of the current system time </span>

Select EXTRACT (Day from sysdate) from Dual;//<span style= "font-family:arial, Helvetica, Sans-serif;" > Get the day of the current system time </span>

Example: Retrieving a product table, using the year-month-day format display for the listed time in the search results

SELECT EXTRACT (Year from p.datee) | | '-'|| EXTRACT (MONTH from p.datee) | | '-' || EXTRACT (day from P.datee) from ES p

Description: Above I used the above-mentioned stitching character | |, take the month and day separately, and then use the stitching display p.datee as the Date field in the commodity table

Conversion FunctionsConversion functions: Mainly used to manipulate multi-table data types, its main role is to convert data from one data type to another data type, such as: To_char: mainly on numbers, dates are converted into character types, commonly used or dates converted into string formatSyntax: to_char (date) or to_char (date, ' Format_model ') The second argument is in the format, Example: Retrieving a product table, using the year-month-day format display for the listed time in the search results

SELECT to_char (p.datee, ' Yyyy-mm-dd ') from ES p

to_date ():It is mainly the time character character converted to the time type functionSyntax: to_date (date, ' Format_model ') Case: Compare product listings to search for items listed in March 2012

SELECT * from es P WHERE to_date (' 2012-2-29 ', ' yyyy-mm-dd ') < p.datee and p.datee <= to_date (' 2012-3-31 ', ' yyyy-mm-dd ') )

Aggregation function grouping: GROUP BY column

SELECT SUM (p.stockcount), p.id frpm es p GROUP by p.id;

Whether the above syntax is grouped by ID, sum as Sum or Count (column) or max (column) to maximize the value,

There are a lot of aggregation functions, and it's interesting to be able to expand on your own to understand that aggregation functions are combined with GROUP by grouping. I remember that I have written a group on my blog, and you can see the blogs in the database types in your blog on your own.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Common functions of SQL language