Mysql 视图创建-查询

最新推荐文章于 2024-08-31 23:40:08 发布

十六.16

最新推荐文章于 2024-08-31 23:40:08 发布

阅读量162

点赞数 2

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_79116064/article/details/140743822

版权

代码：

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

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)

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