mysql建表语句详解

数据类型的列表

Data type NameNormal RangeUnsigned Range
TINYINT()-128 to 127 UNSIGNED.0 to 255
SMALLINT()-32768 to 327670 to 65535
MEDIUMINT()-8388608 to 8388607 UNSIGNED.0 to 16777215
INT( )-2147483648 to 21474836470 to 4294967295
BIGINT( )-9223372036854775808 to 92233720368547758070 to 18446744073709551615
Data type nameTypeRange
CHAR( )fixed string255 characters
VARCHAR( )Variable string255 characters
TINYTEXTstring255 characters
TEXTstring65535 characters
MEDIUMTEXTstring16777215 characters
LONGTEXTstring4294967295 characters
Data type NameFormat
DATEYYYY-MM-DD
DATETIMEYYYY-MM-DD HH:MM:SS
TIMESTAMPYYYYMMDDHHMMSS
TIMEHH:MM:SS

示例

create table class
(
    code varchar(20) primary key,
    name varchar(20) not null,
    
    //NOT NULL :不为空; DEFAULT: 默认值
    `create_time` timestamp NOT NULL DEFAULT:'0000-00-00 00:00:00'
);
create table ceshi
(
	//auto_increment : 代表自增长;  primary key : 代表主键
    ids int auto_increment primary key,
    
    uid varchar(20),
    name varchar(20),
    class varchar(20),
    
     //fornign key+(列名)  代表给哪一个加外键, references 要引用哪个表里的列
    foreign key (class)  references class(code) 
);
  • auto_increment : 代表自增长
  • primary key : 代表主键
  • foreign key (列名) references 主表名 (列名) : fornign key+(列名) 代表给哪一个加外键 references 要引用哪个表里的列

示例二

CREATE TABLE `t_lpr_result_20210630` (
  `pass_id` varchar(255) NOT NULL,
  `cam_id` varchar(255) DEFAULT NULL,
  `device_id` varchar(255) DEFAULT NULL,
  `pass_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `vehicle_img_path` varchar(1024) DEFAULT NULL,
  `plate_img_path` varchar(1024) DEFAULT NULL,
  `image_type` int(11) DEFAULT NULL,
  `bucket_id` varchar(512) DEFAULT NULL,
  `plate_no` varchar(255) DEFAULT NULL,
  `plate_color` varchar(5) DEFAULT NULL,
  `lpr_no` varchar(255) DEFAULT NULL,
  `lpr_color` varchar(5) DEFAULT NULL,
  `lpr_state` int(11) DEFAULT NULL,
  `stationid` varchar(255) DEFAULT NULL,
  `laneType` int(11) DEFAULT NULL,
  `upload_time` bigint(20) DEFAULT NULL,
  `receive_time` bigint(20) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `output_accuracy` varchar(255) DEFAULT NULL,
  `output_correct` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`pass_id`),
  KEY `idx_lpr_passid` (`pass_id`),
  KEY `idx_lpr_cam` (`cam_id`),
  KEY `idx_lpr_image` (`image_type`),
  KEY `idx_lpr_plate` (`plate_no`),
  KEY `idx_lpr_plate2` (`lpr_no`),
  KEY `idx_lpr_state` (`lpr_state`),
  KEY `idx_lpr_station` (`stationid`),
  KEY `idx_lpr_lane` (`laneType`),
  KEY `idx_lpr_accuracy` (`output_accuracy`),
  KEY `idx_lpr_correct` (`output_correct`),
  KEY `idx_lpr_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH(pass_id) */;

key(普通索引是index的一种):主要是用来加快查询速度的。

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×