mysql 테이블 데이터

테이블 데이터

데이터 입력하기

INSERT INTO 테이블 이름(필드명) VALUES(데이터);

INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('ik6623@naver.com','박종호','1234qwer','19950909','28','01040976623','10293857');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('webstoryboy@naver.com','황상연','12dfsdecv','19920405','33','01039858583','578365839');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('jungsik321@naver.com', '김정식', '1234', '19930209', '30', '01025738580', '20220314');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('rmsgur2275@gmail.com','박근혁','alcls2860','19980611','25','01058122860', '12345678');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('apdlvmf8044@gmail.com', '송준섭', 'qweasdf', '19931120', '30', '01088648044', '21846351');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('sbxjs6999@naver.com','박근희','1234qwer','19970214','26',01041046999,'12345678');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('seongwoo@lycos.com','박성우','123456asd','19910506','32', '01044779156','54216451');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('dnwls7738@naver.com','천우진', '12asdf', '19960324', '27', '01055805274', '45678941');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('dnstjq6305@naver.com', '조운섭', 'qweasdf', '19911110', '32', '01024068904', '12345678');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('lhw1847@gmail.com','이현우','1234','19971221','26', '01055332287', '0303');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('dhtpfls321@naver.com', '오세린', '1234', '19980214', '25', '01032229944', '12345678');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('GarenChoi@naver.com','최근영','1234','19970117','26','01054651345','234');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('sna12345@naver.com', '배석찬','1234','19960727','27','01033125279','03270327');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('sbxjs6999@naver.com','박근희','1234qwer','19970214','26',01041046999,'12345678');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('wwuu123@naver.com','최가영','12345678','19970506','26','01066179991','12345678');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('illie@naver.com', '류성일', 'ryuPass', '19940426', '29', '01055327391', '1440');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('jsjh442@naver.com','박지선','1234','19980423','25','01057421662','12341234');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('ohwehoh@naver.com','이연우','dldus1358','19950720','28','01014141441','12341234');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('go_goa@naver.com' ,'김현우', '159753', '19920126', '31', '01087361836', '40250371');
INSERT INTO myMember(youEmail, youName, youPass, youBirth, youAge, youPhone, regTime) VALUES('placidcy@gmail.com', '김지섭', '1234', '19900212', '32', '01011112222','20220314');
20 rows in set (0.00 sec)

데이터 불러오기

전체 데이터 불러오기

SELECT * FROM 테이블명;

SELECT * FROM myMember;

조건 데이터 불러오기

SELECT * FROM 테이블명 WHERE 조건;

SELECT * FROM 테이블명 WHERE 조건;

회원 아이디가 10번인 경우

SELECT * FROM myMember WHERE memberID = 10;

회원 아이디가 10번이 아닌경우 경우

SELECT * FROM myMember WHERE memberID <> 10;

회원 아이디가 5~10번인 경우

SELECT * FROM myMember WHERE memberID >=5 AND memberID < 11;

범위 조건 데이터 불러오기

SELECT * FROM 테이블명 WHERE 칼럼명 BETWEEN 시작 값 AND 끝 값;

회원 아이디가 5~10번인 경우

SELECT * FROM myMember WHERE memberID BETWEEN 5 AND 10;

회원 아이디가 5~10번이 아닌 경우

SELECT * FROM myMember WHERE memberID NOT BETWEEN 5 AND 10;

해당 조건 데이터 불러오기

SELECT * FROM 테이블명 WHERE 칼럼명 IN(값1, 값2, 값3, .....);

SELECT * FROM myMember WHERE memberID IN(4, 6, 13, 14);

패턴 조건 데이터 불러오기

SELECT * FROM 테이블명 WHERE 칼럼명 LIKE;

이메일 중에 naver 텍스트가 포함된 경우

SELECT * FROM myMember WHERE youEmail LIKE '%naver%';

이메일 중에 web 텍스트로 시작되는 경우

SELECT * FROM myMember WHERE youEmail LIKE 'web%';

이메일 중에 com 텍스트로 끝나는 경우

SELECT * FROM myMember WHERE youEmail LIKE '%com';

이름 중에 '김'으로 시작되는 멤버를 출력

SELECT * FROM myMember WHERE youName LIKE '김%';

이름 중에 '김' 이거나 '황'씨인 경우의 멤버를 출력

SELECT * FROM myMember WHERE youName LIKE '김%' or youName LIKE '황%';

이름 중에 중간 글자가 '근'인 경우의 멤버를 출력 (%문자열 , _단일 1개의 글자)

SELECT * FROM myMember WHERE youName LIKE '_근_';

SELECT * FROM myMember WHERE youName LIKE '__연';

NULL 데이터 불러오기

SELECT * FROM 테이블명 WHERE 칼럼명;

SELECT * FROM myMember WHERE youEmail IS NULL;

SELECT * FROM myMember WHERE youEmail IS NOT NULL;

논리 연산자 데이터 불러오기

SELECT * FROM 테이블명 WHERE 칼럼명 연산자;

나이가 30살 이상이거나 또는 (or) 김씨인 멤버

SELECT * FROM myMember WHERE youAge >= 30 || youName LIKE '김%';

나이가 30살 이상 이면서(AND) 김씨인 멤버

SELECT * FROM myMember WHERE youAge >= 30 && youName LIKE '김%';

멤버아이디가 2 또는 10 그리고 나이가 30이상인 경우

SELECT * FROM myMember WHERE (memberID=2 OR memberID=10) AND youAge >= 30;

데이터 갯수 불러오기

SELECT * FROM 테이블명 LIMIT 표시할 수;

SELECT * FROM myMember LIMIT 12;

SELECT * FROM myMember LIMIT 10, 20;

SELECT youEmail FROM myMember LIMIT 10, 20;

데이터 불러오기(내림차순/오름차순)

SELECT * FROM 테이블명 ORDER BY 컬럼명 정렬방식(DESC/ASC);

SELECT * FROM myMember ORDER BY youAge DESC;

SELECT * FROM myMember ORDER BY youAge ASC;

데이터 수정하기

UPDATE 테이블명 SET 칼럼명 조건;

모든 회원의 비밀번호를 '1234' 변경

UPDATE myMember SET youPass = "1234";

아이디 번호가3 인사람 비밀번호를 '1234' 변경

UPDATE myMember SET youPass = "1234" WHERE boardID = 3;

5번 회원부터 10번회원까지의 비밀번호를 '5678' 변경

UPDATE myMember SET youPass = "5678" WHERE memberID BETWEEN 5 and 10;

아이디 내림차순 기준(11~20번)으로 비밀번호를 '****'로 변셩

UPDATE myMember SET youPass = "****" ORDER BY memberID DESC LIMIT 10;

데이터 삭제하기

전체 데이터 삭제

DELETE FROM 테이블명 or DELETE * FROM 테이블명;

DELETE FROM myMember;

조건 데이터 삭제

DELETE FROM 테이블명 조건;

조건 삭제: 회원 아이디가 5번인 멤버를 삭제

DELETE FROM myMember WHERE memberID = 5;

조건 삭제: 회원 패스워드가 '****'를 삭제

DELETE FROM myMember WHERE youPass = '****';

정렬 삭제: 이름을 기준으로 5번째까지 삭제

DELETE FROM myMember ORDER youPass LIMIT 5;

지정 삭제: 이름을 기준으로 5번째까지 삭제

DELETE FROM myMember WHERE memberID IN(8,9,10);

 

테이블 데이터 입력 불러오기 수정 삭제입니다!