PL/SQL模块学习之四、数据操纵语言

1.数据操纵语言

1.1 INSERT

语法格式：

INSERT INTO tablename [(column [,column ...] ) ] VALUES (value [, value... ] )

• 用户需对该表有操作权限
• 可不使用任何column
• value值数值类型需与column相匹配

EX:

SQL> INSERT INTO dept (deptno,dname,loc)
  2  VALUES (50,'MARKETING','NEW YORK');

1.2 UPDATE

语法格式：

UPDATE tablename
SET column = value [, column = value, ... ]
[WHERE condition];

EX:

SQL> UPDATE dept
  2  SET LOC = 'NEW YORK'
  3  WHERE DEPTNO = 60;

也可使用子查询进行更新

UPDATE table
SET column = 
				(SELECT column 
				FROM tablename
				WHERE condition)
		[,
column = 
				(SELECT column
				FROM tablename
				WHERE condition)]
[WHERE condition];

EX:

SQL> UPDATE dept
  2  set LOC = 
  3          (SELECT LOC
  4           FROM dept
  5           WHERE dname = 'MARKETING')
  6  WHERE DEPTNO =60;

1.3 DELETE

语法格式：

DELETE [FROM] tablename
[WHERE condition];

• FROM 关键字可选
• WHERE 子句也可选，不使用时，将删除表中所有行（保留表）