create table users(
       id number(10) primary key,
       name varchar2(50) not null,
       nickName varchar2(80) not null,
       password varchar2(50) not null,
       sex char(1) default '1',
       email varchar2(100)
);
alter table USERS add unique (NAME); 

select * from users;
select count(1) from users where name='yyt' and password='123';

create sequence users_id start with 0 increment by 1 minvalue 0 maxvalue 99999999;

insert into users values(users_id.nextval,'yyt','于以鱼','123','1','yyt@qq.com');


select count(1) from users where name='yyt' and password='123'

create table hobby(
       id number(10) primary key,
       name varchar2(50),
       code number(2) unique
);
create sequence hobby_id start with 0 increment by 1 minvalue 0;
insert into hobby values(hobby_id.nextval,'学习',1);
insert into hobby values(hobby_id.nextval,'唱歌',2);
insert into hobby values(hobby_id.nextval,'看书',3);
insert into hobby values(hobby_id.nextval,'爬山',4);
insert into hobby values(hobby_id.nextval,'写代码',5);
insert into hobby values(hobby_id.nextval,'调bug',6);

select * from hobby;

create table nativePlace(
       id number(10) primary key,
       name varchar2(50) unique,
       code varchar2(6)
);
alter table NATIVEPLACE add constraint unique1 unique (CODE);

create sequence nativePlace_id start with 0 increment by 1 minvalue 0;
insert into nativePlace values(nativePlace_id.nextval,'山东','37');
insert into nativePlace values(nativePlace_id.nextval,'济南','3701');
insert into nativePlace values(nativePlace_id.nextval,'̩泰安','3709');
insert into nativePlace values(nativePlace_id.nextval,'北京','01');
insert into nativePlace values(nativePlace_id.nextval,'朝阳区','0101');
commit;
select * from nativePlace;
update nativePlace set name='泰安' where code='3709';

create table userDetail(
       id number(10) primary key,
       nativeplace_code varchar2(6) references nativePlace(code),
       hobby_code varchar2(50),
       userId number(10) references users(id)
);
alter table USERDETAIL add constraint foreign1 foreign key (NATIVEPLACE_CODE) references nativeplace (CODE);
create sequence userdetail_id start with 0 increment by 1 minvalue 0;
select * from userDetail;
       
    select baseTable.*,rownum from(
       select u.id,u.name,u.nickname,u.password,u.sex,u.email,np.name,ud.hobby_code
        from users u left outer join userdetail ud on u.id=ud.userid
        left outer join nativePlace np on ud.nativeplace_code=np.code) baseTable;