SQL technology Daquan (3)
SQL step gradual (19) ----- Distinct and exclude copy Distinct and exclude copying If you are listed in all the IDs and names that bought antique, it is obvious, you may list all customers without considering some Customers have bought more antiques, so you will find some data to be repeated. This means that you need to inform SQL to exclude the copy of the copy, regardless of how many antiques have only been listed once. To achieve this, you can use the Distinct keyword. First we need to give an antiqueowners table to give the customer's LastName and First detailed data. However, you have to consider the SellerID column in the Antiques table is an external code for the Antiqueowners table, so customers can only list the id and names of the ID and names in the AntiquEowners table. We also want to exclude the occurrence of sellerid replication in the data of the column, so we have to use DISTINCT on the repeated column. In order to prevent copying, we also want to arrange LastName in alphabetical order, then arrange the firstname finally OwnerID in alphabetical order, so we must also use the Order By clause, the specific statements are as follows: Select Distinct Sellerid, OwnerlastName, OwnerfirstNameFrom antiques, AntiqueOwnerswhere Sellerid = OwnerIdorder by OwnerlastName, OwnerfirstName, Ownerid In this example, because everyone bought a lot of antiques, so we list the lasname to the alphabetical sequence of all antique owners SQL step by step (20) ------ Aliases, in and subquery Aliases, in and subquery In this section, we will introduce Aliases, IN, and subquery. First let's take a look at the query statement, it searches for all the customer's lastname and what kind of goods they set, the statement is as follows: select it.Ownerlastname last name, ord.itemdesired item orderdfrom ORDERS ORDERD, ANTIQUEOWNERS OWNWHERE ORD.OWNERID = OWN.OWNERIDAND Ord.ItemFrom Antiques; the result of this query statement is: Last Name Item Ordered -------------------- Smith TableSmith Deskakins Chairlawson Mirror is good Explain the above statement: "Last Name" and "Item Ordered" give the reported data header. OWN & ORD is Aliases (alias), they can be queried in the FROM clause, and you can add a number of points to add a name. This avoids the ambiguous case, especially when the two columns are named Ownerid, especially in the Equijoin WHERE clause, and the little number is notified that we use the different tables of different tables. Here to note that the ORDERS table is first listed in the FROM clause, and ensure that the AntiquEowners table is only used for detailed information (Last Name). More important, the AND forced IN sub-query in the WHERE clause is called ("= any" or "= some" is equivalent to using IN).
But what did this do it? When this subquery is completed, it returns all Items of the Antiques table because there is no WHERE clause. Then, for rows listed from the OrderS table, ItemDesired must list an item in the Items list returned from the Antiques table, and then listed in the case where the order may have another owner to fill in. You can think about this method: subquery returns a series of items from each ItemDesired in the ORDERS table; if ItemDesired is returned from the antiques table, the condition is true. SQL step gradual (21) ------ More Subprints More Subbstroom We can use the SELECT query statement to include a SELECT sub-query statement. For example, first we have listened all customers who purchase valuables, the standard of valuables is more than 100 yuan of items than all sold items. The specific statement is as follows: Select OwneridFrom Antiqueswhere Price> The above child query statement is calculating the average price of the item plus 100 yuan and searches all OwnerIds whose Price greater than this value in the antiques table. Here you can use Distinct OwnerID to exclude copying phenomenon. The following statement lists all LastName in the Antiqueowners table: Select OwnerLastnameFrom Antiqueownerswhere OwneriD = (Select Distinct BuyeridFrom Antiqueowne); this subquery returns a series of customers, and only when the item owner The ID appears in the list of subqueys, and the LastName of the antique owner will appear. To update this example, we assume that a customer bought a bookcase, his FirstName wrong in the database, should be John: UPDATE ANTIQUEOWNERSSET OWNERFIRSTNAME = 'John'WHERE OWNERID = (SELECT BUYERIDFROM ANTIQUESWHERE ITEM =' Bookcase '); above Subride in the statement first searched for the BuyerID of the customer who bought Bookcase, and then updated his firstname in the outer query. SQL step gradually (22) ------ exists and allexists and allexists use a subquery as the condition, only when the child is returned, this condition is true, if the child does not return any line conditions . If the store is handling Chair, there is a customer want to see the list of all owners, you can use the exSist, the statement is as follows: select ownerfirstname, OwnerlastNameFrom antiqueownerswhere exists (select * from antiqueswhere item = 'chair); if at the Antiques column There is chair in the middle, then the child query will return or more lines, so that the exists clause is true, then let SQL list the owner. If there is no chairl, there is no line to return, the condition is false. All is another unusual keyword because all the ALL queries can usually be performed in different ways and may be a simpler way.
For example, please: select Buyerid, itemfrom antiqueswhere price> = all (select pricefrom antiques); The above statement will return the highest price Item and its buyer. The subquery returns all the Price columns in the Antiques table, and the outer query queries the antiques table by line, and if its Price is greater than the prices in the (or all) column, it will be listed, it is the most Good price Item. Here, "> =" is required is the highest price Item is equal to the highest price in the list because this item is in the Price column. SQL step gradually (23) ------ UNION and external connection UNION and external connections Some, you can think about the results of multiple queries together, combine their output, you can use the Union keyword. In order to merge the output of the following two queries: Show all buyers' ID and customers already have the following statements: select BuyeridFrom antiqueownersunionSelect OwneridFrom ORDERS; here, pay attention to SQL requires SELECT list must match, namely the data type matching . In this example, BuyerID and Ownerid are the same data type, which is integer (integer). At the same time, it is also that SQL but uses Union's use will be automatically replicated. And in a single query, you must use Distinct. Outer join is usually in a Join query, and the line is not included in Join, especially when the constant text "Flags" is included. Let's first look at this query: SELECT OWNERID, 'is in both Orders & Antiques'FROM ORDERS, ANTIQUESWHERE OWNERID = BUYERIDUNIONSELECT BUYERID,' is in Antiques only'FROM ANTIQUESWHERE BUYERID NOT IN (SELECT OWNERIDFROM ORDERS); the first query to do A connection is listed to list each Owner in both tables and place a tag cable after the ID is placed. This Union merges this list and the following list. The second list is to list the ID not in the ORDERS table, which generates a list of IDs outside the Join query, which is listed using the reference label. This may be the easiest way to generate this list. This concept is useful for primary keys related to the external code, but some primary keys are NULL. For example, in a table, the primary key is Salespers, and the primary key in other tables is Customers, and their salesperson is listed in the same line. However, if SalesPerson doesn't have Customers, the name of this person will not appear in the Customer table. If all SalesPersons lists, they should be connected to the outside. SQL step gradual (24) ------ embedded SQL embedded SQL In order to better understand SQL, this section will be described using a specific example. Embedded into the SQL allows the program to connect the database and includes SQL code to the program so that the database can be used, operated, and processing data in the program. The following is a routine that is embedded in the use of SQL using C language, which will print a report; this program must pre-compile the SQL statement before normal compilation.
