Day 39 表操作及字段类型

Mysql 操作表

增

create table xxx(id int, name char);  # 创建表xxx并在表下创建id和name字段，他们的类型为 int 和 char

删

drop table xxx;  # 删除表xxx

改

修改表名

alter table xxx rename mmm； # 改名为mmm

修改字段

alter table xxx modify id tinyint;  # 修改表中id字段的类型为 tinyint

alter table xxx change id ID tinyint;  # 修改表中id字段为ID并将它类型改为 tinyint

alter table xxx cahnge id ID tinyint,change name Name varchar(4);  # 修改多个修改意义同上

删除字段

alter table xxx drop ID；  # 删除表xxx中的ID字段 

增加字段

alter table xxx add gender char(4);  # 给表xxx添加gender字段 类型为char 默认加在最后面

alter table xxx add gender char(4) first;  # 同上加在最前面

alter table xxx add gender char(4) after ID;  # 同上 加载字段ID后面

复制表

create table excel2 select user,host, password from mysql.user;  # 创建表excel2 并从mysql下的user表选取字段user，host，password字段及内容添加到表中

# 只复制表结构
create table excel2 select * from mysql.user where1=3;# 创建表excel2 并从mysql下的user表选取所有字段字段及内容添加到表中，但是由于限制条件where 1=3 为false 所以并没有实质的内容 只有表的结构

查

show tables;  # 查看多有的表
show create table xxx;  # 查看指定的表
desc xxx;  # 查看表xxx的结构

表–字段类型

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的

数字：

    整型：tinyinit  int  bigint
    小数：
        float ：在位数比较短的情况下不精准
        double ：在位数比较长的情况下不精准
            0.000001230123123123
            存成：0.000001230000

        decimal：（如果用小数，则用推荐使用decimal）
            精准
            内部原理是以字符串形式去存

小数类型

create table excel1(x float(255,30), y double(255,30), z decimal(65,30));  # 创建表excel1 字段xyz类型为 float double decimal decimal（65,30）表示共有65位数，其中整数35位，小数30位包含小数点 

insert excel1 values(1.11111111111111111111111111111111111,
                    1.11111111111111111111111111111111111,
                    1.11111111111111111111111111111111111);  # 我们存入三个相同的数据
select * from excel1;  # 查看excel1 下的所有数据
---------------------------------------------------
+----------------------------------+----------------------------------+----------------------------------+
| x                                | y                                | z                                |
+----------------------------------+----------------------------------+----------------------------------+
| 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111111111111111 |
+----------------------------------+----------------------------------+----------------------------------+

整数类型

        tinyint[(m)] [unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127  # 有符号时存储的范围 超出报错  默认都是有符号的
            无符号：
                0 ～ 255

            PS： MySQL中无布尔值，使用tinyint(1)构造。
            
insert excel2 values(-130);
------------------------------------------------------
ERROR 1264 (22003): Out of range value for column 'x' at row 1


========================================
        int[(m)][unsigned][zerofill]

            整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -2147483648 ～ 2147483647  # 同上
            无符号：
                    0 ～ 4294967295



========================================
        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -9223372036854775808 ～ 9223372036854775807
            无符号：
                    0  ～  18446744073709551615

example

create table t1(x tinyint（8）); #默认为有符号，即数字前有正负号


insert into excel2 values (-129),(-128), (127),(128);

注意：

• 只有整型括号里面的数字不是表示限制位数
• 针对整型字段,括号内无需指定宽度,存储宽度是固定死的
• 如果输入数字不满足自定义位数，会用0填充至自定义位数，取出会自己去掉
• 如果输入数字超出自定义位数，那么有几位就存几位（但是要遵守最大范围255**n）

日期类型

分类:

• year（1901/2155）
• time 时:分:秒 （’-838:59:59’/‘838:59:59’）
• date 年:月:日 （1000-01-01/9999-12-31）
• datetime 年:月:日 时:分:秒 1000-01-01 00:00:00/9999-12-31 23:59:59
• timestamp 年:月:日 时:分:秒 1970-01-01 00:00:00/2037 （快不够用了）

create table excel2(y year,t time,d date,dt datetime,ts timestamp);
insert t8 values(now(),now(),now(),now(),now());  # sql 内置函数 用来获取当前日期
select * from excel2;
-------------------------------
+------+------------+---------------------+---------------------+
| y    | d          | dt                  | ts                  |
+------+------------+---------------------+---------------------+
| 2020 | 2020-09-02 | 2020-09-02 07:21:47 | 2020-09-02 07:21:47 |
+------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

############################
create table student(
    id int,
    name char(10),
    born_year year,
    bitrh date,
    reg_time datetime
);


insert student values
insert student values(1, "jack", 2000, 20000901);,
(2,"lxx","1988","1988-11-11 11:11:11");  # 是不是字符类型没啥区别
select * from student;
-------------------------------------------------
+------+------+-----------+---------------------+
| id   | name | born_year | reg_time            |
+------+------+-----------+---------------------+
|    1 | jack |      2000 | 2000-09-01 00:00:00 |
|    2 | lxx  |      1988 | 1988-11-11 11:11:11 |
+------+------+-----------+---------------------+
2 rows in set (0.00 sec)




# 注意：timestamp应该勇于记录更新时间，因为可以设置为默认now 所以时间是随动的。
create table t9(
    id int,
    name varchar(16),
    -- update_time datetime not null default now() on update now(),   # 在sql语句中 -- 代表注释语句
    update_time timestamp,
    reg_time datetime not null default now()
);  # 不为空 默认为 now（）


insert into t9(id,name) values(1,"egon");
-----------------------------------------
mysql> select * from t9;
+------+------+---------------------+---------------------+
| id   | name | update_time         | reg_time            |
+------+------+---------------------+---------------------+
|    1 | egon | 2020-09-01 16:45:51 | 2020-09-01 16:45:51 |
+------+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update t9 set name="EGON" where id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t9;
+------+------+---------------------+---------------------+
| id   | name | update_time         | reg_time            |
+------+------+---------------------+---------------------+
|    1 | EGON | 2020-09-01 16:46:50 | 2020-09-01 16:45:51 |
+------+------+---------------------+---------------------+
1 row in set (0.00 sec)

**注意；**除了 timestamp 类型支持系统默认值设置，其他类型都不支持。

ts_time1 time NOT NULL DEFAULT NOW();
ts_time3 yearNOT NULL DEFAULT NOW();
ts_time2 date NOT NULL DEFAULT CURRENT_TIMESTAMP();
ts_time2 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP();1234

都会报错。所以想要设置某个日期列的默认值为当前时间，只能使用 timestamp 类型，并设置 DEFAULT NOW() 或 DEFAULT CURRENT_TIMESTAMP() 作为默认值。

字符类型

char：定长，不够补全空格，取出时删掉空格

看起来特点:
	浪费空间
	读取速度快	

varchar：边长，预留1-2bytes来存储 真实数据的长度

看起来特点:
	节省空间
	读取速度慢
ps:在存储的数据量刚好达到存储宽度限制时,其实varchar更费空间

总结:
大多数情况下存储的数据量都达不到宽度限制,所以大多数情况下varchar更省空间,但省空间不是关键,关键是省空间,会带来io效率的提升,进而提升了查询效率

验证

create table t11(x char(5));
create table t12(x varchar(5));

insert t11 values("我擦嘞 ");  -- "我擦嘞  "
insert t12 values("我擦嘞 ");  -- "我擦嘞 "

t11=>字符个数 5  字节个数 11
t12=>字符个数 4  字节个数 10

set sql_mode="pad_char_to_full_length";
select char_length(x) from t11;
select char_length(x) from t12;


select length(x) from t11;
select length(x) from t12;
12345678910111213141516

枚举与集合类型

分类:

枚举类型enum('a','b','c','d')多选一
集合类型set('a','b','c','d')多选多
12

具体使用:

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');

CREATE TABLE user (
    name VARCHAR(16),
    hobbies set("read","chou","drink","tang")
);
insert user values("lxx","tang,chou");
insert user values("hxx","tangchou");