sql create,concat,as,datediff,date_fomat,이중 select,if, 소개팅어플 db

@membership테이블 생성

create table membsership(

unq int unsigned not null auto_increment,

user_id varchar(50) not null,

user_pwd varchar(100) not null,

user_name varchar(20) not null,

gender enum('M','F'),

birth char(8) not null,

email varchar(100),

tel varchar(20),

hp varchar(20),

post varchar(100),

addr1 varchar(100),

addr2 varchar(100),

primary key(unq)

);

@memberOther테이블 생성

create table memberOther(

unq int unsigned not null auto_increment,

user_id varchar(50) not null,

job char(4),

recId varchar(20),

hobby char(4),

alcohol enum('1','2','3'),

smoke enum('1','2'),

pay char(3),

school enum('1','2','3','4'),

fWan enum('1','2','3'),]

bWan enum('1','2','3'),

blood enum('A','O','B','AB'),

star varchar(20),

house enum('1','2','3'),

primary key(unq)

);

@memberLevInfo테이블 생성

create table memberLevInfo(

unq int unsigned not null auto_increment,

user_id varchar(50) not null,

level char(1),

levelDayS date,

levelDayE date,

meetingHit tinyint unsigned,

primary key(unq)

);

@user_id인덱스 추가

alter table membership add index memberUser(user_id);//인덱스 추가

alter table memberOther add index memberOtherUser(user_id);

alter table memberLevInfo add index memberLecUser(user_id);

@membership데이터 입력

insert into membership(user_id,user_name,user_pwd,gender,birth) values('test1','홍길동','1234','M','19851225');

insert into membership(user_id,user_name,user_pwd,gender,birth) values('test2','이영희','1212','M','19900605');

insert into membership(user_id,user_name,user_pwd,gender,birth) values('test3','김홍도','123456','M','19770113');

insert into membership(user_id,user_name,user_pwd,gender,birth) values('test4','박문제','1111','M','19880730');

@memberOther데이터 입력

insert into memberOther(user_id,job,recId,hobby,blood) values('test1','j001','','h005','A');

insert into memberOther(user_id,job,recId,hobby,blood) values('test2','j010','test1','h003','A');

insert into memberOther(user_id,job,recId,hobby,blood) values('test3','j005','test5','h003','O');

insert into memberOther(user_id,job,recId,hobby,blood) values('test4','j008','test5','h001','AB');

insert into memberOther(user_id,job,recId,hobby,blood) values('test5','j005','','h010','B');

@memberLevInfo데이터 입력

insert into memberLevInfo(user_id,level,levelDayS,levelDayE,meetingHit) values('test1','A','20150101','20151231','10');

insert into memberLevInfo(user_id,level,levelDayS,levelDayE,meetingHit) values('test2','A','20150203','20150202','6');

insert into memberLevInfo(user_id,level,levelDayS,levelDayE,meetingHit) values('test3','B','20150506','2015105','2');

insert into memberLevInfo(user_id,level,levelDayS,levelDayE,meetingHit) values('test4','B','20150525','2015124','3');

insert into memberLevInfo(user_id,level,levelDayS,levelDayE,meetingHit) values('test5','C','20150801','20150831','0');

@membership 출력

-membership 전체 출력

select * from membership;

-membership 전체 갯수 출력

select count(*) from membership;

-membership중 gender가 M인 사람 출력

select * from membership where gender="M";

-membership에서 1990년도 이후 출생자 출력

select user_name from membership where birth>=19900101;

-membership에서 1990년생 출생자 출력

select user_name from membership where birth>=19900101 AND birth<19910101;

-membership에서 1990년생 출생자 출력

select user_name from membership where birth BETWEEN 19900101 AND 19901231;

@memberOther출력

-memberOther 전체 출력

select * from memberOther;

-memberOther 아이디와 직업코드 출력

select user_id, job from memberOther;

-memberOther 전체 갯수 출력

select count(*) from memberOther;

-memberOther 혈액형AB인 사람 출력

select * from memberOther where blood ='AB';

-memberOther 직업코드가 j001이고 혈액형이 A인 사람 출력

select * from memberOther where job='j010' and blood='A';

-memberOther 아이디와 추천인 출력

select user_id,recId from memberOther;

-memberOther 혈액형AB,A 인 사람을 아이디와 membership의 이름 출력 

select user_id as '아이디',(select user_name from membership where user_id=a.user_id) as '이름', blood as '혈액형' from memberOther a where blood='AB' or blood='A';

-memberOther 혈액형AB,A 인 사람을 아이디와 membership의 이름 출력 

select user_id as '아이디',(select user_name from membership where user_id=a.user_id) as '이름', blood as '혈액형' from memberOther a where blood in('AB','A');

@@여러가지 출력1

-membership 이름에 김씨를 출력

select * from membership where user_name like '김%';

-memberOther에 혈액형이 중복되지 않도록 출력

select distinct(blood) from memberOther;

-memberOther에 생일이 30일인 사람 출력

select *from membership where right(birth,2)='30'; //오른쪽에서부터 2번째까지 

-memberOther에 생년이 1990년인 사람 출력

select *from membership where left(birth,4)='1990'; //왼쪽에서부터 4번째까지

-test1님의 혈액형은 A형 입니다. 형태로 출력

select concat(user_id, '님의 혈액형은',blood, '형입니다.') as idBlood from memberOther;

-test(A) 형태로 출력

select concat(user_id,'(',blood,')') as idBlood from memberOther;

-memberOther를 2번부터 2개출력

select *from memberOther limit 1,2;

-membership의 아이디와 memberOther의 혈액혈을 같이 출력

select user_id from membership UNION select blood from memberOther;

@@여러가지 출력2

-아이디 test1의 meetingHit를 1증가

update memberLevInfo set meettingHit=meetingHit+1 where user_id="test1";

-membeLevInfo에 전체갯수 출력

select count(*) from memberLevInfo;

-memberLevInfo를 level 순정렬로 첫번째부터 2개 출력

select * from memberLevInfo order by level asc limit 0,2;

select * from memberLevInfo order by level asc limit 2,2;

select * from memberLevInfo order by level asc limit 4,2;

-memberLevInfo를 meetingHit 역순정렬로 출력

select * from memberLevInfo order by meetingHit desc; 

-아이디별 기간

------------

test1(A):20140705~20150704

형태로 출력

select concat(user_id,'(',level,')',':',levelDayS,'~',levelDayE) as '아이디별 기간' from memberLevInfo;

-test1의 기간을 20140701~20150630으로 변경

update memberLevInfo set levelDayS='20140701',levelDayE='20150630' where user_id='test1';

-현재날짜와 비교해 남은 기간 출력

select *from memberLevInfo where levelDayE<now();

-아이디별 마지막 날짜

-----------------

test1:20150630

형태로 출력

select concat(user_id,':',date_format(levelDayE,"%Y/%m/%d")) as '아이디별 마지막 날짜' from memberLevInfo;

-아이디별 남은날짜 출력

select concat(user_id,'님의 남은 날짜는',datediff(levelDayE,now()),'일 입니다.') as '아이디별 남는 날짜' from memberLevInfo;

-아이디와 이름 남은 날짜를 출력

select concat(user_id,'(',(select user_name from membership where user_id=a.user_id),')','님의 남은 날짜는',datediff(levelDayE,now()),'일 입니다.')as 'receive_date' from memberLevInfo a;

-level이 A인 사람의 이름을 출력

select (select user_name from membership where user_id=a.user_id) from memberLevInfo a where level='A';

-아이디,이름 ,성별을 출력하되 성별은 M일때 남 아니면 여를 출력

select  user_id, user_name if(gender='M','남','여') from membership;