概述
SQL的特点
- 综合统一
- SQL集数据定义语言,数据操纵语言,数据控制语言的功能于一体,语言风格统一,可以独立完成数据库生命周期中的全部活动,可以执行以下一系列要求:
- 定义和修改、删除 关系模式 ,定义和删除 视图 ,插入 数据 ,建立 数据库
- 对数据库中的数据进行查询和更新
- 数据库重构和维护
- 数据库安全性。完整性控制,以及事务控制
- 嵌入式SQL和动态SQL定义
- SQL集数据定义语言,数据操纵语言,数据控制语言的功能于一体,语言风格统一,可以独立完成数据库生命周期中的全部活动,可以执行以下一系列要求:
- 高度非过程化
- 只要提出做什么,而无需指明怎么做
- 面向集合的操作方式
- 以同一种语法结构提供多种使用方式
- 语言简洁,易学易用
SQL的基本概念
- 基本表:基本表是本身独立存在的表,在关系数据库管理系统中一个关系就对于一个基本表
- 存储文件:存储文件的逻辑结构组成了关系数据库的内模式
- 视图:从一个或几个基本表导出的表
SQL的使用
准备的表
- 学生表 Student(Sno,Sname,Ssex,Sage,Sdept)
- 选课表 SC(Sno,Cno,Grade)
- 课程表 Course(Cno,Cname,Cpno,Ccredit) # Cpno:先行课
数据定义
数据库的各个层次:一个关系数据库管理系统的实例中可以建立多个数据库,一个数据库中可以建立多个模式,一个模式下可通常包括多个表、视图和索引等数据库对象
模式
- 定义模式 create
- create schema<模式名> authorization <用户名>
- 删除模式 drop
- drop schema<模式名><cascade|restrict>
基本表
- 定义 create
- 建立一个学生表
create table Student(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
);
- 建立一个学生表
- 修改 alter
alter table<表名>
[add [column] <新列名><数据类型> [完整性约束]]
[add [表级完整性约束]]
[drop [column]<列名>[cascade|restrict]]
[drop constraint<完整性约束> [restirct|cascade]] #constraint:约束
[alter column<列名><数据类型>]
alter table Student add S_entrance date;
3. 删除 drop
- drop table<表名> [restrict|cascade]
索引
- 作用:加快查询速度
- 索引是关系数据库管理系统的内部实现技术,属于内模式的范畴,用户不必也不能显式地选择索引
- 建立索引
- create [unique] [cluster] index<索引名> on <表名>(<列名>[次序],…) # 次序:asc|desc
- 修改索引
- alter index<旧索引名>rename to<新索引名>
- 删除索引
- drop index <索引名>
数据查询
一般格式:
select [all|distinct] <目标列表达式> ...
from <表名或视图名> ....
[where <条件表达式>]
[group by <列名> [having <条件表达式>]]
[order by <列名> [asc|desc]
表单查询(仅涉及一个表的查询)
-
select
- select Sno,Sname from Student
- select Sno,Sname,2019-Sage from Student #使用表达式
- select Sno,‘year_of_birth’,2019-Sage,lower(Sdept) from Student #常量,函数
-
all/distinct
- select distict Sno fron SC # 消除取值重复的行
- 默认为all
-
where
常用的查询条件
- select Sname from Student where Sdept=‘CS’;
- between and/ not between and # 数值前小后大
- in / not in # in(‘CS’,‘MA’,‘IS’)
- like % _ # like ‘刘%’ %:任意长度 _:单个字符
- order by # order by Grader desc
- 聚集函数:
- count
- sum
- avg
- max
- min
- 聚集函数只能用于select 子句和group by 中的having子句。不能用聚聚集函数作为条件表达式
- group by
- select Sno from SC group by Sno having count(*)>3
连接查询 (一个查询同时涉及两个以上的表)
- 等值与非等值连接查询
-
当连接运算符为 = 时,称为等值连接。使用其他运算符称为非等值连接
-
连接谓词中的列名 称为连接字段。
-
where 子句是由 连接谓词 和 选择谓词 组成的复合条件
select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and SC.Grade > 90;
-
- 自身连接
-
//查询每一门课程的间接先修课程(即课程的先修课的先修课)
select first.Cno,secone.Cpno # 课程编号 from Course first,Course second where first.Cpno=second.Cno
3.外连接
-
//在获取学生表所有的数据的情况下与选课表连接,即学生表与选课表连接,删去学生表为null的项,其他项保留
select Student.Sno,Student.Sname,Ssex,Student.Sage,Sdept,Cno,Grade from Student left outer join SC on(Student.Sno=SC.Sno)#on:连接谓词
- left outer join 左外连接
- right outer join 右外连接
- 多表连接(两个以上的表的连接)
select Student.Sno,Sname,Cname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno
-
嵌套查询
-
查询块:select - from - where
-
嵌套查询:将一个查询块嵌套在另一个查询块的 where 子句或 having短语的条件中的查询
-
父查询:上层查询块
-
子查询:下层查询块
-
不相关子查询:子查询条件不依赖于父查询
-
相关子查询:子查询的条件依赖于父查询。整个查询语句称为相关嵌套查询
select Sname from Student where Sno in( select Sno from SC where Cno='2';)
-
带有 in 的子查询
-
带有比较运算符的子查询
-
找出 每个 学生 超过他自己选修课程平均成绩的 课程号
select Sno,Cno from SC x where Grade >= ( select avg(Grade) from SC y where x.Sno=y.Sno);#连接谓词,条件依赖父查询
-
-
带有 any(some) 或 all 谓词的子查询
- | >any >=any <any <=any =any !=(或<>)any
- | >all >=all <all <=all =all(通常没有实际意义) !=all
-
可以用聚集函数来代替
select Sname,Sage from Student where Sage < all( select Sage from Student where Sdept='SC') and Sdept<>'SC';
-
带有exists谓词的子查询
- exist | not exist
- 不返回任何数据,仅产生 true、false
- exist: 若内层查询结果非空,则外层的where子句返回真值,否则返回假值
- exist 存在量词,没有全称量词 -->否定之否定
- (任意 x)P ≡ ¬(存在x(¬P))
- 逻辑蕴含: p -> q 等价于 ¬p V q
-
查询选修了全部课程的学生的名称 <–> 不存在 没有选某一门课的学生名称
select Sname from Student where not exists (select * from Course where not exist (select * from SC where Sno=Student.Sno # 选择谓词 判断当前学生是否选修了当前课程 and Cno=Course.Cno));
-
查询至少选修了学生201215122选修的全部课程的学生号码
-
select distict Sno from SC scx where not exists (select * from SC scy where scy.Sno='201215122' and # 当前学生为201215122 且 其他所有学生都 not exist (select * from SC scz where scz.Sno=scx.Sno and scz.Cno=scy.Cno));
-
-
集合查询
- 并操作 union
- 将两张表合并为一张表
select *
from Student
where Sdept=‘CS’
union
select *
from Student
where Sage<=19
- 将两张表合并为一张表
- 交操作 intersect
- 取两张表的交集
- 差操作 except
-操作同一类型的表时,通常可以用其他方法代替(and 、子查询)
- 并操作 union
-
基于派生表的查询
- 子查询出现在 from 子句中,这时查询产生的临时派生表成为主查询的查询对象
-
select 语句的一般格式
数据更新
- 插入数据 insert into
- 插入元组
- 插入子查询结果
- 修改数据 update set
- 修改某一个元组的值 – where限定
- 修改多个元组的值 – where限定 或 不限定
- 带子查询的修改语句
- 删除数据 delete from
- 删除某一元组的值
- 删除多个元组的值
- 带子查询的删除语句
带空值的处理
-
is null | is not null
-
空值的判断
select sno from sc where Grade < is null and Grade <60;
-
空值的运算
- 空值与另一个值的算术运算的结果为空值
- 空值与另一个值得比较运算的结果为 unknown (true false unknown)
视图
- 视图是一个虚表。数据库中只存放视图的定义,而不存放视图对应的数据,数据存放在原来的基本表中
-
建立视图
create view is_student as select Sno,Sname,Sage from Student where Sdept='IS' [with check option]; -- **不允许更新**
-
删除视图
drop view <name>[cascade];
-
查询视图
select Sno,Sage from is_student where Sage<20;
-
更新视图
-
更新: insert | delete | update
-
由于视图是不实际存储数据的虚表,因此对视图的更新最终要转化为对基本表的逻辑更新。
update is_student set Sage='pg' where Sno='3116000000' // ----> update Student set Sage='pg' where Sno='3116000000' and Sdept='IS';
-
有些视图是不可以更新的,因为有些视图的更新不能唯一地转换成对应基本表的更新。
-
视图的作用
- 视图能够简化用户的操作
- 视图能够使用户以多种角度看待同一数据
- 视图对重构数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
- 适当利用视图可以更清晰地表达查询