AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2018-05-07 1036
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：   ------------吾亦无他,唯手熟尔，谦卑若愚，好学若饥-------------     开篇放上一个SQL脚本，供测试使用   1 create table DEPT 2 ( 3 deptno NUMBER(2) not null, 4...

 

 

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

 

 

开篇放上一个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('讲师','考试专员')

 

文章标签：
关系型数据库
运维
SQL
关键词：
Oracle数据库一键上云语言
Oracle数据库一键上云数据表
Oracle数据库一键上云dml
Oracle数据库一键上云dml语言
晨曦dawn
目录
相关文章
讓丄帝愛伱
|
Oracle 关系型数据库
Oracle新建数据表的两种方法
Oracle新建数据表的两种方法
讓丄帝愛伱
88 1
SarPro
|
6月前
|
SQL Oracle 数据可视化
【Oracle】玩转Oracle数据库（四）：SQL语言
【Oracle】玩转Oracle数据库（四）：SQL语言
SarPro
80 8
姚远OracleACE
|
SQL 数据采集 Oracle
GoldenGate19.1 Oracle单向dml配置全过程
文档说明：GoldenGate19.1 Oracle单向dml配置全过程，源端说19c数据库，asm磁盘组，目标端是11g，本地文件系统。
姚远OracleACE
156 0
翎野君
|
SQL Oracle 安全
Oracle DDL+DML+DCL实例
Oracle DDL+DML+DCL实例
翎野君
125 0
时间静止不是简史
|
SQL Oracle 关系型数据库
Oracle中的分组查询与DML
Oracle中的分组查询与DML相关知识点介绍
时间静止不是简史
96 0
暮角
|
存储 SQL 专有云
Oracle存储过程迁移ODPS-01（专有云）：支持DML(delete/update/merge)SQL
关系型数据库支持的DML(delete/update/merge)SQL ，在maxcompute(ODPS)该如何写？ 总有人问，现写了一个例子，应该可以说明了。 有问题，欢迎大家指正。
暮角
2599 0
笱局长
|
SQL Oracle 关系型数据库
Oracle数据表碎片整理
数据表的增删改总是避免不了产生碎片的问题，在Oracle引入表空间本地管理和ASSM之后，极端情况下，明明表空间使用率不高，需要入库的数据库对象也不大，但就是报错，原因就是无法再分配足够的连续extent （一）创建测试环境 sys@ORCL> select * from v$version; .
笱局长
3660 0
笱局长
|
SQL Oracle 关系型数据库
Oracle Dataguard 下块修复技术-系统数据表（三）
笱局长
1576 0
笱局长
|
Oracle 关系型数据库 数据库
Oracle Dataguard 下块修复技术-用户数据表（二）
笱局长
1660 0
晨曦dawn
|
SQL 数据库 索引
Oracle-02：SQL语言的分类或者说SQL语言的组成
    ------------吾亦无他,唯手熟尔，谦卑若愚，好学若饥-------------     小结一版：    01.DDL（Data Definition Language）数据定义语言。
晨曦dawn
831 0

热门文章

最新文章

  • 1
    Oracle自动清理日志脚本
  • 2
    云MSP服务案例|云上Oracle RAC部署运维及实践
  • 3
    Oracle PL/sql 主要特点
  • 4
    Oracle 11g RAC ASM 错误之(1)
  • 5
    Oracle SQL总结 - 2
  • 6
    oracle和sqlserver中空值的区别
  • 7
    Oracle 语句大全
  • 8
    Oracle 索引质量分析
  • 9
    Linux 下 Oracle随系统自动启动和关闭
  • 10
    【Oracle】使用hanganalyze 命令分析数据库hang【转】
  • 1
    关系型数据库Oracle并行执行
    87
  • 2
    关系型数据库Oracle并行度（Degree of Parallelism, DOP）
    157
  • 3
    关系型数据库Oracle并行查询
    103
  • 4
    mybatis使用statement.getGenreatedKeys()； useGeneratedKeys=”true”；使用自增主键获取主键值策略和Oracle不支持自增，Oracle使用序列
    57
  • 5
    关系型数据库Oracle GoldenGate
    37
  • 6
    关系型数据库Oracle Data Guard
    38
  • 7
    关系型数据库Oracle Real Application Clusters (RAC)
    107
  • 8
    关系型数据库Oracle并行处理能力
    72
  • 9
    关系型数据库Oracle 资源共享
    38
  • 10
    关系型数据库Oracle 故障转移能力
    51

    • 相关课程

    更多
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询
  • 如何在 PolarDB-X 中进行 Online DDL
  • MySQL高级应用 - 索引和锁

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • 一小时快速掌握 SQL 语法
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
  • SQLServer数据库的创建

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    DataWorks售前咨询