create命令
1.创建一个普通表
create table tb_1(id int ,name varchar(64));
Query OK, 0 rows affected (0.17 sec
2.创建一个临时表.temporary
create temporary table temp1(sid int,sname varchar(64));
Query OK, 0 rows affected (0.00 sec)
创建成功.
mysql> show tables;
+------------------+
| Tables_in_course |
+------------------+
| course |
| dept |
| score |
| students |
| students2_tmp |
| students_temp |
| teacher |
+------------------+
7 rows in set (0.00 sec)
但是使用show tables 是无法查看这个表的.
mysql> insert into temp1 values(1,"asda");
Query OK, 1 row affected (0.00 sec)
向临时表中插入数据.
mysql> select * from temp1;
+------+-------+
| sid | sname |
+------+-------+
| 1 | asda |
+------+-------+
1 row in set (0.00 sec)
查看数据
总结:
但是临时表仅仅对当前连接有效,并且不会显示在show tables中.
可以用来在当前连接中存储数据.
其他新建连接不仅看不到这张表,更不能向里面插入数据.
3.创建视图
create view 是将某个查询数据的定义爆了下了,一遍随时调用. view本身不存储查询结果,只是一个定义.
mysql> create view v_1 as select * from dept as a inner join students as b on a.id = b.dept_id;
Query OK, 0 rows affected (0.07 sec)
创建一张视图.将所要查询的数据用规则的形式存放到数据库视图中.
mysql> select *from v_1;
+----+------------------+-----+-------+--------+---------+---------------------+
| id | dept_name | sid | sname | gender | dept_id | brithday |
+----+------------------+-----+-------+--------+---------+---------------------+
| 1 | Education | 3 | Bob | 0 | 1 | 1983-01-01 00:00:00 |
| 2 | Computer Science | 4 | Ruth | 1 | 2 | 1983-01-01 00:00:00 |
| 2 | Computer Science | 5 | Mike | 0 | 2 | 1986-01-01 00:00:00 |
| 3 | Mathematics | 6 | John | 0 | 3 | 1986-01-01 00:00:00 |
+----+------------------+-----+-------+--------+---------+---------------------+
4 rows in set (0.01 sec)
查询的时候可以直接查询视图即可.4.
视图创建好以后可以理解成一张虚拟的表这张表可以与其他底层表进行关联查询.
但是不能向视图中插入数据.以及修改视图中的内容.
4.查看视图的定义
show create view v_1;
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------