Oracle 创建表空间、创建用户、DDL、DML 语句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 创建表空间、创建用户、DDL、DML 语句,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含9710字,纯文字阅读大概需要14分钟。
内容图文
![Oracle 创建表空间、创建用户、DDL、DML 语句](/upload/InfoBanner/zyjiaocheng/910/07e28116764043679e7667bbb9a803fb.jpg)
目录
Oracle SQL 语句由如下命令组成:
数据定义语言( DDL ):包括 CREATE (创建)命令、 ALTER (修改)命令、 DROP (删除)命令等。
数据操纵语言( DML ):包括 INSERT (插入)命令、 UPDATE (更新)命令、 DELETE(删除)命令、 SELECT FOR UPDATE (查询)等。
数据查询语言( DQL ):包括基本查询语句、 Order By 子句、 Group By 子句等。
事务控制语言( TCL ):包括 COMMIT (提交)命令、 SAVEPOINT (保存点)命令、ROLL BACK (回滚)命令。
数据控制语言( DCL): GRANT (授权)命令、 REVOKE (撤销)命令。
创建表空间
1、Oralce 数据库通常只有一个数据库实例 orcl,新建 Java Web 项目后,需要先新建表空间,然后创建用户并分配表空间,接着授权给用户,最后由此用户来建表操作。
2、创建表空间语法:
create tablespace 表空间名 datafile '文件在服务器山的路径' size 表空间初始大小 autoextend on next 每次扩展的大小;
(autoextend on:表示超出初始大小后自动进行扩展)
3、以系统用户 system 登录后,开始创建表空间 "dongShan":
create tablespace dongShan
datafile 'E:\oracle_tablesapce\dongShan.dbf'
size 500M
autoextend on
next 50M;
4、上面的语句执行之后,在 oralce 服务器的指定位置就会自动生成 dbf 文件,且大小为初始大小500M。
5、默认情况下表空间(dbf文件)直接右键删除是删除不了的,因为它被 Oracle 占用了,如果真的不再需要,可以先删除表空间:drop tablespace 表空间名
drop tablespace xxx 删除的是 Oralce 中维护的表空间关系,删除之后 dbf 文件只是一个普通的文件,所以可以手动删除。
创建用户
1、Oralce 数据库通常只有一个数据库实例 orcl,新建 Java Web 项目后,需要先新建表空间,然后创建用户并分配表空间,接着授权给用户,最后由此用户来建表操作。
2、创建用户语法:create user 用户名 identified by 密码 default tablespace 表空间名;
--创建用户。Oracle 12c 创建用户的时候用户名必须以 c## 或者 C## 开头
--oracle12c 创建用户不用 C## 开头时,会报错:Oracle-65096:公用用户名或角色无效
create user c##wmx identified by 123456 default tablespace dongShan;
用户授权
1、上面创建的用户 wmx 暂时只是普通用户,没有任何权限,无法登陆 oralce,接下来需要为它分配角色/授权。
2、Oracle 中有3个重要的角色:connect、resource、dba:
角色 | 权限 |
connect |
ALTER SESSION--修改会话 CREATE CLUSTER--建立聚簇 CREATE DATABASE LINK--建立数据库链接 CREATE SEQUENCE--建立序列 CREATE SESSION--建立会话 CREATE SYNONYM--建立同义词 CREATE VIEW--建立视图 --是授予最终用户的典型权利,是最基本的 |
resource |
--在上面的基础上又加了 CREATE PROCEDERE--建立过程 CREATE TABLE--建表 CREATE TRIGGER--建立触发器 CREATE TYPE--建立类型 --是授予开发人员的 |
dba | 拥有全部权限,是系统最高权限。只有 dba 才可以创建数据库结构,系统权限也只能由 dba 授出,dba 可以操作全体用户(包括其它dba)的任意表,包括删除表。 |
3、授权语法:grant 角色|权限 to 用户名
grant connect to c##wmx;--授予 connect 角色给 c##wmx 用户
grant resource to c##wmx;--授予 resource 角色给 c##wmx 用户
grant dba to c##wmx;--授予 dba 角色给 c##wmx 用户
4、授权操作必须是 dba 操作,授权之后 c##wmx 用户则可以连接数据库进行操作了。
Oracle 数据类型
1、在学习建表、操作表之前,应该先清楚 Oralce 的数据类型。Oracle 数据库的核心是表,表中的列使用到的常见数据类型如下:
类型 | 描述 |
CHAR(length) | 存储 固定长度 的字符串。参数 length 指定了长度,如 果存储的字符串长度小于 length ,用空格填充。默认长度是 1 ,最长不超过 2000 字节。 |
VARCHAR2(length) | 存储可变长度的字符串。length 指定了该字符串的最大长度。默认长度是 1 ,最长不超过 4000 字符。 |
NUMBER(p,s) | 既可以存储浮点数,也可以存储整数。p 表示数字的最大位数(如果是小数,则包括整数部分和小数部分和小数点, p 默认是 38 位), s 是指 小数位数。小数位不能大于总数位。 |
DATE | 存储日期和时间,存储纪元、 4 位年、月、日、时、分、秒,存储时间。从公元前4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。 |
TIMESTAMP | 不但存储日期的年月日,时分秒,以及秒后6 位 ,同时包含时区。 |
CLOB | 存储大的文本,比如存储非结构化的XML 文档 |
BLOB | 存储二进制对象,如图形、视频、声音等。 |
NUMBER 类型的示例:
创建表
1、建表语法:create table 表名( 列名 数据类型 [约束],列名 数据类型 [约束],...);
--创建学生表
CREATE TABLE STUDENT (
STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号
STUNAME VARCHAR2(10) NOT NULL, --姓名
GENDER VARCHAR2(2) NOT NULL, --性别
AGE NUMBER(2) NOT NULL, --年龄
SEAT NUMBER(2) NOT NULL, --座号
ENROLLDATE DATE, --入学时间
STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址
CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号
);
2、desc 用于查看表的结构,它不属于 sql ,所以需要到命令窗口中操作。
3、更多建表与约束操作可以参考下面的“五大约束”部分。
子查询复制表
1、如果对于一张以及存在的表,现在想要复制它,则可以使用子查询复制表来实现。
2、语法:create table 表名 as 查询语句
3、如果子查询有结果,则复制表结构的同时也会复制表中的数据,如果子查询没有结果,则只复制表结构
--通过已经存在的 STUDENT 表复制一张新表 person
CREATE TABLE PERSON AS SELECT * FROM STUDENT;
修改表结构 & 删除表
1、通常有:添加列、修改列、删除列、修改列名、修改表名..
select * from person;--查询 person 表
alter table person add phone varchar2(11);--添加 phone 列
alter table person add ( --同时添加多列
fatherName varchar2(16) not null,
fatherPhone varchar2(11)
);
alter table person drop column fatherName;--删除列
alter table person drop column fatherPhone;--删除列
alter table person modify fatherName varchar2(12);--修改列的数据类型
alter table person rename column gender to sex;--修改列名
rename person to person_1;--修改表名
rename person_1 to person;--修改表名
drop table person_1;--删除表
五大约束
1、表的约束目的是为了保持数据的完整性,约束表中数据的规则。主要有:主键约束、非空约束、唯一约束、检查约束、外键约束。
2、主键约束:主键(primary key)不能为空,且必须唯一
3、如下所示建表的同时设置约束:
--建表的同时创建约束
create table student_1(
stuid number(32) primary key,--主键
stuname varchar2(16) unique,--唯一约束
age varchar2(4) not null,--非空约束
gender varchar2(8) check (gender in ('男','女','保密'))
);
insert into student_1 values(1,'张三',22,'男');--插入数据,必须符合约束
insert into student_1 values(2,'李四',22,'女');--插入数据,必须符合约束
select * from student_1;--查询
4、如下所示建表后设置约束:
--创建学生表
CREATE TABLE STUDENT (
STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号
STUNAME VARCHAR2(10) NOT NULL, --姓名
GENDER VARCHAR2(2) NOT NULL, --性别
AGE NUMBER(2) NOT NULL, --年龄
SEAT NUMBER(2) NOT NULL, --座号
ENROLLDATE DATE, --入学时间
STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址
CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号
);
--添加主键约束。constraint 关键字英文意思为约束、限制,PK_INFOS 为约束名称(自定义),primary key 约束类型
ALTER TABLE STUDENT ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID);
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女');--添加检查约束,性别只能是男或者女
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50);--添加检查约束,坐位号只能在 [0,50] 之间
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100);--与上同理
--添加检查约束,限制班号范围
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_CLASSNO CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999'))
ALTER TABLE STUDENT ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME);--添加唯一约束,姓名不能重复
5、外键约束操作如下:
--创建商品类型表。商品类型与商品 1 对多 关系,商品依赖类型表,类型表为主表,商品为从表
create table productType(
pid number(32) primary key,
pname varchar2(16) not null
);
--创建商品表。建表的同时设置外键
create table product(
pid number(32) primary key,
pname varchar2(16) not null,
price number(8,2) not null,--价格
ptId number(32) ,--外键
foreign key(ptId) references productType(pid)--设置外键,关联类型表的主键
);
--如果建表的时候没有设置外键,则修改表,为其添加外键。默认情况下,当主表记录被从表依赖时,主表记录无法删除,除非先删除依赖项
alter table product add constraints fk_ptId foreign key(ptId) references productType(pid);
--添加外键的同时设置级联删除。即主表删除记录时,会自动搜索,如果有其它记录依赖自己,则先删除依赖项,然后删除自己。
alter table product add constraints fk_ptId foreign key(ptId) references productType(pid) on delete cascade;
--插入数据
insert into productType values(1,'IT');
insert into productType values(2,'书籍');
insert into productType values(3,'美食');
select * from productType;
insert into product values(1,'鹿鼎记',35.55,2);
insert into product values(2,'蚂蚁上树',66.55,3);
select * from product;
delete from productType where pid =1;--正常删除,因为 pid =1 没有被任何记录依赖
--如果依赖自己的外键没有设置级联删除,则这里无法删除自己,报错:违反完整约束条件,已找到子记录。除非先删除所有依赖的子记录,否则只能强制删除(不建议)
--如果依赖自己的外键设置了级联删除,则会一并删除
delete from productType where pid =2 or pid = 3;
插入/更新/删除数据
insert into 表名 values(value1,value2,value3,...);--表中所有列按顺序给出所有值
insert into 表名(列1,列2,列3...) values(value1,value2,value3,...);--为部分列添加值
insert into 表名 查询语句;--使用子查询插入数据
--当前使用的是拥有管理员权限的用户 wmx 登录,管理员可以查询/操作任何用户的数据,包括其它管理员
select * from dept;
select * from system.dept;--查询系统用户 system 的部门表
insert into dept select * from system.dept;--子查询插入。将系统用户的部门表数据全部插入到 wmx 用户的部门表中
insert into dept(deptno,dname) values(50,'财务部');--为部分列添加数据
insert into dept values(60,'开发部','深圳');--为所有列添加值
select * from dept;
--更新数据格式:update 表名 set 列名1=value,列名2=value,... [where 条件];
update dept set dname='测试部',loc='长沙' where deptno = 50;--如果不加 where 条件,则整个表的 dname 都会被更新
update dept set dname='人事部' where deptno = 60;
--delete 删除数据:delete from 表名 [where 条件]; delete 是逐条删除,支持事务操作
--truncate 删除删除:truncate 先删除整个表,然后再创建表结构,不支持事务操作,如果是删除全部数据,效率会高些
delete from dept where deptno = 60 or deptno = 50;
truncate table dept;--删除表中所有数据
内容总结
以上是互联网集市为您收集整理的Oracle 创建表空间、创建用户、DDL、DML 语句全部内容,希望文章能够帮你解决Oracle 创建表空间、创建用户、DDL、DML 语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。