网站设计制作的服务机构,大连在建项目,电商运营推广方案,推广计划pptOracle系列文章目录
第一章 批量创建表并插入测试数据 文章目录 Oracle系列文章目录前言一、创建表和索引二、向表中加入数据总结 前言
使用数据库#xff0c;首先要向数据库中加入大量数据#xff0c;本篇文章提供了一些测试数据 一、创建表和索引
-- 创建数据库和索引
-…Oracle系列文章目录
第一章 批量创建表并插入测试数据 文章目录 Oracle系列文章目录前言一、创建表和索引二、向表中加入数据总结 前言
使用数据库首先要向数据库中加入大量数据本篇文章提供了一些测试数据 一、创建表和索引
-- 创建数据库和索引
-- 创建 3 个示例表
CREATE TABLE department( dept_id INTEGER NOT NULL PRIMARY KEY, dept_name VARCHAR(50) NOT NULL) ;
CREATE TABLE job( job_id INTEGER NOT NULL PRIMARY KEY, job_title VARCHAR(50) NOT NULL) ;
CREATE TABLE employee( emp_id INTEGER NOT NULL PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, sex VARCHAR(10) NOT NULL, dept_id INTEGER NOT NULL, manager INTEGER, hire_date DATE NOT NULL, job_id INTEGER NOT NULL, salary NUMERIC(8,2) NOT NULL, bonus NUMERIC(8,2), email VARCHAR(100) NOT NULL, CONSTRAINT ck_emp_sex CHECK (sex IN (男, 女)), CONSTRAINT ck_emp_salary CHECK (salary 0), CONSTRAINT uk_emp_email UNIQUE (email), CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id), CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id), CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)) ;
CREATE INDEX idx_emp_name ON employee(emp_name);
CREATE INDEX idx_emp_dept ON employee(dept_id);
CREATE INDEX idx_emp_job ON employee(job_id);
CREATE INDEX idx_emp_manager ON employee(manager);二、向表中加入数据
INSERT INTO department(dept_id, dept_name) VALUES (1, 行政管理部);
INSERT INTO department(dept_id, dept_name) VALUES (2, 人力资源部);
INSERT INTO department(dept_id, dept_name) VALUES (3, 财务部);
INSERT INTO department(dept_id, dept_name) VALUES (4, 研发部);
INSERT INTO department(dept_id, dept_name) VALUES (5, 销售部);
INSERT INTO department(dept_id, dept_name) VALUES (6, 保卫部);INSERT INTO job(job_id, job_title) VALUES (1, 总经理);
INSERT INTO job(job_id, job_title) VALUES (2, 副总经理);
INSERT INTO job(job_id, job_title) VALUES (3, 人力资源总监);
INSERT INTO job(job_id, job_title) VALUES (4, 人力资源专员);
INSERT INTO job(job_id, job_title) VALUES (5, 财务经理);
INSERT INTO job(job_id, job_title) VALUES (6, 会计);
INSERT INTO job(job_id, job_title) VALUES (7, 开发经理);
INSERT INTO job(job_id, job_title) VALUES (8, 程序员);
INSERT INTO job(job_id, job_title) VALUES (9, 销售经理);
INSERT INTO job(job_id, job_title) VALUES (10, 销售人员);INSERT INTO employee VALUES (1, 刘备, 男, 1, NULL, TO_DATE(2000-01-01, YYYY-MM-DD), 1, 30000, 10000, liubeishuguo.com);
INSERT INTO employee VALUES (2, 关羽, 男, 1, 1, TO_DATE(2000-01-01, YYYY-MM-DD), 2, 26000, 10000, guanyushuguo.com);
INSERT INTO employee VALUES (3, 张飞, 男, 1, 1, TO_DATE(2000-01-01, YYYY-MM-DD), 2, 24000, 10000, zhangfeishuguo.com);
INSERT INTO employee VALUES (4, 诸葛亮, 男, 2, 1, TO_DATE(2006-03-15, YYYY-MM-DD), 3, 24000, 8000, zhugeliangshuguo.com);
INSERT INTO employee VALUES (5, 黄忠, 男, 2, 4, TO_DATE(2008-10-25, YYYY-MM-DD), 4, 8000, NULL, huangzhongshuguo.com);
INSERT INTO employee VALUES (6, 魏延, 男, 2, 4, TO_DATE(2007-04-01, YYYY-MM-DD), 4, 7500, NULL, weiyanshuguo.com);
INSERT INTO employee VALUES (7, 孙尚香, 女, 3, 1, TO_DATE(2002-08-08, YYYY-MM-DD), 5, 12000, 5000, sunshangxiangshuguo.com);
INSERT INTO employee VALUES (8, 孙丫鬟, 女, 3, 7, TO_DATE(2002-08-08, YYYY-MM-DD), 6, 6000, NULL, sunyahuanshuguo.com);
INSERT INTO employee VALUES (9, 赵云, 男, 4, 1, TO_DATE(2005-12-19, YYYY-MM-DD), 7, 15000, 6000, zhaoyunshuguo.com);
INSERT INTO employee VALUES (10, 廖化, 男, 4, 9, TO_DATE(2009-02-17, YYYY-MM-DD), 8, 6500, NULL, liaohuashuguo.com);
INSERT INTO employee VALUES (11, 关平, 男, 4, 9, TO_DATE(2011-07-24, YYYY-MM-DD), 8, 6800, NULL, guanpingshuguo.com);
INSERT INTO employee VALUES (12, 赵氏, 女, 4, 9, TO_DATE(2011-11-10, YYYY-MM-DD), 8, 6600, NULL, zhaoshishuguo.com);
INSERT INTO employee VALUES (13, 关兴, 男, 4, 9, TO_DATE(2011-07-30, YYYY-MM-DD), 8, 7000, NULL, guanxingshuguo.com);
INSERT INTO employee VALUES (14, 张苞, 男, 4, 9, TO_DATE(2012-05-31, YYYY-MM-DD), 8, 6500, NULL, zhangbaoshuguo.com);
INSERT INTO employee VALUES (15, 赵统, 男, 4, 9, TO_DATE(2012-05-03, YYYY-MM-DD), 8, 6000, NULL, zhaotongshuguo.com);
INSERT INTO employee VALUES (16, 周仓, 男, 4, 9, TO_DATE(2010-02-20, YYYY-MM-DD), 8, 8000, NULL, zhoucangshuguo.com);
INSERT INTO employee VALUES (17, 马岱, 男, 4, 9, TO_DATE(2014-09-16, YYYY-MM-DD), 8, 5800, NULL, madaishuguo.com);
INSERT INTO employee VALUES (18, 法正, 男, 5, 2, TO_DATE(2017-04-09, YYYY-MM-DD), 9, 10000, 5000, fazhengshuguo.com);
INSERT INTO employee VALUES (19, 庞统, 男, 5, 18, TO_DATE(2017-06-06, YYYY-MM-DD), 10, 4100, 2000, pangtongshuguo.com);
INSERT INTO employee VALUES (20, 蒋琬, 男, 5, 18, TO_DATE(2018-01-28, YYYY-MM-DD), 10, 4000, 1500, jiangwanshuguo.com);
INSERT INTO employee VALUES (21, 黄权, 男, 5, 18, TO_DATE(2018-03-14, YYYY-MM-DD), 10, 4200, NULL, huangquanshuguo.com);
INSERT INTO employee VALUES (22, 糜竺, 男, 5, 18, TO_DATE(2018-03-27, YYYY-MM-DD), 10, 4300, NULL, mizhushuguo.com);
INSERT INTO employee VALUES (23, 邓芝, 男, 5, 18, TO_DATE(2018-11-11, YYYY-MM-DD), 10, 4000, NULL, dengzhishuguo.com);
INSERT INTO employee VALUES (24, 简雍, 男, 5, 18, TO_DATE(2019-05-11, YYYY-MM-DD), 10, 4800, NULL, jianyongshuguo.com);
INSERT INTO employee VALUES (25, 孙乾, 男, 5, 18, TO_DATE(2018-10-09, YYYY-MM-DD), 10, 4700, NULL, sunqianshuguo.com);总结
这些数据导入完成后可以进行增删改查一系列操作的测试了不用自己再费劲造数据集合了因为自己找测试数据是很麻烦的一件事是可以进行三表联查分组查询排序等一系列操作
