Trong hướng dẫn này, bạn sẽ học cách tạo, chỉnh sửa và xóa lược đồ (schema) trong cơ sở dữ liệu SQL Server.
Lược đồ (schema) là tập hợp các đối tượng cơ sở dữ liệu bao gồm table, view, trigger, stored procedure, index, v.v. Một lược đồ được liên kết với username được gọi là chủ sở hữu lược đồ, chủ sở hữu của các đối tượng cơ sở dữ liệu liên quan về mặt logic.
Một lược đồ luôn thuộc về một cơ sở dữ liệu. Mặt khác, một cơ sở dữ liệu có thể có một hoặc nhiều lược đồ. Ví dụ, trong cơ sở dữ liệu mẫu BikeStores
, chúng tôi có hai lược đồ: sales
và production
.
Một đối tượng đủ điều kiện trong lược đồ bằng cách sử dụng định dạng schema_name.object_name
, ví dụ: sales.orders
. Hai bảng trong hai lược đồ có thể dùng chung tên nên bạn có thể có hr.employees
và sales.employees
.
SQL Server cung cấp cho chúng ta một số lược đồ được định nghĩa trước, ví dụ: dbo
, guest
, sys
, và INFORMATION_SCHEMA
.
Lưu ý rằng SQL Server sử dụng các lược đồsys
vàINFORMATION_SCHEMA
cho các đối tượng hệ thống, do đó, bạn không thể tạo hoặc xóa bất kỳ đối tượng nào trong các lược đồ này.
Lược đồ mặc định cho cơ sở dữ liệu mới được tạo là dbo
, thuộc sở hữu của tài khoản người dùng dbo
. Theo mặc định, khi bạn tạo người dùng mới bằng lệnh CREATE USER
, người dùng sẽ lấy dbo
làm lược đồ mặc định của nó.
Câu lệnh CREATE SCHEMA
cho phép bạn tạo một lược đồ mới trong cơ sở dữ liệu hiện tại.
Phần sau minh họa phiên bản đơn giản hóa của câu lệnh CREATE SCHEMA
:
CREATE SCHEMA schema_name
[AUTHORIZATION owner_name]
Trong cú pháp này,
CREATE SCHEMA
.AUTHORIZATION
.Ví dụ sau cho thấy cách sử dụng câu lệnh CREATE SCHEMA
để tạo lược đồ customer_services
:
CREATE SCHEMA customer_services;
GO
Lưu ý rằng lệnhGO
hướng dẫn SQL Server Management Studio gửi các câu lệnh SQL ở phía trên lệnhGO
tới máy chủ để thực thi.
Sau khi thực hiện câu lệnh, bạn có thể tìm thấy lược đồ mới được tạo trong Security > Schemas như hình dưới đây.
Nếu bạn muốn liệt kê tất cả các lược đồ trong cơ sở dữ liệu hiện tại, bạn có thể truy vấn các lược đồ từ sys.schemas
như được minh họa trong truy vấn sau:
SELECT
s.name AS schema_name,
u.name AS schema_owner
FROM
sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY
s.name;
Đây là kết quả:
Sau khi có lược đồ customer_services
, bạn có thể tạo các đối tượng cho lược đồ. Ví dụ: câu lệnh sau tạo một bảng mới có tên jobs
trong lược đồ customer_services
:
CREATE TABLE customer_services.jobs(
job_id INT PRIMARY KEY IDENTITY,
customer_id INT NOT NULL,
description VARCHAR(200),
created_at DATETIME2 NOT NULL
);
Câu lệnh ALTER SCHEMA
cho phép bạn chuyển các securable từ một lược đồ sang một lược đồ khác trong cùng một cơ sở dữ liệu.
Lưu ý rằng: securable là tài nguyên mà hệ thống ủy quyền của Database Engine kiểm soát quyền truy cập. Ví dụ, một bảng là một securable.
Sau đây là cú pháp của câu lệnh ALTER SCHEMA
:
ALTER SCHEMA target_schema_name
TRANSFER [ entity_type :: ] securable_name;
Trong cú pháp này:
target_schema_name
là tên của một lược đồ trong cơ sở dữ liệu hiện tại mà bạn muốn di chuyển đối tượng vào đó. Lưu ý rằng nó không thể là SYS
hoặc INFORMATION_SCHEMA
.entity_type
có thể là Đối tượng, Loại hoặc Tập hợp lược đồ XML. Nó mặc định là Đối tượng. entity_type
đại diện cho lớp của thực thể mà chủ sở hữu đang được thay đổi.securable_name
là tên của securable mà bạn muốn chuyển vào target_schema_name
.Nếu bạn di chuyển một stored procedure, function, view hoặc trigger, SQL Server sẽ không thay đổi tên lược đồ của các securable này. Do đó, bạn nên xóa và tạo lại các đối tượng này trong lược đồ mới thay vì sử dụng câu lệnh ALTER SCHEMA
để di chuyển.
Nếu bạn di chuyển một đối tượng, ví dụ: table hoặc synonym, SQL Server sẽ không tự động cập nhật các tham chiếu cho các đối tượng này. Bạn phải sửa đổi thủ công các tham chiếu để phản ánh tên lược đồ mới. Ví dụ: nếu bạn di chuyển một table được tham chiếu trong một stored procedure, bạn phải sửa đổi stored procedure để phản ánh tên lược đồ mới.
Đầu tiên, hãy tạo một bảng mới có tên offices
trong lược đồ dbo
:
CREATE TABLE dbo.offices
(
office_id INT
PRIMARY KEY IDENTITY,
office_name NVARCHAR(40) NOT NULL,
office_address NVARCHAR(255) NOT NULL,
phone VARCHAR(20),
);
Tiếp theo, insert một số hàng vào bảng dbo.offices
:
INSERT INTO dbo.offices(office_name, office_address)
VALUES ('Silicon Valley','400 North 1st Street, San Jose, CA 95130'),
('Sacramento','1070 River Dr., Sacramento, CA 95820');
Sau đó, tạo một stored procedure để tìm văn phòng theo id văn phòng:
CREATE PROC usp_get_office_by_id(
@id INT
) AS
BEGIN
SELECT
*
FROM
dbo.offices
WHERE
office_id = @id;
END;
Cuối cùng, chuyển bảng dbo.offices
này sang lược đồ sales
:
ALTER SCHEMA sales TRANSFER OBJECT::dbo.offices;
Nếu bạn thực thi stored procedure usp_get_office_by_id
, SQL Server sẽ xuất hiện lỗi:
Msg 208, Level 16, State 1, Procedure usp_get_office_by_id, Line 5 [Batch Start Line 30]
Invalid object name 'dbo.offices'.
Để khắc phục lỗi này, hãy sửa đổi stored procedure sử dụng lược đồ mới:
ALTER PROC usp_get_office_by_id(
@id INT
) AS
BEGIN
SELECT
*
FROM
sales.offices
WHERE
office_id = @id;
END;
Câu lệnh DROP SCHEMA
cho phép bạn xóa một lược đồ khỏi cơ sở dữ liệu. Sau đây là cú pháp của câu lệnh DROP SCHEMA
:
DROP SCHEMA [IF EXISTS] schema_name;
Trong cú pháp này:
IF EXISTS
để xóa lược đồ chỉ khi lược đồ tồn tại. Cố gắng xóa một lược đồ không tồn tại mà không có tùy chọn IF EXISTS
sẽ dẫn đến lỗi.Đầu tiên, hãy tạo một lược đồ mới có tên là logistics
:
CREATE SCHEMA logistics;
GO
Tiếp theo, tạo một bảng mới có tên deliveries
bên trong lược đồ logistics
:
CREATE TABLE logistics.deliveries
(
order_id INT
PRIMARY KEY,
delivery_date DATE NOT NULL,
delivery_status TINYINT NOT NULL
);
Sau đó, xóa lược đồ logistics
:
DROP SCHEMA logistics;
SQL Server sẽ thông báo lỗi sau vì lược đồ không trống.
Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'logistics' because it is being referenced by object 'deliveries'.
Để xóa lược đồ này, trước tiên bạn phải xóa bảng logistics.deliveries
bằng lệnh sau:
DROP TABLE logistics.deliveries;
Cuối cùng, sử dụng lệnh DROP SCHEMA
một lần nữa để xóa lược đồ logistics
:
DROP SCHEMA IF EXISTS logistics;
Bây giờ, bạn sẽ thấy rằng lược đồ logistics
đã bị xóa khỏi cơ sở dữ liệu.
Trong hướng dẫn này, bạn đã học cách tạo, chỉnh sửa và xóa lược đồ (schema) trong cơ sở dữ liệu SQL Server.
Bạn có thể vui lòng tắt trình chặn quảng cáo ❤️ để hỗ trợ chúng tôi duy trì hoạt động của trang web.
Trong hướng dẫn này, bạn sẽ tìm hiểu về GUID trong SQL Server và cách sử dụng hàm NEWID() để tạo giá trị GUID.
Trong hướng dẫn này, bạn sẽ học cách sử dụng kiểu dữ liệu DATETIMEOFFSET trong SQL Server để thao tác datetime với múi giờ.
Trong hướng dẫn này, bạn sẽ học cách lưu trữ thời gian trong ngày trong cơ sở dữ liệu bằng cách sử dụng kiểu dữ liệu TIME trong SQL Server.
Trong hướng dẫn này, bạn sẽ học cách sử dụng kiểu dữ liệu DATE trong SQL Server để lưu trữ dữ liệu ngày tháng trong một bảng.