MSQL基本增删改语句汇总练习

删除约束注意：

网上说是 ALTER TABLE 表名 DROP CONSTRAINT 约束名;

这里的CONSTRAINT 是指primary key,foreign key,unique,等实际的约束，删除的时候需要用对应的替换(注意:唯一性约束用 INDEX)

USE work;

/*创建选修数据表*/
DROP TABLE IF EXISTS sc;
CREATE TABLE sc(
    sno            CHAR(8)                       COMMENT '学号',
    cno            CHAR(3)                       COMMENT '课程号',
    grade   　　　　SMALLINT                      COMMENT '成绩' 
);


/*创建学生数据表*/
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    sno               CHAR(8)                     COMMENT'学号',
    sname             VARCHAR(20)                 COMMENT '姓名',
    sgender           CHAR(2)                     COMMENT '性别',
    sage              INT                         COMMENT '年龄',
    sdept             VARCHAR(30)             　　 COMMENT '所系',
    sedat             VARCHAR(30)                 COMMENT '入学日期'
);

/*创建课程数据表*/
DROP TABLE IF EXISTS course;
CREATE TABLE course(
    cno               CHAR(3)                      COMMENT '课程号',
    cname             VARCHAR(30)                  COMMENT '课程名',
    ccredit           SMALLINT                     COMMENT '学分'
);


/*插入数据*/
INSERT INTO student(sno,sname,sgender,sage,sdept,sedat)
                        VALUES
                                    ('20131001','王力','男',19,'计算机','2013/9/11'),
                                    ('20131002','张红','女',20,'计算机','2013/9/11'),
                                    ('20132001','孙国庆','男',18,'信息','2013/9/11');
INSERT INTO course(cno,cname,ccredit)
                        VALUES
                                    ('101','数据结构',4),
                                    ('102','数据库',3),
                                    ('103','C语言程序设计',6);
INSERT INTO sc(sno,cno,grade)
                        VALUES
                                    ('20131001','101','90'),
                                    ('20131001','102','76'),
                                    ('20131001','103','88'),
                                    ('20131002','101','56');

/*修改表结构*/
ALTER TABLE student ADD sphone char(11);
ALTER TABLE student MODIFY COLUMN sphone char(20);
ALTER TABLE student DROP COLUMN sphone;


/*对数据表添加修改约束*/
-- 添加主键约束
ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY(sno);
ALTER TABLE course  ADD CONSTRAINT pk_course  PRIMARY KEY(cno);
ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sno,cno);

-- 当主表中的数据删除，从表的对应行也删除
-- 添加外键约束
ALTER TABLE sc ADD CONSTRAINT fk_student_sc FOREIGN KEY(sno) REFERENCES student(sno) ON DELETE CASCADE;
ALTER TABLE sc ADD CONSTRAINT fk_course_sc    FOREIGN KEY(cno) REFERENCES course(cno) ON DELETE CASCADE;

-- 添加非空约束
ALTER TABLE student MODIFY COLUMN sname VARCHAR(20) NOT NULL;
ALTER TABLE course MODIFY COLUMN cname VARCHAR(30) NOT NULL;

-- 添加唯一约束
-- ALTER TABLE course ADD UNIQUE(cname);
ALTER TABLE course ADD CONSTRAINT u_cname UNIQUE(cname);

-- 添加CHECK约束
ALTER TABLE student ADD CONSTRAINT ck_sgender CHECK(sgender in('男','女'));
ALTER TABLE student ADD CONSTRAINT ck_sage CHECK(sage BETWEEN 15 AND 35);

/*删除刚刚添加的约束*/

-- 先删除外键约束才能删除主表的主键约束
ALTER TABLE sc DROP FOREIGN KEY fk_student_sc;
ALTER TABLE sc DROP FOREIGN KEY fk_course_sc;

ALTER TABLE student DROP PRIMARY KEY;
ALTER TABLE course  DROP PRIMARY KEY;
ALTER TABLE sc DROP PRIMARY KEY;

ALTER TABLE student MODIFY COLUMN sname VARCHAR(20) NULL;
ALTER TABLE course MODIFY COLUMN cname VARCHAR(30) NULL;

ALTER TABLE course DROP INDEX u_cname;
-- 不能删除，报错ALTER TABLE course DROP INDEX(cname);

-- mysql数据库不支持check约束，添加也无效，删除更不支持
/*ALTER TABLE student DROP CHECK ck_sgender;
ALTER TABLE student DROP CHECK ck_sage;*/



SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM sc;

--------------------------------DEMO2-------------------------------

USE work;

-- 查询全体学生的详细信息
SELECT * FROM student;

-- 检索全体学生的学号、姓名
SELECT sno,sname FROM student;

-- 线索已选课程的课程号,要求显示的课程号不重复
SELECT DISTINCT(cno) FROM sc;

-- 查询全体学生的姓名及出生日期
-- SELECT YEAR(CURDATE())- sage AS 出生年份 FROM student;

-- 查询成绩大于80分的学生的学号、课程号及成绩
SELECT sno,cno,grade FROM sc WHERE grade > 80;

-- 查询成绩介于70~80分的学生的学号、课程号及成绩
SELECT sno,cno,grade FROM sc WHERE grade BETWEEN 70 AND 80;

-- 查询选修了课程号为"101",且成绩大于80分的学生的学号
SELECT sno FROM sc WHERE cno = 101 AND grade > 80;

-- 查询"数据结构" 和 "C语言程序设计"课程的详细信息
SELECT * FROM course WHERE cname = '数据结构' OR cname = 'C语言程序设计';

-- 检索姓王的同学的详细信息(like，模糊查询,'%'代表任意0个或多个字符)
SELECT * FROM student WHERE sname like '王%';

-- 检索名字中第二个字是"力"或"历"的学生的详细信息('_'代表任意单个字符)
SELECT * FROM student WHERE sname like '_力%' OR '_历%';

-- 查询全体学生的姓名及其年龄,并按学生的年龄降序排列(升序ASC,降序DESC)
SELECT sname,sage FROM student ORDER BY sage DESC;

-- 查询学生总人数
SELECT COUNT(sno) FROM student;

-- 计算选修'101'号课程的学生的平均成绩、最高分和最低分
SELECT MAX(grade) AS 最高分,MIN(grade) AS 最低分,AVG(grade) AS 平均成绩 FROM sc WHERE cno = '101';

-- 汇总总分大于200分的学生的学号及总成绩
SELECT sno AS 学号,SUM(grade) AS 总成绩 FROM sc GROUP BY sno HAVING SUM(grade) > 200;

-- 统计各个课程的选课人数
SELECT cno AS 课程号,COUNT(*) AS 选课人数 FROM sc GROUP BY cno;

-- 检索多于2名学生选修的并以1结尾的课程号和平均成绩
-- 数据量不够，我自己改成了至少1人选修的以1开头的课程号和平均成绩，
SELECT cno AS 课程号, AVG(grade) AS 平均成绩 FROM sc WHERE cno like '1%' GROUP BY cno HAVING COUNT(cno) > 0;

 --------------------------------DEMO3-------------------------------

-- 检索所有学生的选课信息，包括学号、姓名、课程号、课程名和成绩
SELECT
    s.sno AS 学号 ,
    s.sname AS 姓名,
    c.cno AS 课程号,
    c.cname AS 课程名,
    sc.grade AS 成绩
FROM
    Student s,
    Course c,
    SC
WHERE
    s.sno = sc.sno
AND c.cno = sc.cno;

-- 查询选修了"C语言程序设计"的学生学号和姓名。
SELECT
    s.sno AS 学号,
    s.sname AS 姓名
FROM
    Student s,
    Course c,
    SC
WHERE
    s.sno = sc.sno
AND c.cno = sc.cno
AND c.cname = 'C语言程序设计';

-- 查询与"张红"在同一个系的学生学号、姓名和所属系。
SELECT
    sno AS 学号,
    sname AS 姓名,
    sdept AS 系
FROM
    student
WHERE
    sdept = (
        SELECT
            sdept
        FROM
            student
        WHERE
            sname = '张红'
    );
-- 查询其它系中比信息系的所有学生年龄都大的学生的学号、姓名和年龄
SELECT
    sno AS 学号,
    sname AS 姓名,
    sage AS 年龄
FROM
    student
WHERE
    sage > (
        SELECT
            max(sage)
        FROM
            student
        WHERE
            sdept = '信息'
    )
AND sdept <> '信息';

-- 查询比"王力"年纪大的男学生信息
SELECT
    *
FROM
    student
WHERE
    sage > (
        SELECT
            sage
        FROM
            student
        WHERE
            sname = '王力'
    )
AND sgender = '男';

-- 查询每个学生选修课程的学号和课程号和成绩，要求该选修课程的成绩超过他选修课程的平均成绩
SELECT
    sc.sno AS 学号,
    sc.cno AS 课程号,
    sc.grade AS 成绩
FROM
    (
        SELECT
            AVG(grade) avg,
            sno
        FROM
            SC
        GROUP BY
            sno
    ) av,
    sc
WHERE
    av.sno = sc.sno
AND grade >= av.avg;

-- 检索最高分与最低分之差大于10分的学生的学号、最高分和最低分
SELECT DISTINCT
    sc.sno AS 学号,
    m.max AS 最高分,
    m.min AS 最低分
FROM
    (
        SELECT
            MIN(grade) min,
            MAX(grade) max,
            sno
        FROM
            SC
        GROUP BY
            sno
    ) m,
    sc
WHERE
    sc.sno = m.sno
AND (m.max - m.min) > 10;

-- 检索选修2门以上课程的学生的学号和总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来.
SELECT
    sc.sno AS 学号,
    SUM(grade) AS 总成绩
FROM
    (
        SELECT
            sno,
            count(cno) c
        FROM
            sc
        GROUP BY
            sno
        HAVING
            COUNT(*) > 2
    ) cn,
    sc
WHERE
    cn.sno = sc.sno
AND grade >= 60
GROUP BY
    sc.sno
ORDER BY
    总成绩 DESC;

-- 查询选修了全部课程的学生学号和姓名
-- 第一步:查询出成绩表中的学号和对应选课数量 SELECT sno,COUNT(cno) FROM sc GROUP BY sno;
-- 第二步:查询出课程表中所有课程的数量 SELECT count(cno) FROM course;
-- 查询到 选课数量 和 所有课程的数量 相等(代表选修了全部课程) 的学生的学号，和在student表中与该学号相同学号的 学生姓名就行了
SELECT
    a.sno AS 学号,
    student.sname AS 姓名
FROM
    (
        SELECT
            sno,
            COUNT(cno) count
        FROM
            sc
        GROUP BY
            sno
    ) a,
    student
WHERE
    a.count = (SELECT COUNT(cno) FROM course)
AND a.sno = student.sno;

-- 查询至少选修了学生"20132001"选修的全部课程的学生的学号和姓名
SELECT DISTINCT
    scx.sno AS 学号,
    sname AS 姓名
FROM
    sc scx,
    student
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            sc scy
        WHERE
            scy.sno = '20132001'
        AND NOT EXISTS (
            SELECT
                *
            FROM
                sc scz
            WHERE
                scz.sno = scx.sno
            AND scz.cno = scy.cno
        )
    )
AND scx.sno = student.sno;

 视图与索引

-- 创建视图v1,该视图包含计算机系的学生信息
CREATE VIEW v1(学号,姓名,性别,年龄,系别,入学日期) AS SELECT * FROM student WHERE sdept = '计算机';
-- 创建视图v2,该视图包含成绩及格同学的学号、所以选课程课课程号和成绩
CREATE VIEW v2(学号,课程号,成绩) AS SELECT * FROM sc WHERE grade >= 60;
-- 创建视图v3,该视图包含学号、姓名和平均成绩
CREATE VIEW v3(学号,姓名,平均成绩) AS SELECT student.sno,student.sname,AVG(sc.grade) FROM student,sc WHERE student.sno = sc.sno GROUP BY student.sno;
-- 利用v1视图定义一个包含计算机系学生的学号及其所学课程的课程号和成绩的视图
CREATE VIEW v4(学号,课程号,成绩) AS SELECT v1.`学号`,sc.cno,sc.grade FROM v1,sc WHERE sc.sno = v1.`学号`;
-- 查询计算机系中年龄大于19岁的学生信息
SELECT * FROM v1 WHERE v1.`年龄` > 19;
-- 查询平均成绩在85分以上的学生的学号、姓名和平均成绩
SELECT * FROM v3 WHERE v3.`平均成绩` > 85;
-- 利用v1视图向学生数据表中插入一条记录('20121011','李余力','男',17,'2013/9/11')
INSERT INTO v1(学号,姓名,性别,年龄,入学日期) VALUES('20121011','李余力','男',17,'2013/9/11');
-- 对视图v3进行更新操作，将平均成绩提高5分。观察结果，并分析原因
UPDATE v3 SET 平均成绩 = 平均成绩 + 5; -- 报错:[Err] 1288 - The target table v3 of the UPDATE is not updatable
                                                                             -- 原因:若视图导出时包含有分组和聚合操作，则不允许对这个视图执行更新操作。
-- 为sc表的sno和cno列建普通索引
ALTER TABLE sc ADD INDEX index_sno_cno(sno,cno);
-- 为student表的sno列建聚簇索引

-- 为course表的cname列建唯一索引
CREATE UNIQUE INDEX UK_course_cname ON course(cname);