SQL for tracking enrollment of students (CRUD ← create, read(select), update, delete)
13-Sep-2021
我有心情的話會補上這個篇章出現的解釋, 我想先完成我的筆記才後來補上, 因為我打字太慢了, 不太想浪費時間。
先補上SQL的data type, 發現上課時都只固定用某一些, 想用其他eg date, 都不知道用哪一個比較好, 可能先跳過日期的data, 後來再補上吧。
For date: https://www.w3schools.com/sql/sql_dates.asp
For SQL:
如果我想加上column在table中的話, 可以用以下的syntax:
ALTER TABLE table_name
ADD column_name datatype;eg:
alter table course
add date DATE;多餘的資料: 可能double input
但database會盡量想避免多餘的data,如何做? → 加入primary key(令每一個data都獨一無二 , 不可以有重覆的data)
老師是用drop whole table再用之前儲存在notepad++的syntax去重新create一個table, 而不是直接insert column → why?
ALTER TABLE table_name
ADD column_name datatype primary key;這樣打sql就會不讓這一個特定的data重覆出現, 並會assign一個數字給每一行的data。
**一個table只能有一個Primary key**
老師的做法 (如果我的想法行不通的時候可參考):
drop table course;create table course(
name varchar(100) primary key,
teacher varchar(100),
price int,
quota int
);
Q: use teachingcentre; (<--database) 是會進去了database teachingcentre?
但course名都可能會有重覆, 目前最好的做法是加上一個類似id的number, 然後再把primary key附上去。
drop table course;create table course(
course_code int primary key auto_increment,
course_name varchar(100),
teacher varchar(100),
price int,
quota int
);上面的course code由system自動加上去會比較方便和可以避免不必要的錯誤, 加上auto_increment就可以有這一個效果。
以下再多做一個table去store student的資料
create table student(
sid int primary key auto_increment,
student_name varchar(100),
gender char(1),
phone varchar(20),
email varchar(255)
);插入資料到table中
insert into student(name, gender, phone, email)
values ("Peter", "M", "1234-5678", "peter@yahoo.com"),
("Mary", "F", "2345-6789", "mary@yahoo.com"),
("Joey", "F", "3456-7890", joey@yahoo.com");?不太肯定加不加的category →加一個當作是練習好了
create table area(
area_id int primary key auto_increment,
area_name varchar(255)
);insert into area(area_name)
values("web"),
("apps");
如何relate到course中?
1. 用alter去加
alter table course
add area_id int;
記得要把新的data insert到table中
這個沒有教, 不清楚對不對 →找到應該是用update而不是insert
(source:https://stackoverflow.com/questions/8334918/inserting-values-into-specific-rows-with-sql/8334972
/
https://www.w3schools.com/sql/sql_update.asp)
UPDATE course
SET area_id=1
WHERE course_name = 'frontend';UPDATE course
SET area_id=1
WHERE course_name = 'backend';UPDATE course
SET area_id=2
WHERE course_name = 'IOS';UPDATE course
SET area_id=2
WHERE course_name = 'android';
2.先刪後加
drop table course,create table course(
course_code int primary key auto_increment,
course_name varchar(100),
teacher varchar(100),
area_id int, <-- 我加了在這裡
price int,
quota int
);這個我忘了之前加了怎樣的item, 要回家再copy
Foreign Key
→ 要特定的column可以related to 某個primary key(一定要是primary key?)
→保障一定不會存到一些之前沒有save過的data
create table course(
course_code int primary key auto_increment,
course_name varchar(100),
teacher varchar(100),
area_id int,
price int,
quota int
foreign key(area_id) references category(area_id); <-- 我加了在這裡
);or
不肯定可否這樣做
如何drop
alter table course
add foreign key(area_id) references category(area_id);看來foreign key 的insert和insert多一個column到table的方法一樣
報讀的學生table:
在老師的例子中, 他本來是想用order, 可是order 是php的syntax之一, 所以轉用了Orders.
create table enroll(
invoice_id int primary key auto_increment,
course_id int,
student_id int,
enroll_date date,
foreign key(course_id) references course(course_id),
foreign key(student_id) references student(student_id)
);再插入資料
insert into enroll(course_id, student_id, enroll_date)
values (3, 1, "2021-8-21"),
(2, 2, "2021-8-22");顯示table資料
//顯示所有course 和 area table的資料
Select *
from course, area
where course.area_id = area.area_id;//簡化syntax, 用英文字c/a代表ta
Select c.*, a.area_id
from course c, area a
where c.course_id = a.area_id;//顯示入學的學生資料
Select *
from enroll e, course c, student s
where e.course_id = c.course_id
and e.student_id = s.student_id;//上面的資料過多, 想只顯示需要的column
Select e.enroll_date, s.name, c.name
where e.course_id = c.course_id
and e.student_id = s.student_id;
刪除一行data
Delete from course
where course_id = 4;
如果不加上where <--條件的話, 所有data都會被刪掉更新course data
Update course
set price = 50000
where course_id = 3;