数据库题目四(学生表)

一、题目

二、运算过程

新建数据库

mysql> create database mydb15_indexstu;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb15_indexstu;
Database changed

新建表

mysql> create table Student(
-> Sno int primary key auto_increment,
-> Sname varchar(30) not null unique,
-> Ssex varchar(2) check (Ssex=‘男’ or Ssex=‘女’) not null,
-> Sage int not null,
-> Sdept varchar(10) default ‘计算机’ not null);
Query OK, 0 rows affected (0.02 sec)

mysql> create table Course(
-> Cno int primary key not null,
-> Cname varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> create table SC(
-> Sno int not null,
-> Cno varchar(10) primary key not null,
-> Score int not null);
Query OK, 0 rows affected (0.01 sec)

处理表

1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint
mysql> alter table student modify Sage smallint ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.为Course表中Cno 课程号字段设置索引,并查看索引
mysql> drop index SC_INDEX on Course;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index index_cno on Course(Cno);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX
mysql> alter table SC drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table SC add constraint pk_sc primary key(Sno,Cno);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index sc_index on SC(Sno asc,Cno asc);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩
mysql> create view stu_info as
-> select s.Sname, s.Ssex, c.Cname, sc.Score
-> from student s
-> join sc ON s.Sno = sc.Sno
-> join course c on sc.Cno = c.Cno;
Query OK, 0 rows affected (0.00 sec)
5.删除所有索引
mysql> drop index index_cno on Course;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index SC_INDEX on SC;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

三、运算结果

新建数据库

新建表

处理表

1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint

2.为Course表中Cno 课程号字段设置索引,并查看索引

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

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

5.删除所有索引