어차피 나중에 보게 되어있기에 최종 정리를 해본다. 한번할떄 기억하면 좋지만 나의 기억력은
금붕어
이기에 혹여나 나중에 보더라도 기억하라는 의미...
1) brew install mysql
2) mysql.server start - 서비스 ON
Starting MySQL
.. SUCCESS!
3) mysql_secure_installation - 초기화 root 비번 설정
주의!
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
=> yes로 하면 dbever등의 원격 root 접속에서 에러를 맛보게 될것이다...
임시 비번 q1w2e3r4
- mysql.server start로 실행했다면, mysql.server stop 으로 종료.
- brew services start mysql로 실행했다면, brew services stop mysql로 종료
mysql -u root -p -초기 접속 후 진행
CREATE DATABASE studydb default CHARACTER SET UTF8;
SHOW DATABASES;
create user 'study'@'localhost' identified by 'qwer1234';- 계정 생성
GRANT ALL PRIVILEGES ON studydb.* TO study@localhost ; - 권한 부여
drop user 'study'@'localhost'; - 계정 삭제
접속 완료
set global validate_password.policy=LOW
create table myapp_board(
board_no int not null,
title varchar(255) not null,
content text null,
writer int not null,
password varchar(100) null,
view_count int default 0,
created_date datetime default now(),
category int not null
);
create table myapp_member(
member_no int not null,
name varchar(20) not null,
email varchar(50) not null,
password varchar(100) not null,
gender char(1) not null,
created_date date default (current_date())
);
-- myapp_member 테이블 예제 데이터
insert into myapp_member(member_no, name, email, password, gender)
values(1, 'aaa', 'aaa@test.com', sha1('1111'), 'W');
insert into myapp_member(member_no, name, email, password, gender)
values(2, 'bbb', 'bbb@test.com', sha1('1111'), 'M');
insert into myapp_member(member_no, name, email, password, gender)
values(3, 'ccc', 'ccc@test.com', sha1('1111'), 'W');
insert into myapp_member(member_no, name, email, password, gender)
values(4, 'ddd', 'ddd@test.com', sha1('1111'), 'M');
insert into myapp_member(member_no, name, email, password, gender)
values(5, 'eee', 'eee@test.com', sha1('1111'), 'W');
insert into myapp_member(member_no, name, email, password, gender)
values(6, 'fff', 'fff@test.com', sha1('1111'), 'M');
-- myapp_board 테이블 예제 데이터
insert into myapp_board(board_no, title, content, writer, password, category)
values(11, '제목1', '내용', 1, sha1('1111'), 1);
insert into myapp_board(board_no, title, content, writer, password, category)
values(12, '제목2', '내용', 1, sha1('1111'), 1);
insert into myapp_board(board_no, title, content, writer, password, category)
values(13, '제목3', '내용', 3, sha1('1111'), 1);
insert into myapp_board(board_no, title, content, writer, password, category)
values(14, '제목4', '내용', 4, sha1('1111'), 1);
insert into myapp_board(board_no, title, content, writer, password, category)
values(15, '제목5', '내용', 5, sha1('1111'), 2);
insert into myapp_board(board_no, title, content, writer, password, category)
values(16, '제목6', '내용', 5, sha1('1111'), 2);
insert into myapp_board(board_no, title, content, writer, password, category)
values(17, '제목7', '내용', 5, sha1('1111'), 2);
alter table myapp_board
add constraint primary key (board_no),
modify column board_no int not null auto_increment;
alter table myapp_member
add constraint primary key (member_no),
modify column member_no int not null auto_increment;
alter table myapp_member
add constraint myapp_member_uk unique (email);
-- 게시판 작성자에 대해 외부키 설정
alter table myapp_board
add constraint myapp_board_fk foreign key (writer) references myapp_member (member_no);
DB 조회 : show databases;
DB 접속 : use <DB 이름>; -- 필수!!
select <컬럼 이름> from <테이블 이름>
mysql> select user, host from user;
ERROR 1146 (42S02): Table 'studydb.user' doesn't exist