目录

七、视图与索引

1、提高查询效率

2、视图

2.1、视图的定义和作用

2.2、建立视图的原因和优点

2.3、视图的使用

3、索引

3.1、索引的概念

3.2、索引的作用以及优势

3.3、索引与存储引擎

3.4、索引的分类

3.4.1、普通索引

3.4.2、唯一索引

3.4.3、主键索引/外键索引

3.4.4、全文索引

3.5、索引的缺点

八、函数和存储过程

1、数据库函数

1.1、聚合函数

1.2、时间日期函数

1.3、常用库函数

2、自定义函数

2.1、自定义函数的创建

2.2、自定义结束符

2.3、函数的调用与删除

3、存储过程

3.1、创建无参存储过程

3.2、创建带参存储过程

3.3、存储过程的调用

3.4、查看已创建的指定存储过程的相关信息

3.5、查看已创建的所有存储过程的相关信息

3.6、存储过程的删除

4、存储过程和函数的区别

七、视图与索引

1、提高查询效率

        查询语句也有查询的效率问题，比如说交叉连接与内外链接的区别，交叉连接需要产生一个笛卡尔积的临时表，这样效率比较低。

采取以下方式提高查询语句的效率

        在现实生活中的查询往往需要查找多个表中的数据，要求所查询的数据表存在联系，这就涉及到多表查询了。

交叉连接的基本格式：

        ① 选择有效的表名顺序，select语句一般优先处理写在from子句最后的表，将记录条数最少或者父表（被参考表）放在最后可提高查询效率。

        ② select 尽量避免使用 * ，如果不需要查询所有属性的话，尽量把需要查询的字段一一写出来，而不是用*代替。比如说一共有10个字段，你需要查询其中的9个字段，把9个字段都写上更好，在满足需求的情况下，能少查一个字段就少查一个字段。

        ③ 整合简单的数据库查询语句，能一次性查询出来的数据就不要分为两次或者多次查询。

        ④ 尽可能使用where替换having，能使用where解决的就不要使用分组查询的having子句，因为having是查询所有记录之后，再进行分组过滤，其中可能还需要排序和统计等，比较繁琐。

        ⑤ 尽量多用内部函数，少用运算符自己进行的操作，因为内部函数的处理优化得比较好。

        ⑥ 多使用表的别名，以减少解析时间。

        ⑦ 尽量使用exists代替in，not exists代替not in。

        ⑧ 尽量用>=代替>等。如：有时候>5和>=6所查询的效果是一样的，但是一个是先找5，一个是先找6，多找了一个数。

2、视图

2.1、视图的定义和作用

        视图可以用于代替查询，多次使用同一个查询语句时，可以为此查询创建一个视图，方便后续使用。

        视图就相当于为一个查询结果集（虚拟表）创建一个快捷方式。

2.2、建立视图的原因和优点

        ① 由于有些查询语句又长又麻烦，并且经常使用，这时可以给他创建一个视图，以便于后续操作。

        ② 用户只用关心数据，而不关心数据是如何查询的，表与表之间的连接操作是怎样的，是面向结果，而不是面向过程。

2.3、视图的使用

① 视图关键字： view

一般格式：

create view <视图名> as <查询语句>;

create view <视图名称> as <select <属性名> from <表名>>;

可以查询表中全部数据创建视图，也可查询表中部分数据创建视图

② 视图的基本使用

select * from <视图名>; 视图与上面的查询语句结果一致

③ 视图的修改

alter view 视图名 as 查询语句; # 将视图更改为其他查询

④ 删除视图

drop view 视图名;

3、索引

3.1、索引的概念

        索引是以一种高效获取数据的存储结构来存储数据，例如：hash、 二叉搜索树、 红黑树等。

3.2、索引的作用以及优势

        索引用于提高查询的效率，对需要频繁查询的某些字段建立索引，比普通字段的查询速度要快一些，除此之外索引并没有其他用处。

        建立索引能提高查询速率。

3.3、索引与存储引擎

        在MySQL中，索引与存储引擎是相关的，也就是说索引是存储引擎级别的概念，索引的数据也是需要存储在硬盘中的，不同的存储引擎对索引的实现方式是不同的，也就是存储的数据结构不同。

        ① MyISAM (默认)和 InNODB 支持 BTREE (B树、二叉查找树)索引，数据是以二叉树的形式存储的。

        ② Memory和Heap 支持 BTREE 和 HASH (哈希表、数组)索引，数据是以哈希表的形式存储的(查询速度快)。

3.4、索引的分类

3.4.1、普通索引

        对普通字段建立的索引，允许定义索引的字段重复和空值。

① 创建普通索引

创建普通索引的基本格式：

create index 索引名 on 表名<字段1[,字段2,…,字段n]>;

如：

create index Ind_Sage on student(s_age);

-- 对student表的s_age属性创建了一个普通索引，索引名为Ind_Sage。

select s_age from student;

-- 对于查询student表中s_age这个单个字段，查询效率会有所提高

-- 由于数据表中的数据量较少，所以看不到查询效率提升的明显效果

-- 数据表中数据越多，数据量越大，索引效果提升会越明显

② 以修改的方式添加索引

基本格式：

alter table <表名> add index <索引名(属性名)>;

如：

alter table student add index Ind_Ssex(s_sex);

-- 为student表中的s_sex属性添加一个名为Ind_Ssex的普通索引

③ 查看表中所有索引

基本格式：

show index from <表名>;

如：

show index from student;

-- 查看student表中的所有索引

3.4.2、唯一索引

        唯一索引关键字：unique index

        对添加了唯一索引的字段进行查询，查询效率会提高。

        唯一索引和唯一约束相关，给某属性添加唯一索引后，不允许属性的取值重复。

①创建唯一索引

基本格式：

create unique index <索引名> on <表名(属性名)>;

-- 对表格名称为<表名>的表格中属性名为<属性名>的属性创建索引名为<索引名>的唯一索引

如：

create unique index Ind_Sage on student(s_name);

-- 对表格名称为student的表格中的属性s_name创建索引名为Ind_Sage的唯一索引

#可以不用手动创建唯一索引，给属性添加唯一约束时会自动创建唯一索引。

② 以修改的方式添加唯一索引

基本格式：

alter teble <表名> add unique(<属性名>);

-- 为表格名称为<表名>的数据表中属性名为<属性名>的属性添加一个唯一索引

如：

alter table student add unique(s_name);

-- 为表格名称为student的数据表中属性名为s_name的属性添加一个唯一索引

3.4.3、主键索引/外键索引

① 主键索引的基本概念

主键/外键索引关键字：primary key

主键和外键的查询效率比较高，因为主键和外键有主键索引。

② 主键索引的创建

        为属性建立主键或外键约束时会自动创建主键索引，或者在创建表格时创建主键索引。

如：

create table 表名{ 
id int; 
name varchar(20); 
primary key(id) 
-- 为属性id创建一个主键索引 }

③主键索引的添加

基本格式：

ALTER TABLE <表名> ADD PRIMARY KEY (<列名>);

-- 以修改的方式添加主键索引

3.4.4、全文索引

        全文索引关键字：fulltext index

        对添加了全文索引的字符串或文本类型的属性进行查询，查询效率会提高。

        全文索引相对而言较少使用，其只限于对文本、字符串等数据类型添加。

①全文索引的创建

基本格式：

create fulltest index <索引名> <表名(属性名)>;

-- 为表格名称为<表名>的数据表中属性名为<属性名>的属性创建一个全文索引<索引名>

如：

create fulltext index fullind_tname teacher(t_name);

-- 为表格名称为teacher的数据表中属性名为t_name的属性添加一个全文索引<索引名>

② 以修改的方式添加全文索引

基本格式：

alter table <表名> add fulltext index fullind_<索引名>(<属性名>);

-- 为表格名称为<表名>的数据表中属性名为<属性名>的属性添加一个全文索引<索引名>

如：

alter table teacher add fulltext index fullind_tname(t_name);

-- 为表格名称为teacher 的数据表中属性名为t_name的属性添加一个全文索引fullind_tname

3.4.5、空间索引

        空间索引是对空间数据类型（点、线、面、立体图形）建立的索引，由于空间索引应用范围不广泛，一般只在存储地图、模型等相关数据的数据库中有所应用，不常使用

3.5、索引的缺点

定义了索引的字段与普通字段相比，索引需要占用额外的磁盘空间，由于存储的数据结构有所 差异，对数据进行增加、删除、修改等操作时，建立了索引的字段就需要动态维护，所以增加了DBMS 的工作负担，并且降低了数据库的可维护性。

八、函数和存储过程

1、数据库函数

        高级语言都有自己的库函数，数据库也不例外，在数据库中也提供了一些用于实现特定功能的函数。

1.1、聚合函数

        在数据库函数中，聚集函数是我们最常用的一类。所谓的聚合，就是对一组值进行组合计算，返回单个值。

① count

        计数函数，对元组或者属性计数。

        一般格式：count(属性名)。

select count(*) from 表名;

# ‘*’是对整个表的元组进行计数

select count(属性名)from 表名 where 条件表达式;

# count 支持条件计数，只计算有效值，不计算null值

② max

        求最大值函数，查询字段中的最大的数。

        一般格式：max(属性名)

如： 

 select max(属性名) from 表名 [where 条件表达式];

③ min

        求最小值函数，查询字段中的最小的数。

        一般格式：min(属性名)

如：

select min(属性名) from 表名 [where 条件表达式];

④ avg

        求平均值函数，查询字段中所有数的平均值。

        一般格式：avg(属性名)

如：

select avg(属性名) from 表名 [where 条件表达式];

⑤ sum

        求和函数，查询属性中所有数的和。

        一般格式：sum(属性名)

如：

select sum(属性名) from 表名 [where 条件表达式];

# 除了通过函数计算还能直接用运算符计算

如：

select 属性名1*2,属性名2+属性名3 from 表名;

1.2、时间日期函数

① now

求当前的日期和时间。

一般格式：now()。

如：

select now();

② date

求日期函数。

一般格式：date(日期时间类型的属性名)。

如：

select date(birthday) from student;

③ time

求时间函数。

一般格式：time(日期时间类型的属性名)。

如：

select time(birthday) from student;

④ date_format

时间日期类型转字符串类型函数。

一般格式：date_fromat(日期时间类型，格式字符串)

如：

select date_format(now(),'%Y%m%d:%H%i%s');
# 格式字符串可用变量来代替

⑤其它获取日期时间的库函数

time(); -- 时:分:秒

year(); -- 年

month(); -- 月

day(); -- 日

hour(); -- 小时

minute(); -- 分钟

second(); -- 秒钟

1.3、常用库函数

① upper

        小写字母转大写字母函数。

一般格式：upper(字符型属性名);

如：

select upper('abc');

# 也可用 ucase函数代替，如：select ucase('abc');

② lower

        大写字母转小写字母函数。

一般格式：lower(字符型属性名);

如：

select upper('ABC');

# 也可用 lcase函数代替，如：select lcase('ABC');

③ substring

        提取字串函数，以给定的参数求字符串中的一个子串。

一般格式：

substring(字符串,start,lenth);

# 从字符串中第start个字符开始取出长度为lenth的字符串。

如：

select substring('abc123@#$',4,3);# 结果为123

# 也可以用mid函数代替，如：select mid('abc123@#$',4,3);

④ round

        四舍五入函数。（可代替floor函数，用于取整）

一般格式：

round(数值类型[,精确位数]);

如：

select round(1234.56); # 不加精确位数默认精确到整数位

select round(123.456,2); #加上精确位数就保留多少位小数

⑤ power

求次方函数

一般格式：

power(底数,指数);

如：

select power(2,3); # 求2的3次方的值

# 可以用pow函数代替power函数如：select pow(2,3);

⑥ length

返回字符串的长度

一般格式：

length(字符串)

⑦ concat

字符串连接函数，连接多个字符串

一般格式：

concat(字符串1,字符串2,……,字符串n)

⑧ database

返回当前数据库名

一般格式：

select database();

⑨ user

获取当前用户名

基本格式：

select user();

2、自定义函数

        数据库中的自定义函数和C语言函数类似，可以自定义函数，编辑函数所执行的代码，使其完成自己想要的功能。

2.1、自定义函数的创建

创建自定义函数的基本格式：

create function 函数名(函数参数 数据类型)

returns 返回值类型

begin

# 函数体

return(函数返回值)

end;

# 8.0版本的数据库,增加了一个数据库安全选项

需要执行以下代码才能创建函数

set global log_bin_trust_function_creators=TRUE;

2.2、自定义结束符

MySQL数据库中默认语句结束符为';'分号，此外MySQL数据库还支持自定义结束符。

自定义结束符关键字：delimiter

自定义结束符的基本格式：

delimiter <字符> -- 将<字符>定义成为结束符

如：

delimiter , -- 把默认语句结束符';'改为','，之后的所有SQL语句会以','作为结束符

delimiter $$ -- 把默认语句结束符';'改为'$$'，之后的所有SQL语句会以'$$'作为结束符

2.3、函数的调用与删除

① 函数调用

函数调用的基本格式：

select 函数名(函数参数);

② 用表格数据作为函数参数

select 函数名(表格属性) from 表名;

③ 删除函数

drop function 函数名;

3、存储过程

        存储过程和函数类似，都是一个事先写好命令并编译后存在数据库中的MySQL语句集合，用于简化开发人员的工作，减少数据在数据库和应用服务器之间传输，提高处理效率。

        存储过程：参数的传递(参数类型)能用 IN 、 OUT 、 INOUT ，并且没有直接的返回值。IN 表示数据传入，OUT 表示数据传出，INOUT 表示既可传入又可传出。存储过程内不能使用 use 切换数据库。

        函数：参数只能使用IN，并且必须要有返回值。

        存储过程关键字：procedure

3.1、创建无参存储过程

创建无参存储过程的基本格式：

delimiter 自定义结束符

create procedure 存储过程名()

begin

SQL语句1;

SQL语句2;

……

SQL语句n;

end;

delimiter ;

如：

delimiter //

create procedure select_stu()

begin -- 复合SQL语句

select s_id from student where s_id > 3; -- 存储过程select_stu完成查询学生表 数据的功能

select s_name from student where s_id > 3;

select s_cid from student where s_id > 3;

end//

delimiter ;



call select_stu(); -- 执行此存储过程与视图的效果类似

3.2、创建带参存储过程

①创建带参存储过程的基本格式：

delimiter 自定义结束符 create procedure 存储过程名(

参数类型 参数名1 数据类型,

参数类型 参数名2 数据类型,……,

参数类型 参数名n 数据类型)

began

SQL语句;

end自定义结束符

delimiter ;

3.3、存储过程的调用

调用存储过程的基本格式：

call 存储过程名(参数列表);

如：

call select_stu(); -- 调用无参存储过程

call select_stu; -- 调用无参存储过程时括号可省略

3.4、查看已创建的指定存储过程的相关信息

查看指定存储过程的基本格式：

show create procedere <存储过程名>;

如：

show create procedure select_stu;

3.5、查看已创建的所有存储过程的相关信息

查看所有存储过程的基本格式：

show procedure status;

3.6、存储过程的删除

删除存储过程的基本格式：

drop procedure <存储过程名>；

4、存储过程和函数的区别

        ①. 函数只能返回一个数据，而存储过程可以返回多个;

        ②. 函数的限制比较多,不能返回临时表(select)，只能用表变量，而存储过程限制少，且可以使用动态SQL语句(如：select);

        ③. 存储过程处理的功能比较复杂，而函数实现数据的计算针对性强;

        ④. 存储过程可以执行修改表的操作，但是函数不能执行一组修改全局数据库状态的操作;

        ⑤. 存储过程可以通过out返回多个参数，而函数只能返回单个数据或者表对象。