MySQL视图小练习

素材：
学生表：Student (Sno, Sname, Ssex , Sage,  Sdept) 学号，姓名，性别，年龄，所在系，Sno为主键。
课程表：Course  (Cno,   Cname,)课程号，课程名，Cno为主键。
学生选课表：SC (Sno,   Cno,     Score)学号，课程号，成绩，Sno,Cno为主键。
SC：

create table SC (
Sno int(4) unsigned comment '学号',
Cno int(4) unsigned comment '课程号',
Score int(3) unsigned COMMENT '成 绩',
primary key SC_INDEX(Sno asc,Cno asc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Course：

CREATE TABLE `Course` (
`Cno` int(4) unsigned NOT NULL DEFAULT '0' COMMENT '课程号',
`Cname` varchar(16) DEFAULT NULL COMMENT '课程名',
PRIMARY KEY (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Student：

INSERT INTO `Student` VALUES (1001,'张三','男',21,'计算机');
INSERT INTO `Student` VALUES (1002, '李四', '男', 20, '英语');
INSERT INTO `Student` VALUES (1003, '王五', '男', 22, '计算机');
INSERT INTO `Student` VALUES (1004, '赵六', '女', 20, '英语');
INSERT INTO `Student` VALUES (1005, '钱七', '男', 21, '计算机');
INSERT INTO `Student` VALUES (1006, '孙八', '男', 20, '计算机');
INSERT INTO `Student` VALUES (1007, '胡九', '女', 19, '计算机');
INSERT INTO `Student` VALUES (1008, '武十', '男', 20, '计信管');

SC：

INSERT INTO `SC` VALUES (1001, 2, 45);
INSERT INTO `SC` VALUES (1001, 3, 85);
INSERT INTO `SC` VALUES (1001, 4, 74);
INSERT INTO `SC` VALUES (1002, 1, 90);
INSERT INTO `SC` VALUES (1003, 1, 54);
INSERT INTO `SC` VALUES (1003, 2, 62);
INSERT INTO `SC` VALUES (1003, 3, 81);
INSERT INTO `SC` VALUES (1003, 4, 80);
INSERT INTO `SC` VALUES (1003, 5, 72);
INSERT INTO `SC` VALUES (1003, 6, 88);
INSERT INTO `SC` VALUES (1003, 7, 77);
INSERT INTO `SC` VALUES (1003, 8, 67);
INSERT INTO `SC` VALUES (1004, 1, 92);
INSERT INTO `SC` VALUES (1005, 1, 53);
INSERT INTO `SC` VALUES (1005, 4, 63);
INSERT INTO `SC` VALUES (1005, 7, 77);
INSERT INTO `SC` VALUES (1006, 1, 70);
INSERT INTO `SC` VALUES (1006, 2, 82);
INSERT INTO `SC` VALUES (1006, 4, 78);
INSERT INTO `SC` VALUES (1006, 6, 68);
INSERT INTO `SC` VALUES (1006, 8, 55);
INSERT INTO `SC` VALUES (1007, 2, 76);
INSERT INTO `SC` VALUES (1007, 4, 90);
INSERT INTO `SC` VALUES (1007, 6, 89);
INSERT INTO `SC` VALUES (1008, 1, 81);
INSERT INTO `SC` VALUES (1008, 2, 48);

Course：

INSERT INTO `Course` VALUES (1, '英语');
INSERT INTO `Course` VALUES (2, '高数');
INSERT INTO `Course` VALUES (3, '计算机');
INSERT INTO `Course` VALUES (4, '单片机');
INSERT INTO `Course` VALUES (5, 'java');
INSERT INTO `Course` VALUES (6, 'MySQL');
INSERT INTO `Course` VALUES (7, 'Linux');
INSERT INTO `Course` VALUES (8, 'C++');

1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。

create table Student(
Sno int(4) unsigned primary key comment '学号',
Sname varchar(16) unique comment '姓名',
Ssex enum('男','女') comment '性别',
Sage tinyint(3) unsigned comment '年龄',
Sdept varchar(20) comment '所在系'
)engine=innodb default charset=utf8;

将素材插入：

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

alter table Student modify Sage smallint;

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

create index SC_INDEX on SC(Sno,Cno);

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

create view stu_info                                                      
as 
select Sname,Ssex,Cname,Score from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno;