개발자의 삶

https://cafe.naver.com/thisismysql

위의 링크를 타고 글에 있는 이 링크를 클릭

https://drive.google.com/drive/folders/1KNAvefMzJpAurCj42-kTQmIoOvUTOe4T

movies 를 설치 후

해당 폴더들을 만들고 넣어준다.

그럼 이제 시작을 하기 위해한 자료들은 준비가 된 것이다.

이제 켜져있는 워크벤치를 꺼준다.

그 후 위와 같은 명령들로 해당 파일에 접근

여기서 수정해야 할 곳은 2개가 있다.

동영상을 업로드하기 위해 용량을 바꿔주는 것과

my.ini 파일을 저장후

net stop mysql을 하면 잠시 후 정지가 되고

net start mysql을 하면 다시 실행이 된다.

그리고 나서 워크 벤치를 키고 접속을 하면 적용이 된다.

CREATE DATABASE movieDB;

USE movieDB;
CREATE TABLE movietbl
(movie_id INT,
movie_title VARCHAR(30),
movie_director VARCHAR(20),
movir_star VARCHAR(20),
movie_script LONGTEXT,
movie_film LONGBLOB)
DEFAULT CHARSET = utf8mb4;

INSERT INTO movietbl VALUES (1,'쉰들러 리스트', '스필버그', '리암 니슨',
LOAD_FILE('C:/SQL/Movies/Schindler.txt'),
LOAD_FILE('C:/SQL/Movies/Schindler.mp4'));

SELECT * FROM movietbl;

SHOW variables LIKE 'max_allowed_packet';

SHOW variables LIKE 'secure_file_priv';

use moviedb;
TRUNCATE movietbl;
INSERT INTO movietbl VALUES (1, '쉰들러 리스트', '스필버그', '리암 니슨',
load_file('C:/SQL/Movies/Schindler.txt'), load_file('c:/sql/movies/Schindler.mp4'));
insert into movietbl values(2, '쇼생크 탈출', '프랭크 다라본트', '팀 로빈스',
load_file('c:/sql/movies/Shawshank.txt'), load_file('c:/sql/movies/Shawshank.mp4'));
insert into movietbl values(3, '라스트 모히칸', '마이클 만', '다니엘 데이 루이스',
Load_file('c:/sql/movies/mohican.txt'),load_file('c:/sql/movies/mohican.mp4'));

SELECT movie_script from movietbl where movie_id =1
INTO OUTFILE 'c:/sql/movies/schindler_out.txt'
LINES TERMINATED BY '\\n';

SHOW VARIABLES LIKE 'secure_file_priv';
SELECT * FROM movietbl;

use moviedb;
SELECT movie_film from movietbl where movie_id=3
into dumpfile 'c:/sql/movies/Mohican.mp4';

use sqldb;
create table pivottest
(uName CHAR(3),
season CHAR(2),
AMOUNT INT);
insert into pivotTest VALUES
('김범수', '겨울', 10), ('윤종신', '여름', 15), ('김범수', '가을', 25), ('김범수', '봄', 3),
('김범수', '봄', 37), ('윤종신', '겨울', 40), ('김범수', '여름', 14), ('김범수', '겨울', 22),
('윤종신', '여름', 64);

select * from pivotTest;

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'sqldb' -- 여기에 데이터베이스 이름을 지정하세요
  AND TABLE_NAME = 'pivotTest'; -- 여기에 테이블 이름을 지정하세요

select uName,
	SUM(IF(season = '봄', amount, 0)) as '봄',
    sum(if(season = '여름', amount, 0)) as '여름',
    SUM(IF(season = '가을', amount, 0)) as '가을',
    SUM(IF(season = '겨울', amount, 0)) as '겨울',
    sum(amount) as '합계' from pivottest group by uName;
    
SELECT
    season,
    SUM(IF(uName = '김범수', amount, 0)) AS '김범수',
    SUM(IF(uName = '윤종신', amount, 0)) AS '윤종신'
FROM
    pivottest
GROUP BY
    season;

use sqldb;
SELECT JSON_OBJECT('name', name, 'height', height) as 'json 값'
from usertbl
where height >= 180;

SET @json = '{
    "usertbl": [
        {"name": "임재범", "height": 182},
        {"name": "이승기", "height": 182},
        {"name": "성시경", "height": 186}
    ]
}';

SELECT @JSON;

SELECT JSON_VALID(@JSOON) AS JSON_VALID;
SELECT JSON_SEARCH(@json, 'one', '성시경') AS JSON_SEARCH; -- 여기서 one대신에 all사용가능한데 one은 앞에 있는것 1개를 지칭
SELECT JSON_EXTRACT(@json, '$.usertbl[2].name') as JSON_EXTRACT;
SELECT JSON_INSERT(@json, '$.usertbl[0].mDate', '2009-09-09') AS JSON_INSERT;
SELECT JSON_REPLACE(@json, '$.usertbl[0].name', '홍길동') AS json_replace;
select json_remove(@json, '$.usertbl[0]') AS JSON_REMOVE;



-- JOIN
use sqldb;

SELECT *
FROM buytbl
INNER JOIN usertbl ON buytbl.userid = usertbl.userid
WHERE buytbl.userid = 'jyp';

select *
from buytbl
inner join usertbl
on buytbl.userid = usertbl.userid
order by num;

-- 이렇게하면 userid가 모호해져서 오류가 난다.
select userid, name, prodname, addr, concat(mobile1, mobile2) as '연락처'
from buytbl
inner join usertbl
on buytbl.userid = usertbl.userid
order by name;

select buytbl.userid, name, prodname, addr, concat(mobile1, mobile2) as '연락처'
from buytbl
inner join usertbl
on buytbl.userid = usertbl.userid
order by name;

-- 011-xxx-xxxx로 바꾸기
set @mobile1 = '011';
set @mobile2 = '2222222';
select concat(@mobile1, @mobile2);
-- case1
SELECT CONCAT(@mobile1, '-', left(@mobile2,3),'-', right(@mobile2,4));
-- case2
SELECT CONCAT(@mobile1, '-', SUBSTRING(@mobile2, 1, 3), '-', SUBSTRING(@mobile2, 4)) AS formatted_mobile;

테이블을 두개 만들어 Foreign Key를 이용해 데이터를 연결 시킬 것이다.

레퍼런스가 될 buyTbl2의 groupName이 설정이 되어 있지 않다.

코드로 해결 할 수 있지만 셋팅으로 바꿔보자

Revert를 눌러서 돌아오자

groupName을 추가하고 apply

그후 database -reverse -sqldb선택

USE sqlDB;

SELECT * FROM usertbl;

INSERT INTO usertbl VALUES ('HGD', '홍길동', 1900, '서울', NULL, NULL, 181, '2024-05-28');
DESC usertbl;

CREATE TABLE testTBL1(
id INT,
userName CHAR(3),
age INT
);
INSERT INTO testTBL1 VALUES(1, '홍길동', 25);
INSERT INTO testTBL1(id,userName) VALUES(2, '설현');
INSERT INTO testTBL1(id, userName, age) Values (2, '설현', 26);

CREATE TABLE testTbl2(
id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int
);

INSERT INTO testTbl2 VALUES(NULL, '지민', 25);
INSERT INTO testTbl2 VALUES(NULL, '유나', 22);
-- INSERT INTO testTbl2(userName,age) VALUES ('유나', 22);
INSERT INTO testTbl2 VALUES(NULL, '유정', 21);
SELECT * FROM testTbl2;

ALTER TABLE testTbl2 AUTO_INCREMENT = 100;
INSERT INTO testTBL2 VALUES (NULL, '찬미', 23);
SELECT * FROM testTBL2;
INSERT INTO testTBL2 VALUES (NULL, '유진' ,24);
SELECT * FROM testTBL2; -- 유진은 기본값이 id가 100으로 수정되어서 +1인 101로 자동지정된다.

CREATE TABLE testTbl3(
id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int);
ALTER TABLE testTbl3 AUTO_INCREMENT 1000;
SET @@auto_increment_increment = 3; -- AUTO_INCREMENT의 증가를 3씩 설정
INSERT INTO testTbl3 VALUES(NULL, '나연', 20);
INSERT INTO testTbl3 VALUES(NULL, '정연', 18);
INSERT INTO testTbl3 VALUES(NULL, '모모', 19);
SELECT * FROM testTbl3;

-- 마지막 id 가져오기
SELECT last_insert_id();
-- 최대 id 가져오기
SELECT MAX(id) as 'last_insert_id' FROM testTbl3;

-- 한꺼번에 INSERT
INSERT INTO testTbl3 VALUES (NULL, '꺽정', 55), (NULL, '길산', 45);
SELECT * FROM testTbl3;

-- 대량의 샘플 데이터 생성
USE employees;
DESC employees;

USE sqlDB;
CREATE TABLE testTbl4 (id int, Fname VARCHAR(50), Lname varchar(50));
INSERT INTO testTbl4
	SELECT emp_no, first_name, last_name -- 여러 개의 데이터타입 중 가지고 오고 싶은 3개 추출
		FROM employees.employees; -- 서브쿼리
        
SELECT * FROM testTbl4;

-- 데이터의 수정 : UPDATE
-- WHERE 절은 생략가능하지만 모든 열이 바뀔 수 있다.

SELECT * FROM testTbl4 where Fname = 'Kyoichi'; -- 확인을 먼저 한다

UPDATE testTbl4
	SET Lname = '없음'
    WHERE Fname ='Kyoichi';
    
-- SQL 모드 중 하나인 "safe-updates" 모드를 해제하는 명령
SET SQL_SAFE_UPDATES = 0;
    
SELECT * FROM testTbl4;

-- 'Kyoichi' 정보를 복구하자
desc employees.employees;
SELECT emp_no, first_name, last_name From employees.employees
	WHERE first_name = 'Kyoichi';

SELECT first_name FROM userTbl4 WHERE first_name = 'KYOICHI';


UPDATE testTbl4
SET Lname = (SELECT last_name 
             FROM employees.employees 
             WHERE first_name = 'Kyoichi' 
                   AND employees.emp_no = testTbl4.id)
WHERE Fname = 'Kyoichi';

SELECT * FROM testTbl4
WHERE Fname = 'Kyoichi';




-- 가격을 인상시킨 테이블을 사용하자
USE sqldb;
SELECT * FROM buyTbl;
UPDATE buyTbl2 SET price = price * 1.5;
SELECT * FROM buyTbl2;

DESC buyTbl2;
DESC employees.employees;

CREATE TABLE increaseTbl (
    id INT AUTO_INCREMENT PRIMARY KEY,
    groupName CHAR(6) NOT NULL,
    val FLOAT NOT NULL DEFAULT 1
);

/* => 컬럼의 값을 바꾸고 싶다면

ALTER TABLE increaseTbL
ALTER COLUMN VAL SET DEFAULT 1; 
*/

DESC increaseTbl;

SELECT DISTINCT groupName FROM buytbl2;

INSERT INTO increaseTbl(groupName,val)
values ('전자', 1.2), ('의류', 1.3), ('서적', 1.4);

SELECT * FROM increaseTbl;

SELECT * FROM buyTbl2;

-- 모든 groupName이 전자에 해당하는 아이템을들 찾아서 price를
-- increaseTbl에 있는 전자에 해당하는 인상률 만큼 올려준다

SELECT * FROM buyTbl2
	WHERE groupName = '전자';


UPDATE buyTbl2
SET price = price * (SELECT val FROM increaseTbl WHERE groupName = '전자')
WHERE groupName = '전자';


SELECT * FROM buyTbl2;

-- DELETE
SELECT * FROM testTbl4 WHERE Fname = 'Aamer';
DELETE FROM testTbl4 WHERE Fname = 'Aamer' LIMIT 5;

-- 30만건 복사
CREATE TABLE bigTbl1 (SELECT * FROM employees.employees);
CREATE TABLE bigTbl2 (SELECT * FROM employees.employees);
CREATE TABLE bigTbl3 (SELECT * FROM employees.employees);

-- 삭제(속도 확인하기)
DELETE FROM bigTbl1;
DROP TABLE bigTbl2; -- 전부 지울 때
TRUNCATE TABLE bigTbl3; -- 테이블 구조를 남길 때

-- INSERT 문의 확장 기능 중 하나인 "ON DUPLICATE KEY UPDATE"를 사용

CREATE TABLE memberTBL (SELECT userID, name, addr FROM usertbl LIMIT 3); -- 3건만 가져옴
ALTER TABLE memberTBL
	ADD CONSTRAINT pk_memberTBL PRIMARY KEY (userID); -- PK 지정
SELECT * FROM memberTBL;

INSERT INTO memberTBL VALUES ('BBK', '비비코' , '미국');
INSERT INTO memberTBL VALUES ('SJH', '서장훈' , '서울');
INSERT INTO memberTBL VALUES ('HJY', '현주엽', '경기');
SELECT * FROM memberTbl;

INSERT IGNORE INTO memberTBL VALUES ('BBK', '비비코' , '미국');
INSERT IGNORE INTO memberTBL VALUES ('SJH', '서장훈' , '서울');
INSERT IGNORE INTO memberTBL VALUES ('HJY', '현주엽', '경기');
SELECT * FROM memberTbl;

INSERT INTO memberTBL VALUES('BBK', '비비코', '미국')
	ON DUPLICATE KEY UPDATE name = '비비코', addr = '미국';
INSERT INTO memberTBL VALUES('DJW', '동짜몽', '일본')
	ON DUPLICATE KEY UPDATE name = '동짜몽', addr = '일본';
SELECT * FROM memberTbl;

SELECT userID AS '사용자', SUM(price*amount) AS '총 구매액'
	FROM buyTBL GROUP BY userID;
    

WITH abc(userid, total) AS (
    SELECT userid, SUM(price * amount) 
    FROM buyTbl 
    GROUP BY userid
)
SELECT * FROM abc ORDER BY total DESC;

SELECT addr, MAX(height) FROM usertbl GROUP BY addr;

WITH cte_userTBL(addr, maxHeight) AS (
    SELECT addr, MAX(height) AS maxHeight 
    FROM usertbl 
    GROUP BY addr
)
SELECT * FROM cte_userTBL;



WITH cte_userTBL(addr, maxHeight) AS (
    SELECT addr, MAX(height) AS maxHeight 
    FROM usertbl 
    GROUP BY addr
)
SELECT AVG(maxHeight * 1.0) FROM cte_userTBL;

select * from usertbl;

select addr, Max(height) as maxheight from usertbl
group by addr;

-- 첫 번째 방법: 서브쿼리를 사용하여 주소별 최대 키를 먼저 계산한 후에 평균을 구합니다.
SELECT AVG(maxheight) FROM (
    SELECT addr, MAX(height) AS maxheight FROM usertbl GROUP BY addr
) AS subquery;

-- 두 번째 방법: `GROUP BY`를 사용하여 주소별 최대 키를 먼저 계산한 후에 평균을 구합니다.
SELECT AVG(maxheight) FROM (
    SELECT addr, MAX(height) AS maxheight FROM usertbl GROUP BY addr
) AS subquery;

VisualStudio Installer 에서 ASP.NET 웹 개발 설치

              https://downloads.mysql.com/archives/c-odbc/

64 비트

그냥 Accpet 후 설치

ODBC란?

64비트 선택

test가 완료되면 ok를 누르고 확인

그후 비쥬얼스튜디오를 키고

추가로 솔루션 탐색기 단축키 -> Ctrl + Alt + L

인스톨러로 다시 돌아와서

체크 후 설치

이 사이에 테스트를 돌려보고 Succes가 된다면 저장하고 다음을 눌러주면 된다. 

테이블을 SQL문을 활용해서 만들 수 있다.

CREATE TABLE indexTBL (first_name varchar(14), last_name varchar(16), hire_date date);

INSERT INTO indexTBL
SELECT first_name, last_name, hire_date
FROM employees.employees
LIMIT 500;

SELECT * FROM indexTBL;

아래 모양 키를 누른후 Execution Plan 클릭

Execution Plan이란?

그 후

 CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name);

뷰란?

가상의 테이블

원래 있는 테이블을 가상으로 만들어서 뷰테이블?이라고 만든 후 SELECT로 볼 수 있다.

뷰는 수정을 할 수 있지만 SELECT만 권장

CREATE VIEW uv_memberTBL
AS
SELECT memberName, memberAddress FROM uv_membertblmemberTBL;

DELIMITER 는 '구분 문자'를 의미한다.

뒤에 //가 나오면 기존의 세미클론(;)을 // 로 대신한다는 것이다.

프로시저란?

SELECT를 한다면 어떻게 탐색해서 들어갔는지 보여주는 과정

프로시저 만들기

1. 델리미터를 작성 후 실행

2. CREATE로 만들고

3. BEGIN과 END로 실행

4. 셀렉트 문을 실행

즉, 총 3 번을 실행해야 한다.

그러면 이렇게 프로시져가 만들어짐을 확인할 수 있다.

트리거란?

테이블에 부착되어 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생되면 실행되는 코드

가끔 업데이트할때 오류가 나는 이유는 맨 아래 세이프 모드가 체크 되어있기 때문에

테스트를 하려면 풀어주면 된다.

가끔 업데이트가 안될 경우 Reconnect

생성 후 잘 지워 지는 것을 확인 할 수 있다.

이제 지워진 것을 확인할 테이블을 만들 것이다.

CREATE TABLE deletedMemberTBL(
	memberID CHAR(8),
    memberName CHAR(5),
    memberAddress CHAR(20),
    deletedDate DATE
);

CREATE VIEW uv_membertbl
AS SELECT memberName, memberAddress FROM memberTBL;

DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
    SELECT * FROM memberTBL WHERE memberName = '당탕이';
    SELECT * FROM productTBL WHERE productName = '냉장고';
END //
DELIMITER ;

INSERT INTO memberTBL VALUES ('Figure', '연아', '경기도 군포시 당정동');
UPDATE memberTBL SET memberAddress = '서울 강남구 역삼동'
WHERE memberName = '연아';

DELETE FROM memberTBL WHERE memberName = '연아';
SELECT * from memberTBL;

CREATE TABLE deletedMemberTBL(
	memberID CHAR(8),
    memberName CHAR(5),
    memberAddress CHAR(20),
    deletedDate DATE
);

DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL
	AFTER DELETE
	ON memberTBL
	FOR EACH ROW
BEGIN
	INSERT INTO deletedmembertbl
		VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());
END //

DELIMITER ;

당탕이라는 멤버를 지우고 지워지는지 확인

백업하는 법

C에 DB백업이라는 폴더 생성

왼쪽에 아래에 Administration 클릭후 Data Export

모두 체크 후 -> Star Export

추출이 잘 되어있음을 확인 할 수 있다.

이제 데이터를 옮길 것이다.

삭제가 되거나 문제가 생길 경우 Import 해서 저장된 데이터를 가져올 수 있다.

SQL 언어는 마크업언어(Html 등)과 다르게 "연산이 가능"하여 프로그래밍 언어

데이터의 무결성 - 고유키(중복 x)

데이터의 독립성 

mysql 설치 링크

https://downloads.mysql.com/archives/installer/

설치 후 -> 커스텀

오류가 날때가 있는데

보통 경로가 한글로 되있어서 문제이니 pc의 이름이나 디렉토리에 한글이 있다면

영문으로 바꾸고 mysql을 재시작하고 재부팅하자!

보통 이 경로의 위치를 따라가 메모장으로 열어보면 한글이 있을 확률 매우 높으므로

주석처리도 해보고, 여러방법을 시도해보았지만 결론이 pc의 이름을 바꾸는게 베스트

윈도우에서 powershell을 관리자 권환으로 실행

https://cafe.naver.com/thisisMYSQL

이곳에서 파일을 다운받고 

경로를 지정

그 다음에 파워쉘이다가

source employees.sql; 입력

그후

show databases;

Select * from 테이블명

조건을 지정해 출력

Select * From 테이블명 where 필드명 = '값';

테이블 만드는법

스키마안에 있는 Tables를 우클릭후 생성

그 후 데이터타입이랑 변수명을 지정해주면 된다.

이제 변수와 타입을 만들었으니

값을 할당해주면 된다.

정보를 찾는 방법을 이해하려면 이런 식으로 생각하면 편하다