mysql学习笔记之四,基础Sql语句之二

一、视图介绍
1.1视图定义:是一个虚拟表,本身不包含数据,行和列的数据来自于自由定义视图查询所引用的列,在引用视图的时候动态生成。和普通的表格使用方式一致。
1.2视图的优点:
①简化操作:将复杂的sql关联查询和筛选条件进行视图封装,使用较为方便。
②安全性:视图可以定制可以查询到的数据(列值),提高了数据的安全性,用于权限控制。
③调高了数据查询效率。
④实现了一定层度上的逻辑独立性。
1.3视图和表格区别
图片 1
二、视图的使用
2.1视图的创建
语法:create view 视图名
as
查询语句;
表结构:
图片 2

Mysql 学习笔记(四)

mysql学习笔记之四(视图)

视图

通过对视图的操作不仅可以实现查询的简化,而且还会提高安全性视图:本质是一种虚拟表,其内容和真实表相似,包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储数据值的形式存在。行和列数据来自定义视图的查询所引用基本表,并且在具体引用视图时动态生成。视图使程序员只关心感兴趣的某些特定数据和他们所负责的特定任务。这样程序员只能看到视图所定义的数据而不是视图所引用表中的数据。从而提高了数据库中数据逇安全性。

 

特点:
1、视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系
2、视图由基本表(实表)产生的表(虚表)
3、视图的建立和删除不影响基本表
4、对视图内容的更新(添加,删除,修改)直接影响基本表
5、当视图来自多个表时,不允许添加删除数据(这个应该好理解,因为牵涉到了多个表,显然删除,添加都是不方便的或者说不安全的)

创建

create view viewname例:
create table t_product(

id int,

name varchar(20),

price float

)//创建视图
create view view_product as select id,product from t_product
失败了,原来定义成了name,改一下属性
alter table t_product change name product varchar(20);
再次创建视图,OK
select * from view_product
OK,这样就将price字段隐藏了。

视图大家庭

由于视图实际上是封装查询语句(可以想到吧),那么是不是任何形式的查询语句都可以封装成视图?

查询视图
常量查询:

create view view_const as select 3.1415926;

聚合函数(COUNT MIN SUM MAX等)

create view view_fun as select count(name) from t_student

排序(ORDER BY)

CREATE VIEW view_order as select name from t_student order by id DESC

内连接查询语句

create view view_join
as
select s.name
from t_student as s,t_group as g
where s.group_id=g.id and g.id=2

 

外连接(left join 和 right join)

create view view_left
as
select s.name
from t_student as s left join t_group as g on s.group_id=g.id
where g.id=2

 

子查询  

create view view_child

as
select s.name
from t_student as s
where s.group_id in (select id from t_group)

记录联合(union 和 union all)

create view view_union
as
select id,name from t_student
union all
select id,name from t_group

 

查看视图

show tables ,show table status(由于view是一个特殊的表,所以查看表也就可以查看到view), show create view viewname
1、show table status like "view_product"G
以上面创建的view_product为例
2、show create view view_productG
查看视图的定义
3、describe|desc
desc viewname

删除视图

drop viewname[,viewname,...]
可以一次删除多个view

修改视图

1、create or replace view view_name

以view_product为例

现在想要隐藏id字段。

a、可以先删除视图,重新创建一个视图

b、create or replace view viewname as

如:create or replace view view_product as select id from t_product

这样就不需要先删除在创建

2、alter语句(猜着就有)

alter view viewname

as 查询语句

视图 通过对视图的操作不仅可以实现查询的简化,而且还会提高安全性视图:本质是一种虚拟表,其内容和真实...

汇总数据
函数 说明 实例
avg() 返回某列的均值 select avg(C0) from T0
count() 返回某列的列数 select count(C0) from T0
max() 返回某列的最大值 select max(C0) from T0
min() 返回某列的最小值 select min(C0) from T0
sum() 返回某列的和 select sum(C0) from T0
  • 以上函数均忽略列值为null的行,除了 count(*),其会对表中行的数目进行计数,不管列中包含的是否是null
  • 以上五个聚集函数默认都会使用all,也可以显示的使用distinct参数来忽略相同的值
    select count(distinct C0) from T0

需求1:查询员工last_name like “c”的lase_name(员工名), department_name(部门名),job_title(工种名)
图片 3

视图(View)

➢ 从用户角度来看,一个视图是从一个特定的角度来查看数据库中的数据。从数据 库系统内部来看,一个视图是由SELECT 语句组成的查询定义的虚拟表,视图是由一 张或多张表中的数据组成的,从数据库系统外部来看,视图就如同一张表一样,对表能够进行的一般操作都可以应用于视图,例如查询,插入,修改,删除操作等。 视图是一个虚拟表,其内容由查询定义。

➢ 视图概述:

✓ 视图以经过定制的方式显示来自一个或多个表的数据

✓ 视图是一种数据库对象,用户可以象查询普通表一样查询视图。

✓ 视图内其实没有存储任何数据,它只是对表的一个查询。

✓ 视图的定义保存在数据字典内。创建视图所基于的表为“基表”。

✓ 视图一经定义以后,就可以像表一样被查询、修改、删除和更新

➢ 视图作用

✓ 简化数据查询语句

✓ 使用户能从多角度看到同一数据

✓ 提高了数据的安全性

✓ 提供了一定程度的逻辑独立性

✓ 减少带宽流量、优化后还可提高执行效率

➢ 视图优点

✓ 提供了另外一种级别的表安全性

✓ 隐藏的数据的复杂性

✓ 简化的用户的 SQL 命令

✓ 通过重命名列,从另一个角度提供数据

分组数据
  • 分组允许把数据分为多个逻辑组,以便对每个组进行聚集运算
  • 利用group by进行分组
  • group by子句可以包含任意数目的列,使得能对分组进行嵌套
  • 如果在group by子句中嵌套了分组,数据将在最后规定的分组上进行汇总
  • 分组列中具有null值,则null将作为一个分组返回
  • with rollup可以得到每个分组以及每个分组汇总级别的值
  • having用于过滤分组
  • gourp by关键字可以和group_concat()函数一起使用。group_concat()函数会把每个分组中指定的字段值都显示出来

图片 4

图片 5

需求2:查询各个部门的平均工资的级别
图片 6

(1)视图的创建

1.创建命令

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

注:

➢ OR REPLACE:给定了 OR REPLACE 子句,语句能够替换已有的同名视图。

➢ ALGORITHM:可选的 mysql 算法扩展,算法会影响 MySQL 处理视图的方 式。有以下三个值: UNDEFINED--MySQL 将选择所要使用的算法。如果可能,它倾向于 MERGE 而不是 TEMPTABLE,这是因为 MERGE 通常更有效,而且如果使用了临时表,视图是不可更新的。 MERGE--会将引用视图的语句的文本与视图定义合并起来,使得视图定义 的某一部分取代语句的对应部分。TEMPTABLE--视图的结果将被置于临时表中,然后使用它执行语句。

1、MERGE,将视图的sql语句和引用视图的sql语句合并在一起,最后一起执行。

2、TEMPTABLE,将视图的结果集存放在临时表中,每次执行时从临时表中操作。

3、UNDEFINED,默认的视图类型,DBMS倾向于选择而不是必定选择MERGE,因为MERGE的效率更高,更重要的是临时表视图不能更新。所以,这里推荐使用MERGE算法类型视图。

view_name :视图名

column_list: 要想为视图的列定义明确的名称,列出由逗号隔开的列 名。column_list 中的名称数目必须等于 SELECT 语句检索的列数。若使用与源表或视图中相同的列名时可以省略 column_list。

select_statement : 用来创建视图的 SELECT 语句,可在 SELECT 语 句中查询多个表或视图。但对 SELECT 语句有以下的限制: 1. 定义视图的用户必须对所参照的表或视图有查询(即可执行 SELECT 语句)权限; 2. 在定义中引用的表或视图必须存在;

WITH [cascaded|local] CHECK OPTION:在关于可更新视图的 WITH CHECK OPTION 子句中,当视图是根据另一个视图定义的时,LOCAL 和 CASCADED 关键字决定了检查测试的范围。LOCAL 关键字对 CHECK OPTION 进行了限制,使其仅作用在定义的视图上,CASCADED 会对将进行评估的基表进行检查。如果未给定任一关键字,默认值为 CASCADED。WITH CHECK OPTION 指出在可更新视图上所进行的修改都要符合 select_statement 所指定的 限制条件,这样可以确保数据修改后,仍可通过视图看到修改的数据。

例子:create view student_index as select sno,sname,from student; 

图片 7

视图--1

使用子查询
  • 子查询:嵌套在其他查询中的查询,在select语句中子查询总是自内而外进行的
  • 相关子查询:涉及外部查询的子查询
  • 当列名可能存在多义性,就必须使用完全限定表名

图片 8

图片 9

2.2视图修改:
① create or replace view 视图名
as
查询语句;
② alter view 视图名
as
查询语句;
图片 10
图片 11

(2)修改视图

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

注: 该语句用于更改已有视图的定义。其语法与 CREATE VIEW 类似。该语句需要具有 针对视图的 CREATE VIEW 和 DROP 权限,也需要针对 SELECT 语句中引用的每一列的 某些权限。

例子:alter view student_index as select sno as '学号',sname as '姓名',sage as '年龄' from student; 

图片 12

视图--2

联结表
  • 相同数据出现多次不是一件好事,此因素是关系型数据库设计的基础。关系表的设计就是要保证把信息分解成多个表,一类数据一个表。各表通过某些常用的值互相关联
  • 外键:外键为某个表的一列,它包含另一个表的主键值,定义了两个表之间的关系。
  • 联结是一种机制,用来在一条select语句中关联表。
![](https://upload-images.jianshu.io/upload_images/11143861-42d3adac2fa56cde.png)
  • 内部联结:基于两个表之间的相等测试,对于这种联结可以使用 inner join on
![](https://upload-images.jianshu.io/upload_images/11143861-f7780a3dc2a5c296.png)
  • sql对一条select语句可以联结的表的数目没有限制。不要联结不必要的表,联结的表越多,性能下降的越厉害
  • 笛卡尔积: 由没有联结条件的表关系返回的结果成为笛卡尔积。检索出的行的数目为第一个表中的行数乘以第二个表的行数

2.3查询视图
①desc chen;
②show create view chen02;
2.4删除视图
Drop view 视图名,视图名,(可多个);
三、视图数据更新
3.1可以实现:
数据的插入
数据的修改
数据的删除
会对原始表改变。【相应的CRUT】
图片 13
图片 14
图片 15

(3)查看视图

SHOW CREATE VIEW view_name;

例子:show create view student_index; 

图片 16

视图--1

图片 17

视图--3

创建高级联结
  • 别名除了用于列名和计算字段外,sql还允许给表名起别名,用于缩短sql语句,并且允许在单条select语句中多次使用相同表
  • 自联结
![](https://upload-images.jianshu.io/upload_images/11143861-0a32f4a140c4d55b.png)
  • 自然联结(natural join)是一种特殊的等值联结,它要求两个关系中进行比较的分量必须是相同的属性组,并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列
![](https://upload-images.jianshu.io/upload_images/11143861-52e615e4d57f7a06.png)
  • 外部联结:联结包含那些在相关表中没有关联行的行,使用关键字 right / left outer join

    图片 18

3.2不可更新的视图
①包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
②常量视图
③Select中包含子查询
④含有 join 语句
⑤from一个不能更新的视图
⑥where子句的子查询引用了from子句中的表
图片 19

(4)删除视图

DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]

说明:

1、DROP VIEW 能够删除 1 个或多个视图。必须在每个视图上拥有 DROP 权限。

2、可以使用关键字 IF EXISTS 来防止因不存在的视图而出错。

3、如果给定了 RESTRICT 和 CASCADE,将解析并忽略它们。

例子:drop view student_index; 

图片 20

视图--4

组合查询
  • 有两种情况需要用到组合查询:
    A:在单个查询中从不同表返回类似的结构的数据
    B:在单个表执行多个查询,按单个查询返回结果
  • 使用union来实现组合查询,规则:
    A:union必须由两条或以上的select语句组成,语句之间使用union分隔
    B:union的每个查询都必须包含相同列、表达式或聚集函数(列的顺序可以不同)
    C:列数据类型必须兼容:类型不必完全相同,但必须是能隐式转换的类型
  • union会从结果集中去除重复行,使用union all 可以返回所有匹配行
  • 使用union组合查询的时候,只能使用一条order by子句,它必须出现在最后一条select语句后

图片 21

(5)更新视图

概述:

➢ 视图的使用与表一样,有增删改查四种操作,且语法也与表相同。

➢ 在视图上也可以使用修改数据的 DML 语句,如 INSERT、UPDATE 和 DELETE 可以统称为“通过视图更新数据”。

➢ 通过视图更新数据有如下限制:

✓ 一次只能修改一个底层的基表

✓ 如果修改违反了基表的约束条件,则无法更新视图

✓ 如果视图中的列不是表中的原始列(如创建视图时使用了连接操作符、 聚合函数等),则不能通过视图更新。

视图更新操作:

➢ 可更新的视图:要通过视图更新基本表数据,必须保证视图是可更新视图, 即可以在 INSET、UPDATE 或 DELETE 等语句当中使用它们。对于可更新 的视图,在视图中的行和基表中的行之间必须具有一对一的关系。还有一 些特定的其他结构,这类结构会使得视图不可更新。

如果视图包含下述结 构中的任何一种,那么它就是不可更新的:

✓ 聚合函数;

✓ DISTINCT 关键字;

✓ GROUP BY 子句;

✓ ORDER BY 子句;

✓ HAVING 子句;

✓ UNION 运算符;

✓ 位于选择列表中的子查询;

✓ FROM 子句中包含多个表;

✓ SELECT 语句中引用了不可更新视图;

➢ 插入数据

使用 INSERT 语句通过视图向基本表插入数据

注意:

✓ 当视图所依赖的基本表有多个时,不能向该视图插入数据,因为这将 会影响多个基本表。 ✓ 对 INSERT 语句还有一个限制:SELECT 语句中必须包含 FROM 子句 中指定表的所有不能为空的列。

➢ 修改数据:使用 UPDATE 语句可以通过视图修改基本表的数据 注意:若一个视图依赖于多个基本表,则一次修改该视图只能变动一个基本表的数据。

➢ 删除数据:使用 DELETE 语句可以通过视图删除基本表的数据 注意:对依赖于多个基本表的视图,不能使用 DELETE 语句。

图片 22

视图--5

全文本搜索
  • 使用like和regexp进行文本的搜索有几个缺点:
    A:性能不高,通配符和正则表达式通常要求mysql尝试匹配表中所有行,由于行数多,这些搜索可能很耗时
    B:使用正则表达式和通配符很难明确控制
    C:虽然基于通配符和正则表达式的搜索提供了非常灵活的效率,但它们都不能提供一种智能化的选择结果
  • 一般在创建表时启用全文本搜索。create table语句接受fulltext子句。不要在导入数据时使用fulltext,而应该首先导入数据,然后定义fulltext,这样效率比较高
![](https://upload-images.jianshu.io/upload_images/11143861-a53a573d5fa66a0c.png)
  • 在索引之后,使用match()指定被搜索的列,against()指定要使用的搜索表达式。
    传递给match的值必须与fulltext中定义的相同,而且若指定多个列,列的顺序也要正确
    全文本搜索的一个重要部分是对结果排序,具有较高等级的行先返回,文中词靠前的行的等级比词靠后的行高
![](https://upload-images.jianshu.io/upload_images/11143861-2f92d79099a497ab.png)
  • 使用扩展查询(with query expansion):
    mysql对数据和索引进行两边扫描来完成搜索,首先找出与搜索条件匹配的所有行,其次mysql检查这些匹配行并选择所有有用的词,然后在进行一次全文本搜索,不仅使用原来的条件,而且还使用所有有用的词。扩展查询增加了返回的行数,但是也返回了一些没用的行
![](https://upload-images.jianshu.io/upload_images/11143861-3ad5af7887a6a910.png)
  • 布尔文本搜索(in boolean mode)
  • 在布尔方式中,不按等级值降序排序返回行,即只排列而不排序
    可以在非fulltext列中进行布尔搜索,但是效率很低
  • 全文本搜索说明:
    A:在索引全文本数据时,短词将被从索引中排除。短词定义为具有3个及以下的词(数目可更改)
    B:mysql带有一个内建的非用词列表,这些词在索引中总是被忽略(此表可被覆盖)
    C:如果一个词在行中出现的频率大于百分之50,则将其作为非用词进行忽略。此规则不用于布尔搜索中
    D:仅在myisam数据库引擎中支持全文本搜索
    E:对于汉语不能恰当的返回全文本搜索结果
    F:忽略词中的引号,如don't索引为dont
布尔操作符 说明
包含,词必须存在
- 排除,词必须不存在
> 包含,增加等级
< 包含,减少等级
() 把词组成表达式
~ 取消一个值的排序值
* 词尾通配符
"" 定义一个短语,与单个词的列表不同,它将匹配整个短语

图片 23

图片 24

插入数据
  • insert用来插入行到数据库表中。插入可以以下几种方式进行:
    A:插入完整行
    B:插入行的一部分
    C:插入多行
    D:插入查询结果
  • 插入时候最好提供列名,依赖列定义的顺序是不安全的
    insert into T0 (C0, C1, C2) values (V0, V1, V2)
  • 如果表的定义中,某列允许被定义为NULL或者有默认值,则可以在insert中省略该列。可以在插入的时候指定低优先级的插入(在update和delete时候也生效):
    insert low_priority into T0 (C0, C1, C2) values (V0, V1, V2)
  • 插入多行(比分次插入效率高)
    insert low_priority into T0 (C0, C1, C2) values (V0, V1, V2), (V3, V4, V5)
  • 插入检索出来的值

    图片 25

更新和删除数据
  • 为了更新表中的数据,可以使用update语句。可以更新所有行或者指定行
![](https://upload-images.jianshu.io/upload_images/11143861-d243a4debcf29d67.png)
  • 当更新失败的时候,则整个update被取消,若即使发生错误也继续进行则使用ignore
  • 为了删除某个列的值,可以设置其为null(当此列允许为null值时)
![](https://upload-images.jianshu.io/upload_images/11143861-2b0ad4f247e8f13b.png)
  • 为了从表中删除数据,可以使用delete语句,可以从表中删除特定行或者所有行
  • 若要删除表内所有数据,一般使用truncate table 语句。此语句实际上是删除原来的表并重新创建一个表,而不是逐行进行删除表内数据

    图片 26

创建和操纵表
  • 使用create table语句来创建表,若是仅仅是想在表不存在的情况下创建表可以使用create table if not exists
  • 主键值必须唯一,即表中的每个行必须具有唯一的主键。如果主键使用单个列,则其值必须唯一,若主键使用多个列,则这些列的组合必须唯一
  • auto_increment告诉mysql,本列每当增加一行时自动增量,每个表只允许一个auto_increment列,而且其必须被索引(如:使其成为主键)。last_insert_id()可以用于获取最后一个auto_increment值
  • 如果在插入行时没给出值,mysql允许指定此时使用的默认值,使用关键字default来指定。mysql不允许使用函数作为默认值,只支持常量
  • 引擎类型:
    innodb是一个可靠的事务处理引擎,不支持全文本搜索
    memory在功能上等同与myisam,但由于数据存储在内存而非磁盘所以速度很快,适用于临时表
    myisam是一个性能极高的引擎,支持全文本搜索,不支持事务处理。一般使用此引擎
  • 引擎可以混用。但是混用引擎有一个缺点:外键不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键
  • 更新表:alter table
![](https://upload-images.jianshu.io/upload_images/11143861-85e732cd2f675fb8.png)
  • 删除表:drop table
  • 重命名表 rename table name0 to name1
使用视图
  • 视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询
  • 视图的应用场景:
    A:重用SQL语句
    B:简化复杂的sql语句
    C:使用表的组成部分而不是表
    D:保护数据,可以给用户授予表的特定部分的访问
    E:更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
  • 因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索。如果使用联结和过滤创建了复杂的视图或者嵌套了视图,可以导致性能很差
  • 视图可以包含order by语句,但是如果从该视图检索数据的select语句也包含order语句,那么视图中的order语句将被覆盖。视图不能索引,也不能有关联的触发器或或默认值
  • 使用create view来创建视图,使用show create view viewname来查看创建视图的语句。使用drop删除视图,语法为:drop view viewname。更新视图的时候可以直接drop后再create或者直接使用create or replace view。show tables 可以看到创建的视图和表
  • 一般视图是可以更新的。更新一个视图将更新其基表。如果对视图增加或者删除行,实际上就是对基表进行增减行,当视图包含分组、联结、子查询、并、聚集函数、distinct、导出列时,将不可更新

图片 27

图片 28

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:mysql学习笔记之四,基础Sql语句之二

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。