-- 查看变量
show variables like 'character_set%';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | F:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
修改默认字符集(5.5之后的版本)
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
-- 校对规则
show collation;
-- 字符集_地区名_比较规则(ci, cs, bin)不区分大小写,区分,字节比较、
show collation like 'utf8%';
show collation like 'gbk8%';
+----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+----------------+---------+----+---------+----------+---------+
create table tb14(
name varchar(10)
) character set gbk collate gbk_bin;
/*
mysql 的数据类型
数字型:unsigned 控制是否有正负, zerofill 进行前导0填充
默认有符号
tinyint
一个字节 表示范围-128<--->127
无符号 0 <--> 255
bool = tinyint(1)
smallint
2个字节
-32768 --- 32767
0 --- 65535
mediumint
3个字节
int/intege
4个字节
bigint
8个字节
*/
create table tbl_int(
a tinyint unsigned,
b tinyint
);
insert into tbl_int values(255, 127);
-- 报错ERROR 1264 (22003): Out of range value for column 'a' at row 1
insert into tbl_int values(256, 128);
-- 数据的显示宽度,设置最小宽度需要前导0填充
alter table tbl_int add c tinyint (2) zerofill;
insert into tbl_int values(2, 3, 5);
insert into tbl_int values(24, 36, 125);
+------+------+------+
| a | b | c |
+------+------+------+
| 255 | 127 | NULL |
| 2 | 3 | 05 |
| 24 | 36 | 125 |
+------+------+------+
-----------------------------------小数-----------------------------------------
-- 支持控制位数 type(m, d) m表示总位数,d 表示小数位数
-- 支持无符号:
/*
浮点:
float
4个字节
7 位左右
double
8个字节
16个有效位左右
定点:
decimal
+65 个 9
*/
drop table if exists num_2;
create table num_3(
c decimal(10,2)
);
insert into num_2 values(123.123,12345.123 );
insert into num_2 values(1223.123,12345.123 );
ERROR 1264 (22003): Out of range value for column 'a' at row 1
-- 科学计数法 也可以
insert into num_2 values(0.23E3, 456.2E2);
-- 不丢精度, 但四舍五入
insert into num_3(c) values(123456.4567);
alter table num_3 add money decimal(10 ,2) zerofill;
insert into num_3(money) values(123456.4567);
---------------------------------日期类型------------------------------------------------
/*
datetime
8kb
yyyy-MM-dd HH:mm:ss
date
3kb
yyyy-MM-dd
timestamp
4kb
0<---->20亿
2038-01-19 03:14:07
time
3kb
一天中的时间,
表示时间间隔,可以使用天来表示
D HH:mm:ss
-838<---->838
year
1kb
1901----2155
*/
create table dt_1(
a datetime,
b timestamp
);
-- 可以填写任意格式的日期,支持两位的年份(千年虫)70 <-----> 69 不建议使用
insert into dt_1 values('1992-08-04 10:34:32','2038-01-19 03:14:08');
insert into dt_1 values('1992:08:04 10:34:32','2038-01-19 03:14:08');
insert into dt_1 values('1992:08:04','2038-01-19');
insert into dt_1 values('0000-00-00 00:00:00','2038-01-19');
-- 支持 0000-00-00 00:00:00
-- 例如 2013-04-0 表示 整个 4月
-- 检索时间戳
select b+0 from dt_1;
+----------------+
| b+0 |
+----------------+
| 19920807103434 |
| 20380119031407 |
| 20380119031408 |
+----------------+
-- time
create table t_1(
ago time
);
insert into t_1 values('5 23:23:23');
insert into t_1 values('232323');
+-----------+
| age |
+-----------+
| 23:23:23 |
| 23:23:23 |
| 143:23:23 |
+-----------+
-- year
create table y_1(
`begin` year
);
insert into y_1 values('2155');