视图索引.作业

素材

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

  
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;

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;
  
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, '计信管');

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);

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++');

注释

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

##习题

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

mysql> create table student(
    -> Sno int(4) unsigned primary key,
    -> Sname varchar(16) unique,
    -> Ssex enum('男','女'),
    -> Ssage tinyint(3) unsigned,
    -> Sdept varchar(20) default '计算机'
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected (0.03 sec)

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

mysql> alter table student modify Ssage smallint;
Query OK, 0 rows affected (0.02 sec)

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

mysql> create unique index SC_INDEX on SC (sno asc,cno asc);              
Query OK, 0 rows affected (0.16 sec)

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

mysql> select stu.Sname,stu.Ssex,cou.Cname,sc.Score from Student stu
    -> inner join SC sc
    -> inner join Course cou
    -> on stu.Sno=sc.Sno
    -> and sc.Cno=cou.Cno;