[DataBase] SQL(Structured Query Language)

SQL (Structured Query Language)

• 데이터 베이스 언어로 주로 관계형데이터베이스(Relational Database)에서 사용한다.
  • Mysql, Oracle, SQlite, PostgresSQL
• 구조화된 쿼리 언어 (Structured Query Language)
• 데이터베이스용 프로그래밍 언어이다.
• 데이터베이스에 쿼리를 보내어 데이터를 조회, 삽입, 삭제등 제어 할 수 있다.
• 데이터가 구조화된 테이블을 사용하는 데이터베이스에서 사용 가능하다.
• 데이터의 구조가 고정되어있지 않은 데이터베이스들을 NoSQL이라고 한다. (MongoDB, TSDB 등)

Query

• '질의문'이라는 뜻으로 원하는 데이터를 필터링 하기위해 사용한다
• http에서도 쿼리를 사용하여 리소스를 제어한다. - url-path뒤에 사용한다. newpost/73?type=pos

 

기본적인 sql 문법 

• 조회 : select [필드] from [테이블] (where)
• 삽입 : insert into [테이블](필드) values (레코드)
• 삭제 : delete from [테이블] (where) (레코드)
• 수정 : update [테이블] set [필드] (where) 

 

데이터베이스 및 테이블 관련 문법

• 데이터베이스 생성 : create database [데이터베이스명]
• 데이터베이스 삭제 : drop database [데이터베이스명]
• 데이터베이스 목록 조회 : show databases
• 데이터베이스 사용 : use [데이터베이스명]
• 테이블 생성 : create table [테이블명]
• 테이블 삭제 : drop table [테이블명]
• 테이블 목록 조회 : show tables
• 테이블 스키마 조회 : desc [테이블명]
• 테이블 수정 : alter table
  • 삽입 : alter table add
  • 수정 : alter table modify
  • 삭제 : alter table drop
  • 컬럼명 변경 : alter table chage
  • 테이블명 변경 : alter table rename 

 

테이블 생성시 필드 제약 조건

• PRIMARY KEY - PK로 설정 (not null, unique의 속성을 기본으로 가짐)
• FOREIGN KEY - 참조키
• NOT NULL - null 값이 들어 올 수 없음
• UNIQUE - 중복된 값이 들어 올 수 없음
• AUTO_INCREMENT - 레코드가 추가될때 자동으로 1씩 증가 - 첫번째 레코드 1, 두번쨰 레코드 2 ..... 마지막레코드 N

CREATE TABLE `content` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `title` varchar(255) UNIQUE NOT NULL,
  `body` varchar(255) UNIQUE NOT NULL,
  `userId` int,
  FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);

 

 

함수

• COUNT() : 조건을 만족하는  총 레코드의 수 반환
  • SELECT COUNT(*) FROM user
• MAX(), MIN(): 선택된 필드의 레코드중 가장 큰값과 작은값 반환
  • SELECT MAX(age) FROM user
  • SELECT MIN(age) FROM user
  • 문자열을 사용할 경우 MAX와 MIN는 데이터베이스가 해당 열에 정의한 정렬 순서에서 가장 높은 값(MAX)과 작은 값(MIN)을 반환, 인수가 하나라도 NULL이면 NULL을 반환
• SUM() : 선택된 필드의 레코드 값을 모두 합한값 반환 (숫자 타입의 필드)
  • SELECT SUM(age) FROM user
• AVG() : 선택된 필드의 레코드 평균값을 반환 (숫자 타입의 필드)
  • SELECT SUM(age) FROM user

• SELECT COUNT(*),MAX(age),MIN(age),SUM(age),SUM(age) FROM user;

조인

• INNER JOIN - 교집합, 기준 테이블(from)과 조인되는 테이블의 레코드가 매칭되는 경우만 조회 (null 값 없음)
• LEFT JOIN - INNER JOIN + 기준 테이블(from)의 전부를 출력 (조인 테이블과 매칭되지 않는 값은 null로 출력)

// inner join
mysql> select title, name from user inner join content on user.id = content.userId;
+-------------------+----------+
| title             | name     |
+-------------------+----------+
| database homework | luckykim |
| clean code        | luckykim |
+-------------------+----------+

// left join
mysql> select title, name from content left join user on content.userId = user.id;
+-------------------+----------+
| title             | name     |
+-------------------+----------+
| database homework | luckykim |
| deploy homework   | NULL     |
| first project     | NULL     |
| clean code        | luckykim |
+-------------------+----------+


// 테이블 구조
mysql> select * from user;
+----+------------+---------------------------+--------+
| id | name       | email                     | roleId |
+----+------------+---------------------------+--------+
|  1 | luckykim   | luckykim@abc.com          |   NULL |
|  2 | lattekim   | lattekim@abc.com          |   NULL |
|  3 | nillava    | nillava@abc.com           |   NULL |
|  4 | jungminlee | jungminlee@abc.com        |   NULL |
|  5 | chae       | cococo@gmail.com          |   NULL |
+----+------------+---------------------------+--------+
5 rows in set (0.00 sec)

mysql> select * from content;
+----+-------------------+-----------------------+---------------------+--------+
| id | title             | body                  | created_at          | userId |
+----+-------------------+-----------------------+---------------------+--------+
|  1 | database homework | database is very easy | 2023-03-30 23:08:53 |      1 |
|  2 | deploy homework   | deploy is difficult   | 2023-03-30 23:08:53 |   NULL |
|  3 | first project     | code states           | 2023-03-30 23:08:53 |   NULL |
|  4 | clean code        | good code             | 2023-03-30 23:08:53 |      1 |
+----+-------------------+-----------------------+---------------------+--------+
4 rows in set (0.00 sec)

 

헷갈렸던 것 & 알아둘 것

• where 조건시 부정형 일때 not 대신 <> 기호를 사용 할 수 있다.
  • 유저이름이 chae가 아닌 user의 모든 데이터를 조회
  • SELECT * FROM user WHERE NOT name = 'chae';
  • SELECT * FROM user WHERE name <> 'chae';
• group by는 기준이 될 사람을 그룹화 시켜준다. 
  • 각 user(name)가 작성한 글의 개수를 출력 - user(name)가 기준 
  • select name, count(post) from user group by name
• join시 필드명이 겹칠수 있으므로 테이블명.컬럼명을 쓰는게 좋다. - 각 테이블에 동일한 필드명이 있는 경우
  • SELECT user.name content.name FROM user LEFT JOIN content ON user.id = content.userId
• as(alias) 설정시
  • select에서 as 설정은 테이블 출력시 보이는 필드명을 바꾸는것 
    • select count(*) as ContentCount from content
    • count(*)라는 필드명 대신 ConstentCount라는 필드명으로 테이블이 출력된다.
  • from에서 as 설정은 쿼리문 작성시에 해당 테이블명을 축약해서 사용하는것 - as를 설정했으면 as로 설정한대로 쿼리문에서 사용해야한다.
    • select u.name, u.email, r.name from user as u left join role as r on u.roleId = r.id
• sql문을 대문자를 사용해서 sql문인 것을 명확하게 하는게 좋다. (테이블명과 컬럼명이 소문자인 경우가 많기 때문에)
  • SELECT name FROM user
  • 정작 위에서 예제도 소문자로 많이 했다.. 앞으로 습관을 기르자

 

 

sql 문법 공부

http://tcpschool.com/mysql/mysql_basic_syntax

코딩교육 티씨피스쿨

 

sql quize - https://www.w3schools.com/quiztest/quiztest.asp?qtest=SQL 

W3Schools SQL Quiz

sql 연습 - https://www.w3schools.com/sql/sql_exercises.asp

SQL Exercises

 

mysql 설치후 연습

mysql>
mysql> create database testDB;
Query OK, 1 row affected (0.01 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| testdb             |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> use testdb
Database changed
mysql> create table test(
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(255),
    -> email varchar(255)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> describe test
    -> ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| email | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test             |
+------------------+
1 row in set (0.01 sec)

mysql> insert into test (name, email) values ('chae', 'test@gmail.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test
    -> ;
+----+------+----------------+
| id | name | email          |
+----+------+----------------+
|  1 | chae | test@gmail.com |
+----+------+----------------+
1 row in set (0.00 sec)

mysql> insert into test (name, email) values ('kim', 'test2@gmail.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test
    -> ;
+----+------+-----------------+
| id | name | email           |
+----+------+-----------------+
|  1 | chae | test@gmail.com  |
|  2 | kim  | test2@gmail.com |
+----+------+-----------------+
2 rows in set (0.00 sec)


mysql> select name from test where id = 2;
+------+
| name |
+------+
| kim  |
+------+
1 row in set (0.00 sec)

mysql> select name from test where id = 1;
+------+
| name |
+------+
| chae |
+------+
1 row in set (0.00 sec)

mysql> insert into test (name, email) values ('park', 'test3@gmail.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+-----------------+
| id | name | email           |
+----+------+-----------------+
|  1 | chae | test@gmail.com  |
|  2 | kim  | test2@gmail.com |
|  3 | park | test3@gmail.com |
+----+------+-----------------+
3 rows in set (0.00 sec)