数据库实战练习（一）

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%');