长沙有哪些网站建设公司好,代运营竞价公司,网页设计学徒培训招生,wordpress 多数据库------------吾亦无他,唯手熟尔#xff0c;谦卑若愚#xff0c;好学若饥------------- 本篇博客记录了表分区 表分区的含义#xff1a; 典型的拿空间换时间的案例#xff01; 表分区对一张表进行分区#xff0c;分区之后表中的数据存在相对应的分区内#xff08;可以是不… ------------吾亦无他,唯手熟尔谦卑若愚好学若饥------------- 本篇博客记录了表分区 表分区的含义 典型的拿空间换时间的案例 表分区对一张表进行分区分区之后表中的数据存在相对应的分区内可以是不同物理存储的位置实际上他还是一张表 表分区的好处 均衡了IO可以放在多个不同磁盘的位置 提升了查询性能可以不必检索所有记录 维护方便假如数据出现问题分区之后只需维护出现问题的那个分区 增强了可用性假如一个表分区挂掉了其他分区可以继续使用而不是全挂掉 表分区的缺点 假如数据量太小就无需使用表分区否者浪费性能 提供数据库脚本供测试使用合起来了以免影响阅读 create table DEPT
(deptno NUMBER(2) not null,dname VARCHAR2(20),loc VARCHAR2(13)
);alter table DEPTadd constraint PK_DEPT primary key (DEPTNO);create table TEACHER
(tno NUMBER(4) not null,tname VARCHAR2(30) not null,tid CHAR(18),gendar CHAR(3),birthdate DATE,job VARCHAR2(20),hiredate DATE,sal NUMBER(7,2),deptno NUMBER(2),mgrno NUMBER(4),comm NUMBER(7,2)
);insert into DEPT (deptno, dname, loc)
values (40, 人力部, 北京海淀);
insert into DEPT (deptno, dname, loc)
values (30, 产品部, 北京海淀);
insert into DEPT (deptno, dname, loc)
values (20, 招生部, 上海虹桥);
insert into DEPT (deptno, dname, loc)
values (10, 研究院, 北京海淀);
insert into DEPT (deptno, dname, loc)
values (50, 系统部, 北京昌平);
commit;insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);insert into TEACHER (TNO, TNAME, TID, GENDAR, BIRTHDATE, JOB, HIREDATE, SAL, DEPTNO, MGRNO, COMM)
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);commit; 数据库脚本 --创建表分区根据某一列range用范围限定范围分区 概念 表空间的创建 操作代码 create table TEACHER(tno NUMBER(4) not null,tname VARCHAR2(30) not null,tid CHAR(18),gendar CHAR(3),birthdate DATE,job VARCHAR2(20),hiredate DATE,sal NUMBER(7,2),deptno NUMBER(2),mgrno NUMBER(4),comm NUMBER(7,2))partition by range(tno)(partition part1 values less than(1030) tablespace partition1,partition part2 values less than(1080) tablespace partition2,partition part3 values less than(maxvalue) tablespace partition3); 查询表分区 --查询对应表分区下的数据 select * from teacher partition(part3) --查询所有表分区 select * from all_tab_partitions --查询子分区和分区组合分区中用的 select * from teacher partition(EMPLOYEE_DEPTNO_10)select * from teacher SUBPARTITION(EMPLOYEE_10_JOB_MAGAGER) 删除表分区 --1.删除表分区包含分区中的所有数据 alter table teacher drop partition part1;--查一下有没有select * from teacher partition(part1)select * from teacherselect * from all_tab_partitions--继续删alter table teacher drop partition part2;alter table teacher drop partition part3;--报错所以更换另外一种手段 --2.删除表以及它的表分区到回收站 drop table teacher purge;--查一下select * from all_tab_partitions --3.清空回收站以防止有同名表分区 purge table teacher 创建表分区list根据列表男或者女中国省份这种定值 操作 create table TEACHER(tno NUMBER(4) not null,tname VARCHAR2(30) not null,tid CHAR(18),gendar CHAR(3),birthdate DATE,job VARCHAR2(20),hiredate DATE,sal NUMBER(7,2),deptno NUMBER(2),mgrno NUMBER(4),comm NUMBER(7,2))partition by list(gendar)(partition part1 values (男) tablespace partition1,partition part2 values (女) tablespace partition2); 创建分区Hash必须是2的n次方的分区数量 操作 create table TEACHER(tno NUMBER(4) not null,tname VARCHAR2(30) not null,tid CHAR(18),gendar CHAR(3),birthdate DATE,job VARCHAR2(20),hiredate DATE,sal NUMBER(7,2),deptno NUMBER(2),mgrno NUMBER(4),comm NUMBER(7,2))partition by hash(tname)(partition part1 tablespace partition1,partition part2 tablespace partition2); 组合分区 可以使用上面三种中的任意俩种或者任意一种使用俩次进行组合嵌套 对于同一张组合分区表而言子分区的名字不能相同即使不在相同的分区里 操作 create table TEACHER(tno NUMBER(4) not null,tname VARCHAR2(30) not null,tid CHAR(18),gendar CHAR(3),birthdate DATE,job VARCHAR2(20),hiredate DATE,sal NUMBER(7,2),deptno NUMBER(2),mgrno NUMBER(4),comm NUMBER(7,2))PARTITION BY LIST (DEPTNO)SUBPARTITION BY LIST (JOB)(PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) TABLESPACE partition1( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES (考试专员),SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)),PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) TABLESPACE partition2( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES (考试专员),SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)),PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) TABLESPACE partition3( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES (考试专员),SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT))); --查询子分区和分区组合分区中用的 select * from teacher partition(EMPLOYEE_DEPTNO_10)select * from teacher SUBPARTITION(EMPLOYEE_10_JOB_MAGAGER)
