题目:
解题过程:
建库:
mysql> create database mydb15_indexstu;
使用库:
mysql> use mydb15_indexstu;
创建student表:
mysql> create table Student(
Sno int primary key auto_increment,
Sname varchar(30) not nu
ll unique,
Ssex varchar(2) check(Ssex='男' or Ssex='女') not null,
Sage int not null,
Sdept varchar(10) default '计算机' not null);
查看建表情况:
mysql> desc Student;
+-------+-------------+------+-----+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-----------+----------------+
| Sno | int | NO | PRI | NULL | auto_increment |
| Sname | varchar(30) | NO | UNI | NULL | |
| Ssex | varchar(2) | NO | | NULL | |
| Sage | int | NO | | NULL | |
| Sdept | varchar(10) | NO | | 计算机 | |
+-------+-------------+------+-----+-----------+----------------+
创建course表:
mysql> create table Course(
Cno int primary key not null,
Cname varchar(20) not null);
查看建表情况:
mysql> desc Course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Cno | int | NO | PRI | NULL | |
| Cname | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
创建sc表:
mysql> create table SC(
Sno int not null,
Cno varchar(10) primary key not null,
Score int not null);
查看建表情况:
mysql> desc SC;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | int | NO | | NULL | |
| Cno | varchar(10) | NO | PRI | NULL | |
| Score | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
处理表:
1.修改student 表中年龄(sage)字段属性,数据类型由int 改变为smallint
mysql> alter table Student modify column Sage smallint;
mysql> desc Student;
+-------+-------------+------+-----+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-----------+----------------+
| Sno | int | NO | PRI | NULL | auto_increment |
| Sname | varchar(30) | NO | UNI | NULL | |
| Ssex | varchar(2) | NO | | NULL | |
| Sage | smallint | YES | | NULL | |
| Sdept | varchar(10) | NO | | 计算机 | |
+-------+-------------+------+-----+-----------+----------------+
2.为Course表中Cno 课程号字段设置索引,并查看索引
mysql> create index Cno_index on Course(Cno);
mysql> show index from Course\G
*************************** 1. row ***************************
Table: course
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Cno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: course
Non_unique: 1
Key_name: Cno_index
Seq_in_index: 1
Column_name: Cno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名为SC_INDEX
mysql> create index SC_INDEX on SC(Sno,Cno asc);
mysql> show create table SC\G
*************************** 1. row ***************************
Table: SC
Create Table: CREATE TABLE `sc` (
`Sno` int NOT NULL,
`Cno` varchar(10) NOT NULL,
`Score` int NOT NULL,
PRIMARY KEY (`Cno`),
KEY `SC_INDEX` (`Sno`,`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> show index from SC\G
*************************** 1. row ***************************
Table: sc
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Cno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: sc
Non_unique: 1
Key_name: SC_INDEX
Seq_in_index: 1
Column_name: Sno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: sc
Non_unique: 1
Key_name: SC_INDEX
Seq_in_index: 2
Column_name: Cno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
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;
mysql> desc stu_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sname | varchar(30) | NO | | NULL | |
| Ssex | varchar(2) | NO | | NULL | |
| Cname | varchar(20) | NO | | NULL | |
| Score | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
5.删除所有索引
mysql> drop index Cno_index on Course;
Query OK, 0 rows affected (0.01 sec)
mysql> drop index SC_INDEX on SC;
Query OK, 0 rows affected (0.01 sec)