[大数据]MySQL数据类型

数据类型

数据类型主要给列上添加完整性约束。开发者设置列的数据类型后，DBMS会对添加的数据进行合法性验证，如果不合法则报错，拒绝添加

数值型

• 5中整型 tinyint、smallint、mediumint、int和bigint，主要区别就是取值范围不停，还可以在类型前添加一个限制词unsigned，不允许添加负数

• 三种浮点型：不能精确存放float和double，可以精确存放decimal和numeric

unsigned设置列为无符号型，只能存放大于等于0的数据，没有附属。当使用无符号类型时取值范围由于没有复数部分，从而导致上限扩大一倍

create table t(id int unsigned);
?
mysql> insert into tt value(-10); -- 不允许存放负数
ERROR 1264 (22003): Out of range value for column 'id' at row 1
?
mysql> insert into tt value(255); ?--  存储数据的上限扩大一倍
Query OK, 1 row affected (0.01 sec)

可以再类型后添加括号，其中包含一个正整数，例如int(5)，这里的含义并不是要求只能存放5位长度的整数；含义是当进行查询时自动使用空格填充到5个长，如果真实数据长度大于5，则按实际输出

mysql> create table t3(id int(2));
Query OK, 0 rows affected (0.03 sec)
?
mysql> insert into t3 values(9999);
Query OK, 1 row affected (0.01 sec)
?
mysql> insert into t3 values(9);
Query OK, 1 row affected (0.01 sec)
?
mysql> select * from t3;
+------+
| id ? |
+------+
| 9999 |
| ? ?9 |
+------+
2 rows in set (0.00 sec)

decimal和numeric作为字符串存储浮点数，可以实现浮点数的精确存放，并不是float和double中使用二进制浮点数存储。使用方法numeric(总位宽，小数位数)，小数位数必须小于等于总位宽；小数位数最大值30，总位宽最大值65，注意可能存储的数据会超出范围，其中符号位和小数点不占位宽

mysql> create table t1(id numeric (5,3)); -- 总共5位，小数位3位
Query OK, 0 rows affected (0.03 sec)
?
mysql> insert into t1 values(99.999);
Query OK, 1 row affected (0.00 sec)
?
mysql> insert into t1 values(9.9999); -- 小数位数多余则自动四舍五入
Query OK, 1 row affected, 1 warning (0.01 sec)
?
mysql> insert into t1 values(-99.999); -- 位宽统计符号位不算
Query OK, 1 row affected (0.00 sec)
?
mysql> insert into t1 values(100); ?--  整数超出范围报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t1 values(9.12346); -- 按小数位第三位紧邻的第四位四舍五入
Query OK, 1 row affected, 1 warning (0.01 sec)
?
mysql> select * from t1;
+---------+
| id ? ? ?|
+---------+
| ?99.999 |
| ?10.000 |
| -99.999 |
| ? 9.123 |
+---------+
4 rows in set (0.00 sec)

int和numeric

• int类型不能保存小数位，存储小数时会自动进行四舍五入

mysql> create table t2(id int);
Query OK, 0 rows affected (0.02 sec)
?
mysql> insert into t2 values(10.5);
Query OK, 1 row affected (0.01 sec)
?
mysql> select * from t2;
+------+
| id ? |
+------+
| ? 11 |
+------+
1 row in set (0.00 sec)

• numeric 可以保存小数位，如果小数位为0，可以模拟得到int类型的存储效果。采用实际存储方式为字符串，查询效率远低于int

mysql> create table t3(id numeric);
Query OK, 0 rows affected (0.01 sec)
?
mysql> desc t3;
+-------+---------------+------+-----+---------+-------+
| Field | Type ? ? ? ? ?| Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id ? ?| decimal(10,0) | YES ?| ? ? | NULL ? ?| ? ? ? |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)
?
mysql> insert into t3 values(11);
Query OK, 1 row affected (0.01 sec)
?
mysql> select * from t3;
+------+
| id ? |
+------+
| ? 11 |
+------+
1 row in set (0.00 sec)
?
mysql> insert into t3 values(6.0);
Query OK, 1 row affected (0.01 sec)
?
mysql> select * from t3;
+------+
| id ? |
+------+
| ? 11 |
| ? ?6 |
+------+
2 rows in set (0.00 sec)

数值列的扩展属性

• auto_incerment一般用于主键，可以实现该列的自动生成连续整数值

mysql> create table t4(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
?
mysql> insert into t4 values(null,'王五'); -- 插入数据时不指定插入的值或者设置插入值为null则会自动添加一个自增值
Query OK, 1 row affected (0.01 sec)
?
mysql> insert into t4 values(10,'张三'); -- 如果插入数据时不指定对应的值，则自增长max(id+1);如果指定对应的值，则自增效果失效
Query OK, 1 row affected (0.01 sec)
?
mysql> insert into t4(name) values('李四');
Query OK, 1 row affected (0.01 sec)
?
mysql> select * from t4;
+----+------+
| id | name |
+----+------+
| ?1 | 王五 |
| 10 | 张三 |
| 11 | 李四 |
+----+------+
3 rows in set (0.01 sec)

• zerofill如果查询显示时，实际数据小于指定位宽，则自动添加0值

mysql> create table t5(id int(5) zerofill);
Query OK, 0 rows affected (0.02 sec)
?
mysql> insert into t5 values(20);
Query OK, 1 row affected (0.01 sec)
?
mysql> insert into t5 values(14567);
Query OK, 1 row affected (0.01 sec)
?
mysql> select * from t5;
+-------+
| id ? ?|
+-------+
| 00020 |
| 14567 |
+-------+
2 rows in set (0.00 sec)

• default用于设置默认值

mysql> create table t6(id int default 0,name varchar(10));
Query OK, 0 rows affected (0.03 sec)
?
mysql> insert into t6 values(02,'王五'); -- 指定对应的数据，则default无效
Query OK, 1 row affected (0.01 sec)
?
mysql> insert into t6 values(null,'李四'); -- 指定对应的数据，则default无效，即使设置的值为null
Query OK, 1 row affected (0.01 sec)
?
mysql> insert into t6(name) values('张三'); -- 只有不针对这个列进行数据插入时default才能生效
Query OK, 1 row affected (0.01 sec)
?
mysql> select * from t6;
+------+------+
| id ? | name |
+------+------+
| ? ?2 | 王五 |
| NULL | 李四 |
| ? ?0 | 张三 |
+------+------+
3 rows in set (0.00 sec)

nul的意思为未知的数据，它既不是空字符段，不是任何一个具体的值；不能把任何值于一个null值进行比较。因为null的意思就是不确定的值。真正的null值比较必须使用特殊的运算符is null 或者is not null

mysql> select null = null;
+-------------+
| null = null |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select null != null;
+--------------+
| null != null |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

mysql> select null is not null;
+------------------+
| null is not null |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> select null is null;
+--------------+
| null is null |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

完整性约束

DBMS为了保证存储的数据都是完整有效的，避免存放垃圾数据，所以提供了针对插入的数据进行检查

• 开发人员指定对应的规则，由DBMS负责检查，如果试图插入不合法数据，则会自动报错，拒绝插入

• 实际有六种约束，可以分为3大类完整性约束。

  • 实体完整性，由主键约束实现

  • 参照完整性，由外界约束实现

  • 用户自定义完整性，由其他四种约束实现

• 实际开发中除了主键约束外，其他约束一概不添加。其他的数据检查一般依赖应用程序实现，为了代码测试方便

1.非空约束，指定列值不允许为空

create table t1(id int not null); -- 不与怒id列值为null

create table t2(id int null); -- 允许id列值为null
create table t3(id int); -- 允许id列值为null

2.默认约束default

一般默认约束经常和非空约束一起使用，当不插入数据时，默认值生效

create table t1(id int not null default 0);

3.主键约束，又叫做实体完整性约束，不允许数据重复存储

• 主键可以唯一标识一行数据

• 主键约束含义是非空、唯一

create table t1 (id int primary key, .....);
create table t2(
    id int not null, ....
	primary key(id)   -- 注意这里的主键约束允许使用复合主键，多个列构成的主键
);

4.唯一约束

5.外键约束，又叫做参照完整性

6.检查约束

DDL表操作

创建操作

create table tb_student(
	id bigint primary key auto_increment,
    name varchar(10) not null comment '姓名',
    birth date,
    sex boolean default 1,
    salary numeric(8,2)
)engine=innodb default charset utf8;

快速创建一个表和另外一个表的相同结构，只能克隆表结构，不能克隆数据

create table 新表名称 like 已经存在的旧表名称;

l修改表的结构，语法规则为alter table 表名称 add/drop/modify 列名称【类型 约束】

alter table tb_student add class_name varchar(20) default '软件19'; -- 新增列

alter table tb_student drop column salary; -- 删除指定列

alter table tb_student modify class_name int; -- 修改列定义时，要求已经存在的数据必须符合规则，否则修改失败。modify 一般用于修改的数据类型个长度，但是如果没有数据必须和目标类型匹配，否则修改失败

结果如下

mysql> desc tb_student;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)  | NO   |     | NULL    |                |
| birth  | date         | YES  |     | NULL    |                |
| sex    | tinyint(1)   | YES  |     | 1       |                |
| salary | decimal(8,2) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> alter table tb_student add class_name varchar(20) default '软件19';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tb_student drop column salary;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_student;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(10) | NO   |     | NULL    |                |
| birth      | date        | YES  |     | NULL    |                |
| sex        | tinyint(1)  | YES  |     | 1       |                |
| class_name | varchar(20) | YES  |     | 软件19  |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table tb_student modify class_name int;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_student;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(10) | NO   |     | NULL    |                |
| birth      | date        | YES  |     | NULL    |                |
| sex        | tinyint(1)  | YES  |     | 1       |                |
| class_name | int(11)     | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

修改列名称

alter table 表名称 change 旧有列名称 新列名称 类型 约束

删除表drop table 表名称

drop table tb_student;
drop table if exists tb_student;

查看当前数据库中的所有表

show tables;

查看表结构

desc 表名称;

查看创建表的sql语句

mysql> show create table tb_student;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                     |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_student | CREATE TABLE `tb_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `birth` date DEFAULT NULL,
  `sex` tinyint(1) DEFAULT '1',
  `class_name` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

练习题1：创建一个学生表，要求学号字符型，长度为5，不能为空；姓名字符型长度20，不允许重复，性别字符串型长度为1，邮政编码为6，年龄整数，系别字符串型长度15

-- 一般针对一个应用创建一个数据库
create database  if not exists stu_manager default character set utf8;
-- 切换数据库
use stu_manager;
-- 创建表
create table if not exists tb_student(
	son varchar(5) not null,
    sname varchar(20) unique,
    sex char(1) default '男',
    postcode char(6),
    age int,
    dept varchar(15)
)engine=innodb default charset utf8;

向tb_student表中添加一个入学时间的列

• 注意修改表之前，表中已经有数据，则新增列的值为null，除非设置了default默认约束

alter table tb_student add come date;

将年龄字段修改为小整形

• 注意：修改表之前如果表中已经存有数据，修改不能破坏已有数据

alter table tb_student modify age tinyint(3);

删除姓名字段

alter table tb_student drop column name;

练习题2：

创建一个学生选课表，由学号、课程号、成绩组成，请合理定义主键

• 主键=非空+唯一

• 一个表中只能有一个主键，因为主键会影响数据的具体存放位置【索引问题】

• 一个主键可以是由多个列构成。复合主键是由多个列构成的一个主键

create table if not exists tb_choice(
	sno char(5) comment '学号',
    con int comment '课程号',
    score int,
    primary key(son,con)  -- 定义复合主键，con和son列上并没有添加not null约束，但是con和son都不允许为null;但是唯一是指两个属性组合的唯一，并不是其中列的唯一
)engine=innodb default charset uft8;

表操作DML/DQL

• DML数据操纵语言，用于对数据库表中的记录进行更新操作，插入inset、修改update、删除delete

• DQL数据查询语言，用于对数据库表中的记录进行查询操作，select

样例表

cr

eate table tb_users(
	id bigint primary key auto_increment,
    username varchar(20) not null,
    password varchar(24) not null,
    birth date,
    sex boolean default 1,
    score numeric(8,2)
)engine=innodb default charset utf8;

增加数据

语法1：insert into 表名称 values(值1,值2,...)用于针对表中的每个列指定对应的值，例如insert into tb_users values(null,'yan','123456','2001-03-03',1,1234.56);注意values后面的括号的值和列一一对应，注意数据类型

语法二：insert into 表名称(列名1,列名2,...) values(值1,值2,...) 人为指定列插入数据，注意两 个括号一一对应，一般用于不是每个列都需要插入数据时使用。例如 insert into tb_users(username,password,birth) values('yanjun','123456','1989-2-3') 。如果没有出现 的列而且列上没有default约束，则自动插入值null；如果有default约束则插入默认值

注意：

• 值于字段必须对应，包括个数和数据类型

  • 值必须再合理的字段类型范围内

  • varchar、char和日期类型必须使用单引号包裹

  • 如果没有任何限制，控制允许插入

• 没有指定列名称时，必须给出完整数据，而且对应顺序

• 优化写法：指定列名称。无需遵守定义表时列的顺序

• 批量插入查询出的数据

  • insert into table(列名1，列名2,...) select 列1，列2,... from table2;

• 将一个表的查询出的内容插入创建的新表中。不仅克隆表结构，同时克隆表中的数据。单数约束会有丢失问题

  • create table newTable as select * from oslTable;

修改数据

语法1： update 表名称 set 列名1=值1,列名2=值2,... 。修改表中所有行的指定列对应的值，等号后面 可以是一个表达式进行计算。例如score新增10%写法 update tb_users set score=score*1.1 允许同时修改多个列： update tb_users set score=500, sex=0 语法2： update 表名称 set 列名1=值1,列名2=值2,... where 条件用于修改表中满足条件的行指定列 的值；例如1989年之前的用户积分新增10%， update tb_users set score=score*1.1 where year(birth)<1989 ，year是系统函数，用于获取指定列的年份值

删除数据

语法1： delete from 表名称; 删除表中所有数据。例如delete from tb_users;

语法2：delete from 表名称 where 条件;删除表中满足条件的所有行。例如删除1990年的所有女 生， delete from tb_users where sex=0 and year(birth)=1990

整表删除操作

方法1： delete from tb_student;可以删除表中的所有数据，属于DML语句，所以支持事务，支持撤 销回滚操作，但是大量的删除数据时需要记录日志，所以执行效率很低。优势在于可以反悔，缺点在于 记录日志需要时空代价

方法2： truncate table tb_student; 表的截断操作，可以删除表中的所有数据，属于DDL语句，没 有事务，不会记录操作日志，所以大量删除数据时执行效率高。优势是执行效率，缺点在于没有反悔机会。底层原理就是创建一个相同的空表

逻辑删除和物理删除

所谓的逻辑删除就是从表中直接删除数据；逻辑删除类似于回收站，只是给某行数据上添加一个删除标志，表示该行数据已经删除了

逻辑删除的具体实现：

1.修改表tb_users新增一个额外列，用于表示该行数据是否已经删除

alter table tb_users add deleted boolean default 0; ?-- 0表示没有删除

2.逻辑删除，并没有真实数据删除，只是修改一个表示列的值

update tb_users set deleted =1 where id = 5;-- 删除五号用户信息  delete from tb_users where id = 5;物理删除

3.真正查询时为了避免查到已经删除的数据，所以需要额外的查询条件

select * from tb_users where deleted=0; ?-- 只查询deleted值为0的行，不要已经标识删除的函数deleted=1

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖