SQL数据定义语言DDL、更新语言DML、视图

本文深入介绍了SQL中的数据定义语言DDL,包括创建、修改和删除数据库、表以及索引,详细展示了各种操作实例。同时,讲解了数据操纵语言DML,如插入、删除和修改数据的语法,并探讨了视图的创建、查询和更新。此外,还讨论了视图在数据更新时的限制和注意事项。
摘要由CSDN通过智能技术生成

结构化查询语言SQL包括:数据定义语言DDL、数据操纵语言DML、数据控制语言DCL和其它。

1. SQL数据定义语言DDL

数据库中的关系集合必须由SQL的数据定义语言DDL(data definition language)来定义。
包括:数据库、关系模式、每个属性的值域、完整性约束、每个关系的索引集合和关系的物理存储结构等

SQL数据定义语言DDL包括:

  • 数据库的定义:创建、修改和删除;
  • 基本表的定义:创建、修改和删除;
  • 视图的定义:创建和删除;
  • 索引的定义:创建和删除。
    在这里插入图片描述

1.1 数据库的定义

1.1.1 创建数据库

建立学生成绩数据库ScoreDB

CREATE DATABASE ScoreDB 
ON    -- 定义第一个逻辑设备(默认为主逻辑设备)及其数据文件
      (  NAME=ScoreDB,     -- 数据文件的逻辑文件名(即别名)
         FILENAME='e:\SQLDatabase\ScoreDB.mdf',   -- 物理(磁盘)文件名 
         SIZE=2, 
         MAXSIZE=10, //限制maxsize 使得别人无法在攻破数据库后,无限塞垃圾
         FILEGROWTH=1 )    //初始是2兆,每次增加1兆
LOG ON         -- 定义日志逻辑设备及其日志文件
      (  NAME=ScoreLog,     -- 日志文件的逻辑文件名(即别名)
         FILENAME='e:\SQLDatabase\ScoreLog.ldf',   -- 日志(磁盘)文件名 
         SIZE=1, 
         MAXSIZE=5, 
         FILEGROWTH=1 )

建立一个复杂的数据库MyTempDB

CREATE DATABASE MyTempDB 
ON 
     PRIMARY          -- 定义主逻辑设备及其数据文件
          (  NAME = TempDev,                        -- 数据文件的逻辑文件名(即别名)
             FILENAME ='d:\TempData\TempDev.mdf',      -- 物理(磁盘)文件名 
             SIZE = 5, 
             FILEGROWTH = 2 ), 
      FILEGROUP TempHisDev    -- 定义第1个用户逻辑设备组的名称及数据文件
          (  NAME = TempHisDev1,               -- 数据文件的逻辑文件名(即别名)
             FILENAME ='d:\TempData\TempHisDev1.mdf',        -- 物理文件名 
             SIZE = 10, 
             FILEGROWTH = 5 ), 
                   FILEGROUP TempBakDev    -- 定义第2个用户逻辑设备组的名称及其数据文件
          (  NAME = TempBakDev1,   --该用户逻辑设备组中第1个数据文件的逻辑文件名
             FILENAME ='d:\TempData\TempBakDev1.mdf',  --该数据文件的物理文件名 
             SIZE = 5,  FILEGROWTH = 2 ),
          (  NAME = TempBakDev2,   -- 该用户逻辑设备组中第2个数据文件的逻辑文件名
             FILENAME ='d:\TempData\TempBakDev2.mdf', --该数据文件的物理文件名
             SIZE = 5,  FILEGROWTH = 2 ), 
         (  NAME = TempBakDev3,   -- 该用户逻辑设备组中第3个数据文件的逻辑文件名
             FILENAME ='d:\TempData\TempBakDev3.mdf', --该数据文件的物理文件名
             SIZE = 5,  FILEGROWTH = 2 ) 
 LOG ON                       -- 定义日志逻辑设备及其日志文件
       (  NAME ='TempLogDev1',    -- 日志逻辑设备中第1个日志文件的逻辑文件名
          FILENAME ='d:\TempData\TempLogDev1.ldf',   --该日志文件的物理文件名
          SIZE = 5MB,  FILEGROWTH = 2MB ), 
       (  NAME ='TempLogDev2',    -- 日志逻辑设备中第2个日志文件的逻辑文件名
          FILENAME ='d:\TempData\TempLogDev2.ldf',   --该日志文件的物理文件名
          SIZE = 5MB,  FILEGROWTH = 2MB )

1.1.2修改数据库

修改MyTempDB数据库

     ALTER DATABASE MyTempDB 
     MODIFY FILE ( NAME = TempHisDev1, 
                                    SIZE = 20MB )   

将逻辑文件名(即别名)为TempHisDev1的磁盘文件的初始大小修改为20M

1.1.3 删除数据库

删除数据库时,系统会同时从系统的数据字典中将该数据库的描述一起删除

有的数据库系统会自动删除与数据库相关联的物理文件

删除数据库操作的语法为:

   DROP DATABASE <databaseName>

1.2 基本表的定义

1.2.1 SQL中的基本数据类型

  • 整型:int (4B),smallint (2B),tinyint (1B);
  • 实型:float,real (4B),decimal(p, n),numeric(p, n);
  • 字符型:char(n),varchar(n),text;
    //char固定长度 varchar变长
  • 2进制型:binary(n),varbinary(n),image;
  • 逻辑型:bit,只能取0和1,不允许为空;
  • 货币型:money (8B, 4位小数),small money (4B, 2位小数);
  • 时间型:datetime (4B, 从1753.1.1开始),
    smalldatetime (4B, 从1900.1.1开始)

其中:image为存储图象的数据类型,text存放大文本数据

1.2.2 创建基本表

建立学生成绩管理数据库中的5张基本表。

CREATE TABLE Course (          -- 创建课程表Course
    courseNo	      char(3) 	                             NOT NULL,     --课程号
    courseName   varchar(30)   UNIQUE            NOT NULL,     --课程名
    creditHour     numeric(1)     DEFAULT  0     NOT NULL,     --学分
    courseHour    tinyint            DEFAULT  0      NOT NULL,     --课时数
    priorCourse   char(3)		                NULL,               --先修课程
    /* 建立命名的主码约束和匿名的外码约束 */
    CONSTRAINT CoursePK PRIMARY KEY (courseNo),
    FOREIGN KEY (priorCourse)  REFERENCES  Course(courseNo)  
)              -- 外码约束是匿名的
CREATE TABLE Class (               -- 创建班级表Class
        classNo	        char(6) 	                        NOT NULL,       --班级号
        className   varchar(30)  UNIQUE       NOT NULL,       --班级名
        institute       varchar(30)                         NOT NULL,       --所属学院
        grade           smallint     DEFAULT  0    NOT NULL,       --年级
        classNum     tinyint	                        NULL,                 --班级人数
        CONSTRAINT ClassPK PRIMARY KEY (classNo) 
) 
CREATE TABLE Term (               -- 创建学期表Term
        termNo	        char(3) 	                        NOT NULL,       --学期号
        termName   varchar(30)                         NOT NULL,       --学期描述
        remarks       varchar(10)                         NULL,                 --备注
        CONSTRAINT TermPK PRIMARY KEY (termNo)  
) 
CREATE TABLE Student (               -- 创建学生表Student
        studentNo      char(7) 	                                       NOT NULL
        CHECK (studentNo LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),    --学号
     	studentName   varchar(20)                                      NOT NULL,     --姓名
    	sex 	      char(2)                                             NULL,               --性别
        birthday      datetime 	                                       NULL,      --出生日期
        native        varchar(20)                                     NULL,              --籍贯
        nation        varchar(30)   DEFAULT  ‘汉族’   NULL,              --民族
        classNo       char(6) 	                                        NULL,      --所属班级
       CONSTRAINT StudentPK PRIMARY KEY (studentNo),
       CONSTRAINT StudentFK FOREIGN KEY (classNo) 
                            REFERENCES Class(classNo)
) 
CREATE TABLE Score (                   -- 创建成绩表Score
        studentNo  char(7) 	 	               NOT NULL ,      --学号
        courseNo    char(3) 		               NOT NULL ,      --课程号
        termNo	       char(3) 	                             NOT NULL,       --学期号
        score           numeric(5, 1)   DEFAULT  0   NOT NULL ,      --成绩
         /* 建立由3个属性构成的命名的主码约束 */
        CONSTRAINT ScorePK 
                  PRIMARY KEY (studentNo, courseNo, termNo),
         /* 建立三个命名的外码约束 */
        CONSTRAINT ScoreFK1 FOREIGN KEY (studentNo)
                            REFERENCES Student(studentNo),
        CONSTRAINT ScoreFK2 FOREIGN KEY (courseNo) 
                            REFERENCES Course(courseNo),
        CONSTRAINT ScoreFK3 FOREIGN KEY (termNo) 
                            REFERENCES Term(termNo)
) 

1.2.3 修改基本表

通过ALTER TABLE命令修改基本表的结构,如扩充列等.
修改基本表操作的语法为(为待修改表的名称):

增加列(新增一列的值为空值):
ALTER TABLE <tableName>
     ADD <columnName> <dataType>
增加约束:
ALTER TABLE <tableName>
     ADD CONSTRAINT <constraintName>
删除约束:
ALTER TABLE <tableName>
     DROP <constraintName>
修改列的数据类型:
ALTER TABLE <tableName>
     ALTER COLUMN <columnName> <newDataType>
//在MyTempDB数据库中为TempTable表增加一列。
ALTER TABLE TempTable
ADD xsex int DEFAULT 0
//在MyTempDB数据库中为TempTable表的xname 列修改数据类型。
ALTER TABLE TempTable
ALTER COLUMN xname char(10)
//在MyTempDB数据库中为TempTable表的xname列增加唯一约束
ALTER TABLE TempTable
ADD CONSTRAINT UniqueXname UNIQUE (xname)
// 注意:基本表在修改过程中,不可以删除列,一次仅执行一种操作。

1.2.4 删除基本表

DROP TABLE TempTable

1.3 索引的定义

如在班级表Class中按所属学院建立索引InstituteIdx
在这里插入图片描述

  • 索引的类型有:聚集或非聚集,非聚集索引就是普通索引,一个基本表可以建立多个普通索引。
  • 每个基本表仅能建立一个聚集索引
    1. 聚集索引按搜索码值的某种顺序(升/降序)来重新组织基本表中的记录
    2. 即索引的顺序就是基本表记录存放的顺序
    3. 聚集索引可以极大地提高查询速度,但是给数据的修改带来困难
      建立了聚集索引的基本表一般仅执行查询操作,很少进行更新操作,这在数据仓库中使用得较多。

1.3.1建立索引

//对班级表Class按所属学院建立非聚集索引InstituteIdx.
CREATE NONCLUSTERED INDEX InstituteIdx 
ON Class(institute)
// 在学生表Student中,首先按班级编号的升序,然后按出生日期的降序建立一个非聚集索引ClassBirthIdx。
CREATE INDEX ClassBirthIdx 
ON Student(classNo, birthday DESC)

1.3.2 删除索引

//删除班级表Class中建立的InstituteIdx索引。
DROP INDEX InstituteIdx ON Class

2. SQL数据更新语言DML

数据操纵语言DML(data manipuplation language) 主要用于对数据库的数据进行检索(查询)和更新。

SQL数据更新语句包括三条:插入INSERT、删除DELETE和修改UPDATE。

2.1 插入数据

  1. 将一个新学生元组(‘0700006’, ‘李相东’, ‘男’, ‘1991-10-21 00:00’, ‘云南’, ‘撒呢族’, ‘CS0701’)插入到学生表Student中。
INSERT INTO Student 
VALUES ( '0700006', '李相东', '男', '1991-10-21 00:00', '云南', 
                   '撒呢族', 'CS0701' )
//本例表名Student后没有指定属性名,表示按照Student表定义的属性列的个数和顺序将新元组插入到Student表中。
  1. 将一个新学生元组(姓名:章李立,出生日期:1991-10-12 00:00,学号:1500007)插入到学生表Student中。
 INSERT INTO Student(studentName, birthday, studentNo)
 VALUES ( '章李立', '1991-10-12 00:00', '0700007' )
 //本例按照指定属性的顺序和属性的个数向学生表Student插入一个新元组,没有列出的属性列自动取空值NULL或默认值;
//插入新元组时,数据的组织可不按照基本表结构定义的属性个数和顺序进行插入。
  1. 将少数民族同学的选课信息插入基本表StudentNation中
//首先,创建基本表StudentNation:
     CREATE TABLE StudentNation (
            studentNo  char(7) 	                                NOT NULL ,      --学号
            courseNo   char(3)                                       NOT NULL ,      --课程号
            termNo      char(3)                                       NOT NULL,       --学期号
            score          numeric(5, 1)   DEFAULT  0   NOT NULL        --成绩
                  CHECK( score BETWEEN 0.0 AND 100.0),     
            CONSTRAINT StudentNationPK 
                   PRIMARY KEY (studentNo, courseNo, termNo)
      )
//然后,执行如下插入语句:
     INSERT INTO StudentNation
         SELECT * 
         FROM Score
         WHERE studentNo IN ( 
                SELECT studentNo FROM Student WHERE nation<>'汉族' )

👆此处注意表定义与子查询结构的一致性

  1. 将汉族同学的选课信息插入到StudentNation表中
INSERT INTO StudentNation(studentNo, courseNo, termNo)
      SELECT studentNo, courseNo, termNo
      FROM Score
      WHERE studentNo IN ( 
                       SELECT studentNo 
                       FROM Student 
                       WHERE nation='汉族' )
//该查询仅将汉族同学的学号、课程号和学期号插入到基本表StudentNation中;
//成绩列自动取0值,而不是空值NULL,这是因为在定义基本表StudentNation时该列设置了默认值为0。

2.2 删除数据

删除学号为1600001同学的选课记录。

DELETE FROM Score 
WHERE studentNo='1600001'
[1] 删除选修了“高等数学”课程的选课记录。
DELETE FROM Score 
WHERE courseNo IN ( 
                 SELECT courseNo 
                 FROM Course 
                 WHERE courseName='高等数学' )
[2] 删除平均分在6070分之间的同学的选课记录
DELETE FROM Score 
WHERE studentNo IN (
                 SELECT studentNo 
                 FROM Score 
                 GROUP BY studentNo
                 HAVING avg(score) BETWEEN 60 AND 70 )

2.3 修改数据

  1. 将王红敏同学在151学期选修的002课程的成绩改为88分。
UPDATE Score 
SET score=88
WHERE courseNo='002' AND termNo='151' 
      AND studentNo IN 
                 (  SELECT studentNo FROM Student 
                    WHERE studentName='王红敏' )
也可以写成:
UPDATE Score 
SET score=88
FROM Score a, Student b
WHERE a.studentNo=b.studentNo AND courseNo='002' 
      AND termNo='151' AND studentName='王红敏'
  1. 将注册会计16_02班的男同学的成绩都增加5分。

UPDATE Score 
SET score=score+5
FROM Score a, Student b, Class c
WHERE a.studentNo=b.studentNo AND b.classNo=c.classNo
      AND className='注册会计16_02班' AND sex='男'
  1. 将学号为1600001同学的出生日期修改为1999年5月6日出生,籍贯修改为福州。
UPDATE Student 
SET birthday='1999-5-6 00:00',  native='福州'
WHERE studentNo='0800001'

注意:插入、删除和修改操作会破坏数据的完整性,如果违反了完整性约束条件,其操作会失败。

4.将每个班级的学生人数填入到班级表的ClassNum列中。

UPDATE Class 
SET classNum=sCount 
FROM Class a, 
              ( SELECT classNo, count(*) sCount
                FROM Student
                GROUP BY classNo ) b
WHERE a.classNo=b.classNo

3. 视图

3.1 创建视图

  1. 创建仅包含1999年出生学生的视图StudentView1999。
CREATE VIEW StudentView1999
AS
      SELECT * 
      FROM Student
      WHERE year(birthday)=1999      -- 创建视图中的谓词条件

本例省略了视图的列名,自动取查询出来的列名。

  • 本例没有使用WITH CHECK OPTION选项,下面的插入语句可以执行:
INSERT INTO StudentView1999 VALUES
    ('1500008', '李相东', '男', '1998-10-21 00:00', '云南', '撒呢族', 'CS1501')
  • 但是, 对视图StudentView1999查询时却不能查询出刚插入的元组, 因为刚插入的学生元组并不满足创建视图中的谓词条件。
  1. 创建仅包含1999年出生学生的视图StudentView1999Chk, 并要求在对该视图进行更新操作时,进行合法性检查
CREATE VIEW StudentView1999Chk
AS
      SELECT * 
      FROM Student
      WHERE year(birthday)=1999       -- 创建视图中的谓词条件
WITH CHECK OPTION

本例建立的视图StudentView1999Chk,其更新操作必须满足:
修改操作:自动加上year(birthday)=1999的条件;
删除操作:自动加上year(birthday)=1999的条件;
插入操作:自动检查是否满足条件year(birthday)=1999 ,如果不满足,则拒绝该插入操作。

由于使用了WITH CHECK OPTION选项,下面的插入语句可以执行:
INSERT INTO StudentView1999Chk VALUES
(‘1500008’, ‘李相西’, ‘男’, ‘1999-10-21 00:00’, ‘云南’, ‘撒呢族’, ‘CS1501’)
下面的插入语句不可以执行:
INSERT INTO StudentView1999Chk VALUES
(‘0700009’, ‘李相南’, ‘男’, ‘1998-10-21 00:00’, ‘云南’, ‘撒呢族’, ‘CS1501’)

  1. 创建一个包含学生学号、姓名、课程名、获得的学分和相应成绩的视图ScoreView

由于成绩必须大于等于60分才获得学分,该视图必须含有该条件

CREATE VIEW ScoreView
AS
      SELECT a.studentNo, studentName, courseName, creditHour, score
      FROM Student a, Course b, Score c
      WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo
             AND score>=60     -- 成绩必须大于等于60分才能获得学分

但是,如果某学生选修某门课程2次且都考试及格,则视图的查询结果中会出现2次。

  1. 创建一个包含每门课程的课程编号、课程名称、选课人数和选课平均成绩的视图SourceView
CREATE VIEW SourceView(courseNo, courseName, courseCount, courseAvg)
AS
      SELECT a.courseNo, courseName, count(*), avg(score)
      FROM Course a, Score b
      WHERE a.courseNo=b.courseNo
      GROUP BY a.courseNo, courseName

本例使用聚合函数,必须为视图的属性命名,可在视图名的后面直接给出列名,也可用下面的语句替代:

CREATE VIEW SourceView1
AS
      SELECT a.courseNo, courseName, count(*) courseCount, avg(score) courseAvg
      FROM Course a, Score b
      WHERE a.courseNo=b.courseNo
      GROUP BY a.courseNo, courseName
  1. 视图也可以建立在视图上。
    创建一个包含每门课程的课程编号、课程名称、选课人数和选课平均成绩的视图SourceView2,要求该视图选课人数必须在5人以上。
CREATE VIEW SourceView2
AS
      SELECT *
      FROM SourceView
      WHERE courseCount>=5       
  1. 创建一个包含学生学号、姓名和年龄的视图
CREATE VIEW StudentAgeView
AS
      SELECT studentNo, studentName, year(getdate())-year(birthday) age
      FROM Student

3.2 查询视图

  1. 在视图StudentView1999中查询CS1601班同学的信息。
SELECT *
FROM StudentView1999
WHERE classNo='CS1601'

对于该查询:
系统首先进行有效性检查:判断视图StudentView1999是否存在?
如果存在,则从系统的数据字典中取出该创建视图的语句;
将创建视图中的子查询与用户的查询结合起来,转换为基于基本表的查询,即将视图StudentView1999的定义转换该查询为:

  • SELECT *
  • FROM Student
  • WHERE year(birthday)=1999 AND classNo=‘CS1601’

然后系统执行改写后的查询。

  1. 在视图SourceView中查询平均成绩在80分以上的课程信息。
   SELECT * 
    FROM SourceView
    WHERE courseAvg>=80

视图SourceView是一个基于聚合运算的视图,列是courseAvg,它是经过聚合函数运算的值。
HAVING子句可以对聚合函数直接作用,系统会将该查询转换为如下的形式:

  • SELECT a.courseNo, courseName, count(*) courseCount, avg(Score)
    courseAvg
  • FROM Course a, Score b
  • WHERE a.courseNo=b.courseNo
  • GROUP BY
    a.courseNo, courseName
    HAVING avg(score)>=80

3.3 更新视图

  1. 视图更新指通过视图来插入、删除和修改基本表中的数据。
  2. 视图不实际存放数据,对视图的更新,最终要转换为对基本表的更新
    1. 如果创建视图的语句中包含了表达式,或聚合运算,或消除重复值运算,则不能对视图进行更新操作。
  3. 对视图进行更新操作,其限制条件比较多
    1. 建立视图的作用不是利用视图来更新数据库中的数据,而是简化用户的查询
    2. 达到一定程度的安全性保护;
    3. 尽量不要对视图执行更新操作。

—————————————— 分界线 —————————————

  1. 在视图StudentView1999中,将学号为’1600004’ 同学的姓名修改为’张小立’
UPDATE StudentView1999
SET studentName='张小立'
WHERE studentNo='1600004'

相当于

UPDATE Student
SET studentName='张小立'
WHERE year(birthday)=1999 AND studentNo='1600004'
  1. 在视图StudentView1999中将学号为’1600004’同学的出生年份由1999修改为2000
 UPDATE StudentView1999
    SET birthday='2000-05-20 00:00:00.000'
    WHERE studentNo='1600004'

在视图StudentView1999Chk中不能将出生年份修改为2000,因为该视图对修改操作进行了检查(即检查修改后的结果是否满足创建视图中的谓词条件)。

  1. 在视图StudentView1999中将学号为’1600006’的同学记录删除
 DELETE FROM StudentView1999
    WHERE studentNo='1600006'

系统将操作转化为

DELETE FROM Student
WHERE year(birthday)=1999     --创建视图中的谓词条件
    AND studentNo='1600006'
  1. 在视图SourceView中删除平均成绩大于80分的课程记录。
 DELETE FROM SourceView
    WHERE courseAvg>=80
  1. 在视图SourceView中删除平均成绩大于80分的课程记录。
DELETE FROM SourceView
    WHERE courseAvg>=80

对于该操作,数据库管理系统拒绝执行
因为视图SourceView中包含了聚合运算,系统无法将该视图转化为对基本表的操作。

3.4 删除视图

  1. 删除视图及级联视图
//删除视图StudentView1991:
   DROP VIEW StudentView1991
//级联删除视图SourceView:
   DROP VIEW SourceVIEW CASCADE

T-SQL语言简介

  1. 在ScoreDB数据库中,查询Score表中的最高成绩,如果最高成绩大于95分,则显示“very good!”。
    在这里插入图片描述

  2. 声明两个局部变量@sno和@score,用于接受SELECT语句查询返回的结果,并显示其结果
    在这里插入图片描述

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值