from --> gude 年轻的老创业者，知乎新人
image.png
use girl;
select * from table1;
image.png
image.png
是全英文的，不过没关系，
image.png
create database new1;
use new1;
CREATE TABLE orders
( PRIMARY KEY (order_id),
order_id INT(11) ,
fisrt_name VARCHAR(100) ,
last_name VARCHAR(100) ,
province VARCHAR(100),
city VARCHAR(100) ,
order_pieces DECIMAL(9,2) ,
order_value DECIMAL(9,2)
);
image.png
image.png
在workbench里输入的代码，会被自动的颜色区分，一目了然。黑色的代表我们定义的名称，蓝色的部分是命令。
select * from orders;
image.png
insert into orders
values
(1,'wu','yongli','广东省','东莞市','7','2762.1'),
(2,'wu','yongli','广东省','东莞市','6','2223.1'),
(3,'wang','zhenzhen','广东省','深圳市','6','1802.9'),
(4,'li','yixiao','浙江省','杭州市','5','1087.9'),
(5,'wu','yifan','四川省','达州市','5','1097.25'),
(6,'mei','yishu','上海','上海市','5','1398.1'),
(7,'gu','ting','浙江省','温州市','6','1453.65'),
(8,'zhang','jiajia','广东省','肇庆市','6','1249.05'),
(9,'zhang','jiajia','广东省','肇庆市','5','1053.25'),
(10,'li','feng','广东省','广州市','7','1589.5'),
(11,'zhang','jiayi','广东省','深圳市','6','1973.4'),
(12,'qi','sisi','重庆','重庆市','2','2786.3'),
(13,'an','siqi','浙江省','杭州市','5','1086.25'),
(14,'su','dongdong','江苏省','常州市','4','1152.8'),
(15,'zhang','jingjing','江苏省','苏州市','3','1491.05'),
(16,'Catherine','Ho ','广东省','深圳市','2','2563'),
(17,'Catherine','Ho ','广东省','深圳市','33','9966'),
(18,'Catherine','Ho ','广东省','深圳市','9','11726'),
(19,'Catherine','Ho ','广东省','深圳市','2','2728'),
(20,'Catherine','Ho ','广东省','深圳市','2','2728'),
(21,'che','manli','湖北省','黄石市','6','1293.6'),
(22,'wang','yixuan','浙江省','温州市','1','604.45'),
(23,'wang','yixuan','浙江省','温州市','1','604.45'),
(24,'wang','yixuan','浙江省','温州市','1','604.45'),
(25,'dai','yukai','广东省','深圳市','1','1099.45'),
(26,'wang','dong','广东省','深圳市','5','1175.9'),
(27,'she','nan','广东省','广州市','5','1097.25'),
(28,'wang','zhilong','浙江省','湖州市','6','2316.6'),
(29,'cai','guoqing','北京','北京市','7','1249.6'),
(30,'wei','min','江苏省','南通市','7','2189.55'),
(31,'tang','chuan','广东省','深圳市','4','1647.8'),
(32,'chen','meihong','广东省','东莞市','10','3219.7'),
(33,'tang','jiajia','四川省','自贡市','7','1609.85'),
(34,'chen','mingxiang','浙江省','杭州市','6','1359.6'),
(35,'xiao','jing','四川省','成都市','5','1097.25'),
(36,'liu','weiwei','浙江省','金华市','5','1659.35'),
(37,'chen','jiaqi','广东省','中山市','5','1141.25'),
(38,'xin','yiping','广东省','珠海市','2','1098.9'),
(39,'chen','weijie','江苏省','苏州市','5','1094.5'),
(40,'yan','lei','上海','上海市','4','1361.8'),
(41,'gong','an','广东省','深圳市','4','1602.7'),
(42,'wang','nan','陕西省','西安市','5','1295.25'),
(43,'mu','ziqi','浙江省','台州市','5','1097.25'),
(44,'xiao','zhu','广东省','深圳市','5','1061.5'),
(45,'ji','jie','广东省','广州市','5','2011.35'),
(46,'xu','zheng','广东省','深圳市','6','1803.45'),
(47,'xu','zheng','广东省','深圳市','10','3158.1'),
(48,'xu','zheng','广东省','深圳市','13','3361.6'),
(49,'guo','zinan','上海','上海市','5','1092.3'),
(50,'an','peipei','上海','上海市','10','2744.5'),
(51,'zhen','nanyi','四川省','甘孜藏族自治州','2','1417.9'),
(52,'xu','guoqiang','江苏省','常州市','10','2663.65'),
(53,'yan','wenyan','湖北省','武汉市','8','2150.5'),
(54,'zhu','jinxia','湖南省','株洲市','1','1094.5'),
(55,'zhu','yasong','北京','北京市','10','2501.95'),
(56,'ke','fufu','辽宁省','锦州市','5','1097.25'),
(57,'yan','xiaojiang','北京','北京市','10','2444.2'),
(58,'jiang','yiyan','浙江省','温州市','5','1097.25'),
(59,'zhou','wanjun','湖南省','永州市','1','928.95'),
(60,'xu','feixia','山东省','潍坊市','5','1087.9'),
(61,'du','guifeng','内蒙古自治区','巴彦淖尔市','7','1536.15'),
(62,'mu','naiyi','江西省','南昌市','8','2045.45'),
(63,'hu','yuejiao','北京','北京市','5','1919.5'),
(64,'hu','ziping','天津','天津市','1','1364'),
(65,'xu','jia','辽宁省','鞍山市','5','1514.7'),
(66,'che','zhicong','广东省','深圳市','1','912.45'),
(67,'ke','bingbing','江苏省','淮安市','6','1310.65'),
(68,'ke','bingbing','江苏省','淮安市','7','1371.15'),
(69,'huang','feihong','湖北省','武汉市','10','3202.65'),
(70,'bai','yulan','江苏省','盐城市','2','2107.6'),
(71,'xu','jing','江苏省','苏州市','5','858'),
(72,'xiao','jiajia','陕西省','西安市','7','1368.4'),
(73,'wulan','nacha','内蒙古自治区','呼伦贝尔市','6','2098.8'),
(74,'wulan','nacha','内蒙古自治区','呼伦贝尔市','5','1610.95'),
(75,'bao','yue','重庆','重庆市','1','1165.45'),
(76,'liu','xingyue','北京','北京市','9','1986.05'),
(77,'liu','zelan','江苏省','南京市','6','1111.55'),
(78,'long','qidan','内蒙古自治区','鄂尔多斯市','10','2152.15'),
(79,'xie','feng','北京','北京市','5','1238.05'),
(80,'wu','jianfeng','安徽省','合肥市','5','1094.5'),
(81,'lou','yixiao','天津','天津市','5','1097.25'),
(82,'li','jian','上海','上海市','2','823.9'),
(83,'lu','na','北京','北京市','5','1094.5'),
(84,'luo','huihui','重庆','重庆市','1','1419'),
(85,'luo','meihong','山东省','威海市','5','1358.5'),
(86,'ma','chunjuan','四川省','宜宾市','10','2838.55'),
(87,'mao','tingting','四川省','宜宾市','6','1694'),
(88,'guo','yanxin','辽宁省','大连市','5','1519.65'),
(89,'meng','fei','四川省','成都市','8','2303.95'),
(90,'ning','jing','四川省','南充市','5','1075.25'),
(91,'pan','xuelian','河北省','承德市','5','1097.25'),
(92,'bai','liang','内蒙古自治区','兴安盟','1','929.5'),
(93,'bo','zixiao','陕西省','西安市','5','1427.25'),
(94,'li','lijuan','河南省','洛阳市','5','1086.25'),
(95,'wang','mengjia','河南省','郑州市','5','1097.25'),
(96,'wang','mengjia','河南省','郑州市','5','1097.25'),
(97,'bei','xue','北京','北京市','5','1338.7'),
(98,'mu','chun','辽宁省','沈阳市','3','1737.45'),
(99,'zhang','shuhua','山东省','潍坊市','5','1089'),
(100,'zhang','shuhua','山东省','潍坊市','5','1298')
;
image.png
select province,city from orders;
select province,city from orders where order_id=15;
select province,city from orders where order_id in (15,25,35);
select province,city from orders where fisrt_name = "guo";
select province,city from orders where last_name like "%juan%";
select * from orders
where
fisrt_name = "guo"
or
last_name like "%juan%";
select * from orders where fisrt_name = "guo"
order by order_value desc;
select * from orders where fisrt_name = "guo"
order by order_value desc
limit 1;
select 哪些列
from 哪个表
where 符合什么条件的行
order by 依据什么排序
limit 显示多少行
image.png
- 查找所有洛阳的订单的全部信息。
- 查找订单金额大于2000的订单全部信息，按降序排列。
- 查找金额最大的5条广东的订单的姓和名。
- 查找订单件数最多的5条浙江、福建或者山东的订单 的订单ID。
select
concat(fisrt_name,' ',last_name) as full_name
from orders
where province = "广东省"
order by order_pieces desc
limit 5;
select
order_id, order_value/order_pieces as unit_price
from orders;
image.png
select
concat(fisrt_name, ' ', last_name) as full_name,
sum(order_pieces) as sum_pieces
from
orders
where
province = '广东省'
group by full_name
order by sum_pieces desc
limit 5;
查找
新建一个拼合列，拼合姓和名，并且命名为full_name
新建一个求和列，求订单件数的和，并且命名为sum_pieces
在orders这个表里，符合省='广东省'的行。
对全名full_name进行汇总，
依据订单件数之和sum_pieces进行降序排列。
取前5条。
group by full_name
order by sum_pieces desc
其实有的时候会发现局部变量不管用，因为语句的底层执行顺序不一样，比如先执行where，再执行group by，那么在group by里用as定义的变量，在where命令里无法引用，就会报错。这点我们先忽略，因为今天是入门，入门之后的练习中总会遇到各种问题，遇到问题解决问题，慢慢的就会掌握规律了。
- 查询展示每个省的全部订单金额
- 查询展示单价最高的5个城市
- 查询展示平均订单金额最高的5个姓氏。 （平均的聚合函数是 Avg() ）
select
*,
case
when order_value<1000 then "low"
when order_value between 1000 and 2000 then "middle"
else "high"
end
from orders;
select
concat(fisrt_name, ' ', last_name) as full_name,
sum(order_value)/sum(order_pieces) as unit_price,
case
when unit_price < 200 then "a"
when unit_price >= 200 and unit_price<500 then "b"
when unit_price >= 500 and unit_price<1000 then "c"
else "d"
end as lever
from orders
where lever="d"
group by full_name
order by sum(order_value) desc
limit 10
Error Code: 1054\. Unknown column 'unit_price' in 'field list'
select
concat(fisrt_name, ' ', last_name) as full_name,
sum(order_value)/sum(order_pieces) as unit_price,
sum(order_value) as sum_value
from orders
group by full_name;
image.png
select full_name,unit_price,
case
when unit_price < 200 then "a"
when unit_price >= 200 and unit_price<500 then "b"
when unit_price >= 500 and unit_price<1000 then "c"
else "d"
end as lever
from
(select
concat(fisrt_name, ' ', last_name) as full_name,
sum(order_value)/sum(order_pieces) as unit_price,
sum(order_value) as sum_value
from orders
group by full_name) as t
where unit_price>=1000
group by full_name
order by sum_value desc
limit 10;
select
原有列，
concat()合并列，
+-*/运算列，
case when then else end 分级列
from 表
where 条件
group by 基于什么汇总
order by 基于什么排序
limit 取多少条结果
select order_value from orders where order_id="15" ;
select order_id,order_value from orders
where
order_value > (select order_value from orders where order_id="15") ;
- 查出所有订单金额大于平均金额的订单的全部信息。
- 查出广东省里订单金额最低的订单的订单ID。
- 查出江浙沪的 高于江浙沪客户平均订单金额的 客户的ID和平均订单金额。
select *, sum(order_value) over (partition by province)
from orders;
select *,
row_number() over (partition by province order by order_value desc)
from orders;
image.png
select *,
ntile(5) over (order by order_value desc)
from orders;
select *,
(case ntile(5) over (order by order_value desc)
when 1 then "a"
when 2 then "b"
when 3 then "c"
else "d"
end)
from orders;
select *,
row_number() over w,
ntile(5) over w,
rank() over w
from orders
window w as (partition by province order by order_value desc);
select order_id,order_value,
lag(order_id,1) over (order by order_value)
from orders;
网友评论