스터디노트 (SQL 1~3)

Database 정의

• Database : 여러 사람이 공유하여 사용할 목적으로 체계화해 통합, 관리하는 데이터의 집합체
• DBMS(Database Management System) : 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 생성해주고 데이터베이스를 관리해주는 소프트웨어
• 관계형 데이터베이스(RDB : Relational Database) : 서로 간에 관계가 있는 데이터 테이블들을 모아둔 데이터 저장공간
• SQL(Structured Query Language) : 데이터베이스에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어
  - 데이터 정의 언어(DDL : Data Definition Language) : CREATE, ALTER, DROP 등의 명령어
  - 데이터 조작 언어(DML : Data Manipulation Language) : INSERT, UPDATE, DELETE, SELECT 등의 명령어
  - 데이터 제어 언어(DCL : Data Control Language) : GRANT, REVOKE, COMMIT, ROLLBACK 등의 명령어
  
  
  

---

Database 관리

• root 계정으로 mysql에 접속

% mysql -u root -p

• 현재 database 목록 확인

show databases;

• Database 이름을 지정하여 생성
  - testdb라는 이름으로 생성

create database testdb;

• 데이터베이스로 이동(사용)
  - testdb로 이동

use testdb;

• 데이터베이스 삭제
  - testdb 삭제

drop database testdb;

문제 1. mydb 라는 이름의 데이터베이스 만들고 확인

create database mydb;

show databases;

문제 2. mydb 를 삭제하고 확인

drop database mydb;

show databases;

---

User 관리

• 사용자 정보는 mysql에서 관리하므로 일단 mysql 데이터베이스로 이동 후 조회

use mysql;

select host, user from user;

• 현재 PC에서만 접속 가능한 사용자를 비밀번호와 함께 생성

create user 'usename'@'localhost' identified by 'password';

• 현재 PC에서 접속 가능한 사용자(noma, 1234) 생성
  

create user 'lee'@'localhost' identified by '1234';

• 외부에서 접속 가능한 사용자를 비밀번호와 함께 생성

create user 'usename'@'%' identified by 'password';

• 외부에서 접속 가능한 사용자(noma, 1234) 생성

 create user 'lee'@'%' identified by '5678';

• 접근 범위에 따라 같은 이름의 사용자여도 별도로 삭제해야 한다

drop user 'lee'@'localhost';

 drop user 'lee'@'%';

문제 1. 외부에서도 접속 가능한 사용자를 생성하고 확인(ID : zero, password : 1234)

create user 'zero'@'%' identified by '1234';

문제 2. 외부에서도 접속가능한 사용자 zero를 삭제하고 확인

drop user 'zero'@'%';

select host, user from user;

---

User 권한 관리

• 권한 관리를 실습하기 위한 Database(testdb) 생성

 create database testdb;

show databases;

• 권한 관리를 실습하기 위한 사용자 생성

create user 'lee'@'localhost' identified by '1234';

select host, user from user;

• 사용자에게 부여된 모든 권한 목록을 확인

 show grants for 'username'@'localhost';

• 현재 PC에 접근 가능한 계정의 권한 확인

show grants for 'lee'@'localhost';

• 사용자에게 특정 데이터베이스의 모든 권한을 부여

 grant all on dbname.* to 'username'@'localhost';

• 현재 PC에 접근 가능한 계정에게 testdb의 모든 권한을 부여

 grant all on testdb.* to 'lee'@'localhost';

show grants for 'lee'@'localhost';

• 사용자에게 특정 데이터베이스의 모든 권한을 삭제

 revoke all on dbname.* from 'username'@'localhost';

• 현재 PC에 접근 가능한 계정에게 testdb의 모든 권한을 삭제

 revoke all on testdb.* from 'lee'@'localhost';

 show grants for 'lee'@'localhost';

• 새로고침

 flush privileges;

문제 1. mydb의 모든 권한을 zero에게 부여하고 확인

create database mydb;

create user 'zero'@'localhost' identified by '1234';

show grants for 'zero'@'localhost';

grant all on mydb.* to 'zero'@'localhost';

show grants for 'zero'@'localhost';

문제 2. zero에게 부여한 mydb에 대한 권한 삭제하고 확인

 revoke all on mydb.* from 'lee'@'localhost';

 show grants for 'zero'@'localhost';

 drop user 'zero'@'localhost';

drop database mydb;

---

Table 생성

• 데이터베이스 생성
• zerobase 라는 이름의 데이터베이스 생성

create database zerobase default character set utf8mb4;

show databases;

• Table : 데이터베이스 안에서 실제 데이터가 저장되는 행태, 행(Row)과 열(Column)으로 구성된 데이터 모음

• Table 생성 문법

create table tablename
(
	columnname datatype,
    columnname datatype,
    ...
)

• id(int)와 name(varchar(16)) 컬럼을 가지는 mytable 이라는 이름의 테이블 생성

create table mytable(id int, name varchar(16));

use zerobase;

 create table mytable ( id int, name varchar(16) );

• Table 목록 확인 문법

show tables;

• Table 정보 확인 문법

desc tablename;

 desc mytable;

문제 1. name(varchar(16)), type(varchar(16))을 가진 animal 테이블을 생성하고 확인

create database testdb;

use testdb;

create table animal(name varchar(16), type varchar(16));

show tables;

desc animal;

drop database testdb;

---

Table 변경 & 삭제

• Table 이름 변경 문법

alter table tablename
rename new_tablename

- mytable 이름을 person으로 변경

 use zerobase;

alter table mytable rename person;

show tables;

• Table Column 추가 문법

alter table tablename
add column columnname datatype;

- person 테이블에 agee(double) 컬럼 추가 ``` desc person; ``` ``` alter table person add column agee double; ``` ``` desc person; ```

• Table Column 변경 문법 - Datatype

alter table tablename
modify column columnname datatype;

- person 테이블의 agee 컬럼의 데이터 타입을 int로 변경

alter table person modify column agee int;

desc person;

• Table Column 변경 문법 - Name

alter table tablename
change column old_columnname new_columnname new_datatype;

- person 테이블의 agee 컬럼 이름을 age로 변경

alter table person change column agee age int;

desc person;

• Table Column 삭제 문법

alter table tablename
drop column columnname;

- person 테이블의 age 컬럼을 삭제

alter table person drop column age;

desc person;

• Table 삭제 문법

drop table tablename;

- person 테이블 삭제

 drop table person;

문제 1. animal 테이블에 age(int) 컬럼을 추가하고 확인

show tables;

create database testdb;

show databases;

create table animal(name varchar(16), type varchar(16));

show tables;

desc animal;

alter table animal add column age int;

문제 2. animal 테이블에 old(int) 컬럼을 추가하고 확인

desc animal;

alter table animal add column old int;

desc animal;

문제 3. animal 테이블에서 age(int) 컬럼을 삭제하고 확인

alter table animal drop column age;

desc animal;

문제 4. animal 테이블에서 old(int) 컬럼 이름과 데이터 타입을 sex(char)로 변경하고 확인 (설명 : 컬럼 이름과 데이터 타입을 한번에 변경할 수 있다)

alter table animal change column old sex char;

desc animal;

문제 5. animal 테이블을 삭제하고 확인

drop table animal;

show tables;

💻 출처 : 제로베이스 데이터 취업 스쿨