下载workbench
MySQL :: Download MySQL Workbench
安装过程不用修改,一直点击next即可
打开软件后
点击Schemas可以看到Windows系统下的各种数据表
我希望修改的是auto_assembly_system数据表
如下图写入sql语句后,点击左上角的闪电即可运行
workbench中创建表格
第一种方法,使用SQL语句
简单介绍以下SQL的基本语句,分为以下几类
DDL(data definition language)
create tabel/alter table/drop table
create view/drop view
create index/drop index
DML(data manipulation language)
insert/delete/updata
DCL(data control language)
grant/revoke
第二种方法,点击新建
右键Table,然后点击Create Table。之后按图选择建立数据库的要求
然后点击两次Apply,即可发现左边的框中出现了刚刚创建的数据库
接下来就可以创建这个数据库下的数据表并定义各项属性了。其中PM表示主键,NN表示不可为孔,UQ表示不可有重复,AI表示自增。
鼠标放在Table下的任意一栏,会出现三个图标。从左到右分别是查看表格的详细信息,设置表的结构界面,使用SQL语句对表格进行查询的界面
点击第三个图标,有两种方式可以对数据表的内容进行修改,一是使用代码,二是直接在Result Grid中进行修改
配置MySQL ODBC驱动后,用PowerDesiner连接
安装中,稍等片刻
解压后,Windows11搜索栏打开控制面板,然后选择Windows工具
当然,直接搜索也是一种选择
点击设置ODBC数据员(32位)后,选择系统DSN,然后再点击添加
使用SQL语言查看某数据库中所有的数据表
将下图中的“auto_assembly_system”修改为自己希望查看的数据库名“
使用SQL语言修改数据库
录入多个机械手、搬运小车、产品的信息
INSERT INTO `auto_assembly_system`.`machinical_arm` (`arm_id`, `brand`, `manufacturer`, `manufacturer_phone`, `line_id`) VALUES ('1', 'k', 'kdz', '123', '1');
INSERT INTO `auto_assembly_system`.`machinical_arm` (`arm_id`, `brand`, `manufacturer`, `manufacturer_phone`, `line_id`) VALUES ('2', 'k', 'kdz', '123', '1');
INSERT INTO `auto_assembly_system`.`machinical_arm` (`arm_id`, `brand`, `manufacturer`, `manufacturer_phone`, `line_id`) VALUES ('3', 'k', 'kdz', '123', '1');
INSERT INTO `auto_assembly_system`.`machinical_arm` (`arm_id`, `brand`, `manufacturer`, `manufacturer_phone`, `line_id`) VALUES ('4', 'k', 'bsq', '234', '2');
INSERT INTO `auto_assembly_system`.`machinical_arm` (`arm_id`, `brand`, `manufacturer`, `manufacturer_phone`, `line_id`) VALUES ('5', 'k', 'bsq', '234', '2');
INSERT INTO `auto_assembly_system`.`machinical_arm` (`arm_id`, `brand`, `manufacturer`, `manufacturer_phone`, `line_id`) VALUES ('6', 'd', 'dyj', '345', '2');
INSERT INTO `auto_assembly_system`.`machinical_arm` (`arm_id`, `brand`, `manufacturer`, `manufacturer_phone`, `line_id`) VALUES ('7', 'd', 'dyj', '345', '2');
INSERT INTO `auto_assembly_system`.`transport_car` (`car_id`, `type`, `max_load`, `warehouse_id`) VALUES ('1', 'house', '10', '1');
INSERT INTO `auto_assembly_system`.`transport_car` (`car_id`, `type`, `max_load`, `warehouse_id`) VALUES ('2', 'house', '10', '2');
INSERT INTO `auto_assembly_system`.`transport_car` (`car_id`, `type`, `max_load`, `warehouse_id`) VALUES ('3', 'house', '10', '1');
INSERT INTO `auto_assembly_system`.`transport_car` (`car_id`, `type`, `max_load`, `warehouse_id`) VALUES ('4', 'tiger', '10', '1');
INSERT INTO `auto_assembly_system`.`transport_car` (`car_id`, `type`, `max_load`, `warehouse_id`) VALUES ('5', 'tiger', '10', '2');
录入多条搬运信息、装配信息
INSERT INTO `auto_assembly_system`.`arm_distribution` (`arm_id`, `product_id`) VALUES ('1', '1');
INSERT INTO `auto_assembly_system`.`arm_distribution` (`arm_id`, `product_id`) VALUES ('1', '2');
INSERT INTO `auto_assembly_system`.`arm_distribution` (`arm_id`, `product_id`) VALUES ('1', '3');
INSERT INTO `auto_assembly_system`.`arm_distribution` (`arm_id`, `product_id`) VALUES ('2', '4');
INSERT INTO `auto_assembly_system`.`arm_distribution` (`arm_id`, `product_id`) VALUES ('2', '5');
INSERT INTO `auto_assembly_system`.`car_distribution` (`car_id`, `product_id`) VALUES ('1', '1');
INSERT INTO `auto_assembly_system`.`car_distribution` (`car_id`, `product_id`) VALUES ('1', '2');
INSERT INTO `auto_assembly_system`.`car_distribution` (`car_id`, `product_id`) VALUES ('2', '3');
INSERT INTO `auto_assembly_system`.`car_distribution` (`car_id`, `product_id`) VALUES ('2', '4');
INSERT INTO `auto_assembly_system`.`car_distribution` (`car_id`, `product_id`) VALUES ('2', '5');
INSERT INTO car_distribution(car_id,product_id) VALUES(2,6);
更新运输车的类型为“油车”与“电车”,并更新表格car_distribution中零件装配的数量
UPDATE `auto_assembly_system`.`transport_car` SET `type` = 'oil' WHERE (`car_id` = '1');
UPDATE `auto_assembly_system`.`transport_car` SET `type` = 'oil' WHERE (`car_id` = '2');
UPDATE `auto_assembly_system`.`transport_car` SET `type` = 'oil' WHERE (`car_id` = '3');
UPDATE `auto_assembly_system`.`transport_car` SET `type` = 'ele' WHERE (`car_id` = '4');
UPDATE `auto_assembly_system`.`transport_car` SET `type` = 'ele' WHERE (`car_id` = '5');
查询有多少辆搬运小车,多少油车,多少电车
SELECT type FROM auto_assembly_system.transport_car;
SELECT * FROM auto_assembly_system.transport_car
WHERE type=”oil”;
SELECT * FROM auto_assembly_system.transport_car
WHERE type=”ele”;
查询编号“1”的小车所属的仓库一共有多少辆车
SELECT COUNT(*) FROM auto_assembly_system.transport_car
WHERE warehouse_id=
(SELECT warehouse_id FROM auto_assembly_system.transport_car
WHERE car_id=1
);
查询一号产线上有多少个“%dz”生产的机械手;
SELECT * FROM auto_assembly_system.machinical_arm
WHERE manufacturer LIKE "%dz";
查询总装配量>2的各机械手的编号及总装配量,并按倒序排列;
SELECT arm_id, COUNT(*) AS total_assemblies
FROM auto_assembly_system.arm_distribution
GROUP BY arm_id HAVING total_assemblies > 2
ORDER BY total_assemblies DESC;
查询零件/产品“减速器”装配产量>4的机械手是哪些品牌的
SELECT DISTINCT auto_assembly_system.machinical_arm.brand
FROM auto_assembly_system.arm_distribution ad
JOIN auto_assembly_system.machinical_arm ON ad.arm_id = auto_assembly_system.machinical_arm.arm_id
GROUP BY ad.arm_id HAVING COUNT(*) > 4;
用 "ma" 作为auto_assembly_system.machinical_arm 的别名