数据库笔记-关系数据库的操作
2021/6/17 19:30:18
本文主要是介绍数据库笔记-关系数据库的操作,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
SQL数据库的三级结构
三级结构
关系模式称为"基本表",存储模式称为"存储文件",子模式称为"视图",元组称"行",属性称"列"。
SQL数据库体系的结构要点如下:
- (1)一个SQL数据库是表的汇集。
- (2)一个SQL表由行集构成,行是列的序列,每列对应一个数据项。
- (3)表或者是基本表,或者是视图。基本表是实际存储在数据库中的表,视图由是由若干基本表或其他视图构成的表的定义。
- (4)一个基本表可以跨一个或多个存储文件,一个存储文件也可存放一个或多个基本表。存储文件与物理文件对应。
- (5)用户可以用SQL语句对表进行操作,包括视图和基本表。
- (6)SQL的用户可以是应用程序,也可以是终端用户。
SQL语句(结构化查询语言)
- 数据定义语言(DDL)负责创建,修改,删除表,索引和视图等对象 -Create,Alter,Drop
- 数据操作语言(DML)负责数据库中的数据的插入,修改,查询和删除操作 -Insert,Update,Select,Delete
- 数据控制语言(DCL)用来授权和撤销用户对数据的操作权限 -Grant,Revoke
数据定义
基本数据类型
- 数值型:包括 int、smallint、decimal(p,d)、numeric(p,d)
- 字符串型:char(n) 、nchar(n)、varchar(n)、nvarchar(n)前2者是定长,后2者为变长串
- 时间型:datetime、date
- 以上类型中不常见的解释:
- decimal(p,d)(精确数值型,共p位,小数点后有d位)
- numeric(p,d) (共p位,小数点后有d位)
- nchar:其定义形式为Nchar(n)。它与Char数据类型类似,不同的是Nchar数据类型n的取值为1~4000。Nchar数据类型采用Unicode标准字符集,Unicode标准用两个字节为一个存储单位,其一个存储单位的容纳量就大大增加了,可以将全世界的语言文字都囊括在内,在一个数据列中就可以同时出现中文、英文、法文等,而不会出现编码冲突。
- Nvarchar:其定义形式Nvarchar(n)。它与Varchar数据类型相似,Nvarchar数据类型也采用Unicode标准字符集,n的取值范围为1~4000。
基本表
基本表的创建:(建立表结构)
CREATE TABLE 基本表名 (列名,类型, …… 完整性约束...) --完整性约束包括主键子句(PRIMARY KEY)、检查子句(CHECK)和外键子句(Foreign KEY).
基本表结构的修改
-- 增加新列: ALTER TABLE 基本表名 ADD 列名 类型 [列的完整性约束] -- 删除原有的列: ALTER TABLE 基本表名 DROP COLUMN 列名 -- 增加表级约束: ALTER TABLE 基本表名 ADD 表级完整性约束 或 ALTER TABLE 基本表名 ADD CONSTRAINT 表级完整性约束名 约束条件 -- 删除原有表级约束: ALTER TABLE 基本表名 DROP CONSTRAINT 约束名 --修改数据列,只能修改数据列的数据类型,注意其中的限制: ALTER TABLE 基本表名 ALTER COLUMN 列名 新的数据类型
基本表的撤消
DROP TABLE 基本表名
索引
索引的创建:
CREATE [CLUSTERED][UNIQUE] INDEX 索引名 ON 表名(列名[ASC|DESC])
说明
- 列名后面需要指定ASC[升序]或DESC[降序] 若不指定,默认为升序
- 本语句建立索引的排列方式为首先以列名中的第一个排序,该列值相同的记录,按下一列名的值排序
- UNIQUEL规定索引的每一个索引值只对应表或视图中唯一的记录,可允许存在NULL值,但不允许出现多个NULL值
- CLUSTERED:表示该索引为聚簇索引,一个表或者视图中只允许有一个聚簇索引,建立索引后,表在磁盘中的物理顺序与聚簇索引一致。在最常查询的列上建立聚簇索引可以加快查询速度,在经常更新的列上建立聚簇索引,则DBMS维护索引的代价太大
- NONCLUSTERED:规定此索引为非聚簇索引,是SQL的默认选择。一个表可以有多个非聚簇索引。对于非聚簇索引,数据的物理存储顺序独立于索引存储顺序。每个索引行均包含非聚簇键值和一个或多个含定位器(指向包含该值的行)每个索引均可以提供对数据的不同排序次序的访问。
设计索引的情况分析
- 适合建索引
- 经常被查询,如经常在WHERE子句中出现的属性
- 在order by子句使用的属性
- 频繁出现在连接条件中的属性,即主码或外部码
- 该列的值唯一的属性
- 主码或外部码应建立索引
- 对于以查询为主或只读的表,可以多建索引
- 对于范围查询(=、<、>、>=、<=比较符确定查询范围)可在相关属性上建立索引
- 使用聚集函数(MIN、MAX、AVG、SUM、COUNT)或者排序输出的属性最好建立索引
- 不适合建索引
- 太小的表
- 在查询中很少被引用的属性
- 包含太多重复选用值得属性
- 一些特殊数据类型的属性不能建立索引,BIT,TEXT,IMAGE等
索引的撤消:
DROP INDEX 表名.索引名
数据查询
SELECT语句的基本句法
SELECT-FROM-WHERE句型
SELECT 列名表(逗号隔开) FROM 基本表或视图序列 WHERE 条件表达式
Notice:掌握条件表达式中各种运算符的应用.
- 算术比较运算符=,>,<,<>或!=,<=,>=;
- 逻辑运算符AND、OR、NOT;
- 集合成员资格运算符:IN,NOT IN;like, not like;between and ,any(存在一个),all(所有);
- 谓词:EXISTS(存在量词),NOT EXISTS;
- 聚合函数
聚合函数
如果语句没有Group By则返回单个值;如果语句带Group By则每个小组返回一个值。
函数名 | 函数含义 |
---|---|
COUNT(*) | 计算元组的个数 |
COUNT(列名) | 求一列中值的计算个数 |
COUNT(DISTINCT 列名) | 求一列中值的种类数 |
SUM(列名) | 求一列中值的总和 |
AVG(列名) | 求一列中值的平均值 |
MAX(列名) | 求一列中值的最大值 |
MIN(列名) | 求一列中值的最小值 |
比较运算符的嵌套查询
select * from emplyee where age > any(select age from employee where Dno='02' and Dno <>'02')
- > any 只要大于其中一个就行
- >all 必须大于所有结果
- < any 只要小于其中一个就行
- < all 必须小于所有结果
- >=any 只要大于等于其中一个就行
- >=all 必须大于等于所有结果
- <=any 只要小于等于其中一个就行
- <=all 必须小于等于所有结果
- =any 只要等于一个就行
- <> any 只要不等于其中一个就行
- <>all 必须不等于所有结果
完整SELECT语句
SELECT 列名表(逗号隔开) FROM 基本表或视图序列 [WHERE 条件表达式] (行条件子句) [GROUP BY 列名序列] (分组子句) [HAVING 组条件表达式] (组条件子句) [ORDER BY列名[ASC|DESC]..] (排序子句)
-
SELECT子句中的规定
- 如果要求输出表格中不允许出现重复元组,则在SELECT后加一DISTINCT
- SELECT 子句中允许出现 +,-,*,/,以及列名、常数、函数的算术表达式
-
条件表达式的算术比较操作
- WHERE 子句中可以用BETWEEN…AND…来限定一个值的范围
-
列和基本表的改名操作
- 同一个基本表在SELECT语句中多次引用时可用AS来增加别名
-
字符串的匹配操作
- WHERE子句中字符串匹配用LIKE和两个通配符,%和下划线_. %为任意长度的字符串,_表示单个字符
-
集合的并、交、差操作
- 查询结果的结构完全一致时,可将两个查询进行并(UNION)、交(InterSect)、差(Except)
-
空值的比较操作
- 查询空值操作不是用=‘null’,而是用 IS NULL来测试。
-
集合的比较操作
- 集合成员资格比较用 IN/NOT IN
- 集合成员算术比较用元组θ ANY/ALL (θ是算术比较运算符)(少用)
-
导出表的使用
- 使用INTO关键字,可以给子查询结果起个表名,保存相关查询数据。 (select into)
连接查询
等值连接
select [distinct/all]<目标列表达式[别名]清单> from <关系名1[别名1]> inner join <关系名2[别名2]> on 连接条件表达式 或 select [distinct/all]<目标列表达式[别名]清单> from <关系名清单> on 连接条件表达式
左外连接(left join):除了返回两表中满足条件的元组以外,还将左侧表中不匹配的元组,右侧表中以空值代替
右外连接(right join):除了返回两表中满足条件的元组以外,还将右侧表中不匹配的元组,左侧表中以空值代替
全外连接(full join):除了返回两表中满足条件的元组以外,还返回左侧表中不匹配元组,右侧表中以空值代替,以及右侧表中不匹配元组,左侧表中以空值代替
自身连接:有些特殊的查询需要对同一个关系进行多次连接查询
-- 自身连接 -- emgr表示该员工直接负责人,连接查询得到该员工的间接负责人,也就是找该负责人的负责人 select emp1.cno,emp2.emger from employee emp1, employee emp2 where emp1.emgr = emp2.cno
数据更新
数据插入
(1) 元组值的插入
INSERT INTO 基本表名(列名表) values(元组值)
(2) 查询结果的插入
INSERT INTO 基本表名(列名表) SELECT 查询语句
数据删除
DELETE FROM 基本表名 [WHERE条件表达式] --(语义:从基本表中删除满足条件表达式的元组)
视图
创建:
CREATE VIEW 视图名(列名表) AS SELECT 查询语句 [with check option];
若有[with check option],则在今后进行insert,update,delete操作时,系统自动检查是否符合原定义视图子查询中的条件表达式
注意:如果在视图中碰到聚合函数,必须创建表名
撤消:
DROP VIEW 视图名
视图更新
对试图的更新其实是DBMS自动将视图的更新,转换成了对基本表的更新。
如果在定义时有添加了[with check option],对试图更新时系统发将自动检查原定义时的条件是否满足,若不满足,则拒绝执行。
视图的可更新性
- 如果视图是从多个基本表使用连接操作导出的,则不允许更新。
- 如果导出的视图使用了分组和聚合操作,也不允许更新。
- 如果视图是从单个基本表使用选择、投影操作导出的,并且包括了基本表的主键和没有缺省值的不可为空的列, 那么这样的视图称为“行列子集视图”,则可以执行操作。
这篇关于数据库笔记-关系数据库的操作的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-02Java管理系统项目实战入门教程
- 2024-11-02Java监控系统项目实战教程
- 2024-11-02Java就业项目项目实战:从入门到初级工程师的必备技能
- 2024-11-02Java全端项目实战入门教程
- 2024-11-02Java全栈项目实战:从入门到初级应用
- 2024-11-02Java日志系统项目实战:初学者完全指南
- 2024-11-02Java微服务系统项目实战入门教程
- 2024-11-02Java微服务项目实战:新手入门指南
- 2024-11-02Java项目实战:新手入门教程
- 2024-11-02Java小程序项目实战:从入门到简单应用