一、创建数据库移步之前的文章:

Oracle 19c 创建数据库(详细明了)

二、创建表空间等:

1、创建表空间stu_data,100m,自动扩展表空间

create tablespace stu_data datafile 'D:\oracle\oradata\STUDENT\stu_data.dbf' size 100mAUTOEXTEND on next 2m logging

2、查看表空间

select tablespace_name,contents from dba_tablespaces

3、创建用户studba,口令:student2020,指定默认表空间stu_data,临时表空间temp,具有创建表,创建视图,创建序列,创建对话,对表空间大小不加限制权限,和授予用户最典型、开发人员和系统管理角色

create user studba IDENTIFIED BY studba2020 default TABLESPACE stu_dataTEMPORARY TABLESPACE temp; GRANT create table,create view,create sequence,create session,unlimited tablespace TO studba;GRANT connect TO studba;GRANT resource TO studba;GRANT dba TO studba;

4、使用studba用户创建用户表(包括主键、外键),存放在stu_data表空间

drop table usertab purge;create table usertab (utid number(6) generated by default as identity(start with 1 increment by 1),uname varchar2(20) not null,upwd varchar2(50) not null,uright char(1) default '2',constraint pk_ytid primary key(utid))TABLESPACE stu_datastorage(initial 256k)--数据区初始大小为256KPCTFREE 5 --空闲空间的比例为5%pctused 60 --表示该数据块中有60%的空间可用于插入新数据initrans 10;--设置当前表使用用户的并发数量

查看表名

select table_name,tablespace_name from user_tables

5、使用studba用户创建其他数据表(包括主键,外键),存放在stu_data表空间;

dorp table student_tab purge;create table student_tab(xh varchar2(11) not null primary key,xm char(8) not null,xb char(4) default'男',cssj date not null,zy char(12),zxf number(2) default 0,bz varchar2(200))tablespace stu_datastorage(initial 256k)pctfree 5pctused 60initrans 10;

6、插入数据,每张表10条数据以上

insert into usertab(Uname,Upwd.Uright) values('a','122343','2');insert into usertab(Uname,Upwd,Uright) values('b','12ed44','2');insert into usertab(Uname,Upwd,Uright) values('c','3278js','2');insert into usertab(Uname,Upwd,Uright) values('d','123456','1');insert into usertab(Uname,Upwd,Uright) values('e','shwujwe','2');insert into usertab(Uname,Upwd,Uright) values('f','3isjss','1');insert into usertab(Uname,Upwd,Uright) values('g','123456','2');insert into usertab(Uname,Upwd,Uright) values('h','hwu291i','1');insert into usertab(Uname,Upwd,Uright) values('i','123456','2');insert into usertab(Uname,Upwd,Uright) values('j','773628','2');insert into usertab(Uname,Upwd,Uright) values('k','123456','2');insert into usertab(Uname,Upwd,Uright) values('l','123456','1');

查看usertab表信息,命令如下:

select * from usertab

对student_tab表插入12条数据,命令如下:

insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120107','hhq','女',TO_DATE('2002-05-18','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120104','hzt','女',TO_DATE('2001-12-27','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120101','zlw','女',TO_DATE('2000-10-24','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120102','zdh','女',TO_DATE('2002-07-18','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120103','lqq','女',TO_DATE('2002-05-21','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120105','ccf','女',TO_DATE('2003-01-01','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120106','lst','女',TO_DATE('2002-02-17','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120108','zs','男',TO_DATE('2002-10-18','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120207','ls','男',TO_DATE('2001-10-10','yyyy-mm-dd'),'信息20-2',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120209','ww','男',TO_DATE('2002-12-18','yyyy-mm-dd'),'信息20-1',50,'无');insert into student_tab(xh,xm,xb,cssj,zy,zxf,bz) values('20124120210','jaa','女',TO_DATE('2000-02-18','yyyy-mm-dd'),'信息20-2',50,'无');

觉得不错的话点个赞叭~