db

Mysql中的索引、外键、视图、存储过程、触发器和事务

2019-11-06  本文已影响0人  iDevOps
索引

索引是数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

1.普通索引，最基本的索引，不具备唯一性，就是加快查询速度

# 创建表时添加索引
create table person(
  id int(4),
  name varchar(20),
  passwd varchar(20),
  index psd (passwd)
)
注: 可以使用key代替index, psd是索引名，索引名称可以省略，省略的话字段名作为索引名

# 已经创建的表，使用alter为表添加索引
alter table person add index 索引名称 (name, passwd);

# 查看索引
MariaDB [test]> desc person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(4)      | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| passwd | varchar(20) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

如果Key是MUL, 就是一般性索引，该列的值可以重复

# 删除索引
alter table person drop key psd;

2.唯一索引
与普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，用来约束内容，字段值只能出现一次。应该加唯一索引。唯一性允许有NULL值<允许为空>

# 创建表时加唯一索引
create table 表名(
    列定义：
    unique key 索引名 （字段）;
)
# Key是UNI

# 修改表时添加索引
MariaDB [test]> alter table person add unique(name);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> desc person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(4)      | YES  |     | NULL    |       |
| name   | varchar(20) | YES  | UNI | NULL    |       |
| passwd | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.主键索引
查询数据库，按主键查询是最快的，每个表只能有一个主键列，可以有多个普通索引列。主键列要求列的所有内容必须唯一，不允许为空。

# 创建表时创建主键索引
create table person( 
  id int(4) not null auto_increment, 
  name varchar(20) default null,
  primary key(id)
);

# 创建表后创建主键索引
alter table personchange id id int(4) not null primary key auto_increment;

# 查看索引
MariaDB [test]> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

4.复合索引
索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引

# 联合索引
MariaDB [test]> create table person(
    -> id int(4) not null,
    -> name varchar(20),
    -> primary key(id, name));
Query OK, 0 rows affected (0.02 sec)
# 插入数据
MariaDB [test]> insert into person values (1, "aaa");
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> insert into person values (1, "aaa");
ERROR 1062 (23000): Duplicate entry '1-aaa' for key 'PRIMARY'
注: 插入同样的数据就报错

5.全文索引
全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果
mysql在数据量较大的情况下，使用select * from tbname where name like '%搜索内容%'这类通配符搜索时，不通过索引，会直接扫描全表，数据库压力大
mysql在3.2开始支持全文检索，但是无法正确支持中文
mysql在5.7.6开始，内置了ngram全文检索插件，用来支持中文分词
全文检索只能用在varchar text字段上

# 创建全文索引
create table 表名（
    列定义，
    fulltext key 索引名 （字段）;
）

注: mysql自带的全文索引只支持MyISAM数据库引擎
所以一般会使用第三方软件进行全文索引
http://sphinxsearch.com/

索引使用原则
1、索引并非越多越好
2、数据量不大不需要建立索引
3、列中的值变化不多不需要建立索引
4、经常排序（order by 字段）和分组（group by 字段）的列需要建立索引
5、唯一性约束对应使用唯一性索引

外键约束

外键约束就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整，关连性更强
例如，有二张表，一张是用户表，一张是订单表，如果我删除了用户表里的用户，那么订单表里面与这个用户有关的数据，就成了无头数据了，不完整了。
如果有外键的话，可以不让用户删除数据，或者删除用户的话，通过外键同样删除订单表里面的数据，这样也能让数据完整。

视图

视图就是一个存在于数据库中的虚拟表。
视图本身没有数据，只是通过执行相应的select语句完成获得相应的数据

# 创建视图
create view view_name as select * from ....
# 查看视图
show create view view_name;
# 查询视图
select * from view_name where id = 1
# 修改视图
alter view view_name select * from .....
# 更新视图数据
update view_name set name = "aaa" where id = 1
# 删除视图
drop view 视图名
存储过程

大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单。经常会有一个完整的操作需要多条才能完成。存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

# 定义存储过程
# 切换数据库
use test;
# 修改分隔符
delimiter // # 我这里用//, 也可以使用其他符号
# 创建存储过程
CREATE  PROCEDURE selUser()
begin
select * from user;
end %
# 不用的话记得把分隔符改回来
delimiter ;

# 调用存储过程
call selUser();

In参数

mysql> use test;
Database changed
mysql> delimiter //

# 创建存储过程
mysql> create procedure selUserById(in uid int)
    -> begin
    -> select * from user where id = uid;
    -> end //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;

# 调用存储过程
mysql> call selUserById(1);
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+

# 使用变量的方式调用
mysql> set @uid = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> call selUserById(@uid);
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.04 sec)

Out参数
不读取外部变量值，在存储过程执行完毕后保留新值

mysql> delimiter //
mysql> create procedure testOut(out o int)
    -> begin
    -> set o=2;
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> set @o=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @o;
+----+
| @o |
+----+
|  1 |
+----+
1 row in set (0.04 sec)

mysql> select @o;
+----+
| @o |
+----+
|  1 |
+----+
1 row in set (0.04 sec)

mysql> call testOut(@o);
Query OK, 0 rows affected (0.00 sec)

mysql> select @o;
+----+
| @o |
+----+
|  2 |
+----+
1 row in set (0.04 sec)

InOut参数
读取外部变量，在存储过程执行完后保留新值
传进来，又传出去

mysql> use test;
Database changed
mysql> delimiter //
mysql> create procedure testInOut(inout io int)
    -> begin
    -> select io;
    -> set io=2;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @io=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call testInOut(@io);
+----+
| io |
+----+
|  1 |
+----+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @io;
+-----+
| @io |
+-----+
|   2 |
+-----+
1 row in set (0.04 sec)

mysql> delimiter //
mysql> create procedure test(n int)
    -> begin
    -> set @x=0;
    -> repeat set @x=@x+1;  # 循环
    -> insert into test values (@x);
    -> until @x>n
    -> end repeat;
    -> end //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call test(5);
Query OK, 6 rows affected (0.03 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.04 sec)

# 变量定义， datatype数据类型
declare name datatype 
# 变量赋值
set name = 表达式

mysql> delimiter //
mysql> create procedure selUser()
    -> begin
    -> declare name varchar(200);
    -> set name=(select * from user);
    -> select name;
    -> end //
mysql> delimiter ;
mysql> call selUser;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.06 sec)

if-then-else

# 用法1
if a=0 then
....
end if;

# 用法2
if a=1 then
.....
else
....
end if;

case

case a
when 0 then
.....;
when 1 then
....;
else
....;
end case;

3.循环语句
while

while a>5 do
....;
end while;

repeat

begin
declare a int;
set a=0;
repeat
insert into test values(a);
set a=a+1;
until a>=5
end repeat;
end //

loop

begin
declare a int;
set a=0;
LOOP_LABLE:loop
insert into test values (a);
set a=a+1;
if a>=5 then
leave LOOP_LABLE;
end if;
end loop;
end;
//

# 查看存储过程内容
show create procedure procedure_name;

# 查看所有存储过程
show procedure status;

mysql> drop procedure test;
Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure if exists test;
Query OK, 0 rows affected (0.00 sec)
触发器

生产比较少用触发器

# 语法
create  trigger 触发器名称  触发的时机  触发的动作  on 表名 for each row 触发器状态。

触发器名称:  自己定义
触发的时机： before /after  在执行动作之前还是之后
触发的动作 :指的激发触发程序的语句类型<insert ,update,delete>
each row:监控每一行

触发器创建语法四要素
1.监视地点(table) 
2.监视事件(insert/update/delete) 
3.触发时间(after/before) 
4.触发事件(insert/update/delete)

# 删除user表的记录就清空test表的数据
mysql> delimiter //
mysql> create trigger delUser after delete on user for each row
    -> delete from test;
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> delete from user where id=1;

# 查看所有触发器
show triggers;
# 查看触发器创建过程
 show create trigger delUser;

drop trigger delUser;
事务
set autocommit = 1;
0: 禁止自动提交
1: 开启自动提交

start transaction;
update books set bName="HA" where bId=1;
update books set bName="LB" where bId=2;
commit;

start transaction;
update books set bName="HA" where bId=1;
update books set bName="LB" where bId=2;
rollback;
上一篇下一篇

猜你喜欢

热点阅读