当你需要进行下列操作时,DML语言被执行
—增加新的一行到表中
—修改表中已经存在的行
—从表中删除已经存在的行
一个事务是由一组逻辑单元的DML语句所组成
INSERT语句语法
使用INSERT语句增加新的行到表中
INSERT INTO table [(column [,column…])] VALUES (value [,value…]);
该语法一次只插入一行到表中
插入新的一行,每列都包含值
按照表中列的默认顺序列出数值
在INSERT子句中列出列是可选的
INSERT INTO departments(department_id,department_name,manager_id,location_id)
VALUES (70,’Public Relations’,100,1700);
将日期和字符数值放入单引号中
隐式方法:从列列表中将这些列忽略
INSERT INTO departments (department_id,department_name) VALUES(30,’Purchsing’);
显式方法:在VALUES子句中指定NULL关键字
INSERT INTO departments VALUES (100,’Finance’,NULL,NULL);
SYSDATE函数记录当前的日期和时间
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,
hire_date,job_id,salary,commission_pct,manager_id,department_id)
VALUES(113,’Louis’,’Popp’,’LPOPP’,’515.124.4567’,SYSDATE,’AC_ACCOUNT’,6900,NULL,
205,100);
增加一个新雇员
INSERT INTO employees VALUES (114,’DEN’,’Raphealy’,’DRAPHEAL’,’515.127.4561’,
TO_DATE(‘FEB 3,1999’,’MON DD,YYYY’),’AC_ACCOUNT’,11000,NULL,100,30);
从另外一张表拷贝行
在一个子查询中写INSERT语句
INSERT INTO sales_reps(id,name,salary,commission_pct) SELECT employee_id,
last_name,salary,commission_pct FROM employees WHERE job_id LIKE ‘%REP%’;
不要使用VALUES子句
INSERT子句中插入列的数量要和子查询中的数量相匹配
UPDATE语句语法
使用UPDATA语句修改已经存在的行
UPDATE table SET column = value [,column = value,…] [WHERE condition];
如果需要,一次更新可以多于一行
如果你指定了WHERE子句,指定你要修改的行
UPDATE employees SET department_id = 70 WHERE employee_id = 113;
如果你忽略了WHERE子句,表中所有的行都被修改
UPDATE employees SET department_id = 70;
更新雇员114的工作和工资记录,以匹配雇员205
UPDATE employees SET job_id = ( SELECT job_id FROM employees WHERE employee_id =205),salary =(SELECT salary FROM employees WHERE employee_id =
205) WHERE employee_id = 114;
在UPDATE语句中使用子查询,基于另外一张表的值更新本表中的行
UPDATE copy_emp SET department_id = (SELECT department_id FROM employees
WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees
WHERE employee_id = 200);
DELETE语句语法
你可以使用DELETE语句从一个表中删除已经存在的行
DELETE [FROM] table [WHERE condition];
WHERE子句中指定的行被删除
DELETE FROM departments WHERE department_name = ‘Finance’;
如果你忽略了WHERE子句,表中所有的行都会被删除
DELETE FROM copy_emp;
在DELETE语句中使用子查询,基于另外一张表的值删除本表中的行
DELETE FROM employees WHERE department_id = ( SELECT department_id FROM
Departments WHERE department_name LIKE ‘%Public%’);
在INSERT语句中使用子查询
INSERT INTO (SELECT employee_id,last_name,email,hire_date,job_id,salary,
department_id FROM employees WHERE department_id =50) VALUES (99999,
‘Taylor’,’DTAYLOR’,TO_DATE(’07-JUN-99’,’DD-MON-RR’),’ST_CLERK’,5000,50);