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 về View - Proc (Stored, Function) Lập trình SQL Server - Quản lý điểm thi SQL Server

[Share Code] Tìm hiểu về View - Proc (Stored, Function) Lập trình SQL Server - Quản lý điểm thi SQL Server

by GokiSoft.com - 09:15 26/03/2021 1,428 Lượt Xem



-- TEST
select * from Student
select * from Subject
select * from Marks

-- Query -> Hien thi du lieu theo format
---- rollno, fullname, email, subject_name, marks
select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id

-- Query -> Hien thi du lieu theo format
---- rollno, fullname, email, subject_name, marks
select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student left join Marks on Student.rollno = Marks.rollno
	left join Subject on Subject.subject_id = Marks.subject_id

-- View -> table tam thoi duoc sinh ra boi cau lenh select
create view view_student_marks
as
select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id

select * from view_student_marks
-- Dac diem view
---- 1. Chua duy nhat 1 cau lenh select
---- 2. Moi mot view -> duoc hieu nhu la 1 bang tam thoi.

select * from view_student_marks
where marks >= 8
order by marks desc

alter view view_student_marks
as
select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student left join Marks on Student.rollno = Marks.rollno
	left join Subject on Subject.subject_id = Marks.subject_id

drop view view_student_marks

-- Note: Khi tao view muon su dung order by => ket hop vs lenh top() -> neu khong se bi error
create view view_student_marks
as
select top(10) Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id
order by Marks.marks desc

-- View Test -> error
create view view_test
as
select * from Student
select * from Subject
select * from Marks

-- Quan ly dc danh sach lenh -> giong nhu tao function trong js, c, ...
---- Stored, proc, function
create proc proc_test
as
begin
	select * from Student
	select * from Subject
	select * from Marks
end

exec proc_test
---- Mot so dac tinh cua function
------ Function khong tham so dau vao
------ Function co tham so dau vao
------ Function tra du lieu ve & khong tra du lieu ve
create proc proc_view_student_mark
as
begin
	select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.rollno = Marks.rollno
		and Subject.subject_id = Marks.subject_id
	order by Marks.marks desc
end

---- Goi toi stored/proc -> hieu nhu la dang goi toi 1 ham -> thuc hien cac cau lenh trong ham do.
exec proc_view_student_mark

select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id
	and Marks >= 7
order by Marks.marks desc

select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id
	and Marks >= 9
order by Marks.marks desc

---- Dung giai phap -> stored -> de giai quyet bai toan tren
create proc proc_view_student_marks_by_marks
	@marks float
as
begin
	select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.rollno = Marks.rollno
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @marks
	order by Marks.marks desc
end

exec proc_view_student_marks_by_marks 7
exec proc_view_student_marks_by_marks 9
exec proc_view_student_marks_by_marks 5

create proc proc_view_student_marks_by_marks2
	@marks float,
	@rollno nvarchar(12)
as
begin
	select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.rollno = Marks.rollno
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @marks
		and Student.rollno = @rollno
	order by Marks.marks desc
end

exec proc_view_student_marks_by_marks2 5, 'R001'
exec proc_view_student_marks_by_marks2 5, 'R002'

drop proc proc_view_student_marks_by_marks2

alter proc proc_view_student_marks_by_marks2
	@marks float,
	@rollno nvarchar(12),
	@subjectName nvarchar(30)
as
begin
	select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.rollno = Marks.rollno
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @marks
		and Student.rollno = @rollno
		and Subject.subject_name like CONCAT('%', @subjectName, '%')
	order by Marks.marks desc
end

exec proc_view_student_marks_by_marks2 5, 'R001', 'P'

---- Stored co du lieu tra ve.
select Student.rollno, Student.fullname, count(Student.rollno) 'Count'
from Student left join Marks on Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

create proc proc_count_marks_by_student
	@rollno nvarchar(12),
	@count int output
as
begin
	select Student.rollno, Student.fullname, count(Student.rollno) 'Count'
	from Student left join Marks on Student.rollno = Marks.rollno
	where Student.rollno = @rollno
	group by Student.rollno, Student.fullname
	
	select @count = count(Student.rollno)
	from Student left join Marks on Student.rollno = Marks.rollno
	where Student.rollno = @rollno
end

declare @count1 int
exec proc_count_marks_by_student 'R001', @count = @count1 output
print @count1




Bình luận



Chia sẻ từ lớp học

Đã sao chép!!!