oracle数据库创建表

实际工作中，在数据库中创建表是经常会用到的。我们今天呢？主要给大家来分享一下在数据库如何通过sql语句去创建表。其实，创建表很简单，只需要把数据库的数据类型和约束搞清楚就可以了，其他的就好说了。接下来呢，开始我的表演。首先，先使用plsql连接到oracle数据库，先保证下面的服务是开启的。



 

我们本次创建表的需求是：创建一张班级表，和一张学生表。

1.首先班级表作为主表也就是所谓的主键。在主表中我们这里使用的约束是primarykey 和not null  (当然不局限于这些)

create table classinfo(
       classid number(2) primary key,
       classname varchar(10) not null       
       );

sql解析：

--create table 创建表的关键字 

--classinfo    是创建的表的名字

--classid       是班级表的id 数据类型是number(2)类型，我们默认给了2个长度，我们将班级id设置为主键方便其他外键关联

--classname 是班级名字 数据类型是字符型varchar(10)，我们给了默认10个字符长度，班级名的约束是不能为空

执行sql语句：



 classinfo表创建成功。



 

2.然后我们建立一个外键，也就是关联到主键的一个表，使用的数据类型和约束请看下面的sql语句。

create table studentinfo(
       studentid number(2) primary key,
       studentname varchar(10) not null,
       studentsex char(2) check(studentsex='男' or studentsex='女'),
       studentage number(2) not null,
       studenttel number(11) unique,
       studentaddress varchar(50) default '上海',
       classid number(2) references classinfo(classid)
       );


 

 sql语句解析：

--create table 创建表的关键字

--studentinfo();是创建学生信息表的表名

--studentid（学生id）          约束是主键 primary key

--studentname(学生姓名)    约束是  not  null  

--studentsex（学生性别）   约束是 check 

--studentage（学生年龄）   约束是 not null 

--studenttel(学生电话)          约束是 unique

--studentaddress（学生地址）      分别为学生表中的列名。

 学生表studentinfo建立完成。



 

 完整的sql语句如下：

create table classinfo(
       classid number(2) primary key,
       classname varchar(10) not null       
       );
       
create table studentinfo(
       studentid number(2) primary key,
       studentname varchar(10) not null,
       studentsex char(2) check(studentsex='男' or studentsex='女'),
       studentage number(2) not null,
       studenttel number(11) unique,
       studentaddress varchar(50) default '上海',
       classid number(2) references classinfo(classid)
       );

 到此，我们创建的班级表和学生表就演示完了，是不是很简单呢？

 

                                                                  欢迎关注公众号，和我一起成长！

                                                         

普通创建表

create table C_table(

deptno number(2),

dname varchar2(14),

loc varchar2(13)

);

注释：Orecle创建表的语法使用create table 自定义命名，括号自定义命名字段，添加数据类型并且给予长度没有约束，使用sql plus命令》desc 自定义表查询表结构！！！


子查询创建表

create table A_table

as

select employee_id, last_name,

salary*12 ANNSAL,hire_date

from employees

where department_id = 80;

注释：Orecle创建表的语法使用sql DDL命令create table 自定义命名，as连接一个查询方法条件部门ID=80，获取部门ID80的所有结构字段数据都存放复制到A_table的表中，使用sql plus命令》desc 自定义表查询表结构！！！




添加表的新列

alter table A_table

add (job_id varchar2(9));

注释：添加一个表的新列中使用到Orecle sql DLL命令alter table 自定义命名，换行add新增关键字，然后括号一个自定义名称并且添加Orecle sql的数据类型长度为9，没有约束，使用sql plus命令》desc 自定义表查询表结构！！！



Update表列

ALTER TABLE	A_table

MODIFY		(job_id VARCHAR2(30));

注释：Update（修改）表列名称与数据类型使用到Orecle sql DLL命令alter table 自定义命名，换行MODIFY修改关键字，然后括号一个自定义名称并且添加Orecle sql的可变长字符串数据类型长度为30，没有约束，使用sql plus命令》desc 自定义表查询表结构！！！



Delete表列



alter table A_table

drop column job_id;

注释：Delete（删除）表列名称使用到Orecle sql DLL命令alter table 自定义命名，换行（drop column）删除列的关键字，drop删除 指定列，以上是原字段，drop字段后的数据！！！



重命名表列

alter table  A_table

rename column  job_id TO J_O_B_id;

注释：重命名一个的新列中使用到Orecle sql DLL命令alter table 自定义命名，换行（rename column）重命名关键字，获取表中列名的对象进行to自定义name！！！


改变对象名称

rename A_table to T_table;

注释：改变对象名称使用到Orecle sql DLL命令rename，获取数据表对象1进行to改变成数据库表对象2，执行rename语句改变表、视图、序列、同义词的名称，必须是对象的拥有者！！！

1 表空间
1.1 创建表空间
create tablespace tablespace_name logging datafile 'D:\app\tablespace\tablespace_name.dbf'
size 1024m autoextend on next 20M extent management local;


名称
描述
datafile
后‘’符号中对应的是要修改表空间的路径，例如：‘D:\app\tablespace\tablespace_name.dbf’
size
后的值为表空间的大小
tablespace_name
值为表空间的名称
autoextend
后的值为是否要自增长表空间 off为否 on为是
next
后的值为每次增长的大小
1.1.1 修改表空间自增长
如果在创建的时候已经设置了表空间自增，则不需求二次修改了
alter database datafile 'D:\app\tablespace\tablespace_name.dbf' AUTOEXTEND ON NEXT 200M;


名称
描述
datafile
后‘’符号中对应的是要修改表空间的路径，例如：‘D:\app\tablespace\tablespace_name.dbf’
autoextend
后的值为是否要自增长表空间 off为否 on为是
next
后的值为每次增长的大小
1.1.2 查询表空间剩余大小
select a.tablespace_name,
a.bytes/(1024*1024*1024) total,
b.bytes/(1024*1024*1024) used,
c.bytes/(1024*1024*1024) free,
(b.bytes*100)/a.bytes "%used",
(c.bytes*100)/a.bytes "%free"
from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c
where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name


名称
描述
total
表空间总大小
used
表空间使用大小
free
表空间剩余大小
%used
表空间使用百分比
%free
表空间剩余百分比
2 用户
2.1 创建用户
create user user_name identified by user_password default tablespace tablespace_name;


名称
描述
user_name
值为用户的名称
user_password
值为用户的密码
tablespace_name
默认表空间名称
2.1.1 配置用户的默认表空间
如果在创建的时候已经设置了默认表空间，则不需求二次修改了
alter user user_name default tablespace tablespace_name;


名称
描述
user_name
值为用户名称
tablespace_name
值为表空间的名称
注：默认表空间一个用户只能有一个
2.2 配置用户权限
grant connect,resource,dba to user_name;
grant connect to user_name with admin option;


名称
描述
connect
为登录、连接权限
dba
为创建数据结构权限
resource
创建实体权限
3 查询
3.1 基础查询
查询所有的表空间
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

查询所有用户
SELECT * FROM DBA_USERS;

查询能管理的所有用户
SELECT * FROM ALL_USERS;

查询当前用户信息
SELECT * FROM USER_USERS;

查找表结构
select * from user_tab_columns where Table_Name='tableName';  --当前用户下的表的信息

select * from all_tab_columns where Table_Name='tableName';   --所有表的信息

select * from dba_tab_columns where Table_Name='tableName';   --dba下的表的信息

形成批量修改表字段类型的sql
SELECT
	column_name,
	'ALTER table tableName modify ' || column_name || ' NVARCHAR2(' || char_length || ')' 
FROM
	all_tab_columns 
WHERE
	Table_Name = 'tableName' 
	AND data_type = 'NVARCHAR2';

4 导出导出数据
4.1 执行环境
exp/imp可以在客户端执行也可以在服务器端执行，在客户端执行需要先安装有oracle的客户端,如果是linux系统，就是以oracle用户登录，在控制台下执行。建议在服务器端执行exp，备份速度快。
4.2 如果运行的时候提示没有exp命令则进入到oracle目录
cd /u01/app/oracle/product/11.2.0/dbhome_1/bin

4.3 配置临时环境变量
export ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1/"
export ORACLE_SID=glassfish

4.4 执行导出
4.4.1 切换到oracle用户
su oracle

4.4.2 导出文件
4.4.2.1 默认导出
导出的文件是带有数据的，如果表中没有数据则不会导出（oracle 11g的机制如果没有数据则不分配segment）此操作在系统层级下执行即可
exp userName/pwd@orcl file="/home/backup/tablespaceName.dmp"


名称
描述
userName
用户名称
pwd
密码
orcl
实例名
file
导出的目录，可以新建确保导出的目录权限够用
tables
需要导出的表，如果要导出全部的表则可以不写
例如 tables=tb_test,tb_user
STATISTICS
统计对象，如果exp导出的时候出现EXP-00091: Exporting questionable statistics.错误可以把此参数设置为no
例如 statistics=no
4.4.2.2 导出带有空数据的表
批量处理空表，此方法可以处理所有的空表支持导出
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

执行上述查询，并且将查询的结果复制在执行
例如：
alter table CRM_FORM_DESC_CUSTOMER allocate extent;
alter table CRM_FORM_DESC_CUSTOMER_CORP allocate extent;
alter table CRM_FORM_DESC_DATA allocate extent;
alter table CRM_FORM_DESC_SERVICE allocate extent;


再次执行4.4.2.1 默认导出 的操作即可


设置deferred_segment_creation参数，此参数只对新增的表影响，但是以前是空的表依旧不能导出。如果后续的表没有特殊要求可以不使用此方法

alter system set deferred_segment_creation=false; 

4.5 执行导入
4.5.1 切换oracle用户
su oracle

4.5.2 导入文件
imp userName/pwd@orcl file="/home/backup/tablespaceName.dmp"


名称
描述
userName
用户名称
pwd
密码
orcl
实例名
file
导出的目录，可以新建确保导出的目录权限够用
full
y-导入全部的数据库忽视对应的用户 n-不忽视用户会提示错误，默认为n
例如 full=y
5 创建序列
create sequence SQ_Name
minvalue 1  --最小值
maxvalue 99999999 --最大的值
start with 1   --开始的数值
increment by 1  --每次增长的数值
NOCYCLE -- 一直累加，不循环
nocache; --不建缓冲区

创建表
创建学员表
-- 创建maizi数据库
CREATE DATABASE IF NOT EXISTS maizi DEFAULT CHARACTER SET  'UTF8';
USE maizi;
#创建学员表（USER）
#编号id
#用户名username
#年龄age
#性别sex
#邮箱email
#地址addr
#生日birth
#薪水salary
#电话tel
#是否结婚married
#注意需要输入中文的时候，需要临时转换客户端的编码方式
#SET NAMES GBK;
#字段注释 通过COMMMENT 注释内容 给字段注释
CREATE TABLE IF NOT EXISTS user(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM('男','女','保密'),
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0代表未结婚，非零代表已结婚'
)ENGINE=INNODB CHARSET=UTF8;

查看指定的表结构
DESC user;

DESCRIBE user;

SHOW COLUMNS FROM user;

测试整型
-- 测试整型
CREATE TABLE test1(
num1 TINYINT,
num2 SMALLINT,
num3 MEDIUMINT,
num4 INT,
num5 BIGINT
);

向表中插入记录INSERT tbl_name VALUE|VALUS(值,…)
INSERT test1 VALUES(-128,-32768,-8388608,-2147483648,-9223372036854775808);

查询表中所有记录
SELECT * FROM test1;