MySQL中的数据类型
整数型
浮点型
M是数字的总位数 D是小数点后面的位数 ;(M,D) 可以省略 但是如果省略则会根据硬件允许的限制来保存值。
FLOAT和DOUBLE是浮点数，DECIMAL则是定点数，定点数内部以字符串的形式进行存储，所以更加精确。
字符串类型
字符串类型中：
CHAR为定长型字符串；VARCHAR为变长型字符串。其中，M为定义的长度，即：CHAR定义了多少个字节就为多少个字节的长度；而VARCHAR存储的字节为L(length)+1，即：VARCHAR存储的字节数为实际长度+1个字节，其中多出的1个字节用来保存实际使用了多大的长度。
如：
保存一个字符串'kim'
定义一个CHAR(10)和VARCHAR(10)来保存这个字符串。
CHAR会用10个字节来存储这个字符串；而VARCHAR虽然定义了10个字节，但是实际上会用3+1个字节来进行存储。但是CHAR的效率要高于VARCHAR,CHAR相当于拿空间换时间，VARCHAR相当于拿时间换空间。
另外，CHAR默认在存储数据的时候会用空格填充到指定长度；而在检索的时候会去掉后面的空格，测试如下:
-- 测试CHAR和VARCHAR
-- CHAR的效率高于VARCHAR CHAR相当于拿空间换时间，VARCHAR相当于拿时间换空间
CREATE TABLE test_str(
a CHAR(5),
b VARCHAR(5)
);
INSERT test_str(a,b) VALUES(' 123 ',' 123 ');
SELECT CONCAT('*',a,'*'),CONCAT('*',b,'*') FROM test_str;
-- 在查询的时候，CHAR会自动抹去后面的空格
+-------------------+-------------------+
| CONCAT('*',a,'*') | CONCAT('*',b,'*') |
+-------------------+-------------------+
| * 123* | * 123 * |
+-------------------+-------------------+
时间类型
时间类型的测试如下：
-- 测试TIME类型
-- HH:MM:SS[D HH:SS:SS]
CREATE TABLE test_time(
a TIME
);
INSERT test_time(a) VALUES('12:23:45');
INSERT test_time(a) VALUES('2 12:23:45'); -- 相当于60:23:45
INSERT test_time(a) VALUES('22'); -- 相当于22秒
INSERT test_time(a) VALUES('2 22'); -- 相当于70：00：00
INSERT test_time(a) VALUES(NOW());
INSERT test_time(a) VALUES(CURRENT_TIME);
-- 测试DATE类型YYYY-MM-DD YYYYMMDD
CREATE TABLE test_date(
a DATE
);
INSERT test_date(a) VALUES('2020-04-01');
INSERT test_date(a) VALUES('20200401');
INSERT test_date(a) VALUES(NOW());
INSERT test_date(a) VALUES(CURRENT_DATE);
-- 测试DATETIME
CREATE TABLE test_datetime(
a DATETIME
);
INSERT test_datetime(a) VALUES('2020-04-01 22:05:48');
INSERT test_datetime(a) VALUES(NOW());
-- 测试TIMESTAMP
CREATE TABLE test_timestamp(
a TIMESTAMP
);
INSERT test_timestamp(a) VALUES('1978-10-23 12:12:12');
-- 如果插入CURRENT_TIMESTAMP
-- 或者插入NULL
-- 或者什么也不写
-- 或者插入NOW()
-- 则会得到当前系统日期和时间
INSERT test_timestamp(a) VALUES(CURRENT_TIMESTAMP);
INSERT test_timestamp(a) VALUES(NULL);
INSERT test_timestamp(a) VALUES(NOW());
INSERT test_timestamp VALUES();
-- 测试YEAR
CREATE TABLE test_year(
a YEAR
);
INSERT test_year(a) VALUES('2020');
MySQL中的完整性约束条件
PRIMARY KEY 和 AUTO_INCREMENT
PRIMARY KEY
主键，标识着记录的唯一性，值不能重复，一个表只能有一个主键，且自动禁止为空
AUTO_INCREMENT
自动增长，只能用于数值列，而且要配合索引进行使用；默认起始值从1开始增长，每次增长1
主键和自增长的测试如下：
-- 测试主键
CREATE TABLE test_primary_key(
id INT UNSIGNED PRIMARY KEY,
username VARCHAR(20)
);
-- PRIMARY KEY 可以省略为 KEY
CREATE TABLE test_primary_key1(
id INT UNSIGNED KEY,
username VARCHAR(20)
);
-- 也可以这样定义主键
CREATE TABLE test_primary_key2(
id INT UNSIGNED,
username VARCHAR(20),
PRIMARY KEY(id)
);
-- 复合主键,可以使多个字段共同作为主键
CREATE TABLE test_primary_key3(
id INT UNSIGNED,
courseId INT UNSIGNED,
username VARCHAR(20),
email VARCHAR(50),
PRIMARY KEY(id,courseId)
);
-- 测试AUTO_INCREMENT
CREATE TABLE test_auto_increment(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20)
);
UNSIGNED和ZEROFILL
UNSIGNED
无符号，没有负数即，从0开始
ZEROFILL
零填充，当显示长度不够的时候可以使用前补0的效果，填充至指定的长度
-- 测试零填充 ZEROFILL
CREATE TABLE test_zerofill(
a TINYINT ZEROFILL,
b SMALLINT ZEROFILL,
c MEDIUMINT ZEROFILL,
d INT ZEROFILL,
e BIGINT ZEROFILL
);
INSERT test_zerofill(a,b,c,d,e) VALUES(1,1,1,1,1);
显示效果如下：
+------+-------+----------+------------+----------------------+
| a | b | c | d | e |
+------+-------+----------+------------+----------------------+
| 001 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |
+------+-------+----------+------------+----------------------+
NOT NULL
NOT NULL 非空约束，在插入值的时候，这个字段必须要给值，值不能为空值
测试程序如下：
-- 测试NOT NULL
CREATE TABLE test_not_null(
a VARCHAR(20),
b VARCHAR(20) NOT NULL
);
INSERT test_not_null(a,b) VALUES(NULL,'haha');
显示结果为：
+------+------+
| a | b |
+------+------+
| NULL | haha |
+------+------+
DEFAULT
DEFAULT 默认值，如果插入记录没有给字段赋值，则使用默认值
测试程序如下：
-- 测试DEFAULT
CREATE TABLE test_default(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
email VARCHAR(50) NOT NULL
);
INSERT test_default(username,email) VALUES('C','123456@qq.com');
显示结果为：
+----+----------+------+---------------+
| id | username | age | email |
+----+----------+------+---------------+
| 1 | C | 18 | 123456@qq.com |
+----+----------+------+---------------+
UNIQUE KEY
UNIQUE KEY即唯一索引，代表唯一性。一个表中有多个字段是唯一索引，值不能重复，NULL除外
测试如下：
-- 测试 UNIQUE KEY
CREATE TABLE test_unique(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
email VARCHAR(50) UNIQUE,
card CHAR(18) UNIQUE
);
-- NJLL值不算重复
INSERT test_unique(username,email,card) VALUES('A',NULL,NULL);
INSERT test_unique(username,email,card) VALUES('B',NULL,NULL);
结果如下：
+----+----------+-------+------+
| id | username | email | card |
+----+----------+-------+------+
| 1 | A | NULL | NULL |
| 2 | B | NULL | NULL |
+----+----------+-------+------+
FOREIGN KEY
FOREIGN KEY 外键约束，后面再说\
创建完整的用户表
利用MySQL数据类型和完整性约束条件可以创建一个完整的用户表
-- user
CREATE TABLE IF NOT EXISTS `user`(
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
`username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
`password` CHAR(32) NOT NULL COMMENT '密码',
`email` VARCHAR(50) NOT NULL UNIQUE COMMENT '邮箱',
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
`sex` ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性',
`tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话',
`addr` VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '地址',
`card` CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
`married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚,1代表已婚',
`salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE = INNODB DEFAULT CHARSET = UTF8;
网友评论