I. Multi-table Association Query
Example:
SQL> create table student1 ( sid varchar(3), sname varchar(6), sage number(3)); Table created. SQL> create table course1 ( sid varchar(3), cname varchar(8), cno number(3)); Table created.
student1 table
SQL> select * from student1; SID SNAME SAGE --- ------ ---------- 01 Li Xiaoyao 21 02 yueru leen 20 03 Worship the Moon 100
course1 table
SQL> select * from course1; SID CNAME CNO --- -------- ---------- 01 History 12 02 Chemistry 11 22 German 18
1. inner join... on/join on: Connect only matching rows
SQL> select * from student1 inner join course1 on student1.sid=course1.sid; SID SNAME SAGE SID CNAME CNO --- ------ ---------- --- -------- ---------- 01 Li Xiaoyao 21 01 History 12 02 yueru leen 20 02 Chemistry 11
2. left join... On: Contains all rows of the table on the left, regardless of whether the table on the right has rows that match them or not.
SQL> select * from student1 left join course1 on student1.sid=course1.sid; SID SNAME SAGE SID CNAME CNO --- ------ ---------- --- -------- ---------- 01 Li Xiaoyao 21 01 History 12 02 yueru leen 20 02 Chemistry 11 03 Worship the Moon 100
3. right join... On: Contains all rows of the right table, regardless of whether the left table has rows that match them or not.
SQL> select * from student1 right join course1 on student1.sid=course1.sid; SID SNAME SAGE SID CNAME CNO --- ------ ---------- --- -------- ---------- 01 Li Xiaoyao 21 01 History 12 02 yueru leen 20 02 Chemistry 11 22 German 18
4. full outer join... on or full join... on: All rows that contain both left and right tables, regardless of whether there are matching rows in the other table
SQL> select * from student1 full outer join course1 on student1.sid=course1.sid; SID SNAME SAGE SID CNAME CNO --- ------ ---------- --- -------- ---------- 01 Li Xiaoyao 21 01 History 12 02 yueru leen 20 02 Chemistry 11 22 German 18 03 Worship the Moon 100
In general, we often use inlinking collocation functions (numerical functions, statistical functions, aggregation functions, etc.) for queries in our tests.
2. oracle Common Functions
1. NUMERICAL FUNCTION
ceil(n) returns the smallest integer greater than or equal to the value n
SQL> select ceil(10.7) from dual; CEIL(10.7) ---------- 11
floor(n) returns the largest integer less than or equal to the value n
SQL> select floor(12.3) from dual; FLOOR(12.3) ----------- 12
mod(m,n)m divided by the remainder of n, returns m if n=0, and m if m=0
SQL> select mod(7,5) from dual; MOD(7,5) ---------- 2 SQL> select mod(6,3) from dual; MOD(6,3) ---------- 0 SQL> select mod(3,0) from dual; MOD(3,0) ---------- 3 SQL> select mod(0,4) from dual; MOD(0,4) ---------- 0
N-th power of power(n,m)m
SQL> select power(12,11)from dual; POWER(12,11) ------------ 7.4301E+11
round(n,m) rounds N and reserves the m-bit after the decimal point.
SQL> select round(3.657879,2) from dual; ROUND(3.657879,2) ----------------- 3.66
sign(n) returns 0 if n=0, or 1 if n > 0, and - 1 if n < 0
SQL> select sign(19) from dual; SIGN(19) ---------- 1 SQL> select sign(0) from dual; SIGN(0) ---------- 0 SQL> select sign(-23) from dual; SIGN(-23) ---------- -1
Square root of sqrt(n)n
SQL> select sqrt(36) from dual; SQRT(36) ---------- 6
2. Common Character Functions
initcap(char) converts the first character of each string to uppercase
SQL> select initcap('xiaosheng') from dual; INITCAP(' --------- Xiaosheng
lower(char) whole string lowercase
SQL> select lower('SQL') from dual; LOW --- sql
Replace all STR1 in the replace(char,str1,str2) string with STR2
SQL> select replace('xiaosheng','xiao','ting') from dual; REPLACE(' --------- tingsheng
substr(char,m,n) takes out the substrings of N strings starting with M characters
SQL> select substr('xiaosheng',1,5) from dual; SUBST ----- xiaos
length(char) to find the length of a string
SQL> select length('meiyou ,dengwzhuceyige ') from dual; LENGTH('MEIYOU,DENGWZHUCEYIGE') ------------------------------- 23
|| juxtaposition operator
SQL> select 'xiao'||'sheng' from dual; 'XIAO'||' --------- xiaosheng
3. Date-type function
sysdate returns the current date and time
SQL> select sysdate from dual; SYSDATE ------------ 23-AUG-19
last_day The Last Day of the Month
SQL> select last_day(sysdate) from dual; LAST_DAY(SYS ------------ 31-AUG-19
add_months(d,n) The current date D is postponed by N months
SQL> select add_months(sysdate,2) from dual; ADD_MONTHS(S ------------ 23-OCT-19
Months of difference between (d, n) dates D and n
SQL> select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual; MONTHS_BETWEEN(SYSDATE,TO_DATE('20020812','YYYYMMDD')) ------------------------------------------------------ 204.373818
next_day(d,day): denotes the date of day specified in the first week after D
SQL> select next_day(sysdate,'Monday') from dual; NEXT_DAY(SYS ------------ 26-AUG-19
day formats are: Monday, tuesday, Wednesday, thursday, friday, saturday, Sunday
4. Date-type functions with special formats
Y or Y or YYY: The last or two or three of the year.
SQL> select to_char(sysdate,'YYYY') from dual; TO_C ---- 2019 SQL> select to_char(sysdate,'Y') from dual;#It can be seen that the value is taken from the last bit. T - 9
Q: Quarterly, 1-3 months for a quarter
SQL> select to_char(sysdate,'Q') from dual; T - 3
MM: Number of months
SQL> select to_char(sysdate,'MM') from dual; TO -- 08
RM: Represents the date format for converting months into Rome
SQL> select to_char(sysdate,'RM') from dual; TO_C ---- VIII
Month: means month in English (for example, august means august)
SQL> select to_char(sysdate,'month') from dual; TO_CHAR(SYSDA ------------- august
ww: Represents the week of the year.
SQL> select to_char(sysdate,'ww') from dual; TO -- 34
w: The week of the month
SQL> select to_char(sysdate,'w') from dual; T - 4
DDD: The first day of the year is January 1: 001, and February 1 is the 32nd day of the year: 032.
SQL> select to_char(sysdate,'DDD') from dual; TO_ --- 235
DD: Which day of the month is it?
SQL> select to_char(sysdate,'DD') from dual; TO -- 23
D: Which day of the week is it?
SQL> select to_char(sysdate,'D') from dual; T - 6 ----Because the West counts Sunday as the first day of the week.
DY: Short for the day of the week
SQL> select to_char(sysdate,'DY') from dual; TO_CHA ------ FRI ----Abbreviation for Friday
hh12: Represents 12-hour hours
hh24: Represents 24-hour hours
Mi: Represents minutes.
ss: Represents the number of seconds
TO_CHAR( -------- 02:53:13 ---12 Hourly system SQL> select to_char(sysdate,'hh24:Mi:ss') from dual; TO_CHAR( -------- 14:53:53 ---24 Hourly system
to_number(): Digitalize strings
SQL> select to_number('123333') from dual; TO_NUMBER('123333') ------------------- 123333
to_char(): Represents converting numbers to strings
5. Aggregation function
avg(x): Returns the average value of X
sum(x): Returns the sum of X and
count(x): counting x
MAXMIN(x): Returns the maximum or minimum value of X
median(x): Returns the median value of X
stddev(x): returns the standard deviation of X
[Note: Notes for using aggregation functions]
(1) You can use distinct to remove duplicate data, usually in front of from.
(2) If an aggregation function is included in the query and the selected columns are not in the aggregation function, then the columns must be in the group by clause, otherwise the error will be reported.
(3) The "have" clause can be used to filter line groups and can be placed after the group by clause.
3. Subquery
Subquery: A nested query in a query, often placed on columns, tables, and conditions.
Grammar:
select (sub-query) from (sub-query) where (sub-query) group by (sub-query) have (sub-query) order by (asc/desc) sub-query