10.30 索引，外键，存储过程

索引

一、索引的介绍

1、什么是索引？

（1）定义：索引是一种数据结构

一个索引在存储的表中的数据结构；

（2）索引是在表的字段上创建的

（3）索引包含了一列值，这个值保存在一个数据结构中

2、索引作用？

（1）保证数据记录的唯一性

（2）实现表与表之间的参照性

（3）减少排序和分组的时间（例如在使用order by ，group by 查询语句中进行数据检索）
（4）可以使用索引快速访问数据库中指定信息

3、索引的缺点？

（1）索引要占物理内存

（2）索引对表进行增删改查，索引要动态维护，降低数据的维护速度

4、索引的分类

（1）普通索引

index 简称 mul 最基本的索引，没有任何限制

（2）主键索引

primary key 简称 pri 是一种唯一索引，不能为空

（3）唯一索引

unique 简称 uni 是一种唯一索引，可为空，一个表中可以有多个唯一索引

以下作为了解下：

（4）全文索引

（5）组合索引

（6）单列索引

（7）聚焦索引

（8）非聚焦索引

==========================================

二、索引的应用

1、索引的查询

方法一：

格式1：

格式：show INDEX from 表名;

案例：show INDEX from emp ;

方法二：

格式：show keys from 表名；

案例：show KEYS from student2 ;

（2）查看表结构，通过表结构查看索引

desc 表名

（3）创建普通索引

第一种情况：索引名和字段名不一致

格式：ALTER table 表名 add INDEX 索引名(字段名);

案例：ALTER table student2 add INDEX sym(sex);

简写：mul

第二种情况：索引名和字段名一致

格式：ALTER table 表名 add INDEX (字段名);

案例：ALTER table student2 add INDEX (age);

（4）唯一索引（唯一，为空，在一个表可以有多个唯一索引）

单词：unique

简写：uni

第一种情况：添加唯一索引名和字段名不一致

格式：

alter table 表名 add UNIQUE 索引名(字段名)

案例：

alter table student2 add UNIQUE aa(name)

第二种情况：添加唯一索引名和字段名一致

格式：

alter table 表名 add UNIQUE (字段名)

案例：

alter table student2 add UNIQUE (name)

（5）添加主键索引（唯一，不能为空，一个表中只有一个主键）

单词：primary key 主键

简写：pri

格式：

ALTER table 表名 add PRIMARY key (字段名) ;

案例：

ALTER table student2 add PRIMARY key (id) ;

（6）删除索引：

1、第一种情况：删除普通索引和唯一索引是通一种方法

格式：

alter table 表名 drop INDEX 索引名

案例：
alter table student2 drop INDEX aa;

2、第二种情况：删除主键索引

格式：

alter table 表名 drop primary key；

案例：

alter table student2 drop primary key ；

===============================================

二、创建方法二

格式：create INDEX 索引名 on 表名 (字段名)

案例：create INDEX aa on student2 (english)

===============================================

三、建表时创建索引

格式：

CREATE table 表名( 字段名字段类型(字符长度) PRIMARY key ,字段名字符类型(字符长度) UNIQUE )) ;

案例：

CREATE table wzx( id int(10) PRIMARY key ,name VARCHAR(20) UNIQUE ,age int(10)) ;

索引是对表的一列数据起到约束作用

===========================================

面试题：

什么是索引？
索引是数据库表中一个或多个列的值存储的数据结构，它允许数据库快速检索和操作表中的数据。类似于书籍的目录，索引提供了一种快速定位数据行的方法，而不需要扫描整个表。
索引的作用
- 提高查询速度：索引可以显著加快数据检索的速度，因为索引允许数据库引擎直接跳到所需的数据块，而不是扫描整个表。
- 加速表连接：对于经常需要进行连接操作的表，索引可以提高连接的速度。
- 维护数据的唯一性：唯一索引可以确保列中的值是唯一的。
- 排序和分组：索引可以帮助数据库更快地执行排序和分组操作。
索引有哪些？
- 主键索引：唯一标识表中每条记录的索引。
- 唯一索引：保证列中的值是唯一的，但允许有空值。
- 复合索引：在两个或多个列上创建的索引，用于加速涉及这些列的查询。
- 全文索引：支持在文本列中进行全文搜索。
- 空间索引：用于地理空间数据类型，以优化空间数据的查询。
- 普通索引：最基本的索引类型，没有唯一性要求。
- 覆盖索引：包含查询中所有列的索引，可以避免访问表中的实际数据行。

如何创建索引？
创建索引的基本语法如下：

-- 创建表时直接添加索引
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    INDEX index_name (column1),
    INDEX index_name (column2)
);

-- 向现有表添加索引
CREATE INDEX index_name ON table_name (column);

-- 为多个列创建复合索引
CREATE INDEX index_name ON table_name (column1, column2);

在这里，table_name是表名，column是您要创建索引的列名，index_name是您为索引指定的名称。

主键索引和唯一索引的区别？
- 主键索引：表中每条记录的主键值必须是唯一的，并且不能为空（NOT NULL）。一个表只能有一个主键索引。
- 唯一索引：保证索引列的值是唯一的，但允许有空值（NULL）。一个表可以有多个唯一索引。
两者都确保了列值的唯一性，但主键索引还具有表中每条记录都必须有一个唯一标识符的额外含义，而唯一索引则没有这样的要求。此外，主键索引通常在表创建时定义，并且在数据库设计中扮演着核心角色。

数据库之外键

==========================、

一、外键的介绍

1、外键的定义

让一张表记录的数据不要太过于冗余，在数据库中对表的关系进行解耦，尽量让表的数据单一化。

2、外键的作用

保持数据的一致性和完整性

3、msyql 数据库中的存储引擎？

myisam （默认）

innodb （外键需要用到innodb存储格式）

4、查看存储引擎

格式：show table status from 库名 where name='表名' ;

案例：show table status from hz017 where name='student2' ;

5、存储类型：myisam （默认）

5、查看外键方法：

（1）在navicat中查看

（2）

格式：show create table 表名；

案例：show create table cc

==================================================

二、外键运用

（一）建表时创建外键

（1）创建 engine=INNODB 格式

格式：

CREATE table 表名 (字段名字段类型(字符长度) PRIMARY key , 字段名字符类型(字符长度) ,constraint 外键名 FOREIGN key(子表字段)
REFERENCES 父表(父表字段)) engine=INNODB ;

案例：

父表

create table ss (id int(10) PRIMARY key, name varchar(20) )ENGINE=INNODB ;

子表

CREATE table cc (cid int(10) PRIMARY key ,cname VARCHAR(20) ,constraint wj FOREIGN key(cid)
REFERENCES ss(id)) engine=INNODB ;

constraint 外键名（指定外键名）

FOREIGN key 子表字段指定的字段

REFERENCES 父表（父表字段）引用外部表的主键

（二）建表以后再添加外键

格式：

alter TABLE 子表 add CONSTRAINT 外建名 FOREIGN key (子表字段) REFERENCES 父表(父表字段)

（1）新建两个表

父表

create table xx (id int(10) PRIMARY key ,name varchar(20))engine=INNODB ;

子表

create table yy (yid int(10) PRIMARY key ,yame varchar(20))engine=INNODB ;

select * from xx ;
SELECT * from yy;
show create table yy;

alter TABLE yy add CONSTRAINT wjm FOREIGN key (yid) REFERENCES xx(id)

==============================================================================

删除外键：

格式：ALTER TABLE 表名 drop foreign key 外键名;

案例：ALTER TABLE yy drop foreign key wjm ;

=======================================================================

外键特点：

外键实际操作：

1、当父表不存在的数据，子表也无法插入数据（子表无法插入数据）

2、父表中存在的数据，子表就可以插入数据（插入数据）

3、删除数据，子表中存在的数据，直接删除父表是无法删除（要先删除子表数据，在删除父表数据）

4、删除父表的数据，先删除子表，在删除父表

=========================================

面试题：

什么是外键？
外键是一个表中的字段，它与另一个表的主键相关联。外键用于建立两个表之间的关系，确保数据的参照完整性。在关系数据库中，外键约束是一个重要的特性，它允许一张表引用另一张表的主键列。
外键作用？
- 确保数据一致性：外键确保引用表中的数据必须在主表中存在，从而保持数据的一致性。
- 维护数据完整性：外键帮助维护数据库的完整性，防止插入不相关的数据。
- 实现级联操作：可以设置外键的级联规则，如级联删除或级联更新，这有助于自动维护相关表中的数据。
- 定义表之间的关系：外键用于定义表之间的一对一、一对多或多对多的关系。

如何创建外键？
创建外键通常在创建表时或之后通过ALTER TABLE语句添加。以下是创建外键的基本语法：

-- 创建表时直接添加外键
CREATE TABLE child_table (
    child_column INT,
    FOREIGN KEY (child_column) REFERENCES parent_table (parent_column)
);

-- 向现有表添加外键
ALTER TABLE child_table
ADD FOREIGN KEY (child_column) REFERENCES parent_table (parent_column);

在这个例子中，child_table是引用表，parent_table是主表，child_column是外键列，parent_column是主表的主键列。

如何删除外键？
删除外键使用ALTER TABLE语句，基本语法如下：
```
ALTER TABLE child_table DROP FOREIGN KEY foreign_key_name;
```
在这里，foreign_key_name是外键的名称。如果您不知道外键的名称，可以通过数据库的元数据或系统表查询得到。
公司中为什么使用外键？
在公司中使用外键的原因包括：
- 数据完整性：确保数据库中的数据准确无误，相关数据之间保持一致性。
- 数据查询：通过外键关系，可以方便地进行跨表查询，获取相关联的数据。
- 数据恢复：在外键的帮助下，可以更容易地恢复或回滚数据库中的数据变更。
- 业务规则实施：外键可以帮助实施业务规则，比如，如果主表中的记录被删除，相关联的引用表中的记录可以自动删除或更新。
- 数据库规范化：外键是数据库规范化的一个重要方面，有助于减少数据冗余和提高数据存储效率。

外键的使用可以提高数据库操作的准确性和效率，是数据库设计和维护中的一个重要工具。

存储过程

一、存储过程介绍

1、什么是存储过程？

存储过程是实现某个特点功能的sql语句的集合，编译后的存储过程会保存在数据中，通过存储过程的名称反复的调用执行。

2、存储过程的优点？

t（1）存储过程创建后，就可以反复的调用和使用，不需要重新写复杂的语句

（2）创建，修改存储过程不会对数据有任何的影响

（3）存储过程可以通过输入参数返回输出值

（4）通过存储过程中加入控制语句，可以加强sql语句的功能性和灵活性

（5）对于单个l语句增删改查，可以直接封装一个集合中，存储过程一旦创建就可以直接调用，且可可以重复调用

（6）单个sql语句每一次执行都需要对数据进行编译，而存储过程被创建只需要编译一次，后续即可调用

（7）创建的存储过程，可以重复进行调用，可以减少数据库开发人员的工作量

（8）防止sql 注入

（9）造数据（重点）

3、mysql5.0版本之后就支持存储过程，存储过程是由sql语句和控制语句组成的

===================================================================

格式：

delimiter // #分隔符符号 //
create PROCEDURE 存储名( 参数名：in out inout)
BEGIN 开始
sql语句执行语句
END 结束

// 分隔符号

call 存储名（参数）调用一个存储过程

案例：

delimiter //
create PROCEDURE hz001()
BEGIN
SELECT * from emp ;
select * from dept ;
END

call hz001()

================================================

三、存储过程的基本语句:

(1) 删除存储过程

格式：

drop procedure 存储名称

案例：

drop procedure hz001

（2）查看单个存储过程的详情

格式：

show create procedure 存储过程;

案例：

show create procedure hz001;

（3）查看所有已经建好的存储过程详情

格式：show procedure status

（4）查询指定数据库里创建的存储过程

格式：

show procedure status where db="数据库名"

案例：show procedure status where db="hz017"

===========================================================================

四、创建存储

1、创建无参数存储过程

格式：

delimiter //
create PROCEDURE 存储名1() #无参数，是括号内无参数
BEGIN #开始
sql语句 #执行语句
END #结束

// 结束符号

call 存储名() 调用存储名称

案例：

delimiter //
create PROCEDURE hz1() #无参数，是括号内无参数
BEGIN
SELECT * from emp where dept2=101 ;
END

call hz1()

2、带有in参数的存储

in表示输入数据

格式：

delimiter //
create PROCEDURE 存储名(in x int ) #in参数，是括号
BEGIN
sql语句
END

call 存储过程(入参)

案例：

delimiter //
create PROCEDURE hz(in x int ) #无参数，是括号内无参数
BEGIN
SELECT * from emp where dept2=x ;
END

call hz(105)

3、带out 的参数（输出参数）

格式：

delimiter //
create PROCEDURE 存储名(out x int ) #in参数，是括号
BEGIN
sql语句
END

call 存储过程(入参)

案例：

delimiter //
create PROCEDURE hz5(out y int ) #无参数，是括号内无参数
BEGIN
SELECT age into y from emp where dept2=105;
END

call hz5(@y)
select @y

带有 in 和out 的存储过程

案例：

delimiter //
create PROCEDURE hz6(in x int ,out y int ) #无参数，是括号内无参数
BEGIN
SELECT age into y from emp where dept2=x;
END

call hz6(109,@y)
select @y

===========================================================================

inout参数代表着输入和输出

案例1：

delimiter //
create PROCEDURE hz8(inout m int ) #无参数，是括号内无参数
BEGIN
set m=m+1;
END
//
set @m=2
call hz8(@m)
select @m

案例2：

delimiter //
create PROCEDURE hz9(inout x int ) #无参数，是括号内无参数
BEGIN
SELECT age into x from emp where dept2=x;
END

set @x=109
call hz8(@x)
select @x

备注：

CHARACTER set UTF8 解决？号

===========================================

用户变量：

定义语法L

set @ 变量名

赋值的方法：

（1）方式一

set @ 变量名：=值

或

set @变量名=值；

select @变量名：=值；

（2）方式二

通过查询结果为变量赋值：

select 字段 into 变量名 from 表名 where 条件

（3）

declare 声明变量

例如：

declare i int default 0 ；

============================================

循环语句：

三种：

（1）while ......do ......end where

while 循环语句：

格式：

wile 条件 do

sql语句

end while

(2) loop ......end loop

(3) repeat.......until ,,,,,end repeat

============================================

造数据场景：

1、通过存储过程在一个指定的空表中插入10条数据？

案例：

drop table ff ;
CREATE table ff(id int (10),age int(10));
select * from ff ;
delimiter //
drop procedure if exists hz10; #增强健壮性，判断是否存在指定存储过程，如果存在就删除；
create procedure hz10()
BEGIN
DECLARE i int DEFAULT 0 ;
while (i<10) DO
INSERT into ff(id )VALUES (i);
set i=i+1;
end WHILE;
select * from ff ;
END
//

call hz10()

场景2：插入指定数据的行数

delimiter //
DROP table if EXISTS ff ;
drop procedure if exists hz10; #增强健壮性，判断是否存在指定存储过程，如果存在就删除；
CREATE table ff(id int (10),age int(10));
create procedure hz10( in x int )
BEGIN
DECLARE i int DEFAULT 0 ;
while (i<x) DO
INSERT into ff(id )VALUES (i);
set i=i+1;
end WHILE;
select * from ff ;
END
//

call hz10(5)

场景三：指定插入的数据，并将已有的数据统计，在已有的数据基础上在添加

delimiter //
DROP table if EXISTS ff ;
drop procedure if exists hz10; #增强健壮性，判断是否存在指定存储过程，如果存在就删除；
CREATE table ff(id int (10),age int(10));
create procedure hz10( in x int )
BEGIN
DECLARE i int DEFAULT (select Count(*) from ff ) ;
while (i<x) DO
INSERT into ff(id )VALUES (i+1);
set i=i+1;
end WHILE;
select * from ff ;
END
//

call hz10(100)

==============================================

面试题：

1、你会存储码？

2、存储的结构 ?

3、在工作中你们用存储做什么？造数据

4、你会写存储吗？

============================================

if 语句

if 的单分支

if 条件 then

执行sql1

else

执行sql2

end if ；

案例：

delimiter //
create procedure hz11( in a int )

BEGIN
if a>10 THEN
SELECT * from emp ;
ELSE
SELECT * from dept;
end if;
END
//

call hz11(9)

一个if 对应一个end if

===========================

if 多分支

在if 判断语句汇总，有几个判断分支if，就有几个结束语 end if ；

if 条件 1 then

执行sql1

else if 条件2 then

执行sql2

else if 条件3 then

执行sql3

end if ；

end if；

end

案例：

create procedure hz12( in a int )
BEGIN
if a>100 THEN
SELECT * from emp ;
ELSE if a<10 then
SELECT * from dept;
else if a=50 then
select * from student2 ;
ELSE
SELECT * from ff ;
end IF;
end IF;
end if;
END
//

call hz12(9)

=========================================

面试题：根据student学生表去写
1.当传入的参数（大于0）小于等于表里面数据的条数时，则根据分组显示班级的总成绩
2.当传入的参数大于表里面数据的条数时，则统计表里面的数据有多少条
3.当传入其他，则查询表里面的所有数据

方法1：

delimiter //
create procedure hz09( in x int )
BEGIN
DECLARE i int DEFAULT (select Count() from student2 );
if x<=i and x>0 THEN
SELECT sum(chinese+math+english) from student2 group by class;
ELSE if x>i then
SELECT count() from student2;
else
select * from student2;
end if;
end if;
END
//
call hz09(6)

方法2：

select * from xsb;
delimiter //
create procedure student1( in a int )
BEGIN
if a>0 and a<=(select count(id) from xsb) THEN
SELECT sum(english+chinese+math) from xsb GROUP BY class ;
ELSE if a>(select count(id) from xsb) then
SELECT count(id) from xsb;
ELSE
SELECT * from xsb ;
end IF;
end IF;
END
//

posted @ 2024-10-30 11:09 胡豪阅读(9) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

公告

昵称：胡豪
园龄： 4个月
粉丝： 0
关注： 1

2025年3月

日

一

二

三

四

五

六

huhao20001007

10.30 索引，外键，存储过程

父表

子表

父表

子表

存储过程

公告