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;
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 ;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using UnityEngine;
using UnityEngine.U2D;
using Random = UnityEngine.Random;
public class Test : MonoBehaviour
{
public Board board;
private void Start()
{
board.CreateBoard();
}
private void Update()
{
if (Input.GetMouseButtonDown(0))
{
Ray ray = Camera.main.ScreenPointToRay(Input.mousePosition);
Debug.DrawRay(ray.origin, ray.direction * 100f, Color.red, 1);
RaycastHit2D raycastHit2D = Physics2D.Raycast(ray.origin, ray.direction);
if (raycastHit2D.collider != null)
{
Block block = raycastHit2D.collider.GetComponent<Block>();
Debug.Log($"[{block.row}, {block.col}] ({block.transform.position.x}, {block.transform.position.y}), {block.blockType}");
}
}
}
}
using System.Collections;
using System.Collections.Generic;
using TMPro;
using UnityEngine;
public class Block : MonoBehaviour
{
public enum BlockType
{
Rabbit, Pig, Rat, Monkey, Cat, Chick, Puppy
}
public BlockType blockType;
public SpriteRenderer spriteRenderer;
public TMP_Text debugText;
public int row;
public int col;
public void Init(BlockType blockType)
{
this.blockType = blockType;
//이미지 변경
ChangeSprite(blockType);
}
public void ChangeSprite(BlockType blockType)
{
Sprite sp =
AtlasManager.instance.blockAtlas.GetSprite(blockType.ToString());
spriteRenderer.sprite = sp;
}
public void SetPosition(Vector2 pos)
{
transform.position = pos;
var index = Position2Index(pos);
row = index.row;
col = index.col;
debugText.text = $"[{index.row},{index.col}]";
}
public static (int row, int col) Position2Index(Vector2 pos)
{
return ((int)pos.y, (int)pos.x);
}
public static (int x, int y) Index2Position(Vector2 index)
{
return ((int)index.x, (int)index.y);
}
}
using System.Collections;
using System.Collections.Generic;
using UnityEditor;
using UnityEngine;
[CustomEditor(typeof(Block))]
public class BlockEditor : Editor
{
public override void OnInspectorGUI()
{
base.OnInspectorGUI();
Block block = target as Block;
if (GUILayout.Button("제거"))
{
Debug.Log($"[{block.row},{block.col}]을 제거 합니다.");
Board.instance.RemoveBlock(block.row, block.col);
}
}
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UnityEngine;
using Random = UnityEngine.Random;
public class Board : MonoBehaviour
{
public static Board instance; //싱글톤
private Block[,] board; //2차원 배열이 Block들을 관리
public GameObject blockPrefab;
public int totalHeight = 14;
public int height = 7;
public int width = 7;
private void Awake()
{
instance = this;
}
public void CreateBoard()
{
//크기가 7개인 Block타입의 2차원 배열을 만들기
board = new Block[totalHeight, width];
for (int i = 0; i < height; i++) //행
{
for (int j = 0; j < width; j++) //열
{
CreateBlock(i, j);
}
}
PrintBoard();
}
public void CreateBlock(int row, int col)
{
Vector2 pos = new Vector2(col, row);
Block.BlockType blockType = (Block.BlockType)Random.Range(0, 7); //0 ~ 6
GameObject blockGo = Instantiate(blockPrefab, this.transform);
Block block = blockGo.GetComponent<Block>();
block.Init(blockType);
block.SetPosition(pos);
//배열의 요소에 블록 넣기
board[row, col] = block;
}
public void RemoveBlock(int row, int col)
{
Block block = board[row, col];
Destroy(block.gameObject); //게임오브젝트 파괴
//배열의 요소를 비워줌
board[row, col] = null;
}
public void PrintBoard()
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < height; i++) //행
{
for (int j = 0; j < width; j++) //열
{
Block block = board[i, j];
string strElement = (block == null) ? "null" : block.blockType.ToString();
// 각 엘리먼트를 고정된 너비로 출력
sb.AppendFormat("[{0},{1}] = {2,-10}", i, j, strElement);
}
sb.AppendLine();
}
Debug.Log(sb.ToString());
}
private int[] arrEmptySpaceCol; //빈공간이 있는 컬럼 배열
public void FindEmptySpaceFromColumn()
{
arrEmptySpaceCol = new int[width]; //배열 초기화
//0 : 행, 1: 열
for (int i = 0; i < width; i++) //열
{
int existBlockCnt = 0;
for (int j = 0; j < totalHeight; j++) //행
{
Block block = board[j, i];
if (block != null) existBlockCnt++;
}
int emptySpace = height - existBlockCnt;
arrEmptySpaceCol[i] = emptySpace;
Debug.Log($"{i} 열의 빈공간은 {emptySpace}개 입니다.");
}
}
public void CreateNewBlocks()
{
// arrEmptySpaceCol가 null이거나 모든 요소가 0이면 빈공간이 없음
if (arrEmptySpaceCol == null || arrEmptySpaceCol.All(x => x == 0))
{
Debug.Log("빈공간이 없습니다.");
}
else
{
for (int i = 0; i < arrEmptySpaceCol.Length; i++)
{
int cnt = arrEmptySpaceCol[i]; //i(col)에 몇개 빈공간이 있는가?
if (cnt > 0)
{
Debug.Log($"{i}열에 {cnt}만큼 블록 생성 필요");
CreateBlockFromColumn(i, cnt);
}
}
}
arrEmptySpaceCol = null;
}
private void CreateBlockFromColumn(int col, int cnt)
{
int startRow = height; //행
for (int i = 0; i < cnt; i++)
{
//[7, col] -> [8, col] -> [9,col]
CreateBlock(startRow, col);
startRow++;
}
}
}
using System.Collections;
using System.Collections.Generic;
using UnityEditor;
using UnityEngine;
[CustomEditor(typeof(Board))]
public class BoardEditor : Editor
{
private int currentColIdx = 0;
public override void OnInspectorGUI()
{
base.OnInspectorGUI();
Board board = target as Board;
if (GUILayout.Button("배열 요소 출력"))
{
board.PrintBoard(); ;
}
GUILayout.Space(5);
if (GUILayout.Button("각 열에 빈공간 찾기"))
{
board.FindEmptySpaceFromColumn();
}
GUILayout.Space(5);
if (GUILayout.Button("새로운 블록들 생성하기"))
{
board.CreateNewBlocks();
}
GUILayout.Space(5);
if (GUILayout.Button($"현재 행({currentColIdx})에 있는 모든 블록 내려보내기"))
{
Debug.Log($"{currentColIdx}행에 있는 모든 블록 내려 보냅니다.");
currentColIdx++;
if (currentColIdx > 6)
{
currentColIdx = 0;
}
}
GUILayout.Space(5);
if (GUILayout.Button("리셋 행"))
{
currentColIdx = 0;
}
}
}