MySQL :: MySQL Workbench 手册 :: 8.1.11.1 生成 SQL 语句

MySQL Workbench 手册 / ... / 生成 SQL 语句

8.1.11.1 生成 SQL 语句

MySQL Workbench 可用于生成 SQL，最常见的是作为 INSERT 语句或 SELECT 语句。

以下常见方法用于在 MySQL Workbench 中生成 SQL 语句。

注意

所有 MySQL Workbench 导出选项都包含以 SQL 形式导出。

在模式视图中右键单击 schema 后的上下文菜单选项，使用 sakila 列作为示例。

创建语句

Press CTRL+C to copy
 
CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET latin1 */;

名称

Press CTRL+C to copy
 
`sakila`

在模式视图中右键单击 table 后的上下文菜单选项，使用 sakila.actor 列作为示例

名称（简短）

Press CTRL+C to copy
 
`actor`

名称（完整）

Press CTRL+C to copy
 
`sakila`.`actor`

选择所有语句

Press CTRL+C to copy
 
SELECT `actor`.`actor_id`,
    `actor`.`first_name`,
    `actor`.`last_name`,
    `actor`.`last_update`
FROM `sakila`.`actor`;

使用引用选择

Press CTRL+C to copy
 
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;

插入语句

Press CTRL+C to copy
 
INSERT INTO `sakila`.`actor`
  (`actor_id`,
  `first_name`,
  `last_name`,
  `last_update`)
VALUES
  (<{actor_id: }>,
  <{first_name: }>,
  <{last_name: }>,
  <{last_update: CURRENT_TIMESTAMP}>);

更新语句

Press CTRL+C to copy
 
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}>;

删除语句

Press CTRL+C to copy
 
DELETE FROM `sakila`.`actor`
WHERE <{where_expression}>;

使用引用删除

Press CTRL+C to copy
 
-- 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;

创建语句

Press CTRL+C to copy
 
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 后的上下文菜单选项，使用 sakila.actor.first_name 列作为示例

名称（简短）

Press CTRL+C to copy
 
`first_name`

名称（完整）

Press CTRL+C to copy
 
`actor`.`first_name`

选择列语句

Press CTRL+C to copy
 
SELECT `first_name` FROM `sakila`.`actor`;

插入语句

Press CTRL+C to copy
 
INSERT INTO `sakila`.`actor`
(`first_name`)
VALUES
(<{first_name}>);

更新语句

Press CTRL+C to copy
 
UPDATE `sakila`.`actor`
SET
`first_name` = <{first_name}>
WHERE <{where_expression}>;

在结果视图中右键单击 field 后的上下文菜单选项，使用 sakila.actor 表中的记录 #1 作为示例

复制行（带名称）

Press CTRL+C to copy
 
# actor_id, first_name, last_name, last_update
'1', 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33'

复制行（带名称，未引用）

Press CTRL+C to copy
 
# actor_id, first_name, last_name, last_update
1, PENELOPE, GUINESS, 2006-02-15 04:34:33

复制行（制表符分隔）

Press CTRL+C to copy
 
1	PENELOPE	GUINESS	2006-02-15 04:34:33

复制字段

Press CTRL+C to copy
 
'GUINESS'

上一个主页向上下一个