上一篇博文中建立了4個資料表，基本建表法所建立的表都是空白的，只有表結構，而沒有資料，
以下就讓我們對錶students、courses、s_grade與total 一一進行部分資料的插入，再進一步對部分
資料進行修改：更新或者刪除。

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行資料，相比少了兩行資料。

以上都是對錶的數最基本據增刪改查操作。

表管理之二：表資料的插入修改與刪除

相關文章