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!!!
comment on table table name is'comment';
comment on column column name is'comment';
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!
1. Standard SQL and dialects
2. Standard DQL grammar
select distinct * | column name as alias, list 2 as alias 2... | aggregate function
from table name as alias, table name 2As alias 2,....
where query criteria
group by grouping field having grouping condition
order by sort field asc | desc,....
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
Each row in the table has a physical address in the data file, and the ROWID pseudo column returns the physical address of that row.ROWID allows you to quickly locate a row in a table.ROWID values uniquely identify a row in a table.Since ROWID returns the physical address of the row, you can use ROWID to show how the row is stored.
- 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.
Query statement: * select rowID,t.* from T_AREA t
In the result set of a query, ROWNUM identifies a line number for each row in the result set, returns 1 for the first row, 2 for the second row, and so on.ROWNUM pseudo columns limit the number of rows returned in the query result set.
Query statement: select rownum,t.* from T_OWNERTYPE t
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!!