MYSQL数据库表的数据类型、约束条件、修改表结构、增删改数据

MYSQL数据库、表基本操作

一、常用数据类型

1.1、整型类型

数据类型	所占字节	存储范围（有符号）	存储范围（无符号）
TINYINT	1字节	-128～127	0～255
SMALLINT	2字节	-32768～32767	0～65535
MEDIUMINT	3字节	-8388608～8388607	0～16777215
INT	4字节	-2147483648～2147483647	0～4294967295
BIGINT	8字节	-9223372036854775808～9223372036854775807	0～18446744073709551615

1.2、浮点类型和定点类型

浮点数类型分为两种，单精度（FLOAT）和双精度（DOUBLE），而定点数类型只有DECIMAL一种。

浮点数类型和定点数类型都可以使用类型名称后面加“(M,D)”的形式来定义，其中M称为精度，表示数值的位数（整数位+小数位）；D称为标度，表示小数点后的位数。

数据类型	占用字节	负数存储范围	非负数存储范围
FLOAT	4字节	-3.402823466E+38～-1.175494351E-38	0和1.175494351E-38～3.402823466E+38
DOUBLE	8字节	-1.7976931348623157E+308～-2.2250738585072014E-308	0和2.2250738585072014E-308～1.7976931348623157E+308
DECIMAL	M+2字节	与DOUBLE相同	与DOUBLE相同

1.3、日期和时间类型

数据类型	占用字节	存储格式	存储范围
YEAR	1字节	YYYY	1901～2155
DATE	3字节	YYYY-MM-DD	1000-01-01～9999-12-31
TIME	3字节+小数秒存储	HH:MM:SS	-838:59:59～838:59:59
DATETIME	5字节+小数秒存储	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00～9999-12-31 23:59:59
TIMESTAMP	4字节+小数秒存储	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC～2038-01-19 03:14:07 UTC

1.4、字符串类型

类型名称	占用字节	存储范围
CHAR(M)	M*w字节	0<=M<=255
VARCHAR(M)	L+1字节	0<=M<=65535
BINARY(N)	N字节	0<=N<=255
VARBINARY(N)	L+1字节	0<=N<=65535
BLOB	L+2字节	L<216
TEXT	L+2字节	L<216
ENUM	1或2字节	0～65535
SET	1、2、3、4或8字节	最多64个成员

其中，TEXT类型和BLOB类型都用于存储比较大的数据。它们的不同之处在于TEXT类型存储的是文本字符串，常用于存储文章或新闻的正文；BLOB类型存储的是二进制字符串，常用于存储图片和音乐等文件。

二、数据完整性约束

2.1、主键约束-PRIMARY KEY

主键，也称主码，用于标识表中唯一的一条记录。一张表中只能有一个主键，并且主键值不能为空。设置主键约束的关键字为 PRIMARY KEY

-- 字段 数据类型(长度) primary key
或者
-- primary key(字段)

2.2、自增约束-AUTO_INCREMENT

在向数据表中插入数据时，如果用户希望每条记录的“编号”自动生成，并且按顺序排列，可以为该字段设置自增约束。设置自增约束的关键字为AUTO_INCREMENT。

-- 字段 数据类型（长度） auto_increment
-- 注意：
	-- 一张表中只能设置一个字段为自增约束，并且该字段必须为主键。
	-- 默认的初始值为1，每增加一条记录，字段值自动增加1。
	-- 字段类型必须为整数型。

2.3、非空约束-NOT NULL

设置非空约束的关键字为NOT NULL，作用是规定字段的值不能为空，用户在向数据表中插入数据时，设置非空约束的字段必须要给值。

-- 字段 数据类型（长度） not  null

2.4、唯一约束-UNIQUE

设置唯一性约束的关键字为UNIQUE，作用是规定字段的值不能重复，比如学号字段，身份证字段就可设置唯一约束

-- 字段 数据类型（长度） unique
或者
-- unique(字段)

2.5、默认约束-DEFAULT

设置默认约束的关键字为DEFAULT,作用为该字段设置一个默认值，当添加数据时不给该字段添加值时，该字段就会填充默认值

-- 字段 数据类型（长度） default '默认值'

2.6、外键约束-FOREIGN KEY

设置外键约束的主要作用是保证数据的完整性。外键可以不是所属数据表的主键，但会对应着另外一张数据表的主键，例如有两个表：学生表和班级表之间具有一定关系，学生表中会有一个字段存储班级的编号，而此字段就能关联班级表的信息，通过该字段就能查询到班级信息，那么就可以将班级表的主键引入到学生表中作为外键。

CONSTRAINT 外键名 FOREIGN KEY(外键字段) REFERENCES 主键表(主键)

三、数据库基本操作

数据库的基本操作主要包括创建、查询、更新和删除，以下是相应的语法示例：

#创建数据库：
	CREATE DATABASE database_name;
#判断数据库是否存在，如果不存在则创建：
	CREATE DATABASE IF NOT EXISTS database_name;
#创建数据库并指定字符集：
	CREATE DATABASE database_name CHARACTER SET charset_name;
	
#查询所有数据库的名称：
	SHOW DATABASES;
#查询某个数据库的字符集：
	SHOW CREATE DATABASE database_name;
	
#选择数据库
	use database_name;
#删除数据库
	drop database database_name;
	

四、数据表结构的基本操作

数据表结构的操作，包括创建表，修改表结构，对表中的字段进行添加，删除，修改，删除表等操作

4.1、创建数据表语法

创建数据表，实际上是规定列属性和实现数据完整性约束的过程，基本语法形式如下：

1.创建一个数据表
	1.1建表语法：
		create  table 表名(
			字段名1 数据类型（长度） 约束条件 comment'注释',
			字段名2 数据类型（长度） 约束条件 comment'注释'
		);
	1.2约束条件
		主键约束	primary key
		自增约束	auto_increment
		非空约束	not NULL
		唯一约束	unique
		默认约束    default 默认值
		外键约束    ONSTRAINT key_name FOREIGN KEY(child_col_name) REFERENCES parent_table_name(parent_col_name)

2.查看数据库表
	2.1查看表结构
		desc 表名;
	2.2查看建表语句
		show create table 表名;
3.修改（alter）表结构
	3.1修改表名
		alter table 表名 rename  新表名;
	3.2修改表的字段的数据类型
		alter table 表名 modify 字段 新类型 [约束条件]; 
	3.2修改表的字段
		alter table 表名 change 旧字段 新字段 类型 [约束条件];
	3.3添加表的字段
		alter table 表名 add 字段名 数据类型 [约束条件];
	3.4删除表的字段
		alter table 表名 drop 字段名;
4.删除表
	drop table [if exists] 表名;

4.2、创建数据表示例

根据下表的创建商品信息表

create table goods (
	id int(11) not null primary key  auto_increment comment'商品编号',
	type varchar(30) not null comment '商品类型',
	name varchar(20) unique comment '商品名称',
	price decimal(7,2) comment '商品价格',
	num int(11) default 0 comment'商品库存',
	add_time datetime comment '添加时间'
);
#设置主键约束和唯一约束的第二种方式
create table goods1(
	id int(11) auto_increment  comment'商品编号',
	name varchar(20) comment '商品名称',
	type varchar(30) not null,
	price decimal(7,2) comment '商品价格',
	num int(11) default 0 comment'商品库存',
	add_time datetime comment '添加时间',
	primary key(id),  #主键约束
	unique(name)  # 唯一约束
);

4.3、查看表结构

-- 查看表的基本结构
describe goods1;
desc goods1;
-- 查看建表语句
show create table goods1;

4.4、修改表结构

-- 创建表
drop table if exsits user;
create table user(
	id int(10) not null auto_increment primary key,
	name varchar(20) not null,
	nickname varchar(20) not null,
	password varchar(20) not null,
	sex tinyint(1),
	email varchar(20) not null,
	cellphone char(10) not null,
	date int(10) not null
);
-- 1.修改表名： alter table 旧表名 rename 新表名 ;
alter table user rename tb_user;

-- 2.修改字段的数据类型： alter table 表名 modify 字段名 新的数据类型 [约束条件]
alter table tb_user modify date  datetime;

-- 将 tb_user表汇中的date字段修改为 create_date
-- 3.修改表的字段： alter table 表名 change 旧字段 新字段 数据类型 [约束条件]
alter table tb_user change date create_date datetime;

-- 4.添加字段：alter table 表名 add  字段名称 数据类型(长度) [约束条件]
-- 向tb_user表中添加一个用户头像字段
alter table tb_user add img_url varchar(50);
-- 向tb_user表中添加一个身份证号的字段，添加到邮箱（email)字段之后
alter table tb_user add sfzh varchar(18) after email 

-- 5.删除字段：alter table 表名 drop 字段名称
-- 删除tb_user表中的img_url字段
alter table tb_user drop img_url

-- 6.删除表 ：drop table [if exists] 表名;
drop table if exists tb_user;

五、数据的增删改

5.1、向表中添加数据-insert

-- 语法:
	-- insert into 表名(字段1,字段2,字段3,...,字段n) values(值1,值2,值3,...,值n);
-- 示例：向商品表中添加一条数据
	insert into goods(id,name,type,price,num,add_time) values(null,"三国演义","书籍",79.8,200,"2023-11-29");
	insert into goods(name,type,price,num,add_time) values("水浒传","书籍",79.8,200,"2023-11-29");
	insert into goods(name,type) values("西游记","书籍");
	insert into goods values(null,"书籍","红楼梦",30.9,20,null);
-- 注意：
	-- 1.字段需要和值进行一一对应（顺序，类型，个数）
	-- 2.自增长的主键不需要手动添加值,可以使用 null 填充
	-- 3.not null 约束的字段 必须要给值
	-- 4.字段可以省略，省略了字段之后，默认添加所有字段的值。
-- 通过添加多个值：
	-- insert into 表名(字段1,字段2,字段3,...,字段n) values(值1,值2,值3,...,值n),(值1,值2,值3,...,值n),(值1,值2,值3,...,值n);
-- 示例：
	insert into tb_stu(stu_name,stu_age)values("张三1",20),("张三2",20),("张三3",20);
	

5.2、修改数据-update

-- 语法：
	-- update 表名 set 字段1 = 值,字段2=值,字段n = 值 [where 限制条件];
-- 示例：
	update goods set num=10000; #修改所有数据
	update goods set num = 100 ,name = "《水浒传》" where id = 3; #根据条件修改数据

5.3、删除数据-delete

-- 语法：
	-- delete from  表名 [where 限制条件];
-- 示例：
	delete from goods; #删除所有数据
	delete from goods where id = 1; # 根据条件删除数据

限制条件];
– 示例：
update goods set num=10000; #修改所有数据
update goods set num = 100 ,name = “《水浒传》” where id = 3; #根据条件修改数据

#### 5.3、删除数据-delete

~~~~mysql
-- 语法：
	-- delete from  表名 [where 限制条件];
-- 示例：
	delete from goods; #删除所有数据
	delete from goods where id = 1; # 根据条件删除数据