表管理之二：表数据的插入修改与删除
以下就让我们对表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行数据，相比少了两行数据。以上都是对表的数最基本据增删改查操作。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2125879/ ，如需转载，请注明出处，否则将追究法律责任。
