Oracle创建scott用户导入数据查看emp表

• 写在前面，这里我使用的是database19c， 而从12c开始oracle就不再提供已经创建好的scott用户，所以我们需要自行创建来测试。

流程：

• 过程十分简单，大概两三分钟就能做好

打开cmd,输入

sqlplus sys/orcl as sysdba;

此时就已管理员身份进入了Oracle命令行形式的管理工具SQL plus。创建c##scott用户，输入

create user c##scott identified by tiger;

（这里之所以是c##是Oracle 12C引入了CDB与PDB的新特性，大家感兴趣可以自行跳转 , 创建用户时为什么要在用户名前加C##? 以及 Oracle12c创建用户不加C##）

授权给c##

grant connect,resource,unlimited tablespace to c##scott container=all;

设置表空间

alter user c##scott default tablespace users;
alter user c##scott temporary tablespace temp;

scott用户登录

connect c##scott/tiger;

删除之前的可能存在的表

drop table emp purge;
drop table dept purge;
drop table bonus purge;
drop table salgrade purge;

创建新的数据库并添加数据

-- 创建表
-- 这里需要注意一下建表的顺序
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
	
-- 插入数据
-- dept表
INSERT INTO DEPT VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES(40,'OPERATIONS','BOSTON');
-- emp表
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','CLERK',7566,to_date('19-4-87','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('23-5-87','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
-- values表
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

事务提交

commit;

查看emp表中的数据

select * from emp;

• 写在最后，这是在新的oracle版本中建立了scott用户，对应11g版本的小伙伴，直接用系统高级用户为Scott用户添加权限，就能查看对应表和试图了。如下：

  Sqlplus sys/orcl as sysdba;
  conn sys/123456 as sysdba;
  alter user scott account unlock;
  grant select on scott.emp to scott;
  /*conn scott/tiger;
  更改scott的口令
  新口令:123
  重新键入新口令:123*/
  conn scott/123;
  select * from scott.emp;
  

  • 本文参考 这位大佬