Oracle (zero) -- & gt; pseudo-column and group aggregation (starting from Oracle, relatively beginner ~), Oracle

Source: Internet
Author: User

Oracle (zero) --> pseudo-column and group aggregation (starting from Oracle, relatively beginner ~), Oracle
In the beginning ~It's time to start school .. What does it have to do with me ..), After all, I took up my job even though I didn't know what it was during the summer vacation. I watched my classmates show around me. = It's too much. Just try again .. I learned Oracle in my junior year, but now I don't think there is anything about it, and I have never taught the trigger or the Stored Procedure teacher, and I have never read it myself. Actually... After all, what Huawei engineers say is still vague .. No matter what it is, first understand what it is and then know how to use it in the project. This process is basically based on the PPT in the learning process, and there are too many concepts to move forward. You don't like words very much. You all love to see the pictures ~ Even if the video is too exciting ~ Haha. Start with pseudo columns and SQL functions ~1. Before discussing pseudo columns and group aggregation, let's talk about the use and precautions of select statements and dinstinct.1) the operation in the select statement uses the column aliasTo display the list of all books stored in the database, you need to display the column title as "Titil of books". You can list the column aliases after the column name in the select clause.SQL: SELECT title AS "Title of books", category FROM books;Note the optional keyword AS to distinguish the column alias and column name.Criterion: If the column name contains spaces, special characters, or you do not want to display it in uppercase letters, you must include it in. If only one word is contained, "" is not required "".2) use distinct and unique to delete duplicate columns.
2. pseudo Columns1) The pseudo column in Oracle is like a table column, but it is not stored in the table;2) A pseudo column can be queried from a table, but its values cannot be inserted, updated, or deleted;3) Common pseudo columns include ROWID and ROWNUM.
ROWID: the address of the row stored in the table. This address can uniquely identify a row in the database. You can use the ROWID pseudo column to quickly locate a row in the table.
ROWNUM: the number of rows in the returned result set. You can use it to limit the number of rows returned by the query.
3. Common Aggregate functions (group functions)Also known as multiline functions, they can process each row and return a result. Commonly Used group functions include SUM, AVG, COUNT, MIN, and MAX.
Aggregate functions aggregate all records. Sometimes we need to group records before aggregation.The grouping of records is implemented BY the keyword GROUP by, for example, group BY f1, f2, f3, if and only when two records reach an agreement on all attributes f1, f2, and f3, they are in the same group.Example: average profit of each category of booksSQL: SELECT category, TO_CHAR (AVG (retail-cost), '192. 99') as profit FROM books GROUP BY category.
Group by statements1. If a GROUP function is used in the select clause, separate columns listed in the select clause must be listed in the group by clause;2. Columns used to group data in the group by clause do not need to be listed in the select clause. They are included in the select clause only to specify a group in the output;3. column aliases cannot be used in the group by clause;4. The results returned by the select statement containing the group by clause are displayed in ascending order of the columns listed in the group by clause. to display the results in different order, you can use the order by clause.
HAVING clause1. Used to limit a group returned by a query;2. If you need to use group functions to restrict groups, you must use the having clause, because the where clause cannot contain group functions. The Having clause specifies the items that will be displayed in the result. That is, the having clause acts as the where clause of the group.Example: shows the types of books with an average profit of more than $15SQL: SELECT category, TO_CHAR (AVG (retail-cost), '192. 99') as profit FROM books GROUP BY category Having AVG (retail-cost)> 15.
Order by clause1. Used at the end. The default value is ascending.
Secondary sorting1. When the order by clause only specifies one column, it is called "primary sorting". If the two or more rows in the sorting are identical, the secondary sorting provides another method for sorting.For example, if you want to query customers in descending order of the State, multiple customers in a specific State will be sorted by city in ascending order.SQL: SELECT lastname, firstname, city, state FROM customers ORDER BY state desc, city;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.