mysql之视图的创建以及查询；

在这里插入图片描述
一：数据库及其表的创建：

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)

在这里插入图片描述

mysql> alter table student   modify sage smallint ;
Query OK, 0 rows affected (0.03 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 `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)

在这里插入图片描述

mysql> alter table sc add index sc_index(sno,cno);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

在这里插入图片描述

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)

在这里插入图片描述

mysql> drop index index_cno on course;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> drop index sc_index on sc;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0