【数据库学习】数据库总结

2023年6月21日发(作者：)

【数据库学习】数据库总结1，概念1）数据库数据库是长期存储在计算机内、有组织的、可共享的⼤量数据的集合。数据库中存储的是数据及数据之间的关系。正常情况读写⽂件系统⽐数据库快⼀到两个数据级；数据库的查询，⼤量并发的时候可能最浪费时间的是connect和close。数据库的优势是体现的⼤量数据的查询、统计以及并发读写，不是在速度上。2）数据库数据特点永久存储、有组织、可共享。（数据的最⼩存取单位是数据项）3）数据库系统的特点①数据结构化②数据的共享性，冗余度，易扩充③数据独⽴性⾼数据独⽴性包括：物理独⽴性和逻辑独⽴性a)物理独⽴性(外模式模式映像)：⽤户程序不需要了解，应⽤程序要处理的只是数据的逻辑结构，这样当数据的物理存储改变了，应⽤程序不⽤改变。b)逻辑独⽴性（模式内模式映像）：逻辑独⽴性是指⽤户的应⽤程序与数据库的逻辑结构是相互独⽴的，即，当数据的逻辑结构改变时，⽤户程序也可以不变。逻辑数据独⽴性（logical data independence)是指概念模式改变，外模式和应⽤程序不变。在逻辑数据独⽴性⾥，数据的逻辑结构发⽣改变或存储关系的选择发⽣改变时⽤户不会受到影响。改变概念模式，例如增加和删除实体、增加和删除属性、增加和删除联系，不需要改变现有的外模式或重写应⽤程序。在DBMS中只需要修改视图的定义和映像来⽀持逻辑数据独⽴性。对⽤户来说，不再关⼼所做的修改是⾮常重要的。换句话说，模式经过逻辑重构之后，根据外模式构建的应⽤程序还是和从前⼀样⼯作。4）概念模型（E-R模型）①概念概念模型的⼀种表⽰⽅法：实体联系⽅法，⽤E-R⽅法（E-R模型）来描述。概念模型是⽤于信息世界的建模，是⼀种信息模型，与具体的DBMS⽆关。且能满⾜⽤户对数据的处理要求，易于修改。概念模型与具体数据模型⽆关且容易向数据库模型转化。123实体：举⾏表⽰属性：椭圆表⽰，并⽤直线与实体连接联系：菱形表⽰，⽤直线与实体连接，同时在边上标上联系的类型（1：1，1：n，m:n)。⼀个联系转化为⼀个关系模式，与该联系相连的各实体的码以及联系的属性转化为关系的属性，该关系的码则有三种情况：若联系为1：1，则每个实体的码均是该关系的后选码。若联系为1：n，则关系的码为n端实体的码。若联系为m：n，则关系的码为诸实体码的组合。数据库模式定义语⾔DDL(Data Definition Language)：是⽤于描述数据库中要存储的现实世界实体的语⾔。⼀个数据库模式包含该数据库中所有实体的描述定义。这些定义包括结构定义、操作⽅法定义等。数据库逻辑设计: 将概念设计所得到的概念模型转换为某⼀具体的数据模型（层次、⽹状、关系、⾯向对象）.5）关系完整性在关系模型中,关系完整性主要是指以下三⽅⾯：实体完整性所谓的实体完整性就是指关系(所谓的关系就是表)的主码不能取空值；⽐如学⽣表的主码通常是取学号为主码参照完整性是指参照关系中每个元素的外码要么为空(NULL)，要么等于被参照关系中某个元素的主码；参照关系也称为外键表，被参照关系也称为主键表。⽤户定义的完整性指对关系中每个属性的取值作⼀个限制(或称为约束)的具体定义。⽐如 性别属性只能取”男“或”⼥“，再就是年龄的取值范围,可以取值0-130 ，但不能取负数，因为年龄不可能是负数。6）关系数据库规范化⽬地：使结构更合理，消除存储异常，使数据冗余尽量⼩，便于插⼊、删除和更新。原则：遵从概念单⼀化“⼀事⼀地”原则，即⼀个关系模式描述⼀个实体或实体间的⼀种联系。规范的实质：概念的单⼀化。规范化的⽅法：将关系模式投影分解成两个或两个以上的关系模式。2，依赖和范式1）依赖①部分函数依赖设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真⼦集，存在X’→Y，则称Y部分函数依赖于X。1 举个例⼦：通过AB能得出C，通过A也能得出C，通过B也能得出C，那么说C部分依赖于AB。②完全函数依赖设X,Y是关系R的两个属性集合，X’是X的真⼦集，存在X→Y，但对每⼀个X’都有X’!→Y，则称Y完全函数依赖于X。1 举个例⼦：通过AB能得出C，但是AB单独得不出C，那么说C完全依赖于AB.③传递函数依赖设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。1 举个例⼦：通过A得到B，通过B得到C，但是C得不到B，B得不到A，那么成C传递依赖于A④多值依赖设R(U)是属性集U上的⼀个关系模式。X，Y，Z是U的⼦集,并且Z=U-X-Y。关系模式R(U)中多值依赖X→→Y成⽴，当且仅当对R(U)的任⼀关系r,给定的⼀对(x，z)值有⼀组Y的值，这组值仅仅决定于x值⽽与z值⽆关。举例：有这样⼀个关系 <仓库管理员，仓库号，库存产品号> ，假设⼀个产品只能放到⼀个仓库中，但是⼀个仓库可以有若⼲管理员，那么对应于⼀个 <仓库管理员，库存产品号>有⼀个仓库号，⽽实际上，这个仓库号只与库存产品号有关，与管理员⽆关，就说这是多值依赖。2）范式各个范式联系：5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF①1NF（满⾜最低要求的范式：字段不可再分，原⼦性）如果⼀个关系模式R的所有属性都是不可分的基本数据项，则R∈1NF。⾃我理解1NF就是⽆重复的列。如：（X1，X2）→X3，X2→X3 其中x3对x2部分依赖如：（X1，X2）→X3，X2→X4 其中有⾮主属性X4部分依赖于候选键{X1,X2}，所以这个关系模式不为第⼆范式；⼜因为范式之间的关系满⾜1NF⊇2NF⊇3NF ⊇ BCNF，所以是第⼀范式。②2NF（消除部分⼦函数依赖：⼀个表只能说明⼀个事物）若R∈1NF，且每⼀个⾮主属性完全函数依赖于码，则R∈2NF。 即要求数据库表中的每个实例或⾏必须可以被唯⼀地区分。③3NF（消除传递依赖，即消除⾮主属性对键的传递依赖：每列都与主键有直接关系，不存在传递依赖。任何⾮主属性不依赖于其它⾮主属性。）若R∈3NF，则每⼀个⾮主属性既不部分依赖于码，也不传递依赖于码。⾃我理解是：表中所有的数据元素不但要能唯⼀地被主键所标识，⽽且他们之间还必须相互独⽴，不存在其他的函数关系。④BCNF(修正第三范式、扩充第三范式 消除主属性对键的传递依赖）所有⾮主属性对每⼀个码都是完全函数依赖；所有主属性对每⼀个不包含它的码，也是完全函数依赖；没有任何属性完全函数依赖于⾮码的任何⼀组属性。⑤4NF关系模式R∈1NF，如果对于R的每个⾮平凡多值依赖X->->Y（Y∉X），X都含有码，则称R∈4NF3，数据库管理系统（DBMS）：是系统软件，是数据库系统的核⼼。常见数据库管理系统有：Access、mysql、sql server4，数据库语句SQL 语⾔是⾮过程化的语⾔，易学习。 SQL语⾔具有两种使⽤⽅式：⼀种是在终端交互⽅式下使⽤，称为交互式SQL； 另⼀种是嵌⼊在⾼级语⾔的程序中使⽤，称为嵌⼊式SQL，⽽这些⾼级语⾔可以是C、PASCAL、COBOL等，称为宿主语⾔。1）基本对象关系数据库系统⽀持 三级模式结构，其概念模式、外模式和内模式中的基本对象有表、视图和索引。三级模式结构有效地组织、管理数据，提⾼了数据库的逻辑独⽴性和物理独⽴性。使数据库达到了数据独⽴性。①模式（schema，逻辑模式）A.概念是数据库中全体数据的逻辑结构和特征的描述，是所有⽤户的公共数据视图。是数据库系统模式结构的中间层，即不涉及数据的物理存储细节和硬件环境，也与具体的应⽤程序、开发⼯具及⾼级设计语⾔⽆关。模式是数据库数据在逻辑级上的视图，⼀个数据库只有⼀个模式。也⽤于区分⼀个 ⼤项⽬中的各个⼩项⽬，这样若有相同名字的表的话, 不同模式不会发⽣冲突。相当于编程时的命名空间。如：⼀个公司的系统，分2个⼦系统，分别为财务系统和⼈⼒资源系统.这2个⼦系统, 共⽤⼀个数据库。那么 财务系统的表, 可以放在财务的模式（schema）.⼈⼒资源系统的表，放在⼈⼒资源系统的模式⾥⾯。这2个⼦系统，能够互相访问对⽅的表。但是⼜不因为 表重名 的问题，影响对⽅。B.访问访问具体的⼀个表，可以由 4个部分组成分别为 服务器名， 数据库名，模式名，表名。对于访问本地的数据库：不指定模式名的话， 数据库默认使⽤dbo模式。（DBO是每个数据库的默认⽤户，具有所有者权限，即DbOwner ）pg不指定模式的话默认使⽤public模式。C.操作12--创建CREATE SCHEMA schema_name;②外模式（⼦模式，⽤户模式）是数据库⽤户能够看见和使⽤的局部数据的逻辑结构和特征的描述，是数据库⽤户的数据视图，是与某⼀应⽤有关的数据的逻辑表⽰。外模式通常是模式的⼦集，⼀个数据库可以有多个外模式，但⼀个应⽤程序只能有⼀个外模式。外模式是保证数据库安全性的⼀个有⼒措施：⽤户只能访问外模式的数据，其余数据不可见。③内模式（存储模式）⼀个数据库只有⼀个内模式。内模式是数据物理结构和存储⽅式的描述，是数据在数据库内部的表⽰⽅式。数据库管理系统在三级模式之间提供了两层映像：外模式/模式映像（保证数据的逻辑独⽴性）模式/内模式映像（保证了物理独⽴性）④表表分为临时表和永久表。临时表临时表存储在tempdb中（如下），当不再使⽤时会⾃动删除。1IF OBJECT_ID('tempdb..#ownerAnnouce') IS NOT NULL根据进程独⽴，只有进程的拥有者有表的访问权限，其它⽤户不能访问该表；不同的⽤户进程，创建的临时表虽然“名字”相同，但是这些表之间相互并不存在任何关系；在SQLSERVER中，通过特别的命名机制保证临时表的进程独⽴性。临时表有两种类型：本地和全局。A.本地临时表名称以单个数字符号 (#) 打头；它们仅对当前的⽤户连接是可见的；当⽤户从 SQL Server 实例断开连接时被删除。B.全局临时表名称以两个数字符号 (##) 打头，创建后对任何⽤户都是可见的，当所有引⽤该表的⽤户从 SQL Server 断开连接时被删除。临时表优点真正的临时表利⽤了数据库临时表空间，由数据库系统⾃动进⾏维护，因此节省了表空间。并且由于临时表空间⼀般利⽤虚拟内存，⼤⼤减少了硬盘的I/O次数，因此也提⾼了系统效率。临时表的创建A. create table #临时表名 * into #临时表名 from 表名（永久表或临时表）⑤视图A.概念视图是⼀张虚拟表，视图的字段是⾃定义的，视图只⽀持查询，查询数据来源于实体表。⼀般视图是只读的，在pg中通过添加规则可以进⾏视图的更新。从pg9.1开始，⽤户可以通过INSTEAD OF的触发器来实现视图更新。B.优缺点优点视图可以将多个复杂关联表提取信息，⽅便查询，但不能优化查询速度（调⽤视图查询时才进⾏动态检索数据）。即，如果你认为⼀个sql查询⾮常慢，为了优化它的速度把它建⽴成视图，这是不可取的，视图是每次调⽤的时候⽣成，并不是数据源变化就刷新数据，并不能提⾼检索效率。缺点视图就是临时表，即调即⽤，如果数据源没有任何变化，在反复调⽤中，临时表会缓存到内存中（SHOW STATUS LIKE‘Qcache%’;），视图中不能创建索引，但视图可以基于索引⽣成 。C.场景1. 重⽤SQL语句；2. 简化复杂SQL操作（⽣成视图），重⽤查询且不需要知道基本查询细节。3. 保护数据。⽤户有表的部分权限。4. 更改数据格式和表⽰。视图可返回与底层表不同的表⽰和格式。D.操作1234567--创建视图CREATE OR REPLACE VIEW view_name(studentName, studentAge) --(studentName, studentAge)

可以去掉，加上是重命名列名AS

SELECT user_, user_ from user_info;--删除视图DROP VIEW view_name;⑥实体视图相对于普通的视图来说，实体化视图的不同之处在于实体化视图管理存储数据，占据数据库的物理空间。实体化视图的结果会保存在⼀个普通的数据表中，在对实体化视图进⾏查询的时候不再会对创建实体化视图的基表进⾏查询，⽽是直接查询实体化视图对应的结果表，然后通过定期的刷新机制来更新实体化视图表中的数据。demo11--

创建物化视图CREATE MATERIALIZED VIEW MAX_ID_MVIEW

AS SELECT PART_ID, MAX(ID) MAX_ID FROM PART_DETAIL GROUP BY PART_ID;

--

如果刷新时不带CONCURRENTLY则⽆需创建唯⼀索引CREATE UNIQUE INDEX IDX_MAX_ID ON MAX_ID_MVIEW(PART_ID);--

利⽤watch命令每120s刷新⼀次物化视图REFRESH MATERIALIZED VIEW CONCURRENTLY MAX_ID_MVIEW; watch 120作⽤1. 减轻⽹络负担：通过实体化视图将数据从⼀个数据库分发到多个不同的数据库上，通过对多个数据库访问来减轻对单个数据库的⽹络负担。2. 搭建分发环境：通过从⼀个中央数据库将数据分发到多个节点数据库，达到分发数据的⽬的。3. 复制数据⼦集：实体化视图可以进⾏⾏级/列级的筛选，这样可以复制需要的那⼀部分数据。4. 实体化视图是⽤于汇总,预计算,复制或分发数据的对象, 在⼤型的数据库中使⽤它可以提⾼涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度。5. 物化视图的快速刷新采⽤了增量的机制，在刷新时，只针对基表上发⽣变化的数据进⾏刷新。因此快速刷新是物化视图刷新⽅式的⾸选。⑦索引为了改变数据库的性能和可访问性所增加的⼀组辅助性数据。详细介绍见下⽂。2）数据结构创建及修改1>数据库操作12345678--查看数据库show databases;--建库create database children;--删库drop database children;--调⽤数据库use children;2>表操作171819--pg建表CREATE TABLE if not exists _info( --创建public模式下的表 FOREIGN KEY (ID) REFERENCES people_info (ID), --单个外键，⼀般情况下不建议增加这种强约束 id int8 PRIMARY KEY, --系统会⾃动为主键创建⼀个隐含的索引 primary key(Sno,Cno)组合主键 address VARCHAR (255) UNIQUE NOT NULL, birthday TIMESTAMP NOT NULL,

age int default 15, --默认值，影响后续插⼊值。但对旧数据没有影响。 CONSTRAINT student2_pkey PRIMARY KEY (id), CONSTRAINT ck_age CHECK(age<18), --检查约束，约束某些字段需要满⾜的要求。NULL被认为满⾜条件。 CONSTRAINT uk_tbl_unique_a_b unique(id ,address) --唯⼀约束。唯⼀键中可以写⼊任意多个NULL！即可以存在多组 1，null

)WITH ( OIDS=FALSE);ALTER TABLE _test OWNER TO postgres;--重命名表alter table tableName RENAME TO newName;--pgi>指定默认值⼀般⽤于数据预置或create_time、update_time的⾃动录⼊。各个DBMS获得系统⽇期如下：DBMSAccessDB2MySQL函数/变量NOW()CURRENT_DATECURRENT_DATE()OracleDBMSPostgreSQLSQL ServerSQLiteSYSDATE函数/变量CURRENT_DATEGETDATE()date(‘now’)1234--修改默认值alter table tableName alter column age set DEFAULT 15;--pg--删除默认值alter table tableName alter column age drop DEFAULT 15;--pgii>表约束表约束有：主键、外键、检查约束、唯⼀约束、⾮NULL约束。123456789--添加主键(有些DBMS不允许在建表之后修改主键）ALTER TABLE tableName ADD PRIMARY KEY(fieldName) ; --fieldName在库中不能有重复数据--增加约束alter table tableName add check (age<16);--pg

增加检查约束，约束名为：tableName_age_checkalter table tableName add constraint uk_tbl_unique_a_b unique (a,b);--pg

增加唯⼀约束alter table tableName alter column fieldName set NOT NULL;--pg

增加⾮空约束--删除约束alter table tableName drop constraint constraintName;--pg

根据约束名删除检查约束、唯⼀约束alter table tableName alter column fieldName drop NOT NULL;--pg

删除⾮空约束（⾮空约束没有约束名）iii>修改表字段1234567891--增加列添加⼀个字段alter table tableName add column if not exists columnName varchar(30) default 'a' not null; --column

可加可不加--删除列（会连同字段上的约束⼀并删除）alter table tableName drop column columnName; --column

可加可不加--修改列名：alter table tableName rename column fieldName TO fieldNameNew;--pg、oracle中exec sp_rename '[表名].[列名]‘，’[表名].[新列名]'--在sqlserverALTER TABLE 表名 CHANGE 列名 新列名 列类型--mysql--修改字段类型或长度:alter table tableName modify column 字段名 类型;alter table tableName alter column fieldName TYPE text;--pg修改字段数据类型。仅在当前数据都可以隐式转换为新类型时才可以执⾏成功--将NAME最⼤列宽增加到10个字符ALTER TABLE CARD ALTER COLUMN NAME varchar(10)

3）数据查询数据库处理⼀个查询的步骤：客户端连接->查询缓存->解析器->预处理器->查询优化器->查询执⾏引擎->数据123451. 客户端发送⼀条查询给服务器；2. 服务器先会检查查询缓存query cache，如果命中了缓存，则⽴即返回存储在缓存中的结果。否则进⼊下⼀阶段；3. 服务器端进⾏SQL解析parsing、预处理transition，再由优化器optimization⽣成对应的执⾏计划；4. 根据优化器⽣成的执⾏计划，调⽤存储引擎的API来执⾏分布distribution查询；5. 将结果返回给客户端。1>简单查询12select * from student;select 1+2; #当表达式与表列⽆关时，在pg和mysql中不适⽤“from tableName”拼接查询：Access和 SQL Server使⽤ + 号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base 使⽤ ||。1select label || '_' || id from user_info; --结果：abc_12>条件查询功能等于表达=举例备注不等于<>或!=is null，isnot nullin ，notinbetween空值select * from student where class is not null;在sql标准中仅⽀持100个以内的占位符作为查询参select * from student where age not in(21,23);数。根据数据库不同，对in的参数和长度有不同的限制，否则会直接报错。确定集合，in确定范围and , notbetweenand模糊查询like ,notlikeselect * from student where name like '%丽%';’ %代表任意长度（可为0）的字符串；_（下划线）：代表任意单个字符。（汉字代表2个字符，所以⼀个汉字⽤两个下划线）；为转义字符注意：1. select出的别名是否可以作为where查询条件？不能，因为执⾏计划中where在selectz之前。如：select label a from asset_field where a= '分类'2. not in 和<>都不⾛索引，效率⾮常慢，建议⽤EXISTS和not EXISTS、join⽅式替换。（pg orcle）!= 不是标准的SQL，<>才是，这两个在PostgreSQL中是等效的。exits和not exits的意思是逐条将条件下放到判断条件，⽽jion⽅式是先对表进⾏笛卡尔积，然后判断同⾏之间的各列值是否满⾜关系。3>排序查询⾮排序查询的数据顺序：pg默认返回数据的顺序是插⼊表的数据顺序。12345#

单个排序：select name,age from student order by age desc; #

默认为asc：升序排列。desc：降序排序。#多重排序：order by 字段5,字段6 asc //先按字段5排序，再按字段6排序4>case when then查询123456789--简单case函数case sex when '1' then '男' when '2' then '⼥’ else '其他' end--case搜索函数case when sex = '1' then '男' when sex = '2' then '⼥' else '其他' end 应⽤：1234select (case sex when '1' then '男' when '2' then '⼥’ else '其他' end)sex from student where class = 11;5>where、group by、having⼤部分的where都可以背having代替，不同的是where过滤⾏，⽽having过滤分组，⽤在group by之后。(where在分组前过滤，having在分组后过滤）123select class,avg(age) as age from student

group by class

having avg(age)>23 /*要求平均年龄⼤于23*/where肯定在group by 之前where后的条件表达式⾥不允许使⽤聚合函数，⽽having可以。6> 函数聚合函数avg平均数，同min(age)、max(age)、sum(age)1select avg(age) as age from student group by class order by age desc;

count1234select count(class)from student;/*数量 因为使⽤了92标准，所以null不计⼊count*/count(*) 跟count(1) 的结果⼀样，返回记录的总⾏数，都包括对NULL 的统计，count(column) 是不包括NULL 的统计。distinct12select distinct(class)from student;/*去重复，出现所有不同的内容*/select count(distinct(class)) from student;其它12345LEFT（“123456789”，LEN（“数据库”））/*分两步运算，第⼀步是运算LEN函数，结果是3。第⼆步针对123456789这个字符从左边开始连续取三个数*/select top 100 * from student where no=11;/*显⽰前100⾏*/select isnull(name,'⽆') as name,age,class from student;/*isnull之后就⽆列名了 ⽤as给列重命名*/select name,age,class,'the name is' + name as introduce from student;/*⽤加号形成⼀个⾃定义列*/7>SQL-92 规则是数据库的⼀个标准。以下代码 写在存储过程前⾯，表⽰遵从SQL-92 规则。SQL-92 标准要求在对空值进⾏等于 (=) 或不等于 (<) ⽐较时取值为 FALSE。1234SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ON即使 column_name 中包含空值，使⽤ WHERE column_name = NULL 的 SELECT 语句仍返回零⾏。即使 column_name 中包含⾮空值，使⽤ WHERE column_name < NULL 的 SELECT 语句仍会返回零⾏。SET QUOTED_IDENTIFIER ON为ON：标识符可以由双引号分隔，⽽⽂字必须由单引号分隔。为OFF：标识符不可加引号。8>多层查询 EXISTS如果内层查询语句查询到符合条件的记录，就返回⼀个真值（true），否则，将返回⼀个假值（false）。123SELECT * FROM employeeWHERE EXISTS(SELECT d_name FROM department WHERE d_id=1003);同理还有：NOT EXISTS。9>关联查询、联结（JOIN）表关系数据库设计中表的设计是把信息分解成多个表，⼀类数据⼀个表，各表通过某些共同的值相互关联。⼀般情况下我们不建议建⽴外键这种强关联的关联信息。可伸缩（scale）能够适应不断增加的⼯作量⽽不失败。关系数据库的可伸缩性远远优于⾮关系数据库。注意：1. 联结的表越多效率越低。2. SQL本⾝不限制联结表的数⽬，但DBMS有最⼤数⽬限制。3. ⼀般情况下，联结查询⽐⼦查询快，实际应⽤中应该尝试两种⽅法看哪种快。JSON类型JOINLEFT JOINRIGHT JOINFULL JOIN说明如果表中有⾄少⼀个匹配，则返回⾏即使右表中没有匹配，也从左表返回所有的⾏即使左表中没有匹配，也从右表返回所有的⾏只要其中⼀个表中存在匹配，就返回⾏备注INNER已省略。外联结⽐内联结返回的⾏数多（还包括没有关联的⾏）OUTER已省略OUTER已省略OUTER已省略luo_persons表：id_plast_namefirst_nameaddresscityluo_orders表：id_oorder_noid_p要求输出：谁订购了产品，并且他们订购了什么产品？①联表查询（等值联结，equijoin）1234567SELECT _name, _name, _noFROM luo_persons a, luo_orders b

WHERE _p = _p

②join查询（内联结，inner join， 推荐）/*（推荐）等值联结明确指定联结类型可转换为inner join1234567SELECT last_name, first_name, order_no

FROM luo_persons INNER JOIN luo_orders ON luo__p = luo__p③union查询（复合查询、并查询）UNION 操作符⽤于合并两个或多个 SELECT 语句的结果集。注意：1. UNION 内部的 SELECT 语句必须拥有相同数量的列、表达式或聚集函数。列也必须拥有相似的数据类型（可以不完全相同，但是可以互相转换）。同时，每条 SELECT 语句中的列的顺序必须相同。2. 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使⽤ UNION ALL。3. UNION能组合的最⼤语句数⽬限制需要查询具体的DBMS⽂档。123 select id_p from luo_persons

union

SELECT id_p from luo_orders某些DBMS中还⽀持其它类型的UNION：1. EXCEPT（或MINUS）：检索在第⼀个表中存在⽽在第⼆个表中不存在的⾏；2. INTERSECT：检索两个表中都存在的⾏。4）数据更新①数据插⼊i> insert123insert into tableName(no,name) values（'1','kate');--按表中列的顺序，但如果表结构发⽣了变化那么对应 sql也要改。不推荐insert into product values('001','001','N','N');有⾃增长主键(id)的插⼊：i>可以把id的值设置为null或者0，这样mysql会⾃⼰做处理ii>⼿动指定需要插⼊的列，不插⼊这⼀个字段的数据！ii> insert select将select结果插⼊表中，⼀般⽤于可重复执⾏的sql。注： select语句中，如果select返回多⾏，那么会insert多⾏数据。12INSERT INTO "public"."vendors"("vend_name", "vend_id") select 'vend_name1', 1

WHERE NOT EXISTS (select 1 FROM "public"."vendors" WHERE vend_id = 1);iii> select into1. SELECT INTO 语句从⼀个表中选取数据，然后把数据插⼊另⼀个表中。2. SELECT INTO 语句常⽤于创建表的备份复件或者⽤于对记录进⾏存档。3. select into 可以从多个表中检索数据，但只能插⼊到⼀个表中。函数⾥⾯，把⼀个查询出来的值存⼊临时变量：12SELECT LastName,FirstNameINTO _lName,_fName FROM Persons也可以存⼊临时表中：123SELECT *INTO Persons_backupFROM Persons②数据修改12update tableName set name = 'Tom' where name='kate';update tableName set age = age + 1;5）数据删除删除表中⼏⾏：1DELETE FROM Person WHERE LastName = 'Wilson'

删除表中所有⾏，保留表、不释放空间。所删除的每⾏记录都会进⽇志，可以回滚。1DELETE FROM table_name删除表：删除内容和定义，释放空间12drop table user;

DROP TABLE IF EXISTS "public"."role_relation"; 可重复执⾏sql删除表中所有数据，保留表、同时释放空间（速度⽐delete快，但是⽆法撤回，⽇志⾥⾯只记录页释放）：1truncate table book;truncate是DDL语句（Data Definition，数据定义语句），相当于⽤重新定义⼀个新表的⽅法把原表的内容直接丢弃了，所以执⾏起来很快。delete语句是DML语句（Data Manipulation，数据操作语句），把数据⼀条⼀条的删除，所以删除多⾏数据执⾏较慢。6）其他注意①加中括号列名、表名、存储过程名、函数名等都可以按需要加中括号。防⽌某些关键字在应⽤中引起歧义。1select [select] from 表名;7）数据库授权①授权GRANT1234 GRANT <权限> ON <对象类型> <对象名> TO <⽤户> [WITH GRANT OPTION] // 如果指定了WITH GRANT OPTION⼦句，则获得某种权限的⽤户还可以把这种权限再授予其他⽤户，允许⽤户传递权限，但是不举例：11121314例1：把查询Student表的权限授给⽤户U1GRANT SELECTON TABLE StudentTO U1;例2：把全部操作权限授予⽤户U2和U3GRANT ALL PRIVILEGESON TABLE Student,CourseTO U2,U3;例3：把查询权限授予所有⽤户GRANT SELECTON TABLE SCTO PUBLIC;③权限的收回 REVOKE123举例：1234例6：收回所有⽤户对表sc的查询权限REVOKE SELECTON TABLE SCFROM PUBLIC;REVOKE <权限>ON <对象类型> <对象名>FROM <⽤户>③对⽤户模式的授权由DBA（数据库管理员，Database Administrator，简称DBA）在创建⽤户时实现。12CREATE USER [WITH] [DBA|RESOURCE|CONNECT]只有系统的超级⽤户才有权创建⼀个新的数据库⽤户新创建的⽤户有三种权限：DB，|RESOURCE，CONNECT④数据库⾓⾊创建及授权1CREATE ROLE <⾓⾊名>给⾓⾊授权：123GRANT <权限>ON <对象类型> 对象名TO <⾓⾊>将⼀个⾓⾊授予其他的⾓⾊或⽤户123GRANT <⾓⾊1>TO <⾓⾊3>[WITH ADMIN OPTION]//如果指定了WITH ADMIN OPTION ⼦句，则获得某种权限的⾓⾊或⽤户还可以把这种权限再授予其他⾓⾊⾓⾊权限的收回123REVOKE <权限>ON <对象类型> <对象名>FROM <⾓⾊>⑤DENY 拒绝账户访问在安全系统中创建⼀项，以拒绝给当前数据库内的安全帐户授予权限并防⽌安全帐户通过其组或⾓⾊成员资格继承权限。12DENY { ALL | statement [ ,...n ] }TO security_account [ ,...n ]和授权区别：不授权是没有权限，但是如果这个⽤户属于某个⾓⾊，这个⾓⾊有了权限，那么这个⽤户可以从⾓⾊继承这个权限。如果选择了deny，即使这个⽤户属于某个具有权限的⾓⾊，他也没有权限。8）数据类型①uniqueidentifier可存储16字节的⼆进制值，其作⽤与全局唯⼀标记符（GUID）⼀样。GUID是唯⼀的⼆进制数：世界上的任何两台计算机都不会⽣成重复的GUID值。GUID主要⽤于在⽤于多个节点，多台计算机的⽹络中，分配必须具有唯⼀性的标识符。9）函数①OBJECT_IDA. 返回指定对象的对象 ID1234USE master;GOSELECT OBJECT_ID(N'der') AS 'Object ID';GOB. 验证对象是否存在12345USE AdventureWorks;GOIF OBJECT_ID (N'dVersion', N'U') IS NOT NULLDROP TABLE dVersion;GON是显式的将⾮unicode字符转成unicode字符，它来⾃ SQL-92 标准中的 National（Unicode）数据类型，⽤于扩展和标准化，在这⾥可以不⽤，写作object_id(PerPersonData)。10）SQL中的借书经典案例①问题描述本题⽤到下⾯三个关系表：CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级BOOKS 图书。 BNO 书号，BNAME 书名, AUTHOR 作者，PRICE 单价，QUANTITY 库存册数BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书⽇期备注：限定每⼈每种书只能借⼀本；库存册数随借书、还书⽽改变。要求1． 写出建⽴BORROW表的SQL语句，要求定义主码完整性约束和引⽤完整性约束。12345CREATE TABLE BORROW( CNO int FOREIGN KEY REFERENCES CARD(CNO), BNO int FOREIGN KEY REFERENCES BOOKS(BNO), RDATE datetime, PRIMARY KEY(CNO,BNO))

要求2． 找出借书超过5本的读者,输出借书卡号及所借图书册数。1234SELECT CNO,借图书册数=COUNT(*)FROM BORROWGROUP BY CNOHAVING COUNT(*)>5要求3． 查询借阅了"⽔浒"⼀书的读者，输出姓名及班级CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级BOOKS 图书。 BNO 书号，BNAME 书名, AUTHOR 作者，PRICE 单价，QUANTITY 库存册数BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书⽇期123456SELECT * FROM CARD cWHERE EXISTS( SELECT * FROM BORROW a,BOOKS b

WHERE = AND =N'⽔浒' AND =)

要求4． 查询过期未还图书，输出借阅者（卡号）、书号及还书⽇期。12SELECT * FROM BORROW

WHERE RDATE

要求5． 查询书名包括"⽹络"关键词的图书，输出书号、书名、作者。12SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE BNAME LIKE N'%⽹络%'

N’string’ 表⽰string是个Unicode字符串要求6． 查询现有图书中价格最⾼的图书，输出书名及作者。123SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE PRICE=( SELECT MAX(PRICE) FROM BOOKS)

要求7. 查询当前借了"计算⽅法"但没有借"计算⽅法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。123456789SELECT M BORROW a,BOOKS bWHERE = AND =N'计算⽅法' AND NOT EXISTS( SELECT * FROM BORROW aa,BOOKS bb WHERE = AND =N'计算⽅法习题集' AND =)ORDER BY DESC

要求8． 将"C01"班同学所借图书的还期都延长⼀周。1234UPDATE b SET RDATE=DATEADD(Day,7,)FROM CARD a,BORROW bWHERE = AND =N'C01'

12DATEADD(datepart,number,date)

date 参数是合法的⽇期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。要求9． 从BOOKS表中删除当前⽆⼈借阅的图书记录。1234DELETE FROM BOOKS aWHERE NOT EXISTS( SELECT * FROM BORROW WHERE BNO=)

要求11．在BORROW表上建⽴⼀个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应⽤"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。12345678CREATE TRIGGER TR_SAVE ON BORROWFOR INSERT,UPDATEASIF @@ROWCOUNT>0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE = AND =N'数据库技术及应⽤'

要求13．查询当前同时借有"计算⽅法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。1234567SELECT M BORROW a,BOOKS bWHERE = AND IN(N'计算⽅法',N'组合数学')GROUP BY ING COUNT(*)=2ORDER BY DESC5，6，关系运算1）集合运算符并（∪）、差（-）、交（∩）、笛卡尔积（×）123笛卡尔积（直积）：表⽰为X × Y，第⼀个对象是X的成员⽽第⼆个对象是Y的所有可能有序对的其中⼀个成员。例如，A={a,b}, B={0,1,2}，则A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}2）专门的关系运算符①选择（限制、σ）在关系R中选择满⾜给定条件的诸元组。②投影（π）关系R上的投影是从R中选择出若⼲属性列组成新的关系。投影之后可既改变⾏，⼜改变元组的数量。③连接（θ连接、⋈）从两个关系的笛卡尔积中选取属性间满⾜⼀定条件的元组。（连接由乘积（笛卡尔积）、选择、投影组成）分为等值连接（=）、⾃然连接（要求⽐较的分量是相同的属性组，并在结果中把重复的属性列去掉）。④除运算（

2023年6月21日发(作者：)

【数据库学习】数据库总结1，概念1）数据库数据库是长期存储在计算机内、有组织的、可共享的⼤量数据的集合。数据库中存储的是数据及数据之间的关系。正常情况读写⽂件系统⽐数据库快⼀到两个数据级；数据库的查询，⼤量并发的时候可能最浪费时间的是connect和close。数据库的优势是体现的⼤量数据的查询、统计以及并发读写，不是在速度上。2）数据库数据特点永久存储、有组织、可共享。（数据的最⼩存取单位是数据项）3）数据库系统的特点①数据结构化②数据的共享性，冗余度，易扩充③数据独⽴性⾼数据独⽴性包括：物理独⽴性和逻辑独⽴性a)物理独⽴性(外模式模式映像)：⽤户程序不需要了解，应⽤程序要处理的只是数据的逻辑结构，这样当数据的物理存储改变了，应⽤程序不⽤改变。b)逻辑独⽴性（模式内模式映像）：逻辑独⽴性是指⽤户的应⽤程序与数据库的逻辑结构是相互独⽴的，即，当数据的逻辑结构改变时，⽤户程序也可以不变。逻辑数据独⽴性（logical data independence)是指概念模式改变，外模式和应⽤程序不变。在逻辑数据独⽴性⾥，数据的逻辑结构发⽣改变或存储关系的选择发⽣改变时⽤户不会受到影响。改变概念模式，例如增加和删除实体、增加和删除属性、增加和删除联系，不需要改变现有的外模式或重写应⽤程序。在DBMS中只需要修改视图的定义和映像来⽀持逻辑数据独⽴性。对⽤户来说，不再关⼼所做的修改是⾮常重要的。换句话说，模式经过逻辑重构之后，根据外模式构建的应⽤程序还是和从前⼀样⼯作。4）概念模型（E-R模型）①概念概念模型的⼀种表⽰⽅法：实体联系⽅法，⽤E-R⽅法（E-R模型）来描述。概念模型是⽤于信息世界的建模，是⼀种信息模型，与具体的DBMS⽆关。且能满⾜⽤户对数据的处理要求，易于修改。概念模型与具体数据模型⽆关且容易向数据库模型转化。123实体：举⾏表⽰属性：椭圆表⽰，并⽤直线与实体连接联系：菱形表⽰，⽤直线与实体连接，同时在边上标上联系的类型（1：1，1：n，m:n)。⼀个联系转化为⼀个关系模式，与该联系相连的各实体的码以及联系的属性转化为关系的属性，该关系的码则有三种情况：若联系为1：1，则每个实体的码均是该关系的后选码。若联系为1：n，则关系的码为n端实体的码。若联系为m：n，则关系的码为诸实体码的组合。数据库模式定义语⾔DDL(Data Definition Language)：是⽤于描述数据库中要存储的现实世界实体的语⾔。⼀个数据库模式包含该数据库中所有实体的描述定义。这些定义包括结构定义、操作⽅法定义等。数据库逻辑设计: 将概念设计所得到的概念模型转换为某⼀具体的数据模型（层次、⽹状、关系、⾯向对象）.5）关系完整性在关系模型中,关系完整性主要是指以下三⽅⾯：实体完整性所谓的实体完整性就是指关系(所谓的关系就是表)的主码不能取空值；⽐如学⽣表的主码通常是取学号为主码参照完整性是指参照关系中每个元素的外码要么为空(NULL)，要么等于被参照关系中某个元素的主码；参照关系也称为外键表，被参照关系也称为主键表。⽤户定义的完整性指对关系中每个属性的取值作⼀个限制(或称为约束)的具体定义。⽐如 性别属性只能取”男“或”⼥“，再就是年龄的取值范围,可以取值0-130 ，但不能取负数，因为年龄不可能是负数。6）关系数据库规范化⽬地：使结构更合理，消除存储异常，使数据冗余尽量⼩，便于插⼊、删除和更新。原则：遵从概念单⼀化“⼀事⼀地”原则，即⼀个关系模式描述⼀个实体或实体间的⼀种联系。规范的实质：概念的单⼀化。规范化的⽅法：将关系模式投影分解成两个或两个以上的关系模式。2，依赖和范式1）依赖①部分函数依赖设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真⼦集，存在X’→Y，则称Y部分函数依赖于X。1 举个例⼦：通过AB能得出C，通过A也能得出C，通过B也能得出C，那么说C部分依赖于AB。②完全函数依赖设X,Y是关系R的两个属性集合，X’是X的真⼦集，存在X→Y，但对每⼀个X’都有X’!→Y，则称Y完全函数依赖于X。1 举个例⼦：通过AB能得出C，但是AB单独得不出C，那么说C完全依赖于AB.③传递函数依赖设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。1 举个例⼦：通过A得到B，通过B得到C，但是C得不到B，B得不到A，那么成C传递依赖于A④多值依赖设R(U)是属性集U上的⼀个关系模式。X，Y，Z是U的⼦集,并且Z=U-X-Y。关系模式R(U)中多值依赖X→→Y成⽴，当且仅当对R(U)的任⼀关系r,给定的⼀对(x，z)值有⼀组Y的值，这组值仅仅决定于x值⽽与z值⽆关。举例：有这样⼀个关系 <仓库管理员，仓库号，库存产品号> ，假设⼀个产品只能放到⼀个仓库中，但是⼀个仓库可以有若⼲管理员，那么对应于⼀个 <仓库管理员，库存产品号>有⼀个仓库号，⽽实际上，这个仓库号只与库存产品号有关，与管理员⽆关，就说这是多值依赖。2）范式各个范式联系：5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF①1NF（满⾜最低要求的范式：字段不可再分，原⼦性）如果⼀个关系模式R的所有属性都是不可分的基本数据项，则R∈1NF。⾃我理解1NF就是⽆重复的列。如：（X1，X2）→X3，X2→X3 其中x3对x2部分依赖如：（X1，X2）→X3，X2→X4 其中有⾮主属性X4部分依赖于候选键{X1,X2}，所以这个关系模式不为第⼆范式；⼜因为范式之间的关系满⾜1NF⊇2NF⊇3NF ⊇ BCNF，所以是第⼀范式。②2NF（消除部分⼦函数依赖：⼀个表只能说明⼀个事物）若R∈1NF，且每⼀个⾮主属性完全函数依赖于码，则R∈2NF。 即要求数据库表中的每个实例或⾏必须可以被唯⼀地区分。③3NF（消除传递依赖，即消除⾮主属性对键的传递依赖：每列都与主键有直接关系，不存在传递依赖。任何⾮主属性不依赖于其它⾮主属性。）若R∈3NF，则每⼀个⾮主属性既不部分依赖于码，也不传递依赖于码。⾃我理解是：表中所有的数据元素不但要能唯⼀地被主键所标识，⽽且他们之间还必须相互独⽴，不存在其他的函数关系。④BCNF(修正第三范式、扩充第三范式 消除主属性对键的传递依赖）所有⾮主属性对每⼀个码都是完全函数依赖；所有主属性对每⼀个不包含它的码，也是完全函数依赖；没有任何属性完全函数依赖于⾮码的任何⼀组属性。⑤4NF关系模式R∈1NF，如果对于R的每个⾮平凡多值依赖X->->Y（Y∉X），X都含有码，则称R∈4NF3，数据库管理系统（DBMS）：是系统软件，是数据库系统的核⼼。常见数据库管理系统有：Access、mysql、sql server4，数据库语句SQL 语⾔是⾮过程化的语⾔，易学习。 SQL语⾔具有两种使⽤⽅式：⼀种是在终端交互⽅式下使⽤，称为交互式SQL； 另⼀种是嵌⼊在⾼级语⾔的程序中使⽤，称为嵌⼊式SQL，⽽这些⾼级语⾔可以是C、PASCAL、COBOL等，称为宿主语⾔。1）基本对象关系数据库系统⽀持 三级模式结构，其概念模式、外模式和内模式中的基本对象有表、视图和索引。三级模式结构有效地组织、管理数据，提⾼了数据库的逻辑独⽴性和物理独⽴性。使数据库达到了数据独⽴性。①模式（schema，逻辑模式）A.概念是数据库中全体数据的逻辑结构和特征的描述，是所有⽤户的公共数据视图。是数据库系统模式结构的中间层，即不涉及数据的物理存储细节和硬件环境，也与具体的应⽤程序、开发⼯具及⾼级设计语⾔⽆关。模式是数据库数据在逻辑级上的视图，⼀个数据库只有⼀个模式。也⽤于区分⼀个 ⼤项⽬中的各个⼩项⽬，这样若有相同名字的表的话, 不同模式不会发⽣冲突。相当于编程时的命名空间。如：⼀个公司的系统，分2个⼦系统，分别为财务系统和⼈⼒资源系统.这2个⼦系统, 共⽤⼀个数据库。那么 财务系统的表, 可以放在财务的模式（schema）.⼈⼒资源系统的表，放在⼈⼒资源系统的模式⾥⾯。这2个⼦系统，能够互相访问对⽅的表。但是⼜不因为 表重名 的问题，影响对⽅。B.访问访问具体的⼀个表，可以由 4个部分组成分别为 服务器名， 数据库名，模式名，表名。对于访问本地的数据库：不指定模式名的话， 数据库默认使⽤dbo模式。（DBO是每个数据库的默认⽤户，具有所有者权限，即DbOwner ）pg不指定模式的话默认使⽤public模式。C.操作12--创建CREATE SCHEMA schema_name;②外模式（⼦模式，⽤户模式）是数据库⽤户能够看见和使⽤的局部数据的逻辑结构和特征的描述，是数据库⽤户的数据视图，是与某⼀应⽤有关的数据的逻辑表⽰。外模式通常是模式的⼦集，⼀个数据库可以有多个外模式，但⼀个应⽤程序只能有⼀个外模式。外模式是保证数据库安全性的⼀个有⼒措施：⽤户只能访问外模式的数据，其余数据不可见。③内模式（存储模式）⼀个数据库只有⼀个内模式。内模式是数据物理结构和存储⽅式的描述，是数据在数据库内部的表⽰⽅式。数据库管理系统在三级模式之间提供了两层映像：外模式/模式映像（保证数据的逻辑独⽴性）模式/内模式映像（保证了物理独⽴性）④表表分为临时表和永久表。临时表临时表存储在tempdb中（如下），当不再使⽤时会⾃动删除。1IF OBJECT_ID('tempdb..#ownerAnnouce') IS NOT NULL根据进程独⽴，只有进程的拥有者有表的访问权限，其它⽤户不能访问该表；不同的⽤户进程，创建的临时表虽然“名字”相同，但是这些表之间相互并不存在任何关系；在SQLSERVER中，通过特别的命名机制保证临时表的进程独⽴性。临时表有两种类型：本地和全局。A.本地临时表名称以单个数字符号 (#) 打头；它们仅对当前的⽤户连接是可见的；当⽤户从 SQL Server 实例断开连接时被删除。B.全局临时表名称以两个数字符号 (##) 打头，创建后对任何⽤户都是可见的，当所有引⽤该表的⽤户从 SQL Server 断开连接时被删除。临时表优点真正的临时表利⽤了数据库临时表空间，由数据库系统⾃动进⾏维护，因此节省了表空间。并且由于临时表空间⼀般利⽤虚拟内存，⼤⼤减少了硬盘的I/O次数，因此也提⾼了系统效率。临时表的创建A. create table #临时表名 * into #临时表名 from 表名（永久表或临时表）⑤视图A.概念视图是⼀张虚拟表，视图的字段是⾃定义的，视图只⽀持查询，查询数据来源于实体表。⼀般视图是只读的，在pg中通过添加规则可以进⾏视图的更新。从pg9.1开始，⽤户可以通过INSTEAD OF的触发器来实现视图更新。B.优缺点优点视图可以将多个复杂关联表提取信息，⽅便查询，但不能优化查询速度（调⽤视图查询时才进⾏动态检索数据）。即，如果你认为⼀个sql查询⾮常慢，为了优化它的速度把它建⽴成视图，这是不可取的，视图是每次调⽤的时候⽣成，并不是数据源变化就刷新数据，并不能提⾼检索效率。缺点视图就是临时表，即调即⽤，如果数据源没有任何变化，在反复调⽤中，临时表会缓存到内存中（SHOW STATUS LIKE‘Qcache%’;），视图中不能创建索引，但视图可以基于索引⽣成 。C.场景1. 重⽤SQL语句；2. 简化复杂SQL操作（⽣成视图），重⽤查询且不需要知道基本查询细节。3. 保护数据。⽤户有表的部分权限。4. 更改数据格式和表⽰。视图可返回与底层表不同的表⽰和格式。D.操作1234567--创建视图CREATE OR REPLACE VIEW view_name(studentName, studentAge) --(studentName, studentAge)

可以去掉，加上是重命名列名AS

SELECT user_, user_ from user_info;--删除视图DROP VIEW view_name;⑥实体视图相对于普通的视图来说，实体化视图的不同之处在于实体化视图管理存储数据，占据数据库的物理空间。实体化视图的结果会保存在⼀个普通的数据表中，在对实体化视图进⾏查询的时候不再会对创建实体化视图的基表进⾏查询，⽽是直接查询实体化视图对应的结果表，然后通过定期的刷新机制来更新实体化视图表中的数据。demo11--

创建物化视图CREATE MATERIALIZED VIEW MAX_ID_MVIEW

AS SELECT PART_ID, MAX(ID) MAX_ID FROM PART_DETAIL GROUP BY PART_ID;

--

如果刷新时不带CONCURRENTLY则⽆需创建唯⼀索引CREATE UNIQUE INDEX IDX_MAX_ID ON MAX_ID_MVIEW(PART_ID);--

利⽤watch命令每120s刷新⼀次物化视图REFRESH MATERIALIZED VIEW CONCURRENTLY MAX_ID_MVIEW; watch 120作⽤1. 减轻⽹络负担：通过实体化视图将数据从⼀个数据库分发到多个不同的数据库上，通过对多个数据库访问来减轻对单个数据库的⽹络负担。2. 搭建分发环境：通过从⼀个中央数据库将数据分发到多个节点数据库，达到分发数据的⽬的。3. 复制数据⼦集：实体化视图可以进⾏⾏级/列级的筛选，这样可以复制需要的那⼀部分数据。4. 实体化视图是⽤于汇总,预计算,复制或分发数据的对象, 在⼤型的数据库中使⽤它可以提⾼涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度。5. 物化视图的快速刷新采⽤了增量的机制，在刷新时，只针对基表上发⽣变化的数据进⾏刷新。因此快速刷新是物化视图刷新⽅式的⾸选。⑦索引为了改变数据库的性能和可访问性所增加的⼀组辅助性数据。详细介绍见下⽂。2）数据结构创建及修改1>数据库操作12345678--查看数据库show databases;--建库create database children;--删库drop database children;--调⽤数据库use children;2>表操作171819--pg建表CREATE TABLE if not exists _info( --创建public模式下的表 FOREIGN KEY (ID) REFERENCES people_info (ID), --单个外键，⼀般情况下不建议增加这种强约束 id int8 PRIMARY KEY, --系统会⾃动为主键创建⼀个隐含的索引 primary key(Sno,Cno)组合主键 address VARCHAR (255) UNIQUE NOT NULL, birthday TIMESTAMP NOT NULL,

age int default 15, --默认值，影响后续插⼊值。但对旧数据没有影响。 CONSTRAINT student2_pkey PRIMARY KEY (id), CONSTRAINT ck_age CHECK(age<18), --检查约束，约束某些字段需要满⾜的要求。NULL被认为满⾜条件。 CONSTRAINT uk_tbl_unique_a_b unique(id ,address) --唯⼀约束。唯⼀键中可以写⼊任意多个NULL！即可以存在多组 1，null

)WITH ( OIDS=FALSE);ALTER TABLE _test OWNER TO postgres;--重命名表alter table tableName RENAME TO newName;--pgi>指定默认值⼀般⽤于数据预置或create_time、update_time的⾃动录⼊。各个DBMS获得系统⽇期如下：DBMSAccessDB2MySQL函数/变量NOW()CURRENT_DATECURRENT_DATE()OracleDBMSPostgreSQLSQL ServerSQLiteSYSDATE函数/变量CURRENT_DATEGETDATE()date(‘now’)1234--修改默认值alter table tableName alter column age set DEFAULT 15;--pg--删除默认值alter table tableName alter column age drop DEFAULT 15;--pgii>表约束表约束有：主键、外键、检查约束、唯⼀约束、⾮NULL约束。123456789--添加主键(有些DBMS不允许在建表之后修改主键）ALTER TABLE tableName ADD PRIMARY KEY(fieldName) ; --fieldName在库中不能有重复数据--增加约束alter table tableName add check (age<16);--pg

增加检查约束，约束名为：tableName_age_checkalter table tableName add constraint uk_tbl_unique_a_b unique (a,b);--pg

增加唯⼀约束alter table tableName alter column fieldName set NOT NULL;--pg

增加⾮空约束--删除约束alter table tableName drop constraint constraintName;--pg

根据约束名删除检查约束、唯⼀约束alter table tableName alter column fieldName drop NOT NULL;--pg

删除⾮空约束（⾮空约束没有约束名）iii>修改表字段1234567891--增加列添加⼀个字段alter table tableName add column if not exists columnName varchar(30) default 'a' not null; --column

可加可不加--删除列（会连同字段上的约束⼀并删除）alter table tableName drop column columnName; --column

可加可不加--修改列名：alter table tableName rename column fieldName TO fieldNameNew;--pg、oracle中exec sp_rename '[表名].[列名]‘，’[表名].[新列名]'--在sqlserverALTER TABLE 表名 CHANGE 列名 新列名 列类型--mysql--修改字段类型或长度:alter table tableName modify column 字段名 类型;alter table tableName alter column fieldName TYPE text;--pg修改字段数据类型。仅在当前数据都可以隐式转换为新类型时才可以执⾏成功--将NAME最⼤列宽增加到10个字符ALTER TABLE CARD ALTER COLUMN NAME varchar(10)

3）数据查询数据库处理⼀个查询的步骤：客户端连接->查询缓存->解析器->预处理器->查询优化器->查询执⾏引擎->数据123451. 客户端发送⼀条查询给服务器；2. 服务器先会检查查询缓存query cache，如果命中了缓存，则⽴即返回存储在缓存中的结果。否则进⼊下⼀阶段；3. 服务器端进⾏SQL解析parsing、预处理transition，再由优化器optimization⽣成对应的执⾏计划；4. 根据优化器⽣成的执⾏计划，调⽤存储引擎的API来执⾏分布distribution查询；5. 将结果返回给客户端。1>简单查询12select * from student;select 1+2; #当表达式与表列⽆关时，在pg和mysql中不适⽤“from tableName”拼接查询：Access和 SQL Server使⽤ + 号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base 使⽤ ||。1select label || '_' || id from user_info; --结果：abc_12>条件查询功能等于表达=举例备注不等于<>或!=is null，isnot nullin ，notinbetween空值select * from student where class is not null;在sql标准中仅⽀持100个以内的占位符作为查询参select * from student where age not in(21,23);数。根据数据库不同，对in的参数和长度有不同的限制，否则会直接报错。确定集合，in确定范围and , notbetweenand模糊查询like ,notlikeselect * from student where name like '%丽%';’ %代表任意长度（可为0）的字符串；_（下划线）：代表任意单个字符。（汉字代表2个字符，所以⼀个汉字⽤两个下划线）；为转义字符注意：1. select出的别名是否可以作为where查询条件？不能，因为执⾏计划中where在selectz之前。如：select label a from asset_field where a= '分类'2. not in 和<>都不⾛索引，效率⾮常慢，建议⽤EXISTS和not EXISTS、join⽅式替换。（pg orcle）!= 不是标准的SQL，<>才是，这两个在PostgreSQL中是等效的。exits和not exits的意思是逐条将条件下放到判断条件，⽽jion⽅式是先对表进⾏笛卡尔积，然后判断同⾏之间的各列值是否满⾜关系。3>排序查询⾮排序查询的数据顺序：pg默认返回数据的顺序是插⼊表的数据顺序。12345#

单个排序：select name,age from student order by age desc; #

默认为asc：升序排列。desc：降序排序。#多重排序：order by 字段5,字段6 asc //先按字段5排序，再按字段6排序4>case when then查询123456789--简单case函数case sex when '1' then '男' when '2' then '⼥’ else '其他' end--case搜索函数case when sex = '1' then '男' when sex = '2' then '⼥' else '其他' end 应⽤：1234select (case sex when '1' then '男' when '2' then '⼥’ else '其他' end)sex from student where class = 11;5>where、group by、having⼤部分的where都可以背having代替，不同的是where过滤⾏，⽽having过滤分组，⽤在group by之后。(where在分组前过滤，having在分组后过滤）123select class,avg(age) as age from student

group by class

having avg(age)>23 /*要求平均年龄⼤于23*/where肯定在group by 之前where后的条件表达式⾥不允许使⽤聚合函数，⽽having可以。6> 函数聚合函数avg平均数，同min(age)、max(age)、sum(age)1select avg(age) as age from student group by class order by age desc;

count1234select count(class)from student;/*数量 因为使⽤了92标准，所以null不计⼊count*/count(*) 跟count(1) 的结果⼀样，返回记录的总⾏数，都包括对NULL 的统计，count(column) 是不包括NULL 的统计。distinct12select distinct(class)from student;/*去重复，出现所有不同的内容*/select count(distinct(class)) from student;其它12345LEFT（“123456789”，LEN（“数据库”））/*分两步运算，第⼀步是运算LEN函数，结果是3。第⼆步针对123456789这个字符从左边开始连续取三个数*/select top 100 * from student where no=11;/*显⽰前100⾏*/select isnull(name,'⽆') as name,age,class from student;/*isnull之后就⽆列名了 ⽤as给列重命名*/select name,age,class,'the name is' + name as introduce from student;/*⽤加号形成⼀个⾃定义列*/7>SQL-92 规则是数据库的⼀个标准。以下代码 写在存储过程前⾯，表⽰遵从SQL-92 规则。SQL-92 标准要求在对空值进⾏等于 (=) 或不等于 (<) ⽐较时取值为 FALSE。1234SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ON即使 column_name 中包含空值，使⽤ WHERE column_name = NULL 的 SELECT 语句仍返回零⾏。即使 column_name 中包含⾮空值，使⽤ WHERE column_name < NULL 的 SELECT 语句仍会返回零⾏。SET QUOTED_IDENTIFIER ON为ON：标识符可以由双引号分隔，⽽⽂字必须由单引号分隔。为OFF：标识符不可加引号。8>多层查询 EXISTS如果内层查询语句查询到符合条件的记录，就返回⼀个真值（true），否则，将返回⼀个假值（false）。123SELECT * FROM employeeWHERE EXISTS(SELECT d_name FROM department WHERE d_id=1003);同理还有：NOT EXISTS。9>关联查询、联结（JOIN）表关系数据库设计中表的设计是把信息分解成多个表，⼀类数据⼀个表，各表通过某些共同的值相互关联。⼀般情况下我们不建议建⽴外键这种强关联的关联信息。可伸缩（scale）能够适应不断增加的⼯作量⽽不失败。关系数据库的可伸缩性远远优于⾮关系数据库。注意：1. 联结的表越多效率越低。2. SQL本⾝不限制联结表的数⽬，但DBMS有最⼤数⽬限制。3. ⼀般情况下，联结查询⽐⼦查询快，实际应⽤中应该尝试两种⽅法看哪种快。JSON类型JOINLEFT JOINRIGHT JOINFULL JOIN说明如果表中有⾄少⼀个匹配，则返回⾏即使右表中没有匹配，也从左表返回所有的⾏即使左表中没有匹配，也从右表返回所有的⾏只要其中⼀个表中存在匹配，就返回⾏备注INNER已省略。外联结⽐内联结返回的⾏数多（还包括没有关联的⾏）OUTER已省略OUTER已省略OUTER已省略luo_persons表：id_plast_namefirst_nameaddresscityluo_orders表：id_oorder_noid_p要求输出：谁订购了产品，并且他们订购了什么产品？①联表查询（等值联结，equijoin）1234567SELECT _name, _name, _noFROM luo_persons a, luo_orders b

WHERE _p = _p

②join查询（内联结，inner join， 推荐）/*（推荐）等值联结明确指定联结类型可转换为inner join1234567SELECT last_name, first_name, order_no

FROM luo_persons INNER JOIN luo_orders ON luo__p = luo__p③union查询（复合查询、并查询）UNION 操作符⽤于合并两个或多个 SELECT 语句的结果集。注意：1. UNION 内部的 SELECT 语句必须拥有相同数量的列、表达式或聚集函数。列也必须拥有相似的数据类型（可以不完全相同，但是可以互相转换）。同时，每条 SELECT 语句中的列的顺序必须相同。2. 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使⽤ UNION ALL。3. UNION能组合的最⼤语句数⽬限制需要查询具体的DBMS⽂档。123 select id_p from luo_persons

union

SELECT id_p from luo_orders某些DBMS中还⽀持其它类型的UNION：1. EXCEPT（或MINUS）：检索在第⼀个表中存在⽽在第⼆个表中不存在的⾏；2. INTERSECT：检索两个表中都存在的⾏。4）数据更新①数据插⼊i> insert123insert into tableName(no,name) values（'1','kate');--按表中列的顺序，但如果表结构发⽣了变化那么对应 sql也要改。不推荐insert into product values('001','001','N','N');有⾃增长主键(id)的插⼊：i>可以把id的值设置为null或者0，这样mysql会⾃⼰做处理ii>⼿动指定需要插⼊的列，不插⼊这⼀个字段的数据！ii> insert select将select结果插⼊表中，⼀般⽤于可重复执⾏的sql。注： select语句中，如果select返回多⾏，那么会insert多⾏数据。12INSERT INTO "public"."vendors"("vend_name", "vend_id") select 'vend_name1', 1

WHERE NOT EXISTS (select 1 FROM "public"."vendors" WHERE vend_id = 1);iii> select into1. SELECT INTO 语句从⼀个表中选取数据，然后把数据插⼊另⼀个表中。2. SELECT INTO 语句常⽤于创建表的备份复件或者⽤于对记录进⾏存档。3. select into 可以从多个表中检索数据，但只能插⼊到⼀个表中。函数⾥⾯，把⼀个查询出来的值存⼊临时变量：12SELECT LastName,FirstNameINTO _lName,_fName FROM Persons也可以存⼊临时表中：123SELECT *INTO Persons_backupFROM Persons②数据修改12update tableName set name = 'Tom' where name='kate';update tableName set age = age + 1;5）数据删除删除表中⼏⾏：1DELETE FROM Person WHERE LastName = 'Wilson'

删除表中所有⾏，保留表、不释放空间。所删除的每⾏记录都会进⽇志，可以回滚。1DELETE FROM table_name删除表：删除内容和定义，释放空间12drop table user;

DROP TABLE IF EXISTS "public"."role_relation"; 可重复执⾏sql删除表中所有数据，保留表、同时释放空间（速度⽐delete快，但是⽆法撤回，⽇志⾥⾯只记录页释放）：1truncate table book;truncate是DDL语句（Data Definition，数据定义语句），相当于⽤重新定义⼀个新表的⽅法把原表的内容直接丢弃了，所以执⾏起来很快。delete语句是DML语句（Data Manipulation，数据操作语句），把数据⼀条⼀条的删除，所以删除多⾏数据执⾏较慢。6）其他注意①加中括号列名、表名、存储过程名、函数名等都可以按需要加中括号。防⽌某些关键字在应⽤中引起歧义。1select [select] from 表名;7）数据库授权①授权GRANT1234 GRANT <权限> ON <对象类型> <对象名> TO <⽤户> [WITH GRANT OPTION] // 如果指定了WITH GRANT OPTION⼦句，则获得某种权限的⽤户还可以把这种权限再授予其他⽤户，允许⽤户传递权限，但是不举例：11121314例1：把查询Student表的权限授给⽤户U1GRANT SELECTON TABLE StudentTO U1;例2：把全部操作权限授予⽤户U2和U3GRANT ALL PRIVILEGESON TABLE Student,CourseTO U2,U3;例3：把查询权限授予所有⽤户GRANT SELECTON TABLE SCTO PUBLIC;③权限的收回 REVOKE123举例：1234例6：收回所有⽤户对表sc的查询权限REVOKE SELECTON TABLE SCFROM PUBLIC;REVOKE <权限>ON <对象类型> <对象名>FROM <⽤户>③对⽤户模式的授权由DBA（数据库管理员，Database Administrator，简称DBA）在创建⽤户时实现。12CREATE USER [WITH] [DBA|RESOURCE|CONNECT]只有系统的超级⽤户才有权创建⼀个新的数据库⽤户新创建的⽤户有三种权限：DB，|RESOURCE，CONNECT④数据库⾓⾊创建及授权1CREATE ROLE <⾓⾊名>给⾓⾊授权：123GRANT <权限>ON <对象类型> 对象名TO <⾓⾊>将⼀个⾓⾊授予其他的⾓⾊或⽤户123GRANT <⾓⾊1>TO <⾓⾊3>[WITH ADMIN OPTION]//如果指定了WITH ADMIN OPTION ⼦句，则获得某种权限的⾓⾊或⽤户还可以把这种权限再授予其他⾓⾊⾓⾊权限的收回123REVOKE <权限>ON <对象类型> <对象名>FROM <⾓⾊>⑤DENY 拒绝账户访问在安全系统中创建⼀项，以拒绝给当前数据库内的安全帐户授予权限并防⽌安全帐户通过其组或⾓⾊成员资格继承权限。12DENY { ALL | statement [ ,...n ] }TO security_account [ ,...n ]和授权区别：不授权是没有权限，但是如果这个⽤户属于某个⾓⾊，这个⾓⾊有了权限，那么这个⽤户可以从⾓⾊继承这个权限。如果选择了deny，即使这个⽤户属于某个具有权限的⾓⾊，他也没有权限。8）数据类型①uniqueidentifier可存储16字节的⼆进制值，其作⽤与全局唯⼀标记符（GUID）⼀样。GUID是唯⼀的⼆进制数：世界上的任何两台计算机都不会⽣成重复的GUID值。GUID主要⽤于在⽤于多个节点，多台计算机的⽹络中，分配必须具有唯⼀性的标识符。9）函数①OBJECT_IDA. 返回指定对象的对象 ID1234USE master;GOSELECT OBJECT_ID(N'der') AS 'Object ID';GOB. 验证对象是否存在12345USE AdventureWorks;GOIF OBJECT_ID (N'dVersion', N'U') IS NOT NULLDROP TABLE dVersion;GON是显式的将⾮unicode字符转成unicode字符，它来⾃ SQL-92 标准中的 National（Unicode）数据类型，⽤于扩展和标准化，在这⾥可以不⽤，写作object_id(PerPersonData)。10）SQL中的借书经典案例①问题描述本题⽤到下⾯三个关系表：CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级BOOKS 图书。 BNO 书号，BNAME 书名, AUTHOR 作者，PRICE 单价，QUANTITY 库存册数BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书⽇期备注：限定每⼈每种书只能借⼀本；库存册数随借书、还书⽽改变。要求1． 写出建⽴BORROW表的SQL语句，要求定义主码完整性约束和引⽤完整性约束。12345CREATE TABLE BORROW( CNO int FOREIGN KEY REFERENCES CARD(CNO), BNO int FOREIGN KEY REFERENCES BOOKS(BNO), RDATE datetime, PRIMARY KEY(CNO,BNO))

要求2． 找出借书超过5本的读者,输出借书卡号及所借图书册数。1234SELECT CNO,借图书册数=COUNT(*)FROM BORROWGROUP BY CNOHAVING COUNT(*)>5要求3． 查询借阅了"⽔浒"⼀书的读者，输出姓名及班级CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级BOOKS 图书。 BNO 书号，BNAME 书名, AUTHOR 作者，PRICE 单价，QUANTITY 库存册数BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书⽇期123456SELECT * FROM CARD cWHERE EXISTS( SELECT * FROM BORROW a,BOOKS b

WHERE = AND =N'⽔浒' AND =)

要求4． 查询过期未还图书，输出借阅者（卡号）、书号及还书⽇期。12SELECT * FROM BORROW

WHERE RDATE

要求5． 查询书名包括"⽹络"关键词的图书，输出书号、书名、作者。12SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE BNAME LIKE N'%⽹络%'

N’string’ 表⽰string是个Unicode字符串要求6． 查询现有图书中价格最⾼的图书，输出书名及作者。123SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE PRICE=( SELECT MAX(PRICE) FROM BOOKS)

要求7. 查询当前借了"计算⽅法"但没有借"计算⽅法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。123456789SELECT M BORROW a,BOOKS bWHERE = AND =N'计算⽅法' AND NOT EXISTS( SELECT * FROM BORROW aa,BOOKS bb WHERE = AND =N'计算⽅法习题集' AND =)ORDER BY DESC

要求8． 将"C01"班同学所借图书的还期都延长⼀周。1234UPDATE b SET RDATE=DATEADD(Day,7,)FROM CARD a,BORROW bWHERE = AND =N'C01'

12DATEADD(datepart,number,date)

date 参数是合法的⽇期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。要求9． 从BOOKS表中删除当前⽆⼈借阅的图书记录。1234DELETE FROM BOOKS aWHERE NOT EXISTS( SELECT * FROM BORROW WHERE BNO=)

要求11．在BORROW表上建⽴⼀个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应⽤"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。12345678CREATE TRIGGER TR_SAVE ON BORROWFOR INSERT,UPDATEASIF @@ROWCOUNT>0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE = AND =N'数据库技术及应⽤'

要求13．查询当前同时借有"计算⽅法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。1234567SELECT M BORROW a,BOOKS bWHERE = AND IN(N'计算⽅法',N'组合数学')GROUP BY ING COUNT(*)=2ORDER BY DESC5，6，关系运算1）集合运算符并（∪）、差（-）、交（∩）、笛卡尔积（×）123笛卡尔积（直积）：表⽰为X × Y，第⼀个对象是X的成员⽽第⼆个对象是Y的所有可能有序对的其中⼀个成员。例如，A={a,b}, B={0,1,2}，则A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}2）专门的关系运算符①选择（限制、σ）在关系R中选择满⾜给定条件的诸元组。②投影（π）关系R上的投影是从R中选择出若⼲属性列组成新的关系。投影之后可既改变⾏，⼜改变元组的数量。③连接（θ连接、⋈）从两个关系的笛卡尔积中选取属性间满⾜⼀定条件的元组。（连接由乘积（笛卡尔积）、选择、投影组成）分为等值连接（=）、⾃然连接（要求⽐较的分量是相同的属性组，并在结果中把重复的属性列去掉）。④除运算（