数据库原理sql命令大全（详细解释与总结）

最新推荐文章于 2024-05-08 17:47:16 发布

One_castle

最新推荐文章于 2024-05-08 17:47:16 发布

阅读量3.1k

点赞数 5

分类专栏：数据库原理文章标签： sql命令数据库

本文链接：https://blog.csdn.net/One_castle/article/details/105831587

版权

数据库原理专栏收录该内容

2 篇文章 1 订阅

订阅专栏

文章目录

sql命令

1、数据定义

1.1 模式

1,11 创建模式

CREATE SCHEMA 模式名 AUTHORIZATION 用户名

1.12 删除模式

 DROP SCHEMA 模式名 <CASCADE|RESTRICT>

CASCADE(级联)
- 删除模式的同时把模式中所有的数据库对象全部删除
RESTRICT(限制)
- 如果该模式中定义了下属的数据库对象（比如：表，视图等），那么就会拒绝该删除语句的执行
- 仅当该模式中没有任何下属的对象时才能执行

1.2 基本表

1.2.1 创建基本表

CREATE TABLE 表名 （列名 数据类型 列级完整性约束条件，列名 数据类型 列级完整性约束条件...）

1.2.2 常用的数据类型

1、char(n)、character(n) 长度为n的定长字符串
2、varchar(n),charactervarying(n) 最大长度为n的变长字符串
3、CLOB 字符串打对象
4、BLOB 二进制大对象
5、INT,INTEGER 长整型
6、SMALLINT 短整型
7、BIGINT 大整型
8、NUMERIC(p,d) 定点数（由p位数字（不包括符号、小数点）组成，小数后面有d位数字）
9、DECIMAL(p,d),DEC(p,d) 同NUMERIC
10、REAL 取决于机器精度的单精度浮点数
11、DOUBLE PRECISION 取决于机器精度的双精度浮点数
12、FLOAT(N) 可以选精度的浮点数，精度至少为N位数字
13、BOOLEAN 逻辑布尔值
14、DATE 日期，包括年月日格式为 yyyy-mm-dd
15、TIME 时间包括一日的时分秒，格式为hh:mm:ss
16、TIMESTAMP 时间戳类型
17、INTERVAL 时间间隔类型

1.2.3 常见的用户自定义完整性约束(主键、取值唯一、外键)

PRIMARY KEY列级完整性的约束条件，即确定主键
- 主键可以使外键即 PRIMARY KEY(列名1，列名2…) 需要对列名1，列名2进行定义声明，即如下：
FOREIGN KEY (列名) REFERENCES 表名（列名）
- eg : FOREIGN KEY (Sno) REFERENCES Student(Sno)
UNIQUE 即该列取值唯一，不允许重复

1.2.4 基本表增加字段（增加列名）

alter table 表名 **add** 字段名 类型 其他；

eg : 给student添加一个字段为test，类型为int(4)，默认值为空格
alter table student add test int(4) default ’ ’

1.2.5 删除表中的列

drop column 字段名 [CASCADE/RESTRICT]

CASCADE 自动删除引用了该列的其他对象
RESTRICT 如果该列被其他对象引用，那么DBMS就会拒绝删除该列

1.2.6 删除指定的完整性约束条件

DROP CONSTRAINT

1.2.7 修改原有的列定义(修改列名和修改类型)

 alter table 表名 alter column

eg:将student表中的年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数
alter table Student alter column Sage INT

1.3 模式与表

1.3.1 模式与表的关系

每一个基本表都属于某一个模式
一个模式包含多个基本表

1.3.2 定义基本表所属模式

一、在表名中明显地给出模式名

Create table "S-T".Studnet(....);//模式名为S-T
Create table "S-T".Course

二、在创建模式语句中同时创建表
三、设置所属的模式
关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名

1.4 索引

1.4.1 索引的作用

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

1.4.2 索引的建立

create [UNIQUE] [CLUSTER] INDEX 索引名 ON 表名

eg：为学生-课程数据库中的student建立索引

create UNIQUE INDEX Stusno ON Student(Sno)

1.4.3 索引的修改

alter INDEX 旧索引名 RENAME TO 新索引名

eg :将sc表的SCno索引名改为SCSno
alter index Sno rename to ScSno

1.4.4 索引的删除

DROP INDEX 索引名

删除索引时，系统会从数据字典中删去有关该索引的描述
eg: 删除student表的stusname索引
```
drop INDEX Stusname
```

1.4.5 关系型数据库管理系统中常见索引

顺序文件上的索引
B+树索引
散列（hash）索引
位图索引
特点：
- B+树索引具有动态平衡的优点
- HASH索引具有查找速度快的特点
谁建立索引？
- 数据库管理员或表的属主可以建立索引
谁维护索引？
- 索引由关系型数据库系统自动完成维护工作
怎么使用索引？
- 关系型数据库管理系统自动选择合适的索引作为存取路径，用户不必也不能显示地选择索引

1.5 数据字典

1.5.1 什么是数据字典

数据字典是关系型数据库管理系统内部的一组系统表， 他记录了数据库中的所有定义信息
- 关系模式定义
- 视图定义
- 索引定义
- 完整性约束定义
- 各类用户对数据库的操作权限
- 统计信息等
关系型数据库管理系统在执行sql的数据定义语句时，实际上就是在更新数据字典表中的响应信息

2、数据查询

2.1 查询语句

select [all|distinct] 目标列表达式

from 表名或视图|select语句

[where 条件表达式]

[group by 列名 having 条件表达式]

[order by] 列名

2.1.1 单表查询

2.1.11 选择表中若干列

查询指定列
- select Sno,Sname from Student
查询所有列
- select * from Student
查询经过计算的值
- 查询全体学生姓名以及出生年份
- select Sname, 2014-Sage from Student
使用列别名改变查询结果的列标题
- 通过使用as，可以省略不写
- select * from 表名 as 别名
- eg： select Sname as Name from Student

2.1.12 选择表中的若干不重复元素

查询没有重复元组的结果
- 消除取值重复的行 — 通过DISTINCT关键词，如果没有指定，则默认为ALL
- DISINCT关键词的作用是去掉重复的行
  - select DISTINCT Sno From SC；
  - 上述查询语句的作用是查询SC的学号，并且将学号重复的去掉进行显示结果

2.1.13 查询满足条件的元组

比较关系比较运算符 > < = <> NOT
- select Sname
  
  From Student
  
  where sage < 20
确定范围 BETWEEN AND，NOT BETWEEN AND
- 查询年龄在20~30岁的姓名
- select Sname
  
  from Student
  
  where Sage BETWEEN 20 AND 23
确定集合 IN , NOT IN
- 查询专业是CS MA和IS的学生的姓名
- selcet Sname
  
  from Student
  
  where Sdept IN (‘CS’,‘MA’,‘IS’)
字符匹配 LIKE , NOT LIKE
- 用法：[NOT] LIKE 匹配串 [escape ‘<换码字符>’]
- 匹配串可以完整的字符串，也可以含有通配符 % 和 _
  - %代表任意长度（长度为0）的字符串
  - _(下划线) 代表任意单个字符
  - 比如：a_b表示以a开头，以b结尾的长度为3的任意字符串
  - 又比如：刘%表示以刘开头的任意长度的字符串
- 其中如果想要匹配的字符串中包含 _ 和 %，那应该怎么俩表示呢
  - 可以通过转义字符\来对_ 或者 % 进行转义，但是这个转义字符\需要通过escape来进行声明
  - 比如想要查询以”DB_“开头的课程_：select Cname from Course where Cname LIKE ‘DB_%i_’ escape ‘\’
空值 IS NULL, IS NOT NULL
- IS不能用 = 代替
- 值为NULL 不能用比较符号（比如 = 、<>）来进行代替IS NULL表示
多重条件(逻辑运算) AND, OR
- AND 的优先级比 OR 高（可以通过括号来进行改变优先级）

2.1.14 ORDER BY子句

可以按照 一个或多个属性进行排序
DESC:降序 ASC：升序（为默认值）
- 例子：查询3号课程的学生的学号和成绩，查询结果按照分数降序排列
```
select Sno,Grade

from SC

where Cno = '3'

order by Grade desc;
```
- 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排序（数据按照多个属性进行排序的情况）
```
select *
from Student
order by Sdept ASC,Sage DESC;
```

2.1.15 聚集函数

常见的聚集函数
1. COUNT(*) 统计元组个数
2. COUNT(DISTINCT | ALL 列名) 统计一列中值的个数
3. SUM(DISTINCT | ALL 列名) 计算一列值的总和（此列必须为数值型）
4. AVG(DISTINCT | ALL 列名) 计算一列值的平均值（此列必须为数值型）
5. MAX(DISTINCT | ALL 列名) 求此列中的最大值
6. MIN(DISTINCT | ALL 列名) 求此列中的最小值
```
例如：查询学生总人数
select COUNT(*)
from Student

查询选修了课程的学生人
select COUNT(DISTINCT Sno)
from SC
```

2.1.16 GROUP BY子句

group by子句和聚集函数的关系
- 如果没有对查询结果进行group by分组，那么聚集函数将作用域整个查询结果
- 如果对查询结果分组之后，那么聚集函数将分别对每个组起作用
此外，在使用了group by子句进行分组之后，不能使用where来进行条件限制，而要使用having进行代替
- where 主要使用在基表或者视图
- having 主要使用来组

例如

求各个课程号以及相应的选课人数
select Cno，COUNT(*)
from SC
group by Cno

查询选修了3门以上课程的学生
select Sno
from SC
group by Sno
having COUNT(*)>3

注意：having中聚集函数是作用在每个组

2.1.2 连接查询

2.1.20 什么是连接查询？

连接查询： 同时涉及两个以上的表的查询。
连接条件或者连接谓词： 用来连接两个表的条件
- 一般格式为：
  1. 表名1.列名1 <比较运算符> 表名2.列名
  2. 表名1.列名1 between 表名2.列名2 and 表名2.列名3
连接字段： 连接谓词中的列名称
- 链接条件中的各个连接字段类型必须是可比的，但名字不必相同
连接操作的执行过程
1. 嵌套循环法
2. 排序合并法
3. 索引连接

2.1.21 等值与非等值连接查询

等值连接：连接运算符为 =
- 比如：查询每个学生及其选课的情况
```
select Student.*,SC.*
from Student,SC
where Student.Sno = SC.Sno
```

自然连接
- 自然连接和等值连接之间的关系：等值连接就相当于等值连接之后去掉相同的属性列
非等值连接：就是连接运算除了 = 之外的其他运算符号当做连接查询

2.1.22 自身连接

自身连接：一个表和自己进行连接。
因为需要进行区分，所以在连接的时候，要进行两个操作：
1. 需要给表起别名以示区别
2. 因为属性名都是想同的，所以需要加上别名前缀以示区别
```
查询每一门课的间接先修课（也就是先修课的先修课）
select first.Cno,second.Cno
from course first,course second
where first.Cpno = second.Cno
```

2.1.23 外连接

外连接和普通连接之间的区别：普通连接只输出满足连接条件的元组，而外连接将主题表不满足连接条件的元组一起输出来（不管是否满足连接条件）
外连接包括
- 左外连接
  - 即列出左边关系中所有元组
```
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left out join sc on (Student.Sno = SC.Sno)
```
- 右外连接
  - 即列出右边关系中所有元组

2.1.24 多表连接

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

以下是三表连接的例子

查询每个学生的学号、姓名、选修的课程名及成绩
select Student.Sno,Sname,Cname,Grade
from Student,SC,Course
where Student.Sno = SC.Sno
	  and SC.Cno = Course.Cno;

2.1.3 嵌套查询

2.1.31 什么是嵌套查询

嵌套查询
- 查询块 ：一个select-from-where语句就称为一个查询块
- 嵌套查询： 将一个 查询块 嵌套在另外一个查询块的where子句或者having短语的条件中的查询称为 嵌套查询
```
select Sname  //这部分称为外层查询或者父查询
from Student
where Sno in 
(			 //这层则称为内层查询或者子查询
select Sno
from SC
where Cno='2'
)
```
- 嵌套查询允许多层嵌套查询 ---- 也就是子查询中还可以嵌套其他子查询
- 需要额外注意的东西是： 子查询中不能使用order by 子句

2.1.32 嵌套查询的实现方法

不相关子查询 子查询的查询条件不依赖于父查询
相关子查询：子查询的查询条件依赖于父查询

2.1.33 带有IN谓词的子查询

即父查询通过IN谓词来嵌套查询子查询

查询与刘晨在同一个系的学生

select Sno,Sname,Sdept
from Student
where Sdept IN(
select Sdept
from Student
where Sname = '刘晨'
)

2.1.34 带有比较运算符的子查询

比较运算符有 > ,< , = >=, <=,!= 或者 <>

找出每个学生超过他选修课程平均成绩的课程号

selecet Sno,Cno
from SC x
where Grade >= (
select AVG(Grade)
from SC y
where y.Sno = x.Sno
)

2.1.35 带有ANT(SOME)或ALL谓词的子查询

在使用any(some) 或者 all 谓词的时候需要使用比较运算符
在某种情况下，ANY(SOME)或者ALL谓词和聚集函数、IN谓词之间是可以相互等价转换的

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

selecet Sname,Sage
from Student
where 
Sage < ANY(
select Sage
from student
where Sdept = 'CS' 
) and Sdept <> 'CS'

2.1.36 重点与难点！带有EXISTS谓词的子查询

带EXISTS谓词的子查询是不返回任何数据的，只会产生 布尔值 因此，由EXISTS引出的子查询，其目标列表达式通常都用*
EXISTS 子查询非空即为真，空极为假

NOT EXISTS 则与 EXIST相反

例题：查询所有选修了1号课程的学生的姓名

select Sname
from Student
where ESISTS(
select *
from SC
where Sno = Student.Sno and Cno = '1'
)

可以通过EXISTS/NOT EXISTS来实现全称量词

例如：查询与“刘晨”在同一个系学习的学生

select Sno,Sname
from Student s1
where EXISTS
(
selecet *
from Student s2
where s2.Sname = '刘晨' and s1.Sdept = s2.Sdept 
)

可以通过EXISTS谓词的子查询来实现逻辑蕴含

查询至少选修了学生201215122选修的全部课程的学生号码

-- 转化过来就是不存在这样的课程y,学生201215122选修了但是学生x没有选
selecet DISTINCT Sno
from SC SCA
where NOT EXISTS(
selecet *
from SC SCB
where Sno = '201215122' and NOT EXISTS (
selecet *
from SC SCC
WHERE SCC.Sno = SCA.Sno and SCC.Cno = SCB.Cno
	)
)

2.1.4 集合查询

2.1.40 什么是集合查询：

通过交并差的集合操作来对各个查询结果进行再次处理。
参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同

2.1.41 并操作

UNION 将多个查询结果合并起来时，系统自动去掉重复元组
UNION ALL 在将多个查询结果合并起来的时候，会保留重复的元组
- 查询计算机科学系的学生以及年龄不大于19岁的学生
```
select *
from Student
where Sdept = 'CS'
UNION
select *
from Student
where Sage <= 19
```

2.1.42 交操作

INTERSECT

查询计算机科学系与年龄不大于19岁的学生的交集

selcet * 
from Student
where Sdept = 'CS'
INTERSECT
select *
from student
where Sage<=19

2.1.43 差操作

EXCEPT
- 查询计算机科学系的学生与年龄不大于19岁的学生的差集
```
select *
from Student
where Sdept='CS'
selcet *
from Student
where Sage <= 19
```

2.1.5 基于派生表的查询

子查询出现在from子句中，此时子查询生成的临时派生表就称为了主查询的查询对象
- 例如：查询每个学生超过他自己选修课程平均成绩的课程号
```
select Cno
from SC,(
select Sno,AVG(Grade)
from SC
group by Sno
) as AVG_sc(avg_sno,avg_grade)
where SC.Sno = AVG_sc.avg_sno
and SC.Grade>= AVG_sc.avg_grade
```

3. 数据更新

2.2.1 插入数据

2.2.10 插入数据语句格式

insert 
into 表名（字段名）
values（对应字段名的值）/子查询

2.2.11 插入元素

例如插入一条选课记录（‘200215128’，‘1’）

-- 在Into表名后面将要插入数据的字段名列出来，其余没有列出来的数据如grade就会被自动赋值为空值（null）
insert
into SC(Sno,Cno)
values('201215128','1')

--在into表名也可以不指定字段名，但是在values中需要按顺序将所有值填上，例如
insert
into SC
values('201215128',1',NULL)

2.2.12 插入子查询结果

格式：
```
insert
into 表名
子查询
```

比如向一张Dept_age（Sdept,Avg_age）中插入每个系学生的平均年龄

-- 子查询的结果是根据Sdept进行分组并且对Sage进行求平均值
insert
into Dept_age(Sdept,Avg_age)
select Sdept,AVG(Sage)
from Student
Group by Sdept

2.2.13 在插入语句的时候会检查插入元素是否破坏了表已经定义了的完整性原则

实体性完整性
参照完整性
用户定义的完整性
- NOT NULL
- UNIQUE
- 值域约束

2.2.2 修改数据

2.2.20 语句格式

update 表名
set 列名 = 表达式
[where 条件] 如果没有where子句，说明要修改表中的所有元组

2.2.21 修改某一个元组的值

例如：将学生201215121的年龄修改为22岁

update Student
set Sage = 22
where Sno = '201215121'

2.2.22 修改多个元组的值

例如：将所有学生的年龄增加一岁
```
update Student
set Sage = Sage + 1
```

2.2.23 带子查询的修改

例如：将计算机科学系全体学生的成绩置零

update SC
set Grade
where Sno in (
select Sno 
from Studnet
where Sdept = 'CS'
)

2.2.24 同样在修改数据的时候，会检查修改操作是否破坏了表上定义的完整性规则

2.2.3 删除数据

2.2.30 语句格式

delete
from 表名
[where 条件]

2.2.31 删除某一个元组的值

例如：删除学号为 201215128的学生记录
```
delete
from Student
where Sno = '201215128'
```

2.2.32 删除多个元组的值

例如：删除所有的学生选课记录
```
delete
from SC
```

2.2.33 带子查询的删除语句

删除计算机科学系所有学生的选课记录

delete
from SC
where Sno in(
select Sno
from student
where Sdept = 'CS'
)

2.3 空值的处理

2.3.1 什么是空值

空值就是 不知道 或者 不存在 或者 无意义的值
空值一般表示为 NULL

2.3.2 空值的判断

判断是不是空值，一般通过IS NULL 或者 IS NOT NULL来进行判断

例如：从Student表中找出漏填了数据的学生信息

select *
from Student
where Sname IS NULL OR Ssex IS NULL
OR Sage IS NULL OR Sdept IS NULL

2.3.3 控制的约束

在定义属性或者定义域中，需要遵守以下规则
- 有了NOT NULL 约束条件的不能取空值
- 加了UNIQUE限制的属性不能取空值
- 码属性（例如主码）不能取空值

2.3.4 空值的运算

2.3.41 算术逻辑

空值与另外一个值（包括另外一个空值）的算术运算的结果为空值

2.3.42 比较运算

空值与另外一个值（包括另外一个空值）的比较运算的结果为unknown（表示空值无法比较）

2.3.43 逻辑运算

有unknown后，传统的二值（true、false）就变成了三值逻辑
- and 运算
  - T AND U => U
  - U AND U => U
  - F AND U => F
- or运算
  - T OR U => T
  - U OR U => U
  - F OR U => U
- NOT运算
  - NOT T => F
  - NOT U => U
总结
- AND 运算只有当两者都为真的时候才能为真
- OR 运算只有当其中一个为真的时候才能为真
- NOT U 不知道是T 是 F的情况下只能为 U

2.4 有关空值的例题

选出选修1号课程的不及格的学生以及缺考的学生

缺考即为NULL(空值)

select Sno
from SC
where Cno = '1' and (Grade < 60 OR Grade IS NULL)

2.4 视图

2.4.0 视图特点

视图是一张虚表，是从一个或者多个基本表（或者视图）导出来的表
视图只存放驶入的定义，不存放视图对应的数据
基本表中的数据发生变化，从视图中查询出的数据也随之改变
在执行create view语句的时候，并不会执行其中的select语句，只是将视图定义转存入数据字典中
在视图查询的时候，按照视图的定义从基本表中将数据查询出来

2.4.1 定义视图

2.4.10 行列子集视图

如果一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和列，但是保留了主码，就叫做是 行列子集视图

2.4.11 建立视图

语句格式：

create view 视图名
as 子查询
[with check option]

with check option
- 对视图进行update、insert和delete操作时，需要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
- 也就是说带有with check option子句定义的视图，在进行数据更新之后需要保证
在创建视图的时候属性列名：
- 要么就全部省略（此时的列名则是子查询目的）
- 要么就指定视图的所有列名
- 并且视图列可以是表达式，可以是分组，可以普通字段

例题1 [基于单个基本表]： 建立信息系学生的视图

create view IS_Student
as 
select Sno,Sname,Sage
from Student
where Sdept = 'IS'

例题2 [基于多个基本表建立视图]： 建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

create view IS_S1(Sno,Sname,Grade)
as 
select Student.Sno,Sname,Grade
from Student,SC
where Sdept='IS' and 
	  Student.Sno = SC.Sno
	  and SC.Cno = '1'

例题3 [基于视图建立视图]： 建立信息系选修了1号课程并且成绩在90分以上的学生的视图
```
create view IS_S2
as
selecet Sno,Sname,Grade
from IS_S1
where Grade>=90
```

2.4.12 删除视图

语句格式
```
drop view 视图名 [CASCADE]
```
从数据字典中删除指定的视图定义
在使用了CASCADE 关键字之后，由该基表导出的所有视图定义都会被删除
例如： 删除视图BT_S
```
drop view BT_S 
```

2.4.2 查询视图

2.4.21 查询视图

与查询基本表的方法相同

select 列名
from 视图名
where/having 子句

2.4.22 关系数据库管理系统实现视图查询的方法

视图消解法
- 进行有效性检查
- 转换成等价的对基本表的查询
- 执行修正后的查询
视图消解法有时候会出错：
- 比如在你查询的视图中你使用了where子句，但是你的视图定义中的子查询是有group by子句的。此时你的查询应该用having子句来代替你的where子句

2.4.5 更新视图

更新视图的语句和更新基本表的语句格式一样
```
update 视图名
set 列名
where 子句
```
有些视图不能进行更新，比如说在定义视图的时候有些列名是聚集函数（例如平均值AVG）,在更新视图的时候如果直接让这些列名等于某些值是会报错的
例如：这里的 Gavg在定义的时候是通过分组得到的平均值
```
update S_G
set Gavg = 90
where Sno='201215121'
```

常见的更新视图的限制：

若视图是由两个以上基本表导出的，那么这个视图不允许更新
若视图的字段来自字段表达式或者常数，则不允许对此视图执行insert 和 update操作，但是允许delete
若视图的字段来自聚集函数，那么这个视图不允许更新update
如果视图定义中含有 group by 子句，那么这视图不允许更新
若视图定义中含有DISTINCT,那么视图不允许更新
若视图定义有嵌套查询，并且内层查询的from子句中涉及的表也是导出该视图的基本表，那么此视图不允许更新
一个不允许更新的视图上定义的视图也不允许更新

2.4.6 视图的作用

视图能够简化用户的操作
视图能够使用户已多种角度去看待同一数据
视图对重构数据库提供了一定程度的逻辑独立性
视图能够对机密数据提供安全保护
适当的利用视图可以更清晰的表达查询

One_castle

关注

5
点赞
踩
39

收藏

觉得还不错? 一键收藏
0
评论
数据库原理sql命令大全（详细解释与总结）

sql命令1、数据定义1.1 模式1,11 创建模式CREATE SCHEMA 模式名 AUTHORIZATION 用户名1.12 删除模式 DROP SCHEMA 模式名 <CASCADE|RESTRICT>CASCADE(级联)删除模式的同时把模式中所有的数据库对象全部删除RESTRICT(限制)如果该模式中定义了下属的数据库对象（比如：表，视图等...
复制链接

扫一扫

专栏目录