mysql语句练习

最新推荐文章于 2022-11-16 21:34:46 发布
永恒的狼牙
最新推荐文章于 2022-11-16 21:34:46 发布
阅读量169
点赞数
分类专栏： mysql 文章标签： mysql
本文链接：https://blog.csdn.net/weixin_43117449/article/details/84350070
版权
mysql 专栏收录该内容
5 篇文章 0 订阅
订阅专栏
-- 例子
CREATE TABLE `customer` (
  `CID` varchar(16) NOT NULL COMMENT '用户编码,唯一标识每一个用户，主键',
  `cname` varchar(128) NOT NULL COMMENT '用户名称',
  `cpassword` varchar(8) NOT NULL COMMENT '密码',
  `email` varchar(16) DEFAULT NULL COMMENT '邮箱',
  `mobile` varchar(11) NOT NULL COMMENT '手机号',
  `province` varchar(16) DEFAULT NULL COMMENT '区域',
  `sex` char(1) NOT NULL DEFAULT '0' COMMENT '性别，0：男；1：女',
  `hobby` varchar(32) DEFAULT NULL COMMENT '爱好',
  PRIMARY KEY (`CID`)
);

-- 创建商品表Goods
create table goods(
goodsID varchar(16) not null comment '商品编码，唯一，主键',
gname varchar(128) not null comment '商品名称',
brand char(4) not null comment '品牌,1001:欧资；1002：Nike...',
category char(4) not null comment '类别，1001：风衣；1002：裙装...',
price double(8,2) not null default '0.00' comment '单价',
stock int(8) default null comment '库存',
picture varchar(32) default null comment '图片',
feature varchar(256) default null comment '商品简介',
newIdt char(1) not null default '0' comment '商品特殊标识，0：普通商品；1：新品；2：限时促销品...',
shelftTime timestamp null default current_timestamp on update current_timestamp comment '商品上架时间',
primary key (goodsID)
) comment '商品信息表';

desc goods;

-- 创建购物车订单信息表
create table orderForm(
oid varchar(16) not null comment '订单编码，唯一，主键',
cid varchar(16) NOT NULL COMMENT '用户编码,唯一标识每一个用户',
goodsID varchar(16) not null comment '商品编码，唯一标识每一个商品',
onum int(8) not null default '1' comment '订购数量',
amount double(8,2) not null default '0.00' comment '小计金额',
orderTime timestamp not null default current_timestamp comment '订购时间',
cancelTime date default null comment '订单取消时间',
ostatus char(4) not null default '1001' comment '订单状态,1001:订购中；1002：已支付...；9999：订单已取消',
primary key (oid)
) comment '购物车订单信息表';

-- 创建数据字典表
create table sys_dic(
did int(8) not null auto_increment  comment '编码，自动增长列，主键',
tableName varchar(16) not null comment '表名',
columnName varchar(16) not null comment '列名',
dcode char(4) not null comment '编码',
dvalue varchar(128) not null comment '编码的具体内容',
primary key (did)
) comment '数据字典表';

desc sys_dic;

desc goods;

select CURRENT_TIMESTAMP;

-- 更改customer表中的字段
alter table customer add column address varchar(128) default null comment '配送地址';

alter table customer change address address varchar(256) default null comment '配送地址';

alter table customer drop column address;

desc customer;

-- 删除数据表
drop table orderform;

desc orderform;

desc goods;

-- insert插入数据
insert into goods values('20180002','花花公子男式牛仔裤','1002','1003',1099,6,'20180002.jpg','布料好','1','2018-11-06 15:00:00');

insert into goods(goodsID,gname,brand,category,price,stock,picture) 
values('20180003','欧资女式条纹连衣裙','1001','1002',980,3,'20180003.jpg');

select * from goods;

INSERT INTO goods VALUES ('20180001', '欧资女式风衣', '1001', '1001', '998.00', '7', '20180001.jpg', '美丽好看', '0', '2018-09-14 19:24:28');
INSERT INTO goods VALUES ('20180002', '花花公子男士牛仔裤', '1002', '1004', '1099.00', '100', '20180002.jpg', null, '0', '2018-08-22 14:34:54');
INSERT INTO goods VALUES ('20180003', '欧资女式条纹连衣裙', '1001', '1002', '3988.00', '7', '20180003.jpg', null, '1', '2018-09-14 19:20:34');
INSERT INTO goods VALUES ('20180004', '欧资女式职业裙装', '1001', '1002', '1895.00', '0', '20180004.jpg', '布料好，裁剪合体', '1', '2018-09-14 10:21:43');
INSERT INTO goods VALUES ('20180005', '樱花飘女式风衣', '1003', '1001', '287.00', '4', '20180005.jpg', '端庄好看', '2', '2018-09-14 19:22:15');
INSERT INTO goods VALUES ('20180006', '樱花飘女式长袖连衣裙', '1003', '1001', '392.00', '2', '20180006.jpg', '美丽大方', '2', '2018-08-22 19:05:49');
INSERT INTO goods VALUES ('20180007', '欧资女式半截裙', '1003', '1001', '781.00', '3', '20180007.jpg', '颜色考究', '1', '2018-08-22 19:05:49');
INSERT INTO goods VALUES ('20180008', '欧资女式休闲风衣', '1001', '1001', '661.00', '1', '20180008.jpg', '休闲合体', '1', '2018-08-22 19:05:49');
INSERT INTO goods VALUES ('20180009', '樱花飘女式分红长裙', '1003', '1001', '5916.00', '0', '20180009.jpg', '飘逸大方', '2', '2018-08-30 19:40:30');
INSERT INTO goods VALUES ('20180010', '樱花飘女式带帽风衣', '1003', '1001', '1931.00', '59', '201800010.jpg', '价格适中，舒适', '0', '2018-08-22 19:05:49');
INSERT INTO goods VALUES ('20180011', '骆驼男士牛仔裤', '1004', '1004', '810.00', '75', '20180011.jpg', '舒适耐穿', '0', '2018-09-14 19:19:37');

delete from goods;

select * from goods g where g.shelftTime < '2018-09-01' and g.shelftTime > '2018-08-01';

select * from goods g where g.shelftTime between '2018-08-01'  and '2018-09-01';

-- 查看表结构
desc customer;

-- 插入数据
insert into customer(cid,cname,cpassword,mobile) values('C01','王山','123','13651199001');
insert into customer(cid,cname,cpassword,mobile) values('C02','章子怡','123','1861100905');
insert into customer(cid,cname,cpassword,mobile) values('C03','王菲','678','123456789');
insert into customer(cid,cname,cpassword,mobile,sex) values('C04','那英','678','123456789','1');

-- 修改数据
update customer set sex='1' where cid='C03';

update customer set province='美国',sex='1',hobby='表演' where cid='C02';

update customer set cpassword='111';

update customer set mobile='000000' where mobile='123456789';

update customer set hobby = '未知' where hobby is null;

-- 删除数据
delete from customer where cid='C04';
delete from customer where sex='1';
delete from customer ;

-- 查看数据
select * from customer;

desc goods;

desc orderform;

insert into orderform(oid,cid,goodsID) values('O01','C01','20180001');
insert into orderform(oid,cid,goodsID,onum,amount) values('O02','C01','20180003',2,2*3988);
insert into orderform(oid,cid,goodsID,onum,amount) values('O03','C02','20180001',5,5*998);
insert into orderform(oid,cid,goodsID,onum,amount) values('O04','C02','20180006',1,1*392);

select * from orderform;

-- select查询数据【单表查询】
select * from goods;
select g.* from goods g;
select g.goodsID, g.gname,g.price from goods g;
select g.goodsID, g.gname,g.price from goods as g;
select g.goodsID 商品编码, g.gname 商品名称,g.price 单价 from goods g;
select g.goodsID as 商品编码, g.gname as 商品名称,g.price as 单价 from goods g;

select g.* from goods g where g.price > 1000;
select g.* from goods g where g.price > 1000 and g.price < 3000;
select g.* from goods g where g.price between 1000 and 3000;  -- 包含边界值
select g.* from goods g where g.price >= 1000 and g.price <= 3000;

select g.* from goods g where g.price > 1000 and g.brand='1001';
select g.* from goods g where g.price > 1000 and (g.brand='1001' or g.brand='1002');
select g.* from goods g where g.price > 1000 and g.brand in ('1001','1002');

select g.* from goods g where g.feature is null;
select g.* from goods g where g.feature is not null;

select g.* from goods g order by g.stock;
select g.* from goods g order by g.stock asc;
select g.* from goods g order by g.stock desc;
select g.* from goods g order by g.stock desc, g.price desc;

select g.* from goods g where g.brand in ('1001','1002') order by g.price desc;
select g.* from goods g where g.brand in ('1001','1002') and g.feature is not null order by g.price desc;

select g.* from goods g where g.gname like '%欧资%';
select g.* from goods g where g.gname like '%女式%';
select g.* from goods g where g.gname like '__女式%';
select g.* from goods g where g.gname like '___女式%';

select g.goodsID,g.gname,g.price 原价, g.price * 0.95 活动价  from goods g;

-- 分组统计
select g.brand 品牌, count(g.brand) from goods g group by g.brand;
select g.brand 品牌, sum(g.stock) 总库存量 from goods g group by g.brand;
select g.brand 品牌, sum(g.stock) 总库存量, avg(g.price) 均价 from goods g group by g.brand;
select g.brand 品牌, sum(g.stock) 总库存量, avg(g.price) 均价, max(g.price) 最高价, min(g.price) 最低价 from goods g group by g.brand;

select max(g.price),min(g.price) from goods g;

select count(*), count(1), count(g.goodsID) from goods g;

select count(1) from customer c where c.cname='章子怡' and c.cpassword = '123';

select g.newIdt, count(1)  from goods g group by g.newIdt;

select g.brand 品牌, sum(g.stock) 总库存量  from goods g where g.stock > 0 group by g.brand;

select g.brand 品牌, sum(g.stock) 总库存量  from goods g where g.price>1000 and g.stock > 0 group by g.brand;

select g.brand 品牌, sum(g.stock) 总库存量  from goods g group by g.brand having 总库存量>50;

select c.* from customer c;

select c.sex 性别, count(*) 人数 from customer c group by c.sex;

select c.sex 性别, count(*) 人数 from customer c group by c.sex having 人数>1;

select c.sex 性别, count(*) 人数 from customer c where c.cpassword='123' group by c.sex;

select o.* from orderform o;

select o.cid 用户编码, sum(o.onum) 购买商品件数, sum(o.amount) 总消费金额 from orderform o group by o.cid;

select o.cid 用户编码, sum(o.onum) 购买商品件数, sum(o.amount) 总消费金额 from orderform o group by o.cid order by 总消费金额;

select g.* from goods g limit 0, 3;  -- 第1页的商品  (i-1)*3

select g.* from goods g limit 3, 3;  -- 第2页的商品

select g.* from goods g limit 6, 3;  -- 第3页的商品

select g.* from goods g limit 9, 3;  -- 第4页的商品

select distinct g.brand from goods g;

-- 函数
-- 1、日期时间函数
select current_timestamp, now(), date(now()), time(now()), year(now()), month(now()), day(now()), current_date, current_time ;

select datediff(now(),'1998-01-01')/365;

select datediff(now(), o.ordertime) 相隔几天 from orderform o where oid='O02';

update orderform set ordertime='2018-10-07 10:00:00' where oid='O02';

-- 2、数学函数

select ceiling(datediff(now(),'1998-01-01')/365),floor(datediff(now(),'1998-01-01')/365);

select g.goodsID, g.gname, g.price 原价,format(g.price * 0.95, 1) 活动价 from goods g;

select rand();

select format(g.price * rand(),2) 抽奖价格 from goods g where g.goodsID='20180005';

select mod(10,3);

select least(1,6,9,abs(-1),0);

-- 3、字符串函数

select concat(g.gname, '(', g.goodsID, ')') , g.price from goods g;

select instr(g.gname,'女式') from goods g;

select g.* from goods g where instr(g.gname,'女式') >0; 

select g.goodsID,replace(g.gname,'女式','女士'),g.price from goods g ;

select g.goodsID,replace(g.gname,'女式','女士'),length(g.gname),g.price from goods g ;

select g.goodsID,g.gname,g.price,lcase(ucase(g.picture)) from goods g ;

select g.goodsID,g.gname,substring(g.gname,1,2),g.price,lcase(ucase(g.picture)) from goods g ;

select g.goodsID,g.gname,substring(g.gname,1,2),left(g.gname,2),g.price,lcase(ucase(g.picture)) from goods g ;

-- 4、条件判断函数

select g.goodsID, g.gname, g.stock, if(g.stock=0,'进货提醒：该商品需要立即补货','库存充足') info from goods g order by g.stock;

select g.goodsID, g.gname, g.price,ifnull(g.feature,'未知') from goods g;

select g.goodsID, g.gname, g.price,if(g.feature is null,'未知', g.feature) from goods g;

select g.goodsID, g.gname, 
case g.category 
when '1001' then '风衣'
when '1002' then '连衣裙'
when '1003' then '牛仔裤'
when '1004' then '篮球鞋'
else '未知类别'
end,
g.price from goods g;

-- 创建客户配送地址表
create table customerAddress(
caid varchar(16) not null comment '主键，地址唯一标识',
cid varchar(16) not null comment '用户编码',
address varchar(128) not null comment '用户详细配送地址',
primary key (caid)
) comment '客户配送地址表';

insert into customerAddress values('CA01','C01','北京市海淀区玉泉路');
insert into customerAddress values('CA02','C02','美国纽约');
insert into customerAddress values('CA03','C03','中国香港');
insert into customerAddress values('CA04','C04','北京朝阳区五方桥');
insert into customerAddress values('CA03','C11','美国纽约');

delete from customer where cid='C02';
delete from customer where cid='C04';

-- 多表查询

select * from customeraddress;

-- 内连接查询【隐式内连接】
select d.caid, c.cname, d.address from customeraddress d, customer c where d.cid=c.cid;

select o.* from orderform o;

-- 内连接查询【隐式内连接】
select o.oid,c.cname,o.goodsID,o.onum,o.amount,o.orderTime from orderform o, customer c where o.cid=c.cid;

select o.oid,c.cname,g.gname,o.onum,o.amount,o.orderTime from orderform o, customer c, goods g where o.cid=c.cid and o.goodsID=g.goodsID;

select o.oid,c.cname,g.gname,g.picture,g.price,o.onum,o.amount,o.orderTime from orderform o, customer c, goods g where o.cid=c.cid and o.goodsID=g.goodsID;

select o.oid,c.cname,g.gname,o.onum,o.amount,o.orderTime from orderform o, customer c, goods g 
where o.cid=c.cid and o.goodsID=g.goodsID and o.cid='C02';

-- 内连接查询【显示内连接】
select d.caid, c.cname, d.address from customeraddress d inner join customer c on d.cid=c.cid;

select o.oid,c.cname,o.goodsID,o.onum,o.amount,o.orderTime from orderform o inner join customer c on o.cid=c.cid;

select o.oid,c.cname,g.gname,o.onum,o.amount,o.orderTime from orderform o inner join customer c on o.cid=c.cid
inner join goods g on o.goodsID=g.goodsID;

-- 外连接查询
select o.oid,c.cname,o.goodsID,o.onum,o.amount,o.orderTime from orderform o left outer join customer c on o.cid=c.cid;

select o.oid,c.cname,o.goodsID,o.onum,o.amount,o.orderTime from orderform o right outer join customer c on o.cid=c.cid;

select o.oid,c.cname,o.goodsID,o.onum,o.amount,o.orderTime from customer c left outer join orderform o on o.cid=c.cid;

select g.*, o.oid, o.onum from goods g left outer join orderform o on g.goodsID = o.goodsID;

select g.*, o.oid, o.onum from orderform o right outer join goods g on g.goodsID = o.goodsID;

-- 子查询
select g.* from goods g;

select d.* from sys_dic d;

select d.dvalue from sys_dic d where d.dcode='1001' and d.tableName='goods' and d.columnName='brand';

select g.goodsID,g.gname,
(select d.dvalue from sys_dic d where d.dcode=g.brand and d.tableName='goods' and d.columnName='brand'),
g.price from goods g;

select g.goodsID,g.gname,
(select d.dvalue from sys_dic d where d.dcode=g.brand and d.tableName='goods' and d.columnName='brand'),
(select d.dvalue from sys_dic d where d.dcode=g.category and d.tableName='goods' and d.columnName='category'),
g.newidt,
g.price from goods g;

-- 订购过商品的客户信息
select c.* from customer c where c.cid in (select distinct o.cid from orderform o);
select distinct c.* from orderform o , customer c where o.cid=c.CID;
select distinct c.* from orderform o inner join customer c on o.cid=c.CID;
select distinct c.* from orderform o left outer join customer c on o.cid=c.CID;

-- 没有订购过商品的客户信息
select c.* from customer c where c.cid not in (select distinct o.cid from orderform o);

select distinct c.*, o.* from orderform o right outer join customer c on o.cid=c.CID where o.oid is null;

-- 找出最昂贵的商品
select g.* from goods g where g.price = (select max(g.price) from goods g);

-- 找出最便宜的商品
select g.* from goods g where g.price = (select min(g.price) from goods g);

-- 找出单价 > 所有商品均价的商品
select g.* from goods g where g.price > (select avg(g.price) from goods g);

-- 找出明星客户【订购商品最多的客户】
select c.* from customer c where c.cid = 
(select a.cid from (select o.cid, sum(o.onum) 订购量 from orderform o group by o.cid order by 订购量 desc limit 0,1) a);

select c.*, sum(o.onum) saleCount from customer c right outer join orderform o on c.cid=o.cid group by c.cid order by saleCount desc limit 1;

-- 分解步骤：
select o.cid, sum(o.onum) 订购量 from orderform o group by o.cid;

select o.cid, sum(o.onum) 订购量 from orderform o group by o.cid order by 订购量 desc limit 0,1;

select a.cid from (select o.cid, sum(o.onum) 订购量 from orderform o group by o.cid order by 订购量 desc limit 0,1) a;

-- 找出明星客户【订购商品数量排名前三的客户】

select max(g.price) from goods g;

-- 有销售的商品信息
select g.* from goods g where g.goodsID in (select distinct o.goodsID from orderform o);

-- 没有销售的商品信息
select g.* from goods g where g.goodsID not in (select distinct o.goodsID from orderform o);

select distinct o.cid from orderform o;
select distinct o.goodsID from orderform o;

select * from orderform;
select * from customer;

-- 多表联合查询【表结构一样，查询的列数与类型一致，并具有实际意义】
select * from orderform2016
union all
select * from orderform2017
union all
select * from orderform;

select * from orderform2016
union 
select * from orderform2017
union 
select * from orderform;


select g.goodsID, g.gname from goods g
union all
select c.cid, c.cname from customer c;

-- 视图
create view v_mingxingcid
as 
(select o.cid, sum(o.onum) 订购量 from orderform o group by o.cid order by 订购量 desc limit 0,1);

select c.* from customer c where c.cid = (select cid from v_mingxingcid);

-- 查询配送单
select o.oid, c.cname, c.mobile, d.address, g.gname, g.price, o.onum, o.amount from orderform o, goods g, customer c,customeraddress d 
where o.goodsID=g.goodsID and o.cid=c.cid and c.cid=d.cid;

create view v_peisongdan AS
select o.oid, c.cname, c.mobile, d.address, g.gname, g.price, o.onum, o.amount from orderform o, goods g, customer c,customeraddress d 
where o.goodsID=g.goodsID and o.cid=c.cid and c.cid=d.cid;

select o.oid, c.cname, c.mobile, d.address, g.gname, g.price, o.onum, o.amount 
from orderform o inner join goods g on o.goodsID=g.goodsID
inner join customer c on o.cid=c.cid 
inner join customeraddress d on c.cid=d.cid
where o.oid='O01';

-- 简化查询
select * from v_peisongdan where oid='O03';
select * from v_peisongdan where oid='O04';

-- 索引
create index index_gname on goods(gname);
drop index index_gname on goods;

-- 存储过程
create procedure p_userLoginIdentify(in cname varchar(16), in cpassword varchar(16), out msg varchar(16))
begin
		declare x int;
		select count(1) into x from customer c where c.cname=cname and c.cpassword=cpassword;
		if x > 0 then 
			set msg = '用户登录成功';
		else 
			set msg = '用户登录失败';
		end if;
end;

drop procedure p_userLoginIdentify;

call p_userLoginIdentify('向阳','111', @msg);
select @msg;

call p_userLoginIdentify('章子怡','123', @msg);
select @msg;

select count(1) from customer c where c.cname = '章子怡1' and c.cpassword='123';

create procedure p_getGoodsInfoByID(in goodsID varchar(16), out gname varchar(128), out price double, out stock int)
begin 
	select g.gname, g.price, g.stock into gname,price,stock from goods g where g.goodsID=goodsID;
end;

call p_getGoodsInfoByID('20180006', @gname, @price, @stock);
select @gname, @price, @stock;

-- 根据mobile用户来电的电话号码，获取客户cname、cid、sex【显示 男/女】

-- 用户下单【加入购物车】的存储过程

create procedure p_addOrderForm(in cid varchar(16), in igoodsID varchar(16), in onum int, out msg varchar(32))
begin 
	declare oid varchar(16);
	declare price double;
	declare stock int;
	declare amount double;

	-- 1、生成一个随机的OID
	set oid = concat('O', ceiling(rand()*1000));
	-- 2、计算订单小计金额
	select g.price, g.stock into price, stock from goods g where g.goodsID=igoodsID;
	set amount = price * onum;
	-- 3、判断库存是否足够
	if stock >= onum then 
		-- 4、下单
		insert into orderform(oid, cid, goodsID, onum, amount) values(oid, cid, igoodsID, onum, amount);
		update goods set stock = stock - onum where goodsID=igoodsID;
		set msg = '商品订购成功';
	else 
		set msg = '商品库存不足，请使用到货提醒';
	end if;
end;

-- 验证OID
select concat('O', ceiling(rand()*1000));

drop procedure p_addOrderForm;

call p_addOrderForm('C02','20180009',6, @msg);
select @msg;

-- 触发器
-- 当订单取消【将ostatus='9999'】时，自动归还商品库存
create trigger tri_cancelOrderForm after update on orderform for each row 
begin 
	if new.ostatus = '9999' then 
		-- 这是取消单，需要自动归还库存
		update goods set stock = stock + old.onum  where goodsID=old.goodsID ;
	end if;

end;

drop trigger tri_cancelOrderForm;

update orderform set ostatus='9999' where oid='O311';

-- 下单的触发器【自动生成随机OID，计算小计金额，判断库存是否足够，减库存】
create trigger tri_addOrderForm before insert on orderform for each row 
begin 
	declare price double;
	declare stock int;
	-- 1、自动生成随机OID
	set new.oid = concat('O',ceiling(rand()*1000));
	-- 2、计算小计金额
	select g.price, g.stock into price, stock from goods g where g.goodsID=new.goodsID;
	set new.amount=price * new.onum;
	-- 3、判断库存是否足够
	if stock >= new.onum then 
		-- 4、减库存
		update goods set stock = stock - new.onum where goodsID = new.goodsID;
	else 
		-- 阻止下单
		set new.oid = NULL;
	end if;
end;

insert into orderform(oid, cid,goodsID,onum) values('1', 'C02','20180009',8);

-- 1、首页面【获取商品信息】
select g.goodsID, g.gname,g.price,g.picture,g.newIdt from goods g ;
select g.goodsID, g.gname,g.price,g.picture,g.newIdt from goods g where g.newIdt='1' ;
永恒的狼牙
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql语句练习

-- 例子CREATE TABLE `customer` ( `CID` varchar(16) NOT NULL COMMENT '用户编码,唯一标识每一个用户，主键', `cname` varchar(128) NOT NULL COMMENT '用户名称', `cpassword` varchar(8) NOT NULL COMMENT '密码', `email` varc...
复制链接

扫一扫