目录
SQL--Structured query language结构化查询语言
3.1 SQL概述
3.1.1 SQL的产生和发展
目前,没有一个数据库系统能够支持SQL标准的所有概念和特性。大部分数据库系统能支持SQL/92标准中的大部分功能以及SQL99、SQL2003中的部分新概念。同时,许多软件厂商对SQL基本命令集还进行了不同程度的扩充和修改,又可以支持标准以外的一些功能特性。
3.1.2 SQL的特点
1. 综合统一
数据库系统的主要功是通过数据库支持的数据语言来实现。
非关系模型(层次模型、网状模型)的数据语言一般都分为:
1)模式数据定义语言(Schema Data Definition Language,模式DDL);
2)外模式数据定义语言(Schema Data Definition Language,外模式DDL,子模式DDL);
3)数据存储有关的描述语言(Data Storage Description Language,DSDL);
4)数据操纵语言(Data Manipulation Language,DML)。
它们分别用于定义模式、外模式、内模式和进行数据的存取与处置。当用户数据库投入运行后,如果需要修改模式,必须停止现有数据库的运行,转储数据,修改模式并编译后再重装数据库,十分麻烦。
SQL则集数据定义语言DDL、数据操纵DML、数据控制语言DCL的功能于一体,语言风格统一,可以以独立完成数据库生命周期中的全部活动,包括:
• 定义关系模式,插入数据,建立数据库;
• 对数据库中的数据进行查询和更新;
• 数据库重构和维护;
• 数据库安全性、完整性控制,以及事务控制;
• 嵌入式SQL和动态SQL定义。
这就为数据库应用系统的开发提供了良好的环境。特别是用户在数据库系统投入运行后,还可根据需要随时地逐步地修改模式,并不影响数据库的运行,从而使系统具有良好的可扩展性。
另外,在关系模型中实体和实体之间的联系用关系表示,这种数据结构的单一性带来了数据操作符的统一性,查找、插入、删除、更新等每一种操作都只需一种操作符,从而克服了非关系系统由于信息表示方式的多样性带来的操作复杂性。
2. 高度非过程化
--大大减轻了用户负担,而且有利于提高数据独立性。
3. 面向集合的操作方式
非关系数据模型采用的是面向记录的操作方式,操作对象是一条记录。而SQL采用集合操作方式,不仅操作对象、查找结果可以是元组的集合,而且一次插入、删除、更新操作的对象也可以是元组的集合。
4. 以同一种语法结构提供多种使用方式
SQL既是独立的语言,又是嵌入式语言。 作为独立的语言,它能够独立地用于联机交互的使用方式,用户可以在终端键盘上直接键入SQL命令对数据库进行操作;作为嵌入式语言,SQL语句能够嵌入到高级语言程序中,供程序员设计程序时使用。而在两种不同的使用方式下,SQL的语法结构基本上是一致的。这种以统一的语法结构提供多种不同使用方式的做法,提供了极大的灵活性与方便性。
5. 语言简洁,易学易用
3.1.3 SQL的基本概念
支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构,如图3.1所示,其中,外模式包含若干视图(view)和部分基本表(base table),数据库模式包括若干基本表,内模式包括若干存储文件。
用户可以用SQL对基本表和视图进行各种操作,基本表和视图一样,都是关系。
基本表是本身独立存在的表,一个关系就对应一个基本表。一个或多个基本表对应一个存储文件,一个表可以带若干索引,索引也存放在存储文件里。
存储文件的逻辑结构组成了关系数据库的内模式,存储文件的物理结构对最终用户是隐蔽的。
视图是从一个或多个基本表导出的表,它本身并不独立存在在数据可中,即数据库只存放视图的定义而不存放对应的数据,这些数据在基本表中。视图是一个虚表。视图在概念上和基本表等同,用户可以在视图上继续定义视图。
3.2 学生-课程数据库—然后以此为例讲解SQL基本语句
学生表:student(sno,sname,ssex,sage,sdept)sno为主码
课程表:course (cno,cname,cpno,ccredit) cno为主码
学生选课表 :sc(Sno,Cno,Grade)Sno、Cno同为主码
3.3 数据定义??怎么对应三级结构?
不提供修改模式和视图的语句,只能删掉重建。
3.3.1模式的定义与删除
要有数据库管理员权限,或被授予了权限。
1、定义模式
create schema <模式名> authorization <用户名>
[<表定义子句> / <视图定义子句> / <授权定义子句>]
没有<模式名>会自动命名为用户名。
注意有无分号!!(蓝色字不一定,看3.3.2的3、模式与表方法三)
2、删除模式
drop schema <模式名> < cascade / restrict >
- cascade(级联) 删除模式的同时把该模式中所有的数据库对象全部删除
- restrict(限制) 仅当该模式中没有任何下属对象(如表、视图)时才能执行
3.3.2 基本表的定义、删除和修改
1、定义基本表
create table <表名>
(
<列名> <数据类型> [列级完整性约束条件] ,
<列名> <数据类型> [列级完整性约束条件],
...
[表级完整性约束条件]
)
2、数据类型
每个数据库管理系统不一样。举例MySQL:
3、模式与表
创建表时要定义其所属的模式:
方法一:在表名里明显地给出模式名
CREATE TABLE “S-T”.Student(…);
方法二:在创建模式的时候创建表,就是上面不加分号那里。
方法三:设置所属的模式,这样相当于你就待在这个模式里,干啥都是他的。
SHOW search_path;
//查看当前搜索路径 默认值$user, PUBLIC 指首先搜索与用户名相同的模式,如果没有就使用PUBLIC模式(先不管,用到再查)数据库管理员可以自己设置搜索路径:
SET search_path TO “S-T”, PUBLIC;
4、修改基本表
ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据类型>[完整性约束]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT] ]
[DROP CONSTRAINT <完整性约束名> [CASCADE|RESTRICT]]
[ALTER COLUMN <列名> <数据类型>];
5、删除基本表
DROP TABLE <表名> [CASCADE|RESTRICT];
3.3.3 索引的建立和删除
索引—提高查找效率,只有数据库管理员或表的属主可以建立索引。
常见的索引有:
顺序文件上的索引:指按指定属性值升序或降序存储的关系;
B+树索引:将索引属性组织成B+树的形式;
散列索引:
位图索引:
等。
1、建立索引
create [unique][CLUSTER] index <索引名> on <表名> ( 列名 [asc / desc ] );
*/ <索引名>可以建立在表的一列或多列上;unique 唯一索引一个索引值对应唯一数据记录,不写就是普通索引,cluster先不管,第7章讲;默认asc升序/*
Eg:
create unique index Stusno on student (sno);
create unique index Stusno on sc (Sno asc,Cno desc);
还可以用alert来创建。
!!—索引也是一种关系!
2、修改索引
alter index <旧索引> rename to <新索引>;
3、删除索引
DROP INDEX <索引名>
4、查看索引
show index from <表名>;
3.3.4数据字典
记录了数据库中所有的定义信息。
3.4 数据查询
SELECT [ALL|DISTINCT] <目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] …
[WHERE <条件表达式>]
[GROUP BY<列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC];
3.4.1 单表查询—只涉及一个表
1、选择表中的若干列(自定义顺序)—即projection投影
1)指定查询列
SELECT Sno,Sname
FROM Student;
2)查询全部列
自定义顺序的话就全部列出;
按表中顺序的话:
SELECT *
FROM Student;
3)查询经过计算的值—表达式、字符串常量、函数、别名等。
Eg:??改名为什么不用as,MySQL自己规定的吗
2、选择表中的若干元组
1)消除取值重复的行—distinct
筛掉一些列之后可能有重复的行,用关键字distinct可以去掉。
SELECT DISTINCT Sno
FROM SC;
没有指定的话等价于:
SELECT ALL Sno
FROM SC;
2)查询满足条件的元组—where
Eg:
ESCAPE '\' 表示“ \” 为换码字符
3、OEDER BY子句
可以按一个或多个属性列排序;
升序:ASC;
降序:DESC;
缺省值为升序;
空值看数据库系统怎么实现,例如升序放最后,降序放最前,或者都最后。
4、聚集函数
!!!注意:where语句里不可以有聚集函数!select和having才可以有!!
计数
COUNT([DISTINCT|ALL] *)
COUNT([DISTINCT|ALL] <列名>)
计算总和
SUM([DISTINCT|ALL] <列名>)
计算平均值
AVG([DISTINCT|ALL] <列名>)
最大最小值
MAX([DISTINCT|ALL] <列名>)
MIN([DISTINCT|ALL] <列名>)
关键字distinct和all用来选择去不去重!!
5、GROUP BY语句
Group by语句将查询结果按某一列或多列的值进行分组,值相等的一组。
HAVING将条件作用于每一分组。未对查询结果分组,聚集函数将作用于整个查询结果,对查询结果分组后,聚集函数将分别作用于每个组。作用对象是查询的中间结果表。
3.4.2 连接查询—涉及两个表的查询
通过在where子句中添加连接谓词来实现。(所以where里既可以有连接谓词还会有选择谓词)
1、等值与非等值连接查询
等值连接:比较运算符为“=”,其中,把目标列中重复的属性列去掉则为自然连接。
(关系代数里,自然连接是取两表中相同名字的列对比然后合起来,也一样,并且会引出外连接等。)
2、外连接—保留悬浮元组,用ON<条件>!!
SELECT <字段名>
FROM <表1> OUTER JOIN <表2> <ON子句>
SELECT <字段名>
FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
SELECT <字段名>
FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
3、自身连接—自己和自己连,所以要取别名!!
select c.Cno, c.Cname, c.Cpno , s.Cname 先行课程名, s.Cpno
from Course c, Course s
where c.Cpno = s.Cno
4、多表查询,一样的。
3.4.3 嵌套查询
在SQL语言中,一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询。
SELECT Sname /*外层查询或父查询*/
FROM Student
WHERE Sno IN
(SELECT Sno /*内层查询或子查询*/
FROM SC
WHERE Cno='2');
- 带有IN谓词的子查询
在嵌套查询中,子查询的结果往往是一个集合,所以谓词 IN 是嵌套查询中最经常使用的谓词。
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='刘晨');
一般能用连接查询就用连接查询,因为目前商业的数据库管理系统的连接嵌套查询不够完善。
1) 不相关子查询—子查询的查询条件不依赖于父查询,上面那个例子。
2)相关子查询--子查询的查询条件不依赖于父查询。看下面的例子。
2、带有比较运算符的子查询—当能确定子查询返回的是一个单值的话!!
3、带有ANY(SOME)或ALL谓词的子查询—返回多值时!!
反正就是any、some和all。
!!比任意一个年龄小是只要小于最大那个就好啦!!
4、带有EXISTS谓词的子查询(NOT EXISTS)
带有EXISTS 谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
使用存在量词EXISTS后,若内层查询结果为空,则外层的WHERE子句返回真值,否则返回假值。
一般内层select * ,因为选择列没有意义。
下面这个例子,用连接查询也行!!记得吗,关系代数那里—除运算
3.4.4 集合查询
Select出来的是元组集合,那么可以把这些元组集合再进行一些union、intersect、except。
参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同。
3.4.5 基于派生表的查询
生成临时派生表Avg_scà临时派生表Avg_sc与基本表SC连接à查询。
注意!!临时派生表一定要有别名!
3.4.6 SELECT语句的一般格式(相当于前面的总结)
再说:暂时懒得整理,直接截!
3.5数据更新(插入、修改、删除)
3.5.1 插入数据insert
1、插入元组
INSERT
INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
!!字符串常数用单引号括起来!!没给的列会自动赋NULL
2、插入子查询结果
3.5.2 修改数据
UPDATE <表名>
SET 字段1=值1, 字段2=值2, …
WHERE …;
1、修改某一个元组的值
2、修改所有元组的值
3、带子查询的修改语句
3.5.3 删除数据
3.6 空值的处理—再说:比较简单
3.7 视图
视图是从一个或多个基本表导出的虚表,只存放定义。一旦对应的基本表发生变化,视图也变。但其操作跟基本表都一样。
3.7.1 定义视图
1、建立视图
在对视图定义时并不执行select操作,只是把定义存入数据字典,只有对视图查询时,才会从基本表里面查出来。
CREATE VIEW <视图名>[(<列名>[,<列名>].…)]
AS
select statement
[WITH CHECK OPTION];
注意:
① select statement 可以是任意复杂的SELECT语句,!!但不允许含有ORDERBY子句和DISTINCT短语
② with check option的作用是使得插入、修改和删除操作都自动加上谓词条件。
③ 组成视图的属性列要么全部省略,要么全部指定。如果全部省略,视图的属性列由查询中的SELECT子句中的属性列组成。
④ 行列子集视图—指从一个表导出的。
⑤ 视图的定义里可以from view
⑥ 含表达式的:
2、删除视图
DROP VIEW<视图名>[CASCADE]
CASCADE级联删除语句:将此视图和其导出的所有视图删掉,否则要是有其导出的视图会执行失败。
当导出视图的基本表被删除了,但视图的定义还在,需要显式地使用drop view语句删除。
3.7.2 查询视图
语句和基本表一样。
注意一下本质:
- 过程是这样的:从数据字典中取出视图的定义à把定义中的查询和用户查询结合起来,转换成等价的基本表的查询à执行修正了的查询。这一过程称为视图消解。
- 有时候会出现错误,因为转换过去导致了where子句里出现了聚集函数,但它是不可以的,自己看上面。
- 视图会一直存在,但基于派生表的查询只是短暂的过客。
3.7.3 更新视图
一样的,update、insert into和delete。!!注意,你老是想要alert,不对!
3.7.4 视图的作用
1)视图能够简化用户的操作。
2)视图使用户能以多种角度看待同一数据。
3)视图对重构数据库提供了一定程度的逻辑独立性。
4)视图能够对机密数据提供安全保护。--对系统中的不同用户定义不同的视图,使用户只能访问他权限范围内的数据。
5)适当的利用视图可以更清晰的表达查询。