4、建表与查询
1.数据类型? 包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION),还有枚举类型enum 和集合类型set 以及布尔值、json
数值数据 型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 byte (-128，127) (0，255) 小整数值 SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值 DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值 时间和日期类型 类型 大小 ( bytes) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 ‘-838:59:59‘/‘838:59:59‘ HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 0/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 1970-01-01 0/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07 ，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳字符串类型
类型 大小 用途 CHAR 0-255 bytes 定长字符串 VARCHAR 0-65535 bytes 变长字符串 TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串 TINYTEXT 0-255 bytes 短文本字符串 BLOB 0-65 535 bytes 二进制形式的长文本数据 TEXT 0-65 535 bytes 长文本数据 MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据 LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295 bytes 极大文本数据 常用补充 bit[(M)]
二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127.
无符号：
0 ～ 255
特别的： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
特别的：对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符号：
0
1.175494351E-38 to 3.402823466E+38
**** 数值越大，越不准确 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
有符号：
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 数值越大，越不准确 ****
char (m)
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
PS: 即使数据小于m长度，也会占用m长度
varchar(m)
varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 ? 1)个字符。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 ? 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.
enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM(‘x-small‘, ‘small‘, ‘medium‘, ‘large‘, ‘x-large‘)
);
INSERT INTO shirts (name, size) VALUES (‘dress shirt‘,‘large‘), (‘t-shirt‘,‘medium‘),(‘polo shirt‘,‘small‘);
set
集合类型
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET(‘a‘, ‘b‘, ‘c‘, ‘d‘));
INSERT INTO myset (col) VALUES (‘a,d‘), (‘d,a‘), (‘a,d,a‘), (‘a,d,d‘), (‘d,a,d‘);
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（‘-838:59:59‘/‘838:59:59‘）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 0/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 0/2037 年某时）
2.建表
a、基本用法
create table if not exists（判断是否存在） 表名( 表头1 数据类型 default a , 表头2 数据类型 ) engine=innodb default charset=‘utf-8‘；注意说明 CREATE TABLE 子句之后创建的表的名称。表名在数据库中必须是唯一的 if not exists（判断是否存在） 可选择用于校验 表头+数据类型为一个组合，组合之间用‘,‘隔开，结尾用‘；’ engine 存储引擎：InnoDB ， MyISAM ， HEAP ， EXAMPLE ， CSV ， ARCHIVE ， MERGE， FEDERATED 或 NDBCLUSTER ，默认5.5版本后innodb(支持事务，原子性操作) default charset 设置数据表的编码类型 default a 默认值为 DEFAULT 值用于指定列的默认值 b、NULL 与NOT NULL
设置字段的属性为 NOT NULL ， 在操作数据库时如果输入该字段的数据为 NULL ，就会报错
create table if not exists（判断是否存在） 表名( 表头1 数据类型 NOT NULL 表头2 数据类型 default NULL （表示默认为空） ) engine=innodb default charset=‘utf-8‘；c、主键与自增
primary key 与auto_increment
关于自增与步长
-- 设置主键与自增
-- 主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
-- 一个表只能有一个主键
create table t1(
id int signed not null auto_increment primary key,
num decimal(10,5),
name char(10)
)engine=innodb default charset=utf8;
create table t1(
id int signed not null auto_increment ,
num decimal(10,5),
name char(10),
primary key(id)
)engine=innodb default charset=utf8;
-- 主键可以由多列组成
CREATE TABLE t5 (
nid int(11) NOT NULL AUTO_INCREMENT,
pid int(11) not NULL,
num int(11),
primary key(nid,pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
d、唯一索引
unique
create table t1( id int ...., num int, xx int, unique 唯一索引名称 (列名,列名), )F、外键（foreign key）约束 可为空，当时不能重复
多为其他表的主键
外键表示一个表中的一个字段被另一个表中的一个字段引用
constraint 命名(fk_..) foreign key 表头（当前表参数） references table2(表头) ON DELETE/ON UPDATE NO ACTION或restrict(父表删除更新时检查是否有对应外键，有则禁止删除) cascade （父表删除更新时检查是否有对应外键，有则同步） set Null (父表删除更新时检查是否有对应外键，有则设为null)CONSTRAINT 子句允许您为外键约束定义约束名称。如果省略它，MySQL将自动生成一个名称 FOREIGN KEY 子句指定子表中引用父表中主键列的列 创建外键
CREATE DATABASE IF NOT EXISTS dbdemo; USE dbdemo; CREATE TABLE categories( cat_id int not null auto_increment primary key, comment ‘comment 表示注释‘ cat_name varchar(255) not null, cat_description text ) ENGINE=InnoDB; CREATE TABLE products( prd_id int not null auto_increment primary key, prd_name varchar(355) not null, prd_price decimal, cat_id int not null, FOREIGN KEY fk_cat(cat_id) REFERENCES categories(cat_id) ON UPDATE CASCADE ON DELETE RESTRICT )ENGINE=InnoDB;外键变种，多外链情况，及unique 合并使用
案例1普通用法.一对多的情况，如：一个角色多个头衔的情况
user uid name department 1 n1 1 2 n2 1 3 n3 2 4 n4 2 department did cont 1 A部门 2 B 部门user表的department 需要外链至department表的did
一个user对应一个department
案例2：一对一 ，不允许出现重复
user uid name postcard_id 1 n1 1 2 n2 3 3 n3 2 4 n4 4 postcard pid number 1 3101.... 2 3201.... 3 3301..... 4 3401...... 外键的值与表内id是一一对应的关系,每个user仅有一个postcard_id 这里需要在user表中对postcard_id 进行uniquecreate table postcard( pid int auto_increment primary key, number tinyint, )engine=innodb default charset=utf8; create table user( uid int not null auto_increment primary key, username varchar(64) not null, postcard_id int not null, unique key uq_u1 (postcard_id), commit ‘这里对postcard_id限定唯一不重复‘ CONSTRAINT fk_user_pc FOREIGN key (postcard_id) REFERENCES postcard(pid) )engine=innodb default charset=utf8;
补充一对不足一：
ser 少部分权限 uid name password access 1 n1 1224 access2 2 n2 dasda access1 3 n3 544554 4 n4 dasdasd针对这种情况
可以单独将用户与权限单独建表， 用以节省空间
ser 少部分权限 uid name access 1 n1 access2 2 n2 access1案例3：多对多, 联合唯一的场景
c表内的表头A与表头B，分别为A表，B 表的主键，且表头A与B的数据count>1
由于表头A与表头B存在的这种对应关系，仅能出现一次，故需要两则联合唯一
若业务需求不做约束要求，则不需要使用联合唯一
A aid username gender 1 张三 man 2 李四 woman 3 王五 man B bid store 1 store_a 2 store_b 3 store_c c 人与商店的对应关系 cid username store 1 1 1 2 1 2 3 1 3 4 2 2 5 2 1 6 3 1create table if not exist table_A( aid int auto_increment, username varchar(10), primary key(id) )engine = innodb default charset=utf8; create table if not exist table_B( bid int not null auto_increment, store varchar(30) primary key(bid) )engine = innodbb default charset=utf8; create table if not exist table_c( cid int not null auto_increment, username int not null, store int not null , primary key(cid), unique key uq_username_store(username,store), commit ‘不允许二者一一对应关系再次出现需要使用联合唯一‘ constraint fk_username_a foreign key(username) references to table_a(username), constraint fk_username_b foreign key(store) references to table_b(store), )engine = innodbb default charset=utf8;
mysql 基础 4建表（数据类型、建表、主键外键）
标签：datetime 集合 唯一索引 不能 ade 重复 二进制 innodb str
查看更多关于mysql 基础 4建表（数据类型、建表、主键外键）的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did118236