数据库系统概念-----SQL
本节目录
1、SQL查询语言概览
SQL语言有以下几个部分：
*数据定义语言（DDL）：SQL DDL提供定义关系模式，删除关系以及修改关系模式的命令。
*数据操纵语言（DML）：SQL DML提供从数据库中查询信息以及在数据库中插入元组、删除元组、修改元组的能力。
*完整性：SQL DDL包括定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束，破坏完整性约束的更新是不被允许的。
*视图定义：SQL DDL包括定义视图的命令。
*事务控制：SQL包括定义事务的开始和结束的命令。
*嵌入式SQL和动态SQL：嵌入式和动态SQL定义SQL语句如何嵌入到通用编程语言。
*授权：SQL DDL包括定义对关系和视图的访问权限命令。
2、SQL 数据定义
数据库中的关系集合必须由数据定义语言（DDL）指定给系统，SQL的DDL不仅能够定义一组关系，还能定义每个关系的信息，包括：1）每个关系的模式。2）每个属性的取值类型。3）完整性约束。4）每个关系维护的索引集合。5）每个关系的安全性和权限信息。6）每个关系在磁盘上的物理存储。
2.1 基本类型
SQL标准支持多种固有类型。包括：
*char：固定长度的字符串，用户指定长度n，也可以在使用character
*varchar：可变长度的字符串，用户指定最大长度n，等价于全称character varing。
*int：整数类型，等价于全称integer
*smallint：小整数类型，
*numeric(p,d)：定点数，精度由用户指定，这个数有p位数字（加一个符号位），其中d位数字在小数点右边。所以numeric(3,1)这个可以存储44.5，但不能存储444.5或者0.32这样的数。
*real，double precision：浮点数与双精度浮点数。
*float(n)：精度至少为n位的浮点数
每种类型都有可能包含一个空值的特殊值，空值表示一个缺失的值，该值可能存在但是不为人所知，或者可能根本不存在，在可能的情况下，我们希望禁止加入空值。
2.2 基本模式定义
使用create table命令定义SQL关系，下面的命令在数据库中创建了一个department关系。
上面创建的关系具有三个属性，dept_name是最大长度为20的字符串，building是最大长度为15的字符串，budget是一个12位的数，其中两位是小数点后面，create table还指明了dept_name属性是department关系的主码。
create table命令的通用形式是：
其中r是关系名，每个Ai 是关系r模式中的一个属性名，Di是属性Ai的域，也就是说，D制定了属性A的类型以及可选的约束，用于限制所允许的A取值的集合。
create table命令后面用分号结束。其他的SQL语句也是如此，下面是几个完整性约束：
*primary key(A1,A2,.....Am)：声明属性A1，A2..构成关系的主码。主码属性必须非空且唯一，也就是说没有一个元组在主码属性上取空值，关系中也没有两个元组在所有主码属性上取值相同，虽然主码的声明是可选的，但是为每一个关系指定一个主码通常会更好。
*foreign key(A1,A2,....Am) references S：声明表示关系中任意元组在属性（A1,A2..Am）上的取值必须对应关系S中某元组在主码属性上的取值。比如下图：course表中声明了foreign key(dept_name) references department。此外码声明表明对于每个课程元组来说，该院组所标识的系名必然存在于department关系的主码属性（dept_name）。没有这个约束的话就可能某门课程指定一个不存在的系名。
*not null：一个属性上的not null约束表明在该属性上不允许有空值，换句话说，此约束把空值排除在该属性域之外。如下图表示，dept_name的值不能为空。
SQL禁止破坏完整性约束的任何数据库更新。
一个新创建的关系最初是空的，可以使用inset命令将数据加载到关系中，例如下图，值被给出的顺序应该遵循对应关系属性在关系模型中列出的顺序。
可以使用delete命令从关系中删除元组，命令：delete from student;将从student关系中删除所有元组。
如果要去掉一个关系使用，drop table命令。drop table命令从数据库中删除关于被去掉关系的所有信息，命令drop table r是比delete from r更强的语句，后者保留关系r，但是删除r中的元组。前者不仅删除元组，还删除r的模式。一旦r被去掉，除非使用create table命令重建r，否则不能有元组插入到r中。
可以使用alter table 命令为已有的关系增加属性。其命令格式为
其中r是现有关系名称，A是待添加的属性名称，D是待添加的属性的域，同样可以通过
从关系中去掉属性，r为现有关系，A是关系的一个属性名。
3、SQL查询的基本结构
SQL查询的基本结构由三个字句构成：select、from和where。查询的输入是在from字句中列出的关系，在这些关系上进行where和select字句中指定的运算，产生一个关系作为结果。
3.1 单关系查询
考虑使用大学数据库例子的一个简单查询“找出所有老师的姓名”，教师的姓名可以在instructor关系中找到，因此，把这个关系放到from字句中，教师姓名出现在name属性中，把它放在select字句中。
现在考虑另一个查询，找出所有教师所在的系名。可以写成
在关系模型的形式化数学定义中，关系是一个集合，因此，重复的元组不会出现在关系中，在实践中去除重复是相当费时的，所以SQL允许在关系以及SQL表达式中出现重复，因此上面的查询语句结果中，每个出现的系名都会在结果中，如果我们想要强行删除重复们可以在select后加入关键字，distinct。上面的查询就变成了下图，这样每个系名最多出现一次。
SQL允许我们使用关键字all来显式知名不去除重复。
SQL字句还可以带有加减乘除运算符的算术表达式，运算的对象可以使常数或元组的属性。如下：
返回一个和instructor一样的关系，只是属性salary是原来的1.1倍。
where字句允许我们只选出那些在from子句的结果关系中满足特定谓词的元组。如下语句：
表示的是，从instructor表中找出dept_name为Comp.Sci并且salary大于70000的教师的姓名。where子句可以使用逻辑连词and，or和not。逻辑连词的运算对象可以是包含比较运算符<、<=、>、>=、=和<>的表达式。SQL允许使用比较运算符来比较字符串、算术表达式以及特殊类型。
3.2 多关系查询
如下语句：
表示的是找出所有老师的姓名，以及他们所在系的系名，和系所在的建筑名称。需要注意的是，所需要的信息出于两个不同的关系，并且关系的每个元组都要与另一个关系的每个元组相对应。还有一个需要注意的是如果一个属性只出现在一个关系中，那么不需要用关系名作为前缀，如果属性在多个关系中都有，那么需要使用关系名作为前缀。
一个SQL查询可以包括三种类型的子句，select子句、from子句、where子句。每种字句的作用如下：select子句用于列出查询结果中所需要的属性，from子句是一个查询求值中需要访问的关系列表，where子句是一个作用在from子句中关系的属性上的谓词。
通过from子句定义一个在该子句中所列出的关系上的笛卡尔积（笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员）。
where子句中的谓词用来限制笛卡尔积所建立的组合，只留下对答案有意义的组合。
通常来说，一个SQL查询语句的含义可以理解为下：1）为from子句中列出的关系产生笛卡尔积。2）在步骤1的结果上应用where子句中指定的谓词。3）对步骤2结果中的每个元组输出select子句中指定的属性。
where子句的条件需要小心设置。
3.3 自然连接
自然连接运算作用于两个关系，产生一个关系作为结果，不同于两个关系上的笛卡尔积，它将第一个关系的每个院组和第二个关系的所有元组都进行连接；自然连接只会考虑那些在两个关系模式中都出现的属性上取值相同的元组对。也就是两个关系中相同的属性。
对于之前的一个查询，找到对于大学中所有讲授课程的教师，找出他们的姓名以及讲述的所有课程标识。此前我们所写的查询语句是这样的：
但是利用自然连接之后，便可以这样写：
两者产生的结果是一样的。自然连接的结果是关系。在一个SQL查询from子句中，可以用自然连接将多个关系结合在一起。如下：
为了更好地发挥自然连接的优点，避免不必要的相等属性带来的危险，SQL提供了一种自然连接的构造形式，允许用户来指定需要哪些列相等，如下：
join...using运算中需要给定一个属性名列表，其中两个输入中都必须有指定名称的属性。运算r1 join r2 using（A1,A2）它和r1和r2的自然连接类似。只不过在t1.A1=t2.A1并且t1.A2=t2.A2成立的前提下，来自r1的元组t1和来自r2的元组t2就能匹配，即使r1和r2都有具有A3的属性，也不需要t1.A3=t2.A3成立。
4、附加的基本运算
a
看一下之前使用过的查询：
查询的结果是一个具有下列属性的关系：name，course_id结果中的属性名来自from子句中关系的属性名。SQL提供了一个重命名结果关系中属性的方法，如下使用as语句。
as子句既可以出现在select子句中，也可以出现在from子句中。比如如果想用instructor_name来代替属性名name,重写上面的查询，
类似如下图的查询：
T和S可以看做instructor关系的两个拷贝，更准确地说是instructor关系的别名。一般被称作相关名称，或者表别名，或者相关变量、元组变量。
4.2 字符串运算
SQL用一对字符串表示字符串，如果单引号是字符串的一部分，那就用两个单引号字符来表示。字符串操作函数不同的数据库系统是不同的。
在字符串上可以使用like操作符来实现模式匹配，使用两个特殊的字符来描述模式：
*百分号（%）：匹配任意字串
*下划线（_）：匹配任意一个字符，大小写要区分。
*Intro%：匹配任何以Intro开头的字符
*%Comp%匹配任何包含“Comp”子串的字符串
*（___）匹配只含有三个字符的字符串
*（___%）匹配至少含有三个字符的字符串
在SQL中用比较运算符like来表达模式，查询“找出所在建筑名称中包含子串‘Wastson’"的所有系名。写法如下：
为了使模式中能包含特殊模式的字符即（%和_）SQL允许定义转义字符，转义字符直接放在特殊字符的前面，表示该特殊字符为普通字符。在like比较运算中使用escape关键字定义转义字符。
like 'ab\%cd%' escape '\' 匹配所有以"ab%cd"开头的所有字符串。
like 'ab\\cd%' escape '\' 匹配所有以"ab\cd"开头的字符串。
SQL允许使用not like比较运算符搜寻不匹配项。
4.3 select子句中的属性说明
星号“*”可以用在select子句中表示所有的属性。类似正则表达式。
4.4 排列元组的显示次序
SQL为用户提供了一些对关系中元组显示次序的空值。order by子句就可以让查询结果中元组按排列顺序显示。比如按照字母顺序列出Physics系所有的教师。可以这样写：
order by子句默认使用升序，要说明排列顺序，可以用desc表示降序，或者用asc表示升序。排序可以在多个属性上进行。比如希望按照工资的降序，如果工资一样再按照姓名升序排列。语句如下：
4.5 where子句谓词
为了简化where子句，SQL提供了between比较运算符来说明一个值是小于或等于某个值，同时大于或等于另一个值，比如找出工资在90000到100000之间的教师的姓名，语句如下：
这样在where子句中就不用了使用salary>90000 and salary<100000。
SQL允许使用记号（V1，V2....Vn）表示一个分量值分别为V1，V2，....Vn的n维元组。在元组上可以使用比较运算符，按照字典顺序进行比较运算。例如（a1,a2）<=(b1,b2)在a1<=b1且a2<=b2时为真，类似的两个元组在所有属性上相等时，它们是相等的。
5、集合运算
SQL作用在关系上的union、intersect和except运算对应于数学集合论中的并交和-运算。以下面的集合为例：
5.1 并运算
找出在2009年秋季开课，或者2010年春季开课或两个学习都开课的所有课程，查询语句如下：
union运算会自动去除重复，这样会去除掉在两个季节都开的课程只保留一个。如果想保留重复，使用union all代替union。
5.2 交运算
找出2009秋季和2010春季同时开课的所有课程的集合。如下：
intersect运算也会自动去除重复，如果有一门课程在春季开了4个课程段，在春季开了两个课程段，那么结果还是只有一个课程。和union 一样，如果想保留重复元素，使用intersect all 代替intersect。
5.3 差运算
找出在2009年秋季开课但不在2010年春季开课的所有课程，查询语句如下：
except运算从第一个输入中输出所有不在第二个输入中的元组，也就是执行集差操作。此运算在执行差操作之前自动去除输入中的重复，和之前一样，不想去除重复使用except all 代替except。
6、空值
空值给关系运算带来了特殊的问题，包括算术运算、比较运算和集合运算。
如果算术表达式的任一输入为空，则该算术表达式（+、-、*、/）结果为空。
SQL涉及空值的比较运算的结果视为unknown，这是除了true、false之外的第三个逻辑值。由于在where子句的为此种可以对比较结果使用诸如and、or和not的布尔运算，所以这些布尔运算的定义也被扩展到可以处理unknown值。
*and：true and unknow 结果是unknow，false and unknow结果是false。unknow and unknow 的结果是unknow。
*or ：true or unknow 结果是true ，false or unknow结果是unknow ，unknow or unknow结果是unknow。
*not：not unknow结果是unknow。
如果where子句对一个元祖计算出false或unknow，那么该元组不能被加到结果集中。
SQL在谓词中使用特殊的关键词null测试空值，因而为找出instructor关系中salary为空值的所有教师可以写成如下：
如果is null为非空，则为真。某些SQL语句还允许使用子句is unknow和is not unknow来测试一个表达式的结果是否为unknow。在select distinct子句时，重复元组将被去除。为了达到这个目的，当比较两个元组对应的属性值时，如果这两个值都是非空并且值相等或者都为空，那么它们是相同的。
7、聚集函数
聚集函数是以值得一个集合为输入，返回单个值的函数，SQL提供了五个固有聚集函数，avg（平均值）、min（最小值）、max（最大值）、sum（总和）、count（计数）。
7.1 基本聚集
查询某个系教师的平均工资，查询语句如下：
数据库系统可以给结果关系的属性一个任意的名字，这个属性是聚集产生的，可以用as子句给属性一个有意义的名称。如下：
SQL COUNT(DISTINCT column_name) 语法：COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：
使用聚集函数conut计算一个关系元组的个数，SQL中该函数的写法是count(*)，如下：count(*)不能和distinct一起使用。
7.2 分组聚集
group by可以将聚集函数作用在一组元组集上。group by子句中给出的一个或多个属性是用来构造分组的。在group by子句中所有属性取值相同的元组将被分在一个组中。
查询每个系的平均工资，查询语句如下：
上面的语句会先根据dept_name属性进行分组的情况，分组是计算查询结果的第一步，然后在每个分组上进行指定的聚集计算。如下图，是先分组之后的结果。
查询的结果如下图：
如果不使用group by 语句那么整个关系会被当做一个分组。当SQL查询使用分组时，一个很重要的事情是，需要保证出现在select语句中，但是没有被聚集的属性只能是出现在group by子句的属性。也就是说，任何没有出现在group by子句的属性如果出现在select子句中的话，它只能出现聚集函数内部，否则这样的查询是错误的。
7.3 having 子句
有时候，对分组的限定条件比对元组的限定条件更加有用，该条件不针对单个元组，而是针对group by构成的分组，可以使用having 子句，having 子句中的谓词在形成分组后才起作用，可以使用聚集函数。
上述语句表示的是，系平均工资超过42000的那些教师的平均工资。和select子句情况类似，任何出现在having 子句中没有被聚集的属性必须出现在group by子句中，否则查询就是错误的。
7.4 对空值和布尔值的聚集
空值的存在给聚集运算的处理带来了麻烦。总而言之，聚集函数根据以下原则处理空值：除了count(*)外的所有聚集函数都忽略输入集合中的空值。由于空值被忽略，有可能造成参加函数运算的输入集合为空集，规定空集的count运算值为0，其他所有聚集运算在输入为空集的情况下返回一个空值，在一些更复杂的SQL结构中空值的影响更难判定。
布尔数据类型，可以取true、false、unknow三个值，有两个聚集函数，some和every，其含义正如直观意义一样，可以处理布尔类型值。
8、嵌套子查询
SQL 提供嵌套子查询机制，子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中，通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。
8.1 集合成员资格
SQL允许测试元组关系中的成员资格，连接词in测试元组是否是集合中的成员，集合是由select子句产生的一组值构成的。连接词not in则测试元组是否不是集合中的成员。
在之前做过一个查询，09年秋季和10年春季都开设的课程，之前是使用两个集合的交运算，现在可以使用嵌套查询的方式。
也可以使用与in结构类似的方式使用not in结构。
8.2 集合的比较
SQL提供一种比较，短语至少比某一个大在SQL中用>some表示。如下：
SQL也允许<some，<=some，=some和<>some。类似于some，SQL也允许<all，>all，=all等。
8.3 空关系测试
SQL还有一个特性可以测试一个子查询结果中是否存在元组。exists结构在作为参数的子查询非空时返回true值，使用exists结构，我们还能用另外一种方法书写查询，如下：
上面的查询说明了，来自外层查询的一个相关名称，可以用在where子句的子查询中，使用了来自外层查询的相关的子查询被称为相关子查询。
可以用 not exists结构测试子查询结构中集中是否不存在元组。
8.4 重复元组存在性测试
SQL提供一个布尔函数，用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组，unique结构返回true值。
可以使用not unique结构测试在一个子查询结果中是否存在重复元组。
8.5 from子句中的子查询
SQL允许在from子句中使用子查询表达式。之前做过一个查询，找出系平均工资超过42000的系中教师平均工资，之前是使用having子句来写的，现在可以这样：
不是所有SQL实现都支持from子句嵌套。一般from子句嵌套的子查询不能使用来自from其他关系的变量。但是2003以后，允许from 子句的子查询使用关键字lateral作为前缀，访问from子句中前面的表或子查询中的属性。
8.6 with 子句
with子句提供定义临时关系的方法，这个定义只包含with子句的查询有效。如下查询：
with子句定义了临时关系max_budget，此关系在随后的查询中马上被使用了。
8.7 标量子查询
SQL允许子查询出现在返回单个值得表达式能出现在任何地方，只要该子查询只返回包含单个属性的单个元组。这样的子查询成为标量子查询。如下：
列出所有的系以及他们拥有的教师数，上面的子查询保证返回单个值，因为它使用了不带group by的count(*)聚集函数。标量子查询可以出现在select、where和having子句中，也可以不使用聚集函数来定义标量子查询，在编译时，并非总能判断一个子查询返回的结果中是否有多个元组，如果子查询被执行后结果中不只有一个元组，则产生一个运行时错误。
9、数据库修改
9.1 删除
删除请求的表达与查询非常类似，我们只能删除整个元组，而不能删除某些属性上的值。SQL用如下语句表示删除
其中P代表一个谓词，r表示一个关系。delete语句首先从r中找出所有使P(t)为真的元组t，然后把它们从r中删除。如果省略where，则r中所有元组将被删除。
delete只能作用于一个关系，如果想从多个关系中删除元组，必须在每个关系上是用一条delete命令。where子句中的谓词可以和selete命令的where子句中谓词一样复杂，另一种情况下，where子句可以为空，这样将会删除整个关系中的元组，但是关系本身还是存在的。
delete语句可以使用嵌套。
9.2 插入
要往关系中插入数据，我们可以指定带插入的元组，或者写一条查询语句来生成待插入的元组集合，显然待插入的元组的属性值必须在相同属性的域中，待插入的分量数也必须是正确的。
最简单的insert语句是单个元组的插入请求，如下：
元组属性的排列顺序和关系模式中属性排列顺序一致，考虑到用户可能不记得关系属性的排列顺序，SQL允许在insert语句中指定属性，如下：
更通常的情况是，可能想在查询结果的基础上插入元组，如下：
没有指定元组，而是用select选出一个元组，在执行插入。在插入之前先执行完select语句是非常重要的。如果在执行select语句同时执行插入动作，并且在关系上没有主码约束，这样会插入无数元组。
待插入元组可以只给出模式中部分属性的值，其余属性将被赋空值，用null表示。
大部分关系数据库，有特殊的“bulk loader”工具，可以向一个关系中插入一个非常大的元组集合。这些工具允许从格式化文本中读出数据，且执行速度比通道能目的的插入语句序列要快得多。
9.3 更新
有些情况下，我们可能希望在不改变整个元组的情况下改变其部分属性的值。为了达到这一目的，可以使用update数据与使用insert、delect类似，待更新的元组可以用查询语句找到。
上述语句将instructor关系上的每个元组都执行。
上述是将工资小于70000的教师涨工资。update语句中嵌套select可以引用待更新的关系。SQL提供case结构，可以利用它在一条update语句中执行前面两种更新，避免更新次序引发的问题：
标量子查询在SQL更新语句中也很有用，可以用在set子句中。如下语句
上述语句的意思是，将student元组的tot_cred属性设置为，该学生成功学完的课程的学分总和，假设一个学生的成绩既不是F，也不是空，那么他成功学完这门课程。上面的语句如果一个学生没有学完课程，则tot_cred会设置为空，如果想要将其设置为0，那么可以使用将select sum子句替换成使用case表达式。
10、总结
*SQL语言包括：数据定义语言（DDL），它提供定义关系模式，删除关系和修改关系模式的命令。数据操纵语言（DML）它包括查询语言、以及往数据库中插入元组、删除元组和修改元组
*SQL的数据定义语言用于创建具有特定模式的关系，除了声明关系属性的名称和类型之外，SQL还允许提供声明完整性约束，主码约束和外码约束。
*SQL提供多种用于查询数据库的语言结构，其中包括select、from和where子句，SQL还支持自然连接。
*SQL提供对属性和关系重命名。
*SQL支持关系上的基本集合运算，包括交并和差运算。
*SQL通过真值true、false并且增加unknow，来处理对包含空值的关系查询。
*SQL支持聚集，可以把关系进行分组，在每个分组上单独运用聚集。
*SQL支持在外层查询的where和from子句中嵌套子查询。
浙公网安备 33010602011771号