Chinaunix首页 | 论坛 | 博客
  • 博客访问: 56131
  • 博文数量: 29
  • 博客积分: 667
  • 博客等级: 上士
  • 技术积分: 300
  • 用 户 组: 普通用户
  • 注册时间: 2010-04-11 15:55
文章分类
文章存档

2012年(2)

2011年(27)

我的朋友
最近访客

分类: 数据库开发技术

2011-09-27 16:38:19

  1. SQL 语句
  2.  教学数据库中有三个基本表:
  3.      S(S#, SNAME, AGE, ***) #***这里被屏蔽了,其实是S#E#X
  4.      SC(S#, C#, GRADE)
  5.      C(C#, CNAME, TEACHER)
  6. 1、基本表的创建,可用下列语句来创建:
  7.      CREATE TABLE S
  8.          (S# CHAR(4) NOT NULL, #长度为4的定长字符串
  9.          SNAME CHAR(8) NOT NULL,
  10.          AGE SMALLINT, #短整型,INT为长整型
  11.          *** CHAR(1),
  12.          PRIMARY KEY(S#) );
  13.      #SQL中允许列值为空,不允许某一列的值为空时,可加上关键字"NOT NULL"
  14.      
  15.     CREATE TABLE C
  16.         (C# CHAR(4),
  17.         CNAME CHAR(10) NOT NULL,
  18.         TEACHER CHAR(8),
  19.         PRIMARY KEY(C#) );
  20.         
  21.     CREATE TABLE SC
  22.     (S# CHAR(4),
  23.     C# CHAR(4),
  24.     GRADE SMALLINT,
  25.     PRIMARY KEY(S#, C#),
  26.     FOREIGN KEY(S#)REFERENCES S(S#),
  27.     FOREIGN KEY(C#)REFERENCES C(C#),
  28.         CHECK(GRADE BETWEEN 0 AND 100) );
  29.     #上面的CHECK检查子句,指出成绩GRADE的值应在0~100之间
  30. 2、基本表结构的修改
  31.     (1)在基本表S中增加一个地址列(ADDRESS):#新增加的列不能定义为NOT NULL
  32.         ALTER TABLE S ADD ADDRESS VARCHAR(30); #VARCHAR(30)最大长度为30的字符串
  33.     (2)在基本表S中删除年龄列(AGE),并且把引用该列的所有视图和约束也一起删除:
  34.         ALTER TABLE S DROP AGE CASCADE;
  35.     #CASCADE表示在基本表中删除某列时,所有引用到该列的视图和约束也一起自动删除;
  36.     #RESTRICT方式表示在没有视图或约束引用到该属性时,才能删除该列,否则拒绝删除操作。
  37.     (3)在基本表S中,S#的长度修改为6:
  38.         ALTER TALBE S MODIFY S# CHAR(6);
  39. 3、基本表的撤消
  40.     (1)撤消基本表S:
  41.         DROP TALBE S RESTRICT; ##CASCADE,RESTRICT同上
  42. 4、索引表操作
  43.     (1)对基本表S的列S#建立索引,索引键名为S#_INDEX:
  44.         CREATE INDEL S#_INDEX ON S(S#);
  45.     (2)对基本表SC的(S#, C#)建立索引:
  46.         CREATE UNIQUE INDEX SC_INDEX ON SC(S# ASC, C# DESC);
  47.     (3)撤消索引S#_INDEX和SC_INDEX:
  48.         DROP INDEX S#_INDEX, SC_INDEX;
  49. 5、SQL的数据查询
  50.     (1)检索学习课程号为C2的学生学号与成绩:
  51.         SELECT S#, GRADE
  52.         FROM SC
  53.         WHERE C# = 'C2';
  54.     (2)检索学习课程号为C2的学生学号与姓名:
  55.         SELECT S.S#, SNAME
  56.         FROM S, SC
  57.         WHERE S.S = SC.S AND C# = C2;
  58.         或者:
  59.         SELECT S#, SNAME
  60.         FROM S
  61.         WHERE S# IN (SELECT S#
  62.                 FROM SC
  63.                 WHERE C# = 'C2';);
  64.     (3)检索选修课程名为MATHS的学生学号与姓名:
  65.         SELECT S.S#, SNAME
  66.         FROM S, SC,C
  67.         WHERE S.S# = SC.S# AND SC.C# = C.C# AND CNAME = 'MATHS';
  68.         或者:
  69.         SELECT S#, SNAME
  70.         FORM S
  71.         WHERE S# IN (SELECT S#
  72.                 FROM SC
  73.                 WHERE C# IN (SELECT C#
  74.                     FROM C
  75.                     WHERE CNAME = 'MATHS'));
  76.     (4)检索选修课程号为C2或C4的学生学号:
  77.         SELECT S#
  78.         FROM SC
  79.         WHERE C# = 'C2' OR C# = 'C4';
  80.     (5)检索至少选修课程号为C2和C4的学生学号:
  81.         SELECT X.S#
  82.         FROM SC AS X, SC AS Y
  83.         WHERE X.S# = Y.S# AND X.C# = 'C2' AND Y.C# = 'C4';
  84.     (6)检索不学C2课程的学生姓名与年龄:
  85.         SELECT SNAME, AGE
  86.         FROM S
  87.         WHERE S# NOT IN(SELECT S#
  88.                 FROM SC
  89.                 WHERE C# = 'C2');
  90.         或者:
  91.         SELECT SNAME, AGE
  92.         FROM S
  93.         WHERE NOT EXISTS (SELECT *
  94.                 FROM SC
  95.                 WHERE SC.S# = S.S# AND C# = 'C2');
  96.     
  97.     (7)检索学习全部课程的学生姓名:
  98.         SELECT SNAME FROM S
  99.         WHERE NOT EXISTS
  100.             (SELECT * FROM C
  101.             WHERE NOT EXISTS
  102.                 (SELECT *
  103.                 FROM SC
  104.                 WHERE SC.S# = S.S# AND SC.C# = C.C#));
  105.     (8)统计每一年龄选修课程的学生人数:
  106.         SELECT AGE, COUNT(DISTINCT S.S#)
  107.         FROM S,SC
  108.         WHERE S.S# = SC.S#
  109.         GROUP BY AGE;
  110.     (9)求基本表S中男同学的每一年龄组(超过50人)有多少人?要求查询结果按人数升序排列,人数相同按年龄降序排列:
  111.         SELECT AGE, COUNT(S#)
  112.         FROM S
  113.         WHERE *** = 'M'
  114.         GROUP BY AGE
  115.             HAVING COUNT(*) > 50
  116.         ORDER BY 2, AGE DESC #2表示对SELECT子句中的第2个属性值进行排序
  117.     (10)
  118. 6、SQL的数据更新
  119.     (1)往基本表SC中插入一个元组(S4, C6, 90):
  120.         INSERT INTO SC(S#, C#, GRADE)
  121.             VALUES('S4', 'C6', 90)
  122.     (2)在基本表SC中,把平均成绩大于80分的男学生的学号和平均成绩存入另一个已知的基本表S_GRADE(S#, AVG_GRADE):
  123.         INSERT INTO S_GRADE(S#, AVG_GRADE)
  124.             SELECT S#, AVG(GRADE)
  125.             FROM SC
  126.             WHERE S# IN
  127.                 (SELECT S# FROM S WHERE *** = 'M')
  128.             GROUT BY S#
  129.                 HAVING AVG(GRADE) > 80
  130.     (3)把课程名为MATHS的成绩从基本表SC中删除
  131.         DELETE FROMS SC
  132.             WHERE C# IN (SELECT C# FROM C
  133.                 WHERE CNAME = 'MATHS');
  134.     (4)把C4课程中小于该课程平均成绩的成绩元组从基本表SC中删除:
  135.         DELETE FROM SC
  136.             WHERE C# = 'C4'
  137.             AND GRADE < (SELECT AVG(GRADE) FROM SC
  138.                     WHERE C# = 'C4')
  139.     (5)把C5课程的任课教师姓名改为WU:
  140.         UPDATE C SET TEACHER = 'WU'
  141.             WHERE C# = 'C5';
  142.     (6)把女同学的成绩提高10%:
  143.         UPDATE SC SET GRADE = GRADE * 1.1
  144.             WHERE S# IN (SELECT S# FROM S
  145.                     WHERE *** = 'F')
  146.     (7)当C4课的成绩低于该门课程平均成绩时,提高5%:
  147.             UPDATE SC SET GRADE = GRADE * 1.05
  148.                 WHERE C# = 'C4'
  149.                 AND GRADE < (SELECT AVG(GRADE) FROM SC
  150.                 WHERE C# = 'C4')
  151.     (8)在C中,把课程号为C5的元组修改为(C5, DB, LIU)
  152.         UPDATE C
  153.         SET ROW = (C5, DB, LIU)
  154.         WHERE C# = 'C5';
  155. 7、视图的创建
  156.     (1)对于教学数据库中基本表S, SC, C,用户经常要用到S#, SNAME, CNAME 和 GRADE等列的数据,那么可用下列语句创建视图:
  157.         CREATE VIEW SUTDENT_GRADE(S#, SNAME, CNAME, GRADE)
  158.             AS SELECT S.S#, SNAME, CNAME, GRADE
  159.             FROM S, SC, C
  160.             WHERE S.S# = SC.S# AND SC.C# = C.C#;
  161.     (2)撤消SUTDENT_GRADE视图:
  162.         DROP VIEW SUTDENT_GRADE;
  163.     (3)如果视图是从单个基本表中只使用选择、投影操作导出且包含了基本表的主键,则这样的视图称为“行列子集视图”,且可以被执行更新操作。
  164.     允许用户更新的视图在定义时必须加上“WITH CHECK OPTION”短语。
阅读(748) | 评论(0) | 转发(0) |
0

上一篇:没有了

下一篇:神的代码

给主人留下些什么吧!~~