编辑代码

CREATE DATABASE test;
USE test;
-- 创建表
CREATE TABLE S (
    SNO VARCHAR(10) PRIMARY KEY,
    SN VARCHAR(20) NOT NULL,
    SD VARCHAR(20),
    SB DATE,
    SEX VARCHAR(2) DEFAULT '男',
    CHECK (SEX IN ('男', '女'))
);

CREATE TABLE C (
    CNO VARCHAR(6) PRIMARY KEY,
    CN VARCHAR(10),
    PC VARCHAR(6),
    FOREIGN KEY (PC) REFERENCES C(CNO)
);

CREATE TABLE SC (
    SNO VARCHAR(10),
    CNO VARCHAR(6),
    GRADE DECIMAL(4,1),
    PRIMARY KEY (SNO, CNO),
    FOREIGN KEY (SNO) REFERENCES S(SNO),
    FOREIGN KEY (CNO) REFERENCES C(CNO),
    CHECK (GRADE BETWEEN 0 AND 100)
);

-- 插入数据
INSERT INTO C(CNO) VALUES('C01');
INSERT INTO C(CNO) VALUES('C03');
INSERT INTO C(CNO) VALUES('C05');
INSERT INTO C(CNO) VALUES('C06');
INSERT INTO C(CNO) VALUES('C07');
INSERT INTO C(CNO) VALUES('C11');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S01', '王玲', '计算机', '2000-06-30', '女');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S02', '李渊', '计算机', '1995-03-23', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S03', '罗军', '计算机', '1995-08-12', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S04', '赵泽', '计算机', '1997-09-12', '女');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S05', '许若', '自动化', '1999-06-27', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S06', '王仙华', '自动化', '1996-05-20', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S07', '朱祝', '自动化', '1998-07-10', '女');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S20', '李国民', '数学', '1999-12-31', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S21', '陈浩然', '计算机', '2000-10-15', '男');
INSERT INTO S(SNO, SN, SD, SB, SEX) VALUES('S31', '王浩', '计算机', '1999-10-15', '男');
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S04', 'C06', 90.0);
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S04', 'C11', 87.0);
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S05', 'C03', 79.5);
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S05', 'C05', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S05', 'C07', 90.0);
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S06', 'C03', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S07', 'C11', 88.0);
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S21', 'C05', NULL);
INSERT INTO SC(SNO, CNO, GRADE) VALUES('S31', 'C01', NULL);

-- 查询

select SD from S where SEX='男' group by SD having count(*)>2; 
-- having 给分组group by设置条件
select SNO,AVG(GRADE) from SC group by SNO HAVING AVG(GRADE)>=88 ;
select SNO,CNO from SC where GRADE is null;
select SNO,SN,SD from S where SD not in ('数学','计算机');
select * from C where CN like '\%实验%';
select  SN AS 姓名,SB AS 出生年月 from S where SB between '1999-01-01' and '1999-12-31';
select * from SC order by CNO asc,GRADE desc;
SELECT SNO AS 学号, TIMESTAMPDIFF(YEAR, SB, CURDATE())+1 AS 年龄 FROM S;
SELECT * FROM S;
SELECT DISTINCT SNO, SN, SD, SB, SEX FROM S;
SELECT * FROM S;
SELECT COUNT(*) FROM S;