Bài giảng Cơ sở dữ liệu địa lý: Chương 5 - ThS. Nguyễn Duy Liêm

Chia sẻ: _ _ | Ngày: | Loại File: PDF | Số trang:71

Thêm vào BST

Báo xấu

16
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Bài giảng Cơ sở dữ liệu địa lý: Chương 5 Ngôn ngữ truy vấn có cấu trúc, cung cấp cho người học những kiến thức như: Ngôn ngữ truy vấn có cấu trúc là gì; Đặc điểm của SQL; Định nghĩa dữ liệu; Thao tác dữ liệu. Mời các bạn cùng tham khảo!

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài giảng Cơ sở dữ liệu địa lý: Chương 5 - ThS. Nguyễn Duy Liêm

TRƯỜNG ĐẠI HỌC NÔNG LÂM TP. HỒ CHÍ MINH KHOA MÔI TRƯỜNG & TÀI NGUYÊN | BỘ MÔN GIS & TÀI NGUYÊN Ngôn ngữ truy vấn có cấu trúc (Structured Query Language) SELECT FROM WHERE GROUP BY HAVING ORDER BY ; Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 1
Nội dung  Các khái niệm (Concepts)  Truyvấn (SELECT)  Ngôn ngữ truy vấn có cấu trúc là gì? Chọn cột (SELECT) (Structured Query Language, SQL) Từ bảng (FROM)  Đặc điểm của SQL (Characteristics of SQL) Điều kiện chọn hàng (WHERE)  Phân loại SQL (Classification of SQL) Nhóm theo cột (GROUP BY)  Cú pháp SQL (Syntax of SQL) Điều kiện nhóm cột (HAVING)  Định nghĩa dữ liệu (Data definition) Sắp thứ tự theo cột (ORDER BY)  Tạo (CREATE) Phép kết bảng (INNER JOIN, FULL JOIN)  Thay đổi (ALTER) Truy vấn con (Subquery)  Hủy bỏ (DROP)  Thao tác dữ liệu (Data manipulation)  Thêm (INSERT)  Sửa (UPDATE)  Xóa (DELETE) Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 2
Thế giới Quá trình thiết kế CSDL thực Thu thập, phân tích yêu cầu Ý tưởng Yêu cầu chức năng Yêu cầu dữ liệu Lược đồ liên kết Phân tích chức năng Thiết kế ý niệm – thực thể (ER) Lược đồ ý niệm Đặc tả chức năng ở Độc lập (mô hình dữ liệu ở mức cao) mức cao DBMS Thiết kế luận lý Lược đồ quan hệ Phụ thuộc Thiết kế DBMS chương trình Lược đồ luận lý ứng dụng (mô hình dữ liệu cho DBMS) Thực hiện CSDL quan hệ Thiết kế vật lý giao tác Chương trình ứng dụng Lược đồ vật lý Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 3
Ngôn ngữ truy vấn có cấu trúc là gì?  TS. Donald D. Chamberlin, TS. Raymond F. Boyce (làm việc tại IBM) giới thiệu vào năm 1974. TS. Donald D. TS. Raymond F.  1974: SEQUEL (Structured English QUEry Language). Chamberlin Boyce 1944, Hoa Kì 1946-1974, Hoa Kì  1976: SEQUEL được cải tiến thành SEQUEL2. Chamberlin, Donald D; Boyce, Raymond F (1974). "SEQUEL: A Structured English Query Language". Proceedings of the 1974 ACM SIGFIDET Workshop on Data Description, Access and Control. Association for SEQUEL2 được cải tiến, đổi tên thành SQL. Computing Machinery: 249–64. doi:10.1145/800296.811515  1978-1979: Lịch sử tiêu chuẩn của SQL  Được dùng để truy cập, thao tác CSDL. Phiên bản đầu tiên Nền tảng Phát triển Hiện đại 1986 1989 1992 1999 2003 2006-2019 SQL-86 SQL-89 SQL-1992 SQL:1999 SQL:2003 SQL:2006  Ngôn ngữ thương mại sử dụng mô hình quan hệ. SQL:2008 SQL:2011 SQL:2016  Ngôn ngữ CSDL phổ biến nhất. SQL:2019 SELECT Recursive CTE JOIN Ngôn ngữ chuẩn cho CSDL quan hệ Các chức năng Cải thiện FROM  UNION GROUP BY Window ngôn ngữ WHERE ROLLUP INTERSECT GROUP BY CUBE EXCEPT HAVING GROUPING SETS Viện Tiêu chuẩn Quốc gia Hoa Kỳ (ANSI), CASE WHEN  1986: INSERT UPDATE CAST ALTER TABLE DELETE DROP TABLE Tổ chức Tiêu chuẩn Quốc tế (ISO). CREATE TABLE  1987: ALTER VIEW CREATE VIEW DROP VIEW Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 4
Đặc điểm của SQL  Ngôn ngữ tựa tiếng Anh  Sử dụng các từ như SELECT, INSERT, DELETE,… trong tập lệnh.  Ngôn ngữ phi thủ tục  Người dùng cho biết rõ thông tin gì cần lấy mà không chỉ ra cách thực hiện như thế nào để có được thông tin này.  Xử lý tập hợp mẩu tin hơn là một mẩu tin đơn lẻ  Bảng hơn là hàng.  Nhiều người dùng có thể sử dụng  Quản trị viên, lập trình ứng dụng, nhà quản lý, người dùng đầu cuối.  Dễ học vì SQL cực kỳ thực tế và thân thiện với người dùng (không yêu cầu kinh nghiệm về công nghệ). Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 5
Phân loại SQL 1. Ngôn ngữ định nghĩa dữ liệu 3. Ngôn ngữ điều khiển dữ liệu (DDL- Data Definition Language) (DCL- Data Control Language)  Các lệnh để tạo (CREATE), thay đổi  Cáclệnh để phân quyền truy xuất (ALTER), xóa (DROP) các đối tượng các đối tượng CSDL: gán quyền CSDL. (GRANT), hủy bỏ quyền (REVOKE).  Quảntrị viên CSDL (bảo vệ cấu trúc  Quản trị viên CSDL (bảo mật CSDL). CSDL). 2. Ngôn ngữ thao tác dữ liệu 4. Ngôn ngữ điều khiển giao tác (DML- Data Manipulation (TCL- Transaction Control Language) Language)  Các lệnh để truy vấn (SELECT),  Các lệnh để quản lý giao tác CSDL: thêm (INSERT), sửa (UPDATE), xóa lưu thay đổi CSDL (COMMIT), hủy (DELETE) dữ liệu của bảng. bỏ thay đổi CSDL (ROLLBACK).  Người sử dụng CSDL (khai thác  Quản trị viên CSDL (bảo mật CSDL). CSDL). Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 6
Cú pháp SQL  Câu lệnh (Statement)  Bắt đầu bằng động từ (SELECT, INSERT, UPDATE, DELETE,…),  Tiếp đến là chủ ngữ và vị ngữ (điều kiện),  Kết thúc bằng dấu chấm phẩy (;). -- Lấy nhân viên gia nhập công ty vào năm 2000 SELECT Động từ Mệnh đề SELECT first_name Chủ ngữ FROM Mệnh đề FROM employees WHERE Mệnh đề WHERE YEAR(hire_date) = 2000; Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Vị ngữ Ngôn ngữ truy vấn có cấu trúc 7
Cú pháp SQL  Từ khóa (Keyword)  SELECT, INSERT, UPDATE, DELETE,…  Không phân biệt chữ HOA và chữ thường, quy ước VIẾT HOA. SELECT = select.  Không dùng từ khóa để đặt tên cho các đối tượng trong CSDL (bảng, cột, chỉ mục, khung nhìn, thủ tục, trình kích hoạt,…).  Số nhận dạng (Identifier) -- Lấy nhân viên gia nhập công ty vào năm 2000 SELECT Từ khóa  Tên của các đối tượng trong CSDL. first_name  Không phân biệt chữ HOA và chữ thường, Số nhận dạng quy ước viết thường. FROM employees Employees = EMPLOYEES. WHERE  Không khoảng trắng, tiếng Việt không dấu. Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn YEAR(hire_date) ngữ truy vấn có cấu trúc = 2000; 8
Cú pháp SQL  Chú thích (Comment)  Đoạn văn bản giải thích ý nghĩa của câu lệnh.  Máy chủ CSDL bỏ qua chú thích trong phân tích câu lệnh.  Bắt đầu bằng hai dấu gạch ngang liên tiếp (--), hoặc đặt trong /* */ (nếu chú thích gồm nhiều dòng). /* Lấy nhân viên gia nhập công ty Chú thích vào năm 2000 */ SELECT first_name FROM employees WHERE Chú thích YEAR(hire_date) = 2000; -- năm tuyển dụng là 2000 Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 9
Cú pháp SQL  Hằng số (Literal)  Nhị phân (bắt đầu bằng x)  Chuỗi(phân biệt chữ HOA và chữ x'01' thường, đặt trong dấu nháy đơn) x'0f0ff' 'John' ≠ 'john' ≠ 'JOHN' '1990-01-01' /* Lấy nhân viên gia nhập công ty từ năm 2000 đến nay '50' và có mức lương trên 10 triệu/tháng */  Số SELECT 200 first_name -5 FROM 6.0221415E23 employees Chuỗi Số WHERE hire_date > '1999-12-31' AND salary > 10; Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 10
Định nghĩa dữ liệu – CSDL  Tạo CSDL CREATE DATABASE ; CREATE DATABASE climate;  Đổi tên CSDL ALTER DATABASE ALTER DATABASE climate RENAME TO ; RENAME TO climate1;  Hủy bỏ CSDL DROP DATABASE ; DROP DATABASE climate1; Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 12
CREATE TABLE weather ( Định nghĩa dữ liệu – Bảng, cột city varchar(80),  Tạo bảng temp_lo integer, CREATE TABLE ( temp_hi integer, (), prcp numeric, …, date date () ); ); Kiểu dữ liệu Mô tả integer số nguyên: 2.147.483.648 đến -2.147.483.648 serial số nguyên tự động tăng giá trị: 1 đến 2.147.483.647 numeric (precision, scale) số thập phân - precision (tổng số chữ số nguyên và thập phân) tối đa: 147.455 - scale (số chữ số thập phân) tối đa: 16.383 varchar (n) chuỗi: n = 1 đến 10.485.760 date ngày (độ phân giải 1 ngày): 4.713 BC đến 5.874.897 AD, 'YYYY-MM-DD' time giờ (độ phân giải 1/1 triệu giây ): 00:00:00 đến 24:00:00, 'HH:MM:SS' timestamp mốc thời gian (ngày giờ, độ phân giải 1/1 triệu giây): 4.713 BC đến 294.276 Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn AD, 'YYYY-MM-DD HH:MM:SS' Ngôn ngữ truy vấn có cấu trúc 13
Định nghĩa dữ liệu – Bảng, cột  Tạo bảng với ràng buộc khóa chính CREATE TABLE weather ( CREATE TABLE ( city varchar(80) PRIMARY KEY, () PRIMARY KEY, temp_lo integer, …, temp_hi integer, () prcp numeric, ); date date ); CREATE TABLE weather ( CREATE TABLE ( city varchar(80), (), temp_lo integer, …, temp_hi integer, (), prcp numeric, PRIMARY KEY (,…, ) date date, ); PRIMARY KEY (city) ); Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 14
Định nghĩa dữ liệu – Bảng, cột  Tạo bảng với ràng buộc khóa ngoại CREATE TABLE weather ( city varchar(80) CREATE TABLE ( REFERENCES station (city), () PRIMARY KEY, temp_lo integer, () REFERENCES temp_hi integer, (), prcp numeric, …, date date () ); ); CREATE TABLE weather ( city varchar(80), CREATE TABLE ( temp_lo integer, () PRIMARY KEY, temp_hi integer, …, prcp numeric, (), date date, FOREIGN KEY (,…, ) REFERENCES FOREIGN KEY (city) REFERENCES (,…, ) station (city) ); ); Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 15
Định nghĩa dữ liệu – Bảng, cột  Tạo bảng với ràng buộc kiểu cha và kiểu con CREATE TABLE ( CREATE TABLE cities ( () PRIMARY KEY, name varchar(80), …, population integer, () elevation numeric, ); PRIMARY KEY (name) ); CREATE TABLE ( CREATE TABLE capitals ( (), state varchar(2) ) INHERITS (cities); …, () ) INHERITS (); Bảng con kế thừa tất cả cột của bảng cha Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 16
Định nghĩa dữ liệu – Bảng, cột  Tạo bảng với ràng buộc miền trị của cột CREATE TABLE ( () PRIMARY KEY, …, () CHECK (), CHECK () ); CREATE TABLE products ( product_no integer PRIMARY KEY, name text CHECK (name IN ('A', 'B')), price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) ); Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 17
Định nghĩa dữ liệu – Bảng, cột  Tạo bảng với ràng buộc cột không thể có giá trị rỗng CREATE TABLE ( () PRIMARY KEY, … () NOT NULL ); CREATE TABLE products ( product_no integer PRIMARY KEY, name varchar NOT NULL, price numeric ); Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 18
Định nghĩa dữ liệu – Bảng, cột  Tạo bảng với ràng buộc cột có giá trị duy nhất (khóa dự tuyển) CREATE TABLE ( CREATE TABLE products ( () PRIMARY KEY, product_no integer PRIMARY KEY, … name varchar NOT NULL UNIQUE, () UNIQUE price numeric, ); discounted_price numeric ); CREATE TABLE ( CREATE TABLE products ( product_no integer PRIMARY KEY, () PRIMARY KEY, name varchar NOT NULL, … price numeric NOT NULL, (), discounted_price numeric, UNIQUE (,…, ) UNIQUE (name, price) ); ); Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 19
Bài tập 1  Cho lược đồ CSDL quan hệ công ty bao gồm phòng ban và nhân viên.  Viết câu lệnh SQL: 1. Tạo CSDL tên CongTy? 2. Tạo bảng PhongBan? Nhân viên (Mã số nhân viên, Số định danh, Họ tên, Mã số phòng ban, Lương, Ngày kí hợp đồng) 3. Tạo bảng NhanVien? Thuộc tính Kiểu dữ liệu Ràng buộc Phòng ban (Mã số phòng ban, Tên phòng Mã số nhân viên serial Khóa chính ban) Số định danh varchar Giá trị không rỗng, duy nhất Thuộc tính Kiểu dữ liệu Ràng buộc Họ tên varchar Giá trị không rỗng Mã số varchar Khóa chính Mã số phòng ban varchar Khóa ngoại (tham chiếu Mã số phòng ban phòng ban của quan hệ Phòng Tên phòng varchar Miền trị: Tiếp thị, Kế ban) ban toán, Thông tin, Tài Lương numeric Giá trị lớn hơn 0 chính Ngày kí hợp đồng date Giá trị không rỗng Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 20
Định nghĩa dữ liệu – Bảng, cột ALTER TABLE weather  Thay đổi bảng ADD COLUMN hmd numeric,  Thêm cột ADD COLUMN wnd numeric; ALTER TABLE ADD COLUMN (), ADD COLUMN (), ALTER TABLE weather …; DROP COLUMN hmd,  Hủy bỏ cột DROP COLUMN wnd; ALTER TABLE DROP COLUMN , DROP COLUMN , ALTER TABLE weather …; ALTER COLUMN hmd TYPE integer,  Đổi kiểu dữ liệu của cột ALTER TABLE ALTER COLUMN wnd TYPE integer; ALTER COLUMN TYPE (), ALTER COLUMN TYPE (), …; Copyright © 2022 | nguyenduyliem@hcmuaf.edu.vn Ngôn ngữ truy vấn có cấu trúc 21