Oracle-06：DML语言数据表的操作

------------吾亦无他,唯手熟尔，谦卑若愚，好学若饥-------------

开篇放上一个SQL脚本，供测试使用

  1 create table DEPT
  2 (
  3   deptno NUMBER(2) not null,
  4   dname  VARCHAR2(20),
  5   loc    VARCHAR2(13)
  6 );
  7 
  8   
  9 alter table DEPT
 10   add constraint PK_DEPT primary key (DEPTNO);
 11 
 12 create table TEACHER
 13 (
 14   tno       NUMBER(4) not null,
 15   tname     VARCHAR2(30) not null,
 16   tid       CHAR(18),
 17   gendar    CHAR(3),
 18   birthdate DATE,
 19   job       VARCHAR2(20),
 20   hiredate  DATE,
 21   sal       NUMBER(7,2),
 22   deptno    NUMBER(2),
 23   mgrno     NUMBER(4),
 24   comm      NUMBER(7,2)
 25 );
 26  
 27 insert into DEPT (deptno, dname, loc)
 28 values (40, '人力部', '北京海淀');
 29 insert into DEPT (deptno, dname, loc)
 30 values (30, '产品部', '北京海淀');
 31 insert into DEPT (deptno, dname, loc)
 32 values (20, '招生部', '上海虹桥');
 33 insert into DEPT (deptno, dname, loc)
 34 values (10, '研究院', '北京海淀');
 35 insert into DEPT (deptno, dname, loc)
 36 values (50, '系统部', '北京昌平');
 37 commit;
 38 
 39 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 40 values (1002, '赵辉', '110101760609001   ', '男', to_date('09-06-1976', 'dd-mm-yyyy'), '考试专员', to_date('23-05-2006 02:40:40', 'dd-mm-yyyy hh24:mi:ss'), 29370.95, 40, null, null);
 41 
 42 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 43 values (1003, '杨利乐', '110101198705256045', '女', to_date('25-05-1987', 'dd-mm-yyyy'), '考试专员', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 7134.32, 40, null, null);
 44 
 45 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 46 values (1004, '王益辉', '110101199108154770', '男', to_date('15-08-1991', 'dd-mm-yyyy'), '营销专员', to_date('16-01-2012 03:12:43', 'dd-mm-yyyy hh24:mi:ss'), 30737.36, 20, null, 2300.00);
 47 
 48 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 49 values (1006, '秦璐璐', '110101199004153068', '女', to_date('14-04-1990', 'dd-mm-yyyy'), '运维工程师', to_date('20-06-2012 00:47:09', 'dd-mm-yyyy hh24:mi:ss'), 9013.25, 10, null, null);
 50 
 51 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 52 values (1007, '梁改焕', '110101196911206548', '女', to_date('20-11-1969', 'dd-mm-yyyy'), '讲师', to_date('13-06-2006 23:07:02', 'dd-mm-yyyy hh24:mi:ss'), 8664.16, 50, null, null);
 53 
 54 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 55 values (1008, '冯爱仙', '110101196804021521', '女', to_date('02-04-1968', 'dd-mm-yyyy'), '讲师', to_date('06-06-2006 13:41:50', 'dd-mm-yyyy hh24:mi:ss'), 29936.29, 30, null, null);
 56 
 57 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 58 values (1009, '秦亚杰', '110101196312163531', '男', to_date('16-12-1963', 'dd-mm-yyyy'), '岗位分析师', to_date('13-07-2005 06:32:30', 'dd-mm-yyyy hh24:mi:ss'), 24991.44, 30, null, null);
 59 
 60 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 61 values (1010, '刘浩星', '110101198806283516', '男', to_date('28-06-1988', 'dd-mm-yyyy'), '考试专员', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 22645.92, null, null, null);
 62 
 63 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 64 values (1011, '秦春叶', '110101197103033540', '女', to_date('03-03-1971', 'dd-mm-yyyy'), '网络营销师', to_date('07-07-2007 16:02:04', 'dd-mm-yyyy hh24:mi:ss'), 31653.23, 50, null, null);
 65 
 66 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 67 values (1012, '陈水花', '110101192811043026', '女', to_date('04-11-1928', 'dd-mm-yyyy'), '考试专员', to_date('29-01-2005 23:21:59', 'dd-mm-yyyy hh24:mi:ss'), 29773.37, 20, null, null);
 68 
 69 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 70 values (1013, '马付妮', '110101195806133522', '女', to_date('13-06-1958', 'dd-mm-yyyy'), '研发', to_date('17-11-2004 08:54:04', 'dd-mm-yyyy hh24:mi:ss'), 17041.60, 40, null, null);
 71 
 72 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 73 values (1015, '刘得安', '110101195210083518', '男', to_date('08-10-1952', 'dd-mm-yyyy'), '考试专员', to_date('13-03-2005 23:25:37', 'dd-mm-yyyy hh24:mi:ss'), 30356.99, 20, null, null);
 74 
 75 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 76 values (1016, '高利芹', '110101198307276020', '女', to_date('27-07-1983', 'dd-mm-yyyy'), '岗位分析师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 25751.36, null, null, null);
 77 
 78 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 79 values (1017, '刘志刚', '110101197806164493', '男', to_date('16-06-1978', 'dd-mm-yyyy'), '运维工程师', to_date('10-05-2008 13:46:33', 'dd-mm-yyyy hh24:mi:ss'), 22344.73, 20, null, null);
 80 
 81 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 82 values (1018, '高心立', '110101194102076011', '男', to_date('07-02-1941', 'dd-mm-yyyy'), '网络营销师', to_date('03-03-2005 15:17:07', 'dd-mm-yyyy hh24:mi:ss'), 19981.02, 40, null, null);
 83 
 84 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 85 values (1019, '徐丽', '11010119790809354X', '男', to_date('09-08-1979', 'dd-mm-yyyy'), '研发', to_date('09-12-2009 18:37:22', 'dd-mm-yyyy hh24:mi:ss'), 10950.33, 30, null, null);
 86 
 87 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 88 values (1021, '刘志红', '110101196504284452', '男', to_date('28-04-1965', 'dd-mm-yyyy'), '岗位分析师', to_date('20-09-2011 10:51:33', 'dd-mm-yyyy hh24:mi:ss'), 7122.60, 20, null, null);
 89 
 90 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 91 values (1022, '孔鹏飞', '110101198603304014', '男', to_date('30-03-1986', 'dd-mm-yyyy'), '人力资源师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 7045.28, 40, null, null);
 92 
 93 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 94 values (1023, '李建坡', '110101731107301   ', '男', to_date('07-11-1973', 'dd-mm-yyyy'), '网络营销师', to_date('04-07-2009 06:28:20', 'dd-mm-yyyy hh24:mi:ss'), 21133.91, 50, null, null);
 95 
 96 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
 97 values (1024, '冯爱敏', '110101196907283840', '女', to_date('18-07-1969', 'dd-mm-yyyy'), '营销专员', to_date('12-09-2005 22:41:06', 'dd-mm-yyyy hh24:mi:ss'), 3360.91, 20, null, 50000.00);
 98 
 99 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
100 values (1025, '薛栓成', '110101400710051   ', '男', to_date('10-07-1940', 'dd-mm-yyyy'), '网络营销师', to_date('03-09-2007 14:55:06', 'dd-mm-yyyy hh24:mi:ss'), 12136.65, 40, null, null);
101 
102 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
103 values (1026, '牛伟伟', '11010119851120602X', '女', to_date('20-11-1985', 'dd-mm-yyyy'), '岗位分析师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 6622.66, 30, null, null);
104 
105 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
106 values (1027, '郭香云', '110101196010154525', '女', to_date('15-10-1960', 'dd-mm-yyyy'), '网络营销师', to_date('03-05-2005 13:01:22', 'dd-mm-yyyy hh24:mi:ss'), 26436.18, 50, null, null);
107 
108 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
109 values (1028, '朱花枝', '110101195605154028', '女', to_date('15-05-1956', 'dd-mm-yyyy'), '研发', to_date('11-09-2007 07:41:01', 'dd-mm-yyyy hh24:mi:ss'), 10095.22, 50, null, null);
110 
111 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
112 values (1029, '李岗', '110101196505163095', '男', to_date('16-05-1965', 'dd-mm-yyyy'), '岗位分析师', to_date('09-11-2007 17:26:38', 'dd-mm-yyyy hh24:mi:ss'), 23928.84, 30, null, null);
113 
114 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
115 values (1030, '李军会', '110101197706152524', '男', to_date('15-06-1977', 'dd-mm-yyyy'), '营销专员', to_date('12-05-2007 19:48:21', 'dd-mm-yyyy hh24:mi:ss'), 3492.19, 20, null, 25000.00);
116 
117 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
118 values (1031, '孙龙举', '110101198908306037', '男', to_date('30-08-1989', 'dd-mm-yyyy'), '运维工程师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 4323.84, 30, null, null);
119 
120 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
121 values (1032, '韩朝旭', '110101199003270513', '男', to_date('27-03-1900', 'dd-mm-yyyy'), '人力资源师', to_date('11-09-2010 17:54:30', 'dd-mm-yyyy hh24:mi:ss'), 13740.92, 40, null, null);
122 
123 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
124 values (1033, '孙少光', '110101198809274535', '男', to_date('27-09-1988', 'dd-mm-yyyy'), '网络营销师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 19778.62, 50, null, null);
125 
126 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
127 values (1035, '邓香敏', '110101196607163109', '女', to_date('16-07-1966', 'dd-mm-yyyy'), '人力资源师', to_date('12-04-2007 17:47:14', 'dd-mm-yyyy hh24:mi:ss'), 29463.38, 40, null, null);
128 
129 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
130 values (1037, '霍振方', '110101198411070014', '男', to_date('07-11-1984', 'dd-mm-yyyy'), '讲师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 23731.31, 40, 1087, null);
131 
132 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
133 values (1038, '许双鸽', '110101781217004   ', '女', to_date('17-12-1978', 'dd-mm-yyyy'), '网络营销师', to_date('05-09-2007 18:45:00', 'dd-mm-yyyy hh24:mi:ss'), 16512.52, 30, null, null);
134 
135 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
136 values (1040, '张春红', '110101197806176089', '女', to_date('17-06-1978', 'dd-mm-yyyy'), '运维工程师', to_date('21-11-2004 06:52:30', 'dd-mm-yyyy hh24:mi:ss'), 31728.83, 10, null, null);
137 
138 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
139 values (1041, '卢玉莲', '110101510606004   ', '女', to_date('06-06-1951', 'dd-mm-yyyy'), '人力资源师', to_date('30-03-2009 17:05:45', 'dd-mm-yyyy hh24:mi:ss'), 17596.43, 40, null, null);
140 
141 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
142 values (1042, '李军会', '110101197706152524', '男', to_date('15-06-1977', 'dd-mm-yyyy'), '讲师', to_date('13-06-2008 03:24:47', 'dd-mm-yyyy hh24:mi:ss'), 8443.59, 50, 1008, null);
143 
144 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
145 values (1043, '朱水娃', '110101195210033512', '男', to_date('03-10-1952', 'dd-mm-yyyy'), '讲师', to_date('11-10-2009 03:40:53', 'dd-mm-yyyy hh24:mi:ss'), 9123.09, 30, 1008, null);
146 
147 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
148 values (1045, '高大荣', '110101195504073528', '女', to_date('07-04-1955', 'dd-mm-yyyy'), '考试专员', to_date('07-12-2005 14:56:05', 'dd-mm-yyyy hh24:mi:ss'), 4000.00, 10, null, null);
149 
150 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
151 values (1046, '秦付根', '110101195301034035', '男', to_date('03-01-1953', 'dd-mm-yyyy'), '讲师', to_date('10-12-2004 22:51:51', 'dd-mm-yyyy hh24:mi:ss'), 16420.33, 30, null, null);
152 
153 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
154 values (1047, '樊增妮', '110101410715352   ', '女', to_date('15-07-1941', 'dd-mm-yyyy'), '网络营销师', to_date('02-12-2005 14:41:54', 'dd-mm-yyyy hh24:mi:ss'), 13316.01, 20, null, null);
155 
156 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
157 values (1048, '宋素敏', '11010119720111458X', '女', to_date('11-02-1972', 'dd-mm-yyyy'), '岗位分析师', to_date('26-09-2004 12:15:57', 'dd-mm-yyyy hh24:mi:ss'), 2142.92, 40, null, null);
158 
159 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
160 values (1049, '李记', '110101194803256012', '男', to_date('25-03-1948', 'dd-mm-yyyy'), '网络营销师', to_date('23-03-2006 19:12:45', 'dd-mm-yyyy hh24:mi:ss'), 13866.09, 50, null, null);
161 
162 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
163 values (1050, '秦单风', '110101198911153625', '女', to_date('15-11-1989', 'dd-mm-yyyy'), '考试专员', to_date('21-10-2012 05:31:34', 'dd-mm-yyyy hh24:mi:ss'), 21419.38, 40, null, null);
164 
165 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
166 values (1051, '秦占豪', '110101198509034011', '男', to_date('03-09-1985', 'dd-mm-yyyy'), '讲师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 31564.79, 40, 1087, null);
167 
168 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
169 values (1054, '张利娟', '110101197901173062', '女', to_date('17-01-1979', 'dd-mm-yyyy'), '网络营销师', to_date('08-05-2011 22:37:13', 'dd-mm-yyyy hh24:mi:ss'), 7419.99, 50, null, null);
170 
171 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
172 values (1055, '陈向琼', '11010119871010354X', '男', to_date('10-10-1987', 'dd-mm-yyyy'), '讲师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 4000.00, 10, 1087, null);
173 
174 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
175 values (1056, '杨晓磊', '110101199105106079', '男', to_date('10-05-1991', 'dd-mm-yyyy'), '研发', to_date('05-04-2012 22:16:05', 'dd-mm-yyyy hh24:mi:ss'), 20812.41, 30, null, null);
176 
177 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
178 values (1057, '黄芳菊', '110101196611133520', '女', to_date('13-11-1966', 'dd-mm-yyyy'), '网络营销师', to_date('04-07-2008 17:36:20', 'dd-mm-yyyy hh24:mi:ss'), 12711.46, 40, null, null);
179 
180 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
181 values (1058, '李红勤', '110101197209084047', '女', to_date('08-09-1972', 'dd-mm-yyyy'), '运维工程师', to_date('01-03-2007 05:57:34', 'dd-mm-yyyy hh24:mi:ss'), 21119.95, 30, null, null);
182 
183 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
184 values (1060, '薛栓成', '110101400710051   ', '男', to_date('10-07-1940', 'dd-mm-yyyy'), '考试专员', to_date('22-08-2007 08:06:47', 'dd-mm-yyyy hh24:mi:ss'), 24544.66, 40, null, null);
185 
186 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
187 values (1061, '高发娃', '110101192905226032', '男', to_date('22-05-1929', 'dd-mm-yyyy'), '运维工程师', to_date('12-12-2004 22:49:04', 'dd-mm-yyyy hh24:mi:ss'), 23458.34, 10, null, null);
188 
189 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
190 values (1062, '李新昌', '110101196403106013', '男', to_date('10-03-1964', 'dd-mm-yyyy'), '研发', to_date('01-04-2011 18:00:33', 'dd-mm-yyyy hh24:mi:ss'), 18605.46, 40, null, null);
191 
192 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
193 values (1065, '赵永刚', '110101197507310014', '男', to_date('31-07-1975', 'dd-mm-yyyy'), '岗位分析师', to_date('05-12-2009 15:00:29', 'dd-mm-yyyy hh24:mi:ss'), 3155.96, 20, null, null);
194 
195 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
196 values (1067, '冯世伟', '110101198811154532', '男', to_date('15-11-1988', 'dd-mm-yyyy'), '讲师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 17854.88, 40, 1087, null);
197 
198 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
199 values (1068, '张延付', '110101197005233514', '男', to_date('23-05-1970', 'dd-mm-yyyy'), '岗位分析师', to_date('21-05-2009 10:16:16', 'dd-mm-yyyy hh24:mi:ss'), 24294.43, 30, null, null);
200 
201 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
202 values (1070, '陈粉', '110101195801206040', '女', to_date('20-01-1958', 'dd-mm-yyyy'), '营销专员', to_date('29-06-2011 21:27:04', 'dd-mm-yyyy hh24:mi:ss'), 17708.56, 20, null, 600.00);
203 
204 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
205 values (1071, '张根祥', '110101194412126513', '男', to_date('12-12-1944', 'dd-mm-yyyy'), '岗位分析师', to_date('06-10-2006 12:14:34', 'dd-mm-yyyy hh24:mi:ss'), 8802.52, null, null, null);
206 
207 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
208 values (1074, '李秋彩', '110101197411186020', '女', to_date('18-11-1974', 'dd-mm-yyyy'), '考试专员', to_date('09-04-2005 01:11:29', 'dd-mm-yyyy hh24:mi:ss'), 19315.97, 40, null, null);
209 
210 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
211 values (1075, '李建玲', '110101198110164423', '女', to_date('16-10-1981', 'dd-mm-yyyy'), '运维工程师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 9023.25, 50, null, null);
212 
213 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
214 values (1076, '张仙', '110101530824316   ', '女', to_date('24-08-1953', 'dd-mm-yyyy'), '考试专员', to_date('09-05-2011 11:15:29', 'dd-mm-yyyy hh24:mi:ss'), 8654.83, 40, null, null);
215 
216 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
217 values (1077, '朱金典', '110101196810023531', '男', to_date('02-10-1968', 'dd-mm-yyyy'), '营销专员', to_date('11-01-2008 11:13:31', 'dd-mm-yyyy hh24:mi:ss'), 6687.97, 20, null, 2700.00);
218 
219 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
220 values (1079, '孙玉环', '110101195006056047', '女', to_date('05-06-1950', 'dd-mm-yyyy'), '考试专员', to_date('17-11-2008 12:48:12', 'dd-mm-yyyy hh24:mi:ss'), 7490.24, 20, null, null);
221 
222 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
223 values (1080, '邢中景', '110101198208284519', '男', to_date('28-08-1982', 'dd-mm-yyyy'), '运维工程师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 24990.57, 40, null, null);
224 
225 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
226 values (1081, '韩巧真', '110101198404190026', '女', to_date('19-04-1984', 'dd-mm-yyyy'), '人力资源师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 9880.26, 40, null, null);
227 
228 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
229 values (1084, '杜巧琴', '110101196201036089', '女', to_date('03-01-1962', 'dd-mm-yyyy'), '岗位分析师', to_date('13-01-2011 16:11:23', 'dd-mm-yyyy hh24:mi:ss'), 21655.36, 20, null, null);
230 
231 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
232 values (1085, '席妞娃', '110101194601154026', '女', to_date('15-01-1946', 'dd-mm-yyyy'), '运维工程师', to_date('15-08-2008 06:36:11', 'dd-mm-yyyy hh24:mi:ss'), 24615.12, 20, null, null);
233 
234 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
235 values (1087, '薛素粉', '110101196701044528', '女', to_date('04-01-1967', 'dd-mm-yyyy'), '讲师', to_date('10-09-2007 06:07:14', 'dd-mm-yyyy hh24:mi:ss'), 12357.56, null, 1008, null);
236 
237 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
238 values (1088, '王雪英', '110101193311264521', '女', to_date('26-11-1933', 'dd-mm-yyyy'), '岗位分析师', to_date('18-02-2006 23:37:15', 'dd-mm-yyyy hh24:mi:ss'), 23670.53, 10, null, null);
239 
240 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
241 values (1089, '刘秀菊', '110101530414306   ', '女', to_date('14-04-1953', 'dd-mm-yyyy'), '研发', to_date('11-09-2004 18:17:49', 'dd-mm-yyyy hh24:mi:ss'), 22608.20, null, null, null);
242 
243 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
244 values (1090, '刘芳芳', '110101198912216025', '女', to_date('21-12-1989', 'dd-mm-yyyy'), '网络营销师', to_date('10-10-2012 00:24:11', 'dd-mm-yyyy hh24:mi:ss'), 9749.45, 30, null, null);
245 
246 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
247 values (1091, '郝方方', '110101198605165580', '女', to_date('16-05-1986', 'dd-mm-yyyy'), '运维工程师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 5944.72, 40, null, null);
248 
249 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
250 values (1092, '赵利萍', '110101196712063564', '女', to_date('06-12-1967', 'dd-mm-yyyy'), '岗位分析师', to_date('01-08-2006 17:26:08', 'dd-mm-yyyy hh24:mi:ss'), 4575.09, 30, null, null);
251 
252 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
253 values (1093, '杨联星', '110101195312284013', '男', to_date('28-12-1953', 'dd-mm-yyyy'), '考试专员', to_date('27-10-2009 08:41:47', 'dd-mm-yyyy hh24:mi:ss'), 24545.93, 40, null, null);
254 
255 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
256 values (1094, '冯焕', '110101194205093524', '女', to_date('09-05-1942', 'dd-mm-yyyy'), '运维工程师', to_date('27-09-2010 18:25:55', 'dd-mm-yyyy hh24:mi:ss'), 21391.42, 50, null, null);
257 
258 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
259 values (1096, '杨晓雨', '110101198910070043', '女', to_date('07-10-1989', 'dd-mm-yyyy'), '营销专员', to_date('07-08-2013 05:49:14', 'dd-mm-yyyy hh24:mi:ss'), 4161.31, 20, null, 2322.00);
260 
261 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
262 values (1097, '柴帅友', '110101198510254038', '男', to_date('25-10-1985', 'dd-mm-yyyy'), '网络营销师', to_date('24-11-2011 16:08:38', 'dd-mm-yyyy hh24:mi:ss'), 7805.94, 50, null, null);
263 
264 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
265 values (1098, '韩国强', '110101621016003   ', '男', to_date('16-10-1962', 'dd-mm-yyyy'), '研发', to_date('01-09-2007 12:30:52', 'dd-mm-yyyy hh24:mi:ss'), 13755.87, null, null, null);
266 
267 insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
268 values (1099, '王参妮', '11010119490831352X', '女', to_date('31-08-1949', 'dd-mm-yyyy'), '岗位分析师', to_date('11-10-2005 13:17:18', 'dd-mm-yyyy hh24:mi:ss'), 26627.94, 30, null, null);
269 
270 commit;

Sql脚本

导入方式有两种：

　　1.通过红桶或者其他工具里面执行一遍代码

　　2.通过cmd命令导入

　　　　方式如下：

　　　　打开cmd,登陆oracle

　　　　sqlplus 用户名/密码

　　　　之后执行这一句

　　　　@ 你所放的sql脚本的全路径

DML语言，无非是增删改查

回顾一下sql语法，往下开始写：

一，内连接

-- 查询老师姓名，部门名称
隐式内连接
select tname,dname from teacher,dept
where teacher.deptno=dept.deptno;
显式内连接
select tname,dname from teacher inner join dept
on teacher.deptno=dept.deptno;

二，左连接

select tname,dname from teacher left join dept
on teacher.deptno=dept.deptno;

三，右连接

select tname,dname from teacher right join dept
on teacher.deptno=dept.deptno;

四，全连接

select tname,dname from teacher full join dept
on teacher.deptno=dept.deptno;

五，模糊查询

-- 先修改表中的数据   查询姓刘的老师姓名 2个字
--  _代表一个字符  %代表0到N个字符
select tname from teacher 
where tname like '刘_'


select tname from teacher 
where tname like '刘%'

--查询老师姓刘或者姓赵的 老师姓名和薪水
select tname,sal from teacher 
where tname like '刘%' or tname like '赵%'

-- 在上诉例子中 增加条件   薪水降序排列
select tname,sal from teacher 
where tname like '刘%' or tname like '赵%'
order by sal desc

六，修改操作

-- 再增加条件  薪水低于5K的 薪资+1k

update teacher set sal=sal+1000
where  tname like '刘%' or tname like '赵%'
and sal<=5000

七，between

--  查询老师薪水在10k-20k之间的老师姓名和薪水
select tname,sal from teacher
where sal between 10000 and 20000

八，in

-- 查询教师职位是讲师或者考试专员的老师姓名和职位   
select tname,job from teacher
where job in('讲师','考试专员')

秒客网

Oracle-06：DML语言数据表的操作

相关文章