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个不同的值.

例:

sql复制

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

1.6集合型

SET(值列表)

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

例:

sql复制

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语句:

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:

  sql复制

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

• 方法2:

  sql复制

  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.

sql复制

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 (表达式)¹.

例:

sql复制

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,记录学生的班长的学号.

  sql复制

  ALTER TABLE Student ADD Mno CHAR(6);

• 增加约束

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

  sql复制

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

• 删除属性

  删除Student关系的Mno属性.

  sql复制

  ALTER TABLE Student DROP Mno;

• 删除约束

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

  sql复制

  ALTER TABLE Student DROP CONSTRAINT fk_mno;

• 修改属性定义

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

  sql复制

  ALTER TABLE Student ALTER Sname VARCHAR(20) UNIQUE;

  MySQL使用

  sql复制

  ALTER TABLE Student MODIFY Sname VARCHAR(20) UNIQUE;

5定义视图

5.1创建视图

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

例:

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

sql复制

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,增加性别属性.

sql复制

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”课考试的学生.

sql复制

SELECT Sno, Sname FROM CS_Student_on_DB WHERE Grade < 60;

Footnotes

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