MySQL-3

前言

对MySQL概念常用命令的总结第三部分:
创建表
修改表
删除表
视图
存储过程
游标
触发器
管理事务处理

创建表

1
2
3
4
5
6
7
CREATE TABLE 表名
(
列名1 数据类型 NOT NULL(是否允许为NULL) AUTO_INCREMENT,
列名2 数据类型 NOT NULL(是否允许为NULL) DEFAULT 1,
...
PRIMARY_KEY(列名1)
)ENGINE=InnoDB;

若不指定NOT NULL则NULL是默认设置,允许列的值为NULL,且插入时不给出具体值将设置为NULL。主键不允许为NULL。DEFAULT指定默认值。PRIMARY_KEY设定主键,可以是多个列。ENGINE指定数据引擎,默认为InnoDB。引擎可以混用,但外键不可跨引擎。
AUTO_INCREMENT告诉MySQL本列每行增加一行时自动增量。每个表只允许一个AUTO_INCREMENT列,且必须被索引(让它成为主键即索引)。INSERT指定一个值将覆盖AUTO_INCREMENT,且之后根据此递增。
SELECT last_insert_id()获得最后一个AUTO_INCREMENT的值。

修改表

对表的属性进行更改,而非对数据本身操作(UPDATE、DELETE FROM)。
ALTER TABLE 表名 ADD 列名 数据类型 ...增加一列。
ALTER TABLE 表名 DROP COLUMN 列名删除一列。
RENAME TABLE 旧表名1 TO 新表名1,旧表名2 TO 新表名2;重命名表。

1
2
3
ALTER TABLE 表名1
ADD CONSTRAINT 外键名
FOEIGIN KEY (列名) REFERENCES 表名2 (列名)

定义外键,将限制外键值的插入。
对表复杂的修改需要手动删除和重新建表,用INSERT SELECT拷贝数据。

删除表

DROP TABLE 表名
没有确认,永久删除。

视图

视图是虚拟的表,是对SELECT查询结果的一个包装,其本身并不包含数据,通常与复杂的联结使用,可以重用SQL语句。ORDER BY可以用在视图中,但视图的SELECT语句包含ORDER BY时,会覆盖视图中的ORDER BY。

创建视图

CREATE VIEW 视图名 AS SELECT语句,视图一般用于复杂的联结、格式化数据(用CONCAT(...)连接)、过滤数据、计算字段。

DESCRIBE 视图名,查看视图的字段。
SHOW TABLES中包含创建的视图。
SHOW CREATE VIEW 视图名,查看创建视图的语句。
DROP VIEW 视图名,删除视图。

更新视图

对视图可以进行INSERT、UPDATE、DELETE FROM对数据进行操作,其实质是对基表中的数据进行操作。能够操作的前提必须是更新的基数据必须是确定的。

存储过程

存储过程是为以后的使用而保存的一条或多条MySQL语句的集合。使用比单条SQL语句速度更快。

使用存储过程

1
2
3
4
CALL 存储过程名称(@变量名1,
@变量名2);

SELECT @变量名1,@变量名2;

未声明的变量传参进入存储过程需要用@。对于已经声明的变量,调用传参不需要加@。

创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
--将“//”作为新的语句分隔符
DELIMITER //

CREATE PROCDURE 存储过程名(
... 参数列表
)COMMENT '...'引号内的内容将在SHOW PROCEDURE STATUS中显示
BEGIN
... ;
END //

--恢复“;”为语句分隔符
DELIMITER ;

—后表示注释。
SHOW CREATE PROCDURE 存储过程名查看存储过程创建的语句。
SHOW PROCDURE STATUS [LIKE 存储过程名]列出所有存储过程,可用LIKE过滤。

删除存储过程

DROP PROCDURE 存储过程名,删除存储过程。

使用参数

变量只能是一个数值,不允许是多行数据的数据集。
参数列表中:
OUT/IN/INOUT 变量名 数据类型
OUT表示传出(与调用时@变量名的顺序对应),IN表示传入(在调用时不需要@了),INOUT表示传入传出。
在函数体中用:
SELECT 结果 INTO 变量名 [FROM ... WHERE ...];将结果返回给OUT变量。IN变量可以在函数体中直接使用。
DECLARE 变量名 数据类型 [DEFAULT 默认值]声明局部变量,可设置默认值。

条件判断语句:

1
2
3
IF BOOLEAN型变量名 THEN
...变量为真时执行
END IF;

游标

利用游标可以对数据一行一行地处理,主要应用于与用户的交互式应用(滚动屏幕数据)。创建的游标不是一条SELECT语句,而是代表被检索出来的结果集。

游标只能在存储过程中(BEGIN … END;)使用。按照:定义->打开->使用->关闭的顺序。注意在存储过程END;后游标会自动关闭并清除定义。

创建游标

1
2
3
DECLARE 游标名 CURSOR
FOR
SELECT...;

打开和关闭

OPEN 游标名
CLOSE 游标名
关闭会释放内存资源,只要不清除定义,之后可以再次打开。

使用游标数据

FETCH 游标名 INTO 变量名将一行游标检索出的数据(根据游标定义时的SELECT语句检索)赋值给变量。每次执行FETCH后游标会指向下一行数据。
游标一般与循环控制结合:

1
2
3
4
5
DECLARE done BOOLEAN DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
REPEAT
FETCH...
UNTIL done END REPEAT;

REPEAT内反复执行直到done为真时跳出循环。
第二行定义了CONTINUE HANDLER,它用于在条件出现时执行的代码。即当出现SQLSTATE ‘02000’时,表示没有更多的行可以循环,不能继续,执行SET done=1.
SET 变量名=值用来赋值。

触发器

用于响应INSERT、DELETE、UPDATE语句(只有这三个)时自动执行某个MySQL语句。只有表能使用触发器,视图和临时表不可以,每个表最多6个触发器(三个语句之前和之后)。

创建触发器

每个数据库的触发器名必须唯一。

1
2
CREATE TRIGGER 触发器名 AFTER INSERT ON 表名
FOR EACH ROW SELECT 'Product added';

触发器可在一个操作之前或者之后执行。AFTER INSERT表示在插入后执行。FOR EACH ROW表示对每个插入行执行。product表每插入一行数据,显示文本‘Product added’。

删除触发器

DROP TRIGGER 触发器名

使用

INSERT 触发器

可以用BEFORE/AFTER INSERT表示插入之前/之后执行触发器。

INSERT触发器内可以使用名为NEW虚拟表,包含将要被插入的行,在BEFORE INSERT中可以对其进行,来改变插入的值。对于AUTO_INCREAMENT列,NEW在INSERT执行前为0,执行后包含自动生成的值。

DELETE 触发器

可以在DELETE前后访问名为OLD的虚拟表,访问被删除的行,数据只读,不能更新。一般用在BEFORE DELETE删除前将删除的数据存档。

BEGIN...END;语句块可以容纳多个触发器语句。

UPDATE 触发器

OLD表存放更新前的值,只读。NEW表存放新更新的值。在BEFORE UPDATE内更新NEW中的值来改变将要更新的值。

MySQL触发器不支持CALL语句,只能把代码复制到触发器内。

管理事务处理

保证成批的MySQL语句要么完全执行,要么完全不执行。

事务:一组SQL语句
回退:撤销指定SQL语句
提交:将未存储的SQL语句写入数据库表
保留点:可以对它发布回退

START TRANSACTION;标识事务的开始。

ROLLBACK;回退,只能在事务处理内使用,将撤销前一个COMMIT 或 START TRANSACTION后所有的INSERT、UPDATE、DELETE,不能撤销SELECT和CREATE、DROP。

MySQL内是默认隐含提交,即提交操作是自动进行的。但事务处理块中提交必须用COMMIT;事务内所有语句不出错才会成功提交,否则自动回退(ROLLBACK)事务内所有操作。COMMIT或ROLLBACK后自动关闭事务,回到隐含提交。

在事务处理块中设置保留点可以支持回退到指定保留点。
SAVEPOINT 保留点名;设置
ROLLBACK TO 保留点名;回退
保留点在ROLLBACKCOMMIT后自动释放。

SET autocommit=0;指示MySQL不自动提交更改。只针对每个连接。

您的支持是我创造源源不断地动力