"My Oracle Learning Note (ii)"-----SELECT statement Supplement

One, multi-table query

A multi-table query is a query that queries data from multiple tables that are associated with a syntax similar to a single-table query. In general, tables with multiple table queries are linked together, and if not, the query results are the Cartesian product of these multiple query tables (note 1).

Simulation query employee name and department Name:

Select [Employee Name],[Department name] from [employee table] a,scott,[Department table] b where a.[department number]=b.[department number];

In the previous example, an alias was specified for each query table to facilitate the writing of the SQL statement.

　　

Simulate queries employees who work in the "sales" department whose employee name

Select [Employee name] from [employee table] a,scott,[Department table] b where a.[department number]=b.[department number] and b.[department name]= ' sales ';

Second, nested query

A nested query refers to a select query that contains more than one subquery, the so-called subquery, which is a SELECT query statement nested within another SELECT, INSERT, UPDATE, or DELETE statement. The syntax of a subquery is similar to the SELECT syntax, but has limitations, such as that a subquery cannot contain keywords such as order by and into.

Mock query Employee name of "Sales" department:

Select [Employee name] from [employee table] where [department number] in (SELECT [Department number] from [departmental table] where [department name]= ' sales ');

Where the In keyword is used in the WHERE clause to determine whether the query's expression is in a list of multiple values. Returns the record that satisfies the criteria in the in list.

Simulate the list of employees with a higher than average salary in the employee table:

Select [Employee name] from [employee table] where [employee wages] > (select AVG ([employee wage]) from [employee table]);

The AVG function in the example above is used to calculate the average wage.

Third, function query

The SELECT query statement can use functions, and some of the functions commonly used in SQL statements are listed below.

1) Aggregate function

Min calculates the minimum value

Max calculates the maximum value

AVG calculates the average

Sum sum

Count calculates the total number of records that match the criteria

2) Numeric function

ABS (x) calculates the absolute value of X

MOD (x, y) calculates the remainder of X divided by Y

Ceil (x) calculates the smallest integer greater than or equal to the parameter X

Floor (x) calculates the largest integer less than or equal to the parameter X

Power (x, y) calculates the Y power of X as the bottom

EXP (x) calculates the x-th power of E (e=2.71828183 ...)

SQRT (x) calculates the split root of X

LN (x) calculates the natural logarithm of X

ROUND (X) rounding

Sin (x) calculates the sinusoidal value of x

COS (x) calculates the cosine of X

3) String function

Length (s) Gets the lengths of the string s

CONCAT (S1,S2) string connection that returns a string that is formed after the S2 is added to the S1

LOWER (s) capitalizes the string s all

UPPER (s) will string S all lowercase

Subster (s,m,n) intercepts the substring, returning the substring starting at the m position in S and the length n

Replace (S1,S2,S3) replaces the S2 that appear in the string S1 with S3, then returns the remaining string, S3 default NULL, and all S1 characters in S2 are deleted

INSTR (s,c) Gets the position of the first occurrence of the character C in the string S

4) Date function

Sysdate Get date and time

Months_between get a month interval between two dates

Add_months add a month on a specified date

Next_day the next day of the specified date

Last_day last day of the month

5) Type conversion function

To_number conversion to numeric type

To_date Conversion to date type

To_char conversion to character type

Hex_to_raw Hex Turn Binary

Raw_to_hex binary Turn hex

The total number of employees who have simulated queries:

Select COUNT (*) from [employee table];

Query the month interval between 2 dates:

Select Months_between (to_date (' 2010/01/01 ' yyyy/mm/dd '), to_date (' 2011/05/20 ' yyyy/mm/dd ')) as "month interval" from dual;

Dual is a virtual table that makes up the syntax rules for SELECT, and Oracle guarantees that there will always be only one record in dual.

（

Common Date data format (this paragraph is excerpt)

Y or yy or YYY last, two-bit or three-bit Select to_char (sysdate, ' YYY ') from dual; 002 = 2002

Syear or year syear to add a minus sign to the BC years Select to_char (sysdate, ' Syear ') from dual;-1112 = 111 2 BC

Q quarter, January-March for the first quarter Select to_char (sysdate, ' Q ') from dual; 2 for the second quarter ①

MM number of months Select to_char (sysdate, ' MM ') from dual; 12 = December

The Roman representation of the RM month is Select to_char (sysdate, ' RM ') from dual; IV = April

Month name in 9-character length for month Select to_char (sysdate, ' month ') from dual, and may followed by 6 spaces for May

WW the first few weeks of the year Select To_char (sysdate, ' WW ') from dual; 24 = June 13, 2002 is the 24th week

W this month the first few weeks Select to_char (sysdate, ' W ') from dual; October 1, 2002 is the 1th week

DDD year, January 1 is 001, February 1 for 032 Select to_char (sysdate, ' DDD ') from dual; 363 2002 1 February 2 9th for No. 363 Day

DD the day of the month Select To_char (sysdate, ' DD ') from dual; 04 October 4 is the 4th day

D Week the day of the first Select to_char (sysdate, ' D ') from dual; 5 March 14, 2002 for Monday

DY Week the first day abbreviation Select to_char (sysdate, ' DY ') from dual; SUN March 24, 2002 for Sunday

HH or HH12 12 input hours Select to_char (sysdate, ' HH ') from dual; 02 Midnight 2 O'Clock 8 02

HH24 24-hour Select to_char (sysdate, ' HH24 ') from dual; 14 2:08 P.M. to 14

MI minutes (0~59) Select to_char (sysdate, ' MI ') from dual; 17 4:17 P.M.

SS seconds (0~59) Select to_char (sysdate, ' SS ') from dual; 22 11 points 3 minutes 22 seconds

Note Do not use mm format for minutes (MI should be used in minutes). MM is the format used for the month, and it works for minutes, but the result is wrong.

）

Note 1----Cartesian product:

In mathematics, the Cartesian product of two sets X and Y (Cartesian product), also called the direct product, is represented as XXY, the first object is the member of X and the second object is one of all possible ordered pairs of Y.
Suppose that the set a={a, b}, set b={0, 1, 2}, the Cartesian product of two sets is {(A, 0), (A, 1), (A, 2), (b, 0), (b, 1), (b, 2)}.
Similarly, if a represents a collection of students in a school and b represents a collection of all courses in the school, the Cartesian product of A and B represents all possible elective courses. A represents the set of all consonants, and b represents the set of all vowels, so the Cartesian product of A and B will spell all possible Chinese characters.

"My Oracle Learning Note (ii)"-----SELECT statement Supplement