【数据库原理及应用】——SQL概述及数据定义（学习笔记）

📖 前言：结构化查询语言（Structured Query Language，简称SQL）是关系数据库的标准语言，由于它具有功能丰富、使用方便灵活、语言简洁易学等突出的优点，因而深受计算机界和计算机用户的欢迎。1986年10月，美国国家标准局（ANSI）的数据库委员会批准将SQL作为数据库语言的美国标准，同年公布了标准SQL。此后不久，国际标准化组织（ISO）也做出了同样的决定。

---

🕒 0. 思维导图

🕒 1. SQL概述

🕘 1.1 什么是SQL

• SQL（Structured Query Language）结构化查询语言，是关系数据库的标准语言
• SQL是一个通用的、功能极强的关系数据库语言

🕘 1.2 SQL的特点

①、综合统一

• 集数据定义语言 DDL(Data Definition Language)，数据操纵语言 DML（Data Manipulation Language），数据控制语言 DCL(Data Control Language) 功能于一体。
• 可以独立完成数据库生命周期中的全部活动：
  • 定义关系模式，插入数据，建立数据库；
  • 对数据库中的数据进行查询和更新；
  • 数据库重构和维护
  • 数据库安全性、完整性控制等
• 用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。
• 数据操作符（即语言风格）统一

②、高度非过程化

• 非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径
• SQL只要提出“做什么”，无须了解存取路径。
• 存取路径的选择以及SQL的操作过程由系统自动完成。

③、面向集合的操作方式

• 非关系数据模型采用面向记录的操作方式，操作对象是一条记录
• SQL采用集合操作方式：
  • 操作对象、查找结果可以是元组的集合
  • 一次插入、删除、更新操作的对象可以是元组的集合

④、以同一种语法结构提供多种使用方式

• SQL是自含式（交互式）语言
  • 能够独立地用于联机交互的使用方式
• SQL又是嵌入式语言
  • SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用

⑤、语言简洁，易学易用 ☆

SQL功能极强，完成核心功能只用了9个动词。

SQL功能	动词
数据查询（DQL）	SELECT
数据定义（DDL）	CREATE（增）、DROP（删）、ALTER（改）
数据操纵（DML）	INSERT、UPDATE、DELETE
数据控制（DCL）	GRANT（授权）、REVOKE（撤回权限）

🕘 1.3 SQL的体系结构

SQL支持关系数据库三级模式结构

• 基本表

  • 本身独立存在的表
  • SQL中一个关系就对应一个基本表
  • 一个(或多个)基本表对应一个存储文件
  • 一个表可以带若干索引

• 存储文件

  • 逻辑结构组成了关系数据库的内模式
  • 物理结构是任意的，对用户透明

• 视图

  • 从一个或几个基本表导出的表
  • 数据库中只存放视图的定义而不存放视图对应的数据
  • 视图是一个虚表
  • 用户可以在视图上再定义视图

• SQL用户

  • 可以是应用程序，也可以是终端用户
  • 可用SQL语言对视图和基本表进行查询

🕒 2. 数据定义

SQL的数据定义功能：模式定义、表定义、视图和索引的定义

操作对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX

🕘 2.1 创建、修改和删除基本表

🕤 2.1.1 定义基本表

三要素：关系名（表名）、属性名（列名）、完整性约束

CREATE TABLE <表名>(
      <列名> <数据类型>[ <列级完整性约束条件> ]
      [，<列名> <数据类型>[ <列级完整性约束条件>] ]
       ………
      [，<表级完整性约束条件> ]
 );

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

❗ 转载请注明出处
作者：HinsCoder
博客链接：🔎 作者博客主页

🕤 2.1.2 常用的完整性约束

• 主码约束： PRIMARY KEY （唯一性）
• 参照完整性约束：FOREIGN KEY ... REFERENCES ...
• 唯一性约束：UNIQUE （与主码约束的区别在于可以取空）
• 非空值约束：NOT NULL
• 取值约束：CHECK

🕤 2.1.3 数据类型

• 不同的数据库系统支持的数据类型不完全相同
• SQL中域的概念用数据类型来实现
• 定义表的属性时 需要指明其数据类型及长度
• 选用哪种数据类型
  • 取值范围
  • 要做哪些运算

常见的数据类型

类型	数据类型举例及缩写	说明
整数数据	bigint、int、smallint、tinyint、bit	前4个分别以8、4、2、1个字节表示，其中bit同C语言的bool，输入0以外的值均认为是1
精确数值数据	decimal、numeric	必须指定范围和精度：DECIMAL（p [,d]）例：DECIMAL(10,2)（总体10位，小数点后2位）（其中,2可省略，这样就变成10位整数了） NUMERIC和DECIMAL相同 注意：我们学过C语言的float其实是不精确的
浮点数值数据	float、real	分别是8个字节最大53位、4个字节最大24位
字符串数据	char、varchar	固定长度字符串，如CHAR(n)，不足会用空格补上；可变字符串，尾部的空格会去掉
日期时间数据	date、time、datetime	date：日期类型 DATE’yyyy-mm-dd’ 例： DATE ’ 2022-10-23 ’ time：时间类型 TIME ‘hh:mm:ss’ 例：TIME ’ 16 : 55 : 02 . 5 ’ datetime：日期时间类型

例1：建立一个学生“STUDENT”表。

CREATE TABLE Student
   ( Sno  char(8)  PRIMARY KEY,			/*列级完整性约束*/
     Sname    varchar(10)  NOT NULL,
     Ssex      char(2)  NOT NULL  DEFAULT('女'),
     Sage      smallint,
     Smajor    varchar(20) ,
     Shometown  varchar(24) 
   );

例2： 建立一个课程“Course”表。

CREATE TABLE Course
   ( Cno  char(4) PRIMARY KEY,
     Cname    varchar(20)  NOT NULL,
     Cpno     char(4),			-- 先修课
     Ccredit   real,
     FOREIGN  KEY (Cpno)  REFERENCES Course(Cno)  -- 表级完整性约束条件， Cpno是外码，被参照表是Course 
   );

例3： 建立一个选修“SC”表，其中（Sno，Cno）为主码

CREATE TABLE SC
 (Sno  	char(8)	, 
  Cno  	char(4),
  Grade   smallint,
  PRIMARY  KEY (Sno, Cno), /* 主码由两个属性构成，必须作为表级完整性进行定义*/
  FOREIGN  KEY (Sno)  REFERENCES Student (Sno) , 
  /* 表级完整性约束条件， Sno是外码，被参照表是Student */
  FOREIGN  KEY (Cno)  REFERENCES Course(Cno)
  /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
 );

CREATE TABLE SC
 (Sno  	char(8)	REFERENCES Student (Sno),  /* 列级完整性约束条件*/
  Cno  	char(4)	REFERENCES Course(Cno),    /* 列级完整性约束条件*/
  Grade   	smallint,
  PRIMARY  KEY (Sno, Cno), /* 主码由两个属性构成，必须作为表级完整性进行定义*/
  );

例子分析结果：
（1）主关键字的定义：有两种方法
① 列级的完整性约束，在属性及其类型后加上保留字PRIMARY KEY ；
② 表级的完整性约束，在列出关系模式的所有属性后，再附加一个声明：PRIMARY KEY （<属性1>[，<属性2>，…] 。
注意：当主关键字由一个属性列组成时，可采用上述两种方法定义，但是如果关键字由多个属性构成时，则必须采用第二种方法。

（2）外部关键字的定义
其格式为：REFERENCES <表名> <属性>
既可以定义在列级，也可以定义在表级。外键一定要有被参照表！
（3）关于缺省值
在定义属性时增加保留字DEFAULT和一个合适的值，例如： 年龄 SMALLINT DEFAULT 18

🕤 2.1.4 修改基本表

ALTER TABLE <表名>
   [ ADD     [COLUMN] <新列名>< 数据类型> [完整性约束]] 
   [ DROP    [COLUMN] <列名>[RESTRICT | CASCADE]]
   [ MODIFY  [COLUMN] <列名><新数据类型>];

注意：在SQL Server中不支持MODIFY，用的是ALTER。

其中：

• RESTRICT：删除该列是有限制的。
  在没有视图或约束引用到该属性列时，才能在基本表中删除该列，否则拒绝删除操作。
• CASCADE：删除该列没有限制。
  在基本表中删除该列时，所有引用到此列的视图和约束也要一起被自动删除。

例4：在Student表中增加“总学分TotalCredit”属性，类型为INT型。

ALTER TABLE Student ADD TotalCredit INT;

需要说明的是，新增加的列不能定义为“NOT NULL”。基本表在增加一列后，原有元组在新增加的列上的值都被定义为空值（NULL）。

例5：在Student表中删除“籍贯Shometown”属性。

ALTER TABLE Student DROP Shometown;

注意：这条语句在MySQL里正常，但在SQL Server 会报错，需要在DROP后加上COLUMN。原因是系统无法区分要删除的是列还是完整性约束。

ALTER TABLE Student DROP COLUMN Shometown;

例6：在Student表中将主键（Sno）删去。

ALTER TABLE Student DROP Sno;

例7：在Student表中将“籍贯Shometown”属性的字符长度改为30。

ALTER TABLE Student Alter COLUMN Shometown CHAR(30);

修改原有的列定义时需要慎重，很可能会破坏不满足条件的数据，比如修改Student中属性籍贯Shometown的字符串长度由24变为18，表中超过18长度的字符串都会被截取，丢失信息。

🕤 2.1.5 删除基本表

DROP TABLE <表名>［RESTRICT|CASCADE］；

• 系统从数据字典中删去：
  • 该基本表的描述
  • 该基本表上的所有索引的描述
  • 该基本表表中的数据
• 表上的视图往往仍然保留，但无法引用

这里引入一个概念：数据字典

• 数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。
• 关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。
• 在进行查询优化和查询处理时，数据字典中的信息是其重要依据。

例8：假设已经存在一个表，表名为“临时表”，现将其删除，并将与该表有关的其他数据库对象一起删除。

DROP TABLE 临时表 CASCADE;

例9：删除SC表

DROP TABLE SC

前面曾经提到过，不同的数据库产品对于SQL语言的支持会有所不同，SQL99有RESTRICT和CASCADE选项，含义与前面相同。目前居于主流的Oracle 9i数据库只有CASCADE选项，而SQL Server数据库这两个选项都没有。

🕘 2.2 创建和删除索引

• 建立索引的目的：加快查询速度

• 谁可以建立索引？

  • DBA 或 表的属主（即建立表的人）
  • DBMS一般会自动建立以下列上的索引
    • PRIMARY KEY
    • UNIQUE

• 谁维护索引？

  • DBMS自动完成

• 使用索引

  • DBMS自动选择是否使用索引以及使用哪些索引

• RDBMS中索引一般采用B+树、HASH索引来实现

  • B+树索引具有动态平衡的优点
  • HASH索引具有查找速度快的特点

• 采用B+树，还是HASH索引 则由具体的RDBMS来决定

• 索引是关系数据库的内部实现技术，属于内模式的范畴

• 很多DBMS使用主键的概念建立主索引，一个表只能有一个主索引

• CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引，不同的DBMS略有区别

🕤 2.2.1 建立索引

CREATE [UNIQUE] [CLUSTERED] INDEX <索引名> 
ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；	

• Unique表明此索引的每一个索引值只对应唯一的数据记录
• Cluster指其索引项的顺序与表中记录的物理顺序一致的索引组织

例10：为Student、Course和SC表建立索引。

CREATE  INDEX  STU_IDX_SNO  ON   Student (Sno);
CREATE  INDEX  COU_IDX_CNO  ON   Course (Cno);
CREATE  INDEX  SC_IDX_SNO_CNO  ON  SC (Sno, Cno);

🕤 2.2.2 删除索引

DROP INDEX <索引名> ON <表名>;
DROP INDEX <表名>.<索引名>;

• 删除索引时，系统会从数据字典中删去有关该索引的描述。
• DROP INDEX命令可以删除当前数据库内的一个或几个索引，有的时候需要添加表名作为索引名的前缀，中间通过“.”来连接。当一个索引被删除后，该索引先前占有的存储空间就会被收回。但是，DROP INDEX不会影响PRIMARY KEY和UNIQUE约束条件，这些约束条件删除必须用ALTER TABLE DROP命令来完成。

例11：删除SC表中在学号Sno和课程号Cno上建立的索引

SC_IDX_SNO_CNO。
DROP  INDEX  SC.SC_IDX_SNO_CNO

🕤 2.2.3 使用索引的技巧

（1）记录少的表不适宜建立。
（2）索引列中有较多的不同的数据和空值时，会大大提高索引的性能。
（3）当查询要返回的数据很少时，索引可以优化你的查询（比较好的情况是少于全部数据的25%），否则，会加大系统开销。
（4）索引可以提高数据的返回速度，但会使更新操作变慢，因此不要对经常需要更新或修改的字段创建索引。
（5）在设计数据库的可用空间时要考虑索引所占用的空间。
（6）不要将索引与表存储在同一个驱动器上。

🕒 3. 实操

前置工作：创建数据库




导出数据到EXCEL：

🕒 4. 课后习题

1. 【单选题】建立数据库三级模式的功能由SQL语言的________完成。
   A、数据定义功能
   B、数据操纵功能
   C、数据查询
   D、数据控制

2. 【单选题】以下关于聚集索引和非聚集索引说法正确的是（ ）。
   A、每个表只能建立一个非聚簇索引
   B、每个表只能建立一个聚簇索引
   C、一个表上不能同时建立聚集簇和非聚簇索引
   D、以上都不对

3. 【填空题】数据库语言包括____和数据操纵语言两大部分，前者负责描述和定义数据库的各种特性，后者说明对数据库进行的各种操作。

4. 【填空题】
   假设有两个关系：
   Departments(Deptno, Dname, TotalNumber, Phone, Loc)
   Employees(Eno, Ename, Eage, Sex, Salary, Deptno）
   其中Employees表的Deptno值参照Departments表的Deptno，则Deptno为Employees表的外键，在SQL语句中用（ ）关键词定义该约束，用（ ）关键词表示该引用关系。

5. 【判断题】可以在值为“null”的列上建立主键约束。（ ）

答案：1.A 2.B 3.数据定义语言 4.foreign key references 5.×

---

OK，以上就是本期知识点“SQL概述及数据定义”的知识啦~~ ，感谢友友们的阅读。后续还会继续更新，欢迎持续关注哟📌~
💫如果有错误❌，欢迎批评指正呀👀~让我们一起相互进步🚀
🎉如果觉得收获满满，可以点点赞👍支持一下哟~

❗ 转载请注明出处
作者：HinsCoder
博客链接：🔎 作者博客主页