索引、视图与存储过程
索引
一,概念
索引室友数据库表中一列或者多列组合而成,其作用是提高对表中数据的查询速度。类似于图书的目录,方便快速定位,寻找指定的内容。
索引也是一张表,它保存了主键与索引字段,并指向实体表的记录。
二,索引的优缺点
优点:大大提高了查询数据的速度。
缺点:会大大降低更新表的速度,如对表进行INSERT UPDATE和DELETE。因为在更新表时,MySQL不仅要保存数据,还要保存一下索引文件,建立索引会占用磁盘空间的索引文件。
三,索引的分类
1.普通索引
是最基本的索引,没有任何限制。
创建索引: CREATE INDEX 索引名 ON 表名(字段名);
给表创建一个索引。通过所引来搜索信息。
修改表结构(添加索引)
ALTER TABLE ‘t_p1_blog’ ADD FULLTEXT ft_blog_summary_content (title,summary,content)
上图修改了索引,加入了新的列段
删除索引
DROP INDEX 索引名 ON 表明,成功删除名为i_title的索引。
2.唯一索引
与普通索引类似,不同的是唯一索引的索引列的值必须唯一。如果是组合索引,则列值的组合必须唯一;
3.主键索引
是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。
4.组合索引
指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一字段,索引才会被使用。则列值的组合必须唯一。
5.全文索引
使用FULLTEXT参数可以设置,全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上,主要作用是提高查询较大字符串类型的速度;只有MyISAM引擎支持该索引,MySQL默认引擎不支持。
上图通过对ssm 和Linux关键字的搜索, 会匹配出title,summary,content列段中含有关键字的项。
可以看出在没有索引的情况下搜索用了0.16S
在设置了索引后,查询相同的数据只用了0.032s,效率是很可观的。
四、数据库备份
1.导出整个数据库结构和数据
在cmd输入mysqldump -h localhost -uroot -proot 表明 >输出位置
成功在指定的d盘符复制成功。
2.导出单个数据表结构和数据
同理,只要在数据库后面加上要备份的表的名字即可。
3.导出整个数据库结构。
语法大同小异。
视图
含义: 虚拟表,和普通表一样
创建语法关键字 | 是否占实际占用物理空间 | 使用 | |
---|---|---|---|
视图 | create view | 只是保存了sql逻辑 | 增删改查,只是一般不能增删改 |
表 | create table | 保存了数据 | 只能改查 |
1.创建视图
create view 视图名
as 查询语句;
创建视图成功。
使用:
完成搜索带有a的数据
案例2:
2.视图的修改
视图的修改有两种方式
方式一:
creat or replace view 视图
as 查询语句;
SELECT * FROM myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
方式二:
alter view 视图名
as 查询语句;
ALTER VIEW myv3
AS
SELECT * FROM employees;
3.删除视图
drop view 视图名,视图名;
DROP VIEW emp_v1,emp_v2,myv3;
4.查看视图
4.1 DESC myv3;
4.2 show create view myv3;
区别: 最明显的区别 下面的方法会得到完整的写的语法。
5.视图的更新
-
插入
-
删除
DELETE FROM 视图名 WHERE last_name=要更新的数据。
4.更新
注意: 一下特点的视图不允许更新。
4.1包含:分组函数,distinct, group by, having,
union或者union all 的sql语句
4.2常亮视图
4.3 Select中包含子查询
4.4 join
4.5 from
4.6 where子句的子查询引用了from子句中的表
存储过程
存储过程(Stored Procedure),是一组为了完成特定功能的SQL 语句,集经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数,如果该存储过程带有参数来执行。
在SQL Server 的系列版本中,存储过程分为两类:系统提供的存储过程和用户自定义存储过程。
系统SP,主要存储master 数据库中,并以sp_为前缀并且系统存储过程主要是从系统表中获取信息,从而为系统管理员管理SQL Server。
一、变量
1.1系统变量: 全局变量和会话变量
1.2自定义变量: 用户变量和全局变量
成员 | 说明 | |
---|---|---|
系统变量 | 1.全局变量2.会话变量 | 变量由系统定义,不是用户定义,属于服务器层面注意:全局变量需要添加global关键字,会话变量需要添加session关键字,如果不写,默认会话级别 |
局部变量 | 1.用户变量2.局部变量 | 变量由用户自定义,而不是系统提供的 |
作用域 | 定义位置 | 语法 | |
---|---|---|---|
用户变量 | 当前会话 | 会话的任何地方 | 加@符号不用指定类型 |
局部变量 | 在它的BEGIN END 中 | 第一句话 | 一般不加@,需要指定类型 |
常用创建存储过程的参数:
创建存储过程的参数:
1.procedure_name :存储过程的名称,在前面加#为局部临时存储过程,加##为全局临时存储过程。
2.; number:是可选的整数,用来对同名的过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如,名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符,则数字不应包含在标识符中,只应在 procedure_name 前后使用适当的定界符。
[email protected]: 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有 2.100 个参数。
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身;相同的参数名称可以用在其它过程中。默认情况下,参数只能代替常量,而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息,请参见 EXECUTE。
4.data_type:参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数。不过,cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor,也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息,请参见数据类型。
说明 对于可以是 cursor 数据类型的输出参数,没有最大数目的限制。
5.VARYING: 指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。仅适用于游标参数。
6.default: 参数的默认值。如果定义了默认值,不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字,那么默认值中可以包含通配符(%、_、[] 和 [^])。
7.OUTPUT :表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。
8.RECOMPILE: 表明 SQL Server 不会缓存该过程的计划,该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时,请使用 RECOMPILE 选项。
9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中,SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。
10.FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选,且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。
11.AS :指定过程要执行的操作。
12.sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。