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

上一篇:浅学SQL Sever之数据库操纵(3)数据的操作第一部分


下一篇:visual studio 2010问题修复