课本附录B代码

创建表

-- 创建数据库
CREATE DATABASE IF NOT EXISTS teachsys;
USE teachsys;

-- 创建专业表
CREATE TABLE speciality (
    specialityid CHAR(6) PRIMARY KEY,
    specname VARCHAR(50)
);

-- 创建学生表
CREATE TABLE student (
    studentid CHAR(6) PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sex CHAR(2) NOT NULL DEFAULT '男',
    birthday DATE NOT NULL,
    tc TINYINT,
    specialityid CHAR(6) NOT NULL
);

-- 创建课程表
CREATE TABLE course (
    courseid CHAR(4) PRIMARY KEY,
    cname VARCHAR(50) NOT NULL,
    credit TINYINT
);

-- 创建成绩表
CREATE TABLE score (
    studentid CHAR(6),
    courseid CHAR(4),
    grade TINYINT,
    PRIMARY KEY (studentid, courseid)
);

-- 创建教师表
CREATE TABLE teacher (
    teacherid CHAR(6) PRIMARY KEY,
    tname VARCHAR(20) NOT NULL,
    tsex CHAR(2) NOT NULL DEFAULT '男',
    birthday DATE NOT NULL,
    title VARCHAR(20),
    school VARCHAR(30)
);

-- 创建讲课表
CREATE TABLE lecture (
    teacherid CHAR(6),
    courseid CHAR(4),
    location VARCHAR(20),
    PRIMARY KEY (teacherid, courseid)
);

附B数据插入

-- 插入专业数据
INSERT INTO speciality (specialityid, specname) VALUES
('080701', '电子信息工程'),
('080702', '电子科学与技术'),
('080703', '通信工程'),
('080901', '计算机科学与技术'),
('080902', '软件工程'),
('080903', '网络工程');

-- 插入学生数据
INSERT INTO student (studentid, sname, sex, birthday, tc, specialityid) VALUES
('222001', '唐志浩', '男', '2002-06-17', 52, '080902'),
('222002', '郑兰', '女', '2001-09-23', 50, '080902'),
('222003', '齐雨佳', '女', '2002-03-09', 52, '080902'),
('228001', '曾明', '男', '2002-02-24', 52, '080703'),
('228002', '向勇', '男', '2001-12-14', 50, '080703'),
('228004', '许慧芳', '女', '2001-08-05', 48, '080703');

-- 插入课程数据
INSERT INTO course (courseid, cname, credit) VALUES
('1007', '操作系统', 3),
('1014', '数据库系统', 4),
('1201', '英语', 5),
('4008', '通信原理', 4),
('8001', '高等数学', 5);

-- 插入成绩数据
INSERT INTO score (studentid, courseid, grade) VALUES
('222001', '1014', 94),
('222002', '1014', 85),
('222003', '1014', 93),
('222001', '1201', 95),
('222002', '1201', 84),
('228001', '1201', 91),
('228002', '1201', 92),
('228004', '1201', null),
('228001', '4008', 91),
('228002', '4008', 88),
('228004', '4008', 76),
('222001', '8001', 93),
('222002', '8001', 87),
('228001', '8001', 94),
('228002', '8001', 92),
('228004', '8001', 78);

-- 更新指定行的grade为NULL
UPDATE teachsys.score
SET grade = NULL
WHERE studentid = 228004 AND courseid = 1201;


-- 插入教师数据
INSERT INTO teacher (teacherid, tname, tsex, birthday, title, school) VALUES
('100007', '何思敏', '男', '1976-11-04', '教授', '计算机学院'),
('100020', '万丽', '女', '1980-06-21', '教授', '计算机学院'),
('120031', '陶振雅', '女', '1984-06-19', '副教授', '外国语学院'),
('400015', '蔡桂华', '女', '1999-12-14', '讲师', '通信学院'),
('800028', '郭正', '男', '1996-09-07', '副教授', '数学学院');

-- 插入讲课数据
INSERT INTO lecture (teacherid, courseid, location) VALUES
('100007', '1014', '3-206'),
('120031', '1201', '1-319'),
('400015', '4008', '6-103'),
('800028', '8001', '5-211');

附录C代码

创建表

-- 创建数据库
DROP DATABASE IF EXISTS shoppm;
CREATE DATABASE IF NOT EXISTS shoppm;
USE shoppm;

-- 1. 部门表 DeptInfo
CREATE TABLE DeptInfo (
    DeptNo VARCHAR(4) NOT NULL PRIMARY KEY,      -- 部门号
    DeptName VARCHAR(20) NOT NULL                -- 部门名称
);

-- 2. 员工表 EmpInfo
CREATE TABLE EmpInfo (
    EmplNo VARCHAR(4) NOT NULL PRIMARY KEY,       -- 员工号（已更改为 EmplNo）
    EmpName VARCHAR(3) NOT NULL,                  -- 姓名
    Sex VARCHAR(2) NOT NULL DEFAULT '男',         -- 性别
    Birthday DATE NOT NULL,                       -- 出生日期
    Native VARCHAR(12) NOT NULL,                  -- 籍贯
    Wages DECIMAL(8,2) NOT NULL,                  -- 工资
    DeptNo VARCHAR(4) NOT NULL                    -- 部门号
);

-- 3. 商品表 GoodsInfo
CREATE TABLE GoodsInfo (
    GoodsNo VARCHAR(4) NOT NULL PRIMARY KEY,     -- 商品号
    GoodsName VARCHAR(50) NOT NULL,              -- 商品名称
    GoodsType VARCHAR(20) NOT NULL,              -- 商品类型
    Price DECIMAL(8,2) NOT NULL,                 -- 单价
    Stock INT NOT NULL                           -- 库存量
);

-- 4. 订单表 OrderInfo
CREATE TABLE OrderInfo (
    OrderNo VARCHAR(10) NOT NULL PRIMARY KEY,    -- 订单号
    EmplNo VARCHAR(4),                           -- 员工号（已更改为 EmplNo）
    CustNo VARCHAR(4) NOT NULL,                  -- 客户号
    SaleDate DATE NOT NULL,                      -- 销售日期
    Cost DECIMAL(9,2) NOT NULL                   -- 总金额
);

-- 5. 订单明细表 DetailInfo
CREATE TABLE DetailInfo (
    OrderNo VARCHAR(10) NOT NULL,                -- 订单号
    GoodsNo VARCHAR(4) NOT NULL,                 -- 商品号
    Sunitprice DECIMAL(8,2) NOT NULL,            -- 销售单价
    Quantity INT NOT NULL,                       -- 数量
    Total DECIMAL(9,2) NOT NULL,                 -- 总价
    Discount FLOAT NOT NULL DEFAULT 0.1,         -- 折扣率
    Disctotal DECIMAL(9,2) NOT NULL,             -- 折扣总价
    PRIMARY KEY (OrderNo, GoodsNo)
);

附C数据插入

-- 插入部门数据
INSERT INTO DeptInfo (DeptNo, DeptName) VALUES
('D001', '销售部'),
('D002', '人事部'),
('D003', '财务部'),
('D004', '技术部'),
('D005', '管理部');

-- 插入员工数据（注意：EmplNo 替换了原来的 EmpNo）
INSERT INTO EmpInfo (EmplNo, EmpName, Sex, Birthday, Native, Wages, DeptNo) VALUES
('E001', '徐文涛', '男', '1981-05-14', '北京', 4000.00, 'D001'),
('E002', '岳晓芳', '女', '1988-06-23', '上海', 5000.00, 'D003'),
('E003', '杜林', '女', '1995-12-07', 'NLLL', 4000.00, 'D004'),
('E004', '唐海明', '女', '1995-06-25', '上海', 7000.00, 'D005'),
('E005', '于芳', '女', '1995-06-19', '四川', 3000.00, 'D002'),
('E006', '康明', '男', '1989-01-09', '北京', 4500.00, 'D001');

-- 插入商品数据
INSERT INTO GoodsInfo (GoodsNo, GoodsName, GoodsType, Price, Stock) VALUES
('I001', 'Microsoft Surface Pro 7', '笔记本电脑', 6283.00, 6),
('I002', 'DELL XPS13-7390', '笔记本电脑', 8877.00, 6),
('I003', 'Apple iPad Pro', '平板电脑', 7029.00, 4),
('I004', 'DELL PowerEdgeT140', '服务器', 8899.00, 4),
('I005', 'EPSON L565', '打印机', 1959.00, 9);

-- 插入订单数据（使用 CustNo 和 SaleDate，并且 EmplNo 替换了原来的 EmpNo）
INSERT INTO OrderInfo (OrderNo, EmplNo, CustNo, SaleDate, Cost) VALUES
('S00001', 'E001', 'C001', '2024-01-20', 21657.40),
('S00002', 'E005', 'C002', '2024-01-20', 56620.10),
('S00003', 'E005', 'C003', '2024-01-20', 15978.60),
('S00004', NULL, 'COM', '2024-01-20', 15978.60);

-- 插入订单明细数据
INSERT INTO DetailInfo (OrderNo, GoodsNo, Sunitprice, Quantity, Total, Discount, Disctotal) VALUES
('S00001', 'I001', 6283.00, 1, 6283.00, 0.1, 5654.70),
('S00001', 'I004', 8899.00, 2, 17798.00, 0.1, 16018.20),
('S00002', 'I002', 8877.00, 3, 26631.00, 0.1, 23967.90),
('S00002', 'I003', 7029.00, 2, 14058.00, 0.1, 12652.20),
('S00003', 'I002', 8877.00, 2, 17754.00, 0.1, 15978.60),
('S00004', 'I002', 8877.00, 2, 17754.00, 0.1, 15978.60);