数据库基本操作与操作须知整理

SQL中常用的数据类型整型:int, 表示所有的整数, 例如: 10, 20, 30...TINYINT、SMALLINT、MEDIUMINT、INT或INTEGER、BIGINT范围从小到大浮点型:float, 存储小数, 例如: 10.0, 20.0, 30.0...double更精确占位也更大DECIMAL(M,D)是专门用来存储精确小数的数据类型。它在金融、财务、电商等对数据精度要求极高的场景中是绝对的主力。M表示这个数总共能有多少位数字包含小数点前后的所有数字D表示小数点后面能保留多少位数字。字符串型:varchar, 存储字符串, 例如: 张三, 王五, 赵六...char支持的更短如果你定义了CHAR(10)哪怕你只存了 张三数据库也会在后面补齐 8 个空格强制占满 10 个字节。因为长度固定数据库在内存中分配和处理时非常高效查询和计算速度比varchar更快。日期型:datetime, 存储日期和时间, 例如: 2026-01-01 00:00:00timestamp与时区强相关。它在存储时会自动将当前时间转换为 UTC世界协调时间在检索时又会自动将 UTC 时间转换回当前会话的时区TEXT类型主要用于存储不需要进行复杂数学计算但篇幅较长的文字内容。TINYTEXT适合极短的文本如简短消息、标签。如果长度固定且不超过 255 字节通常建议用VARCHAR(255)替代。TEXT最常见的长文本类型适合存储博客正文、用户评论、产品详细描述等通常在 64KB 以内。MEDIUMTEXT适合存储较大的文本文件如长篇论文、报告、大型 JSON 配置文件等。LONGTEXT适合存储超长文本如完整的电子书、小说、系统级日志归档等。BLOBBinary Large Object将数据作为二进制字符串处理比较和排序基于字节的数值不受字符集影响。TINYBLOB适合存储非常小的二进制文件如证件照、小图标。BLOB适合存储常规大小的文件如普通的 PDF 文档、中等分辨率的图片。MEDIUMBLOB适合存储较大的媒体文件如高清图片、短视频片段、序列化的多维数组等。LONGBLOB适合存储巨大的二进制文件如完整的高清电影、大型压缩包等。对数据库的操作创建数据库CREATE DATABASE my_database;安全创建以及字符适配create database if not exists my_database DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;查看所有数据库SHOW DATABASES;查询当前数据库SELECT DATABASE();查看创建该数据库时的详细语句包括字符集配置SHOW CREATE DATABASE my_database;使用数据库USE my_database;删除数据库DROP DATABASE my_database;安全删除DROP DATABASE IF EXISTS my_database;对数据表的操作结构操作查看当前库中所有的表SHOW TABLES;查看表结构字段类型、约束等DESC table_name; -- 或 DESCRIBE table_name;查看建表时的完整 SQL 语句SHOW CREATE TABLE table_name;创建表时需要定义字段名、数据类型以及约束如主键、非空等CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, email VARCHAR(100) COMMENT 邮箱, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 );克隆表--未建表 CREATE TABLE student_bak AS SELECT * FROM student; --已建表 CREATE TABLE student_bak LIKE student; --迁移格式 INSERT INTO student_bak SELECT * FROM student; --迁移内容添加字段ALTER TABLE users ADD age INT COMMENT 年龄;删除字段ALTER TABLE users DROP age;修改字段类型ALTER TABLE users MODIFY username VARCHAR(100); ALTER TABLE student MODIFY COLUMN sphone VARCHAR(20); --不保留原有约束重命名字段ALTER TABLE table_name CHANGE old_name new_name data_type;重命名表ALTER TABLE old_name RENAME TO new_name; RENAME TABLE old_name TO new_name;安全删除表DROP TABLE IF EXISTS users;清空表数据保留表结构重置自增IDTRUNCATE TABLE users;数据操作CRUD插入单条数据INSERT INTO users (username, email) VALUES (Alice, aliceexample.com);批量插入多条数据INSERT INTO users (username, email) VALUES (Bob, bobexample.com), (Charlie, charlieexample.com) (...);查询所有列不建议在生产环境大表中使用SELECT * FROM users;查询指定列推荐节省带宽SELECT id, username FROM users;条件查询SELECT * FROM users WHERE username Alice;排序与分页SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 0;更新数据UPDATE users SET email new_aliceexample.com WHERE username Alice;删除数据DELETE FROM users WHERE username Alice;约束合集CREATE TABLE orders ( -- 1. 主键约束 自增约束 -- 作用订单号唯一且自动递增作为表的唯一标识 id INT PRIMARY KEY AUTO_INCREMENT COMMENT 订单主键ID, -- 2. 非空约束 -- 作用下单用户不能为空必须知道是谁买的 user_id INT NOT NULL COMMENT 用户ID, -- 3. 唯一约束 -- 作用订单流水号必须全局唯一防止重复下单 order_no VARCHAR(50) UNIQUE COMMENT 订单流水号, -- 4. 检查约束 (MySQL 8.0.16 支持) -- 作用限制订单金额必须大于 0防止录入负数或0元异常订单 amount DECIMAL(10, 2) CHECK (amount 0) COMMENT 订单金额, -- 5. 默认约束 -- 作用如果没有手动传入状态默认订单状态为 待支付 status VARCHAR(20) DEFAULT 待支付 COMMENT 订单状态, -- 6. 外键约束 -- 作用限制 user_id 必须是 users 表中真实存在的 id防止产生幽灵订单 -- 建表前在表中定义 CONSTRAINT fk_user FOREIGN KEY(class_id) REFERENCES classes(class_id) );建表后通过 ALTER 添加外键ALTER TABLE users ADD CONSTRAINT fk_user_class FOREIGN KEY (class_id) REFERENCES classes(class_id);联合主键示例CREATE TABLE course_selection ( student_id INT NOT NULL, course_id INT NOT NULL, score INT, -- 【表级约束演示 1】联合主键 -- 作用一个学生可以选多门课一门课可以被多个学生选但 (学生课程) 的组合必须唯一 PRIMARY KEY (student_id, course_id), -- 【表级约束演示 2】自定义名称的外键约束 -- 作用给约束起个名字 (fk_student)方便以后如果报错能一眼看出是哪个外键出了问题 CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(id) );数据查询条件查询名称符号说明比较查询,,,,,,!大于、小于、大于(小于)等于、不等于范围查询BETWEEN ... AND ...显示在某一区间的值(含头含尾)范围查询IN(set)显示在in列表中的值例in(100,200)模糊查询LIKE 张%,LIKE %涛%模糊查询。Like语句中%代表零个或多个任意字符_代表一个字符例如first_name like _a%非空查询IS NULL,IS NOT NULL判断是否为空逻辑查询and多个条件同时成立逻辑查询or多个条件任一成立逻辑查询not不成立例where not(salary100)聚合函数聚合函数作用count()统计指定列不为NULL的记录行数sum()计算指定列的数值和如果指定列类型不是数值类型那么计算结果为0max()计算指定列的最大值如果指定列是字符串类型那么使用字符串排序运算min()计算指定列的最小值如果指定列是字符串类型那么使用字符串排序运算avg()计算指定列的平均值如果指定列类型不是数值类型那么计算结果为0聚合查询示例# 1、查询商品的总条数 SELECT COUNT(*) FROM product; # 2、查询价格大于200商品的总条数 SELECT COUNT(*) FROM product WHERE price 200; # 3、查询分类为c001的所有商品的总和 SELECT SUM(price) FROM product WHERE category_id c001‘; # 4、查询分类为c002所有商品的平均价格 SELECT AVG(price) FROM product WHERE category_id c002‘; # 5、查询商品的最大价格和最小价格 SELECT MAX(price),MIN(price) FROM product;分组查询分组查询语法SELECT column_name, aggregate_function(column_name) FROM table_name WHERE condition GROUP BY column_name HAVING condition;示例SELECT class_id AS classID, COUNT(*) AS num_student FROM users GROUP BY class_id HAVING COUNT(*) 1;分页查询查询语法SELECT * FROM name_table LIMIT 偏移量(Offset), 每页条数(Page_Size)例如SELECT * FROM users LIMIT 0, 3; -- 查询users表的1-3条数据实际用法需结合后端子查询与自查询子查询为分步查询逻辑递进如先找1班ID再找1班学生-- 外层查询根据第一步找到的 class_id 去查学生 SELECT username, email FROM users WHERE class_id IN ( -- 第一步子查询先查出属于 1班 的 class_id这里假设 1 就是 1班 SELECT DISTINCT class_id FROM users WHERE class_id 1 );自查询用于表内层级关系如员工与领导父子分类SELECT u1.username AS 员工姓名, u2.username AS 领导姓名 FROM users u1 LEFT JOIN users u2 ON u1.leader_id u2.id;窗口函数使用SELECT * FROM ( SELECT empid, ename, deptid, salary, -- rank()排名相同的名次一样同一排名有几个后面排名就会跳过几次 -- row_number()不管排名是否有相同的都按照顺序123.....n -- dense_rank()排名相同的名次一样且后面名次不跳跃 rank() over (PARTITION BY deptid ORDER BY salary DESC) AS t_column_name -- PARTITION BY 是按照...进行分组 -- DESC 这边是降序对应 ASC升序查看表结构为 DESCRIBE 缩写 -- 总体是在按部门分组的基础上按工资降序排序 FROM tablename ) t_tablename WHERE t_tablename.t_column_name 3; -- 新列中排名的前两名 -- 因为 t_column_name 还没有被计算出来所以你不能直接写 WHERE rank() 3。 -- 必须先用子查询把排名算出来然后再在外层进行过滤。连表查询classes分类表为一方也就是主表必须提供主键class_idusers详情表为多方也就是从表必须提供外键class_id在后端代码中u.class_id AS user_class_id, c.class_name, c.class_id AS class_table_id左连接LEFT JOIN语法所有的用户都要输出排序GROUP BY column_nameSELECT u.*, c.class_name FROM users u LEFT JOIN classes c ON u.class_id c.class_id;右连接RIGHT JOIN语法所有的班级都要输出SELECT u.*, c.class_name FROM users u RIGHT JOIN classes c ON u.class_id c.class_id;内连接INNER JOIN语法常用只输出两表的交集SELECT u.*, c.class_name FROM users u INNER JOIN classes c ON u.class_id c.class_id;在真实开发场景中事务保护在执行复杂的 UPDATE/DELETE 时建议先开启事务START TRANSACTION; -- 执行操作... -- 确认无误后 COMMIT; -- 出错则 ROLLBACK;减少SELECT *为了降低改代码时的痛苦程度和风险。我们可以从以下三个实际开发中的痛点来理解1. 报错的时机尽早暴露 vs 线上爆炸显式指定列名推荐当你把数据库里的username删了但忘了改代码时只要应用一启动或者一执行到这条 SQL数据库层面就会直接报错Unknown column。你在测试环境立刻就能发现并修复它。使用SELECT *数据库层面一切正常查询成功返回了数据。直到你的代码执行到user.getUsername()时应用层才抛出空指针异常NullPointerException。这种 Bug 往往更隐蔽排查起来也更麻烦。2. 改代码的工作量全局搜索 vs 盲人摸象显式指定列名当你要删除username字段时只需在代码里全局搜索SELECT id, username, email就能精准定位到所有需要修改的 SQL 语句改起来非常清晰。使用SELECT *代码里可能到处都是SELECT *。当你删了字段你根本不知道哪些SELECT *对应的业务逻辑依赖了这个字段。你只能像盲人摸象一样把整个系统的回归测试跑一遍祈祷不要有隐藏的业务逻辑因此崩溃。3. 性能与索引的隐形代价这一点和改不改代码无关纯粹是SELECT *自身的性能缺陷无法利用覆盖索引假设你建了一个联合索引(username, email)。如果你写SELECT username, email FROM users WHERE username Alice数据库只需要查索引就能直接返回结果根本不需要去查主表这叫覆盖索引极快。但如果你写SELECT *因为索引里没有id、created_at等所有字段数据库就必须拿着索引查出来的主键再回表去查完整数据性能大打折扣。网络带宽浪费如果表里有 50 个字段你只需要 2 个SELECT *会把那 48 个没用的数据也通过网络传给你的应用服务器白白消耗带宽和内存。