SQL templates created for Mysql databases, tables, character sets, and primary and Foreign keys

SQL templates created for Mysql databases, tables, character sets, and primary and Foreign keys

# If a database with the same name exists, delete the database with the same name. Drop database if exists cfms; # If the cfms database whose character set is utf8 does not exist, create Create database if not exists cfms character set utf8; # Indicates that this database is used for the first time USE cfms; # If the users table exists, delete it and recreate it. Drop table if exists cfms. users; # delete a database to be created # If this table does not exist, create it again. Create table if not exists cfms. users ( Id VARCHAR (36) not null, # Use UUID, It is 36-bit Username VARCHAR (10) not null, # User Name Password VARCHAR (32) not null, #32-bit MD5 encryption is used here Sex VARCHAR (4) default null, # Your gender Userage VARCHAR (3) default null, # Your Age Birthday VARCHAR (10) default null, # Date of birth Email VARCHAR (100) not null, # email Edubackground CHAR (1) default null, # Your degree Mobile VARCHAR (11) default null, # mobile phone number Tel VARCHAR (15) default null, # contact number Regtime BIGINT (13) default null, # The registration time is saved as a timestamp. Java timestamp is 13, so BIGINT is used. FLOAT and DOUBLE are too large. Regip VARCHAR (15) default null, # registered IP address, 15-bit Logtimes INT (10) DEFAULT 0, # Number of logons Constraint primary key (id) # id of the table as the primary key constraint-UNIQUE-KEY (password) # Add a unique constraint to the password ) ENGINE = innodb default charset = utf8; # Name-based tables for file classification Drop table if exists cfms. namecategory; Create table if not exists cfms. namecategory ( Id VARCHAR (36) not null, # Use UUID, It is 36-bit Namecategory VARCHAR (100), # file type name Description text, # file description User_id VARCHAR (36) not null, # file Classifier Constraint primary key (id ), CONSTRAINT namecategory_user_id_FK foreign key (user_id) REFERENCES cfms. users (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = innodb default charset = utf8; # File type-specific tables Drop table if exists cfms. typecategory; Create table if not exists cfms. typecategory ( Id VARCHAR (36) not null, # Use UUID, It is 36-bit Typecategory VARCHAR (100), # file type name Description text, # file description User_id VARCHAR (36) not null, # file Classifier Constraint primary key (id ), CONSTRAINT typecategory_user_id_FK foreign key (user_id) REFERENCES cfms. users (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = innodb default charset = utf8; # File-based time-based tables for file classification Drop table if exists cfms. timecategory; Create table if not exists cfms. timecategory ( Id VARCHAR (36) not null, # Use UUID, It is 36-bit Timecategory VARCHAR (100) not null, # file type name Description text, # file description User_id VARCHAR (36) not null, # file Classifier Constraint primary key (id ), CONSTRAINT timecategory_user_id_FK foreign key (user_id) REFERENCES cfms. users (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = innodb default charset = utf8; # File-specific tables by file size Drop table if exists cfms. sizecategroy; Create table if not exists cfms. sizecategroy ( Id VARCHAR (36) not null, # Use UUID, It is 36-bit Sizecategroy VARCHAR (100) not null, # file type name Size_min VARCHAR (20) default null, # Minimum File Size Size_max VARCHAR (20) default null, # Maximum File Size User_id VARCHAR (36) not null, # file Classifier Constraint primary key (id ), CONSTRAINT sizecategroy_user_id_FK foreign key (user_id) REFERENCES cfms. users (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = innodb default charset = utf8; # If the file information table exists, delete it. If it does not exist, create it. Drop table if exists cfms. files; # If this table does not exist, create a new one. Create table if not exists cfms. files ( Id VARCHAR (36) not null, # file primary key value Name VARCHAR (250) not null, # name of the uploaded file, uuid name of the file Realname VARCHAR (250) not null, # The name of the uploaded file Path VARCHAR (250) not null, # File Location Uptime BIGINT (13), # File Upload time Description text, # file description Size BIGINT (13) default null, # File size Keyword VARCHAR (100) default null, # file keyword User_id VARCHAR (36) not null, # uploaded Namecategory_id VARCHAR (36) not null, Typecategory_id VARCHAR (36) not null, Timecategory_id VARCHAR (36) not null, Constraint primary key (id ), CONSTRAINT files_user_id_FK foreign key (user_id) REFERENCES cfms. users (id) on delete cascade on update cascade, CONSTRAINT files_namecategory_id_FK foreign key (user_id) REFERENCES cfms. users (id) on delete cascade on update cascade, CONSTRAINT files_typecategory_id_FK foreign key (user_id) REFERENCES cfms. users (id) on delete cascade on update cascade, CONSTRAINT files_timecategory_id_FK foreign key (user_id) REFERENCES cfms. users (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = innodb default charset = utf8;