《数据库原理》课程笔记 (Ch03-数据库语言)

2024-02-23 18:58:40

DBMS提供操作命令和语言来操作数据库。SQL语言是非过程、关系数据库语言。

数据定义语言

基表和视图

基表：数据显式地存储在数据库中
视图：虚表，只有逻辑定义

建表操作

CREATE TABLE
NOT NULL
PRIMARY KEY
FOREIGN KEY ... REFERENCES ...
ON DELETE
- RESTRICT：凡被基表引用的主键，不得删除
- CASCADE：如主表中删除了某一主键，则基表中引用此主键的行也被删除

表更新操作

添加列：ALTER TABLE ADD
添加主键：ALTER TABLE ADD PRIMARY KEY
删除主键：ALTER TABLE DROP PRIMARY KEY
删除表：DROP TABLE

索引建立

CREATE INDEX H_INDEX ON STUDENT(HEIGHT)
CREATE UNIQUE INDEX SC_INDEX ON SC (SNO DESC, CNO ASC)，UNIQUE用在索引属性是主键时

查询

基本查询语句

SELECT：要查询的项目，通常是列名或表达式
FROM：被查询的表或视图
WHERE：查询条件
GROUP BY：按列的值分组
ORDER BY：查询结果排序

例子

查询所有女学生的身高，以厘米表示

SELECT SNAME, 100*HEIGHT FROM STUDENT WHERE SEX='女'

学习课程CS-211的学生学号和姓名

[方法1：连接查询]
SELECT STUDENT.SNO, SNAME FROM STUDENT, SC WHERE STUDENT.SNO=SC.SNO AND CNO='CS-221'
       ↑由于SNO在STUDENT和SC中都出现，  ↑两个相关表   ↑连接条件是SNO相等的进行连接（笛卡尔积）
        故这里要指明，而SNAME就不用，     都要FROM
        因为只在STUDENT中出现
[方法2：嵌套查询]
SELECT SNO, SNAME FROM STUDENT WHERE SNO IN (
  SELECT SNO FROM SC WHERE CNO='CS-221'
)
即先将选了CS-221的学号筛出来，再通过学号比对确定学生具体信息
[方法3：嵌套查询+存在量词]
SELECT SNO, SNAME FROM STUDENT WHERE EXISTS (
  SELECT * FROM SC WHERE SC.SNO=STUDENT.SNO AND CNO='CS-221'
)
EXISTS表示内层查询结果非空，注意对比方法3和方法2

用嵌套查询逐次求解层次分明，执行效率也比连接查询做笛卡尔积效率高。

查询的几种类型

连接查询
嵌套查询
- 关联嵌套：子查询中有父查询的变量（如方法3）
- 非关联嵌套：子查询中没有父查询的变量（如方法2）

查询秋季学期有一门以上课程获90分以上的学生名

SELECT SNAME FROM STUDENT WHERE SNO IN ( --- 学号反查姓名
	SELECT SNO FROM SC WHERE GRADE>=90 AND CNO IN ( --- 查出分数大于90的学号
  	SELECT CNO FROM COURSE WHERE SEMESTER='秋' --- 查出秋季课程的课程号
  )
)

查询只有一人选修的课程号

SELECT CNO FROM SC SCX --- 别名写法，与后面的SC区分
WHERE CNO NOT IN (
	SELECT CNO FROM SC WHERE SNO<>SCX.SNO
)

查询至少选修课程号为 CS-110 和 CS-201 的学生学号

SELECT X.CNO FROM SC AS X, SC AS Y
WHERE X.SNO=Y.SNO --- 是同一名学生
AND X.CNO='CS-110' AND Y.CNO='CS-201'

列出计算机系所开课程的最高成绩、最低成绩和平均成绩。如果某门课程的成绩不全（GRADE 中有NULL出现），则该课程不统计，结果按CNO升序排列

SELECT CNO, MAX(GRADE), MIN(GRADE), AVG(GRADE) FROM SC
WHERE CNO LIKE 'CS*' --- 计算机系
GROUP BY CNO --- 相同CNO的查询结果聚合
HAVING CNO NOT IN ( --- HAVING要和GROUP BY合用
  SELECT CNO FROM SC WHERE GRADE IS NULL --- 查出成绩不全的课程
) ORDER BY CNO ASC

GROUP BY子句按列值分组，列值相同的分为一组。

查询秋季学期有2门及以上课程获90分以上成绩的学生名

[1 - 嵌套写法]
SELECT SNAME FROM STUDENT WHERE SNO IN (
	SELECT SNO FROM SC WHERE GRADE>90 GROUP BY SNO HAVING COUNT(*)>=2 --- 条目大于等于2的，按SNO汇总
)
[2 - 连接写法]
SELECT A.SNAME FROM STUDENT A, SC B 
WHERE A.SNO=B.SNO AND B.GRADE>90 --- A、B两表按SNO连接，B只给出GRADE>90的记录
GROUP BY A.SNAME HAVING COUNT(*)>=2 --- 然后在利用GROUP BY HAVING COUNT(*)>=2来去除条目数不够的

增删改

增
- INSERT INTO 表 VALUES (按顺序给出)
- INSERT INTO 表(属性顺序) VALUES (按顺序给出)
删

DELETE FROM SC WHERE ...
改

UPDATE 表 SET 属性=值 WHERE ...

视图

视图是由其它视图或基表导出的虚表，是一种在逻辑上定义的表。视图对应的内容总是实时、最新的内容（类似于一个SELECT语句）。

定义一视图GRADE-AVG，表示学生的平均成绩，其中包括SNAME和AVG GRADE（平均成绩）两个属性。
CREATE VIEW GRADE-AVG(SNAME, AVGGRADE) --- 给视图起列名
AS SELECT SNAME, AVG(GRADE) FROM STUDENT, SC WHERE STUDENT.SNO=SC.SNO GROUP BY SNAME

视图的查询可像基表一样参与数据库操作， 视图的更新最终落实到有关基表的更新。但视图的更新存在语义问题，通常都会加以限制。

视图的作用

提供了逻辑独立性：数据库结构发生改变时，只需要修改视图，就能提供原先需要的数据
简化了用户观点：用户只关心结果，不关心内部细节
数据库的安全保护功能：只给用户暴露视图，不暴露数据库细节

其他

递推查询
嵌入式SQL

需要解决如下问题：
- 如何将嵌有SQL的宿主语言程序编译成可执行码
- 宿主语言和DBMS之间如何传递数据和信息
- 如何将查询结果赋值给宿主语言程序中的变量
- 宿主语言与SQL之间数据类型的转换问题
嵌入式SQL的实现，有两种处理方式：
- 扩充宿主语言的编译程序，使之能处理SQL语句
- 采用预处理方式
动态SQL

嵌入式SQL必须在源程序中完全确定，实际问题中用户对数据库的操作只有在运行时才能得到

码农公寓