5数据库和表的基本操作

MySQL数据库和表的基本操作超详细图文版接触MySQL时不少人会被“数据库”“数据表”“SQL语句”等概念困扰。其实MySQL的核心操作并不复杂就像日常使用Excel一样——数据库相当于Excel文件数据表相当于Excel里的工作表掌握对它们的增删改查就迈出了MySQL学习的第一步本文结合《MySQL数据库入门第2版》的核心知识点用通俗的语言实操案例带大家吃透数据库和表的基本操作看完可直接上手练习。一、学习目标开始学习前明确核心目标避免盲目学习掌握数据库的增删改查操作能独立创建、查看、修改和删除数据库掌握数据表的增删改查操作能创建数据表、查看表结构、修改表结构、删除数据表熟悉MySQL常见数据类型明确不同数据的存储类型选择掌握表的4种核心约束非空、唯一、主键、默认值保证数据的完整性学会设置字段自动增长解决手动输入唯一ID的麻烦。二、核心概念类比快速理解理解“数据库”和“数据表”的关系是掌握后续操作的基础用以下类比可快速入门MySQL中的「数据库」 Excel文件比如“员工信息.xlsx”MySQL中的「数据表」 Excel文件里的「工作表Sheet」比如“销售部员工”“技术部员工”数据表中的「字段」 Excel工作表的「列」比如“姓名”“工号”“年龄”数据表中的「记录」 Excel工作表的「行」比如一行员工的完整信息。简单来说先创建数据库Excel文件再在数据库里创建数据表工作表最后在数据表中存储数据行和列逻辑和用Excel存数据完全一致。三、数据库的基本操作重中之重MySQL安装完成后首要操作就是数据库管理——数据表必须依赖数据库存在。所有操作均通过SQL语句实现记住固定语法即可直接复制修改使用。3.1 创建数据库最常用创建数据库即在MySQL中新建一个“数据存储容器”语法格式如下括号内为可选项可先掌握基础用法CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]db_name[create_option];关键说明DATABASE 和 SCHEMA 功能完全一致日常使用记 CREATE DATABASE 即可IF NOT EXISTS可选参数作用是“若该数据库不存在则创建”避免重复创建导致报错db_name需创建的数据库名称如“itcast”“bms”不可与现有数据库重名。实操案例可直接复制执行-- 创建名为itcast的数据库基础用法CREATEDATABASEitcast;-- 推荐用法加上IF NOT EXISTS避免报错CREATEDATABASEIFNOTEXISTSitcast;⚠️ 注意若直接执行 CREATE DATABASE itcast; 且该数据库已存在会报错ERROR 1007 (HY000): Can’t create database itcast; database exists3.2 查看数据库创建数据库后常需查看“当前MySQL中的所有数据库”或“某个数据库的创建配置”以下是两种常用语句1查看所有数据库SHOW{DATABASES|SCHEMAS};实操结果示例执行后会显示所有数据库mysqlSHOWDATABASES;--------------------|Database|--------------------|information_schema|-- MySQL系统自带数据库|itcast|-- 自定义创建的数据库|mysql|-- MySQL系统自带数据库|performance_schema|-- MySQL系统自带数据库|sys|-- MySQL系统自带数据库--------------------5rowsinset(0.03sec)2查看某个数据库的创建语句用于验证数据库配置如字符集语法SHOWCREATEDATABASEdb_name;实操案例查看itcast数据库的创建信息mysqlSHOWCREATEDATABASEitcast;---------------------------------------------------------------------|Database|CreateDatabase|---------------------------------------------------------------------|itcast|CREATEDATABASEitcast/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci *//*!80016 DEFAULT ENCRYPTIONN */|---------------------------------------------------------------------1rowinset(0.00sec)3.3 选择数据库创建数据库后不会自动进入该数据库如同新建Excel文件后需打开才能编辑工作表。因此操作数据表前必须先选择目标数据库USE数据库名;实操案例选择itcast数据库mysqlUSEitcast;Databasechanged-- 提示“数据库已切换”表示操作成功⚠️ 注意若不选择数据库直接创建数据表会报错No database selected查看当前选择的数据库可选SELECTDATABASE();3.4 修改数据库特征少用但需了解数据库创建后可修改其字符集、校对集等特征语法ALTER{DATABASE|SCHEMA}[db_name]alter_option;最常用场景修改数据库字符集如将itcast的字符集改为gbkALTERDATABASEitcastDEFAULTCHARACTERSETgbk;修改后可通过 SHOW CREATE DATABASE itcast; 验证修改结果。3.5 删除数据库谨慎操作删除数据库会彻底清除该数据库下的所有数据表及数据且无法恢复操作前需仔细确认。DROPDATABASE[IFEXISTS]db_name;实操案例删除itcast数据库-- 推荐用法加上IF EXISTS避免删除不存在的数据库报错DROPDATABASEIFEXISTSitcast;删除后执行 SHOW DATABASES; 可确认itcast数据库已移除。四、MySQL常见数据类型创建数据表时需为每个字段指定数据类型——如同Excel中设置单元格格式文本、数字、日期MySQL通过指定数据类型避免存储错误。重点掌握3大类数据类型可满足日常开发需求4.1 数值类型存储数字用于存储工资、年龄、工号等数字类数据核心类型整数类型INT最常用适用于工号、年龄等整数场景浮点类型FLOAT单精度、DOUBLE双精度适用于工资 5000.50 等带小数的场景定点数类型DECIMAL精度高适合金额类数据如 DECIMAL(7,2) 表示最多7位数字保留2位小数。4.2 日期和时间类型存储时间用于存储注册时间、上架时间等时间类数据核心类型DATE存储日期年月日如 2026-04-01TIME存储时间时分秒如 14:30:00DATETIME存储日期时间最常用如 2026-04-01 14:30:00YEAR存储年份如 2026。4.3 字符串类型存储文本用于存储姓名、地址、邮箱等文本类数据核心类型CHAR固定长度字符串如手机号 11位可用 CHAR(11)VARCHAR可变长度字符串最常用适用于姓名、邮箱等如 VARCHAR(20) 表示最多20个字符TEXT大文本数据适用于文章内容、备注等长度较长的文本。五、数据表的基本操作核心中的核心选择目标数据库后即可创建数据表——相当于在Excel文件中新建工作表重点掌握“创建、查看、修改、删除”四大核心操作。5.1 创建数据表语法格式基础版约束部分后续详细说明CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_name(字段名1数据类型1[列级约束1],字段名2数据类型2[列级约束2],...[,表级约束]);关键说明tbl_name数据表名称如“tb_dept”部门表、“tb_emp”员工表字段名数据类型必填项如“deptno INT”部门编号整数类型IF NOT EXISTS可选参数避免重复创建数据表报错。实操案例在itcast数据库中创建部门表tb_dept-- 先选择数据库USEitcast;-- 创建部门表CREATETABLEIFNOTEXISTStb_dept(deptnoINT,-- 部门编号dnameVARCHAR(14),-- 部门名称locVARCHAR(13)-- 部门地址);5.2 查看数据表创建数据表后需确认表是否创建成功、表结构是否符合预期以下3种常用语句重点掌握前2种1查看当前数据库中的所有数据表SHOWTABLES;实操结果示例mysqlSHOWTABLES;---------------|Tables_in_ems|---------------|tb_dept|-- 自定义创建的部门表---------------1rowinset(0.01sec)2查看数据表的创建语句SHOWCREATETABLEtbl_name;实操案例查看tb_dept的创建信息mysqlSHOWCREATETABLEtb_dept;-----------------------------------------------------------------|Table|CreateTable|-----------------------------------------------------------------|tb_dept|CREATETABLEtb_dept(deptnointDEFAULTNULL,dnamevarchar(14)DEFAULTNULL,locvarchar(13)DEFAULTNULL)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_0900_ai_ci|-----------------------------------------------------------------1rowinset(0.00sec)3查看数据表结构最常用用于查看数据表的字段、数据类型、约束等信息语法DESCRIBE数据表名;-- 简写DESC 数据表名;实操案例查看tb_dept的表结构mysqlDESCtb_dept;------------------------------------------------|Field|Type|Null|Key|Default|Extra|------------------------------------------------|deptno|int|YES||NULL||-- 字段名部门编号类型INT允许为空无默认值|dname|varchar(14)|YES||NULL||-- 字段名部门名称类型VARCHAR(14)|loc|varchar(13)|YES||NULL||-- 字段名部门地址类型VARCHAR(13)------------------------------------------------3rowsinset(0.00sec)字段说明Field字段名称列名Type数据类型Null是否允许存储NULL值YES允许NO不允许Key是否建立索引后续章节讲解Default字段的默认值Extra附加信息如自动增长。5.3 修改数据表常用操作数据表创建后可根据需求修改表名、字段名、数据类型或添加/删除字段核心语法ALTERTABLEtbl_name[alter_option,...];以下是5种常用修改场景可直接套用案例1修改数据表名-- 将tb_dept改为deptALTERTABLEtb_deptRENAMETOdept;2修改字段名两种修改方式优先使用第一种仅修改字段名-- 方式1仅修改字段名推荐ALTERTABLEdeptRENAMECOLUMNlocTOlocal_name;-- 方式2修改字段名数据类型灵活适配需求ALTERTABLEdept CHANGE local_name local_nameCHAR(20);3修改字段数据类型-- 将dname的类型从VARCHAR(14)改为CHAR(16)ALTERTABLEdeptMODIFYdnameCHAR(16);4添加字段-- 在dept表的第一列添加id字段INT类型ALTERTABLEdeptADDidINTFIRST;-- 在dname字段后面添加age字段INT类型ALTERTABLEdeptADDageINTAFTERdname;5删除字段-- 删除dept表中的id字段ALTERTABLEdeptDROPid;5.4 删除数据表谨慎操作删除数据表会彻底清除表结构及表中所有数据且无法恢复语法DROPTABLE[IFEXISTS]tbl_name;实操案例删除dept表DROPTABLEIFEXISTSdept;六、表的约束保证数据正确性约束是MySQL中数据的“规则”如“姓名不能为空”“工号不能重复”目的是避免插入错误数据如姓名留空、重复插入同一工号。重点掌握4种核心约束外键约束将在后续章节讲解。6.1 非空约束NOT NULL作用指定字段不能存储NULL值必须填写内容适用于姓名、部门地址等必填字段。用法两种场景-- 1. 创建表时设置非空约束CREATETABLEtb_dept01(deptnoINT,dnameVARCHAR(14),localVARCHAR(13)NOTNULL-- local字段不能为NULL);-- 2. 修改表时添加非空约束ALTERTABLEtb_dept01MODIFYdnameVARCHAR(14)NOTNULL;-- 删除非空约束重新定义字段去掉NOT NULLALTERTABLEtb_dept01 CHANGE dname dnameVARCHAR(14);6.2 唯一约束UNIQUE作用指定字段的值不能重复适用于工号、邮箱等需唯一标识的字段。用法两种场景-- 1. 创建表时设置唯一约束列级表级CREATETABLEtb_emp01(empnoINTUNIQUE,-- 列级约束工号唯一enameVARCHAR(16),deptnameVARCHAR(16),UNIQUE(deptname,ename)-- 表级约束同一部门不能有相同姓名);-- 2. 修改表时添加唯一约束ALTERTABLEtb_emp01ADDUNIQUE(email);-- 删除唯一约束通过索引名删除索引名默认和字段名一致ALTERTABLEtb_emp01DROPINDEXempno;6.3 主键约束PRIMARY KEY作用非空约束唯一约束的组合字段值既不能为NULL也不能重复用于唯一标识一条记录如用户ID、图书ID。注意一个数据表只能有一个主键。用法两种场景-- 1. 创建表时设置主键约束CREATETABLEtb_dept02(idINTPRIMARYKEY,-- 主键约束id既不能空也不能重复dnameVARCHAR(14)UNIQUE,localVARCHAR(13)NOTNULL);-- 2. 修改表时添加主键约束ALTERTABLEtb_dept01ADDPRIMARYKEY(deptno);-- 删除主键约束无需指定字段名因一个表仅一个主键ALTERTABLEtb_dept01DROPPRIMARYKEY;6.4 默认值约束DEFAULT作用指定字段的默认值插入数据时若不填写该字段MySQL会自动填入默认值如用户状态默认启用。用法两种场景-- 1. 创建表时设置默认值约束CREATETABLEtb_emp02(idINTPRIMARYKEY,enameVARCHAR(16)UNIQUE,statusINTDEFAULT1-- 默认值为1如1表示启用);-- 2. 修改表时添加默认值约束ALTERTABLEtb_emp02MODIFYsalDECIMAL(7,2)DEFAULT0.00;-- 删除默认值约束重新定义字段去掉DEFAULTALTERTABLEtb_emp02 CHANGE sal salDECIMAL(7,2);七、字段自动增长AUTO_INCREMENT实用技巧手动输入唯一ID如用户ID、图书ID时易出现重复或遗漏问题。解决方案给主键字段设置自动增长MySQL会为每一条新记录自动生成唯一ID从1开始依次递增。用法及注意事项-- 语法字段名 数据类型 AUTO_INCREMENT;CREATETABLEtb_emp03(empnoINTPRIMARYKEYAUTO_INCREMENT,-- 主键自动增长deptnameVARCHAR(14)NOTNULL,jobVARCHAR(13));4个关键注意点一个数据表只能有一个自动增长字段自动增长字段必须是“键”主键、唯一约束均可通常搭配主键使用插入数据时自动增长字段可填NULLMySQL会自动生成递增的值用DELETE删除记录后自动增长的值不会“回退”如删除ID3的记录下次插入仍为ID4。八、上机实践巩固知识点理论学习后需通过实操巩固知识点。以下以“图书管理系统”为例完成数据库和数据表的创建掌握上述所有核心操作。实践需求创建名称为bms的数据库字符集设为utf8mb4在bms数据库中创建3张表user用户表、book图书表、record借阅记录表查看book表的结构信息。动手实操可直接复制执行-- 1. 创建bms数据库指定字符集CREATEDATABASEbmsCHARACTERSETutf8mb4;-- 2. 选择bms数据库USEbms;-- 3. 创建user表用户表CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENT,-- 主键自动增长用户编号nameVARCHAR(20)NOTNULLUNIQUE,-- 非空唯一用户名stateCHAR(1)NOTNULLDEFAULT0-- 非空默认值0启用1禁用);-- 4. 创建book表图书表CREATETABLEbook(idINTPRIMARYKEYAUTO_INCREMENT,-- 主键自动增长图书编号nameVARCHAR(20)NOTNULLUNIQUE,-- 非空唯一图书名称priceDECIMAL(6,2)NOTNULL,-- 非空图书价格upload_timeDATETIMENOTNULL,-- 非空上架时间borrower_idINT,-- 借阅人编号可选borrow_timeDATETIME,-- 借阅时间可选stateCHAR(1)NOTNULLDEFAULT0-- 非空默认值0可借阅);-- 5. 创建record表借阅记录表CREATETABLErecord(idINTPRIMARYKEYAUTO_INCREMENT,-- 主键自动增长记录编号book_idINTNOTNULL,-- 非空图书编号borrower_idINTNOTNULL,-- 非空借阅人编号borrow_timeDATETIMENOTNULL,-- 非空借阅时间remand_timeDATETIMENOTNULL-- 非空归还时间);-- 6. 查看book表的结构DESCbook;执行完以上语句即可完成一个简单图书管理系统的数据库设计建议实际操作验证效果。九、总结本章核心是数据库和数据表的基本操作掌握以下3个核心逻辑即可快速上手先有数据库再有数据表先建Excel文件再建工作表操作数据表前必须先选择数据库打开Excel文件才能编辑工作表约束和自动增长用于规范数据格式、提升操作效率避免错误数据减少手动输入。学习时可先记住语法格式复制案例执行验证成功后再修改参数练习熟练后即可灵活运用。下一章将讲解数据表中数据的增删改查操作进一步提升MySQL使用能力。