精华内容
下载资源
问答
  • 本篇文章是对ORA-00947:Not enough values (没有足够的值)的解决方法进行了详细的分析介绍,需要的朋友参考下
  • oracle 报错:没有足够的值

    千次阅读 2020-03-22 00:55:53
    想插入一行元组,测试语句的完整性,但是发现报错:没有足够的值。 发现是因为我没有在表明Student后写参数的顺序,则默认用表中属性顺序,但是我少写了一列Sdept,所以出现了错误。 按照默认表中的顺序输入参数,...

    错误语句:

    insert into student values(809,'蔡树敏','男',34,1973);
    

    (下图中运行结果的显示是:select * from student; 的运行结果。 是为了显示student的属性,方便接下来解释问题。)
    第一次写数据库作业遇到了这个问题
    第一次写数据库作业,数据都是老师给的)

    想插入一行元组,测试语句的完整性,但是发现报错:没有足够的值。

    发现是因为我没有在表名Student后写参数的顺序,此时默认使用表中属性顺序。上面的语句中我少写了一列Sdept,所以出现了错误

    解决方案一:按照默认表中的顺序输入参数。结果如下图。
    在这里插入图片描述

    解决方案二:在表明Student后面注明参数输入顺序。结果如下图。

    在这里插入图片描述

    展开全文
  • ORA-00947: 没有足够的值 是因为源数据表的字段比目标表字段少,不能直接全部插入,将要插入的字段依次写出插入,就不会报错了。 示例如下: aaa表有id,name,age,adress等字段,源表bbb有id,name,age字段,此时直接...

    ORA-00947: 没有足够的值
    是因为源数据表的字段比目标表字段少,不能直接全部插入,将要插入的字段依次写出插入,就不会报错了。
    示例如下:
    aaa表有id,name,age,adress等字段,源表bbb有id,name,age字段,此时直接进行下面的操作,会报没有足够的值错误

    insert into aaa select * from bbb; 
    

    修改为这种即可

    insert into aaa(id,name,age) select id,name,age from bbb;
    
    展开全文
  • sql没有足够的值 数据科学 (DATA SCIENCE) SQL is a powerful language. SQL is a part of most of the tech stacks you’ll work with. For a developer, the use of SQL might be limited to inserting and ...

    sql没有足够的值

    数据科学 (DATA SCIENCE)

    SQL is a powerful language. SQL is a part of most of the tech stacks you’ll work with. For a developer, the use of SQL might be limited to inserting and retrieving data in the database, but for data analysts, data scientists and data engineers, it is usually much more than that. SQL gives you direct access to the database — there’s a whole lot of analytics that can be done right there — without getting data out of the database and loading it into pandas or PySpark. Obviously, what you can do within the database is limited because of the resources.

    SQL是一种强大的语言。 SQL是您将使用的大多数技术堆栈的一部分。 对于开发人员而言,SQL的使用可能仅限于在数据库中插入和检索数据,但是对于数据分析人员,数据科学家和数据工程师而言,SQL的用途远不止于此。 SQL使您可以直接访问数据库-可以在此处直接进行很多分析-无需将数据从数据库中取出并加载到pandas或PySpark中。 显然,由于资源的原因,您在数据库中可以执行的操作受到限制。

    From what I have observed over the years, people who work with a statistical programming language like R, Julia or Python tend to do almost everything in that language whereas some of the stuff can be actually done more efficiently with SQL at times. Apart from the basic selects, inserts, updates, joins and subqueries, there are a lot of advanced features of SQL which can be used for data analysis that we don’t exploit often enough.

    根据我多年来的观察,使用诸如R,Julia或Python之类的统计编程语言的人往往会使用该语言来执行几乎所有操作,而有些事情实际上有时可以用SQL更有效地完成。 除了基本的选择,插入,更新,联接和子查询外,还有很多SQL的高级功能可用于数据分析,但我们很少利用这些功能。

    There’s a post on KDNuggets which says that it’s the last guide that you’d need for data analysis. Although it’s a well written guide but I think that it definitely is NOT the last guide you’d need for data analysis. You’ll need to know more. I’d say that the Medium post you’re reading right now is also not the last guide you’ll need to be great at SQL. This just talks about a few neglected, underused but powerful features of SQL. Let’s go ahead and go over some of them.

    KDNuggets上有一篇文章,说这是您进行数据分析所需的最新指南 。 尽管这是一本写得很好的指南,但我认为它绝对不是您进行数据分析所需的最后指南。 您需要了解更多。 我想说的是,您现在正在阅读的中级帖子也不是您精通SQL的最新指南。 此处仅讨论了一些被忽略,未充分利用但功能强大SQL功能。 让我们继续研究其中的一些。

    分层查询 (Hierarchical Queries)

    Enterprise relational databases like Oracle had started supporting storage and retrieval of hierarchical data long time ago. Before MySQL 8 was released, MySQL was probably one of the few databases which didn’t support a straightforward way of querying hierarchical data. I have had to refer this article by Mike Hillyer many times over the last couple of years while implementing hierarchical storage in MySQL. It’s a great read.

    像Oracle这样的企业关系数据库很久以前就开始支持分层数据的存储和检索。 在发布MySQL 8之前,MySQL可能是少数几个不支持直接查询分层数据的数据库之一。 在MySQL中实施分层存储时,最近几年我不得不多次引用Mike Hillyer的文章 。 这是一本好书。

    Hierarchical data is everywhere if you think about it — categories and sub-categories and further subcategories of products, organizational hierarchy, animal and plant species, family trees and so on. Normal SQL features aren’t enough to query hierarchical data efficiently as it would result in a lot of subqueries (and confusion). In MySQL 5.7 or earlier, you’d use something called session variables to do hierarchical queries and in MySQL 8 or later and in other databases, you’d use recursive common table expressions.

    如果您考虑一下,分层数据无处不在-产品的类别和子类别以及其他子类别,组织层次结构,动植物种类,家谱等。 普通SQL功能不足以有效地查询分层数据,因为它会导致大量子查询(和混乱)。 在MySQL 5.7或更早的版本中,您将使用称为会话变量的内容进行分层查询,而在MySQL 8或更高版本中以及其他数据库中,则将使用递归公用表表达式。

    Hierarchical data is everywhere — product categories & subcategories, organizational hierarchies, family trees etc.

    层次结构数据无处不在-产品类别和子类别,组织层次结构,族谱等。

    I’ll give you some context on this. An ex-colleague called me up one day and asked me about how to run a hierarchical query on MySQL 5.7 — until this version MySQL did not support common table expressions. So, here’s what the query would look like. Let’s now talk about CTEs 😄

    我会给你一些背景。 一位前同事一天打电话给我,问我如何在MySQL 5.7上运行分层查询-直到此版本MySQL不支持公用表表达式。 因此,这就是查询的样子。 现在让我们谈谈CTE s

    create database random;
    
    
    drop table if exists random.organization_dimension;
    create table random.organization_dimension as
    select 1 organization_key,
           1 organization_parent_key;
           
    alter table random.organization_dimension change organization_parent_key organization_parent_key int(1) null;
    
    
    truncate table random.organization_dimension;
    select * from random.organization_dimension;
    
    
    insert into random.organization_dimension
    select 1, null union all
    select 2, 1 union all
    select 7, 1 union all
    select 3, 2 union all
    select 4, 2 union all
    select 8, 7 union all
    select 9, 7 union all
    select 5, 4 union all
    select 6, 4 union all
    select 10, 9 union all
    select 13, 9 union all
    select 11, 10 union all
    select 12, 10;
    
    
    select * from random.organization_dimension;
    
    
    alter table random.organization_dimension 
     add column organization_name varchar(20);
    
    
    update random.organization_dimension set organization_name = concat('Firm',organization_key);
    
    
    -- For given Firm ID = 7 as in the example.
    
    
    select t.organization_key, 
           t.organization_parent_key, 
           t.organization_name
      from random.organization_dimension t
     where not exists (select 1 from random.organization_dimension t0 
                       where t0.organization_parent_key = t.organization_key)
       and t.organization_key >= 7
      
    union
       
    select t1.organization_key,
           t1.organization_parent_key,
           t1.organization_name
      from random.organization_dimension t1
    where t1.organization_key in 
          (select t.organization_parent_key     
             from random.organization_dimension t
            where not exists (select 1 from random.organization_dimension t0 
                               where t0.organization_parent_key = t.organization_key)
              and t.organization_key >= 7)
    
    
    order by 2;  
    
    
    
    
    select r2.*
    from (select @_id as _id,
                 (select @_id := organization_parent_key 
                  from random.organization_dimension 
                  where organization_key = _id) as parent_id,
                 @level := @level + 1 as _level
        	  from (select @_id := 12, @level := 0) vars, random.organization_dimension h
           where @_id <> 0) r1
    join random.organization_dimension r2 on r1._id = r2.organization_key
    order by r1._level desc;

    递归公用表表达式 (Recursive Common Table Expressions)

    Fondly known as a CTE, this is a very powerful construct that is supported in all major relational databases (including most relational data warehouses). It essentially reduced the need for writing one query within another within another within another, i.e., nested subqueries. A CTE is essentially a view defined within your query with a scope of that query. The CTE doesn’t exist as a database object, it exists as part of the query. CTEs are also extremely good for readability (if done well).

    这是众所周知的CTE,是一种非常强大的构造,所有主要的关系数据库(包括大多数关系数据仓库)都支持该构造。 从本质上讲,它减少了在另一个查询(另一个嵌套子查询)中的另一个查询中编写一个查询的需求。 CTE本质上是在查询中定义的具有该查询范围的视图。 CTE不作为数据库对象存在,而是作为查询的一部分存在。 CTE的可读性也非常好(如果做得好)。

    A CTE is essentially a view defined within your query with a scope of that query.

    CTE本质上是在查询中定义的具有该查询范围的视图。

    I wrote a piece about generating random data using SQL a while back and I have used CTEs in some of the queries there. You can go have a quick look here. Also a recursive CTE is just like a recursive function or method that calls itself. Imagine a tree data structure where you start parsing and keep parsing till you get to a leaf node. That’s recursivity or recursiveness of the data queried by using recursive CTEs.

    不久前,我写了一篇有关使用SQL生成随机数据的文章,在那里我在某些查询中使用了CTE。 您可以在这里快速浏览。 另外,递归CTE就像调用自身的递归函数或方法一样。 想象一下一个树数据结构,在该结构中您开始进行解析并一直进行解析,直到到达叶节点为止。 这就是使用递归CTE查询的数据的递归性或递归性。

    with recursive series as (
    	select 1 as id union all
    	select id + 1 as id
       from series
       where id < 100), 
       		cities as (select 'Santa Clara' city union all 
       		           select 'Los Angeles' union all 
       		           select 'Santa Clarita' union all 
       		           select 'San Bernardino' union all
       		           select 'Alameda' union all
       		           select 'San Mateo' union all 
       		           select 'Santa Barbara'   		           
       		          ),
       		first_names as (select 'John' first_name union all
       				select 'James' first_name union all
       				select 'David' first_name union all
       				select 'Jeremy' first_name union all
       				select 'Ron' first_name union all
       				select 'Katie' first_name union all
       				select 'Nikita' first_name union all
       				select 'Rachel' first_name union all
       			        select 'Tom' first_name	
       		),
       	 	last_names as (select 'Smith' last_name union all
    			       select 'Johnson' last_name union all
       			       select 'Williams' last_name union all
       			       select 'Brown' last_name union all
       			       select 'Jones' last_name union all
       		               select 'Miller' last_name union all
       			       select 'Davis' last_name union all
       			       select 'Wilson' last_name union all
       			      select 'West' last_name	
       		)
    select id, user_id, first_name, last_name
           dob, city, salary - mod(salary, 100) salary
    from (select id, 
           substring(md5(rand()),1,20) user_id,
           (select first_name from first_names order by rand() limit 1) first_name,
           (select last_name from last_names order by rand() limit 1) last_name,
           date(concat_ws('-',(floor(1919+rand()*100)),
                              (floor(1+rand()*12)),
                              (floor(1+rand()*28))
                         )
               ) dob,
           (select city from cities order by rand() limit 1) city,
           floor((rand() * (120000 + 1)) + 35000) salary
    from series) as t;

    视窗功能 (Window Functions)

    The most commonly used analytic functions in SQL are aggregate functions. While aggregate functions in SQL work on the whole data set or parts of the data set in conjunction with the GROUP BY clause, window functions extend the functionality of aggregate functions to do more than just calculate basic aggregates. Window functions provide the ability to second order aggregate & summary operations on the dataset, and on parts of the dataset, optionally with moving windows and other interesting features. This gives us the chance to calculate things like

    SQL中最常用的分析函数是聚合函数。 虽然SQL中的聚合函数与GROUP BY子句一起在整个数据集或部分数据集上工作,但窗口函数扩展了聚合函数的功能,而不仅仅是计算基本聚合。 窗口函数提供了对数据集以及部分数据集进行二阶聚合和汇总操作的功能,还可以选择移动窗口和其他有趣的功能。 这使我们有机会计算类似

    • moving average of sales over the last seven working days

      最近七个工作日的销售移动平均值
    • running total of revenue month on month

      每月总收入运行情况

    This gives us great power to analyse data within SQL without taking it outside to be processed in pandas or PySpark.

    这使我们拥有强大的能力来分析SQL中的数据,而无需将其带到大熊猫或PySpark中进行处理。

    select p.name,
           row_number() over () rn,
           rank() over () rnk,
           dense_rank() over () dns_rnk,
           lead(p.name) over () lead_name,
           lag(p.name) over () lag_name,
           first_value(p.name) over () fv_name,
           last_value(p.name) over () lv_name,
           ntile(4) over () quartile,
           ntile(5) over () quintile
      from devstronomy.planet as p;

    The table definition can be found in the dataset — NASA Jet Propulsion Laboratory compiled into a GitHub project by devstronomy.

    可以在数据集中找到该表定义 -NASA 喷气推进实验室 devstronomy 编译为 GitHub项目

    Here’s a great in-depth piece about Window Functions in SQL. Do give it a read if you’re interested.

    这是有关SQL中的Window函数的一篇很棒的深入文章。 如果您有兴趣,请阅读。

    程序语言 (Procedural Language)

    From an earlier article I wrote about the many flavours of SQL

    在较早的文章中,我写了关于SQL的多种风格的文章-

    One of the measures of how powerful your flavour of SQL is to go through the full feature set that the database has to offer — and find out whether your flavour of SQL is Turing Complete. But even a Turing complete SQL cannot guarantee handling specific use cases that the query DSL allows but the architecture doesn’t. For this reason, so many flavours of databases/SQL exist.

    衡量SQL风格有多强大的一种方法是浏览数据库必须提供的全部功能集-并确定 SQL 风格是否 为Turing Complete 但是,即使是图灵完整SQL也不能保证处理查询DSL允许但架构不允许的特定用例。 因此,存在许多种数据库/ SQL。

    The procedural language extension to SQL provided by some of the major relational databases make them Turning complete languages. Some of the extensions are PL/SQL, T-SQL and plgsql. A procedural language lets you access a lot of constructs available in a full fledged programming language, natively within the database itself. For example, in the procedural version of SQL, you’ll be able to write loop constructs, you’ll be able to write structured programs using functions & procedures, you’ll have access to a lot of data types and data structures and you’ll be able to do object-oriented programming.

    一些主要的关系数据库提供的对SQL的过程语言扩展使它们成为Turning完整的语言。 一些扩展是PL / SQL,T-SQL和plgsql。 程序语言使您可以在数据库本身内部访问以成熟的编程语言提供的许多构造。 例如,在SQL的过程版本中,您将能够编写循环结构,能够使用函数和过程来编写结构化程序,可以访问许多数据类型和数据结构,并且就能进行面向对象的编程。

    You can imagine a lot can be done using a full programming language’s features right within SQL with direct access to data without pushing it to an external system. Doing that not only removes the additional layer, it also saves a lot of network time spent on moving data around.

    您可以想象在SQL中使用完整的编程语言功能直接访问数据而无需将其推送到外部系统可以完成很多工作。 这样做不仅消除了额外的层,还节省了在移动数据上花费的大量网络时间。

    结论 (Conclusion)

    There are many more superpowerful features of SQL that we could talk about but in my experience, these were some of them which I felt were underused by a lot of people (and teams) I worked with and observed — and these included database engineers too!

    我们可以讨论SQL的许多其他超强大功能,但是根据我的经验,我觉得其中的一些功能被我与之合作并观察到的很多人(和团队)没有充分利用-其中也包括数据库工程师!

    翻译自: https://towardsdatascience.com/4-advanced-sql-features-you-havent-used-enough-919f154ff530

    sql没有足够的值

    展开全文
  • 问题描述: ORA-00947: 没有足够的值 问题分析: 1、数据库有n个"非空"字段,但是insert语句里面的"非空"字段总数少于n个。

    问题描述:

    ORA-00947: 没有足够的值

    问题分析:

    1、数据库有n个"非空"字段,但是insert语句里面的"非空"字段总数少于n个。

    展开全文
  • SQL 报错ORA-00947:Not enough values (没有足够的值没有足够的值,,经过查询是数据库 表中新增了一个字段 , 但是我插入采用的是下面这种方式: insert into tableName select a, b,c ,... from otherTable 并...
  • ora-00947 没有足够的值

    万次阅读 2019-06-01 21:37:31
    创建数据库的表的时候,not null 不为空的值有n个 数据插入的操作时,插入的值少于n个
  • Caused by: java.sql.SQLSyntaxErrorException: ORA-00947: 没有足够的值 报错原因 这种错误大多数都是因为你的sql参数与值不对应导致出错 我的原因 很容易看出来上面和下面值的数量是不对应的 解决方法 主要还是要...
  • Cause: java.sql.SQLSyntaxErrorException: ORA-00947: 没有足够的值 ### The error may involve com.esoon.ids.dao.mapper.ModelConvertRuleMapper.saveConvertRules-Inline ### The error occurred while ...
  • 本人sql新手,我在plsql建立个临时表并且把数据复制进去后插入在yhzl表里就提示ora-00947这问题搞了一天了求各位大侠帮主插入代码是这个insert into yhzl(ch, qh, yhbh, cbbh, cbxh1, sbwz, yhmc, sbbh, sbazrq...
  • 新表结构已经建好了,将旧表数据insert到新表时,发生这个错误了
  • 这段代码报错是mean_loss_val中train_feed_dict未定义 然后我尝试改动这个for内代码 def get_batch(data,label,batch_size): for start_index in range(0,len(data)-batch_size+1,batch_size): ...
  • oracle 插入数据时提示没有足够的值

    千次阅读 2016-05-11 23:49:17
    首先我觉得你建表时候很不严谨,原因如下:1,你以可为空字段为主键2,stu_age字段为字符类型,但check里确和数值进行比较3,check拼错了4,between拼错了如果建表没有问题,那么插入数据就不会有问题,这个我在...
  • create or replace procedure jsl (isNowMonth in varchar2, place_no in varchar2, nowMonth in varchar2, preYear in varchar2, thisYear in varchar2, ...copyerName in varchar2, ... select zc.copyer,sum(case ...
  • 通过测试发现值的个数是对应上。[color=#FF0000]也就说不存在个数不足问题[/color]。 [/size][/b] [b][size=14px]网友有说字段类型不匹配也会有这个提示,如何进行转换或者避免呢[/size][/b]
  • 昨天工作其实不难,就是有点费时间,但就算是不难工作,在实际操作中也会遇到一些问题,一些很常见,但我们又不一定能立刻解决问题。所以我现在就是把昨天在简单插入语句中遇到问题拿来跟大家分享下,虽然...
  • ORA-00947: Not enough values 没有足够的值

    千次阅读 2014-07-21 22:53:10
    请认真查看插入语句,可能是字段数量与插入数量不一致造成.
  • 此查询返回我想要内容(来自查询逗号分隔)SELECT emp_id, LISTAGG(HOBBIES, ',') WITHIN GROUP (ORDER BY HOBBIES)FROM employee_listWHERE emp_id = 123456GROUP BY emp_id;结果:Squash,Tennis,Walking初步...
  • oracle ORA-00947: 没有足够的值

    千次阅读 2009-09-02 11:46:57
    values()里有一个里有后括号,提前加了后括号导致不足 黑色头发:http://heisetoufa.iteye.com/
  • ... <p>No, the problem isn't that the columns are null.... <p>Here is the error I get: ...<pre><code>Warning: oci_execute(): ORA-00947: not enough values in <... $csv_array[] = array_combine(range(1, count...
  • t.data_type , c.COMMENTS FROM user_tab_columns t, user_col_comments c WHERE t.table_name = c.table_name AND t.column_name = c.... AND t.table_name = 'HDSA0001' 本身是没有问题。可以查出来数据
  • 1.PLSQL不支持分号大括号,无论在哪个语句部位全角半角,“;”,“{ }”容易输错,SQL里面不要写不然...2.2 由于目标数据库表文件多出了两个字段, connection无法返回可供执行statement 原数据库表结构: 代...
  • 报错:scanf_s函数报错:没有为格式字符串传递足够的参数 这个问题很变态 解决办法:在使用%c和%s读入字符或字符串时,应在地址参数后附加一个缓冲区边界。 int main() { char s1[20] = "i m name"; char *...
  • 对于没有足够时间业余园丁帮助。 该辅助工具自动为植物浇水,并测量温度,湿度,气压和土壤湿度。 这些可以在网站上找到。 看起来像这样: 尼古拉斯(Niclas),拉斐尔(Raphael)和乔纳斯(Jonas)
  • 最近使用笔记本,刚打开电脑,使用经常会出现“没有足够的可用内存来运行此程序” ,清理系统内存后会恢复正常,或者等电脑使用一段时间后也会恢复正常。 这究竟是什么原因呢? 这是虚拟内存不足引起的。虚拟机...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,196
精华内容 478
关键字:

没有足够的值