mysql学视图的收获怎么写_Mysql练习-视图的操作

一、练习要求

① 创建学生表stu，插入3条记录

② 创建报名表sign，插入3条记录

③ 创建成绩表stu_mark，插入3条记录

④ 创建考上Peking University的学生的视图

⑤ 创建考上Tsinghua University的学生的视图

⑥ XiaoTian的成绩在录入的时候因录入错误多录了50分，对其成绩进行更正

⑦ 查看更新后视图和表的情况

⑧ 查看视图的创建信息

⑨ 删除创建的视图

stu表结构

字段名

数据类型

主键

外键

非空

唯一

自增

s_id

INT(11)

是

否

是

是

否

s_name

VARCHAR(20)

否

否

是

否

否

addr

VARCHAR(50)

否

否

是

否

否

tel

VARCHAR(50)

否

否

是

否

否

stu表内容

s_id

s_name

addr

tel

1

XiaoWang

Henan

0371-12345678

2

XiaoLi

Hebei

13889072345

3

ZhengGu

Henan

0371-1234570

sign表结构

字段名

数据类型

主键

外键

非空

唯一

自增

s_id

INT(11)

是

否

是

是

否

s_name

VARCHAR(20)

否

否

是

否

否

s_sch

VARCHAR(50)

否

否

是

否

否

s_sign_sch

VARCHAR(50)

否

否

是

否

否

sign表内容

s_id

s_name

s_sch

s_sign_sch

1

XiaoWang

Middle School1

Peking University

2

XiaoLi

Middle School2

Tsinghua University

3

ZhengGu

Middle School3

Tsinghua University

stu_mark表结构

字段名

数据类型

主键

外键

非空

唯一

自增

s_id

INT(11)

是

否

是

是

否

s_name

VARCHAR(20)

否

否

是

否

否

mark

INT(11)

否

否

是

否

否

stu_mark表内容

s_id

s_name

mark

1

XiaoWang

80

2

XiaoLi

71

3

ZhengGu

70

二、操作记录

创建学生表stu，插入3条记录

mysql> CREATE TABLE stu

-> (

-> s_id INT PRIMARY KEY,

-> s_name VARCHAR(20),

-> addr VARCHAR(50),

-> tel VARCHAR(50)

-> );

Query OK, 0 rows affected (0.28 sec)

mysql>

mysql> INSERT INTO stu

-> VALUES(1,'XiaoWang','Henan','0371-12345678'),

-> (2,'XiaoLi','Hebei','13889072345'),

-> (3,'XiaoTian','Henan','0371-12345670');

Query OK, 3 rows affected (0.08 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql>

创建报名表sign，插入3条记录

mysql> CREATE TABLE sign

-> (

-> s_id INT PRIMARY KEY,

-> s_name VARCHAR(20),

-> s_sch VARCHAR(50),

-> s_sign_sch VARCHAR(50)

-> );

Query OK, 0 rows affected (0.32 sec)

mysql>

mysql> INSERT INTO sign

-> VALUES(1,'XiaoWang','Middle School1','Peking University'),

-> (2,'XiaoLi','Middle School2','Tsinghua University'),

-> (3,'XiaoTian','Middle School3','Tsinghua University');

Query OK, 3 rows affected (0.07 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql>

创建成绩表stu_mark，插入3条记录

mysql> CREATE TABLE stu_mark (s_id INT PRIMARY KEY ,s_name VARCHAR(20) ,mark int );

Query OK, 0 rows affected (0.24 sec)

mysql>

mysql> INSERT INTO stu_mark VALUES(1,'XiaoWang',80),(2,'XiaoLi',71),(3,'XiaoTian',70);

Query OK, 3 rows affected (0.11 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql>

创建考上Peking University的学生的视图

mysql> CREATE VIEW beida (id,name,mark,sch)

-> AS SELECT stu_mark.s_id,stu_mark.s_name,stu_mark.mark, sign.s_sign_sch

-> FROM stu_mark ,sign

-> WHERE stu_mark.s_id=sign.s_id AND stu_mark.mark>=41 AND sign.s_sign_sch='Peking University';

Query OK, 0 rows affected (0.06 sec)

mysql>

创建考上Tsinghua University的学生的视图

mysql> CREATE VIEW qinghua (id,name,mark,sch)

-> AS SELECT stu_mark.s_id, stu_mark.s_name, stu_mark.mark, sign.s_sign_sch

-> FROM stu_mark ,sign

-> WHERE stu_mark.s_id=sign.s_id AND stu_mark.mark>=40 AND sign.s_sign_sch='Tsinghua University';

Query OK, 0 rows affected (0.05 sec)

mysql>

XiaoTian的成绩在录入的时候因录入错误多录了50分，对其成绩进行更正

mysql> UPDATE stu_mark SET mark = mark-50 WHERE stu_mark.s_name ='XiaoTian';

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql>

查看更新后视图和表的情况

mysql> SELECT * FROM stu_mark;

+------+----------+------+

| s_id | s_name | mark |

+------+----------+------+

| 1 | XiaoWang | 80 |

| 2 | XiaoLi | 71 |

| 3 | XiaoTian | 20 |

+------+----------+------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM qinghua;

+----+--------+------+---------------------+

| id | name | mark | sch |

+----+--------+------+---------------------+

| 2 | XiaoLi | 71 | Tsinghua University |

+----+--------+------+---------------------+

1 row in set (0.00 sec)

mysql> SELECT * FROM beida;

+----+----------+------+-------------------+

| id | name | mark | sch |

+----+----------+------+-------------------+

| 1 | XiaoWang | 80 | Peking University |

+----+----------+------+-------------------+

1 row in set (0.00 sec)

mysql>

查看视图的创建信息

mysql> SELECT * FROM information_schema.views where table_name = 'beida'\G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: index_test

TABLE_NAME: beida

VIEW_DEFINITION: select `index_test`.`stu_mark`.`s_id` AS `id`,`index_test`.`stu_mark`.`s_name` AS `name`,`index_test`.`stu_mark`.`mark` AS `mark`,`index_test`.`sign`.`s_sign_sch` AS `sch` from `index_test`.`stu_mark` join `index_test`.`sign` where ((`index_test`.`stu_mark`.`s_id` = `index_test`.`sign`.`s_id`) and (`index_test`.`stu_mark`.`mark` >= 41) and (`index_test`.`sign`.`s_sign_sch` = 'Peking University'))

CHECK_OPTION: NONE

IS_UPDATABLE: YES

DEFINER: root@localhost

SECURITY_TYPE: DEFINER

CHARACTER_SET_CLIENT: gbk

COLLATION_CONNECTION: gbk_chinese_ci

*************************** 2. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: view

TABLE_NAME: beida

VIEW_DEFINITION: select `view`.`stu_mark`.`s_id` AS `id`,`view`.`stu_mark`.`s_name` AS `name`,`view`.`stu_mark`.`mark` AS `mark`,`view`.`sign`.`s_sign_sch` AS `sch` from `view`.`stu_mark` join `view`.`sign` where ((`view`.`sign`.`s_id` = `view`.`stu_mark`.`s_id`) and (`view`.`sign`.`s_sign_sch` = 'Peking University') and (`view`.`stu_mark`.`mark` >= 41))

CHECK_OPTION: NONE

IS_UPDATABLE: YES

DEFINER: root@localhost

SECURITY_TYPE: DEFINER

CHARACTER_SET_CLIENT: gbk

COLLATION_CONNECTION: gbk_chinese_ci

2 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.views where table_name = 'qinghua'\G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: index_test

TABLE_NAME: qinghua

VIEW_DEFINITION: select `index_test`.`stu_mark`.`s_id` AS `id`,`index_test`.`stu_mark`.`s_name` AS `name`,`index_test`.`stu_mark`.`mark` AS `mark`,`index_test`.`sign`.`s_sign_sch` AS `sch` from `index_test`.`stu_mark` join `index_test`.`sign` where ((`index_test`.`stu_mark`.`s_id` = `index_test`.`sign`.`s_id`) and (`index_test`.`stu_mark`.`mark` >= 40) and (`index_test`.`sign`.`s_sign_sch` = 'Tsinghua University'))

CHECK_OPTION: NONE

IS_UPDATABLE: YES

DEFINER: root@localhost

SECURITY_TYPE: DEFINER

CHARACTER_SET_CLIENT: gbk

COLLATION_CONNECTION: gbk_chinese_ci

*************************** 2. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: view

TABLE_NAME: qinghua

VIEW_DEFINITION: select `view`.`stu_mark`.`s_id` AS `id`,`view`.`stu_mark`.`s_name` AS `name`,`view`.`stu_mark`.`mark` AS `mark`,`view`.`sign`.`s_sign_sch` AS `sch` from `view`.`stu_mark` join `view`.`sign` where ((`view`.`sign`.`s_id` = `view`.`stu_mark`.`s_id`) and (`view`.`sign`.`s_sign_sch` = 'Tsinghua University') and (`view`.`stu_mark`.`mark` >= 40))

CHECK_OPTION: NONE

IS_UPDATABLE: YES

DEFINER: root@localhost

SECURITY_TYPE: DEFINER

CHARACTER_SET_CLIENT: gbk

COLLATION_CONNECTION: gbk_chinese_ci

2 rows in set (0.00 sec)

mysql>

删除创建的视图

mysql> DROP VIEW beida;

Query OK, 0 rows affected (0.00 sec)

mysql> DROP VIEW qinghua;

Query OK, 0 rows affected (0.00 sec)

mysql>