数据库索引

建库

mysql> create database mydb_indexstu;
Query OK, 1 row affected (0.01 sec)

mysql> use mydb_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.02 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.02 sec)

处理表

修改Student表中Sage字段属性，数据类型改为smallint

mysql> alter table Student modify Sage smallint;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> create index indexcno on Course (Cno);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table Course \G
*************************** 1. row ***************************
       Table: Course
Create Table: CREATE TABLE `course` (
  `Cno` int NOT NULL,
  `Cname` varchar(20) NOT NULL,
  PRIMARY KEY (`Cno`),
  KEY `indexcno` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

为SC表建立按学号Sno和Cno组合的升序的主键索引，索引名SC_INDEX

mysql> create unique index SC_INDEX on SC(Sno asc, Cno asc);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> select Sname,Ssex,Cname,Score from Student join SC on Student.Sno=SC.Sno
    -> join Course on SC.Cno=Course.Cno;
Empty set (0.00 sec)

mysql> create view stu_info as select Student.Sname as '姓名',Student.Ssex as '性别',Course.Cname as '课程名',SC.Score a
s '成绩' from Student,Course,SC;
Query OK, 0 rows affected (0.01 sec)

删除所有索引

mysql> drop index indexcno on Course;
Query OK, 0 rows affected (0.02 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