精华内容
下载资源
问答
  • sql建立视图
    2021-11-08 13:56:33

    SQL练习

    视图的作用

    1. 将select查询的数据保存到临时表
    2. 视图的数据只能查,不能删改
    -- 创建视图
    create view view_name as select * from table_name;
    
    -- 删除视图
    drop view view_name;
    
    更多相关内容
  • SQL 创建视图

    千次阅读 2022-06-15 14:55:10
    视图的作用:通过自定义查询 SQL 生成的虚拟表、逻辑表,作为 select 语句保存在数据字典中,本身并不包含数据。 视图的语句:create or replace view 视图名 as select 列名 from 表名1,表名2... where 条件; ...

    在开发过程中遇到了多表关联查询时,由于多表之间有几个相同的字段,在select * 的时候,有些重复字段值会累加,这样会导致查询的结果,在处理数据时会出现问题。所以就采用了视图。

    有两种类型的数据库视图:动态视图和静态视图。动态视图可以包含一个或两个表中的数据,并自动包含一个或多个指定表中的所有列。创建或更改相关对象或扩展对象时,动态视图会自动更新。静态视图可以包含来自多个表的数据,并且必须在静态视图的 SELECT 和 WHERE 子句中指定这些表中所需的列。创建或更改相关对象或扩展对象时,必须手动更新静态视图。

    视图的作用:通过自定义查询 SQL 生成的虚拟表、逻辑表,作为 select 语句保存在数据字典中,本身并不包含数据。

    视图的语句:create or replace view 视图名 as select 列名 from 表名1,表名2... where 条件;

    CREATE or replace VIEW "ZXQYNWPT3".ZJTXXJR_ALL_INFO as
    select a."ID",a."PERSONUUID",a."YEAR","SBLX","SQSJ","QYMC","SZSF","SZSFMC","SZS","SZSMC","SZQ","SZQMC","TXDZ","YB","FDDBR","FDDBRDH","FDDBRSJ","LXR","LXRDH","LXRSJ","SJKZR","KGGD","SJKZRGJ","CZ","EMAIL",to_char(zcsj,'yyyy-mm-dd')"ZCSJ","ZCZB","TYSHXYDM","QYGM","SSHY","JTXFLYMC","QYLX","SFKG","KGQY","SFSB","SBQY","SSQK","GPDM","SSJH","SSJHDETAIL","SSJHDETAILQT",a."SFTB","REGISTEREDNO","COMPANYNAME","ACCTTYPE","ADDRESS","CSZT","JHZT","SHSJ","SFZC","THYJ"
    ,"QZGS2019","QZGS2020","QZGS2021","YFRS2019","YFRS2020","YFRS2021","YYSR2019","YYSR2020","YYSR2021","ZYYWSR2019","ZYYWSR2020","ZYYWSR2021","ZYYWSRZZL2019","ZYYWSRZZL2020","ZYYWSRZZL2021","JLRZE2019","JLRZE2020","JLRZE2021","JLRZZL2019","JLRZZL2020","JLRZZL2021","ZCZE2019","ZCZE2020","ZCZE2021","ZCFZL2019","ZCFZL2020","ZCFZL2021","SJSJ2019","SJSJ2020","SJSJ2021","GQRZ2019","GQRZ2020","GQRZ2021","DYGZ2019","DYGZ2020","DYGZ2021","YHDK2019","YHDK2020","YHDK2021","JNZQ2019","JNZQ2020","JNZQ2021","JWZQ2019","JWZQ2020","JWZQ2021","SFYHDK","XDMZL","DKYT","ZJXQ","RZFF","SCSJ","ZYYWSRZYYSRBZ","PJZZL","QYGLTXRZQK","QYGLTXRZQKQT","HXYWCYXXXTZCQK","HXYWCYXXXTZCQKQT","CPRZQK","CPRZQKQT","SFYDQY","SFSFXM","QYZCFZL","ZDCPGJSCZYL2020","ZDCPGJSCZYL2021","ZDCPGNSCZYL2020","ZDCPGNSCZYL2021","ZDCPCKE2020","ZDCPCKE2021","PPGS2020","PPGS2021","PPSX2020","PPSX2021","JSYJYGJJ","JSYJYSJ","QYJSZXGJJ","QYJSZXSJ","QYGCZXGJJ","QYGCZXSJ","GYSJZXGJJ","GYSJZXSJ","YSZJGZZ","BSHGZZ","HZYXJGMC1","HZYXJGMC2","HZYXJGMC3","YJLYYHDCGJYYQK","YFJFZE2019","YFJFZE2020","YFJFZE2021","YFJFZYYSRBZ2019","YFJFZYYSRBZ2020","YFJFZYYSRBZ2021","YFRYZQYQBZGBZ2019","YFRYZQYQBZGBZ2020","YFRYZQYQBZGBZ2021","YYZSCQQKZS","YYZSCQQKFMZL","YYZSCQQKZWXPZ","YYZSCQQKGJNZWPZ","YYZSCQQKGJXY","YYZSCQQKDLT","SFHDGJKJJ","SFHDGJKJJYEAR","SFHDGJKJJMC","SFHDGJKJJPM","CHINA50TOP","CHINA50TOPYEAR","CHINA50TOPPM","SJLY1","SJLYDETAIL6","SJLYDETAIL7","SJLYDETAIL8","SJLYDETAIL9","SJLYDETAIL10","SJLYDETAIL11","SJLYDETAIL12","SJLYDETAIL13","SJLYDETAIL14","SJLYDETAIL15","SJLYDETAIL16","SJLYDETAIL17","SJLYDETAIL18","SJLYDETAIL19","SJLYDETAIL20","SFGJLYBDB","BDBCPMC","TKBLY","TDGWQYMC","GJLYBDBDETAIL","SFZMDQYZJPT","ZMDQYZJPT1","ZMDQYZJPT2","ZMDQYZJPT3","ZDCPMC","CPLYSJ","ZDCPLB","HYLJQY1","HYLJQY2","HYLJQY3","SYSJLY","SJLY","FBBZZS","FBBZGJZS","FBBZGJIAZS","FBBZHYZS","FBBZMC","XGBMRDCH","XGBMRDCHQT","JWBGQK","JWBGQKZJE","JWBGQKQT","JWFGSQK","JWFGSQKZJE","JWFGSQKQT","JWYFJGQK","JWYFJGQKZJE","JWYFJGQKQT","JWZFZLQK","JWZFZLQKZJE","JWZFZLQKQT","GJZDKJXM","GJZDKJXMMC","GJJJSCXLXM","GJJJSCXLXMMC","QYXXQKJS","FDDBRQM","QZGS2019FILEID","QZGS2019FILENAME","QZGS2020FILEID","QZGS2020FILENAME","QZGS2021FILEID","QZGS2021FILENAME","QYGLTXRZQKFILEID","QYGLTXRZQKFILENAME","HXYWCYXXXTZCQKFILEID","HXYWCYXXXTZCQKFILENAME","ZDCPGNSCZYL2020FILEID","ZDCPGNSCZYL2020FILENAME","ZDCPGNSCZYL2021FILEID","ZDCPGNSCZYL2021FILENAME","YFJFZE2019FILEID","YFJFZE2019FILENAME","YFJFZE2020FILEID","YFJFZE2020FILENAME","YFJFZE2021FILEID","YFJFZE2021FILENAME","YYZSCQQKFILEID","YYZSCQQKFILENAME","SFHDGJKJJFILEID","SFHDGJKJJFILENAME","CHINA50TOPFILEID","CHINA50TOPFILENAME"
    ,"GQRZ2021FILEID","GQRZ2021FILENAME","PPGS2020FILEID","PPGS2020FILENAME","PPGS2021FILEID","PPGS2021FILENAME","YFJGJSQKFILEID","YFJGJSQKFILENAME","QYXXQKJSFILEID","QYXXQKJSFILENAME"
    ,"SFFH","SFTY","TJYJ","SFTG","ZSYJ","ZSRBH","ZSRXM",to_char(zssj,'yyyy-mm-dd hh24:mi:ss')"ZSSJ","ZSRJG","ZSRJGMC","ZSRBM","ZSRBMMC"
    
    from ZJTXXJR_JBXX a,ZJTXXJR_NRB b, ZJTXXJR_SHXX c
    where a.id=b.id and a.id=c.id;

    展开全文
  • SQL创建视图实例

    2021-03-07 03:50:15
    本文章很简单的讲述了关于SQL创建视图 方法,利用了一个简单的实例告诉你如何创建视图,有需要的朋友可以参考着来做。--使用代码创建视图create view viewname --创建视图assql statement例(1)Name Subject Result...

    本文章很简单的讲述了关于SQL创建视图 方法,利用了一个简单的实例告诉你如何创建视图,有需要的朋友可以参考着来做。

    --使用代码创建视图

    create view viewname --创建视图

    as

    sql statement

    例(1)

    Name Subject Result

    张三 语文 80

    张三 数学 90

    张三 物理 85

    李四 语文 85

    李四 数学 92

    李四 物理 82

    想变成

    姓名 语文 数学 物理

    张三 80 90 85

    李四 85 92 82

    代码如下

    复制代码

    declare @sql varchar(4000)

    set @sql = 'create view [viewname] as select Name'

    select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'

    from (select distinct Subject from CJ) as a

    select @sql = @sql+' from CJ group by name'

    select @sql

    exec(@sql)

    关于视图的其它操作

    alter view viewname --修改视图

    as

    sql select statement

    drop view viewname --删除视图

    本文原创发布php中文网,转载请注明出处,感谢您的尊重!

    展开全文
  • 假设已经通过create从表s中,创建视图,只显示学号、姓名、性别和院系共4列。 create view view_s as ( select s.sno as 学号, s.sn as 姓名, s.sex as 性别, s.dept as 院系 from s ); 现在要在这个视图基础上...
  • 资源名称:SQL Server 视图及索引的创建及使用内容简介: 本文档主要讲述的是SQL Server 视图及索引的创建及使用;目的是通过企业管理器和Transact_SQL语句对视图进行创建、修改和删除通过企业管理器。希望本文档会给...
  • 文章目录1 问题2 解决 1 问题 创建视图sql, 希望有一个字段使用默认值(拼接的结果集,本身没有这个字段) 2 解决 select 默认值 as 字段名 from 表名

    文章目录

    1 问题

    • 创建视图的sql, 希望有一个字段使用默认值(拼接的结果集,本身没有这个字段)

    2 解决

    • select 默认值 as 字段名 from 表名
      在这里插入图片描述
    展开全文
  • sql临时创建视图.sql

    2021-08-17 17:44:34
    sql临时创建视图.sql
  • SQL篇-创建视图

    千次阅读 2021-05-16 17:19:57
    可以使用 CREATE VIEW 语句来创建视图,语法格式如下: CREATE VIEW <视图名> AS <SELECT语句> 语法说明如下: <视图名>:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或...
  • oracle pl/sql创建视图

    2021-05-04 06:53:14
    create or replace view vw_rightpeople(rightpeopletypename, rightpeopletype, rightpeopleid, organizetypeno, parentorganizeno, idno, idtype, rightpeoplename, rightpeopleproperty, chairman, resopertype, ...
  • SQL创建视图注意事项

    2020-12-20 17:20:37
    1.如何创建视图及注意事项 创建视图图的基本语法: CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句> from 表名 group by 列名;-- 该语句可以选择或者不写该语句,两者的...
  • 细说SQL Server中的视图

    2021-01-20 08:17:20
    1,什么是视图? 2,为什么要用视图; 3,视图中的ORDER BY; 4,刷新视图; ...当你查询视图时,无论是获取数据还是更新数据,Sql server都用视图的定义来访问基础表;  视图在我们日常操作也扮演
  • sql建立视图的过程

    2013-04-18 16:41:53
    sql建立视图的过程
  • SQL SERVER中先判断视图是否存在,使用IF NOT EXISTS,然后再创建视图,使用create view,整个过程如下
  • 文章目录1 视图概述2 创建视图3 修改视图4 删除视图 1 视图概述 视图就是将一个或多个表中的目标字段抽取出来形成的一个虚拟表。这个虚拟表和真实的表具有相同的功能。 2 创建视图 语法格式: create view view_name...
  • Sql server中 如何用sql语句创建视图

    千次阅读 2021-04-06 17:58:23
    有任何意义,我们可以根据这一实际情况,专门为采购人员创建一个视 图,以后他在查询数据时,只需select * from view_caigou 就 可以啦。 第二点:使用视图,可以简化数据操作。 解释:我们在使用查询时,在很...
  • 创建视图SQL:在SQL Server中创建视图

    万次阅读 2020-07-17 13:42:32
    介绍 (Introduction) ... In this article, we are going to see how to use the CREATE VIEW SQL statement to create a view. This is the first article in a series that will be a programmatical approa...
  • SQL创建视图时设置默认值

    千次阅读 2017-03-21 23:30:13
    ISNULL(字段,0)
  • 如何在SQL Server中创建视图

    千次阅读 2020-07-25 22:30:11
    In this article, we will learn the basics of the view concept in SQL Server and then explore methods to create a view in SQL using T-SQL and SQL Server Management Studio. 在本文中,我们将学习SQL ...
  • 一个视图是从一个特定的角度来查看数据库中的数据 。从数据库系统内部来看,一个视图是由SELECT语句组成的查询定义的虚拟表 。从数据库系统内部来看,视图是由一张或多张表中的数据组成的,从数据库系统外部来看,...
  • SQL视图

    2020-12-14 23:06:01
    视图  视图是虚拟的表。与包含的数据表不一样,视图只包含使用时动态检索的数据查询。...  创建视图之后,可以用于表基本相同的方式使用它们。可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他
  • SQL Sever 创建视图

    2022-08-30 13:03:58
    2.选择需要建立连接的表,点击添加----直接改用创建出的视图作为代替提高效率。然后就会自动建立连接 ,如下图所示。3.选择出需要显示出的列,即可。4.查看下方select 语句查看新建出的视图表.5..右键保存,更改视图名称...
  • 更新记录 ...2022年7月31日 发布。 2022年7月2日 从笔记迁移到博客。 视图说明(VIEW) 视图本质:已编译好的SQL语句 视图外在表现:一个虚拟的表或对象 ...注意:只有当视图建立索引之后,才会被具体化(存...
  • 因为视图有非常多的优点:1,可以简化操作,2,可以建立前台和后台的缓冲,3,可以合并分割数据,4,最重要的是可以提高安全性,因此在SQL视图也占据着非常重要的地位。在这章的总结中,深刻体会到了,两种方法:1...
  • 主要介绍了SQL 如何如何创建、更新和删除视图,文中讲解非常详细,示例代码帮助大家更好的理解和学习,感兴趣的朋友可以了解下
  • 创建一个 名字为 vw_test 的视图视图中的数据从一个基础表中查询并在创建时候添加额外的字段fieldname 1、fieldname是一个比例,保留一位小数( 用ROUND 函数 ) 2、分子和分母均取自 table_name 3、分子和分母...
  • 视图可以看作定义在SQL Server上的虚拟表。视图正如其名字的含义一样,是另一种查看数据的入口。 常规视图本身并不存储实际的数据,而仅仅是由SELECT语句组成的查询定义的虚拟表。 从数据库系统内部来看,视图是由...
  • SQL Server:使用T-SQL创建视图

    千次阅读 2016-11-29 17:41:52
    记录一下,T-SQL视图 作用:取到几个国家、区域、代理下的各个机型价格(LZ目前公司做IP话机) if exists(select 1 from sysobjects where name='V_YEA_SalesPrice') drop view V_YEA_SalesPrice go create view...
  • mysql创建视图sql 什么是SQL视图? (What is a View in SQL?) A View is a database object that presents data existing in one or more tables. Views are used in a similar way to tables, but they don’t ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 298,790
精华内容 119,516
关键字:

sql建立视图