MySQL 常用语句

1 常用语句

# 取别名
SELECT CONCAT(read_pri, write_pri, admin_pri) AS priority, tel AS phone FROM prior_access WHERE id>5;

# 按多个字段依次排序
SELECT author, checked, post_time, create_time FROM thread WHERE thread_id>3 ORDER BY checked DESC, post_time, create_time DESC;

# 使用 DISTINCT 去重复数据
SELECT DISTINCT likes FROM thread_like;
SELECT COUNT(DISTINCT likes) as likes_count FROM thread_like;

# 判断字段值为null
SELECT name, unionid, choose FROM user WHERE choose IS NULL;

SELECT name, unionid, IF(choose IS NULL, "未选择", choose) as choose FROM user WHERE choose IS NULL;

SELECT author, IFNULL(to_who, "无") FROM reply WHERE reply_id>50;
SELECT author, IF(to_who IS NOT NULL, to_who, "无") FROM reply WHERE reply_id>50;

# 统计数量
SELECT COUNT(*) as total FROM reply WHERE reply_id>30;

# 转换大小写
SELECT media_type, UPPER(media_type) as big, LOWER(media_type) as small FROM media LIMIT 10;

# 以xx开头
SELECT author, content FROM thread WHERE content LIKE '测试%';
SELECT author, content FROM thread WHERE content REGEXP '^测试';

# 以xx结尾
SELECT thread_id, media_type FROM media WHERE media_type LIKE '%o';
SELECT thread_id, media_type FROM media WHERE media_type REGEXP 'o$';

# #包含xx
SELECT author, content FROM thread WHERE content LIKE '%是否%';
SELECT author, content FROM thread WHERE content REGEXP '是否';

# 特定位置包含xx
SELECT author, content FROM thread WHERE content LIKE '_80%';
SELECT author, content FROM thread WHERE content REGEXP '^.80';
SELECT author, content FROM thread WHERE content NOT LIKE '_80%';

# 利用字符串函数进行时间处理
SELECT post_time, LEFT(post_time, 4) AS year, MID(post_time, 6, 2) AS month, MID(post_time, 9, 2) AS day, RIGHT(post_time, 8) AS clock FROM thread WHERE thread_id <26;

SELECT post_time, SUBSTRING(post_time, 9) AS clock FROM thread WHERE thread_id <26;

SELECT post_time, CONCAT(LEFT(post_time, 4), "年", MID(post_time, 6, 2), "月", MID(post_time, 9, 2), "日 ", RIGHT(post_time, 8)) AS today FROM thread WHERE thread_id <26;

# 格式化时间日期
SELECT post_time, DATE_FORMAT(post_time, '%M %d, %Y') AS today FROM thread WHERE thread_id <36;
SELECT post_time, TIME_FORMAT(post_time, '%T') AS today FROM thread WHERE thread_id <36;

# 不存在，则插入
INSERT INTO picture (album, src_url, dst_url) SELECT %s, %s, %s FROM DUAL WHERE NOT EXISTS (SELECT * FROM picture WHERE album=%s AND LEFT(src_url, %s)=%s AND is_deleted=0);

# 存在，则更新
UPDATE picture SET dst_url=%s WHERE EXISTS (SELECT * FROM (SELECT * FROM picture WHERE src_url=%s AND dst_url=%s) AS temp) AND src_url=%s AND dst_url=%s;

2 导出导入数据表

# 复制数据表
mysqldump salon user > salon_user.sql
mysqldump salon2 < salon_user.sql

mysqldump salon > salon_all_tables.sql
mysqldump salon3 < salon_all_tables.sql

3 其它技巧

常用复杂语句创建视图
筛选数据可以多多利用子查询
FUNCTION 封装常用的计算到函数，返回一个值
PROCEDURE 封装常用的数据操作到过程，可返回多个值
可利用 before insert 触发器 TRIGGER 动态设置列的默认值
对于大型文档，计算其 hash 值并为 hash 建立索引，可提高查询性能
利用事件 EVENT 来执行定时数据库操作任务
利用 before insert 触发器预处理或校验数据
多表连接查询时，给表中需要比较的列都加上索引，提升性能