SQL
Sql查询语言概览
Sql最早的版本是由IBM开发,它最初的被叫做Sequel,在20世纪70年代早期作为System R项目的一部分。Sequel语言一直发展至今,其名称已经变为SQL(结构化查询语言)。Sql已经很明显的确立了自己作为标准的关系数据库语言的地位。
Sql语言包括一下几个部分:
l 数据定义语言(Data-Definition Language,DDL):SQL DDL提供定义关系模式、删除关系以及修改关系模式的命令。
l 数据操纵语言(Data-Manipulation Language,DML)SQL DML提供从数据库查询信息,以及在数据库中插入元组、删除元组、修改元组的能力。
l 完整性(integrity):SQL DDL包括定义完整性约束的命令,保存在数据库中的数据必须满足所定义的完整性月火速。破坏完整性约束的更新是不允许的。
l 视图定义(view definition) :SQL DDL包括定义视图的命令。
l 事务控制(transaction control):SQL 包括定义事务的开始和结束的命令
l 嵌入式SQL和动态SQL(embedded SQL and dynamic SQL):嵌入式和动态SQL定义SQL语言如何嵌入到通用编程语言中。
l 授权(authorization):SQL DDL 包括定义对关系和视图的访问权限的命令。
Sql数据定义
DDL不仅能够定义关系,还可以定义每个 每个关系的信息:
l 每个关系的模式
l 每个属性的取值类型
l 完整性约束
l 每个关系维护的索引集合
l 每个关系的安全性和权限信息
l 每个关系在磁盘上的物理存储结构
基本类型:
Char(n):固定长度的字符串,用户指定长度n,全称character
Varchar(n):可变长度字符串,用户指定最大长度n,等价于全称character varying。
Int:整型,全称integer
Smallint:小整数类型。
Numeric(p,d):定点数,精度由用户指定,这个数有p位数字,其中有d位在小数点右边
Real,double precision:浮点数与双精度浮点数,精度与机器相关。
Float(n):精度至少为n位的浮点数。
基本模式定义:
用create table 定义sql关系:
Create table department
(
dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key(dept_name)
);
Primary key(A),声明属性A构成关系的主码。主码属性必须非空且唯一,也就是说没有一个元组在主码属性上取空值,关系中也没有两个元组在所有主码属性上取值相同。虽然主码的声明是可选的,但为每个关系指定一个主码通常会更好。
Foreign key(A1,A2,A3…..) references:
Foreign key 声明表示关系中任意元组在属性(A1,A2…)上的取值必须对应与关系s中某元组在主码属性上的值。
Not null:一个属性上的not null 约束表明在该属性上不允许空值。
一个新创建的关系最初是空的,可以用insert 命令将数据加载到关系中。
Insert into instructor values(10211,’Smith’,’Biology’,6600);
值的顺序应该遵循对应属性在关系模式中列出的顺序。
用delete命令删除元组:
Delete from student;
将删除student的所有元组。
如果要从sql数据库中去掉一个关系,我们使用drop table 命令
Drop talbe r;
不仅删除r的所有元组,还删除了关系模式;
我们用alter table 命令为已有关系增加属性。关系中的所有元组在新属性上的取值将被设为null。
Alter table r add A D;
其中r是现有关系的名字,A是待添加属性的名字,D是待添加属性的域。
我们也可以通过命令去除某些属性:
Alter table r drop A;
其中r是现有关系的名字,A是关系中一个属性的名字。很多数据库系统比你更不支持去掉属性,金瓜它们允许去掉整个表。
Sql查询的基本结构
单关系查询
Select name from instructor ;
其结果是由属性名为name的单个属性构成的关系。
“找出所有教师所在的系名”:
Select dept_name from instructor;
在关系模型的形式化数学定义中,关系是一个集合。因此,重复的元组不会出现在关系中。在实践中,去除重复是相当费时的,所以sql允许在关系以及sql表达式结果中出现重复。因此在上述的sql查询中,每个系名在instructor关系的元组没出现一次,都会在查询结果中列出一次。
去除重复的方法。可以在select 后加入关键字distinct。
Select distinct dept_name from instructor;
上述查询的结果中,每个系名最多只出现一次;
Sql允许我们使用关键字all来显式指明不去除重复。
Select all dept_name from instructor;
保留重复元组是默认的,所以可以省去all。
Select 子句还可以带有+,-,*,/运算符的表达式。运算对象通常可以是常数或元组的属性。
例如:
查询:
Select ID,name,dept_name,salary*1.1
From instructor;
Where 子句允许我们只选出那些在from子句的结果关系中满足特定谓词的元组。
“找出所有在computer science系并且工资超过70000美元的教师的姓名”,该查询用sql可以写为:
Select name from instructor where dept_name=’Comp.Sci’ and salary >70000;
Sql允许在where子句中使用逻辑连接词and,or和not。
逻辑连接词的运算对象可以是包含比较运算符<,>/<=,>=等表达式。
多关系查询
通常查询需要从多个关系中获取信息。
“找出所有教师的行in个,以及他们所在系的名称和所在建筑的名称”。
老驴instructor关系的模式,我们可以从dept_name 属性中得到系名,但是系所在建筑的名称是在department关系的building属性中给出的。为了回答查询,instructor关系中的每个元组必须与department关系中的元组匹配,后者在dept_name上的取值相配与instructor元组在dept_name上的取值。
Select name,instructor,dept_name,building from instructor,department where instructor.dept_name=department.dept_name;
Select,from和where 子句的作用如下:
l Select子句用于列出查询结果中所需的属性。
l From 子句是一个查询求值中需要访问的关系列表
l Where 子句是一个作用在from子句中关系的属性上的谓词。
自然连接
Natural join 运算作用于两个关系,并产生一个关系作为结果。不同于连个关系上的笛卡尔积,它将第一个关系的每个元组与第二个关系的所有元组进行连接。
“对于大学中所有讲授课程的教师,找出他们的姓名以及所讲述的所有课程标识”
Select name,course_id
From instructor,teaches
Where instructor.ID=teacher.ID;
该查询可以用sql的自然连接运算更简洁的写作:
Select name,cours_id
From instructor natural join teaches;
以上两个查询产生的相同的结果。
在一个sql查询的from子句中,可以用自然连接多个关系结合在一起,如下所示:
Select A1,A2,A3…..n
From r1 natural join r2 natural join 。。。。。 natural join rn
Where
P
“列出教师的名字以及他们所讲授课程的名称”。
次查询可以用sql写为:
Select name,title
From instructor natural join teaches,course
Where teaches.course_id=course.course_id;
下面的sql查询不会计算出相同的结果:
Select name,title
From instructor natural join teaches natural join course;
为了发扬自然连接的优点,同时避免不必要相等属性带来的危险,sql提供了一种自然连接的构造形式,允许用户来指定需要哪些列相等。
Select name,title
From (instructor natural join teaches) join course using (course_id)
Join …..using 运算中需要给定一个属性名列表,其两个输入中都必须具有指定名称的属性。考虑运算r1 join r2 using(A1,A2),它与r1和r2的自然连接类似。
附加的基本运算
更名运算
Select name,course_id
From instructor,teaches
Where instructor.ID=teaches.ID;
更名:
Old-name as new-name;
用名字instructor_name来代替属性名name:
Select name as instructor_name,course_id
From instructor,teaches
Where instructor.ID=teaches.ID;
“找出满足下面条件的所有教师的姓名,他们的工资至少比Biology系某一个教师的工资要高”
Select distinct T.name
From instructor as T,instructor as S where T.salary >S.salary and S.dept_name=’Biology’;
T和S可以被认为是instructor关系的两个拷贝,但更准确的说是instructor关系的别名,想T和S那样被用来重命名关系的标识符在sql标准中被称作相关名称(correlation name),但通常也被称作表别名(table alias),或者相关变量(correlation variable)或者元组变量(tuple variable)。
字符串运算
Sql使用单引号来标示字符串,字符串的相等运算是大小写敏感的。
Sql在字符串上有多种函数。
在字符串上可以使用like操作符来实现模式匹配.
l 百分号(%):匹配任意一个子串。
l 下划线(_):匹配任意一个字符。
模式是大小写敏感的,也就是大写字符与小写字符不匹配,反之亦然。
详细例子:
u ‘Intro%’匹配任何以“Intro”打头的字符串。
u ‘%Comp%’匹配任何包含“Comp”子串的字符串,例如‘Intro to Computer Science’ 和‘Computational Biology’。
u ‘_ _ _’匹配只含有三个字符的字符串。
u ‘_ _ _%’匹配至少含有三个字符的字符串。
Sql中用比较运算符like来表达模式。
“找出所在建筑名称中包含子串‘Watson’的所有系名”
Select dept_name from department where building like ‘%Watson%’
Like比较运算中用escape关键词来定义转义字符。
为了说明这一用法,考虑一下模式,它使用反斜线(\)作为转义字符
like ‘ab\%cd%’ escape ‘\’匹配所有以“ab%cd”开头的字符串。
like ‘ab\\cd%’ escape ‘\’ 匹配所有以“ab\cd”开头的字符串。
星号 “*”可以用在select子句中表示“所有的属性”,因而如下查询的select子句中使用instructor.*:
Select instructor .*
From instructor,teaches
Where instructor.ID=teaches.ID ;
表示instructor中的所有属性都被选中。
排列元组的显示次序
Sql为用户提供了一些对关系中元组显示次序的控制。Order by子句就可以让查询结果按排列顺序显示。为了按字母顺序列出在Physics系的所有教师。
Select name
From instructor
Where dept_name =’Physics’
Order by name;
Order by 子句默认使用升序。要说明排序顺序,我们可以使用desc表示降序,或者使用asc表示升序。
排序可以在多个属性上进行。假设我们希望按salary的降序列出整个instructor关系。如果有几位教师的工资相同,就将它们按姓名升序排列。
Select *
From
Instructor
Order by salary desc, name asc;
首先是工资,然后是姓名。
Where 子句谓词
Sql提供between比较运算符来说明一个值是小于或等于某个值。
如果我们想找出工资在90000和10000美元之间的教师的姓名,我们可以使用between比较运算符。
Select name
From instructor
Where salary between 90000 and 100000;
它可以取代
Select name
From instructor
Where salary <=1000000 and salary >=90000;
Likewise ,还可以使用not between比较运算符。
Select name,course_id
From instructor,teaches
Where instructor.ID=teaches.ID and dept_name=’Biology’;
Sql允许我们用记号(v1,v2…vn)来表示一个分量值分别为v1,v2….vn的n维元组。在元组上可以运用比较运算符。按字典顺序可以进行比较运算。(a1,a2)<=(b1,b2)在a1<=b1且a2<=b2时为真。类似的,当两个元组在所有属性上相等时,它们是相等的。
Select name,course_id
From instructor,teaches
Where (instructor.ID,dept_name)=(teaches.ID,’Biology’);
集合运算
Sql作用在关系上union,intersect和except运算对应于数学集合论中并集,交集和补集运算。
在2009年秋季学期开设的所有课程的集合
Select course_id
From section
Where semester=’Fall’ and year=2009;
在2010年春季开设的所有课程的集合:
Select course_id
From section
Where semester=’Spring’ and year=2010;
并运算
为了找出在2009年秋季开课,或者在2010年春季开课或两个学期都开课的所有课程,我们可以写查询语句:
(select course_id
From section
Where semester=’Fall’ and year=2009)
Union
(select course_id
From section
Where semester=’Spring’ and year=2010);
与select子句不同,union运算自动会去除重复。如果我们想保留所有重复,就必须用union all 代替union:
(select course_id
From section
Where semester=’Fall’ and year=2009)
Union all
(select course_id
From section
Where semester=’Spring’ and year=2010);
交运算
为了找出2009年秋季和2010年春季同时开课的所有课程的集合,我们写出
(select course_id
From section
Where semester=’Fall’ and year=2009)
Intersect
(select course_id
From section
Where semester=’Spring’ and year=2010);
Intersect自动去除重复。
如果想保留重复,就必须使用intersect all代替intersect
差运算
为了找出在2009年秋季开课但是在2010年春季不开的所有课程,我们写:
(select course_id
From section
Where semester=’Fall’ and year=2009)
Except
(select course_id
From section
Where semester=’Spring’ and year=2010);
前一个关机减去后一个关系的差。
差为正的。自动去除所有重复的。
如果要保留重复则是用except all 代替except:
(select course_id
From section
Where semester=’Fall’ and year=2009)
Except all
(select course_id
From section
Where semester=’Spring’ and year=2010);
空值
u And: true and unknown 结果是unknown, false and unknown 是false,unknown and unknown 的结果是unknown。
u Or true or unknown 结果是true ,false or unknown 的结果是unknown,unknown or unknown的结果似乎unknown。
u Not: not unknown的结果是unknown。
Select name
From instructor
Where salary is null;//salary为空值的教师
聚集函数
聚集函数是以值的一个集合(集或者多重集)为输入、返回单个值的函数。Sql提供了五个固有聚集函数:
u 平均值:avg
u 最小值:min
u 最大值:max
u 总和:sum
u 计数:count
Sum和avg的输入必须是数字集,但其他运算符还可作用在非数字数据类型的集合上,如字符集。
基本聚集
“找出Computer Science系教师的平均工资”。
Select avg(salary)
From instructor
Where dept_name=’Comp.Sci’;
该查询的结果是一个具有单属性的关系,其中只包含一个元组,这个元组的数值赌赢Computer Science系教师的平均工资。数据库系统可以给结果关系的属性一个任意的名字,该属性是由聚集产生的,我们可以给属性赋一个有意义的名称,如下所示:
Select avg(salary) as avg_salary
From instructor
Where dept_name=’Comp.Sci’;
有些情况下在计算聚集函数前需要先删除重复元组。如果我们确实需要删除重复元组,可以在聚集表达式中使用关键词distinct。比方有这样一个查询示例“找出在2010年春季学期讲授一门课程的教师数”,在该例中不论一个教师讲授了几个课程段,他只应该被计算一次。
所需信息包含在teaches关系中,我们书写查询如下:
Select count(distinct ID)
From teaches
Where semester=’Spring’ and year=2010;
我们经常使用聚集函数count计算一个关系中元组的个数。Sql中该函数的写法是count(*)
因此,要找出course关系中的元组数,可写成:
Select count(*)
From course;
Sql不允许用count(*)时使用distinct,在用max和min时使用distinct是合法的,尽管结果无差别。我们可以使用关键字all替代distinct来说明保留重复元组。但是,既然all是默认的,就没有必要这么做了。
分组聚集
有时候我们不仅希望将聚集函数作用在单个元组集上,而且希望将其作用在一组元组集上;在sql可用group by子句实现这个愿望。Group by子句中给出的一个或多个属性是用来构造分组的。
在group by子句中的所有属性上取值相同的元组将被分在一个组中。
作为示例,“找出每个系的平均工资”,该查询的书写如下:
Select dept_name,avg(salary) as avg_salary
From instructor
Group by dept_name;
“找出所有教师的平均工资”,我们查询写在如下形式:
Select avg(salary)
From instructor;
在这里省略了group by子句,因此整个关系被当做是一个分组。
“找出每个系在2010年春季讲授一门课程的教师人数”,有关每位教师在每个学期讲授每个课程段的信息在teaches关系中。但是,这些信息需要与来自instructor关系的信息进行连接,才能得到每位教师所在的系名。这样我们把查询写做如下形式:
Select dept_name ,count(distinct ID) as instr_count
From instructor natural join teaches
Where semester=’Spring’ and year=2010
Group by dept_name;
Having 子句
有时候,对分组限定条件比对元组限定条件更有用。例如,我们只对教师平均工资超过42000美元的系感兴趣。该条件并不针对单个分组,而是针对group by 子句构成的分组。为表达这样的查询,我们使用sql的having 子句。Having 子句中的谓词在形成分组后才起作用,因此可以使用聚集函数。
Select dept_name,avg(salary) as avg_salary
From instructor
Group by dept_name
Having avg(salary) >42000;
与select子句的情况类似,任何出现在having 子句中,但没有被聚集的属性必须出现在group by 子句中,否则查询就被当成是错误的。
包含聚集、group by或having 子句的查询的定义可以通过下述操作序列来定义:
1. 与不带聚集的查询情况类似,最先根据from子句来计算出一个关系。
2. 如果出现了where 子句,where 子句中的谓词将应用到from子句的结果关系上。
3. 如果出现了group by子句,满足where 谓词的元组通过group by子句形成分组。数学如果没有group by子句,满足where 谓词的整个元组集被当作一个分组。
4. 如果出现了having子句,它将应用到每个分组上,不满足having子句谓词的分组将被抛弃。
5. Select子句利用剩下的分组产生出查询结果中的元组,即在每个元组上应用聚集函数来得到单个结果元组。
说明同时使用having 和where子句的情况,“对于在2009年讲授的每个课程段,如果该课程段有至少2名学生选课,找出选修该课程段的所有学生的总学分(tot_cred)的平均值”
Select course_id,semester,year,sec_id,avg(tot_cred)
From takes natural join student
Where year=2009
Group by course_id,semester,year,sec_id
Having count(ID)>=2;
对空值和布尔值的聚集
空值的存在给聚集运算的处理带来麻烦。
嵌套子查询
Sql提供嵌套子查询机制。子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中。通常对于集合的成员资格、集合的比较以及集合的基数进行检查。
集合成员资格
Sql允许测试元组在关系中的成员资格。连接词in测试元组是否是集合中的成员,集合是有select子句产生的一组值构成的。连接词not in 则测试元组是否不是集合中的成员。
“找出2009年秋季和2010年春季学期同时开课的所有课程”
先前,我们通过对两个集合进行交运算来书写该查询,我们下面用另一种写法:
//(select course_id from section where semester=’Spring’ and year=2010
Select distinct couse_id from section where semester=’Fall’ and year 2009 and course_id in
(select couse_id from section where semester =’Spring’ and year=2010);
也可以使用类似的方式使用not in。
In和not in操作符也能用于枚举集合。
Select distinct name from instructor
Where name not in (‘Mozart’,’Einstein’);
例题:
Select count(distinct ID)
From takes
Where (course_id,sec_id,semester,year) in
(select course_id,sec_id,semester,year from teaches where teaches.ID=10101);
集合的比较
Select distinct T.name from instructor as T,instructor as S where T.salary>S.salary and S.dept_name=’Biology’;
空关系测试
Sql还有一个特性就是可以测试一个子查询的结果中是否存在元组。Exists结构在作为参数的子查询非空时返回true值。使用exists结构,我们还能用另外一种方法书写查询“找出在2009年秋季学期和2010年春季学期同时开课的所有课程”
Select course_id
From section as S
Where semester =’Fall’ and year=2009 and exists(select * from section as T where semester=’Spring’ and year =2010 and S.course_id=T.course_id);
同样可以使用not exists 属性。
Select S.ID,S.name
From student as S
Where not exists
((select course_id from course where dept_name=’Biology’)
Except (select T.course_id from takes as T where S.ID =T.ID));
子查询
(select course_id from course where dept_name =’Biology’)
重复元组存在性测试
Sql 提供一个布尔函数,用于测试一个子查询的结果中是否存在重复元组。如果作为子查询结果中没有重复的元组,unique结构将返回true值。我们可以使用unique结构书写查询“找出所有在2009年最多开设一次的课程”
Select T.course_id
From course as T
Where unique(select R.course_id from sectioni as R where T.course_id=R.course_id and R.year=2009);
From 子句的子查询。