【数据库原理】SQL语言
定义
基本定义功能-定义模式
- 创建模式基本语句
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
例子：定义一个 学生-课程模式 S-T
Create schema “S-T” authorization Wang
- 不指明模式名则隐含为 模式名为用户名
- 例子：
Create schema authorization Wang - 定义一个命名空间
- CREATE SCHEMA < 模式名> AUTHORIZATION <用户名> [<表定义子句> |<视图定义子句>|<授权定义子句>]
例子：
Create schema test authorization Zhang
create table Tab1 (A1 int,
A2 char(10),
A3 char(20))
基本定义功能-删除模式
- 删除模式基本语句
DROP SCHEMA <模式名> <CASCADE|RESTRICT>
例子：Drop schema Zhang cascade
基本定义功能-创建表
- SQL基本建表语句
CREATE TABLE 表名
(<列名1> 数据类型 [列级约束],
[<列名2> 数据类型 [列级约束],]
……,
[表级约束])
基本定义功能-数据类型
- char(n) ：固定长度为n的字符串
- varchar(n)： 可变长度为n的字符串
- smallint： 短整数
- int： 长整数
- numeric(p,s) ：定点数。由p位数字组成，包括s 位小数
- float(n)： 精度至少为n位数字的浮点数
- real：取决于机器精度的浮点数
- double precision：取决于机器精度的双精度浮点数
- date： 日期，格式为年、月、日,YYYY-MM-DD
- time： 时间，格式为时、分、秒, HH:MM:SS
基本定义功能-表的操作
- 修改
添加列|完整性约束：ALTER TABLE <表名> ADD <列名> <数据类型> [完整性约束]
删除列|完整性约束：ALTER TABLE <表名> DROP <列名> <数据类型> [完整性约束]
修改列：ALTER TABLE <表名> ALTER COLUMN <列名> <数据类型>
- 修改表：在学生表中添加字段Sclass，字符类型，长度20
Alter table Students add Sclass char(20) - 将年龄的数字类型由字符型改为整型
Alter table Students alter column Sage int - 增加课程名称必须取唯一值的约束条件
Alter table Courses add unique (Cname)
- 删除表
Drop table <表名> [restrict|cascsde]
- Restrict: 表删除有限制条件，不能被其他表的约束所引用，不能有视图、触发器、存储过程或函数
- Cascade:把一个基本表的定义连同表上的所有记录、索引以及由此基本表导出的所有视图都删除，并释放相应的存储空间
- 删除表Students：
Drop table Students cascade
查询
- SQL查询语句的基本结构
SELECT [DISTINCT] <目标列> [,<目标列>]
FROM <基本表 (视图)> [,<基本表 (视图)>]
[WHERE <条件表达式>]
[Group by <列名1> [having <分组表达式>] ]
[Order By <列名2>[ asc | desc ]];
- 用户可以通过指定别名来改变查询结果的列标题
select Sname NAME, ‘Year of birth’ BIRTH,
2020-Sage BIRTHDAY,
islower(Sdept) DEPARTMENT
from Students;
- 消除取值重复的行
- 查询选修了课程的学生学号
select Sno
from SC;
- 消除重复行
select distinct Sno
from SC;
- 查询满足条件的元组：WHERE子句
常用查询条件：
| 查询条件 | 谓词 |
| ---- | ---- |
| 比较 | =, >, <, >=, <=, !=, <>, !>, !< not + 上述比较运算符（例如not=） |
| 确定范围 | between and, not between and |
| 确定集合 | in, not in |
| 字符匹配 | like, not like |
| 空值 | is null, is not null |
| 多重条件 | and, or | - 确定集合查询
- 查询信息系、数学系和计算机系学生的姓名和性别
select Sname, Ssex
from Students
where Sdept in (‘IS’, ‘MA’, ‘CS’); # 注意：字符类型的查询条件需要使用引号标注属性值
- 与in相对的谓词是Not in
- 字符匹配查询
- 用谓词like进行字符串的匹配
[not] like ‘<匹配串>’[escape ‘<转换字符>’]- 通配符
%：任意长度的字符串。 例如：a%b——acb, addgb,ab…
_：任意单个字符 例如：a_b——acb, adb…
- 空值查询
- 查询缺考学生的学号和课号
select Sno, Cno
from SC
where Grade is null;
- 查询有成绩的学生的学号和课号
select Sno, Cno
from SC
where Grade is not null; # is 不能用“=”
- 复合查询
- 对查询结果排序：ORDER BY子句
- ORDER BY <列名> [asc | desc]
- asc 升序；desc降序
- 对于空值，若按升序排列，含空值的元组将在最后显示，若按降序排列，空值的元组将最先显示
- 例子：查询选修了3号课程的学生学号和成绩，要求查询结果按成绩降序排列
select Sno, Grade
from SC
where Cno=‘3’
order by Grade desc;
- 例子：查询全体学生的情况，查询结果按系号升序排列，同一系的学生按年龄降序排列
select *
from Students
order by Sdept asc, Sage desc;
多关系查询
- 集合操作
- 多个SQL的查询结果可以进行集合操作
- 多关系的连接操作
- 等值连接\自身连接\外连接\复合条件连接
- 集合操作主要有: union、intersect和except
- 参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同
- 多表连接查询
SQL嵌套查询
- 嵌套查询
- 不相关子查询
- 带有in的子查询：子查询结果为多值
- 带有比较运算符的子查询：子查询结果为单值的情况
一个能成为元组字段值的原子值称为标量 - 注意：子查询一定要跟在比较符之后，不能用=表示值和表间的等值判断
- 相关子查询
SQL分组聚集查询
- 分组和聚集操作符被用来扩展关系代数，其功能是根据元组的一个或一组属性将关系中的元组划分成组，然后对关系中的其它列使用聚集操作进行相关统计。
- 聚集操作是对关系中的每个分组单独进行的。
- 在查询结果中，每个分组对应结果关系中的一个元组。
- SQL使用SELECT子句中的聚集函数实现聚集操作，使用GROUP BY子句进行分组定义，使用HAVING子句设定分组的选择条件。
- SQL提供的主要聚集函数
count(*)
count( [distinct|all] <列名>)
sum( [distinct|all] <列名>)
avg( [distinct|all] <列名>)
max ( [distinct|all] <列名>)
min ( [distinct|all] <列名>)
4.分组聚集查询的规则
- 所有FROM子句中关系的属性都可以在SELECT子句中用聚集运算，但只有出现在GROUP BY子句中的属性，才能以不聚集的方式出现在SELECT子句中
- HAVING子句中的聚集只应用到正在检测的分组
- 所有FROM子句中关系的属性都可以在HAVING子句中用聚集运算，但只有出现在GROUP BY子句中的属性，才能以不聚集的方式出现在HAVING子句中
SQL数据操纵功能
数据插入
- 单记录插入
insert into Students values(‘2014020’, ‘陈东’, ‘男’, 18,‘IS’);
insert into SC(Sno, Cno) values(‘2014020’, ‘1’);
- 多记录插入(插入子查询结果)
假定数据库中有表:deptage(Sdept char(15), Avgage smallint)
则可以将Students中的部分数据导入到deptage
insert into deptage ( sdept, avgage )
select Sdept, avg( Sage )
from Students
group by Sdept;
数据删除
- delete from Students where Sno = ‘2014019’;
- 带有子查询的删除
delete from SC
where 'CS' =
( select Sdept
from Students
where Students.Sno = SC.Sno );
数据修改
更新操作与数据库的一致性
SQL视图
视图-定义
- 基于关系建立视图
例子：建立信息系学生的视图
create view IS_S as
select * from Students
where Sdept=‘IS’;
- 行列子集视图：从单个基本表导出，去掉了一些
行和列，但保留了码
create view IS_Students(No, Name, Sex) as
select Sno, Sname, Ssex from Students
where Sdept=‘IS’;
4. with check option子句
- 对视图进行UPDATE、INSERT、DELETE时要保证更新、插入、删除的行满足视图定义的谓词条件。
create view IS_S as
select * from Students
where Sdept=‘IS’
with check option;
视图-删除
- 语句格式：DROP VIEW <视图名>
- 将视图定义从数据字典中删除，且由该视图导出的视图均失效，要从数据字典中将失效的视图都删除，以免用户使用错误
浙公网安备 33010602011771号