SQL data table operations Create alert drop

--Create data Table php41_goods Product table
CREATE TABLE Php41_goods (
goods_id mediumint unsigned not null auto_increment comment ' primary key ',
Goods_name varchar (+) NOT null comment ' commodity name ',
Goods_price decimal (10,2) NOT null default 0 comment ' Market price ',
Goods_shop_price decimal (10,2) NOT null default 0 comment ' Shop price ',
Goods_number smallint NOT NULL default 1 comment ' Quantity of goods ',
Goods_weight smallint NOT NULL default 0 comment ' product weight ',
CAT_ID mediumint NOT null default 0 comment ' Commodity category ',
BRAND_ID mediumint NOT null default 0 comment ' merchandise brand ',
Goods_big_logo char (+) NOT null default ' comment ' product big picture ',
Goods_small_logo char (+) NOT null default ' comment ' commodity thumbnail ',
Goods_introduce Text Comment ' Product introduction ',
Is_sale enum (' shelves ', ' bottom shelf ') NOT null default ' shelves ' comment ' shelves, lower shelves ',
Is_rec enum (' Recommended ', ' not recommended ') NOT NULL default ' does not recommend ' comment ' recommendation or not ',
is_hot enum (' Hot sell ', ' not hot ') NOT null default ' not hot sell ' comment ' hot selling or not ',
is_new enum (' new ', ' not new ') NOT null default ' no new ' comment ' New or not ',
Add_time int NOT NULL comment ' Add information time ',
Upd_time int NOT NULL comment ' Modify information time ',
Is_del enum (' delete ', ' do not delete ') NOT null default ' do not delete ' comment ' delete or not ',
Primary KEY (GOODS_ID),
Unique key (Goods_name),
Key (Goods_shop_price),
Key (Goods_price),
Key (CAT_ID),
Key (BRAND_ID),
Key (Add_time)
) Engine=innodb Charset=utf8;

--Add a type_id field to store the product's corresponding type
ALTER TABLE php41_goods add type_id smallint unsigned NOT null default 0 comment ' type id ' after brand_id;

--"Commodity-(many-to-many)-attribute" "Intermediate contact sheet
CREATE TABLE Php41_goods_attr (
ID mediumint unsigned NOT NULL auto_increment comment ' primary key ID ',
goods_id mediumint unsigned not NULL comment ' Product ID ',
attr_id mediumint unsigned NOT NULL comment ' property ID ',
Attr_value varchar (+) not null the value corresponding to the default ' comment ' property,
Primary key (ID),
Key (GOODS_ID),
Key (ATTR_ID)
) Engine=myisam Charset=utf8 Comment ' Commodity-attribute Association table ';

--Delete Index
ALTER TABLE php41_goods drop key goods_name;
--Modify the field length to 256 bytes
ALTER TABLE php41_goods modify Goods_name varchar (n) NOT null comment ' commodity name ';

--Vertical sub-table, the "album" of related fields through "separate table" for storage
CREATE TABLE Php41_goods_pics (
ID int unsigned NOT NULL auto_increment comment ' primary key ',
goods_id mediumint unsigned not NULL comment ' Product ID ',
Pics_big char (+) not null comment ' album artwork ',
Pics_small char (+) not null comment ' album thumbnail ',
Primary KEY (ID)
) Engine=myisam Charset=utf8 comment ' Commodity album table ';

--"type" data sheet
CREATE TABLE Php41_type (
type_id smallint unsigned NOT null auto_increment comment ' primary key ID ',
Type_name varchar (+) NOT null comment ' type name ',
Primary KEY (TYPE_ID)
) Engine=myisam Charset=utf8 comment ' Commodity type table ';

--"attribute" data table
CREATE TABLE Php41_attribute (
attr_id int unsigned not NULL auto_increment comment ' primary key ID ',
Attr_name varchar (+) NOT null comment ' property name ',
type_id smallint unsigned not null comment ' corresponding type ID ',
Attr_is_sel tinyint NOT null default 0 comment ' 0: Unique 1: Multi-select ',
Attr_write_mod tinyint NOT null default 0 comment ' 0: Manual 1: drop-down list selection ',
attr_sel_opt varchar (+) NOT null default ' comment ' Multiple selected item information, multiple values are used with each other, comma separated ',
Primary KEY (ATTR_ID),
Key (TYPE_ID)
) Engine=myisam Charset=utf8 comment ' commodity attribute table ';


--"category" Data sheet
drop table if exists php41_category;
CREATE TABLE Php41_category (
cat_id smallint unsigned NOT null auto_increment comment ' primary key ID ',
Cat_name varchar (+) NOT null comment ' class name ',
cat_pid smallint unsigned NOT null default 0 comment ' ancestor id ',
Cat_path varchar (+) NOT null default ' comment ' Full path ',
Cat_level tinyint NOT null default 0 comment ' rank ',
Primary KEY (CAT_ID),
Key (Cat_pid)
) Engine=myisam Charset=utf8 comment ' Commodity classification table ';


--"Commodity-(many-to-many)-category" "Intermediate contact form"
drop table if exists php41_goods_cat;
CREATE TABLE Php41_goods_cat (
ID mediumint unsigned NOT NULL auto_increment comment ' primary key ID ',
goods_id mediumint unsigned not NULL comment ' Product ID ',
cat_id mediumint unsigned NOT NULL comment ' class ID ',
Primary key (ID),
Key (GOODS_ID),
Key (CAT_ID)
) Engine=myisam Charset=utf8 Comment ' Commodity-classification, association table ';

SQL data table operations Create alert drop