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

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必须在源程序中完全确定，实际问题中用户对数据库的操作只有在运行时才能得到