Database Study Note 1 SQL

--------------------- The Union interset except T operation on the relational function of the SQL set operation corresponds to the operation and intersection and difference in the relational algebra. The relations involved in the calculation must be compatible, that is, they must contain the same property set. 1 Union operation to find out all customers with accounts, loans, or both in the bank, we can write a query statement: (select customer_name from depositor) Union (select customer_name from borrower) // The union operation automatically removes duplicates. If we want to retain duplicates, we must use Union all instead of Union (select customer_name from depositor) Union all (select customer_name from borrower) 2 intersect: Find out the customers who have accounts and loans at the same time in the bank. We can write: (select customer_name from depositor) intersect (select customer_name from borrower) intersect is the same as the Union operation, and the operation is repeated automatically, if you want to save Repeat, Intersect all must be used to replace intersect3 counter T. To find out the users who have an account in the bank but have no loans, write: (select customer_name from depositor) Counter T (select customer_name from borrower) ------------------------- the aggregate function is a function that returns a single value based on a set of values (set or multiple duplicated sets. SQL provides five built-in Aggregate functions: average: AVG minimum: min maximum: Max Total: Sum count: countsum and AVG input must be a number set, other operators can be used on non-numeric data types, such as strings. Example: Find the account balance and average value of the perryidge sub-branch. The query is written as follows: Select AVG (balance) from accountwhere branch_nam = 'perryidge' the query results have only one attribute relationship, including only one row, the value in this row corresponds to the average balance of the perryridge sub-branch. (Optional) You can use the as clause to assign a new name to the attribute in the result relation. Sometimes we not only want to apply the clustering function to a single set of tuples, you also want to apply the group by clause to a set of tuples. in SQL, you can use the group by clause to achieve this. One or more attributes in the group by clause are used to construct a group. tuples with the same value on all attributes in the group by clause are divided into one group. Example: Find the average account balance of each sub-branch select branch_name, AVG (balance) from accountgroup by branch_name example: Find the number of sub-branch depositors // remove duplicate depositors select branch_name, count (distinct customer_name) from depositor, accountwhere depositor. account_number = account. account_numbergroup by branch_name is sometimes more useful for grouping conditions than for tuples. For example, we may only be interested in sub-branches with an average account balance heavy rain of $1200. This condition is not applicable to a group formed by a group by clause for a single tuples. To express such a query, we use the having clause of SQL. The predicates in the having clause take effect only after the group is formed. Therefore, clustering functions can be used. We use SQL to express this query as follows: Select branch_name, AVG (balance) from accountgroup by branch_namehaving AVG (balance)> 1200 ------------------------- null nullsql allows null to indicate missing information about a property value, we use the special keyword null in the predicate to test the null value. Therefore, to find the loan number with the amount as null in the loan relationship, we can write the statement as follows: select loan_numberfrom loanwhere amount is null predicate is not null is used to detect non-null values ---------------------- nested subquery To Find Out customers with accounts and loans in the same bank select distinct customer_namefrom borrowerwhere customer_name in Customer_name from depositor) in the comparison SQL statement <some, <= Some,> some,> = Some, = some, <> some indicates the comparison between sets. = some is equivalent to in. <all, <= All,> All,> = All ---------------------------------- the view is not a part of the logical model, but the link visible to the user as a virtual link becomes the create view V as <query expression> intuitively, at any given moment, the view Link's tuples are the results of the query expression defined in the view at this time. Therefore, if a view link is calculated and stored, once the relationship used to define the view is modified, the view will expire. To avoid this, the view is generally implemented as follows: when we define a view, the database system stores the view definition instead of the calculation result of the relational Algebra Expression defining the view. Once the view relationship appears in the query, it is replaced by the stored relational expression. Therefore, no matter whether the query is executed properly, the view relationship is recalculated. ------------------------- So far, our attention has been focused on extracting information from the database. The following describes how to use SQL statements to add, delete, and modify information. 1. The expression of a Delete request is very similar to that of a query. we can delete the entire tuples, however, we cannot only delete the values of certain attributes. in SQL, the delete statement format is as follows: delete from rwhere P where p is a predicate, and R represents a relationship. If the WHERE clause is omitted, all the tuples in R are deleted. 2 insert into accountvalues (). 3 Update account set balance = balance * 1.05 update accountset balance = case When balance <= 10000 then balance * 1.05 Else balance x 1.06 End11: 02 2009-2-18