MySQL建表及查询实例

       用MySQL数据库实现“共享单车”项目数据的持久化，核心业务实体主要包括用户（编号、昵称、手机号、所在城市、注册日期）和单车（编号、状态、是否损坏），其他暂不考虑。请写出建表语句和查询每个城市各有多少注册的共享单车用户以及查询使用共享单车次数最多的用户的昵称及其所在城市的SQL语句。

1.建表及表关系：

create table tb_city(
    cityid integer not null auto_increment,
    cityname varchar(20) not null,
    primary key(cityid)
);
create table tb_user(
    userid integer not null auto_increment,
    nickname varchar(50) not null,
    cityid integer not null,
    regdate date,
    primary key (userid)
);
create table tb_bike(
    bikeid integer not null auto_increment,
    statecode integer default 0,
    broken bit default 0,
    primary key(bikeid)
);
create table tb_record(
    recordid integer not null auto_increment,
    userid integer not null,
    bikeid integer not null,
    begintime datetime not null,
    endtime datetime,
    payway integer,
    cost float,
    primary key(recordid)
);

alter table tb_user add constraint fk_user_cityid foreign key(cityid) references tb_city(cityid);

alter table tb_record add constraint fk_record_userid foreign key(userid) references tb_user(userid);

alter table tb_record add constraint fk_record_bikeid foreign key(bikeid) references tb_bike(bikeid);

2.查询每个城市各有多少注册的共享单车用户以及查询使用共享单车次数最多的用户的昵称及其所在城市的SQL语句。

select cityname,total from (select cityid,count(cityid) as total 
from tb_user group by cityid) t1 inner join tb_city t2 on t1.cityid=t2.cityid;

select nickname, cityname from (
select userid, count(userid) as total 
from tb_record group by userid having total=(
select max(total) from (
select userid, count(userid) as total from tb_record group by userid) t1)) t2 
inner join tb_user as t3 on 
t2.userid=t3.userid inner join tb_city as t4 on 
t3.cityid=t4.cityid;