从头开始学习mysql,把今天的log记录一下。
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| hardware |
| monitor_type |
+-----------------+
2 rows in set
mysql> DESC monitor_type;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| serial_number | int(11) | YES | | NULL | |
| cpu | varchar(40) | YES | | NULL | |
| date | date | YES | | NULL | |
| MEMORY | int(11) | YES | | NULL | |
| HARDDISK | varchar(20) | YES | MUL | NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set
首先建了1个数据库test1,里面有2个表,为monitor_type表设置了5个参数。
为表格赋值。
mysql> INSERT INTO monitor_type(serial_number,cpu,date,MEMORY,HARDDISK)
-> VALUES(1,'INTEL','2018-07-05',64,'3G');
查询一下,确实有了。
mysql> SELECT *
-> FROM monitor_type;
+---------------+-------+------------+--------+----------+
| serial_number | cpu | date | MEMORY | HARDDISK |
+---------------+-------+------------+--------+----------+
| 1 | INTEL | 2018-07-05 | 64 | 3G |
+---------------+-------+------------+--------+----------+
1 row in set
创建一个名为product的视图
mysql> CREATE VIEW product
-> AS SELECT MEMORY,cpu
-> FROM monitor_type;
Query OK, 0 rows affected
查询视图,确实已经存在
mysql> SELECT *
-> FROM product;
+--------+-------+
| MEMORY | cpu |
+--------+-------+
| 64 | INTEL |
| 64 | AMD |
+--------+-------+
2 rows in set
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| hardware |
| monitor_type |
| product |
+-----------------+
3 rows in set
mysql> SHOW TABLE STATUS
-> FROM test1
;
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| hardware | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 9437184 | NULL | 2018-07-05 13:26:22 | NULL | NULL | utf8_general_ci | NULL | | |
| monitor_type | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 16384 | 9437184 | NULL | 2018-07-05 14:06:35 | NULL | NULL | utf8_general_ci | NULL | | |
| product | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
3 rows in set
mysql> SHOW CREATE VIEW product;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| product | CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`%` SQL SECURITY DEFINER VIEW `product` AS select `monitor_type`.`MEMORY` AS `MEMORY`,`monitor_type`.`cpu` AS `cpu` from `monitor_type` | utf8 | utf8_general_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set
mysql> DESCRIBE product;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| MEMORY | int(11) | YES | | NULL | |
| cpu | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set
好愁啊,数据库是一项很有挑战的技术。我要从原理到实践,迅速学习。