Oracle's single-table queries and common functions

1. Syntax:
Select Field List
From table name
[Where query condition]
[GROUP by group]
[Having group conditions]
[ORDER BY order]

Select *represents all fields of the querySelectId as"Number", sname student's name, age "" "--as followed by aliases can also be omitted directly    SelectT.*     fromT_student T--to alias a table    whereClassID is NULL --NULL judgment    whereAge not inch( -, at,...)--Range Judgment    whereAgebetween  -  and  -   --interval judgment between ' A ' and ' Z '    whereSname not  like '% River%'   --sname like ' jiang% '--' Jiang ' to the river beginning '% Jiang ' with the river end '% River ' contains Jiang                     --Fuzzy Query _ an underscore represents a location    Order  byAge--ASC Ascending desc Descending default Ascending    Select distinctAge,sex fromT_student--distinct remove duplicate records

Statistical functions:
Count: Number of statistics bars

Select Count(*) fromt_studentSelect Count(ID) fromt_studentSelect Count(ClassID) fromt_student--count is the number of non-empty records in this column    Select Count(1) fromt_student; SelectId,sname,age,sex,classid,1,2,3  fromt_studentsum: SumSelect sum(age) fromt_student; min: Take minimum valueSelect min(age) fromt_student; Max: Take maximum valueSelect Max(age) fromt_student; avg: Take averageSelect avg(age) fromt_student; Select sum(age),min(age),Max(age),avg(age) fromT_student;

COUNT (1) compared to COUNT (*):

If your data table does not have a primary key, then count (1) is faster than COUNT (*)
If there is a primary key, then the primary key (the Federated primary key) as the count condition is also faster than COUNT (*)
If your table has only one field, then count (*) is the quickest.
COUNT (*) count (1) compares the two. The data field that corresponds to count (1) is mostly still.
If Count (1) is a clustered index, ID, that must be count (1) fast. But the difference is very small.
Because Count (*), automatically optimizes the specified field to that one. So there is no need to count (?), with Count (*), SQL will help you to complete the optimized

Count Detailed:
COUNT (*) returns the total number of rows that exist in the table, including rows with a value of NULL, whereas count (column name) returns the total number of all rows except null in the table (columns with default values will also be counted).
Distinct the column name, the result will be the result of dropping the value null and repeating the data

GROUP BY: Grouping functions
Select Age,sex
From T_student
GROUP BY Age,sex
--Note: The field list in the grouping function can only show grouped fields and statistical functions
--the function of grouping functions is the same as distinct when no statistical function is used.
Select Sex,count (Sex)
From T_student
GROUP BY sex
---The GROUPING function "aggregate function" when not in use with group BY, all the data of the query is counted.
-If used with group by, then the grouped data are counted

　　

SelectClassid,sex,Count(1)     fromt_studentGroup  byClassid,sex having Count(1)> 1 --the conditions after grouping    SelectClassid,sex,Count(1)     fromt_studentwhereAge>  - --add conditions before grouping    Group  byClassid,sex

The difference between where and having
Where only the data source filter that represents the query is followed by the From
Having only the group by followed by filtering the data after grouping,

Common functions:
Concat: Connection function
Select Concat (id,sname), Length (sname) from t_student
Date function:
String goto date:to_date
Update t_student set birth=to_date (' 1990-01-01 ', ' yyyy-mm-dd ')
Date goto string: To_char

 select   Sysdate, To_char (Sysdate,  Span style= "COLOR: #ff0000" > " yyyy-mm-dd hh:mi:ss   ") -- , to_char (Sysdate, " yyyy-mm-dd   " ), to_ char (sysdate,   " yyyy-mm   " ), To_char (sysdate,  yyyy   " )  from  dual; 

Months_between (sysdate,date);--the number of months of both time

Add_months: Increase the number of months based on the current time
Select Add_months (sysdate,12) from dual;

Last_day (): Returns the last day of the month for the specified date
Select Last_day (sysdate) from dual;

Extract: Capturing the contents of a specified part of a date
Select Extract (Day from sysdate) from dual; --dual is a virtual table that comes with a system

NVL (Column,value); If the queried field is null, it is populated with the default value
Select ID,SNAME,SEX,NVL (Sex, ' haha ') from t_student

decode: Similar to the IF statement in JavaSelectid,sname,sex, decode (sex,1,'male')--if (sex = = 1) {male}, Decode (Sex,1,'male',2,'female')--if () {}else if () {}, Decode (Sex,1,'male',2,'female','Unknown')--if () {}else if () {}else{}     fromT_student

ROWID: Row ID, location of data store, unique
RowNum: line number, automatic maintenance of the system, starting from 1 self-increment, there are 1 only 2
Select T.*,rownum from T_student t

Check out the top 5 student records in the student table
Select T.*,rownum from T_student t where RowNum <=5

Check out the records of 5th to 10th in the student's table
Select T.*,rownum from T_student t where rownum >=5 and rownum <=10---this is the wrong wording.

---How the paging query is implemented (this is done in Oracle)    SelectT1.*, RowNum from     (SelectT.*, RowNum num fromT_student t) T1--all data and line numbers are queried first    whereT1.num>= 5  andT1.num<=Ten           --and then choose the part you want.    SelectT2.*, RowNum from     (SelectT1.*, RowNum num fromT_student T1whereRowNum<=Ten) T2--first fetch the upper bound data and the line number    whereT2.num>=5   --and then the lower limit of the data

Use of case
Find out how many people are in the "20 years old" "21-25" and "26" in the Student's table.

SelectT.*,             Case  whenAge<=  -  Then 1 Else 0 End"Less than 21", Case  whenAge> -  andAge< -  Then 1 Else 0 End" +- -"            , Case  whenAge>=  -  Then 1 Else 0 End"More than 26" fromt_student TSelect             sum( Case  whenAge<=  -  Then 1 Else 0 End) "Less than 21",sum( Case  whenAge> -  andAge< -  Then 1 Else 0 End) " +- -"            ,sum( Case  whenAge>=  -  Then 1 Else 0 End) "26 or more" fromt_student TSelect             Count( Case  whenAge<=  -  Then 1 Else NULL End) "Less than 21",Count( Case  whenAge> -  andAge< -  Then 1 Else NULL End) " +- -"            ,Count( Case  whenAge>=  -  Then 1 Else NULL End) "26 or more" fromT_student T

Oracle's single-table queries and common functions