MySQL索引视图的基本练习

1.创建表

学生表：Student (Sno, Sname, Ssex , Sage, Sdept)学号，姓名，性别，年龄，所在系 Sno为主键
课程表：Course (Cno, Cname,)课程号，课程名 Cno为主键
学生选课表：SC (Sno, Cno, Score)学号，课程号，成绩 Sno,Cno为主键
    CREATE TABLE Student(
    Sno INT(10) NOT NULL,
    Sname VARCHAR(20) UNICODE NOT NULL,
    Ssex VARCHAR(20) UNIQUE NOT NULL,
    Sage INT(10) NOT NULL,
    Sdept VARCHAR(20) DEFAULT '计算机',
    PRIMARY KEY(Sno),
    CHECK(Ssex IN ('男','女'))
    )ENGINE=INNODB DEFAULT CHARSET=utf8;

    ​
    CREATE TABLE Course(
    Cno INT(10) NOT NULL,
    Cname VARCHAR(20) NOT NULL,
    PRIMARY KEY(Cno)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
     
    CREATE TABLE SC(
    Sno INT(10) NOT NULL,
    Cno INT(10) NOT NULL,
    Score INT(10) NOT NULL,
    PRIMARY KEY(Sno,Cno)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
   

2. 插入数据

INSERT INTO student (Sno,Sname,Ssex,Sage,Sdept)
VALUES(101,'小白','男',20,' `student`'),(102,'小蔡','女',21,'体育');

INSERT INTO course (Cno,Cname)VALUES(1001,'机组');
INSERT INTO course (Cno,Cname)VALUES(1002,'网原');

INSERT INTO sc (Sno,Cno,Score)VALUES(101,'1001',90);
INSERT INTO sc (Sno,Cno,Score)VALUES(101,'1002',20);
INSERT INTO sc (Sno,Cno,Score)VALUES(102,'1001',50);
INSERT INTO sc (Sno,Cno,Score)VALUES(102,'1002',80);

 

 3.修改student表中年龄（age）字段属性，数据类型由int改变为smallint。

 

4.为Sc表建立按学号(sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 

5.创建一视图stu_info,查询全体学生的姓名，性别，课程名，成绩。

CREATE VIEW stu_info (姓名,性别,课程名,成绩)
AS SELECTStudent.`Sname`,Student.`Ssex`,Course.`Cname`,SC.`Score`
FROM Student,Course,SC
WHERE Student.`Sno`=SC.`Sno` AND Course.`Cno`=SC.`Cno`;