1.Oracle建表及使用
1.概述
1. 表：用于 '存储数据' -- 是我们最常见的数据库对象
2. 表设计注意事项
(1) 表设计时，尽量遵从 '第三范式（3NF）'
(2) 名称不能超过 30 个字符 -- 超过会报错
(3) 名称只能以 '字母' 大头，可由'数字'、 '_'、 '$' 或 '#' 组成
2.语法
2.1 创建表 create table
-- 创建表: student_info 属主: scott (默认当前用户)
create table scott.student_info (sno number(10) constraint pk_si_sno primary key,sname varchar2(10),sex varchar2(2),create_date date
);-- 添加注释
comment on table scott.student_info is '学生信息表';
comment on column scott.student_info.sno is '学号';
comment on column scott.student_info.sname is '姓名';
comment on column scott.student_info.sex is '性别';
comment on column scott.student_info.create_date is '创建日期';-- 语句授权，如：给 hr 用户下列权限
grant select, insert, update, delete on scott.student_info to hr;
2.1.1创建单列主外键
主键值必须是唯一的。也就是说，表中的每一行都必须具有唯一的主键值。
create table tb_supplier
(
supplier_id number not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)
);
create table tb_products
(
product_id number not null,
product_name varchar2(100),
supplier_id number not null,
constraint fk_products_supplier foreign key (supplier_id) references tb_supplier(supplier_id)
);
创建多列主外键
create table tb_supplier
(
supplier_id number not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT pk_supplier PRIMARY KEY (supplier_id,supplier_name)
);
create table tb_products
(
product_id number not null,
product_name varchar2(100),
supplier_name varchar2(50),
supplier_id number not null,
constraint fk_products_supplier foreign key (supplier_id,supplier_name) references tb_supplier(supplier_id,supplier_name)
);
使用alter table命令定义外键
create table tb_supplier
(
supplier_id number not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT pk_supplier PRIMARY KEY (supplier_id,supplier_name)
);
create table tb_products
(
product_id number not null,
product_name varchar2(100),
supplier_name varchar2(50),
supplier_id number not null
);
--使用alter table创建外键
alter table tb_products add constraint fk_products_supplier foreign key (supplier_id,supplier_name) references tb_supplier(supplier_id,supplier_name);
2.1.2 处理NULL值
CREATE TABLE vendors
(
vend_id int NOT NULL,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL
);
2.1.3指定默认值
在插入行时，如果没有指定值，Oracle允许指定默认值。默认值是在CREATE TABLE语句中的列定义中使用DEFAULT关键字指定的
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int DEFAULT 1 NOT NULL ,
item_price decimal(8,2) NOT NULL
);
2.2 insert 插入验证数据
-- 插入
insert into scott.student_info (sno, sname, sex, create_date)
values (1, '张三', '男', sysdate);
insert into scott.student_info (sno, sname, sex, create_date)
values (2, '李四', '女', sysdate);
insert into scott.student_info (sno, sname, sex, create_date)
values (3, '王五', '男', sysdate);-- 修改
update scott.student_info si set si.sex = '女' where si.sno = 3;-- 删除
delete scott.student_info si where si.sno = 1; -- 提交
commit; -- 查询
select * from scott.student_info;
2.3 修改表 alter table
1. '增加' 一列或者多列alter table scott.student_info add address varchar2(50);alter table scott.student_info add (id_type varchar2(2), id_no varchar2(10));2. '修改' 一列或者多列(1) 数据类型alter table scott.student_info modify address varchar2(100);alter table scott.student_info modify (id_type varchar(20), id_no varchar2(20));(2) 列名alter table scott.student_info rename column address to new_address;(3) 表名alter table scott.student_info rename to new_student_info ;alter table scott.new_student_info rename to student_info; 3. '删除' 一列或者多列，删除多列时，不需要关键字 columnalter table scott.student_info drop column sex;alter table scott.student_info drop (id_type, id_no);
2.4 删除表（记录和结构）的语名：delete —— truncate —— drop
-- delete (删除数据表里记录的语句)
delete from 表名 where 条件; 2.5 删除表 drop table
-- 删除表结构
drop table scott.student_info;
2.6 清空表 truncate table
-- 清空表数据
truncate table scott.student_info;