03 SQL
SQL查询语言概述
Structured Query Language
历史
最早Sequel语言在IBM的圣约瑟研究实验室被作为第一个关系型数据库System R项目的一部分而开发的,后被更名为Structured Query Language (SQL)
ANSI和ISO的SQL标准:
✓ANSI: SQL-86, SQL-89, SQL-92
✓ISO: SQL:1999, SQL:2003, SQL:2006, SQL:2008,
SQL:2011, SQL:2016, SQL:2019
商业系统提供了绝大部分的SQL标准功能(即使不是全部的SQL-92),再加上从后来的标准和特殊的专有功能(可能是非SQL标准)提供的不同功能集合
不只是“查询”(query)语言
DDL 数据定义语言
DML 数据操纵语言
完整性
视图定义
授权
事务控制
嵌入式SQL和动态SQL
SQL数据定义
数据定义语言 (DDL)
SQL的 数据定义语言 (DDL) 能够定义每个关系的信息
✓关系的模式
✓属性的取值类型、取值范围(属性域)
✓完整性约束(主外码)
✓关系的安全性和权限信息
✓还包括其它信息如:
• 每个关系维护的索引集合 *
• 每个关系在磁盘上的物理存储结构 *
SQL的基本类型
➢ char(n): 固定长度的字符串,用户指定长度n
➢ varchar(n): 可变长度字符串,用户指定最大长度n
➢ int: 整数类型 (4字节)
➢ smallint: 小整数类型 (2字节)
➢ numeric(p,d): 定点数,精度由用户指定。这个数有p位数字,
其中,d位数字在小数点右边
➢ real, double precision: 浮点数与双精度浮点数,精度与机
器相关
➢ float(n): 精度至少为n位的浮点数
基本关系模式定义
create table定义
create table 命令定义SQL关系
create table r ( A 1 D 1 ,
...,
A n D n ,
<完整性约束 1 >,
...,
<完整性约束 k >);(分号结束)
r 是关系名,A i 是关系r模式中的一个属性名,D i 是属性 A i 的域(即取值类型、范围)
eg
create table instructor ( ID char(5) ,
name varchar(20) ,
dept_name varchar(20) ,
salary numeric(8,2),
primary key (ID));
Create Table中的完整性约束
➢ not null
➢ primary key (A 1 , …, A n )
➢ foreign key (A m , …, A n ) references r(A p , …, A q )
eg
create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8, 2),
primary key (ID),
foreign key (dept_name) references department );
简化版
create table course (
course_id varchar(8) primary key,
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
foreign key (dept_name) references department);
primary key声明的属性会自动为not null
SQL禁止破坏完整性约束的数据库更新
✓新插入的元组主码属性为空值、或取值与关系中的另一元组主码属性相同
✓破坏外码约束
删除和修改表
drop table
drop table student
删除表和其中内容
delete from
delete from student
删除表里的内容,但是保留表(关系模式)
alter table
增加、删除属性
alter table r add A D
• 其中 A 是要被添加到关系 r 的属性的名称,并且 D 是 A 的域。
• 关系中所有元组使用 null 作为新的属性值
alter table r drop A
• 其中,A 是关系 r 的属性的名称
• 许多数据库都不支持删除属性,但支持drop整个表
SQL查询的基本结构
select语句的基本结构
典型的SQL查询语句的形式
select A 1 , A 2 , ..., A n
from r 1 , r 2 , ..., r m
where P 1 and (or, not) P n ;
✓ A i 代表一个属性
✓ r i 代表关系实例
✓ P i 是一个谓词**(限定条件)**
一条SQL语句的结果是一个关系
SQL查询语句末尾有分号
select子句
选择子句列出的是查询语句需要的属性
对应关系代数中的投影操作(属性的选择)
eg
select name
from instructor ;
SQL语句是不区分大小写的 (即,既可以使用大写也可以使用小写)
SQL在查询结果和关系中默认允许重复
为了强制消除重复,可以在select后加上关键字distinct
eg
select distinct dept_name
from instructor;
distinct可以后接多个属性,表示选出在多个属性上都不重复的元组
关键字all 显式指定不消除重复
eg
select all dept_name
from instructor;
**“*”**在select子句中表示“所有属性”
eg
select *
from instructor;
select子句可以包含算术表达式,算术表达式中可以有+,-,*, / 运算符和对常量和属性的操作
eg
select ID, name, salary/12
from instructor;
select子句中还可以包含其他一些特殊的数据类型,如日期,以及算术函数
where子句
where子句表示结果必须满足的限定条件
对应关系代数的选择操作(元组的选择)
eg
select name
from instructor
where dept_name = ‘Comp. Sci.’ and salary > 80000;
#找出Comp. Sci.系中工资大于80000的教师的姓名
where子句中可以包含逻辑运算符and, or, 和not
逻辑运算符的运算对象可以是包含比较运算符> , >= , <, <= ,**=**和< > 的表达式
允许使用比较运算符来比较字符串、算术表达式以及日期类型等
from分句
from分句列出了查询中用到的关系
对应关系代数中笛卡尔积操作
eg
select *
from instructor, teaches;
#笛卡尔积instructor × teaches
✓生成每一个可能instructor–teaches对, 所有属性来自两个表
✓理解为一个**迭代(多重循环)**的过程
经常结合where子句使用 (关系代数中选择操作)
如果多关系中存在相同属性,则在select、where子句中须作区分 ,如: instructor.ID, teaches.ID
连接
eg
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID; #限制条件
#对所有上课的教师,查询他们的姓名及课程ID
select section.course_id, semester, year, title
from section, course
where section.course_id = course.course_id and dept_name = ‘Comp. Sci.’;
# 查询Comp. Sci开的每一门课course ID, semester, year, title
SQL查询语句
通常说来,一个SQL查询的含义可以 理解 如下:
1.为from子句中列出的关系产生笛卡尔积
2.在步骤1的结果上应用where子句中指定的谓词
3.对于步骤2结果中的每个元组,输出select子句中指定的属性(或表达式的结果)
上述过程不是SQL查询语句的执行顺序,实际SQL查询语言的执行是经过查询优化
➢ 如果省略where子句,则谓词P为true
➢ 与关系代数表达式不同,在SQL的查询结果中可以包含很多重复的元组
自然连接
自然连接会匹配两个关系中所有共同属性的相同值的元组, 去掉重复属性列
eg
select *
from instructor natural join teaches;
自然连接结果=共同属性+第一个关系属性+第二个关系属性
eg
列出教师姓名、教师所教课程的编号
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;
select name, course_id
from instructor natural join teaches;
以上两个SQL查询语句完全等价
谨防无关的属性具有相同的名字
错误:
select name, title
from instructor natural join teaches natural join course;
正确
select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;
select name, title
from (instructor natural join teaches)
join course using(course_id);
附加的基本运算
更名运算
SQL允许对关系和属性进行更名操作
使用as子句
old-name as new-name
eg
select ID, name, salary/12 as monthly_salary
from instructor
#找出满足下面条件的所有教师的姓名,他们至少比某一个Comp.Sci.系教师的工资要高
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ‘ Comp.Sci. ’
关键字as是可选的,可以省去
instructor as T ≡ instructor T
在Oracle数据库中,必须省去as关键字
在SQL标准中,重名关系的标识符(如T、S)称之为
✓相关名称(correlation name)
✓表别名(table alias)
✓相关变量(correlation variable)
✓元组变量(tuple v