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'