day03
- (使用工具大概 sql 下令)完成 Vspace 项目中所有表的创建
- 添加 Vspace 项目中数据表之间的关联关系
- -- 创建Vspace数据库相关的数据表-- 1. 创建用户表create table vspace.i_user ( phone char(11) not null primary key, password varchar(20) not null);-- 2.创建根本信息表-- 备注: 为了包管用户和根本信息一一对应关系,以用户表为主--》-- 根本信息表中的phone既是主键也是外键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);-- 添加约束: 根本信息表中的手机号码列必须要引用自用户表的phone列alter table vspace.i_basic add constraint FK_basic_phone foreign key(phone) references vspace.i_user(phone);-- 创建商品分类表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);-- 添加外键约束: 商品表中的范例编号是引用自范例表的编号alter table vspace.i_goods add constraint FK_category_id foreign key(category_id) references vspace.i_category(category_id);-- 创建购物车信息表create table vspace.i_cart( CART_ID bigint not null primary key, PHONE char(11), JOIN_DATE datetime, GOODS_ID bigint, AMMOUNT int);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);-- 创建订单表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));alter table vspace.i_receive_addr add constraint FK_own_user_phone foreign key(OWN_USER_PHONE) references vspace.i_user(phone);
复制代码- alter table vspace.i_receive_addr add constraint phone_unique unique("RECEIVE_PHONE");
复制代码- insert into vspace.I_CATEGORY values(66666,2,'插入1',1);insert into vspace.I_CATEGORY values(66667,2,'插入2',2);
复制代码
- 在商品表中插入两个新的商品数据,新商品的范例属于第 1 步中新增的范例
- insert into vspace.I_GOODS values(77777771,'货品1',32,2,'1kg/份','形貌1',66666,12,'www.baidu',2019-02-11,2020-03-21);insert into vspace.I_GOODS values(77777772,'货品2',32,2,'2kg/份','形貌2',66667,32,'www.sohu',2015-03-21,2030-04-24);
复制代码
- 使用达梦数据库迁移工具向 Vspace 项目中导入测试数据
- 查询代价在 50.0 到 100.0 元之间的商品信息
- select * from vspace.i_goods where price between 50.0 and 100.0;
复制代码- select * from vspace.i_goods where goods_title like '%肉%';
复制代码- select phone from vspace.i_order where ammount >11 ;
复制代码
- 请使用 top,limit 和伪列三种方式实现商品信息的分页查询,要求每页显示 3 条数据,显示 第 5 页的数据
- Select top 3 * from vspace.i_goods where goods_id not in (Select top (4*3) goods_id from vspace.i_goods);Select * from vspace.i_goods limit (4*3),3;select t.* from (select rownum num, * from vspace.i_goods where rownum (4*3);
复制代码
- 从商品信息列表中查询包罗”五花肉”的商品标题信息,并从标题中截取出”五花肉”这个 字符串
- select *from vspace.i_goods where goods_title like '%五花肉%';select substring('猪五花肉',2,3);
复制代码- select floor(rand()*(999-100+1)+100);
复制代码 [code]select* from vspace.i_order where datediff (DD,order_date,curdate) |