MySQL数据库的视图、存储过程、触发器、函数的学习

一、视图 View

1.概念
视图:有结构(有行有列),但没有结果(结构中不真实存储数据)的虚拟的表,
虚拟表的结构来源不是自己定义,而是从对应的基表中产生(视图数据的来源)

2.创建视图语法
create view 视图名称 as select语句(这个语句可以是一张或多张表的的普通查询,或多表查询)
例如:创建单表视图
create view my_v1 as select * from student;
例如:创建多表视图 注意:不要查询两张表中的同名字段 不然会报错
create view my_v2 as select a.字段名,b.字段名 from a,b where a.id=b.id;
注意：MySQL中视图不支持封装子查询查出来的数据

3.查看视图
其实视图是一张 虚拟表 那关于查询表的语句 对视图都是可以用的

        比如:show tables;  desc my_v1

只是在查看视图创建语句的的时候 把table 改成view

       如:show create view my_v1;

4.视图一旦创建
系统会在视图对应的数据库文件夹下,创建一个对应的结构文件:frm文件.
5.视图的使用:
视图的使用,只是为了查询,你可以把 视图当作表一样去使用

   例如:select * from my_v1;

视图的执行:其实本质就是执行封装的select 语句

6.修改视图:

视图本身不可以修改,但是视图的来源是可以修改的(其实就是修改select 语句)
语法: alter view 视图名字 as 新的select语句
7.删除视图:

 drop view 视图名称  
  	     例如:drop view my_v1

8.视图数据的操作:
视图是可以进行数据操作的(比如 增,删,改,视图中的数据),但是有很多限制
视图插入数据:

	(1)多表视图不能插入数据
	(2)单表视图中可以插入数据(如果视图中字段没有基表中不能为空的字段且没有默认值的字段,是插入不成功的)
	(3)视图是可以向基表中插入数据的 (视图的操作是影响基表的)

	视图删除数据
	(1):多表视图不能删除数据
	(2):单表视图可以删除数据,也会影响到基表

9、视图更新数据
(1):单表视图,多表视图都可以更新数据
更新限制:with check option
例如:create view my_v1 as select * from student where age>30 with check option;
表示视图数据的来源都是年龄大于30的,with check option 决定通过视图更新的时候,不能将已得到
数据age>30的学生 改成age<30 的.

	     那么:update  my_v1 set age=20 where id=1; 就会报错 不允许改 因为做了限制

二、函数

函数：包括内置函数，和自定义函数
自定义函数语法
DELIMITER $$

 CREATE
  FUNCTION `mytestdb`.`myFun`(num INT)
  RETURNS INT
  BEGIN
 DECLARE i INT DEFAULT 100;
 SET i=i+num;
  RETURN i;
  END$$
 DELIMITER ;

函数的调用 select 函数名();

函数和存储过程的区别

   1.存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out能实现返回值这个作用
    2.存储过程有in out inout 这几个参数类型 函数的参数全是用来收实参

四、 数据库的权限

1、mysql权限问题

-- mysql数据库权限问题：root ：拥有所有权限（可以干任何事情）
-- 权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表
-- 如何修改mysql的用户密码？

– password: md5加密函数(单向加密)

 SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

– mysql数据库，用户配置 : user表
USE mysql; – 使用数据库
Select password(‘root’ ) 查询用户密码
SELECT * FROM USER; -查询数据库用户

2、修改密码
UPDATE USER SET PASSWORD=PASSWORD(‘123456’) WHERE USER=‘root’;
3、分配权限账户

权限：

   select insert delete update drop create/  或,all
   @ 后面可以是localhost 也可以是ip  也可以给%那%代表任意一台计算机都可以连接上来

4、语法

 GRANT 权限 ON 数据库名.某张表名 TO '用户名'@'localhost' IDENTIFIED BY '123456';
 GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
 GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

注意分配多个权限用逗号隔开
GRANT DELETE,SELECT,UPDATE ON day16.employee TO ‘eric’@‘localhost’ IDENTIFIED BY ‘123456’;

删除用户

Delete FROM user Where User='eric' and Host='localhost';

数据库备份和还原 注意备份还原不需要登陆数据库

 备份:mysqldump -uroot -p day02 > d:/back.sql
  C:\Documents and Settings\Administrator>mysqldump -uroot -p day02 > d:/back.sql
         Enter password: ****	

恢复

：mysql -uroot -p day02 < d:/back.sql

注意恢复之前 先创建跟你原来一样的名称的数据库 相当于一个空的数据库,然后再还原

  C:\Documents and Settings\Administrator>mysql -uroot -p day02 < d:/back.sql
  Enter password: ****

五、 数据库表设计

数据库设计
引入
需求分析 - 需求分析师 -》 原始需求- > 抽取业务模型
图书模型：图书名称，版本号，作者
学生模型： 学号，学生姓名 手机号码
…
角色：学生 老师，图书管理员
《需求说明书》
需求设计 -
概要设计：
抽取实体：业务模型 -> 实体模型（java 类 c++类）内存
class Book{ name, bookNo,author }
数据库设计:
业务模型/实体模型 - > 数据模型 (硬盘)

三大范式
设计原则： 建议设计的表尽量遵守三大范式。

第一范式： 要求表的每个字段必须是不可分割的独立单元

	student     :   name              -- 违反第一范式
									  张小名|狗娃					
	sutdent    ： name    old_name    --符合第一范式
				  张小名    狗娃

第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。

	employee（员工）: 员工编号  员工姓名 部门名称   订单名称  --违反第二范式

				员工表：员工编号  员工姓名 部门名称   

				订单表：  订单编号  订单名称             -- 符合第二范式

第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

员工表： 员工编号（主键） 员工姓名  部门编号  部门名 --符合第二范式，违反第三范式																	（数据冗余高）

员工表：员工编号（主键） 员工姓名  部门编号    --符合第三范式（降低数据冗余）
部门表：部门编号  部门名