Oracle learning - DQL syntax

Standard DQL syntax

select distinct * | column name as alias, list 2 as alias 2... | aggregate function
 from table name as alias, table name 2 as alias 2
 where query criteria
 group by group field having group condition
 order by sort field asc | desc

1. Query - single table query

1.1. Simple query exercise

-- 1 Query the owner's record of water meter No. 30408
select * from t_owners where watermeter = '30408';
--  Use alias of table
select * from t_owners ow where ow.watermeter = '30408';


-- 2 Query the owner's record containing "Liu"
--- like sentence  %Match multiple characters  _Match 1 field
select * from t_owners where name like '%Liu%';

-- 3 Inquire the owner record whose name includes "Liu" and whose house number includes 5
select * from t_owners where name like '%Liu%' and housenumber like '%5%';

-- 4 Inquire the owner's record with the owner's name including "Liu" or the house number including 5
select * from t_owners where name like '%Liu%' or housenumber like '%5%';

-- 5 Query the owner record whose name includes "Liu" or whose house number includes 5, and whose address number is 3.
--- and Priority over or,If you execute first or,Parentheses required
select * from t_owners where (name like '%Liu%' or housenumber like '%5%') and addressid = 3;

-- 6 Query the records with the number of words used in account records greater than or equal to 10000 and less than or equal to 20000
--- Relational operators: > >= < <= == <>
select * from t_account where usenum >= 10000 and usenum <= 20000;
--- field between ... and ...
select * from t_account where usenum between 10000 and 20000;

-- 7 query T_PRICETABLE In the table MAXNUM Empty record
select * from t_pricetable where maxnum is null;

-- 8 query T_PRICETABLE In the table MAXNUM Records not empty
select * from t_pricetable where maxnum is not null;

1.2 de duplication and sorting

-- To repeat
-- Requirement: query the address in the owner table ID,Do not repeat
select distinct addressid from t_owners;
select distinct(addressid) from t_owners;

-- sort
-- select ... order by field asc|desc ,Field 2 asc|desc,....
--Demand: Yes T_ACCOUNT Tables are sorted in ascending order by usage
select * from t_account order by usenum asc

--Demand: Yes T_ACCOUNT Table sorted in descending order by usage
select * from t_account order by usenum desc

--Demand: Yes T_ACCOUNT Table according to month Descending order, if the same according to usenum Ascending
select * from t_account order by month desc , usenum asc ;

1.3 pseudo column

  • Fake columns are unique to oracle. They are also real columns. They are used for query operations and cannot be added, deleted or modified.
Common pseudocolumns describe
rowid Unique identification of unique record on physical file < br > purpose: used to distinguish duplicate data
rownum In the result set of the query, ROWNUM identifies a line number for each line in the result set < br > purpose: paging in Oracle
  • rowid

    • There are the same data records in mysql table. If you operate on one of them, you need to modify all the data.

  • Oracle uses rowid to distinguish each piece of data. There will be no one operation that affects multiple pieces.

  • rownum

    -- query
    select rownum,t_account.* from t_account;
    

1.4 aggregate function

  • The aggregate statistics of ORACLE is implemented by grouping function, which is consistent with MYSQL.
  • Aggregate function: by providing functions, the query results are processed into one row and one column of data.
    • Feature: aggregate function does not calculate null value
Aggregate function describe
sum Summation
avg average
max Maximum
min minimum value
count count
-- Aggregate function
--1 Total water consumption of all users in 2012
select sum(usenum) from t_account where year = '2012';

--2 Statistics of average water consumption (words) in 2012
insert into t_account values( seq_account.nextval,2,1,3,'2012','12',95076,99324,null,1,sysdate,44.51,'1',to_date('2014-01-14','yyyy-MM-dd'),2 );
select avg(usenum) from t_account where year = '2012';

--3 Statistics of maximum water consumption in 2012 (words)
select max(usenum) from t_account;

--4 Statistics of minimum water consumption in 2012 (words)
select min(usenum) from t_account;

--5 Number of statistical records count
select count(id) from t_account;

2. Query -- connection query

  • Cartesian product: product of two tables, maximum set of all data (useless for development)

    select * from A , B;
    
  • Internal connection

    • Implicit inner connection

      select * from A , B where a.id = b.id
      
    • Show internal connections

      select * from A inner join B a.id = b.aid
      
  • External connection

    • Left outer connection: query all data in the left table (A). If the condition is correct, the data in the right table (B) will be displayed. Otherwise, null will be displayed

      select * from A left outer join B on a.id = b.aid
      
    • Right outer connection: query all data in the right table (B). If the condition is true, the data in the left table (A) will be displayed. Otherwise, null will be displayed

      select * from A right outer join B on a.id = b.aid
      

2.1. Internal connection query exercise

--join query
-- 1 Inquiry shows the owner number, owner name, owner type name
--- Implicit inner connection
select ow.id,ow.name,ot.name from t_owners ow , t_ownertype ot 
where ow.ownertypeid = ot.id;
--- Show internal connections
select ow.id,ow.name,ot.name from t_owners ow
inner join t_ownertype ot on ow.ownertypeid = ot.id;

-- 2 The inquiry shows the owner number, name, address and type of owner
--- Implicit inner connection
select ow.id,ow.name,ad.name,ot.name from t_owners ow , t_ownertype ot , t_address ad
where ow.ownertypeid = ot.id and ow.addressid = ad.id;
select ow.id,ow.name as Name of owner,ad.name Address name,ot.name Owner type name from t_owners ow , t_ownertype ot , t_address ad
where ow.ownertypeid = ot.id and ow.addressid = ad.id;
--- Show internal connections
select ow.id,ow.name,ad.name,ot.name from t_owners ow 
inner join t_address ad on ow.addressid = ad.id
inner join t_ownertype ot on ow.ownertypeid = ot.id;

-- 3 The query shows the owner number, owner name, address, area and owner classification
--- Implicit inner connection
select ow.id,ow.name Name of owner,ad.name address,ar.name Area,ot.name Owner classification from t_owners ow , t_ownertype ot , t_address ad , t_area ar 
where ow.ownertypeid = ot.id and ow.addressid = ad.id and ad.areaid = ar.id
--- Show internal connections
select ow.id,ow.name Name of owner,ad.name address,ar.name Area,ot.name Owner classification from t_owners ow
inner join t_ownertype ot on ow.ownertypeid = ot.id
inner join t_address ad on ow.addressid = ad.id 
inner join t_area ar on ad.areaid = ar.id;

2.2 left outer connection

-- Left outer link
--Demand: query the owner's accounting records, showing the owner's number, name, year, month and amount. If the owner does not have accounting records, he shall also list his name.
select ow.id,ow.name,ac.year,ac.month,ac.money from t_owners ow
left outer join t_account ac on ow.id = ac.ownerid 

2.3 right outer connection

--Demand: query the owner's accounting records, showing the owner's number, name, year, month and amount. If there is no corresponding owner information in the accounting records, the records shall also be listed.
--- Prepare data, modify t_account surface ownerid Non NULL constraint removal
insert into t_account values( seq_account.nextval,null,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-14','yyyy-MM-dd'),2 );

select ow.id,ow.name,ac.year,ac.month,ac.money from t_owners ow right outer join t_account ac on ow.id = ac.ownerid

2.4 special usage of Oracle left outer connection (understand)

  • On the basis of inner connection, use (+) to convert the outer left connection
-- Oracle Class book left outer link
select ow.id,ow.name,ac.year, ac.month ,ac.money from t_owners ow , t_account ac where ow.id = ac.ownerid (+);
  • Usage:

    • Left outer connection: at the connection condition, the right table condition field adds (+)

3. Subquery

3.1 overview

  • Subquery: a select statement as part of the syntax of another select statement.

  • select statement syntax:

    select distinct * | field from table name
     where query criteria
     group by group field having group condition
     order by sort field asc | desc
    

3.2 single line sub query

  • Write steps, divide a requirement into multiple sub requirements, complete each sub requirement in turn, and finally combine the sub requirements
-- Query the account records of water consumption greater than average in January 2012
-- 1 Average water consumption
select avg(usenum) from t_account where year='2012' and month = '01'
-- 2 Query all water consumption in January 2012
select * from t_account where year='2012' and month = '01'
-- 3 merge
select * from t_account where year='2012' and month = '01' and usenum > 20009.5
select * from t_account where year='2012' and month = '01' and usenum > (
select avg(usenum) from t_account where year='2012' and month = '01'
)

3.3. Multi row sub query

--Query the account data in 2012 with the usage greater than the maximum usage in March 2012
-- Mode 1: maximum value
-- 1. Maximum usage in March 2012
select max(usenum) from t_account where year = '2012' and month = '03'
-- 2. Query 2012 account is greater than 13808
select * from t_account where year = '2012' and usenum > 13808
-- 3. integration
select * from t_account where year = '2012' and usenum > (
select max(usenum) from t_account where year = '2012' and month = '03')

-- Mode 2: use all operator
-- 1. All usage in March 2012
select usenum from t_account where year = '2012' and month = '03'
-- 2. Query 2012 account is greater than all usage in March
select * from t_account where year = '2012' and usenum > all (13808,13390)
-- 3. integration
select * from t_account where year = '2012' and usenum > all (select usenum from t_account where year = '2012' and month = '03')


3.4. Nested sub query

  • Nested subquery: embed subquery again in subquery
-- Query the owner record of the garden in the name of the community in Haidian District
-- 1. Query area id,Name is "Haidian" --Many Haidian
select id from t_area where name = 'Haidian'
-- 2. Inquiry address id,Condition: name includes "garden" and area id 
select id from t_address where name like '%garden%' and areaid in (1)
-- 3 Combination 1+2
select id from t_address where name like '%garden%' and areaid in (
  select id from t_area where name = 'Haidian'
)
-- 4. Query the owner, condition: a group of addresses id
select * from t_owners where addressid in (1)
-- 5. combination
select * from t_owners where addressid in (
  select id from t_address where name like '%garden%' and areaid in (
    select id from t_area where name = 'Haidian'
  )
)

--- There is only one Haidian
select id from t_address where name like '%garden%' and areaid = (
  select id from t_area where name = 'Haidian'
)


3.5 scalar subquery

  • Scalar subquery: the execution result of the subquery statement is directly displayed as the result of the main query
-- Query the sum of user's annual water consumption in the account table    And annual average water consumption
-- 1. Total inquiry water consumption 137868
select sum(usenum) from t_account
-- 2. Query average water consumption 5514.72
select avg(usenum) from t_account
-- 3. Use virtual table to use two unrelated data dual Put it together
select (137868) as the sum,(5514.72) as average from dual
select (
  select sum(usenum) from t_account
) as the sum,(
  select avg(usenum) from t_account
) as average from dual

3.6. Related sub query

  • Related sub query: the sub query depends on the results of the external main query

Exercise 1:3 table exercise

-- The inquiry shows the owner number, name, address and type of owner
-- 1. Inquire the owner's number and name
select id,name,addressid,ownertypeid from t_owners 
-- 2. By address id,Inquiry address
select name from t_address where id = 1
-- 3. According to type id,Type of inquiry owner
select name from t_ownertype where id = 1

-- 4. combination
select ow.id,ow.name,(
  select name from t_address where id = ow.addressid
) Address name ,ownertypeid from t_owners ow

select ow.id,ow.name,(
  select name from t_address where id = ow.addressid
) Address name ,(
  select name from t_ownertype where id = ow.ownertypeid
) from t_owners ow


Exercise 2:4 table exercise

-- The query shows the owner number, owner name, address, area and owner classification
-- 1. Inquire the owner's number and name
select id,name,addressid,ownertypeid, ar.name from t_owners 
-- 2. By address id,Inquiry address
select name from t_address where id = 1
-- 3. According to type id,Type of inquiry owner
select name from t_ownertype where id = 1
-- 4. According to area id,Query area
select ar.name from t_area ar, t_address ad where ar.id = ad.areaid and ad.id = 1
-- 5 combination

select ow.id, ow.name,(
  select name from t_address where id = ow.addressid
) ,(
  select name from t_ownertype where id = ow.ownertypeid
) , (
  select ar.name from t_area ar, t_address ad where ar.id = ad.areaid and ad.id = ow.addressid
) from t_owners ow


Tags: Database Oracle MySQL less

Posted on Tue, 26 May 2020 09:57:14 -0400 by tomprogers