Nôi dung kiến thức:
- Chữa bài tập
- Nói qua lý thuyết -> giới thiệu -> Azure SQL -> SEM3
- Tìm hiểu index trong thiết kế CSDL
-> clustered index <-> primary key -> duy 1 ->
-> non-clustered index -> tao nhieu dc
- Xây dựng & thiết kế CSDL
==============================================================
-- Tao CSDL
create database BT2243
go
-- Kich hoat CSDL
use BT2243
go
-- Tao tables
create table TrongTai (
id int primary key identity(1,1),
fullname nvarchar(50) not null,
address nvarchar(200) not null,
level float,
exp date
)
go
create table DoiBong (
id int primary key identity(1,1),
name nvarchar(50),
studium_name nvarchar(100),
trainer nvarchar(50)
)
go
create table LichSu (
id int primary key identity(1,1),
id_trongtai int references TrongTai(id),
giaidau nvarchar(100) not null,
ngaybat datetime,
rate float,
id_club_1 int references DoiBong (id),
id_club_2 int references DoiBong (id),
note nvarchar(500)
)
go
create clustered index index_giaidau on LichSu (giaidau)
go
create nonclustered index index_giaidau on LichSu (giaidau)
go
drop index index_giaidau on LichSu
create table CauThu (
id int primary key identity(1,1),
fullname nvarchar(50),
birthday date,
salary money,
joined_date date
)
go
create table FootballMembers (
id_club int references DoiBong(id),
id_player int references CauThu(id),
joined_date date,
primary key (id_club, id_player)
)
go
-- Insert data
insert into TrongTai (fullname, address, level, exp)
values
('TRONG TAI 1', 'HA NOI', 20, '2001-02-18'),
('TRONG TAI 2', 'HA NOI', 12, '2006-06-18'),
('TRONG TAI 3', 'HA NOI', 7, '2012-02-18'),
('TRONG TAI 4', 'HA NOI', 9, '2010-03-18'),
('TRONG TAI 5', 'HA NOI', 30, '2000-02-18')
go
insert into CauThu (fullname, birthday, joined_date, salary)
values
('CAU THU 1', '2001-02-16', '2007-06-08', 30000),
('CAU THU 2', '2002-02-16', '2006-06-08', 30000),
('CAU THU 3', '2003-02-16', '2008-06-08', 30000),
('CAU THU 4', '2004-02-16', '2015-06-08', 30000),
('CAU THU 5', '2005-02-16', '2015-06-08', 30000),
('CAU THU 6', '2002-02-16', '2007-06-08', 30000),
('CAU THU 7', '2002-02-16', '2007-06-08', 30000)
go
insert into DoiBong (name, studium_name, trainer)
values
('DOI BONG 1', 'SAN BONG 1', 'TRAINER 01'),
('DOI BONG 2', 'SAN BONG 2', 'TRAINER 02'),
('DOI BONG 3', 'SAN BONG 3', 'TRAINER 03')
go
insert into FootballMembers (id_club, id_player, joined_date)
values
(1, 1, '2018-06-12'),
(1, 2, '2019-06-12'),
(1, 3, '2016-06-12'),
(2, 4, '2017-06-12'),
(2, 5, '2019-06-12'),
(3, 6, '2018-06-12'),
(3, 7, '2018-06-12')
go
insert into LichSu (id_club_1, id_club_2, id_trongtai, giaidau, ngaybat, rate)
values
(1, 2, 1, 'World Cup 1', '1996-02-12',4),
(2, 1, 1, 'World Cup 1', '1996-02-12',4),
(1, 3, 1, 'World Cup 2', '2000-02-12',5),
(2, 3, 1, 'World Cup 2', '2000-02-12',5),
(1, 2, 1, 'World Cup 3', '2004-02-12',4),
(2, 3, 1, 'World Cup 4', '2004-02-12',4)
go
-- test ket qua
select * from TrongTai
select * from DoiBong
select * from CauThu
select * from FootballMembers
select * from LichSu
go
-- Xem thông tin lịch sử bắt của trọng tài - tên trọng tài (TrongTai), level (TrongTai), exp (TrongTai), giải bóng (LichSu), đội 1 (DoiBong), đội 2 (DoiBong)
---- where, join (inner join), left join, right join
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.giaidau,
LichSu.id_club_1, LichSu.id_club_2
from TrongTai, LichSu
where LichSu.id_trongtai = TrongTai.id
go
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.giaidau,
LichSu.id_club_1, DoiBong.name 'Doi Bong 1',
LichSu.id_club_2, DoiBong.name 'DoiBong 2'
from TrongTai, LichSu, DoiBong
where LichSu.id_trongtai = TrongTai.id
and DoiBong.id = LichSu.id_club_1
go
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.giaidau,
LichSu.id_club_1, DoiBong.name 'Doi Bong 1',
LichSu.id_club_2, DoiBong.name 'DoiBong 2'
from TrongTai, LichSu, DoiBong
where LichSu.id_trongtai = TrongTai.id
and DoiBong.id = LichSu.id_club_2
go
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.giaidau,
LichSu.id_club_1, DB1.name 'Doi Bong 1',
LichSu.id_club_2, DB2.name 'Doi Bong 2'
from TrongTai, LichSu, DoiBong DB1, DoiBong DB2
where LichSu.id_trongtai = TrongTai.id
and DB2.id = LichSu.id_club_2
and DB1.id = LichSu.id_club_1
go
-- Xem danh sách cầu thủ của 1 đội bóng (Ten Doi Bong - DoiBong, Ten Cau Thu - CauThu, Ngay Tham Gia (FootballMembers))
select DoiBong.name 'Ten Doi Bong', CauThu.fullname, FootballMembers.joined_date
from DoiBong, CauThu, FootballMembers
where DoiBong.id = FootballMembers.id_club
and CauThu.id = FootballMembers.id_player
go
select DoiBong.name 'Ten Doi Bong', CauThu.fullname, FootballMembers.joined_date
from DoiBong left join FootballMembers on DoiBong.id = FootballMembers.id_club
left join CauThu on CauThu.id = FootballMembers.id_player
go
-- where, join, left join, right join, full outer join, ...
-- 10K, 100K, 1M, 2M, 100M -> ???
select * from LichSu
select * from LichSu
where giaidau = 'World Cup 1'
go
Ứng Dụng Học
Theo dõi cập nhật nội dung học trên Youtube & Facebook
Thông Tin Liên Hệ
Công Ty Cổ Phần Phát Triển Công Nghệ Gozic.
Website: https://ziczacvn.com
SĐT: 096 - 70 25 996
Email: ziczacgroup@gmail.com
Thiết kế webiste chuyên nghiệp
Thiết kế phần mềm quản trị
Thiết kế ứng dụng Android
Thiết kế ứng dụng IOS
Thiết kế Web App
Hỗ trợ Digital Marketing
Hỗ trợ quảng cáo Google Ads
Hỗ trợ quảng cáo Facebook Ads
Hỗ trợ SEO Website