mysql中谓语_数据库概论相关总结-校招准备

数据库基础

概念与基础知识数据库三级模式使不同级别的用户对数据库形成不同的视图。所谓视图，就是指观察、认识和理解数据的范围、角度和方法

可达到达到了数据独立性

概念模式概念级对应概念模式

是数据库中全部数据的逻辑结构和特征的描述

它由若干个概念记录类型组成，只涉及行的描述，不涉及具体的值

描述概念模式的数据定义语言称为“模式DDL”

外模式用户级对应外模式

是用户与数据库系统的接口，是用户用到的那部分数据的描述

程序员不必关心概念模式，只与外模式发生联系，按外模式的结构存储和操作数据。内模式物理级对应内模式

内模式也称为存储模式，是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式

锁的类型共享锁(Share locks简记为S锁)：也称读锁

排它锁(Exclusivelocks简记为X锁)变量的使用@V:=

into @V

三大范式满足着第三范式，那么就一定满足第二范式、满足着第二范式就一定满足第一范式

第一范式：字段是最小的的单元不可再分

第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键。

第三范式：满足第二范式，非主键外的所有字段必须互不依赖表连接方式A,B

A join B

A inner join B

子查询类别子查询是将一个 SELECT 语句的查询结果作为中间结果，供另一个 SQL 语句调用

相关子查询与非相关子查询非相关子查询的执行不依赖与外部的查询。

相关子查询的执行依赖于外部查询。 多数情况下是子查询的WHERE子句中引用了外部查询的表。

谓语来分In/all/any/some查询some any等价

all满足所有条件exists

其他子查询spj子查询,有选择连接投影组成

groupby子查询

结果集来分标量子查询

列子子查询

行子查询

表子查询

视图

表与视图的区别视图是已经编译好的sql语句。而表不是

视图没有实际的物理记录。而表有。

表是内容，视图是窗口

表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改

表是内模式，视图是外模式

视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。

表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。

视图的建立和删除只影响视图本身，不影响对应的基本表。

表和视图的联系视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表

它依据基本表存在而存在

一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

关系型数据库表的设计

过程描述需求

概念设计ER模型

逻辑设计把概念模型转换为关系模型物理设计文件组织格式和索引结构

范式概述范式化中每个事实数据只出现一次

反范式化中,信息是冗余的

范式化优点数据表更小

更新等操作比非范式快(关联

范式化缺点需要关联

查询语句代价昂贵

会使一些索引策略无效数据库范式知道吗范式一强调数据表的原子性。属性不可分

字段是最小的的单元不可再分

二范式就是要有主键， 要求其他字段都依赖于主键。,表中的字段必须完全依赖于全部主键而非部分主键三范式就是要消除传递依赖非主键外的所有字段必须互不依赖

就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依

混用部分范式化更好

缓存表

汇总表

关系代数

基础概念关系模型的数据结构就是数据库表的二维结构

数据库上的所有操作都可以被五种基本操作推导出来

组成关系数据结构

关系操作集合

专门的关系运算符选择select单个关系筛选元组,对应的是限制条件where condiction

优化方式是选择操作下推[即在查询树的底部先做筛选]

投影project单个关系筛选列select co1,co2

选择操作下推并 section拓展:交集 R交S=r - ( r -s )

差

交

笛卡尔积 连接join多个关系根据列间的逻辑运算筛选元组

自然连接

内连接

外连接左外连接

右外连接

全外连接

Sql小技巧

mysql局部变量使用@V 使用变量

@:V=1 用来给变量赋值

count(*) 和 count(列名)count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行

使用 ISNULL()来判断是否为 NULL 值

集合运算union 并运算

intersect 交运算

except 差运算 - t1 - t2

集合比较

in 和exists的区别?内表和外表的概念select * from user where exists (select 1);

user 外表

select1 内表

1: select * from A where exists (select * from B where B.id = A.id);

2: select * from A where A.id in (select id from B);

inin查询相当于多个or条件的叠加

in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积

然后按照条件进行筛选exists含义子查询为空时

返回True

并不是先算子查询,而是先看前面

而exists是对外表作loop循环，每次loop循环再对内表进行查询

all和any/some 的区别?all所有的SELECT DISTINCT 系号 FROM 教师 WHERE工资>= (SELECT MAX(工资) FROM 教师 WHERE 系号="02")

任意一个人的工资都大于这个系的

同一意思的是 Max()

any some任意一个

delete、truncate 、Drop删除表的区别drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

truncate 删除全部

delete 可以带where

表连接类型

自然连接与笛卡尔积的区别就是根据属性相等的关系 进行连接

内连接natural join

左外natural left join、右外natural right join

MySql会使用表内的，相同的字段，作为连接条件

无须自己添加连接条件

内连接内连接与自然连接基本相同

不同之处在于自然连接只能是同名属性的等值连接，而内连接可以使用using或on子句来指定连接条件

on 或 using

外连接左外连接

右外连接

全连接

笛卡尔积对两个表之间进行连接形成的是笛卡尔积

两个表任意两个都要组合

on和where在外连接中on是外连接声明的一部分,并不会排除外连接的主表记录

where是在连接后的结果中操作, 会排除记录

sql中的运算

更名运算 as

子查询谓语

like/in/exists/all/somelike操作符通配符

inin查询相当于多个or条件的叠加

与in后面子查询关系不大

等价于：1、SELECT id FROM B ----->先执行in中的查询2、SELECT * FROM A WHERE A.id = B.idexists主查询的结果集放到子查询中做验证

1、SELECT * FROM A;

2、SELECT I FROM B WHERE B.id = A.id;all所有数据都满足条件

any/some任一数据都满足条件

操作函数字符串函数

聚集函数avg

min

max

sum

count

集合运算union并集

intersect 交集

差运算 except第一个输出中,减去第二个输出中出现的

查询语句 select 格式: 序号即为执行顺序

- (8)SELECT (9)DISTINCT

- (1)FROM [left_table]

- (3) JOIN

- (2) ON

- (4)WHERE

- (5)GROUP BY

- (6)WITH

- (7)HAVING

- (10)ORDER BY

- (11) limit

视图概述是从一个或多个表(或视图)导出的表

视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义

mysql不支持物化视图

定义create view vas 优点数据库视图允许简化复杂查询

安全性

数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

缺点改变表,视图也会受影响

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。

使用场景存储过程处理比较复杂的业务时比较实用。比如说，一个复杂的数据操作。如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话。就只有一次。从响应时间上来说有优势。也就是说存储过程可以给我们带来运行效率提高的好处。另外，程序容易出现 BUG 不稳定，而存储过程，只要数据库不出现问题，基本上是不会出现什么问题的。也就是说从安全上讲，使用了存储过程的系统更加稳定。

触发器触发器是一种特殊的存储过程，存储过程需要手动调用，而触发器是经过事件触发而执行的，触发器是绑定一张表，在表中创建保存，而存储过程是在一个数据库中创建并保存。触发器=存储过程+事务+自定义约束

是一条语句在数据库做更改的时候,自动执行

引起触发器检测的事件

执行的操作

聚集函数 与 窗口函数

普通聚集函数avg

min

max

sum

count

关于聚合可以用在select中

也可以用在, having中

高级聚集函数 开窗函数

开窗函数 : 聚合函数如max/min/avg/特殊函数rank() + over(partition by .. [order by ..])

定义: 与 聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样, 每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口; 普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值, 窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列;

SQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持, - PS mysql作出这个操作我能理解, 毕竟我了解到的mysql是追求性能和功能够用的, hash join 不是也不支持吗, 正常操作

用法 - over() 按所有行进行分组 - over(partition by xxx) 按xxx分组的所有行进行分组 - over(partition by xxx order by aaa) 按xxx分组，按列aaa排序 的按到当前行(含当前行)进行分组 - over前可以加聚合函数 例如sum count avg min max等 - over前也可以加 first_value last_value等 - rank() over等的用法

例子: 要求:取出各个城市销售金额在TOP50的店铺ID,并给出shop_ id, 城市排名，当天的销售额, 一行命令直接出结果

#假设存在一张表名为 TABLE3

order_ id shop_ id city pay_ time pay_ amt

1234561 S121 杭州 2018-03-16 12:32:05 19.3

1234562 S126 上海 2018-03-16 09:25:31 1045

1234563 S133 南京 2018-03-16 13:57:46 888.8

1234564 S131 北京 2018-03-16 20:42:12 109.9

1234565 S131 上海 2018-03-16 19:42:12 388.0使用窗口函数实现

select city, shop_id,pay_amt, rank() over (partition by city order by pay_amt des) as rank

from TABLE3使用mysql临时变量实现

#使用临时变量在mysql中,实现一个开窗函数的功能

select city,shop_id,pay_amt,new_rank as rank from

(select *, if(@tmp=city ,@rank:=@rank + 1,@rank:=1) as new_rank, @tmp:=city as tmp

from table3 order by city,pay_amt desc)b

where new_rank <= 2 order by city;