20210618 (금) DB

MariaDB 테이블 생성 삭제 SELECT 문

 

 

설치과정 (MariaDB)

패키지 설치 mariadb-server

서비스 설정 /etc/my.cnf, /etc/my/cnf.d/*.cnf

  autocommit = 0 자동저장 비활성화

  skip-networking=1 네트워크 사용 비활성화 (원격 접속 불가)

  # mysql_secure_installation

서비스 활성화 mariadb

방화벽 설정 mysql

포트 번호 :  cat /usr/liv/firewalld/services/mysql.xml

 PORT = 3306

 

DML	SELECT INSERT UPDATE DELETE	데이터 조작어
DDL	CREATE ALTER DROP	데이터 정의어
DCL	GRANT REVOKE	데이터 제어어
TCL	COMMIT ROLLBACK	트랜잭션 제어어

 

※ 커밋설정

/etc/my.cnf.d/server.cnf

/etc/my.cnf

[mysqld]

 autocommit = 0 → autocommit 해제 : 롤백이 가능하다. 반대로 커밋을 까먹으면 변경된 데이터가 손실된다.

 

 

 

[root@dns html]# mysql -u root -p
Enter password: 
MariaDB [(none)]> USE knowledgepia;
Database changed
MariaDB [knowledgepia]> CREATE TABLE customers
    -> (cust_id INT NOT NULL AUTO_INCREMENT,
    -> cust_name CHAR(30) NOT NULL,
    -> cust_address CHAR(50) NULL,
    -> cust_email CHAR(30) NOT NULL,
    -> cust_zip INT NOT NULL DEFAULT 123456,
    -> PRIMARY KEY (cust_id)
    -> ) ENGINE=Aria;
Query OK, 0 rows affected (0.01 sec)
MariaDB [knowledgepia]> show tables;
+------------------------+
| Tables_in_knowledgepia |
+------------------------+
| customers              |
+------------------------+
1 row in set (0.00 sec)

MariaDB [knowledgepia]> describe tables;
ERROR 1146 (42S02): Table 'knowledgepia.tables' doesn't exist
MariaDB [knowledgepia]> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| cust_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(30) | NO   |     | NULL    |                |
| cust_address | char(50) | YES  |     | NULL    |                |
| cust_email   | char(30) | NO   |     | NULL    |                |
| cust_zip     | int(11)  | NO   |     | 123456  |                |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

 

MariaDB [knowledgepia]> describe customers;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| cust_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(30) | NO   |     | NULL    |                |
| cust_address | char(50) | YES  |     | NULL    |                |
| cust_email   | char(30) | NO   |     | NULL    |                |
| cust_date    | date     | NO   |     | NULL    |                |
| cust_zip     | int(11)  | NO   |     | 123456  |                |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

MariaDB [knowledgepia]> ALTER TABLE customers MODIFY cust_id BIGINT
    -> NOT NULL AUTO_INCREMENT;

                                                   ▶ 데이터 타입을 변경 (기본값 20)

Query OK, 2 rows affected (0.08 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [knowledgepia]> describe customers;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| cust_id      | bigint(20) | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(30)   | NO   |     | NULL    |                |
| cust_address | char(50)   | YES  |     | NULL    |                |
| cust_email   | char(30)   | NO   |     | NULL    |                |
| cust_date    | date       | NO   |     | NULL    |                |
| cust_zip     | int(11)    | NO   |     | 123456  |                |
+--------------+------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

 

MariaDB [knowledgepia]> alter table customers DROP COLUMN cust_date ;

                                                   ▶ 컬럼 삭제

MariaDB [knowledgepia]> alter table customers RENAME TO new_customers;

                                                   ▶ 테이블 이름 변경

MariaDB [knowledgepia]> delete FROM new_customers;

                                                   ▶ 테이블 내용삭제

MariaDB [knowledgepia]> DROP TABLE new_customers;

                                                   ▶ 테이블 전체삭제

 

SELECT 컬럼이름

FROM 테이블이름                →필수

WHERE 조건식                    → 조건에 해당하는 값만 출력

GROUP BY HAVING 조건식 2 → 그룹함수 사용 시 추가 조건식 설정

ORDER BY 정렬방식             → 선택적으로 오름차순/내림차순으로 정렬

;

 ※ 순서 상 order by 가 구문의 마지막에 위치

 

INSERT INTO 테이블이름 (컬럼이름1, 컬럼이름2 ) VALUES (데이터값1, 데이터값2)

UPDATE TO 테이블이름 SET 컬럼이름=변경할값 WHERE 조건식

DELETE FROM 테이블이름 WHERE 조건식

CREATE TABLE 테이블이름 (컬럼이름, 데이터 타입(&길이), 값입력시 제약조건, 키 설정 등)

ALTER TABLE 테이블이름 ADD/DROP/MODIFY/CHANGE 변경

DROP TABLE 테이블이름; 테이블 전체삭제

 

[root@dns test_db-master]# mysql -u root -p < employees.sql

 

MariaDB [employees]> SELECT emp_no, birth_date, first_name, last_name, gender 

FROM employees 

WHERE gender ="M" and emp_no >=499970

ORDER BY birth_date asc;
+--------+------------+------------+---------------+--------+
| emp_no | birth_date | first_name | last_name     | gender |
+--------+------------+------------+---------------+--------+
| 499975 | 1952-11-09 | Masali     | Chorvat       | M      |
| 499996 | 1953-03-07 | Zito       | Baaz          | M      |
| 499989 | 1954-05-26 | Keiichiro  | Lindqvist     | M      |
| 499982 | 1954-08-25 | Mohammed   | Pleszkun      | M      |
| 499998 | 1956-09-05 | Patricia   | Breugel       | M      |
| 499999 | 1958-05-01 | Sachin     | Tsukuda       | M      |
| 499980 | 1959-06-28 | Gino       | Usery         | M      |
| 499984 | 1959-08-31 | Kaijung    | Rodham        | M      |
| 499978 | 1960-03-29 | Chiranjit  | Kuzuoka       | M      |
| 499997 | 1961-08-03 | Berhard    | Lenart        | M      |
| 499979 | 1962-10-29 | Prasadram  | Waleschkowski | M      |
| 499970 | 1963-03-25 | Danai      | Hedayat       | M      |
| 499973 | 1963-06-03 | Lobel      | Taubman       | M      |
| 499993 | 1963-06-04 | DeForest   | Mullainathan  | M      |
| 499976 | 1963-08-20 | Guozhong   | Felder        | M      |
| 499990 | 1963-11-03 | Khaled     | Kohling       | M      |
| 499971 | 1963-12-28 | Uwe        | Uludag        | M      |
| 499985 | 1964-12-26 | Gila       | Lukaszewicz   | M      |
+--------+------------+------------+---------------+--------+

 

MariaDB [employees]> SELECT emp_no, birth_date, first_name, last_name 

FROM employees 

WHERE emp_no BETWEEN 49970 AND 49989 AND gender='M';
+--------+------------+------------+-----------+
| emp_no | birth_date | first_name | last_name |
+--------+------------+------------+-----------+
|  49972 | 1957-11-10 | Gladys     | Fraisse   |
|  49973 | 1963-10-28 | Stabislas  | Billawala |
|  49976 | 1964-10-25 | Miyeon     | Peternell |
|  49982 | 1961-03-16 | Huican     | Kalefeld  |
|  49983 | 1954-03-09 | Danil      | Lorch     |
|  49985 | 1964-06-17 | Phuoc      | Bazzichi  |
|  49988 | 1959-06-30 | Fumitake   | Rohrbach  |
+--------+------------+------------+-----------+

 

 

MariaDB [employees]> 

SELECT emp_no, birth_date, first_name, last_name 

FROM employees 

WHERE emp_no BETWEEN 49970 AND 49989 AND emp_no <> 49973 AND gender='M';

<>같지 않다 조건연산자 사용 가능

+--------+------------+------------+-----------+
| emp_no | birth_date | first_name | last_name |
+--------+------------+------------+-----------+
|  49972 | 1957-11-10 | Gladys     | Fraisse   |
|  49976 | 1964-10-25 | Miyeon     | Peternell |
|  49982 | 1961-03-16 | Huican     | Kalefeld  |
|  49983 | 1954-03-09 | Danil      | Lorch     |
|  49985 | 1964-06-17 | Phuoc      | Bazzichi  |
|  49988 | 1959-06-30 | Fumitake   | Rohrbach  |
+--------+------------+------------+-----------+

 

연산자 

산술 비교 연산자 : <, >, =, <=, <=, <>, !=, BETWEEN, IN

문자 연산자 : =, LIKE

          LIKE사용시, _ (한글자대체), %(0개이상의 글자 대체)

논리 연산자 : AND, OR, NOT

정렬 : ASC, DESC

 

 

 

MariaDB [employees]> 

SELECT emp_no, birth_date, first_name, last_name

FROM employees where emp_no BETWEEN 49970 AND 49989 AND emp_no<>49973 AND gender='M'

and MONTH(birth_date)='3';  → month(컬럼명)=값 을 통해 3월생만 출력 가능

+--------+------------+------------+-----------+
| emp_no | birth_date | first_name | last_name |
+--------+------------+------------+-----------+
|  49982 | 1961-03-16 | Huican     | Kalefeld  |
|  49983 | 1954-03-09 | Danil      | Lorch     |
+--------+------------+------------+-----------+

 

MariaDB [employees]> Select gender, count(*)
    -> from employees
    -> Group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| M      |   179973 |
| F      |   120051 |
+--------+----------+

 

MariaDB [employees]> SELECT emp_no, first_name, last_name 

FROM employees 

WHERE emp_no < 10010 AND first_name LIKE 'San%';  → 부등호대신 LIKE로 문자열 조건식
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|  10008 | Saniya     | Kalloufi  |
+--------+------------+-----------+

 

MariaDB [employees]> select emp_no, first_name, last_name 

from employees 

where emp_no<10050 AND first_name LIKE '_____';  → 5글자만 검색할 수도 있음
+--------+------------+------------+
| emp_no | first_name | last_name  |
+--------+------------+------------+
|  10003 | Parto      | Bamford    |
|  10014 | Berni      | Genin      |
|  10021 | Ramzi      | Erde       |
|  10023 | Bojan      | Montemayor |
|  10029 | Otmar      | Herbst     |
|  10030 | Elvis      | Demeyer    |
|  10032 | Jeong      | Reistad    |
|  10034 | Bader      | Swan       |
|  10035 | Alain      | Chappelet  |
|  10040 | Weiyi      | Meriste    |
|  10049 | Basil      | Tramer     |
+--------+------------+------------+
11 rows in set (0.00 sec)

 

 

MariaDB [employees]> 

select emp_no, first_name, last_name 

from employees 

where emp_no < 10010 AND first_name LIKE '_a%'; → 와일드카드로 한글자 조건을 줄 수 있음.
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|  10003 | Parto      | Bamford   |
|  10008 | Saniya     | Kalloufi  |
+--------+------------+-----------+

MariaDB [employees]> select emp_no, first_name, last_name 

from employees 

where emp_no in (10010, 10020, 10030); → IN 연산자를 통해 다양한 조건값을 선택할 수 있음
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|  10010 | Duangkaew  | Piveteau  |
|  10020 | Mayuko     | Warwick   |
|  10030 | Elvis      | Demeyer   |
+--------+------------+-----------+

 

MariaDB [employees]> 

select gender, count(emp_no) AS emp_no_COUNT 

from employees 

where emp_no <10020 

group by gender;                     → 사번10020 이하 직원들의 성별별 인원수를 카운트하라,
+--------+--------------+
| gender | emp_no_COUNT |
+--------+--------------+
| M      |           11 |
| F      |            8 |
+--------+--------------+

 

MariaDB [employees]> 

select MONTH(birth_date) AS MONTH , COUNT(MONTH(birth_date)) as HOWMANY 

from employees 

where emp_no<10100 

group by MONTH(birth_date) 

order by month(birth_date) ;  → 사원번호가 10100보다 낮은 직원들을 생일을 각 월별로 카운트하여 월별로 정렬하여라
+-------+---------+
| MONTH | HOWMANY |
+-------+---------+
|     1 |       6 |
|     2 |       9 |
|     3 |       2 |
|     4 |       8 |
|     5 |      10 |
|     6 |       8 |
|     7 |      11 |
|     8 |       8 |
|     9 |      13 |
|    10 |       9 |
|    11 |       8 |
|    12 |       7 |
+-------+---------+

 

MariaDB [employees]> 

select MONTH(birth_date) AS MONTH , COUNT(MONTH(birth_date)) as HOWMANY 

from employees 

where emp_no<10300 AND MONTH(birth_date)in ('3','6') 

group by MONTH(birth_date) 

order by month(birth_date) ;   → 10300이하 직원 중 3월,6월 생인 직원의 수를 카운트하라
+-------+---------+
| MONTH | HOWMANY |
+-------+---------+
|     3 |      17 |
|     6 |      19 |
+-------+---------+

 

제약조건

PK primary Key 기본키

FK foreign Key 외래키

 NOT NULL

UQ unique Key 유일키

 

 

 

MariaDB [employees]> 

select distinct employees.emp_no, salary, employees.first_name, employees.last_name 

from salaries, employees 

where employees.emp_no<10005 and YEAR(salaries.to_date) >'2020';

 

 

 

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

 

사용자 생성

 

CREATE USER 'kim'@'localhost' IDENTIFIED BY '1234';

 kim 유저는 로컬에서만 접속가능하며 pw 는 1234로 설정하여 생성.

 'kim'@'IP주소' 또는 'kim' @ '%' 등으로 특정 IP, 어디서나 등으로 생성 가능.

 

GRANT ALL PRIVILEGES ON *.* TO 'kim'@'localhost';

  → 모든 권한을 모든디비.모든테이블 -> kim에게 부여  : 너무 높은 권한 - 지양

GRANT SELECT, UPDATE, INSERT, DELETE ON employees.salaries TO 'kim'@'localhost';

  → 특정디비와 특정테이블에만 특정 명령어 권한 부여 :  일반적인 권한 부여

REVOKE SELECT, UPDATE, INSERT, DELETE ON employees.salaries FROM 'kim'@'localhost';

  권한 제거 시에 revoke ~ FROM user

DROP USER 'kim';

  유저삭제

 

 

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

실습

employees 테이블에서 작업하세요.
1. emp_no 값이 10100 이상 10110 이하인 값을 확인하세요.

MariaDB [employees]> select * from employees where emp_no BETWEEN 10100 and 10110;

2. emp_no 값이 10020 이상인 값을 모두 삭제하세요.

MariaDB [employees]> delete from employees where emp_no>=10020;

3. 테이블의 모든 값을 확인하세요.

MariaDB [employees]> select * from employees;

4. gender 가 M 인 항목들만 확인하세요.

MariaDB [employees]> select * from employees where gender='M';

5. gender 가 M 이면서 emp_no 가 10010 보다 큰 값을 확인하세요.

MariaDB [employees]> select * from employees where gender='M' AND emp_no >10010;

6. emp_no 가 홀수인 값만 확인하세요. ( MOD 함수 사용 )

MariaDB [employees]> select * from employees where mod(emp_no, 2)=1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10011 | 1953-11-07 | Mary       | Sluis     | F      | 1990-01-22 |
|  10013 | 1963-06-07 | Eberhardt  | Terkki    | M      | 1985-10-20 |
|  10015 | 1959-08-19 | Guoxiang   | Nooteboom | M      | 1987-07-02 |
|  10017 | 1958-07-06 | Cristinel  | Bouloucos | F      | 1993-08-03 |
|  10019 | 1953-01-23 | Lillian    | Haddadi   | M      | 1999-04-30 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.01 sec)

7. birth_date 가 1950년대(50~59)의 사람만 확인하세요.

MariaDB [employees]> select * from employees where YEAR(birth_date) BETWEEN '1950' AND '1959';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10011 | 1953-11-07 | Mary       | Sluis     | F      | 1990-01-22 |
|  10014 | 1956-02-12 | Berni      | Genin     | M      | 1987-03-11 |
|  10015 | 1959-08-19 | Guoxiang   | Nooteboom | M      | 1987-07-02 |
|  10017 | 1958-07-06 | Cristinel  | Bouloucos | F      | 1993-08-03 |
|  10018 | 1954-06-19 | Kazuhide   | Peha      | F      | 1987-04-03 |
|  10019 | 1953-01-23 | Lillian    | Haddadi   | M      | 1999-04-30 |
+--------+------------+------------+-----------+--------+------------+
14 rows in set (0.00 sec)

8. first_name 에 'a' 가 들어가는 경우만 찾으세요. (위치무관)

MariaDB [employees]> select * from employees WHERE first_name LIKE '%a%';

9. first_name 에 'n' 이 연속 2번 들어가는 경우만 찾으세요. (위치무관)

MariaDB [employees]> select * from employees WHERE first_name LIKE '%nn%';

10. list_name 에 'i' 가 3번 들어가는 경우만 찾으세요. (위치무관, 연속아님)

MariaDB [employees]> select * from employees WHERE last_name LIKE '%i%i%i%';

11. gender 가 M 인 항목의 hire_date 를 오늘 날짜로 바꾸세요.

MariaDB [employees]> update employees SET hire_date='2021-06-18' where gender='M';
Query OK, 11 rows affected (0.01 sec)
Rows matched: 11  Changed: 11  Warnings: 0
MariaDB [employees]> select * from employees where hire_date='2021-06-18';

12. emp_no 가 10005, 10010, 10015 인 값들을 지우세요.

MariaDB [employees]> delete from employees where emp_no in (10005,10010,10015);
Query OK, 3 rows affected (0.00 sec)

13. 모두 원래 상태로 되돌리고 데이터베이스를 종료합니다.

 

 

 

 

 

 

 

 

 

 

 

select p.name as 교수이름, m.major_title as 학과명from class.professor p         join class.major m              on p.bl_major_id = m.major_id;              select p.name as 교수이름, m.major_title as 학과명from class.professor p         cross join class.major m              on p.bl_major_id = m.major_id;              

 

select p.name as 교수이름, m.major_title as 학과명

From class.professor p         

inner join class.major m              

on p.bl_major_id = m.major_id;