Oracle multi table associated query and sub query

Time:2019-11-12

Oracle multi table associated query and sub query

I. multi table associated 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 Lin Yueru 20
03 Baiyue 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 Lin Yueru 20 02 chemical 11

2. Left join On): contains all the rows in the left table, regardless of whether there are any matching rows in the right table

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 Lin Yueru 20 02 chemical 11
03 Baiyue 100

3. Right join On): contains all the rows in the right table, regardless of whether there are any matching rows in the left table

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 Lin Yueru 20 02 chemical 11
                      22 German 18

4. Full outer join On or full join On): contains all the rows of the left and right tables, regardless of whether there are any matching rows in the other side of the 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 Lin Yueru 20 02 chemical 11
                      22 German 18
03 Baiyue 100

In general, we often use the inner join collocation function (numerical function, statistical function, aggregate function, etc.) in our test

2. Common functions in Oracle

1. Numerical function

Ceil (n) returns the minimum integer greater than or equal to the value n

SQL> select ceil(10.7) from dual;

CEIL(10.7)

        11

Floor (n) returns the maximum integer less than or equal to the value n

SQL> select floor(12.3) from dual;

FLOOR(12.3)

         12

The remainder of mod (m, n) m divided by N, if n = 0, then M is returned, if M = 0, then M is also returned

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

The nth power of power (n, m) M

SQL> select power(12,11)from dual;

POWER(12,11)

  7.4301E+11

Round (n, m) rounds n to the nearest m 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, otherwise returns 1 if n > 0, returns – 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 (char, STR1, STR2) string

SQL> select replace('xiaosheng','xiao','ting') from dual;

REPLACE('
---------
tingsheng

Substr (char, m, n) takes the substring of N strings starting from the M character

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

||Collocation operator

SQL> select 'xiao'||'sheng' from dual;

'XIAO'||'
---------
xiaosheng

3. Date function

Sysdate returns the current date and time

SQL> select sysdate from dual;

SYSDATE

23-AUG-19

Last day of the month

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYS

31-AUG-19

Add ﹣ months (D, n)

SQL> select add_months(sysdate,2) from dual;

ADD_MONTHS(S

23-OCT-19

Months between (D, n) dates

SQL> select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('20020812','YYYYMMDD'))

                                            204.373818

Next [day (D, day): indicates the day specified in the first week after D

SQL> select next_day(sysdate,'Monday') from dual;

NEXT_DAY(SYS

26-AUG-19

Day format: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

4. Date function with special format

Y or Y or YYY: the last or two or three digits 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: quarter, one to three months

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: the date format to convert the month to Rome

SQL> select to_char(sysdate,'RM') from dual;

TO_C

VIII

Month: indicates the month in English characters (for example, August refers to August)

SQL> select to_char(sysdate,'month') from dual;

TO_CHAR(SYSDA

august

WW: the week of the year

SQL> select to_char(sysdate,'ww') from dual;

TO
34

W: the week of this month

SQL> select to_char(sysdate,'w') from dual;

T
-
4

DDD: indicates the day of the year, January 1 is the first day of the year: 001, February 1 is the 32nd day of the year: 032

SQL> select to_char(sysdate,'DDD') from dual;

TO_

235

DD: the day of the month

SQL> select to_char(sysdate,'DD') from dual;

TO
23

D: the day of the week

SQL> select to_char(sysdate,'D') from dual;

T
-
6     

Dy: abbreviation for the day of the week

SQL> select to_char(sysdate,'DY') from dual;

TO_CHA

FRI   

Hh12: the number of hours in 12 hour system

Hh24: refers to 24-hour hours

Mi: minutes

SS: seconds

TO_CHAR(

02:53:13    

SQL> select to_char(sysdate,'hh24:Mi:ss') from dual;

TO_CHAR(

14:53:53   

To ou number(): digitize a string

SQL> select to_number('123333') from dual;

TO_NUMBER('123333')

             123333

To_char(): to convert a number to a string

5. Aggregate function

AVG (x): returns the average value of X

Sum (x): returns the sum of X

Count (x): count x

Max \ min (x): returns x Max or x min

Media (x): returns the middle value of X

StdDev (x): returns the standard deviation of X

[Note: precautions for using aggregate functions]

(1) you can use distinct to remove duplicate data, which is usually placed in front of from

(2) if the query contains an aggregate function and the selected columns are not in the aggregate function, the columns must be in the group by sentence, otherwise an error will be reported

(3) the “having” sentence can be used to filter row groups, which can be placed after the group by clause

III. sub query

Subquery: refers to the nested query in the query, which is often placed on columns, tables and conditions

Syntax:

Select (sub query) from (sub query) where (sub query) group by (sub query) having (sub query) order by (ASC / DESC) sub

 

Recommended Today

The method of obtaining the resolution of display by pyqt5

The code is as follows import sys from PyQt5.QtWidgets import QApplication, QWidget class Example(QWidget): def __init__(self): super().__init__() self.initUI() #Interface drawing to initui method def initUI(self): self.desktop = QApplication.desktop() #Get display resolution size self.screenRect = self.desktop.screenGeometry() self.height = self.screenRect.height() self.width = self.screenRect.width() print(self.height) print(self.width) #Show window self.show() if __name__ == ‘__main__’: #Create applications and objects app […]