By GokiSoft.com| 20:00 01/08/2020|

SQL Server/MySQL

[Share Code] Hướng dẫn tạo table + insert trong SQL Server

Tạo bảng trong CSDL => Bằng lệnh
Bài toán:
	Quản lý sinh viên => đặt database => C2002L
	- Quản lý thông tin sinh viên	=> Student
		- mã sinh viên (rollno) => nvarchar(20)
		- tên (fullname) => nvarchar (50)
		- ngày sinh (birthday) => date
		- quê quán (address) => nvarchar(150)
		- giới tính (gender) => nvarchar(10)
		- số điện thoại (phone_number) => nvarchar(20)
	- Quản lý lớp học => class
		- mã lớp học (class_id) => nvarchar(20)
		- tên lớp học (classname) => nvarchar(50)

	- Quản lý sinh viên trong 1 lớp học =>class_group
		- mã sinh viên (rollno) => khóa ngoài => liên kết với trường rollno trong bảng student => nvarchar(20)
		- mã lớp học (class_id) => khóa ngoài => liên kết với trường class_id trong bảng class => nvarchar(20)

-- Tao CSDL
create database C2002L
go

-- active CSDL C2002L
use C2002L
go

-- THIET KE CAU TRUC TABLE TRONG CSDL
-- Tao bang sinh vien : Student
create table student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50) not null,
	birthday date,
	gender nvarchar(16),
	phone_number nvarchar(16),
	address nvarchar(200)
)
go

-- Tao bang lop hoc : class
create table class (
	class_id nvarchar(20) primary key,
	class_name nvarchar(50) not null
)
go

-- Tao bang class_group
create table class_group (
	class_id nvarchar(20),
	rollno nvarchar(20),
	constraint pk_class_group primary key(class_id, rollno),
	constraint fk_rollno foreign key (rollno) references student (rollno),
	constraint fk_class_id foreign key (class_id) references class (class_id)
)
go

-- TEST DU LIEU
-- select *
select * from student
select * from class
select * from class_group

-- Bai toan them du lieu
-- Student
insert into student(rollno, fullname, birthday, gender, phone_number, address)
values
('R001', 'TRAN VAN A', '1999-06-26', 'NAM', '+84967025996', '285 DOI CAN, BA DINH, HA NOI, VIET NAM')

insert into student(rollno, fullname, birthday, gender, phone_number, address)
values
('R002', 'TRAN VAN B', '1999-08-26', 'NAM', '+84967025999', '285 DOI CAN, BA DINH, HA NOI, VIET NAM')

insert into student(rollno, fullname, birthday, gender, phone_number, address)
values
('R003', 'TRAN VAN C', '1999-07-26', 'NAM', '+84967025888', '285 DOI CAN, BA DINH, HA NOI, VIET NAM'),
('R004', 'TRAN VAN D', '1999-09-26', 'NAM', '+84967026868', '285 DOI CAN, BA DINH, HA NOI, VIET NAM')

-- Class
insert into class(class_id, class_name)
values
('C2002L', 'Lop Hoc C2002L'),
('C1803L', 'Lop Hoc C1803L')

-- class_group
insert into class_group(class_id, rollno)
values
('C2002L', 'R001')

-- Tim hieu select >>> basic
select * from student
select rollno, fullname from student

-- Hien thi thong tin sinh vien co rollno = R002
select * from student where rollno = 'R002'

-- Hien thi sinh vien sinh thang 7 va 8 nam 1999
select * from student where birthday >= '1999-07-01' and birthday <= '1999-08-31'
------ sap xep nam sinh theo thu tu tang dan
select * from student where birthday >= '1999-07-01' and birthday <= '1999-08-31' order by birthday asc
------ sap xep nam sinh theo thu tu giam dan
select * from student where birthday >= '1999-07-01' and birthday <= '1999-08-31' order by birthday desc

Phản hồi từ học viên

5

(Dựa trên đánh giá ngày hôm nay)

Tung Nguyen

2020-08-01 14:14:31

create database StudentDataManagement

--active

use StudentDataManagement

-- THIET KE CAU TRUC TABLE TRONG CSDL

-- Tao bang sinh vien : Student

create table Student(

rollNo nvarchar(20) primary key,

fullname nvarchar(50) not null,

age int,

address nvarchar(200),

email nvarchar(100),

phoneNumber nvarchar(16),

gender nvarchar(20)

)

-- Tao bang diem: Score

create table Score(

score int,

rollNo nvarchar(20),

subject_id nvarchar(20)

constraint pk_class_group primary key(rollNo, subject_id),

constraint fk_rollNo foreign key (rollNo) references Student (rollNo),

constraint fk_subject_id foreign key (subject_id) references MonHoc (subject_id)

)

--Tao bang mon hoc: MonHoc

create table MonHoc(

subject_id nvarchar(20) primary key,

subjectname nvarchar(20)

)

-- Tao bang lop hoc: Class

create table Class(

class_id nvarchar(20) primary key,

classname nvarchar(20),

rollNo nvarchar(20)

constraint fk_rolln0 foreign key (rollNo) references Student (rollNo)

)

--Tao bang lop hoc: Classroom

create table Classroom(

classroomname nvarchar(20),

classroom_id nvarchar(20) primary key,

numoftables int,

numofchairs int,

classroom_add nvarchar(20)

)

--Tao bang book gio day: Teaching-booking

create table Teaching_booking(

classroom_id nvarchar(20),

start datetime,

finish datetime,

class_id nvarchar(20)

constraint pk_teaching_booking primary key( classroom_id,class_id ),

constraint fk_classroom_id foreign key(classroom_id) references Classroom(classroom_id),

constraint fk_class_id foreign key(class_id) references Class(class_id),

)

-- Test DL

-- select

select * from Student

select * from Score

select * from MonHoc

select * from Class

select * from Classroom

select * from Teaching_booking

-- Bai toan them DL

-- Student