数据库第四次作业

Destiny_12134

于 2024-07-23 17:04:41 发布

阅读量324

点赞数 3

文章标签： adb

本文链接：https://blog.csdn.net/Destiny_12134/article/details/140641016

版权

mysql> create database mydb15_indexstu;
Query OK, 1 row affected (0.01 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.04 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)

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

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   |     | 计算机  |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

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

mysql> show index from Course;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| course |          0 | PRIMARY          |            1 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| course |          1 | index_course_cno |            1 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

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

mysql> create view stu_info as
    -> select Student.Sname as '姓名'
    -> ,Student.Ssex as '性别',Course.Cname as '课程名',SC.score as '成绩'
    -> from Student join SC on Student.Sno=SC.Sno
    -> join Course on SC.Cno=Course.Cno;
Query OK, 0 rows affected (0.01 sec)

mysql> drop index index_course_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