{"msg":"操作成功","code":200,"data":{"createBy":"admin","createTime":"2020-03-10 20:51:26","updateBy":"admin","updateTime":"2025-06-04 12:38:37","remark":null,"id":19,"articleTitle":"MySQL（三）增删改查CRUD","articleUrl":"mysql_crud","articleThumbnail":"https://www.asumimoe.com/imgfiles/20220908/9e192744fab244d39d3b15fca035c9f7.jpg","articleFlag":"0","draftStatus":"1","reprintStatement":"0","articleSummary":"MySQL常用增删改查语句。包括(C)reate - 创建数据、(R)ead - 读取数据、(U)pdate - 更新数据、(D)elete - 删除数据操作","articleContent":"## 插入INSERT\n\n```sql\nCREATE TABLE students (\n    id INT AUTO_INCREMENT PRIMARY KEY,     -- 学生ID，主键自增长\n    name VARCHAR(50) NOT NULL,            -- 学生姓名，不能为空\n    age INT NOT NULL,                     -- 年龄，不能为空\n    gender ENUM(1,2)\t\t\t\t\t-- 性别，1表示男性，2表示女性\n    email VARCHAR(100) UNIQUE,            -- 邮箱，唯一约束\n    major VARCHAR(50) DEFAULT 'Undeclared', -- 专业，默认值'Undeclared'\n    enrollment_date DATE NOT NULL         -- 入学日期\n);\n```\n\n**示例：**\n\n```sql\n-- 插入单条记录（指定所有列）\nINSERT INTO students (name, age, email, major, enrollment_date)\nVALUES ('张明', 20,1, 'zhangming@example.com', '计算机科学', '2023-09-01');\n\n-- 插入单条记录（使用默认值）\nINSERT INTO students (name, age, enrollment_date)\nVALUES ('李华', 22,1,'2023-09-01');  -- major将使用默认值'Undeclared'\n\n-- 插入多条记录\nINSERT INTO students (name, age, email, major, enrollment_date) VALUES\n('王芳', 21,1,2'wangfang@example.com', '数学', '2023-09-01'),\n('赵强', 19,1,'zhaoqiang@example.com', '物理学', '2023-09-02'),\n('刘婷', 20,1, NULL, '化学', '2023-09-02');  -- 允许email为空\n```\n\n\n\n## 更新UPDATE\n\n```sql\n-- 更新单个记录\nUPDATE students \nSET age = 23, email = 'lihua_new@example.com' \nWHERE name = '李华';\n\n-- 更新多个记录\nUPDATE students \nSET major = '数据科学' \nWHERE major = '计算机科学';\n\n-- 基于现有值更新\nUPDATE students \nSET age = age + 1  -- 所有学生年龄增加1岁\nWHERE enrollment_date < '2023-09-02';\n\n-- 使用表达式更新\nUPDATE students \nSET email = CONCAT(LOWER(REPLACE(name, ' ', '')), '@uni.edu' \nWHERE email IS NULL;  -- 为没有邮箱的学生生成邮箱\n```\n\n## 删除DELETE\n\n```sql\n-- 删除特定记录\nDELETE FROM students \nWHERE name = '赵强' AND major = '物理学';\n\n-- 删除所有空邮箱记录\nDELETE FROM students \nWHERE email IS NULL;\n\n-- 清空整个表（危险操作！）\nTRUNCATE TABLE students;  -- 快速清空并重置自增ID\n\n-- 删除整个表（更危险！）\nDROP TABLE students;  -- 彻底删除表结构和数据\n```\n\n## 查询SELECT\n\n### 1.基础查询\n\n```sql\nSELECT * FROM students;\n\n-- 查询制定列\nSELECT id,name FROM students;\n\n-- 使用AS给字段起别名\nSELECT id,name AS '姓名', age, high, gender FROM students;\n\n-- 通过表名字段查询\nSELECT students.name FROM students;\n\n-- 给表起别名查询\nSELECT s.id,s.name,s.age FROM students AS s;\n\n-- 消除重复行\nSELECT distinct age FROM students;\n```\n\n### 2.条件查询\n\n```sql\n-- 查询年纪大于18岁的信息\nSELECT * FROM students WHERE age > 18;\n        \n-- 18岁到28岁之间(AND)\nSELECT * FROM students WHERE age >= 18 AND age =< 28;    允许使用&&\nSELECT * FROM students WHERE age BETWEEN 18 AND 28\n\n-- in (1,3,8)表示在一个非连续的范围内\n-- 查询年纪为18和34的人\nSELECT * FROM students WHERE age in (18, 34);\n\n-- 查询 年龄在17岁到34岁之间的信息\nSELECT * FROM students WHERE age BETWEEN 17 AND 34;\n\n-- 查询 年纪不在18到34岁的信息\nSELECT * FROM students WHERE age NOT BETWEEN 17 AND 34;\n```\n\n### 3.模糊查询\n\n```sql\n--  LIKE为条件关键字，%替代1个或者多个甚至是没有，类似通配符中的*\n-- 查询姓名中有‘小’的所有名字\nSELECT * FROM students WHERE name LIKE '%小%';\n\n-- 查询两个字人的名字\nSELECT * FROM students WHERE name LIKE '__';    两个下划线，一个下划线表示一个必须存在的字符\n\n-- 查询至少有2个字的名字\nSELECT * FROM students WHERE name LIKE '%__%';\n```\n\n### 4.结果排序\n\n```sql\n-- asc从小到大排列,即升序，默认机位升序排序，不需要加入此关键字\n-- desc从大到小排序，即降序\n-- ORDER BY支持多字段\n-- 查询年纪在18到34岁之间的男性，按照年纪从小到大\nSELECT * FROM students WHERE gender=1 AND age BETWEEN 18 AND 34 ORDER BY age;\n\n-- 查询年纪在18到34岁的男性，按照年纪从大到小排序，如果年龄也相等那么按照id从小到大排序；\nSELECT * FROM users WHERE gender = 1 AND age BETWEEN 18 AND 34\nORDER BY \n    age DESC,\n    id ASC;\n```\n\n### 5.聚合函数\n\n```sql\n-- 总数count \n-- 查询男性有多少人\nSELECT count(*) FROM students WHERE gender=1;\n\n-- 最大值max\n-- 查询最大的年纪\nSELECT max(age) FROM students;\n\n-- 求和sum\n-- 计算所有人的年龄总和\nSELECT sum(age) FROM students;\n\n-- 平均值avg\n-- 计算平均年纪 sum(age)/count(*)\nSELECT sum(age)/count(*) FROM students;\nSELECT avg(age),2 FROM students;\n\n-- 保留2位小数\nSELECT round(avg(age),2) FROM students;\n```\n\n### 6.结果分组\n\n```sql\n-- 按照性别分组，查询所有的性别\nSELECT gender FROM students GROUP BY gender;\n\n-- 计算每组性别的人数\nSELECT gender, count(*) FROM students GROUP BY gender;\n\n-- 查询男性组中的姓名 group_concat\nSELECT gender,group_concat(name) FROM students WHERE gender=1 GROUP BY gender;\n-- group_concat()按照拼接后的字符串显示\n```\n\n### 7.聚合HAVING\n\n```sql\n--查询每个性别平均年纪超过30岁的性别，以及姓名 HAVING avg(age) > 30\nSELECT gender, group_concat(name) FROM students GROUP BY gender HAVING avg(age) > 30;\n\n--查询每种性别中的人数多于4个的组的信息\nSELECT gender,group_concat(name) FROM students GROUP BY gender HAVING count(*)>4;\n```\n\n### 8.分页LIMIT\n\n```sql\n--显示5页\nSELECT * FROM students LIMIT 5;\n\n--分页显示，每页显示2条数据\nSELECT * FROM students LIMIT 0, 2;\n\n--按照年龄排序，查找出所有女性，并且分页显示，每页显示2条数据\nSELECT * FROM students WHERE gender=2 ORDER BY age desc LIMIT 0,2;\n```","categoryId":4,"viewCount":955,"categoryName":"MySQL","author":"球接子","authorAvatar":null,"tagIds":[2,17],"tagNames":["MySQL","数据库"]}}