[2021.02.17] SQL 공부

www.youtube.com/watch?v=HXV3zeQKqGY

 

데이터베이스란?

Any collection of related information

ex) phone book, shopping list, todo list

 

Databases can be stored in different ways.

ex) on paper, in your mind, on a computer

 

Computers are great at keeping track of large amounts of information

 

예시) Amazon.com vs Shopping List

Amazon.com	Shopping List
trillions of pieces of info	10-20
info is extremely valueable and critical	info is for convenience sake
security is essential	security is not important

 

Database Management Systems (DBMS)

a special software program that helps users create and maintain a database

-make it easy to manage

-handles security

-backups, import/export data

-interacts with software applications

It allows you to preform C.R.U.D opertaions and other administrative tasks.

C.R.U.D = Create. Read. Update. Delete

 

---

Two Types of Databases

1) Relational Databases (SQL) 

-organize data into one or more tables (정형된 수치화)

-a lot like spreadsheet

 

2) Non-Relational (noSQL)

-organize data is anything but a traditional table

-documents, graphs...

 

 

Relational DataBases(SQL)

예시) Student Table

ID	Name	Major
1	Jack	Biology
2	Kate	Sociology

 

RDBMS (Relational Database Management Sytems)

-help users create and maintain a relational database

-ex. mySQL, Oracle... etc

 

Structured Query Language (SQL)

standardized language for interacting with RDBMS

used to perform C.R.U.D operations

 

Non-Relational Databases (noSQL)

-documents (JSON, XML...)

-Graph (relaional nodes)

-key-value hash

 

 

Database Queries

Queries are request made to the databse management system for specific information

(A google search is a query)

 

 

---

Tables & Keys

예시) Student Table

ID	Name	Major
1	Jack	Biology
2	Kate	Sociology
3	Clarie	English
4	Jack	Biology
5	Mike	Comp. Sci

columns = attribute (id, name, major)

row = a single student's info

 

primary keys = always unique (differeniate btw info that share the same info)

-surrogate key = random number to unequally identify has no purpose

-national key = had a purpose in the real world (social security number)

 

foreign key = primary key in a different table

can find the relationship btw tables or even inside the table

 

sometimes primary keys can be more than one. It can be two foreign keys together.

 

---

Structured Query Language (SQL)

a language used for interacting with Relaional Database Management Systems(RDBMS)

-create &mange

-design & create

-perform administrative

 

implementations vary between systems

 

actually a hybrid language (4 types of language in one)

DQL

-used to query the database for information

DDL

-used for defining database schemas

DCL

-used for controlling access to the data in the database.

-user & permissions management

DML

-used for inserting, updating and deleting data

 

Queries

a set of instructions given to the RDBMS that tell the RDBMS what information you want it to retrieve for you

 

example)

SELECT employee.name, employe.age
FROM employee
WHERE employee.salary > 30000;

 

---

MySQL 설치

dev.mysql.com/downloads/mysql/

MySQL :: Download MySQL Community Server

여기서 다운받으면 된다.

강의에서는 popsql를 사용하는데 무료체험이 14일밖에 안되길래 workbench로 사용하기로 했다. (popsql이 더 userfriendly 한듯)

이 짓을 한시간반을 한거같다.

---

새로운 Table 만들기

Data Types

INT 
DECIMAL(M,N)  
VARCHAR(1) 
BLOB 
DATE 
TIMESTAMP 

INT --정수

DECIMAL(M,N) --실수 (10,4)=(10개, 4자리까지)

VARCHAR --텍스트 (길이)

BLOB --binary large object(이미지, 파일)

DATE --날짜 YYYY-MM-DD

TIMESTAMP --시간까지 YYYY-MM-DD HH:MM:SS

 

CREATE TABLE student(
    student_id INT,
    name VARCHAR(20),
    major VARCHAR(20),
    PRIMARY KEY(student_id)
);

 

생성된 table을 확인하려면,

DESCRIBE student;

 

생성된 table을 없애고 싶으면,

DROP TABLE student;

 

**** 한줄씩 실행하기 때문에 조심하기!

 

 

새로운 field 추가하고 싶은면,

ALTER TABLE student ADD gpa DECIMAL(3,2); 

 

field를 없애고 싶으면,

ALTER TABLE student DROP COLUMN gpa;

 

새로운 정보를 추가하고 싶으면,

INSERT INTO student VALUES(2,'Kate', 'Sociology');

 

모든 정보말고 몇개의 새로운 정보를 추가하고 싶으면,

INSERT INTO student(student_id, name) VALUES(3, 'Claire');