语法与其它的sql数据库教程是一样的,如下
DELETE FROM COURSES
WHERECOURSE_DESIGNATER = 'Java110'
按条件比较复杂的操作方法
DELETE FROM CLASSCONVENINGS
WHERECLASSES_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'
下面来看个详细的实例
SQL> -- create demo table
SQL> create table Employee(
2IDVARCHAR2(4 BYTE)NOT NULL,
3First_NameVARCHAR2(10 BYTE),
4Last_NameVARCHAR2(10 BYTE),
5Start_DateDATE,
6End_DateDATE,
7SalaryNumber(8,2),
8CityVARCHAR2(10 BYTE),
9DescriptionVARCHAR2(15 BYTE)
10)
11/
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,First_Name, Last_Name,Start_Date,End_Date,Salary,City,Description)
2values ('01','Jason','Martin',to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56,'Toronto','Programmer')
3/
1 row created.
SQL> insert into Employee(ID,First_Name, Last_Name,Start_Date,End_Date,Salary,City,Description)
2values('02','Alison','Mathews',to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78,'Vancouver','Tester')
3/
1 row created.
SQL> insert into Employee(ID,First_Name, Last_Name,Start_Date,End_Date,Salary,City,Description)
2values('03','James','Smith',to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78,'Vancouver','Tester')
3/
1 row created.
SQL> insert into Employee(ID,First_Name, Last_Name,Start_Date,End_Date,Salary,City,Description)
2values('04','Celia','Rice',to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78,'Vancouver','Manager')
3/
1 row created.
SQL> insert into Employee(ID,First_Name, Last_Name,Start_Date,End_Date,Salary,City,Description)
2values('05','Robert','Black',to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78,'Vancouver','Tester')
3/
1 row created.
SQL> insert into Employee(ID,First_Name, Last_Name,Start_Date,End_Date,Salary,City,Description)
2values('06','Linda','Green',to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'),4322.78,'New York','Tester')
3/
1 row created.
SQL> insert into Employee(ID,First_Name, Last_Name,Start_Date,End_Date,Salary,City,Description)
2values('07','David','Larry',to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'),7897.78,'New York','Manager')
3/
1 row created.
SQL> insert into Employee(ID,First_Name, Last_Name,Start_Date,End_Date,Salary,City,Description)
2values('08','James','Cat',to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'),1232.78,'Vancouver', 'Tester')
3/
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2/
IDFIRST_NAME LAST_NAMESTART_DAT END_DATESALARY CITYDESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01JasonMartin25-JUL-96 25-JUL-061234.56 TorontoProgrammer
02AlisonMathews21-MAR-76 21-FEB-866661.78 VancouverTester
03JamesSmith12-DEC-78 15-MAR-906544.78 VancouverTester
04CeliaRice24-OCT-82 21-APR-992344.78 VancouverManager
05RobertBlack15-JAN-84 08-AUG-982334.78 VancouverTester
06LindaGreen30-JUL-87 04-JAN-964322.78 New YorkTester
07DavidLarry31-DEC-90 12-FEB-987897.78 New YorkManager
IDFIRST_NAME LAST_NAMESTART_DAT END_DATESALARY CITYDESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
08JamesCat17-SEP-96 15-APR-021232.78 VancouverTester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- delete command with conditions
SQL>
SQL> delete from Employee where Salary > 3000;
4 rows deleted.
SQL>
SQL> select * from Employee;
IDFIRST_NAME LAST_NAMESTART_DAT END_DATESALARY CITYDESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01JasonMartin25-JUL-96 25-JUL-061234.56 TorontoProgrammer04CeliaRice24-OCT-82 21-APR-992344.78 VancouverManager
05RobertBlack15-JAN-84 08-AUG-982334.78 VancouverTester
08JamesCat17-SEP-96 15-APR-021232.78 VancouverTester
4 rows selected.
删除指定的内容
SQL> CREATE TABLE project (
2pro_idNUMBER(4),
3pro_nameVARCHAR2(40),
4budgetNUMBER(9,2),
5CONSTRAINT project_pkPRIMARY KEY (pro_id)
6);
Table created.
SQL>
SQL>
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, 'A',1912000);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, 'ERP',9999999);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, 'SQL',897000);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, 'CRM',294000);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, 'VPN',415000);
1 row created.
SQL>
SQL>
SQL> SET ECHO ON
SQL> DELETE
2FROM project
3WHERE pro_id = 1006
4