Mysql数据库高级特性【一】

目录

主键(primary key)

1.主键的特性：唯一标识一条记录，不能有重复值

2.主键的创建

练习测试

3.主键的修改

外键（foreign key）

1.外键的特性

2.外键的创建

3.外键的修改

索引（index）

索引的特性

索引的类型

索引的创建

索引的修改

check约束（check constraint）

约束的特性

约束的查看

约束的创建

3.约束的修改

存储过程（stored procedure）

存储过程的作用

存储过程的的特性

存储过程创建

存储过程修改

存储过程调用

存储过程查看

触发器（trigger）

触发器的作用

触发器的特性

触发器的创建

触发器的修改

触发器的类型

触发器中的特殊变量

启用和禁用触发器

查看触发器

---

 

主键(primary key)

1.主键的特性：唯一标识一条记录，不能有重复值

一个表只能有一个主键，可以是单列或者多列的组合

自动定义为NOT NULL

2.主键的创建

create table users (id INT AUTO_INCREMENT,username VARCHAR(50) NOT NULL,PRIMARY KEY (id));

其中，AUTO_INCREMENT #按顺序自动增长，自动增长值为1

          NOT NULL         #不能为空

          VARCHAR(50)         #数据类型为可变长度字符串，最大长度为五十

          PRIMARY KEY (id)         #主键约束，定义id为表的主键

#可以用describe users查看创建的表结构，在key列中为PRI为设置的主键，若是FK则为外键

练习测试

插入一些数据

Inset users(username) value(‘lisi’);

Select * from users;         #查看一下，id是否自动排序为1，多插入几条查看是否自动排序

跳序插入可以执行

inset users(id,username) value(5,’jack’);

查看可以发现，此条的顺序直接跳到了5，若再插入数据，则从5往后排序。

3.主键的修改

#mysql不直接支持修改现有的主键约束，但可以删除并重新创建它

删除列

ALTER TABLE users DROP PRIMARY KEY;

#alter为修改表结构的命令

（注：MySQL不允许直接删除含有主键约束的列，需要先删除主键约束，然后再删除该列

删除主键约束        ALTER TABLE users DROP PRIMARY KEY;

删除该列         ALTER TABLE users DROP PRIMARY column_name）

外键（foreign key）

1.外键的特性

确保子表中的数据在父表中有对应值，可以实现级联更新和删除

2.外键的创建

Create table orders(order_id INT AUTO_INCREMENT,user_id INT,PRIMARY KEY (oder_id),FOREIGM KEY (user_id) REFERENCES USERS(id));

其中：

order_id INT AUTO_INCREMENT：定义一个名为"order_id"的列，数据类型为整数（INT）。AUTO_INCREMENT该列的值会自动递增，用于生成唯一的标识符。

PRIMARY KEY (order_id)：定义主键约束，指定"order_id"列作为表的主键

FOREIGN KEY (user_id) REFERENCES USERS(id)：定义外键约束，指定"user_id"列作为外键，这个外键引用了另一个名为"USERS"的表的"id"列，意味着在"orders"表中，"user_id"列的值必须存在于"USERS"表的"id"列中，这建立了两个表之间的关联关系，确保数据的一致性和完整性。REFERENCES是参考的意思。

3.外键的修改

#通常来说，外键是在创建表时一同定义的，修改外键需要先删除再重新创建

删除

ALTER TABLE orders DROP FOREIGN KEY fk_user_id;

创建

ALTER TABLE order ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES new_users(id)

• 索引（index）

1. 索引的特性

• 通过快速定位数据来提高查询速度，可以是唯一的或非唯一的

1. 索引的类型

按数据结构分类：B+tree索引、hash索引、full-text索引

按物理存储分类：聚簇索引、二级索引（辅助索引）

按字段特性分类：主键索引，普通索引，前缀索引

按字段个数分类：单列索引、联合索引（复合索引、组合索引）

3.查看表中所有索引

show index from st1\G;

1. 索引的创建

Create index idx_st1(要创建的索引名) on st1(st_id)（st1指定要在哪个表上创建索引，st_id定义了索引所基于的列）

1. 索引的修改

#索引不能直接修改，但可以重建

删除

DROP INDEX idx_username ON users;

创建

CREATE INDEX idx_username ON users (username(10));

• check约束（check constraint）

1. 约束的特性

• 保证列数据满足特定条件，确保数据的一致性和完整性规则，在mysql 8.0.16及以后的版本中可用

#一般使用业务代码写

1. 约束的查看

   select * from table_constraints where table_name=’users’\G;

2. 约束的创建

   ALTER TABLE users ADD CONSTRAINT chk_username_nonempty CHECK (username <> ‘’);

   其中 ADD CONSTRAINT：用于向表中添加新的约束条件的语法部分

                 chk_username_nonempty：是新添加的约束的名称，可自行定义

                CHECK (username <> '')：定义了约束的具体条件。在这个例子中，创建了一个检查约束，它规定"username"列的值不能为一个空字符串（即''）

3.约束的修改

#约束创建后无法直接修改，需要删除并重新定义

删除约束

ALTER TABLE users DROP CONSTRAINT chk_username_nonempty;

定义约束

ALTER TABLE users ADD CONSTRAINT chk_username_nonempty CHECK (username <> '' AND username IS NOT NULL);

• 存储过程（stored procedure）

1. 存储过程的作用

• 隐藏SQL语句增强安全性，保证数据完整性，维护和更新便利，代码复用和模块化，简化复杂操作，提高性能，降低网络带宽的使用

1. 存储过程的的特性

• 封装一段SQL语句，便于复用和维护，可以包含业务逻辑

1. 存储过程创建

DELIMITER //         #更改mysql语句的结束符为//
CREATE PROCEDURE GetAllUsers()         #定义存储过程名为GetAllUsers
BEGIN         #标志存储过程开始
    SELECT * FROM users;
END //         #存储过程结束
Mysql>DELIMITER ;         #将mysql语句结束符恢复到“;”

1. 存储过程修改

#必须先删除再重新创建

DELIMITER //
DROP PROCEDURE IF EXISTS GetAllUsers;
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT username FROM users;
END //
DELIMITER ;

存储过程删除

DROP PROCEDURE IF EXISTS GetAllUsers;

存储过程调用

CALL GetAllUsers();

存储过程查看

SHOW PROCEDURE status\G;         #查看所有

SHOW PROCEDURE status where Db=’databaseName’\G; #查看指定数据库的存储过程

SHOW CREATE procedure GetAll\G; #查看指定存储过程

• 触发器（trigger）

1. 触发器的作用

• 自动响应事件，数据完整性保证，审计和日志记录，业务逻辑自动化，数据同步和复制，增强安全性

1. 触发器的特性

• 在INSERT、UPDATE或DELETE操作之前或之后自动执行，用于自动化维护数据的完整性或实施业务规则

1. 触发器的创建

DELIMITER // #更改mysql语句的结束符为//
CREATE TRIGGER before_user_insert BEFORE INSERT ON users #定义一个新的触发器，名为before_user_insert，并定义触发器的激活时间点和事件。在这个例子中，触发器将在执行INSERT操作之前被激活，并且该操作是针对users表的。
FOR EACH ROW #触发器将在每次插入新行时被调用。对于批量插入的情况，触发器会为每一行单独执行
BEGIN #触发器主体的开始
    SET NEW.created_at = NOW(); #为即将插入的新行设置created_at字段的值为当前时间（NOW()函数返回当前日期和时间）。NEW是一个特殊变量，用于引用正在被插入的新行。
END;
DELIMITER ; #将语句结束符改回默认的分号

1. 触发器的修改

#必须先删除再重新创建

DELIMITER //
DROP TRIGGER IF EXISTS before_user_insert;
CREATE TRIGGER before_user_insert BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END;
DELIMITER ;

触发器的删除

DROP TRIGGER before_user_insert;

1. 触发器的类型

• BEFORE INSERT：在插入新行之前执行。

AFTER INSERT：在插入新行之后执行。

BEFORE UPDATE：在更新行之前执行。

AFTER UPDATE：在更新行之后执行。

BEFORE DELETE：在删除行之前执行。

AFTER DELETE：在删除行之后执行。

1. 触发器中的特殊变量

OLD：在BEFORE UPDATE和AFTER UPDATE触发器中，用于引用被更新前的行值。

NEW：在BEFORE INSERT、AFTER INSERT、BEFORE UPDATE和AFTER UPDATE触发器中，用于引用被插入或更新后的行值。

#在DELETE触发器中，OLD变量可以用来引用被删除的行值。

1. 启用和禁用触发器

• ALTER TRIGGER trigger_name ENABLE|DISABLE; #ENABLE启用，DISABLE禁用

1. 查看触发器

• SHOW TRIGGERS\G;