【数据库】数据库入门(三): SQL

SQL: 结构化查询语言(Structured Query Language)

SQL 是由 IBM 公司首先开发产生,它是关系型数据库最早出现的商用语言之一。1974年,IBM 公司 San Jose, California 研究实验室的 D.D.Chamberlin 和 R.F. Boyce 研制出一套规范语言 SEQUEL (Structured English Query Language),并在1976年11月的 IBM Journal of R&D 上公布新版本的 SQL (叫 SWQUEL/2)。

  • 1980年改名为 SQL。
  • 1986年,美国ANSI采用SQL作为关系数据库管理系统的标准语言(ANSI X3. 135-1986),后为国际标准化组织(ISO)采纳为国际标准。
  • 1989年,美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言,称为ANSI SQL 89,该标准替代ANSI X3.135-1986版本。
  • 1999年,SQL 继续扩展并划分出核心功能并根据这些功能划分出不同的封装。
  • 2003年,SQL 继续扩展,并可以允许 XML 支持。
  • 2011年,SQL 继续扩展,改善了对临时数据库的支持。

跟关系型数据模型不同的是,关系型数据库主要处理对象的集合,即集合中不能出现有重复的对象;而 SQL 主要处理的是多集,即 SQL 允许一种数据关系中包含重复的对象。

SQL 为关系型数据库提供了许多接口,是一种高级的非过程化编程语言,其中包括:

  • 数据定义语言 (DDL: Data Definition Language)
  • 数据操纵语言 (DML: Data Manipulation Language)
  • 数据控制语言 (DCL: Data Control Language)
  • 事务控制语言 (TCL: transaction Control Language)

需要强调的是,在SQL语言的使用过程中,我们常将关键字用大写字母来表示,而其他变量名等,一般用小写字母表示或者按照其自身含义具体实现。

数据定义语言 (DDL: Data Definition Language)

1. 创建表 (CREATE TABLE)

使用 CREATE TABLE 可以创建一个新的关系模式,声明它的表名,属性,还可以指定每个属性的限制条件以及表自身的限制条件。基本格式如:

CREATE TABLE table_name
    (attr_name data_type [attr constraints],
     ...,
     attr_name data_type [attr constraints],
     [table constraints]);

举几个例子:

StudentID Name DoB Email
CREATE TABLE Student
    (StudentID INT,
     Name VARCHAR(50),
     DoB Date,
     Email VARCHAR(100));
No Cname Unit
CREATE TABLE Course
    (No VARCHAR(20),
     Cname VARCHAR(50),
     Unit SMALLINT);
StudentID CourseNo Semester Status
CREATE TABLE Enrol
    (StudentID INT,
     CourseNo VARCHAR(20),
     Semester VARCHAR(50),
     Status VARCHAR(50));

2. 属性的数据类型

常用的属性数据类型有4中,分别是数值型(Numeric)、字符串型(String)、日期与时间(Date and time)以及布尔型(Boolean)。

  • 数值型
    • 整型:INT,SMALLINT
    • 浮点型: FLOAT,REAL,DOUBLE PRECISION
    • 指定显示位数:NUMERIC(i, j),DECIMAL(i, j)。其中 i 为精度,表示总共显示的数字的位数;j 为范围,表示小数点后显示的数字的位数。
  • 字符串型
    • CHAR(n):n 指定属性字符串的长度。
    • VARCHAR(n):n 指定不定长属性字符串允许的最大长度。
    • BIT(n):n 指定比特串的长度。
    • BIT VARYING(n):n 指定不定长比特串允许的最大长度。
  • 日期与时间
    • DATE:(年,月,日)/ (year, month, day)。
    • TIME:(时,分,秒)/ (hour, minute, second)。
    • TIMESTAMP:包含日期和时间,秒最少精确到6位,且可以限定所选的时区。
    • INTERVAL:指定一个相对值,用于增加或者减少上述三种数据类型对应的值。
  • 布尔值:包含 TRUE 和 FALSE 两个值。

在我们创建属性的时候,相应的会配置属性的数据类型。根据需求的不同,使用上述的数据类型,有时候我们也会自定义一些新的数据类型,使用 CREATE DOMAIN 语句,如:

CREATE DOMAIN ssn_type AS CHAR(9);

3. 属性的约束条件

常用于 SQL 语句中的属性约束有以下几种:

  • NOT NULL:声明属性的值不能为空。
  • DEFAULT:为属性指定一个默认的值。
  • CHECK (attr > 0):限定当前属性数据的值在一个范围以内。
  • UNIQUE:确保一个属性的值或者一个表内属性的独特性,即不能出现重复的值或者属性。
  • PRIMARY KEY:当前属性作为主键,在表中唯一识别一个元组。
  • FOREIGN KEY:在两个关系表之间执行实现引用完整性。
  • INDEX:使用索引更快速访问表的特定行。

其中,PRIMARY KEY 和 UNIQUE 约束的属性自带 NOT NULL 的约束,也就是说,被约束为主键或者独特的属性,必然不能为空。

如果主键只包含一个属性,那么 PRIMARY KEY 可以定义在属性后面;如果主键包含多个属性,那么 PRIMARY KEY 要定义为表约束,如:

-- PRIMARY KEY 定义在属性后 
CREATE TABLE Course
    (No VARCHAR(20) PRIMARY KEY,
     Cname VARCHAR(50) UNIQUE,
     Unit SMALLINT not null Default 6);

-- PRIMARY KEY 定义在表约束中
CREATE TABLE Enrol
    (StudentID INT not null CHECK (StudentID>0),
     CourseNo VARCHAR(20) not null,
     Semester VARCHAR(50) not null,
     Status VARCHAR(50),
     PRIMARY KEY(StudentID, CourseNo, Semester),
     ...);

下面是指定 FOREIGN KEY 的一个例子:

CREATE TABLE Enrol
    (StudentID INT,
     CourseNo VARCHAR(20),
     Semester VARCHAR(50),
     Status VARCHAR(50),
     FOREIGN KEY(StudentID) REFERENCES Student(StudentID),
     FOREIGN KEY(CourseNo) REFERENCES Course(No));

INDEX 的例子:

CREATE TABLE Customer
    (CustomerID INT not null CHECK (CustomerID>0),
     Name VARCHAR(50) not null,
     DOB DATE not null,
     Address VARCHAR(80),
     Phone INT CHECK (Phone>0),
     PRIMARY KEY(CustomerID));

CREATE INDEX index1 ON Customer (Name, DOB);
CREATE UNIQUE INDEX index2 ON Customer (Phone);

4. 更改与删除表

使用 ALTER TABLE 常用来更改已经存在的表的内容,可以作出的更改包括:

  • 修改表名

  • 添加或删除属性

  • 修改属性的定义

  • 添加或删除表的约束

ALTER TABLE Customer ALTER COLUMN Address SET NOT NULL;

ALTER TABLE Customer ADD UNIQUE(Phone);

ALTER TABLE Customer ADD CONSTRAINT positive_id CHECK (CustomerID > 0);

ALTER TABLE Enrol ADD FOREIGN KEY(StudentID) REFERENCES Student(StudentID);

ALTER TABLE Customer ADD Email VARCHAR(100);

使用 DROP TABLE 常用于移除一个已创建的表

DROP TABLE Enrol;

DROP TABLE IF EXISTS Customer;

数据操纵语言 (DML: Data Manipulation Language)

 数据操纵语言主要包括四种命令:INSERT,UPDATE,DELETE,SELECT。

1. INSERT 插入命令

INSERT 命令常用于向一个表添加新的元组或者新的项,其主要格式为:

INSERT INTO table_name
    [(attribute_name,...,attribute_name)]
    VALUES (value,...,value),...,(value,...,value);

该系列的前一篇文章里曾经提到过关系型数据库完整性约束的内容:当我们执行 INSERT,UPDATE,DELETE 命令时,可能会导致违反完整性约束的情况。关于完整性约束的内容,可以去看这篇博客:【数据库】数据库入门(二): 关系型数据库

违反主键约束

主键作为在一个表中独立识别一个元组的标志,在不同的元组中,其主键对应的值不能相同,否则违反了主键约束。例如在下表中:

<u>StudentID</u> Name DoB Email
456 Tom 25/01/1988 tom@gmail.com
458 Peter 20/02/1991 peter@hotmail.com
... ... ... ...

如果在这个表中,我们再执行以下插入命令:

INSERT INTO Student(StudentID, Name, DoB, Email)
    VALUES (456, 'Smith', '27/08/1989', 'smith@gmail.com');

即插入一个主键同样为456,但其余值不同的一个新的元组。如果该元组能添加到表中,则存在两个元组的主键即 StudentID 为456,那么我们通过主键 StudentID 并不能识别出一个独立的元组,因此违反了主键约束,故上面这条插入命令不会被正确执行。DBMS 不允许两个主键值相同的元组存在于一个表中。

违反外键约束

违反外键约束常常出现在多个表相互引用的情况下,即在一个表中新添加的元组,其外键对应的值并没有出现在另一个表中时,就会导致违反外键约束。例如有以下两个表:

<u>StudentID</u> Name DoB Email
456 Tom 25/01/1988 tom@gmail.com
458 Peter 20/02/1991 peter@hotmail.com
... ... ... ...
<u>StudentID</u> <u>CourseNo</u> <u>Semester</u> Status EnrolDate
456 COMP1111 2017 S1 active 25/02/2017
458 COMP2222 2017 S2 active 09/06/2017

然后我们执行以下插入命令:

INSERT INTO Enrol(StudentID, CourseNo, Semester, Status)
    VALUES (460, 'COMP2400', '2016 S2', 'active');

可以想象这么一个现实情况:在某一个学期里数据库储存了一个学生在该学期参加某个课程的注册信息,然而该学生却并没有个人记录在学校的数据中,这种情况是非常不合理的。注册信息表必须依赖于学生信息表而存在,因此上面这条命令不能执行成功。DBMS 不会允许一个表中存在这么一个元组:其外键对应的值,在外键对应的引用表中却不存在。

2. UPDATE 命令

UPDATE 命令常用于修改一个或者多个元组中相应属性的值,其主要格式为:

UPDATE table_name
    SET attribute_name = value,...,attribute_name = value
    [WHERE selection_condition];

3. DELETE 命令

DELETE 命令常用于从表中移除存在的元组,其主要格式为:

DELETE FROM table_name
    [WHERE selection_condition];

执行删除命令可以根据具体的条件进行删除操作,也可以删除表中所有的元组。

-- Delete the tuple whose studentID equals to 456.
DELETE FROM Student WHERE StudentID=456;

-- Delete all tuple in Student relation.
DELETE FROM Student;

这里需要注意区别 DELETE 和 DROP 作用是不一样的。DELETE FROM Student 会清空 Student 表内的所有元组,执行命令后该表依然存在,且为空表;而 DROP TABLE Student 不仅清空所有元组,还会删除 Student 这个表,执行命令后该表将不再存在。

引用表之间的行为

由于关系型数据库存在表间的相互引用关系,所以我们会希望在修改一个表中的内容时,其具有引用关系的表中对应内容也能够跟随着同步改变。所以 SQL 提供了以下的关键字:

  • NO ACTION(default):当修改或者删除一个存在引用关系的元组时,系统会抛出一个错误提示 error。
  • CASCADE:删除或者更新操作会随着所有引用关系同步进行。
  • SET NULL:执行删除或者更新操作后,存在引用关系的元组的对应属性会置为 NULL。
  • SET DEFAULT:执行删除或者更新操作后,存在引用关系的元组的对应属性会置为一个指定的默认值。

以上的关键字常用于定义表中作为外键的属性,下面是一个例子:

CREATE TABLE Enrol
    (StudentID INT,
     CourseNo VARCHAR(20),
     Semester VARCHAR(50),
     Status VARCHAR(50),
     FOREIGN KEY(StudentID) REFERENCES Student(StudentID)
         ON DELETE NO ACTION ,
     FOREIGN KEY(CourseNo) REFERENCES Course(No));

4. SELECT 命令

SELETE 命令常用于从数据库中检索数据,其主要格式如下:

SELECT attribute_list
    FROM table_list
    [WHERE condition]
    [GROUP BY attribute_list [HAVING group_condition]]
    [ORDER BY attribute_list [ASC | DESC]];
image.gif

SELETE 后面跟的是想要显示的属性,如果使用星号 “*”,代表要获取指定表的所有属性的值。

FROM 后面跟的是所要获取的表的名字。如果是多个表用逗号 “,” 隔开,则取的是两个表所有元组分别进行笛卡尔积后结合的结果。如果使用 JOIN 关键字,则会根据具体的 JOIN 的形式将多个表进行结合,并从中选出目标属性值。

WHERE 关键字后跟的是取值条件,条件通常为判断某个属性是否为某个指定值或者是否满足数值上的大小关系等。

GROUP BY 关键字会按照后面指定属性进行分组,即该属性值相同的元组将会归为一个组,后面可以使用 HAVING 关键字,其功能与 WHERE 类似,不过它指定的所划分的组中所有元组需要满足的条件。GROUP BY 关键字通常会跟以下的内置函数结合使用:

  • COUNT:统计值不为 NULL 的条目的个数。
  • AVG:求指定参数的平均值。
  • MIN:求指定参数的最小值。
  • MAX:求指定参数的最大值。
  • SUM:求指定参数的总和。

ORDER BY 对检索的内容按照给定属性的值进行排序,默认按照升序排序,也可以使用关键字 ASC 指定升序或者 DESC 指定降序。

关于 SELETE 命令的深入内容,在下一篇文章中会进行详细介绍,并且提供一些例子的讲解。

【数据库】数据库入门(四): SQL查询 - SELETE的进阶使用

参考资料

维基百科:https://zh.wikipedia.org/wiki/SQL

数据库基础:Fundamentals of Database Systems, 7th Edition (Global Edition), R. Elmasri and S. Navathe, 2017

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 213,254评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,875评论 3 387
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 158,682评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,896评论 1 285
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,015评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,152评论 1 291
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,208评论 3 412
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,962评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,388评论 1 304
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,700评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,867评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,551评论 4 335
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,186评论 3 317
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,901评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,142评论 1 267
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,689评论 2 362
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,757评论 2 351

推荐阅读更多精彩内容