Part 2: Basic Syntax

1️⃣ LINESIZE defaults to 80, which means that 80 character counts are displayed in one line, and PAGESIZE defaults to 10, which means that 10 lines are displayed in one page. You can use SET to increase or decrease it.

2️⃣ AS can be used to create an alias, for example, SELECT ENAME, SAL+COMM AS TOTALSALARY FROM EMP; can change the column name to TOTALSALARY.
3️⃣ NVL(A, B) means that if the value of A is null, it is replaced by B. For example, NVL(comm, 0) means that if the value of comm is null, it is replaced by 0. NVL is function, used to replace NULL values.
4️⃣ To determine whether a value is null or not, use xxx is null or xxx is not null, instead of the equal sign
5️⃣ DISTINCT, UNIQUE are used for deduplication
6️⃣ When judging the condition, you can use >, <, IN (one or more values), BETWEEN xxx and yyy, and the inequality sign can be <>
7️⃣ The fuzzy query keyword is LIKE, % represents multiple characters, and _ represents a character. For example, where ename like “%S” means a name ending with S, where ename like “_D_” means looking for a name with three characters and D in the middle
8️⃣ ORDER BY, the result is sorted, the default is ascending order (ASC), if you want to descend, you need to manually add DESC. You can sort by multiple columns, for example, SELECT * FROM emp ORDER BY sal DESC, comm DESC; means sort by sal in descending order first, and if sal is the same, sort by comm in descending order.
When sorting, you can use COL NAME, COL ALIAS, COL NUMBER, for example, SELECT ename, sal FROM emp ORDER BY 2; means sort by the second column (sal) in ascending order. But WHERE does not work, WHERE can only use COL NAME (column name itself)

Group Function / Aggregate Function — works on group and returns single value
MAX, MIN, SUM, AVG, COUNT…
COUNT() means to count the number of rows. If it is COUNT(), it will display the entire table and count the number of rows. COUNT(1) will replace each row of data with a 1 and then count the number of rows. So the results of both are the same, but COUNT(1) is more efficient than COUNT(*). However, if it is a certain column, you cannot just replace it with COUNT(1), because the COUNT() method will ignore the null value. So if it is COUNT(comm), the answer is not 14 rows but 4 rows, because there are 10 rows with empty comm.

GROUP BY — used for grouping of date.
Understanding: It is equivalent to grouping the values ​​according to the thing after group by, and then executing the aggregate function once for each group. If there are multiple columns after group by, it is the Cartesian product to divide the groups. For example, GROUP BY DEPTNO, JOB means that the manager of department 30 and the sales of department 30 are divided into two groups; if it is GROUP BY DEPTNO, it means that all the people in department 30 are counted as the same group regardless of their positions.
group by is only used when there are similar values. If it is used on a column where each value is unique, there will be no error if the statement is written correctly, but the displayed result is meaningless. It is like if you count the average salary of each person, isn’t it the salary of each person itself? It is meaningless. What is meaningful is, for example, counting the average salary of each department, that is GROUP BY DEPTNO.
If we are using multiple columns, some col having group function, then remaining column must be in GROUP BY.
SELECT deptno FROM emp GROUP BY deptno; can achieve the effect of distinct and unique.
Use HAVING to filter after grouping
For example, I want to check the groups where the total salary of each group is greater than 9000. That is SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

DDL， DML
DDL – CREATE, ALTER, DROP, RENAME, TRUNCATE, FLASHBACK, PURGE DML – INSERT, UPDATE, DELETE

 - - - TO CREATE A NEW TABLE
 CREATE TABLE TESTN1
 (EMPNO NUMBER(5),
 ENAME VARCHAR2(20),
 SAL NUMBER(4),
 DOB DATE);
 
  - - - TO SEE STRUCTURE OF TABLE
  DESC TESTN1
  
  - - - TO CREATE A TABLE FROM EXISTING TABLE
  - - - TO COPY THE TABLE
  CREATE TABLE TESTN2 AS SELECT * FROM EMP; 
// This copies the EMP table and names it TESTN2
  
  - - - TO CREATE A TABLE FROM EXISTING TABLE WITHOUT DATA
  - - - ONLY STRUCTURE NEED TO COPY 
// Just add a false condition
  CREATE TABLE TESTN3 AS SELECT * FROM EMP WHERE 1 = 2; 
 
 
  - - - TO INSERT RECORD IN TABLE
  - - - TO INSERT IN ALL FIELD
  INSERT INTO TESTN1 VALUES(1, 'OSCAR', 9000, '12-JAN-2009');
  // By the way, this date format is the Oracle data format
  
  - - - TO INSERT IN PARTICULAR FIELD
  INSERT INTO TESTN1(EMPNO, ENAME) VALUE(2,'MIKE');
  
  - - - TO INSERT DATA FROM ANOTHER TABLE
  INSERT INTO TESTN1(EMPNO, ENAME) SELECT EMPNO, ENAME FROM EMP;
// No VALUE keyword!!
  
  - - - Modify the data of an existing row: First, the row must already exist, and then use update to modify it.
  - - - The place that was originally empty looks like a value has been added, and the place that originally had a value looks like a modification
  - - - In essence, it is a modification. The only difference is that the empty places are replaced by empty -> new value
  - - - UPDATE
  UPDATE TESTN1 SET SAL = 7000, DOB = '13-FEB-2008' WHERE EMPNO = 2;
  - - - DELETE // delete a row
  DELETE FROM TESTN1 WHERE EMPNO = 2;
  // UPDATE and DELETE must add WHERE restrictions, otherwise all rows will be modified/deleted.
  
  
  - - - TO ADD, MODIFY, DROP, RENAME - - - FIELDS --> ALTER
  ALTER TABLE TESTN1
  ADD
  (MOBILE NUMBER(10));
  // Add a mobile field to the TESTN1 table, the length limit is 10
  
  ALTER TABLE TESTN1
  MODIFY
  (MOBILE NUMBER(15));
  // Change the length of the mobile field to 15
  
  ALTER TABLE TESTN1
  RENEME COLUMN MOBILE TO PHONE;
  // Rename the mobile field to phone
  
  ALTER TABLE TESTN1
  DROP COLUMN PHONE;
  // Delete the PHONE field
  
  - - - RENAME THE TABLE --> RENAME is a keyword
  RENAME TESTN1 TO TESTN9;
  
  - - - TO SEE THE LIST OF TABLE
  SELECT * FROM TAB;
  SELECT TNAME FROM TAB; 
// Quickly view the table name of yourself (the current user), without scalability.

  SELECT table_name FROM user_tables; 
// Only look at your own table name, and then you can connect where, join, etc., which is highly extensible
  
DELETE vs TRUNCATE vs DROP
DELETE is a DML command. It can delete all or some records (with optional WHERE condition). It can be rolled back, and the table structure remains.

TRUNCATE is a DDL command. It deletes all records (no condition allowed), and cannot be rolled back. The table structure still remains.

DROP is also a DDL command. It removes the entire table, including both data and structure. After DROP, the table no longer exists, and the operation cannot be rolled back.


The table after DROP will enter the recycle bin, which can be viewed with SHOW RECYCLEBIN
The table in the recycle bin can be restored with "FLASHBACK TABLE EMP TO BEFORE DROP;”

PURGE RECYCLEBIN; Empty the Recycle Bin.

TCL (Transaction Control Language) includes COMMIT and ROLLBACK, which are used to save or undo changes made by DML operations. A transaction typically consists of a series of INSERT, UPDATE, or DELETE statements, ending with either a COMMIT to save or a ROLLBACK to undo. These commands only apply to DML and not to DDL. Oracle also supports auto commit and auto rollback as concepts: if a DML operation is followed by a DDL statement or a proper exit from the session, changes are automatically committed; if the session ends unexpectedly (such as due to power failure or force close), the changes are automatically rolled back.

Locking
1️⃣Row level lock
When using DML, these rows will be automatically locked. Others cannot use DML on these rows during the lock period. They can only SELECT or wait. When the person who occupies the lock commits or rolls back, the lock is released.
2️⃣Deadlock, A locks when using the first row, and B locks when using the second row. At this time, if A wants to use the second row and B wants to use the first row at the same time, a situation of mutual waiting will occur, which is called deadlock. Deadlock will be detected by Oracle and then forced to roll back one side to release the deadlock.
3️⃣Table level lock
Need to be added manually. After adding, others can only SELECT
The operation instruction is:
LOCK TABLE emp IN EXCLUSIVE MODE; It will be released after commit/rollback occurs.