Oracle Knowledge Point (2) Simple query fuzzy query scope query ____oracle

1 Simple query is to display the contents of all the data rows in a data table;
2 Execute the FROM clause in the query to determine the data source (which is actually all rows and columns) to control the data columns to display
3 If there is duplication of data, you can use distinct to eliminate duplicate data row display
4select clause inside can run arithmetic;
The 5SELECT clause can directly output constant content but for strings using "'", numbers are written directly
6 "| |" The content connection that is responsible for the output However, it is important to note that generally this type of operation is rarely directly present in the query (typically used less in the query than in the stored procedure).
Note: Use aliases in query SQL statements without adding "'" quotes but other constants need to be added


The common operators are as follows:
Relational operators: >,< >=, <=,<> (!=);
logical operators: And,or,not;
Range operator: BETWEEN. and..
predicate range: In,notin;
Null judgment: Is Null,is not NULL;
Fuzzy query: Like


All data in an Oracle database needs to be case-sensitive.
"=" can be used digitally or in a string to be used in an Oracle database for easy development = No data type can be used directly


In addition to and or logic in the logical operation there will be a logic must be logical if the original condition of the judgment is true, then the result is false vice versa
If the judgment of the original condition is false, then the result is true.
For logical operations there is a point to not write overly complex logical operations
select* from EMP where sal<2000;
Select*from EMP where not sal>=2000; The above two SQL statements have the same meaning.


Scope query:
Between and generally do not make sense when querying strings, usually when it comes to numbers.
Null judgment:
Null is interpreted as indeterminate on the database but it should be noted that if you use judgment in a numeric column to not be able to use the relational operator to complete an empty operation, you can only use is null
Either is or not NULL or is NULL
In:
In the process of specifying a query, the operation in is the shortest
Since in the specified range is used in so, if not used in the specified range
select* from EMP where empno in (7369,7566,7788,9999,null)
select* from EMP where empno not in (7369,7566,7788,9999)
Note: Questions about not in and null:
If the scope contains NULL when the range is used to judge with not, there is no output.
You can explain from a shallow level that the operation relies on a large amount of memory data when you have NULL in the back (that is, indeterminate meaning).
At this point, you may have to do all the query at this time the amount of data is very big risk caused by panic

Fuzzy query: Like
Summarize:
The WHERE clause is generally written after the FROM clause but immediately after the FROM clause
The WHERE clause controls the operation of the displayed data row and the SELECT clause controls the operation of the Display data column
The alias for the select definition cannot be used in the WHERE clause because the SELECT clause is executed after the WHERE clause


The following is an analysis of null values and instances in a contrasting programming language:
Null in the database means "no input data to (column) variables", that is, NULL in the database can distinguish between "no input data to (column) variables" and "Input data to (column) variables". Instead of entering an empty string,
Then there is the case of "input data to (column) variables", which has input data, except that the input data is an empty string (or a terminator). In this sense, such as int t; statements,
Also belong to Here "have to (column) variable input data" this kind of situation, just not have the programmer to enter the value, but by (compile) the system input a random number just.
The difference between null in a database and Null in languages such as C + +:
Null in the database is a keyword that says "No input data to (column) variables";
Null in languages such as C + + is a macro, expressed as an integer 0 general.
Therefore, you cannot use NULL (a macro) or ' null ' (a string) in the source binding variable function, because neither of them represents the keyword NULL in the database.
Of course, in the source code, NULL in the SQL statement string that does not contain the binding variable (for example, "INSERT into Table 1 values (null,null)") can be interpreted as null for a database. The SQL statement string is executed with the step function to successfully achieve the goal that is, the value in the table field is null.
Example analysis is as follows:
Use NULL to differentiate between "no input data" and "input null data", the difference being:
The length of NULL is NULL, and the length of the empty string is 0
A string of NULL data is precedence over an empty string
COUNT (message) counts an empty string, but does not count null data into
You can search for an empty string by using a binding variable, but you cannot search for NULL in this way.




MySQL empty string and null value problem


MySQL empty string and null values we all often meet, but the two are not a concept, and here is a brief description of the difference between null and MySQL null strings for your reference.
For beginners of SQL, the concept of null values often creates confusion, and they often assume that null is the same thing as a MySQL empty string. This is not the case. For example, the following statements are completely different:


Mysql> INSERT into my_table (phone) VALUES (NULL);
Mysql> INSERT into my_table (phone) VALUES (');



Each of these two statements inserts a value into the phone column, but the 1th statement inserts a null value, and the 2nd statement inserts an empty string. The meaning of the 1th situation could be interpreted as "unknown telephone number", and the meaning of the 2nd situation could be interpreted as "the person has no telephone and therefore no telephone number".
For null processing, you can use the is null and are not NULL operators, and the Ifnull () function.
In SQL, a comparison of a null value with any other value (even if it is null) will never be true. An expression containing NULL always exports null values, unless otherwise specified in the document on the operator and in the function of the expression. All columns in the following example return null:


Mysql> SELECT NULL, 1+null, CONCAT (' invisible ', NULL);