SQL

1.3. SQL#

1.3.1. Definitions#

1.3.1.1. SQL#

Structured Query Language: A language for querying and manipulating data.

1.3.1.2. DDL#

Data Definition Language: Create and modify structures in the database (tables, views, indexes, etc.)

1.3.1.3. DML#

Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE etc. Used to store, modify, retrieve, delete and update data in database.

1.3.1.4. DCL#

Data Control Language: Rights, permissions and other controls of the database system.

  • Khi kết nối tới database sẽ bắt đầu 1 session, trong session bao gồm nhiều transaction

  • Các công lệnh DCL hoặc DDL sẽ end transaction

  • Câu lệnh DCL sẽ end session

1.3.1.5. Data#

The information that is stored in the database.

1.3.1.6. Database#

A collection of data.

1.3.1.7. Schema#

The structure of the database. The tables, views, indexes, etc.

1.3.1.8. Table#

A collection of data organized in rows and columns.

1.3.1.9. Row#

A single record in a table.

1.3.1.10. Column#

A single field in a table.

1.3.1.11. Primary Key#

A unique identifier for a row in a table.

1.3.1.12. Foreign Key#

A field in a table that is a primary key in another table.

1.3.1.13. View#

A virtual table that is the result of a query.

1.3.1.14. Index#

A data structure that improves the speed of data retrieval.

1.3.1.15. Query#

A request for data or information from a database table or combination of tables.

1.3.1.16. Query Language#

A language for requesting information from a database.

1.3.1.17. Postgres#

A relational database management system.

1.3.1.18. Database Client#

A program that allows you to connect to a Postgres database and run queries.

1.3.1.19. Database Server#

A program that runs on a computer and manages the database.

1.3.1.20. Database Management System#

A program that manages the database.

1.3.1.21. Database Administrator#

A person who manages the database.

1.3.1.22. Database Developer#

A person who creates and maintains the database.

1.3.1.23. Database User#

A person who uses the database.

1.3.2. Connection#

#chạy code ko cần pandas để hiển thị
with cx_Oracle.connect('SYSTEM/21061998@localhost/db12c') as connection:
    cursor = connection.cursor()
    query=cursor.execute("select * from allstocktest") # viết SQL query code
    connection.commit() # chạy code query ở sql

class Connection(cx_Oracle.Connection):
    def cursor(self):
        cursor = super(Connection, self).cursor()
        return cursor

conn = Connection("SYSTEM/21061998@localhost/db12c")
sql="select * from allstocktest"
df = pd.read_sql_query(sql, conn)

5000

import SQLcode as sql
import pandas as pd
import cx_Oracle
conn = cx_Oracle.Connection('SYSTEM','21061998','192.168.0.100/db12c')
curs = conn.cursor()

  • Câu SQL không phân biệt chữ HOA vs thường

  • Kết thúc câu SQL bằng ;

1.3.3. Câu lệnh DDL#

  • khi kết nối tới database sẽ bắt đầu 1 session, trong session bao gồm nhiều transaction

  • Các công lệnh DCL hoặc DDL sẽ end transaction

  • Câu lệnh DCL sẽ end session

1.3.3.1. Create table#

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

1.3.3.2. Alter table#

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

1.3.3.3. Insert#

THÊM DỮ LIỆU VÀO TRONG TABLE

1.cú pháp insert từng row

INSERT INTO table(col1,col2,...)
VALUES (val1,val2,...)

2.cú pháp insert nhiều row theo bảng có sẵn

INSERT INTO table(col1,col2)
SELECT to_col1, to_col2
FROM TABLE1
WHERE conditions

1.3.3.4. Update#

SỬA ROW TRONG TABLE

UPDATE table
SET col1=value2,col2=value2,...
WHERE conditions

WHERE điệu kiện các row được update

1.3.3.5. Delete#

XÓA ROW TRONG TABLE

DELETE [FROM] table
WHERE conditions

1.3.3.6. Truncate#

XÓA TOÀN BỘ DATA TRONG TABLE TƯƠNG ĐƯƠNG với DELETE mà không có WHERE

TRUNCATE TABLE table

1.3.3.7. Indexing#

1. Create index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

2. Remove index

DROP INDEX index_name ON table_name;

1.3.4. Câu lệnh DCL#

  • Câu lệnh kết thúc transaction, đồng thời mở ra 1 transaction mới: COMMIT

  • Hủy bỏ thao tác transaction này, mở ra 1 transaction mới ROLLBACK [TO savepoint_name]

  • Tạo bookmark điểm COMMIT or ROLLBACK SAVEPOINT savepoint_name

INSERT INTO table(col1,col2)
SELECT to_col1, to_col2
FROM TABLE1
WHERE conditions
SAVEPOINT insert_done;

DELETE [FROM] table
WHERE conditions
SAVEPOINT delete_done;

ROLLBACK TO insert_done
ROLLBACK
COMMIT

1.3.5. Join#

1.3.5.1. Natural Join#

1.3.5.1.1. Natural Join#

Join tự động 2 table với những column cùng tên với nhau

  • Nếu 2 bảng có 2 tên giống nhau nhưng type data khác nhau thì báo ERROR

  • Chọn ROW cùng giá trị giữa các Key-column và ghép lại với nhau

SELECT STOCK, CLOSE_P, VOLUME_S
FROM ALLSTOCKTEST
NATURAL JOIN TABLE2

1.3.5.1.2. USING#

Chỉ định 1 or 1 vài columns cùng tên để match

SELECT a.STOCK,a.STOCK_ID, a.CLOSE_P, b.VOLUME_S
FROM ALLSTOCKTEST a JOIN TABLE2 b
USING (STOCK_ID)
WHERE STOCK_ID LIKE 'AC%'

Chú ý:

  • Trong USING, WHERE sử dụng tên chung chứ ko cần viết tên bảng đứng trước

1.3.5.1.3. ON#

JOIN TRÊN 1 CONDITIONS NÀO ĐÓ

SELECT a.STOCK,a.STOCK_ID,b.STOCK_ID, b.VOLUME_S,c.volume_sp
FROM ALLSTOCKTEST a 
JOIN TABLE2 b ON (a.STOCK_ID = b.STOCK_ID)

Join nhiều hơn 2 bảng

SELECT a.STOCK,a.STOCK_ID,b.STOCK_ID, b.VOLUME_S
FROM ALLSTOCKTEST a 
JOIN TABLE2 b ON (a.STOCK_ID = b.STOCK_ID)
JOIN TABLE3 c on (b.volumn_s = c.volume_sp)

1.3.5.2. Self-Join#

Join các dữ liệu trong cùng 1 bảng

Bảng EMPLOYEES

employee_id

Name_employee

manager_id

a1

dat

(null)

d2

son

a1

d3

minh

a1

c4

linh

d2

c5

TA

d2

a6

Loan

d2

a7

XUAN

d2

a8

DUong

c4

s9

Chang

c4

d21

Huyen

a1

c32

Thao

c4

  • Muốn tìm tên của manager : join manager_id với employee_id

SELECT e.employee_id, e.Name_employee, m.manager_id, m.Name_employee Name_manager
FROM EMPLOYEES e JOIN EMPLOYEES m 
ON (e.manager_id=m.employee_id)

1.3.5.3. Nonequijoins#

Rating

lowest_return

highest_return

Excellent

0.1

999

Good

0.05

0.1

Normal

0.02

0.05

Poor

-0.02

0.02

Bad

-999

-0.02

  • Dựa vào bản RAKING_RETURN để xếp hạng STOCK trong bảng ALLSTOCKTEST

SELECT a.STOCK, a.DAY_TRADING, a.RETURN_S, b.Rating
FROM ALLSTOCKTEST a JOIN RAKING_RETURN b
ON (a.RETURN_S BETWEEN b.lowest_return AND b.Highest_return)

1.3.5.4. Outer Join#

Lấy Full 1 bên table, kể cả những row ko có data để ghép bên bảng kia

LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN

SELECT a.STOCK, a.DAY_TRADING, a.RETURN_S, b.Rating
FROM ALLSTOCKTEST a LEFT OUTER JOIN RAKING_RETURN b
ON (a.RETURN_S BETWEEN b.lowest_return AND b.Highest_return)

1.3.5.5. Cross Join#

Join không cần điều kiện gì, mục đích để tạo table có nhiều dòng: Table1(10rows) x Table2(15rows) = Table3(150rows)

SELECT A.STOCK A.CLOSE_P B.SECTOR_S
FROM ALLSTOCKTEST A CROSS JOIN TABLE2 B

1.3.6. Querying Data#

1.3.6.1. Describe#

DESCRISE table:

DESC ALLSTOCKTEST;

1.3.6.2. Select table#

chọn toàn bộ table

SELECT * 
FROM TABLETEST;

chọn 1 số columns table

SELECT col1,col2 
FROM TABLETEST;

ddl2='SELECT STOCK,DAY_TRADING FROM ALLSTOCKTEST'
pd.read_sql_query(ddl2,conn).head()
STOCK DAY_TRADING
0 ACB 2020-01-20
1 ACB 2020-01-19
2 ACB 2020-01-18
3 ACB 2020-01-17
4 ACB 2020-01-16 
ddl2='SELECT STOCK,CLOSE_P,volume_s,Close_p + 4,close_p*volume_s FROM ALLSTOCKTEST'
pd.read_sql_query(ddl2,conn).head()
STOCK CLOSE_P VOLUME_S CLOSE_P+4 CLOSE_P*VOLUME_S
0 ACB 19 1300000 23 24700000
1 ACB 20 1257000 24 25140000
2 ACB 20 1214000 24 24280000
3 ACB 21 1171000 25 24591000
4 ACB 22 1128000 26 24816000

Column Alias : ký hiệu tên column

SELECT col1 col1_alias,col2 as col2_alias, col3 "col3 alias",col4 "Col4!#AliAS" 
FROM TABLETEST;

  • sử dụng dấu nháy kép " để khi cần ký hiệu có space ,ký hiệu đặc biệt , hoặc phân biệt HOA thường

ddl2='SELECT STOCK cp,CLOSE_P as price FROM ALLSTOCKTEST'
pd.read_sql_query(ddl2,conn).head()
CP PRICE
0 ACB 19
1 ACB 20
2 ACB 20
3 ACB 21
4 ACB 22

concat chuỗi by || : nối chuỗi

ddl2='SELECT STOCK||DAY_TRADING as IDStock FROM ALLSTOCKTEST'
pd.read_sql_query(ddl2,conn).head()
IDSTOCK
0 ACB20-JAN-20
1 ACB19-JAN-20
2 ACB18-JAN-20
3 ACB17-JAN-20
4 ACB16-JAN-20

nối giá trị với ‘literal string’

SELECT 'giá của ' || STOCK || ' vào ngày' || DAY_TRADING || ' là '||close_p as StatusStock FROM ALLSTOCKTEST

or use 2 single quote ''

SELECT 'The ' || STOCK || '''s close price on ' || DAY_TRADING || ' is '||close_p as StatusStock FROM ALLSTOCKTEST

1.3.6.3. Distinct#

select loại bỏ trùng lặp

Select + loại bỏ giá trị trùng lặp

SELECT distinct STOCK FROM ALLSTOCKTEST

# loại bỏ trùng lặp
ddl2='SELECT distinct STOCK FROM ALLSTOCKTEST'
pd.read_sql_query(ddl2,conn)
STOCK
0 ACC
1 ACB
2 AVM

1.3.6.4. Filter#

SELECT col1,col2
FROM tabletest
WHERE conditions

Toán tử

Miêu tả

=

bằng

>=

lớn hơn hoặc bằng

<=

bé hơn hoặc bằng

>

lớn hơn

<

bé hơn

<>

khác

BETWEEN a AND b

Giá trị nằm trong khoảng a <= x <= b

IN {SET}

lấy x trong tập set

LIKE ‘a%’

lấy giá trị gần giống a: a123,abc,ax,…

LIKE ‘a_’

lấy giá trị gần giống a: a1,ab,ax,…

IS NULL

so sánh giá trị null

AND

và

OR

hoặc

NOT

ngược lại

 
ddl2='SELECT STOCK, CLOSE_P FROM ALLSTOCKTEST WHERE Close_p BETWEEN 17 AND 27'
pd.read_sql_query(ddl2,conn)
STOCK CLOSE_P
0 ACB 17.00
1 AVM 22.00
2 AVM 23.40
3 AVM 24.44
4 AVM 23.00
5 AVM 25.00
6 AVM 26.00 
# '%' là 0 hoặc nhiều characters
ddl2='SELECT STOCK, CLOSE_P FROM ALLSTOCKTEST WHERE STOCK like \'AC%\''
pd.read_sql_query(ddl2,conn)
STOCK CLOSE_P
0 ACB 12.20
1 ACB 12.23
2 ACB 12.44
3 ACB 12.50
4 ACB 14.89
5 ACB 17.00
6 ACB 14.00
7 ACC 28.00
8 ACC 29.00
9 ACC 30.00 
# gạch dưới '_' là 1 character
ddl2='SELECT STOCK, CLOSE_P FROM ALLSTOCKTEST WHERE STOCK like \'A_C%\''
pd.read_sql_query(ddl2,conn)
STOCK CLOSE_P
0 ACC 28.0
1 ACC 29.0
2 ACC 30.0

Sử dụng escape nếu muốn where-like pattern chứa ký tự thường là ‘%’ hoặc ‘_’

SELECT col1,col2
FROM tabletest
WHERE last_name LIKE 'Khong\_Tien%' ESCAPE '\';

khi đó filter last_name = ‘Khong_Tien…’

ddl2='SELECT STOCK, CLOSE_P FROM ALLSTOCKTEST WHERE DAY_TRADING=\'18-JAN-20\''
pd.read_sql_query(ddl2,conn)
STOCK CLOSE_P
0 ACB 12.44
1 AVM 24.44
2 ACC 30.00 
ddl2='SELECT STOCK, CLOSE_P FROM ALLSTOCKTEST WHERE STOCK in (\'ACB\',\'AVM\')'
pd.read_sql_query(ddl2,conn)
STOCK CLOSE_P
0 ACB 12.20
1 ACB 12.23
2 ACB 12.44
3 ACB 12.50
4 ACB 14.89
5 ACB 17.00
6 ACB 14.00
7 AVM 22.00
8 AVM 23.40
9 AVM 24.44
10 AVM 23.00
11 AVM 25.00
12 AVM 26.00
13 AVM 27.67 
ddl2='SELECT STOCK, CLOSE_P FROM ALLSTOCKTEST WHERE STOCK in (\'ACB\',\'AVM\') AND close_p between 15 and 23'
pd.read_sql_query(ddl2,conn)
STOCK CLOSE_P
0 ACB 17.0
1 AVM 22.0
2 AVM 23.0

1.3.6.5. Sorted#

SELECT col1,col2
FROM tabletest
[WHERE conditions]
[ORDER BY Col1 [DESC],col2 [DESC],..]

  • nếu không DESC thì mặc định là ASC

  • Có thể dùng column alias (ký hiệu) trong ORDER BY

  • Có thể dùng thứ tự column 1,2,3,..

  • NULL sẽ đóng vai trò là giá trị lớn nhất, nếu DESC thì null nằm đầu tiên

ddl2='SELECT STOCK, CLOSE_P FROM ALLSTOCKTEST ORDER BY STOCK DESC, CLOSE_P'
pd.read_sql_query(ddl2,conn)
STOCK CLOSE_P
0 AVM 22.00
1 AVM 23.00
2 AVM 23.40
3 AVM 24.44
4 AVM 25.00
5 AVM 26.00
6 AVM 27.67
7 ACC 28.00
8 ACC 29.00
9 ACC 30.00
10 ACB 12.20
11 ACB 12.23
12 ACB 12.44
13 ACB 12.50
14 ACB 14.00
15 ACB 14.89
16 ACB 17.00 
# sử dụng column alias
# 1 tương ứng với column 1 : là column STOCK
ddl2='SELECT STOCK, CLOSE_P GIA,Volume_s FROM ALLSTOCKTEST ORDER BY 3 DESC, GIA'
pd.read_sql_query(ddl2,conn)
STOCK GIA VOLUME_S
0 ACB 12.20 1300000
1 ACB 12.23 1257000
2 ACB 12.44 1214000
3 ACB 12.50 1171000
4 ACB 14.89 1128000
5 ACB 17.00 1085000
6 ACB 14.00 1042000
7 AVM 22.00 999000
8 AVM 23.40 956000
9 AVM 24.44 913000
10 AVM 23.00 870000
11 AVM 25.00 827000
12 AVM 26.00 784000
13 AVM 27.67 741000
14 ACC 28.00 698000
15 ACC 29.00 655000
16 ACC 30.00 612000

1.3.6.6. Group by#

SELECT col1,col2
FROM tabletest
[WHERE conditions]
[GROUP BY expression]
[ORDER BY Col1 [DESC],col2 [DESC],..]

ddl2="""SELECT STOCK,AVG(CLOSE_P)
FROM ALLSTOCKTEST
GROUP BY STOCK
"""
pd.read_sql_query(ddl2,conn)
STOCK AVG(CLOSE_P)
0 ACC 29.000000
1 ACB 13.608571
2 AVM 24.501429 
#group_by nhỏ hơn
ddl2="""SELECT STOCK,AVG(CLOSE_P)
FROM ALLSTOCKTEST
GROUP BY STOCK,DAY_TRADING
ORDER BY STOCK
"""
pd.read_sql_query(ddl2,conn)
STOCK AVG(CLOSE_P)
0 ACB 14.00
1 ACB 17.00
2 ACB 14.89
3 ACB 12.50
4 ACB 12.44
5 ACB 12.23
6 ACB 12.20
7 ACC 30.00
8 ACC 29.00
9 ACC 28.00
10 AVM 27.67
11 AVM 26.00
12 AVM 25.00
13 AVM 23.00
14 AVM 24.44
15 AVM 23.40
16 AVM 22.00

1.3.6.7. Group by + having#

tạo bộ lọc sau khi đã chạy GROUP BY

SELECT col1,col2
FROM tabletest
[WHERE conditions]
[GROUP BY expression]
[HAVING conditions]
[ORDER BY Col1 [DESC],col2 [DESC],..]

  • Having giúp tạo bộ lọc sau khi đã chạy group by còn WHERE tạo bộ lọc trước khi chạy GROUP BY

  • Sử dụng được multiple-function trong Having chứ ko sử dụng được trong WHERE

ddl2="""SELECT STOCK,MAX(CLOSE_P)
FROM ALLSTOCKTEST
GROUP BY STOCK
HAVING MAX(CLOSE_P)>20
"""
pd.read_sql_query(ddl2,conn)
STOCK MAX(CLOSE_P)
0 ACC 30.00
1 AVM 27.67 
ddl2="""
SELECT STOCK,MAX(CLOSE_P),MAX(VOLUME_S)
FROM ALLSTOCKTEST
WHERE STOCK NOT LIKE 'AV_'
GROUP BY STOCK
HAVING MAX(CLOSE_P)>20
ORDER BY MAX(CLOSE_P)
"""
pd.read_sql_query(ddl2,conn)
STOCK MAX(CLOSE_P) MAX(VOLUME_S)
0 ACC 30 698000

1.3.6.8. Union#

ghép bảng = thêm row theo cột

  • UNION : LẤY PHẦN TỬ IN A HOẶC B

  • UNION ALL : LẤY PHẦN TỬ IN A + PHẦN TỬ IN B (ko quan tâm tới duplicate)

  • INTERSECT : LẤY PHẦN TỬ IN A VÀ B

  • MINUS : LẤY PHẦN TỬ IN A TRỪ IN B

chú ý

  • Số lượng column ở 2 câu query phải như nhau, data-type từng column tương ứng phải giống nhau

  • Tên column sẽ lấy tên column ở câu query thứ 1

SELECT STOCK,CLOSE_P
FROM ALLSTOCKTEST
UNION
SELECT STOCK,CLOSE_P
FROM ALLSTOCKS
ORDER BY STOCK

SELECT STOCK,RETURN_P, TO_CHAR(NULL) NOTE
FROM ALLSTOCKTEST
UNION ALL
SELECT STOCK,TO_Number(NULL), NOTE_STOCK
FROM ALLSTOCKS

  • cột return_p(number) ở query1 nên phải to_number cột thứ 2 ở câu query 2

1.3.6.9. Subqueries#

SELECT STOCK,CLOSE_P
FROM ALLSTOCKTEST
WHERE CLOSE_P > (SELECT MAX(CLOSE_P) FROM ALLSTOCKTEST WHERE STOCK = 'ACB')

Single-row Subquery : câu subquery return ra 1 row khi đó ta sử dụng các toán tử so sánh 1 row

Multi-row Subquery : câu subquery return ra nhiều rows, khi đó ta sử dụng:

  • các multi-row function để trả về 1 row

  • hoặc sử dụng IN, ANY, ALL

SELECT STOCK,CLOSE_P
FROM ALLSTOCKTEST
WHERE ( CLOSE_P > ALL(SELECT CLOSE_P FROM ALLSTOCKTEST WHERE STOCK = 'ACB')
AND STOCK NOT IN (SELECT STOCK FROM ALLSTOCKTEST WHERE CLOSE_P > 20) )

1.3.7. Single-row Function#

1.3.7.1. Character Functions#

Function

Description

LOWER(str)

viết thường str

UPPER(str)

VIẾT HOA STR

INITCAP(str)

Viết Hoa Chữ Đầu

CONCAT(str1,str2,…)

Nối str

SUBSTR(str, start, length)

trích từ str với đánh dấu từ 1,2,3,… hoặc …-3,-2,-1

LENGTH(str)

Độ dài str

INSTR(str, substr, a, b)

Trả vị trí của ký tự substr lần thứ b[1] trong str bắt đầu từ vị trí a[1]

LPAD/RPAD(str,full-length,’x’)

lấp đầy bên L/R của str thành full-length bởi ký tự ‘x’

TRIM(‘x’ FROM str

xóa toàn bộ ‘x’ hai bên của str

TRIM( [LEADING/TRAILING] ‘x’ FROM str

xóa toàn bộ ‘x’ bên trái(LEADING) hoặc bên phải(TRAILING) của str

REPLACE(str,’x’,’y’)

thay thế ‘x’ bằng ‘y’ trong str

 
code='''
SELECT instr('dssais ka m ais','is') 
test
FROM dual
'''
pd.read_sql_query(code,conn)
TEST
0 5 
code='''
SELECT substr(\'dssaims ka m ais\',-3,3) 
test
FROM dual
'''
pd.read_sql_query(code,conn)
TEST
0 ais 
code='''
SELECT concat('AB','t') 
test
FROM dual
'''
pd.read_sql_query(code,conn)
TEST
0 ABt

1.3.7.2. Number Functions#

Function

Description

ROUND(num,a)

làm tròn num ở vị trí a sau dấu thập phân

TRUNC(num,a)

cắt num ở vị trí a sau dấu thập phân

MOD(num,a)

số dư của num chia cho a

ABS

FLOOR

CEIL

 

code='''
SELECT ROUND(123.456,2) test1, ROUND(123.456,-1) test2, TRUNC(123.456,-2) test3, MOD(123456,1000) test4
FROM dual
'''
pd.read_sql_query(code,conn)
TEST1 TEST2 TEST3 TEST4
0 123.46 120 100 456

1.3.7.3. Date Functions#

# trả về thời điểm hiện tại
code='''
SELECT SYSDATE,SYSDATE + 1, SYSDATE + 1/24
FROM dual
'''
pd.read_sql_query(code,conn)
SYSDATE SYSDATE+1 SYSDATE+1/24
0 2020-02-02 15:59:06 2020-02-03 15:59:06 2020-02-02 16:59:06

Function

Description

MONTHS_BETWEEN(date1,date2)

Tính số tháng giữa date1 vs date2

ADD_MONTHS(date1,num_mon)

cộng thêm num_mon tháng vào date1

NEXT_DAY(date,weekday)

trả về ngày tiếp theo là weekday từ ngày date

LAST_DAY(date)

trả ngày cuối cùng của tháng date

ROUND(date, ‘month/year’)

làm tròn date theo tháng hoặc năm

TRUNC(date, ‘month/year’)

cắt date theo tháng hoặc năm

 
code='''
SELECT SYSDATE,MONTHS_BETWEEN(SYSDATE,'01-JAN-20'), ADD_MONTHS('31-JAN-96',1), NEXT_DAY(SYSDATE,'FRIDAY'), LAST_DAY(SYSDATE)
FROM dual
'''
pd.read_sql_query(code,conn)
SYSDATE MONTHS_BETWEEN(SYSDATE,'01-JAN-20') ADD_MONTHS('31-JAN-96',1) NEXT_DAY(SYSDATE,'FRIDAY') LAST_DAY(SYSDATE)
0 2020-02-02 16:18:30 1.054178 1996-02-29 2020-02-07 16:18:30 2020-02-29 16:18:30 
#ngày từ 1-15 làm tròn xuống, tháng từ 1-6 làm tròn xuống
code='''
SELECT SYSDATE,ROUND(SYSDATE,'month'), ROUND(SYSDATE,'year'), TRUNC(SYSDATE,'month'), TRUNC(SYSDATE,'year')
FROM dual
'''
pd.read_sql_query(code,conn)
SYSDATE ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'YEAR') TRUNC(SYSDATE,'MONTH') TRUNC(SYSDATE,'YEAR')
0 2020-02-02 16:45:56 2020-02-01 2020-01-01 2020-02-01 2020-01-01

1.3.7.4. Conversion Functions#

Function

Description

TO_DATE(char,’format’)

Convert char thành DATE với khai báo char đang follow theo format cho trước

TO_NUMBER

TO_CHAR(CHARACTER)

TO_CHAR(NUMBER,format)

TO_CHAR(date,’format-date’)

convert date thành character với date theo format mong muốn

  • implicit data type conversion : oracle tự động converse được sang kiểu phù hợp. ví dụ: ‘123’+3 thì oracle converse được ‘123’ thành kiểu number 123

  • explicit data type conversion : ta phải tự viết hàm converse data, data không ở dạng mà oracle có thể tự động converse được.

ký hiệu cho date

Function

Description

YYYY

hiển thị năm 2017

YEAR

Hiển thị năm viết chữ

MONTH

Hiển thị tháng January

MON

Hiển thị tháng JAN

MM

Hiển thị tháng 01

DY

Hiển thị ngày trong tuần FRI

DAY

Hiển thị ngày trong tuần full-name MONDAY

DD

Hiển thị ngày trong tháng 01,02,…

HH24:MI:SS AM

14:05:34 PM

‘DD “of” MONTH’

12 of JUNE

DDspth

ngày 14 là ‘fourteenth’, ‘sp’ ký hiệu của spelling

1.3.7.4.1. to_char#

thường dùng cho out-put data

SELECT TO_CHAR(SYSDATE,'hh24:mi:ss DD/Month/YYYY') AS DAYY
FROM dual

SELECT TO_CHAR(CLOSE_P,'$99,999.00') AS GIA 
FROM dual

#đưa giá về dạng format, 9 đại diện cho số, 0 đại diện cho không phải số nhưng ép số, $/L đại diện cho tiền tệ

1.3.7.4.2. to_num / to_date#

thường dùng cho in-put data

SELECT STOCK,CLOSE_P AS GIA
FROM ALLSTOCKTEST
WHERE DAY_TRADING < TO_DATE('21/06/18','DD/MM/RR')

'DD/MM/RR' giúp format '21/06/18' cho oracle hiểu được

1.3.7.5. Null Functions#

chủ yếu là hàm xử lý null input parameter có thể là char, num , date, tuy nhiên các kiểu giữ liệu trong cùng 1 hàm phải giống nhau

Function

Description

NVL(a,c)

=a nếu a notnull else =c nếu a=null

NVL2(a,b,c)

=b nếu a notnull else =c nếu a=null

NULLIF(a,b)

=null if a=b else =a

COALESCE(a1,a2,…,an)

trả về giá trị đầu tiên notnull trong chuỗi từ a1,a2,… else trả null

 
SELECT *, COALESCE(TO_char(close_p),to_char(stock),'stock ko có data') as checkdata
FROM ALLSTOCKTEST

SELECT *, COALESCE(TO_char(close_p),to_char(stock),'stock ko có data') as checkdata
FROM ALLSTOCKTEST

1.3.7.6. Condition#

Function

Description

CASE a WHEN b THEN c ELSE d END

so sánh nếu a=b thì return c , nếu ko thì return d

DECODE(a,b1,c1,b2,c2,d)

nếu a=b1 return c1 else nếu a=b2 return c2 else d

 
SELECT STOCK, CLOSE_P,
CASE STOCK 
    WHEN tb.stock='ACB' THEN CLOSE_P*1.1
    WHEN tb.stock>'ACC' THEN CLOSE_P*1.2
    WHEN 'AVM' THEN CLOSE_P*1.3
    ELSE CLOSE_P END AS NEW_P
FROM ALLSTOCKTEST tb;

SELECT STOCK, CLOSE_P,
DECODE(STOCK,'ACB', CLOSE_P*1.1,
             'ACC', CLOSE_P*1.2,
             'AVM', CLOSE_P*1.3,
        CLOSE_P) AS NEW_P
FROM ALLSTOCKTEST

1.3.8. Aggregate Function#

Multiple-rows Function

AVG,MIN,MAX,COUNT,SUM,STDDEV,MEDIAN,VARIANCE,…

  • CÁC HÀM BỎ QUA null

  • COUNT(*) đếm cả giá trị null

  • COUNT(col) ko đếm giá trị null

code='''
SELECT AVG(CLOSE_P)
FROM ALLSTOCKTEST
'''
pd.read_sql_query(code,conn)
AVG(CLOSE_P)
0 20.81
Contents