day40---MySQL进阶

1. 视图

• 定义：视图(view)是一个虚拟表，视图中的内容是真实表数据的查询结果

• 本质：根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用名称可获取结果集，可以将该结果集当做表来使用

• 描述：使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

• 优点：

  • 可以隐藏非公开的和关键性字段的数据，灵活的开放指定字段的数据

  • 不用每次都重写子查询的sql语句，节省sql语句的代码量，可读性比较高

• 缺点：

  • 视图的效率低，没有子查询效率高

  • 视图中的数据是依赖数据库中的数据，针对视图做增删改的操作会影响正式的数据，安全性不可靠

• 创建视图语法：CREATE VIEW 视图名称 AS SQL语句;

• 修改视图：ALTER VIEW 视图名称 AS SQL语句;

• 删除视图：DROP VIEW 视图名称;

• 查看当前数据库中的视图信息：SELECT * FROM information_schema.VIEWS;

• 使用视图(查询视图)：跟普通查询的数据的方式一样，FROM后跟视图名称

• 视图中数据的任何增删改操作都会影响原始表中的数据，此操作危险性比较大，所以，不建议使用视图

2. 触发器

• 定义：触发器(trigger)定制用户对表进行【增、删、改】操作时前后的行为(监视某种情况，并触发某种操作)

• 触发器创建语法四要素：

  • 1. 监视地点(table)

  • 2. 监视事件(insert/update/delete)

  • 3. 触发时间(after/before)

  • 4. 触发事件(insert/update/delete)

• 触发时间：BEFORE/AFTER，任选其一；BEFORE表示前置触发，AFTER表示后置触发

• 监视事件：INSERT/UPDATE/DELETE，任选其一；INSERT表示执行新增语句时触发，UPDATE表示执行修改语句时触发，DELETE表示执行删除语句时触发

• 创建触发器：

CREATE TRIGGER 触发器名称 触发时间(BEFORE/AFTER) 触发事件(INSERT/UPDATE/DELETE) ON 表名 FOR EACH ROW
BGEIN
# 指定触发器需要运行的sql语句
END

• 动态获取参数：关键字(NEW/OLD)

  • NEW：表示即将插入的数据行

  • OLD：表示即将删除的数据行

• 查看当前数据库中的触发器信息：SHOW TRIGGERS;

• 删除触发器：DROP TRIGGER 触发器名称;

3. 存储过程

• 定义：存储过程(PROCEDURE)为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句(增删改查)、条件语句和循环语句等，类似于程序中的函数(方法)

• 说明：存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql；MySQL数据库在5.0版本后开始支持存储过程

• 优点：

  • 用于替代程序写的SQL语句，实现程序与sql解耦

  • 增强了SQL语言灵活性

  • 减少网络流量，降低网络负载

  • 基于网络传输，传别名的数据量小，而直接传sql数据量大

  • 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度

  • 系统管理员通过设定某一存储过程的权限实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全

• 缺点：不利于扩展

• 创建存储过程：

  • 无参数：

  CREATE PROCEDURE 存储过程名称()
  BEGIN
      # 指定存储过程需要运行的sql语句
  END
  

  • 有参数：存储过程可以接收的参数有三种方式：

    • in：传入参数

    • out：返回值

    • inout：既可以当作参数传入又可以当作返回值

  CREATE PROCEDURE 存储过程名称(参数方式,参数名,参数类型)
  BEGIN
      # 指定存储过程需要运行的sql语句
      SELECT 参数名 # 调用时输出返回值(如果不指定SELECT，也可以在外部调用时SELECT)
  END
  

  • 条件语句(IF/CASE)

  • 循环语句(WHILE/REPEAT/LOOP)

  • 变量定义：DECLARE 变量名 变量类型 [DEFAULT 变量值](定义变量并直接赋值)

  • 变量赋值：SET 变量名 = 变量值

  • 用户变量：以'@'符开头

    • 在MySQL客户端可以直接使用用户变量

    • 在存储过程中可以使用用户变量

    • 在存储过程间可以传递全局范围的用户变量

  • 注释：

    • 单行注释：双模杠(--)

    • 多行注释：C风格(/*注释内容*/)

• 调用存储过程：

  • 无参数：

  call 存储过程名称();
  

  • 有参数(全in)：

  call 存储过程名称(参数1,参数2);
  

  • 有参数(有in、out、inout)：

  set @变量名1 = 变量值1;
  set @变量名2 = 变量值2;
  call 存储过程名称(参数1,参数2,变量名1,变量名2)
  

• 删除存储过程：DROP PROCEDURE 存储过程名称;

• 查看当前数据库中的存储过程信息：SHOW PROCEDURE STATUS;

4. 函数

• 定义：函数(FUNCTION)是封装一些功能逻辑集合，用于sql中的应用

• 注意：

  • 函数中不能写sql语句，会报错

  • 函数仅仅是一个功能，是一个在sql中应用的功能

  • 需要在begin...end中写sql实现功能，需要使用存储过程

• 创建函数：

CREATE FUNCTION 函数名(参数1 参数1类型,参数2 参数2类型)
    
    RETURNS 返回值的类型
BEGIN
    # 函数体(需要实现的功能)
END

• 调用函数：

  • 直接调用：SELECT 函数名(参数1,参数2);

  • 在sql语句中使用：SELECT 函数名(参数1,参数2) FROM 表名 WHERE 条件;

• 删除函数：DROP FUNCTION 函数名;

• 查看当前数据库中的函数信息：SHOW FUNCTION STATUS;

5. 创建千万级数据

-- 创建数据库
create database yange default charset utf8;
use yange;

-- 创建表结构
create table yange(
  `id` int(11) not null auto_increment primary key comment 'ID',
  `name` varchar(20) not null default '' comment '姓名',
  `username` varchar(50) not null default '' comment '用户名',
  `password` varchar(32) not null default '' comment '密码',
  `age` int(11) not null comment '年龄',
  `email` varchar(50) not null default '' comment '邮箱地址',
  `salary` int(11) not null comment '工资',
  `signature` text not null comment '个人签名'
)engine=myisam
default charset=utf8
comment='大数据测试表';

-- 创建存储过程
create procedure yange_p(in data int)
begin
	declare n int default 1;
	while n <= data do
		insert into yange values (
			null,concat('岩哥',n),concat('yy',n),md5(concat('yy',n,'yy',n)),
			rand()*100,concat('yy',n,'@sina.com'),rand()*100000,concat('我是','yy',n,',','我喜欢','yy',n*2));
	set n = n + 1;
	end while;
end

-- 查看储存过程
show procedure status;

-- 插入一千万条数据
call yange_p(10000000);

-- 查看表数据
select * from yange;

-- 修改存储引擎
alter table yange engine=innodb;

-- 给name字段添加索引
create index index_name on yange (`name`);