MySQL 创建，管理，增删改实用笔记

1.1 数据库的创建创建数据库是操作的第一步合理的命名和字符集设置能避免后续乱码等问题。create {databa|seschema} [if not exists] 数据库名 [[default]character set 字符集名 | [default] collate 校对规则名]注{|}中的内容表达的意思是二选一if not exists 在保证该数据库不存在的前提下创建但一般在navicat中可省略character set指定数据库字符集如utf8mb4兼容 emoji 和特殊字符collate指定字符校对规则如utf8mb4_general_ci不区分大小写。# 创建图书管理数据库指定utf8mb4字符集 create database if not exists LibraryDB default character set utf8mb4 collate utf8mb4_general_ci;1.2 管理数据库操作数据表前必须先指定要操作的数据库use 数据库名1.3 修改数据库alter {database|schema} 数据库名[default] character set 新字符集名;1.4删除数据库⚠️ 高危操作删除后数据库内所有数据将永久丢失建议先备份。drop database 数据库名1.5显示数据库show databases;2.1 创建数据库表数据表是存储数据的核心载体需先明确字段设计数据类型、约束再执行创建 / 修改 / 删除等操作。数据表的结构以图书为例字段名图书编号书名出版时间单价数量字段值的表示方法10个字符表示40字符表示时间表示2位小数的五位数字5位整数数据类型char10varchar(40)datafloat(5,2)int(5)1.数据类型新手必看先理清常用数据类型的区别避免选型错误类型分类常用类型适用场景注意事项数值型int(n)整数如编号、数量n 仅为显示宽度不限制存储范围如 int (5) 仍存 ±21 亿float(m,d)小数如价格、评分精度有限金融场景用 decimal (m,d)decimal(m,d)高精度小数如金额m 为总位数d 为小数位数如 decimal (5,2)000.00字符串型char(n)固定长度字符串如编号、手机号n 范围 0-255查询速度快浪费少量空间varchar(n)可变长度字符串如姓名、地址n 范围 0-65535节省空间查询略慢于 chartext长文本如简历、备注不建议建索引需索引用 varchar (255)日期型date仅日期如出版时间格式YYYY-MM-DDdatetime日期 时间如创建时间格式YYYY-MM-DD HH:MM:SS范围 1000-9999 年timestamp时间戳如更新时间自动更新范围 1970-2038 年占用空间更小2.创建数据表create table 表名 (列名 数据类型 [not null|null][default 列默认值]) 列名2 类型 约束主键外键不为空默认值2.2管理数据库表1.修改表结构alter [ignore] table 表名 add [column] 列名 [first |alter 列名]; /*添加列*/ | alter [column]列名 [set default 默认值|drop default]/*修改默认值*/ | alter [column] 旧列名 列定义 /*重命名列*/ [first|alter 列名] | modiey[column]列定义 [first|alter 列名]/*修改列类型*/ | drop[column]列名/*删除列*/ | rename[to]新表名/*重命名表*/2.复制表create table 新表名 like 被复制表名 create table 表名 as (select*form 被复制表名);3.删除表drop table 表名4.显示数据库表信息show tables;实操 1以读者表为例对上面的内容进行实现属性名称数据类型长度是否允许为空备注读者编号char6NOT NULL主键姓名char10NOT NULL类别号char2NOT NULL单位varchar20NULL有效性char10NULL1.创建数据库create database LibraryDB;2.使用数据库use LibraryDB;3.建表create table 读者表 ( 读者编号 char(6) not null, 姓名 char(10) not null, 类别号 char(2) not null, 单位 varchar(20) null, 有效性 char(10) null, primary key (读者编号) );4.显示信息show databases;5.显示所有的表use LibraryDB; show tables;6.删除数据库⚠️ 高危操作删除前建议备份drop database LibraryDB;2.3 数据完整性约束1.定义数据完整性指的是数据的一致性和正确性完整性约束是指数据库中的内容必须随时遵守的规则。各类完整性约束与约束方法之间的关系完整性约束约束方法描述约束对象域完整性default当使用insert 语句插入数据时若已定义默认值的列没有提供指定值则将该默认值插入记录中列域完整性check指定某一列可接受的值列实体完整性primary key每行记录的唯一标识符确保用户不能输入重复值并自动创建索引提高性能该列不允许使用空值行实体完整性unique在列集内强制执行值的唯一性防止出现重复值表中不允许存在两行的同一列包含相同的非空值行参照完整性foreing key定义一列或几列其值与本表或其他表的主键或unique列相匹配表之间约束类型关键字作用非空约束not null列值不能为 NULL默认约束default插入数据时未指定值则用默认值主键约束primary key唯一标识行记录非空 唯一唯一约束unique列值唯一可 NULL允许多个 NULL外键约束foreign key关联其他表的主键 / 唯一键保证数据一致性2.4 主键约束1.定义主键在mysql通过定义primary key 来创建逐渐约束该列中的值不能取空值定义主键的两种方法1.作为列的完整性约束时只需要在定义列的时候加入 primary key 关键字2.作为表的完整性约束时需要在语句最后加上一句 primary key(列名 ....)例子# 方式1列级约束适合单字段主键 create table reader_info ( 读者编号 char(6) not null primary key, 姓名 char(10) not null ); # 方式2表级约束适合多字段联合主键 create table reader_book ( 读者编号 char(6) not null, 图书编号 char(10) not null, 借阅时间 datetime not null, primary key (读者编号, 图书编号) # 联合主键读者图书唯一标识一条借阅记录 );2.定义主键的规则(1)每个表必须定义一个主键(2)唯一性规则(3)最小化规则2.5 替代键约束替代键 候选键 可以作为主键的唯一键除了主键之外能唯一标识一行数据的字段 / 字段组合必须满足唯一 可以为空最多一个 NULL一个表可以有多个替代键但只能有一个主键一句话理解主键是 “正式唯一标识”替代键是 “备用唯一标识”。二、替代键的作用保证业务字段不重复如手机号、身份证号、邮箱、学号不做主键但必须唯一的字段必须用替代键约束提高查询速度自动创建唯一索引可以被外键引用三替代键特点必背一个表可以有多个替代键值必须唯一不能重复允许 NULL但只能有一个 NULL自动创建唯一索引查询快可以作为外键关联的依据功能等价于候选键 / 唯一键主键 vs 替代键对比表特性主键 PRIMARY KEY替代键 UNIQUE数量一个表只能 1 个一个表可以有多个是否允许空不允许 NULL允许 NULL1 个作用唯一标识一行数据保证业务字段不重复索引自动创建主键索引自动创建唯一索引3.1数据增删改查一、数据插入 insert1.完整语法-- 指定字段插入 INSERT INTO 表名(字段1,字段2,字段3) VALUES(值1,值2,值3); ​ -- 全字段插入顺序必须和表结构一致 INSERT INTO 表名 VALUES(所有字段值);2.批量插入INSERT INTO 表名(字段1,字段2) VALUES (值1,值2), (值3,值4), (值5,值6);3.查询结果INSERT INTO 新表(字段) SELECT 字段 FROM 原表;二、数据修改 update1.基础语法UPDATE 表名 SET 字段1新值,字段2新值 WHERE 条件;2.常用示例-- 单字段修改 UPDATE user SET name张三 WHERE id1; ​ -- 多字段修改 UPDATE user SET age20,sex男 WHERE id2;3.高危提醒必记不加 WHERE 会修改全表所有数据-- 错误全表更新 UPDATE user SET age18;4.配合运算修改UPDATE user SET ageage1 WHERE id3;三、数据删除 DELETE1. 基础语法DELETE FROM 表名 WHERE 条件;2. 示例-- 删除单条 DELETE FROM user WHERE id5; ​ -- 按条件批量删 DELETE FROM user WHERE age18;3. 清空整张表DELETEDELETE FROM 表名;四删除注意事项不加WHERE清空全表生产严禁乱用有外键关联数据需先删子表再删主表重要数据删除前先 SELECT 查询确认