-- 刪除表
DROP TABLE IF EXISTS Student;
-- 創(chuàng)建表
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY,-- 學(xué)號(hào) 主鍵
Sname VARCHAR(20) NOT NULL,-- 姓名 非空且唯一
Sage INT NOT NULL,-- 年齡
Ssex VARCHAR(5) NOT NULL,-- 性別
Sdept VARCHAR(10) NOT NULL -- 所在系
)charset=utf8;

-- 刪除表
DROP TABLE IF EXISTS Course;
-- 創(chuàng)建表
CREATE TABLE Course(
Cno CHAR(5) PRIMARY KEY,-- 課程號(hào) 主鍵
Cname VARCHAR(20) NOT NULL ,-- 課程名 非空
Cpno VARCHAR(20)  NOT NULL,-- 先行課
Ccredit Double(5,2) NOT NULL-- 學(xué)分
)charset=utf8;

-- 刪除表
DROP TABLE IF EXISTS SC;
-- 創(chuàng)建表
CREATE TABLE SC(
Sno CHAR(9),-- 學(xué)號(hào) 主鍵
Cno CHAR(5),-- 課程號(hào) 主鍵
Grade INT NOT NULL, -- 成績(jī)
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
)charset=utf8;

INSERT INTO Student
(Sno,Sname,Sage,Ssex,Sdept) VALUES
('20211127','殷郊',22,'男','數(shù)學(xué)系'),
('20211128','姬發(fā)',20,'男','農(nóng)業(yè)系'),
('20211129','蘇妲己',18,'女','外語(yǔ)系'),
('20211130','崇應(yīng)彪',23,'男','體育系')

INSERT INTO Course
(Cno,Cname,Cpno,Ccredit) VALUES
('1001','數(shù)據(jù)庫(kù)原理及應(yīng)用','數(shù)據(jù)結(jié)構(gòu)',2),
('2002','LINUX操作系統(tǒng)與程序設(shè)計(jì)','操作系統(tǒng)',4),
('3003','離散數(shù)學(xué)','高等數(shù)學(xué)',2),
('4004','Java程序設(shè)計(jì)語(yǔ)言','c語(yǔ)言和c++語(yǔ)言',2),
('5005','網(wǎng)絡(luò)安全','計(jì)算機(jī)網(wǎng)絡(luò)',4)

INSERT INTO SC
(Sno,cno,Grade) VALUES
('20211127','1001',98),
('20211127','2002',96),
('20211127','3003',95),
('20211127','4004',90),
('20211127','5005',93),
('20211128','1001',98),
('20211128','2002',96),
('20211128','3003',95),
('20211128','4004',90),
('20211128','5005',93),
('20211129','1001',98),
('20211129','2002',96),
('20211129','3003',95),
('20211129','4004',90),
('20211129','5005',93),
('20211130','1001',98),
('20211130','2002',96),
('20211130','3003',95),
('20211130','4004',90),
('20211130','5005',93)

-- 查詢表所有
select * from Student;
select * from Course;
select * from SC;

-- 多表查詢，
-- 查詢表 Student和SC
SELECT SC.Sno AS 學(xué)生學(xué)號(hào) ,Student.Sname AS 學(xué)生姓名,Student.Ssex AS 學(xué)生性別 Student.Sdept AS 學(xué)生院系,SC.Cno AS 選擇課程號(hào),SC.Grade AS 課程成績(jī)
FROM SC,Student
WHERE SC.Sno=Student.Sno;
-- 查詢表 course 和 SC
SELECT SC.Sno AS 學(xué)生學(xué)號(hào) ,SC.Cno AS 選擇課程號(hào),Course.Cname AS 學(xué)生課程名, Course.cpno AS 學(xué)生先行課, Course.Ccredit AS 課程學(xué)分, SC.Grade AS 課程成績(jī)
FROM SC,Course
WHERE SC.Cno=Course.Cno;
-- 查詢表 course 和 SC 和 Student
SELECT SC.Sno AS 學(xué)生學(xué)號(hào) ,Student.Sname AS 學(xué)生姓名,Student.Ssex AS 學(xué)生性別, Student.Sdept AS 學(xué)生院系,
SC.Cno AS 選擇課程號(hào),Course.Cname AS 學(xué)生課程名, Course.cpno AS 學(xué)生先行課, Course.Ccredit AS 課程學(xué)分,SC.Grade AS 課程成績(jī)
FROM SC,Student,Course
WHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno AND SC.Sno= 20211127;

?
-- 修改語(yǔ)句 單個(gè)示例
update Student set Sname='姜子牙' where Sno='20211127';
-- 刪除語(yǔ)句，單個(gè)示例
-- 這里刪除不了是因?yàn)橥怄I約束的問(wèn)題
delete from Student where Sno='20211127';?