Mysql语句习题测试

1、建立db_text表
create database db_text;
2、使用db_text;
use db_text;
3、建立user_property
create table user_property(
uuid varchar(100) primary key not null,
usename varchar(30) not null,
nickname varchar(100),
email varchar(100),
sex varchar(10),
age int(3),
hobbies varchar(255) not null,
longitude decimal(10,6) not null,
latitude decimal(10,6) not null
);
4、建立user_friend_relation
create table user_friend_relation(
uuid varchar(100) primary key not null,
usename varchar(30) not null,
friend_id varchar(30) not null,
hobby varchar(255) not null
);
5、建立hobby_dict
create table hobby_dict(
hobby_key int(11),
hobby_value varchar(255)
);
6、插入user_property 数据
insert into user_property values
(‘Aabdikaakaa175345aaj’,‘xiaoqiang’,‘肖强’,‘xiaoqiang@qq.com’,‘男’,25,‘1001,2000’,‘113.28669’,‘23.11245’),
(‘6abdidaakaalfe234ay’,‘lilei’,‘李磊’,‘lilei@qq.com’,‘男’,25,‘1001’,‘113.28623’,‘23.11233’),
(‘56bdidaakaalfexaah’,‘gaojie’,‘高洁’,‘gaojie@qq.com’,‘女’,24,‘2000’,‘113.28643’,‘23.11244’),
(‘Aabd5273akaalfepaa0’,‘tengfei’,‘腾飞’,‘tengfei@qq.com’,‘男’,26,‘1001’,‘113.28645’,‘23.11232’),
(‘Aabdi5436kaalfepaat’,‘maomao’,‘毛毛’,‘maomao@qq.com’,‘女’,27,‘1001,2000’,‘113.28664’,‘23.112548’);
7、插入 user_friend_relation数据
insert into user_friend_relation values
(‘4573ikaakaal75345aaj’,‘xiaoqiang’,‘lilei’,‘1001’),
(‘Ebdi156sfewgas45aa43’,‘lilei’,‘xiaoqiang’,‘1001’),
(‘iydi1werfewgas4234tt’,‘xiaoqiang’,‘gaojie’,‘2000’),
(‘osduii1esdo82938hshf3’,‘xiaoqiang’,‘tengfei’,‘1001’);
8、显示以下表的数据
select * from user_property;
select * from user_friend_relation;
select * from hobby_dict;
9、在 hobby_dict插入部分数据
insert into hobby_dict values(1000,‘足球’),(1001,‘篮球’),(1002,‘桌球’),(2000,‘电影’),(2001,‘戏剧’),(2002,‘唱歌’);

查询lilei用户的基本消息（昵称，邮箱，性别，年龄，爱好）
方法一
select p.nickname,p.email,p.sex,p.age,group_concat(distinct h.hobby_value)
from user_property as p
left join hobby_dict as h
on find_in_set(h.hobby_key,p.hobbies)
where p.usename=‘tengfei’ or p.usename=‘lilei’;
方法二
select * from user_property as P
left join hobby_dict as H
on P.hobbies like concat(’%’,h.hobby_key,’%’)
where p.usename=‘tengfei’ or p.usename=‘lilei’ order by P.nickname;
查询xiaoqiang用户关于篮球爱好的好友推荐信息，展示内容包含推荐好友的基本信息（昵称，邮箱，性别，年龄，爱好），并按照年龄倒序排序
#先连接 关系表， 再过滤 篮球
select * from user_property as P
inner join user_friend_relation as F
on P.usename = F.friend_id and F.usename=‘xiaoqiang’
inner join hobby_dict as H
on P.hobbies like concat(’%’,h.hobby_key,’%’) and h.hobby_value=‘篮球’;
#先过滤 篮球 ,查 关系表，
select * from user_property as P
inner join hobby_dict as H
on P.hobbies like concat(’%’,h.hobby_key,’%’) and h.hobby_value=‘篮球’
where P.usename
in (select friend_id from user_friend_relation where usename=‘xiaoqiang’);