Oracle SQL语句之UPDATE

最新推荐文章于 2026-04-18 03:24:20 发布

转载最新推荐文章于 2026-04-18 03:24:20 发布 · 6.5w 阅读

Oracle 专栏收录该内容

12 篇文章

订阅专栏

本文提供了多个 Oracle SQL 语句实例，包括查询所有列、限制返回行数、计数表中行数、连接表、更新单行或多行数据、插入及删除数据等操作。这些示例有助于理解如何使用 SQL 进行基本的数据管理和分析。

ELECT - All columns

Return all columns.

SELECT 
   *  
FROM COURSES

SELECT - Return first 10 rows

Run a SQL Select statement and limit the number of rows returned. Useful when you are not sure of how to limit the recordset to be returned and you only want to return a few rows to do some initial analysis.

SELECT 
  COURSE_DESIGNATER , 
  COURSE_NAME       , 
  EMPLOYEE_INACTIVATING 
FROM COURSES
WHERE RowNum < 11

SELECT - Count rows in table

Returns a count of the total number of rows in a table.

SELECT COUNT(*) from COURSES

SELECT - Joining tables

Join two Oracle tables together.

SELECT  
       b.COURSE_NAME        ,
       a.CLASS_END_DATE     , 
       a.CLASS_START_DATE                         
 
FROM         CLASSES a 
        JOIN COURSES b 
             ON  b.COURSE_DESIGNATER = a.COURSE_DESIGNATER_FK
WHERE  RowNum < 100 
/

UPDATE - Single row

Update a single row of data.

UPDATE STUDENTS
SET    
       EMPLOYEE_ID_FK       = '3789'  ,
       GENDER_CODE          = 'F'  ,
       MARITAL_STATUS_CODE  = 'M'  
WHERE  STUDENT_ID = 11  
/

COMMIT WORK ;
/

UPDATE - Update multiple rows
Update multiple rows of data..
UPDATE CLASSCONVENINGS
SET    ACITVE_FLG  = 2
WHERE 
    CLASS_CONVENE_DATE = TO_DATE('2005-12-31 00:00:00', 
    'YYYY-MM-DD HH24:MI:SS') 
/

COMMIT WORK ;
/
Oracle SQL INSERT Example
INSERT - Row
Insert a row of data. Note: the minimum requirements for and insert must include columns that are part of the primary key, part of indexes where no duplicates are allowed, and all columns that cannot be null.
INSERT INTO COURSES
     (
        COURSE_DESIGNATER           ,
        COURSE_NAME                 ,
        EDUCATION_DELIVERY_METHOD   ,
        COURSE_DESCRIPTION_SHORT    ,
        COURSE_DESCRIPTION_LONG     ,
        COURSE_SYLLABUS_ATTACHMENT  ,
        COURSE_MATERIALS_ZIP        ,
        COURSE_MATERIALS_LOCATION   ,
        MAX_RECOMMENDED_ENROLLMENT  ,
        CONVENINGS                  ,
        CONVENINGS_DETAILS          ,
        CONVENINGS_CONSECUTIVE_FLG  ,
        TYPICAL_PRECLASS_PREP_TIME  ,
        TYPICAL_POSTCLASS_PREP_TIME ,
        TYPICAL_CONVENING_TIME      ,
        SPONSER_NAME                ,
        ACTIVE_FLG                  ,
        INACTIVE_DATE               ,
        INACTIVE_REASON             ,
        EMPLOYEE_INACTIVATING       
     ) 
VALUES
     (
        'SQL200' , 
        'Intermediate SQL' , 
        'Classroom' , 
        NULL , 
        NULL , 
        'doc' , 
        'zip' , 
        'DataExpressions\csematerials' , 
        16 , 
        0 , 
        NULL , 
        0 , 
        NULL , 
        NULL , 
        NULL , 
        NULL , 
        1 , 
        NULL , 
        NULL , 
        0 
     ) 
/

COMMIT WORK ;
Oracle SQL DELETE Examples
DELETE - Row
Delete a row of data. 
DELETE FROM COURSES
WHERE  COURSE_DESIGNATER = 'Java110' 
/

COMMIT WORK ;

DELETE - Multiple rows of data
Delete multiple rows of data. Be careful!
DELETE FROM CLASSCONVENINGS
WHERE      CLASSES_NUM_FK      > 4  
  AND CLASS_CONVENE_DATE = TO_DATE('2006-02-15 00:00:00', 
                                   'YYYY-MM-DD HH24:MI:SS') 
  AND CLASS_LOCATION_FK   = 'Seattle-Training Room 1

http://www.sqlinfo.net/oracle/oracle_INSERT.php