day03

最新推荐文章于 2024-09-16 07:53:54 发布

Alwaysyoung00

最新推荐文章于 2024-09-16 07:53:54 发布

阅读量260

点赞数 1

文章标签： sql 数据库

本文链接：https://blog.csdn.net/Alwaysyoung00/article/details/112000493

版权

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 '%肉%';

查询购买订单数量超过 11 个的用户手机号码

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 <= (5*3)
) t where num > (4*3);

从商品信息列表中查询包含”五花肉”的商品标题信息,并从标题中截取出”五花肉”这个字符串

select *from vspace.i_goods where goods_title like '%五花肉%';
select substring('猪五花肉',2,3);

请使用随机数函数得到任意一个随机三位数

select floor(rand()*(999-100+1)+100);

查询最近 10 天的订单信息

select* from vspace.i_order where datediff (DD,order_date,curdate)<=10;

查询统计最近一个月的总销售额

select sum(sum_price) from vspace.i_order where datediff (DD,order_date,curdate)<30;

使用多表内联接(两种方式)查询用户手机号码、密码、注册时间、昵称信息
*~~表中并没有昵称信息奥~~ *

select u.phone,u.password,b.reg_date,b.phone,
u.phone,b.phone
from vspace.i_user u inner join vspace.i_basic b
on u.phone=b.phone;

select u.phone,u.password,b.reg_date,b.phone,
u.phone,b.phone
from vspace.i_user u,vspace.i_basic b
where u.phone=b.phone;

查询用户手机号、昵称、下单时间、订单商品标题、商品数量、总价等信息
~~依然没有昵称~~

select b.phone,g.goods_title,g.ammount,g.price
from vspace.i_basic b,vspace.i_cart c,vspace.i_goods g
where b.phone=c.phone and g.goods_id=c.goods_id

查询显示所有类型的商品，如果某个类型没有对应的商品则显示为空

select *from vspace.i_goods g
left outer join vspace.i_category c
on g.category_id=c.category_id

请用子查询查找年龄最小的用户的基本信息

select *from vspace.i_basic where birthday in(
select max(birthday) from vspace.i_basic
);

请用子查询查找所有商品以及该商品所属的一级类型名称

select goods_id,goods_title,(
	select category_name from vspace.i_category where
	category_id=(
		select parent_id from vspace.i_category
		where category_id=g.category_id
	)
) 一级类型名称 from vspace.i_goods g;

分页查询订单编号、用户身份证号、商品名称、商品数据量、总价，要求每页显示 7 条，显示第 5 页的数据

select o.order_id,b.id_card,
	(select goods_title from vspace.i_goods where goods_id=o.goods_id),
	o.ammount,o.sum_price from vspace.i_order o,vspace.i_basic b 
where o.phone=b.phone limit (4*7),7;