본문 바로가기

mysql41

우분투 mysql에 외부에서 접속하기 우분투 mysql에 외부에서 접속하기 가비아에서 클라우드 서버를 쓰고 있는데 ip/phpmyadmin 들어가서 쿼리 작업하기가 많이 불편했다. 이참에 나의 맥북으로 원격으로 접속할 수 있게 설정을 해보았다. 사진위주로 정리해보았다. 1.나의 맥북 터미널로 ssh 서버 원격 접속!2. etc/mysql/ 위치로 가서 vi my.cnf 열기~!3.bind-address 부분 주석처리 #bind-address ! 이렇게!4.sudo service mysql restart - 재시작! 5. mysql -uroot -p 접속6.select user, host from mysql.user (유저와 호스트 확인)7. % 이렇게 퍼센트로 기입된 host가 모든 host 허용이라는 뜻(나는 이미해놨음)8.사진에 crea.. 2018. 6. 13.
10 연습문제 적어도 한번 이상 장학금을 받은 학생의 학번을 출력하라"select stu_nofrom fee where jang_total is not null;" 1,500,000원 이상 장학금을 받은 학생의 학번과 이름을 출력하라"select distinct stu_no, jang_totalfrom fee where jang_total >= 1500000;" "select distinct student.stu_no, student.stu_name, jang_totalfrom fee, studentwhere fee.stu_no = student.stu_noand jang_total >= 1500000group by student.stu_no "성별이 남자가 아닌 학생의 학번과 이름을 출력하라"select * from.. 2017. 11. 15.
9 mysql 서브쿼리 부속질의 부속 질의어는 in 연산자 다음에 사용할 수도 있고, 관계 연산자 다음에도 사용할 수도 있다.수강신청을을 한 학생의 학번과 이름을 출력하라."select stu_no, stu_name from student where stu_no in (select stu_no from attend where att_div = 'Y');""=" 연산자는 부속 질의어가 항상 정확히 하나의 값만을 반환할 때 유효하다김유미 1983년생 보다 나이가 더 많은 각 학생의 학번과 이름, 주민등록번호를 출력하라."select stu_no, stu_name, id_num from studentwhere birth_year < (select birth_year from student where stu_name = '김유미')"부속질의어.. 2017. 11. 14.
5 mysql 함수 & 문제 1학년 신입생의 경우에만 입학금을 내고 재학생의 경우에는 입학금을 내지 않으므로 등록금 총액은 입학금 (fee_enter) + 수업료 (fee_price) 가 된다. 그러나 입학금이 null 인경우에는 가산을 할 수 없으므로ifnull (fee_enter,0) 과 같이 ifnull 함수를 사용하여 null 값을 0으로 변환하여 가산을 할 수 있다.등록금의 총액을 변경하여라use haksa;select * from fee;update fee set fee_total = ifnull(fee_enter, 0) + fee_price;납입금 총액은 등록금 총액 - 장학금 총액 이다. 납입금 총액을 변경하라update fee set fee_pay = fee_total - ifnull(jang_total,0);- 영.. 2017. 11. 10.
4 mysql 테이블 생성 create table 테이블 생성alter table 테이블과 열 변경drop table 테이블 삭제rename 테이블 이름 변경새로운 테이블의 생성#우편번호테이블create table post(post_no varchar(7) Not null, #우편번호post_dong char(30) Not null, #동이름post_address char(60) Not null, #주소ddd char(4), #DDD 전화지역번호primary key (post_no))engine = innoDB; 다음은 학사 데이터 베이스에서 post 테이블을 생성하는 create table 명령문의 예제를 보여주고 있다.테이블의 이름은 post이며, 이 이름은 학사 데이터베이스내에 포함되어 있는 모든 테이블에 대하여 유일한 이름.. 2017. 11. 9.
3 mysql 데이터 타입 및 필수 기능 앞에서 설명한 학사 관리 데이터 베이스를 실제 mysql 상에서 구현하고 이에 필요한 필수적인 기능들에 대해 설명한다.먼저 mysql을 이용하여 데이터 베이스를 생성하기 위해서는 mysql에 데이터 베이스를 생성해야 한다. 하나의 mysql 에는 여러개의 데이터 베이스가 존재할 수 있으며, 각각의 데이터 베이스 이름은 사용자가 지정할 수 있다.데이터 베이스 생성create database haksa;확인show databases;db변경use haksa;인사 테이블 insa 생성 및 데이터 입력create table insa(bunho int(1) auto_increment, name char(8) not null, e_name char(4) not null, town char(6) not null, p.. 2017. 11. 9.
2 학사관리 예제 설명 학사관리에서 사용하는 데이터 베이스로 학생의 신상과 등록, 수강, 성적을 기록하는 데이터베이스에 대하여 설명하고자 한다.- 테이블은 총 9개가 있다.student 학생 신상 테이블attend 수강fee등록테이블score 성적subject 교과목professor교수post 우편circle 동아리department 학과학생테이블은 학생 신상에 관한 데이터로서 이름, 주소, 주민등록번호 같은 내용을 가지고 있다.primary key는 stu_no 학번이고 이 column은 중복 데이터를 생성할 수 없다.테이블은 과거의 데이터를 가지지 않기 때문에, 어떤 학생이 다른 집으로 이사 가면 과거의 주소는 새로운 주소로 대치 된다.다시 말하면, 과거 주소는 다른 곳에서 보유하지 않는다.attend 테이블은 학생의 수강.. 2017. 11. 8.
1 mysql 데이터 생성 삽입 예제 표이름연락처idcity홍길동010-1234-1234102수원이말자010-1234-1235103서울이고순010-1234-1236104대전테이블 생성하기create table example (name varchar(10), phone varchar(15), id varchar(10), city varchar(10));조회select * from example;테이블 확인하기show tables;테이블 다시 생성하기create table example2 (name varchar(10), phone varchar(15), id varchar(10), city varchar(10));이메일 열 추가하기alter table example2 add email varchar(20);테이블 내용 확인하기desc e.. 2017. 11. 8.
예제 소스 CREATE SCHEMA `haksa` ; use haksa; 학사 테이블 만들기#학과테이블create table department(dept_code int(2) Not null, #학과번호dept_name char(30) Not null, #학과명dept_ename varchar(50), #학과영문이름Create_date date default null, #학과생성날짜primary key (dept_code))engine = innoDB; #우편번호테이블create table post(post_no varchar(7) Not null, #우편번호post_dong char(30) Not null, #동이름post_address char(60) Not null, #주소ddd char(4), #DDD 전화.. 2017. 10. 30.
우분투 16.04 vmware&iptime 포트포워딩 후 윈도우 workbench에서 우분투 접속 우분투 16.04 vmware&iptime 포트포워딩 후 윈도우 workbench에서 우분투 접속 #iptime 포트 열기 192.168.0.1로 접속후3306 포트를 접속 허용 해준다. #컴퓨터 방화벽을 풀어주거나 3306포트만 열어준다. #vmware -> edite -> network에서 포트포워딩 3306 #공인ip로 window워크밴치에서 접속 2017. 7. 26.
<실습> 3.mysql join&outer join & 프로그래밍 -- join-- inner join 은 두테이블 양쪽에 다 데이터가 있는 것만 출력 한다. select *from buyTbl inner join userTbl -- userTbl 과 join 해라 on buyTbl.userID = userTbl.userID -- 설정해주기WHERE buyTbl.userID = 'JYP'; -- 오류 예)-- 전체를 다 출력할때는 해당 칼럼이 어느 테이블의 칼럼인지 명시해 줘야 한다.select userID, name, prodName, addr, mobile1 + mobile2 as '연락처'from buyTbl inner join userTbl -- userTbl 과 join 해라 on buyTbl.userID = userTbl.userID; -- 설정해주기 -- .. 2017. 5. 12.
<실습> 2.mysql 내장함수(날짜,문자,숫자,JSON 등) 정리 -- 데이터 형식 -- 문자 데이터 형식 -- 날짜와 시간 데이터 형식 -- MYSQL 고급 / 내장함수 및 JSON 예제 -- 날짜 함수select cast('2020-10-19 12:23:21.123' AS DATE) as 'DATE' ;select cast('2020-10-19 12:23:21.123' AS TIME) as 'TIME' ;select cast('2020-10-19 12:23:21.123' AS DATETIME) as 'DATETIME' ; -- 변수SET @myVar1 =5;SET @myVar2 =3;SET @myVar3 =4.25;SET @myVar4 ='가수이름=>'; select @myVar1;select @myVar2 + @myVar3;select @myVar4, Name f.. 2017. 5. 11.
<실습> 1.mysql 기본 drop database if exists sqlDB; -- 만약 sqlDB가 있으면CREATE database sqlDB; use sqlDB; create table userTbl -- 회원 테이블( userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 ID (PK) name varchar(10) NOT NULL, -- 이름 birthYear INT NOT NULL, -- 출생년도 addr char(2) not null, -- 지역 mobile1 char(3), -- 폰번호 앞자리 3 자리 mobile2 char(8), height SMALLINT, -- 키 mDate DATE -- 가입일); create TABLE buyTbl -- 회원 구매 테이블 (( num int aut.. 2017. 5. 10.
MYSQL 트리거&TRIGGER 예제&문제 17 트리거 트리거는 테이블에 삽입 , 수정, 삭제등의 작업을 할때 자동으로 작동되는 개체로 프로시저와 비슷한 모양을 갖는다.하지만 트리거에는 스토어드 프로시저와 달리 IN OUT 매개변수를 사용할 수도 없다. -- 테이블 생성 SELECT * FROM testTbl; create TABLE IF NOT EXISTS testTbl5(id INT, txt VARCHAR(10)); INSERT INTO testTbl5 VALUES(1,'EXID'); INSERT INTO testTbl5 VALUES(2,'AFTERS'); INSERT INTO testTbl5 VALUES(3,'IOI'); DROP TRIGGER IF EXISTS testTrg; DELIMITER //CREATE TRIGGER testTrg -- .. 2016. 12. 20.
MYSQL 커서&CURSOR 예제&문제 16 커서 커서는 테이블의 여러행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식 순서 : 파일을 연다 -> 처음 데이터를 읽는다. -> 파일의 끝까지 반복한다. -> 파일을 닫는다. 명령어 커서선언 DECLARE CURSOR 반복조건 선언 DECLARE CONTINUE HANDLER(더이상 읽을 행이 없을 경우에, 실행할 내용 설정) 커서 열기 OPEN 커서에서 데이터 가져오기 FETCH(LOOP ~ END LOOP 문으로 반복 구간 지정 ) 데이터 처리 커서닫기 -- 예제 테이블 생성 및 데이타 생성 CREATE TABLE Member ( userid VARCHAR(20), `point` INT) ENGINE = InnoDB ROW_FORMAT = DEFAULT; insert into.. 2016. 12. 20.
MYSQL 뷰&VIEW 예제&문제 15 MySQL 뷰 생성CREATE VIEW 뷰이름AS SELECT문 -- student 테이블로부터 모든 학생의 학번과 학년, 반을 가지고 있는 뷰 테이블을 생성하라 create view student_1as select * from student; select * from student_1; -- 등록한 학생의 학번과 등록년도에 대한 뷰테이블을 생성하라-- create view student_2as select s.stu_no, a.att_yearfrom student s, attend awhere s.stu_no = a.stu_no; select * from student_2; create view v_feeas select stu_no, fee_yearfrom feewhere fee_year is no.. 2016. 12. 19.
MYSQL 스토어드 함수 예제&문제 14 스토어드 함수 사용자가 직접 만들어서 사용하는 함수를 스토어드 함수라고 한다. 개요 DELIMITER $$CREATE FUNCTION NAME (PARAMETER)RETURNS 반환값; BEGIN 이 부분에 프로그래밍 코딩 ;RETURN반환값; END $$DELIMITER; SELECT NAME(); - 스토어드 함수는 프로시저와 달리 IN OUT 을 사용할수 없다. - 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다. - 스토어드 함수는 RETURNS문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값을 반환 해야 한다. 스토어드 프로시저는 별도의 반환하는 구문이 없고 OUT 파라미터를 이용해서 값을 반환 할 수 있다. - 스토어드 프로시저는 CALL 로 호.. 2016. 12. 16.
MYSQL procedure&프로시저 예제&문제 13 프로시져는 쿼리문의 집합이다. -- 형태-- -- DELIMITER $$-- -- CREATE PROCEDURE NAME (IN OR OUT PARAMETER)-- BEGIN-- -- SQL QUERY-- -- END $$-- -- DELIMITER ;-- -- CALL NAME(); -- EX) DROP PROCEDURE IF EXISTS STUDENT;DELIMITER //CREATE PROCEDURE STUDENT()BEGINSELECT * FROM STUDENT;END //DELIMITER; CALL STUDENT(); -- ------------------------------- 프로시져의 수정 ALTER PROCEDURE -- 프로시져의 삭제 DROP PROCEDURE -- 배개변수의 이용 I.. 2016. 12. 16.
MYSQL IF ELSE &CASE&동적sql 예제&문제 12 -- SQL PROGRAMING DELIMITER $$ CREATE PROCEDURE NAME()BEGIN SQL CODING END$$ DELIMITER; CALL NAME(); -- --------------------형식 IF THEN SQL 문장 ELSE SQL 문장 END IF; -- EX) DROP PROCEDURE IF EXISTS ifProc;DELIMITER $$CREATE PROCEDURE ifProc()BEGINDECLARE var1 INT; -- 변수 선언 SET var1 = 100; -- 변수에 값 대입 if var1 = 100 then -- 만약 @VAR1이 100 이하라면 select 'it is 100'; ELSE select 'it is not 100';end if;end .. 2016. 12. 15.
MYSQL join&inner join & outer join 예제&문제 11 CREATE table stdTbl(stdName varchar(10) not null primary key,addr char(4) not null); create table clubTbl( clubName VARCHAR(10) not null PRIMARY key,roomNo char(4) not null ); create TABLE stdclubTbl( num int AUTO_INCREMENT not null PRIMARY key,stdName VARCHAR(10) not null,clubName VARCHAR(10) not null,FOREIGN KEY(stdName) REFERENCES stdTbl(stdName),FOREIGN KEY(clubName) REFERENCES clubTbl(clubN.. 2016. 12. 14.
MYSQL join 예제&문제 10 -- 학적 테이블에 존재하는 학생들 중에서 아직 등록을 못한 학생이 있다. 각 학생에 대하여 학번, 이름, 등록년도, 학기, 이름, 등록년도, 학기, 학생이 등록한 납입금 총액을 출력하라 -- (x ???) select student.stu_no, stu_name, fee_year, fee_term, fee_payfrom student, feewhere not EXISTS (select student.stu_no, stu_name, fee_year, fee_term, fee_payfrom student, feewhere student.stu_no = fee.stu_no); -- -- 학적 테이블과 성적테이블을 크로스 조인하여 학번 , 이름, 성적년도, 학기를 출력하라 select student.stu_n.. 2016. 12. 14.
6.나만의 가계부를 만들어 보자 - mysql 날짜 계산 -- 현재시간에서 하루 + 1select date_add(now(), INTERVAL +1 DAY);select date_add(curdate(), INTERVAL +1 DAY);-- 현재시간에서 하루 - 1select date_add(now(), INTERVAL -1 DAY);select date_add(curdate(), INTERVAL -1 DAY);-- 현재시간에서 + 한달select date_add(now(), interval +1 month);-- 현재시간에서 - 한달select date_add(now(), interval -1 month);-- 조건한달전 : where reg_date >= date_add(now(), interval -1 month) 하루전 : where reg_date >.. 2016. 12. 13.
MYSQL 부속질의어 예제&문제 9 -- 적어도 한 번 이상 등록한 학생의 학번과 이름을 출력하라 -- (o)select DISTINCT s.stu_no, s.stu_namefrom student s, fee fwhere s.stu_no = f.stu_no;-- (o)select stu_no,stu_namefrom studentwhere EXISTS(select * from fee where student.stu_no=stu_no);-- 학생중에 동아리의 등급이 일반 회원인 학생의 학번과 이름, 주민번호를 출력하라 -- (o)select s.stu_no, s.stu_name, s.id_num from student s, circle cwhere s.stu_no= c.stu_noand c.president=2;-- (o)select stu_.. 2016. 12. 13.
MYSQL select 명령문의 조합 &union 예제&문제 8 -- select 명령문의 조합 -- 학년이 2, 3 학년인 학생의 학번과 이름을 출력하라select stu_no, stu_name,grade from student where grade in (2 ,3); select stu_no, stu_name,gradefrom studentwhere grade = 2unionselect stu_no, stu_name,gradefrom studentwhere grade = 3 -- 적어도 한번 이상 수강신청을 했거나 등록을 한 학생의 학번을 출력하라 select stu_no from attendunionselect stu_nofrom fee; -- 적어도 한번 동아리에 가입했거나,-- 등록을 하고 그리고 수강신청을 한 학생 중에서-- 이상의 조건 중 둘 또는 세가지.. 2016. 12. 12.
MYSQL order by 예제&문제 7 -- 등록한 학생중 등록일자가 2006에 해당하는 학생의 학번과 등록일자를 출력하라 . 이때 정렬순서는 학번으로 한다. -- select stu_no, fee_datefrom feewhere year(fee_date) = 2006order by stu_no; -- 등록한 학생에 대하여 학번과 전체 등록금의 총액을 출력하라. -- 이 때 출력 순서는 등록금 총액이다. select stu_no, sum(fee_total) a from fee group by stu_no order by a ; select stu_no, sum(fee_total) a from fee group by stu_no order by 2 ; -- -- 학적테이블의 영문이름의 첫 번째 문자가 j보다 큰 아스키 코드로 시작하는 학생의 학.. 2016. 12. 9.
4.나만의 가계부를 만들어 보자 - 최종테이블설계 구현기능 추가 및 정리 가계부 1. 최종 금액 조회 2. 오늘 지출 한 금액 기입3. 과거 지출한 금액 기입 가능4. 오늘 입금된 금액 기입 가능 5. 어제 입금된 금액 기입 가능6.지출한 금액 입금된 금액 바로 최종금액에 반영 7. 주간 별 지출 금액8. 월간 지출 금액9. 연간 지출 금액 10. 지출 카테고리별 지출 금액 (예 식비 100000)11. 지출 카테고리별 주간별 지출 금액 12. 지출 카테고리별 월간별 지출 금액 13. 주간 별 입금 금액14. 월간 입금 금액15. 연간 입금 금액 16. 입금 카테고리별 입금 금액 (예 월급 100000)17. 입금 카테고리별 주간별 입금 금액 18. 입금 카테고리별 월간별 입금 금액 목표 페이지 19.목표 금액 지정 가능 20. 목표 금액 까지 얼마나 .. 2016. 12. 8.
MYSQL Groupby & having 예제 문제 6 -- student 테이블에 있는 학생의 입학년도별 그룹을 출력하라 -- select substring(stu_no,1,4) from student GROUP BY substring(stu_no,1,4); select substring(stu_no,1,4),stu_name from student GROUP BY substring(stu_no,1,4),stu_name; -- 각 입학년도별 총 학생 수를 출력하라 select substring(stu_no,1,4),count(stu_name) from student GROUP BY substring(stu_no,1,4); -- 등록한 학생에 대하여 학번, 등록횟수 , 각 학생이 받은 장학금의 전체 합을 출력하라 select stu_no, count(fee_y.. 2016. 12. 8.
3.나만의 가계부를 만들어 보자 - 잘 작동하는지 테스트 해보기&추가 설계한 테이블을 가지고 값도 넣어보고 조회도 해보고 테스트 해보자 insert into region(R_NAME) VALUES('ZHONGRO');SELECT * FROM region;INSERT INTO MEMBER VALUES('abcnt',1,'1111','kang',1988);select * from member;insert into bank VALUES(null,'abcnt','WOORI',1251144);SELECT * FROM BANK; -- 지출 했을때 쿼리 INSERT INTO OUTCOME VALUES(NULL,1,NOW(),1200,'교통비');SELECT * FROM OUTCOME; -- insert into bank VALUES(null,'abcnt','WOORI',1251144).. 2016. 12. 7.
MYSQL 통계 함수 SUM AVG MAX MIN 예제 문제 5 -- 2007년에 등록한 학생에 대한 학번, 년도, 학기, 장학금액, 납부총액(등록금-장학금),-- 납부금비율(납부총액/등록금*100), %를 출력하시오 SELECT * FROM FEE; SELECT STU_NO, FEE_YEAR, FEE_TERM, (FEE_TOTAL-JANG_TOTAL) AS '납부총액', ((FEE_TOTAL-JANG_TOTAL)/FEE_TOTAL*100) AS '납부금비율'FROM FEEWHERE FEE_YEAR = 2007; -- 등록 테이블에서 등록년도가 2006년인 학생의 학번, 이름을 출력하라 SELECT S.STU_NO, S.STU_NAMEFROM FEE F, STUDENT SWHERE F.STU_NO=S.STU_NOAND FEE_YEAR=2006; -- 등록테이블에서 2.. 2016. 12. 7.
MYSQL any&all&in&예제& WHERE 절 문제4 -- 수강신청을 한 학생의 학번과 이름을 출력하라 select DISTINCT s.stu_no,s.stu_namefrom attend a, student swhere a.stu_no= s.stu_no; select stu_no, stu_namefrom studentwhere stu_no in (select stu_no from attend where att_div='Y'); -- 김유미 (1983)보다 나이가 더 많은 각 학생의 학번과 이름 주민번호를 출력하라 select stu_no, stu_name, id_numfrom studentwhere birth_year (select max(fee_pay) from fee where stu_no=20001015); select DISTINCT stu_no f.. 2016. 12. 6.

티스토리툴바