建表
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);
mysql> create table SC(sno int not null, cno varchar(10) primary key not null, score int not null);
处理表
(1)修改student 表中年龄(sage)字段属性,数据类型由int 改变为smallint
mysql> alter table student modify sage smallint;
(2)为Course表中Cno 课程号字段设置索引,并查看索引
mysql> create index index_cno{新表名} on course(cno){唯一id};
(3)为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名为SC_INDEX
mysql> alter table sc add index sc_index(sno,cno);
(4)创建一视图 stu info,查询全体学生的姓名,性别,课程名,成绩
mysql> create view stu_info as select sname,ssex,cname,score
-> from student,sc,course
-> where student.sno=sc.sno and sc.cno=course.cno;
(5)删除所有索引
mysql> drop index index_cno on course;
mysql> drop index sc_index{索引名} on sc{表名};