MySQL练习总结

顾客表

字段名	数据类型	长度	约束	描述
cid	int	11	非空，主键	顾客ID,自动增长
cname	varchar	50	非空	顾客姓名
caddress	varchar	50	非空	顾客地址
ccity	varchar	50	非空	顾客所在城市
cstate	varchar	20	非空	顾客所在州
czip	varchar	20	非空	邮政编码
ccountry	varchar	20	非空	顾客所在国家
ccontact	varchar	20	可以为null	顾客联系姓名
cemail	varchar	20	可以为null	顾客邮箱

产品表

字段名	数据类型	长度	约束	描述
pid	int	11	非空，主键	唯一的产品ID，自动增长
pname	varchar	50	非空	产品名
pprice	decimal	(8,2)	非空	产品价格
pdesc	varchar	100	可以为null	产品描述
pcount	int	11	非空 可以为0	库存数量

订单表

字段名	数据类型	长度	约束	描述
oid	int	11	非空，主键	订单号,自动增长
pid	int	11	非空,外键	产品ID
cid	int	11	非空，外键	顾客ID
ocount	int	11	非空 默认为1	物品数量
oprice	decimal	(8,2)	非空	物品单价
odate	datetime		非空	订单时间

供应商表

字段名	数据类型	长度	约束	描述
vid	int	11	非空，主键	唯一的供应商ID,自动增长
vname	varchar	50	非空	供应商姓名
vaddress	varchar	50	可以为null	供应商地址
vcity	varchar	50	可以为null	供应商所在城市
vstate	varchar	20	可以为null	供应商所在州
vzip	varchar	10	可以为null	供应商地址邮政编码
vcountry	varchar	50	可以为null	供应商所在国家

库存表

字段名	数据类型	长度	约束	描述
rid	int	11	非空，主键	入库ID,自动增长
pid	int	11	非空，外键	物品ID
vid	int	11	非空，外键	供货商
rprice	decimal	(10,0)	非空	单价
rcount	int	11	非空	数量
returncount	int	11	可以为null	退货数量
rdecp	varchar	255	可以为null	订单描述
rdate	datetime		非空	入库时间

建表代码实现

create database productorder default charset utf8 collate utf8_general_ci;
use productorder;
create table `customer`(
    `cid` int(11) not null primary key auto_increment comment '顾客ID',
    `cname` varchar(50) not null comment '顾客姓名',
    `caddress` varchar(50) not null comment '顾客地址',
    `ccity` varchar(50) not null comment '顾客所在城市',
    `cstate` varchar(20) not null comment '顾客所在州',
    `czip` varchar(20) not null comment '邮政编码',
    `ccountry` varchar(20) not null comment '顾客所在国家',
    `ccontact` varchar(20) comment '顾客联系姓名',
    `cemail` varchar(20) comment '顾客邮箱'
)engine = InnoDB default charset =utf8;
create table `product`(
    `pid` int(11) not null primary key unique auto_increment comment '产品ID',
    `pname` varchar(50) not null comment '产品名',
    `pprice` decimal(8,2) not null comment '产品价格',
    `pdesc` varchar(100) comment '产品描述',
    `pcount` int(11) not null default 0 comment '库存数量'
)engine = InnoDB default charset =utf8;

create table `order`(
    `oid` int(11) not null primary key auto_increment comment '订单号',
    `pid` int(11) not null  comment '产品ID',
    `cid` int(11) not null comment '顾客ID',
    `ocount` int(11) default 1 comment '物品数量',
    `oprice` decimal(8,2) not null  comment '物品单价',
    `odate` datetime not null comment '订单时间',
    constraint `fk_pid_order` foreign key (pid) references product(pid),
    constraint `fk_cid_order` foreign key (cid) references customer(cid)
)engine = InnoDB default charset =utf8;
create table `vendor`(
    `vid` int(11) not null primary key unique auto_increment comment '供应商ID',
    `vname` varchar(50) not null comment '供应商姓名',
    `vaddress` varchar(50) comment '供应商地址',
    `vcity` varchar(50)  comment '供应商所在城市',
    `vstate` varchar(20) comment '供应商所在州',
    `vzip` varchar(10) comment '供应商地址邮政编码',
    `vcountry` varchar(50) comment '供应商所在国家'
)engine = InnoDB default charset =utf8;

create table `recruit`(
    `rid` int(11) not null primary key auto_increment comment '入库ID',
    `pid` int(11) not null comment '物品ID',
    `vid` int(11) not null comment '供货商ID',
    `rprice` decimal(10,0) not null comment '单价',
    `rcount` int(11) not null comment '数量',
    `returncount` int(11) comment '退货数量',
    `rdesc` varchar(255) comment '订单描述',
    `rdat` datetime not null comment '入库时间',
    constraint fk_pid_recruit foreign key (pid) references product(pid),
    constraint fk_cid_recruit foreign key (vid) references vendor(vid)
)engine = InnoDB default charset =utf8;

注意点：创建数据库或者数据表时应该注意设置字符集，否则后面插入数据的时候，会很难办！

插入数据代码实现

# 添加数据
INSERT INTO `customer` (cid,cname, caddress, ccity, cstate, czip, ccountry, ccontact, cemail)
values (1,'马云','大马路一号','杭州','浙江',100000,'中国','马云','mayun@alibaba.com');
INSERT INTO `customer` (cid, cname, caddress, ccity, cstate, czip, ccountry, ccontact, cemail)
values (2,'刘强东','中关村上地街一号','北京','北京',100000,'中国','奶茶妹妹','liuqiangdong@jd.com');
INSERT INTO `customer` (cid, cname, caddress, ccity, cstate, czip, ccountry, ccontact, cemail)
values (3,'马化腾','你懂得','东莞','广东',300000,'中国','马化腾','mahuateng@qq.com');

Insert Into `product` (pid, pname, pprice, pdesc, pcount) VALUES
(1,'小米Note2',2799,'小米Note 2 双曲面商务旗舰机',100);
Insert Into `product` (pid, pname, pprice, pdesc, pcount) VALUES
(2,'小米Mix',3499,'4GB+128GB',100);
Insert Into `product` (pid, pname, pprice, pdesc, pcount) VALUES
(3,'iPhone7',5388,'4.7屏+玫瑰金色',100);
Insert Into `product` (pid, pname, pprice, pdesc, pcount) VALUES
(4,'iPhone6s',4588,'分期每月最低约RMB 382 ',100);

Insert Into `order` (oid, pid, cid, ocount, oprice, odate) VALUES
(1,1,1,1,2999,'2016-12-02 15:41:39');
Insert Into `order` (oid, pid, cid, ocount, oprice, odate) VALUES
(2,3,2,2,5888,'2016-12-01 15:42:42');
Insert Into `order` (oid, pid, cid, ocount, oprice, odate) VALUES
(3,4,3,100,4888,'2016-12-06 15:43:26');
Insert Into `order` (oid, pid, cid, ocount, oprice, odate) VALUES
(4,1,1,2,2999,'2016-12-31 15:43:26');
Insert Into `order` (oid, pid, cid, ocount, oprice, odate) VALUES
(11,1,1,2,2999,'2017-01-01 01:59:59');

INSERT INTO `vendor`(vid, vname, vaddress, vcity, vstate, vzip, vcountry) VALUES
(1,'雷军','蟠桃园','仙桃','湖北','4000000','中国');
INSERT INTO `vendor`(vid, vname, vaddress, vcity, vstate, vzip, vcountry) VALUES
(2,'库克','Apple 1 Infinite Loop Cup','库比蒂诺','加利福尼亚州','8000000','美国');

Insert Into `recruit`(rid, pid, vid, rprice, rcount, returncount, rdesc, rdat) VALUES
(1,4,2,4588,50,0,'来50个iPhone6s','2016-12-01 16:07:57');
Insert Into `recruit`(rid, pid, vid, rprice, rcount, returncount, rdesc, rdat) VALUES
(2,4,2,4388,200,0,'200个iPhone6s','2016-12-04 16:08:41');
Insert Into `recruit`(rid, pid, vid, rprice, rcount, returncount, rdesc, rdat) VALUES
(3,3,2,5388,100,0,'100台iPhone7','2016-12-06 16:09:40');
Insert Into `recruit`(rid, pid, vid, rprice, rcount, returncount, rdesc, rdat) VALUES
(4,3,1,5288,50,0,'从雷军手里弄来50个iPhone','2016-11-15 16:10:20');
Insert Into `recruit`(rid, pid, vid, rprice, rcount, returncount, rdesc, rdat) VALUES
(5,2,1,3000,50,1,'小米MIX来50个','2016-11-16 16:10:45');
Insert Into `recruit`(rid, pid, vid, rprice, rcount, returncount, rdesc, rdat) VALUES
(6,2,1,2800,150,2,'小米MIX再来150个','2016-11-30 16:11:01');
Insert Into `recruit`(rid, pid, vid, rprice, rcount, returncount, rdesc, rdat) VALUES
(7,1,1,2500,10,5,'小米Note2来10个','2016-11-21 16:11:17');
Insert Into `recruit`(rid, pid, vid, rprice, rcount, returncount, rdesc, rdat) VALUES
(8,1,1,2399,20,15,'小米Note2再来20个','2016-12-13 16:11:35');

操作数据代码实现

# 3 .customer表：将马云的邮政编码改为：110120 ,地址改为：北边小湖边
update customer set czip = '110120' where cname = '马云';
# 4. product表：将产品编号为2的产品描述改为:我世界最牛的手机，手机中的大哥大
update product set pdesc = '我世界最牛的手机，手机中的大哥大' where pid=2;
# 1).查询所有顾客信息。
select * from customer;
# 2).查询所在城市是北京的顾客姓名，顾客地址，顾客所在城市，邮编。
select cname,caddress,ccity,czip from customer where ccity='北京';
# 查询产品名中带有“小米”的商品信息
select * from product where pname like '%小米%';
# 3).查询产品名中以“小”开头或者产品描述中带有“玫瑰”的产品信息
select * from product where pname like '小%' or pname like '%玫瑰%';
# 4).查询价格在3000以上的的产品名和单价（使用别名）
select pname as '产品名' , pprice as '单价' from product where pprice > 3000;
# 5).查询价格在3000以上并且库存数量在80以上的产品的名字和单价
select pname,pprice from product where pprice > 3000 and pcount > 80;

# 查询物品数量大于10的订单信息
select * from `order` where ocount>10;
# 3).分别使用IN和OR的方式查询pid为2和4的商品销售记录
select * from `order` where pid = 2 or pid = 4;
select * from `order` where pid in (2,4);
# 4)查询2016-12-05到2016-12-15之间的订单详情(使用两种方法查询)
select * from `order` where odate between '2016-12-05' and '2016-12-15';
select * from `order` where odate > '2016-12-05' and odate < '2016-12-15';
# 5)查询订单编号为4的交易总金额（请使用别名）
select oprice * `order`.ocount as '交易总金额' from `order` where oid=4;

# 找出供应商地址以A起头、 以o结尾的所有供应商
select vname from vendor where vaddress like 'A%o';
# 3).查询供应商地址为空的供应商信息
select * from vendor where vaddress = '';
# 4).查询出所有供应商的姓名，地址，城市（使用别名）
select vname as '姓名', vaddress as '地址', vcity as '城市' from vendor;

# 2)查询退货数量大于10的物品信息
select * from product where pid =
                            (select pid from recruit where returncount > 10);
# 3)查询所有的供应商编号(需过滤重复行)
select distinct vid from recruit;