코딩묻은 디자이너

DB 테이블 리스트 조회 방법

회사에서 업무를 하다보면 테이블 조회할 일이 많은데

ERD가 문서화되어있지 않으면 일단 내가 어떤 테이블을 조회할 수 있는지 어떤 테이블이 있는지조차 몰라서

문의가 들어왔을 때

'엥...' 하고 있을 때가 있다

최근 새로운 시스템 업무를 조금씩 인수인계 받기 시작하면서 생소한 테이블도 조회를 하고 있는데 앞으로 내가 어떤 테이블을 다룰지 미리미리 확인을 하기 위해서 권한을 부여받은 DB의 테이블을 미리 확인해보려고 하였다.

MySQL

SHOW TABLES;

MySQL에서는 show table로 현재 선택된 데이터베이스 내의 모든 테이블 출력 가능

SHOW TABLES FROM 데이터베이스명;

특정 데이터베이스의 테이블을 조회하려면 데이터베이스 명을 입력한다

SELECT table_name 
FROM information_schema.tables
WHERE table_schema = '데이터베이스명' AND table_name LIKE '%키워드%';

특정 조건을 만족하는 테이블만 필터링 하기 위해서 infomation_schema.table 을 사용할 수있다

Oracle

Oracle에서는 ALL_TABLES 뷰를 통해 데이터베이스의 모든 테이블을 조회할 수 있지만

테이블을 스키마별로 관리하고 있으므로 스키마에 따라결과가 달라질 수 있다.

SELECT table_name 
FROM all_tables
WHERE owner = '스키마명';

테이블 이름으로 필터링 가능

SELECT table_name
FROM all_tables
WHERE owner = '스키마명' AND table_name LIKE '%키워드%';

Oracle에서는 ALL_TABLES, USER_TABLES, DBA_TABLES 세 가지 테이블 뷰가 있는데, 각 뷰는 다음과 같은 용도로 사용됩니다:

• ALL_TABLES: 현재 사용자가 접근할 수 있는 모든 테이블
• USER_TABLES: 현재 사용자에게 속한 테이블만 조회
• DBA_TABLES: 데이터베이스 내 모든 테이블 조회 (DBA 권한 필요)

최근 개인 프로젝트로 만드는 중인 타자연습 사이트

JSON파일을 만들어서 로컬에서 던어를 불로오도록 만들었는데 DB와 프론트를 연결해보고 싶어졌다.

항상 팀 프로젝트만 해와서 다른 분들이 DB나 백을 해주셨던터라 그쪽으로는 아는게 없어서 Chat GPT에게 질문하면서

JSON 파일을 테이블로 만들어봤다

1. Chat GPT에게 제안받은 방법

JSON 파일 -> csv파일 -> MySQL에 테이블로 생성

Chat GPT가 제안한 방법은 가지고 있는 JSON파일을 일단 CSV파일로 생성한 후

MySQL에서 다시 테이블로 변환하는 과정을 추천해줬다

일단 Json 파일을 csv로 만드는 javaScript 코드를 생성하여 csv파일을 생성하는 것 까지는 어렵지 않았으나

이제 막 설치한 MySQL Workbench는 설정이 안된게 많았던건지 여러가지 오류를 발생시키며 진행이 안되어서 3시간이나 쓰고 말았다

2. MySQL Workbrench를 사용해서 테이블을 생성하는 방법 *매우간단

스키마에서 오른쪽 마우스 클릭 후

"Table Data Import Wizard"를 클릭해준다.

table에 넣기 원하는 json 파일 경로를 지정

새로운 테이블을 만들거나, 기존에 테이블에 넣기를 선택한다

이후 원하는 Field Type을 작성해주면 완료

--

id, value와 같은 컬럼 명을 사용하여서 생성하였는데, 이런... 단어들은 뭔가 컬럼명으로 사용해도 괜찮은걸까? 좀 더 고민해보고 컬럼 명은 수정하도록 해야겠다

Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.

해당 오류는 어떤 DB의 데이터 테이블을 가지고 올지 선언을 해주지 않았을 때 해당 오류가 발생한다.

해결방안으로는

1. 테이블 앞에 DB이름을 작성해준다

2. 자주 사용하는 스키마를 기본 스키마(Default Schema)로 지정해준다.

1. 테이블 앞에 DB이름 작성

회사에서는 이 방법으로 DB 조회를 하고있다.

SELECT *
FROM DB이름.테이블 이름
WHERE ...

위와 같이 DB이름을 먼저 입력 후 뒤에 테이블 이름을 작성해주면 된다.

나의 경우 1046오류를 내가 기존에 가지고 있는 scv파일을 새로운 테이블로 새로 작성하는 과정에서 만났는데

스키마가 생성되지 않은 상태라서 스키마부터 생성해주었다

스키마 생성 방법

MySQL Workbench의 왼쪽 상단

"Create a new schema in the connected server"

을 클릭하여 새로운 스키마를 생성해준다

이름과 캐릭터셋을 설정해주면 된다

utf8로 설정하여 만든다

2. 자주 사용하는 스키마를 기본 스키마(Default Schema)로 지정

개인 프로젝트를 진행할 예정이므로 가장 자주 사용할 DB를 기본 스키마로 지정해주었다

오른쪽 마우스 클릭 후 Set as Default Schema 클릭

만약 네비게이터에서 스키마가 보이지 않는다면

탭의 하단에서 Schemas를 클릭하여 찾아준다

'mysql'은(는) 내부 또는 외부 명령, 실행할 수 있는 프로그램, 또는 배치 파일이 아닙니다

원인

환경 변수가 설정이 되어있지 않아 발생하는 오류

해결방법

시스템 환경 변수 편집 접속 > 고급 > 환경변수 클릭 > 아래 시스템 변수 중 path 클릭 > 편집 클릭 > 가장 하단에 MySQL 경로 삽입 > 확인

1. 시스템 환경 변수 편집 접속

3번째 탭인 고급에서 아래의 환경 변수(N)... 클릭

시스템 변수 중 Path 를 선택하고, 편집 버튼을 누른다

해당 편집 창에서 MySQL 의 경로를 붙여넣어준다

나의 경우는

C:\Program File\MySQL\MySQL Server 8.0\bin

해당 경로를 입력하고 확인을 눌러준다

그 후 cmd 창에서 입력을 시도하면 Enter password : 가 나오고, 처음 입력받은 비밀번호를 입력해주면 CMD창에서 MySQl 을 실행할 수 있다.

MySQL설치

프로젝트를 MySQL과 연동하기 위해서 MySQL을 설치 할 예정이다.

타 블로그 등을 참고하여 MySQL을 설치해보았다.

MySQL 다운로드 주소

https://dev.mysql.com/downloads/windows/installer/8.0.html

사이트에 들어간 후 쿠키 관련 팝업이 나오면 모든 쿠키를 허용해준다.

위 버전은 웹이라 아래걸로 다운받아준다

로그인/회원가입 페이지가 나오는데 하단의

No thanks, just start my download.를 클릭해서 가입하지 않고 다운받아준다. (몰론 가입해두 된다)

다운로드 한 파일을 열면 아래 Installer가 나온다.

Choosing a Setup Type

설치 유형을 선택할 수 있다.

위 페이지에서는 필요한 것들을 골라서 설치하기 위해 Custom을 선택해준다

처음 들어가면 3개의 대분류 폴더가 있는데 각 폴더에서 하나씩 제품을 선택해서 설치해준다

경로

MySQL servers > MySQL Server -MySQL Server 

Applications > MySQL Workbench -MySQL Workbench 8.0.xx

Documentation > Samples and Examples > Samples and Examples 8.0.xx

Development Computer를 선택하고, TCP/IP체크 도니 상태에서 Port는 3306으로 설정해준다

MySQL은 기본설정으로 127.0.0.1 주소의 3306 번의 포트로 접속하게 되어있는데, 이전에 사용하던 MySQL이 있다면 위와같이 빨간 느낌표가 뜨는 오류가 발생할 수 있다.

해당 오류가 발생한다면 기존에 사용하고있는 MySQL의 3306 포트는 삭제한다면 해결할 수 있다.

Use Strong Password Encryption for Authentication

MySQL 8 supports a new authentication based on improved stronger SHA256-based password methods. it is recommended that all new MySQL Server installations use this method going forward.

Attention : this new authentication plugin on the server side require new versions of connectors and clients which add support for this new 8.0 default authentication(caching_sha2_password authentication).


Currently MySQL 8.0 Connectors and community drivers which use libmysqlclient 8.0 support this new method. if clients and applications cannot be updated to support this new authentication method, the MySQL 8.0 Server can be configured to use the legacy MySQL Authentication Method below.

인증에 강력한 암호 암호화 사용

MySQL 8은 향상된 더 강력한 SHA256 기반 암호 방식을 기반으로 하는 새로운 인증을 지원합니다. 앞으로 모든 새로운 MySQL Server 설치에서 이 방식을 사용하는 것이 좋습니다.

주의: 서버 측의 이 새 인증 플러그인에는 이 새 8.0 기본 인증(caching_sha2_password 인증)을 지원하는 새로운 버전의 커넥터 및 클라이언트가 필요합니다.


현재 libmysqlclient 8.0을 사용하는 MySQL 8.0 커넥터 및 커뮤니티 드라이버가 이 새 방식을 지원합니다. 이 새 인증 방식을 지원하도록 클라이언트 및 애플리케이션을 업데이트할 수 없는 경우 MySQL 8.0 서버는 아래의 레거시 MySQL 인증 방식을 사용하도록 구성할 수 있습니다.

Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)

Using the old MySQL 5.x legacy authentication method should only be considered in the following cases :

- If applications cannot be updated to use MySQL 8 enabled Connectors and drivers.
- For cases wherre re-compilation of an existing application is not feasible.
- An updated, language specific connector or driver is not yet available.

Security Guidance : When possible, we highly recommend taking needed steps towards upgrading your applications, libraries, and database servers to the new stronger authentication. This new method will significantly improve your security.

레거시 인증 방법 사용(MySQL 5.x 호환성 유지)

이전 MySQL 5.x 레거시 인증 방법을 사용하는 것은 다음과 같은 경우에만 고려해야 합니다:

- MySQL 8 지원 커넥터 및 드라이버를 사용하도록 애플리케이션을 업데이트할 수 없는 경우.
- 기존 애플리케이션의 재컴파일이 불가능한 경우.
- 업데이트된 언어별 커넥터 또는 드라이버를 아직 사용할 수 없습니다.

보안 지침 : 가능하면 응용 프로그램, 라이브러리 및 데이터베이스 서버를 새로운 더 강력한 인증으로 업그레이드하기 위해 필요한 조치를 취할 것을 강력히 권장합니다. 이 새로운 방법은 보안을 크게 향상시킬 것입니다.

위의 선택창이 추천되는 선택지지만, 나는 파이썬과의 연동을 진행할 예정이라 아래 선택지를 선택하였다.

다른 블로그들을 확인했을 때에도 아래 선택지를 선택한 분들이 많았다

Accounts and Roles 에서는 비밀번호를 설정한다.

Windows Service Name은 전통적으로 MySQL을 많이 사용한다고 한다.

기본 설정은 MySQL80으로 설정되어있지만, MySQL로 변경하여준다.

Server File Permissions 에서 서버 권한을 설정할 수 있다.

그룹 관지자 그룹만 권한을 부여하도록 가장 위에 있는것을 체크한다.

Apply Configuration 모두 설치 완료되면 finish 클릭

아래 비밀번호를 입력하고 Check 클릭 

Start MySQL  Workbench after setup 을 클릭하고 종료하면 바로 MySQL Workbench가 실행된다.

설치가 완료되면 클릭 후 설치 과정에서 입력한 비밀번호를 설정하면 정상적으로 접속이 가능하다

참고한 문서

https://hongong.hanbit.co.kr/mysql-%EB%8B%A4%EC%9A%B4%EB%A1%9C%EB%93%9C-%EB%B0%8F-%EC%84%A4%EC%B9%98%ED%95%98%EA%B8%B0mysql-community-8-0/