[생활코딩]
1)DATABASE2 MySQL - 7.SQL과 테이블의 구조
2)mysql 데이터 관리-조회(SELECT)
3)mysql 데이터관리-조회(group)
4)mysql 데이터관리-조회(join)
&
----------------------
summarize:
- SELECT is the clause we use every time we want to query information from a database.
- AS renames a column or table.
- DISTINCT return unique values.
- WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.
- LIKE and BETWEEN are special operators.
- AND and OR combines multiple conditions.
- ORDER BY sorts the result.
- LIMIT specifies the maximum number of rows that the query will return.
- CASE creates different outputs.
- COUNT(): count the number of rows
- SUM(): the sum of the values in a column
- MAX()/MIN(): the largest/smallest value
- AVG(): the average of the values in a column
- ROUND(): round the values in the column
Aggregate functions combine multiple rows together to form a single value of more meaningful information.
GROUP BY is a clause used with aggregate functions to combine data from one or more columns.
HAVING limit the results of a query based on an aggregate property.
JOIN will combine rows from different tables if the join condition is true.
LEFT JOIN will return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table.
Primary key is a column that serves a unique identifier for the rows in the table.
Foreign key is a column that contains the primary key to another table.
CROSS JOIN lets us combine all rows of one table with all rows of another table.
UNION stacks one dataset on top of another.
WITH allows us to define one or more temporary tables that can be used in the final query.
데이터 베이스를 넘어서서 표를 다룰 준비가 되었다!
1. SQL이란?
sql이라고 하는 컴퓨터 언어를 배울것이다. (Structured Query Language)
- 표를 작성하는 것 정리 정돈을 하는 것을 좀 유식하게 구조화되었다고 하고 영어로는 structured
- 데이터 베이스에 의해 데이터를 넣어줘 똑같은 효과를 삭제해줘 등 데이터베이스에게 뭔가 요청한다라는게 query
- 데이터베이스에게 아무렇게나 얘기한다고 해주는 것이 아니라 데이터베이스도 이해할수있고 저도 이해할 수 있는 약속에 따라 데이터 베이스 서버에게 요청을 해야지만 알아들을수있다.. 이때 사용하는 언어
sql이라는 컴퓨터 언어의 두가지 특징
1)sql은 앞으로 우리가 만나게 될 어떤 컴퓨터 언어보다 쉽다. (html도 쉬움)
2)sql 이라는 컴퓨터 언어는 관계형 데이터 베이스라는 카테고리에 속하는 제품들이 공통적으로 데이터베이스 서버를 제어할때 사용하는 언어이다. 표준화까지 되어있는..그렇기 때문에 압도적인 다수의 데이터베이스 시스템이 sql을 통해 작동하고 하루에도 여러분 모르게 sql 통해 수많은 정보 서비스를 접하고 있다..-> 한마디로 가성비가 아주 뛰어난 컴퓨터 언어이다!!
2. 테이블의 구조와 관련 된 용어 정리
표를 영어로는 table이라고 함.
X축을 보면 수평으로 되어있는 것이 row = record = 행 ---> 데이터 자체,하나하나
Y축을 보면 수직으로 되어있는 것이 column = 열 --> 데이터의 타입, 데이터의 구조
3. SELECT
- 가장 많이 사용하고 중요한 명령
데이터베이스에 있는 데이터를 조회할수있는 것. 데이터 베이스에 있는 데이터를 다양한 방법으로 가져와야되기 때문에 선택함에 있어 까다롭다..
문법
SELECT 칼럼명1, 칼럼명2 #어떤 컬럼의 데이터들만 부분적으로 조회하고 싶을때 /전체하고싶으면*
[FROM 테이블명 ] #[] 대괄호는 전부다 생략이 가능한 부분
[GROUP BY 칼럼명]
[ORDER BY 칼럼명 [ASC | DESC]]
[LIMIT offset, 조회 할 행의 수]
#📌 기술되어있는 명령어 순서대로 명령이 내려져야 된다.
데이터
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` tinyint(4) NOT NULL,
`name` char(4) NOT NULL,
`sex` enum('남자','여자') NOT NULL,
`address` varchar(50) NOT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES (2, '박재숙', '남자', '서울', '1985-10-26 00:00:00');
INSERT INTO `student` VALUES (1, '이숙경', '여자', '청주', '1982-11-16 00:00:00');
INSERT INTO `student` VALUES (3, '백태호', '남자', '경주', '1989-2-10 00:00:00');
INSERT INTO `student` VALUES (4, '김경훈', '남자', '제천', '1979-11-4 00:00:00');
INSERT INTO `student` VALUES (8, '김정인', '남자', '대전', '1990-10-1 00:00:00');
INSERT INTO `student` VALUES (6, '김경진', '여자', '제주', '1985-1-1 00:00:00');
INSERT INTO `student` VALUES (7, '박경호', '남자', '영동', '1981-2-3 00:00:00');
예제
> student라고하는 테이블에 있는 모든 컬럼에 들어있는 각각의 행들을 다 조회. ```sql SELECT * FROM student; # * 모든 테이블 조회 from 조회를 하는 그 대상이 되는 테이블 지정 ``` student라고하는 테이블에 있는 name, birthday 컬럼의 행들을 조회 ```sql SELECT name, birthday FROM student; #지정된 컬럼들만 조회 ``` student라고하는 테이블에서 모든 컬럼 가져오기 where은 operation하려고 하는 작업의 대상이 되는 행을 지정. 즉 id값이 3인 행에 대해 조회해라. ```sql SELECT * FROM student WHERE id=3; ``` sex라고하는 컬럼명으로 남자 값이 들어있고 & address 컬럼명의 서울이라고하는 값 대입. -> 성별이 남자이고 주소가 서울인 사람을 조회해라. '' -> 작은 따옴표는 grave accent와 비슷하지만 다름. 이름을 더 분명하게 지정할때 쓴다 grave accent -> 셀렉트라고 하는 테이블을 사용할 경우 저렇게 해야된다. (성별이 남자고 서울이 주소인 사람 AND) ```sql SELECT * FROM student WHERE sex='남자' AND address='서울'; ``` 문자는 단따옴표 숫자는 그냥 기록해도 된다. (여자이거나 주소가 서울인사람 OR) ```sql SELECT * FROM student WHERE sex='여자' OR address='서울'; ``` 행을 몇개 가져올건지 .. 리밋 지정안하고 select from 테이블 하면 백만개 데이터가 튀어나올수있으니!! 📌 셀렉트문 쓸때는 where나 limit 를 꼭 쓰!자! ```sql SELECT * FROM student LIMIT 1; ``` limit 앞에있는 첫 숫자는 오프셋. 컴퓨터는 0부터 센다. LIMIT offset, row count 뒤에 오는 숫자는 행을 몇개 가져올건지 데이터 조회할때 결과를 페이지 단위로 끊을때 사용. 이건 게시판 만들때 요긴하게 사용한다. ```sql SELECT * FROM student LIMIT 1,1; SELECT * FROM student LIMIT 2,1; SELECT * FROM student LIMIT 3,1; ``` where과 limit 조합해서 사용. 두개의 데이터 가져오고 두개의 데이터는 성이 남자인 사람들의 리스트.
> ```sql SELECT * FROM student WHERE sex='남자' LIMIT 2; ```
4. GROUP BY
- 특정 칼럼을 기준으로 데이터를 그룹핑함
- 그룹이라고 하는 것은 여러개의 데이터 중에 데이터가 어떻게 구성되어 있는가 그것을 원자화 시키는 것도 그룹핑이 할수있는 역할이지만 그 각각의 그룹핑 된 컬럼에 기준으로 해서 어떤 특정한 다른 컬럼의 합계나 평균이나 기타 등등의 작업들을 할 수 있는게 그룹이라고 하는 기능이다.
문법
SELECT * FROM 테이블명 GROUP BY 그룹핑 할 기준 칼럼명
데이터
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` tinyint(4) NOT NULL,
`name` char(4) NOT NULL,
`sex` enum('남자','여자') NOT NULL,
`address` varchar(50) NOT NULL,
`distance` INT NOT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
INSERT INTO `student` VALUES (2, '박재숙', '남자', '서울', 10, '1985-10-26 00:00:00');
INSERT INTO `student` VALUES (1, '이숙경', '여자', '청주', 200, '1982-11-16 00:00:00');
INSERT INTO `student` VALUES (3, '백태호', '남자', '경주', 350, '1989-2-10 00:00:00');
INSERT INTO `student` VALUES (4, '김경훈', '남자', '제천', 190, '1979-11-4 00:00:00');
INSERT INTO `student` VALUES (8, '김정인', '남자', '대전', 200, '1990-10-1 00:00:00');
INSERT INTO `student` VALUES (6, '김경진', '여자', '제주', 400, '1985-1-1 00:00:00');
INSERT INTO `student` VALUES (7, '박경호', '남자', '영동', 310, '1981-2-3 00:00:00');
>
예제
student라고하는 테이블에 있는 데이터 중에 성과 관련있는 값을 가져온다.
성을기준으로 그룹핑
어떠한 성들이 있는지 추출할때 복잡한 데이터 중에서 깔끔하게 추출이 가능
select sex from student group by sex; #여자와 남자가 산발적으로 있는데 데이터 중 여자 남자끼리 묶으니 결과는 여자 남자가 나옴.
select sex,sum(distance), avg(distance) from student group by sex;
5. ORDER BY
저장된 칼럼을 기준으로 행을 정렬
문법
SELECT * FROM 테이블명 ORDER BY 정렬의 기준으로 사용할 열 [DESC | ASC]
데이터
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` tinyint(4) NOT NULL,
`name` char(4) NOT NULL,
`sex` enum('남자','여자') NOT NULL,
`address` varchar(50) NOT NULL,
`distance` INT NOT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
INSERT INTO `student` VALUES (2, '박재숙', '남자', '서울', 10, '1985-10-26 00:00:00');
INSERT INTO `student` VALUES (1, '이숙경', '여자', '청주', 200, '1982-11-16 00:00:00');
INSERT INTO `student` VALUES (3, '백태호', '남자', '경주', 350, '1989-2-10 00:00:00');
INSERT INTO `student` VALUES (4, '김경훈', '남자', '제천', 190, '1979-11-4 00:00:00');
INSERT INTO `student` VALUES (8, '김정인', '남자', '대전', 200, '1990-10-1 00:00:00');
INSERT INTO `student` VALUES (6, '김경진', '여자', '제주', 400, '1985-1-1 00:00:00');
INSERT INTO `student` VALUES (7, '박경호', '남자', '영동', 310, '1981-2-3 00:00:00');
예제
distance라고하는 숫자들을 기준으로해서 여기 있는 행들을 정렬한다.
dsc를 asc로 바꾸면 내림차순으로 바뀐다.
select * from student order by distance desc;
address를 함께 정렬
기본적으로 distance중심으로 정렬이 되고 address asc는 distance안에 동일한 데이터가 있는 경우 address 중심으로 다시 정렬.
select * from student order by distance desc, address asc;
6. JOIN
테이블을 분할하는 방법이랑 조인을 통해 분할된 테이블을 마치 하나의 테이블처럼 만들어보겠음.
1)여러개의 테이블 사용하기
데이터의 규모가 커지면서 하나의 테이블로 정보를 수용하기가 어려워지면 테이블을 분활하고 테이블 간의 관계성을 부여한다.
데이터
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` tinyint(4) NOT NULL,
`name` char(4) NOT NULL,
`sex` enum('남자','여자') NOT NULL,
`address` varchar(50) NOT NULL,
`distance` INT NOT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
INSERT INTO `student` VALUES (2, '박재숙', '남자', '서울', 10, '1985-10-26 00:00:00');
INSERT INTO `student` VALUES (1, '이숙경', '여자', '청주', 200, '1982-11-16 00:00:00');
INSERT INTO `student` VALUES (3, '백태호', '남자', '경주', 350, '1989-2-10 00:00:00');
INSERT INTO `student` VALUES (4, '김경훈', '남자', '제천', 190, '1979-11-4 00:00:00');
INSERT INTO `student` VALUES (8, '김정인', '남자', '제주', 400, '1990-10-1 00:00:00');
INSERT INTO `student` VALUES (6, '김경진', '여자', '제주', 400, '1985-1-1 00:00:00');
INSERT INTO `student` VALUES (7, '박경호', '남자', '영동', 310, '1981-2-3 00:00:00');
> ### 예제 *** 위의 예제 중 address는 distnace와 관련되어 있기 때문에 location이라는 별도의 테이블로 분할 할 수 있다. ```sql DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` tinyint(4) NOT NULL, `name` char(4) NOT NULL, `sex` enum('남자','여자') NOT NULL, `location_id` tinyint(4) NOT NULL, #젤 중요한 파트 `birthday` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > DROP TABLE IF EXISTS `location`; CREATE TABLE `location` ( `id` tinyint UNSIGNED NOT NULL AUTO_INCREMENT , `name` varchar(20) NOT NULL , `distance` tinyint UNSIGNED NOT NULL , #지명에 해당하는 거리 PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;; ``` ### 📌 [desc student]: 테이블 구조 확인
### 📌 [desc location]
address랑 distance라고하는 컬럼이 사라지고 location_id가 사라짐.
insert 이용해서 로케이션 데이터 추가하기!!
INSERT INTO `location` VALUES (1, '서울', 10);
INSERT INTO `location` VALUES (2, '청주', 200);
INSERT INTO `location` VALUES (3, '경주', 255);
INSERT INTO `location` VALUES (4, '제천', 190);
INSERT INTO `location` VALUES (5, '대전', 200);
INSERT INTO `location` VALUES (6, '제주', 255);
INSERT INTO `location` VALUES (7, '영동', 255);
INSERT INTO `location` VALUES (8, '광주', 255);
📌 [지역별로 거리들이 테이블로 맵핑됨]
> insert 이용해서 student 데이터 추가하기!! ```sql INSERT INTO `student` VALUES (1, '이숙경', '여자', 1, '1982-11-16 00:00:00'); INSERT INTO `student` VALUES (2, '박재숙', '남자', 2, '1985-10-26 00:00:00'); INSERT INTO `student` VALUES (3, '백태호', '남자', 3, '1989-2-10 00:00:00'); INSERT INTO `student` VALUES (4, '김경훈', '남자', 4, '1979-11-4 00:00:00'); INSERT INTO `student` VALUES (6, '김경진', '여자', 5, '1985-1-1 00:00:00'); INSERT INTO `student` VALUES (7, '박경호', '남자', 6, '1981-2-3 00:00:00'); INSERT INTO `student` VALUES (8, '김정인', '남자', 5, '1990-10-1 00:00:00'); ``` ### 📌 [location_id에 숫자들이 적힘]
--> 이 데이터들이 연관 연결성을 가지고 있다는걸 쉽게 알아채기 힘듦. 숫자간의 연결성을 사람이 찾는게 쉽지않으니 join을통해 쉽게 해보겠다.
2)JOIN
테이블간의 관계성에 따라서 복수의 테이블을 결합, 하나의 테이블인 것처럼 결과를 출력
JOIN의 종류
- OUTTER JOIN : 매칭되는 행이 없어도 결과를 가져오고 매칭되는 행이 없는 경우 NULL로 표시한다.LEFT JOIN과 RIGHT JOIN이 있다.
- INNER JOIN : 조인하는 두개의 테이블 모두에 데이터가 존재하는 행에 대해서만 결과를 가져온다.
예제 (LEFT JOIN)
가장 많이 사용되는 조인의 형태
SELECT s.name, s.location_id, l.name AS address, l.distance
FROM student AS s LEFT JOIN location AS l ON s.location_id = l.id;
- Location에서 제주를 삭제 후 OUTTER JOIN(LEFT JOIN)과 INNER JOIN의 차이를 비교
- from 뒤에 student AS s라고 한것임. (as는 엘리엇이라는 것의 약자. 엘릿엇은 별명이라는 뜻.)
- 별명을 왜 붙이나면 테이블의 이름이 길기 때문에/ 따라서 컬럼 이름 명명할때 s.name = sudent.name
- 왼쪽에 있는 테이블을 기준으로 해서 오른쪽에 있는 테이블의 데이터를 가져 오다는 뜻
- left join하고 그 뒤에다가 테이블의 이름을 적으면 location student라고하는 테이블에다가 location이라고 하는 테이블을 조인한다.
- ON: student라는 테이블과 location 테이블을 결합했는데 그냥 결합만 하면 안되고 조건을 붙여야되는데 그때 쓰는 것
- s.location_id 컬럼과 location 테이블의 id 값이 같다라는 것을 on이라는 키워드를 통해 기술.
- 나중에 테이블에 있는 데이터를 가져올 때 기본적으로 student라는 테이블에 있는 데이터를 쭉 가져온다음, student 한 테이블에 있는 location_id 컬럼에 있는 숫자에 해당되는 locaion 테이블에 행들을 결과를 붙여서 출력해줌.
- 결과값을 제한하고 싶으면 where (특정 행만 가져오기)
- 만약 제주도에 distance가 10인 결과를 가져오고싶으면 where라고 치고 별명 중에 Location은 l이기 때문에 --> WHERE l.distance=10; ///거리가 10키로 인 사람을 가져오는데 이숙경이 거기 해당함.
- WHERE s.location_id=3;
DELETE FROM location WHERE name='제주';
>
SELECT s.name, s.location_id, l.name AS address, l.distance
FROM student AS s LEFT JOIN location AS l ON s.location_id = l.id;
📌 [차이]
outter join은 테이블간의 결함을 했을 때 한쪽 테이블의 데이터가 없다고 하더라도 그것을 가져와서 null로 표시하는 특징이 있고
> ### 📌 [차이] null 한쪽 테이블에 데이터가 없다면 그 데이터가 없기 때문에 행 자체를 아예 결과에서 빼버린다. > ```sql SELECT s.name, s.location_id, l.name AS address, l.distance FROM student AS s INNER JOIN location AS l ON s.location_id = l.id; ```
.
7. 📌 CODECADEMY 모르겠는거 ..
MULTIPLE TABLES
[ With ]
Often times, we want to combine two tables, but one of the tables is the result of another calculation.
Let’s return to our magazine order example. Our marketing department might want to know a bit more about our customers. For instance, they might want to know how many magazines each customer subscribes to. We can easily calculate this using our orders table:
SELECT customer_id,
COUNT(subscription_id) AS 'subscriptions'
FROM orders
GROUP BY customer_id;
This query is good, but a customer_id isn’t terribly useful for our marketing department, they probably want to know the customer’s name.
We want to be able to join the results of this query with our customers table, which will tell us the name of each customer. We can do this by using a WITH clause.
WITH previous_results AS (
SELECT ...
...
...
...
)
SELECT *
FROM previous_results
JOIN customers
ON _____ = _____;
- The WITH statement allows us to perform a separate query (such as aggregating customer’s subscriptions)
- previous_results is the alias that we will use to reference any columns from the query inside of the WITH clause
- We can then go on to do whatever we want with this temporary table (such as join the temporary table with another table)
'STUDY > SQL' 카테고리의 다른 글
[SQL] 날짜 년월로 group by하는 여러가지 방법들 (0) | 2021.05.09 |
---|---|
SQL 문제를 풀어보자 (0) | 2021.05.09 |
MySQL 데이터베이스 한번에 끝내기 (0) | 2021.05.09 |
[생활코딩] 관계형 데이터 모델링 (26-32강의 : 물리적데이터 모델링/역정규화) (0) | 2021.05.09 |
[생활코딩] 관계형 데이터 모델링 (22-25강의 : 정규화) (0) | 2021.05.09 |