SQL 정의

SQL

- 구조화된 질의언어
- DBMS를 관리하고 제어하기 위해 사용하는 인터페이스 또는 언어
- 데이터베이스로부터 정보를 얻거나 갱신하기 위한 표준 대화식 프로그래밍 언어
- DBMS에 따라 사용되는 SQL 문법이 다르다.

 

기본 명령어

▶ show 명령어

• 데이터베이스나 테이블 목록 조회
• show databases;
• show tables; (← 먼저 데이터베이스가 선택되어 있어야 가능하다)

→ information_schema : 데이터사전, 설정 정보

→ mysql : 계정, 권한의 정보

→ performance_schema : 실시간 이벤트가 발생했을 때 기록하기 위해 사용(영구 X, 재시작하면 없어진다)

 

▶ use 명령어

• 데이터베이스 선택 명령어
• use [DB명]
• 명령어 끝에 세미콜론(;)을 안 붙여도 된다.

 

기본 SQL 문법 종류

▶DDL (데이터베이스나 테이블 자체의 설정을 변경, 삭제, 생성하기 위한 언어) (크알드트)

• create (개체 생성)
• alter (개체 수정)
• drop (개체 삭제)
• truncate (개체 초기화)

① Database 생성

→ create database [DB명];

→ drop database [DB명];

 

② Table 생성

→ create table [Table명] (<컬럼명> <데이터타입> (옵션) (제약조건));

→ 옵션과 제약조건은 생략이 가능하다.

→ 옵션 : 해당 컬럼에 추가 기능을 넣고 싶은 경우에 사용

• default : 사용자가 나중에 해당 컬럼에 값을 입력하지 않고 생략하는 경우 기본으로 들어가는 값을 지정
• auto_increment : 자동으로 숫자가 입력되는 옵션 (무조건 증가하는 값) (ex-회원번호)
• int unsigned : 양수만 입력 가능

Table 생성

데이터 타입

제약 조건

 

③ Table 구조 확인

→ desc [Table명];

 

④ Table 수정 (ALTER) (구조 변경)

→ alter table [Table명] [액션] [옵션 값];

→ alter table [Table명] add <컬럼명> <데이터타입> (옵션) (제약조건) (위치옵션);

• 위치옵션 : first (가장 위에 추가), after <컬럼명> (해당 컬럼 뒤에 추가)

→ alter table [Table명] drop <컬럼명>;

→ alter table [Table명] modify <컬럼명> <데이터타입> (옵션) (제약조건);→ 기존 컬럼 설정 변경

→ alter table [Table명] change <기존컬럼명> <새컬럼명> <데이터타입> (옵션) (제약조건);→ 해당 컬럼의 이름 변경

※기존컬럼명과 새컬럼명만 쓰면 안 되고 그 뒤에 기존의 데이터타입을 사용하던 변경된 타입을 적어야 된다.

→ alter table [기존 Table명] rename [변경할 Table명];→ 테이블 이름 변경

→ rename table [기존 Table명] to [변경할 Table명];→ 테이블 이름 변경

액션

 

⑤ Table 초기화 (TRUNCATE) (기존 Table 자체를 지우고 새로운 Table 생성)

[구조는 남겨놓고 Table에 입력된 Data들만 초기화하는 역할]

[해당 Table의 설정 정보까지 초기화된다.]

→ truncate [Table명];

 

⑥ 특정 Table의 상태 정보 확인 (현재 사용 중인 DB의 모든 테이블의 상태정보 확인)

→ show tables status;

 

⑦ Table 삭제 (DROP)

→ drop table [Table명];

table 삭제

▶DML (실제 저장되는 데이터를 생성, 수정, 삭제, 조회하기 위한 언어) (세인업데)

• select (데이터 조회)
• insert (데이터 생성)
• update (데이터 수정)
• delete (데이터 삭제)

① Data 생성 (INSERT INTO VALUES)

→ insert into [Table명] values (입력데이터);

→ insert into [Table명] (컬럼명) values (입력데이터);

→ insert into [Table명] set <컬럼명> = <입력데이터>;

 

※숫자데이터를 입력할 때는 ' ', " "를 사용하지 않아도 된다.

※문자데이터를 입력할 때는 ' ', " "를 사용해야 한다.

 

② Data 조회 (LIKE)

→ select * from [Table명];

→ select <컬럼명> from [Table명];

→ select <컬럼명> from [Table명] where (조건);

• 조건이 여러 개인 경우 논리연산자 (and, or)을 이용할 수 있다.
• and가 or보다 우선순위가 높다.
• 먼저 연산하고 싶은 조건이 있다면 ()를 사용하면 된다.

→ 조건과 비슷한 데이터 조회 (like 연산자)

• like 연산자에서 %는 [자리 수 제한 없이 입력한 문자를 포함한 모든 문자를 의미]한다.

'%' 연산자

• like 연산자에서 _는 [한 자리 수의 모든 문자를 의미]한다.

'_' 연산자

예시

③ Data 수정 (UPDATE)

→ update [Table명] set <컬럼명> = <수정할 데이터> [where <조건>];

→ update [Table명] set <컬럼명> = <수정할 데이터>, <컬럼명> = <수정할 데이터> [where <조건>];

• Data를 여러개 수정할 수 있다~

update

④ Data 삭제 (DELETE)

→ delete from [Table명] [where<조건>];

Data 삭제

특정 데이터 삭제

⑤ Data 조회 (LIMIT) (Data 자르기)

→ SELECT (컬럼명) FROM [Table명] WHERE (조건) LIMIT (개수);

• SELECT (컬럼명) FROM [Table명] WHERE (조건) LIMIT 5;    (5개만 출력)

→ SELECT (컬럼명) FROM [Table명] WHERE (조건) LIMIT (개수) OFFSET (위치);

• SELECT (컬럼명) FROM [Table명] WHERE (조건) LIMIT 5 OFFSET 3;    (5번째줄부터 3개만 출력)

→ SELECT (컬럼명) FROM [Table명] WHERE (조건) LIMIT (개수),(위치);

• SELECT (컬럼명) FROM [Table명] WHERE (조건) LIMIT 4,3;    (5번째부터 3개만 출력)

⑥ Data 조회 (ORDER BY) (Data 정렬)

→ SELECT (컬럼명) FROM [Table명] ORDER BY (기준컬럼명) (정렬방식); 

→ 기준컬럼 : 기본 컬럼명을 작성하여 기준 컬럼 설정 (숫자 입력 시에는 컬럼의 순서에 맞게 설정된다.)

• SELECT * FROM member ORDER BY no ASC;     (no를 기준으로 오름차순으로 정렬)
• SELECT * FROM member ORDER BY no DESC;     (no를 기준으로 내림차순으로 정렬)

→ 다중 정렬 : SELECT (컬럼명) FROM [Table명] ORDER BY (첫번째기준컬럼) (정렬방식), (두번째기준컬럼) (정렬방식);

• SELECT * FROM member ORDER BY sex DESC, address ASC;

⑦ Data 조회 (중복 제거)

→ SELECT DISTINCT (컬럼명) FROM [Table명];

• SELECT DISTINCT sex FROM member;    (성별에서 중복된 항목을 제거하고 성별만 출력)

⑧ Data 조회 (GROUP BY) (그룹핑)

→ SELECT (컬럼명) FROM [Table명] GROUP BY (기준컬럼명);

• SELECT sex,COUNT(*) FROM member GROUP BY sex;

→ 집계함수 : 데이터의 집계를 결과 출력해주는 함수 (꼭 GROUP과 같이 써야되는 것은 아님)

→ SELECT 집계함수(컬럼명) FROM [Table명] GROUP BY (기준컬럼명);

• COUNT() : 데이터의 개수
• AVG() : 데이터의 평균
• SUM() : 데이터의 합계
• MAX() : 데이터의 최대값
• MIN() : 데이터의 최소값

→ GROUP BY에서 조건을 사용할 때는 WHERE이 아닌 HAVING을 사용한다.

• SELECT (컬럼명) FROM [Table명] GROUP BY (기준컬럼명) HAVING (조건);
• SELECT sex,COUNT(*) FROM member GROUP BY sex HAVING COUNT(*) > 5;

⑨ Data 조회 (HAVING)

→ GROUP BY절과 집계함수를 사용할때 함께 사용한다.

→ WHERE절에서는 집계함수를 사용할 수 없다.

→ SELECT (컬럼명) FROM [Table명] GROUP BY (컬럼명) HAVING (조건);

※ GROUP BY에서 조건을 사용하려면 WHERE 대신 HAVING을 사용해야 한다.

▶DCL (그 외에 작업을 진행하기 위한 언어)

• grant (권한 할당)
• revoke (권한 삭제)
• commit (실행) (TCL) - 트랜잭션에서 사용
• rollback (복구) (TCL) - 트랜잭션에서 사용

① 계정 생성 및 삭제

→ CREATE user (계정명@경로);

• CREATE USER kuser1@localhost;

비밀번호 없는 계정을 만들었을시

 

→ CREATE user (계정명)@(경로) IDENTIFIED BY '비밀번호';

• CREATE user kuser2@localhost IDENTIFIED BY 'P@ssw0rd';

비밀번호 있는 계정을 만들었을시

이미 생성된 계정의 비밀번호 설정

→ SET password FOR (계정명)@(경로) = password('비밀번호');

kuser1에 비밀번호가 설정됨

계정설정, 패스워드 설정, 권한설정이던 설정을 바꾼 경우에 바뀐 설정을 적용해야한다. (습관화 할 것)

→ FLUSH PRIVILEGES;

 

계정 삭제

→ DROP user (계정명)@(경로);

kuser2가 삭제됨

② 권한 확인

현재 로그인한 계정 확인

→ SHOW GRANTS;

root 계정의 권한 설정되어있는 모습

 

특정 계정의 권한 확인

→ SHOW GRANTS FOR (계정명)@(경로);

kuser1 계정의 권한이 설정되어있는 모습

[localhost에서 접속하는 kuser1은 모든DB와 모든 Table에 기본권한만 가지고 있다.]

GRANT 권한값 ON DB.TABLE TO 계정명@경로 IDENTIFIED BY PASSWORD '비밀번호' 옵션

→ 비밀번호가 있다면 IDENTIFIED BY PASSWORD '비밀번호'가 뒤에 출력된다.

 

권한값↓

USAGE = 기본 권한 (거의 없는 권한)

ALL PRIVILEGES = 모든 권한

INSERT = 데이터 생성 권한

SELECT = 데이터 조회 권한

UPDATE = 데이터 수정 권한

DELETE = 데이터 삭제 권한

 

옵션↓

with grant option : 다른 계정의 권한 설정을 할 수 있는 권한

 

③ 특정 계정으로 로그인하기

mariadb -u kuser1 -pP@ssw0rd

-p와 P@ssw0rd는 붙여서 입력해야된다.

※mariadb -u kuser1 -p [DB명]을 입력하면 해당 DB명으로 로그인 된다.

mariadb -u kuser1 -p 까지 입력하고 비밀번호는 뒤에 입력하면 비밀번호 노출되지 않고 로그인 가능

④ 권한 부여하기

→ GRANT 권한값 ON DB.TABLE TO 계정명@경로;

root 계정으로 로그인해서 권한부여해야 된다.

권한 부여하고 flush privileges; 입력해야 된다.

id와age만 조회할 수 있게 권한을 부여했다

kuser1로 mariadb 로그인한 후에 SELECT * FROM member; 입력하면 접근거부된다.

SELECT id와 age FROM member;만 가능하다.

address 컬럼을 조회하려했지만 거부되었음

 

⑤ 권한 삭제하기

→ REVOKE 권한값 ON DB.TABLE FROM 계정명@경로;

SELECT가 들어가 있는 권한은 다 삭제하기 때문에 SELECT(id,age)도 삭제됐다.

---

특정 DB에서 다른 DB에 있는 Table의 정보를 확인하고 싶을 때

→SELECT (컬럼명) FROM (DB).(TABLE) WHERE (조건);

↑mysql DB에서 naver_db의 member TABLE을 조회한 모습↑

↑mysql DB에서 naver_db의 member TABLE에 있는 차범길에 대한 모든 정보 출력한 모습↑