表管理之二：表数据的插入修改与删除-CSDN博客

上一篇博文中创建了4个数据表，基本建表法所创建的表都是空白的，只有表结构，而没有数据，
以下就让我们对表 students、 courses、 s_grade与 tota l 一一进行部分数据的插入，再进一步对部分
数据进行修改：更新或者删除。

1、插入数据并查询 ：（对表插入数据有多种方法，在此只介绍两种 ）insert into table_name values(..... )
1》表 students：

insert into students values (45211,'Mar','21-Jun-02','man'); ---------------------所有字段都插入数据的方法

insert into students values (45212,'Maro','21-Mar-02','man');

insert into students values (45221,'July','29-Jun-02','woman');

insert into students values (45222,'Marry','12-Jun-03','woman');

insert into students values (45223,'Mare','21-Jun-02','woman');

insert into students values (45214,'Jone','10-Jul-02','man');

insert into students values (45215,'Kaha','31-Aug-04','man');

insert into students(student_id,name) values (45216,'Kaka'); ------------------只插入部分字段数据的方法

commit;

SQL> select * from students;

STUDENT_ID NAME DOB SEX

---------- --------------- --------- ------

45211 Mar 21-JUN-02 man

45212 Maro 21-MAR-02 man

45221 July 29-JUN-02 woman

45222 Marry 12-JUN-03 woman

45223 Mare 21-JUN-02 woman

45214 Jone 10-JUL-02 man

45215 Kaha 31-AUG-04 man

45216 Kaka ----------------------------- 部分字段插入数据

8 rows selected.

2》表courses：

insert into courses values(1002,'Earth',3,32,'0201');

insert into courses values(1003,'Earth Song',2,24,'0202');

insert into courses values(1012,'The Earth',3,32,'0211');

insert into courses values(1202,'History',4,48,'0221');

insert into courses values(1204,'Eat and Sport',2,32,'0222');

insert into courses values(1222,'My song of the her',4,48,'0201');

insert into courses values(1102,'With the Earth',6,72,'0200');

commit;

SQL> select * from courses;

COURSE_ID COURSE_NMAE CREDIT_HOUR CREDIT_HOURS TERM

---------- ------------------------------ ----------- ------------ ----

1002 Earth 3 32 0201

1003 Earth Song 2 24 0202

1012 The Earth 3 32 0211

1202 History 4 48 0221

1204 Eat and Sport 2 32 0222

1222 My song of the her 4 48 0201

1102 With the Earth 6 72 0200

3》表 s_grade：

insert into s_grade values(45211,1002,89);

insert into s_grade values(45212,1003,89);

insert into s_grade values(45211,1222,90);

insert into s_grade values(45212,1002,75);

。。。。。。

insert into s_grade values(45214,1012,83);

insert into s_grade values(45214,1222,70);

insert into s_grade(student_id,course_id) values(45216,1222);

commit;

SQL> select * from s_grade;

STUDENT_ID COURSE_ID SCORE

---------- ---------- ----------

45211 1002 89

45212 1003 89

45211 1222 90

45212 1002 75

。。。。。。

45214 1222 70

45216 1222

16 rows selected.

4》表 total ：

insert into total values(3,12);

insert into total values(4,15);

insert into total values(5,20);

commit;

SQL> select * from total;

T1 T2

---------- ----------

3 12

4 15

5 20

2、更新数据：update table_name set column ....

本次以courses 表作为测试对象：

SQL> select * from courses; ---------更新前

COURSE_ID COURSE_NMAE CREDIT_HOUR CREDIT_HOURS TERM

---------- ------------------------------ ----------- ------------ ----

1002 Earth 3 32 0201

1003 Earth Song 2 24 0202

1012 The Earth 3 32 0211

1202 History 4 48 0221

1204 Eat and Sport 2 32 0222

1222 My song of the her 4 48 0201

1102 With the Earth 6 72 0200

7 rows selected.

更新：course_id为1002的course_name（课程名）：

SQL> update courses set course_nmae = 'Earth and Land'

2 where course_id = 1002;

1 row updated.

SQL> select * from courses; -----------更新后

COURSE_ID COURSE_NMAE CREDIT_HOUR CREDIT_HOURS TERM

---------- ------------------------------ ----------- ------------ ----

1002 Earth and Land 3 32 0201

1003 Earth Song 2 24 0202

1012 The Earth 3 32 0211

1202 History 4 48 0221

1204 Eat and Sport 2 32 0222

1222 My song of the her 4 48 0201

1102 With the Earth 6 72 0200

3、删除表数据：delete from table_name where ...

删除s_grade 中SCORE为89分的数据：

SQL> select * from s_grade;

STUDENT_ID COURSE_ID SCORE

---------- ---------- ----------

45211 1002 89

45212 1003 89

45211 1222 90

45212 1002 75

45221 1012 85

45221 1002 85

45221 1204 67

45223 1202 56

45223 1102 79

45223 1012 81

45215 1204 98

STUDENT_ID COURSE_ID SCORE

---------- ---------- ----------

45215 1222 81

45214 1002 90

45214 1012 83

45214 1222 70

45216 1222

16 rows selected.

SQL> delete from s_grade where SCORE=89;

2 rows deleted.

SQL> select * from s_grade;

STUDENT_ID COURSE_ID SCORE

---------- ---------- ----------

45211 1222 90

45212 1002 75

45221 1012 85

45221 1002 85

45221 1204 67

45223 1202 56

45223 1102 79

45223 1012 81

45215 1204 98

45215 1222 81

45214 1002 90

STUDENT_ID COURSE_ID SCORE

---------- ---------- ----------

45214 1012 83

45214 1222 70

45216 1222

14 rows selected.

删除了2行数据，相比少了两行数据。

以上都是对表的数最基本据增删改查操作。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2125879/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2125879/