数据库（三）

CREATE DATABASE work;
CREATE TABLE auto_coding(  -- 创建表auto_coding
                    equipment_number VARBINARY(32), -- 设备编号
                    device_name VARCHAR(32),  -- 设备名字
                    opening_date DATE ,  -- 启用日期
                    device_coding VARBINARY(32)  -- 设备编码
                );
CREATE TABLE GBcoding(
                    device_name VARCHAR(32),  -- 设备名字
                    device_coding VARBINARY(32)  -- 设备编码
                );
DROP TABLE auto_coding;  -- 删除auto_coding表
TRUNCATE TABLE auto_coding;
CREATE TABLE auto_coding(  -- 创建表auto_coding
                    serial_number INT,  -- 序号
                    asset_name VARCHAR(32),  -- 资产名称
                    asset_classification VARCHAR(32),  -- 资产分类
                    unit_of_measurement VARCHAR(32),  -- 计量单位
                    manufacturer VARCHAR (32),  -- 生产厂家
                    opening_date DATE ,  -- 启用日期
                    state VARCHAR(32),  -- 状态
                    equipment_number VARCHAR(32), -- 设备编号
                    device_coding INT  -- 设备编码
                );
                
ALTER TABLE auto_coding AUTO_INCREMENT=100;  -- device_coding自增从100开始
-- 给表赋值
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','1');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','2');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','3');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','4');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','5');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','6');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','7');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','8');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','9');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','10');
INSERT INTO auto_coding(asset_name,asset_classification,unit_of_measurement,manufacturer,opening_date,state,
equipment_number) VALUES('台式机','计算机设备及软件','台','联想','2019-09-09','在用','11');
                
DELETE FROM auto_coding WHERE equipment_number=1;  -- 删除equipment_number=1的行