【数据库原理复习】ch2 SQL基本语句增删改查（主要基于sql server）

猫毛已经快要掉光的小猫

已于 2023-03-09 10:36:54 修改

阅读量379

点赞数

分类专栏：数据库系统原理文章标签：数据库 sql

于 2023-03-06 16:36:27 首次发布

本文链接：https://blog.csdn.net/qq_49400568/article/details/129358747

版权

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

4 篇文章 0 订阅

订阅专栏

这里写目录标题

基本知识
表SQL语句

基本知识

常用基本数据类型

字符型数据类型

名称	大小	说明
char(n)	占n个字节	只能显示英文字符
nchar(n)	2n字节+2字节额外开销	Unicode编码，支持中文
ntext	每字符2字节	Unicode编码
nvarchar(max)	2n字节 + 2字节额外开销	根据存储字符数字节占用空间动态变化，Unicode
varchar(n)	n字节 + 2字节额外开销	仅支持英文，动态变化

二进制数据类型

名称	大小
binary(n)	占n个字节
image	每个字符1字节
varbinary(n)	每字符1字节
varbinary(max)	每字符1字节 + 2字节额外开销

日期类型

名称	大小	说明
Date	占3个字节	保存月日
Datetime	8个字节	保存年月日秒，精确到最近的3,3ms

数字类型

整型：int
近似数据类型：float
numeric(p ,q) / decimal(p, q)：总共有p位数，小数后面有d位
逻辑数据类型：bit, 仅取1，0或null

Null：表示当前字段值未知，未插入，不可用，或者稍后添加等。
Default：表示表中数据默认取值。

约束条件

非空约束（not null）：限制当前列数据都不为空。
默认约束（default）：为这个数据列定义一个默认值
唯一性约束（unique）：表示当前列的所有数据都不重复（除了null，可以有多个null）
主键约束（primary key）：表示这个或者这几个字段标识某一行数据
外键约束（foreign key）：根据另外一个表的某个列获得的数据进行有效值判定，通常是另外一个表的主键
检查约束（check）：对列进行约束，一般是自定义的表达式。

unique约束与not null 约束没有关系， unique只限制有值的情况下每一个值不能相同，可以有多个null 值。

表SQL语句

创建语句

create table table_name(# 是括号
	字段名1 类型  额外条件(没有就省略), # 逗号隔开
	字段名2 类型 额外条件,
	xxxx,
	xxx
)

create table 表名
(
	<列名> <数据类型> [<列级完整性约束>],
	<列名> <数据类型> [<列级完整性约束>],
	xxxx,
	[<表级完整性约束条件>]
)
# 具体例子
create table student_table(
	stu_no char(10) Primary key,
	name char(6) Not Null,
	sex char(2),
	birthday Datetime,
	class_no char(4) 	# 最后一个不需要逗号
)
# 表级完整性约束定义
Primary key(字段名1， 字段名2， 。。。。) # 主码定义
FOREIGN KEY (字段1) REFERENCES 表名（字段名2）# 外键定义

如果主码由属性组组成，需要作为表级完整性进行定义

修改基本表 & 删除基本表

Alter table <表名>
	[add <新列名> <数据类型> [完整性约束]]	# 添加新列
	[drop <完整性约束名>]					# 删除完整性约束
	[alter column <列名> <数据类型>];		# 修改原有的列定义

Drop table <表名> [restrict | cascade]

删除时，cascade表示级联删除，依赖于这张表的也将被删除，例如参考这张表的外键所在的表也将被删除。
restrict，表示如果有参考这张表的，在删除时候该条指令会被拒绝。默认restrict删除。
DML(data management language)：create alter drop

数据查询

单表查询

# 大体查询语句框架
SELECT [ALL | DISTINCT]
 <目标表达式>							# 表示查询结果需要的字段
[, <目标表达式>]							# 如果有多个字段，逗号进行隔开
FROM <表或视图> [, 表或者视图]			# 从哪里查询数据
[WHERE <条件表达式>]						# 条件是什么
[GROUP BY <列名1> [HAVING <条件表达式>]]	# 是否需要进行分组
[ORDER BY <列名2> [ASC|DESC]]			# 结果是否需要进行排序，对那个字段进行排序

distinct 与 ALL 区别在于查询结果是否消除重复行，all显示所有，不消除重复行，结果有几个就有几个。distinct会将查询结果向同行只显示一行。
[not] between and 表示在什么之间，包括两个边界。
[not] in <值表> 这个字段值是否在这个值表当中
例如：where Sdept in (‘cs’, ‘ma’, ‘sse’)
[not] like ‘<匹配串>’ [ESCAPE ‘<换码字符>’]
匹配串中可以使用占位符：%表示任意长度的字符串，下划线_只占单个字符。
如果本来就有%或者_需要\进行转义
AND OR 用来连接多个查询条件，AND优先级高于OR
ORDER BY 排序， ASC 升序 DESC 降序

多表查询

# 嵌套查询，指的是在一个select块中的where或having语句中包含另外一个select块
select xxx from xxx
where xx > (select xxx from xxx)

# 多表查询，联系关系演算的连接操作进行理解
# 等值连接
select xx from a, b
where a.val = b.val

# 左外连接
select xx from a, b
where a.val left join b.val
# 右外连接类似

# 多表连接多加条件即可
select xx from a, b, c
where a.val1 = b.val1 and b.val2 = c.val2

子查询不允许使用order by语句

带特殊谓词的子查询

# 带in的子查询，表示查询条件是否在子查询的结果当中
# e.g.
select Sno, Sname from Student
where Sno in 
	(select Sno from SC where Cno in
		(select Cno from Course where Cname = '信息系统')
	)
# 如果确定子查询结果只会返回单值，可以换成 < > = 等

# any(some) all
# 字面意思，比子查询结果一个（any）/全部（all）要> < 等等
# e.g.
SELECT Sname, Sage
from Student
where Sage < All (select Sage from Student where Sdept='CS') and Sdept <> 'CS'

部分any all等价于对内层使用min max聚集函数

EXIST：如果子语句返回有元组就范围true如果无就返回false

唯一难点：表示‘全部’，可以令其等价为，不存在xxx不xxx，等价于两个not exist语句。

# 查询选修了全部课程的学生姓名
# 等价于不存在一门课是他没有修的
select Sname
from Student
where not exists					# 不存在这样一门课
	(select *						# 他没有修这门课
	from Course						# 他没修这门课等价于
	where not exists				# 在Course表有但SC表没有
		(select * from sc
		where Sno = Student.Sno and Cno=Course.Cno
		) 
	)

集合查询

UNION [ALL] 并
INTERSECT 交
EXCEPT 差
只是将查询结果进行并交叉。all表示消除重复

数据插入 & 数据删除 & 数据修改

# 数据插入
# 需要与建表所有字段一一对应，否则报错
insert into table_name values (val1, val2,...)		
# 如果只需要插入部分字段，需要说明插入字段是什么
insert into table_name (var1, var2) values (val1, val2)

# 数据删除
# 如果有条件就加在where后面，不加表示删除所有表的数据
delete from table_name
[where xxx ] 

# 数据更新
update table_name 
set var = val			# 可以是表达式
where xxx

高阶知识点理解

连接执行过程

嵌套循环（可以用双for循环理解）
表1找第一个元组，然后从表头扫描表2，逐一查找，满足连接条件就拼起来形成一个元组。表2找完之后，再选表1的一个元组，重复操作。
排序合并（常用于等值连接）
对表1，表2排序。表1选第一个元组。从头扫描表2，顺序查找满足连接条件的元组，找到后就连接拼起来，遇到不符合后，直接不需要继续查找表2.从表1再选一个元组。
索引连接
对表2连接字段建立索引。对表1中的每个元素，根据其连接字段查询表2索引，找到满足条件就拼起来。重复操作。

嵌套查询执行过程

不相关子查询。子查询条件与父查询无关
有里向外，子查询结束之后，返回值给父查询。类似函数调用。
相关子查询。子查询条件要用到父查询字段值
取父查询一个元组，然后根据这个元组条件寻找子查询，子查询结束返回结果，验证父查询条件是否符合。符合就将这个元组加入结果表。重复循环

辨析

where与having有什么区别？
相同点：where与having 都作为条件筛查
不同点：where针对的是一行一行元组进行筛查。having针对的是已经group by 分组完的一组一组进行筛查。所以having必须跟在group by 之后。同时having 后常用聚集函数，这个聚集函数针对的也是之后分组之后一组内部的元组（只要使用了group by，该select块所有的聚集函数针对的都是分组之后的结果）。where之后（一般）不能使用聚集函数。
drop table delete truncate table有什么区别？
相同点：三者都可以达到删除数据的目的
不同点：drop table 删除的是表，是将组成该数据所有的磁盘文件都删除，包括日志文件等。delete 删除记录，删除一次就会在日志文件中记录一次。truncate 删除记录，每次删除时候不记录日志，只进行删除然后释放，速度会快一点。