`CREATE TABLE logs (id INT, message TEXT) ENGINE=MyISAM;`
• Memory:将数据存储在内存中,速度极快但不持久
`CREATE TABLE temp_data (id INT) ENGINE=MEMORY;`
• Archive:适合存储和检索大量很少被查询的历史数据
`CREATE TABLE old_logs (id INT, log_text TEXT) ENGINE=ARCHIVE;`
MySQL数据类型
1. 数值类型:
• INT:整数类型,4字节
• TINYINT:小整数,1字节
• BIGINT:大整数,8字节
• FLOAT/DOUBLE:浮点数
• DECIMAL:精确小数
2. 字符串类型:
• CHAR(n):固定长度字符串
• VARCHAR(n):可变长度字符串
• TEXT:长文本
3. 日期和时间类型:
• DATE:日期,格式'YYYY-MM-DD'
• TIME:时间,格式'HH:MM:SS'
• DATETIME:日期和时间,格式'YYYY-MM-DD HH:MM:SS'
• TIMESTAMP:时间戳
4. 其他类型:
• ENUM:枚举类型
• SET:集合类型
• BLOB:二进制大对象
二、数据库和表的基本操作
数据库操作语法详解
-- 创建数据库 CREATE DATABASE [IF NOTEXISTS] mydb [CHARACTER SET charset_name] [COLLATE collation_name]; -- 查看所有数据库 SHOW DATABASES; -- 使用数据库 USE mydb; -- 删除数据库 DROP DATABASE [IF EXISTS] mydb;
表操作语法详解
-- 创建表 CREATE TABLE students ( id INT AUTO_INCREMENT PRIMARY KEY, -- 主键,自动递增 name VARCHAR(50) NOT NULL, -- NOT NULL约束 age INTCHECK (age >0), -- CHECK约束 gender ENUM('男', '女'), -- 枚举类型 class VARCHAR(20), score FLOATDEFAULT0, -- 默认值 created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP, -- 自动记录创建时间 INDEX idx_class (class) -- 索引 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 查看表结构 DESCRIBE students; -- 或 SHOW COLUMNS FROM students; -- 修改表结构 ALTER TABLE students ADDCOLUMN email VARCHAR(100); ALTER TABLE students MODIFY COLUMN name VARCHAR(100); ALTER TABLE students DROPCOLUMN email; -- 删除表 DROPTABLE [IF EXISTS] students;
-- 插入或更新(如果主键存在则更新) INSERT INTO students (id, name, score) VALUES (1, '张三', 95) ON DUPLICATE KEY UPDATE score =95; -- 从其他表插入数据 INSERT INTO students_backup SELECT*FROM students WHERE class ='计算机科学1班'; -- 忽略错误继续执行 INSERT IGNORE INTO students (id, name, age) VALUES (1, '张三', 20);
SELECT [DISTINCT] column1, column2, ... FROM table_name [JOIN table_name2 ON join_condition] [WHEREcondition] [GROUPBYcolumn(s)] [HAVING group_condition] [ORDERBYcolumn(s) [ASC|DESC]] [LIMIT offset, row_count];
查询所有记录
`SELECT*FROM students;`
查询特定列
`SELECT name, age, score FROM students;`
条件查询与WHERE子句详解
-- 查询计算机科学1班的学生 SELECT*FROM students WHERE class ='计算机科学1班'; -- 查询成绩大于80的学生 SELECT name, score FROM students WHERE score >80; -- 查询年龄在19到21岁之间的学生 SELECT*FROM students WHERE age BETWEEN19AND21; -- 复合条件:AND, OR, NOT SELECT*FROM students WHERE (class ='计算机科学1班'OR class ='计算机科学2班') AND score >=80 ANDNOT gender ='女'; -- NULL值处理 SELECT*FROM students WHERE score ISNULL; SELECT*FROM students WHERE score ISNOT NULL;
排序与ORDER BY子句
-- 按成绩降序排列 SELECT*FROM students ORDERBY score DESC; -- 先按班级升序,再按成绩降序 SELECT*FROM students ORDERBY class ASC, score DESC; -- 按字段位置排序(不推荐,但需了解) SELECT name, age, score FROM students ORDERBY3DESC; -- 按第3列(score)排序
分组和聚合函数
-- 计算每个班级的平均分 SELECT class, AVG(score) as avg_score FROM students GROUPBY class; -- 查找每个班级的最高分和最低分 SELECT class, MAX(score) as highest_score, MIN(score) as lowest_score, COUNT(*) as student_count, SUM(score) as total_score, STDDEV(score) as score_deviation -- 标准差 FROM students WHERE score ISNOT NULL GROUPBY class; -- HAVING子句(对分组结果进行筛选) SELECT class, AVG(score) as avg_score FROM students GROUPBY class HAVING avg_score >80;
限制结果数量
-- 查询前3名学生 SELECT*FROM students ORDERBY score DESC LIMIT 3; -- 分页查询:每页5条,查询第2页 SELECT*FROM students LIMIT 5, 5; -- 偏移量5,返回5条 -- 或使用更现代的语法 SELECT*FROM students LIMIT 5OFFSET5;
模糊查询与LIKE操作符
-- 查询名字中包含"张"的学生 SELECT*FROM students WHERE name LIKE'%张%'; -- 查询以"计算机"开头的班级 SELECTDISTINCT class FROM students WHERE class LIKE'计算机%'; -- 通配符说明 -- %:匹配任意数量的字符 -- _:匹配单个字符 SELECT*FROM students WHERE name LIKE'张_'; -- 匹配"张"后跟一个字符的名字
正则表达式查询
-- 查询名字中包含数字的学生 SELECT*FROM students WHERE name REGEXP '[0-9]'; -- 查询名字以"张"或"王"开头的学生 SELECT*FROM students WHERE name REGEXP '^[张王]';
实际应用场景
期末考试后,教师需要统计班级情况:
-- 查询每个班级的及格率 SELECT class, COUNT(*) as total_students, SUM(CASEWHEN score >=60THEN1ELSE0END) as passed_students, ROUND(SUM(CASEWHEN score >=60THEN1ELSE0END) /COUNT(*) *100, 2) as pass_rate FROM students WHERE score ISNOT NULL GROUPBY class;
-- 更新张三的成绩 UPDATE students SET score =92.0WHERE name ='张三';
更新多个字段
-- 李四转班并更新信息 UPDATE students SET class ='计算机科学2班', age =20 WHERE name ='李四';
条件更新与表达式
-- 给所有90分以上的学生加5分奖励(但不超过100分) UPDATE students SET score = LEAST(score +5, 100) WHERE score >90; -- 所有学生年龄增加1岁 UPDATE students SET age = age +1; -- 使用CASE表达式进行条件更新 UPDATE students SET score =CASE WHEN score <60THEN score +5-- 不及格加5分 WHEN score >=60AND score <90THEN score +3-- 良好加3分 ELSE score -- 优秀不变 END;
多表更新
-- 基于另一个表的数据更新当前表 UPDATE students s JOIN student_extra_info sei ON s.id = sei.student_id SET s.email = sei.email, s.phone = sei.phone WHERE sei.update_flag =1;
实际应用场景
期中考试后,某些学生参加了补考,需要更新成绩:
-- 批量更新补考成绩 UPDATE students SET score =CASE WHEN name ='李四'THEN82.5 WHEN name ='钱七'THEN88.0 ELSE score END WHERE name IN ('李四', '钱七');
-- 删除已经毕业的学生 DELETEFROM students WHERE id IN ( SELECT id FROM graduated_students ); -- 假设要删除旧学期的数据并保留新学期数据 -- 创建备份 CREATE TABLE students_new_semester AS SELECT*FROM students WHERE entry_year =2025; -- 清空原表 TRUNCATETABLE students; -- 将新数据插回原表 INSERT INTO students SELECT*FROM students_new_semester; -- 删除临时表 DROPTABLE students_new_semester;
-- 内连接:查询学生及其选修的课程 SELECT s.name, c.course_name, c.teacher FROM students s JOIN student_courses sc ON s.id = sc.student_id JOIN courses c ON sc.course_id = c.course_id ORDERBY s.name; -- 左连接:查询所有学生,包括未选课的 SELECT s.name, IFNULL(c.course_name, '未选课') as course FROM students s LEFTJOIN student_courses sc ON s.id = sc.student_id LEFTJOIN courses c ON sc.course_id = c.course_id ORDERBY s.name; -- 右连接:查询所有课程,包括无人选修的 SELECT c.course_name, IFNULL(s.name, '无人选修') as student FROM student_courses sc RIGHTJOIN courses c ON sc.course_id = c.course_id LEFTJOIN students s ON sc.student_id = s.id ORDERBY c.course_name; -- 自连接:查找同班同学 SELECT s1.name, s2.name as classmate FROM students s1 JOIN students s2 ON s1.class = s2.class AND s1.id != s2.id ORDERBY s1.class, s1.name;
子查询详解
子查询是嵌套在另一个查询中的SELECT语句,可以用在:
• SELECT子句
• FROM子句
• WHERE子句
• HAVING子句
-- WHERE子句中的子查询 -- 查询选修了"数据库原理"课程的学生 SELECT name, age, class FROM students WHERE id IN ( SELECT student_id FROM student_courses WHERE course_id = (SELECT course_id FROM courses WHERE course_name ='数据库原理') ); -- FROM子句中的子查询(派生表) -- 查询每个班级的平均分,并与学生个人分数比较 SELECT s.name, s.score, c.avg_score, s.score - c.avg_score as difference FROM students s JOIN ( SELECT class, AVG(score) as avg_score FROM students GROUPBY class ) c ON s.class = c.class ORDERBY difference DESC; -- SELECT子句中的子查询(标量子查询) -- 查询每个学生选修的课程数量 SELECT s.name, s.class, (SELECTCOUNT(*) FROM student_courses WHERE student_id = s.id) AS course_count FROM students s ORDERBY course_count DESC; -- EXISTS子查询 -- 查询至少选修了一门课程的学生 SELECT name, class FROM students s WHEREEXISTS ( SELECT1FROM student_courses WHERE student_id = s.id );
公用表表达式(CTE)
CTE是一种临时结果集,可以在单个SQL语句中多次引用:
-- 使用WITH子句定义CTE WITH ClassAvg AS ( SELECT class, AVG(score) as avg_score FROM students GROUPBY class ), ClassRanking AS ( SELECT s.id, s.name, s.score, s.class, RANK() OVER (PARTITIONBY s.class ORDERBY s.score DESC) as class_rank FROM students s ) -- 使用定义的CTE SELECT r.name, r.score, r.class, r.class_rank, c.avg_score FROM ClassRanking r JOIN ClassAvg c ON r.class = c.class WHERE r.class_rank <=3 ORDERBY r.class, r.class_rank;
窗口函数
窗口函数对一组行执行计算,返回每行的值:
-- 计算每个班级中学生的排名 SELECT name, score, class, RANK() OVER (PARTITIONBY class ORDERBY score DESC) as class_rank, DENSE_RANK() OVER (PARTITIONBY class ORDERBY score DESC) as dense_rank, ROW_NUMBER() OVER (PARTITIONBY class ORDERBY score DESC) as row_num FROM students; -- 计算累计总和 SELECT name, score, class, SUM(score) OVER (PARTITIONBY class
ORDERBY score) as running_total, AVG(score) OVER (PARTITIONBY class) as class_avg FROM students;
八、事务控制
事务是一组操作,要么全部成功,要么全部失败。
-- 开始事务 START TRANSACTION; -- 执行操作 UPDATE students SET score = score +10WHERE id =1; UPDATE courses SET credits = credits +1WHERE course_id =2; -- 如果一切正常,提交事务 COMMIT; -- 如果出现问题,回滚事务 -- ROLLBACK;
START TRANSACTION; UPDATE students SET score = score +5WHERE id =1; SAVEPOINT point1; UPDATE students SET score = score +10WHERE id =2; -- 如果需要,可以回滚到保存点 ROLLBACKTOSAVEPOINT point1; -- 继续事务 UPDATE students SET score = score +15WHERE id =3; COMMIT;
九、索引与性能优化
索引是提高查询性能的关键:
-- 创建索引 CREATE INDEX idx_student_name ON students(name); -- 创建复合索引 CREATE INDEX idx_class_score ON students(class, score); -- 创建唯一索引 CREATEUNIQUE INDEX idx_email ON students(email); -- 查看表的索引 SHOW INDEX FROM students; -- 删除索引 DROP INDEX idx_student_name ON students;
索引类型
1. B-Tree索引:默认索引类型,适用于等值查询和范围查询
2. 哈希索引:仅适用于等值比较,Memory引擎支持
3. 全文索引:用于全文搜索
`CREATE FULLTEXT INDEX idx_fulltext ON articles(title, content);` AI写代码sql
4. 空间索引:用于地理空间数据
`CREATE SPATIAL INDEX idx_location ON places(location);` AI写代码sql
EXPLAIN分析查询
-- 分析查询执行计划 EXPLAIN SELECT*FROM students WHERE class ='计算机科学1班'AND score >80;
查询优化
-- 使用FORCE INDEX强制使用特定索引 SELECT*FROM students FORCE INDEX (idx_class_score) WHERE class ='计算机科学1班'AND score >80; -- 使用STRAIGHT_JOIN控制连接顺序 SELECT STRAIGHT_JOIN s.name, c.course_name FROM students s JOIN student_courses sc ON s.id = sc.student_id JOIN courses c ON sc.course_id = c.course_id;
十、视图
视图是基于SQL查询的虚拟表,可以简化复杂查询:
-- 创建视图 CREATEVIEW student_course_view AS SELECT s.id, s.name, s.class, c.course_name, c.teacher FROM students s JOIN student_courses sc ON s.id = sc.student_id JOIN courses c ON sc.course_id = c.course_id; -- 使用视图 SELECT*FROM student_course_view WHERE class ='计算机科学1班'; -- 更新视图(如果基表允许) UPDATE student_course_view SET name ='张三丰'WHERE id =1; -- 删除视图 DROPVIEW student_course_view;
视图的优点
1. 简化复杂查询:将复杂查询封装为视图
2. 提高安全性:限制用户只能访问视图中的特定列
3. 数据独立性:应用程序使用视图,底层表结构变化时不需要修改应用
十一、存储过程与函数
存储过程是一组预编译的SQL语句,可以接受参数并执行复杂操作:
-- 创建存储过程 DELIMITER // CREATEPROCEDURE update_student_score(IN student_id INT, IN new_score FLOAT) BEGIN UPDATE students SET score = new_score WHERE id = student_id; -- 条件语句 IF new_score >=90THEN INSERT INTO honor_students (student_id, honor_type) VALUES (student_id, '优秀学生'); END IF; END// DELIMITER ; -- 调用存储过程 CALL update_student_score(1, 95); -- 创建函数 DELIMITER // CREATEFUNCTION get_grade(score FLOAT) RETURNSCHAR(1) DETERMINISTIC BEGIN DECLARE grade CHAR(1); IF score >=90THEN SET grade ='A'; ELSEIF score >=80THEN SET grade ='B'; ELSEIF score >=70THEN SET grade ='C'; ELSEIF score >=60THEN SET grade ='D'; ELSE SET grade ='F'; END IF; RETURN grade; END// DELIMITER ; -- 使用函数 SELECT name, score, get_grade(score) as grade FROM students;
存储过程的高级特性
DELIMITER // CREATEPROCEDURE process_new_students(IN class_name VARCHAR(50)) BEGIN -- 声明变量 DECLARE done INTDEFAULTFALSE; DECLARE s_id INT; DECLARE s_name VARCHAR(50); -- 声明游标 DECLARE student_cursor CURSORFOR SELECT id, name FROM students WHERE class = class_name AND score ISNULL; -- 声明异常处理 DECLARE CONTINUE HANDLER FORNOT FOUND SET done =TRUE; -- 开始事务 START TRANSACTION; -- 打开游标 OPEN student_cursor; -- 循环处理 read_loop: LOOP FETCH student_cursor INTO s_id, s_name; IF done THEN LEAVE read_loop; END IF; -- 为新生创建初始记录 INSERT INTO student_records (student_id, status) VALUES (s_id, 'active'); END LOOP; -- 关闭游标 CLOSE student_cursor; -- 提交事务 COMMIT; END// DELIMITER ;
十二、触发器
触发器是在表上执行特定操作(INSERT、UPDATE、DELETE)时自动执行的特殊存储过程:
-- 创建触发器 DELIMITER // CREATETRIGGER after_student_update AFTER UPDATEON students FOREACHROW BEGIN -- 记录成绩变化 IF OLD.score != NEW.score THEN INSERT INTO score_changes (student_id, old_score, new_score, change_date) VALUES (NEW.id, OLD.score, NEW.score, NOW()); END IF; END// DELIMITER ; -- 查看触发器 SHOW TRIGGERS; -- 删除触发器 DROPTRIGGER after_student_update;