编辑代码

CREATE DATABASE test;
use test;
  
-- 创建 abc1 表  
CREATE TABLE abc1 (    
    id INT AUTO_INCREMENT PRIMARY KEY,    
    studentID VARCHAR(20) UNIQUE NOT NULL,    
    name1 VARCHAR(50) NOT NULL,    
    gender CHAR(1),    
    chineseScore DECIMAL(5, 2),    
    mathScore DECIMAL(5, 2),    
    englishScore DECIMAL(5, 2)    
);  
-- 创建 abc2 表,并添加外键约束到 abc1 的 studentID  
CREATE TABLE abc2 (    
    recordID INT AUTO_INCREMENT PRIMARY KEY,    
    studentID VARCHAR(20),    
    score INT,   
    FOREIGN KEY (studentID) REFERENCES abc1(studentID)    
    ON DELETE CASCADE 
);  
-- 向 abc1 表中插入数据  
INSERT INTO abc1 (studentID, name1, gender, chineseScore, mathScore, englishScore)    
VALUES    
    ('S001', 'Alice', 'F', 85.5, 92.0, 88.0),    
    ('S002', 'Bob', 'M', 78.0, 85.0, 90.0),  
    ('S003', 'Charlie', 'M', 88.0, 95.0, 82.0),    
    ('S004', 'Diana', 'F', 92.5, 88.0, 94.0),    
    ('S005', 'Evan', 'M', 75.0, 80.0, 78.0),    
    ('S006', 'Fiona', 'F', 82.0, 90.0, 85.0),    
    ('S007', 'George', 'M', 90.0, 92.0, 90.0),  
    ('S008', 'Kara', 'F', 95.0, 91.0, 65.0);  
  
-- 向 abc2 表中插入数据  
INSERT INTO abc2 (studentID, score)    
VALUES    
    ('S001', 1),    
    ('S002', 2), 
    ('S003', 3),   
    ('S004', 4),   
    ('S005', 5),   
    ('S006', 6),   
    ('S007', 7),  
    ('S008', 8); 
-- 需求 1: 查询所有学生的姓名、性别和各科总分
-- select name1,gender,(chineseScore+ mathScore+englishScore) as 总分 from abc1;

-- 需求 2:查询平均分最高的科目及其平均分

--     SELECT 'chinese' AS 科目, AVG(chineseScore) AS 平均分  
--     FROM abc1  
--     UNION ALL  
--     SELECT 'math' AS 科目, AVG(mathScore) AS 平均分  
--     FROM abc1  
--     UNION ALL 
--     SELECT 'english' AS 科目, AVG(englishScore) AS 平均分  
--     FROM abc1  

-- ORDER BY 平均分 DESC  
-- limit 1
-- 需求 3: 查询 abc2 表中没有对应 abc1 表记录的学生ID
-- SELECT a.studentID  
-- FROM abc1 a  
-- LEFT JOIN abc2 b ON a.studentID = b.studentID  
-- WHERE b.recordID IS NOT NULL;

select * from abc2;
select *from abc1;
-- select round(chineseScore,0) from abc1;
-- select round(avg(chineseScore),2)from abc1

-- create view view111
-- as
-- select studentID,chineseScore,mathScore,englishScore from abc1
-- where gender='M';


-- select * from view111;
-- create index index111 on abc1(chineseScore);