代码:
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.01 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.
mysql> alter table student modify sage smallint ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.
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 `index_cno` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno | int | NO | PRI | NULL | |
| cname | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3.
mysql> alter table sc add index sc_index(sno,cno);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
4..
mysql> create view stu_info
-> as select student.sname ,student.ssex,course.cname,sc.score
-> from student
-> join sc on sc.sno=student.sno
-> join course on course.cno=sc.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