MySQL操作汇总3——数据类型+建表约束

最新推荐文章于 2021-04-15 18:27:59 发布
最新推荐文章于 2021-04-15 18:27:59 发布
阅读量329 收藏 1
点赞数
分类专栏： SQL 文章标签： mysql java 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38596491/article/details/106483177
版权

一、数据类型：

1、数值型

（1）整型：

整数类型（Tinyint(1)、Smallint(2)、Mediumint(3)、Int/integer(4)、Bigint(8)）

int:默认有符号(-32768~32767)，设置无符号：t1 INT UNSIGNED

t2 INT(5) ZEROFILL #长度不够5，左边用0填充，并且默认为无符号型。

（2）小数：

1）浮点型：float(4字节)、double(8字节)——或者：float(M,D)
2）定点型：精确度较高的小数

DEC(M,D)：M可省略，M为整数部分+小数部分最大总长度，D为小数部分最大位数，字节大小为M+2，最大范围同double，

而float和double只要别超最大范围就行，会根据插入数值的精度来决定精度。
**原则：**所选择类型越简单越好，能保存数值情况下，类型越小越好。

2、字符型：

（1）较短的文本

char(M):该字段最多的字符数为M(0~255，可以省略，默认为1)，固定长度字符，比较耗费空间，效率高. eg: sex char
varchar(M)：可变长度字符(M为0~65535之间的整数)，比较节省空间，效率低

（2）较长的文本

text、blob（较长的二进制数据）

（3）其他

binary和varbinary：用于保存较短的二进制
enum：用于保存枚举
set：用于保存集合

#1.enum枚举
CREATE TABLE tab_char(
   c1 ENUM('a','b','c')#枚举，只能插入a、b、c三种，不分大小写
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('m');
INSERT INTO tab_char VALUES('A');

SELECT * FROM tab_char;

#2.set集合
CREATE TABLE tab_set(
	s1 SET('a','b','c','d')#多选字符串数据类型，适合存储预设的“多个值”,
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('a,c,d');

SELECT * FROM tab_set;

3、日期型

在这里插入图片描述

#3.
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP #时间戳
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;

在这里插入图片描述

SET time_zone = '+9:00';
SHOW VARIABLES LIKE 'time_zone';

在这里插入图片描述

二、常见约束

添加列级约束：

1、主键约束：primary key

它能够唯一确定一张表中的一条记录，也就是我们通过给某个子段添加约束，就可以使得该字段不重复，
特点：一张表最多一个主键，不允许为空null。

create table uesr(
     id int primary key,   （primary key：主键约束）
     name varchar(20)
);
insert into uesr values(1,'张三');

insert into uesr values(1,'张三');

ERROR 1062 (23000): Duplicate entry ‘1’ for key 'PRIMARY’
——保证输入不会重复

insert into uesr values(2,'张三');

在这里插入图片描述
——用于唯一确定一条记录
在这里插入图片描述
对多个数据进行约束：——联合主键

 create table user1(
     id int,
     name varchar(20),
     password varchar(20),
     primary key(id,name)   （id和name都是主键）
 );
insert int user1 values(1,'张三','123');
insert into user1 values(2,'张三','123');

在这里插入图片描述
如果忘记加主键约束，可以修改表结构，添加主键：

alter table user1 add primary key(id)

也支持删除主键约束：

alter table user1 drop primary key;

2、自增约束:auto_increment

结合主键约束一起使用primary key

create table user2(
    id int primary key auto_increment,
    name varchar(20)
 );
insert into user2 (name) values('zhangsan');
insert into user2 (name) values('zhangsan');
select * from user2;

在这里插入图片描述
——管理加了自增约束的值，让它可以自动增长。

3、唯一约束：unique

——约束修饰的字段的值不可以重复

特点：一张表可以有多个唯一健，允许为空null。

create table user5(
     id int,
     name varchar(20)
);

添加约束方式一：add unique()

alter table user5 add unique(name);
desc user5;

在这里插入图片描述

 insert into user5 value(1,'zhangsan');
 insert into user5 value(2,'zhangsan');

ERROR 1062 (23000): Duplicate entry ‘zhangsan’ for key ‘name’——加了唯一约束的变量重复才会报错

添加约束方式二：在创建表时添加唯一约束

create table user5(
    id int,
    name varchar(20) unique
    );

或者：

create table user5(
     id int,
    name varchar(20),
    unique(id,name)
 ); #表示对id,name同时添加约束，要两个都重复才报错。

desc user6;

在这里插入图片描述
添加多个主键约束时，会显示MUL，表示多个。

添加约束方式三：modify

alter table user5 modify name varchar(20) unique;

删除唯一约束：

alter table user5 drop index name;

在这里插入图片描述
——唯一约束的标志不见了。

4、非空约束：not null

——not null所修饰的字段不能为空！

create table user8(
    id int,
    name varchar(20) not null
);

在这里插入图片描述

 insert into user8 (id) values(1);

ERROR 1364 (HY000): Field ‘name’ doesn’t have a default value
——因为name加了非空约束，因为不能只给id传数据，而不给name传数据

insert into user8 values(1,'张三');

insert into user8 (name) values('李四');

——不给未加非空约束的变量传数据是可以的。
在这里插入图片描述

5、默认约束：default num

——当我们插入字段值的时候，如果没有传值，就会使用默认值。

create table user9(
   id int,
   name varchar(20),
   age int default 10   #默认值是10
 );

在这里插入图片描述

insert into user9 (id,name)values(1,'张三');

——只给前两个字段插入值
在这里插入图片描述
——会添加默认值

6、外键约束：foreign key

——涉及到两个表：主表、从表

【案例分析】

班级表：——主表

create table classes(
    id int primary key,
    name varchar(20)
);

学生表：——从表

create table students(
    id int primary key,
    name varchar(20),
    class_id int,
    foreign key(class_id) references classes(id)
);

 desc classes;

在这里插入图片描述

desc students;

在这里插入图片描述

insert into classes values(1,'一班');
insert into classes values(2,'二班');
insert into classes values(3,'三班');
insert into classes values(4,'四班');

在这里插入图片描述

insert into students values(1005,'李四','5');#主表中id没有为5的

-ERROR 1452 (23000):

——主表（父表）classes中没有的数据，在从表（子表）中是不可以使用的。

delect from classes where id=4;

ERROR…
——如果主表中的记录被从表引用，是不可以被删除的。

【外键总结】：

（1）要求在从表设置外键关系；
（2）从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求；
（3）主表的关联列必须是一个key（一般是主键primary key或唯一键,，就是关联唯一不重复的）
主：id int primary key,
从：foreign key(class_id) references classes(id)
（4）插入数据时，先插入主表，再插入从表——主表classes中没有的数据，在从表中是不可以使用的；
（5）删除数据时，先删除从表，再删除主表——如果主表中的记录被从表引用，是不可以被删除的。

综合应用案例：

（1）添加列级约束

列级约束语法：直接在字段名和类型后面追加约束类型即可

CREATE DATABASE students;
USE students;

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键约束
	stuName VARCHAR(20) NOT NULL,#非空约束
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#性别，检查约束
	seat INT UNIQUE,#唯一约束
	age INT DEFAULT 18, #默认约束
	majorID INT REFERENCES major(id) #外键约束
	#或者上一行references不写，这一行写：
	#foreign key(majorID) reference major(id)
);

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

SHOW INDEX FROM stuinfo;#查看表中所有索引

在这里插入图片描述
在这里插入图片描述

（2）添加表级约束

DROP TABLE IF EXISTS stuinfo;#删了再建
CREATE TABLE stuinfo(
	id INT,
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorID INT,
	
	#表级约束
	CONSTRAINT pk PRIMARY KEY(id),#主键约束，用CONSTRAINT来命名为pk
	CONSTRAINT uq UNIQUE(seat),#唯一健
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查约束
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorID) REFERENCES major(id) #外键约束
);

SHOW INDEX FROM stuinfo;#查看表中所有索引

在这里插入图片描述

（3）推荐的通用写法：

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键约束
	stuName VARCHAR(20) NOT NULL,#非空约束
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#性别，检查约束
	seat INT UNIQUE,#唯一约束
	age INT DEFAULT 18, #默认约束
	majorID INT,
	
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorID) REFERENCES major(id) #外键约束
);

【经典面试题】主键约束 和 唯一约束的对比

       保证唯一性    是否允许为空        一个表中可以有多少个      是否允许组合
 主键       √            ×                    至多1个            √,但不推荐          
 唯一       √            √,一列只能插一个null 可以有多个          √,但不推荐

组合主键：

CREATE TABLE stuinfo(
	id INT,
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorID INT,

	PRIMARY KEY(id,stuName),#添加组合主键
	UNIQUE(seat),#唯一健
	CHECK(gender='男' OR gender='女'),#检查约束
	FOREIGN KEY(majorID) REFERENCES major(id) #外键约束
);

【外键特点】
1）要求在从表设置外键关系；
2）从表的外键列的类型和主表的关联列类型一致；
3）主表的关联列必须是一个key
4）插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表。

修改表时添加约束：ALTER

（1）列级约束
alter table 表名 modify column 字段名 字段类型 新约束；
（2）表级约束
alter table 表名 add 【constraint 约束名】 约束类型（字段名） 【外键的引用】;

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

#修改表时添加约束
CREATE TABLE stuinfo(
	id INT,
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorID INT
);
#1、添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;#列级约束
#2、添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;#列级约束
#3、添加主键约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;#列级约束写法
ALTER TABLE stuinfo ADD PRIMARY KEY(id);#表级约束写法
#4、唯一键约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5、外键约束
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorID) REFERENCES major(id);#表级即可
DESC stuinfo;

在这里插入图片描述

修改表时删除约束：

#1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NULL;
#2、删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
#3、删除主键约束
ALTER TABLE stuinfo MODIFY COLUMN id INT;
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4、删除唯一约束
ALTER TABLE stuinfo DROP INDEX seat;
#5、删除外键约束
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

三、标识列（自增长列）:auto_increment

——自增长列：可以不用手动地插入值，系统提供默认的序列值

#标识列
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO tab_identity VALUE(NULL,'John');#自增长部分可以不用管

SELECT * FROM tab_identity;

插入语句执行几次，id就自增长几次：
在这里插入图片描述
也可以设置步长后再插入：

SET auto_increment_increment=3;#设置自增长步长为3
INSERT INTO tab_identity VALUE(NULL,'John');
SELECT * FROM tab_identity;

在这里插入图片描述
如果要设置起始值，设置第一个插入的值为起始值即可：——从10开始自增长

INSERT INTO tab_identity VALUE(10,'John');
INSERT INTO tab_identity VALUE(NULL,'John');

特点：

1、标识列必须和主键搭配吗？——不一定，但要求必须是一个key（主键、外键、唯一）；
2、一个表中可以有几个标识列？——不可以，一个表至多一个自增长列；
3、标识列类型：只能是数值型（int 、float 、double ）
4、标识列可以通过语句 SET auto_increment_increment=3设置步长，也可以通过手动插入设置起始值。

修改表时设置标识列：

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

修改表时删除标识列：

ALTER TABLE tab_identity MODIFY COLUMN id INT;
等待的疲倦
关注
专栏目录
MYSQL数据库基本数据类型及建表和约束
08-01
MYSQL数据库基本数据类型及建表和约束
如何创建和使用自定义的数据类型
jiahua123_dongzhou的博客
09-02 907
如何创建和使用自定义的数据类型
MYSQL增加已有字段唯一索引，查看唯一约束，删除唯一约束
宇默
08-01 2206
增加已有字段唯一索引： alter table 表名称 add unique(需要添加唯一性的字段，也会作为唯一名称)； 查看唯一约束： show keys from 表名称; 删除唯一约束： drop index 索引名称 on 表名称;...
pg订阅oracle修改,PG修改字段
weixin_34012355的博客
04-15 440
今天又遇到一个需求，要把PG中的字段类型修改一下。本来以为是个很简单的事情，毕竟Oracle就是一条指令就行了。但是在PG中改字段真的真的太难了。当你修改表字段的时候，会报ERROR: cannot alter type of a column used by a view or rule.这主要是因为这个表上存在视图或者是rule，rule这里代表是触发器。所以在PG中它不能像Oracle那样修...
MySQL创建表时的约束（Constraint）
柳落青
05-28 1万+
创建表时的约束什么是约束？常见的约束有那些？1：非空约束：not null2：唯一性约束（unique）3：主键约束（primary key）4：外键约束（foreign key）*外键值是否可以为null？*外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？ 什么是约束？ 在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性，有效性，完整性。 常见的约束有那些？ 非空约束（not null）：约束的字段不能为null 唯一约束（unique）：约束的字段不能重
（2）MySQL——————DML，DDL，数据类型，约束以及事务
12-14
3，数据类型 3.1，整型 3.2，浮点型 3.3，字符型 3.4，日期型 4，约束 4.1，什么是约束 4.2，NOT NULL 约束 4.3，UNIQUE 约束 4.4，PRIMARY KEY 约束 4.5，FOREIGN KEY 约束 5，事务 5.1，什么是约束 5.2，事务的...
mysql学习——数据表的基本操作
12-14
嗯，接上一章数据库的基本操作后，这篇是基于数据表的基本操作，我们将数据库建立好后，就有创建数据表，数据库好比一个文件柜，而数据表才是存放数据的地方，，数据表的...字段名3，数据类型[完整约束条件] ); 这样咱
MySQL常见数据类型——数值型1
08-08
3. Mediumint： Mediumint 是 MySQL 中的第三小的整型数据类型，占用 3 字节空间。其有符号范围为 -8388608~8388607，无符号范围为 0~1677215。 4. Int/Integer： Int 和 Integer 是 MySQL 中的第四小的整型数据类型...
DDL常见约束
lsssssssssssy的博客
03-03 276
DDL常见约束 /* 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性 分类：六大约束 NOT NULL：【非空】，用于保证该字段的值不能为空 比如姓名、学号等 DEFAULT: 默认，用于保证该字段有默认值 比如性别 PRIMARY KEY: 主键，用于保证该字段的值具有唯一性，并且【非空】 比如学号、员工编号等 UNIQUE: 唯一，用于保证该字段的值...
mysql数据库练习题
林中明月间丶
11-08 7681
有一段时间想好好的练习一下数据库 就去网上找了好多练习题目。但是这些练习题目都感觉好少 所以我把我搞到的所有数据库练习题 综合了一下起来。喜欢的小好伙伴们赶紧收藏好了撒。一次够你练习几个小时了 别坚持不下去了撒   那我就贴出来了 //登录和退出数据库 1：mysql -u root -p//输入密码 2: exit 3: mysql -u root -p create
MySQL 多表查询时 [Err] 1060 - Duplicate column name 'ID'
热门推荐
jcroad的博客
09-28 3万+
1、Duplicate column name 'ID'分析         1.1、第一种情况：               MySQL这个这个错误的一种情况，保存数据时，id重复         1.2、第二种情况               多表关联查询，只有一层查询时，id重复不会报错，但是如果再套一层查询，就报这个错误。              eg: SELECT a...
MySQL一些简单的操作题
VeryBeauty1123的博客
09-18 1029
1.学校想做一个选课系统，其中涉及到课程表，学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。 create table course ( course_name varchar(10) primary key, course_amount int, course_class char(5) ) create table student( stuid varcha...
MYSQL插入报错:SQLSTATE[HY000]: General error: 1364 Field 'xxxxx' doesn't have a default value
梦华空影的博客
07-18 3万+
mysql报错提示： SQLSTATE[HY000]: General error: 1364 Field ‘xxxxx’ doesn’t have a default value 这个报错翻译过来就是： xxxxx字段没有默认的数值；也就是说我们没有为其分配数值，而表中此字段也没有设置默认值。这是MySQL5出来后搞出来的东东，认真看一下my.ini文件。 我用的宝塔查看了下mysql配置： #...
表的创建
weixin_34279061的博客
10-18 115
创建一个管理系统 create database jwglxt 　　//jwglxt为系统名 创建一个表 create table student　　　　//student为表名 (sno char(10) not null,　　　　//表的内容 sname char(8), ssex char(2)) 创建主键约束 Tno char(8) primary key 　　　　...
MySql给表添加列和注释
weixin_34162629的博客
03-16 6050
1.给表添加列 ALTER TABLE supplier_seller ADD COLUMN company_id INT NULL COMMENT '供应主体id'; 复制代码默认情况下，添加的列会添加到最后一列。 如果要求添加到指定位置，语句如下： ALTER TABLE supplier_seller ADD COLUMN company_id INT NULL COMMENT '供应主体i...
mysql建表auto_increment_mysql create table auto_increment
weixin_32329555的博客
02-02 4192
当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序 列编号。默认编号从1开始(如果未指定auto_increment开始值，如果指定开始值，则从指定值开始， 比如...
MySQL_约束（级联操作）
weixin_44664432的博客
10-26 796
1. 概述 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。 分类： 1. 主键约束：primary key 2. 非空约束：not null 3. 唯一约束：unique 4. 外键约束：foreign key 2.非空约束 非空约束：not null，某一列的值不能为null 1. 创建表时添加约束 CREATE TABLE stu1( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 ); 2. 创建表
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值