1. 表的创建与数据新增
1.1 创建用户表
create table user_info_table
(
user_id int not null auto_increment,
user_name char(10),
password varchar(10),
user_nick varchar(10),
card_num bigint,
primary key(user_id)
);
1.2 用户表中新增数据
insert into user_info_table values
(1,'zhangsan','abc123','zhangsanfeng',124567894651329785),
(2,'lisi','122bbb','limochou',124567894651324567),
(3,'wangwu','123aaa','wangbaiwan',214567894651324567),
(4,'liuqi','12aaa','liuchuanfeng',214563356651324567),
(5,'zhangliu','12aaa','zhangwuji',214563356658966567)
;
1.3 创建订单表
create table order_table(
order_id int unsigned zerofill not null,
price decimal(10,2) not null,
order_status varchar(30) not null,
product_id int not null,
created datetime default '2019-01-01 00:00:00',
user_id int not null,
primary key(order_id)
);
1.4 订单表中新增数据
insert into order_table values
(1,4.99,'pay',1001,'2019-09-25 10:25:26',1),
(2,9.99,'nopay',1002,'2019-09-26 10:25:26',1),
(3,4.99,'pay',1001,'2019-09-25 10:25:26',2),
(4,9.99,'nopay',1002,'2019-09-24 10:25:26',2),
(5,19.99,'pay',1003,'2019-09-26 10:25:26',2),
(6,4.99,'pay',1001,'2019-09-25 10:25:26',3),
(7,4.99,'pay',1001,'2019-09-24 10:25:26',4),
(8,9.99,'pay',1002,'2019-09-25 10:25:26',4),
(9,19.99,'pay',1003,'2019-09-26 10:25:26',4),
(10,29.99,'pay',1002,'2019-09-26 10:25:26',6)
;
2.where子句小练习
2.1 满足价格大于等于9的所有信息
select * from order_table where price>=9;
2.2 查找满足product_id在1002和1003之间的
select * from order_table where product_id
between 1002 and 1003;
2.3 查找user_id在1、3、5这三个数内的信息
select * from order_table where user_id in(1,3,5);
2.4 查找订单状态是已支付的信息
select * from order_table where order_status='pay';
2.5 查找用户名类似于已li开头的信息
select * from user_info_table where user_name like 'li%';
2.6 查找用户名中第二个字母是h的信息
select * from user_info_table where user_name like '_h%';
2.7 查找用户名中第二个字母不是h的信息
select * from user_info_table where user_name
not like '_h%';
2.8 查找用户名中最后一个字母以i结尾的信息
select * from user_info_table where user_name like '%i';
2.9 查找价格大于8,并且订单状态是已支付的所有信息
select * from order_table where price>8 and
order_status='pay';
2.10 查找用户表中user_nick为null的信息
select * from user_info_table where user_nick is null;
2.11 查找用户表中user_nick为 not null的信息
select * from user_info_table where user_nick is not null;
3. 聚合函数练习
3.1 查找订单表中最大的价格,查找订单表中最小的价格
select max(price),min(price) from order_table;
3.2 查找订单表中user_id=2的最小价格
select min(price) from order_table where user_id=2;
3.3 分别列出订单表中user_id=2的最小价格和最大价格
select min(price),max(price) from order_table
where user_id=2;
3.4 分别列出订单表中user_id=2的最小价格和最大价格,并把最小价格的展示结果的列名改为"min_price"
select min(price) as 'min_price' ,max(price)
from order_table where user_id=2;
3.5 求订单表的价格的平均值,求订单表中user_id=2的价格的平均值
select avg(price) from order_table;
select avg(price) from order_table where user_id=2;
3.6 分别列出订单表中user_id=2的价格的平均值、最小值、最大值
select avg(price),min(price),max(price) from order_table
where user_id=2;
3.7 求订单表中user_id=1的价格的总和
select sum(price) from order_table where user_id=1;
3.8 求订单表中user_id=1或者user_id=3的价格总和
select sum(price) from order_table where user_id in (1,3);
4. 分组练习
4.1 首先筛选状态为已支付的订单,然后按照user_id分组,分组后每一组对支付金额进行求 和,最终展示user_id和对应组求和金额
select user_id,sum(price) from order_table where
order_status='pay' group by user_id;
4.2 首先筛选状态为支付的订单,然后按照user_id分组,分组后每一组对支付金额进行求和,再过滤求和金额大于10的,最终展示user_id和对应组的求和金额
select user_id,sum(price) from order_table where
order_status='pay' group by user_id having sum(price)>10;
5. 数据表连接查询和子查询练习
5.1 查询订单表中的价格大于10元的用户的昵称
小提示:用户昵称在用户表中,订单价格在订单表中
select a.user_name,b.price from user_info_table a right join order_table b
on a.user_id=b.user_id where price>10;
5.2 查询用户名以l开头的用户买过的所有订单id和对应价格
小提示:订单id和对应价格在订单表中,用户名在用户表中
(1)数据表连接方式查询
select b.order_id, b.price from user_info_table a right join order_table b
on a.user_id=b.user_id where a.user_name like 'l%';
(2)子查询方式
select order_id,price from order_table where user_id
in(select user_id from user_info_table where user_name like 'l%');