8.1.11.1 生成SQL语句

MySQL Workbench 可用于生成 SQL,最典型的是作为 INSERT语句或SELECT 语句。

以下是MySQL Workbench中生成SQL语句的常用方法。

笔记

所有 MySQL Workbench 导出选项都包括导出为 SQL 的选项。

schema在架构视图中 右键单击 a 后的上下文菜单选项 ,以该sakila列为例。

创建语句

CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET latin1 */;

姓名

`sakila`

table在架构视图中 右键单击 a 后的上下文菜单选项 ,以该sakila.actor列为例:

姓名(简称)

`actor`

名称(长)

`sakila`.`actor`

全选语句

SELECT `actor`.`actor_id`,
    `actor`.`first_name`,
    `actor`.`last_name`,
    `actor`.`last_update`
FROM `sakila`.`actor`;

选择参考

SET @actor_id_to_select = <{row_id}>;
SELECT film_actor.*
    FROM film_actor, actor
    WHERE `actor`.`actor_id` = `film_actor`.`actor_id`
          AND actor.actor_id = @actor_id_to_select;
SELECT actor.*
    FROM actor
    WHERE actor.actor_id = @actor_id_to_select;

插入语句

INSERT INTO `sakila`.`actor`
  (`actor_id`,
  `first_name`,
  `last_name`,
  `last_update`)
VALUES
  (<{actor_id: }>,
  <{first_name: }>,
  <{last_name: }>,
  <{last_update: CURRENT_TIMESTAMP}>);

更新声明

UPDATE `sakila`.`actor`
SET
`actor_id` = <{actor_id: }>,
`first_name` = <{first_name: }>,
`last_name` = <{last_name: }>,
`last_update` = <{last_update: CURRENT_TIMESTAMP}>
WHERE `actor_id` = <{expr}>;

删除语句

DELETE FROM `sakila`.`actor`
WHERE <{where_expression}>;

删除引用

-- All objects that reference that row (directly or indirectly)
-- will be deleted when this snippet is executed.
-- To preview the rows to be deleted, use Select Row Dependencies
START TRANSACTION;
-- Provide the values of the primary key of the row to delete.
SET @actor_id_to_delete = <{row_id}>;

DELETE FROM film_actor
    USING film_actor, actor
    WHERE `actor`.`actor_id` = `film_actor`.`actor_id`
          AND actor.actor_id = @actor_id_to_delete;
DELETE FROM actor
    USING actor
    WHERE actor.actor_id = @actor_id_to_delete;
COMMIT;

创建语句

CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

column在架构视图中 右键单击 a 后的上下文菜单选项 ,以该sakila.actor.first_name列为例:

姓名(简称)

`first_name`

名称(长)

`actor`.`first_name`

选择列语句

SELECT `first_name` FROM `sakila`.`actor`;

插入语句

INSERT INTO `sakila`.`actor`
(`first_name`)
VALUES
(<{first_name}>);

更新声明

UPDATE `sakila`.`actor`
SET
`first_name` = <{first_name}>
WHERE <{where_expression}>;

在结果视图中右键单击 a 后的上下文菜单选项 ,以表中的field记录 #1sakila.actor为例:

复制行(带名称)

# actor_id, first_name, last_name, last_update
'1', 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33'

复制行(带名称,不带引号)

# actor_id, first_name, last_name, last_update
1, PENELOPE, GUINESS, 2006-02-15 04:34:33

复制行(制表符分隔)

1	PENELOPE	GUINESS	2006-02-15 04:34:33

复制字段

'GUINESS'