SK networks AI Camp - DB 명령어

Networks/MySQL&DB

SK networks AI Camp - DB 명령어

코딩하는 Español되기 2024. 7. 20. 08:00

이전 포스팅에서 설치한 DBeaver에서 명령어를 배워볼게요.

명령어를 입력할 때 항상 ;(세미클론) 입력해줘야 해요!

○ DDL

: DB와 테이블을 정의, 수정, 삭제하는 구문

● 데이터 베이스

- CREATE DATABASE (db명); : 데이터 베이스 생성

- SHOW DATABASES; : 데이터 베이스 목록 보기

- USE (db명); : db명의 데이터베이스 사용

- DROP DATABASE IF EXISTS (db명) : db명의 데이터 베이스가 있으면 삭제

mysql> CREATE DATABASE dbname; 
mysql> SHOW DATABASES;
mysql> USE dbname; 
mysql> DROP DATABASE IF EXISTS dbname;

● 테이블 생성

# 학생 student
create table student (
	student_id INT UNSIGNED auto_increment COMMENT '학생아이디',
	student_name varchar(10) not null COMMENT '학생이름',
	student_address varchar(50) null COMMENT '학생집주소',
	create_dt TIMESTAMP not null default now() COMMENT '생성일자',
	modify_dt TIMESTAMP not null default now() COMMENT '수정일자',
	PRIMARY KEY(student_id)
);

# 교수 professor
create table professor (
	professor_id INT UNSIGNED auto_increment COMMENT '교수아이디',
	professor_name varchar(10) not null COMMENT '교수이름',
	create_dt TIMESTAMP not null default now() COMMENT '생성일자',
	modify_dt TIMESTAMP not null default now() COMMENT '수정일자',
	PRIMARY KEY(professor_id)
);

commit;

# 과목 subject
create table subject (
	subject_cd varchar(10) COMMENT '과목코드',
	subject_name varchar(10) not null unique COMMENT '과목명',
	subject_desc text COMMENT '과목설명',
	professor_id INT unsigned not null COMMENT '교수아이디',
	create_dt TIMESTAMP not null default now() COMMENT '생성일자',
	modify_dt TIMESTAMP not null default now() COMMENT '수정일자',
	PRIMARY KEY(subject_cd),
	FOREIGN KEY (professor_id) REFERENCES professor(professor_id) ON UPDATE CASCADE
);

# 수강신청 enrolment
create table enrolment (
	enrolment_id INT UNSIGNED auto_increment COMMENT '수강신청아이디',
	semester varchar(10) not null COMMENT '학기',
	student_id INT unsigned not null COMMENT '학생아이디',
	subject_cd varchar(10) not null COMMENT '과목코드',
	create_dt TIMESTAMP not null default now() COMMENT '생성일자',
	modify_dt TIMESTAMP not null default now() COMMENT '수정일자',
	PRIMARY KEY(enrolment_id),
	FOREIGN KEY (student_id) REFERENCES student(student_id) ON UPDATE cascade,
	FOREIGN KEY (subject_cd) REFERENCES subject(subject_cd) ON UPDATE CASCADE
);

commit;

● 테이블 조회

- SHOW TABLES; : 데이터 베이스 안에 테이블들을 조회

- desc mytable; : 테이블 정보 조회

mysql> USE dbname;
mysql> SHOW TABLES;
mysql> desc mytable;

● 테이블 수정

- ALTER TABLE (테이블명) ADD COLUMN (새로운 칼럼명) (타입) (조건); : 새로운 컬럼 생성

- ALTER TABLE (테이블명) MODIFY COLUMN (칼럼명) (타입) (조건); : 컬럼 타입 변경

- ALTER TABLE (테이블명) CHANGE COLUMN (칼럼명) 9타입) (조건); : 컬럼 이름 변경

- ALTER TABLE (테이블명) DROP COLUMN (칼럼명); : 컬럼 삭제

mysql> ALTER TABLE mytable ADD COLUMN new_column varchar(10) NOT NULL;
mysql> ALTER TABLE mytable MODIFY COLUMN modelnumber varchar(20) NOT NULL;
mysql> ALTER TABLE mytable CHANGE COLUMN modelnumber new_modelnumber varchar(10) NOT NULL;
mysql> ALTER TABLE mytable DROP COLUMN series;

○ DCL

: 데이터의 보안, 무결성, 회복 등을 제어하는 구문

● 사용자 권한

- select * from (사용자 명);

- create user '아이디'@localhost identified by '비밀번호'; : 로컬에서만 접속 가능한 user 생성

- create user '아이디'@'%' identified by '비밀번호'; : 모든 호스트에서 접속 가능한 user 생성

- SET PASSWORD FOR '아이디'@'%' = '신규비밀번호'; : 사용자 비밀번호 변경

- drop user '아이디'@'%'; : 사용자 삭제

mysql> use mysql;
mysql> select * from user;
mysql> create user 'userid'@localhost identified by '비밀번호';
mysql> create user 'userid'@'%' identified by '비밀번호';
mysql> SET PASSWORD FOR 'userid'@'%' = '신규비밀번호';
mysql> drop user 'userid'@'%';

● 데이터베이스 권한

- GRANT ALL privileges ON *.* to '계정명'@계정 호스트 정보; : 전체 DB 전체 테이블의 조회/수정/추가 권한

- GRANT select ON 특정 데이터베이스.* to '계정명'@계정 호스트 정보; : 특정 DB 전체 테이블의 권한 부여

- GRANT select, insert, update ON 특정 데이터베이스. 특정 테이블 to '계정명'@계정 호스트 정보;

: 특정 데이터베이스 특정 테이블의 조회/수정/추가 권한 부여

- FLUSH PRIVILEGES; : 새로운 권한 적용(이걸 적어야 적용)

mysql> GRANT ALL privileges ON *.* to '계정명'@계정 호스트 정보;
mysql> FLUSH PRIVILEGES; 
mysql> GRANT select ON 특정 데이터베이스.* to '계정명'@계정 호스트 정보;
mysql> GRANT select,insert,update ON 특정 데이터베이스.특정 테이블 to '계정명'@계정 호스트 정보;
mysql> FLUSH PRIVILEGES;

○ DML

: 테이블의 데이터를 삽입, 조회, 수정, 삭제하는 구문

● 데이터 조회

mysql> SELECT * FROM 테이블명;
mysql> SELECT 컬럼1, 컬럼2, ... FROM 테이블명;
mysql> SELECT 컬럼1 as 뉴 컬럼1, 컬럼2 as 뉴 컬럼2, ... FROM 테이블명;

● 데이터 정렬하여 조회

# DESC(내림차순), ASC(오름차순)
mysql> SELECT * FROM 테이블명 order by 컬럼1 DESC;
mysql> SELECT * FROM 테이블명 order by 컬럼1, 컬럼2 ASC;

● 조건으로 데이터 조회

mysql> SELECT * FROM 테이블명 WHERE 컬럼1 = '값'
mysql> SELECT * FROM 테이블명 WHERE 컬럼1 > '값'
mysql> SELECT * FROM 테이블명 WHERE 컬럼1 = '값' and 컬럼2 > '값'
mysql> SELECT * FROM 테이블명 WHERE 컬럼1 = '값' or 컬럼2 < '값'
mysql> SELECT * FROM 테이블명 WHERE 컬럼1 LIKE '%값%'

● 조건 결과 중 일부만 조회

mysql> SELECT * FROM 테이블명 LIMIT 10; # 결과중 처음부터 10개만
mysql> SELECT * FROM 테이블명 LIMIT 100, 10; # 결과중 100번째부터 10개만

○ Join 데이터 조회

● Inner Join

select u.userid, name 
from usertbl as u inner join buytbl as b 
on u.userid=b.userid 
where u.userid="111" -- join을 완료하고 그다음 조건을 따진다.

select u.userid, name 
from usertbl u, buytbl b 
where u.userid=b.userid and u.userid="111"

● Left Join

-- 예) 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학생도 출력되게 하라.

SELECT STUDENT.NAME, PROFESSOR.NAME 
FROM STUDENT LEFT OUTER JOIN PROFESSOR -- STUDENT를 기준으로 왼쪽 조인
ON STUDENT.PID = PROFESSOR.ID 
WHERE GRADE = 1

● Right Join

-- 예) 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학생도 출력되게 하라.

SELECT STUDENT.NAME, PROFESSOR.NAME 
FROM STUDENT RIGHT OUTER JOIN PROFESSOR -- PROFESSOR를 기준으로 오른쪽 조인
ON STUDENT.PID = PROFESSOR.ID 
WHERE GRADE = 1

● Ful Outer Join

select * 
from topic FULL OUTER JOIN autor 
on topic.auther_id = authoer.id

(select * from topic LEFT JOIN autor on topic.auther_id = authoer.id)) 
UNION 
(select * from topic RIGHT JOIN autor on topic.auther_id = authoer.id))

○ Group by 데이터 조회

● 함수

- SUM : 그룹별 합계 구하는 집계함수

- MIN / MAX : 그룹별 최소, 최댓값 구하는 집계함수

- COUNT : 그룹별로 건수를 카운트하는 집계함수

- AVG : 그룹별로 평균을 구하는 집계함수

- HAVING : WHERE절과 유사하게 조회할 데이터를 걸러내는 역할

# A컬럼별 카운트 집계
mysql> SELECT COUNT(*) CNT FROM 테이블명 GROUP BY A컬럼; 

# A컬럼별 카운트 집계 및 A컬럼으로 정렬 
mysql> SELECT A컬럼, COUNT(*) CNT FROM 테이블명 GROUP BY A컬럼 ORDER BY A컬럼; 

# A컬럼별 카운트 집계 및 Having 조건 및 A컬럼으로 정렬 
mysql> SELECT A컬럼, COUNT(*) CNT FROM 테이블명 GROUP BY A컬럼 HAVING A컬럼 = 'AA' ORDER BY A컬럼;

● 칼럼의 데이터 변경

SELECT  SUBSTR(T1.STK_NM,1,2) STK_SUB_NM ,T1.STK_NM
FROM    STOCK T1
WHERE   (T1.STK_NM LIKE '삼성%' OR T1.STK_NM LIKE '현대%')
ORDER BY T1.STK_NM;

● DT(일자) 칼럼을 DATE_FORMAT을 사용해 연월로 변경해 GROUP BY 처리한 SQL

SELECT  DATE_FORMAT(T1.DT,'%Y%m') YM ,COUNT(*) CNT
FROM    HISTORY_DT T1
WHERE   T1.STK_CD = '005930'
GROUP BY DATE_FORMAT(T1.DT,'%Y%m')
ORDER BY YM ASC;

● 여러 컬럼 GROUP BY

SELECT  T1.EX_CD ,T1.SEC_NM ,COUNT(*) CNT
FROM    STOCK T1
WHERE   T1.STK_NM LIKE '동일%'
GROUP BY T1.EX_CD ,T1.SEC_NM
ORDER BY T1.EX_CD ,T1.SEC_NM;

● HAVING 적용

SELECT  T1.STK_CD ,SUM(T1.C_PRC * T1.VOL) SUM_VOL_AMT
FROM    HISTORY_DT T1
WHERE   T1.DT >= STR_TO_DATE('20190201','%Y%m%d')
AND     T1.DT < STR_TO_DATE('20190301','%Y%m%d')
GROUP BY T1.STK_CD
HAVING SUM(T1.VOL * T1.C_PRC) > 2000000000000
ORDER BY STK_CD ASC;

○ root 계정 PW 잊어버렸을 때

● MySQL 서버 멈추기

> mysql.server stop

● MySQL 서버 승인 생략 옵션

> mysql.server start --skip-grant-tables

● pw 없이 root 유저로 접속

> mysql -u root

● mysql 데이터베이스 사용

mysql> use mysql;

● 비밀번호 정책 확인

mysql> show variables like 'validate_password%';

- (필요시) 비밀번호 정책 변경

mysql> set global validate_password.policy=LOW;

● root 계정 정보 확인

mysql> select * from user;

- root 계정 pw 변경

mysql> SET PASSWORD FOR 'root'@localhost = '신규비밀번호';

- 변경사항 적용 & 나가기

mysql> flush privileges;
mysql> exit;

● 서버 재가동&접속

> mysql.server restart
> mysql -u root -p

'Networks > MySQL&DB' 카테고리의 다른 글

SK networks AI Camp - MySQL & DBeaver설치 (0)	2024.07.19
SK networks AI Camp - 데이터 베이스 정규화 (0)	2024.07.18
SK networks AI Camp - 데이터 모델링 & ERD (1)	2024.07.18
SK networks AI Camp - MySQL (0)	2024.07.18

현재글SK networks AI Camp - DB 명령어

코딩에서 살아남기

스페인어, 일상, 코딩, SK networks AI Camp 관련 글 올립니다! 많이 봐주세요.

sk networks, MySQL, 명령어, 코딩, 머신러닝, playdata, 데이터 전처리, db, django, Crawling, 데이터, 딥러닝, encore, 스페인어, docker, 크롤링, Python, 프로젝트, AI, 회고록,

Today :
Yesterday :

일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

코딩에서 살아남기