达梦数据库练习笔记

| 本次SQL语句练习使用的是国产的达梦数据库, 数据库数据和练习题目由@Teacher熊 提供

1. 数据表和约束管理

1.1 使用SQL命令完成数据表的创建

-- 创建用户表
create table vspace.i_user (
	phone char(11) not null  primary key,
	password varchar(20) not null
);

-- 创建基本信息表
create table vspace.i_basic(
	phone char(11) not null  primary key,
	name varchar(30) not null,
	id_card varchar(18) not null,
	birthday datetime not null,
	reg_date datetime not null,
	last_login_date datetime not null,
	head_images varchar(100) not null
);

-- 创建商品分类表
create table vspace.i_category(
	CATEGORY_ID	int not null primary key,
	CATEGORY_LEVEL	int not null,
	CATEGORY_NAME varchar(20) not null,
	PARENT_ID int not null
);

-- 创建商品表
create table vspace.i_goods(
	GOODS_ID bigint not null primary key,
	GOODS_TITLE	varchar(1024),
	PRICE double,
	DISCOUNT double,
	SPECIFICATION varchar(100),
	DESCRIPTION text,
	CATEGORY_ID int,
	AMMOUNT	int,
	IMAGE_URL text,
	UP_DATE datetime,	
	DOWN_DATE datetime
);

-- 创建购物车信息表
create table vspace.i_cart(
	CART_ID	bigint not null primary key,
	PHONE char(11),
	JOIN_DATE datetime,	
	GOODS_ID bigint,
	AMMOUNT int
);

-- 创建订单表
create table vspace.i_order(
	ORDER_ID bigint not null primary key,
	PHONE char(11),
	ORDER_DATE	datetime,
	GOODS_ID bigint,
	AMMOUNT	int,
	SUM_PRICE	number(10,2),
	ORDER_STATUS varchar(50),
	RECEIVE_ADDR_ID bigint
);

-- 创建配送地址表
create table vspace.i_receive_addr(
	ADDR_ID bigint not null primary key,
	PROV varchar(50),
	CITY varchar(50),
	SECT	varchar(50),
	DETAIL	varchar(100),
	RECEIVE_NAME varchar(30),	
	RECEIVE_PHONE char(11),	
	OWN_USER_PHONE char(11)
);

1.2 添加 Vspace 项目中数据表之间的关联系

alter table vspace.i_basic 
add constraint FK_basic_phone 
foreign key (phone) references vspace.i_user(phone);

alter table vspace.i_goods 
add constraint FK_category_id 
foreign key (category_id) references vspace.i_category(category_id);

alter table vspace.i_cart 
add constraint FK_user_phone 
foreign key (phone) references vspace.i_user(phone);

alter table vspace.i_cart 
add constraint FK_goods_id 
foreign key (goods_id) references vspace.i_goods(goods_id);

alter table vspace.i_receive_addr 
add constraint FK_own_user_phone 
foreign key(OWN_USER_PHONE) references vspa