[Mysql][MariaDB] 기본 쿼리 모음 - 자주 쓰는 쿼리들 소개

 

1. DB users 설정 개요 조회

SELECT host, user, password FROM mysql.user;

 

 

 

2. mysql root 비밀번호 변경

SET PASSWORD = PASSWORD('sample123');

 

 

 

3. Local에서 접속 가능한 사용자 추가하기 ( Create User Example )

 - 비밀번호와 함께 사용자 생성

CREATE USER '사용자'@'localhost' IDENTIFIED BY '비밀번호';

 - 단순히 'testuser'라는 유저만 생성 (비밀번호 미설정)

CREATE USER 'testuser'@localhost;

 - 생성된 계정의 'testuserpassword' 라는 비밀번호 설정

SET PASSWORD FOR 'testuser'@localhost = PASSWORD('testuserpassword');

 - 만약 외부의 모든 IP 접속을 허용하는 계정 생성한다면 'localhost' 대신 '%' 사용

CREATE USER '사용자'@'%' IDENTIFIED BY '비밀번호';

 

 

 

4. DB 권한 부여 ( 'db이름'이라는 DB에 대한 모든 권한을 주는 Example )

# 모든 권한 주기
GRANT ALL PRIVILEGES ON *.* to '사용자'@'localhost';            # 모든 DB 접속 권한 주기
GRANT ALL PRIVILEGES ON db이름.* to '사용자'@'localhost';    # db이름 이라는 DB에만 접속 권한 주기
GRANT ALL PRIVILEGES ON testDB.* TO testuser@localhost IDENTIFIED BY 'testuserpassword';

# select 권한만 주기
GRANT SELECT ON testDB.* TO 'testuser'@'localhost' IDENTIFIED BY 'testuserpassword';

# 999.888.777.666에 대한 testuser 계정 권한 정보 조회
SHOW GRANTS FOR 'testuser'@'999.888.777.666';

FLUSH PRIVILEGES;        # 권한 설정 적용을 위해서 마지막에 꼭 입력해줘야함

( TIP : localhost와 본인의 IP를 직접 사용하는 것은 동일하지 않음! 둘이 서로 다름을 인식하고 권한을 줘야함! )

 

GRANT 종류 >>> SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE

 

 

 

5. User 계정 삭제 ( Example )

DROP USER '사용자'@'localhost';

 

 

 

6. test_table 이라는 테이블 삭제 ( Example )

DROP TABLE 'test_table';

 

 

 

7. DB 생성 ('testDB'라는 데이터베이스 생성)

CREATE DATABASE testDB;

 

 

 

8. Table 생성 ( Example )

CREATE TABLE IF NOT EXISTS store_information (

idx int UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '고유값',

callnum VARCHAR(13) UNIQUE KEY COMMENT '전화번호',

name VARCHAR(20) NOT NULL COMMENT '가게 이름',

road_name_address_kor VARCHAR(30) COMMENT '도로명주소',

location_old_kor VARCHAR(35) COMMENT '지번',

postcode VARCHAR(6) COMMENT '우편번호',

coordinate VARCHAR(35) COMMENT '좌표',

opening_hours VARCHAR(45) COMMENT '영업시간',

regdate TIMESTAMP DEFAULT NOW() COMMENT '등록일시',

remark1 VARCHAR(50) COMMENT '비고1',

remark2 VARCHAR(50) COMMENT '비고2',

FOREIGN KEY (idx) REFERENCES (user.id),

INDEX index_name_sample (coordinate)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

COMMENT '가게 정보';

 <<< 제약조건 (CONSTRAINT) >>>

 - PRIMARY KEY (기본키)
하나의 테이블에 하나의 기본키만 제약 가능
PRIMARY KEY = UNIQUE KEY & NOT NULL

 - UNIQUE KEY (고유키)
테이블에 저장된 행 데이터 고유 식별을 위한 키
NULL은 고유키 제약 대상이 아니기때문에 NULL은 여러개 있어도 문제 없음

 - NOT NULL
NULL값 금지

 - CHECK
입력할 수 있는 값 범위를 제한함
ex. True or False

 - FOREIGN KEY
관계형 데이터베이스에서 테이블 간의 관계 정의를 위해 기본키를 다른 테이블의 외래키로 복사하여 생성
외래키 지정시 참조 무결성 제약 옵션 선택 가능

 

 

 

9. Table 데이터 구조 확인하기

DESC sample_test_table;

 

 

 

10. DB 내에서 데이터 이동 ( Example )

INSERT INTO my_suppliers.rawdatas_ver100 SELECT * FROM my_suppliers.rawdatas_ver000 WHERE date BETWEEN '2019.08.01' AND '2019.11.23';

# 데이터 삽입
INSERT INTO test_ver100 (call_num, name) VALUES ('010-9999-9999', 'Jane');

 

 

 

11. Table 데이터 구조 수정

# 테이블에 column_name 추가
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(32) NOT NULL;

# 테이블의 기존 컬럼 column_name 구조 수정
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(16) NULL;

# 테이블의 기존 컬럼 column_name1 구조 수정 & 이름 column_name2로 변경
ALTER TABLE table_name CHANGE COLUMN column_name1 column_name2 VARCHAR(16) NULL;

# 테이블의 기존 컬럼 column_name 삭제
ALTER TABLE table_name FROP COLUMN column_name;

# 테이블의 기존 컬럼 column_name1 이름 column_name2로 변경
ALTER TABLE table_name1 RENAME table_name2;

 

 

 

12. inner join

교집합과 유사한 개념이라고 보면 된다.

SELECT ou._id, ou.name, nu.email

FROM old_user AS ou

JOIN new_user AS nu

ON nu._id = ou._id;

 # (ON 대신 WHERE 사용해도 무방함)

 = new user 테이블의 id와 old user 테이블의 id가 같은 데이터들의 id, name, email을 가져와라

 

 

 

13. Self Join

아래와 같은 테이블이 있을 때 셀프 조인을 통해 깔끔한 데이터를 얻을 수 있다.

SELECT c.name AS child, p.name AS parent

FROM chicken_gate AS p

JOIN chicken_gate AS c

ON p._id = c.boss;

 

 

 

14. 종류별 OUTER JOIN

 - LEFT JOIN 으로 사용해도 무방함 (OUTER 생략 가능)

SELECT gg._id, gg.name, s.title

FROM girl_group AS gg

LEFT OUTER JOIN song AS s

ON s._id = gg.hit_song_id;

 -> gg 테이블의 모든 데이터 가져옴 (왼쪽에 있으므로), right에 데이터 없을 경우 NULL로 출력

============================================================

SELECT s._id, s.title, gg.name

FROM girl_group AS gg

RIGHT OUTER JOIN song AS s

ON s._id = gg.hit_song_id;

 -> s 테이블의 모든 데이터 가져옴 (오른쪽에 있으므로), left에 데이터 없을 경우 NULL로 출력

============================================================

SELECT s._id, s.title, gg.name

FROM girl_group AS gg

JOIN song AS s

ON s._id <> gg.hit_song_id;

 -> mysql에는 별도의 outer join이 없지만 위와 같은 쿼리로 OUTER 작업을 대신할 수 있다.

 

 

 

15. 카티전 JOIN

 - 예를 들어 A= {a, b, c, d} , B = {1, 2, 3} 일 때

A CROSS JOIN B 는

(a,1), (a, 2), (a,3), (b,1), (b,2), (b,3), (c, 1), (c,2), (c,3), (d, 1), (d, 2), (d,3)

이다. ( 4 x 3 = 12 )

===================

SELECT s._id, s.title, gg.name

FROM girl_group AS gg

CROSS JOIN song AS s;

 -> gg와 s를 모든 조합 경우의 수에 대해 출력

===================

SELECT s._id, s.title, gg.name

FROM girl_group AS gg, song AS s;

 ( 위와 동일한 결과값이 나옴 )

 

 

 

16. 기본 조합 변경

# testdb 데이터베이스의 기본 조합 설정을 'utf8_unicode_ci'
ALTER DATABASE `testdb` COLLATE 'utf8_unicode_ci';

# test_table 테이블의 기본 조합 설정을 'utf8_unicode_ci'로 변경
ALTER TABLE `test_table` COLLATE='utf8_unicode_ci'

# test_table 테이블의 기본 조합 설정 변경 및 기존 데이터들 기본 조합 변경
ALTER TABLE `test_table`
	COLLATE='utf8_unicode_ci',
	CONVERT TO CHARSET utf8;

 

 

 

17. test_table 테이블 비우기

TRUNCATE `test_table`;

 

 

 

18. 외래키 설정

rooms_notices 테이블의 room_id 열에 대하여 fk_room_id 외래키를 생성하고, 외부 rooms 테이블을 참조해 room_id 를 외래 열로 설정합니다.

ALTER TABLE `rooms_notices`
	ADD CONSTRAINT `fk_room_id` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`room_id`) ON UPDATE NO ACTION ON DELETE NO ACTION

 

 

 

19. `table_name` 이라는 테이블의 AUTO_INCREASE 시작값을 1로 설정

 - AUTO_INCREASE 초기화

ALTER TABLE `table_name` AUTO_INCREMENT=1;

 

 

 

20. 특정 데이터 삭제

users 테이블에서 user_id 가 3인 데이터 삭제

DELETE FROM users WHERE user_id = 3

 

 

 

21. 테이블 데이터만 복사하기

old_table_name 이라는 테이블을 복사하여 new_table_name 테이블을 생성하면서 데이터 전부 붙여넣기

CREATE TABLE new_table_name SELECT * FROM old_table_name

 

 

 

22. 테이블 구조만 복사하기

old_table 이라는 테이블의 구조를 복사하여 new_table 이라는 테이블을 생성하기

CREATE TABLE IF NOT EXISTS `new_table` LIKE `old_table`;