1. 表字段按照汉字拼音排序
命令：
若库表为uft8字符集时，需转换为gbk编码再排序：【比较常见】
order by convert(columnname(排序的列名) using gbk) asc;
order by convert(columnname(排序的列名) using gbk) desc;
若库表为gbk字符集时，直接排序：
order by columnname(排序的列名) asc;
order by columnname(排序的列名) desc;
举例：
对数据库user表的name字段按照汉字拼音排序，user表数据如下：
user表
查看user表的DDL：user表采用utf8字符集，中文不能按照拼音排序，需转换为gbk编码，再进行排序
user DDL
对user表的name字段进行gbk编码再排序：【升序的话，英文排在中文前面的；反之降序排在后面】
name asc
name desc
2. 查询表中重复记录/查询表中某字段值存在重复值
一般不存在表中有完全重复的记录，因为我们在设计数据库表时，都有主键(id)，不可重复，所以常见情况都是表中某字段值存在重复。
<1> 查询某一字段存在重复值的命令：
举例：查询user表中name字段重复值
方式一：使用group by和having 【简洁，高效，推荐】
命令格式：select columnname(字段名), count(*) as count from tablename(数据库表名) group by columnname(字段名) having count > 1;
具体命令：
select name from user group by name having count(name) > 1;
或
select name, count(*) as count from user group by name having count > 1;
或
select name, count(name) as count from user group by name having count > 1 order by count desc;
name字段存在重复值
name字段存在重复值命令扩展
方式二：使用group by和临时表
命令：select name from (select name, count(name) as count from user group by name) as usertemp where count > 1;
group by+临时表
<2> 查询多个字段同时存在重复值的命令：
举例：查询user表中name和address字段同时存在重复值
命令格式：
select columnname1(字段名1), columnname2(字段名2), count(*) as count from tablename(数据库表名) group by columnname1(字段名1), columnname2(字段名2) having count > 1;
name,address字段值都重复
<3> 查询某一字段存在重复值的具体行记录信息：
方式一：使用in + 子查询 【数据量大时，耗时长，效率低】
举例：先用子查询查出user表中name字段的重复值，然后在user全表中做in匹配。
name字段值重复的具体行记录
方式二：使用临时表做关联查询 【建立临时表，耗时短，效率高，推荐】
举例：把user表中重复的name字段值存放临时表usertemp中，使用关联查询，查出重复name字段的具体行记录信息。
临时表关联查询字段重复值行记录
说明：using() 括号必须有，关联查询 join using() 表明要关联的两个表user和usertemp，关联的字段名是一致的，都为name。
<4> 查询多个字段同时存在重复值的具体行记录信息：
举例：
name,address字段值都重复的具体行记录
3. 去除表中重复记录
表中重复记录： 指的是表中完全重复的记录，即所有字段值均重复。
一般我们在设计数据库表时，都有主键id，不可重复，所以一般不存在表中记录完全重复的情况；若出现了此情况，建议先从表结构设计上查找问题，增加主键，优化表结构。
查询表中完全重复的记录命令：【不常用】
select distinct * from tablename(数据库表名);
4. 查询第N高薪水
思路：先按薪水字段降序排序，同时去掉重复项，结合limit，即可查出。
举例：user表信息
user表信息
从user表中找出薪水第三高的用户信息
降序+去重+limit
关于limit的坑提醒：
查询user表中薪水第三高的用户信息，如下写sql，可以吗？
select distinct salary, user.* from user order by salary desc limit 3-1,1;
select distinct salary, user.* from user order by salary desc limit (3-1),1;
当然不可以，limit不支持计算表达式，带不带括号都不行，如下报错：
limit不支持计算表达式
总结：查询第N高 = 降序(desc) + 去重(distinct) + limit(不含计算表达式)
扩展：查询第N高薪水，如果不存在，返回null。
思路：先查出第N高薪水，使用ifnull函数做判断显示。
举例：
ifnull+降序+去重+limit
5. 查询涉及取余运算MOD(x,y)
MOD(x,y)：返回x除以y后的余数。
select mod(5,2); -- 返回1
菜鸟教程中有MySQL相关函数的整理介绍，地址：http://www.runoob.com/mysql/mysql-functions.html
实际应用举例：从user表中查出所有id为奇数的男性的用户信息，且按照薪资从高到低排名。
mod函数
6. 交换值
实际应用：user表中，更新操作，将男性(m)和女性(f)的性别进行交换。
update user set sex = if(sex='f','m','f');
或者
UPDATE user SET sex = (CASE sex WHEN 'm' THEN 'f' ELSE 'm' END); (为了区分字段，关键字大写)
交换值
7. 交换相邻行的值
实际应用：user表中，交换相邻两行的用户信息，若user表中用户总数为奇数的话，最后一行用户信息不变。
解题思路：交换相邻两行数据可以转换为交换相邻两行id，然后按照id排序即可。
select (
case when mod(id,2)=1 and id = (select max(id) from user) then id
when mod(id,2)=1 then id+1 else id-1
end
) as id, name, sex, age, phone, address, salary, create_time, update_time from user order by id;
交换相邻行的值
8. 相隔几天DATEDIFF(d1,d2)
DATEDIFF(d1,d2)：返回d1 -> d2之间相隔的天数
select DATEDIFF('2001-01-01','2001-02-02'); -- -32
实际应用：有一温度表weather记录着每天的平均温度，查出后一天比前一天温度高的日期。
温度表：
weather表
后一天比前一天温度高的日期：
select w1.date from weather w1 inner join weather w2 on DATEDIFF(w1.date, w2.date) = 1 and w1.temperature > w2.temperature;
后一天比前一天温度高的日期
9.MySQL连接
分为三类：
<1> inner join 内连接或等值连接
等价写法：
inner join <==> join
select * from a inner join b where a.id = b.id <==> select * from a, b where a.id = b.id;
inner join a>b
inner join a<b
<2> left join 左连接
left join
<3> right join 右连接
right join
后续持续更新，完善...
网友评论