MySQL表和列、增删改查语句及数据类型约束示例详解_Mysql

一、数据库、表和列的基本概念

1.1 数据库的概念与用途

数据库是存储在计算机上的有组织的数据集合，核心目的是实现数据的高效存储、管理、访问与更新，其主要用途体现在：

数据存储和管理：可存储结构化（表格）与非结构化（文本、图片、视频）等多种类型的海量数据，实现数据的有序组织；
数据访问：支持查询、过滤、排序等高效操作，让用户快速检索所需数据；
数据共享：允许多个用户同时访问，实现团队间的数据协作与共享；
数据分析和挖掘：提供统计分析、数据挖掘等能力，助力用户从数据中提取价值，为决策提供支撑。

1.2 mysql的层级关系与使用步骤

mysql中存在清晰的层级关系：mysql数据库服务器 → 数据库（db） → 表 → 列，且层级间均为一对多关系，一个服务器可创建多个数据库，一个数据库可包含多张表，一张表由多个列（字段）组成。

使用mysql的标准步骤为：登录mysql → 创建数据库 → 切换到指定数据库 → 创建表 → 使用表，所有表的操作都需先确定所属的数据库，这是mysql操作的基础原则。

二、数据库与表的基础操作

2.1 数据库的创建、查看与删除

（1）创建数据库

-- 基础创建
create database [if not exists] 数据库名;
-- 指定字符集（推荐）
create database [if not exists] 数据库名 default charset utf8;

（2）查看数据库

-- 查看所有数据库
show databases;
-- 查看指定数据库的创建语句（含字符集等配置）
show create database 数据库名;

（3）删除数据库

-- if exists避免删除不存在的数据库时报错
drop database [if exists] 数据库名;

2.2 数据库的切换与当前库查看

-- 切换到指定数据库（后续表操作均基于此库）
use 数据库名;
-- 查看当前正在使用的数据库
select database();

2.3 表的创建、修改与删除

（1）表的创建

创建表使用create table语句，需指定表名、列名、数据类型及约束，if not exists可选，用于避免重复创建。核心语法及示例：

create table [if not exists] 表名(
  列名1 数据类型 [约束],
  列名2 数据类型 [约束],
  ...
);

-- 学生表创建示例
create table [if not exists]  students(
  id varchar(255) primary key,
  name varchar(255) not null,
  age int,
  gender varchar(255),
  clazz varchar(255)
);

补充：可通过load data加载本地数据到表中，示例：

load data local infile 'students.txt' into table students fields terminated by ',' ;

（2）表的修改

表的修改通过alter table语句实现，支持追加列、修改列属性、重命名列/表、修改字符集、删除列等操作，核心用法：

-- 增加列
alter table students add length float;
-- 修改列类型/属性
alter table students modify length int not null;
-- 修改表名
rename table students to student;
-- 修改列名
alter table  student change id sid varchar(255);
-- 修改表的字符集
alter table student character set utf8;
-- 删除列
alter table students drop length;

（3）表的删除

-- if not exists避免删除不存在的表时报错
drop table [if not exists ] students;

三、数据类型和约束

3.1 常见数据类型

mysql的数据类型丰富，可满足不同业务的数据存储需求，核心分为数值类型、字符串类型、日期时间类型、二进制类型、枚举类型、集合类型六大类，重点常用类型如下：

（1）数值类型

适用于存储数字类数据，包含整数和浮点数，不同类型的存储大小、取值范围不同，需根据业务场景选择：

类型	大小	范围（有符号）	范围（无符号）	用途
tinyint	1 bytes	(-128，127)	(0，255)	小整数值
smallint	2 bytes	(-32768，32767)	(0，65535)	大整数值
mediumint	3 bytes	(-8388608，8388607)	(0，16777215)	大整数值
int/integer	4 bytes	(-2147483648，2147483647)	(0，4294967295)	常用整数值（id、年龄等）
bigint	8 bytes	(-9223372036854775808，9223372036854775807)	(0，18446744073709551615)	极大整数值
float	4 bytes	(-3.402823466e+38，-1.175494351e-38)、0、(1.175494351e-38，3.402823466351e+38)	0、(1.175494351e-38，3.402823466e+38)	单精度浮点数值
double	8 bytes	(-1.7976931348623157e+308，-2.2250738585072014e-308)、0、(2.2250738585072014e-308，1.7976931348623157e+308)	0、(2.2250738585072014e-308，1.7976931348623157e+308)	双精度浮点数值
decimal(m,d)	m+2/d+2	依赖m和d的值	依赖m和d的值	高精度小数值（价格、金额等）

（2）字符串类型

适用于存储文本类数据，text系列适用于长文本存储：

类型	大小	用途
char	0-255 bytes	定长字符串（性别、状态等短固定长度数据）
varchar	0-65535 bytes	变长字符串（姓名、邮箱等长度不固定数据）
tinytext	0-255 bytes	短文本字符串
text	0-65535 bytes	长文本数据（备注、详情等）
mediumtext	0-16777215 bytes	中等长度文本数据
longtext	0-4294967295 bytes	极大文本数据
补充：utf-8编码下一个汉字占3个字节，gbk编码下一个汉字占2个字节，定义字符串长度时需考虑编码规则。

（3）日期和时间类型

适用于存储时间相关数据，不同类型对应不同的时间存储粒度：

类型	大小	范围	格式	用途
date	3 bytes	1000-01-01/9999-12-31	yyyy-mm-dd	仅存储日期（生日、创建日期等）
time	3 bytes	-838:59:59/838:59:59	hh:mm:ss	时间值或持续时间
year	1 byte	1901/2155	yyyy	年份值
datetime	8 bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	yyyy-mm-dd hh:mm:ss	混合日期和时间值
timestamp	4 bytes	1970-01-01 00:00:01 utc/2038-01-19 03:14:07 utc	yyyy-mm-dd hh:mm:ss	时间戳（自动更新时间优选）

3.2 常用约束规则

约束是对表中列数据的强制校验规则，用于保证数据的规范性、完整性和唯一性，核心约束类型及作用如下：

主键约束（primary key）：唯一标识表中的每一行数据，主键列默认非空且唯一，可使用单列或多列组合作为一个主键；
唯一约束（unique）：保证列中的所有值唯一，允许包含空值，一个表可定义多个唯一约束；
外键约束（foreign key）：相同的属性列，定义表与表之间的关联关系，保证引用完整性（如订单表的产品id关联产品表的产品id）；
非空约束（not null）：确保列中不包含空值，插入/修改数据时必须为该列赋值；
默认值约束（default）：插入新行时，若未指定该列值，则自动使用默认值；
检查约束（check）：确保列中的值满足特定条件（如年龄≥18）。

3.3 特殊属性：auto_increment

这是mysql特有的属性，仅适用于int、bigint等整型列，通常与主键约束配合使用，实现列值的自动递增，每次插入新行时，该列会自动生成唯一值，无需手动赋值，是创建自增id的核心属性，示例：

create table users (
  id int primary key auto_increment
);

3.4 数据类型+约束综合建表示例

create table users (
  id int primary key,
  name varchar(50) not null,
  email varchar(100) unique,
  password varchar(100) not null,
  created_at datetime default current_timestamp,
  updated_at timestamp default current_timestamp on update current_timestamp,
  age int check(age >= 18),
  address text,
  role enum('admin', 'user') default 'user'
);

枚举类型（enum）

语法格式：字段名 enum('值1', '值2', ..., '值n')
作用：限制该字段只能存储枚举列表中指定的值（多选一），无法插入列表外的任何内容，强制数据规范性。

四、sql语言基础

4.1 sql语言简介

sql的核心功能分为四大类：查询、操纵、定义、控制关系型数据库，对应的语言分类为dql、dml、ddl、dcl，是mysql操作的核心语法体系。

4.2 sql语言四大分类

sql按功能可分为四大类，各类别的核心作用、关键字及典型操作不同，是掌握sql的基础框架，具体如下：

分类	核心作用	核心关键字	典型操作
ddl（数据定义语言）	定义/修改/删除数据库对象（表、索引、视图等）的结构	create、alter、drop、rename、truncate	建库、建表、修改表结构、删表
dml（数据操作语言）	增/删/改数据库表中的数据内容（不改变表结构）	insert、update、delete、merge	新增记录、修改数据、删除记录
dcl（数据控制语言）	管理数据库权限、控制事务	grant、revoke、commit、rollback	授权、撤销权限、提交/回滚事务
dql（数据查询语言）	从数据库中查询/检索数据（仅读取，不修改数据）	select、from、where、group by、order by	单表查询、多表查询、数据统计

truncate 操作不可回滚，drop可回滚

4.3 核心sql语句详解

4.3.1 dql：数据查询语言（select）

select是最常用的sql语句，用于从表中检索数据，核心语法为：

select column1, column2, ... from table_name where condition;

column1, column2：指定要查询的列，*代表查询所有列；
from table_name：指定要查询的表；
where condition：指定查询的筛选条件。

基础示例：

-- 查询学生表中所有男生的记录
select * from student where  sex='男';

高级查询扩展：

模糊查询（like）：%代表任意字符，_代表单个字符

-- 查询班级以“文科”开头的学生
select * from students where clazz like '文科%';

2.范围查询（in/between and）

-- 查询文科一班、二班、三班的学生
select * from students where clazz in ('文科一班','文科二班','文科三班');
-- 查询年龄在22到24之间的学生
select * from students where age between 22 and 24;

3.排序（order by）：asc升序（默认），desc降序

-- 按年龄降序查询学生
select * from students order by age desc;

4. 分组（group by）+ 聚合函数：聚合函数包括count（计数）、sum（求和）、avg（平均值）等，用于分组统计

-- 按性别分组，统计每组学生数量
select gender, count(*) from students group by gender;

5.分组筛选（having）：对group by的统计结果进行筛选（区别于where：where筛选原始数据，having筛选分组结果）

-- 按班级分组，统计学生数大于20的班级
select clazz, count(*) from students group by clazz having count(*) > 20;

6.分页查询（limit/offset）：限制查询结果的行数，实现分页

-- 查询前10条记录
select * from students limit 10;
-- 从第5条开始，查询10条记录（offset为偏移量，从0开始）
select * from students limit 10 offset 4;

4.3.2 dml：数据操作语言

（1）insert（新增数据）

用于向表中插入新的记录，核心语法：

insert into table_name (column1, column2, column3, ...)
values (value1, value2, value3, ...);

注意：列名与值的数量、类型必须一一对应，非空约束的列必须赋值。

（2）update（修改数据）

用于修改表中已有的记录，核心语法：

update table_name
set column1 = value1, column2 = value2, ...
where some_column = some_value;

关键注意：where some_column = some_value：指定修改数据的筛选条件，仅修改满足条件的记录。条件支持多种写法（如>、<、!=、and、or等）

（3）delete（删除数据）

用于删除表中的记录，核心语法：

delete from table_name
where some_column = some_value;

关键注意：① 必须加where子句，否则删除表中所有数据；② delete是dml操作，删除的数据可通过事务回滚恢复，与truncate（ddl操作，不可回滚）不同。

4.3.3 dcl：数据控制语言

权限管理

-- 授予用户test对users表的查询和插入权限
grant select, insert on users to 'test'@'localhost';
-- 撤销test用户的插入权限
revoke insert on users from 'test'@'localhost';

2.事务控制

-- 开启事务（部分数据库需显式声明）
start transaction;
-- 设置保存点
savepoint sp1;
-- 回滚到保存点（仅撤销删除操作，修改操作保留）
rollback to sp1;
-- 提交最终修改
commit;

总结

到此这篇关于mysql表和列、增删改查语句及数据类型约束的文章就介绍到这了,更多相关mysql表和列、增删改查及数据类型约束内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

MySQL表和列、增删改查语句及数据类型约束示例详解

2026年02月11日 • Mysql •我要评论