SQL (CREATE, ALTER, DROP, PURGE, ) & PHP (Connect to DB)

09/28/2020

CREATE, ALTER, DROP

• 테이블은 원래 설계부터 조심하게 잘 만들어야함

> CREATE TABLE 테이블 (컬럼 데이터_타입 [DEFAULT default값] [컬럼 레벨 제약조건], [컬럼 레벨 제약조건], ... [테이블 레벨 제약조건], ...);

• 데이터_타입 - 컬럼에 입력될 데이터의 종류와 크기를 결정한다.
  - 숫자 - NUMBER (for both floats and integers)
  • 날짜 - DATE
  • 문자 - VARCHAR (Only Oracle writes it as VARCHAR2)
• DEFAULT - 입력이 누락됐을 때 기본 입력 값을 정의한다. If you don't set it up, NULL is saved as default
• Column Level Constraints - PK, FK, UK, CHECK, NOT NULL
  - CHECK - WHERE 조건 처럼 내맘대로 조건을 줄수 있음.
  • UK - Unique Key
• Table Level Constraints - PK, FK, UK, CHECK --> *this one is used more often
  - PK - every table has it, and has only 1.

> DROP TABLE 테이블 [CASCADE CONSTRAINT];

• [CASCADE CONSTRAINT] --> if you're trying to delete dept, you gotta write this down. BC of constraints with its PK and FK in emp (dno col)
• 오라클도 휴지통이 있음. 테이블 지우면 여기로 감.

> PURGE RECYCLEBIN;

• 휴지통을 깔끔하게 지움.

> SELECT table_name FROM user_tables;

• user_tables --> can only see my own tables. only st01 tables.
• this query gives us the table_names of all the tables

> SELECT table_name, column_name, data_type, data_length FROM user_tab_columns [WHERE table_name = '테이블'];

• gotta write '테이블' capitalized --> '테이블' for this one.in dictionary, every table name is capitalized.
• similar to DESC table; command.

• In Oracle, their Meta Data is called "Dictionary" --> user_tables, user_tab_columns, these are examples of things in Dictionary. There are thousands more.

• table creation 이름 규칙
  - 문자로 시작한다. 30자 이내로 한다. 영문, 숫자, _, $, # 만을 사용한다

  • 예약어는 사용 불가
  • 대소문자 구별안함.

DATA TYPE
- 문자 타입
- VARCHAR2 (VARCHAR2(50) 50 글자 아님 LESS), CHAR (만약 CHAR50 하면 무조건 50바이트 먹음. VARCHAR는 maximum을 decide하는거.), LONG, CLOB (large object) --> LONG, CLOB는 제한이 들어감. column하나에 2 아님 4 GB 까지 들어감. 엄청남. 그대신 검색이 안됨. LIKE 이런 검색 안됨. 아주 대용양 --> 근데 이젠 use BFILE instead. in one table, only one LONG. but CLOB there can be multiple in 1 table.
- We'll probably only use VARCHAR2
- remember, things like 부서번호 needs to be a 문자!!
- 한글이름은 VARCHAR2(15)정도. 한글 한글자는 3byte 정도 됨. 영어는 1자 당 1byte
- 숫자 타입
- NUMBER (NUMBER(4,2) --> 99.99까지. 4자리 수고 2자리 소수점)
- 날짜
- DATE
- use TRUNC(hdate) to make sure you can get the data you want. TRUNC gets rid of hh/mm/ss
- 이진 타입
- RAW
- ROWID 타입
- ROWID --> 각각의 행의 물리적인 위치. 프로그래밍할때 많이 쓰임.

(Note: for DML queries (INSERT, DELETE, UPDATE), they are applied to the tables right away. But it just doesn't show on other sessions until you commit)

In Oracle, there is no History feature. So you must make a separate script file (an sql file) for creation fo all tables. And you must start that file with DROP TABLE .... ; PURGE RECYCLEBIN;

SELECT count(*) dept FROM dept; --> gives you the number of entries in the dept table.

PHP로 DB 접속
- 기본 개념: SQLplus라는 프로그램이 오라클에 접속함. sqlplus는 그냥 query를 서버에 보냄. 그러면 오라클 서버 안에있는 서버프로세서가 parsing 함. Through parsing, the server processor creates an execution plan. Then, oci_execute() will execute the plan. After execution, fetching needs to occur, where the server processor puts the data into an array (oci_fetch_array/all --> oci_fetch_array brings the data in a 1-dimensional array).
- in MySQL, all the tables are in the database. (st01.emp --> st01 is DB name)
- that's why in MySQL, you need to do mysql_select_db after you do the mysql_connect().
- in Oracle, all the tables are in individual users. (st01.emp --> st01 is user name)

For our class:

oci_connect() function:

• connection_string --> TNS name

• but if you don't have the tnsnames.ora file, then you gotta replace "oracle" with "(DESCRIPTION = (ADDRESS = (PROTOCOL = tc)(HOst)()... blahblahblah)"

• require('conn.php'); --> including conn.php file in the current php file

• then the query --> $sql = "";

• :v[var name] --> ex: :v_sno --> :v denotes a bind-variable. not giving the actual value yet in the sql query. give the actual value later by oci_bind_by_name(result, ":v_sno", $sno); --> you do this so you don't have to waste unnecessary time when parsing. After you finish the oci_parse(), you do the oci_bind_by_name to give values for each of the bind-variable. BTW, the $result = oci_parse(conn, $sql);

• oci_execute(); --> returns 1 if things were done properly, 0 if error occurred. Also, as it executes, it commits. So don't need to worry about committing for the insert query.

• oci_free_statement() --> free-up the memory that was used(?)

"\<meta http-equiv='Refresh' content= '0; URL=st_vi.php>" tag --> 링크인데, 클릭 필요없이 바로 감.

• don't ever use SELECT * FROM ~ when you do the sql query in php files

while ($row = oci_fetch_array($result, OCI_NUM)) {
echo("$row\[0]-$row[1]- ... ");
} --> each instance of the loop indicates a single row in the $result data table. when the pointer reaches the end of the data, the while condition becomes false.

oci_free_statement(result); --> deletes the table it used for the $result data. oci_close(conn); --> the end.

Side Note: 제약조건은 프로그램 만들고 나서 꼭!! 다시 re-activate 해놔야 함. So that they can't put in data that is invalid (since all the tables are related to each other somehow, can't put in a pno or sno that doesn't exist inside a score table)