SQL 数据定义

目录

1 基本数据类型

1.1 数值型

类型 SQL语句 参数
整型 INTEGER/INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
定点型 DECIMAL(p, s)/NUMERIC(p, s) 有效数字共\(p\)位, 小数点后\(s\)位
浮点型 FLOAT, DOUBLE
二进制数型 FLOAT, DOUBLE \(b\)位二进制数 (\(1 \leq b \leq 64\))

1.2 日期时间型

类型 SQL语句 显示和输入格式
年类型 YEAR 'YYYY'
日期型 DATE 'YYYY-MM-DD'
时间型 TIME 'HH:MM:SS' / 'HHH:MM:SS'
日期时间型 (与时区无关) DATETIME 'YYYY-MM-DD HH:MM:SS'
时间戳型 TIMESTAMP 'YYYY-MM-DD HH:MM:SS'

1.3 字符串型

类型 SQL语句 参数
定长字符串型 CHAR(n) 最多储存\(n\)个字符 (\(n \leq 255\))
变长字符串型 VARCHAR(n) 最多储存\(n\)个字符 (\(n \leq 65535\))
文本型 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

1.4 二进制串

类型 SQL语句 参数
定长二进制串型 BINARY(n) 最多储存\(n\)个字符 (\(n \leq 255\))
变长二进制串型 VARBINARY(n) 最多储存\(n\)个字符 (\(n \leq 65535\))
二进制对象型 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

1.5 枚举型

ENUM(值列表)

枚举型的值只能取自值列表, 值列表中最多包含65535个不同的值.

例:

ENUM('Mercury', 'Venus', 'Earth', 'Mars')

1.6 集合型

SET(值列表)

集合型的值只能是值集合的子集, 如 'Mercury, Earth', 值列表中最多包含64个不同的值.

例:

SET('Mercury', 'Venus', 'Earth', 'Mars')

2 创建关系模式

2.1 创建关系模式

CREATE TABLE

定义关系模式, 包括关系名, 属性名, 属性类型, 主键, 外键, 完整性约束等.

例:

创建Student关系, 其模式如下:

属性名 属性类型 属性含义
Sno CHAR(6) 学号
Sname VARCHAR(10) 姓名
Ssex ENUM('M', 'F') 性别
Sage INT 年龄
Sdept VARCHAR(20) 所在系

SQL语句:

CREATE TABLE Student (    
    Sno CHAR(6),    
    Sname VARCHAR(10),    
    Ssex ENUM('M', 'F'),    
    Sage INT,    
    Sdept VARCHAR(20)
);

2.2 声明主键

PRIMARY KEY

声明关系的主键 (一个关系仅有一个主键), 有两种声明方法.

例:

将Sno属性声明位Student关系的主键.

  • 方法1:

    CREATE TABLE Student (    
    Sno CHAR(6) PRIMARY KEY,    
    Sname VARCHAR(10),    
    Ssex ENUM('M', 'F'),    
    Sage INT,    
    Sdept VARCHAR(20)
);

  • 方法2:

    CREATE TABLE Student (    
    Sno CHAR(6),    
    Sname VARCHAR(10),    
    Ssex ENUM('M', 'F'),    
    Sage INT,    
    Sdept VARCHAR(20)    
    PRIMARY KEY (Sno)
);

    声明多个属性构成的主键时只能用方法2.

2.3 声明外键

FOREIGN KEY

声明关系的外键 (一个关系可以有多个外键).

例:

创建SC关系, 其模式如下:

属性名 属性类型 属性含义
Sno CHAR(6) 学号
Cno CHAR(4) 课号
Grade INT 成绩
{Sno, Cno}是SC的主键, Sno是SC的外键, 参照Student关系的主键Sno. 
CREATE TABLE SC (
    Sno CHAR(6),
    Cno CHAR(4),
    Grade INT,
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student (Sno)
);

2.4 声明用户定义完整性约束

  • 规定属性值非空: NOT NULL;
  • 规定属性值不重复: UNIQUE;
  • 规定数值型属性值自动递增: AUTO_INCREMENT;
  • 定义属性的缺省值: DEFAULT 缺省值;
  • 规定属性值必须满足表达式给出的条件: CHECK (表达式)[1].

例:

CREATE TABLE Student (
    Sno CHAR(6),
    Sname VARCHAR(10) NOT NULL,
    Ssex ENUM('M', 'F') NOT NULL CHECK (Ssex IN ('M', 'F')),
    Sage INT DEFAULT 0 CHECK (Sage >= 0),
    Sdept VARCHAR(20),
    PRIMARY KEY (Sno)
);

3 删除关系模式

DROP TABLE 关系名1, 关系名2, ..., 关系名n;

删除关系时会连同关系中的数据一起删除.

4 修改关系模式

ALTER TABLE

  • 增加, 修改, 删除属性;
  • 增加, 删除约束.

例:

  • 增加属性

    在Student关系中增加属性Mno, 记录学生的班长的学号.

    ALTER TABLE Student ADD Mno CHAR(6);

  • 增加约束

    将属性Mno声明为Student关系的外键, 参照Student的主键Sno.

    ALTER TABLE Student
ADD CONSTRAINT fk_mno
FOREIGN KEY (Mno) REFERENCES Student(Sno);

  • 删除属性

    删除Student关系的Mno属性.

    ALTER TABLE Student DROP Mno;

  • 删除约束

    删除Student关系中Mno属性上的外键约束fk_mno.

    ALTER TABLE Student DROP CONSTRAINT fk_mno;

  • 修改属性定义

    将Student关系中Sname属性的类型修改为VARCHAR(20)且不重名.

    ALTER TABLE Student ALTER Sname VARCHAR(20) UNIQUE;

    MySQL使用

    ALTER TABLE Student MODIFY Sname VARCHAR(20) UNIQUE;

5 定义视图

5.1 创建视图

CREATE VIEW 视图名 [(属性名列表)] AS 子查询;

例:

为选修了3006号课的计算机系(CS)的学生建立视图, 列出学号, 姓名和成绩.

CREATE VIEW CS_Student_on_DB AS
SELECT Sno, Sname, Grade
FROM Student NATURAL JOIN SC
WHERE Sdept = 'CS' AND Cno = '3006';

MySQL对CREATE VIEW中的子查询有很多限制条件, 参考https://dev.mysql.com/doc/refman/5.5/en/create-view.html.

5.2 修改视图定义

ALTER VIEW 视图名 [(属性名列表)] AS 子查询;

例:

修改视图CS_Student_on_DB, 增加性别属性.

ALTER VIEW CS_Student_on_DB AS
SELECT Sno, Sname, Ssex, Grade
FROM Student NATURAL JOIN SC
WHERE Sdept = 'CS' AND Cno = '3006';

5.3 删除视图

DROP VIEW 视图名;

5.4 视图查询

与SQL查询语法相同.

例:

查询计算机系(CS)没有通过"Database Systems"课考试的学生.

SELECT Sno, Sname FROM CS_Student_on_DB WHERE Grade < 60;

  1. MySQL只解析CHECK, 但储存引擎并不处理. ↩︎

posted @ 2021-10-10 02:56  ZZN而已  阅读(244)  评论(0)    收藏  举报