BRabbit's Blog

This blog has super rabbit power!

发表于 更新于 分类于

数据查询(连接查询)

  • 不像关系代数中”连接”是用一种特殊符号来表达的,在SQL中”连接”是用”连接条件”来表达的.

连接条件或连接谓词: 用来连接两个表的条件
一般格式: [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

连接字段: 连接谓词中的列名称
连接条件中的各连接字段类型必须是可比的, 但名字不同.

等值与非等值连接查询

  • 等值连接 : 连接运算符为 ‘=’

例子-1:

1
2
3
4
#查询每个学生及其选修课程的情况
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student,Sno = SC.Sno;

上述查询将把两个表的字段首尾相接同时显示出来(类似关系代数中的自然连接).
与关系代数中的自然连接不同的是,遇到不同表中拥有相同字段时,SQL将其都显示出来.
例如Student.Sno / SC.Sno
上述查询语句的结果为:
avatar
若想要实现真正的自然连接,则在SELECT中显式指定要列出的字段即可:

1
2
3
4
#查询每个学生及其选修课程的情况
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student,Sno = SC.Sno;

例子-2:

1
2
3
4
5
#查询选修2号课程且成绩咋90以上的所有学生的学号和姓名.
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND    #连接谓词
      SC.Cno='2' AND SC.Grade>90; #选择谓词

可见,一条SQL语句可以同时完成选择和连接查询, 这时WHERE子句是由选择谓词和连接谓词组成的复合条件.

连接操作的执行过程

  1. 嵌套循环法 (NESTED-LOOP)
    • 先在表1中找到第1个元组, 再从头开始扫描表2, 逐一找到满足连接的元组.
      找到后将表1的第1个元组与该元组拼接起来, 形成结果表中的一个元组.
    • 表2全部查找完后, 再找表1中第2个元组. 从头开始扫描表2, 逐一找到满足连接的元组.
      找到后将表1中第2个元组与该元组拼接起来, 形成结果表中的一个元组.
    • 重复上述操作,直到表1中的全部元组都处理完毕.
  2. 排序合并法 (SORT-MERGE)
    • 常用于等值连接
    • 首先按连接属性对表1表2排序.
    • 设置指针, 分别指向表1和表2的第一个元组.
      如果这两个元组满足连接条件, 则进行元组连接(若有多个连接元组满足条件,则一一拼接)
      , 并将指针分别后移一个元组. 否则, 将具有较小值的指针后移一个元组.
    • 重复上述操作, 直到表1或表2的全部元组都处理完毕位为止.
  3. 索引连接 (INDEX-JOIN)
    • 对表2按连接字段建立索引
    • 对表1的每个元组, 一次根据其连接字段值查询表2的索引, 从中找到满足条件的元组.
      找到后就将表1的第1个元组与该元组拼接起来, 形成结果表中的一个元组.
    • (该方法可以视作嵌套循环法的一个变种)

自身连接

自身连接即将一个表于其自身进行连接,是一种特殊的连接.

  • 需要给表起别名以示区别
  • 由于所有属性名都是同名属性, 因此必须使用别名前缀

例子:

1
2
3
4
#查询每一门课的直接先修课程的名称
SELECT FIRST.Cname, SECOND.Cname
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno - SECOND.Cno;

假设Course表的内容如下:
avatar
则上例查询结果如下:
avatar

外连接

外连接与普通连接的区别:

  • 普通连接操作只输出满足连接条件的元组
  • 外连接操作指定表为连接主体, 将主体表中不满足连接条件的元组一并输出
  • 左外连接: 列出左边关系中所有的元组
  • 右外连接: 列出右边关系中所有的元组

例子:

1
2
3
4
5
6
7
8
#查询每个学生及其选修课程的情况
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student,Sno = SC.Sno;
#改写上例,将其用外连接实现
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUT JOIN SC #LEFT OUT JOIN可以简写为LEFT JOIN
ON (Student.Sno = SC.Sno);

上述例子中外连接实现的查询结果(蓝色部分是与等值连接不同的地方):
avatar

多表连接

多表连接: 两个以上的表进行连接

例子

1
2
3
4
#查询每个学生的学号, 姓名, 选修的课程名 以及 成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course #多表连接
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

发表于 更新于 分类于

数据查询（单表查询）

语句格式

SELECT [ALL | DEISTINCT] <目标列表达式>[, <目标列表达式>]...
FROM <表名或视图名>[, <表名或视图名>]...|(SELECT 语句) [AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
SELECT: 要查询的内容 (选定列) (ALL不去重(缺省) / DEISTINCT去重) ;
FROM: 从哪个表查询 (选定表) ;
WHERE: 查询的约束条件 (选定行) (条件表达式里面不能使用聚集函数) ;
——前三者为一组,用于查询出结果集——
GROUP BY: 结果集的分组方式 (按照某列值分组,相同值为一组统计数量) ;
HAVING: 只输出满足条件的结果 ;
——中间二者为一组,用于对结果集的输出限制,通常伴随使用聚集函数——
ORDER BY: 结果集的排序方式 (ASC升序(缺省) / DESC降序) .
——最后一条为一组,用于对结果排序——

功能分解

avatar

SELECT FROM WERE组合 (查询出结果集)

SELECT: 要查询的内容 (选定列) (ALL不去重(缺省) / DEISTINCT去重) ;
FROM: 从哪个表查询 (选定表) ;
WHERE: 查询的约束条件 (选定行) ;

代码示例(简单查询-灵活调整目标表达式):

1
2
3
4
5
6
7
8
9
10
11
12
#查询全体学生的学号与性名
SELECT Sno, Sname FROM Student;
#查询全部列(将<目标表达式指定为*即表示指定所有列>)
SELECT * FROM Student;
#查询全体学生的姓名及其出生年份(<目标表达式>可以是简单的计算表达式)
SELECT * FROM Sname, 2020-Sage FROM Student;
#查询签体学生的姓名,出生年份,院系(院系用小写)(<目标表达式>可以是字符串或使用函数)
SELECT Sname, 'Birth:', 2020-Sage, LOWER(Sdept) FROM Student; #这里Birth单独做一个列,每列值都是Birth:
#同上,使用别名改变查询结构的列标题(在<目标表达式后可以加上自定义结果集的列名>)
SELECT Sname NAME, 'Birth:' BIRTH, 2020-Sage BIRTHDAT, LOWER(Sdept) DEPARTMENT;
#查询先修了课程的学生学号(去重)
SELECT DISTINCT Sno FROM SC;

聚集函数(对结果集做一些计算)

  • 统计元组个数 COUNT(*)
  • 统计一列中值的个数 COUNT ( [DISTINCT | ALL] <列名> )
  • 计算一列值的总和(此列值必须是数值型) SUM ( [DISTINCT | ALL] <列名> )
  • 计算一列值的平均值(此列值必须是数值型) AVG ( [DISTINCT | ALL] <列名> )
  • 求一列中的最大值/最小值 MAX ( [DISTINCT | ALL] <列名> ) / MIN ( [DISTINCT | ALL] <列名> )

代码示例:

1
2
3
4
5
6
7
8
9
10
#查询学生总人数
SELECT COUNT(*) FROM Student;
#查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM SC; #DISTINCT要写在括号里面
#计算1号课程的学生平均成绩
SELECT AVG(Grade) FROM SC WHERE Cno = '1';
#查询选修1号课程学生的最高分
SELECT MAX(Grade) FROM SC WHERE Cno = '1';
#查询学生201215012选修课程的总学分数
SELECT SUM(Ccredit) FROM SC, Course WHERE Sno = '201215012' AND SC.Cno = Course.Cno; #涉及多表查询

常用的查询条件 (<条件表达式>中用到的运算符)

查询条件 谓词
比较 =, >, <, >=, <=, !=, <>, !>, !<
确定范围 BETEWEEN AND, NOT BETWEEN AND
确定集合 IN, NOT IN
字符匹配 LIKE, NOT LIKE (通配符: _ % ESCAPE(一个字符,任意个字符,转义字符))
空值 IS NULL, IS NOT NULL
多重条件(逻辑运算) AND, OR, NOT

代码示例(运算符的使用-灵活调整条件表达式):

  • 比较 =等于, >大于, <小于, >=大于等于, <=小于等于, != <>不等于, !>不大于, !<不小于
    1
    2
    3
    4
    5
    6
    #查询计算机系全体学生名单
    SELECT Sname FROM Student WHERE Sdept='CS';
    #查询所有年龄在20以下的学生姓名及其年龄
    SELECT Sname, Sage FROM Student WHERE Sage < 20;
    #查询考试成绩不及格的所有学生学号
    SELECT DISTINCT Sno FROM SC WHERE Grade NOT > 60;
  • 确定范围 BETWEEN ... AND ... / NOT BETWEEN ... AND ...
    1
    2
    3
    4
    #查询年龄在20到23之间的学生姓名,院系,年龄(也包含20与23)
    SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
    #查询年龄不在20到23之间的学生姓名,院系,年龄(也排除20与23)
    SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
  • 确定集合 IN <值表> / NOT IN <值表>
    1
    2
    3
    4
    #查询计算机系,数学系,信息系学生的姓名与性别
    SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS', 'MA', 'IS');
    #查询非计算机系,数学系,信息系学生的姓名与性别
    SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('CS', 'MA', 'IS');
  • 字符匹配 [NOT] LIKE '<匹配串>' [ESCAPE '<转换码字符>']
    1
    2
    3
    4
    5
    6
    7
    8
    #查询姓刘的学生的姓名,学号,性别
    SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%';#%代表任意数量字符,也包括0个
    #查询姓刘且全名是三个字的学生的姓名,学号,性别
    SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘____';#这里有四个_,代表2个汉字字符(1个汉字2个字符)
    #查询不姓刘的学生的姓名,学号,性别
    SELECT Sname, Sno, Ssex FROM Student WHERE Sname NOT LIKE '刘%';
    #查询DB_Design课程的课程号和学分(ESCAPE '\'表示\字符之后的一个字符不做转换,除了\也可以换成其他字符)
    SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Desgin' ESCAPE '\';
  • 涉及空值的查询 IS NULL / IS NOT NULL
    1
    2
    #查询缺少成绩的学生的学号和相应的课程号
    SELECT Sno, Cno FROM SC WHERE Grade IS NULL; #IS不能用=代替
  • 多重条件查询 AND / OR (AND的优先级高于OR, 可以用括号改变优先级)
    1
    2
    #查询计算机系年龄在20以下的学生姓名
    SELECT Sname FROM Student WHERE Sdept = 'CS' AND Sage < 20;

GROUP BY HAVING组合 (对结果集的输出限制,通常伴随使用聚集函数)

GROUP BY: 结果集的分组方式 (按照某列值分组,相同值为一组统计数量) ;
HAVING: 只输出满足条件的结果 ;

该分组虚化聚集函数的作用对象:

  • 如果未对查询结果分组,聚集函数将作用与整个查询结果
  • 对结果集分组后,聚集函数将分别作用于每个组
  • 按照指定的一列或多列值分组,值相等的为一组

代码示例:

1
2
3
4
5
6
#求每个课程号及相应的选课人数
SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno; #可以这么理解:先按照Cno分组,再对每个分组COUNT(Sno)
#查询选修了3门以上课程的学生学号
SELECT Cno FROM SC GROUP BY Sno HAVING COUNT(*) > 3;
#查询平均成绩大于等于90的学生学号和平均成绩
SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) > 90;

HAVING短语和WHERE子句的区别:

  • 作用对象不同
  • WHERE子句作用于基表或视图,从中选择满足条条件的的元组
  • HAVING短语作用于组,从中选择满足条件的组.

ORDER BY组合 (对结果的排序)

ORDER BY: 结果集的排序方式 (ASC升序(缺省) / DESC降序).
对于空值的排序,由系统具体的实现决定.

代码示例:

1
2
3
4
#查询选修3号课程的学生的学号,成绩. 结果按照分数降序排序
SELECT Sno, Grade FROM SC WHERE Cno = '3' ORDER BY Grade DESC;
#查询全体学生情况,按照院系号升序,同系按年龄降序排列
SELECT * FROM Student ORDER BY Sdept ASC, Sage DESC; #ASC缺省可省略

综合练习

1
2
3
4
5
6
7
8
9
10
#列出计算机系姓刘的同学的信息,按照学号大小排序
SELECT *
FROM Student
WHERE Sdept='CS' AND Sname LIKE '刘%'
ORDER BY Sno ASC;
#按系并区分男女统计各系学生的人数,并按照人数降序排序
SELECT Sdept 院系, Ssex 性别, COUNT(Sno) 学生人数
FROM Student
GROUP BY Sdept, Ssex
ORDER BY COUNT(Sno) DESC;

发表于 更新于 分类于

SQL——结构化查询语言

SQL特点：

  1. 综合统一：可以独立完成数据库生命周期中的全部活动
  2. 高度非过程化：主要提出”做什么”，无需了解具体过程
  3. 面向集合的操作方式：操作对象是集合，结果也是集合
  4. 多种使用方式：既是独立语言又是嵌入语言

SQL的动词

SQL功能 动词
数据查询 SELECT
数据定义 CREATE, DEOP, ALTER
数据操纵 INSERT, UPDATE, DELETE
数据控制 GRANT, REVOKE

数据定义

SQL的数据定义功能：定义各种数据库的”对象”

  • 模式定义
  • 表定义
  • 视图定义
  • 索引定义

SQL的数据定义语句

操作对象 操作方式 操作方式 操作方式
创建 删除 修改
模式 CREATE SCHEMA DEOP SCHEMA
表 CREATE TABLE DROP TABLE ALTER TABLE
视图 CREATE VIEW DROP VIEW
索引 CREATE INDEX DROP INDEX ALTER INDEX

数据字典
系统内部的一组系统表，记录数据库中所有对象的定义信息及一些统计信息：

  • 关系模式、表、视图索引的定义
  • 完整性约束的定义
  • 各类用户对数据库的操作权限
  • 统计信息等

关系数据库管理系统在执行SQL时，实际上就是在更新数据字典表中的信息。

模式定义

定义模式
定义模式实际上定义了一个”命名空间”(或者说一个目录)。
在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。
在CREATE SCHEMA中可以接收CREATE TABLE,CREATE VIEW和GRANT子句。
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [表定义|视图定义|授权定义]

1
2
3
4
5
6
7
8
9
10
11
#为用户Wang定义一个模式Test (MySQL)
CREATE SCHEMA Test AUTHORIZATION Wang;
#模式名的缺省值是用户名同名
CREATE SCHEMA AUTHORIZATION Wang;
#为用户Zhang创建Test模式，并在其中定义一个表格Tab_1
CREATE SCHEMA Test AUTHORIZATION Zhang
CREATE TABLE Tab_1 (
  Col_1 SMALLINT,
  Col_2 INT,
  Col_3 CHAR(20)
);

删除模式
删除模式有两种方式：CASCADE(级联)、RESTRICT(限制)。
前者将删除模式中所有的对象，后者只有在模式内为空时才允许删除。
DROP SCHEMA <模式名> <CASCADE|RESTRICT>

1
2
#删除模式Test及其内部所有对象
DROP SCHEMA Test CASCADE;

基本表定义

定义基本表
CREATE TABLE <表名> (
<列名> <数据类型> [<列级完整性约束条件>]
[,<列名> <数据类型> [<列级完整性约束条件>]]
...
[,<表级完整性约束条件>]);
列级完整性约束条件：涉及相应属性列的完整性约束条件。
表级完整性约束条件：涉及一个或多个属性列的完整性约束条件。
（如果完整性约束条件涉及多个属性列，则必须定义在表级）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#建立“学生”表Student，学号是主键，姓名取值唯一。
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,  #主键PRIMARY KEY
  Sname CHAR(20) UNIQUE,    #唯一UNIQUE
  Ssex CHAR(2),
  Sage SMALLINT,
  Sdept CHAR(20)
);
#建立“课程”表Course
CREATE TABLE Course (
  Cno CHAR(4) PRIMARY KEY,
  Cname CHAR(40),
  Cpno CHAR(4),
  Ccredit SMALLINT,
  FOREIGN KEY (Cpno) REFERENCES Course(Cno)
  #FOREIGN KEY外键 REFERENCES参照对象
);
#建立学生选课表SC
CREATE TABLE SC (
  Sno CHAR(9),
  Cno CHAR(4),
  Grade SMALLINT,
  PRIMARY KEY(Sno, Cno),
  FOREIGN KEY (Sno) REFERENCES Student(Sno),
  FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

数据类型：关系模型中“域”的概念。
MySQL中的数据类型

修改基本表
删除列/完整性约束时，同样有两种方式CASADE、RESTRICT。
前者将连带相关的对象都删除，后者只有目标对象不被引用才允许删除。
ALTER TABLE <表名>选择表
[ADD [COLUMN] <新列名> <数据类型> [完整性约束]]增加新列
[ADD <表级完整性约束]增加表级完整性约束
[DROP [COLUMN] <列名> [CASADE|RESTRICT]]删除列
[DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]删除完整性约束
[ALTER COLUMN <列名> <数据类型>]修改列名/数据类型

1
2
3
4
5
6
#向Student中增加“入学时间”，日期类型。
ALTER TABLE Student ADD S_entrance DATE;
#将年龄的数据类型有字符改为整数
ALTER TABLE Student ALTER COLUMN Sage INT;
#新增课程名必须取唯一值的约束条件
ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表
删除基本表时，同样有两种方式CASADE、RESTRICT。
前者将连带相关的对象都删除，后者只有目标对象不被引用才允许删除。
DROP TABLE <表名> [RESTRICT|CASCADE];

1
2
#删除Student表及其相关对象
DROP TABLE Student CASCADE;

索引定义

建立索引的目的： 加快查询的速度

关系数据库管理系统常见的索引：

  • 顺序文件上的索引
  • B+树索引
  • 散列(HASH)索引
  • 位图索引

特点：

  • B+树索引具有动态平衡的优点
  • HASA索引具有查找速度快的特点

建立索引的权限： 数据库管理员或表的属主(建表人)
维护和使用索引： 系统自动维护、选择，用户不能显式地选择索引

建立索引
创建索引有两个选项：UNIQUE、CLUSTER。
前者表示每索引值只对应唯一的数据记录，后者表示要建立的索引是聚簇索引。
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名>[<次序>][,<列名>[<次序>]]...);

  • <表名>：建立索引的基本表的名字
  • 索引：可以建立在该表的一列或多列上，各列名之间用逗号隔开
  • <次序>：指定索引的排列次序，升序(缺省)ASC、降序DESC
  • UNIQUE：此索引的每一个索引值只对应唯一的数据记录
  • CLUSTER：表示要建立的索引是聚簇索引
    1
    2
    3
    4
    5
    6
    7
    #为Student Course SC三个表建立索引：
    #Student表按学号升序建立唯一索引，
    #Course表按照课程号升序建立唯一索引，
    #SC表按学号升序和课程号降序建立唯一索引。
    CREATE UNIQUE INDEX Stusno ON Student(Sno);
    CREATE UNIQUE INDEX Coucno ON Course(Cno);
    CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);

修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>;

1
2
#将SC表的SCno索引改名为SCSno
ALTER INDEX SCno RENAME TO SCSno;

删除索引
DROP INDEX <索引名>;

1
2
#删除Student表中的Stusname索引
DROP INDEX Stusname;

发表于 更新于 分类于

关系模型

关系数据结构

域： 一组具有相同数据类型的值的集合。
比如整数、性别。

笛卡尔积： 一组域中，所有域的所有取值的任意组合的一个空间。
笛卡尔积可以看作关系的“域”。
例如D1域有2个值，D2域有2个值，D3域有3个值，则笛卡尔积为2X2X3=12

关系： 笛卡尔积的子集叫做域上的关系，表示为：R(D1, D2,…,Dn)
R:关系名 n:关系的目(度)
元组： 关系中每一个元素(d1,d2…dn)称为一个元组，用t表示。（一行）
属性： 关系中不同列可以对应相同的域，每个列都是一个属性。（一列）
码： 就是键

分量必须去原子值：（也就是每格都要是不能再拆分的状态）

关系模式： 关系模式是型，关系是值。（表的结构）
关系模式可以形式化的表示为：R(U,D,DOM,F)
R关系名，U属性名集合,D域，DOM属性向域的映像集合,F属性间数据的依赖
简记为：R(U)或R(A1,A2,A3)

关系完整性约束

  1. 实体完整性
  2. 参照完整性
  3. 用户定义的完整性

前二者是关系模型必须满足的约束条件，也被称为关系的不变性。（系统支持）
第三者是应用领域中的语义约束。（用户支持）

实体完整性 关系的主属性不能取空值（主键不能为空）
参照完整性 关系与关系之间的引用（外键是其他表的主键）

参照完整性规则：
若属性F是基本关系R的外码，他与基本关系S的主码Ks相对应，
则对于R中每个元组在F上的值必须为：

  • 或者取空值
  • 或者等于S中某个元组的主码值

用户定义完整性 针对具体关系数据库的约束条件（属性的取值范围）

关系代数

关系代数是一种抽象的查询语言，它用对关系的运算来表达查询。

  • 运算对象是关系
  • 运算结果也是关系
  • 运算符有两类：集合运算符、关系运算符

一些符号：
R：关系模式R(A1,A2..An)的一个关系
t∈R：t是R的一个元组
A：属性组
t[Ai]：t中相应于属性Ai的一个分量
－A：除去A中属性后剩余的属性组（补集）
tr ^ ts：元组的连接（首尾相接）
Zx：一个属性为x的集合里，z元素的集合

传统集合操作

集合运算符 ∪ − ∩ ×
含义 并 差 交 笛卡尔积

并运算： 目数相同相应属性取自同一个域，元组叠加。
差运算： 目数相同相应属性取自同一个域，元组相减。
交运算： 目数相同相应属性取自同一个域，保留相同元组。
笛卡尔积： 二者属性叠加，二者元组随机排列不重复。

关系特有操作

关系运算符 σ π ⋈ ÷
含义 选择 投影 链接 除

选择： 从关系R中选取表达式F值为真的元组。
投影： 从关系R中选取部分属性。
等值链接： 选取两个关系的笛卡尔积中满足条件的元组。
自然链接： 选取两个关系的笛卡尔积中重复属性相同的元组。
外链接： 选取两个关系的自然连接，不丢弃属性和元组，空位填NULL。
左外链接： 类似外连接，不丢弃左操作数属性和元组，空位填NULL。
右外链接： 类似外连接，不丢弃右操作数属性和元组，空位填NULL。
除： R中与S不同属性的值，像集覆盖S中于R同属性者，组成关系。

关系演算

关系演算是以数理逻辑中的谓词演算为基础，按照谓词变元不同，可分为：

  1. 元组关系演算：
    以元组变量为谓词变元的基本对象，元组关系演算语言ALPHA
  2. 域关系演算：
    以域变量作为谓词变元的基本对象，域关系演算语言QBE

元组关系演算 ALPHA

语句格式：
GET 工作空间名(表达式1)[:条件][DOWN|UP 表达式2]
**表达式1:**指定语句的操作对象
关系名|关系名.属性名|元组变量.属性名|集函数[, ...]
**条件:**结果元组应该满足的条件
逻辑表达式
**表达式2:**指定排序方式
关系名.属性名|元组变量.属性名[, ...]

1
2
3
4
5
6
7
8
9
10
#例1:查询所有选修课程号码:
GET W (SC.Cno)
#例2:查询所有学生的数据
GET W (Student)
#例3:查询IS系年龄小于20的学生的学号和年龄
GET W (Std.Sno, Std.Sage):Std.Sdept='IS'^Std.Sage<20
#例4:查询CS系学生的学号和年龄,结果按年龄降序
GET W (Std.Sno, Std.Sage):Std.Sdept='CS'DOWN Std.Sage
#例5:查询IS系年龄最大三个人下名字
GET W(3) (Std.Sname):Std.Sdept='IS'DOWN Std.Sage

元组变量的含义： 表示可以在某一关系变化范围内变化。
元组变量的用途： 简化关系名，使用量词时必须用元组变量。
定义元组变量：RANGE 关系名 变量名

域关系演算 QBE

QBE操作框架：

关系名 属性名 属性名 属性名
操作命令 元组属性值/查询条件/操作命令 同左 同左

发表于 更新于 分类于

数据库系统概述

数据库的基本概念

  • 数据(Data)——描述事物的符号记录
  • 数据库(Database, DB)——
  • 数据库管理系统(Database Management System, DBMS)——
  • 数据库系统(Database System, DBS)——
  1. 数据
    语义：数据于其语义是不可分的（比如”性别男，”男”和”性别”是不可分的）
    解释：针对数据的语义用自然语言描述（某人的性别是男性）
    结构：”记录”是计算机存储数据的一种格式/方法
  2. 数据库
    定义：数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合。
    用处：收集并抽取出一个应用所需要的大量有用数据，将其保存，以供京一部加工处理。
    特征：按一定数据模型组织、描述、储存；可为各种用户共享、冗余度小、易扩展；数据独立性高。
  3. 数据库管理系统
    定义：位于应用程序与操作系统之间的一层数据管理软件。是大型复杂的软件系统。
    用途：科学地组值和存储数据、高效地获取和维护数据。
    功能：数据定义；数据管理；数据操纵；数据库管理等。
  4. 数据库系统
    定义：在计算机系统中引入数据库后的系统构成（常常将其简称为数据库）
    构成：数据库、数据库管理系统、应用程序、数据库管理员

数据管理技术的产生和发展

什么是数据管理?

  • 对数据进行分类、组织、编码、存储、检索、维护。
  • 数据处理和数据分析的中心问题。

数据管理技术的发展过程：

  • 人工管理阶段（20世纪50年代之前）
  • 文件系统阶段（20世纪50年代末——60年代中）
  • 数据库系统阶段（20世纪60年代末——现在）

数据库系统的特点

  • 数据结构化
  • 数据的共享性高，冗余度滴且易扩充
  • 数据独立性高
  • 数据由数据库管理系统统一管理和控制

数据库管理系统提供的数据控制功能

1.数据的安全性保护(Security)
2.数据的完整性检查(Integrity)
3.并发控制(Concurrency Control)
4.数据库恢复(Recovery)

数据模型

数据模型是对现实世界数据特征的抽象——现实世界的模拟。

数据模型应满足三方面要求：

  1. 能比较真实的模拟现实世界；
  2. 容易为人所理解；
  3. 便于在计算机上实现。

数据模型是数据库系统的核心和基础。

数据模型的两类（两个层次）

  1. 概念模型/信息模型：
    按照用户的观点来对数据的信息建模，用于数据库设计。
  2. 逻辑模型和物理模型：
    逻辑模型：按照计算机系统的观点对数据建模，用于DBMS实现。
    物理模型：描述数据在系统内（磁盘上）的表示方式和存取方法。

先将现实世界抽象为概念模型，再将概念模型转换为逻辑模型，再把逻辑模型转换为物理模型。
第一步由数据库设计人员完成，第二步设计人员借助工具完成，第三步由DBMS自动完成。

概念模型
概念模型用于信息世界的建模，是现实世界到机器的中间层次，也是数据库设计人员和用户之间交流的语言。

基本概念：

  1. 实体(Entity)（用正方形画）
    可关存在并可以相互区别的事务。
  2. 属性(Attribute)（用椭圆画）
    实体所具有的某一特性称为属性，一个实体可以由若干个属性来刻画。
  3. 键(key)
    唯一标识实体的属性集称为码。
  4. 实体型(Entity type)
    实体名及其属性名集合来抽象和刻画同类实体称为实体型。
  5. 实体集(Entity Set)
    同一类型实体的集合称为实体集。
  6. 联系(Relationship)（用菱形画，直线和实体相连）
    事物内部以及事物之间的联系被反应为实体(型)的联系。
    实体内部的联系：各个属性之间的联系。
    实体之间的联系：不同实体之间的联系。
    联系的类型：一对一、一对多、多对多。

概念模型的表示方法（E-R图）：
avatar

数据模型的组成

  1. 数据结构——描述系统的静态特性
  2. 数据操作——描述系统的动态特性
  3. 完整性约束

数据结构： 描述对象之间的关系，描述数据库的组成对象。
数据操作： 对数据库中各种对象的实例允许指向的操作的集合。
完整性约束： 一种完整性规则的集合（数据及其联系所具有的制约和依存规则）

数据系统的结构

从数据库应用开发人员的角度看
数据库采用三级模式结构，是数据库系统内部的系统结构。

从最终用户角度看

  1. 单用户结构
  2. 主从式结构
  3. 分布式结构
  4. 客户-服务器

数据库系统的三级模式结构

模式： 对数据库逻辑结构和特征的描述。是型的描述，不涉及具体值。模式是相对稳定的。
实例： 数据库某一时刻的状态——模式的一个具体值。
（比如说学生选课情况的模式，每一年的选课情况是实例。）

三级模式包括：外模式、模式、内模式。其中也包括外模式——模式映射、模式——内模式映射。
avatar

模式（逻辑模式）

  • 数据库中全体数据的逻辑结构和特征的描述
  • 所有用户的公共数据视图

一般来说，一个应用的数据库有一个模式。
模式是数据库系统模式结构的中心，与数据的无聊存储细节和硬件无关，与具体应用和开发工具无关。
（用SQL语言定义）

外模式（用户模式）

  • 数据库用户使用的局部数据逻辑结构和特征描述
  • 数据库用户的数据视图，是与某一应用有关的数据逻辑表示。

外模式与模式： 外模式通常是模式的子集，一个模式可以由多个外模式。其反应用户的不同需求。
外模式与应用： 一个外模式可以为多个应用使用，一个应用使用一个外模式。

（读出来的数据）

内模式（存储模式）

  • 数据物理结构和存储方式的描述
  • 数据再数据库内的表示方式

一个数据库只有一个内模式。
（存在系统里的数据结构）

数据库的二级映像

三级模式是对数据的三个抽象级别。
二级映像是三个抽象层次的联系和转换。

外模式——模式映像
对于每个外模式，有一个外模式——模式的映像。
映像定义通常包含在各外模式的描述中。

保证数据的逻辑独立性:

  1. 当模式改变时，数据库管理员对外模式/模式映像做出改变，使得外模式不变。
  2. 应用程序是依据外模式编写的，应用程序不必修改，保证了数据的逻辑独立性。

模式——内模式映像：
只有一个模式、一个内模式，所以只有一个模式/内模式映像。
定义了数据全局逻辑结构与存储结构之间的对应关系。

保证数据的物理独立性：
当数据库的存储结构变了（换了种数据结构），数据库管理员修改模式/内模式映像，使模式保持不变。

数据系统的组成

  1. 数据库
  2. 数据库管理系统
  3. 应用程序
  4. 数据库管理员

从硬件平台及数据库的角度：

  1. 足够大的内存
  2. 足够大的磁盘或磁盘阵列等外部设备
  3. 较高的通道能力，提高数据传输率

从软件的角度：

  1. 支持数据库管理系统运行的操作系统
  2. 与数据库结构的高级语言及其编译系统
  3. 以数据库管理系统为核心的应用开发工具
  4. 以特定应用环境开发的数据库应用系统

从人员的角度：

  1. 数据库管理员
  2. 系统分析员和数据库设计人员
  3. 应用程序员
  4. 最终用户

小结

数据库的组成要素
三级模式与两级映像
逻辑独立性与物理独立性

发表于 更新于 分类于

SQL语法——增删改查

SELECT 查询语句(从数据库中选取数据)

FROM 范围子句(限定查询的范围)

语法：SELECT 字段 FROM 表名；

1
2
SELECT * FROM table_1;      #查询table_1中所有内容
SELECT col_1 FROM table_1;	#查询table_1中的col_1列

WHERE 条件子句(提出选取的条件)

语法：SELECT 字段 FROM 表 WHERE 条件;

条件 通常为字段=值
如果是文本字段，则要用单引号环绕；如果是数值字段，则不要。

1
SELECT * FROM table_1 WHERE col_1='val';  #查询table_1所有col_1列为val的内容

GRPUP BY 分组子句()

HAVING 再统计子句()

ORDER BY 排序子句(对结果集进行排序)

语法：SELECT 字段 FROM 表 ORDER BY 排序字段 排序方式;

排序字段可选多个，按照前后顺序优先排序。
排序方式可选：ASC:顺序、DESC：降序。

1
2
SELECT * FROM table_1 ORDER BY col_1 DESC, col_2 ASC;
#查询table_表所有内容，按照col_1降序排序，col_1值相同的按照col_2升序排序

LIMIT 查询数量子句(设置查询记录的条数)

语法：SELECT 字段 FROM 表 LIMIT 数量;

这个子句是MySQL中特有的，SQL Server中对应的是TOP，Oracle中对应的是ROWNUM，用法略有不同。

1
SELECT * FROM table_1 LIMIT 1000; #查询table_1表中前1000条记录的所有字段

DISTINCT去重查询子句（结果集删除重复项)

语法：SELECT DISTINCT 字段 FROM 表;

1
SELECT DISTINCT col_1 FROM table_1;	  #查询table_1表col_1列不重复的记录

INSERT 插入语句(向表中插入新记录)

INTO 指定表子句(指定插入到哪张表(以及哪些字段))

VALUES 指定值子句(指定要插入的值)

语法：INSERT INTO 表 VALUES 值;

INTO可指定插入数据的表，也可指定表和具体字段。
VALUES列出指定字段的值(若没有指定字段则要列出所有字段的值)，可以一次插入多条记录。
1
2
3
INSERT INTO table_1 VALUES (val_1, val_2,...);            #在table_1表中插入一条记录
INSERT INTO table_1 VALUES (val_1,...), (val_2,...);      #在table_1表中插入两条记录
INSERT INTO table_1 (col_1, col_2) VALUES (val_1, val_2); #在table_1表中插入新记录，只记录col_1,col_2的值

UPDATE 更新语句(更新表中记录)

SET 设置子句(设置某字段的值)

语法：UPDATE 表 SET 字段名='值';

SET一次可以设置多个字段的值，字符型数值要用单引号环绕。

1
2
UPDATE table_1 SET col_1='val_1', col_2='val_2';
#将table_1表中所有记录的col_1设为val_1,col_2设为val_2

WHERE 条件子句(限制更新的范围)

语法：UPDATE 表名 SET 字段名='值' WHERE 条件;

条件通常为字段名='值'，字符型数值要用单引号环绕。
1
2
UPDATE table_1 SET col_1='val_1' WHERE col_2='val_2';
#将table_1表 中所有col_2的值为val_2的记录 的 col_1字段更新为val_1

DELETE 删除语句(删除表中记录)

FROM 范围子句(限定删除的范围)

语法：DELETE FROM 表; 或 DELETE * FROM 表;

不会删除表，只会删除表中记录。(效率不及TRUNCATE TABLE)

1
DELETE FROM table_1; #删除table_1表中的所有记录

WHERE 条件子句(限定删除的条件)

语法：DELETE FROM 表 WHERE 条件;

条件通常为字段名='值'，字符型数值要用单引号环绕。
1
DELETE FROM table_1 WHERE col_1='val_1'; #删除table_1表中col_1字段值为val_1的记录

操作符 (通常配合WHERE子句使用)

AND & OR 条件连接操作符(用于连接条件)

语法：条件1 AND 条件2/条件1 OR 条件2 AND OR就是字面意思

1
2
SELECT * FROM table_1 WHERE col_1='val_1' AND col_2='val_2';
查询table_1表所有col_1列为val_1且col_2列为val_2的内容

LIKE & RLIKE 模糊匹配操作符(用于对值的模糊匹配)

LIKE语法：... WHERE 字段 LIKE '通配符条件';
RLIKE语法：...WHERE 字段 RLIKE '正则表达式';

LIKE、RLIKE用于WHERE子句中。

1
2
SELECT * FROM table_1 WHERE col_1 LIKE 'B%';    #查询table_1表中所有col_1字段以B开头的记录
SELECT * FROM table_1 WHERE col_1 RLIKE '[Be]'; #查询table_1表中所有col_1字段以B或e开头的记录

通配符 与 正则表达式

通配符与LIKE一起使用，其包括：

通配符 描述
% 替代0个或多个字符。
_ 替代1个字符。

正则表达式与RLIKE一起使用，例如：

正则表达式(例子) 描述
[charlist] 列表中任意字节开头，如：[BfG]匹配’B’或’f’或’G’。
[^charlist]或[!charlist] 不在列表中的任意字节开头。

IN 多值指定操作符(指定多个可能的值)

语法：...WHERE 字段 IN (多个值);

IN操作符用于WHERE子句中。

1
2
SELECT * FROM table_1 WHERE col_1 IN (val_1, val_2);
#查询table_1表中所有col_1字段值为val_1或val_2的记录

BETWEEN 二选一操作符(指定两个可能的值)

语法：...WHERE 字段 BETWEEN 值1 AND 值2;

BETWEEN配合AND一起使用。

1
2
SELECT * FROM table_1 WHERE col_1 BETWEEN val_1 AND val_2;
#查询table_1表中所有col_1字段值为val_1或val_2的记录

发表于 更新于 分类于

SQL语法——创建与选择

USE 选择语句(选择一个数据库)

语法：USE 数据库名;

只有选择了一个数据库后，才可以在这个数据库内增删改查。

1
USE database_1; #选择database_1数据库

SHOW 展示语句(展示数据库或表格)

通过SHOW语句查看所有的数据库/数据库内所有的表

DATABASES & TABLES 数据库子句

语法：SHOW DATABASES;或SHOW TABLES;

1
2
SHOW DATABASES; #查看所有的数据库
SHOW TABLES;    #查看该数据库内所有表格

DESCRIBE 描述表语句(查看表的结构)

语法：DESCRIBE 表名

1
DESCRIBE table_1; #展示table_1表的结构信息

CREATE 创建语句(创建数据库/表)

通过CREATE语句可以创建数据库或表。

DATABASE 数据库子句(用于创建数据库)

语法：CREATE DATABASE 数据库名;

1
CREATE DATABASE database_1; #创建一个名为database_1的数据库

TABLE 表格子句(用于创建表格)

语法-1：CREATE TABLE 表名;
语法-2：CREATE TABLE 表名{字段名 数据类型 约束};

创建表格之前，需要先选择一个已创建的数据库。
创建时可以在大括号内描述该表格的结构。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_1; #创建一个名为table_1的空表
CREATE TABLE table_1 {
  col_1 int PRIMARY KEY ,
  col_2 varchar(255) NOT NULL
}; #创建一个名为table_1的空表，主键为col_1，col_2不可为空
CREATE TABLE table_1 {
  col_1 int,
  col_2 varchar(255)，
  NOT NULL (col_1, col_2)
}; #创建一个名为table_1的空表，col_1与col_2不可为空

有关约束的部分在后面。

DROP 删除语句(删除数据库/表/索引/字段)

DATABASE 数据子句

语法：DROP DATABASE 数据库名;

1
DROP DATABASE database_1; #删除database_1数据库

TABLE 表格子句

语法：DROP TABLE 表名;

1
DROP TABLE table_1; #删除table_1表

INDEX 索引子句

语法：...DROP INDEX 索引名;

MySQL中：DROP INDEX要指定表，所以要写在ALTER语句后面

1
2
#MySQL
ALTER TABLE table_1 DROP INDEX index_1; #删除table_1表中的index_1索引

COLUMN 字段子句

语法：...DROP COLUNM 字段名;

MySQL中：DROP COLUNM要指定表，所以要写在ALTER语句后面

1
2
#MySQL
ALTER TABLE table_1 DROP COLUNM col_1; #删除table_1表中的col_1字段

TRUNCATE TABLE 清空表内数据

语法：TRUNCATE TABLE 表名;

TRUNCATE TABLE 只清空表内数据，不删除表。

ALTER TABLE 修改表语句(在表中添加、删除、修改)

ADD 添加子句(新增字段)

语法：ALTER TABLE 表名 ADD 字段名 数据类型 约束;

1
2
#添加字段
ALTER TABLE table_1 ADD col_new int;  #在table_1表中新增int类型的col_new字段

DROP COLUMN / INDEX 删除字段/索引

在DROP中提到过

1
2
3
#MySQL
ALTER TABLE table_1 DROP COLUNM col_1;  #删除table_1表中的col_1字段
ALTER TABLE table_1 DROP INDEX index_1; #删除table_1表中的index_1索引

AUTO_INCREMENT 递增子句(让字段的值自动递增)

语法：ALTER TABLE 表 AUTO_INCREMENT=值

当一个表中有字段被约束为AUTO_INCREMENT时才能使用。
AUTO_INCREMENT=val设置该表中AUTO_INCREMENT字段的递增值从val开始。(从现在起)

1
ALTER TABLE table_1 AUTO_INCREMENT=100; #让表table_1的AUTO_INCREMENT字段从100开始递增

约束

当描述表格时，可以指定约束条件，其包括：

约束关键字 描述
NOT NULL 该字段不能为空
UNIQUE 该字段值不能重复
PRIMARY KEY 主键(可看作NOT NULL与UNIQUE结合，用于唯一标识一条记录)
FOREIGN KEY 外键(保证该表中的这个字段数据与另一个表中值有参照完整性)
CHECK 保证该字段的值符合给指定条件
DEFAULT 指定一个默认值
AUTO_INCREMENT 该字段的值从1开始，每条记录递增1

NOT NULL 非空约束

NOT NULL 约束强制某一字段不为空，若不为该字段指定值则无法插入记录。

0%