54 mysql 基本数据类型 完整性约束

主要内容:

1 . 数值类型:

　　整型:

　　　　1)  有符号和无符号的tinyint

　　　　　　默认是有符号  create table t1(x tinyint)                      -127 -  127

　　　　　　无符号设置:     create table t1(x tinyint unsigned)       0     -   255

　　　　 2)  int 整数 : 用于保存一些范围的整数数值范围;

　　　　　　有符号:　　-2147483648 ～ 2147483647

　　　　　　无符号:　　0 ～ 4294967295

　　　　3 )  bigint: 大整数，数据类型用于保存一些范围的整数数值范围

　　　　　　int类型后的存储是显示宽度不是存储宽度.为其指定宽度, 仅仅是指定查询结果的显示宽度,与存储无关, 其实我们没必要为整数类型指定显示宽度, 使用默认就可以

　　　　默认的显示宽度, 都是在最大值的基础上加一

　　浮点型float:

　　　　单精度浮点型:float    　　 create table t5(x float(255,30)); #建表成功

　　　　双精度浮点型:double　　 create table t6(x float(255,30)); #建表成功

　　　　定点数类型: decimal :　　create table t7(x decimal(65,30));

　　　　　　　　　　　　随着小数的增多, 精度始终准确, 其原因是在于其内部是按照字符串存储的.

insert into t5 values(1.1111111111111111111111111111111);#小数点后31个1
insert into t6 values(1.1111111111111111111111111111111);
insert into t7 values(1.1111111111111111111111111111111);
select * from t5; #随着小数的增多，精度开始不准确
select * from t6; #精度比float要准确点，但随着小数的增多，同样变得不准确
select * from t7; #精度始终准确,d为30，于是只留了30位小数

2  日期类型

　　　date  　　: YYYY-MM-DD

　　　time  　　:  HH:MM:SS

　　   datetime   :  YYYY-MM-DD HH:MM:SS

           year　　  :  YYYY

mysql> create table t9(d date,t time,dt datetime);
#查看表的结构
mysql> desc t9;
# 调用mysql自带的now()函数，获取当前类型指定的时间 如下结构
mysql> insert into t9 values(now(),now(),now());

　　    例题:

mysql>  create table student(id int, birth_y year, birth date , class_time time, now_time datatime);

mysql> insert into student values
    -> (1, '1995','1995-08-28','11:11:11',now());

mysql> select * from student;
+------+---------+------------+------------+---------------------+
| id   | birth_y | birt       | class_time | now_time            |
+------+---------+------------+------------+---------------------+
|    1 |    1995 | 1995-08-28 | 11:11:11   | 2018-09-18 20:25:17 |
+------+---------+------------+------------+---------------------+

3  char 类型和varchar类型

　　char类型: 定长,简单粗暴, 浪费空间, 存取速度快

　　　　　　字符长度范围:0-255(一个中文是一个字符, 是utf8编码的三个字节)

　　　　　　存储: 在检索或者查询时,查出的结果会自动的删除尾部的元素,除非修改sql模式:让其现出原形;

　　varchar : 变长,精准, 节省空间,存取速度慢;

　　　　　　存储 : 真实的存储数据, 不会用空格填充,如果'ab  ',尾部的空格也会被存起来

　　两个函数:

　　　　　   length(); 　　  查看字节数

　　　　　   char_length(); 查看字符数

　　1.char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形。

# 创建t1表,分别指明字段x为char类型，字段y为varchar类型
mysql> create table t1(x char(5),y varchar(4));
# char存放的是5个字符，而varchar存4个字符
mysql>  insert into t1 values('你瞅啥 ','你瞅啥 ');
# 在检索时char很不要脸地将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少
mysql> select x,char_length(x),y,char_length(y) from t1;
+-----------+----------------+------------+----------------+
| x         | char_length(x) | y          | char_length(y) |
+-----------+----------------+------------+----------------+
| 你瞅啥    |              3 | 你瞅啥     |              4 |
+-----------+----------------+------------+--------------
 #略施小计，让char现原形
 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
#查看当前mysql的mode模式
mysql> select @@sql_mode;
#原形毕露了吧。。。。
mysql> select x,char_length(x) y,char_length(y) from t1;
+-------------+------+----------------+
| x           | y    | char_length(y) |
+-------------+------+----------------+
| 你瞅啥      |    5 |              4 |
+-------------+------+----------------+
# 查看字节数
#char类型：3个中文字符+2个空格=11Bytes
#varchar类型:3个中文字符+1个空格=10Bytes
mysql> select x,length(x),y,length(y) from t1;
+-------------+-----------+------------+-----------+
| x           | length(x) | y          | length(y) |
+-------------+-----------+------------+-----------+
| 你瞅啥      |        11 | 你瞅啥     |        10 |
+-------------+-----------+------------+-----------+

4  枚举类型和集合类型

　　　枚举: enum单选只能在指定的范围内选一个值, 如男或者女

　　　集合: set　 多选在给定的范围内选择一个或者多个值

create table consumer(
				id int unsigned,
				name char(20),
				sex enum('male','female') not null default 'male',
				fav set('抽烟','喝酒','烫头')
			);
			insert into consumer(id,name,sex,fav) values (1,'alex','female','抽烟,烫头');
			insert into consumer(id,name,sex) values (2,'alex2','female');wanzheng

 5  完整性约束

　　 not null  default(表示该字段不能为空,默认值)

　　unique(表示该字段的值是唯一)

　　primary key(标识该字段为该表的主键, 可以唯一的表示记录)

　　auto-increment (标识该字段的值是自增整数类型)

　　foreign key(标识该字段为该表的外键) 

　　 1 )  null : 表示空, 非字符串

　　      default:默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

create table tb1(
    nid int not null defalut 2,
    num int not null

);　

 　　2 )  unique 唯一

　　　　单列唯一

create table department(
    id int,
    name char(10) unique
);
mysql> insert into department values(1,'it'),(2,'it');
ERROR 1062 (23000): Duplicate entry 'it' for key 'name'

　　　　多列唯一

create table department(
    id int unique,
    name char(10) unique
);
insert into department values(1,'it'),(2,'sale');

　　　　组合唯一

create table department3(
	id int,
	name char(10),
	unique(id,name));
insert into department3 values(1,'it'),(2,'it');
					
只要有一个字段不一样，都可以插入                    

 　　3 )  primary key主键

 　　　　约束: 等价于not null + unique 字段值不能为空且唯一

create table t2(
			
    id int primary key,
    name char(10) not null
);

　　4 )  auto_increment 自增长

　　　　约束: 约束的字段为自动增长, 约束的字段同时必须被key约束

　　　　　　三种情况:

　　　　不指定ip:  则自动增长

　　　　指定ip:   按你指定的插入, 再插入下一个数据时, 则从最后一个id开始计算

　　　　对于自增的字段, 在用delete删除后, 再插入值,该字段扔按照删除前的位置开始计算. 而用truncate删除后, 该字段从开始位置计算.

　　　　　　清空表: delete和truncate 的区别:

　　　　　　　　delete from t1: 如果有自增, 仍然是以删除前的最后一个作为开始;

　　　　　　　　truncate table t1 ; 数据量大, 删除速度快, 且直接从0开始.

　　　　　　对于自增长的两个变量:步长,和起始的偏移

　　　　　　　　查看两个变量的语句: show variables like 'auto_inc%';

　　　　　　　　设置为会话设置,只在本次连接中有效

　　　　　　　　　　设置步长:  set session auto_increment_increment=5;

　　　　　　　　　　设置起始偏移量:set session auto_increment_offset=5;

 　　　　　　　　　　注意: 退出本次连接之后会记录一次上一次的记录, 

 　　　　　　　　设置全局: 都有效

　　　　　　　　　　设置步长:  set global auto_increment_increment=5;

　　　　　　　　　　设置起始量偏移:set global auto_increment_increment=5;