MySQL 쿼리 기본, 데이터베이스/테이블/데이터 생성, 복사, 삭제

데이터베이스/MySQL

MySQL 쿼리 기본, 데이터베이스/테이블/데이터 생성, 복사, 삭제

앙큼아기 2022. 6. 29. 09:58

✅ Query(쿼리)의 기본은

▶ select 열_명1, 열_명2, ... (OR 모든 열이면 *) from 테이블_명

▶ where 조건; 이다.

❇️ Query(쿼리)란?

데이터베이스에게 특정한 데이터를 보여달라는 클라이언트(사용자)의 요청이다.
흔히 ‘쿼리문을 작성한다'고 많이 말하는데, 이는 데이터베이스에서 원하는 정보를 가져오는 코드를 작성한다는 정도로 이해하면 된다. 쿼리문을 잘 작성한다는 건 데이터베이스에서 필요한 정보에 빠르게 접근하고, 데이터를 능숙하게 핸들링 한다는 말로도 볼 수 있다.

(참고출처) https://hengbokhan.tistory.com/133

❇️ SELECT란?

select 쿼리는 테이블에서 원하는 컬럼의 데이터를 조회합니다.

(참고출처) https://extbrain.tistory.com/49 (MySQL 테이블 조회 기본)

select * from mysql.user; ⇒ mysql.user는 mysql 데이터베이스, user 테이블의 의미다.
centos 사용자_명 | *12.............. 은 centos 패스워드의 해시(hash) 값으로 외부에서 함부로 읽지 못하게 하기 위한 보안설정이다.

✅ user 테이블에서 사용자, 호스트, 패스워드 정보 확인 및 삭제

▶ MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

IP 주소 중에서 ::1 식으로 보이면 IPv6 의 주소다. ⇒ 2^128개의 주소 가능
192.168.100.200 식으로 보이면 IPv4의 주소다. ⇒ 2^32개의 주소
root는 root끼리 centos는 centos끼리 해시가 동일한 걸 확인할 수 있다.

▶ 쓸데없는 user를 없앨 때는 drop user ‘root’@’centos7’;

▶ 다시 select user, host, password from user;로 없어질 걸 확인가능하다.

✅ 기타 확인 코드

▶ 데이터베이스 상태를 볼 때는 status;를 해서 Connection id, Current database, Current user 등의 현재 데이터베이스에 대한 여러 상태 정보를 보고,

▶ 오늘의 날짜 select curdate();

▶ 버전 확인 select version();

💡 유튜브의 IPv6 주소 살펴보기

[centos@CentOS7 ~]$ su Password: [root@CentOS7 centos]# nslookup

www.youtube.com Server: 192.168.100.2 Address: 192.168.100.2#53

Non-authoritative answer: www.youtube.com canonical name = youtube-ui.l.google.com. Name: youtube-ui.l.google.com Address: 216.58.220.110 Name: youtube-ui.l.google.com Address: 142.250.196.142

...

Name: youtube-ui.l.google.com Address: 2404:6800:4004:808::200e Name: youtube-ui.l.google.com Address: 2404:6800:4004:827::200e Name: youtube-ui.l.google.com Address: 2404:6800:4004:826::200e Name: youtube-ui.l.google.com Address: 2404:6800:4004:825::200e

⇒ 유튜브는 일부는 IPv6 주소를 준비해둔 것이다.

✅ 데이터 베이스 생성

▶ MariaDB [mysql]> create database centos_db; Query OK, 1 row affected (0.00 sec)

▶ MariaDB [mysql]> use centos_db; Database changed ▶ MariaDB [centos_db]> show tables; Empty set (0.00 sec)

▶ MariaDB [centos_db]> create table centos_tbl ( -> id int not null auto_increment, -> first_name varchar(20) not null, -> last_name varchar(20) not null, -> city varchar(20), -> phone varchar(15), -> primary key(id) -> ); Query OK, 0 rows affected (0.01 sec)

✅ 테이블 만들기 설명

create table B_tbl ( id int not null, ⇒ id 열+명, int 데이터_타입, not null 제약조건(constraints) 비어있으면 안됨 fname varchar(20) not null, ⇒ varchar(20) 가변 데이터타입(variable charactor) 20, lname varchar(20) not null, phone varchar(15), primary key (id) ); ⇒ 테이블 내에서 유일하고 비어있지 않은 열_명, reference(=foreign) key, ...

❇️ auto_increment (값 자동증가)

(참고출처) https://blog.naver.com/PostView.naver?blogId=imf4&logNo=220762181574

❇️ primary key

해당 테이블의 식별자 역할을 하는 제약조건으로 테이블에 하나만 설정할 수 있는 키
primary key로 설정한 컬럼에서는 중복이 절대로 들어가면 안된다. 즉 데이터의 유일성 보장이 되어야 한다.
null 값은 절대로 허용이 안된다.

❇️ unique key

테이블 내에 항상 유일해야 하는 값. 중복을 허용하지 않는다.
해당 칼럼에 입력되는 데이터가 각각 유일하다는 것을 보장하기 위한 제약조건
null 값도 허용된다.

⇒ 크게 보면 primary key는 unique key에 속해있다고 보면 될 것이다.

⇒ 기본적으로 둘다 유일한 키이지만, null 값에 따라 primary key와 unique key로 나눌 수 있다.

❇️ foreign key

테이블 내의 열중 다른 테이블의 기본키를 참조하는 열을 말한다.

(참조출처) https://snepbnt.tistory.com/68 (간단한 정의)

(참조출처) https://chartworld.tistory.com/16 (디테일한 설명)

💻 실습1

▶ mysql -u root -p 입력후 패스워드

d. paul 사용자 생성과 삭제는

▶ mysql> create user 'paul'@''localhost' identified by 'rootoor'; 해서 생성해주고

▶ mysql> **drop user 'paul'@'localhost';**식으로 해서 삭제한다.

e. 사용자 root의 패스워드 변경은

▶ mysql> update user set password=PASSWORD('rootoor') where user='root'; 해준다.

f. root가 로컬로 로그인하는 것을 허용하고, 모든 DB와 Table(.)에 모든 권한을 준다면

▶ mysql> grant all privileges on . to 'root'@'localhost' identified by 'rootoor'; 과

▶mysql> grant all privileges on . to 'root'@'%' identified by 'rootoor'; 해서 로컬과 원격 호스트에서도 root로 들어오게 해준다.

g. 권한을 갱신한다면

▶ mysql> flush privileges; 해주고,

▶ quit; 해서 mysql> 프롬프트를 벗어난다.

💻 실습2. B 데이터 베이스 만들고, 그 안에 B 테이블 넣기

▶ [root@CentOS7 centos]# mysql -u root -prootoor (u 뒤에는 무조건 띄어쓰기, p는 붙여도 됨) Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 14 Server version: 5.5.68-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

▶ MariaDB [(none)]> create database B_db; Query OK, 1 row affected (0.00 sec)

▶ MariaDB [(none)]> use B_db; Database changed ▶ MariaDB [B_db]> create table B_tbl ( -> id int not null, -> fname varchar(20) not null, -> lname varchar(20) not null, -> phone varchar(15), -> primary key (id) ); Query OK, 0 rows affected (0.01 sec)

▶ MariaDB [B_db]> select * from B_tbl; Empty set (0.00 sec)

✅ 테이블 구조 및 내용 보기

select * from B_tbl; 하면 B_tbl에 저장된 모든 데이터를 봄
desc B_tbl; 하면 테이블의 구조만 봄 (describe)

Database changed ▶ MariaDB [centos_db]> show tables; +---------------------+ | Tables_in_centos_db | +---------------------+ | centos_tbl | +---------------------+ 1 row in set (0.00 sec)▶ MariaDB [centos_db]> select id, first_name, city from centos_tbl; Empty set (0.00 sec)
MariaDB [centos_db]> create table B_tbl ( -> id int not null, -> fname varchar(20) not null, -> lname varchar(20) not null, -> phone varchar(15), -> primary key (id) ); Query OK, 0 rows affected (0.01 sec)
▶ MariaDB [centos_db]> desc centos_tbl; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | NO | | NULL | | | last_name | varchar(20) | NO | | NULL | | | city | varchar(20) | YES | | NULL | | | phone | varchar(15) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
▶ MariaDB [B_db]> use centos_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

✅ 데이터 넣기

insert into centos_tbl (id, first_name, phone) values('03', 'Woo', '222-2222');

▶ MariaDB [centos_db]> insert into centos_tbl values('01', 'Bee', 'You', 'Seoul', '111-1111'); Query OK, 1 row affected (0.00 sec)

▶ MariaDB [centos_db]> insert into centos_tbl values('02', 'Bee', 'You', 'Seoul', ''); Query OK, 1 row affected (0.00 sec)

▶ MariaDB [centos_db]> insert into centos_tbl (id, first_name, phone) values('03', 'Woo', '222-2222'); Query OK, 1 row affected, 1 warning (0.00 sec)

▶ MariaDB [centos_db]> select * from centos_tbl; +----+------------+-----------+-------+----------+ | id | first_name | last_name | city | phone | +----+------------+-----------+-------+----------+ | 1 | Bee | You | Seoul | 111-1111 | | 2 | Bee | You | Seoul | | | 3 | Woo | | NULL | 222-2222 | +----+------------+-----------+-------+----------+ 3 rows in set (0.00 sec)

✅ centos 화면보호기 설정하기

Application - System tools - settings - Power - Never

✅ 테이블 복사

create table paul_tbl LIKE centos_tbl;

▶ MariaDB [centos_db]> create table paul_tbl LIKE centos_tbl; Query OK, 0 rows affected (0.01 sec)

▶ MariaDB [centos_db]> select * from paul_tbl; Empty set (0.01 sec)

✅ 테이블 내용 복사하기

create table mary_tbl SELECT * FROM centos_tbl;

▶ MariaDB [centos_db]> create table mary_tbl SELECT * FROM centos_tbl; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

▶ MariaDB [centos_db]> select * from mary_tbl; (데이터도 보여주세요) +----+------------+-----------+-------+----------+ | id | first_name | last_name | city | phone | +----+------------+-----------+-------+----------+ | 1 | Bee | You | Seoul | 111-1111 | | 2 | Bee | You | Seoul | | | 3 | Woo | | NULL | 222-2222 | +----+------------+-----------+-------+----------+ 3 rows in set (0.00 sec)

✅ 테이블 만들고 내용 넣기

insert into tom_tbl SELECT * FROM centos_tbl;

▶ MariaDB [centos_db]> create table tom_tbl LIKE centos_tbl; Query OK, 0 rows affected (0.01 sec)

▶ MariaDB [centos_db]> insert into tom_tbl SELECT * FROM centos_tbl; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

❔ 일부만 옮기는 게 가능할까?

▶ MariaDB [centos_db]> create table xyz_tbl like centos_tbl; Query OK, 0 rows affected (0.00 sec)

▶ MariaDB [centos_db]> insert into xyz_tbl SELECT id, last_name, phone from centos_tbl; ERROR 1136 (21S01): Column count doesn't match value count at row 1

Oracle은 가능하나, MySQL은 불가하다!

Oracle DB와 MySQL DB의 SQL 문법은 유사하다 ⇒ 이 말은 조금 다른 것도 있다는 뜻이다.1이 0보다 적다는 ‘틀만 복사해가라’는 뜻.
ex) create table ABC_tbl LIKE centos_tle where 1<0;

▶️ [root@CentOS7 centos]# mysql -u root -prootoor mysql -h localhost Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 15 Server version: 5.5.68-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL 데이터베이스 쓰겠다는 것, localhost로 들어간다는 거다.

✅ 테이블 내 데이터 삭제하기

▶ MariaDB [mysql]> use centos_db; (데이터베이스를 centos로 접속) Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

▶ MariaDB [centos_db]> select * from tom_tbl; +----+------------+-----------+-------+----------+ | id | first_name | last_name | city | phone | +----+------------+-----------+-------+----------+ | 1 | Bee | You | Seoul | 111-1111 | | 2 | Bee | You | Seoul | | | 3 | Woo | | NULL | 222-2222 | +----+------------+-----------+-------+----------+ 3 rows in set (0.00 sec)

▶ MariaDB [centos_db]> delete from tom_tbl -> where id=3; Query OK, 1 row affected (0.04 sec)

▶ MariaDB [centos_db]> select * from tom_tbl; +----+------------+-----------+-------+----------+ | id | first_name | last_name | city | phone | +----+------------+-----------+-------+----------+ | 1 | Bee | You | Seoul | 111-1111 | | 2 | Bee | You | Seoul | | +----+------------+-----------+-------+----------+ 2 rows in set (0.00 sec)

테이블에서 데이터 삭제는 delete ~ 구문을 쓰고,
테이블 자체 삭제는 drop table table_name; 식으로 해준다.
(참조출처) https://www.lesstif.com/dbms/mysql-table-drop-95879602.html
❇️ MySQL 은 IF EXISTS 구문을 지원하므로 아래와 같이 테이블이 존재할 경우 삭제하도록 하면 에러를 방지할 수 있다. DROP TABLE if exists table_name;

❇️ 그룹화하기

MySQL에서 유형별로 갯수를 가져오고 싶은데, 단순히 COUNT 함수로 데이터를 조회하면 전체 갯수만을 가져옵니다. 이렇게 유형별로 갯수를 알고 싶을 때는 컬럼에 데이터를 그룹화 할 수 있는 GROUP BY를 사용하는 것입니다.
GROUP BY를 사용할 때는 두가지를 기억해야 합니다.
- 특정 컬럼을 그룹화 하는 GROUP BY
- 특정 컬럼을 그룹화한 결과에 조건을 거는 HAVING
WHERE랑 HAVING을 헷갈리는 경우가 많은데 WHERE는 그룹화 하기 전이고, HAVING은 그룹화 후에 조건입니다.

(참고출처) https://extbrain.tistory.com/56 (MySQL 그룹화하여 데이터 조회 (GROUP BY))

order by ~ (정렬하겠다) 등이 옵션으로 붙을 수 있다.

(참고출처) https://extbrain.tistory.com/51 (추가예시 확인용)

✅ 삭제하기, 가지치기

drop ~은 테이블이나 데이터베이스를 삭제하고
delete ~는 테이블 내의 데이터를 삭제한다.
truncate(가지치다) ~하면 테이블 내의 모든 데이터만 삭제하고 테이블 틀은 놔둔다.▶️ MariaDB [centos_db]> select * from xyz_tbl; Empty set (0.00 sec)

▶️ MariaDB [centos_db]> desc xyz_tbl; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | first_name | varchar(20) | NO | | NULL | | | last_name | varchar(20) | NO | | NULL | | | city | varchar(20) | YES | | NULL | | | phone | varchar(15) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
▶️ MariaDB [centos_db]> truncate xyz_tbl; Query OK, 0 rows affected (0.00 sec)

알아두면 좋은 개념

💡 해쉬와 암호화

Cloud는 말그대로 구름, 그 안에 가상머신들을 만들어둔다. 여기에 centos를 깔 수 있다. 아마존 클라우드에 들어가서 가상머신을 만들어 작업하고 있었다. 이 사용자의 패스워드가 털린 것이다. 가상머신을 1000여개 만들어 비트코인을 채굴한 것이다.

해시는 푼다? 아니다. Reverse가 성립할 수 없다. 수만개의 단어를 해시로 만들어 비교하는 것이다. CPU가 많아야 하는데, 요즘 그래픽 카드에 메모리와 CPU를 때려박는다. 이를 GPU라고 부른다. 이번 일은 AWS에서 가상머신을 만들어 비트코인을 채굴한 셈이다.