MYSQL第四次作业

最新推荐文章于 2024-11-01 21:52:14 发布

t偷亲星星

最新推荐文章于 2024-11-01 21:52:14 发布

阅读量279

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_74053853/article/details/140632041

版权

1、新建数据库

mysql> create database mydb15_indexstu;
Query OK, 1 row affected (0.01 sec)
mysql> 
Database changed

2、新建表

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.06 sec)
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   |     | 计算机  |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> create table course(cno int primary key not null, cname varchar(20) not null);
Query OK, 0 rows affected (0.10 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> create table sc(sno int not null, cno varchar(10) primary key not null, score int not null);
Query OK, 0 rows affected (0.10 sec)

mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | int         | NO   |     | NULL    |       |
| cno   | varchar(10) | NO   | PRI | NULL    |       |
| score | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3、处理表

1、修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint
mysql> alter table student modify sage smallint;
Query OK, 0 rows affected (0.12 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)

2、为Course表中 Cno课程号字段设置索引,并查看索引
mysql> create index idx_cno on course (cno);
Query OK, 0 rows affected (0.09 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 | idx_cno  |            1 | cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.07 sec)


3、为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX
mysql> alter table sc add index SC_INDEX (sno, cno asc);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

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)

4、创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩
mysql> create view stuinfo1 as select sname,ssex,sc.cno,score  from student join sc on student.sno=sc.sno;
Query OK, 0 rows affected (0.08 sec)
mysql> create view stuinfo as select sname,ssex,cname,score
    -> from stuinfo1 join course on stuinfo1.cno=course.cno;
Query OK, 0 rows affected (0.08 sec)

mysql> desc stuinfo;
+-------+-------------+------+-----+---------+-------+
| 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    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5、删除所有索引
mysql> drop index SC_INDEX on sc;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> drop index idx_cno on course;
Query OK, 0 rows affected (0.09 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)