The first chapter 3,6

2010-10-31  来源:本站原创  分类:Database  人气:128 

3.3 Grouping rows
3.3.1 use the GROUP BY clause to group rows
GROUP BY clause can be used to line the same group as having more than one part of the column value
2. Line group can use the aggregate functions. Aggregate function to calculate the rows in each group, and each row to return a result.
3. By default, GROUP BY clause, grouping column values based on the order of the rows in ascending order. ORDER BY clause is used to specify which column to sort.
Not necessarily in the SELECT clause in the GROUP BY clause contains columns used.
3.3.2 misuse of aggregate function calls if the query contains aggregate functions, and the selected column is not in the aggregate function, then these columns must be in the GROUP BY clause.
Also, can not be used in the WHERE clause to limit rows aggregate functions. If you attempt to do so, there will be the following error: ORA-00934: group function is not allowed here.
The reason why this error occurs because the WHERE clause can only be used on a single line rather than the packet filter. To filter the packet line, you can use the HAVING clause.

Line using the HAVING clause 3.3.3 Packet Filter
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...;
But the HAVING clause must be used together with GROUP BY.
3.3.4 combined WHERE clause and GROUP BY when used this way, WHERE clause to filter the returned rows first, then GROUP BY clause groups rows on the reservation.
3.3.5 used in combination with WHERE, GROUP BY and HAVING clause when used this way, WHERE clause to filter the returned rows first, then GROUP BY clause groups rows on the reservation, the last group HAVING clause to filter rows, and finally ORDER BY and then specify which column to sort.

6 subquery
6.1 Types of subqueries
6.2 write a single line single subquery subquery does not return to the outside of the SQL statement results, or only the return line. Subquery SELECT statement can be placed in WHERE clause, HAVING clause or the FROM clause.
SELECT FIRST_NAME, LASST_NAME
FROM COUSTOMERS
WHERE CUSTOMER_ID =
(SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE LAST_NAME = 'Brown');

6.2.1 used in the WHERE clause subquery
SELECT FIRST_NAME, LASST_NAME
FROM COUSTOMERS
WHERE CUSTOMER_ID =
(SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE LAST_NAME = 'Brown');
HAVING clause 6.2.2 in this example uses a subquery to retrieve the average price lower than similar products that the average price of the product product_type_id maximum and average prices:
SELECT product_type_id, AVG (price)
FROM product
GROUP BY products_type_id
HAVING AVG (price) <
(SELECT MAX (AVG (price))
FROM products
GROUP BY product_type_id);
6.2.3 In the From clause subquery (inline view)
This example in the outer query to retrieve the table product_id from products and price list, in the sub-query to retrieve a number of products have been purchased:
SELECT prds.product_id, price, purchases_data.product_count
FROM products prds,
(SELECT product_id, COUNT (product_id) product_count
FROM purchases
GROUP BY product_id) purchases_data
WHERE prds.product_id = purchases_data.product_id;
Subquery to retrieve the table from the purchases prodcut_id and COUNT (product_id), and returns to external inquiries. Can see that the output sub-query is just outside the FROM clause of another query a data source.
Subquery can not contain the ORDER BY clause, you must make any sort of external query.
6.3 The preparation of multi-line multi-row subqueries subquery can return to the outside of the SQL statement that one or more rows. Back to process multiple rows of sub-queries, queries can be used outside IN, ANY or ALL operator.
6.4 write more Liezi query subquery does not actually limit can only return one: we can write the sub query returns multiple columns. The following example retrieves the lowest price for each product type of products:
SELECT product_id, product_type_id, name, price
FROM products
WHERE (product_type_id, price) IN
(SELECT product_type_id, MIN (price)
FROM products
GROUP BY product_type_id);
Sub-query returns two columns: product_type_id and the minimum price list; and the outer query WHERE clause in parentheses to contain two columns: product_type_id and price.

6.5 Relevance write correlated subqueries subquery references the outer query will be one or more columns. This subquery is said to be known as a correlated subquery because the subquery does with external inquiries. When the answer to rely on external query contains a value for each row, the
Usually need to use correlated subqueries.
The following example retrieves similar products that cost more than the average price of the product:
SELECT product_id, product_type_id, name, price
FROM products outer
WHERE price>
(SELECT AVG (price)
FROM products inner
WHERE inner.product_type_id = outer.product_type_id);
The associated sub-query, each row in the outer query is passed to the first row subqueries. Subquery in order to read external query the value of each row, and apply it to check on the child until all the lines in the outer query is processed so far.
And then return the query results.
EXISTS subquery operator is used to check the existence of rows returned. Although EXISTS can also use the non-correlated subqueries, but the use of EXISTS is used more commonly associated with sub-query.
The following example uses the EXISTS retrieve those responsible for managing employee records of other employees:
SELECT employee_id, last_name
FROM employees outer
WHERE EXISTS
(SELECT employee_id FROM employees inner
WHERE inner.manager_id = outer.emplyee.id);
EXISTS subquery as just check the existence of rows returned, so do not have to return to a query; can just return a constant value. This can improve query performance. The following query was rewritten the example above: sub-query is simply a return to constant values:
SELECT employee_id, last_name
FROM employees outer
WHERE EXISTS
(SELECT 1 FROM employees inner
WHERE inner.manager_id = outer.emplyee.id);
Associated with the use of the NOT EXISTS subquery
SELECT product_id, name
FROM products outer
WHERE NOT EXISTS
(SELECT 1
FROM purchases inner
WHERE inner.product_id = outer.product_id);
EXISTS and NOT EXISTS with IN and NOT IN comparison:
EXISTS just check the existence of the line, while the actual value of the IN will have to check the existence.
Generally, EXISTS IN than the high performance and should therefore be possible to use EXISTS, not IN.
In the preparation of NOT EXISTS and NOT IN using the query must be cautious. When a value list contains a null value, NOT EXISTS returns true, the NOT IN returns false.
The following uses the NOT EXISTS, retrieve those products table does not have any products Product Type:
SELECT product_type_id, name
FROM product_types outer
WHERE NOT EXISTS
(SELECT 1
FROM products inner
WHERE inner.product_type_id = outer.product_type_id);

PRODUCT_TYPE_ID NAME
--------------------------- ----------
5 Magazine
NOT IN now use the above example rewritten:
SELECT product_type_id, name
FROM product_types
WHERE product_type_id NOT IN
(SELECT product_type_id FROM products)
No row selected.
The reason does not return rows, because the sub-query returns a list of values product_type_id, which contains a null value. The value of the product product_type_id is empty. Therefore, the external query NOT IN operator returns false, so eventually did not return any rows. This problem can use the NVL () function to convert null value to a value solution
SELECT product_type_id, name
FROM product_types
WHERE product_type_id NOT IN
(SELECT NVL (product_type_id, 0) FROM products);
This time, return the record:
PRODUCT_TYPE_ID NAME
--------------------------- ----------
5 Magazine

6.6 The preparation of nested subqueries subquery is nested in the SELECT, UPDATE, or DELETE statement inside the query.
Should minimize the use of nested sub-query technology, since the use of table joins, the query performance even higher.
Write a query that contains sub-6.7 UPDATE, and DELETE statements in the UPDATE statement, you can set the value of the new single column sub-query returns the results.
UPDATE employees
SET salary =
(SELECT AVG (high_salary)
FROM salary_grades)
WHERE employee_id = 4;
WHERE clause in the DELETE statement, you can use sub-query returns the results.
DELETE FROM employees
WHERE salary>
(SELECT AVG (high_salary)
FROM salary_grades);

7 Advanced search

7.8 Use of function
7.8.2 using the rating function rating function (ranking function) is used to calculate grade points, n fragmentation and so on.
RANK () and DENSE_RANK () function can calculate the data item in the group's ranking. The difference between these two functions is to handle the way data items are equal: RANK () in the event of the same element class ranking position will be left out, and DENSE_RANK () is not. For example, if sales by product type approval rating, tied for first place in the two product types, then RANK () these two types are set to first, while the third type is the next product. DENSE_RANK () is also all set to the first of these two types, and the next is the second product type.
Need to be grouped into the sub-group, the PARTITION BY clause can be used in combination and analysis functions.

Using the ROW_NUMBER () function
ROW_NUMBER () starting at 1 for each packet record to return a number. The following query shows the ROW_NUMBER () usage:
SELECT
prd_type_id, SUM (amount),
ROW_NUMBER () OVER (ORDER BY SUM (amount) DESC) AS row_number
FROM all_sales
WHERE year = 2003
GROUP BY prd_type_id
ORDER BY prd_type_id;

PRD_TYPE_ID SUM (AMOUNT) ROW_NUMBER
------------------- ------------------- ------------ -------
18882
21115
33333
42224
51

相关文章
  • Chapter III of the essence of software testing 2009-07-29

    First, test the principle of These principles can be regarded as software testing and software development "rules of the road" or "knowledge of life", each for a thorough understanding of the principle of the whole process is a valuabl

  • Open-source technology-RIA chapter in the manual selection of Flex 2009-09-30

    Open-source technology-RIA chapter in the manual selection of Flex With the development of Web technology and the Internet has become more sophisticated, Web application of the requirements of increasing the hope that its bearer more business logic t

  • JBoss EJB 3.0 Chapter II: References and injected 2009-10-09

    JBoss EJB 3.0 Chapter II: References and injected JBoss EJB3.0 RC6-PFD http://www.jboss.org/jbossejb3/docs/reference/build/reference/en/html/index.html 2 references and the injection 2.2 EJB reference * @ EJB's mappedName () manufacturers custom, JBo

  • Chapter 5 Spring AOP: Spring's aspect-oriented programming 2010-03-29

    Chapter 5 Spring AOP: Spring's aspect-oriented programming 5.1. The concept of aspect-oriented programming (AOP) provides another perspective from the program structure in order to improve the object-oriented programming (OOP). Object-oriented will b

  • What is the first chapter Android? 2010-03-29

    Key skills and concepts l history of embedded programming l About the Open Mobile Alliance l Android beginning of the main screen surface It is no exaggeration to say that the traditional desktop application developers have been pets for a very long

  • Chapter II download. Install Eclipse 2010-03-29

    Key skills and concepts l Choose a development environment l download the Eclipse l install and configure the Eclipse Android application is to use java to develop. Android itself is not a language, but rather a platform for running applications. For

  • Chapter III download. Install Android SDK 2010-03-29

    Key skills and concepts l download the Android SDK l use the update feature in Eclipse l download, install and configure Eclipse's Android plug-in l Detection of PATH Statement Previous section, you have to download and install the main development e

  • Chapter IV to explore Android SDK 2010-03-29

    Key skills and concepts l using the Android SDK documentation l using the Android SDK tools l use application examples l to understand the life cycle of Android applications Since you have already built good development environment, you can explore t

  • Chapter V Application: Hello World! - On the 2010-03-29

    Key skills and concepts l Create a new Android project. l Use the View l Use a text view l Change main.xml file l to run applications in the Android emulator In this chapter, you will create the first Android event. This chapter studies the applicati

  • Chapter V Application: Hello World! - Next 2010-03-29

    Or Hello World! In this section, you will create another Hello World! Android applications. But this time you are using coding UI, rather than using xml file - in fact you will do more work. The first step is to remove main.xml in TextView code. Here

  • Chapter VI using the command-line tools and Android simulator - on the 2010-03-29

    Key skills and concepts l using the Android SDK command-line tool l Create a command environment l through the shell-side navigation Android Service l to use Android SDK in Linux At present, this book has covered a number of broad themes to let you k

  • Chapter VI using the command-line tool with the Android emulator - Next 2010-03-29

    Since you placed in the Android application server, you can delete it. Rm HelloWorldCommandLine.apk use the command syntax to remove the application. The following illustration shows the rm command, if run properly, it will not again feedback. And th

  • Android Getting Started chapter of the ListView 2010-03-29

    Android started sixth chapter of the ListView ListView is a frequently used controls, ListView inside of each sub-item Item can make a string, it can be a combination of the control. ListView first talk about the realization of: 1. Ready to ListView

  • Chapter VII of the use of Intents with the Phone Dialer - on the 2010-03-29

    Chapter the use of Intents and Phone Dialer Key skills and concepts l the use of Intents l to create code that interacts with the cell phone hardware l understand the difference between dialing and calling So far, this chapter has been introduce you

  • Chapter VII of the use of Intents with the Phone Dialer - in the 2010-03-29

    Activity assigned to you for the correct permissions, you first need to know what permissions you need to allocate. The correct example is the use of Dialer Activity. Access Dailer Activity managed by CALL_PHONE permission. Through the allocation of

  • Chapter VII of the use of Intents with the Phone Dialer - Next 2010-03-29

    EditText view to achieve You need to add to your Activity love a view allows the user to enter some text. Then you parse the text and send it to the previous section of Intent call. Because all views are coming from the basic view of integration. In

  • Chapter VIII of the list. Menu, as well as other view - on the 2010-03-29

    Key skills and concepts l Construction Activity l Use Android Menu l Use AutoCompleteTextView This chapter of the Views and Intents to do an in-depth research, which is for the novice Android need to know the most important features. The two entities

  • Chapter VIII of the list. Menu, as well as other views - Next 2010-03-29

    Here is your complete AndroidManifest.xml project file: <? xml version = "1.0" encoding = "utf-8"?> <manifest xmlns: android = http://schemas.android.com/apk/res/android package = "android_programmers_guide.AndroidViews &

  • Window to use android NDK develop Android applications - Getting Started chapter 2010-03-29

    Original articles, welcome reproduced, reproduced when the original address and the author must indicate 1. To build the environment 1. Download and install Cygwin, the installation process takes a little time, a bunch of online tutorials, here is no

  • In-depth study of SSL [Chapter] - What is SSL, SSL How does it work 2010-03-29

    Some time ago to study the relationship between the project the next SSL, there are some doubts about their feelings and experiences, and recorded together, and we intend to share and discuss the issue. This section intends to write some of the chapt