mysql에 새로운 사용자 추가하기

로컬호스트에서만 사용

CREATE USER 'minibrary'@'localhost' IDENTIFIED BY 'some_password';
GRANT ALL PRIVILEGES ON *.* TO 'minibrary'@'localhost'
    ->     WITH GRANT OPTION;

어디서든지 접속 가능

CREATE USER 'minibrary'@'%' IDENTIFIED BY 'some_password';
GRANT ALL PRIVILEGES ON *.* TO 'minibrary'@'%'
    ->     WITH GRANT OPTION;

mysqladmin reload, mysqladmin refresh, mysqladmin flush-xxx 등의 명령어가 사용 가능한 관리자 계정

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

비밀번호가 없는 그냥 더미 계정

CREATE USER 'dummy'@'localhost';

테스트용 mysql 덤프 데이터를 받기

git clone https://github.com/datacharmer/test_db.git

mysql 에 추가 하기

test_db 디렉토리로 이동 해서

mysql < employee.sql

명령어 이것 저것

SHOW databases;
USE database_name;
SHOW tables;
SHOW full columns from table_name;

테스트로 추가한 employees DB를 사용해서 2000년 1월 1일 이후 입사한 종업원 보기

DB명은 employees

USE employees;
SHOW FULL TABLES;
+----------------------+------------+
| Tables_in_employees  | Table_type |
+----------------------+------------+
| V_SALARY             | VIEW       |
| V_SALARY2            | VIEW       |
| current_dept_emp     | VIEW       |
| departments          | BASE TABLE |
| dept_emp             | BASE TABLE |
| dept_emp_latest_date | VIEW       |
| dept_manager         | BASE TABLE |
| employees            | BASE TABLE |
| salaries             | BASE TABLE |
| titles               | BASE TABLE |
+----------------------+------------+
10 rows in set (0.00 sec)
SHOW FULL COLUMNS FROM employees;
+------------+---------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field      | Type          | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+------------+---------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| emp_no     | int(11)       | NULL               | NO   | PRI | NULL    |       | select,insert,update,references |         |
| birth_date | date          | NULL               | NO   |     | NULL    |       | select,insert,update,references |         |
| first_name | varchar(14)   | utf8mb4_general_ci | NO   |     | NULL    |       | select,insert,update,references |         |
| last_name  | varchar(16)   | utf8mb4_general_ci | NO   |     | NULL    |       | select,insert,update,references |         |
| gender     | enum('M','F') | utf8mb4_general_ci | NO   |     | NULL    |       | select,insert,update,references |         |
| hire_date  | date          | NULL               | NO   |     | NULL    |       | select,insert,update,references |         |
+------------+---------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
6 rows in set (0.00 sec)
SELECT * FROM employees WHERE hire_date >= '2000-01-01';
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name  | gender | hire_date  |
+--------+------------+------------+------------+--------+------------+
|  47291 | 1960-09-09 | Ulf        | Flexer     | M      | 2000-01-12 |
|  60134 | 1964-04-21 | Seshu      | Rathonyi   | F      | 2000-01-02 |
|  72329 | 1953-02-09 | Randi      | Luit       | F      | 2000-01-02 |
| 205048 | 1960-09-12 | Ennio      | Alblas     | F      | 2000-01-06 |
| 222965 | 1959-08-07 | Volkmar    | Perko      | F      | 2000-01-13 |
| 226633 | 1958-06-10 | Xuejun     | Benzmuller | F      | 2000-01-04 |
| 227544 | 1954-11-17 | Shahab     | Demeyer    | M      | 2000-01-08 |
| 422990 | 1953-04-09 | Jaana      | Verspoor   | F      | 2000-01-11 |
| 424445 | 1953-04-27 | Jeong      | Boreale    | M      | 2000-01-03 |
| 428377 | 1957-05-09 | Yucai      | Gerlach    | M      | 2000-01-23 |
| 463807 | 1964-06-12 | Bikash     | Covnot     | M      | 2000-01-28 |
| 499553 | 1954-05-06 | Hideyuki   | Delgrande  | F      | 2000-01-22 |
+--------+------------+------------+------------+--------+------------+
12 rows in set (0.00 sec)

입사일 순으로 오름차순 정렬해보기

ORDER BY를 사용하는데, 이는 WHERE이 있을 경우 WHERE 뒤에 와야 한다.

SELECT * FROM employees WHERE hire_date >= '2000-01-01' ORDER BY hire_date ASC;

결과

+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name  | gender | hire_date  |
+--------+------------+------------+------------+--------+------------+
|  60134 | 1964-04-21 | Seshu      | Rathonyi   | F      | 2000-01-02 |
|  72329 | 1953-02-09 | Randi      | Luit       | F      | 2000-01-02 |
| 424445 | 1953-04-27 | Jeong      | Boreale    | M      | 2000-01-03 |
| 226633 | 1958-06-10 | Xuejun     | Benzmuller | F      | 2000-01-04 |
| 205048 | 1960-09-12 | Ennio      | Alblas     | F      | 2000-01-06 |
| 227544 | 1954-11-17 | Shahab     | Demeyer    | M      | 2000-01-08 |
| 422990 | 1953-04-09 | Jaana      | Verspoor   | F      | 2000-01-11 |
|  47291 | 1960-09-09 | Ulf        | Flexer     | M      | 2000-01-12 |
| 222965 | 1959-08-07 | Volkmar    | Perko      | F      | 2000-01-13 |
| 499553 | 1954-05-06 | Hideyuki   | Delgrande  | F      | 2000-01-22 |
| 428377 | 1957-05-09 | Yucai      | Gerlach    | M      | 2000-01-23 |
| 463807 | 1964-06-12 | Bikash     | Covnot     | M      | 2000-01-28 |
+--------+------------+------------+------------+--------+------------+
12 rows in set (0.17 sec)

자주 사용하는 명령어: DB 만들고 모든 권한을 가진 사용자 추가하기

create database DBNAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL privileges ON DBNAME.* TO USERNAME@localhost IDENTIFIED BY 'PASSWORD';
flush privileges;

MYSQL 설치 후 root 비밀번호 변경 등

mysql_secure_installation