编辑代码

CREATE DATABASE JXGL;
USE JXGL;
CREATE TABLE STUDENT (
    no CHAR(10) NOT NULL PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2) NOT NULL CHECK (Ssex IN ('男', '女')),
    Sage INT CHECK (Sage BETWEEN 20 AND 30),
    Sdept NVARCHAR(20)
);
CREATE TABLE COURSE (
    Cno INT NOT NULL PRIMARY KEY,
    Cname CHAR(20) NOT NULL,
    Cpno INT,
    Ccredit INT
);
CREATE TABLE SC (
    Sno CHAR(10) NOT NULL,
    Cno INT NOT NULL,
    Grade INT,
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES STUDENT(no),
    FOREIGN KEY (Cno) REFERENCES COURSE(Cno)
);
INSERT INTO STUDENT (no, Sname, Ssex, Sage, Sdept) VALUES
('S001', '张三', '男', 22, '计算机'),
('S002', '李四', '女', 21, '信息工程'),
('S003', '刘七', '男', 24, '软件工程'),
('S004', '陈八', '女', 26, '网络工程'),
('S005', '杨九', '男', 28, '电子信息');
INSERT INTO COURSE (Cno, Cname, Cpno, Ccredit) VALUES
(101, '数据库', NULL, 3),
(102, '操作系统', NULL, 4),
(103, '数据结构', 101, 4),
(104, '计算机网络', NULL, 3),
(105, '人工智能', NULL, 4);
INSERT INTO SC (Sno, Cno, Grade) VALUES
('S001', 101, 90),
('S001', 102, 85),
('S002', 101, 95),
('S003', 103, 87),
('S003', 104, 91),
('S004', 102, 82),
('S004', 105, 78),
('S005', 101, 89),
('S005', 104, 93);    
-- 查询 STUDENT 表中的数据
SELECT * FROM STUDENT;

-- 查询 COURSE 表中的数据
SELECT * FROM COURSE;

-- 查询 SC 表中的数据
SELECT * FROM SC;