클라우드 51일차

어제 했던 내용

프로시져 if -> case -> while

제약조건 -> primary key, foreign key

지정한 조건에 부합하는 데이터만 입력되도록

birthyear : int : [1111-2022]

not null -> 값을 입력하지 않으면 기본값 부여 -> DEFAULT 생년 -> -1, 지역 -> 서울

 

 

DB에서 변수 선언, 값 입력

SET @var1=5;  -- "@"를 이용하면 선언, 입력이 같이 된다.

 

join 은 두 개 이상의 테이블간 관계를 형성하여 유의미한 데이터의 확인, 출력이 가능하다.

join 시에는 기준테이블을 지정하고 해당 기준 테이블에 보조 테이블을 연결하는 형식으로 구성한다.

 

- inner join (일반적으로 join 이라고 하면 inner join을 의미한다. 또한 구성시 inner join 이라고 하지 않고 그냥 join 이라고만 입력해도 이는 inner join으로 구성된다.)

: 두 테이블간 join 되었을 때 두 테이블에 모두 존재하는 데이터만 출력된다.

 

- outer join

한쪽 테이블에만 존재하는 데이터는 

 

usertbl에서 ID를 이용해서 PK를 선언할 수 있다.

 

????

 

PK는 무조건 1개의 열로 구성되는게 아닌 두 개 이상으로도 가능하다. 

 

문제는 제품을 누가 샀는지 알 수 있어야 하기 떄문에 buybtl도 usertbl의 ID를 적어주어야 한다.

이것만으로는 PK를 처리할 수 없기 때문에 주문번호 num을 auto increment로 지정해주어야한다. 이 번호는 PK이 이면서 unique 해야 한다.

 

????

 

join을 위해서는 제품번호를 PK로 지정하고 다른 테이블에서 정보를 찾기 위해 FK를 buytbl에서 받아온다.

 

이때 inner join은 서로 다른 테이블간의 같은 성분을 엮어서 만든다.

 

[ self join ]

실제 두개 이상의 테이블을 이용하는 것이 아니라 하나의 테이블을 자신에게 연결하여 원하는 데이터를 추출할 수 있다.

 

---

 

현재 DB에는 테이블이 두개가 있다.

usertbl -> U

buytbl -> B

 

use sqldb;
select B.userid, U.name, U.addr, CONCAT(U.mobile1, U.mobile2) as '휴대폰번호', B.prodname, B.amount
	FROM buytbl B
		INNER JOIN usertbl U
			ON B.userid = U.userid;

use sqldb;
DROP VIEW IF EXISTS v_check;
CREATE VIEW v_check AS 
select B.userid, U.name, U.addr, CONCAT(U.mobile1, U.mobile2) as '휴대폰번호', B.prodname, B.amount
FROM buytbl B
    INNER JOIN usertbl U
        ON B.userid = U.userid;
select * from v_check;

아예 VIEW를 만들어서 빠르게 처리해주자

 

use sqldb;
select U.name, B.prodname
	FROM usertbl U
		LEFT OUTER JOIN buytbl B
			ON U.userid=B.userid
    WHERE B.prodname is null
    ORDER BY U.userid;

OUTER JOIN으로 DB를 엮을 수도 있다.

 

SQL 프로그래밍

SQL 에서도 다른 프로그래밍 언어와 비슷한 분기, 흐름제어 반복의 기능이 있다. 이러한 기능을 변수와 함께 잘 사용한다면 유용한 SQL 프로그래밍이 가능하다.

 

스토어드 프로시저 -> 사용자 함수

 

함수를 미리 만들어 놓으면 나중에 다른 위치에서 불러 사용이 가능해진다.

 

번외)

라이브러리 파일을 touch를 이용해서

touch mylib.sh

 

a.sh

. ./mylib.sh  # 현재의 위치로 내가 있는 디렉토리하의 mylib.sh를 import 하겠다.

 

????

대충 위와같이 작성해주면된다.

 

이번에는 employees DB 의 employees 테이블을 사용하여 직원번호에 10001 번에 해당하는 직원의 입사일이 5년이 넘었는지를 살펴보자.

 

오늘날-입사일 -> 날짜로 출력(두 날짜 사이의 일치)

 

이를 위해서 CURRENT_DATE()와 CURDATE() 두 PROCEDURE을 알아야 한다.

 

두 프로시저는 날짜를 받아올 수 있지만 이걸 또 분리해서 년도/월/일만 받아올 수 있다.

 

2022년 -> 짝수년 -> 생년 마지막 숫자가 짝수라면 올해 건강검진, 홀수라면 내년 건강검진.

use sqldb;
drop procedure if exists test;

delimiter //
create procedure test()
begin
	declare thisyearvalue INT; -- 올해의 짝홀수 여부
	declare birthvalue INT; -- 이승기의 생년
    SET thisyearvalue = YEAR(CURDATE())%2 ;

	select birthyear INTO birthvalue
		from usertbl WHERE name = '이승기';

	if (birthvalue%2) = thisyearvalue 
	THEN
		SELECT '이승기','올해' ;
	ELSE
		SELECT '이승기','내년' ;
	end if;
end //
delimiter ;
call test();

 

CASE

다른 프로그래밍에서와 동일하게 CASE는 ‘다중분기’ 의 기능을 수행한다. 아래의 예는 CASE를 활 용하여 만든 학점계산 프로그램이다.

.

---

 

 

usertbl로 가서 조관우, 이승기의 birthyear을 NULL로 바꾸자

 

이제 짝수년도는 올해, 홀수년도는 내년, 나머지는 정보없음으로 바꾸는 쿼리를 짜보자

use sqldb;
SELECT name, birthyear,
   (CASE WHEN birthyear is null THEN '정보없음'
        WHEN birthyear %2 = 0 THEN '올해'
        ELSE '내년'
    END) AS '건강검지'
FROM usertbl;

 

[ while ]

while 문 사용도 가능하다

 

[ 제약 조건 ]

- 데이터의 무결성 유지를 위한 제약 조건

- PK(기본 키), FK(외래 키)

 

외래키에 두개의 키를 넣어주자

x테이블 생성된 이후 PK를 지정하고자 한다면?

ALTER TABLE testtest ADD PRIMARY KEY(prodnum);

 

CREATE DATABASE keydb;
USE keydb;
CREATE TABLE usertbl (
	userid CHAR(8) NOT NULL PRIMARY KEY,
    name CHAR(10) NOT NULL,
    birthyear INT NOT NULL
);

CREATE TABLE buytbl (
	num INT AUTO_INCREMENT PRIMARY KEY, -- 자동으로 not null
    userid CHAR(8) NOT NULL,
    prodname CHAR(6) NOT NULL,
    FOREIGN KEY(userid) REFERENCES usertbl(userid)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

 

위의 경우에는 주테이블의 userid가 변경되거나 삭제될 경우에 FK 테이블의 userid에는 영향이 없다.

 

그리고 buytbl에 ON UPDATE CASCADE, ON DELETE CASCADE를 작성해주어서 주테이블 id가 바뀌거나 삭제될때 함께 바뀌도록 만들어주자.

 

만약에 CASCADE의 추가적인 설정을 원한다면

ALTER TABLE buytbl ADD ON UPDATE CASCADE;

 

[ unique ]

는 PK 와 동일하게 겹쳐서는 안되는 정보이나 NULL이 허용됨

또한 한 테이블 내에서 여러번 사용가능

가령, userid는 PK, 주민등록 번호, 휴대폰번호, 이메일은 UNIQUE를 사용

 

CREATE TABLE USERTBL (

userid CHAR(5) PRIMARY KEY,

name VARCHAR(10),

email CHAR(30) NULL UNIQUE

);

 

[ check ]

데이터 입력시 정책, 룰을 적용하여 원하는 데이터의 입력만 가능하도록 할 수 있다. 예를 들어 회원가입시 휴대폰 번호 앞자리는 010, 011~019 까지만 입력되도록 할 수 있다.

 

DROP DATABASE IF EXISTS keydb;
CREATE DATABASE keydb;
USE keydb;
CREATE TABLE usertbl (
	userid CHAR(8) NOT NULL PRIMARY KEY,
    name CHAR(10) NOT NULL,
    birthyear INT CHECK(birthyear >= 1900 AND birthyear <= 2022),
    mobile1 CHAR(3) CHECK(mobile1 IN ('010', '011','019'))
);

db를 새로 만들고 한번 데이터를 다르게 넣어보자

 

만약 깜빡하고 못넣었다면 아래의 방법을 사용하자

SELECT * FROM sqldb.usertbl;
ALTER TABLE usertbl ADD CONSTRAINT CHECK (birthyear >= 1900 AND birthyear <= 2000);
ALTER TABLE usertbl ADD CONSTRAINT CHECK (mobile1 IN ('010','011'));

이딴식으로 데이터를 넣을려고 하니 막힌다.

 

먼저 CentOS DB에 DB를 만들어주자

drop database if exists sqldb;
create database sqldb;
use sqldb;
create table usertbl
( userid char(8) not null primary key,
name varchar(10) not null,
birthyear int not null,
addr char(2) not null,
mobile1 char(3),
mobile2 char(8),
height smallint,
mdate date );

create table buytbl (
num int auto_increment not null primary key,
userid char(8) not null,
prodname char(6) not null,
groupname char(4),
price int not null,
amount smallint not null,
foreign key (userid) references usertbl(userid)
);


use sqldb;
insert into usertbl values ('lsg','이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
insert into usertbl values ('kbs','김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
insert into usertbl values ('kkh','김경호', 1971, '전남', '011', '3333333', 177, '2007-7-7');
insert into usertbl values ('jyp','조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
insert into usertbl values ('ssk','성시경', 1979, '서울', null, null, 186, '2013-12-12');
insert into usertbl values ('ljb','임재범', 1963, '서울', '011', '6666666', 182, '2009-9-9');
insert into usertbl values ('yjs','윤종신', 1969, '경남', null, null, 170, '2005-5-5');
insert into usertbl values ('ejw','은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
insert into usertbl values ('jkw','조관우', 1965, '경기', '011', '9999999', 172, '2010-10-10');
insert into usertbl values ('bbk','바비킴', 1973, '서울', '011', '0000000', 176, '2013-5-5');

insert into buytbl values (null, 'kbs', '운동화', null, 30, 2);
insert into buytbl values (null, 'kbs', '노트북', '전자', 1000, 1);
insert into buytbl values (null, 'jyp', '모니터', '전자', 200, 1);
insert into buytbl values (null, 'bbk', '모니터', '전자', 200, 5);
insert into buytbl values (null, 'kbs', '청바지', '의류', 50, 3);
insert into buytbl values (null, 'bbk', '메모리', '전자', 80, 10);
insert into buytbl values (null, 'ssk', '책', '서적', 15, 5);
insert into buytbl values (null, 'ejw', '책', '서적', 15, 2);
insert into buytbl values (null, 'ejw', '청바지', '의류', 50, 1);
insert into buytbl values (null, 'bbk', '운동화', null, 30, 2);
insert into buytbl values (null, 'ejw', '책', '서적', 15, 1);
insert into buytbl values (null, 'bbk', '운동화', null, 30, 2);

 

mysql -u root -p'TEst!234'

 

DB 접속후 이제 일을 처리해보자

 

alter user 'root'@'localhost' identified with mysql_native_password by 'TEst!234';

alter user 'root'@'%' identified with mysql_native_password by 'TEst!234';

flush privilesges;

이렇게 하면 원격과 로컬 접속이 가능해진다.

flush privileges -> 권한 등록해주기

 

yum install -y httpd ; cd /var/www/html

yum install -y php php-mysql

<!DOCTYPE html>
<html lang="ko">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<title>Check DB</title>
</head>
<body>
	<center>
		<h2>DB 연결 상태 확인 하기</h2>
		<a href="check.php">클릭하세요</a>
	</center>
	<?php
		echo "위의 링크를 클릭하세요";
	?>
</body>
</html>

vi index.php

<?php 
	echo "mysql test"
?>

vi check.php

 

파일 두개를 만들고 index.php로 연결해보자

 

 

[PHP] echo, print 설명과 비교 – 블로그채널 (tistory.com)

[PHP] echo, print 설명과 비교

<!DOCTYPE html>
<html lang="ko">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<title>Signup</title>
</head>
<body>
  <h1>회원가입페이지</h1>
	<form method="POST" action="signup.post.php">
		<p>
				아이디(이메일) : 
				<input type="text" name="userid" />
		<p>
		<p>
				이름 : 
				<input type="text" name="name" />
		<p>
		<p>
				출생년도 : 
				<input type="text" name="birthyear" />
		<p>
		<p>
				지역 : 
				<input type="text" name="addr" />
		<p>
		<p>
				휴대폰앞자리(010,011,019) : 
				<input type="text" name="mobile1" />
		<p>
		<p>
				휴대폰뒷자리 : 
				<input type="text" name="mobile2" />
		<p>
		<p>
				신장(키) : 
				<input type="text" name="height" />
		<p>
		<p><input type="submit" value="가입하기"></p>
	</form>
</body>
</html>

<?php
$con=mysqli_connect("localhost","root","TEst!234","sqldb") or die("접속실패");
        $userid=$_POST["userid"];
        $name = $_POST["name"];
        $birthyear = $_POST["birthyear"];
        $addr = $_POST["addr"];
        $mobile1 = $_POST["mobile1"];
        $mobile2 = $_POST["mobile2"];
        $height = $_POST["height"];
        $mdate = date("Y-m-d");

        $sql="INSERT INTO usertbl VALUES('".$userid."','".$name."',".$birthyear.",'".$addr."','".$mobile1."','".$mobile2."',".$height.",'".$mdate."')";

        $ret = mysqli_query($con,$sql);

        echo "<h2> 가입 처리 결과 </h2>";

        if($ret) {
                echo "데이터 입력 완료";
        }
        else {
                echo "데이터 입력 실패"."<br>";
                echo "실패원인 : ".mysqli_error($con);
        }
        mysqli_close($con);

        echo "<br><a href='join.php'>[가입페이지로 이동]</a>";

?>

문제는 위와같이 짜주면 글자가 꺠진다.

 

추가적으로 birthyear은 1900이상 2000 이하로 하고

mobile1은 010,011만 해당되도록 만들라고 한다.

 

SELECT * FROM sqldb.usertbl;
ALTER TABLE usertbl ADD CONSTRAINT CHECK (birthyear >= 1900 AND birthyear <= 2000);
ALTER TABLE usertbl ADD CONSTRAINT CHECK (mobile1 IN ('010','011'));

 

을 DB에서 입력해주자

 

제약조건이 뜬다.

같은 데이터 입력 못하게 막았다.