SQL Structured Query Language for Oracle database

1.select

Select *  from test                 //query information for all columns in test select test1,2,3 from test         // Querying data in the test1,2,3 column in the test table

1.1 Grouping and sorting

Select *  from Group  by test1   //View all the information in the test table to group the information in the Test1 column selectfromorderby< /c11>desc/ASC    // View all information in the test table sorted by information in the TEST1 column desc descending sort, asc ascending sort 

1.2 Aggregation functions

Select Max(test1) XX fromTest//View max value in test1 column in Test table xx is second alias optionalSelect min(test1) fromtest//Minimum valueSelect avg(test1) fromtest//AverageSelect Count(test1) fromthe number of data in the test//Statistics test1 columnSelectNVL (Test1,1) fromTest//query if the data in the Test1 column is empty, the default is 1 (1 can be changed to any value that matches the constraint condition)

1.3 Connection Query

Select *  fromTest1,test2whereTest1.1=Test2.1//equivalent connectionSelectTest1.*,test2.*  fromTest1Inner JoinTest2 onTest1.1=Test2.1//Internal connectionSelect test1.*,test2.*  fromTest1 Right outer JoinTest2 onTest1.1=Test2.1//Outer connection 1.1 right outer connection, the display of the Test2 table data is the main, the left side of the table without restrictions usually omitted outerSelecttest1.*,test2.* fromTest1 Left outer JoinTest2 onTest1.1=Test2.1//1.2 left outer connectionSelecttest1.*,test2.* fromTest1 Full outer JoinTest2 onTest1.1=Test2.1//1.3 full outer JOIN, display all columns of data, no limit

1.3.1

Non-equivalent connection

Select  from where between test2.1 and test3.1  //Query the value bits of the Test1 column test2.1 and test3.1 data

1.3.2 Natural Connection

1) column names are the same
2) Same data type, error if different
3) You cannot use the table name to qualify the column name, you can write the column name directly

Select test1. *, test2. *  from Join test2                //Natural Connection  principle: Two tables found in the same name. The data type is also the same column to connect to select test1. *, test2. *  from Join test2 using (TEST3)    //If more than one column meets the criteria, use the using to select the column name to connect to

1.3.3 Joint Query

Union/union All union comes with distance (remove duplicate data)

Multiple query results with union/union All connections can be connected to a table for output viewing

SQL Structured Query Language for Oracle database