mysql 学习笔记 day02

-- 查看变量
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');