SQL命令(三)
数据完整性约束
1)数据完整性约束包含:实体完整性、参照完整性、用户定义完整性。
CREATE TABLE table(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,*//主键约束(主键唯一,不能为空)候选键约束(候选键值唯一,不能为空)
name CHAR(50) NOT NULL,
sex,CHAR(1) NOT NULL DEFAULT 0,
age INT NOT NULL,
major CHAR(50) NOT NULL,
contact CHAR(50) NULL
CHECK(id IN (SELECT id FROM table)) *//用户完整性约束(用户应用所涉及的数据限制要求)包含非空约束、CHECK约束、触发器。
UNIQUE KEY index_name(sex)
Or
CONSTRAINT a PRIMARY KEY(id)*//命名主键约束名为a
CONSTRAINT b FOREIGN KEY(id2) * // 外键 (参照完整性 = 表间主键外键关系) 命名外键约束名为b
REFERENCES othertable(id2)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);
更新完整新约束
ALTER TABLE table
ADD FOREIGN KEY index_name(id)
or
DROP PRIMARY KEY
DROP FOREIGN KEY 外键名 //(外键约束名)
2)表维护语句(mysql数据库优化)
查看索引散列程度:
ANALYZE TABLE table; * //计算散列程度+更新
SHOW INDEX FROM table;
CHECKSUM TABLE table [QUICK |EXTENDED]*//计算表校验和
CHECK TABLE table; *//检查表错误
查看:
SELECT TABLE_NAME,TABLE_TIME
FROM INFOMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'table' //表名称
AND TABLE_SCHEMA = 'database' //数据库名称
恢复表 :
REPAIR TABLE table
优化表(整理碎片特别是VARCHAR、BLOB、TEXT类型):
OPTIMIZE NO_WRITE_TO_BINLOG TABLE table; //插入,更新,删除会产生数据文件碎片
2. 触发器(指定关联到一个表的数据库对象,当一个表的特定事件(INSERT UPDATE DELETE)时,会激活触发器)
创建触发器 :
CREATE TRIGGER <触发器名> <BEFORT | AFTER> <INSERT|UPDATE|DELETE> ON <表名> FOR EACH ROW <触发器主体>
CREATE TRIGGER trig_name AFTER INSERT //插入后触发
ON table
FOR EACH ROW SET@a = NEW.table.id //使用NEW虚拟新表,即插入后的表
*同一个表不能拥有两个具有相同触发时刻和事件的触发器
*每个表最多支持6个触发器(3*2)
例子:
CREATE TRIGGER trig_name BEFORE UPDATE ON table FOR EACH ROW SET NEW.id = OLD.age+1;
删除触发器:DROP TRIGGER IF EXISTS <数据库名> <触发器名>
3.事件(指定时刻才被执行,与表不关联)
查看事件调度器
SHOW VARIABLES LIKE 'EVENT_SCHEDULER'; or SELECT@@EVENT_SCHEDULER; //查看事件调度器状态
开启调度器:
SET GLOBAL EVENT_SCHEDULER = TRUE|1|ON) *//(默认是关闭的,需要手动打开)
创建事件:
格式
CREATE EVENT <事件名> ON SCHEDULER <schedule> DO <事件主体>(schedule包含AT,EVERY,分别设定事件发生的时间和周期性发生的事件,STARTS代表开始事件 ENDS表示结束时间)
CREATE EVENT IF NOT EXISTS event_name
ON SCHEDULER EVERY 1 MONTH //每个月会执行事件
DO
INSERT INTO table
VALUES(NULL,......);
修改事件
格式:
ALTER EVENT <事件名> [RENAME TO <新事件名>] [DO <事件主体>][ENABLE|DISABLE]
ALTER EVENT event_name ENABLE|DISABLE; //开启,关闭事件
ALTER EVENT event_name RENAME TO event_name2;
删除事件:
DROP EVENT IF EXISTS event_name;