MySQL中的数据类型
MySQL数据库数据类型分类
整数类型
MySQL数据类型 | 含义(有符号) |
---|---|
tinyint(m) | 1个字节 范围(-128~127) |
smallint(m) | 2个字节 范围(-32768~32767) |
mediumint(m) | 3个字节 范围(-8388608~8388607) |
int(m) | 4个字节 范围(-2147483648~2147483647) |
bigint(m) | 8个字节 范围(±9.22*10的18次方) |
数值类型中的长度 m 是指显示长度,并不表示存储长度,只有字段指定 zerofill 时有用
例如: int(3) ,如果实际值是 2 ,如果列指定了 zerofill ,查询结果就是 002 ,左边用 0 来 填充
浮点类型
MySQL数据类型 | 含义 |
---|---|
float(m,d) | 单精度浮点型 8位精度(4字节) m总个数,d小数位 |
double(m,d) | 双精度浮点型 16位精度(8字节) m总个数,d小数位 |
字符类型
MySQL数据类型 | 含义 |
---|---|
char(n) | 固定长度,最多255个字符 |
tinytext | 可变长度,最多255个字符 |
varchar(n) | 可变长度,最多65535个字符 |
text | 可变长度,最多65535个字符 |
mediumtext | 可变长度,最多2的24次方-1个字符 |
longtext | 可变长度,最多2的32次方-1个字符 |
char和varchar:
------------------------------------------------------------------------------------------------------------------------------------------
1. char长度固定, 即每条数据占用等长字节空间;适合用在身份证号码、手机号码等定长。
2. varchar可变长度,可以设置最大长度;适合用在长度可变的属性。
3. text不设置长度, 当不知道属性的最大长度时,适合用text。
按照查询速度: char最快, varchar次之,text最慢。
字符串型使用建议:
------------------------------------------------------------------------------------------------------------------------------------------
1. 经常变化的字段用varchar
2. 知道固定长度的用char
3. 尽量用varchar
4. 超过255字符的只能用varchar或者text
5. 能用varchar的地方不用text
日期类型
MySQL数据类型 | 含义 |
---|---|
date | 日期 YYYY-MM-DD |
time | 时间 HH:MM:SS |
datetime | 日期时间 YYYY-MM-DD HH:MM:SS |
timestamp | 时间戳YYYYMMDD HHMMSS |
二进制数据(BLOB)
- BLOB和TEXT存储方式不同,TEXT以文本方式存储,英文存储区分大小写,而Blob是以二进制方式存储,不分大小写。
- BLOB存储的数据只能整体读出。
- TEXT可以指定字符集,BLOB不用指定字符集。
创建表与删除表
创建表
-
使用DDL语句创建表
CREATE TABLE 表名(列名 类型,列名 类型......);
示例:
创建一个 employees 表包含雇员 ID ,雇员名字,雇员薪水。
create table employees( employee_id int, employee_name varchar(10), salary float(8,2) );
查看已创建的表。
show tables;
删除表
-
使用DDL语句删除表
DROP TABLE 表名;
示例:
删除 employees 表。
drop table employees;
修改表
修改表名
-
使用DDL语句修改表
ALTER TABLE 旧表名 RENAME 新表名;
示例:
创建一个 employees 表包含雇员 ID ,雇员名字,雇员薪水。
create table employees( employee_id int, employee_name varchar(10), salary float(8,2) );
将 employees 表名修改为 emp。
alter table employees rename emp;
修改列名
-
使用DDL语句修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;
示例:
将 emp 表中的 employee_name 修改为 name。
alter table emp change column employee_name name varchar(20);
修改列类型
-
使用DDL语句修改列类型
ALTER TABLE 表名 MODIFY 列名 新类型;
示例:
将 emp 表中的 name 的长度指定为 40。
alter table emp modify name varchar(40);
添加新列
-
使用DDL语句添加新列
ALTER TABLE 表名 ADD COLUMN 新列名 类型;
示例:
在 emp 表中添加佣金列,列名为 commission_pct。
alter table emp add column commission_pct float(4,2);
删除指定列
-
使用DDL语句删除指定的列
ALTER TABLE 表名 DROP COLUMN 列名;
示例:
删除 emp 表中的 commission_pct。
alter table emp drop column commission_pct;
MySQL中的约束
约束概述
数据库约束是对表中的数据进行进一步的限制,保证数据的正确性、有效性和完整性。
-
主键约束(Primary Key) PK
主键约束是使用最频繁的约束。在设计数据表时,一般情况下,都会要求表中设置一个主键。 主键是表的一个特殊字段,该字段能唯一标识该表中的每条信息。例如,学生信息表中的学号是唯一的。
-
外键约束(Foreign Key) FK
外键约束经常和主键约束一起使用,用来确保数据的一致性。
-
唯一性约束(Unique)
唯一约束与主键约束有一个相似的地方,就是它们都能够确保列的唯一性。与主键约束不同的是,唯一约束在一个表中可以有多个,并且设置唯一约束的列是允许有空值的。
-
非空约束(Not Null)
非空约束用来约束表中的字段不能为空。
-
检查约束(Check)
检查约束也叫用户自定义约束,是用来检查数据表中,字段值是否有效的一个手段,但目前 MySQL 数据库不支持检查约束。
添加主键约束(Primary Key)
-
单一主键
使用一个列作为主键列,当该列的值有重复时,则违反唯一约束。
-
联合主键
使用多个列作为主键列,当多个列的值都相同时,则违反唯一约束。
修改表添加主键约束
-
使用DDL语句添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列名)
示例:
将 emp 表中的 employee_id 修改为主键。
alter table emp add primary key(employee_id); ```![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/36235fffe70648aabd92c0374ae3cfe9.png#pic_center)
主键自增长
MySQL 中的自动增长类型要求:
- 一个表中只能有一个列为自动增长。
- 自动增长的列的类型必须是整数类型。
- 自动增长只能添加到具备主键约束与唯一性约束的列上。
- 删除主键约束或唯一性约束,如果该列拥有自动增长能力,则需要先去掉自动增长然 后在删
除约束。
alter table 表名 modify 主键 类型 auto_increment;
示例:
将 emp 表中的 employee_id 主键修改为自增。
alter table emp modify employee_id int auto_increment;
删除主键
-
使用DDL语句删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
注意: 删除主键时,如果主键列具备自动增长能力,需要先去掉自动增长,然后在删除 主键。
示例:
删除emp表中的 employee_id 主键约束。
去掉自动增长:
alter table emp modify employee_id int;
删除主键:
alter table emp drop primary key;
添加外键约束(Foreign Key)
修改表添加外键约束
-
使用DDL语句添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY( 列 名 ) REFERENCES 参照的表名 (参照的列名);
示例:
创建 departments 表包含 department_id 、department_name ,location_id。
create table departments( department_id int, department_name varchar(30), location_id int );
修改departments表,向department_id列添加主键约束与自动递增。
alter table departments add primary key(department_id); alter table departments modify department_id int auto_increment;
修改 emp 表,添加 dept_id 列。
alter table emp add column dept_id int;
向 emp 表中的 dept_id 列添加外键约束。
alter table emp add constraint emp_fk foreign key(dept_id) references departments(department_id);
删除外键约束
-
使用DDL语句删除外键约束。
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;
示例:
删除 dept_id 的外键约束。
alter table emp drop foreign key emp_fk;
添加唯一性约束(Unique)
修改表添加唯一性约束
-
使用DDL语句添加唯一性约束。
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);
示例:
向 emp 表中的 name 添加唯一约束。
alter table emp add constraint emp_uk unique(name);
删除唯一性约束
-
使用DDL语句删除唯一性约束。
ALTER TABLE 表名 DROP KEY 约束名;
示例:
删除 name 的唯一约束。
alter table emp drop key emp_uk;
非空约束(Not Null)
修改表添加非空约束
-
使用DDL语句添加非空约束。
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;
示例:
向 emp 表中的 salary 添加非空约束。
alter table emp modify salary float(8,2) not NULL;
删除非空约束
-
使用DDL语句删除非空约束。
ALTER TABLE 表名 MODIFY 列名 类型 NULL;
示例:
删除emp表中salary 的非空约束。
alter table emp modify salary float(8,2) NULL;
创建表时添加约束
查询表中的约束信息:
SHOW KEYS FROM 表名;
示例:
创建 depts 表包含 department_id 该列为主键且自动增长,department_name 列不 允许重复,location_id 列不允含有空值。
create table depts(
department_id int primary key auto_increment,department_name varchar(30) unique,
location_id int not null
);
NULL;
示例:
删除emp表中salary 的非空约束。
```mysql
alter table emp modify salary float(8,2) NULL;
[外链图片转存中…(img-c3WTuLjZ-1713681238983)]
创建表时添加约束
查询表中的约束信息:
SHOW KEYS FROM 表名;
示例:
创建 depts 表包含 department_id 该列为主键且自动增长,department_name 列不 允许重复,location_id 列不允含有空值。
create table depts(
department_id int primary key auto_increment,department_name varchar(30) unique,
location_id int not null
);