二.SQL入门及DDL语言实例

1.SQL的分类

  1.1DDL：数据定义语言

  1.2DML：数据操作语言

  1.3DQL：数据查询语言

  1.4DCL：数据控制语言

2.数据类型

  2.1字符型：char(不可变长)， varchar（可变长）

  2.2数字型：number(n,m) n代表总位数，m代表保留小数位数；float

  2.3日期型：date，timestamp

  2.4其他型：blob，clob，bfile

3.建表实例

--创建商品表
CREATE TABLE goods(
goods_id  varchar(255) primary key,
goods_name  varchar(255),
goods_type_id  varchar(255),
goods_price  number(20,2),
goods_stock  number,
goods_agency  number
);
--创建商品类型表
CREATE TABLE goods_type(
goods_type_id  varchar(255) primary key,
goods_type_name  varchar(255)
);
--创建用户表
CREATE TABLE customer(
customer_id  varchar(255) primary key,
customer_name  varchar(255),
customer_pwd varchar(255),
customer_mail varchar(255) check(customer_mail like '%@%'),
customer_phone varchar(255),
customer_create_date date NOT NULL
);
--创建订单表
CREATE TABLE orderinfo(
orderinfo_id varchar(255) primary key,
orderinfo_customer varchar(255),
orderinfo_addr varchar(255),
orderinfo_pay_state int,
constraint f_k foreign key(orderinfo_customer) references customer(customer_id) 
);
--创建订单商品信息表
CREATE TABLE order_goods_info(
order_goods_info_id  varchar(255),
order_goods_info_goods  varchar(255),
order_goods_info_goodsnum  int,
order_goods_info_orderid varchar(255),
order_goods_info_customer  varchar(255),
constraint f_k1 foreign key(order_goods_info_goods) references goods(goods_id),
constraint f_k2 foreign key(order_goods_info_orderid) references orderinfo(orderinfo_id),
constraint f_k3 foreign key(order_goods_info_customer) references customer(customer_id)
);

 4.表操作实例

--删除管道，杆路审核表
DROP TABLE PIP_PIPELINE_CHECK;
DROP TABLE PIP_POLELINE_CHECK;
SELECT * FROM PIP_PIPELINE_CHECK;
SELECT * FROM PIP_POLELINE_CHECK;

--删除主键约束
select * from user_cons_columns where table_name='PIP_PIPELINE_CHECK';
alter table PIP_PIPELINE_CHECK drop constraint SYS_C00167278;


--增加aa  字段
ALTER TABLE PIP_PIPELINE_CHECK ADD AA VARCHAR(255);