Sql语句的编写DML语句操作 增删改查

/*============================================================

                      DML操作

  ============================================================*/

CREATE  TABLE  stuInfo  

(

 stuNo   CHAR(6)  NOT  NULL,   --学号，非空

 stuName  VARCHAR2(20)  NOT  NULL ,  --学员姓名，非空

 stuAge  NUMBER(3,0)  NOT  NULL,  --年龄，非空

 beginDate  DATE     --开始学习时间，日期类型

) ;

 

 

INSERT INTO stuInfo(stuNo,stuName,stuAge)  VALUES('034','Tom',23);

INSERT INTO stuInfo VALUES('032','Ford',22,SYSDATE);

 

INSERT INTO stuInfo  

    SELECT stuNo,stuName,stuAge,beginDate FROM stuInfoBAK;

 

 

-- UPDATE单列

UPDATE stuInfo SET stuAge = 26

WHERE stuNo = '035';

 

-- UPDATE多列

UPDATE stuInfo SET stuage = 26, beginDate = '2012-09-18'

WHERE stuNo= '035';

 

--删除学号为035的学生记录

DELETE FROM stuInfo WHERE stuNo = '035';

 

--查询语句

SELECT *  FROM stuInfo;

 

SELECT  * FROM  stuInfo where stuNo = '035';

 

SELECT  stuNo,stuName FROM  stuInfo;  

 

SELECT  distinct stuNo,stuName FROM  stuInfo;

 

SELECT  * FROM stuInfo order by stuage desc,stuNo asc;

 

CREATE TABLE  stuInfoBAK 

as SELECT  * FROM stuInfo;

 

SELECT ‘S’||stuNo 学号,stuName “姓 名” FROM  stuInfo;