MySql中各种功能用sql语句实现总结

目录1.排序和分页2.聚合统计与分组3.数据增删改DML4.判断表是否存在指定字段5.向数据库中的每个表都增加或删除一个字段后续待加...1.排序和分页1.排序ORDER BY-- 升序(ASC默认)、降序(DESC) SELECT * FROM user ORDER BY age ASC; SELECT * FROM user ORDER BY create_time DESC; -- 多字段排序先按年龄降序年龄相同按创建时间升序 SELECT * FROM user ORDER BY age DESC, create_time ASC;2.分页LIMIT-- LIMIT 起始索引, 每页条数起始索引从0开始 -- 第1页10条数据 SELECT * FROM user LIMIT 0,10; -- 第2页10条数据 SELECT * FROM user LIMIT 10,10;2.聚合统计与分组1.常用聚合函数-- 统计数量 SELECT COUNT(*) FROM user; -- 统计总行数 SELECT COUNT(email) FROM user; -- 统计非空email数量 -- 求和/平均值/最大/最小值 SELECT SUM(age) FROM user; SELECT AVG(age) FROM user; SELECT MAX(age) FROM user; SELECT MIN(age) FROM user;2.分组统计GROUP BY核心GROUP BY后只能跟分组字段 聚合函数-- 按性别统计人数 SELECT gender, COUNT(*) AS 人数 FROM user GROUP BY gender; -- 按城市统计平均年龄 SELECT city, AVG(age) AS 平均年龄 FROM user GROUP BY city;3.分组后筛选HAVING区别WHERE筛选原始数据HAVING筛选分组后结果-- 统计人数大于5的城市 SELECT city, COUNT(*) AS 人数 FROM user GROUP BY city HAVING COUNT(*) 5;3.数据增删改DML1.插入数据INSERT-- 单条插入 INSERT INTO user(name, age, gender) VALUES (张三, 20, 男); -- 批量插入性能更高高效推荐 INSERT INTO user(name, age, gender) VALUES (李四,21,女),(王五,22,男);2.更新数据UPDATE必须加 WHERE 条件否则全表更新-- 条件更新 UPDATE user SET age 21, email xxx163.com WHERE id 1;3.删除数据DELETE/TRUNCATE-- 条件删除 DELETE FROM user WHERE id 1; -- 清空全表不可回滚速度远快于DELETE TRUNCATE TABLE user;4.插入或更新数据INSERT ... SET ON DUPLICATE KEY UPDATE标准写法MySQL 支持INSERT INTO 表 SET 字段值语法可直接拼接ON DUPLICATE KEY UPDATE依赖主键 / 唯一索引冲突触发更新INSERT INTO table_name SET id 1, username test, age 20 ON DUPLICATE KEY UPDATE username test, age 20;5.查询结果插入INSERT ... SELECT将一个查询结果集批量插入目标表适合数据迁移 / 复制-- 替换库名、表名、字段名 SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA 数据库名 AND TABLE_NAME 表名 AND COLUMN_NAME 字段名;如-- 复制部门10的员工到新表 INSERT INTO new_employees(id, name, dept) SELECT id, name, 20 FROM employees WHERE dept 10;支持复杂子查询、JOIN 与聚合函数。4.判断表是否存在指定字段查询 information_schema通用推荐-- 替换库名、表名、字段名 SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA 数据库名 AND TABLE_NAME 表名 AND COLUMN_NAME 字段名;返回0字段存在0不存在示例SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA testdb AND TABLE_NAME user AND COLUMN_NAME phone;5.向数据库中的每个表都增加或删除一个字段1.增加字段先判断表中是否有这个字段没有则增加有则跳过sql语句如下-- 如果存储过程存在则删除 DROP PROCEDURE IF EXISTS add_username_all_table; DELIMITER $$ /*修改语句结束符为$$类似sql server中的go语句*/ --db_name 数据库名称 --col_name 字段名 CREATE PROCEDURE add_username_all_table(IN db_name VARCHAR(64), IN col_name VARCHAR(64)) BEGIN DECLARE done INT DEFAULT 0; DECLARE tb_name VARCHAR(64); -- 游标查询指定库下所有数据表 DECLARE cur_tb CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA db_name AND TABLE_TYPEBASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1; OPEN cur_tb; tb_loop:LOOP FETCH cur_tb INTO tb_name; IF done1 THEN LEAVE tb_loop; END IF; -- 判断字段是否存在 SET is_exist (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMAdb_name AND TABLE_NAMEtb_name AND TABLE_NAMEusertable AND COLUMN_NAMEcol_name); -- 不存在则添加字段varchar(50)可自行修改类型 SET sql IF(is_exist0, CONCAT(ALTER TABLE ,db_name,.,tb_name, ADD COLUMN ,col_name, VARCHAR(255) NOT NULL COMMENT \*unVisible\;), SELECT 1;); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP tb_loop; CLOSE cur_tb; END$$ DELIMITER ; /*改回默认分号结束符*/ --调用此过程 CALL add_username_all_table(xxxxxx, userName);2.删除字段DROP PROCEDURE IF EXISTS drop_column_all_tables; DELIMITER $$ /*修改语句结束符为$$类似sql server中的go语句*/ --db_name 数据库名称 --col_name 字段名 CREATE PROCEDURE drop_column_all_tables(IN db_name VARCHAR(64), IN col_name VARCHAR(64)) BEGIN DECLARE done INT DEFAULT 0; DECLARE tb_name VARCHAR(64); DECLARE cur_tb CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA db_name AND TABLE_TYPEBASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1; OPEN cur_tb; tb_loop:LOOP FETCH cur_tb INTO tb_name; IF done1 THEN LEAVE tb_loop; END IF; -- 判断字段是否存在 SET is_exist (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMAdb_name AND TABLE_NAMEtb_name AND TABLE_NAMEusertable AND COLUMN_NAMEcol_name); -- 存在则删除字段 SET sql IF(is_exist0, CONCAT(ALTER TABLE ,db_name,.,tb_name, DROP COLUMN ,col_name,;), SELECT 1;); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP tb_loop; CLOSE cur_tb; END$$ DELIMITER ; /*改回默认分号结束符*/ -- 调用示例删除 xxxxx 库中所有表的 userName 字段 CALL drop_column_all_tables(xxxxxx, userName);后续待加...