mysql 游戏背包_MySQL实战技能包

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

实际应用举例：从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

<2> left join 左连接

left join

<3> right join 右连接

right join

后续持续更新，完善...