数据库讲解---(SQL语句--表的使用)【MySQL版本】

零.前言

数据库讲解（MySQL版）（超详细）【第一章】-CSDN博客

数据库-ER图教程_e-r图数据库-CSDN博客

数据库讲解（MySQL版）（超详细）【第二章】【上】-CSDN博客

一.SQL概述

1.1SQL简介

SQL是关系数据库的标准语言，也称“结构化查询语言”，它介于“关系代数”和“元组演算”之间的语言

1.2SQL的特点

• “高度非过程化”：用户只需要提出“做什么”，无须指明“怎么做”
• “功能完备并且一体化”：使用SQL可以完成绝大部分数据库的操作
• “统一的语法结构”：SQL可以用于所有用户模型
• “语言简洁，易学易用”：SQL功能很强，并且语言简洁

1.3SQL体系结构

SQL支持“外模式、模式、内模式”三级结构：

二.SQL的定义和功能

2.1表结构的定义

SQL创建基本表的语法结构：

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

不同数据的数据类型是不同的，例如：“MySQL”与“Oracle”之间的数据类型就是不完全相同的

常见的“完整性约束条件”有：

• “NOT NULL”：非空约束，即该属性值不能为空
• “UNIQUE”：唯一性约束，即该属性值只能出现一次
• “PRIMARY KEY”：主键约束
• “FOREIGN KEY .... REFERENCE ...”：外键约束

“主键”的作用主要是：“唯一的标识某个属性为整个表的特征值，即该属性的值必须是唯一且非空的”

而“非空约束” + “唯一性约束”组合在一起实现的作用为“属性的值必须是唯一且非空的”

这个“组合”与“主键”的“作用”基本一致，但是不能混淆在一起使用！

（一个表只能定义一个“主键”，而“非空约束”+“唯一性约束”组合可以无限定义！！）

2.1.1下面是一个创建普通“学生”表的例子：

2.1.2下面是一个“课程”表，其中“课程号”为主关键字：

在这里的“课程号”属性中，没必要添加约束：“NOT NULL”和“UNIQUE”，因为“课程号”本身是一个“主键”了

2.1.3下面是一个“学习”表，其中使用多个“属性”作为主关键字：

当我们的“主键”只有一个属性时，可以在“对应属性之后作为列约束添加”也可以在“表完整性约束中添加”

但当我们的“主键”拥有多个属性时，只能在“表完整性约束中添加”

2.1.4外部关键字的定义

我们假设表B的某个关键字是表A的外键

外部关键字的定义与“主键”的定义方式一样，都有两种定义方式：

• “外部关键字只有一个属性”：直接在对应属性名之后使用“REFERENCE <表名> [属性]”
• “外部关键字有多个属性”：在表完整性约束中使用“FOREGIN KEY [表A属性] REFERENCE <表B名字> [表B属性]”

2.1.5建立一个“学习”表，学号和课程是“主键”也是“外键”，分别参照学生表中的学号和课程表中的课程号

注意：“我们使用了两组FOREGIN KEY来分别对应两个外键属性！！”

2.1.6为表中属性指定默认值

我们在定义表时，可以给表中某个属性一个“默认值”

使用方法：

<属性名> (数据类型) DEFAULT [默认值]

例如，创建一个学生表：

CREATE TABLE 学生
(
    学号 CHAR(8),
    排名 CHAR(8),
    性别 CHAR(4) DEFAULT '男',
    年龄 SAMLLINT DEFAULT 19
);

2.2表的修改

当我们创建好一张“表”后，我们可能需要对表中的某些属性约束进行修改，或者删除/添加属性等

为此，我们需要学习表修改的语法：

ALTER TABLE <表名>
[
    ADD <新列名> (数据类型) [完整性约束],
    DROP <列名> [完整性约束],
    DROP COLUMN <列名>,
    ALTER COLUMN <列名> (数据类型)
];

• “ADD”：可用于增加新列和新的完整性约束条件
• “DROP”：可用于删除指定的属性或者完整性约束条件
• “ALTER”：可用于修改原有的列定义（包括列名、数据类型、完整性约束）

2.2.1在学生表中增加“年龄”属性，类型为SAMLLINT

ALTER TABLE 学生 ADD COLUMN 年龄 SMALLINT;

2.2.2在学生表中删除“年龄”属性

ALTER TABLE 学生 DROP COLUMN 年龄;

2.2.3在学生表中将“学号”属性的主键约束删除

ALTER TABLE 学生 DROP 学号 PRIMARY KEY;

2.2.4在学生表中修改“课程”属性的数据类型为“CHAR”，长度为“40”位

ALTER TABLE 学生 ALTER COLUMN 课程 CAHR(40);

2.2.5在学生表中向“学号”属性添加主键约束

ALTER TABLE 学生 DROP PRIMARY KEY;
ALTER TABLE 学生 ADD PRIMARY KEY(学号);

注意：

• “先删除原有主键，再添加一个主键”
• “当修改某一列属性的属性值长度时，修改后的长度不能小于修改前”
• “向一个属性添加非空约束或者唯一性约束时，该属性值中不能出现空值或者重复值”
• “添加一个主键时，该表不能已存在主键”

2.3表的删除

当我们创建了一张“表”之后，不想要这张“表”了，我们就可以删除它：

DROP TABLE <表名> [RESTRICT|CASCADE];

“RESTRICT”和“CASCADE”分别表示：“如果有视图存在或作为参照表时，禁止删除”，“将该表与设计的对象一起删除”

“CASCADE”的使用应该是谨慎的，因为CASCADE会将与该表相关联的表一起删除！

而使用“RESTRICT”时，如果该表有关联对象，那么就会发出提醒，不会删除！

三.索引的建立与删除

3.1索引的介绍

当我们想要从数据库中获得数据时，数据库有两种检索数据的方式：“顺序检索”、“索引检索”

“顺序检索”：需要SQL检查数据库中的每一个数据项，效率很低，但它是确定记录准确位置的唯一方法

“索引检索”：使用索引检索可以快速查找到某个数据。索引实际上是对数据分类，特征相同的属性摆放在一起，从而形成一个树状结构的文件

3.2索引的建立

建立方法：

CREATE [UNIQUE][CLUSTER] INDEX <索引名字> ON <表名> (<列名> [次序],<列名> [次序],.....)

• “UNIQUE”：“每个索引值对应唯一的数据记录”
• “CLUSTER”：“表示建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致的索引组织”

ps：一般常用“UNIQUE”

3.2.1为学生、课程和学习表建立索引

CREATE UNIQUE INDEX STU_IDX_SNO ON 学生 (学号);
CREATE UNIQUE INDEX COU_IDX_CNO ON 课程 (课程号);
CREATE UNIQUE INDEX SC_IDX_SNO_CNO ON 学习 (学号 ASC,课程号 DESC);

• “ASC”：升序排序
• “DESC”：降序排序

3.3索引的删除

删除方法：

DROP INDEX <索引名>;

3.3.1删除“STU_IDX_SNO”、“COU_IDX_CNO”、“SC_IDX_SNO_CNO”索引

DROP INDEX STU_IDX_SNO;
DROP INDEX COU_IDX_CNO;
DROP INDEX SC_IDX_SNO_CNO;

注意：

在实际使用中，想要删除某个索引，需要先进入到某个“库”中才能删除该“库”中某“表”的索引

3.4索引的使用技巧

1. 小表无需使用索引
2. 索引列中有较多不同数据项时，使用索引之后，性能会有极大提高
3. 查询返回数据较少时，使用索引辅助查询会优化速度(一般少于全部数据25%)
4. 索引会占用数据库空间
5. 不要对经常需要“更新”或“修改”的属性添加索引，会增加系统开销
6. 不要将索引与表存在一个磁盘上，可能会产生访问冲突