9/22/2020
SQL
BETWEEN ... AND 연산자
> SELECT ... FROM ... WHERE 컬럼 BETWEEN 값1 AND 값2 ORDER BY ... ;
- BETWEEN 대신 그냥 비교연산자를 써도 됨 (컬럼 >= 값1 AND 컬럼 <= 값2). 그치만 between을 쓰면 읽기는 훨씬 편함.
- 값1 과 값2 둘다 range에 포함됨.
- 값1 이 꼭 값2 보다 작아야 함. else the query returns no data.
(side note:) 여기서 date 폼은 왠만하면
> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD:HH24:MI:SS';
로 하면 좋음.
- BETWEEN can always be replaced with AND operators, but that would be messier to read.
- should know that BETWEEN is a range query. 그래서 IN과 다른게 BETWEEN은 꼭 range가 필요하기 때문에 문자도 숫자로 바꿔서 rnage를 꼭 찾음. 그래서 IN이 어떨떈 성능이 아주 쪼오금 나을수 있음.
IN 연산자
- IN can always be replaced with OR operators, but that would be messier to read.
> SELECT ... FROM ... WHERE 컬럼 IN (값1, 값2 ... ) ORDER BY ... ;
--> 숫자, 문자, 날짜 다 값에 넣을수 있음.
PRIMARY KEY (주 식별자) - aka 'PK'
- it's gotta be unique. And it can never be NULL.
- when it's a table like 'score' where both sno and cno are FKs (Foreign Keys), meaning they are not unique, the PK for 'score' becomes sno|cno (combined).
- every table needs to have a PK
in SQL, none of the entries in all the tables are sorted. Except for PKs. For instance, for 'student' table, a copy of the 'sno' column is saved separately, in a sorted order. ('sno' 컬럼만 따로 저장함.) In this table, there are basically 2 columns, 'sno' and 'RowID' where the RowID indicates the address of the row/entry that the respective 'sno' refers to. --> 그래서 이 테이블을 통해 엄청 빠르게 single entry를 찾을수 있음. Such a column (referring to 'sno' column) is called 'index'.
- it is true that SQL's 성능 (performance) can still be pretty good even if someone makes a disastrous dataset where the tables do not have PKs. However, after a while, the performance drops super suddenly.
- Index Scan: faster if there are less than, let's say, like 100 entries to print/scan.
- Full Table Scan: faster if you need to scan a LOT of data, like 1억개 (1 million).
- But if the data's like 10,000s, it's around the same for Index Scan and Full Table Scan. But, in today's world, data is increasing in size for everything. So Index Scan is becoming more crucial.
(side note: in SQL, > clear screen; command clears the screen)
RDBMS vs. File System Database
- RDBMS는 table을 안 붙여놔야 됨. When you combine tables, sometimes there will be data entries that overlap. In RDB, the more data there are, the less its performance. It's the opposite for File System Database, where combining tables will always make any search faster.
- So in RDB, gotta use JOIN instead of combining tables.
- 사실 성능은 FSD (File System Database) 가 훨씬 좋은데, 기업은 생산성을 훨씬 더 중요하게 생각함. 그리고 RDB는 생산성이 훨씬 높음. Database가 아예 따로 이기 때문에. (kinda similar to Object-Oriented Programming, where its performance lacks but its productivity is so good.
