Previously, we detailed the changes and additions to Oracle. Today let's go on to learn more about Oracle's queries.

Queries in Oracle are a big deal!!! Follow the steps of brilliant sir, move, move ~~

 

Small bag of knowledge

Before that, let's talk about a little bit of Oracle commentary, which is very helpful for our development, oh!!!

Practical:

comment  on table test1.ID  is ''Primary key';

Can you understand the meaning of the corresponding field more clearly, so can you operate faster?

 

Next, let's enter the query!

Query_Form Query

1. Standard SQL and dialects

 

2. Standard DQL grammar

 

 

3. Simple Query

--Exact Query  
--1 Query owner records for meter number 30408
select * from t_owners where WATERMETER='30408' ;

--Use aliases for tables
select * from t_owners ow as where ow.WATERMETER='30408' ;



--Fuzzy Query
--2 Query for owner name containing owner record of "Liu"
--like Sentence  %Match multiple characters  _Match 1 field

select * from t_owners ow where ow.NAME like '%Liu%';


--and operator
--3 Query the owner record whose name contains "Liu" and the house number contains 5
select * from t_owners ow where ow.name like '%Liu%' and ow.HOUSENUMBER like '%5%';


--or operator
--4 Query owner's name for owner records containing "Liu" or house number containing 5
select * from t_owners ow where ow.name like '%Liu%' or ow.HOUSENUMBER like '%5%';


--and and or Mixed use of operators
--5 Query for owner name containing "Liu" or house number containing 5 owner records, and address number 3 records.
--and Priority and or, If executed first or, Parentheses are required
--1.Unbracketed,Execute First and
select * from t_owners ow where ow.name like '%Liu%' or ow.HOUSENUMBER like '%5%' and ow.ADDRESSID=3;

--2.Parentheses,Execute First or
select * from t_owners ow where (ow.name like '%Liu%' or ow.HOUSENUMBER like '%5%') and ow.ADDRESSID=3;


-- Range Query
--6 Query records with numbers greater than or equal to 10000 and less than or equal to 20000
--1. > <
select * from t_account a where a.USENUM >=10000 and a.USENUM<=20000;

--2. between ..to..
select * from t_account a where a.usenum between 10000 and 20000;


-- Null Value Query
--7 query T_PRICETABLE In table MAXNUM Empty record

select * from T_PRICETABLE p where p.maxnum is null; 

--8 query T_PRICETABLE In table MAXNUM Records that are not empty

select * from T_PRICETABLE p where p.maxnum is not  null; 

 

 

4. Remove duplication and sorting

--1.Sort ascending
--Requirements: For T_ACCOUNT Tables are sorted in ascending order by usage

select * from T_ACCOUNT t order by t.USENUM asc;


--2.Sort in descending order
--Requirements: For T_ACCOUNT Sort tables in descending order by usage
select * from T_ACCOUNT t order by t.usenum desc;



--demand:Yes T_ACCOUNT Table by month Descending,If the same according to usenum In ascending order
select * from T_ACCOUNT t order by t.month desc ,t.usenum asc ;

 

 

5. Pseudo Columns

Pseudo columns are unique to oracle, and they are also real columns. They are used for query operations and cannot be added or deleted.

ROWID: A unique identifier on a physical file that uniquely distinguishes this record

 

• rowid
• There are identical records of data in the mysql table, and if you operate on one, all data will be modified.

 

Oracle uses rowid to differentiate each data, and there is no case where one action affects multiple rows.

 

 

 

 

 

6. Aggregation Functions

• The aggregation statistics of ORACLE are implemented by grouping functions, which are consistent with MYSQL.

• Aggregation function: Processing query results into a row and a column of data by providing a function.

  • Features: Aggregate functions do not compute null values

Examples of actual warfare:

--(5) Aggregate statistics ----No calculation null value
--(1)Summation  sum
--1 Total water consumption for all users in 2012
select sum(a.USENUM) from t_account a where a.YEAR='2012';


--(2)Average avg
--2 Statistics the average of all water consumption (words) in 2012 

select avg(a.usenum) from t_account a where a.year='2012';


--(3)Maximum max
--3 Statistics of maximum water consumption in 2012 (word count)
select max(a.usenum) from t_account a where a.year='2012';


--(4)Minimum Value min
--4 Statistical minimum water consumption for 2012 (word count)
select min(a.usenum) from t_account a where a.year='2012';



--(5)Number of statistical records count
--5 Number of statistical records count
--1
select count(a.usenum) from t_account a ;

--2
select count(*) from t_account a ;






--2. Grouping Aggregation Group by --+++++++++++++++++++++++++++++++++++++++++++++++++

--Demand: Total water bills for 2012 grouped by Region

select a.areaid region,sum(a.money) Total Annual Water Cost Count   from t_account a  where a.year='2012' group by a.areaid;



--3. Conditional query after grouping having 
--Requirements: Query areas with total water charges greater than 169000 in 2012 and total water charges
select a.areaid region,sum(a.money)Total water charges from t_account a where a.year='2012' group by a.areaid having sum(a.money)>=169000; 

 

That's the end of the day. Oh, see you next time, buddies~~

 

 

Congratulations, you know a little more!!!

The more you know, the more you don't know!

~Thank you for reading like-minded, your support is my greatest motivation to learn! Go on, strangers work together, reluctantly!!