IMG-LOGO
×

Giáo Trình Môn Học

Khoá học lập trình SQL

Kiến Thức Core SQL Server

[Video] Hướng dẫn cách xác định kiểu dữ liệu, primary key, foreign key trong SQL - My SQL - SQL Server [Video] Hướng dẫn thao tác nhiểu bảng - select - multi table - join, inner join trong SQL Server [Video] Hướng dẫn phân tích CSDL - phân tích bảng, column trong bản - Học lập trình SQL, SQL Server [Video] Hướng dẫn tìm hiểu create, alter , insert table và bản chất primary key, foreign key - SQL Server [Video] Hướng dẫn tìm hiểu select, and, in, like, not in,group by .. having, order by asc, desc - SQL Server [Video] Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

View SQL Server

[Video] Hướng dẫn tìm hiểu view trong SQL Server Bài Giảng: Hướng dẫn tìm hiểu view + proc trong SQL - Lập trình SQL Server

Store & Procedure

[Video] Hướng dẫn tìm hiểu proc procedure trong SQL Server - Lập trình SQL Server, MySQL Bài Giảng: Xây dự hệ thống đưa đón học sinh bằng xe bus của Trường Học - Lập trình SQL Server [Video] Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

Trigger

[Video] Học Trigger qua ví dụ - quản lý nhà trên phố - Lập trình SQL Server [Video] Hướng dẫn học trigger qua bài quản lý điểm danh Aptech - Lập trình SQL Server [Video] Bài tập - Quản lý nhân khẩu - Lập trình SQL Server - MySQL




Trang Chủ SQL Server/MySQL [Share Code] Tìm hiểu PROC - View - Lập trình SQL Server

[Share Code] Tìm hiểu PROC - View - Lập trình SQL Server

by Nguyễn Tiến Đạt - 15:03 30/11/2020 1,613 Lượt Xem



-- create database
CREATE DATABASE bt1776

-- active database
USE bt1776

-- create tables
CREATE TABLE product (
	id INT PRIMARY KEY IDENTITY(1,1),
	title NVARCHAR(150),
	manufacturer_name NVARCHAR(100),
	madein NVARCHAR(50),
	buy_price FLOAT,
	sell_price FLOAT,
	created_at DATE
)

CREATE TABLE orders (
	id INT PRIMARY KEY IDENTITY(1,1),
	id_product INT REFERENCES dbo.product(id),
	note NVARCHAR(500),
	created_at DATE,
	num INT
)

-- insert data
INSERT INTO product( title ,manufacturer_name ,madein ,buy_price ,sell_price ,created_at)
VALUES
( N'san pham 1' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 2' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 3' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 4' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 5' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 6' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 7' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 8' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 9' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
( N'san pham 10' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE())

SELECT * FROM dbo.product

INSERT INTO dbo.orders
        (id_product, note, created_at, num )
VALUES
(2,N'OKOK',GETDATE(),2),
(1,N'OKOK',GETDATE(),21),
(6,N'OKOK',GETDATE(),5),
(2,N'OKOK',GETDATE(),12),
(1,N'OKOK',GETDATE(),27),
(2,N'OKOK',GETDATE(),28),
(9,N'OKOK',GETDATE(),34),
(2,N'OKOK',GETDATE(),21),
(5,N'OKOK',GETDATE(),20)

SELECT * FROM dbo.orders
-- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, ghi chu, so luong
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
ORDER BY dbo.product.id ASC
-- -- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, ghi chu, so luong (tinh tong so luong ban ra)

SELECT dbo.product.id, dbo.product.title, dbo.orders.num, dbo.orders.note
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.orders.note, SUM(dbo.orders.num) AS 'Total'
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title, dbo.orders.note
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title
ORDER BY dbo.product.id ASC

-- -- Hien thi thong tin don hang dc ban ra: id san pham, tieu de, MADEIN, ghi chu, so luong (tinh tong so luong ban ra)
SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'Viet Nam'
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num, dbo.product.madein
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'Viet Nam'
ORDER BY dbo.product.id ASC

--- Xem thong tin don hang
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product

-- Luu xuong duoi 1 view => 1 bang du lieu tam thoi => dc sinh ra khi chay cau select
CREATE VIEW view_show_order_detail
AS
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product

SELECT * FROM view_show_order_detail
ORDER BY id ASC

ALTER VIEW view_show_order_detail
AS
SELECT * FROM dbo.product

ALTER VIEW view_show_order_detail
AS
SELECT dbo.product.id, dbo.product.title, dbo.orders.note, dbo.orders.num
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product

DROP VIEW view_show_order_detail

-- Produce
CREATE PROC proc_test
AS
BEGIN
	SELECT * FROM dbo.product
	SELECT * FROM dbo.orders
	SELECT * FROM view_show_order_detail
END

EXEC proc_test

CREATE PROC proc_fake_data
AS
BEGIN
	-- insert data
	INSERT INTO product( title ,manufacturer_name ,madein ,buy_price ,sell_price ,created_at)
	VALUES
	( N'san pham 1' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 2' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 3' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 4' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 5' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 6' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 7' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 8' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 9' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE()),
	( N'san pham 10' ,N'gokisoft' ,N'Viet Nam' ,20.0 ,50.0 ,GETDATE())

	SELECT * FROM dbo.product

	INSERT INTO dbo.orders
			(id_product, note, created_at, num )
	VALUES
	(2,N'OKOK',GETDATE(),2),
	(1,N'OKOK',GETDATE(),21),
	(6,N'OKOK',GETDATE(),5),
	(2,N'OKOK',GETDATE(),12),
	(1,N'OKOK',GETDATE(),27),
	(2,N'OKOK',GETDATE(),28),
	(9,N'OKOK',GETDATE(),34),
	(2,N'OKOK',GETDATE(),21),
	(5,N'OKOK',GETDATE(),20)

	SELECT * FROM dbo.orders
END

EXEC proc_fake_data


SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
FROM dbo.product, dbo.orders
WHERE dbo.product.id = dbo.orders.id_product
	AND dbo.product.madein = 'JP'
GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
ORDER BY dbo.product.id ASC

CREATE PROC proc_view_order_madein
	@madein nvarchar(50)
AS
BEGIN
	SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
	FROM dbo.product, dbo.orders
	WHERE dbo.product.id = dbo.orders.id_product
		AND dbo.product.madein = @madein
	GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
	ORDER BY dbo.product.id ASC
END

EXEC proc_view_order_madein 'Viet Nam'

-- PRO => xem don xuat xu tu 1 quoc + san pham ban ra co gia @min
CREATE PROC proc_view_order_madein_min
	@madein nvarchar(50),
	@min int
AS
BEGIN
	SELECT dbo.product.id, dbo.product.title, dbo.product.madein, SUM(dbo.orders.num)
	FROM dbo.product, dbo.orders
	WHERE dbo.product.id = dbo.orders.id_product
		AND dbo.product.madein = @madein
	GROUP BY dbo.product.id, dbo.product.title, dbo.product.madein
	HAVING SUM(dbo.orders.num) >= @min
	ORDER BY dbo.product.id ASC
END

EXEC proc_view_order_madein_min 'Viet Nam', 30

-- Dem tong so san pham trong product
CREATE PROC proc_count_product
	@count int OUTPUT
AS
BEGIN
	SELECT @count = COUNT(*) FROM dbo.product
END

DECLARE @countItem INT
EXEC proc_count_product @[email protected] output
PRINT @countItem




Bình luận



Chia sẻ từ lớp học

Đã sao chép!!!