还是要好好学习的…万一哪天就有用了呢？
原创文章，考前必备，当前20014字，已更新至3.2.6 SQL的嵌套查询
持续更新中…
转载请附带如下文字：
版权声明：本文为CSDN博主「风几许」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Fray_2002/article/details/127280254
绪论
1.1 数据库系统概述
1. 定义与历史
- 数据库是指按照一定规则存储数据的仓库
- 数据库技术诞生于上世纪六十年代末，是信息系统的核心与基础
- 目前，数据库有三种类型，关系型数据库、非关系型数据库、键值数据库。
- 关系型数据库：如MySQL、MariaDB
- 非关系型数据库：Cassandra、MongoDB
- 键值型数据库：Dynamo、LevelDB
2. 四个基本概念
-
数据(Data)
- 数据是数据库中存储的基本对象
- 它是被描述事物的符号记录
- 数据的种类可以是文本、图形、图像等等等
- 数据和语义是密不可分的。举个例子，数字3是一个数据，他的语义可以是年龄、学年、学分等等等。
-
数据库(Database)
- 数据库就是存储数据的仓库
-
数据库管理系统(DBMS)
- 数据库管理系统，是位于用户和操作系统之间的一层数据管理软件。
- 它可以科学的组织和存储数据，高效的获取和维护数据。
- 一般而言，数据库管理系统会提供操纵语言(DML)，用以实现对数据库的基本操作
- 也就是增删改查
- 数据库管理系统要保障数据的安全性、完整型，保障多用户对数据的并发使用
-
数据库系统(DBS)
- 在计算机系统中引入数据库后的系统构成数据库系统
- 这玩意包括：
- 数据库、数据库管理系统、应用程序、数据库管理员、用户
1.2 数据模型
1. 两大类数据模型
- 数据模型分为两类，概念模型，逻辑模型和物理模型
- 概念模型，又称信息模型，他是按照需求对数据和信息进行建模后得到的，用于数据库设计。
- 逻辑模型，包括层次模型，关系模型，面向对象模型，网状模型等，用于数据库管理系统实现。
- 物理模型，就是对数据最底层的抽象，描述数据在系统内部的表示方式和存取方法。
2. 基本概念
-
实体(Entity)
-
客观存在并可以相互区别的事物被称为实体。
-
可以是具体的人、事、物活抽象的概念。
-
-
属性(Attribute)
-
实体所具有的某一特性被称为属性
-
一个实体可以有很多很多的属性
-
-
码(Key)
-
可以唯一标识实体的属性集被称为码。
-
比如id就是最典型的Key。
-
-
域(Domain)
-
属性的取值范围被成为该属性的域。
-
这里的取值范围不一定是数学的那种取值范围，实体“小明”的属性“姓名”，在“中文”域里，也可以。
-
-
实体型(Entity Type)
-
实体名及其属性名来抽象和刻画同类实体的，被称为实体型。
-
其实和编程语言里抽象出来的类概念有一点像。
-
-
实体集(Entity Set)
-
同一类型实体的集合被成为实体集。
-
小明小红都是实体，人是实体型，这个班级同学是实体集。
-
-
联系(Relationship)
-
现实世界中事物内部以及事物之间的联系，在信息世界中，反应为实体内部的联系或是实体之间的联系。
-
实体内部的联系通常是属性之间的联系，实体之间的联系通常是不同实体集之间的联系。
-
3. 两个实体型之间的联系
-
一对一 ( 1 : 1 )
-
如果对于实体集A中的每一个实体，实体B中最多有一个实体与其联系，反之亦然，就称这两个实体集A\B具有一对一联系，记为1:1
-
比如，一个班级只有一个正班长
-
一个正班长也只在一个班级里任职
-
-
一对多联系 ( 1 : n )
-
如果对于实体集A中的每一个实体，实体B中有n个实体与其联系( n >= 0 )，反之，对于实体B中的每个实体，实体A中至多有一个实体与之联系，就称这两个实体集A\B具有一对多联系，记为1:n
-
比如，一个班级只有一个数学老师
-
一个数学老师可以在多个班级里任职
-
-
多对多联系 ( m : n )
-
如果对于实体集A中的每一个实体，实体B中有m个实体与其联系( m >= 0 )，反之，对于实体B中的每个实体，实体A中也有n个实体与其联系( n >= 0 )，就称这两个实体集A\B具有多对多联系，记为m:n
-
比如，一个班级有多个老师
-
每个老师可以在多个班级里任职
-
4. ER图
- ER图中，矩形表示实体，圆形表示属性，菱形表示联系
- 联系本身也是一种实体型，可以有属性。
- 示例如下：
5. 数据模型的组成要素
-
数据结构
- 数据结构描述了数据库的组成对象,以及对象间的联系
- 这是对系统静态特性的描述
- 比如说，学生关系模式里，可能有学号(CHAR 8)、姓名(CHAR 8)、性别(INT 1)、专业代号(INT 2)
-
数据操作
-
对各种对象或实例允许执行的操作。
-
这是对系统动态特性的描述
-
增删改查就属于这个
-
-
数据的完整性约束条件
-
完整性约束条件是一组完整性规则的集合。
-
完整性规则指的是，给定数据模型中，数据及其联系所具有的制约和存储规则。
-
完整性约束用来限定数据库状态和数据库状态变化，保证数据的正确、有效、相容。
-
-
关系模型
-
关系数据库采用关系模型作为数据的组织方式
-
在用户观点下，关系模型中数据的逻辑结构是一张二维表，它由行和列组成。
-
关系模型中，关系必须是规范化的，每个分量必须是一个不可分的数据项，不允许表里还有个表
-
-
关系数据模型的完整性约束条件
-
实体完整性
实体完整性这项规则要求每个数据表都必须有主键，而作为主键的所有字段，其属性必须是独一及非空值。
-
参照完整性
参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。参照完整性又称引用完整性。
-
用户自定义完整性
比如，用户要求输入年龄时，不能输入负数。
-
1.3 数据库系统结构
1. 数据库系统模式的概念
-
类型(Type)和值(Value)是最基本的概念之一。
-
类型是对某一类数据的结构和属性的说明
-
值是类型的一个具体赋值
-
-
模式(Schema)和实例(Instance)也是
-
模式是数据库中全体数据的逻辑结构和特征的描述
-
这是类型的描述，不涉及具体的值
-
实例是模式的一个具体的值，反应某一时刻状态
-
同一个模式可以有很多实例
-
2. 数据库系统的三级模式结构
-
模式 (Schema)
- 模式，又称逻辑模式，上一个小结刚写完
- 一个数据库只有一个模式
-
外模式 (External Schema)
- 外模式，是数据库用户使用的局部数据结构的逻辑结构和特征的描述
- 外模式通常是模式的自己
-
内模式 (Internal Schema)
-
内模式是数据物理结构和存储方式的模式
-
一个数据库只有一个内模式
-
这玩意距离的是数据在数据库内部的表示方式
-
关系代数
2.1 概述
1. 定义
-
关系代数是一种抽象的查询语言，利用对关系的运算来表达查询。其主要特点为:
-
是以关系为运算对象的一组运算集合
-
以集合的操作为基本运算
-
运算后的结果还是关系
-
2. 传统的集合运算
-
传统的集合运算符 [并(∪)、交(∩)、差(－)、广义笛卡儿积(×)]
-
并(∪)
-
在集合运算里，代表求两个集合的并集
在关系运算里，所运算的两个关系应该：
- 都具有n个属性
- 相应的属性取自同一个域
-
运算后得到的结果仍是一个n目的关系，由属于R或者S的元素组成
R∪S =
-
比如
关系R具有A,B,C三个属性，分别有(a1,b1,c1), (a2,b2,c2)两个元组
关系S具有A,B,C三个属性，分别有(a3,b3,c3), (a2,b2,c2)两个元组
R和S的属性分别取自同一个域
那么，R∪S的结果就是
有重复的，就去掉重复元素哦
-
-
差(-)
-
在集合运算里，代表求两个集合的差集
在关系运算里，所运算的两个关系应该：
- 都具有n个属性
- 相应的属性取自同一个域
-
运算后得到的结果仍是一个n目的关系，由属于R或者S的元素组成
R∪S =
-
比如
关系R具有A,B,C三个属性，分别有(a1,b1,c1), (a2,b2,c2)两个元组
关系S具有A,B,C三个属性，分别有(a3,b3,c3), (a2,b2,c2)两个元组
R和S的属性分别取自同一个域
那么，R∪S的结果就是
-
-
交(∩)
-
在集合运算里，代表求两个集合的交集
在关系运算里，所运算的两个关系应该：
- 都具有n个属性
- 相应的属性取自同一个域
-
运算后得到的结果仍是一个n目的关系，由属于R或者S的元素组成
R∪S =
-
比如
关系R具有A,B,C三个属性，分别有(a1,b1,c1), (a2,b2,c2)两个元组
关系S具有A,B,C三个属性，分别有(a3,b3,c3), (a2,b2,c2)两个元组
R和S的属性分别取自同一个域
那么，R∪S的结果就是
-
-
笛卡尔积(X)
-
在集合运算里，代表求两个集合的直积
在关系运算里，所运算的两个关系：
- R具有n目关系，k1个元组
- S具有m目关系，k2个元组
-
运算后得到的结果仍是一个n X m目的关系，由属于R或者S的元素组成
它的列是(n+m)列元组的集合
行是k1 * k2个元组
R×S =
-
比如
关系R具有A,B,C三个属性，分别有(a1,b1,c1), (a2,b2,c2)两个元组
关系S具有A,B,C三个属性，分别有(a3,b3,c3), (a2,b2,c2)两个元组
R和S的属性分别取自同一个域
那么，R∪S的结果就是
然后属性变成了(R.A, R.B, R.C, S.A, S.B, S.C)
不同名的话就不用管了，同名就以关系名.属性名表示
-
-
3. 专门的关系运算
-
设R一个关系模式为R(A1,A2,A3,A4)
- 把它的一个关系设为R
- t∈R表示t是R的一个元组
- t[Ai]表示t中属性Ai的一个分量
-
还是对于上面那个
- 若A={Ai1, Ai2, …, Aik}，其中Ai1，Ai2是A1,A2的一部分，则称A为属性列或属性组
- t[A] = (t[Ai1], t[Ai2], …, t[Aik])表示元组t在属性列A上诸分量的集合
- A拔则表示R中去掉A的属性组
-
元组的连接
- R为n目关系，S为m目关系。
- 假设t属于R，t2∈S
- 那么元组的连接是一个n+m列的元组，前n个分量是R里的一个n元组，后m个是S里的一个m元组
-
象集Zx
- 给定一个关系R（X，Z），X 和 Z 为属性组。
- 当t[X]=x 时，x在R 中的象集（Images Set）
- Zx=
- 它表示R 中属性组X上值为x的诸元组在Z上分量的集合
李明 在 R 中的象集课程名称李明 =
张敏在R中的象集 课程名称张敏 =
王强在R中的象集 课程名称王强=
4. 选择、投影、连接和除
- 来一个数据库
| **学号 **Sno | 姓名 Sname | 性别 Ssex | **年龄 **Sage | 所在系 Sdept |
|---|---|---|---|---|
| 200215121 | 李勇 | 男 | 20 | CS |
| 200215122 | 刘晨 | 女 | 19 | IS |
| 200215123 | 王敏 | 女 | 18 | MA |
| 200215125 | 张立 | 男 | 19 | IS |
-
再来俩R和S
学号 姓名 年龄 001 John 23 002 Rose 20
| 教师号 | 姓名 | 年龄 |
|---|---|---|
| 001 | Bill | 22 |
| 002 | Rose | 30 |
-
选择
-
选择又称为限制
-
简单的来说，就是设定一个条件，过滤出所有符合条件的元组
-
条件可以是>,<, ≥ ,≤, =等
-
顺便一提，所有空值，都算不满足条件
-
查询σSage < 20 ∧ Sdept=‘IS’(Student)
-
从学生表中选出性别为女的所有学生：σ Ssex = “女”(Student)
从学生表中选出性别为女且年龄为19的所有学生σ Ssex = “女”.and.σ Sage = 19(Student)
-
-
投影
-
投影，从R中选择出若干属性列组成新的关系
-
选择是横向选出若干个元组，投影是纵向选出若干个列
-
投影可能会取消某些元组，行数可能会减少（可能主键被干掉了）
-
求全体学生的学号：∏ Sno, (Student)
-
求Student关系上姓名和所在系的投影：∏ Sname，Sdept(Student)
-
-
连接
-
又称θ连接
-
θ是比较运算符。
-
公式如下，我觉得写那么多乱乱的东西更不好理解。
-
就是先求R和S的笛卡尔积
然后在这里面选择满足AθB的。
比如，R⟗S(R.年龄 > S.年龄)
先笛卡尔积，如下图
学号 R.姓名 R.年龄 教师号 S.姓名 S.年龄 001 John 23 001 Bill 22 001 John 23 002 Rose 30 002 Rose 20 001 Bill 22 002 Rose 20 002 Rose 30 然后选择(R.年龄 > S.年龄)的
结果就是
学号 R.姓名 R.年龄 教师号 S.姓名 S.年龄 001 John 23 001 Bill 22 -
然后，我要进行一些定义
首先，连接，又称θ连接
等值连接是一种特殊的θ连接，θ运算符为=的时候，即成立等值连接
等值连接可以分为内连接和外连接
其中，内连接又称自然连接
外连接包括全外连接，左外连接，右外连接。
【这里的定义很乱，网上无数个不同版本，这只是其一。】
-
如果比较运算符θ变成了=，那么就成立为了等值连接。
比如，如果我现在有两个表，都是学生信息，以学号作为主键。一个表有选了多少课，另一个表表有参加了多少社团
那就是找到两个表里学号相同的元组，这两个表里另一个表没有的元组会被舍弃
等值连接中，公共属性不会合并，原先有多少列，结束的时候就有多少列。
-
如果在所有公共属性上都做等值连接，并且在结果中去掉重复属性，就是自然连接。
可以理解为直接把那两个学生信息表合并起来，合并主键–学号。
回到刚刚的连接，我们舍弃了不符合θ关系的元组，如果不舍弃这部分元组，并在缺失的属性上填空值，就变成了外连接。
为了方便理解，我花了半个多小时做了一个图
-
一图让你看懂内外连接
-
再举个例子
查询信息系（IS）选修了‘Access’课程的学生姓名
首先，在学生表里查询信息系的学生σSdept=‘IS’( Student)
然后在课程表查询选修了’Access’的学生σCname=‘Access’(Course)
但是这两个不能直接连接，因为没有公告信息
所以和总表连接
σSdept=‘IS’( Student)∞SC∞σCname=‘Access’(Course)
然后再投影取它的姓名
πSname(σSdept=‘IS’( Student)∞SC∞σCname=‘Access’(Course))
简写一下，就是
πSname(σCname=‘Access‘ ∧ sdept=‘IS’(Student ∞ SC ∞ Course))
-
查询所有选修了课程的学生姓名和选修课程号
∏Sname，cno( Student [连接] SC )
先把这两个表合并做自然连接，再投影找到姓名和选修课程号构成的元组
-
询所有学生的学生姓名和他们的选修课程号
∏Sname，cno( Student [左外连接] SC )
先把这两个表做左外连接，保留没有选课信息的表
然后投影找到姓名和选修课程号构成的元组
-
-
除
-
关系R和S除运算的结果T为满足如下条件的最大的元组集合
T x S 包含于R
-
除法运算的除数和被除数都是一个关系
-
除运算的被除数R一般是表达两个实体之间一对多或多对多之间联系的关系（ST）
其中各元组表达了现实中某实体型（X）中某个实体（x）与另一类实体型（Y）中各实体（y）之间的联系。
比如，某学生选修某一门课程
-
除数S一般是由参与该联系的某个实体型（Y）中的多个实体（y）组成的关系。
比如，信息系的所有学生
-
-
除法运算的具体含义是从一个实体型（X）中选择出与除数S中所有实体均有联系的实体t，组成新的关系T，即被除数T。
-
除法运算的笛卡尔积的逆运算。 有点类似于线代矩阵的除法，我们只是找到一个关系，和除数关系做笛卡尔积，能得到被除数关系。
其前提条件为，关系R和S中必须具有相同属性Y，且R中必须包含不在S中的属性集X。
-
R ÷ S ， R(其他属性, 相同属性) \ S(相同属性，其他属性)
-
运算第一步，把除数简化为相同属性
-
R(x,y)÷S(y,z) == R(x,y) ÷ S(y)
-
-
除法操作同时从行角度和列角度进行运算，直接用老师课件
-
Eating÷Apple
= R(Pno, Ano) ÷ Apple(Ano)
找到包含Apple(Ano)里的每一个元素的Pno
也就是
-
来个例题
-
查询选修了全部课程的学生号码和姓名
-
先在Course表里，找到所有课程的id，∏ Cno（Course）
-
然后用SC表，做投影拿到学生id和课程id，除上面那个，找到选修所有课程的学生的学生id ∏Sno，Cno（SC）÷ ∏ Cno（Course）
-
然后用选修了所有课程的学生的学生id，连接学生宗表，投影找他们的学号和姓名
-
(∏Sno，Cno（SC）÷ ∏ Cno（Course）) ∏ Sno，Sname（Student）
-
2.2 例题
1. 综合案例题干
-
假设有一个供应商和零件数据库，其中有三个关系S，P和SP分别表示供应商关系表、零件关系表和供应商供应零件关系表。
-
S (S#, SNAME, STATUS, CITY)，对应的属性分别为供应商编号、供应商名称、供应商状态、供应商所在城市；
-
P (P#, PNAME, COLOR, WEIGHT, PRICE)，对应的属性分别为零件编号、零件名称、颜色、重量和价格，其中价格的单位为元；
-
SP (S#, P#, QTY),其中S#参照S.S#, P#参照P.P#，都是外码，属性分别为供应商编号、零件编号、供应量。
2. 查询
-
求London城市中的供应商的全部信息
- 全部信息，也就是获取行，直接在供应商表里选择就行
- σcity = ‘London’ (S)
-
求London城市中的供应商的供应商号,名称和状态
- 这回要求供应商号、名称和状态，投个影就行
- ∏s#, SNAME, STATUS(σcity = ‘London’ (S))
-
求红色并且重量不超过15的零件号和零件名
- 选择出这部分零件，投影出号和名
- ∏P#, PNAME (σCOLOR = ‘red’ ∧ WEIGHT < '15§)
-
求提供零件P2的供应商名称
- 先求供应商
- SC表里没有名称，先连接到S表
- 再投影拿名称
- ∏SNAME((σP# = ‘P2’(SP) ∞ S)
-
求所有提供红色零件的供应商名称
- 所有提供红色零件，不是提供所有红色零件
- 把SC表和P表联起来，查询红色零件
- 投影拿供应商编号，然后连S表找供应商名称
- 但实际上，这就是把所有表联起来，找红色零件
- 然后直接投影拿名称
- 就可以了，因为重复的会直接被舍掉
- ∏SNAME((σCOLOR = ‘red’(SP ∞ S ∞ P ))
-
求提供所有零件的供应商名称
- 提供所有零件，所以要除
- 先拿到所有零件∏P#§
- 直接SP表除所有零件，得到有所有零件的供应商编号和供应量
- 再在联S表，自然连接
- 再选择，找供应商名字
- ∏SNMAE( (SP ÷ ∏P#§) ∞ S)
-
顺便一提
- 做除法的时候，除数和被除数尽量都精确确定。
- 比如上面那一个，写成∏ SNAME (S∞(πS#,P# ( SP) ÷ πP#§))
- 会更好一点
-
求提供所有红色零件的供应商名称
- 在零件表里投影零件
- 改成查红色的再投影
- ∏ SNAME (S∞(πS#,P# ( SP) ÷ π( σCOLOR = ‘red’( P#§))
-
求不提供零件P2的供应商名称
- 先找提供P2的供应商编号
- 也就是 σ P#= ‘p2’ (P ∞ SP )
- ∏ SNAME ( S ) - ∏ SNAME ( σ P#= ‘p2’ (P ∞ SP ))
-
求至少提供了S2提供的所有零件的供应商名称
- 先找S2提供的所有零件
- ∏P#(σ S#= ‘S2’ (SP ))
- 然后用SP总表的P#\S#除一下
- ∏P#, S# (SP) ➗∏P#(σ S#= ‘S2’ (SP ))
- 再去S表找名字
- ∏SNAME((∏P#, S# (SP) ➗∏P#(σ S#= ‘S2’ (SP ))) ∞ S)
-
求提供P1但是不提供P2的供应商名称
- 先找提供P1的供应商编号
- 再减去提供P2的供应商编号
- ∏SNAME( ( ∏S#(σ P#= ‘P1’ (SP )) - ∏S#(σ P#= ‘P2’ (SP )) ) ∞ S )
-
求提供零件的价格均高于50元的供应商的编号
- 先找价格小于等于50的零件编号
- σ PRICE <= 50 §
- 找提供这些零件的供货商
- ∏S#(σ PRICE <= 50 § ∞ SP)
- 用所有供货商减
- ∏S#(SP) - ∏S#(σ PRICE <= 50 § ∞ SP)
MySQL语句
3.1 SQL概述
1. 定义
- SQL，一瞅就是个简写。它是（Structured Query Language），结构化查询语言的简写。
- SQL是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的功能极强的关系数据库标准语言。
- SQL是一种标准化的查询语言，MySQL是一种关系型数据库软件
- 这俩不在一个层面上哦
- SQL目前是一种很广泛的查询语言，很多数据库都在用
2. 特点
- 集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。
- SQL可以独立完成数据库生命周期中的全部活动
- 定义关系模式，插入数据，建立数据库
- 对数据库中的数据进行查询和更新
- 数据库重构和维护
- 数据库安全性、完整性控制
- 非关系数据模型的数据操纵语言面向过程，必须指定存取路径
- 而SQL只需要提出做什么，不需要了解存取路径
- 存取路径的选择以及SQL的操作过程都由系统自动完成
- SQL既可以作为独立语言，也可以作为嵌入式语言，嵌入到高级语言程序中使用
3. 基本概念
-
约束
-
定义
- 约束是强加在表上的规则或条件。
- 当我们对表进行DML或DDL操作时，如果此操作会造成表中的数据库违反约束条件或规则，系统就会拒绝执行这个操作。
- 我们写的约束被称为自定义约束
-
从功能分类
- 主键约束，用于唯一表示表中的某属性，不能重复，不能为空。
- 外键约束，用来维护从表和主表之间的引用完整性。
- 唯一约束，用来保证表中每行的某属性不能彼此相通
- 非空约束，用来约束某属性不能为空值
- 条件约束，用户自定义某条件，保证表中的每行都要满足该约束条件。
-
从层级分类
-
列级约束
- 包含在列定义中，直接写在列定义后头，只对单独列有效
- 支持所有的约束
-
表级约束
-
不包含在列定义中，单独作为一个东西写，必须指明要约束的列的名称，对这些列都有效。
-
不支持非空约束和默认约束。
-
-
-
常见的约束
-
not null 非空约束，用于保证这个字段不为空
-
default 默认约束，用于保证这个字段有默认值
-
PRIMARY KEY 主键约束，用于保证这个字段唯一且不为空
-
UNIQUE 唯一性约束，保证这个字段唯一
-
FOREGIN KEY 外键约束，保证该字段的值必须来自于主表
-
CHECK 用户自定义约束，自定义检查条件
-
-
-
名词
- 数据库中的基本单位
- 表 TABLE
- 模式 SCHEMA
- 视图 VIEW
- 索引 INDEX
- 数据库中的基本单位
3.2 SQL语法
1. 定义并添加约束
-
语法
CREATE TABLE <表名>(
<列名><数据类型> [列级完整性约束条件] ,
<列名> <数据类型> [列级完整性约束条件] ,
<表级完整性约束条件>)；
-
实例
-
实例1，主键约束，表级/列级约束
CREATE TABLE STUDENT (SNO CHAR(9) PRIMARY KEY, #创建学号属性，字符类型，9个字符长度，创建主键约束使其成为主键 SNAME CHAR(20) UNIQUE, #创建名字属性，字符类型，20个字符长度，创建唯一性约束使其不能同名(为啥不能同名) SSEX CHAR(2), #创建性别属性，字符类型，2个字符长度 SAGE SMALLINT, #创建年龄属性，小整数类型 SDEPT CHAR(20) #创建部门属性，字符类型，20个字符长度 # Constraint pk_SNO primary key(Cno) # 上面这个是表级约束，可以替代第一个 # Constraint [约束名] [约束类型](约束列) ); -
实例2，表级外键约束
CREATE TABLE SC (Sno number(12), #创建学号属性，数字类型，12个数字长度 Cno number(4), #创建课程号属性，数字类型，4个数字长度 Grade number(3), #创建成绩属性，数字类型，3个数字长度 Constraint pk_SC primary key (Sno,Cno), # 创建名为 pk_SC 的表级约束 # 对Sno和Cno两个属性添加主键约束 Constraint fk_c foreign key (Cno ) references Course(Cno) # 创建名为 fk_c 的表级约束 # 对Cno属性添加外键约束，Cno的属性只能从 Course 表里的 Cno属性获得 ); -
实例3 ，自引用列级外键约束
CREATE TABLE Course (Cno number(4) primary key, #创建课程号属性，数字类型，4个数字长度，创建主码约束 Cname char(20), #创建课程名属性，数字类型，20个数字长度 Cpno number (4) constraint fk_cpno references Course (Cno), # 创建pno属性，数字类型，4个长度 # 创建列级约束，约束名为fk_cpno， 使Pno属性只能从本表的Cno里获得 Ccredit number(4) #创建学分属性，数字类型，4个数字长度 ); -
实例4 ，自引用表级级外键约束，实例3的表级约束版本
CREATE TABLE Course (Cno number(4), #创建课程号属性，数字类型，4个数字长度 Cname char(20), #创建课程名属性，数字类型，20个数字长度 Cpno number (4)， # 创建pno属性，数字类型，4个长度 Ccredit number(4), #创建学分属性，数字类型，4个数字长度 Constraint fk_cpno foreign key (Cpno) references Course(Cno), Constraint pk_cno primary key (Cno) ); -
实例5 ，列级CHECK约束
CREATE TABLE SC (Sno number(12), #创建学号属性，数字类型，12个数字长度 Cno number(4), #创建课程号属性，数字类型，4个数字长度 Grade number(3) Constraint ck_g check(Grade>=0 AND Grade<=100), # 创建分数属性，数字类型，3个数字长度 # 添加名为ck_g的列级约束，保证(Grade>=0 AND Grade<=100) Constraint pk_SC primary key (Sno,Cno), # 添加名为pk_sc的表级主键约束，保证Sno,Cno的唯一性和非空性 Constraint fk_s foreign key (Sno ) references Student(Sno), # 添加名为fk_s的表级外键约束，保证Sno继承自学生表的Sno属性 Constraint fk_c foreign key (Cno ) references Course(Cno) # 添加名为fk_c的表级外键约束，保证Cno继承自课程表的Cno属性 );-
实例6 ，列级非空约束
CREATE TABLE Course (Cno number(4) constraint pk_Course primary key, #创建课程号属性，数字类型，4个数字长度，添加名为pk_Course的主键约束 Cname char(20) not null， #创建课程名属性，数字类型，20个数字长度，约束其非空 Cpno number(4), # 创建pno属性，数字类型，4个长度 Ccredit number(4) #创建学分属性，数字类型，4个数字长度 ); -
实例7 ，列级唯一性约束
CREATE TABLE Course (Cno number(4) constraint pk_Course primary key, #创建课程号属性，数字类型，4个数字长度，添加名为pk_Course的主键约束 Cname char(20) constraint u_cname unique, #创建课程名属性，数字类型，20个数字长度，添加名为u_cname的唯一性约束 Cpno number(4), # 创建pno属性，数字类型，4个长度 Ccredit number(4) #创建学分属性，数字类型，4个数字长度 ); -
实例7 ，列级约束
CREATE TABLE STUDENT (SNO CHAR(9) PRIMARY KEY, #创建学号属性，字符类型，9个字符长度，创建主键约束使其成为主键 SNAME CHAR(20) UNIQUE, #创建名字属性，字符类型，20个字符长度，创建唯一性约束使其不能同名(为啥不能同名) SSEX CHAR(2) default('男'),#创建性别属性，字符类型，2个字符长度，创建默认属性约束，添加默认为'男' SAGE SMALLINT, #创建年龄属性，小整数类型 SDEPT CHAR(20) #创建部门属性，字符类型，20个字符长度 );
-
-
2. 表的删除和修改
-
语法
ALTER TABLE <表名>
[ADD <新列名><数据类型>[完整性约束]]
[ DROP column 列名]
[add constraint <完整性约束名> <完整性约束>]
[DROP constraint <完整性约束名>]
[MODIFY<列名> <数据类型>[完整性约束]]
];
-
其中<表名>指定需要修改的基本表
-
ADD子句用于增加新列和新的完整性约束条件
-
DROP子句用于删除指定的完整性约束条件
-
MODIFY子句用于修改原有的列定义。
-
-
实例
-
向Student表增加“入学时间”列，其数据类型为日期型
ALTER TABLE [TABLE名] ADD [列名] [列类型] (其实就是个列定义)ALTER TABLE Student ADD S_entrance date;
-
向Student表中加入“入学时间”、“生源地”两列。
和上面那个那个一样，只是添加了两列，所以要加个括号。ALTER TABLE Student ADD (S_entrance date, S_area char(10));
-
删除Student表中的“入学时间”列
ALTER TABLE [TABLE名] DROP [列名] [列类型]ALTER TABLE Student DROP COLUMN S_entrance；
-
删除Student表中“入学时间”、“生源地”两列。
和上面那个那个一样，只是添加了两列，所以要加个括号。ALTER TABLE Student DROP(S_entrance date, S_area char(10));
-
将Stuent表中性别Ssex这一列由原来的char(2)修改为char(8)，并赋默认值为‘女’
ALTER TABLE [TABLE名] MODIFY [列名] [修改后的列类型] [约束条件] (其实就是新的列定义)ALTER TABLE Student MODIFY Ssex char(8) DEFAULT(‘女’)；
-
将SC表中的检查约束ck_g删除。#Grade number(3) Constraint ck_g check(Grade>=0 AND Grade<=100)
ALTER TABLE [TABLE名] DROP CONSTRAINT [约束名]ALTER TABLE SC DROP CONSTRAINT ck_g；
-
删除关于学生姓名必须取唯一值的约束
对于这种没有约束名字的，就直接ALTER TABLE [TABLE名] DROP [约束类型](列名)ALTER TABLE Student DROP UNIQUE(Sname);
-
删除Student表
直接DROP 表DROP TABLE Student;
-
3. 索引的建立与删除
-
定义
- 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。
- 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针单。
- 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
- 一般来说，建立与删除索引需要由表的创建者或者数据库管理员，或具有创建、删除索引权限的用户完成。
- SQL索引有两种，聚集索引和非聚集索引，但是咱
-
语法
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [<次序>] ,[<列名>[<次序>]]…);
-
<表名>指定要建索引的基本表的名字。
-
索引可以建在该表的一列或多列上，各列名之间用逗号分隔。
-
每个<列名>后面还可以用<次序>指定索引值的排列次序
包括ASC(升序)和DESC(降序)两种，缺省值为ASC
-
UNIQUE:表明此索引的每一个索引值只对应唯一的数据记录。
-
CLUSTER:表示要建立的索引是聚簇索引。
所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织，聚簇索引在Oracle中只能在定义表的同时定义。用户可以在最常查询的列上建立聚簇索引以提高查询效率。在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。
-
在Oracle中，对一个表中的主键的字段不能建立聚簇索引，因为创建主键约束时，系统已经自动生成一个聚簇索引。
-
-
实例
-
为Course按课程名升序建唯一索引，
CREATE UNIQUE(唯一索引) INDEX Coucname(索引名) ON Course(Cname);CREATE UNIQUE INDEX Coucname ON Course(Cname);
-
为SC表按Sno升序和Cno降序建唯一索引。
CREATE UNIQUE(唯一索引) INDEX SCno(索引名) ON SC(Sno ASC,Cno DESC);CREATE UNIQUE INDEX SCnog ON SC(Sno ASC,Cno DESC);
-
删除Course表的Cname索引。
直接DROP INDEX 索引名DROP INDEX Coucname;
-
4. 数据的查询
-
语法
SELECT [ALL|DISTINCT] <目标列表达式>
[，<目标列表达式>] …FROM <表名或视图名>
[， <表名或视图名> ] …[ WHERE <条件表达式> ]
[[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；
-
咱还是看实例
-
实例1，指定查询列
# 查询全体学生的学号与姓名 SELECT Sno，Sname FROM Student； -
实例2，查询指定列
# 查询全体学生的姓名、学号、所在系 SELECT Sname，Sno，Sdept FROM Student； -
实例3，查询全部列
# 查询全体学生的全部信息 # *，懂得都懂，不懂得 SELECT * FROM Student； -
实例4，查询经过计算的信息
# 从Student表中查找学生姓名，出生年份 # SELECT的带查询值，可以为字符串常量、别名、表达式、函数等等 # 所以，出生年份等于今年的时间减去年龄 SELECT Sname, 2022-Sage FROM Student -
实例5，查询经过计算的信息
# 查询学生的姓名和出生日期，出生日期用别名Stu birthday显示 # 和实例4的区别是，出生日期要用别名表示 SELECT Sname, 2022-Sage "Stu birthday" FROM Student -
实例6，查询经过计算的值
# 再写一个，都用别名 # 这里提一下，列名名后的那个别名，加不加双引号都可以 # 但是如果别名有空格，必须加双引号 SELECT Sname NAME， 'Year of Birth:' BIRTH， 2020-Sage BIRTHDAY， LOWER(Sdept) DEPARTMENT FROM Student； -
实例7，关键字DISTINCT
# 这个关键字是用来去除重复行的 # 比如，我先让你查询所有课程的学分 SELECT Ccredit FROM Course; # 这个东西其实等价于 SELECT all Ccredit FROM Course; # 然后，我想让你查询学分都有哪几种 # 那咱就 SELECT DISTINCT Ccredit FROM Course; -
实例8，关键字DISTINCT
# 查询所有学生,姓名和性别的组合 SELECT DISTINCT Ssex，Sage FROM Student; # 这个DISTINCE，不止作用于其后一个属性，而是作用于查询的所有元组 # 换句话说，作用于（Ssex, Sage），保证这个不重复 -
实例9，使用WHERE子句
查 询 条 件 谓 词 比 较 =，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符 确定范围 BETWEEN AND，NOT BETWEEN AND 确定集合 IN，NOT IN 字符匹配 LIKE，NOT LIKE 空 值 IS NULL，IS NOT NULL 多重条件（逻辑运算） AND，OR，NOT # WHERE子句后面跟的是条件的布尔组合，其结果是查询指定条件的元组。WHERE子句常用的查询条件如上表所示。 # 从Course表中找到数据库的信息 SELECT * FROM Course WHERE Cname = '数据库'; -
实例10，使用WHERE子句
# 查询所有年龄不等于20岁的学生姓名及其年龄。 SELECT Sname, Sage FROM Student WHERE Sage != 20; # 不等于，可以被写作!=，或者<> -
实例11，使用WHERE子句
# 使用BETWEEN AND操作符可以选中排列于两值(包括这两个值)之间的数据。 # 这些数据可以是数字，文字或是日期。 # 也就是说，通过BETWEEN AND确定一个范围，并且把这个范围内的数据库中的值输出。 # 查询年龄在20至23岁之间的学生的姓名和年龄。 SELECT Sname, Sage FROM Student WHERE Sage between 20 and 23; # 顺带一提，SQL大小写不敏感 -
实例12，使用WHERE子句
# 查询年龄不在20至23岁之间的学生的姓名和年龄。(查询结果不包含20和23岁的学生的情况)。 # 就直接not上一个就行 SELECT Sname, Sage FROM Student WHERE Sage not between 20 and 23; -
实例13，使用WHERE子句
# 当用户知道某列的准确值并想要返回其记录，则可以使用IN操作符。 # IN指令可以让用户在一个或数个不连续的值的限制之内取出表中的值。 # 与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。 # 查询年龄为18或者20的学生的姓名和年龄。 SELECT Sname, Sage FROM Student WHERE Sage in (18,20); -
实例14，使用WHERE子句进行字符匹配
-
谓词LIKE可以用来进行模式的匹配。其一般语法格式如下：
- [NOT] LIKE ‘<模式>’ [ESCAPE ‘<换码字符>’]
- 其作用为， 查找指定的属性列值与<模式>相匹配的元组。
- 这里说的<模式>，可以包含常规字符和通配符字符。
- 模式匹配过程中，常规字符必须与字符串中指定的字符完全匹配。
- 而通配符可以匹配字符串中的某一部分，使用通配符可以使得LIKE运算更为灵活。
- 通配符一般包括单个字符通配和多个字符通配。
-
通配符%，包含零个或更多字符的任意字符串。
- WHERE Sname LIKE ‘%力%’
- 将查找处于学生名任意位置的包含‘力’字的所有学生名。
-
通配符_，代表任何单个字符。
- WHERE Sno LIKE ‘_5’
- 将查找以5结尾的所有2个字符长度的学生学号(如15、25 等)。
-
通过模式的匹配，如果匹配到指定的模式，LIKE 将返回 TRUE。否则，返回FALSE。
-
由于数据存储方式的原因，使用包含Char数据模式的字符串比较无法通过LIKE比较。
- 例如，Student表中Sname属性的数据类型是Char(6)，存在姓名为Dtt的学生
- 但是通过WHERE Sname LIKE '_tt’语句查不到记录
- 这是因为Char是定长的数据类型，在存储“Dtt”时，默认以空格补足后面的3位长度。
- 也就是，存储的是"Dtt "，这并不是以tt结尾
# 查找所有姓张的学生的姓名、学号 SELECT Sname, Sno FROM Student WHERE Sname LIKE '张%'; -
-
实例15，使用WHERE子句进行字符匹配
# 查学号中倒数第二个数字为1的学生姓名和学号。 # 倒数第二个数字为一，前面通配符无所谓 # 所以是% + 1 + _ SELECT Sname, Sno FROM Student WHERE Sno LIKE '%1_'; -
实例16，使用WHERE子句进行字符匹配
# 查DB_Design开头课程的课程号和学分。 # 如果用户要查询的匹配字符串本身就含有%或_，比如要查名字为DB_Design开头的课程的学分 # 这个时候就需要使用ESCAPE ‘ <换码字符>’短语，对通配符进行转义。 # ESCAPE ‘\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的“_”字符。 SELECT Cno, Ccredit FROM Course WHERE Cname LIKE "DB\_Design%" ESCAPE ‘\’; -
实例17，使用WHERE子句进行空值查找
# 某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面来查一下缺少成绩的学生的学号和相应的课程号。 # 因为空值表示缺少数据，所以空值和其它值没有可比性，即不能用等于、不等于、大于或小于和其它数值比较，测试空值只能用比较操作符IS NULL和IS NOT NULL。 SELECT Sno, Cno FROM SC WHERE Grade is NULL; -
实例18，使用WHERE子句进行复合查找
# 查年龄在20岁以下的男同学姓名 # 用一个AND连接起来就好了 SELECT Sname FROM Student WHERE Ssex = "男" AND Sage <= 20; -
实例19，使用ORDER BY子句进行查询结果排序
# 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。 # 用户也可以用ORDER BY子句指定按照一个或多个属性列的升序(ASC)或降序(DESC)重新排列查询结果。 # 其中升序ASC为缺省值。如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序(通常是元组在表中的先后顺序)输出查询结果。 SELECT Sno, Grade FROM SC WHERE Cno = 3 ORDER BY Grade DESC; # 空值算最大的 -
实例20，使用聚集函数
- COUNT([DISTINCT|ALL] *) 统计元组个数；
- COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数；
- SUM([DISTINCT|ALL] <列名>) 计算一列值的总和(此列必须是数值型) ;
- AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值(此列必须是数值型)；
- MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值；
- MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值。
- 如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。
- 如果不指定DISTINCT短语或指定ALL短语(ALL为缺省值)，则表示不取消重复值。
# 查询学生总人数 SELECT COUNT(*) FROM Student -
实例21，使用聚集函数
# 查询选修了2门以上课程的学生学号 SELECT Sno FROM SC GROUP BY Sno having count(cno) > 2; # 1.Where子句不能使用聚集函数 # 1.?，但是PPT上其实有使用的例子...不太理解 # 2.除了*，都不统计空值 -
实例22，使用聚集函数
# 查询学生的平均年龄 SELECT Avg(Sage) FROM Student; -
实例23，使用聚集函数
# 查询年龄最大的学生姓名 SELECT Sname FROM Student WHERE Sage = (SELECT Max(Sage) FROM Student); -
实例24，使用GROUP BY子句和HAVING子句
# GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组，也就是将聚集函数应用到关系中每个由多个分组组成的行上。 # 当在用聚集函数的时候，一般都要用到GROUP BY 先进行分组，然后再进行聚集函数的运算。 # 运算完后可能要用到HAVING 子句进行判断，例如判断聚集函数的值是否大于某一个值等等。 # 查询每个学生选修的课程数 SELECT Sno, COUNT(Sno) FROM SC GROUP BY Sno; -
实例25，使用GROUP BY子句和HAVING子句
# 查询平均分在80分以上的学生的学号及其选课数。 SELECT Sno, COUNT(Sno) FROM SC GROUP BY Sno HAVING AVG(Grade) > 80;
-
5. 数据的连接查询
-
定义
-
有时用户查询可能涉及多个表，才能查出所需要的信息。如果一个查询需要对多个表进行操作，就称为连接查询。
-
连接可以在SELECT 语句的FROM子句或WHERE子句中建立，不同的子句有不同的分类方式。
-
用WHERE子句连接的查询一般分为等值连接查询、非等值连接查询、自然连接查询和复合条件连接查询
-
用FROM子句连接的查询一般分为内连接、外连接和交叉连接。
详见2.1.4
-
-
实例
-
实例1，WHERE子句连接
# 查询每个学生及其选修课程的情况 SELECT * FROM SC,Student WHERE SC.Cno = Student.Cno; -
实例2，WHERE子句连接，笛卡尔积
# 对Student和SC表做笛卡尔积 SELECT * FROM Student,SC -
实例3，WHERE子句连接，自然连接
# 还记得θ连接不 # R,S就是这俩表 # A,B就是R.S.的东西 # θ运算符就是WHERE后面连接AB的符号 # 自然连接Course和SC表 # 等值连接后，SELECT所有不重复属性，就是自然连接 SELECT Course.Cno, Cname, Cpno,Ccredit, Sno, Grade FROM Course, SC WHERE Course.Cno=SC.Cno; -
实例4，WHERE子句连接，等值连接
# 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名 SELECT Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND SC.Cno = 2 AND SC.Grade > 90; # 先把俩表等值连接连起来 # 然后找到SC.Cno = 2的，也就是选修课2号课程的记录 # 再加个条件，成绩大于90 -
实例5，WHERE子句连接，inner join on，等值连接
# 查询所有男同学的选课信息 SELECT * FROM Student inner join SC on student.sno =sc.sno WHERE Student.Ssex = "男"; -
FROM连接语法
# FROM join_table1 join_type join_table2 [ON (join_condition)] # 上面是FROM的连接语法 # 其中, # join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。 # join_type指出连接类型，可分为三种：内连接、外连接和交叉连接。 # 内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等值连接三种。 # 外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中被舍弃的数据行。 # 交叉连接(CROSS JOIN)没有ON子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 # 连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。 # 内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种： # 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。 # 不等值连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。 # 自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。 -
实例6，FROM子句连接，inner join
# 使用等值连接查询选修了课程的学生全部信息 SELECT * FROM Student inner join SC on Student.Sno = SC.Sno # 使用自然连接查询选修了课程的学生全部信息 SELECT Student.*,Cno,Grade FROM Student INNER JOIN Sc N Student.Sno=Sc.Sno; -
实例7，FROM子句连接，left join
# 使用左外连接将Student和Sc表中的信息连接起来 # 内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。 # 而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行 # 而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。 SELECT * FROM Student left join SC on Student.Sno = Sc.Sno; -
实例8，FROM子句连接，笛卡尔积
# 交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积 。 # 求学生和课程两表的笛卡尔积 SELECT * FROM Student CROSS JOIN Course;
-
6. 数据的嵌套查询
- 定义
- 嵌套查询是将其他查询嵌套在另一个查询里面的查询，嵌套在查询中的查询称为子查询。
- 当然，子查询本身也可以是嵌套查询，这样就可以形成更深层次的查询。
- 子查询通常出现在WHERE子句中，有时候也出现FROM子句中，有时候也出现在HAVING短语中。
- 子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句永远只能对最终查询结果排序。
- 相关子查询
- 子查询的查询条件依赖于父查询
- 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
- 然后再取外层表的下一个元组
- 重复这一过程，直至外层表全部检查完为止
- 不相关子查询
- 子查询的查询条件不依赖于父查询
- 由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
- 嵌套查询是将其他查询嵌套在另一个查询里面的查询，嵌套在查询中的查询称为子查询。