mysql数据库的基础知识和操作，笔记3，权亮

AS 用来重命名 表名或者字段名，只是在查询中有效。作用省事。
同时用来解释说明，将子查询重命名，当表用，在查询命令内，定义在后面，重命名在前面也有效。

Limit 整数值，连续显示前多少个整数值的信息，写在最后，
Limit 整数值，整数值。两个参数时候，第一个参数代表开始元素也就是开始的行的位置，第二个参数代表连续的数值。0代表起始第一行位置。Limit 2,3，也就是第三行，连续三个，三四五行。

数学函数，在mysql里是针对字段的。
常用数学函数：
Abs（x），求绝对值
Log（x，y），求x的以y为底对数。
Mod（x，y），求x/y的余数。
Rand（），返回（0,1）之间的随机小数。和excel的算法不同。
Ceiling（x），返回大于x的的最小整数，进一取整。
Round（x，y），返回x的四舍五入的有y位小数的值。
Sqrt（x），返回一个数的平方根。

常用字符串函数：
Substring（取数对象，起始位置，连续取得字符数）
Concat（s1，s2，…）讲里面的参数链接起来。
Left（str，x），取字符串str的左边的x位字符。
Right（str，x），取字符串str的右边的x位字符。
Length（str），返回字符串中的字符数。
日期时间类函数：
Now（），返回当前日期和时间。
Date（datetime），返回datetime的日期值。
Time（datetime），返回datetime的是兼职

其他函数：

Group_concat 文本类的聚合,文本内的所有内容，存在一起，用逗号隔开。

分组后没聚合，那就只能找到维度的第一个值。
Update …set函数和delete from 函数都是针对字段内容进行修改。
在修改前，输入命令：
Set sql_safe_updates=0;

函数：Update …set…，为某个字段赋值。
Update 表名 set 字段=新字段;

函数：Delete from

Delete from 表名 where 要求;

单标查询练习
变量定义
Set @abc =();给变量定义，如果不加@，就只能在存储过程中使用。加了就是全局变量。
Select @abc; //展示变量
Set @def=();
Select @def;

用查询有语句快速建表的方法
Create table 新表1（查询语句，查询的结果就是新的字段）;

多表连接
Select * from table1 left join table2 on t1.r=t2.r left jion table3 on t1.r=t3.r.

练习：

– 建表
use ceshi;
drop table goodsbrand;
create table goodsbrand
(
SupplierID int ,
BrandType varchar(20)
);
load data infile ‘E:/cda/rawdata/goodsbrand.csv’
into table goodsbrand
fields terminated by ‘,’
ignore 1 lines;

select * from goodsbrand;

create table goodscolor
(
ColorId int,
ColorNote varchar(20),
ColorSort int
);

load data infile ‘E:/cda/rawdata/goodscolor.csv’
into table goodscolor
fields terminated by ‘,’
ignore 1 lines;

select * from goodscolor;

create table goodsinfo
(
goodsid int,
typeid int,
markid int,
goodstag varchar(200),
barandtag varchar(200),
customtag varchar(200),
goodsname varchar(200),
clickcount int,
clickcr int,
goodsnumber int,
goodsweight int,
marketprice int,
shopprice int,
addtime int,
isonsale int,
sales int,
realsales int,
extraprice int,
goodsno int
);

load data infile ‘E:/cda/rawdata/goodsinfo.csv’
into table goodsinfo
fields terminated by ‘,’
ignore 1 lines;

alter table goodsinfo add addtime1 datetime not null;
update goodsinfo set addtime1=from_unixtime(addtime);
alter table goodsinfo drop addtime;

select * from goodsinfo;

create table goodssize
(
SizeID int,
SizeNote varchar(200),
SizeSort int
);

load data infile ‘E:/cda/rawdata/goodssize.csv’
into table goodssize
fields terminated by ‘,’
ignore 1 lines;

select * from goodssize;

create table orderinfo
(
OrderId int,
Userid int,
OrderState int,
PayState int,
AllotStatus int,
Consignee varchar(200),
Country int,
Province int,
City int,
District int,
Address varchar(500),
GoodsAmount float,
OrderAmount float,
ShippingFee int,
RealShippingFee int,
PayTool int,
IsBalancePay int,
BalancePay int,
OtherPay float,
PayTime varchar(20),
Addtime varchar(20)
);

load data infile ‘E:/cda/rawdata/orderinfo.csv’
into table orderinfo
fields terminated by ‘,’
ignore 1 lines;

alter table orderinfo add paytime1 datetime not null;
alter table orderinfo add addtime1 datetime not null;
update orderinfo set paytime1 = from_unixtime(paytime);
update orderinfo set addtime1 = from_unixtime(addtime);

alter table orderinfo drop paytime;
alter table orderinfo drop addtime;

select * from orderinfo;

create table regioninfo
(
regionid int,
parentid int,
regionname varchar(200),
regiontype int,
agencyid int

);

load data infile ‘E:/cda/rawdata/regioninfo.csv’
into table regioninfo
fields terminated by ‘,’
ignore 1 lines;

select * from regioninfo;

create table useraddress
(
addressid int,
userid int,
consignee varchar(100),
country int,
province int,
city int,
district int,
address varchar(500)
);

load data infile ‘E:/cda/rawdata/useraddress.csv’
into table useraddress
fields terminated by ‘,’
ignore 1 lines;

select * from useraddress;

create table userinfo
(
userid int,
username varchar(20),
userpassword varchar(50),
sex int,
usermoney int,
frozenmoney int,
addressid int,
regtime varchar(20),
lastlogin varchar(20)
);

load data infile ‘E:/CDA/rawdata/userinfo.csv’
into table userinfo
fields terminated by ‘,’
ignore 1 lines;

alter table userinfo add regtime1 datetime not null;
update userinfo set regtime1=from_unixtime(regtime);
alter table userinfo add lastlogin1 datetime not null;
update userinfo set lastlogin1=from_unixtime(lastlogin);

alter table userinfo drop regtime;
alter table userinfo drop lastlogin;
select * from userinfo;

create table orderdetail
(
RecID int,
OrderID int,
UserID int,
SpecialID int,
GoodsID int,
GoodsPrice int,
ColorID int,
SizeID int,
Amount int
);

load data infile ‘E:/CDA/rawdata/orderdetail.csv’
into table orderdetail
fields terminated by ‘,’
ignore 1 lines;
select * from orderdetail;

解答如下:
– 1.求出购买产品金额最多的前十名顾客
select userid,consignee,sum(orderamount) from orderinfo
group by userid
order by sum(orderamount) desc
limit 10;

– 2.求出购买金额最多的前十名顾客的最后登录时间
select b.userid, sum(orderamount), lastlogin1
from orderinfo as a left join userinfo as b on a.userid=b.userid
group by userid
order by sum(orderamount) desc
limit 10;

– 3.求出购买产品金额最多的前十名顾客的所在城市
select userid ,sum(orderamount), regionname
from orderinfo left join regioninfo
on city=regionid
group by userid
order by sum(orderamount) desc
limit 10;

– 4.求出购买力最强的前十名城市
select sum(orderamount) ,regionname
from orderinfo left join regioninfo
on city=regionid
group by city
order by sum(orderamount) desc
limit 10;

– 5.求出购买力最强的前十个城市的所在省份
create table aa ( select sum(orderamount) as amount ,regionid ,province,regionname as city1
from orderinfo left join regioninfo
on city=regionid
group by city
order by sum(orderamount) desc
limit 10);
select * from aa;

select amount, city1,regioninfo.regionname
from aa left join regioninfo
on province = regioninfo.regionid
order by amount desc;

select sum(orderamount), userid,province,city,b.regionname as 城市 ,c.regionname as 省份
from orderinfo as a left join regioninfo as b on a.city=b.regionid
left join regioninfo as c on a.province=c.regionid
group by a.city
order by sum(orderamount) desc
limit 10;

– 6.求出最畅销的十个品牌
select brandtype, sum(amount)
from orderdetail as a left join goodsinfo as b
on a.goodsid=b.goodsid
left join goodsbrand
on b.typeid=supplierid
group by brandtype
order by sum(amount) desc
limit 10;

– 7.求出最畅销的十种颜色,以及最不畅销的十种颜色
– 最畅销的10种颜色
select a.colorid,colornote,sum(amount)
from orderdetail as a left join
goodscolor as b
on a.colorid=b.colorid
group by colorid
order by sum(amount) desc
limit 10;

– 最不畅销的十种颜色
select a.colorid,colornote,sum(amount)
from orderdetail as a left join
goodscolor as b
on a.colorid=b.colorid
group by colorid
order by sum(amount) asc
limit 10;

– 8.求出最畅销的十个商品所属品牌的所有商品的销售额
理解1的答案:
alter table orderdetail add sales int not null;
update orderdetail set sales=amount*goodsprice;

create table newtype (select typeid ,sum(a.sales)
from orderdetail as a left join goodsinfo as b
on a.goodsid=b.goodsid
group by a.goodsid
order by sum(a.sales) desc
limit 10);
create table uniquetype (select distinct(typeid) from newtype);

select a.typeid, b.goodsid,sum(c.sales)
from uniquetype as a left join goodsinfo as b
on a.typeid = b.typeid
left join orderdetail as c
on b.goodsid =c.goodsid
group by b.goodsid
order by sum(c.sales) desc;

select a.typeid ,sum(c.sales) from uniquetype as a left join goodsinfo as b
on a.typeid = b.typeid
left join orderdetail as c
on b.goodsid=c.goodsid
group by a.typeid
order by sum(c.sales) desc;

select * from orderdetail;
理解2的答案:
create table aa1
(select typeid as 品牌id
from orderdetail as od left join goodsinfo as gs on od.goodsid = gs.goodsid
group by od.goodsid
order by sum(amount) desc
limit 10);

– 品牌中所有商品
create table bb1
(select goodsid as 商品id
from aa1 left join goodsinfo on 品牌id = typeid
group by 商品id);

– 商品的销售额
select 商品id, sum(amount * goodsprice) as 销售额
from bb1 left join orderdetail on 商品id = goodsid
group by 商品id
order by 销售额 desc;

– 9.买不同商品种类最多的前十名用户所使用的收货城市有哪些

select a.userid ,count(distinct goodsid),group_concat(distinct city), group_concat( distinct c.regionname)
from orderdetail as a left join orderinfo as b
on a.orderid = b.orderid
left join regioninfo as c
on b.city = c.regionid
group by userid
order by count(distinct goodsid) desc
limit 10;