数据库第十一次作业-视图的应用

1.创建表

mysql>create table stu(
    -> s_id int(11) not null unique primary key,
    -> s_name varchar(20) not null,
    -> addr varchar(50) not null,
    -> tel varchar(50) not null);

create table sign(
    -> s_id int(11) not null unique primary key,
    -> s_name varchar(20) not null,
    -> s_sch varchar(50) not null,
    -> s_sign_sch varchar(50) not null);

mysql> create table stu_mark(
    ->  s_id int(11) not null unique primary key,
    -> s_name varchar(20) not null,
    -> mark int(11) not null);

 

 

 2.添加数据

 

mysql> insert into stu values
    -> (1,"ZhangPeng","Hebei",13889075861),
    -> (2,"LiXiao","Shandong",13953508223),
    -> (3,"HangYun","Shandong",13905350996);

mysql> insert into sign values
    -> (1,"ZhangPeng","High_School1","Peking_University"),
    -> (2,"LiXiao","High_School2","Peking_University"),
    -> (3,"HuangYun","High_School3","Tsinghua_University");

mysql> insert into stu_mark values
    -> (1,"ZhangPeng",730),
    -> (2,"LiXiao",725),
    -> (3,"HuangYun",736);

 3.查看数据

mysql> select * from stu;
+------+-----------+----------+-------------+
| s_id | s_name    | addr     | tel         |
+------+-----------+----------+-------------+
|    1 | ZhangPeng | Hebei    | 13889075861 |
|    2 | LiXiao    | Shandong | 13953508223 |
|    3 | HangYun   | Shandong | 13905350996 |
+------+-----------+----------+-------------+
3 rows in set (0.00 sec)

mysql> select * from sign;
+------+-----------+--------------+---------------------+
| s_id | s_name    | s_sch        | s_sign_sch          |
+------+-----------+--------------+---------------------+
|    1 | ZhangPeng | High_School1 | Peking_University   |
|    2 | LiXiao    | High_School2 | Peking_University   |
|    3 | HuangYun  | High_School3 | Tsinghua_University |
+------+-----------+--------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from stu_mark;
+------+-----------+------+
| s_id | s_name    | mark |
+------+-----------+------+
|    1 | ZhangPeng |  730 |
|    2 | LiXiao    |  725 |
|    3 | HuangYun  |  736 |
+------+-----------+------+
3 rows in set (0.00 sec)

 4.创建视图

 

mysql> create view beida as
    -> select a.s_id,a.s_name,c.mark,b.s_sign_sch from
    -> stu a inner join stu_mark c on  a.s_id = c.s_id
    -> inner join sign b on c.s_id = b.s_id where
    -> c.mark>=720 and
    -> b.s_sign_sch ="Peking_University";

 

 

mysql> create view qinghua as
    -> select a.s_id,a.s_name,c.mark,b.s_sign_sch from
    -> stu a inner join stu_mark c on  a.s_id = c.s_id
    -> inner join sign b on c.s_id = b.s_id where
    -> c.mark>=725 and
    -> b.s_sign_sch ="Tsinghua_University";

 

 

mysql> select * from beida;
+------+-----------+------+-------------------+
| s_id | s_name    | mark | s_sign_sch        |
+------+-----------+------+-------------------+
|    1 | ZhangPeng |  730 | Peking_University |
|    2 | LiXiao    |  725 | Peking_University |
+------+-----------+------+-------------------+
2 rows in set (0.01 sec)

mysql> select * from qinghua;
+------+---------+------+---------------------+
| s_id | s_name  | mark | s_sign_sch          |
+------+---------+------+---------------------+
|    3 | HangYun |  736 | Tsinghua_University |
+------+---------+------+---------------------+
1 row in set (0.00 sec)

 

 

mysql> update stu_mark set
    -> mark=mark-10
    -> where stu_mark.s_name ="HuangYun";

 

mysql> select * from stu_mark;
+------+-----------+------+
| s_id | s_name    | mark |
+------+-----------+------+
|    1 | ZhangPeng |  730 |
|    2 | LiXiao    |  725 |
|    3 | HuangYun  |  726 |
+------+-----------+------+
3 rows in set (0.00 sec)