精华内容
下载资源
问答
  • sql删除元组
    2020-11-21 14:31:50

    1766c1369f6f055fe3ae5f2cf17a5991.png
    本专栏是对CMU15445的笔记的翻译,原文地址: https:// 15445.courses.cs.cmu.edu /fall2020/notes/02-advancedsql.pdf

    1 关系语言

    在20世纪70年代,Edgar Codd发布了一篇关于关系模型的论文。最初,他只定义了数据库在关系模型上运行查询的数学符号。

    用户只需要使用声明性的语言指定他们想要的结果(例如SQL)。数据库有责任去决定最有效率的计划并返回结果。

    关系代数基于set(无序,没有重复),SQL基于bag(无序,有重复)。

    2 SQL历史

    用于关系型数据库的声明式查询语言,最初是在20世纪70年代作为IBM System R项目的一部分开发的。IBM最初称为“SEQUEL”(Structured English Query Language 结构化英文查询语言)。在20世纪80年代改名为“SQL”(Structured Query Language 结构化查询语言)。

    这个语言由不同种类的命令组成:

    1. Data Manipulation Language (DML 数据操纵语言):SELETE(查询),INSERT(插入),UPDATE(更新)和DELETE(删除)。
    2. Data Definition Language (DDL 数据定义语言):定义表,索引,视图和另外对象的模式。
    3. Data Control Language (DCL 数据控制语言):安全,访问控制。

    SQL不是一个死(一成不变)的语言,每隔几年就会添加新功能。SQL-92标准是一个数据库支持SQL的最低要求。每个供应商都在一定程度上遵循标准,但同时他们也有很多自己的扩展语法。

    3 聚合

    聚合函数接受一批元组作为输入并产生单个标量作为输出。聚合函数只能用于SELECT的输出列。

    例子:获取学生用“@cs”登录的数量。下列3个查询是等价的:

    SELECT 

    可以使用多个聚合函数在一个SELECT语句块中:

    SELECT 

    一些聚合函数支持DISTINCT关键字:

    SELECT 

    同时输出聚合函数列和非聚合函数列是未定义行为(e.cid在这里是未定义行为)

    注:未定义行为指该行为标准中未定义,不同数据库可能会返回不同的结果(甚至可能会报错)

    SELECT 

    因此,其他非聚合函数的列必须使用GROUP BY命令进行聚合:

    SELECT 

    HAVING:聚合后进行过滤,像WHERE子句中使用GROUP BY过滤这样

    SELECT 

    4 字符串操作

    SQL标准规定字符串区分大小写只能使用单引号。有些函数可以在查询的任何地方使用并操纵字符串。

    Pattern Matching(模式匹配):LIKE关键字被用于在谓词中的字符串匹配。

    • “%” 匹配任何子串(包括空串)。
    • “_” 匹配单个字符。

    Concatenation(连接):两个竖线(“||”)会连接2个或多个字符串变成1个字符串。

    5 输出重定向

    除了可以直接给用户返回结果,你还可以告诉数据库存储结果到另外一张表,然后你可以在后续查询中访问这些数据。

    • New Table(新表):存储查询的输出到新表中。
    SELECT 
    • Existing Table(已存在的表):存储查询的输入到已经存在表中。目标表必须有相同的列数和相同的类型,列名可以和输出列不匹配。
    INSERT 

    6 输出控制

    由于SQL是无序的,你必须使用ORDER BY子句去对结果进行排序:

    SELECT 

    你可以使用多个ORDER BY子句做更复杂的排序:

    SELECT 

    你可以使用任意的表达式在ORDER BY子句中:

    SELECT 

    通常,数据库会返回所有符合条件的元组。你可以使用LIMIT子句去限制返回元组的数量:

    SELECT 

    同时也可以提供offset去获得一个结果的区间:

    SELECT 

    除非你使用一个ORDER BY子句搭配LIMIT,否则数据库可能在每次查询会返回不同的元组,因为关系模型不在意顺序。

    7 嵌套查询

    嵌套查询可以使单个查询中运行更复杂的查询。外部查询的作用域在内部查询中(内部查询可以访问外部查询的属性),反之不行。

    内部查询可以出现在查询的几乎所有地方:

    1. SELECT输出目标:
    SELECT 

    2. FROM子句:

    SELECT 

    3. WHERE子句:

    SELECT 

    例子:获取所有报名“15-445”的学生名字

    SELECT 

    注意,根据sid出现的不同位置,它拥有不同的作用域。

    Nest Query Results Expressions(嵌套查询表达式):

    • ALL:必须满足子查询中所有的行
    • ANY:必须满足子查询中至少1行
    • IN:等价于ANY()
    • EXISTS:至少1行被返回

    8 窗口函数

    跨元组执行“移动”计算。和聚合一样,但它依然返回原始元组。

    Functions(函数):窗口函数可以是上面讨论的任意的聚合函数。同时还有一些特殊的窗口函数:

    1. ROW_NUMBER:当前行的number
    2. RANK:当前行的排序

    Grouping(分组):OVER子句指定当计算窗口函数的时候如何分组。使用PARTITION BY去指定分组。

    SELECT 

    你也可以在OVER中使用ORDER BY去确定结果的顺序,即使数据库内部存储结构发生改变也不会影响结果。

    SELECT 

    重点:RANK函数在窗口函数排序后计算,ROW_NUBMER函数在排序前计算。

    9 公用表表达式(CTE)

    公用表表达式(CTE)是窗口函数和嵌套查询的替代方法去编写更加复杂的查询。可以认为CTE是在单次查询的一个临时表。

    WITH子句将内部查询的输出绑定到临时的结果。

    例子:生成一个CTE叫cteName包含1个tuple的1个属性,属性值为1。这个查询返回了cteName的所有属性及值。

    WITH 

    你可以绑定输出列的名字在AS之前:

    WITH 

    单个查询可以包括多个CTE的定义:

    WITH 

    在WITH后添加RECURSIVE关键字允许CTE引用自己。

    例子:打印1到10的序列

    WITH 
    更多相关内容
  • sql删除元组_SQL笔记

    千次阅读 2020-11-21 01:42:12
    SQL笔记MARCUS_YANGSQL基本概念SQL(Structured Query Language)结构化查询语言,是关系数据库的标准语言特点:SELECT Sno, Grade FROM SC WHERE Cno= '3' ORDER BY Grade DESC; //从表 SC 中选取学号 Sno 和成绩 ...

    SQL笔记

    MARCUS_YANG

    SQL基本概念

    SQL(Structured Query Language)结构化查询语言,是关系数据库的标准语言特点:

    SELECT Sno, Grade
    FROM SC
    WHERE Cno= '3'
    ORDER BY Grade DESC;
    //从表 SC 中选取学号 Sno 和成绩 Grade 满足条件课程号 Cno=“3”按照成绩 Gradel 降序排列
    
    • 综合统一:
      • 集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)功能于一体。
      • 定义和修改、删除关系模式,定义和删除视图,插入数据,建立数据库;
      • 对数据进行增删改查
      • 数据库的重构和维护
      • 数据库安全性、完整性控制,以及事务控制
      • 嵌入式和动态SQL定义
    • 高度非过程化:SQL 只要提出“做什么”,无须了解存取路径。
    • 面向集合的操作方式:
      • 非关系数据模型采用面向记录的操作方式,操作对象是一条记录
      • 采用集合操作方式
      • 操作对象、查找结果可以是元组的集合
      • 一次插入、删除、更新操作的对象可以是元组的集合
    • 以同一种语法结构提供多种使用方式:独立使用和嵌入高级语言中
    • 语言简洁,易学易用

    5ef9104ad4c4cf8b9fc5482226885ef5.png
    • SQL支持关系数据库三级模式结构:

    38364e68b53b4344b616a4729cc65afc.png
      • 基本表:
        • 本身独立存在的表
        • SQL 中一个关系就对应一个基本表
        • 一个(或多个)基本表对应一个存储文件
        • 一个表可以带若干索引
      • 存储文件:
        • 逻辑结构组成了关系数据库的内模式
        • 物理结构对用户来说是隐蔽的
      • 视图:
        • 用户可以在视图上再定义视图
        • 视图是一个虚表
        • 数据库中只存放视图的定义而不存放视图对应的数据
        • 从一个或几个基本表导出的表

    贯彻这门课的三个样例表:

    student table:

    SNO 学号Sname 姓名Ssex 性别Sage 年龄Sdept 系17150011001MarcusMA21CS

    Course表

    Cno课程号Cname 课程名Cpno 先修课Ccredit 学分1数据库542数学NULL23信息系统144操作系统635数据结构746数据处理NULL27PASCAL64

    学生课程表SC

    Sno 学号Cno 课程号Grade 成绩171500110011100


    数据定义

    SQL 的数据定义功能:定义各种数据库的“对象”

    • 模式定义
    • 表定义
    • 视图定义
    • 索引定义
    • [ ]

    4752b0d5cec7a197b2b0205f179a52ac.png

    定义模式:

    example: 定义模式实际上定义了一个命名空间(或者说目录)

    [例3.1] 为用户WANG定义一个学生-课程模式S-T

    CREATE SCHEMA “S-T” AUTHORIZATION WANG;

    [例3.2] CREATE SCHEMA AUTHORIZATION WANG;

    该语句没有指定<模式名,<模式名隐含为<用户名

    • 在 CREATE SCHEMA 中可以接受 CREATETABLE CREATE VIEW 和 GRANT 子句。
    • [例3.3]为用户ZHANG创建了一个模式TEST,并且在其中定义一个表 TAB1CREATE SCHEMA TEST AUTHORIZATION ZHANGCREATE TABLE TAB1(COL1 SMALLINT,
      COL2 INT,
      COL3 CHAR(20),
      COL4 NUMERIC(10,3),
      COL5 DECIMAL(5,2)
      );

    删除模式

    DROP SCHEMA <模式名<CASCADE|RESTRICT

    • CASCADE删除模式的同时把该模式中所有的数据库对象全部删除
    • RESTRICT如果该模式中定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。仅当该模式中没有任何下属的对象时才能执行。

    [例3.4] DROP SCHEMA ZHANG CASCADE;

    删除模式ZHANG

    同时该模式中定义的表TAB1也被删除

    基本表的定义

    6c84fb53de0acc7f107f2f2db6fd16dc.png
    • 列级完整性约束条件:涉及相应属性列的完整性约束条件
    • 表级完整性约束条件:涉及一个或多个属性列的完整性约束条件
    • 如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上。

    [例3.5] 建立“学生”表Student。学号是主码,姓名取值唯一。

    CREATE TABLE Student
     (Sno CHAR(9) PRIMARY KEY,
     /* 列级完整性约束条件,Sno是主码*/
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    );
    

    [例3.6 ] 建立一个“课程”表Course

    CREATE TABLE Course
     (Cno CHAR(4) PRIMARY KEY,
    Cname CHAR(40),
    Cpno CHAR(4),/*先修课*/
    Ccredit SMALLINT,
    FOREIGN KEY (Cpno) REFERENCES Course(Cno)/*Cpno是外码,被参照表是Course,被参照列是Cno*/
    );
    

    [例3.7] 建立一个学生选课表SC

    CREATE TABLE SC
     (Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno,Cno),/* 主码由两个属性构成,必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student(Sno),/* 表级完整性约束条件,Sno是外码,被参照表是Student */
    FOREIGN KEY (Cno)REFERENCES Course(Cno)/* 表级完整性约束条件, Cno是外码,被参照表是Course*/
    );
    

    数据类型

    关系模型中“域”的概念:

    MYSQL:https://www.runoob.com/mysql/mysql-data-types.html

    作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

    类型大小范围(有符号)范围(无符号)用途TINYINT1 byte(-128,127)(0,255)小整数值SMALLINT2 bytes(-32 768,32 767)(0,65 535)大整数值MEDIUMINT3 bytes(-8 388 608,8 388 607)(0,16 777 215)大整数值INT或INTEGER4 bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值BIGINT8 bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值FLOAT4 bytes(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度 浮点数值DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度 浮点数值DECIMAL对DECIMAL(M,D) ,如果MD,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值


    日期和时间类型

    表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

    每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

    TIMESTAMP类型有专有的自动更新特性,将在后面描述。

    类型大小 ( bytes)范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳


    字符串类型

    字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

    类型大小用途CHAR0-255 bytes定长字符串VARCHAR0-65535 bytes变长字符串TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串TINYTEXT0-255 bytes短文本字符串BLOB0-65 535 bytes二进制形式的长文本数据TEXT0-65 535 bytes长文本数据MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215 bytes中等长度文本数据LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据LONGTEXT0-4 294 967 295 bytes极大文本数据

    注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。

    修改基本表

    • ALTER TABLE <表名
      • [ADD[COLUMN] <新列名 <数据类型 [ 完整性约束 ] ]
      • [ADD <表级完整性约束]
      • [DROP [ COLUMN ] <列名 [CASCADE| RESTRICT] ]
      • [DROP CONSTRAINT<完整性约束名[ RESTRICT | CASCADE ] ]
      • [ALTER COLUMN <列名<数据类型 ] ;
      • ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
      • DROP COLUMN子句用于删除表中的列
        • CASCAD:则自动删除引用了该列的其他对象
        • RESTRICT:如果该列引用了其他对象,则关系数据库解决删除
      • DROP CONSTRAINT子句用于删除指定的完整性约束条件
      • ALTER COLUMN子句用于修改原有的列定义,包括修改列名和数据类型
    • [例3.8] 向Student表增加“入学时间”列,其数据类型为日期型
      ALTER TABLE Student ADD S_entrance DATE;
    • [例3.9] 将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
      ALTER TABLE Student ALTER COLUMN Sage INT;[例3.10] 增加课程名称必须取唯一值的约束条件。
      ALTER TABLE Course ADD UNIQUE(Cname);

    删除基本表

    • DROP TABLE <表名[RESTRICT| CASCADE];
      • RESTRICT:删除表是有限制的。
      • CASCADE:删除该表没有限制。
    • [例3.11] 删除Student表
      DROP TABLE Student CASCADE;

    c46ce50daf3eb024ed5cbc8a6fc4f839.png

    索引

    常见的索引:

    • 顺序文件上的索引
    • B+树索引
    • 散列(hash)索引
    • 位图索引

    创建索引

    CREATE [UNIQUE] [CLUSTER] INDEX <索引名

    ON <表名(<列名[<次序][,<列名[<次序] ]…);

    • 升序:ASC(默认)
    • 降序:DESC

    [例3.13] 为学生-课程数据库中的Student,Course,SC三个 表建立索引。Student表按学号升序建唯一索引,Course表 按课程号升序建唯一索引 , SC 表按学号升序和课程号降序 建唯一索引

    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<旧索引名RENAME TO<新索引名

    例 3.149 将 SC 表的 SCno 索引名改为 Scsno

    ALTER INDEX Scno RENAME TO Scsno

    删除索引

    DROP INDEX <索引名;

    [例3.15] 删除Student表的Stusname索引

    DROP INDEX Stusname;


    数据查询_1

    单表查询

    一共有 6 个子句

    3b3e58dac3f9ee72694c4ef4180129e5.png
    • SELECT选择基本表的行和列 ,初始状态下不去重复,
      • DISTINC 关键字可以去重
    • Group BY-HAVING (聚集函数)
    • order by 排序

    SELECT子句

    [例3.16] 查询全体学生的学号与姓名。

    SELECT Sno,Sname
    FROM Student;
    

    [例3.18] 查询全体学生的详细记录

    SELECT Sno,Sname,Ssex,Sage,Sdept
    FROM Student;
    或
    SELECT *
    FROM Student;
    

    查询计算表达式:

    [例3.19] 查全体学生的姓名及其出生年份

    SELECT Sname,2020-Sage
    FROM Student;
    

    [例3.20] 查询全体学生的姓名、出生年份和所在的院系,要 求用小写字母表示系名。

    SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)
    FROM Student;
    

    使用列别名改变查询结果的列标题:

    SELECT Sname NAME,'Year of Birth:' BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
    FROM Student;
    

    [例3.21] 查询选修了课程的学生学号。

    SELECT DISTINCT Sno
    FROM SC;
    

    bcea7f020858094ec1b5dc47c8b48c07.png

    [例3.22] 查询计算机科学系全体学生的名单。

    SELECT Sname
    FROM Student
    WHERE Sdept=‘CS’;

    [例3.23]查询所有年龄在20岁以下的学生姓名及其年龄。

    SELECT Sname,Sage
    FROM Student
    WHERE Sage < 20;

    [例3.24]查询考试成绩有不及格的学生的学号。

    SELECT DISTINCT Sno
    FROM SC
    WHERE Grade<60;

    [例3.25] 查询年龄在20~23岁(包括20岁和23岁)之间的学生 的姓名、系别和年龄

    SELECT Sname, Sdept, Sage
    FROM Student
    WHERE Sage BETWEEN 20 AND 23;

    [例3.26] 查询年龄不在20~23岁之间的学生姓名、系别和年龄

    SELECT Sname, Sdept, Sage
    FROM Student
    WHERE Sage NOT BETWEEN 20 AND 23;
    

    [例3.27]查询计算机科学系(CS)、数学系(MA)和信息 系(IS)学生的姓名和性别。

    SELECT Sname, Ssex
    FROM Student
    WHERE Sdept IN ('CS','MA’,'IS' );
    

    [ 例 3.28] 查询既不是计算机科学系 、 数学系 , 也不是信息系 的学生的姓名和性别。

    SELECT Sname, Ssex
    FROM Student
    WHERE Sdept NOT IN ('CS','MA’,'IS' );
    

    字符串匹配:

    %:如a%b表示以a开头,以b结尾的任意长度的字符串

    _:a_b表示以a开头,以b结尾的长度为3的任意字符串

    [例3.29] 查询学号为201215121的学生的详细情况。

    SELECT *
    FROM Student
    WHERE Sno LIKE ‘201215121';//WHERE Sno = ' 201215121 ';
    

    [例3.30] 查询所有刘某学生的姓名、学号和性别。

    SELECT Sname, Sno, Ssex
    FROM Student
    WHERE Sname LIKE '刘%';
    

    [例3.31] 查询姓"欧阳"且全名为三个汉字的学生的姓名。

    SELECT Sname
    FROM Student
    WHERE Sname LIKE '欧阳__';
    

    [例3.32] 查询名字中第2个字为"阳"字的学生的姓名和学号。

    SELECT Sname,Sno
    FROM Student
    WHERE Sname LIKE '__阳%';
    

    [例3.33] 查询所有不姓刘的学生姓名、学号和性别。

    SELECT Sname, Sno, Ssex
    FROM Student
    WHERE Sname NOT LIKE '刘%';
    

    [例3.34] 查询DB_Design课程的课程号和学分。

    SELECT Cno,Ccredit
    FROM Course
    WHERE Cname LIKE 'DB_Design' ESCAPE ' ' ;
    

    _转义表示_

    [例3.35] 查询以"DB_"开头,且倒数第3个字符为 i的课程的 详细情况。

    SELECT *
    FROM Course
    WHERE Cname LIKE 'DB_%i_ _' ESCAPE ' ' ;
    

    [例3.36] 某些学生选修课程后没有参加考试,所以有选课记 录,但没 有考试成绩。查询缺少成绩的学生的学号和相应 的课程号。

    SELECT Sno,Cno
    FROM SC
    WHERE Grade IS NULL
    
    

    [例3.37] 查所有有成绩的学生学号和课程号。

    SELECT Sno,Cno
    FROM SC
    WHERE Grade IS NOT NULL;
    
    

    [例3.38] 查询计算机系年龄在20岁以下的学生姓名。

    SELECT Sname
    FROM Student
    WHERE Sdept= 'CS' AND Sage<20;
    
    

    [例3.27] 查询计算机科学系(CS)、数学系(MA)和信息系 (IS)学生的姓名和性别。

    SELECT Sname, Ssex
    FROM Student
    WHERE Sdept IN ('CS','MA’,'IS' );
    
    

    [ 例 3.28] 查询既不是计算机科学系 、 数学系 , 也不是信息系 的学生的姓名和性别。

    SELECT Sname, Ssex
    FROM Student
    WHERE Sdept NOT IN ('IS','MA’,'CS' );
    
    

    [例3.29] 查询学号为201215121的学生的详细情况。

    SELECT *
    FROM Student
    WHERE Sno LIKE ‘201215121';
    
    

    [例3.30] 查询所有姓刘学生的姓名、学号和性别。

    SELECT Sname, Sno, Ssex
    FROM Student
    WHERE Sname LIKE '刘%';
    
    

    [例3.31] 查询姓"欧阳"且全名为三个汉字的学生的姓名。

    SELECT Sname
    FROM Student
    WHERE Sname LIKE '欧阳__';
    
    

    [例3.32] 查询名字中第2个字为"阳"字的学生的姓名和学号。

    SELECT Sname,Sno
    FROM Student
    WHERE Sname LIKE '__阳%';
    
    

    [例3.33] 查询所有不姓刘的学生姓名、学号和性别。

    SELECT Sname, Sno, Ssex
    FROM Student
    WHERE Sname NOT LIKE '刘%';
    
    

    [例3.34] 查询DB_Design课程的课程号和学分。

    SELECT Cno,Ccredit
    FROM Course
    WHERE Cname LIKE 'DB_Design' ESCAPE ' ' ;
    
    

    [例3.35] 查询以"DB_"开头,且倒数第3个字符为 i的课程的 详细情况。

    SELECT *
    FROM Course
    WHERE Cname LIKE 'DB_%i_ _' ESCAPE ' ' ;
    
    

    [例3.36] 某些学生选修课程后没有参加考试,所以有选课记 录,但没 有考试成绩。查询缺少成绩的学生的学号和相应 的课程号。

    SELECT Sno,Cno
    FROM SC
    WHERE Grade IS NULL //IS”不能用“=”代替
    
    

    [例3.37] 查所有有成绩的学生学号和课程号。

    SELECT Sno,Cno
    FROM SC
    WHERE Grade IS NOT NULL;
    
    

    [例3.38] 查询计算机系年龄在20岁以下的学生姓名。

    SELECT Sname
    FROM Student
    WHERE Sdept= 'CS' AND Sage<20;
    
    

    ORDER BY 子句

    可以按一个或多个属性列排序

    升序:ASC;降序:DESC;缺省值为升序

    [例3.39]查询选修了3号课程的学生的学号及其成绩,查询结 果按分数降序排列。

    SELECT Sno, Grade
    FROM SC
    WHERE Cno= ' 3 '
    ORDER BY Grade DESC;
    
    

    [例3.40]查询全体学生情况,查询结果按所在系的系号升序 排列,同一系中的学生按年龄降序排列。

    SELECT *
    FROM Student
    ORDER BY Sdept, Sage DESC;
    
    

    聚集函数

    • DISTINCT 去重
    • ALL 所有(默认)
    • 统计元组个数COUNT(*)
    • 统计一列中值的个数COUNT([DISTINCT|ALL] <列名)
    • 计算一列值的总和SUM([DISTINCT|ALL] <列名)
    • 计算一列值的平均值AVG([DISTINCT|ALL] <列名)
    • 求一列中的最大值和最小值:
      • MAX([DISTINCT|ALL] <列名)
      • MIN([DISTINCT|ALL] <列名)

    [例3.41] 查询学生总人数。

    SELECT COUNT(*)
    FROM Student;
    
    

    [例3.42] 查询选修了课程的学生人数。

    SELECT COUNT(DISTINCT Sno)
    FROM SC;
    
    

    [例3.43] 计算1号课程的学生平均成绩。

    SELECT AVG(Grade)
    FROM SC
    WHERE Cno= ' 1 ';
    
    

    [例3.44] 查询选修1号课程的学生最高分数。

    SELECT MAX(Grade)
    FROM SC
    WHERE Cno='1';
    

    [例3.45 ] 查询学生201215012选修课程的总学分数。

    SELECT SUM(Ccredit)
    FROM SC,Course
    WHERE Sno='201215012' AND SC.Cno=Course.Cno;
    

    GROUP BY 子句:细化聚集函数的作用对象

    [例3.46] 求各个课程号及相应的选课人数。

    SELECT Cno,COUNT(Sno)
    FROM SC
    GROUP BY Cno;
    

    7dc6c302e0dcd9397b3d3ddf7e7afe56.png

    [例3.47] 查询选修了3门以上课程的学生学号。

    SELECT Sno
    FROM SC
    GROUP BY Sno HAVING COUNT(Cno) 3;
    

    [例3.48 ]查询平均成绩大于等于90分的学生学号和平均成绩

    SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno HAVING AVG(Grade)=90;
    

    HAVING 短语与 WHERE 子句的区别:

    • 作用对象不同
    • WHERE 子句作用于基表或视图,从中选择满足条件的元组
    • HAVING 短语作用于组,从中选择满足条件的组

    综合练习

    [练习1] 列出计算机系姓刘的同学的信息,按照学号大小 排序

    SELECT *
    FROM Student
    WHERE Sdept=‘CS’ AND Sname LIKE ‘刘%’
    ORDER BY Sno;
    

    [练习2] 按系并区分男女统计各系学生的人数、并按照人 数降序排序

    SELECT Sdept, Ssex,COUNT(Sno)
    FROM Student
    GROUP BY Sdept,Ssex
    ORDER BY COUNT(Sno) DESC;
    

    数据连接

    不像关系代数中“连接”是用一个特殊符号来表达的, 在 SQL中“连接”是用“连接条件”来表达的。

    一般格式:[<表名1.]<列名1 <比较运算符 [<表名2.]<列名2

    • 等值连接
    • 自身连接
    • 外连接
    • 多表连接

    等值连接

    连接运算符为 “=”

    [例 3.49] 查询每个学生及其选修课程的情况

    SELECT Student.*, SC.*
    FROM Student, SC
    WHERE Student.Sno = SC.Sno;
    

    bbc76d3984952b490f5b8a3adad53313.png
    • 嵌套循环法(NESTED-LOOP)
    • 排序合并法(SORT-MERGE)
    • 索引连接(INDEX-JOIN)

    [例 3.51 ]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

    SELECT Student.Sno, Sname
    FROM Student, SC
    WHERE Student.Sno=SC.Sno AND
    SC.Cno=' 2 ' AND SC.Grade90;
    
    

    自身连接

    自身连接:一个表与其自己进行连接,是一种特殊的连接

    !!!需要给表起别名以示区别

    !!!由于所有属性名都是同名属性,因此必须使用别名前缀

    [例 3.52]查询每一门课的直接先修课的名称

    SELECT FIRST.Cname , SECOND.Cname
    FROM Course FIRST, Course SECOND
    WHERE FIRST.Cpno = SECOND.Cno;
    
    

    71af799156d2de703d6dc0b5e9203ac2.png

    e6e6cf67abfb9c0ef3d9905172ba9e2a.png

    外连接

    • 外连接操作以指定表为连接主体,将主体表中不满足连 接条件的元组一并输出
    • 普通连接操作只输出满足连接条件的元组

    [例 3.49] 查询每个学生及其选修课程的情况

    SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
    FROM Student LEFT OUT JOIN SC ON
    (Student.Sno=SC.Sno);
    //或
    SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
    FROM Student,SC
    WHERE Student.Sno(+)=SC.Sno;
    
    

    多表连接

    [例3.54]查询每个学生的学号、姓名、选修的课程名及成绩

    SELECT Student.Sno, Sname, Cname, Grade
    FROM Student,SC,Course /*多表连接*/
    WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
    
    

    嵌套查询

    不相关子查询:

    子查询的查询条件不依赖于父查询

    由里向外逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。

    相关子查询:

    子查询的查询条件依赖于父查询

    带 IN 谓词

    [例 3.55] 查询与“刘晨”在同一个系学习的学生。

    分步法:

    ① 确定“刘晨”所在系名

    SELECT Sdept
    FROM Student
    WHERE Sname= ' 刘晨 ';
    
    

    ② 查找所有在CS系学习的学生。

    SELECT Sno, Sname, Sdept
    FROM  Student
    WHERE Sdept= ' CS ';
    
    

    将第一步查询嵌入到第二步查询的条件中:

    SELECT Sno, Sname, Sdept
    FROM Student
    WHERE Sdept IN
     (SELECT Sdept
    FROM Student
    WHERE Sname= '刘晨'
    );
    
    
    

    自身连接法:

    SELECT S1.Sno, S1.Sname,S1.Sdept
    FROM Student S1,Student S2
    WHERE S1.Sdept = S2.Sdept AND
    S2.Sname = '刘晨';
    
    

    [例 3.56]查询选修了课程名为“信息系统”的学生学号和姓名

    SELECT Sno,Sname  //③ 最后在Student关系中取出Sno和Sname
    FROM Student
    WHERE Sno IN
     (SELECT Sno //② 然后在SC关系中找出选修了3号课程的学生学号
      FROM SC
    WHERE Cno IN
      (SELECT Cno //① 首先在Course关系中找出“信息系统”的课程号,为3号
       FROM Course
       WHERE Cname= '信息系统')
    );
    
    

    或:

    SELECT Sno,Sname
    FROM Student,SC,Course
    WHERE Student.Sno = SC.Sno AND
     SC.Cno = Course.Cno AND
     Course.Cname='信息系统';
    
    

    带有比较运算符的子查询

    [例 3.55] 查询与“刘晨”在同一个系学习的学生。

    SELECT Sno,Sname,Sdept
    FROM Student
    WHERE Sdept=
     (SELECT Sdept/*由于一个学生只可能在一个系学习 , 用=代替*/
    FROM Student
    WHERE Sname= '刘晨');
    
    

    [例 3.57 ]找出每个学生超过他选修课程平均成绩的课程号。(自身嵌套的例子)

    SELECT Sno, Cno
    FROM SC x
    WHERE Grade =(SELECT AVG(Grade)
            FROM SC y
                 WHERE y.Sno=x.Sno);
    
    

    带有 ANY (SOME)或 ALL 谓词的子査询

    • ANY 大于子查询结果中的某个值
    • =ANY 大于等于子査询结果中的某个值
    • !=(或<) ANY 不等于子査询结果中的某个值
    • !=(或<) ALL 不等于子查询结果中的任何一个值

    c8da81f9d43a8c134c25e41d8643d2b7.png

    image-20200430001129003

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

    SELECT Sname,Sage
    FROM Student
    WHERE Sage < ANY (SELECT Sage
                   FROM Student
                   WHERE Sdept= 'CS')
     AND Sdept < 'CS' ;/*父查询块中的条件 */
    
    

    或者使用聚集函数

    SELECT Sname,Sage
    FROM Student
    WHERE Sage <(SELECT MAX(Sage)
              FROM Student
              WHERE Sdept= 'CS')
     AND Sdept < 'CS ';
    

    [例 3.59] 查询非计算机科学系中比计算机科学系所有学 生年龄都小的学生姓名及年龄。

    SELECT Sname,Sage
    FROM Student
    WHERE Sage < ALL(SELECT Sage
                  FROM Student
                  WHERE Sdept= 'CS')
     AND Sdept < 'CS’;
    
    SELECT Sname,Sage
    FROM Student
    WHERE Sage <(SELECT MIN(Sage)
              FROM Student
              WHERE Sdept= 'CS')
     AND Sdept <'CS';
    

    带有EXISTS谓词的子查询

    • 带有EXISTS谓词的子查询产生逻辑真值“true”或逻辑假值“false”。
    • 由 EXISTS引出的子查询,其目标列表达式通常都用 *
    • 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:
    • 可以利用谓词演算将逻辑蕴涵谓词等价转换为:

    [例 3.60]查询所有选修了1号课程的学生姓名。

    SELECT Sname
    FROM Student
    WHERE EXISTS(SELECT *
           FROM SC
           WHERE Sno=Student.Sno AND Cno= ' 1 ');
    

    [例 3.61] 查询没有选修1号课程的学生姓名。

    SELECT Sname
    FROM Student
    WHERE NOT EXISTS(SELECT *
           FROM SC
           WHERE Sno=Student.Sno AND Cno= ' 1 ');
    

    [例 3.55]查询与“刘晨”在同一个系学习的学生。

    SELECT Sno,Sname,Sdept
    FROM Student S1
    WHERE EXISTS(SELECT *
           FROM Student S2
           WHERE S2.Sdept = S1.Sdept 
           AND S2.Sname = '刘晨');
    

    [例 3.62] 查询选修了全部课程的学生姓名。

    SELECT Sname
    FROM Student
    WHERE NOT EXISTS(SELECT *
               FROM Course
               WHERE NOT EXISTS(SELECT *
                                FROM SC
                                WHERE Sno= Student.Sno
                                AND Cno= Course.Cno)
             );
    

    [例 3.63]查询至少选修了学生17150011001选修的全部课程的 学生号码。

    用逻辑蕴涵表达该查询:

    用P表示谓词 “学生201215122选修了课程y”

    用q表示谓词 “学生x选修了课程y”

    则上述查询为:

    SELECT DISTINCT SCX.Sno
    FROM SC SCX
    WHERE NOT EXISTS(SELECT *
                  FROM SC SCY
                  WHERE SCY.Sno = '17150011001' AND
                  NOT EXISTS(SELECT *
                             FROM SC SCZ
                             WHERE SCZ.Sno=SCX.Sno 
                             AND SCZ.Cno=SCY.Cno));
    

    数据查询_2_

    集合查询

    • 并操作UNION
      • UNION:将多个查询结果合并起来时,系统自动去掉重复元组(系统默认)
      • UNION ALL:将多个查询结果合并起来时,保留重复元组
    • 交操作INTERSECT
    • 差操作EXCEPT

    !参加集合操作的各查询结果的列数必须相同;对应 项的数据类型也必须相同

    [例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。

    SELECT *
    FROM Student
    WHERE Sdept= 'CS'
    
    UNION
    
    SELECT *
    FROM Student
    WHERE Sage<=19;
    

    [例 3.65] 查询选修了课程1或者选修了课程2的学生。

    SELECT Sno
    FROM SC
    WHERE Cno=' 1 '
    
    UNION
    
    SELECT Sno
    FROM SC
    WHERE Cno= ' 2 ';
    

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

    SELECT *
    FROM Student
    WHERE Sdept='CS'
    
    INTERSECT
    
    SELECT *
    FROM Student
    WHERE Sage<=19
    
    

    [例 3.66] 实际上就是查询计算机科学系中年龄不大 于19岁的学生。

    SELECT *
    FROM Student
    WHERE Sdept= 'CS' AND Sage<=19;
    
    

    [例 3.67]查询既选修了课程1又选修了课程2的学生。

    SELECT Sno
    FROM SC
    WHERE Cno=' 1 '
    
    INTERSECT
    
    SELECT Sno
    FROM SC
    WHERE Cno='2 ';
    
    
    SELECT Sno
    FROM SC
    WHERE Cno=' 1 ' AND Sno IN(SELECT Sno
                            FROM SC
                            WHERE Cno=' 2 ');
    
    
    //下面是一个典型的错误例子
    SELECT Sno
    FROM SC
    WHERE Cno='1' and Cno='2'
    
    

    [例 3.68] 查询计算机科学系的学生与年龄不大于19岁的 学生的差集。

    SELECT *
    FROM Student
    WHERE Sdept='CS'
    
    EXCEPT
    
    SELECT *
    FROM Student
    WHERE Sage <=19;
    
    
    SELECT *
    FROM Student
    WHERE Sdept= 'CS' AND Sage19;
    
    

    数据更新

    插入数据

    插入元组

    INSERT INTO <表名 [(<属性列1[,<属性列2 …)]

    VALUES (<常量1 [,<常量2]… );

    INTO 子句

    四种情况:

    • 属性列的顺序可与表定义中的顺序不一致
    • 指定要插入数据的表名及属性列
    • 没有指定属性列:表示要插入的是一条完整的元组,且 属性列属性与表定义中的顺序一致
    • 指定部分属性列:插入的元组在其余属性列上取空值

    VALUES子句

    提供的值必须与INTO子句匹配

    [例3.69]将一个新学生元组(学号:201215128;姓名:陈冬; 性别:男;所在系:IS;年龄:18岁)插入到Student表中。

    INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage)
    VALUES ('17150011001','陈扬','男','CS',21);
    

    [例3.71] 插入一条选课记录( '200215128','1 ')。

    INSERT INTO SC(Sno,Cno)
    VALUES ('201215128 ',' 1 ');
    

    或:

    INSERT INTO SC
    VALUES (' 201215128 ',' 1 ',NULL);
    

    [例3.70]将学生张成民的信息插入到Student表中。

    INSERT INTO Student
    VALUES ('201215126','张成民','男’,18,'CS');
    

    在 student 后没有定义每个元素的意义,就必须和表一一对应

    插入子查询结果

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

    [例3.72] 对每一个系,求学生的平均年龄,并把结果存入数据库

    建表:

    CREATE TABLE Dept_age(Sdept CHAR(15)/*系名*/
                          Avg_age SMALLINT);/*学生平均年龄*/
    

    插入数据:

    INSERT INTO Dept_age(Sdept,Avg_age)
     SELECT Sdept,AVG(Sage)
     FROM Student
     GROUP BY Sdept;
    

    插入数据的时候会检测完整性:

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

    修改数据

    UPDATE <表名

    SET <列名=<表达式[,<列名=<表达式]…

    [WHERE <条件];

    三种修改方式:

    • 修改某一个元组的值
    • 修改多个元组的值
    • 带子查询的修改语句

    [例3.73] 将学生201215121的年龄改为22岁

    UPDATE Student
    SET Sage=22
    WHERE Sno=' 201215121 ';
    
    

    [例3.74] 将所有学生的年龄增加1岁。

    UPDATE Student
    SET Sage= Sage+1;
    
    

    [例3.75] 将计算机科学系全体学生的成绩置零。

    UPDATE SC
    SET Grade=0
    WHERE Sno IN(SELETE Sno
                 FROM Student
                 WHERE Sdept= 'CS' );
    
    

    更新数据同样会检测完整性约束

    删除数据

    DELETE FROM <表名

    [WHERE <条件];

    删除指定表中满足WHERE子句条件的元组,无该子句将会删除表中的全部元组

    三种删除方式:

    • 删除某一个元组的值
    • 删除多个元组的值
    • 带子查询的删除语句

    [例3.76] 删除学号为201215128的学生记录。

    DELETE FROM Student
    WHERE Sno= 201215128 ';
    
    

    [例3.77] 删除所有的学生选课记录。

    DELETE FROM SC;
    
    

    [例3.78] 删除计算机科学系所有学生的选课记录。

    DELETE
    FROM SC
    WHERE Sno IN(SELETE Sno
                 FROM Student
                 WHERE Sdept= 'CS') ;
    
    

    空值的处理

    空值不等于 NULL,表示本来应该有一个值,但是现在未知

    判断一个属性的值是否为空值,用IS NULL或IS NOT NULL来表示。

    [例 3.81]找出漏填了性别或者年龄信息的记录

    SELECT *
    FROM Student
    WHERE Ssex IS NULL OR Sage IS NULL ;
    

    约束条件:

    • 有NOT NULL约束条件的不能取空值
    • 加了UNIQUE限制的属性不能取空值
    • 码属性不能取空值

    [例3.82] 找出选修1号课程的不及格的学生。

    SELECT Sno
    FROM SC
    WHERE Grade < 60 AND Cno='1';
    

    [例 3.83] 选出选修1号课程的不及格的学生以及缺考的学生。

    SELECT Sno
    FROM SC
    WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);
    

    视图view

    视图是定义在基本表之上的虚表,是从一个或几个基本表(或视图)导出的表

    • 只存放视图的定义,不存放视图对应的数据
    • 基表中的数据发生变化,从视图中查询出的数据也随之改变

    视图的定义

    CREATE VIEW

    <视图名 [(<列名 [,<列名]…)]

    AS <子查询

    [WITH CHECK OPTION];

    • WITH CHECK OPTION:子查询中的条件表达式约束
    • 子查询可以是任意的SELECT语句
    • 关系数据库管理系统执行 CREATE VIEW 语句时只是把视图定义存入数据字典,并不执行其中的SELECT 语句。
    • 对视图查询时,按视图的定义从基本表中将数据查出。
    • 若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,我们称这类视图为行列子集视图

    [例3.84] 建立信息系学生的视图。

    CREATE VIEW IS_Student
    AS
    SELECT Sno,Sname,Sage
    FROM Student
    WHERE Sdept= 'IS';
    
    

    [例3.85]建立信息系学生的视图,并要求进行修改和插入 操作时仍需保证该视图只有信息系的学生 。

    CREATE VIEW IS_Student
    AS
    SELECT Sno,Sname,Sage
    FROM Student
    WHERE Sdept= 'IS'
    //对该视图进行插入、修改和删除操作会自动加上Sdept='IS'的条件。
    WITH CHECK OPTION;
    
    

    [例3.86] 建立信息系选修了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.87] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。

    在视图上定义视图(套娃)

    CREATE VIEW IS_S2
    AS
    SELECT Sno,Sname,Grade
    FROM IS_S1
    WHERE Grade=90;
    
    

    [例3.88] 定义一个反映学生出生年份的视图。

    CREATE VIEW BT_S(Sno,Sname,Sbirth)
    AS
    SELECT Sno,Sname,2014-Sage
    FROM Student;
    
    

    [例3.89] 将学生的学号及平均成绩定义为一个视图

    [聚集函数]

    CREAT VIEW S_G(Sno,Gavg)
    AS
    SELECT Sno,AVG(Grade)
    FROM SC
    GROUP BY Sno;
    
    

    [例3.90]将Student表中所有女生记录定义为一个视图

    CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
    AS
    SELECT *
    FROM Student
    WHERE Ssex=‘女’;
    //* 有可能导致在后面修改 student 表的时候视图出错
    
    

    删除视图

    DROP VIEW <视图名[CASCADE];

    • 该语句从数据字典中删除指定的视图定义
    • 如果该视图上还导出了其他视图,使用CASCADE级联删除语句
    • 删除基本表时需要DROP由该表导出的视图

    [例3.91 ] 删除视图BT_S和IS_S1

    DROP VIEW BT_S;/*成功执行*/
    
    DROP VIEW IS_S1;/*拒绝执行*/
    DROP VIEW IS_S1 CASCADE;/*成功执行*/
    
    

    查询视图

    • 查询视图与查询基本表相同

    [例3.92] 在信息系学生的视图中找出年龄小于20岁的学生。

    SELECT  Sno,Sage
    FROM IS_Student
    WHERE Sage<20;
    
    

    [例3.93] 查询选修了1号课程的信息系学生

    SELECT IS_Student.Sno,Sname
    FROM IS_Student,SC
    WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1';
    
    

    [例3.94]在S_G视图中查询平均成绩在90分以上的学生学号 和平均成绩

    CREATE VIEW S_G(Sno,Gavg)
    AS
    SELECT Sno,AVG(Grade)
    FROM SC
    GROUP BY Sno;
    
    SELECT *
    FROM S_G
    WHERE Gavg=90;
    

    SELECT *
    FROM (SELECT Sno,AVG(Grade)
          FROM SC
          GROUP BY Sno) 
          AS 
          S_G(Sno,Gavg)
    WHERE Gavg=90;
    

    更新视图

    [例3.95] 将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。

    UPDATE IS_Student
    SET Sname= '刘辰'
    WHERE Sno= ' 201215122 ';
    
    UPDATE Student
    SET Sname= '刘辰'
    WHERE Sno= ' 201215122 ' AND Sdept= 'IS';
    

    [例3.96] 向信息系学生视图IS_Student中插入一个新的学生记录,其中学号为”201215129”,姓名为”赵新”,年龄 为20岁

    INSERT
    INTO IS_Student
    VALUES(‘201215129’,’赵新’,20);
    
    INSERT
    INTO Student(Sno,Sname,Sage,Sdept)
    VALUES('200215129','赵新',20,'IS' );
    

    [例3.97]删除信息系学生视图IS_Student中学号为”201215129”的记录

    DELETE
    FROM IS_Student
    WHERE Sno= ' 201215129 ';
    
    DELETE
    FROM Student
    WHERE Sno= ' 201215129 ' AND Sdept= 'IS';
    

    注意!聚集函数的视图在修改的时候聚集函数那一列是不可更新的

    UPDATE S_G
    SET Gavg=90
    WHERE Sno= ‘201215121’;
    //以上为反例
    
    • 允许对行列子集视图进行更新
    • 两个以上基本表导出视图原则上也不允许更新
    • 若视图的字段来自集函数,则此视图不允许更新。
    • 若视图定义中含有 GROUP BY 子句,则此视图不允许更新。
    • 若视图定义中含有 DISTINCT 短语,则此视图不允许更新 。
    • 若视图定义中有嵌套查询,并且内层查询的子句中涉及的表也是FROM导出该视图的基本表,则此视图不允许更新
    展开全文
  • 关系数据库标准语言SQLSQL概述SQL特点:综合统一,高度非过程化,面向集合的操作方式,以同一种语法结构提供多种使用方式视图是从基本表里面导出的表,是一个虚表,用户可以在视图上再定义视图索引的建立和删除目的...

    关系数据库标准语言SQL

    SQL概述

    • SQL特点:综合统一,高度非过程化面向集合的操作方式,以同一种语法结构提供多种使用方式
    • 视图是从基本表里面导出的表,是一个虚表,用户可以在视图上再定义视图

    索引的建立和删除

    • 目的:加快查询速度
    • 具体实现技术由DBMS来决定
    • 索引可以是:唯一索引,非唯一索引,聚簇索引(前面两个又称普通索引)
    • 在下图中,我们使用普通索引索引来查询,红线下方为数据表,上方类似B+树;使用索引之后,用四个文件块就可以读取到数据,否则只能遍历

    bc7b8f4cd8a8ab4e9ca7400b9363cf46.png
    • 聚簇索引要求数据按照属性来顺序存储,极大加快了按照聚簇值存储的数据的查询速度
    • 一个关系只能有一个聚簇索引,可以有多个普通索引

    数据查询

    • 一个查询块结束用结尾
    • 查询所有属性列*
    • 单表查询SELECT FROM WHERE;
      • 前面三个关键字为在原表操作
      • 聚集函数不可以在WHERE语句中出现

    cc3a38ef71474a53d8dcfa4d1af11701.png
    • HAVING短语与WHERE子句的区别
      • HAVING短语:作用于组,从组中选择满足条件的组
      • WHERE子句:作用于基表或视图,从中选择满足条件的元组,因此不能用聚集函数
    • HAVING短语与GROUP BY短语顺序问题:GROUP BY不能放在HAVING短语中,因为HAVING短语是先要分组,再选择,因此要先用GROUP BY分组,所以将其放在HAVING短语前面
    • 连接查询:
      • 连接操作的执行过程:
        • 嵌套循环法
        • 排序合并法
        • 索引连接法
      • 连接查询:
        • 等值连接和非等值连接查询
        • 自身连接
        • 外连接
        • 多表连接
    • 嵌套查询:
      • 将一个查询块嵌入到另一个查询块的WHERE或者HAVING语句中
      • 内层向外层传播
      • 连接查询和嵌套查询的速度根据不同的系统有不同的速度
      • 不能用ORDER BY子句
      • 嵌套查询求解方法:
        • 不想关子查询
        • 相关子查询
      • 带有IN谓词的子查询
      • 带有比较运算符的子查询
      • 带有ANY(SOME)或者ALL谓词的子查询
      • 带有EXISTS谓词的子查询
        • 只产生逻辑真或假,因此列名无意义,使用*

    数据更新

    • 插入数据
      • 插入元组
      • 插入子查询结果
    • 修改数据
      • 在MySQL中使用让全体学生年龄+1的语句时候,提示安全更新问题?
    • 删除数据

    空值

    • 表示不存在,无意义

    视图

    • 虚表,是一个或几个基本表导出的表
    • 定义视图
    • 查询视图
    • 更新视图
    • 视图的作用
      • 简化用户的操作
      • 对机密数据提供安全保护
      • 更加清晰的表达查询
    展开全文
  • 我们知道postgresql数据库通过数据多版本实现mvcc,pg又没有undo段,老版本的数据元组直接存放在数据页面中,这样带来的问题就是旧元组需要不断地进行清理以释放空间,这也是数据库膨胀的根本原因。本文简单介绍一下...
    57224b66690e4a72dbcb139a23204895.png

    我们知道postgresql数据库通过数据多版本实现mvcc,pg又没有undo段,老版本的数据元组直接存放在数据页面中,这样带来的问题就是旧元组需要不断地进行清理以释放空间,这也是数据库膨胀的根本原因。本文简单介绍一下postgresql数据库的元组、页面的结构以及索引查找流程。

    bc6ac351e7825f700a0e095e7c33b934.png 24d5ed8b8aa98b94dcd335237217e64d.gif

    元组结构

    57224b66690e4a72dbcb139a23204895.png

    元组,也叫tuple,这个叫法是很学术的叫法,但是现在数据库中一般叫行或者记录。下面是元组的结构:

    typedef struct HeapTupleFields{    TransactionId t_xmin;       /* inserting xact ID */    TransactionId t_xmax;       /* deleting or locking xact ID */     union    {        CommandId   t_cid;      /* inserting or deleting command ID, or both */        TransactionId t_xvac;   /* old-style VACUUM FULL xact ID */    }           t_field3;} HeapTupleFields;struct HeapTupleHeaderData{    union    {        HeapTupleFields t_heap;        DatumTupleFields t_datum;    }           t_choice;     ItemPointerData t_ctid;     /* current TID of this or newer tuple (or a                                 * speculative insertion token) */     /* Fields below here must match MinimalTupleData! */#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK2 2    uint16      t_infomask2;    /* number of attributes + various flags */#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK 3    uint16      t_infomask;     /* various flag bits, see below */#define FIELDNO_HEAPTUPLEHEADERDATA_HOFF 4    uint8       t_hoff;         /* sizeof header incl. bitmap, padding */    /* ^ - 23 bytes - ^ */#define FIELDNO_HEAPTUPLEHEADERDATA_BITS 5    bits8       t_bits[FLEXIBLE_ARRAY_MEMBER];  /* bitmap of NULLs */    /* MORE DATA FOLLOWS AT END OF STRUCT */};

    ffae833ccf8e01a4055e88f0e471e1c4.png 

    t_xmin:代表插入此元组的事务xid;

    t_xmax:代表更新或者删除此元组的事务xid,如果该元组插入后未进行更新或者删除,t_xmax=0;

    t_cid:command id,代表在当前事务中,已经执行过多少条sql,例如执行第一条sql时cid=0,执行第二条sql时cid=1;

    t_ctid:保存着指向自身或者新元组的元组标识(tid),由两个数字组成,第一个数字代表物理块号,或者叫页面号,第二个数字代表元组号。在元组更新后tid指向新版本的元组,否则指向自己,这样其实就形成了新旧元组之间的“元组链”,这个链在元组查找和定位上起着重要作用。

    了解了元组结构,再简单了解下元组更新和删除过程。

    bc6ac351e7825f700a0e095e7c33b934.png 24d5ed8b8aa98b94dcd335237217e64d.gif

    更新过程

    57224b66690e4a72dbcb139a23204895.png

    65d33678ec937da904d0ee5d61d1f461.png

     上图中左边是一条新插入的元组,可以看到元组是xid=100的事务插入的,没有进行更新,所以t_xmax=0,同时t_ctid指向自己,0号页面的第一号元组。右图是发生xid=101的事务更新该元组后的状态,更新在pg里相当于插入一条新元组,原来的元组的t_xmax变为了更新这条事务的xid=101,同时t_ctid指针指向了新插入的元组(0,2),0号页面第二号元组,第二号元组的t_xmin=101(插入该元组的xid),t_ctid=(0,2),没有发生更新,指向自己。

    bc6ac351e7825f700a0e095e7c33b934.png 24d5ed8b8aa98b94dcd335237217e64d.gif

    删除过程

    57224b66690e4a72dbcb139a23204895.png

    be7d7d96a44889c1ca45e913711d2589.png

     上图代表该元组被xid=102的事务删除,将t_xmax设置为删除事务的xid,t_ctid指向自己。

    bc6ac351e7825f700a0e095e7c33b934.png 24d5ed8b8aa98b94dcd335237217e64d.gif

    页面结构

    57224b66690e4a72dbcb139a23204895.png

    下面再来看看页面的结构

    74662f74067ec98de10834f635b55e15.png

    从上图可以看到,页面包括三种类型的数据

    1.header data:数据头是page生成的时候随之产生的,由pageHeaderData定义结构,24个字节长,包含了page的相关信息,下面是数据结构:

    typedef struct PageHeaderData{    /* XXX LSN is member of *any* block, not only page-organized ones */    PageXLogRecPtr pd_lsn;      /* LSN: next byte after last byte of xlog                                 * record for last change to this page */    uint16      pd_checksum;    /* checksum */    uint16      pd_flags;       /* flag bits, see below */    LocationIndex pd_lower;     /* offset to start of free space */    LocationIndex pd_upper;     /* offset to end of free space */    LocationIndex pd_special;   /* offset to start of special space */    uint16      pd_pagesize_version;    TransactionId pd_prune_xid; /* oldest prunable XID, or zero if none */    ItemIdData  pd_linp[FLEXIBLE_ARRAY_MEMBER]; /* line pointer array */} PageHeaderData;

    pd_lsn: 存储最近改变该页面的xlog位置。

    pd_checksum:存储页面校验和。

    pd_lower,pd_upper:pd_lower指向行指针(line pointer)的尾部,pd_upper指向最后那个元组。

    pd_special: 索引页面中使用,它指向特殊空间的开头。

    2.line pointer:行指针,四字节,每一条元组会有一个行指针指向真实元组位置。

    3.heap tuple:存放真实的元组数据,注意元组是从页面的尾部向前堆积的,元组和行指针之间的是数据页的空闲空间。

    bc6ac351e7825f700a0e095e7c33b934.png 24d5ed8b8aa98b94dcd335237217e64d.gif

    索引查找

    57224b66690e4a72dbcb139a23204895.png

     看了页面和元组结构,再看看索引的结构。

    6d635e9ebb5477e2fe589fe6da0b1e11.png

    以上图为例,索引的数据包含两部分(key=xxx,TID=(block=xxx,offset=xxx)),key表示真实数据,tid代表指向数据行的指针,具体block代表页面号,offset代表行偏移量,指向数据页面的line pointer,比如执行下面的查询语句

    select * from tbl where id=1000;

    key=1000,根据key值在索引中找到tid为5号页面的1号元组,再通过一号元组行指针找到元组1,检查元组1的t_ctid字段,发现指向了新的元组2,于是定位到真实元组数据2。

    bc6ac351e7825f700a0e095e7c33b934.png 24d5ed8b8aa98b94dcd335237217e64d.gif

    ccbb1e1c0b4ce0bbe5a25533dbf90ac2.png

    展开全文
  • SQL:插入、修改、删除表数据

    千次阅读 2020-12-17 19:51:01
    SQL的数据插入语句INSERT通常有两种形式: 一种是插入一个元组 另一种是插入子查询结果,可以一次插入多个元组 1.1 插入元组 插入元组的INSERT语句的格式为: INSERT INTO <表名> [(<属性列1>,[,<...
  • 插入元组 基本语法 insert into <表名> [属性列] values(常量1,2) into 指定要插入数据的表名及属性列,属性列的顺序可与表中定义的不一致 没有指定属性列的话,表示插入的是一条完整的元组,且属性列属性...
  • 数据更新 数据更新操作的目的是修改表中的数据。 有三种操作: ...DBMS 在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则。 举例说明: 如果表中已经建立了索引,对索引关键字的更新,...
  • leetcode 196. Delete Duplicate Emails(SQL删除重复元组
  • 数据库1 关系(relation) 元组(tuple) join

    千次阅读 2021-01-30 03:40:33
    如 union 和 union all ,对于返回的查询结果,前者会把重复项随机删除一个查找与排序均为不稳定,不同的操作系统对于相同的插入元组数据会有不同的排序数据其他高级语言的数据类型自动转换仍然有效,比如float与int...
  • 第一部分:SQL语言实践 ...3、熟练掌握SQL数据操纵语句,能够熟练使用SQL数据操纵语句插入、修改及删除元组。 4、熟练掌握SQL数据查询语句,能够熟练使用SQL数据查询语句实现数据基本查询,包括单表查
  • 我们经常会碰到需要删除数据表中重复记录,下面我来总结了几种能删除重复记录并助相对来讲效率是非常不错的,有需要了解的朋友可进入参考。创建一个表用来存放,要删除的记录的id信息:代码如下复制代码CREATE TABLE ...
  • sql server 2005 填空题

    2013-11-28 20:32:25
    SQL语言的英文全称为structured packingQuery Language ,在SQL中,基本表的删除可用drop table table-name 语句,删除基本表中的所有元组可用delete [from] table-name where search-conditions语句。...
  • 1. 数据库的相关操作 ...删除表 drop table 表名; 修改表——增加属性 alter table 表名 add 属性 属性类型; 修改表——删除属性 alter table 表名 drop 属性; 修改表——修改属性 alter table 表名 modi...
  • SQLServer基础知识总结

    千次阅读 2016-11-27 16:18:57
    master数据库是SQL Server中最重要的数据库,记录了SQL Server系统中所有的系统信息,包括登入账户、系统配置和设置、服务器中数据库的名称、相关信息和这些数据库文件的位置,以及SQL Server初始化信息等。...
  • SQL删除语句

    千次阅读 2019-11-28 22:31:53
    DROP ...delete 语句删除的是元组(记录、行)不会把表删除,还可以直接向表中插入数据; 基本语法 DELETE FROM <表名>; DELETE FROM student; 删除部分数据 可以通过where子句来指定对象...
  • 修改基本表的基本语句: ALTER TABLE [ ADD[COLUMN] [ 完整性约束 ] ] [ ADD ] [ DROP [ COLUMN ] [CASCADE| RESTRICT] ] [ DROP CONSTRAINT[ RESTRICT | CASCADE ] ] [ALTER COLUMN ] ; 其中 ...
  • 数据的更新 1.创建数据库后创建表格 CREATE TABLE Student... (2)删除多个元组的值 删除所有课程 delete from Course; (3)带子查询的删除语句 delete from SC where Sno IN(select Sno from Student where Sdept='CS');
  • # Python基础 -- 元组
  • SQL更新与删除

    2016-08-02 17:29:24
    SQL更新与删除
  • SQL 介绍3.1 SQL 查询语言概览3.2 SQL 数据定义3.2.1 基本类型3.2.2 基本模式定义3.3 SQL 查询的基本结构3.3.1 单关系查询3.3.2 多关系查询3.4 附加的基本运算3.4.1 更名运算3.4.2 字符串运算3.4.3 select 字句中的...
  • 通常,您永远不能从元组删除字符。因为它们是不可变的,所以您只能创建一个新的并使用原始元素的所需元素来构建它。之所以有方括号是因为“姓名和分数”的输出是使用列表理解创建的。也就是说,这是一个单子。方...
  • 1. SQL查询语言概述 “In 1986, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) published an SQL standard, calledSQL-86. ANSI published an ...
  • 一、插入操作 INSERT ... 1、插入单个元组 格式:INSERT INTO 表名[(列名,…)] VALUES (列值,…); INSERT INTO student VALUES('2003001' ,'Rain Man',18,'男','电商','管理学院','徐州') INSERT INTO ...
  • 八、元组 九、主键 十、外键 零、码仙励志 坚持和放弃,都不会绝对正确。最恨不过一句,逼不得已。最大大不过一句,老子乐意 一、字段 一个事物的某一个特征 二、记录 字段的组合,表示的是一个具体的事物 ...
  • 4、聚集函数 为进一步方便用户,增强检索功能,SQL提供了许多聚集函数 聚集函数代码 实现功能 COUNT(*) 统计元组个数 COUNT( [DISTINCT / ALL] ) 统计一列值的个数 SUM( [DISTINCT / ALL] ) 计算一列值的总和(此列...
  •     使用SSMS界面方式进行数据的添加修改删除操作比较简单,因此只在此进行介绍,内容分主要使用SQL方式讲解。     展开“数据库”,打开相应的数据库,然后再展开“表”,,右击需要操作的数据表,选择...
  • sql server基本操作

    2019-06-27 01:29:11
    sql server基本操作创建数据库创建基本表基本表的赋值基本表的修改(alert修改表结构,update修改表内容)删除基本表建立索引修改索引删除索引查询的相关操作(distinct去掉重复行)order by子句(用来排序 asc升序...
  • 【数据库学习笔记】Day03 - SQL语言基础及数据库定义功能 〇、本文所用数据库表格: 一、关系运算:         关系运算,数学名词,基本运算有两类:一类是传统的集合...
  • 文章目录SQL语句基操(二)1、建立/删除索引1)建立索引[例 3.13] 为学生-课程数据库建立索引。2)修改索引[例 3.14]将SC表内的SCno索引名改为SCSno。3)删除索引[例 3.15] 删除Student 表的 Stusno 索引。2、插入数据1)...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 28,811
精华内容 11,524
关键字:

sql删除元组