mysql基础知识(2)

视图

• 视图： 虚拟表，是从一个或多个基本表（或视图）导出的表。

  只存放视图的定义（能知道有什么字段，数据从哪几张表而来等等信息足够），不存放视图对应的数据，因为基本表已经存放过一次数据，再次存放会造成数据的冗余。

  因此，基表中的数据发生变化，从视图中查询出的数据也会随之变化。

• 视图的优点：
  简单：使用视图的用户完全不需要关系后面对应的表结构、关联条件和筛选条件，对用户来说已经是过滤好的符合条件的结果集。

  安全：使用视图的用户只能访问他们被允许的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

  数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

• 创建视图

  create view 视图名(列1，列2...) as select(列1，列2...) from...;

  注意： 视图名一般是以v_ 开头

  create view v_student(sno,sname,grade) as select student.sno,sname,grade from student,sc;
  

• 修改视图

  若视图不存在则创建

  create or replace view 视图名(列1，列2...) as select(列1，列2...) from...;

• 查询视图

  show tables;

• 删除视图

  drop view 视图的名称 [cascade];

  cascade 表示级联删除语句，把该视图和由它导出的所有视图一起删除

• 视图中插入数据

  只能基于一个基本表进行插入，不能跨表插入数据

  insert into v_student(sno,sname) values('20121020','陈梦');
  
  insert into v_student values('20121020','陈梦',90); //会报错，提示不能修改超过一个表的数据
  

  • with check option

    如果在创建视图的时候制定了“with check option”，那么更新数据时不能插入或更新不符合视图限制条件的记录。

  create view v_student(sno,sname,grade) as select student.sno,sname,grade from student,sc where grade >= 80 with check option;
  insert into v_student values('20121020','陈梦',70); 
  

  系统会提示错误CHECK OPTION FAILED。因为视图限制了成绩要高于80。 若不加“WITH CHECK OPTION”的视图，则可以成功插入。

• 视图中的更新

  update v_student set sname='陈庚' where sno='20121020';
  

  注：有一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新。

  例如聚合函数avg等，因为avg是几个数据的平均值，但修改后并不知道往哪个数据上修改，对基本表无法修改，自然也不能对视图修改（视图数据从基本表得来）。

  update v_student set Gavg=90 where sno='20121020';
  

• 视图中的删除

  delete from v_student where sname='陈梦';
  

事务

• 事务概念：事务Transaction，是指作为一个基本工作单元执行的一系列SQL语句的操作,要么全成功，要不全失败

• 作用：要么完全地执行，要么完全地都不执行

• 事务的特征 ACID：

  • A, 原子性 ，事务的操作是原子的，不能分隔
  • C ,一致性，双方的结果要一致
  • I，隔离性， 同一时间只有一个事务在操作
  • D, 持久性， 操作完成结果持久不变

• 使用步骤：

  • 开启事务

    begin;

  • 操作数据库

    insert update delete

  • 确认修改

    commit;

  • 回滚

    rollback;

索引

• 索引作用：提升查询效率

• 索引的使用：

  • 查看索引 show index from 表名

  • 创建索引: create index 索引名 on 表名(表中的字段名(字段长度))

    如果字段是字符串类型，需要指定长度

    如果字段不是字符串类型，可以不指定长度

  • 删除索引： drop index 索引名 on 表名;

用户管理

• 创建用户

  create user '用户名'@'主机' identified by '密码'

• 授权

  • 权限：create drop select insert update...
  • 所有权限：all privileges;

  grant 权限 on 数据库.表 to '用户名'@'主机';

  • 查询权限 show grants for '用户名'@'主机名'

• 刷新权限

  flush privileges;

• 修改用户权限

  grant 权限 on 数据库.表 to '用户名'@‘主机’ with grant option;

• 修改用户密码

  • 知道密码，改新密码

    alter user '用户名'@‘主机’ identified by '新密码';

  • 忘记密码，重置密码

    • 停止服务，设置跳过正常验证，设置密码为空
    • 设置正常验证模式，重启服务，用空密码登录，改成新密码

• 删除用户

  drop user '用户名'@'主机';

  delete from user where user='' and host=''