Oracle Database-DML (data operation language), oracle-dml

Oracle Database-DML (data operation language), oracle-dml
DML: data operation language
Note: In addition, deletion, modification, and query, the query is the most important, the core, and the most gold content.
That is to say, this section! Important
I. Select [query]
First, let's talk about the select syntax structure [Lite version]
1. query all fields
Select * from table name;
2. query specified fields
Select field name, field name, field name from table name;
3. Get system time [get system time through sysdate]
Select sysdate from dual;

II. Arithmetic Operator [+ -*/]
This section is also very simple
Example:
Select studentname, (studentage/2) + 2-18/3 from t_student;
First, he also executes the arithmetic operator priority. Nothing else

3. Alias
First, pay attention to the alias.
1. the alias should be enclosed in double quotation marks.
2. Use as to start an alias
Aliases are assigned to fields and tables.
The first is the field
Use the previous example
Select studentname "stuName", (studentage/2) + 2-18/3 "age" from t_student;
Here, "stuName" and "age" can be replaced with Chinese characters.
Or query all the information.
Select studentname as "stuName", (studentage/2) + 2-18/3 as "age" from t_student;
Then, start the table alias.
Select s. Field name 1, s. Field name 2 from table name s;

4. Connection operation |
[Enclose strings in single quotes in oracle]
Example:
Select field name 1 | ':' | field name 2 "field name written" from table name;
In this way, the output is "written name" as the field name, which is "field name 1: field name 2" in the table"
| In fact, the effect is equivalent to + in java

5. Where clause [add query conditions after where]
The where clause is right divided into two groups
1. Comparison operators [=,>, <, >=, <= ,! =, <>]
2. logical operators [or, and, not, in, like, between... and, is null]
Let's talk about comparison operators first,
This is actually very simple, but there are two examples that need to be mentioned.
Column:
Query the information of a student aged 20
Select * from t_student where studentage = 20;
Here, we mainly say "! = "And" <>"
These two are not equal
Example:
Query the information of students of not 20 age
(He has three solutions)
Select * from t_student where studentage <> 20;
Select * from t_student where studentage! = 20;
Select * from t_student where studentage> 20 or studentage <20;
Then let's talk aboutLogical operators
First, what do these words mean?
Or (or), and (and), not (no), in (in .. Inside), like (like ),
Beteen .. And .. (In .. ), Null (null), default (default)

Let's take another example.
For example:Query the students whose names are Zhang San and whose ages are greater than 20.
Select * from t_student where studentname = 'zhang san' and studentage> 20;
Or:Query the information of students aged 18, 27, and 30.
Select * from t_student where studentage in (18, 27, 30 );
Select * from t_student where studentage = 18 or? Studentage = 27 or studentage = 30;
In this way, I will not elaborate on other methods, but I will focus on some of them that are not very easy to understand.
Like can be understood as: fuzzy query
He has two symbols.
%: 0 or multiple characters
_: Represents any character
How to use it? Let's talk with examples.
Query Information of all students surnamed Zhang
Select * from t_student where studentname like 'zhang % ';
Select * from t_student where studentname like 'zhang __';
Or something that contains a word can be found.

Let's talk about between... and...
Example
Query student information between 18 and 25
Select * from t_student where studentage between 18 and 25;
It can also be out of this age group.
Select * from t_student where studentage not between 18 and 25;
In short, the code is ever-changing, and you must learn to imagine and operate it in a bold way.
Then the null value is null.
Column:Query the information of students whose birthdays are empty.
Select * from t_student where birthday is null;
Of course, he can also work with others.
For example, whether or not null is null, or is not null,
Or not null is not null, and so on,

Sat. Order by sorting
Sort. Also divided into two
SC: ascending [Default]
Desc: Descending Order
No nonsense. Example:
Sort students by age in descending order
Select * from t_student order by studentage asc;
Select * from t_student order by studentage desc; (ascending)
Sort students in descending order by age and in ascending order by weight [multi-field sorting]
Select * from t_student order by studentage desc, weight asc;

7. Aggregate functions
.. This is much more.
Min (): Minimum value
Max (): Maximum Value
Avg (): Average Value
Sum (): sum
Count (): Total number
For example:
Queries the minimum age of a class.
Select min (studentage) from t_student;
The maximum, minimum, sum, and average values are the same.
View the total number of instances.
Number of middle school students in the class
Select count (*) from t_student;
Select count (id) from t_student; -- in practice
Select count (1) from t_student; -- in practice

Someone asked * What is it?
As I forgot to mention above, * indicates all fields.
Also, why can id and 1 be the same?
Why can't I find it elsewhere or have never seen someone else use it?
This is what the Database Administrator sees when writing a program.
According to his explanation, 1 and id can also achieve the same effect,
However, you must note that id is a field defined by yourself,
You need to define and add numbers by yourself ,,,

Here I insert a sentence
Teach you a word decode (value, value 1, result 1, value 2, result 2, final result)
Example:
Select decode (10, 1, 'A', 2, 'B', 3, 'C', 'Kong ') from dual;
The meaning is very simple. In value 10, the result corresponding to the output value. Otherwise, the final result is output.
For example, it can also be used as follows:
Select decode (sex, 1, 'male', 'female ') as sex, count (id) from t_student group by sex;

8. Mathematical functions
Divided into five:
Abs () absolute value
Ceil () rounded up
Floor () rounded down
Trunc () truncation Function
Round () Rounding Function
Let's talk about abs ().
Select abs (-3) from dual;
Select abs (studentage-100) from t_student;
I don't need to say it if I rounded up or down or rounded down?
Trunc () Interception
Select trunc (3.98) from dual ;\\The output is 3.
Select trunc (3.98, 1) from dual ;\\The output value is 3.9 (retain a decimal number)
Select trunc (334.98,-1) from dual ;\\The output value is 330 (rounded forward !!)

9. String Functions
The point is coming!

1. concat (string 1, string 2)
Connect two strings
Eg: select concat (studentname, ' ') | 'practical training' from table name;

2. initcap ('string ')
Converts the first letter of a string to uppercase.

3. instr (source string, the character to be searched)
Searches for a specified character in a string and returns the location where the specified character is found.
It can contain multiple characters, but must be separated by commas (,).

4. length (string)
Returns the length of a string.

5. lower (string)
Returns a string and lowercase letters of all characters.

6. upper (string)
Returns a string and upper-case all characters.

7. rpad | LPAD (source string, total length, and filled characters)
Paste characters on the right (left) side of the string
EG: select replace ('abcdea', 'A', 'M') from dual; \ The output is MbcdeMM

8. rtrim | ltrim (source string, search string)
Deletes a string that appears on the right (left) side.
Eg: select ltrim ('abcd', 'AB') from dual; \ The output is: bc

9. substr (source string, start position, number of interceptions)
Substring, starting from start, count
Eg: select substr ('abc',) from dual; \ The output is AB.
Select substr ('abc',) from dual; \ The output is: bc

10. replace (source string, string to be searched, string to be replaced)
Replace string

11. reverse (source string)
Invert each character in a string
I have written their formats.

10. Conversion functions
1. to_char (): convert it to the string type
In practice, to_char () is usually used to convert the date type to the string type.
Eg:
Select to_char (sysdate, 'yyyy-MM-dd') from dual; \ output: year, month, and day of the current time
Select to_char (sysdate, 'yyyy-MM-dd HH: mi: ss') from dual; \ output: year, month, day, hour, minute, second of the current time (in 12-hour format)
Select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual; \ output: year, month, day, minute, and second of the current time (in the 24-hour format)
2. to_date (): convert it to the date type
In practice, to_date () is usually used to convert the string type to the date type.
Eg:
Select to_date ('1970-10-13 ', 'yyyy-MM-dd') from dual; \ output: 2017 \ 10 \ 13
Select to_date ('1970-10-13 15:23:10 ', 'yyyy-MM-dd HH24: mi: ss') from dual; \ output: 2017 15:23:10

11. Date and Time Functions
Three
1. add_months () increases or subtracted from the month
Eg:

Select add_months (sysdate, 2) from dual; \ The output is: the current time plus two months.

2. last_day () returns the last day of the month

Eg:

Select add_months (sysdate,-2) from dual; \ The output is: the current time minus two months

3. next_day () calculates the date of the next week based on the given date and the date after the week.
Eg:
Select add_months (to_date ('1970-02-10 ', 'yyyy-MM-dd'),-2) from dual; \ output: 2017

12th. Other functions
1. nvl (value 1, value 2) If value 1 is not blank, 1 is displayed; otherwise, 2 is displayed.
For example:
Select nvl (birthday, sysdate) from t_student;
\ The output is: if the value of birthday is not null, the value of birthday is displayed; otherwise, the value of sysdate is displayed (The meaning of the current time)

2. nvl2 (value 1, value 2, value 3). If value 1 is not empty, the value 2 is displayed. If the value is empty, the value 3 is displayed.
Eg:
Select nvl2 (birthday, sysdate, to_date ('1970-02-10 ', 'yyyy-MM-dd') from t_student;

\ Output: If the birthday value is not blank, sysdate is displayed. If the value is blank, sysdate is displayed.