数据库系统概论 An Introduction to Database Systems 第三章 SQL 语言 ( 之基本查询部分 ) 2016, 3, 17
上节课 SQL: SQL86,SQL89,SQL92,SQL99 DDL,DML,DCL DDL 基本表, 索引, 视图 CREATE TABLE,CREATE INDEX ALTER TABLE, DROP TABLE, DROP INDEX 数据字典与 DDL 的处理
Major Components of SQL DDL --- Data Definition Language used to define tables. DML --- Data Manipulation Language used to manipulate data directly. Query:Select Modify: Insert, Update, Delete DCL---Data Control Language
Basic Structure of an SQL Query select target-attribute-list from table-list where conditions Relational algebra correspondence: select-clause projection (π) from-clause Cartesian product ( ) where-clause selection (σ)
基本查询 (1) Relation schemas under consideration: Students (SSN, Name, GPA, Age) Courses (Course_no, Title, Dept_Name) Enrollment (SSN, Course_no, Grade)
基本查询 (2) Find the SSN, Name and GPA of all students whose GPA is higher than 3.8. SQL> select SSN, Name, GPA 2 from Students 3 where GPA > 3.8;
基本查询 (3) To retrieve all attributes of a relation, use the shorthand *. Find all students whose GPA is higher than 3.8. select * from Students where GPA > 3.8 The where-clause may be absent. Find the Names of all students. select Name from Students
基本查询 (4) The select command does not remove duplicate rows in the result table. Duplicate rows can provide additional information. It may be costly to remove duplicate rows. To remove duplicate rows, select distinct should be used.
基本查询 (5) Example: Find the Names of all students without duplicate rows. select distinct Name from Students select all can be used to explicitly request that all duplicate rows are kept. Questions: How select distinct is implemented? Is the following query an efficient query? select distinct SSN, Name from Students
基本查询 (6) Example: Find the names of all students whose GPA is between 3.5 and 3.8. select Name from Students where GPA between 3.5 and 3.8 not between... and is the opposite of between and...
基本查询 (7) Example: Find the course numbers and titles of all courses whose title contains systems. select Course_no, Title from Courses where Title like `%systems%' % matches 0 or more characters.
基本查询 (8) Example: Find all students whose name starts with M and is six-character long. select * from Students where Name like `M _' _ matches exactly one character not like is the opposite of like.
基本查询 (9) Example: Find all students whose name contains a _. select * from Students where Name like `%\_ % escape \ Escape character can be defined in a query.
基本查询 (10) Consider relation: Employees (SSN, Name, Age, Manager_SSN) Find all employees who have no managers. select * from Employees where Manager-SSN is null Find all employees who have a manager. select * from Employees where Manager-SSN is not null
Computation in SQL (1) Consider Employees (SSN, Name, Monthly_Salary) Example: Find the SSN, name and annual salary of each employee. select SSN, Name, 12*Monthly_Salary from Employees Result: SSN Name 12*Monthly_Salary ------ ------- -------------------------
Computation in SQL (2) Naming the expression in the output. In Oracle, 12*Monthly_Salary will be used for our example. A more general solution is to assign a new name to the column. select SSN, Name, 12*Monthly_Salary as Annual_Salary from Employees as is optional. Result: SSN Name Annual_Salary ------ ------- -------------------------
Computation in SQL (3) 例使用列别名改变查询结果的列标题 SELECT Sname NAME,'Year of Birth: ' BIRTH,2014-Sage BIRTHDAY FROM Student; 输出结果 : NAME BIRTH BIRTHDAY ------- ---------------- --------- 李勇 Year of Birth: 1976 刘晨 Year of Birth: 1977 王名 Year of Birth: 1978 张立 Year of Birth: 1978
Computation in SQL (4) Some remarks: The set of mathematical operators supported includes +, -, *, /. Other operators include length(x), lower(x), upper(x), x y, Computations and functions can be used in select and where clauses.
Computation in SQL (4) Examples: select SSN, upper(first_name ' ' from Employees Last_Name) select substr(ssn, 6, 4), Grade from Enrollment where Course_no = 'CS532'
Computation in SQL (5) Common Oracle Functions: mod(m,n) : remainder of m divided by n power(x,y) : x raised to the power y round(n,m) : round n to the m-th digit following the point
Computation in SQL (6) Common Oracle Functions: sign(x) : 0 if x = 0; 1 if x > 0; -1 if x < 0 sqrt(x) : the square root of x initcap(s) : change the first char of each word in s to uppercase lower(s) : change all chars in s to lowercase replace(s,s1,s2) : change each occurrence of s1 in s to s2 substr(s,m,n) : select n chars from s starting at the m-th char
Computation in SQL (7) Common Oracle Functions: length(s) : the length of s sysdate : the current date user: user id of the current user last_day : the last day of current month to_char(x) : convert x to char data type to_number(x) : convert numeric string x to number type to_date(x) : convert x of proper format to date type
Computation in SQL (8) to_char(x) is used for displaying date and time in different formats. Format: to_char(attribute, format_mask ) Examples: select name, to_char(birthdate, MM/DD/YYYY ) birth_date from students; Select name, to_char(birthdate, MM/DD/YYYY day HH:MM:SS ) birth_time from students;
Computation in SQL (9) Oracle s decode function. select SSN, Name, decode(year, 1, freshman, 2, sophomore, 3, junior, 4, senior ) Status from Students
Computation in SQL (10) Any computation involves a null value yields a null value. Eaxmple: Consider table Employees Emp# Name Salary Bonus 123 Smith 25000 5000 234 John 28000 null
Computation in SQL (11) select Name, Salary+Bonus Total_wage from Employees result: Name Total_wage Smith 30000 John null
Computation in SQL (12) Use NVL function to convert null values format: nvl(exp1, exp2) exp1 is a source value that may be null exp2 is the target value for converting null Ex: select Name, Salary + nvl(bonus, 0) Total_wage from Employees will compute all total wages correctly.
Computation in SQL (11) select Name, Salary+ nvl(bonus, 0) Total_wage from Employees result: Name Total_wage Smith 30000 John 2800
连接查询 (1) Find the names and GPAs of all students who take database systems. select Name, GPA from Students, Enrollment, Courses where Title = `database systems' and Students.SSN = Enrollment.SSN and Enrollment.Course_no = Courses.Course_no
连接查询 (2) The semantics of the previous query can be expressed (almost correctly!) as: π Name, GPA (σ Title = `database systems and Students.SSN = Enrollment.SSN and Enrollment.Courses_no = Courses.Course_no (Students Enrollment Courses)) = π Name, GPA (Students (Enrollment σ Title = `database systems' (Courses)))
连接查询 (3) In general, select distinct Ri.A, Rj.B,..., Rk.C from R1, R2,..., Rn where Conditions is equivalent to π Ri.A, Rj.B,..., Rk.C (σ Conditions (R1 R2... Rn))
连接查询 (4) Example: Find the names and GPAs of all students who take database systems. select Name, GPA from Students s, Enrollment e, Courses c where Title = `database systems' and s.ssn = e.ssn and e.course_no = c.course_no RANGE OF c is Courses RANGE OF s is Students RANGE OF e is Enrollment {(s.name,s.gpa) s (s.ssn=e.ssn e.course_no=c.course_no)}
连接查询 (5) s, e and c are (relational) tuple variables (aliases, correlation names) for relations Students, Enrollment and Courses. Tuple variables can be used to simplify query specification and save time. Tuple variables are also useful when tuples from the same relation need to be compared.
连接查询 (6) Example: Find all pairs of students who have the same GPA. select s1.ssn, s2.ssn from Students s1, Students s2 where s1.gpa = s2.gpa and s1.ssn < s2.ssn Question: Why use s1.ssn < s2.ssn?
连接查询 (7) Example: Find the names of all students whose GPA is higher than Tom's GPA. select s1.name from Students s1, Students s2 where s2.name = `Tom' and s1.gpa > s2.gpa Question: Is a student qualified if his/her GPA is higher than some Tom's GPAs but not all Tom's GPAs?
连接查询 (8) s1 SSN Name GPA 123456789 John 3.6 234567891 Tom 3.2 345678912 Tom 3.8 s2 SSN Name GPA 123456789 John 3.6 234567891 Tom 3.2 345678912 Tom 3.8 Joins imply the existence semantics.
Joining Tables in From Clause (1) Example: Find the titles of all courses offered by departments located in building INFO. Departments (Name, Location, Chairman) Departments1 (Dept_Name, Location, Chairman) select Courses.Title from Courses join Departments on Dept_Name = Name where Location = INFO'
Joining Tables in From Clause (2) or select Courses.Title from Courses join Departments1 using (Dept_Name) where Location = 'EB' or select Courses.Title from Courses natural join Departments1 where Location = 'EB'
Outerjoin (1) R1 R2 R1 R2 A B C C D E A B C D E a1 b1 c1 c1 d1 e1 a1 b1 c1 d1 e1 a4 b3 c2 c6 d3 e2 The second tuples of R1 and R2 are not present in the result (called dangling tuples). Applications exist that require to retain dangling tuples.
Outerjoin (2) 10. o --- outer join Format: R1 o R2 Semantics: like join except (a) it retains dangling tuples from both R1 and R2; (b) it uses null to fill out missing entries. R1 o R2 A B C D E a1 b1 c1 d1 e1 a4 b3 c2 null null null null c6 d3 e2
Left Outerjoin and Right Outerjoin 11. --- left outer join Format: R1 R2 Semantics: like outerjoin but retains only dangling tuples of the relation on the left. 12. --- right outer join Format: R1 R2 Semantics: like outerjoin but retains only dangling tuples of the relation on the right.
Outerjoining Tables in From Clause (1) Employees (SSN, Name, Position, Proj_no) Projects(Proj_no, Title, Budget) Example: Find who works for which project. Not quite correct: select SSN, Name, Title from Employees e, Projects p where e.proj_no = p.proj_no
Outerjoining Tables in From Clause (2) Identify also those who do not work for any project. select SSN, Name, Title from Employees left join Projects on Employees.Proj_no = Projects.Proj_no
Outerjoining Tables in From Clause (3) Identify also those projects no one works for. select SSN, Name, Title from Employees right join Projects using (Proj_no) Identify even those who do not work for any project and those projects no one works for: select SSN, Name, Title from Employees natural full join Projects
集合查询 (1) SQL supports three set operations: union, intersect, except (Sybase) or minus (Oracle version of set difference) Union compatibility is required. Consider the following two relations: G_Students(SSN, Name, GPA, GRE, Dept_Name) Instructors(SSN, Name, Office, Salary, Dept_Name)
集合查询 (2) Example: Find the names of those people who are either a graduate student or an instructor or both in CS department. (select Name from G_Students where Dept_Name = 'CS') union (select Name from Instructors where Dept_Name = 'CS') union removes duplicate rows. union all keeps duplicate rows.
集合查询 (3) Example: Find the SSNs and names of those who are both a graduate student and an instructor in CS department. (select SSN, Name from G_Students where Dept_Name = 'CS') intersect (select SSN, Name from Instructors where Dept_Name = 'CS')
集合查询 (4) Example: Find the SSNs and names of those who are an instructor but not a graduate student in CS department. (select SSN, Name from Instructors where Dept_Name = 'CS') minus (select SSN, Name from G_Students where Dept_Name = 'CS')
集合查询 (4) Example: Find all students who are 20, 22, or 24 years old. select * from Students where Age in (20, 22, 24) not in is the opposite of in.
嵌套查询 (1) Example: Find the names of all students who take at least one course offered by the CS department. select Name from Students s, Enrollment e where s.ssn = e.ssn and e.course_no in (select Course_no from Courses where Dept_Name = 'CS')
嵌套查询 (2) The previous query is a nested query. The query outside is an outer query and the query nested under the outer query is an inner query. Multiple nestings are allowed. The semantics of the above query is to evaluate the inner query first and evaluate the outer query last. Many nested queries have equivalent nonnested versions.
嵌套查询 (3) The previous query is equivalent to: (1) select Name from Students s, Enrollment e, Courses c where s.ssn = e.ssn and e.course_no = c.course_no and c.dept_name = 'CS (2) select Name from Students where SSN in (select SSN from Enrollment where Course_no in (select Course_no from Courses where Dept_Name = 'CS'))
第三章习题 1-4 补充习题 dbhw2 中的查询练习
实验三 SQL 操作 布置时间 :3 月 15 日 DDL 与数据字典 (3 月 17 日 ) 基本查询 (3 月 24 日 ) 高级查询 (3 月 31 日 ) 实验讲评 :4 月 7 日