Oracle Primary Data types
Overview: The data types supported by various databases are similar, and may differ slightly from the data types in standard SQL languages
Type: VARCHAR2: You can set its length limit when declaring a field. And after using it, you don't have to think about the existence of spaces.
If you insert a string that is less than the length of the upper limit, the system automatically shrinks it to the true length of the string
Number (M,n): can be used to represent an integral type or a floating-point type. But M can not exceed 38. If n is 0 or omits n, it means that it is an integer
Date: For storing dates and times
BLOB: Typically used in an application, rather than using SQL instructions directly in the database
For example, access to the database through JDBC technology, read and write blob or clob type of field, that is, read-write long string information, etc.
Char: It is a fixed-length character type. The Oracle database does not differentiate between characters and strings, which are collectively referred to as character or literal
The so-called fixed-length character is the insertion of a string, if not reached the agreed field length, the system will automatically fill the tail of the string space
Similarly, the length of the field at the time of the read is always the length of the field at the time of declaration. And when you compare string content, you also need to take into account the filtering of spaces
NCHAR: It is also a fixed-length string type, which is defined in the SQL language standard and typically uses Unicode encoding to hold characters in different countries or languages
The VARCHAR:SQL standard does not guarantee forward and backward compatibility when defining varchar, that is, it is possible to create incompatibilities with changes in language standards
So Oracle defines a VARCHAR2 type similar to the varchar type. is to support the VARCHAR2 type forever in future versions of Oracle.
Oracle is doing this to ensure that this type is compatible for the purpose of importing and exporting data in an Oracle family database
Long: It differs from the VARCHAR2 in that it does not support retrieving the contents of a string, that is, the query cannot be conditionally queried for its contents
VARCHAR2 and char, nchar type, and so on, can retrieve the contents of a string directly in the query.
Add: SELECT * from v$nls_parameters; the configuration information of a database exists as a datasheet, usually referred to as a data or data dictionary of data
In fact, it queries a view in the data dictionary. Where Nls_characterset corresponds to the current database character set
The default is to use the database character set. The tutorial uses the default ZHS16GBK character set when installed, that is, Chinese characters account for 2 bytes and English is 1 bytes
The nls_nchar_characterset corresponds to the auxiliary character set used by the NCHAR or nvarchar2 type, that is, the AL16UTF16 character set
In fact, AL16UTF16 is a 16-bit fixed-length Unicode-encoded character set. The database character set and the national character set can be modified
However, the database character set may face serious consequences, unless it is a database administrator, and ordinary users do not need to delve into this aspect
When the data in the database is imported and exported, it can be problematic if the source database and the target database use different character sets.
Function
Overview: Functions can be thought of as a collection of code that can accomplish relatively independent functions. Oracle functions are equivalent to methods or procedures in other languages
Oracle functions can be divided into single line functions and multi-line functions in two broad categories. All Oracle functions have return values.
The so-called Single-line function works for each row in the query result, and returns a result
A multiline function, which is called a grouping function, is a record of a set of queries, or multiple lines, that returns a result
Single line: Manipulate data items, accept parameters and return processing results, work on each returned row, modify the data type, can be nested using
Single-line functions are divided into character functions, numerical functions, date functions, conversion functions, general functions
Multiline: Also called a grouping function that operates on a set of data that can only return one result for a set of data (multiple-line records)
Multiline functions include AVG (), COUNT (), Max (), Min (), sum (), and so on.
For example, select AVG (SAL), Max (Sal), Min (sal), sum (SAL), Max (HireDate), Min (hiredate) from EMP;
Continuation: The use of one-line functions in Oracle's system functions can achieve many functions, such as the calculation of data, control the output format of the data
Set up and change the display format of the date, do data type conversion, use functions such as NVL to deal with null value, implement IF-THEN-ELSE branch logic, etc.
Second: The conversion function does not change the field type and value of the data in the table. It's the equivalent of copying the data and converting the data after it's replicated.
Data type conversions include both implicit and explicit conversions, and it is recommended that explicit data type conversions be used to ensure the reliability of SQL statements
Continuation III: Common functions apply to any type of data, including null values. Usually used to implement null value processing, empty value filter or set default value, etc.
Common functions include NVL (), Nvl2 (), Nullif (), coalesce (), Case expressions, decode (), etc.
Nesting: Single-line functions can be nested, with no limit on nesting levels. Grouping functions can be nested up to two levels. The order in which nested functions are executed is from inside to outside
Single line such as Select Empno,lpad (Initcap (Trim (ename)), 10, ' ") name, job, Sal from EMP;
Multiple lines such as SELECT MAX ("Avg") from the EMP group by deptno;--There's no point in using a group function here.
Description: Usually the function provided at the database level is simply processing the data, or it can only achieve very conventional functions
So it should not, or should not, be expected to implement particularly complex business logic at the level of database queries
If the logic of the application is mixed with the database, it will be detrimental to the maintenance and update of the code.
But also not conducive to database management, including data migration, database import and export, etc.
Date type
Overview: In a computer operating system or in a variety of advanced programming languages, dates are usually saved in a long integer, usually in milliseconds
Oracle internally stores date and time information in digital format: century, year, month, day, hour, minute, second
The default date format is Dd-mon-yy. You can use the SysData function to get the current system date and time
Operation: Date-type data can be directly added or minus a value, which results in the date. The agreed value represents the number of days to add and subtract
Two date data can be subtracted, the result is how many days difference between the two. Neither, because it doesn't make sense to add dates together.
NVL () function
Overview: It is used to replace null nulls with a specified default value, for type data such as characters, numbers, dates, and so on
Format: NVL (EXP1,EXP2)--Returns EXP2 value if the expression EXP1 value is null, otherwise EXP1 value is returned
For example: select Empno, ename, Sal, Comm, SAL+NVL (comm,0) from EMP;
Select Empno, ename, Job, NVL (Job, ' No job yet ') from EMP;
NVL2 () function
Overview: It is used to implement conditional expression functionality
Format: NVL2 (EXP1,EXP2,EXP3)--Returns the EXP2 value if the expression EXP1 value is not NULL, otherwise returns the EXP3 value
For example: select Empno, ename, Sal, Comm, NVL2 (comm, Sal+comm, Sal) gross income from EMP;
Nullif () function
Overview: It is used for data equivalence comparisons and returns null or one of the values being compared based on the comparison. Not many applications in actual development
Format: Nullif (EXP1,EXP2)--Returns null if the expression Exp1 is equal to EXP2 value, otherwise returns the value of EXP1
For example: Select name formerly known as Nullif (Pen_name, name) alias from author;
COALESCE () function
Overview: It is used to implement data "splice" function
Format: coalesec (EXP1,EXP2,...) --Examine each parameter expression in turn, and stop and return the value if it encounters a non-null value
Returns null if the expression is a null value. Usually the last expression is a field that ensures that it is not a null value
For example: select Empno, ename, Sal, Comm, Coalesec (sal+comm,sal,0) total income from EMP;
Case expressions
Overview: It is used to implement a multiple branch structure
Format: Case exp when COMPARISON_EXP1 then RETURN_EXP1
[When Comparison_exp2 then RETURN_EXP2
When COMPARISON_EXPN then RETURN_EXPN
else Else_exp]
End
For example: select Empno, ename, Sal,
Case Deptno Then ' finance Department '
When then ' research and Development Department '
When then ' sales Department '
Else ' Unknown Department '
End Department
from EMP;
Description: Every expression in case (such as deptno,10, finance Department, etc.) can be compounded.
This alignment is written to increase readability and, of course, to write code on the same line
The whole of the case to end is the equivalent of a field in a normal query, and the "department" after
Dedode () function
Overview: Similar to case expressions, it is also used to implement a multiple branch structure
Format: Decode (col|expression, Search1, RESULT1
[, SEARCH2, RESULT2,...,]
[, default])
For example: select Empno, ename, Sal,
Decode (Deptno, 10, ' finance Department ',
20, the ' Research and development Department ',
30, ' Sales department ',
' Unknown department ')
Department
from EMP;
COUNT () function
Format: COUNT (*) returns the total number of records in a group
COUNT (exp) returns a record that is not empty for an expression exp value
COUNT (DISTINCT (exp)) returns the number of non-empty records with an expression exp value that is not repeated
Example: SELECT COUNT (*) from emp;--by default, the entire table is a set of
Select COUNT (comm) from emp;--returns the number of records (rows) that are not empty Comm fields in the EMP table
Select COUNT (Distinct (DEPTNO)) from emp;--find the number of records that have deptno values that are non-null and not duplicates
grouping functions and Null values
Overview: Group functions omit null values in a column. You can use the NVL () function to force a grouping function to handle null values
Example: Select AVG (comm) from emp;--equivalent to sum (COMM)/count (comm)
Select SUM (comm) from emp;--calculates the sum of the comm values of the Non-empty table
Select AVG (NVL (comm,0)) from emp;--equivalent to AVG (NVL (comm,0))/count (*)
GROUP BY clause
Overview: It is used to divide data in a table into several groups
Format: Select column, group_function (column)
From table
[Where Condition]
[GROUP BY Group_by_expression]
[Order by column];
For example: Select Deptno, Avg (SAL) from EMP Group by DEPTNO;
Description: A field that appears in the select list, if not included in a group function, that field must appear in the GROUP BY clause at the same time
Fields that are contained in the gropy by clause do not have to appear in the select list. The order in which clauses are executed is Where→group By→order by default in ascending order
Add: Select Deptno, Job, avg (SAL) from EMP Group by DEPTNO, job order by Deptno desc;--grouping based on multiple fields
Select Deptno, Avg (SAL) from emp;--illegal
Note: If the GROUP BY clause is not present, the field (Single-line function) is not allowed to be mixed with the grouping function in the select list
Grouping functions are not allowed in a where, such as select Deptno, avg (SAL) from EMP where AVG (SAL) >2000 GROUP by deptno;--illegal
This is related to the order in which the clauses are executed. The WHERE clause executes first, and the GROUP BY clause is not executed when the WHERE clause is executed
The program does not know that this is a grouping, nor has it computed avg (SAL) 's average wage, so the group function is not allowed in the WHERE clause
Because the GROUP BY clause has not been executed, it is not sure how to group and how many groups are divided at this time
So there is only primary filtering in the WHERE clause. You can use the HAVING clause to filter the average wage at this time
HAVING clause
Overview: It is used to filter groupings
Format: Select column, group_function (column)
From table
[Where Condition]
[GROUP BY Group_by_expression]
[Having group_condition]
[Order by column];
Example: Select Deptno, Job, avg (SAL)
From EMP
where HireDate >= to_date (' 1981-05-01 ', ' yyyy-mm-dd ')
Group by by Deptno, job
Having avg (SAL) > 1200
Order by deptno, job;