数据库笔记（二）

写在前面：本篇博客大部分内容参考数据库系统概念（本科教学版）第三章

SQL的组成
PS：其中DDL、DML、integrity有一些补充，剩下的几项都是课本内容，应该后面会讲到。现在就先放在那边吧

数据定义语言（Data-Definition Language， DDL）
- 提供定义关系模式，删除关系以及修改关系模式的命令
- 如create、drop、alter等执行以后会产生、销毁表（定义、删除关系模式）或改变表的结构（修改关系模式）
数据操纵语言（Data-Manipulation Language，DML）
- SQL DML提供从数据库中查询信息，以及在数据库中插入元组、删除元组、修改元组的能力
- 只是对表中数据的增删改查，并没有实际影响表的结构（即关系模式并没有改变，改变的只是表中的数据）
完整性约束（integrity）
- SQL DDL 会给每一个关系（每一张表）在定义或者修改的时候定义一定的完整性约束。之后所有的操作都应该满足这些约束，不满足的操作将不被允许执行
  
  复习注释: 不过如果对违反约束的行为做了处理，有些违反约束的语句也是可以成功执行的。比如在定义外键的时候指定了级联删除，那么一旦删除语句违反了外键约束，数据库不是拒绝执行，而是级联删除相关数据
- 比如主键约束表示一个或多个字段唯一标识了一行数据，如果试图插入主键值相同的数据，就不会被允许
- 具体的下面会单独说明并举例
视图定义（view definition）
- SQL DDL 包括定义视图的命令
事务控制（transaction control）
- SQL包括定义事务的开始和结束的命令
嵌入式SQL和动态SQL（embedded SQL）
- 嵌入式和动态SQL定义SQL语句如何嵌入到通用编程语言，如C、C++、Java
授权
- SQL DDL包括定义对关系和视图的访问权限的命令

基本类型
这些基本数据类型不依赖于某一个具体的数据库，是SQL数据库通用的类型

char(n)——全称character
- 用户指定长度的字符串，所有取值的长度都固定为长度为n的char
- 比如身份证或者电话号码等长度固定的数据可以使用这个
- 一般实际用的较少
varchar(n)——全称character varying
- 可变长的字符串，n为串的最大允许长度
- 实际存的时候数据多长，就存多长，较省空间
- 实际应用中大多用varchar，而不是char
int——integer
- 整数类型，没啥好讲的，Oj8k
smallint
- 小整数类型

numeric(p, d)

定点数，这个数有p位数字，其中小数点右边有d位
例如，numberic(3, 1)可表示-99.9 ~ 99.9

下面在MySQL数据库上面的测试，举个栗子：(测试结果表明，如果定义numberic(3, 1), 则小数点前面有三位数时是无法插入的，如果小数点后面有多位小数，则通过四舍五入的方法只保留一位小数)

先简单定义一张Student表（没有定义主键，允许插入相同的数据）

-- 下面定义了一张学生表，其中grade的取值范围为-99.9 ~ 99.9（不合理的取值，只是为了测试）
CREATE TABLE Student (
  study_id VARCHAR(20) NOT NULL,
  age INTEGER DEFAULT 0,
  grade NUMERIC(3, 1)
);

执行正常的插入操作


-- 下面两条插入语句都能正常执行，并且结果正确
INSERT INTO Student(study_id, age, grade) VALUES (
  '201590001', 18, 92.5
)

INSERT INTO Student(study_id, age, grade) VALUES (
  '201590002', 18, -92.5
)

-- 执行下面一条插入操作，最后保存的记录是 -92.6，
INSERT INTO Student(study_id, age, grade) VALUES (
  '201590003', 18, -92.56
)

-- 执行下面一条插入操作，最后保存的记录是 -92.5，
INSERT INTO Student(study_id, age, grade) VALUES (
  '201590004', 18, -92.54
)

-- 下面的这条插入操作将会报错
--  [22001][1264] Data truncation: Out of range value for column 'grade' at row 1
INSERT INTO Student(study_id, age, grade) VALUES (
  '201590005', 18, 192.5
)

执行上面操作后的结果

study_id	age	grade
201590001	18	92.5
201590002	18	-92.5
201590003	18	-92.6
201590004	18	-92.5

real,double,precision
- 浮点数与双精度浮点数
float(n)
- 精度至少为n位的浮点数
Date, time, timestamp, interval
- 日期类型

SQL中的完整性约束
下文中列举的只是部分简单约束，具体的会在后面的内容中讨论

先来简单聊一聊我对完整性约束的理解吧，其实数据库中的完整性约束讲起来就是数据库在定义一张表的时候，对其作出了一系列的约束（这些个约束可能影响一个或多个字段，甚至影响整个表结构）。如果后续的操作中（不管是DDL还是很DML），如果违背了之前定义的约束，（如果没有对违反约束行为做特殊的处理）这个操作就不会成功

primary key（A1, A2, …, An）——主键约束

该约束主要指定了以属性A1~An构成关系的主键，该主键值必须非空且唯一
即只要一个表的主键确定，那这个表中就不允许存在一行数据其主键的值为null，也不允许存在两行数据其主键的值一样。一旦后续操作违反了这个原则（约束），则该操作就不会被执行

举个栗子：

新定义一张department表

-- 下面的操作创建了一张department表，并指定dept_name为主键
CREATE TABLE department (
  dept_name VARCHAR(20),
  building  VARCHAR(15),
  budget    NUMERIC(12, 2),
  PRIMARY KEY (dept_name)
);

下面顺序执行一系列插入操作

-- 下面的操作将成功插入一条记录
INSERT INTO department (dept_name, building, budget)
VALUES ('Software', 'A', 100000);

-- 下面再执行一次与上面一模一样的操作，会发现报错，因为之前已经有一条
-- 记录的主键值为'Software'，顾无法再插入主键值相同的数据
-- [23000][1062] Duplicate entry 'Software' for key 'PRIMARY'
INSERT INTO department (dept_name, building, budget)
VALUES ('Software', 'A', 100000);

-- 下面的语句试图插入一条dept_name为空的数据，显然也是报错的
-- 因为作为主键的字段是不允许为空的
-- [23000][1048] Column 'dept_name' cannot be null
INSERT INTO department (dept_name, building, budget) VALUES (NULL , 'A', 100000);

执行上面操作后的结果

dept_name	building	budget
Software	A	100000

foregin key(A1, A2, …, An) references —— 外键约束

简单来讲如果一个关系（表）A中包含一个外键C，则作为外键的一个或多个字段必然是另一张表B的主键。则由于外键约束这一层关系，对A表和B表都会收一定程度的限制（现在解释的还是比较抽象，看下面的例子会比较直观一点）
- 如果想向A表中插入一条数据，且数据中C的值在B表中找不到对应表项，则这个操作不会成功（就像你如果向运动员表中插入一条数据，国家属性对应的标识在国家表中不存在，则这次插入操作就会失败）
- 如果试图删除一个表B的记录，而这条记录的主键只正好是A表中某条数据的外键，则这次删除操作不会成功

举个栗子：

先创建两张表

-- 创建一张表department，并制定dept_name为主键
CREATE TABLE department (
  dept_name VARCHAR(20),
  building  VARCHAR(15),
  budget    NUMERIC(12, 2),
  PRIMARY KEY (dept_name)
);

-- 再创建一张couese表，并指定dept_name为本表关于department表的外键
CREATE TABLE course (
  course_id VARCHAR(7) NOT NULL,
  title     VARCHAR(50),
  dept_name VARCHAR(20),
  credits   NUMERIC(2, 0),
  PRIMARY KEY (course_id),
  FOREIGN KEY (dept_name) REFERENCES department (dept_name)
);

我们先试图插入一条course记录

-- 下面试图往course表中插入一条记录，但是department表中并没有
-- dept_name = 'DUT'的记录，故该操作会失败
INSERT INTO course (course_id, title, dept_name, credits)
VALUES ('08', 'English', 'DUT', 3)

我们先往department表中插入记录，再往course表中插入记录，下面的操作就能成功插入


INSERT INTO department (dept_name, building, budget) VALUES ('DUT' , 'A', 100000);

INSERT INTO course (course_id, title, dept_name, credits)
VALUES ('08', 'English', 'DUT', 3)

执行上面操作后的结果

dept_name	building	budget
DUT	A	100000

course_id	title	dept_name	credits
08	Endlish	DUT	3

* 此时我们如果试图删除department表中dept_name = DUT的那条数据
  1
2
3
-- 执行下面的操作就会报错，因为course表中还有一项数据的dept_name的值为DUT，如果删除掉department中的这条数据，就会破坏其完整性，顾执行失败
-- 23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`db_study`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`))
DELETE FROM department WHERE dept_name = 'DUT';


* 同样的如果，我们试图删除department表，则只要course表中还有数据，这个操作就会失败

not null——非空约束
- 理解上面两个约束以后这个就很好理解了，这个约束就是指定某个字段不能为空，试图传入null值的操作都不会成功
- 作为主键的字段隐式拥有非空约束

打赏
如果你觉得本篇博文写的还过得去，而且乐意随手打赏，将是对博主最大的鼓励～

SQL的组成

数据定义语言（Data-Definition Language， DDL）

数据操纵语言（Data-Manipulation Language，DML）

完整性约束（integrity）

视图定义（view definition）

事务控制（transaction control）

嵌入式SQL和动态SQL（embedded SQL）

授权

基本类型

char(n)——全称character

varchar(n)——全称character varying

int——integer

smallint

numeric(p, d)

real,double,precision

float(n)

Date, time, timestamp, interval

SQL中的完整性约束

primary key（A1, A2, …, An）——主键约束

foregin key(A1, A2, …, An) references —— 外键约束

not null——非空约束

打赏