程序员经验分享-hwhale

【数据库实验】sql总结

2023-11-17

首先说明：以下大部分针对的是标准sql

目录

结构

大致结构：
数据库-模式-表，视图，索引

关键词

创建：create
删除：drop
修改：alter
插入数据：insert
修改数据：update
删除数据：delete
cascade：删除数据时级联方式
restrict：删除数据时如果别的数据参照这个则拒绝
avg max min ：聚集函数
…

关于模式

创建模式：
CREATE SCHEMA Schema_Name AUTHORIZATION UserName;
删除模式
DROP SCHEMA Schema_Name CASCADE;

cascade:如果模式上定义了表视图或者其他东西，都会直接删除；而如果是restrict，则会提示，且拒绝操作。

关于表

创建表

这里举个例子吧

CREATE TABLE Student
		(Sno CHAR(9) PRIMARY KEY,
		 Sname CHAR(20) UNIQUE,
		 Ssex CHAR(2),
		 Sage SMALLINT,
		 Sdept CHAR(20)
		 );

在创建表的时候可以定义约束。有实体，参照，用户自定义三种约束，primary key，foreign key() reference __,check ,not null,unique等。这一部分我们下面会有个单元单独总结。

修改表

增加一列

ALTER TABLE Table_Name
ADD 列名 数据类型;

修改一列的数据类型

ALTER TABLE Table_Name
ALTER COLUMN 列名 数据类型;

增加一个约束（例子）

ALTER TABLE Course
ADD UNIQUE(Cname);

语法：

ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;
删除表
drop table 表名;

如果加上cascade：表上建立的索引、视图、触发器等一般也将被删除；缺省值为restrict，就是提示且拒绝删除请求。

关于索引

建立索引

例子：

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 SCno 
RENAME TO SCSno;

语法：？

删除索引
DROP INDEX 索引名字;

关于查询

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名> [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ];
几个点：
  1. 关于all和distinct：缺省时all。distinct就是若查询结果有重复元组则只输出一个重复元组。
  2. where里面不能用聚集函数。
  3. group by就是分组，然后查询结果中如果有聚集函数就会针对的每个组里面的元素，在组里面用聚集函数。
  4. having是group by后面呢，他是一个条件，针对的是每个组。
  5. 排序的缺省值是？
指定列：
SELECT Sno,Sname
FROM Student;
全部列：
SELECT *
FROM Student;
经过计算的值：
select Sname,2021-Sage
from student;
列的别名（方便查看）以及聚集函数
select Sname,'出生月份：',2021-Sage,LOWER(Sdept)
from student;
where就是加条件，用来选择行的。

between and用法：（可以加not）

select Sname , Sdept,Sage
from student
where Sage between 18 and 23;
in用法：（可以加not）
select Sname,Ssex
from student 
where Sdept in ('cs','ma','is');
模糊查询：
  1. 通配符：% _，%表示的是长度为任意（包括0）的字符串，_表示的是一个字符。
  2. 关于转义字符，这里叫换码字符：比如我们要查询的字符串里真的有%号这个字符，那我们就使用下面这种形式处理：WHERE Cname LIKE ‘DB%Design’ ESCAPE ‘’ ;表示的是\后面的那个字符是真正的字符而不是通配字符。
select *
from student
where Sname like '梁%';

select *
from student
where Sname like '欧阳_';

select Sname '姓名'
from student
where Sname like '_阳%'

select Sname '姓名',Sno '学号',Ssex '性别'
from student
where Sname not like '梁%'

select Cno , Ccredit
from course
where Cname like 'DB/_Design' escape '/';
NULL

注意不能用=NULL，要用is NULL 或者 is not null。

排序

ORDER BY子句
注意：可以按照好几个属性排序，有优先级。（类似写的cmp，asc升序；desc降序，默认为asc）；对于空值，由具体系统实现决定（也就是说没有规定）

常用聚集函数
◼ 统计元组个数 COUNT(*) 
◼ 统计一列中值的个数 COUNT([DISTINCT|ALL] <列名>) 
◼ 计算一列值的总和 SUM([DISTINCT|ALL] <列名>) 
◼ 计算一列值的平均值 AVG([DISTINCT|ALL] <列名>) 
◼ 求一列中的最大值和最小值
MAX([DISTINCT|ALL] <列名>)
MIN([DISTINCT|ALL] <列名>)
等值连接

就是在where里面加条件

自身连接

起别名

select first_table.Cno,second_table.Cpno
from course first_table,course second_table
where first_table.Cpno=second_table.Cno;
外连接
select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from student  left outer join sc ON(student.Sno=Sc.Sno);
镶嵌连接
select Sno,Cno
from SC x
where Grade>(
				select AVG(Grade)
				from SC y
				where x.Sno=y.Sno
);
带有比较运算符的子查询

事实上，select返回的是一个集合，刚才用in是集合里可能有好几个数据；这里用 比较运算符（>，<，=，>=，<=，!=或< >）就是得确定 集合只有一个元素。关于例子我们在这个文章的第一题已经说过了，这里不再写。

#带有ANY（SOME）或ALL谓词的子查询

any 相当于存在，all相当于所有。所以这个语义就很简单了。我们高中就讲过。
在这里插入图片描述

带有EXISTS谓词的子查询

关于几点：

  1. 只返回true或者false；
  2. 当内层查询里有数据时，为true，否则为false;
  3. 相当于存在量词。
  4. 一般内层查询的列只用*，因为无论写什么，他只返回一个true 或者 false。
  5. not exists 相当于exist取反。

例如

select Sname,Sno
from student 
where NOT EXISTS(
		select *
		from SC
		where Sno=student.Sno AND Cno='1'
)

这里有个难点：用存在量词替换全程连词和蕴含逻辑
这一部分比较难，我直接贴一份之前写的博客，里面写的比较详细。
exists
但大体就是 把肯定句换成双重否定
在这里插入图片描述
在这里插入图片描述

集合查询

这里就比较简单了，我们都知道，select返回的是一个集合，那么他的集合操作就是普通的集合操作，交 并 差 union intersect except。
例如：

select *
from student
where Sdept = 'cs'
union 
select *
from student
where Sage<=19;
基于派生表的查询

不仅可以在where里镶嵌查询块，在from里也可以，这个时候叫做 临时派生表。
例如：

select Sno,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;

关于插入数据

其中数据插入有两种方式，一种是插入元组，我；还有一种是插入查询结果，也就是查询集合。

INSERT 
INTO <表名>  [(<属性列1> [,<属性列2>…  )]
valuse(值，值。。);--需要和上面一一对应，values可以插入几组组

INSERT 
INTO <表名>  [(<属性列1> [,<属性列2>…  )]
子查询;

关于修改数据

UPDATE  <表名>
SET  <列名>=<表达式>[,<列名>=<表达式>]… 
[WHERE <条件>];

关于删除数据

DELETE FROM     <表名> 
[WHERE <条件>];

关于视图

创建视图
CREATE  VIEW <视图名>  [(<列名>  [,<列名>]…)] 
AS  <子查询> 
[WITH  CHECK  OPTION];

属性名全部省略或者指定
啥时候全部指定？

  1. 某个目标列是聚集函数或列表达式
  2. 多表连接时选出了几个同名列作为视图的字段
  3. 需要在视图中为某个列启用新的更合适的名字

“关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。”

视图不保存数据，只记录select语句，当使用的时候，再执行select语句。

删除视图
DROP  VIEW  <视图名>[CASCADE];

“删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除”
“如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除 ”

关于创建视图时的with check option选项

with check option参数会对添加数据产生限制，如果不符合创建视图时的where，将不能添加。
这里有个差异关于标准sql和tsql：
标准sql会

带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS’的条件。

带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS’的条件。

而tsql直接不能插入。
标准sql中，向视图里插入数据会自动补充一个信息：就是创建视图的那个条件。

关于数据库安全性

授予权限
GRANT <权限>[,<权限>]... 
ON <对象类型> <对象名>[,<对象类型> <对象名>]… 
TO <用户>[,<用户>]... 
[WITH GRANT OPTION];
WITH GRANT OPTION子句: 指定：可以再授予 没有指定：不能传播
收回权限
REVOKE <权限>[,<权限>]... 
ON <对象类型> <对象名>[,<对象类型><对象名>]… 
FROM <用户>[,<用户>]...[CASCADE | RESTRICT];
角色
1.角色的创建 
CREATE  ROLE  <角色名> 
2.给角色授权 
GRANT  <权限>[,<权限>]… 
ON <对象类型>对象名 
TO <角色>[,<角色>]…
3.
将一个角色授予其他的角色或用户 
GRANT  <角色1>[,<角色2>]… 
TO  <角色3>[,<用户1>]… 
[WITH ADMIN OPTION]

指定了WITH ADMIN OPTION则获得某种权限的角色或用户还可以把这种权限
授予其他角色

4.角色权限的收回 
REVOKE <权限>[,<权限>]… 
ON <对象类型> <对象名> 
FROM <角色>[,<角色>]…
敏感度
敏感度标记（Label）
 对于主体和客体，DBMS为它们每个实例（值）指派一个敏感度标记（Label）
 敏感度标记分成若干级别
绝密（Top Secret，TS）
机密（Secret，S）
可信（Confidential，C）
公开（Public，P）
TS>=S>=C>=P
主体的敏感度标记称为许可证级别（Clearance Level）
客体的敏感度标记称为密级（Classification Level）

规则：主体（操作者）的密级别=客体的密集时，可读可写。
主体>客体，只读不写。
主体<客体，只写不读。

审计

审计就是把对应表或者数据库别的元素的所进行的操作保存下来，然后人或者机器通过这个文件，观察这些操作，找出是否存在可疑行为。

AUDIT语句和NOAUDIT语句
audit alter ,update
on sc

noaudit alter ,update
on sc

完整性

有实体完整性，参照完整性，用户自定义完整性。
下面的例子包括了 实体和参照，以及显示处理违规操作。

create Table SC2
(Sno CHAR(9) NOT NULL, 
Cno CHAR(4)  NOT NULL,  
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),   

FOREIGN KEY (Sno) REFERENCES Student(Sno)
on update cascade
on delete cascade, 

FOREIGN KEY (Cno) REFERENCES Course(Cno)    
on update cascade
on delete no action, --拒绝
);
参照完整性的违约处理

在这里插入图片描述

用户定义的完整性

这里分为属性上约束还有元组的约束。
列上的：not null,unique,check
元组上的：check
关于not null和unique，我们之前就做过例子，在这里不做示题
关于check
例子：

create Table Student3
(Sno char(9) PRIMARY KEY,
 Sname char(9) NOT NULL,
 Ssex char(2) check(Ssex IN('男','女')),
 Sage smallint,
 Sdept char(20),
 check (Ssex='女' OR Sname NOT like 'Mr.%')
 );
完整性约束命名子句
CONSTRAINT <完整性约束条件名><完整性约束条件>

注意是子句，他也是嵌入到create里面写的。其实也就是多了一个关键字和一个你自己命名的名字。
这是列级的

例如：

 create Table SC4
(Sno CHAR(9) NOT NULL, 
Cno CHAR(4)  NOT NULL,  
Grade SMALLINT check (Grade>=0 AND Grade<=100),

constraint SC_PK PRIMARY KEY (Sno, Cno),   
constraint SC_FK1  FOREIGN KEY (Sno) REFERENCES Student(Sno),
constraint SC_FK2  FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
删除约束
alter table Student4
drop constraint Student_PK_Sno;
修改约束

通过先删除旧的，再添加新的方法修改。

alter table Student4
drop constraint Student_PK_Sno;
alter table Student4
add constraint Student_PK_Sno PRIMARY KEY(Sno,Sname);
断言

创建

create assertion ASSE_SC_DB_NUM
check (60>=(select count(*)
		   from course,sc
		   where course.Cno=sc.Cno AND course.Cname='数据库'));

删除

drop assertion asse_name

触发器

定义触发器
CREATE TRIGGER语法格式
CREATE TRIGGER <触发器名> 
{BEFORE | AFTER} <触发事件> ON <表名> 
REFERENCING NEW|OLD ROW AS<变量> FOR EACH  {ROW | STATEMENT} 
[WHEN <触发条件>]<触发动作体>
删除触发器
DROP TRIGGER <触发器名> ON <表名>;

存储过程

创建

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) 
AS
 <过程化SQL块>；

执行

CALL/PERFORM  PROCEDURE
 过程名([参数1,参数2,...])；

修改

ALTER PROCEDURE 过程名1  RENAME TO 过程名2;

删除

DROP  PROCEDURE 过程名()；

函数

1. 函数的定义语句格式
CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型>  AS <过程化SQL块>;
2. 函数的执行语句格式
CALL/SELECT 函数名 ([参数1,参数2,…]);
3. 修改函数
重命名
ALTER FUNCTION 过程名1 RENAME TO 过程名2;
重新编译
ALTER FUNCTION 过程名 COMPILE;

~

感觉脑子还是不是很清楚（可能是标准sql和tsql差异有点小多），还是需要继续的复习。
难点感觉在于查询（主要是用exist实现全称和蕴含那个），还有存储过程，存储过程就类似函数（没有返回值的函数，就是执行这个过程，主要是语法和之前学的语言差异很大）；还有触发器，触发器就是自动执行，类似qt里面的槽函数。

本篇完。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库

【数据库实验】sql总结 的相关文章

  • Leetcode【DFS BFS】

    Leetcode 200 岛屿数量 题目 解题 思路 DFS解法 BFS解法 题目 给你一个由 1 陆地 和 0 水 组成的的二维网格 请你计算网格中岛屿的数量 岛屿总是被水包围 并且每座岛屿只能由水平方向和 或竖直方向上相邻的陆地连接形成

  • ES6 method写法与TypeError: is not a constructor

    公司前端最近开始强推ESlint 很多文件需要逐步修改为符合ESlint规则的形式 结果遇到了一个神奇的问题 有一段类似这样的代码 let obj init function el 此处ESlint检查提示 Expect method sh

随机推荐

  • k8s部署tomcat及web应用_在k8s部署tomcat

    小试牛刀 准备编排文件tomcat yaml 包含两部分 副本rc和service配置可为两个文件 不过我们此处合并为一个 rc副本相关 apiVersion extensions v1beta1 表示Deployment调度配置 kind

  • Keras默认权值初始化方式

    20230117 在最初使用Keras进行神经网络编程的时候 除了设置神经元个数 层数 或者激活函数之后 基本上对神经网络内部就不怎么管了 所以最后很多参数都是默认的 这种情况一般遇到的数据集问题 都能轻易解决 一般不是层数非常深的神经网络

  • 【华为OD统一考试A卷

    华为OD统一考试A卷 B卷 新题库说明 2023年5月份 华为官方已经将的 2022 0223Q 1 2 3 4 统一修改为OD统一考试 A卷 和OD统一考试 B卷 你收到的链接上面会标注A卷还是B卷 请注意 根据反馈 目前大部分收到的都是

  • Kali系统(Debian 10.3) 遇到的问题

    目录 问题一 Kali系统 相关技术网站 博客 文章 论坛 工具包 包跟踪 提交BUG 问题二 黑客入门 手痒地方 问题三 Kali系统 MySQL问题Can t connect to local MySQL server through

  • 边缘计算操作系统安装及测试实验报告

    边缘计算操作系统安装及测试 一 实验目的 二 实验环境 三 实验原理 1 系统组成部分 2 总体数据流程 四 实验步骤及结果 1 安装 Docker 和 Docker Compose 2 下载 EdgeX compose 文件 3 运行Ed

  • qt中clicked(bool checked)和toggled(bool checked)的区别

    先来看qt文档的解释 上面看出 共同点是 当点击按钮时 状态信号都会被发送 不同点 clicked this signal is not emitted if you call setDown setChecked or toggle to

  • 5年测试面试要20K，面试三个问题把我打发走了···

    都说金三银四 金九银十跳槽涨薪季 我是着急忙慌的准备简历 5年软件测试经验 可独立测试大型产品项目 熟悉项目测试流程 薪资要求 5年测试经验起码能要个20K吧 我加班肝了一页半简历 投出去一周 面试电话倒是不少 自信满满去面试 现场被问了这

  • Nmap源码分析（服务与版本扫描）

    Nmap源码分析 服务与版本扫描 2012年8月23日 在进行端口扫描后 Nmap可以进一步探测出运行在端口上的服务类型及应用程序的版本 目前Nmap可以识别几千种服务程序的签名 Signature 覆盖了180多种应用协议 比如 端口扫描

  • java写后端接口中mapper的一些操作

    文章目录 Mybatis Mapper的动态SQL语句问题 一 if 二 choose when otherwise 三 where 四 trim 元素来定制 where 元素的功能 五 set 动态地在行首插入 SET 关键字 六 for

  • PTA 7-4 统计学生平均成绩与及格人数 (15 分)

    本题要求编写程序 计算学生们的平均成绩 并统计及格 成绩不低于60分 的人数 题目保证输入与输出均在整型范围内 输入格式 输入在第一行中给出非负整数N 即学生人数 第二行给出N个非负整数 即这N位学生的成绩 其间以空格分隔 输出格式 按照以

  • C语言函数大全-- y 开头的函数

    y 开头的函数 1 yperror 1 1 函数说明 1 2 演示示例 2 yp match 2 1 函数说明 2 2 演示示例 3 y0 零阶第二类贝塞尔函数 3 1 函数说明 3 2 演示示例 3 3 运行结果 4 y1 一阶第二类贝塞

  • 在Vue中使用flex布局 echarts多图标不能自适应缩放问题

    前言 最近有个项目需要用到echarts绘制多个图表 需求是要支持大屏展示 还有需要支持不同比例的缩放和任意手动缩放 因此 深入学习了echarts和flex布局 虽然遇到很多问题 但都一一解决了收获良多 故此写下遇到的问题与坑 与之共勉

  • go 进阶 多路复用支持: 二. Accept/Read/Write

    目录 一 通过httpServer服务端引用Accept 二 Listener Accept 等待连接 三 Conn Read读数据 Conn Write写数据 四 gopark 阻塞 五 netpoll 唤醒等待队列中挂起的协程 什么时候

  • C#桌面应用程序打包

    使用微软的技术开发windows桌面应用程序是很快捷方便的 开发完之后肯定要打包安装才能发布 以前有做过但过长时间没有打包一下子还真有些遗忘 今天专门又重温了一些 干脆写下来算是加深些印象 以后需要时也好有个参考 感觉有很多技术上手都没有太

  • std::bind可以绑定成员变量

    include

  • java student类_java定义一个Student类，包含内容如下

    展开全部 public class Student 成员变量 学号 姓名 性别 班干部否 数学 语文 外语 成62616964757a686964616fe58685e5aeb931333337613166员方法 输入 总分 平均分 编程实

  • MeterSphere实践指南汇总，搬砖党

    闲来无事 整理了MeterSphere实践指南 我司用了MeterSphere一段时间 感觉挺好用 百度网盘下载链接 链接 https pan baidu com s 1s8sAuz31lgnvTRTLkWZuiQ pwd 98bg 提取码

  • 我的算法笔记（1）——冒泡排序

    我的算法笔记 1 冒泡排序 排序是指将一个无序序列按某个规则进行有序排列 而冒泡排序是排序算法中最基础的一种 现给出一个序列a 其中元素的个数为n 要求将他们按从小到大的顺序排序 冒泡排序的本质在于交换 即每次通过交换的方式把当前剩余元素的

  • BP神经网络阈值

    在基于神经网络的数据融合文章里 有改进权值和阈值 但是没有说清阈值到底是什么 神经网络是模仿大脑的神经元 当外界刺激达到一定的阈值时 神经元才会受到刺激 影响下一个神经元 简单来说 超过阈值 就会引起某一变化 不超过阈值 无论是多少 都不产

  • 【数据库实验】sql总结

    首先说明 以下大部分针对的是标准sql 目录 结构 关键词 关于模式 创建模式 删除模式 关于表 创建表 修改表 删除表 关于索引 建立索引 修改索引 删除索引 关于查询 几个点 指定列 全部列 经过计算的值 列的别名 方便查看 以及聚集函

热门标签