数据库管理系统概述英文版课件：6 SQL.pptx

1、COMP231,COMP231,1,SQL,Outline,IntroductionData Definition Language(DDL)Fundamental conceptsData Manipulation Language(DML)Data Definition Language(DDL)Advanced concepts,COMP231,2,Introduction,COMP231,3,Structured Query Language(SQL)结构化查询语言the most widely used relational database language.It was orig

2、inally developed at IBM in the SEQUEL-XRM and System-R projects(1974-1977).SEQUEL(Structured English Query Language)Almost immediately,other vendors introduced DBMS products based on SQL,and it is now a de facto standard,Introduction,COMP231,4,The SQL continuous to evolve in response to the changing

3、 need.The current ANSI/ISO standard for SQL is called SQL:1999Not all DBMS products support the full SQL:1999 yet.,Outline,IntroductionData Definition Language(DDL)Fundamental conceptsData Manipulation Language(DML)Data Definition Language(DDL)Advanced concepts,COMP231,5,Data Definition Language(DDL

4、),COMP231,6,Allows the specification of,The schema for each relationThe domain of values associated with each attributeIntegrity constraints(ICs)The physical storage structure of each relation on diskThe set of indices to be maintained for each relationSecurity and authorization information for each

5、 relation,Domain Types in SQL,COMP231,7,char(n)Fixed length character string,with user-specified length n.varchar(n)Variable length character string,with user-specified maximum length n.int integer(a finite subset of the integers that is machine-dependent).smallint Small integer(a machine-dependent

6、subset of the integer domain type).Numeric(p,d)Fixed point number,with user-specified precision of p digits,with d digits to the right of decimal point.小数点,COMP231,8,real,double precision Floating point and double-precision floating point numbers,with machine-dependent precision.float(n)Floating poi

7、nt number,with user-specified precision by parameter n.,date Dates,containing a(4 digits)year,month and date.time Time of day,in hours,minutes and seconds.,Null values are allowed in all the domain types.Declaring an attribute to be not null prohibits null values for that attribute.,COMP231,9,Schema

8、 Definition in SQL,create table customer,(customer-name customer-street customer-city,char(20)not null,char(30),char(30),primary key(customer-name),Customercustomer-name customer-street,customer-city,COMP231,10,Drop Table,COMP231,11,To remove a relation from a SQL database,we use the drop table comm

9、and:drop table r,Alter Table,COMP231,12,We use the alter table command to add or delete attributes to an existing relation.All tuples in the relation are assignednull for a new attribute.alter table customer add phonechar(10)alter table customer drop phone,Integrity Constraints(ICs)完整性约束,COMP231,13,

10、IC:condition that must be true for any instance of the database.,E.g.The not null constraint means the value of the attribute cannot be null.ICs are specified when the schema is defined.ICs are checked when relations are modified.,COMP231,14,More details will be discussed later.,Outline,Introduction

11、Data Definition Language(DDL)Fundamental conceptsData Manipulation Language(DML)Data Definition Language(DDL)Advanced concepts,COMP231,15,Data Manipulation Language(DML),Basic SQL Query Arithmetic Operation Rename Operation String OperationOrdering the Display of TuplesAggregate Operator Set Operati

12、on DivisionGroup By NULL valueSequence,COMP231,16,Basic SQL Query,The basic form of SQL,relation-list,A list of relation names(possibly with a range-variable after each name).target-list,A list of attributes of relations in relation-list.,DISTINCT target-,COMP231,17,SELECTlistFROM,relation-list,WHER

13、E,qualification,qualificationComparisons(Attr op const or Attr1 op Attr2,),where op is one ofcombined using AND,OR and NOT.DISTINCT,an optional keyword indicating that the answer should not contain duplicates.Default is that duplicates are not eliminated.,DISTINCT target-,COMP231,18,SELECTlistFROM,r

14、elation-list,WHERE,qualification,COMP231,19,SELECT clause,specifies columns to be retained in the result.FROM clausespecifies a cross-product of tables.WHERE clause(optional),specifies selection conditions on the tables mentioned in the FROM clause.A SQL query intuitively corresponds to a relational

15、 algebra expression involving selections,projections,and cross-products.,SELECT DISTINCT a1,a2,an,FROM R1,R2,RmWHERE P,a1,a2,an(,P(R1 x R2 x x Rm),COMP231,20,Note:for our examples we use the tables:,Branch(branch-name,branch-city,assets)Customer(customer-name,customer-street,customer-city)Loan(loan-

16、number,amount,branch-name)Account(account-number,balance,branch-name)Borrower(customer-name,loan-number)Depositor(customer-name,account-number),Assume that customer-name is UNIQUE in this example.(In real life applications,(customer)name is NOT unique.),COMP231,21,Example:Find the names of all branc

17、hes in the loan relation.,SELECT branch-nameFROM Loan,Loan,Result,COMP231,22,To remove duplications,SELECT DISTINCT branch-nameFROM LoanLoan,Result,COMP231,23,branch_name(Loan),Data Manipulation Language(DML),Basic SQL Query Arithmetic Operation Rename Operation String OperationOrdering the Display

18、of TuplesAggregate Operator Set Operation DivisionGroup By NULL valueSequence,COMP231,24,Arithmetic Operations on Retrieved Results,The select clause can contain arithmetic expressions involving the operators,and and operating on constants or attributes of tuples.The query:select branch-name,loan-nu

19、mber,amount*100 from loan,would return a relation which is the same as the loan relations,except that the attribute amount is multiplied by 100,COMP231,25,The where Clause,The where clause specifies conditions that tuples in the relations in the from clause must satisfy.Find all loan numbers for loa

20、ns made at the Perryridge branch with loan amounts greater than$1200.select loan-numberfrom loanwhere branch-name=“Perryridge”and amount 1200SQL allows logical connectives and,or,and not.Arithmetic expressions can be used in the comparison operators.Note:attributes used in a query(both select and wh

21、ere parts)must be defined in the relations in the from clause.,COMP231,26,The where Clause(Cont.),COMP231,27,SQL includes the between operator for convenience.Find the loan number of those loans with loan amounts between$90,000 and$100,000(that is,$and$select loan-number from loanwhere amount betwee

22、n 90000 and 100000,The from Clause,The from clause corresponds to the Cartesian product operation of the relational algebra.,Find the Cartesian product borrowerloanselect*from borrower,loanIt is rarely used without a where clause.Find the name and loan number of all customers having a loan at the Pe

23、rryridge branch.select distinct customer-name,borrower.loan-numberfrom borrower,loanwhere borrower.loan-number=loan.loan-number and branch-name=“Perryridge”,COMP231,28,Note:for our examples we use the tables:,Branch(branch-name,branch-city,assets)Customer(customer-name,customer-street,customer-city)

24、Loan(loan-number,amount,branch-name)Account(account-number,balance,branch-name)Borrower(customer-name,loan-number)Depositor(customer-name,account-number),Assume that customer-name is UNIQUE in this example.(In real life applications,(customer)name is NOT unique.),COMP231,29,Data Manipulation Languag

25、e(DML),Basic SQL Query Arithmetic Operation Rename Operation String OperationOrdering the Display of TuplesAggregate Operator Set Operation DivisionGroup By NULL valueSequence,COMP231,30,The Rename Operation,COMP231,31,Renaming relations and attributes using the as clause:old-name as new-nameFind th

26、e name and loan number of all customers having a loan at the Perryridge branch;replace the column name loan-number with the name loan-id.select distinct customer-name,borrower.loan-number as loan-id from borrower,loanwhere borrower.loan-number=loan.loan-number and branch-name=“Perryridge”,The Rename

27、 Operation,COMP231,32,For convenience,usually,“as”is omitted.In Oracle,“as”is omitted.,select distinct customer-name,borrower.loan-number loan-id from borrower,loanwhere borrower.loan-number=loan.loan-number and branch-name=“Perryridge”,Tuple Variables/Alias,COMP231,33,Tuple variables are defined in

28、 the from clause via the use of the“as”clause.Find the customer names and their loan numbers for all customers having a loan at some branch.select distinct customer-name,T.loan-number from borrower as T,loan as Swhere T.loan-number=S.loan-number,COMP231,34,Tuple Variables/Alias,Find the names of all

29、 branches that have greater assets thansome branch located in Brooklyn.,select distinct T.branch-name from branch as T,branch as Swhere T.assets S.assets and S.branch-city=“Brooklyn”,branch,branch,T,S:a branch in Brooklyn,branches in Brooklyn,Does it returns branches within Brooklyn?,Data Manipulati

30、on Language(DML),Basic SQL Query Arithmetic Operation Rename Operation String OperationOrdering the Display of TuplesAggregate Operator Set Operation DivisionGroup By NULL valueSequence,COMP231,35,String Operations,COMP231,36,Character attributes can be compared to a pattern:%matches any substring._

31、 matches any single character.Find the name of all customerswhose street includes the substring Main.(E.g.,Mainroad,Small Main Road,AMainroad,)select customer-name from customerwhere customer-street like“%Main%”,Data Manipulation Language(DML),Basic SQL Query Arithmetic Operation Rename Operation St

32、ring OperationOrdering the Display of TuplesAggregate Operator Set Operation DivisionGroup By NULL valueSequence,COMP231,37,Ordering the Display of Tuples,COMP231,38,List in alphabetic order the names of all customersselect distinct customer-name from customerorder by customer-name,select distinct c

33、ustomer-name from customerorder by customer-name descasc for ascending order(default)desc for descending orderSQL must perform a sort to fulfill an order by request.Since sorting a large number of tuples may be costly,it is desirable to sort only when necessary.,Ordering the Display of Tuples,COMP231,39,List the names of all customers in the ascending order of customer-city and then descending ord